mig

class RevenueByProductBreakdowns < ActiveRecord::Migration
  def up
    execute <<-SQL
      CREATE MATERIALIZED VIEW revenue_by_product_breaakdowns AS
        SELECT (sum(payments_amount) - sum(refunds_amount) - sum(charges_amount)) AS revenue_amount,
               sum(payments_amount) AS payments_amount,
               sum(refunds_amount) AS refunds_amount,
               sum(charges_amount) AS charges_amount,
               sum(gateway_charges_amount) AS gateway_charges_amount,
               sum(ticketinghub_charges_amount) AS ticketinghub_charges_amount,
               channel_id,
               channel_name,
               user_id,
               user_name,
               location_id,
               location_name,
               reseller_id,
               reseller_name,
               supplier_id,
               currency,
               created_at_date,
               payment_type
        FROM
        (
          SELECT
               sum(booking_payments.amount * orders.exchange_rate) as payments_amount,
               0 AS refunds_amount,
               0 AS charges_amount,
               0 AS ticketinghub_charges_amount,
               0 AS gateway_charges_amount,
               channels.id AS channel_id,
               channels.name AS channel_name,
               users.id AS user_id,
               users.full_name AS user_name,
               locations.id AS location_id,
               locations.name AS location_name,
               resellers.id AS reseller_id,
               resellers.name AS reseller_name,
               orders.supplier_id as supplier_id,
               orders.currency AS currency,
               payments.created_at::date AS created_at_date,
               payments.type AS payment_type,
               products.id as product_id
          FROM "booking_payments"
          JOIN "payments" ON "payments"."id" = "booking_payments"."payment_id"
          LEFT OUTER JOIN "orders" ON "orders"."id" = "payments"."order_id"
          LEFT OUTER JOIN "bookings" ON "bookings"."id" = "booking_payments"."booking_id"
          LEFT OUTER JOIN "products" ON "products"."id" = "bookings"."product_id"
          LEFT OUTER JOIN "channels" ON "channels"."id" = "orders"."channel_id"
          LEFT OUTER JOIN "users" ON "users"."id" = "orders"."user_id"
          LEFT OUTER JOIN "locations" ON "locations"."id" = "orders"."location_id"
          LEFT OUTER JOIN "resellers" ON "resellers"."id" = "orders"."reseller_id"
          WHERE payments.confirmed_at IS NOT NULL
          GROUP BY channels.id,
                   channels.name,
                   users.id,
                   users.full_name,
                   locations.id,
                   locations.name,
                   resellers.id,
                   resellers.name,
                   payments.created_at::date,
                   orders.supplier_id,
                   orders.currency,
                   payments.type,
                   products.id

          UNION ALL

            SELECT 0 AS payments_amount,
                  sum(booking_refunds.amount * orders.exchange_rate) as refunds_amount,
                   0 AS charges_amount,
                   0 AS ticketinghub_charges_amount,
                   0 AS gateway_charges_amount,
                   channels.id AS channel_id,
                   channels.name AS channel_name,
                   users.id AS user_id,
                   users.full_name AS user_name,
                   locations.id AS location_id,
                   locations.name AS location_name,
                   resellers.id AS reseller_id,
                   resellers.name AS reseller_name,
                   orders.supplier_id as supplier_id,
                   orders.currency AS currency,
                   refunds.created_at::date AS created_at_date,
                   payments.type AS payment_type,
                   products.id AS product_id
            FROM "booking_refunds"
            JOIN "refunds" ON "refunds"."id" = "booking_refunds"."refund_id"
            LEFT OUTER JOIN "payments" ON "payments"."id" = "refunds"."payment_id"
            LEFT OUTER JOIN "orders" ON "orders"."id" = "payments"."order_id"
            LEFT OUTER JOIN "bookings" ON "bookings"."id" = "booking_refunds"."booking_id"
            LEFT OUTER JOIN "products" ON "products"."id" = "bookings"."product_id"
            LEFT OUTER JOIN "channels" ON "channels"."id" = "orders"."channel_id"
            LEFT OUTER JOIN "users" ON "users"."id" = "orders"."user_id"
            LEFT OUTER JOIN "locations" ON "locations"."id" = "orders"."location_id"
            LEFT OUTER JOIN "resellers" ON "resellers"."id" = "orders"."reseller_id"
            GROUP BY channels.id,
                     channels.name,
                     users.id,
                     users.full_name,
                     locations.id,
                     locations.name,
                     resellers.id,
                     resellers.name,
                     refunds.created_at::date,
                     orders.supplier_id,
                     orders.currency,
                     payments.type,
                     products.id

          UNION ALL

            SELECT 0 AS payments_amount,
                   0 AS refunds_amount,
                   sum(charges.amount * charges.exchange_rate) as charges_amount,
                   sum(CASE when (payee = 'ticketinghub') THEN charges.amount * charges.exchange_rate ELSE 0 END) as ticketinghub_charges_amount,
                   sum(CASE when (payee != 'ticketinghub') THEN charges.amount * charges.exchange_rate ELSE 0 END) as gateway_charges_amount,
                   channels.id AS channel_id,
                   channels.name AS channel_name,
                   users.id AS user_id,
                   users.full_name AS user_name,
                   locations.id AS location_id,
                   locations.name AS location_name,
                   resellers.id AS reseller_id,
                   resellers.name AS reseller_name,
                   orders.supplier_id as supplier_id,
                   orders.currency AS currency,
                   charges.created_at::date AS created_at_date,
                   payments.type AS payment_type
            FROM "charges"
            LEFT OUTER JOIN "adjustments" ON "adjustments"."id" = "charges"."context_id" AND "charges"."context_type" = 'Adjustment'
            LEFT OUTER JOIN "payments" ON "payments"."id" = "charges"."context_id" AND "charges"."context_type" = 'Payment'
            LEFT OUTER JOIN "refunds" ON "refunds"."id" = "charges"."context_id" AND "charges"."context_type" = 'Refund'
            LEFT OUTER JOIN "payments" AS "refunds_payments" ON "refunds"."payment_id" = "refunds_payments"."id"
            LEFT OUTER JOIN "disputes" ON "disputes"."id" = "adjustments"."dispute_id"
            LEFT OUTER JOIN "payments" AS "disputes_payments" ON "disputes"."id" = "disputes_payments"."dispute_id"
            LEFT OUTER JOIN "orders" ON ("orders"."id" = "payments"."order_id" OR "orders"."id" = "disputes_payments"."order_id" OR "orders"."id" = "refunds_payments"."order_id")
            LEFT OUTER JOIN "channels" ON "channels"."id" = "orders"."channel_id"
            LEFT OUTER JOIN "users" ON "users"."id" = "orders"."user_id"
            LEFT OUTER JOIN "locations" ON "locations"."id" = "orders"."location_id"
            LEFT OUTER JOIN "resellers" ON "resellers"."id" = "orders"."reseller_id"
            GROUP BY channels.id,
                     channels.name,
                     users.id,
                     users.full_name,
                     locations.id,
                     locations.name,
                     resellers.id,
                     resellers.name,
                     charges.created_at::date,
                     orders.supplier_id,
                     orders.currency,
                     payments.type
          ) x
          GROUP BY channel_id,
                   channel_name,
                   user_id,
                   user_name,
                   location_id,
                   location_name,
                   reseller_id,
                   reseller_name,
                   supplier_id,
                   currency,
                   created_at_date,
                   payment_type
    ;
    SQL

    add_index :revenue_breakdowns, :supplier_id
    add_index :revenue_breakdowns, [:supplier_id, :created_at_date]
    add_index :revenue_breakdowns, :reseller_id
    add_index :revenue_breakdowns, :location_id
    add_index :revenue_breakdowns, :user_id
    add_index :revenue_breakdowns, :channel_id
    add_index :revenue_breakdowns, :payment_type
  end

  def down
    execute "DROP MATERIALIZED VIEW revenue_breakdowns;"
  end

end