-- VideoDownloader Database Schema
-- This is optional - only needed if you want to track downloads, users, or statistics

-- Create database
CREATE DATABASE IF NOT EXISTS videodownloader CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE videodownloader;

-- Downloads Log Table
CREATE TABLE IF NOT EXISTS downloads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    platform VARCHAR(50) NOT NULL,
    video_url VARCHAR(500) NOT NULL,
    video_title VARCHAR(255),
    quality VARCHAR(50),
    ip_address VARCHAR(45),
    user_agent VARCHAR(255),
    downloaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_platform (platform),
    INDEX idx_downloaded_at (downloaded_at),
    INDEX idx_ip (ip_address)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Contact Messages Table
CREATE TABLE IF NOT EXISTS contact_messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    subject VARCHAR(200) NOT NULL,
    message TEXT NOT NULL,
    ip_address VARCHAR(45),
    status ENUM('new', 'read', 'replied', 'archived') DEFAULT 'new',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Rate Limiting Table
CREATE TABLE IF NOT EXISTS rate_limits (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ip_address VARCHAR(45) NOT NULL UNIQUE,
    request_count INT DEFAULT 1,
    last_request TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    blocked_until TIMESTAMP NULL,
    INDEX idx_ip (ip_address),
    INDEX idx_blocked (blocked_until)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Statistics Table (Daily aggregates)
CREATE TABLE IF NOT EXISTS daily_stats (
    id INT AUTO_INCREMENT PRIMARY KEY,
    date DATE NOT NULL UNIQUE,
    total_downloads INT DEFAULT 0,
    facebook_downloads INT DEFAULT 0,
    youtube_downloads INT DEFAULT 0,
    instagram_downloads INT DEFAULT 0,
    unique_visitors INT DEFAULT 0,
    page_views INT DEFAULT 0,
    INDEX idx_date (date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Cached Videos Table (Optional - for caching video info)
CREATE TABLE IF NOT EXISTS video_cache (
    id INT AUTO_INCREMENT PRIMARY KEY,
    platform VARCHAR(50) NOT NULL,
    video_url VARCHAR(500) NOT NULL UNIQUE,
    video_data JSON,
    cached_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP,
    INDEX idx_platform (platform),
    INDEX idx_url (video_url),
    INDEX idx_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Sample queries for analytics

-- Get download statistics for last 7 days
-- SELECT 
--     DATE(downloaded_at) as date,
--     COUNT(*) as total_downloads,
--     SUM(CASE WHEN platform = 'facebook' THEN 1 ELSE 0 END) as facebook,
--     SUM(CASE WHEN platform = 'youtube' THEN 1 ELSE 0 END) as youtube,
--     SUM(CASE WHEN platform = 'instagram' THEN 1 ELSE 0 END) as instagram
-- FROM downloads
-- WHERE downloaded_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
-- GROUP BY DATE(downloaded_at)
-- ORDER BY date DESC;

-- Get most popular videos
-- SELECT 
--     video_title,
--     platform,
--     COUNT(*) as download_count
-- FROM downloads
-- WHERE video_title IS NOT NULL
-- GROUP BY video_title, platform
-- ORDER BY download_count DESC
-- LIMIT 10;

-- Get hourly download pattern
-- SELECT 
--     HOUR(downloaded_at) as hour,
--     COUNT(*) as downloads
-- FROM downloads
-- WHERE downloaded_at >= DATE_SUB(NOW(), INTERVAL 1 DAY)
-- GROUP BY HOUR(downloaded_at)
-- ORDER BY hour;

-- Clean old cache entries (run periodically)
-- DELETE FROM video_cache WHERE expires_at < NOW();

-- Clean old rate limit entries (run periodically)
-- DELETE FROM rate_limits WHERE last_request < DATE_SUB(NOW(), INTERVAL 1 DAY);
