-- Notification system schema
CREATE TYPE NOTIFICATION_CHANNEL_TYPE AS ENUM ('webhook', 'telegram');
CREATE TYPE NOTIFICATION_STATUS AS ENUM ('pending', 'delivering', 'delivered', 'failed', 'dead');

CREATE TABLE notification_channels (
    id         BIGSERIAL PRIMARY KEY,
    name       TEXT NOT NULL,
    type       NOTIFICATION_CHANNEL_TYPE NOT NULL,
    enabled    BOOLEAN DEFAULT true,
    config     JSONB NOT NULL DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE notification_subscriptions (
    id         BIGSERIAL PRIMARY KEY,
    channel_id BIGINT NOT NULL REFERENCES notification_channels(id) ON DELETE CASCADE,
    event_type EVENT_TYPE NOT NULL,
    UNIQUE(channel_id, event_type)
);

CREATE TABLE notification_queue (
    id           BIGSERIAL PRIMARY KEY,
    channel_id   BIGINT NOT NULL REFERENCES notification_channels(id) ON DELETE CASCADE,
    event_type   EVENT_TYPE NOT NULL,
    title        TEXT NOT NULL,
    message      JSONB NOT NULL DEFAULT '{}',
    status       NOTIFICATION_STATUS DEFAULT 'pending',
    attempts     INT DEFAULT 0,
    max_attempts INT DEFAULT 5,
    last_error   TEXT,
    next_retry_at TIMESTAMPTZ,
    created_at   TIMESTAMPTZ DEFAULT NOW(),
    delivered_at TIMESTAMPTZ
);

CREATE TABLE notification_state (
    id                  INT PRIMARY KEY DEFAULT 1 CHECK (id = 1),
    last_event_id       BIGINT DEFAULT 0,
    last_event_created_at TIMESTAMPTZ
);

CREATE INDEX idx_notification_queue_status ON notification_queue (status, next_retry_at);

INSERT INTO notification_state (id) VALUES (1) ON CONFLICT DO NOTHING;