<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Facades\DB;
return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        DB::statement("CREATE OR REPLACE VIEW view_tmp_summ1 AS
        SELECT 
            b.BillID AS BillNo,
            c.CustomerName,
            fn_billing_month_ex(b.BillingPeriod) AS BillMonth,
            b.BillingPeriod,
            tl.TLineName AS BLineDesc,
            bl.BLineAmount AS Amount
        FROM Bill b
        JOIN Customer c ON b.CustomerID = c.CustomerID
        JOIN BillLine bl ON b.BillID = bl.BillID
        JOIN (
            SELECT 'L01' AS TLineID, '01. Fixed Charge' AS TLineName
            UNION ALL SELECT 'L02', '02. Consumption'
            UNION ALL SELECT 'L03', '03. Fuel Costs'
            UNION ALL SELECT 'L04', '04. Forex Adj'
            UNION ALL SELECT 'L05', '05. Inflation Adj.'
            UNION ALL SELECT 'L06', '06. Max KVA'
            UNION ALL SELECT 'L07', '07. ERC Levy'
            UNION ALL SELECT 'L08', '08. REP Levy'
            UNION ALL SELECT 'L09', '09. WARMA Levy'
            UNION ALL SELECT 'VAT', '10. VAT @ 16%'
        ) AS tl ON bl.BLineTLineID = tl.TLineID

        UNION ALL

        SELECT 
            b.BillID AS BillNo,
            c.CustomerName,
            fn_billing_month_ex(b.BillingPeriod) AS BillMonth,
            b.BillingPeriod,
            'Payments' AS BLineDesc,
            0 - b.TotalPayments AS Amount
        FROM Bill b
        JOIN Customer c ON b.CustomerID = c.CustomerID

        UNION ALL

        SELECT 
            b.BillID AS BillNo,
            c.CustomerName,
            fn_billing_month_ex(b.BillingPeriod) AS BillMonth,
            b.BillingPeriod,
            'Cons (Kwh)' AS BLineDesc,
            b.ConsumptionBilled AS Amount
        FROM Bill b
        JOIN Customer c ON b.CustomerID = c.CustomerID;");
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        DB::statement('DROP VIEW IF EXISTS view_tmp_summ1');
    }
};
