ultimatepos/database/migrations/2026_06_30_200000_create_maintenance_tables.php

<?php

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

return new class extends Migration
{
    public function up(): void
    {
        // Recover from a previously failed partial migration (FK column type mismatch).
        if (Schema::hasTable('maintenance_equipment_categories') && ! Schema::hasTable('maintenance_work_orders')) {
            Schema::dropIfExists('maintenance_equipment_categories');
        }

        Schema::create('maintenance_equipment_categories', function (Blueprint $table) {
            $table->id();
            LegacyForeignKeys::business($table);
            $table->foreignId('parent_id')->nullable()->constrained('maintenance_equipment_categories')->nullOnDelete();
            $table->string('name');
            $table->text('description')->nullable();
            $table->boolean('is_active')->default(true);
            $table->timestamps();
            $table->softDeletes();

            $table->index(['business_id', 'is_active']);
        });

        Schema::create('maintenance_equipment', function (Blueprint $table) {
            $table->id();
            LegacyForeignKeys::business($table);
            $table->unsignedBigInteger('site_id')->nullable();
            $table->unsignedBigInteger('branch_id')->nullable();
            $table->foreignId('category_id')->nullable()->constrained('maintenance_equipment_categories')->nullOnDelete();
            $table->unsignedBigInteger('department_id')->nullable();
            $table->unsignedBigInteger('fixed_asset_id')->nullable();
            $table->string('code');
            $table->string('name');
            $table->string('asset_number')->nullable();
            $table->string('manufacturer')->nullable();
            $table->string('model')->nullable();
            $table->string('serial_number')->nullable();
            $table->string('capacity')->nullable();
            $table->date('installation_date')->nullable();
            $table->date('warranty_expires_at')->nullable();
            $table->string('location')->nullable();
            $table->string('qr_code')->nullable();
            $table->string('barcode')->nullable();
            // active, inactive, under_maintenance, under_overhaul, retired
            $table->string('status')->default('active');
            $table->decimal('working_hours', 12, 2)->default(0);
            $table->date('last_service_date')->nullable();
            $table->date('next_service_date')->nullable();
            $table->date('last_overhaul_date')->nullable();
            $table->date('next_overhaul_date')->nullable();
            // low, medium, high, critical
            $table->string('priority')->default('medium');
            $table->string('criticality')->default('medium');
            $table->string('risk_level')->default('low');
            $table->decimal('health_percentage', 5, 2)->default(100);
            $table->json('technical_documents')->nullable();
            $table->json('images')->nullable();
            $table->json('videos')->nullable();
            $table->json('user_manual')->nullable();
            $table->text('notes')->nullable();
            LegacyForeignKeys::user($table, 'created_by');
            LegacyForeignKeys::user($table, 'updated_by');
            $table->timestamps();
            $table->softDeletes();

            $table->unique(['business_id', 'code']);
            $table->index(['business_id', 'status']);
            $table->index(['business_id', 'category_id']);
            $table->index(['business_id', 'next_overhaul_date']);
            $table->index(['business_id', 'next_service_date']);
        });

        Schema::create('maintenance_preventive_schedules', function (Blueprint $table) {
            $table->id();
            LegacyForeignKeys::business($table);
            $table->foreignId('equipment_id')->constrained('maintenance_equipment')->cascadeOnDelete();
            $table->string('title');
            $table->text('description')->nullable();
            // daily, weekly, monthly, quarterly, semi_annual, annual, running_hours, cycle_count, production_quantity
            $table->string('frequency_type');
            $table->unsignedInteger('frequency_value')->default(1);
            $table->unsignedInteger('reminder_days_before')->default(3);
            $table->timestamp('last_performed_at')->nullable();
            $table->timestamp('next_due_at')->nullable();
            $table->decimal('running_hours_trigger', 12, 2)->nullable();
            $table->unsignedBigInteger('cycle_count_trigger')->nullable();
            $table->unsignedBigInteger('production_quantity_trigger')->nullable();
            LegacyForeignKeys::user($table, 'assigned_user_id');
            $table->boolean('is_active')->default(true);
            $table->timestamps();
            $table->softDeletes();

            $table->index(['business_id', 'equipment_id']);
            $table->index(['business_id', 'next_due_at']);
        });

        Schema::create('maintenance_overhauls', function (Blueprint $table) {
            $table->id();
            LegacyForeignKeys::business($table);
            $table->foreignId('equipment_id')->constrained('maintenance_equipment')->cascadeOnDelete();
            $table->string('title');
            $table->text('description')->nullable();
            $table->date('start_date')->nullable();
            $table->date('end_date')->nullable();
            $table->decimal('estimated_duration_hours', 8, 2)->nullable();
            $table->decimal('actual_duration_hours', 8, 2)->nullable();
            $table->unsignedBigInteger('estimated_cost')->default(0);
            $table->unsignedBigInteger('actual_cost')->default(0);
            LegacyForeignKeys::user($table, 'supervisor_id');
            $table->json('assigned_team')->nullable();
            $table->json('contractors')->nullable();
            $table->json('required_parts')->nullable();
            $table->json('required_tools')->nullable();
            $table->text('safety_requirements')->nullable();
            $table->boolean('shutdown_required')->default(false);
            // planned, approved, waiting, in_progress, suspended, completed, cancelled
            $table->string('status')->default('planned');
            $table->json('attachments')->nullable();
            $table->timestamps();
            $table->softDeletes();

            $table->index(['business_id', 'equipment_id']);
            $table->index(['business_id', 'status']);
            $table->index(['business_id', 'start_date']);
        });

        Schema::create('maintenance_work_orders', function (Blueprint $table) {
            $table->id();
            LegacyForeignKeys::business($table);
            $table->foreignId('equipment_id')->constrained('maintenance_equipment')->cascadeOnDelete();
            $table->foreignId('preventive_schedule_id')->nullable()->constrained('maintenance_preventive_schedules')->nullOnDelete();
            $table->foreignId('overhaul_id')->nullable()->constrained('maintenance_overhauls')->nullOnDelete();
            $table->string('work_order_number');
            LegacyForeignKeys::user($table, 'requester_id');
            LegacyForeignKeys::user($table, 'assigned_technician_id');
            // low, medium, high, urgent
            $table->string('priority')->default('medium');
            $table->text('description')->nullable();
            // draft, open, in_progress, on_hold, completed, cancelled
            $table->string('status')->default('draft');
            $table->decimal('labor_hours', 8, 2)->nullable();
            $table->unsignedBigInteger('cost')->default(0);
            $table->json('images')->nullable();
            $table->json('attachments')->nullable();
            $table->json('signature')->nullable();
            $table->text('completion_report')->nullable();
            $table->timestamp('scheduled_at')->nullable();
            $table->timestamp('completed_at')->nullable();
            $table->timestamps();
            $table->softDeletes();

            $table->unique(['business_id', 'work_order_number']);
            $table->index(['business_id', 'equipment_id']);
            $table->index(['business_id', 'status']);
            $table->index(['business_id', 'scheduled_at']);
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('maintenance_work_orders');
        Schema::dropIfExists('maintenance_overhauls');
        Schema::dropIfExists('maintenance_preventive_schedules');
        Schema::dropIfExists('maintenance_equipment');
        Schema::dropIfExists('maintenance_equipment_categories');
    }
};