flatrender/backend/db/migrations/14_notification.sql

-- =====================================================================
-- NOTIFICATION SCHEMA — In-app, Push, Email, SMS, Telegram
-- =====================================================================

SET search_path TO notification, public;

CREATE TYPE notification_kind AS ENUM (
    'RenderCompleted','RenderFailed','RenderProgress',
    'PlanExpiring','PlanExpired','PaymentSuccess','PaymentFailed',
    'StorageWarning','StorageFull','ExportExpiring','ExportDeleted',
    'GiftEarned','QuestCompleted','LevelUp',
    'AccountSecurity','SystemAnnouncement','TenantInvite',
    'Marketing','Other'
);

CREATE TYPE notification_priority AS ENUM ('Low','Normal','High','Urgent');

CREATE TYPE delivery_channel AS ENUM ('InApp','Push','Email','SMS','Telegram','Webhook');

CREATE TYPE delivery_status_kind AS ENUM (
    'Pending','Sent','Delivered','Failed','Bounced','Suppressed'
);

-- ---------------------------------------------------------------------
-- notifications — in-app notification feed
-- ---------------------------------------------------------------------
CREATE TABLE notifications (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id           UUID NOT NULL,
    user_id             UUID NOT NULL,

    notification_type   notification_kind NOT NULL,
    priority            notification_priority NOT NULL DEFAULT 'Normal',

    title               TEXT NOT NULL,
    message             TEXT NOT NULL,
    label               TEXT,
    signature           TEXT,
    icon                TEXT,
    image               TEXT,
    animation_demo      TEXT,
    design              TEXT,

    -- Link target
    action_url          TEXT,
    action_text         TEXT,

    -- Linked entities (sparse)
    render_job_id       UUID,
    export_id           UUID,
    payment_id          UUID,
    gift_id             UUID,
    earned_gift_id      UUID,

    -- State
    is_emergency        BOOLEAN NOT NULL DEFAULT FALSE,
    seen                BOOLEAN NOT NULL DEFAULT FALSE,
    seen_at             TIMESTAMPTZ,
    clicked             BOOLEAN NOT NULL DEFAULT FALSE,
    clicked_at          TIMESTAMPTZ,
    gift_used           BOOLEAN NOT NULL DEFAULT FALSE,

    -- Lifecycle
    expire_date         TIMESTAMPTZ,
    create_date         TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at          TIMESTAMPTZ
);

CREATE INDEX idx_notifs_user_feed     ON notifications(user_id, created_at DESC) WHERE deleted_at IS NULL;
CREATE INDEX idx_notifs_unread        ON notifications(user_id) WHERE seen = FALSE AND deleted_at IS NULL;
CREATE INDEX idx_notifs_tenant_type   ON notifications(tenant_id, notification_type);

CREATE TRIGGER tg_notifs_updated_at
BEFORE UPDATE ON notifications FOR EACH ROW EXECUTE FUNCTION public.tg_set_updated_at();

-- ---------------------------------------------------------------------
-- readed_notifications — read receipts (kept for analytics)
-- ---------------------------------------------------------------------
CREATE TABLE readed_notifications (
    id                          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id                     UUID NOT NULL,
    notification_id             UUID NOT NULL REFERENCES notifications(id) ON DELETE CASCADE,
    read_date                   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_at                  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (user_id, notification_id)
);

CREATE INDEX idx_readed_user ON readed_notifications(user_id, read_date DESC);

-- ---------------------------------------------------------------------
-- notification_deliveries — outbound across channels
-- ---------------------------------------------------------------------
CREATE TABLE notification_deliveries (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id           UUID NOT NULL,
    user_id             UUID NOT NULL,
    notification_id     UUID REFERENCES notifications(id) ON DELETE SET NULL,

    channel             delivery_channel NOT NULL,
    recipient           TEXT NOT NULL,                      -- email/phone/push endpoint
    subject             TEXT,
    body_text           TEXT,
    body_html           TEXT,
    template_id         TEXT,                               -- reference to template engine
    template_vars       JSONB,

    -- Provider tracking
    provider            TEXT,                               -- 'web-push','smtp','kavenegar','telegram','firebase'
    provider_message_id TEXT,
    provider_response   JSONB,

    status              delivery_status_kind NOT NULL DEFAULT 'Pending',
    error_message       TEXT,
    error_code          TEXT,

    -- Retry
    attempt             INT NOT NULL DEFAULT 1,
    max_attempts        INT NOT NULL DEFAULT 3,
    next_retry_at       TIMESTAMPTZ,

    -- Timing
    sent_at             TIMESTAMPTZ,
    delivered_at        TIMESTAMPTZ,
    failed_at           TIMESTAMPTZ,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_deliveries_user        ON notification_deliveries(user_id, created_at DESC);
CREATE INDEX idx_deliveries_pending     ON notification_deliveries(next_retry_at)
       WHERE status IN ('Pending','Failed') AND next_retry_at IS NOT NULL;
CREATE INDEX idx_deliveries_channel     ON notification_deliveries(channel, status);

CREATE TRIGGER tg_deliveries_updated_at
BEFORE UPDATE ON notification_deliveries FOR EACH ROW EXECUTE FUNCTION public.tg_set_updated_at();

-- ---------------------------------------------------------------------
-- notification_preferences — per-user opt-in per channel per type
-- ---------------------------------------------------------------------
CREATE TABLE notification_preferences (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id             UUID NOT NULL,
    notification_type   notification_kind NOT NULL,
    channel             delivery_channel NOT NULL,
    enabled             BOOLEAN NOT NULL DEFAULT TRUE,
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (user_id, notification_type, channel)
);

CREATE INDEX idx_notif_prefs_user ON notification_preferences(user_id);

CREATE TRIGGER tg_notif_prefs_updated_at
BEFORE UPDATE ON notification_preferences FOR EACH ROW EXECUTE FUNCTION public.tg_set_updated_at();

-- ---------------------------------------------------------------------
-- notification_templates — reusable templates per tenant
-- ---------------------------------------------------------------------
CREATE TABLE notification_templates (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id           UUID,                               -- NULL = default

    code                TEXT NOT NULL,                      -- 'render.completed.email'
    channel             delivery_channel NOT NULL,
    locale              TEXT NOT NULL DEFAULT 'fa',

    subject             TEXT,
    body_text           TEXT,
    body_html           TEXT,
    push_title          TEXT,
    push_body           TEXT,
    push_icon           TEXT,

    variables_schema    JSONB,                              -- expected variables
    is_active           BOOLEAN NOT NULL DEFAULT TRUE,

    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    UNIQUE (tenant_id, code, channel, locale)
);

CREATE INDEX idx_notif_tpl_lookup ON notification_templates(tenant_id, code, channel, locale) WHERE is_active = TRUE;

CREATE TRIGGER tg_notif_tpl_updated_at
BEFORE UPDATE ON notification_templates FOR EACH ROW EXECUTE FUNCTION public.tg_set_updated_at();

-- ---------------------------------------------------------------------
-- Wire back: earned_gifts.notification_id
-- ---------------------------------------------------------------------
ALTER TABLE identity.earned_gifts
  ADD CONSTRAINT fk_earned_gifts_notification
  FOREIGN KEY (notification_id) REFERENCES notification.notifications(id) ON DELETE SET NULL;