-- ============================================================
--  Daniel — Schéma SQL neuf (v1)
--  Compatible MySQL 8 / MariaDB 10.11 · encodage utf8mb4
--  À importer sur une base VIDE (ne jamais importer l'ancienne base).
-- ============================================================

SET NAMES utf8mb4;
SET time_zone = '+00:00';

-- ---- Administrateurs -----------------------------------------------------
CREATE TABLE IF NOT EXISTS admins (
    id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
    email         VARCHAR(190) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,            -- password_hash()
    display_name  VARCHAR(120) NOT NULL DEFAULT '',
    last_login_at DATETIME NULL,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_admins_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---- Galeries (plates : pas d'imbrication — D-23) ------------------------
CREATE TABLE IF NOT EXISTS galleries (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    slug            VARCHAR(190) NOT NULL,
    title           VARCHAR(190) NOT NULL,
    intro_html      MEDIUMTEXT NULL,
    cover_image_id  INT UNSIGNED NULL,
    sort_order      INT NOT NULL DEFAULT 0,
    status          ENUM('draft','published') NOT NULL DEFAULT 'draft',
    seo_title       VARCHAR(190) NULL,
    seo_description VARCHAR(255) NULL,
    seo_noindex     TINYINT(1) NOT NULL DEFAULT 0,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_galleries_slug (slug),
    KEY idx_galleries_status_order (status, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---- Images / items de galerie ------------------------------------------
CREATE TABLE IF NOT EXISTS gallery_images (
    id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
    gallery_id    INT UNSIGNED NOT NULL,
    name          VARCHAR(190) NOT NULL,            -- nom OBLIGATOIRE
    alt           VARCHAR(255) NULL,
    filename      VARCHAR(255) NOT NULL,            -- binaire stocké (normalisé)
    original_name VARCHAR(255) NULL,
    width         INT UNSIGNED NULL,
    height        INT UNSIGNED NULL,
    sort_order    INT NOT NULL DEFAULT 0,           -- ordre MANUEL
    status        ENUM('published','hidden') NOT NULL DEFAULT 'published',
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_images_gallery_order (gallery_id, sort_order),
    CONSTRAINT fk_images_gallery FOREIGN KEY (gallery_id)
        REFERENCES galleries (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Couverture de galerie (FK ajoutée après pour éviter la dépendance circulaire)
ALTER TABLE galleries
    ADD CONSTRAINT fk_galleries_cover FOREIGN KEY (cover_image_id)
        REFERENCES gallery_images (id) ON DELETE SET NULL;

-- ---- Pages éditoriales (accueil, mentions légales…) ---------------------
CREATE TABLE IF NOT EXISTS pages (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    slug            VARCHAR(190) NOT NULL,
    title           VARCHAR(190) NOT NULL,
    body_html       MEDIUMTEXT NULL,
    status          ENUM('draft','published') NOT NULL DEFAULT 'draft',
    seo_title       VARCHAR(190) NULL,
    seo_description VARCHAR(255) NULL,
    seo_noindex     TINYINT(1) NOT NULL DEFAULT 0,
    updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_pages_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---- Paramètres du site (clé/valeur) ------------------------------------
CREATE TABLE IF NOT EXISTS settings (
    `key`   VARCHAR(100) NOT NULL,
    `value` MEDIUMTEXT NULL,
    PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---- Profils de dérivés d'images (D-26) ---------------------------------
CREATE TABLE IF NOT EXISTS image_profiles (
    id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `key`       VARCHAR(50) NOT NULL,
    label       VARCHAR(100) NOT NULL,
    width       INT UNSIGNED NOT NULL DEFAULT 0,
    height      INT UNSIGNED NOT NULL DEFAULT 0,
    crop        TINYINT(1) NOT NULL DEFAULT 0,
    orientation ENUM('auto','landscape','portrait') NOT NULL DEFAULT 'auto',
    webp        TINYINT(1) NOT NULL DEFAULT 1,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_profiles_key (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---- Redirections SEO (optionnel) ---------------------------------------
CREATE TABLE IF NOT EXISTS redirects (
    id       INT UNSIGNED NOT NULL AUTO_INCREMENT,
    old_path VARCHAR(255) NOT NULL,
    new_path VARCHAR(255) NOT NULL,
    code     SMALLINT NOT NULL DEFAULT 301,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_redirects_old (old_path)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---- Messages de contact (journal optionnel) ----------------------------
CREATE TABLE IF NOT EXISTS contact_messages (
    id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name       VARCHAR(190) NOT NULL,
    email      VARCHAR(190) NOT NULL,
    message    MEDIUMTEXT NOT NULL,
    ip         VARCHAR(45) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_contact_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
