-- #migration 2026_10_21_001
-- Messagerie privee (CDC, lot 2) — socle : conversations directes, messages, demandes de message. Les groupes, pieces jointes, reactions et partages
-- viendront par d'autres migrations ; les colonnes qui leur sont necessaires (type, titre, projet, sourdine…) existent deja.
--  - conversations.direct_key : « idA|idB » (identifiants tries) : UNE seule conversation directe par paire de membres.
--  - conversation_members : un membre par ligne ; accepted = 0 tant qu'une DEMANDE de message n'est pas acceptee (n° 9) ; deleted_at + cleared_before_id :
--    « supprimer pour moi » (la conversation disparait de ma liste ; l'historique anterieur ne revient pas).
--  - messages : le texte (body) n'est lu et ecrit QUE par la classe MessageStore (api/_message_store.php). cipher / key_version restent NULL (pas de chiffrement
--    pour le moment, decision du 13/10/2026) : ils existent pour pouvoir l'activer plus tard sans refonte.
--  - message_requests : une demande par conversation ; « declined » n'est jamais revele a l'expediteur.
CREATE TABLE IF NOT EXISTS conversations (
  id              BIGINT       NOT NULL AUTO_INCREMENT,
  type            ENUM('direct','group','project') NOT NULL DEFAULT 'direct',
  direct_key      VARCHAR(140) NULL,
  title           VARCHAR(120) NULL,
  project_id      BIGINT       NULL,
  created_by      VARCHAR(64)  NOT NULL,
  created_at      DATETIME     NOT NULL DEFAULT UTC_TIMESTAMP(),
  last_message_id BIGINT       NULL,
  last_message_at DATETIME     NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_direct (direct_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS conversation_members (
  conversation_id      BIGINT      NOT NULL,
  user_id              VARCHAR(64) NOT NULL,
  role                 ENUM('member','admin') NOT NULL DEFAULT 'member',
  accepted             TINYINT(1)  NOT NULL DEFAULT 1,
  pinned               TINYINT(1)  NOT NULL DEFAULT 0,
  archived             TINYINT(1)  NOT NULL DEFAULT 0,
  muted                TINYINT(1)  NOT NULL DEFAULT 0,
  marked_unread        TINYINT(1)  NOT NULL DEFAULT 0,
  last_read_message_id BIGINT      NOT NULL DEFAULT 0,
  cleared_before_id    BIGINT      NOT NULL DEFAULT 0,
  joined_at            DATETIME    NOT NULL DEFAULT UTC_TIMESTAMP(),
  deleted_at           DATETIME    NULL,
  PRIMARY KEY (conversation_id, user_id),
  KEY idx_cm_user (user_id, deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS messages (
  id              BIGINT      NOT NULL AUTO_INCREMENT,
  conversation_id BIGINT      NOT NULL,
  user_id         VARCHAR(64) NOT NULL,
  body            MEDIUMTEXT  NULL,
  cipher          VARCHAR(16) NULL,
  key_version     SMALLINT    NULL,
  quoted_id       BIGINT      NULL,
  created_at      DATETIME    NOT NULL DEFAULT UTC_TIMESTAMP(),
  edited_at       DATETIME    NULL,
  deleted_at      DATETIME    NULL,
  PRIMARY KEY (id),
  KEY idx_msg_conv (conversation_id, id),
  KEY idx_msg_author (user_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS message_requests (
  id              BIGINT      NOT NULL AUTO_INCREMENT,
  conversation_id BIGINT      NOT NULL,
  sender_id       VARCHAR(64) NOT NULL,
  recipient_id    VARCHAR(64) NOT NULL,
  status          ENUM('pending','accepted','declined') NOT NULL DEFAULT 'pending',
  created_at      DATETIME    NOT NULL DEFAULT UTC_TIMESTAMP(),
  decided_at      DATETIME    NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_req_conv (conversation_id),
  KEY idx_req_recipient (recipient_id, status),
  KEY idx_req_sender (sender_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;