-- Migration 022 : tables pour le système de blocs modulaires
-- Remplace progressivement page_content_sections, activities et *_images distincts.

CREATE TABLE page_blocks (
    id          INT UNSIGNED    AUTO_INCREMENT PRIMARY KEY,
    owner       VARCHAR(100)    NOT NULL COMMENT 'cobaye | spa | room:belle-endormie | home …',
    block_type  VARCHAR(50)     NOT NULL COMMENT 'text | two-col-text | module-image | hero | booking-widget …',
    block_name  VARCHAR(255)    NOT NULL DEFAULT '' COMMENT 'Nom interne (non affiché sur le site)',
    anchor_key  VARCHAR(100)    NOT NULL DEFAULT '' COMMENT 'Slug pour ancres internes, dérivé du block_name',
    config      JSON            NULL COMMENT 'Paramètres non-linguistiques : layout, show_title, widget_type …',
    sort_order  SMALLINT        NOT NULL DEFAULT 0,
    is_locked   TINYINT(1)      NOT NULL DEFAULT 0 COMMENT '1 = position fixe (titre, hero)',
    created_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_owner_order (owner, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE page_block_translations (
    block_id    INT UNSIGNED    NOT NULL,
    locale      VARCHAR(10)     NOT NULL,
    content     JSON            NULL COMMENT '{"title":"…","body":"…"} — clés selon block_type',
    PRIMARY KEY (block_id, locale),
    FOREIGN KEY (block_id) REFERENCES page_blocks(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE page_block_images (
    id          INT UNSIGNED    AUTO_INCREMENT PRIMARY KEY,
    block_id    INT UNSIGNED    NOT NULL,
    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,
    FOREIGN KEY (block_id) REFERENCES page_blocks(id) ON DELETE CASCADE,
    INDEX idx_block_order (block_id, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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