-- Create the database
CREATE DATABASE IF NOT EXISTS telegram_bot
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE telegram_bot;

-- Create users table
CREATE TABLE IF NOT EXISTS users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL UNIQUE,
    username VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create media channels table
CREATE TABLE IF NOT EXISTS media_channels (
    id INT PRIMARY KEY AUTO_INCREMENT,
    channel_id BIGINT NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_channel_id (channel_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create uploaded files table
CREATE TABLE IF NOT EXISTS uploaded_files (
    id INT PRIMARY KEY AUTO_INCREMENT,
    file_id VARCHAR(255) NOT NULL,
    message_id INT NOT NULL,
    channel_id BIGINT NOT NULL,
    uploader_id BIGINT NOT NULL,
    download_code VARCHAR(5) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (channel_id) REFERENCES media_channels(channel_id) ON DELETE CASCADE,
    FOREIGN KEY (uploader_id) REFERENCES users(user_id) ON DELETE CASCADE,
    INDEX idx_download_code (download_code),
    INDEX idx_uploader (uploader_id),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create trigger to automatically delete old files (optional)
DELIMITER //
CREATE TRIGGER IF NOT EXISTS cleanup_old_files
    BEFORE INSERT ON uploaded_files
    FOR EACH ROW
BEGIN
    -- Delete files older than 30 days
    DELETE FROM uploaded_files 
    WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);
END;//
DELIMITER ;

-- Create views for statistics
CREATE OR REPLACE VIEW vw_bot_statistics AS
SELECT 
    (SELECT COUNT(*) FROM users) as total_users,
    (SELECT COUNT(*) FROM uploaded_files) as total_files,
    (SELECT COUNT(*) FROM media_channels) as total_channels;

-- Create view for user activity
CREATE OR REPLACE VIEW vw_user_activity AS
SELECT 
    u.user_id,
    u.username,
    COUNT(f.id) as total_uploads,
    MAX(f.created_at) as last_upload
FROM users u
LEFT JOIN uploaded_files f ON u.user_id = f.uploader_id
GROUP BY u.user_id, u.username;

-- Create view for channel statistics
CREATE OR REPLACE VIEW vw_channel_statistics AS
SELECT 
    mc.channel_id,
    COUNT(f.id) as total_files,
    COUNT(DISTINCT f.uploader_id) as unique_uploaders,
    MAX(f.created_at) as last_upload
FROM media_channels mc
LEFT JOIN uploaded_files f ON mc.channel_id = f.channel_id
GROUP BY mc.channel_id; 