Unified_Authentication_Platform/sql/V3__add_message_system.sql

CREATE TABLE IF NOT EXISTS messages (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    sender_id INT DEFAULT NULL COMMENT 'Sender User ID',
    receiver_id INT NOT NULL COMMENT 'Receiver User ID',
    app_id INT DEFAULT NULL COMMENT 'Source Application ID',
    type VARCHAR(20) NOT NULL COMMENT 'MESSAGE or NOTIFICATION',
    content_type VARCHAR(20) NOT NULL DEFAULT 'TEXT' COMMENT 'TEXT, IMAGE, VIDEO, FILE',
    title VARCHAR(255) NOT NULL COMMENT 'Message Title',
    content TEXT NOT NULL COMMENT 'Message Content or JSON',
    action_url VARCHAR(1000) DEFAULT NULL COMMENT 'SSO Jump URL',
    action_text VARCHAR(50) DEFAULT NULL COMMENT 'Button Text',
    is_read TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Is Read',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    read_at TIMESTAMP NULL DEFAULT NULL,
    INDEX idx_receiver_id (receiver_id),
    INDEX idx_sender_id (sender_id),
    INDEX idx_app_id (app_id),
    FOREIGN KEY (receiver_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (app_id) REFERENCES applications(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Messages Table';

CREATE TABLE IF NOT EXISTS user_devices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    device_token VARCHAR(255) NOT NULL COMMENT 'Push Token',
    platform VARCHAR(20) NOT NULL COMMENT 'ios, android, harmony',
    device_name VARCHAR(100) DEFAULT NULL,
    last_active TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_user_device (user_id, device_token),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='User Push Devices';