-- ============================================
-- TubeFind - Maps Leads Table (Server Cache)
-- Shared across all users
-- ============================================

CREATE TABLE IF NOT EXISTS map_leads (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    place_id VARCHAR(255) NOT NULL,
    search_query VARCHAR(500) NOT NULL,
    name VARCHAR(500) NOT NULL,
    phone VARCHAR(100),
    website VARCHAR(1000),
    address VARCHAR(1000),
    lat DECIMAL(10, 7),
    lng DECIMAL(10, 7),
    rating DECIMAL(3, 1),
    reviews_count INT DEFAULT 0,
    category VARCHAR(255),
    price_level VARCHAR(50),
    hours VARCHAR(500),
    description TEXT,
    has_website TINYINT(1) DEFAULT 0,
    has_phone TINYINT(1) DEFAULT 0,
    detail_url TEXT,
    email VARCHAR(255),
    facebook VARCHAR(500),
    instagram VARCHAR(500),
    twitter VARCHAR(500),
    linkedin VARCHAR(500),
    youtube VARCHAR(500),
    tiktok VARCHAR(500),
    source ENUM('web', 'api') DEFAULT 'web',
    user_id INT,
    cached_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    UNIQUE KEY uk_place_query (place_id(100), search_query(200)),
    INDEX idx_query (search_query(200)),
    INDEX idx_place (place_id(100)),
    INDEX idx_cached (cached_at),
    INDEX idx_email (email(100)),
    INDEX idx_category (category(100))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Search history (per user)
CREATE TABLE IF NOT EXISTS map_search_history (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    query VARCHAR(500) NOT NULL,
    result_count INT DEFAULT 0,
    source ENUM('web', 'api') DEFAULT 'web',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_user_query (user_id, query(200)),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Scraper config (dynamic selectors)
CREATE TABLE IF NOT EXISTS scraper_config (
    id INT AUTO_INCREMENT PRIMARY KEY,
    config_key VARCHAR(100) NOT NULL UNIQUE,
    config_value TEXT NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default scraper config
INSERT INTO scraper_config (config_key, config_value) VALUES
('version', '1'),
('selectors', '{"feed":"[role=\\"feed\\"]","article":"div[role=\\"article\\"]","name":".qBF1Pd","rating":".ZkP5Je","link":"a.hfpxzc","phone":"[data-item-id^=\\"phone\\"]","website":"[data-item-id=\\"authority\\"]","detail_name":"h1.DUwDvf","category":"button.DkEaL"}'),
('email_ignore_domains', '["sentry.io","wixpress.com","godaddy.com","domain.com","example.com","wordpress.org","w3.org","schema.org","googleapis.com","googletagmanager.com","cloudflare.com","jquery.com","bootstrapcdn.com"]')
ON DUPLICATE KEY UPDATE config_value = VALUES(config_value);
