CREATE TABLE IF NOT EXISTS outbox_events (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    event_id varchar(128) NOT NULL,
    event_type varchar(64) NOT NULL,
    aggregate_type varchar(64),
    aggregate_id varchar(128),
    payload jsonb NOT NULL DEFAULT '{}'::jsonb,
    status varchar(16) NOT NULL DEFAULT 'pending',
    attempts integer NOT NULL DEFAULT 0,
    max_attempts integer NOT NULL DEFAULT 5,
    next_attempt_at timestamptz NOT NULL DEFAULT now(),
    last_error text,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_outbox_events_event_id ON outbox_events (event_id);
CREATE INDEX IF NOT EXISTS idx_outbox_events_status_next
    ON outbox_events (status, next_attempt_at);

CREATE TABLE IF NOT EXISTS notifications (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id uuid,
    channel varchar(32) NOT NULL,
    target varchar(128) NOT NULL,
    title varchar(128) NOT NULL,
    body text NOT NULL,
    status varchar(16) NOT NULL DEFAULT 'pending',
    attempts integer NOT NULL DEFAULT 0,
    next_attempt_at timestamptz NOT NULL DEFAULT now(),
    last_error text,
    template_id varchar(128),
    open_id varchar(128),
    authorized boolean NOT NULL DEFAULT false,
    provider_response jsonb,
    data jsonb,
    business_type varchar(64),
    business_id varchar(128),
    event_id varchar(128),
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_notifications_event_id ON notifications (event_id);
CREATE INDEX IF NOT EXISTS idx_notifications_user_created ON notifications (user_id, created_at);
CREATE INDEX IF NOT EXISTS idx_notifications_status_next
    ON notifications (status, next_attempt_at);
