<?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_customer_avg AS
        SELECT 
            CustomerID,
            AVG(Consumption) AS AvgCons,
            CONCAT('UPDATE CUSTOMER SET EAvgCons = ', AVG(Consumption), ' WHERE CustomerID = ', CustomerID) AS AVG_QRY
        FROM MeterReading
        WHERE BillingPeriod NOT IN (
            SELECT MAX(BillingPeriod) FROM TMP_MeterReading
        )
        GROUP BY CustomerID;");
    }

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