CREATE TABLE drops (

    id              BIGSERIAL PRIMARY KEY,

    event_id        BIGINT NOT NULL REFERENCES events(id) ON DELETE CASCADE,

    drop_type       TEXT NOT NULL CHECK (drop_type IN ('new','status','price','inventory','announcement')),

    from_state      JSONB,

    to_state        JSONB NOT NULL,

    detected_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    notified        BOOLEAN NOT NULL DEFAULT FALSE,

    notified_at     TIMESTAMPTZ

);

CREATE INDEX idx_drops_detected_desc  ON drops(detected_at DESC);

CREATE INDEX idx_drops_event          ON drops(event_id);

CREATE INDEX idx_drops_type           ON drops(drop_type);

CREATE INDEX idx_drops_type_detected  ON drops(drop_type, detected_at DESC);