-- Migration 019 : tables pour le contenu administrable des pages statiques
-- (Spa, Espace Privatif, Conditions Générales de Réservation)

CREATE TABLE page_content_sections (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    page         VARCHAR(50)          NOT NULL COMMENT 'spa | private-area | terms-of-reservation',
    section_key  VARCHAR(100)         NOT NULL COMMENT 'clé unique dans la page (ex: features.jacuzzi.label)',
    anchor_key   VARCHAR(100)         NULL     COMMENT 'ID HTML fixe pour ancres CGR (immuable)',
    section_type ENUM('text','html')  NOT NULL DEFAULT 'html',
    sort_order   SMALLINT             NOT NULL DEFAULT 0,
    updated_at   TIMESTAMP            NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_page_section (page, section_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE page_content_section_translations (
    section_id  INT          NOT NULL,
    locale      VARCHAR(10)  NOT NULL,
    value       MEDIUMTEXT   NOT NULL,
    PRIMARY KEY (section_id, locale),
    FOREIGN KEY (section_id) REFERENCES page_content_sections(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE page_content_images (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    page       VARCHAR(50)   NOT NULL COMMENT 'spa | private-area',
    context    VARCHAR(100)  NOT NULL COMMENT 'carousel | features.jacuzzi | features.saltwall | features.sauna',
    filename   VARCHAR(500)  NOT NULL,
    credits    VARCHAR(255)  NULL,
    sort_order SMALLINT      NOT NULL DEFAULT 0,
    is_primary TINYINT(1)    NOT NULL DEFAULT 0,
    created_at TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE page_content_image_translations (
    image_id  INT          NOT NULL,
    locale    VARCHAR(10)  NOT NULL,
    alt       VARCHAR(255) NOT NULL DEFAULT '',
    PRIMARY KEY (image_id, locale),
    FOREIGN KEY (image_id) REFERENCES page_content_images(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
