-- Table messagerie privée
CREATE TABLE IF NOT EXISTS private_messages (
    id          INT(11)      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    sender_id   INT(11)      NOT NULL,
    receiver_id INT(11)      NOT NULL,
    content     TEXT         NOT NULL,
    is_read     TINYINT(1)   DEFAULT 0,
    created_at  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (sender_id)   REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (receiver_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_pm_receiver ON private_messages(receiver_id, is_read);
CREATE INDEX IF NOT EXISTS idx_pm_sender   ON private_messages(sender_id);
