<?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_period_to_bill AS
        SELECT 
            bi.BulkInvoiceID,
            c.CompanyName,
            mr.BillMonth,
            bi.BillingPeriod,
            bi.CompanyID,
            (SELECT COUNT(*) FROM Bill WHERE BulkInvoiceID = bi.BulkInvoiceID) AS BillNbr
        FROM (
            SELECT CompanyID, BillingPeriod, fn_billing_month_ex(BillingPeriod) AS BillMonth
            FROM (
                SELECT CompanyID, MAX(BillingPeriod) AS BillingPeriod
                FROM MeterReading
                GROUP BY CompanyID
            ) AS mr
        ) AS mr
        JOIN Company c ON mr.CompanyID = c.CompanyID
        JOIN BulkInvoice bi ON mr.CompanyID = bi.CompanyID AND mr.BillingPeriod = bi.BillingPeriod;");
    }

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