<?php

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

return new class extends Migration
{
    public function up(): void
    {
        DB::unprepared('
            CREATE OR REPLACE FUNCTION delete_client_case_cascade(case_id INTEGER)
            RETURNS TABLE(deleted_table TEXT, deleted_count INTEGER) AS $$
            DECLARE
                v_deleted_count INTEGER;
            BEGIN
                -- Delete bookings related to payments in this case
                WITH deleted AS (
                    DELETE FROM bookings
                    WHERE payment_id IN (
                        SELECT p.id FROM payments p
                        INNER JOIN accounts a ON p.account_id = a.id
                        INNER JOIN contracts c ON a.contract_id = c.id
                        WHERE c.client_case_id = case_id
                    )
                    RETURNING *
                )
                SELECT COUNT(*) INTO v_deleted_count FROM deleted;
                RETURN QUERY SELECT \'bookings\'::TEXT, v_deleted_count;

                -- Delete payments related to accounts in this case
                WITH deleted AS (
                    DELETE FROM payments
                    WHERE account_id IN (
                        SELECT a.id FROM accounts a
                        INNER JOIN contracts c ON a.contract_id = c.id
                        WHERE c.client_case_id = case_id
                    )
                    RETURNING *
                )
                SELECT COUNT(*) INTO v_deleted_count FROM deleted;
                RETURN QUERY SELECT \'payments\'::TEXT, v_deleted_count;

                -- Delete activities
                WITH deleted AS (
                    DELETE FROM activities WHERE client_case_id = case_id
                    RETURNING *
                )
                SELECT COUNT(*) INTO v_deleted_count FROM deleted;
                RETURN QUERY SELECT \'activities\'::TEXT, v_deleted_count;

                -- Delete accounts
                WITH deleted AS (
                    DELETE FROM accounts
                    WHERE contract_id IN (
                        SELECT id FROM contracts WHERE client_case_id = case_id
                    )
                    RETURNING *
                )
                SELECT COUNT(*) INTO v_deleted_count FROM deleted;
                RETURN QUERY SELECT \'accounts\'::TEXT, v_deleted_count;

                -- Delete contracts
                WITH deleted AS (
                    DELETE FROM contracts WHERE client_case_id = case_id
                    RETURNING *
                )
                SELECT COUNT(*) INTO v_deleted_count FROM deleted;
                RETURN QUERY SELECT \'contracts\'::TEXT, v_deleted_count;

                -- Delete the client_case itself
                WITH deleted AS (
                    DELETE FROM client_cases WHERE id = case_id
                    RETURNING *
                )
                SELECT COUNT(*) INTO v_deleted_count FROM deleted;
                RETURN QUERY SELECT \'client_cases\'::TEXT, v_deleted_count;

            END;
            $$ LANGUAGE plpgsql;
        ');
    }

    public function down(): void
    {
        DB::unprepared('DROP FUNCTION IF EXISTS delete_client_case_cascade(INTEGER);');
    }
};
