-- ============================================================
--  Event Invitation System - Schema MySQL
-- ============================================================

CREATE DATABASE IF NOT EXISTS event_invitations CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE event_invitations;

-- Table des invités
CREATE TABLE IF NOT EXISTS guests (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email         VARCHAR(255) NOT NULL UNIQUE,
    name          VARCHAR(255) NOT NULL DEFAULT '',
    token         CHAR(64)     NOT NULL UNIQUE,
    created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    mail_sent_at  DATETIME     NULL,
    is_orga       TINYINT(1)   NOT NULL DEFAULT 0,
    INDEX idx_token (token),
    INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Migration (si la table existe déjà) :
-- ALTER TABLE guests ADD COLUMN is_orga TINYINT(1) NOT NULL DEFAULT 0;

-- Table des événements de tracking
CREATE TABLE IF NOT EXISTS tracking_events (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    guest_id    INT UNSIGNED NOT NULL,
    event_type  ENUM('email_open','link_click','qrcode_scan','page_view') NOT NULL,
    ip          VARCHAR(45)  NULL,
    user_agent  TEXT         NULL,
    created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (guest_id) REFERENCES guests(id) ON DELETE CASCADE,
    INDEX idx_guest  (guest_id),
    INDEX idx_type   (event_type),
    INDEX idx_date   (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table des réponses RSVP
CREATE TABLE IF NOT EXISTS rsvp_responses (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    guest_id        INT UNSIGNED NOT NULL UNIQUE,
    attendance      ENUM('yes','no','maybe') NOT NULL,
    coming_with     ENUM('alone','couple') NOT NULL DEFAULT 'alone',
    message         TEXT         NULL,
    responded_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (guest_id) REFERENCES guests(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
