cmroubao/backend-api/migrations/00010_order_dry_runs.sql

-- +goose Up
CREATE TABLE order_dry_runs (
    id TEXT PRIMARY KEY NOT NULL CHECK (length(id) = 36),
    authorization_id TEXT NOT NULL UNIQUE
        REFERENCES order_authorizations(id)
            ON UPDATE RESTRICT ON DELETE RESTRICT,
    task_id TEXT NOT NULL
        REFERENCES purchase_tasks(id) ON UPDATE RESTRICT ON DELETE RESTRICT,
    execution_id TEXT NOT NULL
        REFERENCES task_executions(id) ON UPDATE RESTRICT ON DELETE RESTRICT,
    command_sha256 TEXT NOT NULL
        CHECK (
            length(command_sha256) = 64
            AND command_sha256 NOT GLOB '*[^0-9a-f]*'
        ),
    status TEXT NOT NULL CHECK (status IN ('PREPARING', 'READY')),
    card_signature TEXT
        CHECK (
            card_signature IS NULL
            OR (
                length(card_signature) = 64
                AND card_signature NOT GLOB '*[^0-9a-f]*'
            )
        ),
    detail_signature TEXT
        CHECK (
            detail_signature IS NULL
            OR (
                length(detail_signature) = 64
                AND detail_signature NOT GLOB '*[^0-9a-f]*'
            )
        ),
    observed_title TEXT,
    selected_sku TEXT,
    quantity INTEGER CHECK (
        quantity IS NULL OR quantity BETWEEN 1 AND 99
    ),
    unit_price_cents INTEGER CHECK (
        unit_price_cents IS NULL OR unit_price_cents > 0
    ),
    total_price_cents INTEGER CHECK (
        total_price_cents IS NULL OR total_price_cents > 0
    ),
    evidence_asset_id TEXT
        REFERENCES execution_evidence_assets(id)
            ON UPDATE RESTRICT ON DELETE RESTRICT,
    evidence_sha256 TEXT
        CHECK (
            evidence_sha256 IS NULL
            OR (
                length(evidence_sha256) = 64
                AND evidence_sha256 NOT GLOB '*[^0-9a-f]*'
            )
        ),
    started_at TEXT NOT NULL,
    ready_at TEXT,
    CHECK (
        status = 'PREPARING'
        OR (
            status = 'READY'
            AND card_signature IS NOT NULL
            AND detail_signature IS NOT NULL
            AND length(trim(observed_title)) > 0
            AND length(trim(selected_sku)) > 0
            AND quantity IS NOT NULL
            AND unit_price_cents IS NOT NULL
            AND total_price_cents IS NOT NULL
            AND evidence_asset_id IS NOT NULL
            AND evidence_sha256 IS NOT NULL
            AND ready_at IS NOT NULL
        )
    )
);

CREATE INDEX order_dry_runs_task_started_idx
    ON order_dry_runs (task_id, started_at DESC, id DESC);

CREATE TABLE device_order_dry_run_requests (
    device_id TEXT NOT NULL
        REFERENCES devices(id) ON UPDATE RESTRICT ON DELETE RESTRICT,
    operation TEXT NOT NULL CHECK (operation IN ('START', 'READY')),
    idempotency_key TEXT NOT NULL
        CHECK (
            length(trim(idempotency_key)) > 0
            AND length(CAST(idempotency_key AS BLOB)) <= 128
        ),
    request_sha256 TEXT NOT NULL
        CHECK (
            length(request_sha256) = 64
            AND request_sha256 NOT GLOB '*[^0-9a-f]*'
        ),
    task_id TEXT NOT NULL
        REFERENCES purchase_tasks(id) ON UPDATE RESTRICT ON DELETE RESTRICT,
    dry_run_id TEXT NOT NULL
        REFERENCES order_dry_runs(id) ON UPDATE RESTRICT ON DELETE RESTRICT,
    created_at TEXT NOT NULL,
    PRIMARY KEY (device_id, operation, idempotency_key)
);

ALTER TABLE task_events RENAME TO task_events_v9;

CREATE TABLE task_events (
    id TEXT PRIMARY KEY NOT NULL CHECK (length(id) = 36),
    task_id TEXT NOT NULL
        REFERENCES purchase_tasks(id) ON UPDATE RESTRICT ON DELETE CASCADE,
    event_type TEXT NOT NULL
        CHECK (
            event_type IN (
                'TASK_CREATED',
                'TASK_CLAIMED',
                'TASK_RECLAIMED',
                'TASK_RELEASED',
                'TASK_STARTED',
                'TASK_CANCEL_REQUESTED',
                'TASK_CANCELED',
                'CANDIDATES_READY',
                'ORDER_AUTHORIZATION_CREATED',
                'ORDER_AUTHORIZATION_DELIVERED',
                'ORDER_AUTHORIZATION_ACKNOWLEDGED',
                'ORDER_DRY_RUN_STARTED',
                'ORDER_DRY_RUN_READY'
            )
        ),
    message TEXT NOT NULL,
    occurred_at TEXT NOT NULL,
    actor_user_id TEXT
        REFERENCES users(id) ON UPDATE RESTRICT ON DELETE RESTRICT,
    actor_device_id TEXT
        REFERENCES devices(id) ON UPDATE RESTRICT ON DELETE RESTRICT
);

INSERT INTO task_events (
    id, task_id, event_type, message, occurred_at,
    actor_user_id, actor_device_id
)
SELECT
    id, task_id, event_type, message, occurred_at,
    actor_user_id, actor_device_id
FROM task_events_v9;

DROP TABLE task_events_v9;

CREATE INDEX task_events_task_occurred_idx
    ON task_events (task_id, occurred_at ASC, id ASC);
CREATE INDEX task_events_actor_user_idx
    ON task_events (actor_user_id, occurred_at DESC, id DESC);
CREATE INDEX task_events_actor_device_idx
    ON task_events (actor_device_id, occurred_at DESC, id DESC);

-- +goose Down
CREATE TEMP TABLE order_dry_runs_v10_down_guard (
    allowed INTEGER NOT NULL CHECK (allowed = 1)
);

INSERT INTO order_dry_runs_v10_down_guard (allowed)
SELECT CASE
    WHEN EXISTS (SELECT 1 FROM order_dry_runs)
      OR EXISTS (SELECT 1 FROM device_order_dry_run_requests)
      OR EXISTS (
        SELECT 1 FROM task_events
        WHERE event_type IN ('ORDER_DRY_RUN_STARTED', 'ORDER_DRY_RUN_READY')
      )
    THEN 0
    ELSE 1
END;

DROP TABLE order_dry_runs_v10_down_guard;
DROP TABLE device_order_dry_run_requests;
DROP TABLE order_dry_runs;

ALTER TABLE task_events RENAME TO task_events_v10;

CREATE TABLE task_events (
    id TEXT PRIMARY KEY NOT NULL CHECK (length(id) = 36),
    task_id TEXT NOT NULL
        REFERENCES purchase_tasks(id) ON UPDATE RESTRICT ON DELETE CASCADE,
    event_type TEXT NOT NULL
        CHECK (
            event_type IN (
                'TASK_CREATED',
                'TASK_CLAIMED',
                'TASK_RECLAIMED',
                'TASK_RELEASED',
                'TASK_STARTED',
                'TASK_CANCEL_REQUESTED',
                'TASK_CANCELED',
                'CANDIDATES_READY',
                'ORDER_AUTHORIZATION_CREATED',
                'ORDER_AUTHORIZATION_DELIVERED',
                'ORDER_AUTHORIZATION_ACKNOWLEDGED'
            )
        ),
    message TEXT NOT NULL,
    occurred_at TEXT NOT NULL,
    actor_user_id TEXT
        REFERENCES users(id) ON UPDATE RESTRICT ON DELETE RESTRICT,
    actor_device_id TEXT
        REFERENCES devices(id) ON UPDATE RESTRICT ON DELETE RESTRICT
);

INSERT INTO task_events (
    id, task_id, event_type, message, occurred_at,
    actor_user_id, actor_device_id
)
SELECT
    id, task_id, event_type, message, occurred_at,
    actor_user_id, actor_device_id
FROM task_events_v10;

DROP TABLE task_events_v10;

CREATE INDEX task_events_task_occurred_idx
    ON task_events (task_id, occurred_at ASC, id ASC);
CREATE INDEX task_events_actor_user_idx
    ON task_events (actor_user_id, occurred_at DESC, id DESC);
CREATE INDEX task_events_actor_device_idx
    ON task_events (actor_device_id, occurred_at DESC, id DESC);