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

    DROP VIEW subscription_monthly_report_for_project_owners;
    CREATE OR REPLACE VIEW 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, '.', ',') AS amount,
        replace(round(((s.data->>'amount'::text)::numeric * proj.service_fee - (s.gateway_general_data->>'gateway_cost'::text)::numeric) / 100, 2)::text, '.', ',')   as service_fee,
        replace(round((s.gateway_general_data->>'gateway_cost'::text)::numeric / 100, 2)::text, '.', ',')   as payment_method_fee,
        replace(round(((s.data->>'amount'::text)::numeric - (s.data->>'amount'::text)::numeric * proj.service_fee) / 100, 2)::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
    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;
    
    grant select on subscription_monthly_report_for_project_owners to admin, web_user;

    SQL
  end

  def down
    execute <<-SQL

      DROP VIEW "subscription_monthly_report_for_project_owners";
      CREATE OR REPLACE VIEW "public"."subscription_monthly_report_for_project_owners" AS 
      SELECT s.project_id,
          u.name,
          u.public_name,
          u.email,
          (((s.data ->> 'amount'::text))::numeric / (100)::numeric) AS amount,
          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)))
        WHERE (s.status = 'paid'::payment_service.payment_status)
        GROUP BY 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;

      grant select on subscription_monthly_report_for_project_owners to admin, web_user;
    SQL
  end
end
