-- Tables applicatives CookPlanner
-- À exécuter APRÈS cookplanner_mysql.sql

CREATE TABLE IF NOT EXISTS cookplanner_users (
    id            INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    nom           VARCHAR(100)    NOT NULL,
    prenom        VARCHAR(100)    NOT NULL,
    email         VARCHAR(200)    NOT NULL,
    password_hash VARCHAR(255)    NOT NULL,
    nb_convives   TINYINT         DEFAULT 4,
    objectif      ENUM('equilibre','proteine','leger','gourmand') DEFAULT 'equilibre',
    budget        ENUM('economique','modere','premium') DEFAULT 'modere',
    created_at    TIMESTAMP       DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP       DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cookplanner_preferences (
    id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id     INT UNSIGNED NOT NULL,
    cuisine_id  SMALLINT UNSIGNED DEFAULT NULL,
    tag         VARCHAR(50),
    valeur      VARCHAR(100),
    PRIMARY KEY (id),
    KEY fk_pref_user (user_id),
    CONSTRAINT fk_pref_user FOREIGN KEY (user_id)
        REFERENCES cookplanner_users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cookplanner_plannings (
    id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id      INT UNSIGNED NOT NULL,
    nom          VARCHAR(100) DEFAULT 'Mon planning',
    date_debut   DATE         NOT NULL,
    date_fin     DATE         NOT NULL,
    periode_type ENUM('jour','semaine','deux_semaines','mois','personnalise') DEFAULT 'semaine',
    statut       ENUM('brouillon','actif','archive') DEFAULT 'actif',
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY fk_plan_user (user_id),
    CONSTRAINT fk_plan_user FOREIGN KEY (user_id)
        REFERENCES cookplanner_users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cookplanner_planning_repas (
    id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
    planning_id INT UNSIGNED NOT NULL,
    plat_id     INT UNSIGNED NOT NULL,
    date_repas  DATE         NOT NULL,
    creneau     ENUM('matin','dejeuner','diner','collation') NOT NULL,
    nb_portions TINYINT      DEFAULT 4,
    genere_ia   TINYINT(1)   DEFAULT 0,
    notes       TEXT,
    PRIMARY KEY (id),
    UNIQUE KEY uq_planning_date_creneau (planning_id, date_repas, creneau),
    KEY fk_repas_planning (planning_id),
    KEY fk_repas_plat (plat_id),
    CONSTRAINT fk_repas_planning FOREIGN KEY (planning_id)
        REFERENCES cookplanner_plannings(id) ON DELETE CASCADE,
    CONSTRAINT fk_repas_plat FOREIGN KEY (plat_id)
        REFERENCES cookplanner_plats(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cookplanner_liste_courses (
    id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id     INT UNSIGNED NOT NULL,
    planning_id INT UNSIGNED NOT NULL,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY fk_lc_user (user_id),
    KEY fk_lc_planning (planning_id),
    CONSTRAINT fk_lc_user     FOREIGN KEY (user_id)     REFERENCES cookplanner_users(id) ON DELETE CASCADE,
    CONSTRAINT fk_lc_planning FOREIGN KEY (planning_id) REFERENCES cookplanner_plannings(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cookplanner_liste_items (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    liste_id        INT UNSIGNED NOT NULL,
    ingredient_id   INT UNSIGNED DEFAULT NULL,
    nom_libre       VARCHAR(200) DEFAULT NULL,
    quantite        VARCHAR(100),
    categorie       VARCHAR(50),
    coche           TINYINT(1) DEFAULT 0,
    PRIMARY KEY (id),
    KEY fk_item_liste (liste_id),
    CONSTRAINT fk_item_liste FOREIGN KEY (liste_id)
        REFERENCES cookplanner_liste_courses(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
