-- Migration 0011: create_notifications_and_activity_log
-- Auto-generated from database/schema.sql by database/build_migrations.php
-- Do not hand-edit this file — edit schema.sql and re-run that script instead.

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- =====================================================================
-- SECTION 10 — NOTIFICATIONS & ACTIVITY LOG (system-wide)
-- =====================================================================

CREATE TABLE IF NOT EXISTS notifications (
    id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id          BIGINT UNSIGNED NOT NULL,
    type             VARCHAR(60)     NOT NULL COMMENT 'new_lead, invoice_overdue, appointment_change, ai_escalation...',
    title            VARCHAR(191)    NOT NULL,
    message          VARCHAR(500)    NOT NULL,
    related_entity_type VARCHAR(40)  NULL,
    related_entity_id   BIGINT UNSIGNED NULL,
    is_read          TINYINT(1)      NOT NULL DEFAULT 0,
    read_at          DATETIME        NULL,
    created_at       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_notifications_user_unread (user_id, is_read, created_at),
    CONSTRAINT fk_notifications_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Per-user, per-type opt in/out (Notifications > Notification Preferences).
CREATE TABLE IF NOT EXISTS notification_preferences (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     BIGINT UNSIGNED NOT NULL,
    type        VARCHAR(60)     NOT NULL,
    channel     ENUM('in_app','email','sms') NOT NULL DEFAULT 'in_app',
    is_enabled  TINYINT(1)      NOT NULL DEFAULT 1,
    UNIQUE KEY uk_notification_prefs (user_id, type, channel),
    CONSTRAINT fk_notification_prefs_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- The system-wide audit trail (Activity Logs page: User Action /
-- System / Security / Admin categories).
CREATE TABLE IF NOT EXISTS activity_logs (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id      BIGINT UNSIGNED NULL COMMENT 'NULL = system-generated',
    category     ENUM('user_action','system','security','admin') NOT NULL DEFAULT 'user_action',
    action       VARCHAR(191)    NOT NULL,
    entity_type  VARCHAR(40)     NULL,
    entity_id    BIGINT UNSIGNED NULL,
    ip_address   VARCHAR(45)     NULL,
    user_agent   VARCHAR(255)    NULL,
    created_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_activity_logs_user (user_id),
    KEY idx_activity_logs_category_time (category, created_at),
    KEY idx_activity_logs_entity (entity_type, entity_id),
    CONSTRAINT fk_activity_logs_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


SET FOREIGN_KEY_CHECKS = 1;
