-- ════════════════════════════════════════════════════════════════
--  dermiteseborrheique.fr — Schéma MySQL complet
--  Moteur InnoDB, charset utf8mb4 (supporte emojis)
--  Version 1.0.0
-- ════════════════════════════════════════════════════════════════

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ─────────────────────────────────────────────────────────────────
--  UTILISATEURS
-- ─────────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS users (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    uuid            CHAR(36)        NOT NULL UNIQUE,           -- UUID public (dans les URLs)
    email           VARCHAR(255)    NOT NULL UNIQUE,
    email_verified  TINYINT(1)      NOT NULL DEFAULT 0,
    email_token     VARCHAR(128)    NULL,                      -- Token de vérification email
    name            VARCHAR(100)    NOT NULL,
    avatar_url      VARCHAR(500)    NULL,                      -- URL avatar (Google ou upload)
    password_hash   VARCHAR(255)    NULL,                      -- NULL si auth Google uniquement
    google_id       VARCHAR(100)    NULL UNIQUE,               -- ID Google OAuth
    plan            ENUM('free','essential','premium') NOT NULL DEFAULT 'free',
    plan_expires_at DATETIME        NULL,                      -- NULL = pas d'expiration (free)
    lang            VARCHAR(5)      NOT NULL DEFAULT 'fr',     -- Langue préférée
    timezone        VARCHAR(50)     NOT NULL DEFAULT 'Europe/Paris',
    is_admin        TINYINT(1)      NOT NULL DEFAULT 0,
    is_active       TINYINT(1)      NOT NULL DEFAULT 1,        -- Soft ban possible
    last_login_at   DATETIME        NULL,
    meta            JSON            NULL,                      -- Données extensibles futures
    created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_email         (email),
    INDEX idx_google_id     (google_id),
    INDEX idx_plan          (plan),
    INDEX idx_created_at    (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────
--  SESSIONS "REMEMBER ME"
-- ─────────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS remember_tokens (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     INT UNSIGNED    NOT NULL,
    token_hash  VARCHAR(255)    NOT NULL UNIQUE,               -- Hash du cookie remember_me
    expires_at  DATETIME        NOT NULL,
    created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_token_hash (token_hash),
    INDEX idx_expires_at (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────
--  PROTECTION BRUTE FORCE
-- ─────────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS login_attempts (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ip_address  VARCHAR(45)     NOT NULL,                      -- IPv4 et IPv6
    email       VARCHAR(255)    NULL,                          -- Email tenté (pour audit)
    attempted_at DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_ip_address    (ip_address),
    INDEX idx_attempted_at  (attempted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────
--  TOKENS (reset password, vérification email, etc.)
-- ─────────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS tokens (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     INT UNSIGNED    NOT NULL,
    type        ENUM('email_verify','password_reset','api_key') NOT NULL,
    token_hash  VARCHAR(255)    NOT NULL UNIQUE,
    expires_at  DATETIME        NOT NULL,
    used_at     DATETIME        NULL,                          -- NULL = pas encore utilisé
    created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_token_hash (token_hash),
    INDEX idx_type_user  (type, user_id),
    INDEX idx_expires_at (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────
--  CATALOGUE D'ITEMS (aliments, produits, habitudes)
--  Table partagée — certains items sont globaux, d'autres privés
-- ─────────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS items (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     INT UNSIGNED    NULL,                          -- NULL = item global/communautaire
    category    ENUM(
                    'food',         -- Aliment
                    'drink',        -- Boisson
                    'alcohol',      -- Alcool
                    'tobacco',      -- Tabac / cigarette
                    'cosmetic',     -- Produit cosmétique (crème, sérum...)
                    'haircare',     -- Soin capillaire (shampoing, après-shampoing...)
                    'medication',   -- Médicament / complément
                    'supplement',   -- Complément alimentaire
                    'other'         -- Autre
                ) NOT NULL DEFAULT 'food',
    name        VARCHAR(200)    NOT NULL,
    name_en     VARCHAR(200)    NULL,                          -- Nom anglais pour i18n
    brand       VARCHAR(100)    NULL,                          -- Marque (cosmétiques/produits)
    ingredients TEXT            NULL,                          -- Ingrédients (INCI pour cosmétiques)
    is_global   TINYINT(1)      NOT NULL DEFAULT 0,            -- 1 = visible par tous
    usage_count INT UNSIGNED    NOT NULL DEFAULT 0,            -- Popularité pour autocomplete
    meta        JSON            NULL,
    created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_category      (category),
    INDEX idx_name          (name),
    INDEX idx_is_global     (is_global),
    INDEX idx_usage_count   (usage_count DESC),
    FULLTEXT idx_ft_name    (name)                             -- Recherche fulltext pour autocomplete
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────
--  ENTRÉES DE JOURNAL (une par user par jour)
-- ─────────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS journal_entries (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id         INT UNSIGNED    NOT NULL,
    entry_date      DATE            NOT NULL,                  -- Date de l'entrée (pas datetime)

    -- État de la peau
    skin_score      TINYINT UNSIGNED NOT NULL DEFAULT 3,       -- 1 (très mauvais) à 5 (excellent)
    is_crisis       TINYINT(1)      NOT NULL DEFAULT 0,        -- Crise déclarée ce jour
    crisis_severity TINYINT UNSIGNED NULL,                     -- 1-3 si is_crisis = 1

    -- Zones touchées (stockées en JSON pour flexibilité)
    skin_zones      JSON            NULL,
    -- Exemple : {"scalp": 2, "face": 1, "eyebrows": 3, "nose": 0, "chest": 0, "ears": 1}
    -- 0 = pas touché, 1 = léger, 2 = modéré, 3 = sévère

    -- Photo de peau (optionnel, premium)
    photo_path      VARCHAR(500)    NULL,

    -- Facteurs lifestyle
    sleep_hours     DECIMAL(3,1)    NULL,                      -- Heures de sommeil
    sleep_quality   TINYINT UNSIGNED NULL,                     -- 1-5
    stress_level    TINYINT UNSIGNED NULL,                     -- 1-5
    exercise_mins   SMALLINT UNSIGNED NULL,                    -- Minutes d'exercice
    water_liters    DECIMAL(3,1)    NULL,                      -- Litres d'eau

    -- Notes libres
    notes           TEXT            NULL,

    -- Données extensibles
    meta            JSON            NULL,

    created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY uq_user_date (user_id, entry_date),             -- Une seule entrée par user par jour
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_date     (user_id, entry_date DESC),
    INDEX idx_skin_score    (user_id, skin_score),
    INDEX idx_is_crisis     (user_id, is_crisis)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────
--  ITEMS D'UNE ENTRÉE (pivot journal ↔ items)
-- ─────────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS entry_items (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    entry_id    INT UNSIGNED    NOT NULL,
    item_id     INT UNSIGNED    NOT NULL,
    quantity    VARCHAR(50)     NULL,                          -- "2 verres", "1 portion"
    meal_time   ENUM('morning','noon','afternoon','evening','night') NULL,
    notes       VARCHAR(255)    NULL,
    created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (entry_id) REFERENCES journal_entries(id) ON DELETE CASCADE,
    FOREIGN KEY (item_id)  REFERENCES items(id) ON DELETE RESTRICT,
    INDEX idx_entry_id  (entry_id),
    INDEX idx_item_id   (item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────
--  RÉSULTATS DE CORRÉLATION (calculés périodiquement)
-- ─────────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS correlations (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id             INT UNSIGNED    NOT NULL,
    item_id             INT UNSIGNED    NOT NULL,

    -- Scores de corrélation
    score               DECIMAL(5,2)    NOT NULL DEFAULT 0,    -- 0.00 à 100.00
    occurrences_total   SMALLINT UNSIGNED NOT NULL DEFAULT 0,  -- Nb de fois consommé
    occurrences_crisis  SMALLINT UNSIGNED NOT NULL DEFAULT 0,  -- Nb de fois avant crise
    time_window_hours   TINYINT UNSIGNED  NOT NULL DEFAULT 48, -- Fenêtre utilisée (12/24/48/72h)

    -- Statut
    status              ENUM('suspect','cleared','ignored','confirmed') NOT NULL DEFAULT 'suspect',
    -- cleared = testé et éliminé sans amélioration
    -- confirmed = user confirme que c'est un déclencheur
    -- ignored = user veut ignorer cet item

    last_computed_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY uq_user_item_window (user_id, item_id, time_window_hours),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE,
    INDEX idx_user_score    (user_id, score DESC),
    INDEX idx_status        (user_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────
--  OBJECTIFS D'ÉLIMINATION
--  "Je teste sans gluten pendant 3 semaines"
-- ─────────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS elimination_goals (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     INT UNSIGNED    NOT NULL,
    item_id     INT UNSIGNED    NOT NULL,
    start_date  DATE            NOT NULL,
    end_date    DATE            NOT NULL,
    status      ENUM('active','completed','abandoned') NOT NULL DEFAULT 'active',
    outcome     ENUM('improved','no_change','worse','unknown') NULL, -- Résultat après test
    notes       TEXT            NULL,
    created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE,
    INDEX idx_user_status (user_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────
--  STATISTIQUES COMMUNAUTAIRES (agrégées anonymisées)
--  Recalculées périodiquement par un cron
-- ─────────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS community_stats (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    item_id         INT UNSIGNED    NOT NULL UNIQUE,
    total_users     INT UNSIGNED    NOT NULL DEFAULT 0,        -- Users ayant consommé cet item
    crisis_users    INT UNSIGNED    NOT NULL DEFAULT 0,        -- Users ayant eu une crise après
    avg_score       DECIMAL(5,2)    NOT NULL DEFAULT 0,        -- Score moyen de suspicion
    computed_at     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE,
    INDEX idx_avg_score (avg_score DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────
--  GAMIFICATION — BADGES
-- ─────────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS badges (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    slug        VARCHAR(50)     NOT NULL UNIQUE,               -- Ex: "streak_7", "first_suspect"
    name        VARCHAR(100)    NOT NULL,
    name_en     VARCHAR(100)    NULL,
    description TEXT            NULL,
    icon        VARCHAR(10)     NOT NULL DEFAULT '🏆',         -- Emoji
    created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_badges (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     INT UNSIGNED    NOT NULL,
    badge_id    INT UNSIGNED    NOT NULL,
    earned_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,

    UNIQUE KEY uq_user_badge (user_id, badge_id),
    FOREIGN KEY (user_id)  REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (badge_id) REFERENCES badges(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────
--  ABONNEMENTS / PAIEMENTS (préparation Stripe)
-- ─────────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS subscriptions (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id             INT UNSIGNED    NOT NULL,
    plan                ENUM('essential','premium') NOT NULL,
    status              ENUM('active','cancelled','expired','trial') NOT NULL DEFAULT 'trial',
    stripe_customer_id  VARCHAR(100)    NULL,
    stripe_sub_id       VARCHAR(100)    NULL,
    current_period_start DATETIME       NULL,
    current_period_end   DATETIME       NULL,
    cancelled_at        DATETIME        NULL,
    meta                JSON            NULL,
    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_status       (user_id, status),
    INDEX idx_stripe_customer   (stripe_customer_id),
    INDEX idx_stripe_sub        (stripe_sub_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────
--  NOTIFICATIONS
-- ─────────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS notifications (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     INT UNSIGNED    NOT NULL,
    type        VARCHAR(50)     NOT NULL,                      -- "new_suspect", "streak", "reminder"...
    title       VARCHAR(200)    NOT NULL,
    body        TEXT            NULL,
    is_read     TINYINT(1)      NOT NULL DEFAULT 0,
    meta        JSON            NULL,                          -- Données contextuelles (item_id, etc.)
    created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_read     (user_id, is_read),
    INDEX idx_created_at    (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────
--  DONNÉES INITIALES — Badges
-- ─────────────────────────────────────────────────────────────────

INSERT IGNORE INTO badges (slug, name, name_en, description, icon) VALUES
('first_entry',    'Premier journal',       'First Entry',       'Première entrée de journal complétée', '📔'),
('streak_7',       '7 jours consécutifs',   '7-day streak',      'Journal complété 7 jours de suite',    '🔥'),
('streak_30',      '30 jours consécutifs',  '30-day streak',     'Journal complété 30 jours de suite',   '⚡'),
('streak_90',      '90 jours consécutifs',  '90-day streak',     'Journal complété 90 jours de suite',   '💎'),
('first_suspect',  'Premier suspect',       'First Suspect',     'Premier déclencheur identifié',        '🔍'),
('confirmed_trigger', 'Déclencheur confirmé', 'Confirmed Trigger', 'Un déclencheur confirmé après test', '✅'),
('first_elim',     'Test d\'élimination',   'Elimination Test',  'Premier objectif d\'élimination lancé','🧪'),
('pdf_export',     'Rapport généré',        'Report Generated',  'Premier rapport PDF exporté',          '📄'),
('community',      'Membre actif',          'Active Member',     '30 jours d\'utilisation',              '🌍');

-- ─────────────────────────────────────────────────────────────────
--  DONNÉES INITIALES — Items globaux les plus courants
-- ─────────────────────────────────────────────────────────────────

INSERT IGNORE INTO items (user_id, category, name, name_en, is_global) VALUES
-- Aliments suspects connus
(NULL, 'food',    'Pain au levain',         'Sourdough bread',       1),
(NULL, 'food',    'Pain blanc',             'White bread',           1),
(NULL, 'food',    'Fromage affiné',         'Aged cheese',           1),
(NULL, 'food',    'Yaourt nature',          'Plain yogurt',          1),
(NULL, 'food',    'Lait de vache',          'Cow milk',              1),
(NULL, 'food',    'Gluten (général)',        'Gluten (general)',      1),
(NULL, 'food',    'Sucre raffiné',          'Refined sugar',         1),
(NULL, 'food',    'Chocolat noir',          'Dark chocolate',        1),
(NULL, 'food',    'Noix',                   'Nuts',                  1),
(NULL, 'food',    'Avocat',                 'Avocado',               1),
(NULL, 'food',    'Œufs',                   'Eggs',                  1),
(NULL, 'food',    'Poisson gras',           'Fatty fish',            1),
-- Boissons
(NULL, 'drink',   'Eau plate',              'Still water',           1),
(NULL, 'drink',   'Café',                   'Coffee',                1),
(NULL, 'drink',   'Thé vert',               'Green tea',             1),
(NULL, 'drink',   'Jus de fruit',           'Fruit juice',           1),
-- Alcool
(NULL, 'alcohol', 'Vin rouge',              'Red wine',              1),
(NULL, 'alcohol', 'Vin blanc',              'White wine',            1),
(NULL, 'alcohol', 'Bière',                  'Beer',                  1),
(NULL, 'alcohol', 'Spiritueux',             'Spirits',               1),
-- Tabac
(NULL, 'tobacco', 'Cigarette',              'Cigarette',             1),
(NULL, 'tobacco', 'Cigarette électronique', 'E-cigarette',           1),
-- Soins capillaires (suspects courants)
(NULL, 'haircare','Shampooing ordinaire',    'Regular shampoo',       1),
(NULL, 'haircare','Shampooing antipelliculaire', 'Anti-dandruff shampoo', 1),
(NULL, 'haircare','Après-shampooing',        'Conditioner',           1),
(NULL, 'haircare','Gel coiffant',            'Hair gel',              1),
(NULL, 'haircare','Huile capillaire',        'Hair oil',              1),
-- Cosmétiques (ingrédients suspects)
(NULL, 'cosmetic','Crème hydratante',        'Moisturizer',           1),
(NULL, 'cosmetic','Huile de coco',           'Coconut oil',           1),
(NULL, 'cosmetic','Gel douche',              'Shower gel',            1),
(NULL, 'cosmetic','Savon ordinaire',         'Regular soap',          1),
(NULL, 'cosmetic','Crème solaire',           'Sunscreen',             1);

SET FOREIGN_KEY_CHECKS = 1;