CREATE TABLE IF NOT EXISTS theme_profiles (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(120) NOT NULL,
    slug VARCHAR(140) NOT NULL,
    description VARCHAR(500) NULL,
    tokens_json LONGTEXT NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 0,
    created_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_theme_profiles_slug (slug),
    KEY idx_theme_profiles_active (is_active),
    CONSTRAINT fk_theme_profiles_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS theme_profile_versions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    theme_profile_id BIGINT UNSIGNED NOT NULL,
    version_number INT UNSIGNED NOT NULL,
    name VARCHAR(120) NOT NULL,
    tokens_json LONGTEXT NOT NULL,
    created_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_theme_profile_version (theme_profile_id, version_number),
    KEY idx_theme_profile_versions_profile (theme_profile_id, created_at),
    CONSTRAINT fk_theme_profile_versions_profile FOREIGN KEY (theme_profile_id) REFERENCES theme_profiles(id) ON DELETE CASCADE,
    CONSTRAINT fk_theme_profile_versions_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
