-- Mistake / Warning system table
-- Safe to import more than once.
CREATE TABLE IF NOT EXISTS csr_discipline_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    record_type ENUM('mistake','warning') NOT NULL,
    sender_id INT NOT NULL,
    sender_role VARCHAR(40) NOT NULL DEFAULT 'csr',
    recipient_id INT NOT NULL,
    recipient_role VARCHAR(40) NOT NULL DEFAULT 'csr',
    team_id INT NULL,
    title VARCHAR(180) NOT NULL,
    message TEXT NOT NULL,
    severity ENUM('low','normal','high') NOT NULL DEFAULT 'normal',
    is_read TINYINT(1) NOT NULL DEFAULT 0,
    read_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_disc_type_created (record_type, created_at),
    INDEX idx_disc_sender (sender_id, record_type),
    INDEX idx_disc_recipient (recipient_id, record_type, is_read),
    INDEX idx_disc_team (team_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
