-- These are the cache tables
-- Cache table for artist shows from Tunefind
CREATE TABLE IF NOT EXISTS cache_artist_shows (
    id INT AUTO_INCREMENT PRIMARY KEY,
    artist_name VARCHAR(255) NOT NULL,
    formatted_artist_name VARCHAR(255) NOT NULL,
    response_data LONGTEXT NOT NULL,
    server_used VARCHAR(255),
    success TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NOT NULL,
    INDEX idx_formatted_artist (formatted_artist_name),
    INDEX idx_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Cache table for show/movie supervisors
CREATE TABLE IF NOT EXISTS cache_show_supervisors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    appearance_link VARCHAR(500) NOT NULL UNIQUE,
    supervisors_data TEXT NOT NULL,
    server_used VARCHAR(255),
    supervisor_count INT DEFAULT 0,
    success TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NOT NULL,
    INDEX idx_appearance_link (appearance_link),
    INDEX idx_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Cache table for supervisor profiles
CREATE TABLE IF NOT EXISTS cache_supervisor_profiles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    supervisor_slug VARCHAR(255) NOT NULL UNIQUE,
    profile_data TEXT NOT NULL,
    server_used VARCHAR(255),
    success TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NOT NULL,
    INDEX idx_slug (supervisor_slug),
    INDEX idx_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Cache table for LinkedIn searches and contact info
CREATE TABLE IF NOT EXISTS cache_linkedin_contacts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    supervisor_name VARCHAR(255) NOT NULL,
    normalized_name VARCHAR(255) NOT NULL,
    linkedin_url VARCHAR(500),
    contact_data TEXT,
    google_results_count INT DEFAULT 0,
    ai_selected_index INT DEFAULT 0,
    has_email TINYINT(1) DEFAULT 0,
    has_phone TINYINT(1) DEFAULT 0,
    success TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NOT NULL,
    INDEX idx_normalized_name (normalized_name),
    INDEX idx_has_email (has_email),
    INDEX idx_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Stats table to track cache performance
CREATE TABLE IF NOT EXISTS cache_stats (
    id INT AUTO_INCREMENT PRIMARY KEY,
    endpoint VARCHAR(100) NOT NULL,
    cache_hits INT DEFAULT 0,
    cache_misses INT DEFAULT 0,
    total_requests INT DEFAULT 0,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY idx_endpoint (endpoint)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert initial stats rows
INSERT IGNORE INTO cache_stats (endpoint, cache_hits, cache_misses, total_requests) VALUES
('fetch_artist_shows', 0, 0, 0),
('fetch_show_supervisors', 0, 0, 0),
('fetch_supervisor_profile', 0, 0, 0),
('find_supervisor_linkedin', 0, 0, 0);