<?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_bill_summ2 AS
        SELECT 
            fn_billing_month_ex(b.BillingPeriod) AS BillMonth,
            b.BillingPeriod,
            CONCAT(RIGHT(100 + bl.BLineID, 2), '. ', bl.BLineDesc) AS BLineDesc,
            SUM(0 - bl.BLineAmount) AS Amount,
            b.BulkInvoiceID,
            CONCAT('Total Billing - ' COLLATE utf8mb4_unicode_ci, fn_billing_month_ex(b.BillingPeriod)) AS CustomerName,
            'Kshs. ' COLLATE utf8mb4_unicode_ci AS LineType
        FROM bill b
        JOIN customer c ON b.CustomerID = c.CustomerID
        JOIN billline bl ON b.BillID = bl.BillID
        GROUP BY 
            fn_billing_month_ex(b.BillingPeriod),
            b.BillingPeriod,
            CONCAT(RIGHT(100 + bl.BLineID, 2), '. ', bl.BLineDesc),
            b.BulkInvoiceID,
            CONCAT('Total Billing - ' COLLATE utf8mb4_unicode_ci, fn_billing_month_ex(b.BillingPeriod))

        UNION ALL

        SELECT 
            fn_billing_month_ex(b.BillingPeriod) AS BillMonth,
            b.BillingPeriod,
            CONCAT(RIGHT(100 + tl.TLinePriority, 2), '. ', bl.BulkLineDesc) AS BLineDesc,
            bl.BulkLineAmount AS Amount,
            b.BulkInvoiceID,
            CONCAT('KPLC Bill ' COLLATE utf8mb4_unicode_ci, b.InvoiceNo) AS CustomerName,
            'Kshs. ' COLLATE utf8mb4_unicode_ci AS LineType
        FROM bulkinvoiceline bl
        JOIN tariffline tl ON bl.BulkCategID = tl.CategoryID AND bl.BulkLineID = tl.TLineID
        JOIN bulkinvoice b ON bl.BulkInvoiceID = b.BulkInvoiceID
        WHERE bl.BulkLineAmount <> 0

        UNION ALL

        SELECT 
            fn_billing_month_ex(b.BillingPeriod) AS BillMonth,
            b.BillingPeriod,
            ' Cons. (Kwh) ' COLLATE utf8mb4_unicode_ci AS BLineDesc,
            SUM(0 - b.ConsumptionBilled) AS Amount,
            b.BulkInvoiceID,
            CONCAT('Total Billing - ' COLLATE utf8mb4_unicode_ci, fn_billing_month_ex(b.BillingPeriod)) AS CustomerName,
            'Kwh ' COLLATE utf8mb4_unicode_ci AS LineType
        FROM bill b
        JOIN customer c ON b.CustomerID = c.CustomerID
        GROUP BY 
            fn_billing_month_ex(b.BillingPeriod),
            b.BillingPeriod,
            b.BulkInvoiceID,
            CONCAT('Total Billing - ' COLLATE utf8mb4_unicode_ci, fn_billing_month_ex(b.BillingPeriod))

        UNION ALL

        SELECT 
            fn_billing_month_ex(BillingPeriod) AS BillMonth,
            BillingPeriod,
            ' Cons. (Kwh) ' COLLATE utf8mb4_unicode_ci AS BLineDesc,
            Consumption AS Amount,
            BulkInvoiceID,
            CONCAT('KPLC Bill ' COLLATE utf8mb4_unicode_ci, InvoiceNo) AS CustomerName,
            'Kwh ' COLLATE utf8mb4_unicode_ci AS LineType
        FROM bulkinvoice;");
    }

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