<?php

$SQL = "create or replace view view_company_permit_trends as

        select companies.permit_company, 
               ifnull(prev.prev_ytd_permits, 0) as prev_ytd_permits,
               ifnull(curr.ytd_permits, 0)      as ytd_permits,
               ifnull(prev.prev_ytd_value, 0)   as prev_ytd_value,
               ifnull(curr.ytd_value, 0)        as ytd_value,

          case
               when ifnull(prev.prev_ytd_permits, 0) = 0 then null 
               else round( ((ifnull(curr.ytd_permits, 0) - prev.prev_ytd_permits) / prev.prev_ytd_permits) * 100, 2 )
                end 
            as permits_pct_change,

          case
               when ifnull(prev.prev_ytd_value, 0) = 0 then null
               else round(((ifnull(curr.ytd_value, 0) - prev.prev_ytd_value) / prev.prev_ytd_value) * 100, 2 ) 
                end
            as value_pct_change,

         case
              when    ifnull(prev.prev_ytd_permits, 0) = 0 then ''
              when (  ifnull(curr.ytd_permits,      0) / prev.prev_ytd_permits) < 0.75                            then 'down'
              when (( ifnull(curr.ytd_permits,      0) - prev.prev_ytd_permits) / prev.prev_ytd_permits) >= 0.05  then 'up'
              when (( ifnull(curr.ytd_permits,      0) - prev.prev_ytd_permits) / prev.prev_ytd_permits) <= -0.05 then '='
              else '=' 
               end 
            as permits_trend,

         case
              when    ifnull(prev.prev_ytd_value, 0) = 0 then ''
              when (  ifnull(curr.ytd_value,      0) / prev.prev_ytd_value) < 0.75                          then '='
              when (( ifnull(curr.ytd_value,      0) - prev.prev_ytd_value) / prev.prev_ytd_value) >= 0.05  then 'up'
              when (( ifnull(curr.ytd_value,      0) - prev.prev_ytd_value) / prev.prev_ytd_value) <= -0.05 then 'down'
              else '='
               end 
            as value_trend
         
         from (
         
          select distinct permit_company
            from permits
           where permit_company is not null
             and trim(permit_company) <> '' ) companies
             
       left join (
          select permit_company, count(*) as prev_ytd_permits, sum(ifnull(permit_value, 0)) as prev_ytd_value
            from permits
           where permit_company is not null
             and trim(permit_company) <> ''
             and permit_permit_date >= makedate(year(curdate()) - 1, 1)
             and permit_permit_date <= date_sub(curdate(), interval 1 year)
        group by permit_company ) prev
        
              on companies.permit_company = prev.permit_company

       left join (
          select permit_company, count(*) as ytd_permits, sum(ifnull(permit_value, 0)) as ytd_value
            from permits
           where permit_company is not null
             and trim(permit_company) <> ''
             and permit_permit_date >= makedate(year(curdate()), 1)
             and permit_permit_date <= curdate()
        group by permit_company ) curr
              on companies.permit_company = curr.permit_company";
?>