-- +goose Up
CREATE TABLE platform_event_log (
    sequence_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    event_id uuid NOT NULL UNIQUE,
    source_service text NOT NULL,
    event_type text NOT NULL,
    schema_version integer NOT NULL,
    aggregate_type text NOT NULL,
    aggregate_id uuid NOT NULL,
    payload jsonb NOT NULL,
    occurred_at timestamptz NOT NULL,
    recorded_at timestamptz NOT NULL,
    CONSTRAINT platform_event_log_source_service_chk CHECK (source_service <> ''),
    CONSTRAINT platform_event_log_event_type_chk CHECK (event_type <> ''),
    CONSTRAINT platform_event_log_schema_version_chk CHECK (schema_version > 0),
    CONSTRAINT platform_event_log_aggregate_type_chk CHECK (aggregate_type <> '')
);

CREATE INDEX platform_event_log_type_idx
    ON platform_event_log (event_type, sequence_id);

CREATE INDEX platform_event_log_aggregate_idx
    ON platform_event_log (aggregate_type, aggregate_id, sequence_id);

CREATE TABLE platform_event_consumer_checkpoints (
    consumer_name text PRIMARY KEY,
    last_sequence_id bigint NOT NULL DEFAULT 0,
    lease_owner text NOT NULL DEFAULT '',
    locked_until timestamptz,
    retry_sequence_id bigint NOT NULL DEFAULT 0,
    retry_attempt integer NOT NULL DEFAULT 0,
    last_error text NOT NULL DEFAULT '',
    updated_at timestamptz NOT NULL,
    CONSTRAINT platform_event_consumer_name_chk CHECK (consumer_name <> ''),
    CONSTRAINT platform_event_consumer_last_sequence_chk CHECK (last_sequence_id >= 0),
    CONSTRAINT platform_event_consumer_retry_sequence_chk CHECK (retry_sequence_id >= 0),
    CONSTRAINT platform_event_consumer_retry_attempt_chk CHECK (retry_attempt >= 0),
    CONSTRAINT platform_event_consumer_lease_consistency_chk
        CHECK ((lease_owner = '' AND locked_until IS NULL) OR (lease_owner <> '' AND locked_until IS NOT NULL))
);

CREATE INDEX platform_event_consumer_lock_idx
    ON platform_event_consumer_checkpoints (locked_until)
    WHERE locked_until IS NOT NULL;

-- +goose Down
DROP TABLE IF EXISTS platform_event_consumer_checkpoints;
DROP TABLE IF EXISTS platform_event_log;
