minerops/scripts/test-begin-repair-migration.sh

#!/usr/bin/env bash
set -euo pipefail

repo_root="$(cd "$(dirname "${BASH_SOURCE[0]}")/.." && pwd)"
cd "${repo_root}"

project_name="${MINEROPS_REPAIR_MIGRATION_PROJECT:-minerops-repair-migration-$$}"
empty_database="minerops_repair_empty"
compose=(
    docker compose
    --project-name "${project_name}"
    --env-file .env.dev
    -f compose.dev.yaml
)

cleanup() {
    status=$?
    if (( status != 0 )); then
        echo
        echo "Begin Repair migration test failed. Recent PostgreSQL logs:"
        "${compose[@]}" logs --no-color --tail 120 postgres || true
    fi
    "${compose[@]}" down --volumes --remove-orphans >/dev/null 2>&1 || true
    exit "${status}"
}

trap cleanup EXIT

if [[ ! -f .env.dev ]]; then
    echo "Missing .env.dev. Run ./scripts/dev-env-init.sh first." >&2
    exit 1
fi

run_alembic() {
    "${compose[@]}" run --rm --no-deps backend alembic "$@"
}

echo "Starting disposable PostgreSQL..."
"${compose[@]}" up --detach postgres
for attempt in $(seq 1 60); do
    if "${compose[@]}" exec -T postgres sh -ec \
        'pg_isready -U "$POSTGRES_USER" -d "$POSTGRES_DB"' \
        >/dev/null 2>&1; then
        break
    fi
    if (( attempt == 60 )); then
        echo "PostgreSQL did not become ready." >&2
        exit 1
    fi
    sleep 1
done

echo "Building migration image..."
"${compose[@]}" build backend

echo "Upgrading the previous head to Begin Repair..."
run_alembic upgrade 0009_inventory_asset_id_history
previous_revision="$(run_alembic current 2>/dev/null)"
[[ "${previous_revision}" == *"0009_inventory_asset_id_history"* ]]
run_alembic upgrade 0010_begin_repair
current_revision="$(run_alembic current 2>/dev/null)"
[[ "${current_revision}" == *"0010_begin_repair"* ]]

echo "Checking repair schema, constraints, permissions, and lifecycle..."
"${compose[@]}" exec -T postgres sh -ec \
    'psql -v ON_ERROR_STOP=1 -U "$POSTGRES_USER" -d "$POSTGRES_DB"' \
    <<'SQL'
DO $$
DECLARE
    v_actor UUID := '00000000-0000-0000-0000-000000001001';
    v_miner UUID := '00000000-0000-0000-0000-000000001002';
    v_other_miner UUID := '00000000-0000-0000-0000-000000001003';
    v_invalid_miner UUID := '00000000-0000-0000-0000-000000001004';
    v_repair UUID;
    v_other_repair UUID;
    v_role_id UUID;
    v_count INTEGER;
BEGIN
    IF to_regclass('public.repairs') IS NULL
       OR to_regclass('public.repair_events') IS NULL THEN
        RAISE EXCEPTION 'Repair workflow tables were not created';
    END IF;

    IF EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'public'
          AND table_name = 'repairs'
          AND column_name IN (
              'id', 'miner_id', 'status', 'reason', 'opened_at', 'opened_by'
          )
          AND is_nullable <> 'NO'
    ) OR EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'public'
          AND table_name = 'repairs'
          AND column_name IN ('completed_at', 'completed_by')
          AND is_nullable <> 'YES'
    ) THEN
        RAISE EXCEPTION 'Repair column nullability is incorrect';
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM pg_indexes
        WHERE schemaname = 'public'
          AND tablename = 'repairs'
          AND indexname = 'uq_repairs_one_active_per_miner'
          AND indexdef LIKE '%UNIQUE%'
          AND indexdef LIKE '%WHERE (status = ANY%'
    ) THEN
        RAISE EXCEPTION 'Active repair partial unique index is missing';
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM pg_indexes
        WHERE schemaname = 'public'
          AND tablename = 'repair_events'
          AND indexname = 'idx_repair_events_repair_created'
          AND indexdef LIKE '%repair_id, created_at, sequence, id%'
    ) THEN
        RAISE EXCEPTION 'Repair event chronological index is missing';
    END IF;

    IF (SELECT COUNT(*) FROM permissions WHERE key = 'repairs.create') <> 1 THEN
        RAISE EXCEPTION 'repairs.create permission was not registered once';
    END IF;

    SELECT COUNT(*)
    INTO v_count
    FROM role_permissions
    JOIN roles ON roles.id = role_permissions.role_id
    JOIN permissions ON permissions.id = role_permissions.permission_id
    WHERE permissions.key = 'repairs.create'
      AND roles.name IN ('Admin', 'Manager', 'Technician');
    IF v_count <> 3 THEN
        RAISE EXCEPTION 'Expected Admin, Manager, and Technician repair access';
    END IF;

    IF EXISTS (
        SELECT 1
        FROM role_permissions
        JOIN roles ON roles.id = role_permissions.role_id
        JOIN permissions ON permissions.id = role_permissions.permission_id
        WHERE permissions.key = 'repairs.create'
          AND roles.name IN ('Read Only', 'Viewer', 'Auditor')
    ) THEN
        RAISE EXCEPTION 'A read-only role received repairs.create';
    END IF;

    INSERT INTO permissions (key, description)
    VALUES ('repairs.create', 'Begin repairs for miner assets')
    ON CONFLICT (key) DO UPDATE
    SET description = EXCLUDED.description;
    INSERT INTO role_permissions (role_id, permission_id)
    SELECT roles.id, permissions.id
    FROM roles
    JOIN permissions ON permissions.key = 'repairs.create'
    WHERE roles.name IN ('Admin', 'Manager', 'Technician')
    ON CONFLICT DO NOTHING;
    IF (SELECT COUNT(*) FROM permissions WHERE key = 'repairs.create') <> 1 THEN
        RAISE EXCEPTION 'Permission seeding is not idempotent';
    END IF;

    SELECT id INTO v_role_id FROM roles WHERE name = 'Admin';
    INSERT INTO users (
        id, username, display_name, password_hash, role_id
    ) VALUES (
        v_actor,
        'begin-repair-migration-test',
        'Begin Repair Migration Test',
        'not-used',
        v_role_id
    );
    INSERT INTO miners (id, asset_tag, status, lifecycle_status)
    VALUES
        (v_miner, 'REPAIR-MIGRATION-001', 'unknown', 'active'),
        (v_other_miner, 'REPAIR-MIGRATION-002', 'unknown', 'spare'),
        (v_invalid_miner, 'REPAIR-MIGRATION-003', 'unknown', 'active');

    INSERT INTO repairs (miner_id, reason, opened_by)
    VALUES (v_miner, 'CONTROL_BOARD', v_actor)
    RETURNING id INTO v_repair;
    INSERT INTO repair_events (
        repair_id, sequence, event_type, created_by, payload
    ) VALUES
        (
            v_repair,
            1,
            'REPAIR_STARTED',
            v_actor,
            '{"reason":"CONTROL_BOARD","from_lifecycle":"active","to_lifecycle":"in_repair"}'
        ),
        (
            v_repair,
            2,
            'INITIAL_NOTE',
            v_actor,
            '{"note":"Migration test note."}'
        );
    INSERT INTO repairs (miner_id, reason, opened_by)
    VALUES (v_other_miner, 'FAN', v_actor)
    RETURNING id INTO v_other_repair;
    INSERT INTO repair_events (
        repair_id, sequence, event_type, created_by, payload
    ) VALUES (
        v_other_repair,
        1,
        'REPAIR_STARTED',
        v_actor,
        '{"reason":"FAN","from_lifecycle":"spare","to_lifecycle":"in_repair"}'
    );
    UPDATE miners
    SET lifecycle_status = 'in_repair'
    WHERE id IN (v_miner, v_other_miner);

    IF EXISTS (
        SELECT 1
        FROM repairs
        WHERE id = v_repair
          AND (
              status <> 'OPEN'
              OR completed_at IS NOT NULL
              OR completed_by IS NOT NULL
              OR opened_at IS NULL
          )
    ) THEN
        RAISE EXCEPTION 'New repair defaults or completion fields are invalid';
    END IF;
    IF (
        SELECT array_agg(event_type ORDER BY sequence)
        FROM repair_events
        WHERE repair_id = v_repair
    ) <> ARRAY['REPAIR_STARTED', 'INITIAL_NOTE'] THEN
        RAISE EXCEPTION 'Initial event ordering is incorrect';
    END IF;
    IF (
        SELECT payload ->> 'from_lifecycle'
        FROM repair_events
        WHERE repair_id = v_other_repair
          AND event_type = 'REPAIR_STARTED'
    ) <> 'spare' THEN
        RAISE EXCEPTION 'Spare lifecycle history was not preserved';
    END IF;

    BEGIN
        INSERT INTO repairs (miner_id, reason, opened_by)
        VALUES (v_miner, 'PSU', v_actor);
        RAISE EXCEPTION 'A duplicate active repair was accepted';
    EXCEPTION WHEN unique_violation THEN
        NULL;
    END;


    BEGIN
        INSERT INTO repairs (miner_id, status, reason, opened_by)
        VALUES (v_invalid_miner, 'BROKEN', 'OTHER', v_actor);
        RAISE EXCEPTION 'Unsupported repair status was accepted';
    EXCEPTION WHEN check_violation THEN
        NULL;
    END;

    BEGIN
        INSERT INTO repairs (miner_id, reason, opened_by)
        VALUES (v_invalid_miner, 'NOT_A_REASON', v_actor);
        RAISE EXCEPTION 'Unsupported repair reason was accepted';
    EXCEPTION WHEN check_violation THEN
        NULL;
    END;

    BEGIN
        INSERT INTO repair_events (
            repair_id, sequence, event_type, created_by, payload
        ) VALUES (
            v_repair, 3, 'UNKNOWN_EVENT', v_actor, '{}'::jsonb
        );
        RAISE EXCEPTION 'Unsupported repair event type was accepted';
    EXCEPTION WHEN check_violation THEN
        NULL;
    END;

    BEGIN
        INSERT INTO repair_events (
            repair_id, sequence, event_type, created_by, payload
        ) VALUES (
            v_repair, 3, 'INITIAL_NOTE', v_actor, '{"note":"   "}'::jsonb
        );
        RAISE EXCEPTION 'Blank initial note payload was accepted';
    EXCEPTION WHEN check_violation THEN
        NULL;
    END;

    BEGIN
        INSERT INTO repair_events (
            repair_id, sequence, event_type, created_by, payload
        ) VALUES (
            v_repair, 2, 'INITIAL_NOTE', v_actor, '{"note":"duplicate"}'::jsonb
        );
        RAISE EXCEPTION 'Duplicate event sequence was accepted';
    EXCEPTION WHEN unique_violation THEN
        NULL;
    END;

    BEGIN
        INSERT INTO repairs (miner_id, reason, opened_by)
        VALUES (gen_random_uuid(), 'OTHER', v_actor);
        RAISE EXCEPTION 'Unknown miner repair was accepted';
    EXCEPTION WHEN foreign_key_violation THEN
        NULL;
    END;

    BEGIN
        INSERT INTO repairs (miner_id, reason, opened_by)
        VALUES (v_invalid_miner, 'OTHER', gen_random_uuid());
        RAISE EXCEPTION 'Repair with an unknown actor was accepted';
    EXCEPTION WHEN foreign_key_violation THEN
        NULL;
    END;

    BEGIN
        INSERT INTO repairs (miner_id, reason, opened_by)
        VALUES (v_invalid_miner, 'OTHER', NULL);
        RAISE EXCEPTION 'Repair without an actor was accepted';
    EXCEPTION WHEN not_null_violation THEN
        NULL;
    END;

    BEGIN
        INSERT INTO repair_events (
            repair_id, sequence, event_type, created_by, payload
        ) VALUES (
            v_repair,
            3,
            'INITIAL_NOTE',
            NULL,
            '{"note":"missing actor"}'::jsonb
        );
        RAISE EXCEPTION 'Repair event without an actor was accepted';
    EXCEPTION WHEN not_null_violation THEN
        NULL;
    END;

    BEGIN
        INSERT INTO repair_events (
            repair_id, sequence, event_type, created_by, payload
        ) VALUES (
            gen_random_uuid(),
            1,
            'INITIAL_NOTE',
            v_actor,
            '{"note":"unknown repair"}'::jsonb
        );
        RAISE EXCEPTION 'Event for an unknown repair was accepted';
    EXCEPTION WHEN foreign_key_violation THEN
        NULL;
    END;

    BEGIN
        INSERT INTO repair_events (
            repair_id, sequence, event_type, created_by, payload
        ) VALUES (
            v_repair,
            3,
            'INITIAL_NOTE',
            gen_random_uuid(),
            '{"note":"unknown actor"}'::jsonb
        );
        RAISE EXCEPTION 'Repair event with an unknown actor was accepted';
    EXCEPTION WHEN foreign_key_violation THEN
        NULL;
    END;

    BEGIN
        UPDATE miners SET lifecycle_status = 'unknown' WHERE id = v_miner;
        RAISE EXCEPTION 'Unsupported miner lifecycle was accepted';
    EXCEPTION WHEN check_violation THEN
        NULL;
    END;
END
$$;

INSERT INTO miners (id, asset_tag, status, lifecycle_status)
SELECT
    gen_random_uuid(),
    'REPAIR-STATUS-' || status,
    'unknown',
    'active'
FROM (
    VALUES
        ('OPEN'),
        ('IN_PROGRESS'),
        ('WAITING_FOR_PARTS'),
        ('READY_FOR_TEST'),
        ('COMPLETED'),
        ('CANCELLED')
) AS supported(status);

INSERT INTO repairs (
    miner_id,
    status,
    reason,
    opened_by,
    completed_at,
    completed_by
)
SELECT
    miners.id,
    supported.status,
    'OTHER',
    actor.id,
    CASE WHEN supported.status = 'COMPLETED' THEN now() END,
    CASE WHEN supported.status = 'COMPLETED' THEN actor.id END
FROM (
    VALUES
        ('OPEN'),
        ('IN_PROGRESS'),
        ('WAITING_FOR_PARTS'),
        ('READY_FOR_TEST'),
        ('COMPLETED'),
        ('CANCELLED')
) AS supported(status)
JOIN miners ON miners.asset_tag = 'REPAIR-STATUS-' || supported.status
CROSS JOIN (
    SELECT id FROM users WHERE username = 'begin-repair-migration-test'
) actor;

INSERT INTO miners (id, asset_tag, status, lifecycle_status)
SELECT
    gen_random_uuid(),
    'REPAIR-REASON-' || reason,
    'unknown',
    'active'
FROM (
    VALUES
        ('HARDWARE_FAILURE'),
        ('LOW_HASHRATE'),
        ('CONTROL_BOARD'),
        ('HASHBOARD'),
        ('PSU'),
        ('FAN'),
        ('OVERHEATING'),
        ('NETWORKING'),
        ('PREVENTIVE_MAINTENANCE'),
        ('INSPECTION'),
        ('OTHER')
) AS supported(reason);

INSERT INTO repairs (
    miner_id,
    status,
    reason,
    opened_by,
    completed_at,
    completed_by
)
SELECT
    miners.id,
    'COMPLETED',
    supported.reason,
    actor.id,
    now(),
    actor.id
FROM (
    VALUES
        ('HARDWARE_FAILURE'),
        ('LOW_HASHRATE'),
        ('CONTROL_BOARD'),
        ('HASHBOARD'),
        ('PSU'),
        ('FAN'),
        ('OVERHEATING'),
        ('NETWORKING'),
        ('PREVENTIVE_MAINTENANCE'),
        ('INSPECTION'),
        ('OTHER')
) AS supported(reason)
JOIN miners ON miners.asset_tag = 'REPAIR-REASON-' || supported.reason
CROSS JOIN (
    SELECT id FROM users WHERE username = 'begin-repair-migration-test'
) actor;
SQL


echo "Checking downgrade refuses missing lifecycle history..."
"${compose[@]}" exec -T postgres sh -ec \
    'psql -v ON_ERROR_STOP=1 -U "$POSTGRES_USER" -d "$POSTGRES_DB"' <<'SQL'
INSERT INTO miners (id, asset_tag, status, lifecycle_status)
VALUES (
    '00000000-0000-0000-0000-000000001999',
    'REPAIR-MIGRATION-INCONSISTENT',
    'unknown',
    'in_repair'
);
SQL
if downgrade_error="$(run_alembic downgrade 0009_inventory_asset_id_history 2>&1)"; then
    echo "Downgrade accepted missing lifecycle history." >&2
    exit 1
fi
[[ "${downgrade_error}" == *"do not have exactly one active REPAIR_STARTED event"* ]]
failed_revision="$(run_alembic current 2>/dev/null)"
[[ "${failed_revision}" == *"0010_begin_repair"* ]]
"${compose[@]}" exec -T postgres sh -ec \
    'psql -v ON_ERROR_STOP=1 -U "$POSTGRES_USER" -d "$POSTGRES_DB"' <<'SQL'
DELETE FROM miners
WHERE id = '00000000-0000-0000-0000-000000001999';
SQL
echo "Downgrading and re-upgrading Begin Repair..."
run_alembic downgrade 0009_inventory_asset_id_history
downgrade_state="$(
    "${compose[@]}" exec -T postgres sh -ec \
        'psql -v ON_ERROR_STOP=1 -U "$POSTGRES_USER" -d "$POSTGRES_DB" -At' \
        <<'SQL'
SELECT
    to_regclass('public.repairs') IS NULL
    AND to_regclass('public.repair_events') IS NULL
    AND NOT EXISTS (
        SELECT 1 FROM permissions WHERE key = 'repairs.create'
    )
    AND NOT EXISTS (
        SELECT 1 FROM miners WHERE lifecycle_status = 'in_repair'
    )
    AND (
        SELECT lifecycle_status
        FROM miners
        WHERE asset_tag = 'REPAIR-MIGRATION-001'
    ) = 'active'
    AND (
        SELECT lifecycle_status
        FROM miners
        WHERE asset_tag = 'REPAIR-MIGRATION-002'
    ) = 'spare';
SQL
)"
[[ "${downgrade_state}" == "t" ]]
run_alembic upgrade 0010_begin_repair
reupgraded_revision="$(run_alembic current 2>/dev/null)"
[[ "${reupgraded_revision}" == *"0010_begin_repair"* ]]

echo "Migrating an empty database from base through Begin Repair..."
"${compose[@]}" exec -T postgres sh -ec \
    'psql -v ON_ERROR_STOP=1 -U "$POSTGRES_USER" -d postgres \
    -c "CREATE DATABASE minerops_repair_empty"' >/dev/null
"${compose[@]}" run --rm --no-deps \
    -e "POSTGRES_DB=${empty_database}" \
    backend alembic upgrade 0010_begin_repair

empty_revision="$(
    "${compose[@]}" run --rm --no-deps \
        -e "POSTGRES_DB=${empty_database}" \
        backend alembic current 2>/dev/null
)"
[[ "${empty_revision}" == *"0010_begin_repair"* ]]

echo "Begin Repair migration test passed."