CREATE TABLE IF NOT EXISTS users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    email VARCHAR(190) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin', 'editor') NOT NULL DEFAULT 'admin',
    last_login_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS broker_categories (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title_tr VARCHAR(160) NOT NULL,
    title_en VARCHAR(160) NULL,
    slug VARCHAR(160) NOT NULL UNIQUE,
    description_tr TEXT NULL,
    description_en TEXT NULL,
    sort_order TINYINT UNSIGNED NOT NULL DEFAULT 0,
    is_featured TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS brokers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(160) NOT NULL,
    slug VARCHAR(160) NOT NULL UNIQUE,
    tagline_tr VARCHAR(255) NULL,
    tagline_en VARCHAR(255) NULL,
    summary_tr TEXT NULL,
    summary_en TEXT NULL,
    content_tr MEDIUMTEXT NULL,
    content_en MEDIUMTEXT NULL,
    meta_description_tr VARCHAR(255) NULL,
    meta_description_en VARCHAR(255) NULL,
    rating DECIMAL(2,1) NOT NULL DEFAULT 4.0,
    leverage INT UNSIGNED NOT NULL DEFAULT 500,
    min_deposit DECIMAL(10,2) NOT NULL DEFAULT 100.00,
    registered_users VARCHAR(120) NULL,
    bonus_text_tr VARCHAR(255) NULL,
    bonus_text_en VARCHAR(255) NULL,
    spread_text VARCHAR(120) NULL,
    has_turkish_support TINYINT(1) NOT NULL DEFAULT 1,
    offers_islamic_account TINYINT(1) NOT NULL DEFAULT 0,
    regulation_summary VARCHAR(255) NULL,
    founded_year VARCHAR(20) NULL,
    headquarters VARCHAR(160) NULL,
    support_channels VARCHAR(160) NULL,
    funding_methods_tr VARCHAR(255) NULL,
    funding_methods_en VARCHAR(255) NULL,
    pros_tr JSON NULL,
    pros_en JSON NULL,
    cons_tr JSON NULL,
    cons_en JSON NULL,
    platforms JSON NULL,
    account_types JSON NULL,
    hero_image VARCHAR(255) NULL,
    cover_image VARCHAR(255) NULL,
    video_url VARCHAR(255) NULL,
    cta_account_url VARCHAR(255) NULL,
    cta_review_url VARCHAR(255) NULL,
    cta_demo_url VARCHAR(255) NULL,
    featured_order TINYINT UNSIGNED NOT NULL DEFAULT 10,
    is_featured TINYINT(1) NOT NULL DEFAULT 0,
    is_published TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS broker_category_map (
    broker_id INT UNSIGNED NOT NULL,
    category_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (broker_id, category_id),
    CONSTRAINT fk_bcm_broker FOREIGN KEY (broker_id) REFERENCES brokers(id) ON DELETE CASCADE,
    CONSTRAINT fk_bcm_category FOREIGN KEY (category_id) REFERENCES broker_categories(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS articles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title_tr VARCHAR(190) NOT NULL,
    title_en VARCHAR(190) NULL,
    slug VARCHAR(190) NOT NULL UNIQUE,
    excerpt_tr TEXT NULL,
    excerpt_en TEXT NULL,
    content_tr MEDIUMTEXT NULL,
    content_en MEDIUMTEXT NULL,
    cover_image VARCHAR(255) NULL,
    author VARCHAR(120) NULL,
    is_published TINYINT(1) NOT NULL DEFAULT 0,
    published_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS glossary_terms (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    term_tr VARCHAR(160) NOT NULL,
    term_en VARCHAR(160) NULL,
    slug VARCHAR(160) NOT NULL UNIQUE,
    excerpt_tr TEXT NULL,
    excerpt_en TEXT NULL,
    content_tr MEDIUMTEXT NULL,
    content_en MEDIUMTEXT NULL,
    bullets_tr JSON NULL,
    bullets_en JSON NULL,
    is_visible TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS site_settings (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `group` VARCHAR(80) NOT NULL DEFAULT 'general',
    `key` VARCHAR(160) NOT NULL UNIQUE,
    value VARCHAR(255) NULL,
    value_tr TEXT NULL,
    value_en TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS media_library (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    file_name VARCHAR(190) NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    mime_type VARCHAR(80) NOT NULL,
    file_size INT UNSIGNED NOT NULL,
    alt_text VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by INT UNSIGNED NULL,
    CONSTRAINT fk_media_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
