-- #migration 2026_10_12_002
-- /collaboration : vues, « Booster le projet », invitations de membres correspondants.
--  - published_at : date de PUBLICATION (distincte de created_at, la date de creation) ; sert au tri de la liste.
--    Les projets existants : published_at = created_at. Un « boost » la remet a maintenant ; boosted_at memorise le
--    dernier boost (limite de 7 jours pour les prospects).
--  - collaboration_request_views : membres distincts ayant ouvert un projet (hors auteur).
--  - collaboration_invites : membres auxquels l'auteur a deja envoye le projet (pas de doublon d'email).

ALTER TABLE collaboration_requests
  ADD COLUMN published_at DATETIME NULL AFTER created_at,
  ADD COLUMN boosted_at   DATETIME NULL AFTER published_at;

UPDATE collaboration_requests SET published_at = created_at WHERE published_at IS NULL;

ALTER TABLE collaboration_requests
  MODIFY COLUMN published_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ADD KEY idx_status_published (status, published_at);

CREATE TABLE IF NOT EXISTS collaboration_request_views (
  request_id    INT         NOT NULL,
  viewer_id     VARCHAR(64) NOT NULL,
  first_seen_at TIMESTAMP   DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (request_id, viewer_id),
  KEY idx_viewer (viewer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS collaboration_invites (
  request_id   INT         NOT NULL,
  recipient_id VARCHAR(64) NOT NULL,
  sender_id    VARCHAR(64) NOT NULL,
  sent_at      TIMESTAMP   DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (request_id, recipient_id),
  KEY idx_sender (sender_id, sent_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;