CREATE DATABASE IF NOT EXISTS traffx CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE traffx;

CREATE TABLE IF NOT EXISTS cache (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `domain` VARCHAR(500) NOT NULL,
    data LONGTEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_domain (`domain`(255)),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL DEFAULT '',
    email VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('user','admin') NOT NULL DEFAULT 'user',
    avatar VARCHAR(500) DEFAULT NULL,
    preferences JSON DEFAULT NULL,
    email_verified TINYINT UNSIGNED DEFAULT 0,
    verify_token VARCHAR(100) DEFAULT NULL,
    reset_token VARCHAR(100) DEFAULT NULL,
    reset_expires DATETIME DEFAULT NULL,
    last_login DATETIME DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_role (role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS saved_searches (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    domain VARCHAR(255) NOT NULL,
    competitors TEXT DEFAULT NULL,
    notes TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user (user_id),
    INDEX idx_domain (domain)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS search_history (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED DEFAULT NULL,
    domain VARCHAR(255) NOT NULL,
    competitors TEXT DEFAULT NULL,
    ip_address VARCHAR(45) DEFAULT NULL,
    user_agent TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_user (user_id),
    INDEX idx_domain (domain),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS site_settings (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `key` VARCHAR(100) NOT NULL UNIQUE,
    value LONGTEXT DEFAULT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_key (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS feedback (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED DEFAULT NULL,
    domain VARCHAR(255) NOT NULL,
    rating TINYINT UNSIGNED DEFAULT NULL,
    message TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS api_keys (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL DEFAULT '',
    `key` VARCHAR(64) NOT NULL UNIQUE,
    `permissions` VARCHAR(255) NOT NULL DEFAULT 'traffic,keywords,backlinks,tech',
    rate_limit INT UNSIGNED NOT NULL DEFAULT 60,
    active TINYINT UNSIGNED NOT NULL DEFAULT 1,
    last_used_at DATETIME DEFAULT NULL,
    expires_at DATETIME DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_key (`key`),
    INDEX idx_user (user_id),
    INDEX idx_active (active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS api_usage (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    api_key_id INT UNSIGNED DEFAULT NULL,
    endpoint VARCHAR(100) NOT NULL,
    ip_address VARCHAR(45) DEFAULT NULL,
    user_agent TEXT DEFAULT NULL,
    response_time_ms INT UNSIGNED DEFAULT 0,
    status_code SMALLINT UNSIGNED DEFAULT 200,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (api_key_id) REFERENCES api_keys(id) ON DELETE SET NULL,
    INDEX idx_key (api_key_id),
    INDEX idx_endpoint (endpoint),
    INDEX idx_created (created_at),
    INDEX idx_ip (ip_address)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS website_pages (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    domain VARCHAR(255) NOT NULL,
    url VARCHAR(2048) NOT NULL,
    title VARCHAR(512) DEFAULT NULL,
    meta_description TEXT DEFAULT NULL,
    status_code SMALLINT UNSIGNED DEFAULT 200,
    content_type VARCHAR(128) DEFAULT NULL,
    word_count INT UNSIGNED DEFAULT 0,
    crawl_depth TINYINT UNSIGNED DEFAULT 1,
    parent_url VARCHAR(2048) DEFAULT NULL,
    discovered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_domain (domain),
    INDEX idx_url (url(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS website_snapshots (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    domain VARCHAR(255) NOT NULL,
    og_image VARCHAR(512) DEFAULT NULL,
    favicon VARCHAR(512) DEFAULT NULL,
    page_title VARCHAR(512) DEFAULT NULL,
    meta_description TEXT DEFAULT NULL,
    h1_tags TEXT DEFAULT NULL,
    word_count INT UNSIGNED DEFAULT 0,
    link_count INT UNSIGNED DEFAULT 0,
    has_ssl TINYINT(1) DEFAULT 0,
    server_header VARCHAR(128) DEFAULT NULL,
    captured_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_domain (domain)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_analytics (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    ga_tracking_id VARCHAR(50) DEFAULT NULL,
    ga_measurement_id VARCHAR(100) DEFAULT NULL,
    ga_view_id VARCHAR(100) DEFAULT NULL,
    ga_property_name VARCHAR(255) DEFAULT NULL,
    ga_access_token TEXT DEFAULT NULL,
    ga_refresh_token TEXT DEFAULT NULL,
    token_expires_at DATETIME DEFAULT NULL,
    is_connected TINYINT(1) DEFAULT 0,
    connected_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user (user_id),
    INDEX idx_ga_tracking (ga_tracking_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS analytics_data (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    domain VARCHAR(255) NOT NULL,
    data_type ENUM('ga4','ga3','csv_import','manual') DEFAULT 'manual',
    metric_name VARCHAR(128) DEFAULT NULL,
    metric_value DECIMAL(20,2) DEFAULT 0.00,
    metric_date DATE DEFAULT NULL,
    dimension_name VARCHAR(128) DEFAULT NULL,
    dimension_value VARCHAR(512) DEFAULT NULL,
    import_batch_id VARCHAR(64) DEFAULT NULL,
    imported_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_domain (user_id, domain),
    INDEX idx_date (metric_date),
    INDEX idx_batch (import_batch_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS analytics_imports (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    domain VARCHAR(255) NOT NULL,
    source ENUM('ga4','ga3','csv_upload') DEFAULT 'csv_upload',
    record_count INT UNSIGNED DEFAULT 0,
    date_from DATE DEFAULT NULL,
    date_to DATE DEFAULT NULL,
    status ENUM('pending','processing','completed','failed') DEFAULT 'pending',
    error_message TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user (user_id),
    INDEX idx_domain (domain)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rankings (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    domain VARCHAR(255) NOT NULL,
    rank_type ENUM('traffic','engagement','seo','overall','kenya') DEFAULT 'overall',
    rank_position INT UNSIGNED DEFAULT 0,
    rank_score DECIMAL(10,2) DEFAULT 0.00,
    real_visits BIGINT UNSIGNED DEFAULT 0,
    data_source ENUM('real','modeled','mixed') DEFAULT 'modeled',
    computed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_domain_type (domain(200), rank_type),
    INDEX idx_rank_position (rank_position),
    INDEX idx_type_score (rank_type, rank_score)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO site_settings (`key`, `value`) VALUES
('site_name', 'TraffX'),
('site_tagline', 'Check Website Traffic in Kenya & Globally - Free Traffic Checker'),
('site_description', 'Check website traffic in Kenya and worldwide for free. TraffX is the best free website traffic checker for competitive analytics, SEO keywords, referral sources, and audience insights.'),
('site_keywords', 'check website traffic in Kenya, website traffic checker, free traffic analytics, competitor analysis Kenya, SEO Kenya, domain traffic check, free website analysis'),
('admin_email', 'admin@traffx.dev'),
('analytics_id', ''),
('maintenance_mode', '0'),
('cache_ttl', '21600'),
('max_competitors', '3'),
('results_per_page', '10');

INSERT INTO users (name, email, password, role) VALUES
('Admin', 'admin@traffx.dev', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin');