-- =============================================================================
-- SSO VietProDev Backend — Notification Schema v3
-- Tables: notification_messages, notification_receipts, notification_delivery_attempts,
--         notification_devices, notification_preferences
-- Depends on: 001-auth-schema.sql (users, update_updated_at_column, platform_type)
--
-- Design improvements:
-- - Separated message content from recipient state (broadcast efficiency)
-- - Split notification_receipts into: receipts (inbox), attempts (delivery), events (analytics)
-- - Removed is_read BOOLEAN, using read_at TIMESTAMP NULL instead
-- - Constrained notification_type ENUM instead of free text
-- - Reduced triggers: business logic moved to service layer, stats to async worker
-- - Soft delete with audit trail
-- - Push token uniqueness constraint
-- - Partitioned tables: notification_delivery_attempts
-- Note: Partitioned tables require monthly partition creation via scheduled job
-- =============================================================================

-- =============================================================================
-- ENUM TYPES
-- =============================================================================

-- Notification delivery status (replaces is_sent BOOLEAN)
CREATE TYPE notification_status AS ENUM (
    'queued',
    'processing',
    'sent',
    'delivered',
    'failed',
    'cancelled',
    'expired'
);

-- Notification type (constrained values instead of free text)
CREATE TYPE notification_type AS ENUM (
    'order_created',
    'payment_success',
    'payment_failed',
    'order_shipped',
    'order_delivered',
    'system_alert',
    'promotion',
    'chat_message',
    'security_alert',
    'account_update'
);

-- Notification channel (replaces VARCHAR(50) freeform)
CREATE TYPE notification_channel AS ENUM (
    'in_app',
    'push',
    'email',
    'sms',
    'zalo'
);

-- Notification event type for analytics
CREATE TYPE notification_event_type AS ENUM (
    'read',
    'opened',
    'clicked',
    'dismissed'
);

-- Note: platform_type enum should already exist in 001-auth-schema.sql
-- CREATE TYPE platform_type AS ENUM ('web', 'ios', 'android', 'zalo_mini_app');

-- =============================================================================
-- TABLES
-- =============================================================================

-- Notification messages (template/content - no recipient info)
CREATE TABLE IF NOT EXISTS notification_messages (
    id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    title         VARCHAR(255) NOT NULL,
    body          TEXT,
    type          notification_type NOT NULL,
    data          JSONB,
    tenant_id     UUID,
    scheduled_at  TIMESTAMP WITH TIME ZONE,
    priority      INTEGER DEFAULT 0,
    created_by    UUID,
    campaign_id    UUID,
    template_id   UUID,
    external_key  VARCHAR(255),
    dedupe_key    VARCHAR(255),
    created_at    TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

    CHECK (priority >= 0)
);

-- Notification receipts (inbox state - one row per user per message)
-- Simplified: only tracks inbox state, not delivery details
CREATE TABLE IF NOT EXISTS notification_receipts (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    message_id      UUID NOT NULL REFERENCES notification_messages(id) ON DELETE CASCADE,
    user_id         UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    channel         notification_channel NOT NULL,
    read_at         TIMESTAMP WITH TIME ZONE,
    deleted_at      TIMESTAMP WITH TIME ZONE,
    archived_at     TIMESTAMP WITH TIME ZONE,
    created_at      TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

    UNIQUE (message_id, user_id, channel)
);

-- Notification devices (push tokens and device registry)
CREATE TABLE IF NOT EXISTS notification_devices (
    id                   UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id              UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    platform             platform_type NOT NULL,
    device_id            VARCHAR(255) NOT NULL,
    device_name          VARCHAR(255),
    device_info          JSONB,
    push_token           TEXT,
    one_signal_player_id TEXT,
    is_active            BOOLEAN DEFAULT TRUE,
    last_used_at         TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    created_at           TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at           TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

    UNIQUE (user_id, device_id, platform)
);

-- Notification delivery attempts (sending attempts tracking)
CREATE TABLE IF NOT EXISTS notification_delivery_attempts (
    id                 UUID DEFAULT uuid_generate_v4(),
    receipt_id         UUID NOT NULL REFERENCES notification_receipts(id) ON DELETE CASCADE,
    device_id          UUID REFERENCES notification_devices(id) ON DELETE SET NULL,
    channel            notification_channel NOT NULL,
    status             notification_status NOT NULL,
    attempt_no         INTEGER DEFAULT 1,
    provider_name      VARCHAR(50),
    provider_message_id VARCHAR(255),
    latency_ms         INTEGER,
    response_payload   JSONB,
    error_message      TEXT,
    sent_at            TIMESTAMP WITH TIME ZONE,
    delivered_at       TIMESTAMP WITH TIME ZONE,
    failed_at          TIMESTAMP WITH TIME ZONE,
    next_retry_at      TIMESTAMP WITH TIME ZONE,
    created_at         TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at         TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

    CHECK (attempt_no >= 1),
    CHECK (latency_ms >= 0),
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- Notification preferences (user notification settings)
CREATE TABLE IF NOT EXISTS notification_preferences (
    id                UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id           UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    notification_type notification_type NOT NULL,
    channel           notification_channel NOT NULL,
    is_enabled        BOOLEAN DEFAULT TRUE,
    quiet_hours_start TIME,
    quiet_hours_end   TIME,
    timezone          VARCHAR(50) DEFAULT 'Asia/Ho_Chi_Minh',
    created_at        TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at        TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

    UNIQUE (user_id, notification_type, channel)
);


-- =============================================================================
-- INDEXES
-- =============================================================================

-- Notification messages indexes
CREATE INDEX IF NOT EXISTS idx_notification_messages_type
    ON notification_messages(type);

CREATE INDEX IF NOT EXISTS idx_notification_messages_tenant_type_created
    ON notification_messages(tenant_id, type, created_at DESC)
    WHERE tenant_id IS NOT NULL;

CREATE INDEX IF NOT EXISTS idx_notification_messages_scheduled_at
    ON notification_messages(scheduled_at)
    WHERE scheduled_at IS NOT NULL;

CREATE INDEX IF NOT EXISTS idx_notification_messages_campaign_id
    ON notification_messages(campaign_id)
    WHERE campaign_id IS NOT NULL;

CREATE INDEX IF NOT EXISTS idx_notification_messages_dedupe_key
    ON notification_messages(dedupe_key)
    WHERE dedupe_key IS NOT NULL;

-- Notification receipts indexes
-- Partial index for unread notifications (read_at IS NULL)
CREATE INDEX IF NOT EXISTS idx_notification_receipts_unread
    ON notification_receipts(user_id, created_at DESC)
    WHERE read_at IS NULL AND deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS idx_notification_receipts_message_id
    ON notification_receipts(message_id);

CREATE INDEX IF NOT EXISTS idx_notification_receipts_user_id
    ON notification_receipts(user_id);

CREATE INDEX IF NOT EXISTS idx_notification_receipts_channel
    ON notification_receipts(channel);

-- Notification devices indexes
CREATE INDEX IF NOT EXISTS idx_notification_devices_user_id_platform
    ON notification_devices(user_id, platform);

CREATE INDEX IF NOT EXISTS idx_notification_devices_user_id_is_active
    ON notification_devices(user_id, is_active)
    WHERE is_active = TRUE;

CREATE INDEX IF NOT EXISTS idx_notification_devices_device_id
    ON notification_devices(device_id);

-- Push token uniqueness (partial index)
CREATE UNIQUE INDEX IF NOT EXISTS idx_notification_devices_push_token
    ON notification_devices(push_token)
    WHERE push_token IS NOT NULL;

-- Notification delivery attempts indexes
CREATE INDEX IF NOT EXISTS idx_notification_delivery_attempts_receipt_id
    ON notification_delivery_attempts(receipt_id);

CREATE INDEX IF NOT EXISTS idx_notification_delivery_attempts_device_id
    ON notification_delivery_attempts(device_id);

CREATE INDEX IF NOT EXISTS idx_notification_delivery_attempts_status
    ON notification_delivery_attempts(status);

CREATE INDEX IF NOT EXISTS idx_notification_delivery_attempts_channel
    ON notification_delivery_attempts(channel);

CREATE INDEX IF NOT EXISTS idx_notification_delivery_attempts_created_at
    ON notification_delivery_attempts(created_at DESC);

-- Index for retry logic (queued/failed notifications)
CREATE INDEX IF NOT EXISTS idx_notification_delivery_attempts_retry
    ON notification_delivery_attempts(status, next_retry_at)
    WHERE status IN ('queued', 'failed');

CREATE INDEX IF NOT EXISTS idx_notification_delivery_attempts_provider_message_id
    ON notification_delivery_attempts(provider_message_id)
    WHERE provider_message_id IS NOT NULL;

-- Notification preferences indexes
CREATE INDEX IF NOT EXISTS idx_notification_prefs_user_id
    ON notification_preferences(user_id);

CREATE INDEX IF NOT EXISTS idx_notification_prefs_type
    ON notification_preferences(notification_type);

CREATE INDEX IF NOT EXISTS idx_notification_prefs_enabled
    ON notification_preferences(is_enabled)
    WHERE is_enabled = TRUE;

-- TRIGGERS
-- Only keep updated_at triggers - business logic moved to service layer
-- Stats updates moved to async worker to prevent write amplification

-- FUNCTIONS

-- Create notification message
CREATE OR REPLACE FUNCTION create_notification_message(
    p_title      VARCHAR(255),
    p_body       TEXT,
    p_type       notification_type,
    p_data       JSONB DEFAULT NULL,
    p_tenant_id  UUID DEFAULT NULL,
    p_scheduled_at TIMESTAMP WITH TIME ZONE DEFAULT NULL,
    p_priority   INTEGER DEFAULT 0,
    p_created_by UUID DEFAULT NULL,
    p_campaign_id UUID DEFAULT NULL,
    p_template_id UUID DEFAULT NULL,
    p_external_key VARCHAR(255) DEFAULT NULL,
    p_dedupe_key VARCHAR(255) DEFAULT NULL
)
RETURNS UUID AS $$
DECLARE
    message_id UUID;
BEGIN
    INSERT INTO notification_messages (title, body, type, data, tenant_id, scheduled_at, priority, created_by, campaign_id, template_id, external_key, dedupe_key)
    VALUES (p_title, p_body, p_type, p_data, p_tenant_id, p_scheduled_at, p_priority, p_created_by, p_campaign_id, p_template_id, p_external_key, p_dedupe_key)
    RETURNING id INTO message_id;
    
    RETURN message_id;
END;
$$ LANGUAGE plpgsql;

-- Create notification receipt
CREATE OR REPLACE FUNCTION create_notification_receipt(
    p_message_id  UUID,
    p_user_id     UUID,
    p_channel     notification_channel
)
RETURNS UUID AS $$
DECLARE
    receipt_id UUID;
BEGIN
    INSERT INTO notification_receipts (message_id, user_id, channel)
    VALUES (p_message_id, p_user_id, p_channel)
    RETURNING id INTO receipt_id;
    
    RETURN receipt_id;
END;
$$ LANGUAGE plpgsql;

-- Mark notifications as read
CREATE OR REPLACE FUNCTION mark_notifications_read(user_uuid UUID)
RETURNS INTEGER AS $$
DECLARE
    updated_count INTEGER;
BEGIN
    UPDATE notification_receipts
    SET read_at    = CURRENT_TIMESTAMP,
        updated_at = CURRENT_TIMESTAMP
    WHERE user_id    = user_uuid
      AND read_at    IS NULL
      AND deleted_at IS NULL;
    GET DIAGNOSTICS updated_count = ROW_COUNT;
    RETURN updated_count;
END;
$$ LANGUAGE plpgsql;

-- Get unread notification count (simple query)

-- Cleanup expired notifications (soft delete with batching)
-- Run multiple times to process all expired notifications
-- Each run processes up to 10,000 rows to prevent table bloat
CREATE OR REPLACE FUNCTION cleanup_expired_notifications()
RETURNS INTEGER AS $$
DECLARE
    updated_count INTEGER;
BEGIN
    UPDATE notification_receipts
    SET deleted_at = CURRENT_TIMESTAMP,
        updated_at = CURRENT_TIMESTAMP
    WHERE id IN (
        SELECT id FROM notification_receipts
        WHERE created_at < CURRENT_TIMESTAMP - INTERVAL '90 days'
          AND deleted_at IS NULL
        LIMIT 10000
    );
    
    GET DIAGNOSTICS updated_count = ROW_COUNT;
    RETURN updated_count;
END;
$$ LANGUAGE plpgsql;

-- NOTES
--
-- Partition management:
-- - notification_delivery_attempts is partitioned by RANGE(created_at)
-- - Create monthly partitions via scheduled job (cron/worker)
-- - Example: CREATE TABLE notification_delivery_attempts_2026_05 PARTITION OF notification_delivery_attempts
--           FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');

