-- Invitations and group participants tables for the new group signup flow
USE lvj_inscriptions;

CREATE TABLE IF NOT EXISTS invitations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  id_sejour INT NOT NULL,
  group_name VARCHAR(160) NOT NULL,
  recipient_nom VARCHAR(120) NULL,
  recipient_prenom VARCHAR(120) NULL,
  recipient_email VARCHAR(160) NOT NULL,
  token VARCHAR(64) NOT NULL UNIQUE,
  status ENUM('pending','sent','used','expired','cancelled') DEFAULT 'pending',
  expires_at DATETIME NULL,
  sent_at DATETIME NULL,
  used_at DATETIME NULL,
  created_by INT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_inv_sej FOREIGN KEY (id_sejour) REFERENCES sejours(id)
);

CREATE TABLE IF NOT EXISTS inscrits (
  id INT AUTO_INCREMENT PRIMARY KEY,
  id_inscription INT NOT NULL,
  nom VARCHAR(120) NOT NULL,
  prenom VARCHAR(120) NOT NULL,
  date_naissance DATE NULL,
  assurance TINYINT(1) DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_inscrits_ins FOREIGN KEY (id_inscription) REFERENCES inscriptions(id)
);

