-- Adds team chat: messages (regular chat, admin announcements, and
-- document assignments all in one feed) and per-user read tracking for
-- the unread badge. Run once via phpMyAdmin -> Import, or:
--   mysql -u user -p dbname < sql/migration_005_chat.sql

CREATE TABLE IF NOT EXISTS messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    type ENUM('message', 'announcement', 'assignment') NOT NULL DEFAULT 'message',
    user_id INT NOT NULL,
    body TEXT NULL,
    document_id INT NULL,
    assigned_to INT NULL,
    assignment_status ENUM('open', 'done') NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE SET NULL,
    FOREIGN KEY (assigned_to) REFERENCES users(id),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS chat_reads (
    user_id INT PRIMARY KEY,
    last_read_id INT NOT NULL DEFAULT 0,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;