<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        Schema::create('BillDEZ', function (Blueprint $table) {

            // Primary Key
            $table->integer('BillID')->primary();

            // Core References
            $table->integer('BillingPeriod');
            $table->integer('BulkInvoiceID')->nullable();
            $table->integer('CustomerID');
            $table->date('BillingDate');
            $table->integer('MeterReadingID')->nullable();

            // Billing Info
            $table->string('BillType', 6);

            // Meter Readings
            $table->integer('CurReading')->nullable();
            $table->integer('PrvReading')->nullable();
            $table->date('CurReadingDate')->nullable();
            $table->date('PrvReadingDate')->nullable();
            $table->date('DueDate')->nullable();

            $table->integer('ConsumptionBilled');

            // Financials (SQL Server money → decimal)
            $table->decimal('BalanceBF', 19, 4);
            $table->decimal('TotalPayments', 19, 4);
            $table->decimal('TotalBill', 19, 4);
            $table->decimal('BalanceCF', 19, 4);

            // Audit
            $table->dateTime('LastUpdateDate');
            $table->string('LastUpdateUser', 10);
            $table->char('BillQuality', 1);

            // Indexes for performance
            $table->index('CustomerID');
            $table->index('BillingPeriod');
            $table->index('BulkInvoiceID');
            $table->index('MeterReadingID');

            /*
            // Optional foreign keys
            $table->foreign('CustomerID')
                  ->references('CustomerID')
                  ->on('CUSTOMER')
                  ->onDelete('cascade');

            $table->foreign('MeterReadingID')
                  ->references('MeterReadingID')
                  ->on('MeterReading')
                  ->onDelete('set null');
            */
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('BillDEZ');
    }
};
