<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up()
    {
        // Drop the existing view if it exists
        DB::statement("DROP VIEW IF EXISTS VIEW_BILL_COURTS");
        
        // Recreate the view with the correct table references
        DB::statement("
            CREATE OR REPLACE VIEW VIEW_BILL_COURTS AS
            SELECT
                b.BillID,
                fn_BillingMonthEx(b.BillingPeriod) AS BillMonth,
                b.BillingPeriod,
                CAST(b.BillingDate AS DATETIME) AS BillingDate,

                c.CustomerID,
                c.CustomerName,
                c.TenantName,
                c.ClientCode,
                c.AccountNo,
                CONCAT(c.Address2, ' ', c.Address3) AS SuiteInfo,
                c.Address1 AS Court,
                LEFT(c.Address3, 3) AS CourtID,
                p.PropName,
                b.BulkInvoiceID,

                CAST(b.DueDate AS DATETIME) AS DueDate,

                -- Meter information from meterreading table
                mr.MeterNo AS MeterNumber,
                mr.PrvReading AS PreviousReading,
                mr.Reading AS CurrentReading,
                mr.ReadingDate AS CurrentReadingDate,
                mr.PrvReadingDate AS PreviousReadingDate,

                -- Billing details from BillLine
                b.ConsumptionBilled,
                (SELECT SUM(BLineAmount) FROM BillLine bl WHERE bl.BillID = b.BillID AND bl.BLineDesc = 'Fixed Charge') AS FixedCharge,
                (SELECT SUM(BLineAmount) FROM BillLine bl WHERE bl.BillID = b.BillID AND bl.BLineDesc = 'Consumption') AS ConsumptionCharge,
                (SELECT SUM(BLineAmount) FROM BillLine bl WHERE bl.BillID = b.BillID AND bl.BLineDesc = 'Max Demand KVA') AS MaxDemandCharge,
                (SELECT SUM(BLineAmount) FROM BillLine bl WHERE bl.BillID = b.BillID AND bl.BLineDesc = 'Fuel Energy Cost') AS FuelEnergyCost,
                (SELECT SUM(BLineAmount) FROM BillLine bl WHERE bl.BillID = b.BillID AND bl.BLineDesc = 'Forex Exchange Adj.') AS ForexExchangeAdj,
                (SELECT SUM(BLineAmount) FROM BillLine bl WHERE bl.BillID = b.BillID AND bl.BLineDesc = 'Inflation Adj.') AS InflationAdj,
                (SELECT SUM(BLineAmount) FROM BillLine bl WHERE bl.BillID = b.BillID AND bl.BLineDesc = 'ERC Levy') AS ERCLevy,
                (SELECT SUM(BLineAmount) FROM BillLine bl WHERE bl.BillID = b.BillID AND bl.BLineDesc = 'REP Levy') AS REPLevy,
                (SELECT SUM(BLineAmount) FROM BillLine bl WHERE bl.BillID = b.BillID AND bl.BLineDesc = 'WARMA LEVY') AS WarmaLevy,
                (SELECT SUM(BLineAmount) FROM BillLine bl WHERE bl.BillID = b.BillID AND bl.BLineDesc LIKE 'VAT%') AS VAT,
                b.TotalBill,

                -- Additional fields
                COALESCE(b.BalanceBF, 0) AS BalanceBF,
                0 AS TotalPayments,  -- Will be updated by payment processing
                b.TotalBill AS BalanceCF,  -- Will be updated by payment processing

                CONCAT(c.ClientCode, '_', b.BillingPeriod) AS BillNo,
                1 AS NoOfBills,

                -- Bank details from BankDetails table
                bd.CompanyID,
                bd.PropID,
                bd.BankName,
                bd.AccountNumber,
                bd.BankBranch,
                bd.BankFullName

            FROM Bill b
            INNER JOIN customer c ON b.CustomerID = c.CustomerID
            INNER JOIN prounits pu ON c.UnitID = pu.UnitID
            INNER JOIN property p ON pu.PropID = p.PropID
            LEFT JOIN meterreading mr ON b.MeterReadingID = mr.MeterReadingID
            LEFT JOIN BankDetails bd ON p.PropID = bd.PropID
        ");
    }

    /**
     * Reverse the migrations.
     */
    public function down()
    {
        // Revert to the original view structure if needed
        // Or simply drop the view
        DB::statement("DROP VIEW IF EXISTS VIEW_BILL_COURTS");
    }
};
