class SubscriptionReportAddCurrentUserAddress < ActiveRecord::Migration
  def up
    execute <<-SQL

    CREATE OR REPLACE VIEW "public"."subscription_monthly_report_for_project_owners" AS 
    SELECT s.project_id,
       u.name,
       u.public_name,
       u.email,
       replace((round((((s.data ->> 'amount'::text))::numeric / (100)::numeric), 2))::text, '.'::text, ','::text) AS amount,
       replace((round((((((s.data ->> 'amount'::text))::numeric * proj.service_fee) / (100)::numeric) - fees.gateway_fee), 2))::text, '.'::text, ','::text) AS service_fee,
       replace((fees.gateway_fee)::text, '.'::text, ','::text) AS payment_method_fee,
       replace((round(((((s.data ->> 'amount'::text))::numeric - (((s.data ->> 'amount'::text))::numeric * proj.service_fee)) / (100)::numeric), 2))::text, '.'::text, ','::text) AS net_value,
       r.title,
       r.description,
       (s.data ->> 'payment_method'::text) AS payment_method,
       zone_timestamp(s.created_at) AS created_at,
       zone_timestamp(pst.created_at) AS paid_at,
       'Confirmado'::text AS confirmed,
       u.id AS user_id,
           CASE
               WHEN COALESCE(((sub.checkout_data ->> 'anonymous'::text) = 'true'::text), ((s.data ->> 'anonymous'::text) = 'true'::text)) THEN 'sim'::text
               ELSE 'não'::text
           END AS anonymous,
       user_address.street::text,
       user_address.complement::text,
       user_address.number::text,
       user_address.neighborhood::text,
       user_address.city::text,
       user_address.state::text,
       user_address.zipcode::text,
       u.cpf
      FROM (((((((common_schema.catalog_payments s
        JOIN users u ON ((u.common_id = s.user_id)))
        LEFT JOIN rewards r ON ((r.common_id = s.reward_id)))
        JOIN common_schema.payment_status_transitions pst ON (((pst.catalog_payment_id = s.id) AND (pst.to_status = 'paid'::payment_service.payment_status))))
        LEFT JOIN common_schema.subscriptions sub ON ((sub.id = s.subscription_id)))
        LEFT JOIN projects proj ON ((sub.project_id = proj.common_id)))
        LEFT JOIN LATERAL ( SELECT round((
                   CASE
                       WHEN ((s.gateway_general_data ->> 'gateway_payment_method'::text) = 'credit_card'::text) THEN (COALESCE(((s.gateway_general_data ->> 'gateway_cost'::text))::numeric, (0)::numeric) + COALESCE(((s.gateway_general_data ->> 'payable_total_fee'::text))::numeric, (0)::numeric))
                       ELSE (COALESCE((s.gateway_general_data ->> 'payable_total_fee'::text), (s.gateway_general_data ->> 'gateway_cost'::text), '0'::text))::numeric
                   END / (100)::numeric), 2) AS gateway_fee) fees ON (true))
                   
           LEFT JOIN LATERAL (
           
           select 
               a.id,
               a.address_street as street,
               a.address_complement as complement,
               a.address_number as number,
               a.address_neighbourhood as neighborhood,
               a.address_city as city,
               s.acronym as state,
               a.address_zip_code as zipcode
               
           from 
               addresses as a left join states as s on a.state_id = s.id where u.address_id = a.id
       ) user_address ON (true))
     WHERE (s.status = 'paid'::payment_service.payment_status)
     GROUP BY     
           user_address.street,
           user_address.complement,
           user_address.number,
           user_address.neighborhood,
           user_address.city,
           user_address.state,
           user_address.zipcode,
           proj.id, s.gateway_general_data, s.project_id, u.name, u.public_name, u.email, s.data, r.title, r.description, s.created_at, s.updated_at, u.id, pst.created_at, sub.checkout_data, fees.gateway_fee;
   
   
    SQL
  end

  def down
    execute <<-SQL

    CREATE OR REPLACE VIEW "public"."subscription_monthly_report_for_project_owners" AS 
    SELECT s.project_id,
       u.name,
       u.public_name,
       u.email,
       replace((round((((s.data ->> 'amount'::text))::numeric / (100)::numeric), 2))::text, '.'::text, ','::text) AS amount,
       replace((round((((((s.data ->> 'amount'::text))::numeric * proj.service_fee) / (100)::numeric) - fees.gateway_fee), 2))::text, '.'::text, ','::text) AS service_fee,
       replace((fees.gateway_fee)::text, '.'::text, ','::text) AS payment_method_fee,
       replace((round(((((s.data ->> 'amount'::text))::numeric - (((s.data ->> 'amount'::text))::numeric * proj.service_fee)) / (100)::numeric), 2))::text, '.'::text, ','::text) AS net_value,
       r.title,
       r.description,
       (s.data ->> 'payment_method'::text) AS payment_method,
       zone_timestamp(s.created_at) AS created_at,
       zone_timestamp(pst.created_at) AS paid_at,
       'Confirmado'::text AS confirmed,
       u.id AS user_id,
           CASE
               WHEN COALESCE(((sub.checkout_data ->> 'anonymous'::text) = 'true'::text), ((s.data ->> 'anonymous'::text) = 'true'::text)) THEN 'sim'::text
               ELSE 'não'::text
           END AS anonymous,
       (((s.data -> 'customer'::text) -> 'address'::text) ->> 'street'::text) AS street,
       (((s.data -> 'customer'::text) -> 'address'::text) ->> 'complementary'::text) AS complement,
       (((s.data -> 'customer'::text) -> 'address'::text) ->> 'street_number'::text) AS number,
       (((s.data -> 'customer'::text) -> 'address'::text) ->> 'neighborhood'::text) AS neighborhood,
       (((s.data -> 'customer'::text) -> 'address'::text) ->> 'city'::text) AS city,
       (((s.data -> 'customer'::text) -> 'address'::text) ->> 'state'::text) AS state,
       (((s.data -> 'customer'::text) -> 'address'::text) ->> 'zipcode'::text) AS zipcode,
       u.cpf
      FROM ((((((common_schema.catalog_payments s
        JOIN users u ON ((u.common_id = s.user_id)))
        LEFT JOIN rewards r ON ((r.common_id = s.reward_id)))
        JOIN common_schema.payment_status_transitions pst ON (((pst.catalog_payment_id = s.id) AND (pst.to_status = 'paid'::payment_service.payment_status))))
        LEFT JOIN common_schema.subscriptions sub ON ((sub.id = s.subscription_id)))
        LEFT JOIN projects proj ON ((sub.project_id = proj.common_id)))
        LEFT JOIN LATERAL ( SELECT round((
                   CASE
                       WHEN ((s.gateway_general_data ->> 'gateway_payment_method'::text) = 'credit_card'::text) THEN (COALESCE(((s.gateway_general_data ->> 'gateway_cost'::text))::numeric, (0)::numeric) + COALESCE(((s.gateway_general_data ->> 'payable_total_fee'::text))::numeric, (0)::numeric))
                       ELSE (COALESCE((s.gateway_general_data ->> 'payable_total_fee'::text), (s.gateway_general_data ->> 'gateway_cost'::text), '0'::text))::numeric
                   END / (100)::numeric), 2) AS gateway_fee) fees ON (true))
     WHERE (s.status = 'paid'::payment_service.payment_status)
     GROUP BY proj.id, s.gateway_general_data, s.project_id, u.name, u.public_name, u.email, s.data, r.title, r.description, s.created_at, s.updated_at, u.id, pst.created_at, sub.checkout_data, fees.gateway_fee;
    
    SQL
  end
end
