<?php

use App\Enum\Dsar\DsarStatus;
use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;

/**
 * dsars.received_at has existed (fillable, cast) since the table was created but
 * was never written to — the response timestamp lived on dsar_responses instead.
 * Now that the DSAR reports derive response time and the "completed" trend from
 * dsars.received_at, historic terminal DSARs need a value.
 *
 * Best available source, in order of preference:
 *   1. the earliest 'full' response logged against the DSAR
 *   2. the latest response of any type
 *   3. the DSAR's updated_at (approximate — the row has not moved since)
 *
 * Non-terminal DSARs are deliberately left null.
 */
return new class extends Migration {
    public function up(): void {
        $terminal     = DsarStatus::terminal();
        $placeholders = implode(',', array_fill(0, count($terminal), '?'));

        // 1. Earliest full response.
        DB::update("
            UPDATE dsars
            SET received_at = (
                SELECT MIN(r.received_at) FROM dsar_responses r
                WHERE r.dsar_id = dsars.id AND r.type = 'full'
            )
            WHERE received_at IS NULL AND status IN ({$placeholders})
        ", $terminal);

        // 2. Latest response of any type, for DSARs with no full response.
        DB::update("
            UPDATE dsars
            SET received_at = (
                SELECT MAX(r.received_at) FROM dsar_responses r
                WHERE r.dsar_id = dsars.id
            )
            WHERE received_at IS NULL AND status IN ({$placeholders})
        ", $terminal);

        // 3. Fall back to updated_at where no response was ever logged.
        DB::update("
            UPDATE dsars
            SET received_at = updated_at
            WHERE received_at IS NULL AND status IN ({$placeholders})
        ", $terminal);
    }

    public function down(): void {
        // Backfilled values are indistinguishable from ones stamped live by
        // Dsar::booted(), so clearing them all would lose real data. No-op.
    }
};
