-- ACTIVE MIGRATION
-- This file is part of the master replay chain.
-- Safe to include in controlled fresh environment replay.

CREATE TABLE IF NOT EXISTS site_template_categories (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category_key VARCHAR(120) NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT NULL,
    template_type VARCHAR(50) NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_site_template_categories_category_key (category_key),
    KEY idx_site_template_categories_template_type (template_type),
    KEY idx_site_template_categories_sort_order (sort_order),
    KEY idx_site_template_categories_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS site_layout_templates (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category_id BIGINT UNSIGNED NULL,
    template_key VARCHAR(150) NOT NULL,
    template_type VARCHAR(50) NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT NULL,
    preview_image VARCHAR(500) NULL,
    layout_json LONGTEXT NULL,
    schema_json LONGTEXT NULL,
    token_json LONGTEXT NULL,
    behavior_json LONGTEXT NULL,
    version VARCHAR(50) NOT NULL DEFAULT '1.0.0',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    status VARCHAR(20) NOT NULL DEFAULT 'active',
    is_system TINYINT(1) NOT NULL DEFAULT 1,
    sort_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_site_layout_templates_template_key (template_key),
    KEY idx_site_layout_templates_template_type (template_type),
    KEY idx_site_layout_templates_category_id (category_id),
    KEY idx_site_layout_templates_is_active (is_active),
    KEY idx_site_layout_templates_status (status),
    KEY idx_site_layout_templates_sort_order (sort_order),
    CONSTRAINT fk_site_layout_templates_category
        FOREIGN KEY (category_id) REFERENCES site_template_categories (id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS site_template_previews (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    template_id BIGINT UNSIGNED NOT NULL,
    device_type VARCHAR(30) NOT NULL,
    preview_image VARCHAR(500) NOT NULL,
    preview_label VARCHAR(255) NULL,
    sort_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_site_template_previews_template_id (template_id),
    KEY idx_site_template_previews_device_type (device_type),
    KEY idx_site_template_previews_sort_order (sort_order),
    CONSTRAINT fk_site_template_previews_template
        FOREIGN KEY (template_id) REFERENCES site_layout_templates (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS site_template_versions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    template_id BIGINT UNSIGNED NOT NULL,
    version VARCHAR(50) NOT NULL,
    layout_json LONGTEXT NULL,
    schema_json LONGTEXT NULL,
    token_json LONGTEXT NULL,
    behavior_json LONGTEXT NULL,
    changelog TEXT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_site_template_versions_template_id (template_id),
    KEY idx_site_template_versions_version (version),
    CONSTRAINT fk_site_template_versions_template
        FOREIGN KEY (template_id) REFERENCES site_layout_templates (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Example category ideas for later manual seeding:
-- Kurumsal
-- Esnaf
-- Servis
-- E-Ticaret
-- Minimal
-- Portal
-- Katalog
-- Blog