-- Latest investigation reading snapshots and controlled browser presentation events.
BEGIN;
SET LOCAL lock_timeout = '5s';

DO $$
BEGIN
    IF current_database() <> 'tjwater_v2' THEN
        RAISE EXCEPTION 'Investigation progress migration is restricted to tjwater_v2';
    END IF;
END $$;

CREATE TABLE IF NOT EXISTS workbench.operational_task_progress (
    project_id uuid NOT NULL,
    task_id uuid NOT NULL,
    run_id uuid NOT NULL,
    revision bigint NOT NULL CHECK (revision >= 1),
    document jsonb NOT NULL,
    published_at timestamptz NOT NULL,
    PRIMARY KEY (project_id,task_id,run_id),
    FOREIGN KEY (project_id,task_id)
        REFERENCES workbench.operational_tasks(project_id,task_id) ON DELETE CASCADE,
    FOREIGN KEY (project_id,run_id)
        REFERENCES workbench.operational_task_runs(project_id,run_id) ON DELETE CASCADE,
    CHECK ((jsonb_typeof(document)='object'
        AND document->>'run_id'=run_id::text
        AND (document->>'revision')::bigint=revision) IS TRUE)
);

ALTER TABLE workbench.domain_event_log
    DROP CONSTRAINT IF EXISTS domain_event_log_topic_check;
ALTER TABLE workbench.domain_event_log ADD CONSTRAINT domain_event_log_topic_check
    CHECK (topic IN (
        'operational.task.changed','operational.run.queued','operational.run.changed',
        'operational.report.published','operational.progress.changed',
        'operational.presentation.requested','operational.action.changed',
        'operational.notification.changed','workbench.alert.changed',
        'workbench.work_order.changed','analysis.run.changed'
    ));

COMMENT ON TABLE workbench.operational_task_progress IS
    'One latest complete reading snapshot per investigation run; never an execution scheduler.';
COMMIT;