-- ============================================
-- 迁移: 011_add_notifications.sql
-- 目的: 创建统一通知表，覆盖系统公告/个人通知/事件通知
-- 日期: 2026-05-26
-- ============================================

BEGIN;

CREATE TABLE IF NOT EXISTS notifications (
    id         SERIAL PRIMARY KEY,
    scope      VARCHAR(16) NOT NULL DEFAULT 'user',
    user_id    INTEGER REFERENCES users(id) ON DELETE CASCADE,
    type       VARCHAR(32) NOT NULL,
    title      VARCHAR(255) NOT NULL,
    content    TEXT,
    priority   VARCHAR(16) NOT NULL DEFAULT 'normal',
    link       VARCHAR(512),
    is_read    BOOLEAN NOT NULL DEFAULT false,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    expires_at TIMESTAMPTZ
);

CREATE INDEX IF NOT EXISTS idx_notifications_user
    ON notifications(scope, user_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_notifications_all
    ON notifications(scope, created_at DESC) WHERE scope = 'all';
CREATE INDEX IF NOT EXISTS idx_notifications_expires
    ON notifications(expires_at) WHERE expires_at IS NOT NULL;

COMMENT ON TABLE notifications IS '统一通知表：scope=all 系统广播，scope=user 定向通知';
COMMENT ON COLUMN notifications.scope IS '作用域: all(广播) / user(定向)';
COMMENT ON COLUMN notifications.type IS '通知类型: system_maintenance / member_expiry / payment_success / version_update';

COMMIT;