USE db_abos_v0.1;

-- =============================================================================
-- notifications_module.sql — Notifications, Broadcast & Notice Board.
--
-- One shared mechanism backs all three surfaces:
--   fact_notifications        — the notification/announcement/notice itself.
--   fact_notification_targets — its audience (can have multiple rows — e.g.
--                                Role A AND Role B). target_type='TENANT' means
--                                everyone in the tenant (target_id NULL);
--                                'BUSINESS'/'ROLE'/'USER' point at that id.
--   fact_notification_reads   — per-user read state for the bell's unread count.
--
-- Deliberately NOT registered as an AppModule path prefix (see AppModule.java) —
-- gating it behind a subscription plan would silently break the bell for every
-- existing tenant whose plan predates this feature, the same reasoning that
-- already keeps /api/public/web out of AppModule.WEB's prefixes. Authoring
-- (CREATE_NOTIFICATION) is instead gated in NotificationService itself.
--
-- Safe to re-run: CREATE TABLE IF NOT EXISTS throughout.
-- =============================================================================

CREATE TABLE IF NOT EXISTS fact_notifications (
    id            BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id     INT NOT NULL,
    business_id   INT NULL,
    category      VARCHAR(20) NOT NULL DEFAULT 'SYSTEM',
    title         VARCHAR(255) NOT NULL,
    body          TEXT NULL,
    link_url      VARCHAR(500) NULL,
    is_pinned     TINYINT NOT NULL DEFAULT 0,
    pinned_until  TIMESTAMP(6) NULL,
    created_at    TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    created_by    INT NULL,
    is_deleted    TINYINT NOT NULL DEFAULT 0,
    INDEX idx_fact_notifications_tenant (tenant_id, is_deleted, created_at),
    INDEX idx_fact_notifications_pinned (tenant_id, is_pinned, is_deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS fact_notification_targets (
    id              BIGINT AUTO_INCREMENT PRIMARY KEY,
    notification_id BIGINT NOT NULL,
    target_type     VARCHAR(20) NOT NULL,
    target_id       BIGINT NULL,
    INDEX idx_fact_notification_targets_notification (notification_id),
    INDEX idx_fact_notification_targets_lookup (target_type, target_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS fact_notification_reads (
    id              BIGINT AUTO_INCREMENT PRIMARY KEY,
    notification_id BIGINT NOT NULL,
    user_id         INT NOT NULL,
    read_at         TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    UNIQUE KEY uq_fact_notification_reads (notification_id, user_id),
    INDEX idx_fact_notification_reads_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;