<?php

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

/**
 * E7, decided: an excuse lives in `service_requests`.
 *
 * An excuse and a freeze request are the same interaction — member-initiated,
 * pending/approved/rejected, with `admin_notes` back. A separate `excuses`
 * table would mean two queues, two admin screens and two half-built workflows,
 * which is exactly the state this replaces.
 *
 * Today there are two contradictory implementations and neither works:
 *
 *  - `ParentExcuseForm` validates the request, stores the attachment, then
 *    flashes "تم تقديم العذر بنجاح. سيتم مراجعته من قبل الإدارة" while
 *    discarding the excuse entirely behind a `// TODO`. There is no Excuse
 *    model and no table. A parent believes the absence was excused; nobody at
 *    the academy ever sees it.
 *  - The deleted API's `POST /v1/absences/report` wrote `status='excused'`
 *    straight into `attendance_records` with no marker, no transition check,
 *    no audit, and no check that the session even belonged to the participant.
 *    A player could excuse himself, which corrupts EnforceAttendanceThresholds,
 *    SuspendOnThreshold and every attendance-rate figure in the product.
 *
 * So: an excuse is a **request**, never a direct attendance write. Approval
 * calls AttendanceMarkingService with the approving staff member as the marker,
 * which is the one attendance write path and stays the one.
 */
return new class extends Migration
{
    private const TYPES = ['freeze', 'unfreeze', 'transfer', 'cancellation', 'excuse', 'renewal', 'other'];

    public function up(): void
    {
        if (! Schema::hasTable('service_requests')) {
            return;
        }

        Schema::table('service_requests', function (Blueprint $table) {
            if (! Schema::hasColumn('service_requests', 'training_session_id')) {
                $table->foreignId('training_session_id')->nullable()->after('participant_id')
                    ->constrained()->nullOnDelete();
            }

            if (! Schema::hasColumn('service_requests', 'attendance_record_id')) {
                $table->foreignId('attendance_record_id')->nullable()->after('training_session_id')
                    ->constrained()->nullOnDelete();
            }

            // Where the member's supporting file lives. Private disk only: a
            // medical note is the most sensitive thing this product handles,
            // and the form it replaces wrote them to the PUBLIC disk — orphaned,
            // unauthenticated, undeletable child medical records on the
            // academy's own TLS certificate.
            if (! Schema::hasColumn('service_requests', 'attachment_path')) {
                $table->string('attachment_path')->nullable();
                $table->string('attachment_mime', 60)->nullable();
                $table->unsignedBigInteger('attachment_size')->nullable();
            }

            if (! Schema::hasColumn('service_requests', 'effect_applied_at')) {
                // Approving a freeze must actually freeze something. Nothing in
                // the codebase reads ServiceRequest beyond the model, the event
                // and the listener — approval has never had a domain effect, so
                // this records when one was applied.
                $table->timestamp('effect_applied_at')->nullable();
                $table->string('effect_error', 255)->nullable();
            }
        });

        if (DB::getDriverName() !== 'pgsql') {
            return;
        }

        $list = implode(', ', array_map(fn ($t) => "'{$t}'", self::TYPES));

        DB::statement('ALTER TABLE service_requests DROP CONSTRAINT IF EXISTS service_requests_type_check');
        DB::statement("ALTER TABLE service_requests ADD CONSTRAINT service_requests_type_check CHECK (type IN ({$list}))");

        // One open excuse per session per participant. A member tapping twice
        // on a bad connection must not fill the queue with duplicates.
        DB::statement("CREATE UNIQUE INDEX IF NOT EXISTS service_requests_one_open_excuse
            ON service_requests (academy_id, participant_id, training_session_id)
            WHERE type = 'excuse' AND status = 'pending'");

        Schema::table('service_requests', function (Blueprint $table) {
            $table->index(['academy_id', 'status', 'type'], 'service_requests_academy_status_type_index');
        });
    }

    public function down(): void
    {
        if (! Schema::hasTable('service_requests')) {
            return;
        }

        if (DB::getDriverName() === 'pgsql') {
            DB::statement('DROP INDEX IF EXISTS service_requests_one_open_excuse');
            DB::statement('ALTER TABLE service_requests DROP CONSTRAINT IF EXISTS service_requests_type_check');
            DB::statement("ALTER TABLE service_requests ADD CONSTRAINT service_requests_type_check CHECK (type IN ('freeze','unfreeze','transfer','cancellation','other'))");
        }

        Schema::table('service_requests', function (Blueprint $table) {
            $table->dropIndex('service_requests_academy_status_type_index');
            $table->dropConstrainedForeignId('training_session_id');
            $table->dropConstrainedForeignId('attendance_record_id');
            $table->dropColumn(['attachment_path', 'attachment_mime', 'attachment_size', 'effect_applied_at', 'effect_error']);
        });
    }
};
