require 'skr/db/migration_helpers'

class AddPaymentsToInvDetails < ActiveRecord::Migration

    def view(select = '', join = '')
      <<-EOS.squish
      create or replace view #{skr_prefix}inv_details as
         select
           inv.id as invoice_id
           , so.visible_id as sales_order_visible_id
           , pt.id as pick_ticket_id
           , to_char(so.created_at,'YYYY-MM-DD')   as string_order_date
           , to_char(inv.created_at,'YYYY-MM-DD')  as string_invoice_date
           , cust.code as customer_code, cust.name as customer_name
           , ba.name as bill_addr_name
           , coalesce( ttls.total,0.0 ) as invoice_total
           , coalesce( ttls.num_lines, 0 ) as num_lines
           , coalesce( ttls.other_charge_total, 0 ) as total_other_charge_amount
           , coalesce( ttls.total,0.0 ) - coalesce( ttls.other_charge_total, 0.0 ) as subtotal_amount
           #{select}
         from #{skr_prefix}invoices inv
         join #{skr_prefix}customers cust on cust.id = inv.customer_id
         left join #{skr_prefix}addresses ba on ba.id = inv.billing_address_id
         left join #{skr_prefix}sales_orders so on so.id = inv.sales_order_id
         left join #{skr_prefix}pick_tickets pt on pt.id = inv.pick_ticket_id
         left join (
             select
               invoice_id,
               sum(ivl.qty*ivl.price) as total,
               sum( case when s.is_other_charge then ivl.qty*ivl.price else 0 end ) as other_charge_total,
               count(ivl.*) as num_lines
             from #{skr_prefix}inv_lines ivl
             join #{skr_prefix}sku_locs sl on sl.id = ivl.sku_loc_id
             join #{skr_prefix}skus s on s.id = sl.sku_id
             group by invoice_id
          ) ttls on ttls.invoice_id = inv.id
          #{join}
      EOS
    end

    def up
        execute view(", coalesce( payments.amount_paid, 0 ) as amount_paid\n",
             "left join (
                 select invoice_id, sum(amount) as amount_paid
                 from #{skr_prefix}payments where invoice_id is not NULL group by invoice_id
              ) as payments on payments.invoice_id = inv.id")
    end

    def down
        execute "drop view #{skr_prefix}inv_details"
        execute view
    end

end