CREATE TABLE timeline_regeneration_campaigns
(
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    campaign_key    VARCHAR(120) NOT NULL,
    affected_from   TIMESTAMPTZ  NOT NULL,
    reason          TEXT         NOT NULL,
    source          VARCHAR(20)  NOT NULL,
    status          VARCHAR(20)  NOT NULL DEFAULT 'ACTIVE',
    created_by      UUID REFERENCES users (id) ON DELETE SET NULL,
    total_users     INTEGER      NOT NULL DEFAULT 0,
    completed_users INTEGER      NOT NULL DEFAULT 0,
    failed_users    INTEGER      NOT NULL DEFAULT 0,
    skipped_users   INTEGER      NOT NULL DEFAULT 0,
    created_at      TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    completed_at    TIMESTAMPTZ,
    CONSTRAINT uq_timeline_regeneration_campaigns_key UNIQUE (campaign_key),
    CONSTRAINT chk_timeline_regeneration_campaigns_reason CHECK (length(trim(reason)) > 0),
    CONSTRAINT chk_timeline_regeneration_campaigns_source CHECK (source IN ('RELEASE', 'ADMIN')),
    CONSTRAINT chk_timeline_regeneration_campaigns_status CHECK (status IN ('ACTIVE', 'COMPLETED', 'CANCELLED')),
    CONSTRAINT chk_timeline_regeneration_campaigns_counts CHECK (
        total_users >= 0
        AND completed_users >= 0
        AND failed_users >= 0
        AND skipped_users >= 0
    )
);

CREATE INDEX idx_timeline_regeneration_campaigns_status_created
    ON timeline_regeneration_campaigns (status, created_at);

CREATE TABLE timeline_regeneration_campaign_users
(
    id              BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    campaign_id     UUID        NOT NULL REFERENCES timeline_regeneration_campaigns (id) ON DELETE CASCADE,
    user_id         UUID        NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    status          VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    job_id          UUID,
    attempts        INTEGER     NOT NULL DEFAULT 0,
    last_error      TEXT,
    next_attempt_at TIMESTAMPTZ,
    claimed_at      TIMESTAMPTZ,
    started_at      TIMESTAMPTZ,
    completed_at    TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT uq_timeline_regeneration_campaign_users UNIQUE (campaign_id, user_id),
    CONSTRAINT chk_timeline_regeneration_campaign_users_status CHECK (status IN ('PENDING', 'RUNNING', 'COMPLETED', 'FAILED', 'SKIPPED')),
    CONSTRAINT chk_timeline_regeneration_campaign_users_attempts CHECK (attempts >= 0)
);

CREATE INDEX idx_timeline_regeneration_campaign_users_campaign_status
    ON timeline_regeneration_campaign_users (campaign_id, status);

CREATE INDEX idx_timeline_regeneration_campaign_users_due
    ON timeline_regeneration_campaign_users (status, next_attempt_at, created_at);

CREATE INDEX idx_timeline_regeneration_campaign_users_user_active
    ON timeline_regeneration_campaign_users (user_id, status)
    WHERE status IN ('PENDING', 'RUNNING', 'FAILED');