CREATE TABLE events (

    id                   BIGSERIAL PRIMARY KEY,

    source_id            TEXT NOT NULL REFERENCES sources(id) ON DELETE RESTRICT,

    source_event_id      TEXT NOT NULL,

    title                TEXT NOT NULL CHECK (length(title) BETWEEN 1 AND 512),

    subtitle             TEXT,

    category             TEXT NOT NULL CHECK (category IN ('concert','sports','festival','theater','other')),

    sport                TEXT CHECK (sport IS NULL OR sport IN ('nfl','nba','nhl','mlb','tennis','f1','soccer','golf','boxing','other')),

    starts_at            TIMESTAMPTZ,

    venue_name           TEXT,

    city                 TEXT,

    country              CHAR(2),

    latitude             DOUBLE PRECISION CHECK (latitude IS NULL OR (latitude BETWEEN -90 AND 90)),

    longitude            DOUBLE PRECISION CHECK (longitude IS NULL OR (longitude BETWEEN -180 AND 180)),

    image_url            TEXT,

    on_sale_at           TIMESTAMPTZ,

    presale_on_sale_at   TIMESTAMPTZ,

    status               TEXT NOT NULL CHECK (status IN ('announced','presale','onsale','soldout','cancelled')),

    price_min            INTEGER CHECK (price_min IS NULL OR price_min >= 0),

    price_max            INTEGER CHECK (price_max IS NULL OR price_max >= 0),

    currency             CHAR(3),

    inventory_remaining  INTEGER CHECK (inventory_remaining IS NULL OR inventory_remaining >= 0),

    buy_url              TEXT NOT NULL,

    raw                  JSONB,

    content_hash         CHAR(64) NOT NULL,

    first_seen_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    updated_at           TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    UNIQUE (source_id, source_event_id),

    CONSTRAINT price_range CHECK (price_min IS NULL OR price_max IS NULL OR price_min <= price_max)

);

CREATE INDEX idx_events_status           ON events(status);

CREATE INDEX idx_events_on_sale          ON events(on_sale_at) WHERE on_sale_at IS NOT NULL;

CREATE INDEX idx_events_category         ON events(category);

CREATE INDEX idx_events_sport            ON events(sport) WHERE sport IS NOT NULL;

CREATE INDEX idx_events_starts_at        ON events(starts_at) WHERE starts_at IS NOT NULL;

CREATE INDEX idx_events_source_status    ON events(source_id, status);

CREATE INDEX idx_events_updated_at       ON events(updated_at DESC);

CREATE INDEX idx_events_title_trgm       ON events USING gin (title gin_trgm_ops);

CREATE INDEX idx_events_starts_at_source ON events(starts_at, source_id);

CREATE TRIGGER events_set_updated_at

    BEFORE UPDATE ON events

    FOR EACH ROW EXECUTE FUNCTION set_updated_at();