-- Migration: Création de la table pages
-- Date: 2025-09-09
-- Description: Table pour la gestion dynamique des pages

CREATE TABLE `pages` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `title` varchar(255) NOT NULL,
    `slug` varchar(255) NOT NULL,
    `type` enum('static', 'dynamic', 'template') NOT NULL DEFAULT 'static',
    `status` enum('active', 'inactive', 'draft') NOT NULL DEFAULT 'draft',
    `template` varchar(100) NULL DEFAULT NULL,
    `content` longtext NULL DEFAULT NULL,
    `meta_title` varchar(255) NULL DEFAULT NULL,
    `meta_description` text NULL DEFAULT NULL,
    `meta_keywords` text NULL DEFAULT NULL,
    `custom_css` longtext NULL DEFAULT NULL,
    `custom_js` longtext NULL DEFAULT NULL,
    `layout` varchar(100) NOT NULL DEFAULT 'main',
    `sort_order` int(11) NOT NULL DEFAULT 0,
    `is_homepage` tinyint(1) NOT NULL DEFAULT 0,
    `requires_auth` tinyint(1) NOT NULL DEFAULT 0,
    `allowed_roles` json NULL DEFAULT NULL,
    `created_by` bigint(20) UNSIGNED NOT NULL,
    `updated_by` bigint(20) UNSIGNED NULL DEFAULT NULL,
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `pages_slug_unique` (`slug`),
    KEY `pages_status_index` (`status`),
    KEY `pages_type_index` (`type`),
    KEY `pages_created_by_index` (`created_by`),
    FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE RESTRICT,
    FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table pour les composants de page (page builder)
CREATE TABLE `page_components` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `page_id` bigint(20) UNSIGNED NOT NULL,
    `component_type` varchar(100) NOT NULL,
    `component_data` json NOT NULL,
    `sort_order` int(11) NOT NULL DEFAULT 0,
    `is_active` tinyint(1) NOT NULL DEFAULT 1,
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `page_components_page_id_index` (`page_id`),
    KEY `page_components_component_type_index` (`component_type`),
    FOREIGN KEY (`page_id`) REFERENCES `pages` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table pour les blocs réutilisables
CREATE TABLE `blocks` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL,
    `slug` varchar(255) NOT NULL,
    `type` varchar(100) NOT NULL,
    `content` longtext NOT NULL,
    `is_global` tinyint(1) NOT NULL DEFAULT 0,
    `created_by` bigint(20) UNSIGNED NOT NULL,
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `blocks_slug_unique` (`slug`),
    KEY `blocks_type_index` (`type`),
    KEY `blocks_created_by_index` (`created_by`),
    FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;