-- ============================================================
--  NITS MAIL — Database Installation Schema
--  Compatible: MariaDB 10.4+ · MySQL 8.0+
--  Charset: utf8mb4 · Collation: utf8mb4_unicode_ci
--
--  Install: SOURCE nitsmail.sql;
--  Or via phpMyAdmin: Import this file on your database
--
--  DO NOT EDIT unless you know what you are doing.
--  Version: 1.0.0
-- ============================================================

SET CHARACTER SET utf8mb4;

-- ============================================================
--  SCHEMA VERSION TABLE  (migration tracking)
-- ============================================================

CREATE TABLE IF NOT EXISTS `nitsmail_schema_versions` (
    `id`          INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `version`     VARCHAR(20)     NOT NULL,
    `description` VARCHAR(255)    NOT NULL,
    `applied_at`  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_version` (`version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Tracks applied schema migrations';
    END IF;
    END IF;
END//
DELIMITER ;



-- ============================================================
--  4. FOLDERS
-- ============================================================

CREATE TABLE IF NOT EXISTS `nm_folders` (
    `id`             CHAR(36)      NOT NULL,
    `account_id`     CHAR(36)      NOT NULL,
    `user_id`        CHAR(36)      NOT NULL,
    `parent_id`      CHAR(36)      DEFAULT NULL,
    `name`           VARCHAR(255)  NOT NULL,
    `full_path`      VARCHAR(500)  NOT NULL,
    `folder_type`    ENUM('inbox','sent','drafts','trash','spam','archive','custom') NOT NULL DEFAULT 'custom',
    `imap_uid_val`   BIGINT UNSIGNED DEFAULT NULL,
    `imap_uid_next`  BIGINT UNSIGNED DEFAULT NULL,
    `total_count`    INT UNSIGNED  NOT NULL DEFAULT 0,
    `unread_count`   INT UNSIGNED  NOT NULL DEFAULT 0,
    `is_subscribed`  TINYINT(1)    NOT NULL DEFAULT 1,
    `is_selectable`  TINYINT(1)    NOT NULL DEFAULT 1,
    `sort_order`     SMALLINT      NOT NULL DEFAULT 0,
    `created_at`     DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`     DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_nm_folder_path`  (`account_id`, `full_path`(255)),
    KEY `idx_nm_folders_user`       (`user_id`),
    KEY `idx_nm_folders_parent`     (`parent_id`),
    KEY `idx_nm_folders_type`       (`account_id`, `folder_type`),
    CONSTRAINT `fk_nm_folders_account`
        FOREIGN KEY (`account_id`) REFERENCES `nm_mail_accounts`(`id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_nm_folders_user`
        FOREIGN KEY (`user_id`) REFERENCES `nm_users`(`id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_nm_folders_parent`
        FOREIGN KEY (`parent_id`) REFERENCES `nm_folders`(`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='IMAP folder tree and POP3 virtual folders';

-- ============================================================
--  5. EMAILS
-- ============================================================

CREATE TABLE IF NOT EXISTS `nm_emails` (
    `id`                CHAR(36)         NOT NULL,
    `account_id`        CHAR(36)         NOT NULL,
    `user_id`           CHAR(36)         NOT NULL,
    `folder_id`         CHAR(36)         DEFAULT NULL,

    `message_id`        VARCHAR(512)     DEFAULT NULL,
    `imap_uid`          BIGINT UNSIGNED  DEFAULT NULL,
    `imap_flags`        VARCHAR(500)     DEFAULT NULL,
    `pop3_uidl`         VARCHAR(255)     DEFAULT NULL,
    `thread_id`         CHAR(36)         DEFAULT NULL,

    `subject`           VARCHAR(998)     DEFAULT NULL,
    `from_name`         VARCHAR(255)     DEFAULT NULL,
    `from_email`        VARCHAR(320)     NOT NULL,
    `reply_to_name`     VARCHAR(255)     DEFAULT NULL,
    `reply_to_email`    VARCHAR(320)     DEFAULT NULL,
    `to_addresses`      JSON             DEFAULT NULL,
    `cc_addresses`      JSON             DEFAULT NULL,
    `bcc_addresses`     JSON             DEFAULT NULL,
    `in_reply_to`       VARCHAR(512)     DEFAULT NULL,
    `references_header` TEXT             DEFAULT NULL,

    `body_text`         LONGTEXT         DEFAULT NULL,
    `body_html`         LONGTEXT         DEFAULT NULL,
    `body_size_bytes`   INT UNSIGNED     NOT NULL DEFAULT 0,

    `status`            ENUM('unread','read','archived','deleted','spam') NOT NULL DEFAULT 'unread',
    `is_starred`        TINYINT(1)       NOT NULL DEFAULT 0,
    `is_draft`          TINYINT(1)       NOT NULL DEFAULT 0,
    `is_sent`           TINYINT(1)       NOT NULL DEFAULT 0,
    `has_attachments`   TINYINT(1)       NOT NULL DEFAULT 0,
    `importance`        TINYINT          NOT NULL DEFAULT 0,
    `spam_score`        DECIMAL(4,2)     DEFAULT NULL,

    `sent_at`           DATETIME         DEFAULT NULL,
    `received_at`       DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `read_at`           DATETIME         DEFAULT NULL,
    `deleted_at`        DATETIME         DEFAULT NULL,

    `raw_headers`       MEDIUMTEXT       DEFAULT NULL,
    `raw_size_bytes`    INT UNSIGNED     NOT NULL DEFAULT 0,

    `created_at`        DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`        DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    KEY `idx_nm_emails_account`    (`account_id`),
    KEY `idx_nm_emails_user`       (`user_id`),
    KEY `idx_nm_emails_folder`     (`folder_id`),
    KEY `idx_nm_emails_thread`     (`thread_id`),
    KEY `idx_nm_emails_message_id` (`message_id`(255)),
    KEY `idx_nm_emails_imap_uid`   (`account_id`, `folder_id`, `imap_uid`),
    KEY `idx_nm_emails_pop3_uidl`  (`account_id`, `pop3_uidl`(100)),
    KEY `idx_nm_emails_status`     (`user_id`, `status`, `deleted_at`),
    KEY `idx_nm_emails_starred`    (`user_id`, `is_starred`),
    KEY `idx_nm_emails_received`   (`user_id`, `received_at`),
    KEY `idx_nm_emails_sent_at`    (`user_id`, `sent_at`),
    KEY `idx_nm_emails_from`       (`user_id`, `from_email`(100)),
    FULLTEXT KEY `ft_nm_emails`    (`subject`, `from_name`, `from_email`, `body_text`),

    CONSTRAINT `fk_nm_emails_account`
        FOREIGN KEY (`account_id`) REFERENCES `nm_mail_accounts`(`id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_nm_emails_user`
        FOREIGN KEY (`user_id`) REFERENCES `nm_users`(`id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_nm_emails_folder`
        FOREIGN KEY (`folder_id`) REFERENCES `nm_folders`(`id`)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Core email message store';

-- Thread auto-assign trigger
DELIMITER //
CREATE TRIGGER `trg_nm_emails_thread`
BEFORE INSERT ON `nm_emails`
FOR EACH ROW
BEGIN
    DECLARE v_thread CHAR(36) DEFAULT NULL;
    IF NEW.in_reply_to IS NOT NULL AND NEW.in_reply_to != '' THEN
        SELECT `thread_id` INTO v_thread
        FROM `nm_emails`
        WHERE `message_id` = NEW.in_reply_to AND `user_id` = NEW.user_id
        LIMIT 1;
    END IF;
    SET NEW.thread_id = COALESCE(v_thread, NEW.thread_id, NEW.id);
END//

-- Folder count triggers
CREATE TRIGGER `trg_nm_emails_count_insert`
AFTER INSERT ON `nm_emails`
FOR EACH ROW
BEGIN
    IF NEW.folder_id IS NOT NULL THEN
        UPDATE `nm_folders`
        SET `total_count`  = `total_count` + 1,
            `unread_count` = `unread_count` + IF(NEW.status = 'unread', 1, 0)
        WHERE `id` = NEW.folder_id;
    END IF;
END//

CREATE TRIGGER `trg_nm_emails_count_update`
AFTER UPDATE ON `nm_emails`
FOR EACH ROW
BEGIN
    IF COALESCE(OLD.folder_id,'') != COALESCE(NEW.folder_id,'') THEN
        IF OLD.folder_id IS NOT NULL THEN
            UPDATE `nm_folders`
            SET `total_count`  = GREATEST(0, `total_count` - 1),
                `unread_count` = GREATEST(0, `unread_count` - IF(OLD.status='unread',1,0))
            WHERE `id` = OLD.folder_id;
        END IF;
        IF NEW.folder_id IS NOT NULL THEN
            UPDATE `nm_folders`
            SET `total_count`  = `total_count` + 1,
                `unread_count` = `unread_count` + IF(NEW.status='unread',1,0)
            WHERE `id` = NEW.folder_id;
        END IF;
    ELSEIF OLD.status != NEW.status AND NEW.folder_id IS NOT NULL THEN
        UPDATE `nm_folders`
        SET `unread_count` = GREATEST(0,
            `unread_count`
            + IF(NEW.status='unread',1,0)
            - IF(OLD.status='unread',1,0))
        WHERE `id` = NEW.folder_id;
    END IF;
END//

CREATE TRIGGER `trg_nm_emails_count_delete`
AFTER DELETE ON `nm_emails`
FOR EACH ROW
BEGIN
    IF OLD.folder_id IS NOT NULL THEN
        UPDATE `nm_folders`
        SET `total_count`  = GREATEST(0, `total_count` - 1),
            `unread_count` = GREATEST(0, `unread_count` - IF(OLD.status='unread',1,0))
        WHERE `id` = OLD.folder_id;
    END IF;
END//
DELIMITER ;

-- ============================================================
--  6. ATTACHMENTS
-- ============================================================

CREATE TABLE IF NOT EXISTS `nm_attachments` (
    `id`               CHAR(36)      NOT NULL,
    `email_id`         CHAR(36)      NOT NULL,
    `user_id`          CHAR(36)      NOT NULL,
    `filename`         VARCHAR(512)  NOT NULL,
    `filename_safe`    VARCHAR(512)  NOT NULL,
    `content_type`     VARCHAR(255)  NOT NULL DEFAULT 'application/octet-stream',
    `content_id`       VARCHAR(255)  DEFAULT NULL,
    `attachment_type`  ENUM('inline','attachment') NOT NULL DEFAULT 'attachment',
    `size_bytes`       INT UNSIGNED  NOT NULL DEFAULT 0,
    `storage_driver`   ENUM('local','s3','gcs','ftp') NOT NULL DEFAULT 'local',
    `storage_bucket`   VARCHAR(255)  DEFAULT NULL,
    `storage_key`      TEXT          NOT NULL,
    `checksum_sha256`  VARCHAR(64)   DEFAULT NULL,
    `is_inline`        TINYINT(1)    NOT NULL DEFAULT 0,
    `sort_order`       SMALLINT      NOT NULL DEFAULT 0,
    `created_at`       DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    KEY `idx_nm_attach_email` (`email_id`),
    KEY `idx_nm_attach_user`  (`user_id`),
    CONSTRAINT `fk_nm_attach_email`
        FOREIGN KEY (`email_id`) REFERENCES `nm_emails`(`id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_nm_attach_user`
        FOREIGN KEY (`user_id`) REFERENCES `nm_users`(`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Email attachment metadata';
END//
DELIMITER ;

-- ============================================================
--  7. LABELS
-- ============================================================

CREATE TABLE IF NOT EXISTS `nm_labels` (
    `id`         CHAR(36)     NOT NULL,
    `user_id`    CHAR(36)     NOT NULL,
    `name`       VARCHAR(100) NOT NULL,
    `color`      ENUM('red','orange','yellow','green','teal','blue','purple','pink','gray','brown') NOT NULL DEFAULT 'blue',
    `hex_color`  VARCHAR(7)   DEFAULT NULL,
    `is_system`  TINYINT(1)   NOT NULL DEFAULT 0,
    `sort_order` SMALLINT     NOT NULL DEFAULT 0,
    `created_at` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_nm_label_name` (`user_id`, `name`),
    KEY `idx_nm_labels_user` (`user_id`),
    CONSTRAINT `fk_nm_labels_user`
        FOREIGN KEY (`user_id`) REFERENCES `nm_users`(`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='User-defined labels / tags';

CREATE TABLE IF NOT EXISTS `nm_email_labels` (
    `email_id`   CHAR(36) NOT NULL,
    `label_id`   CHAR(36) NOT NULL,
    `applied_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`email_id`, `label_id`),
    KEY `idx_nm_el_label` (`label_id`),
    CONSTRAINT `fk_nm_el_email`
        FOREIGN KEY (`email_id`) REFERENCES `nm_emails`(`id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_nm_el_label`
        FOREIGN KEY (`label_id`) REFERENCES `nm_labels`(`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Many-to-many: emails <-> labels';

-- ============================================================
--  8. CONTACTS
-- ============================================================

CREATE TABLE IF NOT EXISTS `nm_contacts` (
    `id`                 CHAR(36)     NOT NULL,
    `user_id`            CHAR(36)     NOT NULL,
    `first_name`         VARCHAR(100) DEFAULT NULL,
    `last_name`          VARCHAR(100) DEFAULT NULL,
    `display_name`       VARCHAR(200) DEFAULT NULL,
    `nickname`           VARCHAR(100) DEFAULT NULL,
    `company`            VARCHAR(200) DEFAULT NULL,
    `job_title`          VARCHAR(200) DEFAULT NULL,
    `department`         VARCHAR(200) DEFAULT NULL,
    `emails`             JSON         DEFAULT NULL,
    `phones`             JSON         DEFAULT NULL,
    `addresses`          JSON         DEFAULT NULL,
    `urls`               JSON         DEFAULT NULL,
    `social`             JSON         DEFAULT NULL,
    `avatar_url`         TEXT         DEFAULT NULL,
    `avatar_storage_key` TEXT         DEFAULT NULL,
    `notes`              TEXT         DEFAULT NULL,
    `birthday`           DATE         DEFAULT NULL,
    `source`             ENUM('manual','imported','synced_google','synced_ms','synced_carddav') NOT NULL DEFAULT 'manual',
    `external_id`        VARCHAR(255) DEFAULT NULL,
    `is_favorite`        TINYINT(1)   NOT NULL DEFAULT 0,
    `email_count`        INT UNSIGNED NOT NULL DEFAULT 0,
    `created_at`         DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`         DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    KEY `idx_nm_contacts_user`     (`user_id`),
    KEY `idx_nm_contacts_favorite` (`user_id`, `is_favorite`),
    FULLTEXT KEY `ft_nm_contacts`  (`display_name`, `first_name`, `last_name`, `company`),
    CONSTRAINT `fk_nm_contacts_user`
        FOREIGN KEY (`user_id`) REFERENCES `nm_users`(`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Address book';

-- ============================================================
--  9. EMAIL FILTERS / INBOX RULES
-- ============================================================

CREATE TABLE IF NOT EXISTS `nm_email_filters` (
    `id`               CHAR(36)     NOT NULL,
    `user_id`          CHAR(36)     NOT NULL,
    `account_id`       CHAR(36)     DEFAULT NULL,
    `name`             VARCHAR(255) NOT NULL,
    `is_active`        TINYINT(1)   NOT NULL DEFAULT 1,
    `stop_processing`  TINYINT(1)   NOT NULL DEFAULT 0,
    `sort_order`       SMALLINT     NOT NULL DEFAULT 0,
    `conditions`       JSON         NOT NULL,
    `condition_logic`  ENUM('AND','OR') NOT NULL DEFAULT 'AND',
    `actions`          JSON         NOT NULL,
    `applied_count`    INT UNSIGNED NOT NULL DEFAULT 0,
    `last_applied_at`  DATETIME     DEFAULT NULL,
    `created_at`       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    KEY `idx_nm_filters_user`    (`user_id`, `is_active`),
    KEY `idx_nm_filters_account` (`account_id`),
    CONSTRAINT `fk_nm_filters_user`
        FOREIGN KEY (`user_id`) REFERENCES `nm_users`(`id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_nm_filters_account`
        FOREIGN KEY (`account_id`) REFERENCES `nm_mail_accounts`(`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Inbox rules / filters';

-- ============================================================
--  10. SYNC QUEUE
-- ============================================================

CREATE TABLE IF NOT EXISTS `nm_sync_queue` (
    `id`            CHAR(36)      NOT NULL,
    `account_id`    CHAR(36)      NOT NULL,
    `user_id`       CHAR(36)      NOT NULL,
    `task_type`     ENUM('send_email','fetch_imap','fetch_pop3','delete_remote',
                         'move_remote','flag_remote','initial_backfill') NOT NULL,
    `payload`       JSON          NOT NULL,
    `priority`      TINYINT UNSIGNED NOT NULL DEFAULT 5,
    `scheduled_at`  DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `started_at`    DATETIME      DEFAULT NULL,
    `completed_at`  DATETIME      DEFAULT NULL,
    `failed_at`     DATETIME      DEFAULT NULL,
    `retry_count`   TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `max_retries`   TINYINT UNSIGNED NOT NULL DEFAULT 3,
    `error_message` TEXT          DEFAULT NULL,
    `created_at`    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    KEY `idx_nm_queue_pending`  (`scheduled_at`, `priority`, `completed_at`, `failed_at`),
    KEY `idx_nm_queue_account`  (`account_id`),
    CONSTRAINT `fk_nm_queue_account`
        FOREIGN KEY (`account_id`) REFERENCES `nm_mail_accounts`(`id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_nm_queue_user`
        FOREIGN KEY (`user_id`) REFERENCES `nm_users`(`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Background task queue';

-- ============================================================
--  11. SYNC LOGS
-- ============================================================

CREATE TABLE IF NOT EXISTS `nm_sync_logs` (
    `id`             CHAR(36)      NOT NULL,
    `account_id`     CHAR(36)      NOT NULL,
    `user_id`        CHAR(36)      NOT NULL,
    `folder_id`      CHAR(36)      DEFAULT NULL,
    `protocol`       ENUM('SMTP','IMAP','POP3') NOT NULL,
    `status`         ENUM('pending','syncing','success','failed','paused') NOT NULL,
    `started_at`     DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `finished_at`    DATETIME      DEFAULT NULL,
    `duration_ms`    INT UNSIGNED  DEFAULT NULL,
    `emails_fetched` INT UNSIGNED  NOT NULL DEFAULT 0,
    `emails_new`     INT UNSIGNED  NOT NULL DEFAULT 0,
    `emails_updated` INT UNSIGNED  NOT NULL DEFAULT 0,
    `emails_deleted` INT UNSIGNED  NOT NULL DEFAULT 0,
    `bytes_fetched`  BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `error_code`     VARCHAR(50)   DEFAULT NULL,
    `error_message`  TEXT          DEFAULT NULL,
    `retry_count`    TINYINT UNSIGNED NOT NULL DEFAULT 0,

    PRIMARY KEY (`id`),
    KEY `idx_nm_synclog_account` (`account_id`, `started_at`),
    KEY `idx_nm_synclog_status`  (`status`),
    CONSTRAINT `fk_nm_synclog_account`
        FOREIGN KEY (`account_id`) REFERENCES `nm_mail_accounts`(`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Sync operation audit log';

-- ============================================================
--  13. USER PREFERENCES
-- ============================================================

CREATE TABLE IF NOT EXISTS `nm_user_preferences` (
    `user_id`               CHAR(36)     NOT NULL,
    `theme`                 ENUM('light','dark','auto') NOT NULL DEFAULT 'light',
    `accent_color`          VARCHAR(7)   NOT NULL DEFAULT '#3a7bc1',
    `density`               ENUM('compact','comfortable','spacious') NOT NULL DEFAULT 'comfortable',
    `font_size`             TINYINT UNSIGNED NOT NULL DEFAULT 14,
    `preview_pane`          ENUM('right','bottom','off') NOT NULL DEFAULT 'right',
    `default_reply`         ENUM('reply','reply_all') NOT NULL DEFAULT 'reply',
    `auto_read_ms`          SMALLINT UNSIGNED NOT NULL DEFAULT 2000,
    `undo_send_seconds`     TINYINT UNSIGNED NOT NULL DEFAULT 10,
    `confirm_delete`        TINYINT(1)   NOT NULL DEFAULT 1,
    `auto_advance`          ENUM('next','prev','list') NOT NULL DEFAULT 'next',
    `desktop_notifications` TINYINT(1)   NOT NULL DEFAULT 1,
    `notification_sound`    TINYINT(1)   NOT NULL DEFAULT 1,
    `emails_per_page`       TINYINT UNSIGNED NOT NULL DEFAULT 50,
    `updated_at`            DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (`user_id`),
    CONSTRAINT `fk_nm_prefs_user`
        FOREIGN KEY (`user_id`) REFERENCES `nm_users`(`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Per-user UI and behavior settings';

-- ============================================================
--  14. NOTIFICATIONS
-- ============================================================

CREATE TABLE IF NOT EXISTS `nm_notifications` (
    `id`         CHAR(36)     NOT NULL,
    `user_id`    CHAR(36)     NOT NULL,
    `type`       ENUM('new_email','sync_error','quota_warning','security_alert','system') NOT NULL,
    `title`      VARCHAR(255) NOT NULL,
    `body`       TEXT         DEFAULT NULL,
    `action_url` VARCHAR(512) DEFAULT NULL,
    `related_id` CHAR(36)     DEFAULT NULL,
    `is_read`    TINYINT(1)   NOT NULL DEFAULT 0,
    `created_at` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    KEY `idx_nm_notif_user`   (`user_id`, `created_at`),
    KEY `idx_nm_notif_unread` (`user_id`, `is_read`),
    CONSTRAINT `fk_nm_notif_user`
        FOREIGN KEY (`user_id`) REFERENCES `nm_users`(`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
--  15. AUDIT LOG (security events)
-- ============================================================

CREATE TABLE IF NOT EXISTS `nm_audit_log` (
    `id`          CHAR(36)     NOT NULL,
    `user_id`     CHAR(36)     DEFAULT NULL,
    `action`      VARCHAR(100) NOT NULL,
    `resource`    VARCHAR(100) DEFAULT NULL,
    `resource_id` CHAR(36)     DEFAULT NULL,
    `ip_address`  VARCHAR(45)  DEFAULT NULL,
    `user_agent`  VARCHAR(512) DEFAULT NULL,
    `severity`    ENUM('low','medium','high','critical') NOT NULL DEFAULT 'low',
    `metadata`    JSON         DEFAULT NULL,
    `created_at`  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    KEY `idx_nm_audit_user`     (`user_id`, `created_at`),
    KEY `idx_nm_audit_action`   (`action`, `created_at`),
    KEY `idx_nm_audit_severity` (`severity`, `created_at`),
    CONSTRAINT `fk_nm_audit_user`
        FOREIGN KEY (`user_id`) REFERENCES `nm_users`(`id`)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Security and action audit trail';

-- ============================================================
--  17. DRAFT VERSIONS
-- ============================================================

CREATE TABLE IF NOT EXISTS `nm_draft_versions` (
    `id`        CHAR(36)      NOT NULL,
    `email_id`  CHAR(36)      NOT NULL,
    `version`   SMALLINT UNSIGNED NOT NULL DEFAULT 1,
    `subject`   VARCHAR(998)  DEFAULT NULL,
    `body_html` MEDIUMTEXT    DEFAULT NULL,
    `body_text` MEDIUMTEXT    DEFAULT NULL,
    `to_json`   JSON          DEFAULT NULL,
    `cc_json`   JSON          DEFAULT NULL,
    `bcc_json`  JSON          DEFAULT NULL,
    `saved_at`  DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_nm_draft_ver` (`email_id`, `version`),
    KEY `idx_nm_draft_email` (`email_id`),
    CONSTRAINT `fk_nm_draft_email`
        FOREIGN KEY (`email_id`) REFERENCES `nm_emails`(`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Auto-saved draft history';

-- ============================================================
--  VIEWS
-- ============================================================

CREATE OR REPLACE VIEW `v_nm_account_badges` AS
SELECT
    e.account_id,
    e.user_id,
    COUNT(*)                              AS total_count,
    SUM(IF(e.status='unread',1,0))        AS unread_count,
    SUM(IF(e.is_starred=1,1,0))           AS starred_count,
    MAX(e.received_at)                    AS last_email_at
FROM `nm_emails` e
WHERE e.deleted_at IS NULL
GROUP BY e.account_id, e.user_id;

CREATE OR REPLACE VIEW `v_nm_thread_summary` AS
SELECT
    thread_id, user_id,
    COUNT(*)                                                AS message_count,
    MIN(sent_at)                                            AS first_sent_at,
    MAX(sent_at)                                            AS last_sent_at,
    MAX(IF(status='unread',1,0))                           AS has_unread,
    MAX(is_starred)                                         AS has_starred,
    MAX(has_attachments)                                    AS has_attachments,
    GROUP_CONCAT(DISTINCT from_email ORDER BY received_at)  AS participants
FROM `nm_emails`
WHERE deleted_at IS NULL
GROUP BY thread_id, user_id;

-- ============================================================
--  SCHEMA VERSION
-- ============================================================

INSERT IGNORE INTO `nitsmail_schema_versions` (`version`, `description`) VALUES
('1.0.0', 'Initial NITS Mail schema install');


-- ============================================================
--  END — NITS MAIL SCHEMA v1.0.0
-- ============================================================
