<?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('bulkinvoice', function (Blueprint $table) {
            $table->id('BulkInvoiceID');
            $table->unsignedBigInteger('CompanyID');
            $table->integer('BillingPeriod');
            $table->string('ConsType', 6);
            $table->unsignedBigInteger('PropID');
            $table->string('InvoiceNo', 50);
            $table->date('InvoiceDate');
            $table->bigInteger('Consumption');
            $table->decimal('FixedAmount', 19, 4)->default(0);
            $table->decimal('ConsAmount', 19, 4)->default(0);
            $table->decimal('InvoiceAmount', 19, 4)->default(0);
            $table->date('DueDate')->nullable();
            $table->bigInteger('CurrentReading1')->default(0);
            $table->bigInteger('PreviousReading1')->default(0);
            $table->bigInteger('CurrentReading2')->default(0);
            $table->bigInteger('PreviousReading2')->default(0);
            $table->string('InvoiceComments', 100)->nullable();
            $table->string('Status', 20)->default('Pending');

            // Unique constraint - one invoice per property per billing period per consumption type
            // $table->unique(['CompanyID','PropID', 'BillingPeriod', 'ConsType'], 'IX_UK_BulkInvoice');

            // Foreign keys - temporarily disable foreign key checks for tblconstype
            $table->foreign('CompanyID')
                  ->references('CompanyID')
                  ->on('company')
                  ->onDelete('cascade');
                  
            $table->foreign('PropID')
                  ->references('PropID')
                  ->on('property')
                  ->onDelete('cascade');
                  
            // Temporarily disabled due to potential issues with tblconstype
            // $table->foreign('ConsType')
            //       ->references('tID')
            //       ->on('tblconstype')
            //       ->onDelete('cascade');
        });
    }

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