-- ============================================
-- TubeFind - Database Setup
-- YouTube Channel Finder for Ad Placement
-- ============================================

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

-- ─── USERS ───
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    plan ENUM('free','pro','agency') DEFAULT 'free',
    max_devices TINYINT DEFAULT 1,
    max_searches_per_day INT DEFAULT 3,
    is_active TINYINT(1) DEFAULT 1,
    auth_token VARCHAR(255) DEFAULT NULL,
    token_expires_at DATETIME DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_token (auth_token)
) ENGINE=InnoDB;

-- ─── DEVICES ───
CREATE TABLE IF NOT EXISTS devices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    device_id VARCHAR(255) NOT NULL,
    device_name VARCHAR(100) DEFAULT NULL,
    platform ENUM('windows','macos','ios','android') DEFAULT NULL,
    last_active_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_user_device (user_id, device_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user (user_id)
) ENGINE=InnoDB;

-- ─── API KEYS (YouTube Data API keys - user's own + pool) ───
CREATE TABLE IF NOT EXISTS api_keys (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT DEFAULT NULL,
    api_key VARCHAR(255) NOT NULL,
    label VARCHAR(100) DEFAULT NULL,
    is_pool_key TINYINT(1) DEFAULT 0,
    daily_quota INT DEFAULT 10000,
    used_today INT DEFAULT 0,
    quota_reset_at DATETIME DEFAULT NULL,
    is_active TINYINT(1) DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user (user_id),
    INDEX idx_pool (is_pool_key, is_active)
) ENGINE=InnoDB;

-- ─── SEARCH KEYWORDS (cached searches) ───
CREATE TABLE IF NOT EXISTS search_keywords (
    id INT AUTO_INCREMENT PRIMARY KEY,
    keyword VARCHAR(255) NOT NULL,
    region_code VARCHAR(5) DEFAULT 'US',
    min_subscribers INT DEFAULT 0,
    total_results INT DEFAULT 0,
    search_count INT DEFAULT 1,
    last_searched_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    data_updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_keyword_region (keyword, region_code, min_subscribers),
    INDEX idx_keyword (keyword),
    INDEX idx_last_searched (last_searched_at)
) ENGINE=InnoDB;

-- ─── CHANNELS (cached channel data) ───
CREATE TABLE IF NOT EXISTS channels (
    id INT AUTO_INCREMENT PRIMARY KEY,
    channel_id VARCHAR(50) NOT NULL UNIQUE,
    title VARCHAR(255) DEFAULT NULL,
    custom_url VARCHAR(100) DEFAULT NULL,
    description TEXT DEFAULT NULL,
    country VARCHAR(5) DEFAULT NULL,
    subscriber_count BIGINT DEFAULT 0,
    view_count BIGINT DEFAULT 0,
    video_count INT DEFAULT 0,
    topic_categories TEXT DEFAULT NULL,
    channel_keywords TEXT DEFAULT NULL,
    thumbnail_url VARCHAR(500) DEFAULT NULL,
    published_at DATETIME DEFAULT NULL,
    made_for_kids TINYINT(1) DEFAULT 0,
    data_updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_channel_id (channel_id),
    INDEX idx_country (country),
    INDEX idx_subscribers (subscriber_count),
    INDEX idx_updated (data_updated_at)
) ENGINE=InnoDB;

-- ─── KEYWORD-CHANNEL MAPPING ───
CREATE TABLE IF NOT EXISTS keyword_channels (
    id INT AUTO_INCREMENT PRIMARY KEY,
    keyword_id INT NOT NULL,
    channel_id INT NOT NULL,
    relevance_rank INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_kw_ch (keyword_id, channel_id),
    FOREIGN KEY (keyword_id) REFERENCES search_keywords(id) ON DELETE CASCADE,
    FOREIGN KEY (channel_id) REFERENCES channels(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ─── USER SAVED LISTS ───
CREATE TABLE IF NOT EXISTS saved_lists (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT DEFAULT NULL,
    channel_count INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user (user_id)
) ENGINE=InnoDB;

-- ─── LIST CHANNELS ───
CREATE TABLE IF NOT EXISTS list_channels (
    id INT AUTO_INCREMENT PRIMARY KEY,
    list_id INT NOT NULL,
    channel_db_id INT NOT NULL,
    added_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_list_ch (list_id, channel_db_id),
    FOREIGN KEY (list_id) REFERENCES saved_lists(id) ON DELETE CASCADE,
    FOREIGN KEY (channel_db_id) REFERENCES channels(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ─── SEARCH LOG (for analytics + rate limiting) ───
CREATE TABLE IF NOT EXISTS search_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    keyword VARCHAR(255) NOT NULL,
    region_code VARCHAR(5) DEFAULT 'US',
    results_count INT DEFAULT 0,
    source ENUM('cache','api','mixed') DEFAULT 'cache',
    searched_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_date (user_id, searched_at)
) ENGINE=InnoDB;

-- ─── INSERT DEFAULT ADMIN USER (password: admin123) ───
-- Password hash for 'admin123' using PHP password_hash
INSERT INTO users (name, email, password, plan, max_devices, max_searches_per_day) 
VALUES ('Admin', 'admin@tubefind.app', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'agency', 5, 999)
ON DUPLICATE KEY UPDATE name=name;

-- ─── PLAN LIMITS REFERENCE ───
-- free:   max_devices=1, max_searches_per_day=3,  export=no,  lists=1
-- pro:    max_devices=2, max_searches_per_day=50, export=yes, lists=10
-- agency: max_devices=5, max_searches_per_day=999, export=yes, lists=unlimited
