CREATE TABLE IF NOT EXISTS conversations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    type ENUM('channel', 'dm', 'automated') NOT NULL,
    folder_id INT,
    status ENUM('active', 'locked', 'archived') DEFAULT 'active' NOT NULL,
    metadata JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_folder_id (folder_id),
    KEY idx_status (status)
);

CREATE TABLE IF NOT EXISTS messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    conversation_id INT NOT NULL,
    sender_id INT NOT NULL,
    content TEXT NOT NULL,
    type ENUM('text', 'doc', 'system') DEFAULT 'text' NOT NULL,
    is_snapshot BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (conversation_id) REFERENCES conversations(id),
    KEY idx_conversation_id (conversation_id),
    KEY idx_sender_id (sender_id),
    KEY idx_created_at (created_at)
);

CREATE TABLE IF NOT EXISTS participants (
    conversation_id INT NOT NULL,
    user_id INT NOT NULL,
    role ENUM('member', 'compliance_officer') DEFAULT 'member' NOT NULL,
    last_read_at TIMESTAMP,
    PRIMARY KEY (conversation_id, user_id),
    FOREIGN KEY (conversation_id) REFERENCES conversations(id),
    KEY idx_user_id (user_id)
);

CREATE TABLE IF NOT EXISTS compliance_snapshots (
    id INT AUTO_INCREMENT PRIMARY KEY,
    original_message_id INT,
    conversation_id INT NOT NULL,
    snapshot_conversation_status ENUM('active', 'locked', 'archived'),
    sender_id INT NOT NULL,
    content TEXT NOT NULL,
    type ENUM('text', 'doc', 'system') DEFAULT 'text' NOT NULL,
    created_at TIMESTAMP,
    snapshot_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (conversation_id) REFERENCES conversations(id),
    KEY idx_snapshot_conversation_id (conversation_id),
    KEY idx_original_message_id (original_message_id),
    KEY idx_snapshot_at (snapshot_at)
);
