CREATE TABLE IF NOT EXISTS users (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(190) NOT NULL,
    email VARCHAR(190) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role VARCHAR(32) NOT NULL DEFAULT 'contributor',
    created_by INT UNSIGNED NULL,
    updated_by INT UNSIGNED NULL,
    last_login_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at DATETIME NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS settings (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    group_name VARCHAR(120) NOT NULL,
    key_name VARCHAR(120) NOT NULL,
    value TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_settings_group_key (group_name, key_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS media (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    internal_code VARCHAR(64) NOT NULL,
    original_name VARCHAR(255) NOT NULL,
    stored_name VARCHAR(255) NOT NULL,
    title VARCHAR(255) NULL,
    alt_text VARCHAR(255) NULL,
    caption TEXT NULL,
    mime_type VARCHAR(120) NULL,
    extension VARCHAR(20) NULL,
    size_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
    width INT UNSIGNED NOT NULL DEFAULT 0,
    height INT UNSIGNED NOT NULL DEFAULT 0,
    variants_json LONGTEXT NULL,
    created_by INT UNSIGNED NULL,
    updated_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at DATETIME NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_media_internal_code (internal_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS form_submissions (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    source_type VARCHAR(32) NOT NULL,
    source_id INT UNSIGNED NULL,
    source_label VARCHAR(255) NULL,
    source_url VARCHAR(255) NULL,
    form_key VARCHAR(64) NULL,
    request_type VARCHAR(32) NOT NULL DEFAULT 'contact',
    full_name VARCHAR(190) NOT NULL,
    email VARCHAR(190) NOT NULL,
    phone VARCHAR(64) NULL,
    company VARCHAR(190) NULL,
    message LONGTEXT NULL,
    metadata_json LONGTEXT NULL,
    status VARCHAR(32) NOT NULL DEFAULT 'new',
    processed_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_form_submissions_source (source_type, source_id),
    KEY idx_form_submissions_request_type (request_type),
    KEY idx_form_submissions_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS products (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    internal_code VARCHAR(64) NOT NULL,
    name VARCHAR(190) NOT NULL,
    slug VARCHAR(190) NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    hero_eyebrow VARCHAR(255) NULL,
    tagline VARCHAR(255) NULL,
    short_description TEXT NULL,
    hero_media_id INT UNSIGNED NULL,
    hero_cta_label VARCHAR(255) NULL,
    hero_cta_url VARCHAR(255) NULL,
    hero_secondary_cta_label VARCHAR(255) NULL,
    hero_secondary_cta_url VARCHAR(255) NULL,
    hero_anchor VARCHAR(120) NULL,
    presentation LONGTEXT NULL,
    key_functions LONGTEXT NULL,
    use_cases LONGTEXT NULL,
    connectivity LONGTEXT NULL,
    hosting LONGTEXT NULL,
    support_body LONGTEXT NULL,
    status VARCHAR(32) NOT NULL DEFAULT 'draft',
    featured TINYINT(1) NOT NULL DEFAULT 0,
    meta_title VARCHAR(255) NULL,
    meta_description TEXT NULL,
    canonical_url VARCHAR(255) NULL,
    created_by INT UNSIGNED NULL,
    updated_by INT UNSIGNED NULL,
    published_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at DATETIME NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_products_internal_code (internal_code),
    UNIQUE KEY uq_products_slug (slug),
    KEY idx_products_sort_order (sort_order, name),
    KEY idx_products_hero_media_id (hero_media_id),
    CONSTRAINT fk_products_hero_media FOREIGN KEY (hero_media_id) REFERENCES media(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS module_types (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    code VARCHAR(64) NOT NULL,
    name VARCHAR(190) NOT NULL,
    slug VARCHAR(190) NOT NULL,
    description TEXT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_by INT UNSIGNED NULL,
    updated_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at DATETIME NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_module_types_code (code),
    UNIQUE KEY uq_module_types_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS categories (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    code VARCHAR(64) NULL,
    name VARCHAR(190) NOT NULL,
    slug VARCHAR(190) NOT NULL,
    description TEXT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_by INT UNSIGNED NULL,
    updated_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at DATETIME NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_categories_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS modules (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    internal_code VARCHAR(64) NOT NULL,
    product_id INT UNSIGNED NULL,
    module_type_id INT UNSIGNED NULL,
    sort_order INT NOT NULL DEFAULT 0,
    name VARCHAR(190) NOT NULL,
    slug VARCHAR(190) NOT NULL,
    hero_eyebrow VARCHAR(255) NULL,
    hero_icon VARCHAR(120) NULL,
    tagline VARCHAR(255) NULL,
    short_description TEXT NULL,
    hero_media_id INT UNSIGNED NULL,
    hero_secondary_cta_label VARCHAR(255) NULL,
    hero_secondary_cta_url VARCHAR(255) NULL,
    presentation LONGTEXT NULL,
    problem_statement LONGTEXT NULL,
    solution_body LONGTEXT NULL,
    process_body LONGTEXT NULL,
    cta_label VARCHAR(255) NULL,
    cta_url VARCHAR(255) NULL,
    status VARCHAR(32) NOT NULL DEFAULT 'draft',
    featured TINYINT(1) NOT NULL DEFAULT 0,
    meta_title VARCHAR(255) NULL,
    meta_description TEXT NULL,
    canonical_url VARCHAR(255) NULL,
    created_by INT UNSIGNED NULL,
    updated_by INT UNSIGNED NULL,
    published_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at DATETIME NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_modules_internal_code (internal_code),
    UNIQUE KEY uq_modules_slug (slug),
    KEY idx_modules_product_id (product_id),
    KEY idx_modules_module_type_id (module_type_id),
    KEY idx_modules_sort_order (sort_order, name),
    KEY idx_modules_hero_media_id (hero_media_id),
    CONSTRAINT fk_modules_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL,
    CONSTRAINT fk_modules_module_type FOREIGN KEY (module_type_id) REFERENCES module_types(id) ON DELETE SET NULL,
    CONSTRAINT fk_modules_hero_media FOREIGN KEY (hero_media_id) REFERENCES media(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS module_category (
    module_id INT UNSIGNED NOT NULL,
    category_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (module_id, category_id),
    KEY idx_module_category_category_id (category_id),
    CONSTRAINT fk_module_category_module FOREIGN KEY (module_id) REFERENCES modules(id) ON DELETE CASCADE,
    CONSTRAINT fk_module_category_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS module_relations (
    module_id INT UNSIGNED NOT NULL,
    related_module_id INT UNSIGNED NOT NULL,
    relation_type VARCHAR(64) NOT NULL DEFAULT 'complementary',
    PRIMARY KEY (module_id, related_module_id),
    KEY idx_module_relations_related_module_id (related_module_id),
    CONSTRAINT fk_module_relations_module FOREIGN KEY (module_id) REFERENCES modules(id) ON DELETE CASCADE,
    CONSTRAINT fk_module_relations_related_module FOREIGN KEY (related_module_id) REFERENCES modules(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS content_items (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    owner_type VARCHAR(32) NOT NULL,
    owner_id INT UNSIGNED NOT NULL,
    item_type VARCHAR(32) NOT NULL,
    title VARCHAR(255) NULL,
    body LONGTEXT NULL,
    icon VARCHAR(120) NULL,
    media_id INT UNSIGNED NULL,
    sort_order INT NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_content_items_owner (owner_type, owner_id, item_type),
    KEY idx_content_items_media_id (media_id),
    CONSTRAINT fk_content_items_media FOREIGN KEY (media_id) REFERENCES media(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pages (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    internal_code VARCHAR(64) NOT NULL,
    title VARCHAR(190) NOT NULL,
    slug VARCHAR(190) NOT NULL,
    page_type VARCHAR(64) NOT NULL DEFAULT 'page',
    excerpt TEXT NULL,
    body LONGTEXT NULL,
    hero_media_id INT UNSIGNED NULL,
    status VARCHAR(32) NOT NULL DEFAULT 'draft',
    featured TINYINT(1) NOT NULL DEFAULT 0,
    meta_title VARCHAR(255) NULL,
    meta_description TEXT NULL,
    canonical_url VARCHAR(255) NULL,
    created_by INT UNSIGNED NULL,
    updated_by INT UNSIGNED NULL,
    published_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at DATETIME NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_pages_internal_code (internal_code),
    UNIQUE KEY uq_pages_slug (slug),
    KEY idx_pages_hero_media_id (hero_media_id),
    CONSTRAINT fk_pages_hero_media FOREIGN KEY (hero_media_id) REFERENCES media(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS page_sections (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    page_id INT UNSIGNED NOT NULL,
    section_type VARCHAR(64) NOT NULL,
    title VARCHAR(255) NULL,
    eyebrow VARCHAR(255) NULL,
    body LONGTEXT NULL,
    settings_json LONGTEXT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_page_sections_page_id (page_id),
    CONSTRAINT fk_page_sections_page FOREIGN KEY (page_id) REFERENCES pages(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS page_formation_details (
    page_id INT UNSIGNED NOT NULL,
    objectives LONGTEXT NULL,
    target_skills LONGTEXT NULL,
    pedagogical_program LONGTEXT NULL,
    pedagogical_resources LONGTEXT NULL,
    pedagogical_methods LONGTEXT NULL,
    evaluation_methods LONGTEXT NULL,
    access_terms LONGTEXT NULL,
    instructors LONGTEXT NULL,
    equipment LONGTEXT NULL,
    training_contact LONGTEXT NULL,
    program_media_id INT UNSIGNED NULL,
    performance_media_id INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (page_id),
    KEY idx_page_formation_details_program_media_id (program_media_id),
    KEY idx_page_formation_details_performance_media_id (performance_media_id),
    CONSTRAINT fk_page_formation_details_page FOREIGN KEY (page_id) REFERENCES pages(id) ON DELETE CASCADE,
    CONSTRAINT fk_page_formation_details_program_media FOREIGN KEY (program_media_id) REFERENCES media(id) ON DELETE SET NULL,
    CONSTRAINT fk_page_formation_details_performance_media FOREIGN KEY (performance_media_id) REFERENCES media(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS entity_sections (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    owner_type VARCHAR(32) NOT NULL,
    owner_id INT UNSIGNED NOT NULL,
    section_type VARCHAR(64) NOT NULL,
    title VARCHAR(255) NULL,
    eyebrow VARCHAR(255) NULL,
    body LONGTEXT NULL,
    settings_json LONGTEXT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_entity_sections_owner (owner_type, owner_id, sort_order, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS entity_relations (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    source_type VARCHAR(32) NOT NULL,
    source_id INT UNSIGNED NOT NULL,
    target_type VARCHAR(32) NOT NULL,
    target_id INT UNSIGNED NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_entity_relations_pair (source_type, source_id, target_type, target_id),
    KEY idx_entity_relations_source (source_type, source_id, sort_order, id),
    KEY idx_entity_relations_target (target_type, target_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS posts (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    internal_code VARCHAR(64) NOT NULL,
    kind VARCHAR(32) NOT NULL DEFAULT 'news',
    title VARCHAR(190) NOT NULL,
    slug VARCHAR(190) NOT NULL,
    excerpt TEXT NULL,
    body LONGTEXT NULL,
    hero_media_id INT UNSIGNED NULL,
    status VARCHAR(32) NOT NULL DEFAULT 'draft',
    featured TINYINT(1) NOT NULL DEFAULT 0,
    meta_title VARCHAR(255) NULL,
    meta_description TEXT NULL,
    canonical_url VARCHAR(255) NULL,
    created_by INT UNSIGNED NULL,
    updated_by INT UNSIGNED NULL,
    published_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at DATETIME NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_posts_internal_code (internal_code),
    UNIQUE KEY uq_posts_kind_slug (kind, slug),
    KEY idx_posts_hero_media_id (hero_media_id),
    CONSTRAINT fk_posts_hero_media FOREIGN KEY (hero_media_id) REFERENCES media(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS menu_items (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    location VARCHAR(64) NOT NULL,
    parent_id INT UNSIGNED NULL,
    label VARCHAR(190) NOT NULL,
    link_type VARCHAR(32) NOT NULL DEFAULT 'custom',
    entity_type VARCHAR(32) NULL,
    entity_id INT UNSIGNED NULL,
    url VARCHAR(255) NULL,
    target VARCHAR(32) NOT NULL DEFAULT '_self',
    sort_order INT NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_menu_items_location (location, sort_order),
    KEY idx_menu_items_parent_id (parent_id),
    CONSTRAINT fk_menu_items_parent FOREIGN KEY (parent_id) REFERENCES menu_items(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS redirects (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    source_path VARCHAR(255) NOT NULL,
    target_url VARCHAR(255) NOT NULL,
    status_code SMALLINT UNSIGNED NOT NULL DEFAULT 301,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    note TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_redirects_source_path (source_path),
    KEY idx_redirects_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS audit_log (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id INT UNSIGNED NULL,
    action VARCHAR(120) NOT NULL,
    entity_type VARCHAR(64) NOT NULL,
    entity_id INT UNSIGNED NOT NULL DEFAULT 0,
    entity_label VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_audit_log_user_id (user_id),
    KEY idx_audit_log_created_at (created_at),
    CONSTRAINT fk_audit_log_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
