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 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 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 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 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;
