SET LOCAL search_path TO oxide_batch, pg_catalog;

CREATE TABLE ob_schema_version (
    singleton boolean PRIMARY KEY DEFAULT true CHECK (singleton),
    version integer NOT NULL CHECK (version > 0),
    installed_at timestamptz NOT NULL
);

INSERT INTO ob_schema_version (singleton, version, installed_at)
VALUES (true, 1, CURRENT_TIMESTAMP);

CREATE TABLE ob_job_definition (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY CHECK (id > 0),
    job_name varchar(128) COLLATE "C" NOT NULL
        CHECK (octet_length(job_name) BETWEEN 1 AND 128),
    definition_revision varchar(128) COLLATE "C" NOT NULL
        CHECK (octet_length(definition_revision) BETWEEN 1 AND 128),
    manifest_format smallint NOT NULL CHECK (manifest_format > 0),
    manifest_digest bytea NOT NULL CHECK (octet_length(manifest_digest) = 32),
    manifest jsonb NOT NULL
        CHECK (jsonb_typeof(manifest) = 'object')
        CHECK (pg_column_size(manifest) <= 65536),
    registered_at timestamptz NOT NULL,
    CONSTRAINT ob_job_definition_revision_unique
        UNIQUE (job_name, definition_revision),
    CONSTRAINT ob_job_definition_manifest_unique
        UNIQUE (job_name, manifest_digest)
);

CREATE TABLE ob_definition_upgrade (
    from_definition_id bigint NOT NULL
        REFERENCES ob_job_definition (id) ON DELETE RESTRICT,
    to_definition_id bigint NOT NULL
        REFERENCES ob_job_definition (id) ON DELETE RESTRICT,
    upgrade_key varchar(128) COLLATE "C" NOT NULL
        CHECK (octet_length(upgrade_key) BETWEEN 1 AND 128),
    step_mapping jsonb NOT NULL
        CHECK (jsonb_typeof(step_mapping) = 'object')
        CHECK (pg_column_size(step_mapping) <= 65536),
    registered_at timestamptz NOT NULL,
    PRIMARY KEY (from_definition_id, to_definition_id),
    CONSTRAINT ob_definition_upgrade_target_key_unique
        UNIQUE (to_definition_id, upgrade_key),
    CONSTRAINT ob_definition_upgrade_distinct
        CHECK (from_definition_id <> to_definition_id)
);

CREATE TABLE ob_job_instance (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY CHECK (id > 0),
    job_name varchar(128) COLLATE "C" NOT NULL
        CHECK (octet_length(job_name) BETWEEN 1 AND 128),
    instance_key bytea NOT NULL CHECK (octet_length(instance_key) = 32),
    identifying_parameters jsonb NOT NULL
        CHECK (jsonb_typeof(identifying_parameters) = 'object')
        CHECK (pg_column_size(identifying_parameters) <= 1048576),
    created_at timestamptz NOT NULL,
    CONSTRAINT ob_job_instance_identity_unique
        UNIQUE (job_name, instance_key)
);

CREATE TABLE ob_job_execution (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY CHECK (id > 0),
    job_instance_id bigint NOT NULL
        REFERENCES ob_job_instance (id) ON DELETE RESTRICT,
    definition_id bigint NOT NULL
        REFERENCES ob_job_definition (id) ON DELETE RESTRICT,
    upgrade_from_definition_id bigint,
    restart_of_execution_id bigint
        REFERENCES ob_job_execution (id) ON DELETE RESTRICT,
    attempt integer NOT NULL CHECK (attempt > 0),
    status varchar(16) COLLATE "C" NOT NULL
        CHECK (status IN (
            'STARTING', 'STARTED', 'STOPPING', 'STOPPED', 'FAILED',
            'COMPLETED', 'ABANDONED', 'UNKNOWN'
        )),
    exit_code varchar(64) COLLATE "C" NOT NULL
        CHECK (octet_length(exit_code) BETWEEN 1 AND 64),
    parameters jsonb NOT NULL
        CHECK (jsonb_typeof(parameters) = 'object')
        CHECK (pg_column_size(parameters) <= 1048576),
    context_format smallint NOT NULL CHECK (context_format > 0),
    context_schema varchar(128) COLLATE "C" NOT NULL
        CHECK (octet_length(context_schema) BETWEEN 1 AND 128),
    context_schema_version integer NOT NULL CHECK (context_schema_version > 0),
    context_payload jsonb NOT NULL
        CHECK (jsonb_typeof(context_payload) = 'object')
        CHECK (pg_column_size(context_payload) <= 1048576),
    failure_category varchar(32) COLLATE "C"
        CHECK (failure_category IN (
            'INVALID_DEFINITION', 'DUPLICATE_EXECUTION', 'ILLEGAL_TRANSITION',
            'TRANSIENT_INFRASTRUCTURE', 'PERMANENT_INFRASTRUCTURE',
            'USER_COMPONENT', 'CANCELLED', 'SERIALIZATION', 'INVARIANT'
        )),
    failure_id bigint CHECK (failure_id > 0),
    created_at timestamptz NOT NULL,
    started_at timestamptz,
    ended_at timestamptz,
    updated_at timestamptz NOT NULL,
    version bigint NOT NULL DEFAULT 0 CHECK (version >= 0),
    CONSTRAINT ob_job_execution_attempt_unique
        UNIQUE (job_instance_id, attempt),
    CONSTRAINT ob_job_execution_upgrade_fk
        FOREIGN KEY (upgrade_from_definition_id, definition_id)
        REFERENCES ob_definition_upgrade (from_definition_id, to_definition_id)
        ON DELETE RESTRICT,
    CONSTRAINT ob_job_execution_upgrade_shape
        CHECK (
            (upgrade_from_definition_id IS NULL AND restart_of_execution_id IS NULL)
            OR restart_of_execution_id IS NOT NULL
        ),
    CONSTRAINT ob_job_execution_failure_shape
        CHECK (
            (failure_category IS NULL AND failure_id IS NULL)
            OR (failure_category IS NOT NULL AND failure_id IS NOT NULL)
        ),
    CONSTRAINT ob_job_execution_timestamp_order
        CHECK (
            (started_at IS NULL OR started_at >= created_at)
            AND (ended_at IS NULL OR ended_at >= COALESCE(started_at, created_at))
            AND updated_at >= created_at
        ),
    CONSTRAINT ob_job_execution_end_state
        CHECK (
            (status IN ('STARTING', 'STARTED', 'STOPPING', 'UNKNOWN') AND ended_at IS NULL)
            OR (status IN ('STOPPED', 'FAILED', 'COMPLETED', 'ABANDONED') AND ended_at IS NOT NULL)
        )
);

CREATE UNIQUE INDEX ob_job_execution_one_unresolved
    ON ob_job_execution (job_instance_id)
    WHERE status IN ('STARTING', 'STARTED', 'STOPPING', 'UNKNOWN');
CREATE INDEX ob_job_execution_instance_latest
    ON ob_job_execution (job_instance_id, attempt DESC);
CREATE INDEX ob_job_execution_status_updated
    ON ob_job_execution (status, updated_at, id);

CREATE TABLE ob_step_execution (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY CHECK (id > 0),
    job_execution_id bigint NOT NULL
        REFERENCES ob_job_execution (id) ON DELETE RESTRICT,
    step_name varchar(128) COLLATE "C" NOT NULL
        CHECK (octet_length(step_name) BETWEEN 1 AND 128),
    status varchar(16) COLLATE "C" NOT NULL
        CHECK (status IN (
            'STARTING', 'STARTED', 'STOPPING', 'STOPPED', 'FAILED',
            'COMPLETED', 'ABANDONED', 'UNKNOWN'
        )),
    exit_code varchar(64) COLLATE "C" NOT NULL
        CHECK (octet_length(exit_code) BETWEEN 1 AND 64),
    read_count bigint NOT NULL DEFAULT 0 CHECK (read_count >= 0),
    processed_count bigint NOT NULL DEFAULT 0 CHECK (processed_count >= 0),
    write_count bigint NOT NULL DEFAULT 0 CHECK (write_count >= 0),
    filter_count bigint NOT NULL DEFAULT 0 CHECK (filter_count >= 0),
    commit_count bigint NOT NULL DEFAULT 0 CHECK (commit_count >= 0),
    rollback_count bigint NOT NULL DEFAULT 0 CHECK (rollback_count >= 0),
    checkpoint_format smallint NOT NULL CHECK (checkpoint_format > 0),
    checkpoint_schema varchar(128) COLLATE "C" NOT NULL
        CHECK (octet_length(checkpoint_schema) BETWEEN 1 AND 128),
    checkpoint_schema_version integer NOT NULL CHECK (checkpoint_schema_version > 0),
    checkpoint_payload jsonb NOT NULL
        CHECK (jsonb_typeof(checkpoint_payload) = 'object')
        CHECK (pg_column_size(checkpoint_payload) <= 1048576),
    context_format smallint NOT NULL CHECK (context_format > 0),
    context_schema varchar(128) COLLATE "C" NOT NULL
        CHECK (octet_length(context_schema) BETWEEN 1 AND 128),
    context_schema_version integer NOT NULL CHECK (context_schema_version > 0),
    context_payload jsonb NOT NULL
        CHECK (jsonb_typeof(context_payload) = 'object')
        CHECK (pg_column_size(context_payload) <= 1048576),
    failure_category varchar(32) COLLATE "C"
        CHECK (failure_category IN (
            'INVALID_DEFINITION', 'DUPLICATE_EXECUTION', 'ILLEGAL_TRANSITION',
            'TRANSIENT_INFRASTRUCTURE', 'PERMANENT_INFRASTRUCTURE',
            'USER_COMPONENT', 'CANCELLED', 'SERIALIZATION', 'INVARIANT'
        )),
    failure_id bigint CHECK (failure_id > 0),
    created_at timestamptz NOT NULL,
    started_at timestamptz,
    ended_at timestamptz,
    updated_at timestamptz NOT NULL,
    version bigint NOT NULL DEFAULT 0 CHECK (version >= 0),
    CONSTRAINT ob_step_execution_name_unique
        UNIQUE (job_execution_id, step_name),
    CONSTRAINT ob_step_execution_failure_shape
        CHECK (
            (failure_category IS NULL AND failure_id IS NULL)
            OR (failure_category IS NOT NULL AND failure_id IS NOT NULL)
        ),
    CONSTRAINT ob_step_execution_timestamp_order
        CHECK (
            (started_at IS NULL OR started_at >= created_at)
            AND (ended_at IS NULL OR ended_at >= COALESCE(started_at, created_at))
            AND updated_at >= created_at
        ),
    CONSTRAINT ob_step_execution_end_state
        CHECK (
            (status IN ('STARTING', 'STARTED', 'STOPPING', 'UNKNOWN') AND ended_at IS NULL)
            OR (status IN ('STOPPED', 'FAILED', 'COMPLETED', 'ABANDONED') AND ended_at IS NOT NULL)
        )
);

CREATE INDEX ob_step_execution_job_name
    ON ob_step_execution (job_execution_id, step_name, id DESC);

CREATE TABLE ob_recovery_decision (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY CHECK (id > 0),
    job_execution_id bigint NOT NULL
        REFERENCES ob_job_execution (id) ON DELETE RESTRICT,
    execution_version bigint NOT NULL CHECK (execution_version >= 0),
    prior_status varchar(16) COLLATE "C" NOT NULL
        CHECK (prior_status IN ('STARTING', 'STARTED', 'STOPPING', 'UNKNOWN')),
    resulting_status varchar(16) COLLATE "C" NOT NULL
        CHECK (resulting_status IN ('FAILED', 'ABANDONED')),
    reason_code varchar(64) COLLATE "C" NOT NULL
        CHECK (octet_length(reason_code) BETWEEN 1 AND 64),
    operator_reference varchar(128) COLLATE "C" NOT NULL
        CHECK (octet_length(operator_reference) BETWEEN 1 AND 128),
    evidence_digest bytea NOT NULL CHECK (octet_length(evidence_digest) = 32),
    decided_at timestamptz NOT NULL,
    CONSTRAINT ob_recovery_decision_version_unique
        UNIQUE (job_execution_id, execution_version)
);

CREATE INDEX ob_recovery_execution_time
    ON ob_recovery_decision (job_execution_id, decided_at, id);
