<?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_print_ex AS
        SELECT 
            b.BillID AS BillNo,
            fn_billing_month_ex(b.BillingPeriod) AS BillMonth,
            b.BillingPeriod,
            CAST(b.BillingDate AS DATETIME) AS BillingDate,
            b.CurReading,
            b.PrvReading,
            0 AS BalanceBF,
            b.TotalBill,
            b.TotalPayments,
            b.TotalBill AS BalanceCF,
            b.ConsumptionBilled,
            c.AccountNo,
            c.CustomerName,
            c.EMeterNo,
            CAST(b.DueDate AS DATETIME) AS DueDate,
            p.PropName,
            pu.UnitIdentity AS ShopNo,
            b.BulkInvoiceID,
            CAST(b.CurReadingDate AS DATETIME) AS CurReadingDate,
            CAST(b.PrvReadingDate AS DATETIME) AS PrvReadingDate,
            c.Address1 AS Court,
            c.Address2 AS Floor,
            c.TelNo AS Suite,
            c.AddressLine,
            c.Address3 AS Suffix,
            LEFT(c.Address3,3) AS CourtID,
            c.TenantName
        FROM bill b
        JOIN customer c ON b.CustomerID = c.CustomerID
        JOIN prounits pu ON c.UnitID = pu.UnitID
        JOIN property p ON pu.PropID = p.PropID;");
    }

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