<?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_summ1 AS
        SELECT 
            b.BillID AS BillNo,
            c.CustomerName,
            fn_billing_month_ex(b.BillingPeriod) AS BillMonth,
            b.BillingPeriod,
            CONCAT(RIGHT(100 + bl.BLineID, 2), '. ', bl.BLineDesc) 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;");
    }

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