<?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_invoice_summ AS
        SELECT 
            b.BillingPeriod,
            c.CompanyName,
            p.PropName,
            COUNT(*) AS Nbr
        FROM Bill b
        JOIN BulkInvoice bl ON b.BulkInvoiceID = bl.BulkInvoiceID
        JOIN Company c ON bl.CompanyID = c.CompanyID
        JOIN Property p ON c.CompanyID = p.CompanyID
        WHERE b.BillingPeriod > 201402
        AND b.TotalBill <> 0
        GROUP BY b.BillingPeriod, c.CompanyName, p.PropName
        ORDER BY b.BillingPeriod DESC, c.CompanyName, p.PropName
        LIMIT 40;");
    }

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