<?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::table('crm_leads', function (Blueprint $table) {
            $table->timestamp('next_followup_at')->nullable();
            $table->text('notes')->nullable();
            $table->index(['tenant_id', 'stage', 'next_followup_at'], 'idx_crm_pipeline_followup');
        });

        Schema::table('pro_jobs', function (Blueprint $table) {
            $table->foreignId('branch_id')->nullable()->constrained('branches')->nullOnDelete();
            $table->timestamp('departure_at')->nullable();
            $table->timestamp('expected_return_at')->nullable();
            $table->timestamp('actual_return_at')->nullable();
            $table->decimal('expense_amount', 12, 2)->default(0);
            $table->index(['tenant_id', 'status', 'pro_user_id'], 'idx_pro_jobs_work_queue');
        });

        Schema::table('pro_document_custodies', function (Blueprint $table) {
            $table->foreignId('branch_id')->nullable()->constrained('branches')->nullOnDelete();
            $table->timestamp('received_at')->nullable();
            $table->timestamp('delivered_at')->nullable();
            $table->index(['tenant_id', 'status', 'current_holder_id'], 'idx_custody_current_holder');
        });

        Schema::create('pro_custody_history', function (Blueprint $table) {
            $table->id();
            $table->foreignId('tenant_id')->constrained('tenants')->cascadeOnDelete();
            $table->foreignId('custody_id')->constrained('pro_document_custodies')->cascadeOnDelete();
            $table->string('from_status')->nullable();
            $table->string('to_status');
            $table->foreignId('from_holder_id')->nullable()->constrained('users')->nullOnDelete();
            $table->foreignId('to_holder_id')->nullable()->constrained('users')->nullOnDelete();
            $table->text('handover_note')->nullable();
            $table->foreignId('recorded_by')->constrained('users');
            $table->timestamps();

            $table->index(['tenant_id', 'custody_id', 'created_at'], 'idx_custody_history_timeline');
        });

        Schema::table('queue_tokens', function (Blueprint $table) {
            $table->date('service_date')->nullable();
            $table->timestamp('checked_in_at')->nullable();
            $table->timestamp('no_show_at')->nullable();
            $table->foreignId('handled_by')->nullable()->constrained('users')->nullOnDelete();
            $table->index(['tenant_id', 'branch_id', 'service_date', 'status'], 'idx_queue_live_board');
        });

        Schema::table('appointments', function (Blueprint $table) {
            $table->string('reference_number')->nullable();
            $table->foreignId('assigned_user_id')->nullable()->constrained('users')->nullOnDelete();
            $table->integer('duration_minutes')->default(30);
            $table->text('notes')->nullable();
            $table->timestamp('checked_in_at')->nullable();
            $table->index(['tenant_id', 'branch_id', 'appointment_date', 'status'], 'idx_appointments_schedule');
        });

        Schema::create('workflow_tasks', function (Blueprint $table) {
            $table->id();
            $table->foreignId('tenant_id')->constrained('tenants')->cascadeOnDelete();
            $table->foreignId('branch_id')->nullable()->constrained('branches')->nullOnDelete();
            $table->string('task_number');
            $table->string('source_type')->nullable();
            $table->unsignedBigInteger('source_id')->nullable();
            $table->string('title');
            $table->text('description')->nullable();
            $table->string('priority')->default('normal');
            $table->string('status')->default('open');
            $table->foreignId('assigned_user_id')->nullable()->constrained('users')->nullOnDelete();
            $table->foreignId('created_by')->constrained('users');
            $table->timestamp('due_at')->nullable();
            $table->timestamp('completed_at')->nullable();
            $table->timestamps();

            $table->unique(['tenant_id', 'task_number']);
            $table->index(['tenant_id', 'assigned_user_id', 'status', 'due_at'], 'idx_tasks_assignee_due');
            $table->index(['tenant_id', 'source_type', 'source_id'], 'idx_tasks_source');
        });

        Schema::create('approval_requests', function (Blueprint $table) {
            $table->id();
            $table->foreignId('tenant_id')->constrained('tenants')->cascadeOnDelete();
            $table->foreignId('branch_id')->nullable()->constrained('branches')->nullOnDelete();
            $table->string('approval_number');
            $table->string('type');
            $table->string('subject_type')->nullable();
            $table->unsignedBigInteger('subject_id')->nullable();
            $table->foreignId('requested_by')->constrained('users');
            $table->foreignId('assigned_to')->nullable()->constrained('users')->nullOnDelete();
            $table->decimal('amount', 12, 2)->nullable();
            $table->text('reason');
            $table->json('payload')->nullable();
            $table->string('status')->default('pending');
            $table->text('decision_notes')->nullable();
            $table->foreignId('decided_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamp('decided_at')->nullable();
            $table->timestamp('expires_at')->nullable();
            $table->timestamps();

            $table->unique(['tenant_id', 'approval_number']);
            $table->index(['tenant_id', 'status', 'assigned_to', 'created_at'], 'idx_approvals_inbox');
            $table->index(['tenant_id', 'subject_type', 'subject_id'], 'idx_approvals_subject');
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('approval_requests');
        Schema::dropIfExists('workflow_tasks');

        Schema::table('appointments', function (Blueprint $table) {
            $table->dropForeign(['assigned_user_id']);
            $table->dropColumn(['reference_number', 'assigned_user_id', 'duration_minutes', 'notes', 'checked_in_at']);
        });

        Schema::table('queue_tokens', function (Blueprint $table) {
            $table->dropForeign(['handled_by']);
            $table->dropColumn(['service_date', 'checked_in_at', 'no_show_at', 'handled_by']);
        });

        Schema::dropIfExists('pro_custody_history');

        Schema::table('pro_document_custodies', function (Blueprint $table) {
            $table->dropForeign(['branch_id']);
            $table->dropColumn(['branch_id', 'received_at', 'delivered_at']);
        });

        Schema::table('pro_jobs', function (Blueprint $table) {
            $table->dropForeign(['branch_id']);
            $table->dropColumn(['branch_id', 'departure_at', 'expected_return_at', 'actual_return_at', 'expense_amount']);
        });

        Schema::table('crm_leads', function (Blueprint $table) {
            $table->dropColumn(['next_followup_at', 'notes']);
        });
    }
};
