<?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_anom AS
        SELECT 
            b.BillID AS BillNo,
            fn_billing_month_ex(BillingPeriod) AS BillMonth,
            b.BillingPeriod,
            CAST(b.BillingDate AS DATETIME) AS BillingDate,
            b.CurReading,
            b.PrvReading,
            b.BalanceBF,
            b.TotalBill,
            b.TotalPayments,
            b.BalanceCF,
            b.ConsumptionBilled,
            c.AccountNo,
            c.CustomerName,
            c.EMeterNo,
            CAST(b.DueDate AS DATETIME) AS DueDate,
            c.Address1,
            'Negative Consumption' AS AnomType
        FROM Bill b
        JOIN Customer c ON b.CustomerID = c.CustomerID
        WHERE b.ConsumptionBilled < 0

        UNION ALL

        SELECT 
            b.BillID AS BillNo,
            fn_billing_month_ex(BillingPeriod) AS BillMonth,
            b.BillingPeriod,
            CAST(b.BillingDate AS DATETIME) AS BillingDate,
            b.CurReading,
            b.PrvReading,
            b.BalanceBF,
            b.TotalBill,
            b.TotalPayments,
            b.BalanceCF,
            b.ConsumptionBilled,
            c.AccountNo,
            c.CustomerName,
            c.EMeterNo,
            CAST(b.DueDate AS DATETIME) AS DueDate,
            c.Address1,
            'Invalid Previous Reading' AS AnomType
        FROM Bill b
        JOIN Customer c ON b.CustomerID = c.CustomerID
        WHERE b.PrvReading = -1

        UNION ALL

        SELECT 
            b.BillID AS BillNo,
            fn_billing_month_ex(BillingPeriod) AS BillMonth,
            b.BillingPeriod,
            CAST(b.BillingDate AS DATETIME) AS BillingDate,
            b.CurReading,
            b.PrvReading,
            b.BalanceBF,
            b.TotalBill,
            b.TotalPayments,
            b.BalanceCF,
            b.ConsumptionBilled,
            c.AccountNo,
            c.CustomerName,
            c.EMeterNo,
            CAST(b.DueDate AS DATETIME) AS DueDate,
            c.Address1,
            'Abnormal Consumption' AS AnomType
        FROM Bill b
        JOIN Customer c ON b.CustomerID = c.CustomerID
        WHERE b.ConsumptionBilled > 3 * c.EAvgCons
        AND c.EAvgCons <> 0;");
    }

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