<?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('bill', function (Blueprint $table) {

            $table->increments('BillID');

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

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

            $table->integer('CurReading')->nullable();
            $table->integer('PrvReading')->nullable();
            $table->date('CurReadingDate')->nullable();
            $table->date('PrvReadingDate')->nullable();

            $table->date('DueDate')->nullable();

            $table->integer('ConsumptionBilled')->default(0);

            $table->decimal('BalanceBF', 19, 4)->default(0);
            $table->decimal('TotalPayments', 19, 4)->default(0);
            $table->decimal('TotalBill', 19, 4)->default(0);
            $table->decimal('BalanceCF', 19, 4)->default(0);

            $table->dateTime('LastUpdateDate')->useCurrent();
            $table->string('LastUpdateUser', 10)->default('system');

            $table->char('BillQuality', 1)->default('E');
            $table->string('IsPosted', 10)->nullable();

            // Foreign Keys
            $table->foreign('CustomerID')
                  ->references('CustomerID')
                  ->on('customer')
                  ->onDelete('cascade');

            $table->foreign('MeterReadingID')
                  ->references('MeterReadingID')
                  ->on('meterreading')
                  ->onDelete('set null');

            $table->foreign('BulkInvoiceID')
                  ->references('BulkInvoiceID')
                  ->on('bulkinvoice')
                  ->onDelete('set null');

            // Temporarily disabled due to potential issues with tblbilltype
            // $table->foreign('BillType')
            //       ->references('tID')
            //       ->on('tblbilltype');
        });
    }

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