<?php

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

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        // Ensure the company table exists and has the correct structure
        if (!Schema::hasTable('company')) {
            Schema::create('company', function (Blueprint $table) {
                $table->unsignedInteger('CompanyID');
                $table->primary('CompanyID');
                $table->string('CompanyName', 50);
                $table->engine = 'InnoDB';
            });
        }

        // Set the connection character set and collation
        DB::statement('SET FOREIGN_KEY_CHECKS=0;');
        
        Schema::create('maintenance_requests', function (Blueprint $table) {
            // Set table options
            $table->engine = 'InnoDB';
            $table->charset = 'utf8mb4';
            $table->collation = 'utf8mb4_unicode_ci';
            $table->id('request_id');

            // Ownership
            $table->unsignedBigInteger('CompanyID');
            $table->unsignedBigInteger('property_id');
            $table->unsignedBigInteger('block_id')->nullable();
            $table->unsignedBigInteger('unit_id');
            $table->unsignedBigInteger('customer_id');

            // Request Details
            $table->string('title', 150);
            $table->text('description');
            $table->string('category', 100);
            $table->enum('priority', ['Low', 'Medium', 'High', 'Emergency'])->default('Medium');

            // Assignment Section
            $table->unsignedBigInteger('assigned_technician_id')->nullable();
            $table->timestamp('assigned_date')->nullable();

            // Status Tracking
            $table->enum('status', [
                'Pending',
                'Approved',
                'Assigned',
                'In Progress',
                'Completed',
                'Rejected',
                'Cancelled'
            ])->default('Pending');

            // Resolution Section
            $table->text('resolution_notes')->nullable();
            $table->timestamp('completed_date')->nullable();

            // Attachments
            $table->string('attachment_path', 255)->nullable();

            // Audit Fields
            $table->timestamps();
            $table->unsignedBigInteger('created_by');
            $table->unsignedBigInteger('updated_by')->nullable();

            // Indexes
            $table->index('customer_id');
            $table->index('unit_id');
            $table->index('assigned_technician_id');
            $table->index('status');
            $table->index('CompanyID');

            // Foreign Keys
            if (Schema::hasTable('company') && Schema::hasColumn('company', 'CompanyID')) {
                $table->foreign('CompanyID', 'fk_maintenance_requests_company')
                      ->references('CompanyID')
                      ->on('company')
                      ->onDelete('cascade');
            }

            $table->foreign('property_id', 'fk_maintenance_requests_property')
                  ->references('PropID')
                  ->on('property')
                  ->onDelete('cascade');

            $table->foreign('block_id', 'fk_maintenance_requests_block')
                  ->references('BlockID')
                  ->on('blocks')
                  ->onDelete('set null');

            $table->foreign('unit_id', 'fk_maintenance_requests_unit')
                  ->references('UnitID')
                  ->on('prounits')
                  ->onDelete('cascade');

            $table->foreign('customer_id', 'fk_maintenance_requests_customer')
                  ->references('CustomerID')
                  ->on('customer')
                  ->onDelete('cascade');

            $table->foreign('assigned_technician_id', 'fk_maintenance_requests_technician')
                  ->references('id')
                  ->on('users')
                  ->onDelete('set null');
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        DB::statement('SET FOREIGN_KEY_CHECKS=0;');
        
        Schema::table('maintenance_requests', function (Blueprint $table) {
            $table->dropForeign('fk_maintenance_requests_company');
            $table->dropForeign('fk_maintenance_requests_property');
            $table->dropForeign('fk_maintenance_requests_block');
            $table->dropForeign('fk_maintenance_requests_unit');
            $table->dropForeign('fk_maintenance_requests_customer');
            $table->dropForeign('fk_maintenance_requests_technician');
        });
        
        Schema::dropIfExists('maintenance_requests');
        
        DB::statement('SET FOREIGN_KEY_CHECKS=1;');
    }
};
