BEGIN;

-- Create enum types first
CREATE TYPE attempt_status AS ENUM ('pending', 'processing', 'success', 'failure');

-- transform_requests table
CREATE TABLE transform_requests (
    request_id JSONB PRIMARY KEY,
    input JSONB NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- transform_attempts table
CREATE TABLE transform_attempts (
    request_id JSONB NOT NULL REFERENCES transform_requests(request_id) ON DELETE CASCADE,
    attempt_id JSONB NOT NULL,
    return_pkg JSONB,
    status attempt_status NOT NULL DEFAULT 'pending',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    started_at TIMESTAMPTZ,
    updated_at TIMESTAMPTZ,
    PRIMARY KEY (request_id, attempt_id)
);

-- consume_attempts table
CREATE TABLE consume_attempts (
    request_id JSONB NOT NULL,
    attempt_id JSONB NOT NULL,
    consume_id JSONB NOT NULL,
    return_ctx JSONB,
    status attempt_status NOT NULL DEFAULT 'pending',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ,
    FOREIGN KEY (request_id, attempt_id) REFERENCES transform_attempts(request_id, attempt_id) ON DELETE CASCADE,
    PRIMARY KEY (request_id, attempt_id, consume_id)
);

-- archive_transform_requests table
CREATE TABLE archive_transform_requests (
    request_id JSONB PRIMARY KEY,
    input JSONB NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- archive_transform_attempts table
CREATE TABLE archive_transform_attempts (
    request_id JSONB NOT NULL REFERENCES archive_transform_requests(request_id) ON DELETE CASCADE,
    attempt_id JSONB NOT NULL,
    return_pkg JSONB,
    status attempt_status NOT NULL DEFAULT 'pending',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    started_at TIMESTAMPTZ,
    updated_at TIMESTAMPTZ,
    PRIMARY KEY (request_id, attempt_id)
);

-- archive_consume_attempts table
CREATE TABLE archive_consume_attempts (
    request_id JSONB NOT NULL,
    attempt_id JSONB NOT NULL,
    consume_id JSONB NOT NULL,
    return_ctx JSONB,
    status attempt_status NOT NULL DEFAULT 'pending',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ,
    FOREIGN KEY (request_id, attempt_id) REFERENCES archive_transform_attempts(request_id, attempt_id) ON DELETE CASCADE,
    PRIMARY KEY (request_id, attempt_id, consume_id)
);

-- dynamic_configs table
CREATE TABLE dynamic_configs (
    key TEXT PRIMARY KEY,
    value TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

COMMIT;