SET NAMES utf8mb4;
CREATE TABLE IF NOT EXISTS smart_notifications (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 workspace_id BIGINT UNSIGNED NOT NULL,
 user_id BIGINT UNSIGNED NOT NULL,
 notification_type VARCHAR(80) NOT NULL,
 entity_type VARCHAR(80) NOT NULL,
 entity_id BIGINT UNSIGNED NOT NULL,
 title VARCHAR(255) NOT NULL,
 body TEXT NULL,
 action_url VARCHAR(500) NULL,
 severity ENUM('critical','warning','info') NOT NULL DEFAULT 'info',
 status ENUM('unread','read','archived') NOT NULL DEFAULT 'unread',
 dedupe_key CHAR(64) NOT NULL,
 read_at DATETIME NULL,
 archived_at DATETIME NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_sn_user_dedupe(user_id,dedupe_key),
 INDEX idx_sn_ws_user_status(workspace_id,user_id,status),
 INDEX idx_sn_severity(workspace_id,severity,created_at),
 CONSTRAINT fk_sn_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
 CONSTRAINT fk_sn_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS smart_notification_preferences (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 workspace_id BIGINT UNSIGNED NOT NULL,
 user_id BIGINT UNSIGNED NOT NULL,
 notification_type VARCHAR(80) NOT NULL,
 enabled TINYINT(1) NOT NULL DEFAULT 1,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_snp_user_type(workspace_id,user_id,notification_type),
 CONSTRAINT fk_snp_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
 CONSTRAINT fk_snp_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
