-- Schéma de base de données pour le système de gestion des invitations de mariage (RSVP)

-- Table pour stocker les invités
CREATE TABLE IF NOT EXISTS guests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    public_id CHAR(36) NOT NULL UNIQUE,
    last_name VARCHAR(255) NOT NULL,
    first_name VARCHAR(255) NOT NULL,
    is_adult BOOLEAN NOT NULL DEFAULT TRUE,
    is_attending BOOLEAN NOT NULL DEFAULT FALSE,
    added_by_guest BOOLEAN NOT NULL DEFAULT TRUE,
    menu_adult BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Table pour stocker les invitations
CREATE TABLE IF NOT EXISTS invitations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    public_id CHAR(36) NOT NULL UNIQUE,
    invitation_type_id INT NOT NULL,
    dinner_invitation BOOLEAN NOT NULL DEFAULT FALSE,
    reception_invitation BOOLEAN NOT NULL DEFAULT FALSE,
    last_email_sent_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Table pour stocker les réponses aux invitations
CREATE TABLE IF NOT EXISTS rsvp_responses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    invitation_id INT NOT NULL,
    edit_token VARCHAR(36) NOT NULL UNIQUE,
    is_attending_dinner BOOLEAN NOT NULL DEFAULT FALSE,
    is_attending_reception BOOLEAN NOT NULL DEFAULT FALSE,
    email VARCHAR(255),
    phone VARCHAR(20),
    dietary_restrictions TEXT,
    notes TEXT,
    FOREIGN KEY (invitation_id) REFERENCES invitations(id) ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY (edit_token)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Table intermédiaire pour associer les invités aux invitations
CREATE TABLE IF NOT EXISTS invitation_guests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    invitation_id INT NOT NULL,
    guest_id INT NOT NULL,
    FOREIGN KEY (invitation_id) REFERENCES invitations(id) ON DELETE CASCADE,
    FOREIGN KEY (guest_id) REFERENCES guests(id) ON DELETE CASCADE,
    UNIQUE KEY (invitation_id, guest_id)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;