<?php
// inc/functions.php - Complete Helper Functions File

require_once __DIR__ . '/db.php';

/**
 * Log user activity
 */
function log_activity($user_id, $action, $details = '') {
    $sql = "INSERT INTO activity_logs (user_id, action, details, created_at) VALUES (?, ?, ?, NOW())";
    db_query($sql, [$user_id, $action, $details]);
}

/**
 * Get user info, create if not exists
 */
function get_or_create_user($user_id, $username, $first_name, $last_name = '') {
    $sql = "SELECT * FROM users WHERE user_id = ?";
    $user = db_query($sql, [$user_id])->fetch();
    
    if (!$user) {
        $sql = "INSERT INTO users (user_id, username, first_name, last_name, created_at) VALUES (?, ?, ?, ?, NOW())";
        db_query($sql, [$user_id, $username, $first_name, $last_name]);
        
        $sql = "SELECT * FROM users WHERE user_id = ?";
        $user = db_query($sql, [$user_id])->fetch();
        
        log_activity($user_id, 'user_created', "Username: $username");
    }
    
    return $user;
}

/**
 * Get all files
 */
function get_all_files() {
    $sql = "SELECT * FROM files ORDER BY created_at DESC";
    return db_query($sql)->fetchAll();
}

/**
 * Get file by ID
 */
function get_file_by_id($file_id) {
    $sql = "SELECT * FROM files WHERE id = ?";
    return db_query($sql, [$file_id])->fetch();
}

/**
 * Get file by public ID
 */
function get_file_by_public_id($public_id) {
    $sql = "SELECT * FROM files WHERE public_id = ?";
    return db_query($sql, [$public_id])->fetch();
}

/**
 * Check if user has joined required channels
 */
function check_channel_membership($user_id, $required_channels) {
    if (empty($required_channels)) return true;
    
    $channels = explode(',', $required_channels);
    $not_joined = [];
    
    foreach ($channels as $channel) {
        $channel = trim($channel);
        if (!empty($channel) && !is_telegram_member($user_id, $channel)) {
            $not_joined[] = $channel;
        }
    }
    
    return empty($not_joined) ? true : $not_joined;
}

/**
 * Create unlock request
 */
function create_unlock_request($user_id, $file_id) {
    // Check if request already exists
    $sql = "SELECT id FROM unlock_requests WHERE user_id = ? AND file_id = ? AND status = 'pending'";
    $existing = db_query($sql, [$user_id, $file_id])->fetch();
    
    if ($existing) {
        return $existing['id'];
    }
    
    $sql = "INSERT INTO unlock_requests (user_id, file_id, status, created_at) VALUES (?, ?, 'pending', NOW())";
    db_query($sql, [$user_id, $file_id]);
    
    global $db;
    return $db->getConnection()->lastInsertId();
}

/**
 * Get pending unlock requests
 */
function get_pending_requests() {
    $sql = "SELECT ur.*, u.username, u.first_name, u.last_name, f.title, f.public_id 
            FROM unlock_requests ur 
            JOIN users u ON ur.user_id = u.user_id 
            JOIN files f ON ur.file_id = f.id 
            WHERE ur.status = 'pending' 
            ORDER BY ur.created_at DESC";
    return db_query($sql)->fetchAll();
}

/**
 * Update unlock request status
 */
function update_unlock_request($request_id, $status) {
    $sql = "UPDATE unlock_requests SET status = ?, updated_at = NOW() WHERE id = ?";
    db_query($sql, [$status, $request_id]);
    
    return true;
}

/**
 * Save uploaded file info to database
 */
function save_file_info($title, $filename, $public_id, $required_channels, $is_paid, $price_text) {
    $sql = "INSERT INTO files (title, filename, public_id, required_channels, is_paid, price_text, created_at) 
            VALUES (?, ?, ?, ?, ?, ?, NOW())";
    db_query($sql, [$title, $filename, $public_id, $required_channels, $is_paid ? 1 : 0, $price_text]);
    
    global $db;
    return $db->getConnection()->lastInsertId();
}

/**
 * Delete file from database and filesystem
 */
function delete_file_by_id($file_id) {
    $file = get_file_by_id($file_id);
    if ($file) {
        // Delete physical file
        $file_path = __DIR__ . '/../uploads/' . $file['filename'];
        if (file_exists($file_path)) {
            unlink($file_path);
        }
        
        // Delete unlock requests for this file
        $sql = "DELETE FROM unlock_requests WHERE file_id = ?";
        db_query($sql, [$file_id]);
        
        // Delete from database
        $sql = "DELETE FROM files WHERE id = ?";
        db_query($sql, [$file_id]);
        
        return true;
    }
    return false;
}

/**
 * Get all users
 */
function get_all_users() {
    $sql = "SELECT * FROM users ORDER BY created_at DESC";
    return db_query($sql)->fetchAll();
}

/**
 * Get statistics for dashboard
 */
function get_dashboard_stats() {
    $stats = [];
    
    $stats['files_count'] = db_query("SELECT COUNT(*) as count FROM files")->fetch()['count'];
    $stats['users_count'] = db_query("SELECT COUNT(*) as count FROM users")->fetch()['count'];
    $stats['pending_requests'] = db_query("SELECT COUNT(*) as count FROM unlock_requests WHERE status = 'pending'")->fetch()['count'];
    $stats['approved_requests'] = db_query("SELECT COUNT(*) as count FROM unlock_requests WHERE status = 'approved'")->fetch()['count'];
    
    return $stats;
}

/**
 * Get broadcast statistics
 */
function get_broadcast_stats() {
    $stats = [];
    
    // Total broadcasts
    $result = db_query("SELECT COUNT(*) as count FROM broadcast_logs")->fetch();
    $stats['total_broadcasts'] = $result['count'];
    
    // Total messages sent
    $result = db_query("SELECT SUM(success_count) as total FROM broadcast_logs")->fetch();
    $stats['total_messages_sent'] = $result['total'] ?? 0;
    
    // Last broadcast
    $result = db_query("SELECT created_at FROM broadcast_logs ORDER BY id DESC LIMIT 1")->fetch();
    $stats['last_broadcast'] = $result;
    
    return $stats;
}

/**
 * Send message to specific user
 */
function send_user_message($user_id, $message, $keyboard = null) {
    return send_message($user_id, $message, $keyboard);
}

/**
 * Log broadcast message for individual user
 */
function log_broadcast_message($user_id, $subject, $status, $error = '') {
    $sql = "INSERT INTO user_messages (user_id, subject, status, error, created_at) VALUES (?, ?, ?, ?, NOW())";
    db_query($sql, [$user_id, $subject, $status, $error]);
}

/**
 * Save broadcast log
 */
function save_broadcast_log($subject, $message, $total_users, $success_count, $failed_count) {
    $sql = "INSERT INTO broadcast_logs (subject, message, total_users, success_count, failed_count, created_at) 
            VALUES (?, ?, ?, ?, ?, NOW())";
    db_query($sql, [$subject, $message, $total_users, $success_count, $failed_count]);
}

/**
 * Get recent activity logs
 */
function get_recent_activity($limit = 50) {
    $sql = "SELECT al.*, u.username, u.first_name 
            FROM activity_logs al 
            LEFT JOIN users u ON al.user_id = u.user_id 
            ORDER BY al.created_at DESC 
            LIMIT ?";
    return db_query($sql, [$limit])->fetchAll();
}

/**
 * Get user by ID
 */
function get_user_by_id($user_id) {
    $sql = "SELECT * FROM users WHERE user_id = ?";
    return db_query($sql, [$user_id])->fetch();
}

/**
 * Get files count by type
 */
function get_files_count_by_type() {
    $sql = "SELECT 
            SUM(is_paid = 0) as free_files,
            SUM(is_paid = 1) as paid_files 
            FROM files";
    return db_query($sql)->fetch();
}

/**
 * Get daily stats for charts
 */
function get_daily_stats($days = 7) {
    $sql = "SELECT 
            DATE(created_at) as date,
            COUNT(*) as users,
            (SELECT COUNT(*) FROM files WHERE DATE(created_at) = date) as files,
            (SELECT COUNT(*) FROM unlock_requests WHERE DATE(created_at) = date) as requests
            FROM users 
            WHERE created_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
            GROUP BY DATE(created_at)
            ORDER BY date DESC";
    return db_query($sql, [$days])->fetchAll();
}

/**
 * Search users by name or username
 */
function search_users($query) {
    $sql = "SELECT * FROM users 
            WHERE first_name LIKE ? OR last_name LIKE ? OR username LIKE ? 
            ORDER BY created_at DESC";
    $search_term = "%$query%";
    return db_query($sql, [$search_term, $search_term, $search_term])->fetchAll();
}

/**
 * Get user unlock history
 */
function get_user_unlock_history($user_id) {
    $sql = "SELECT ur.*, f.title, f.public_id 
            FROM unlock_requests ur 
            JOIN files f ON ur.file_id = f.id 
            WHERE ur.user_id = ? 
            ORDER BY ur.created_at DESC";
    return db_query($sql, [$user_id])->fetchAll();
}

/**
 * Check if public ID already exists
 */
function public_id_exists($public_id) {
    $sql = "SELECT id FROM files WHERE public_id = ?";
    $result = db_query($sql, [$public_id])->fetch();
    return $result ? true : false;
}

/**
 * Get system statistics
 */
function get_system_stats() {
    $stats = [];
    
    // Basic counts
    $stats['total_users'] = db_query("SELECT COUNT(*) as count FROM users")->fetch()['count'];
    $stats['total_files'] = db_query("SELECT COUNT(*) as count FROM files")->fetch()['count'];
    $stats['total_requests'] = db_query("SELECT COUNT(*) as count FROM unlock_requests")->fetch()['count'];
    
    // Today's activity
    $stats['today_users'] = db_query("SELECT COUNT(*) as count FROM users WHERE DATE(created_at) = CURDATE()")->fetch()['count'];
    $stats['today_requests'] = db_query("SELECT COUNT(*) as count FROM unlock_requests WHERE DATE(created_at) = CURDATE()")->fetch()['count'];
    
    // File type distribution
    $file_types = db_query("SELECT is_paid, COUNT(*) as count FROM files GROUP BY is_paid")->fetchAll();
    $stats['free_files'] = 0;
    $stats['paid_files'] = 0;
    foreach ($file_types as $type) {
        if ($type['is_paid']) {
            $stats['paid_files'] = $type['count'];
        } else {
            $stats['free_files'] = $type['count'];
        }
    }
    
    // Request status distribution
    $request_status = db_query("SELECT status, COUNT(*) as count FROM unlock_requests GROUP BY status")->fetchAll();
    $stats['pending_requests'] = 0;
    $stats['approved_requests'] = 0;
    $stats['rejected_requests'] = 0;
    foreach ($request_status as $status) {
        if ($status['status'] == 'pending') {
            $stats['pending_requests'] = $status['count'];
        } elseif ($status['status'] == 'approved') {
            $stats['approved_requests'] = $status['count'];
        } elseif ($status['status'] == 'rejected') {
            $stats['rejected_requests'] = $status['count'];
        }
    }
    
    return $stats;
}

/**
 * Clean up old activity logs (keep only last 1000 records)
 */
function cleanup_old_logs() {
    $sql = "DELETE FROM activity_logs 
            WHERE id NOT IN (
                SELECT id FROM (
                    SELECT id FROM activity_logs 
                    ORDER BY created_at DESC 
                    LIMIT 1000
                ) as temp
            )";
    db_query($sql);
    return true;
}

/**
 * Get top requested files
 */
function get_top_requested_files($limit = 10) {
    $sql = "SELECT f.*, COUNT(ur.id) as request_count 
            FROM files f 
            LEFT JOIN unlock_requests ur ON f.id = ur.file_id 
            GROUP BY f.id 
            ORDER BY request_count DESC 
            LIMIT ?";
    return db_query($sql, [$limit])->fetchAll();
}

/**
 * Get user engagement stats
 */
function get_user_engagement_stats() {
    $sql = "SELECT 
            COUNT(*) as total_users,
            COUNT(DISTINCT user_id) as active_users,
            (SELECT COUNT(*) FROM activity_logs WHERE action = 'file_sent_free') as free_downloads,
            (SELECT COUNT(*) FROM activity_logs WHERE action = 'unlock_request_created') as paid_requests
            FROM users";
    return db_query($sql)->fetch();
}

/**
 * Backup database tables (structure only)
 */
function backup_database_tables() {
    $tables = ['users', 'files', 'unlock_requests', 'activity_logs', 'broadcast_logs', 'user_messages'];
    $backup_sql = "";
    
    foreach ($tables as $table) {
        $sql = "SHOW CREATE TABLE $table";
        $result = db_query($sql)->fetch();
        $backup_sql .= $result['Create Table'] . ";\n\n";
    }
    
    return $backup_sql;
}

/**
 * Validate file type
 */
function validate_file_type($filename) {
    $allowed_types = ALLOWED_TYPES;
    $extension = strtolower(pathinfo($filename, PATHINFO_EXTENSION));
    return in_array($extension, $allowed_types);
}

/**
 * Validate file size
 */
function validate_file_size($file_size) {
    return $file_size <= MAX_FILE_SIZE;
}

/**
 * Generate unique filename
 */
function generate_unique_filename($original_name) {
    $extension = pathinfo($original_name, PATHINFO_EXTENSION);
    $unique_id = uniqid();
    $safe_name = preg_replace('/[^a-zA-Z0-9._-]/', '_', pathinfo($original_name, PATHINFO_FILENAME));
    return $unique_id . '_' . $safe_name . '.' . $extension;
}

/**
 * Get file download count
 */
function get_file_download_count($file_id) {
    $sql = "SELECT COUNT(*) as count FROM unlock_requests WHERE file_id = ? AND status = 'approved'";
    $result = db_query($sql, [$file_id])->fetch();
    return $result['count'];
}

/**
 * Get user download history
 */
function get_user_download_history($user_id) {
    $sql = "SELECT f.title, f.public_id, ur.created_at 
            FROM unlock_requests ur 
            JOIN files f ON ur.file_id = f.id 
            WHERE ur.user_id = ? AND ur.status = 'approved' 
            ORDER BY ur.created_at DESC";
    return db_query($sql, [$user_id])->fetchAll();
}

/**
 * Check if user has access to file
 */
function user_has_file_access($user_id, $file_id) {
    $sql = "SELECT id FROM unlock_requests WHERE user_id = ? AND file_id = ? AND status = 'approved'";
    $result = db_query($sql, [$user_id, $file_id])->fetch();
    return $result ? true : false;
}

/**
 * Get admin notifications (pending requests, etc.)
 */
function get_admin_notifications() {
    $notifications = [];
    
    // Pending unlock requests
    $pending_count = db_query("SELECT COUNT(*) as count FROM unlock_requests WHERE status = 'pending'")->fetch()['count'];
    if ($pending_count > 0) {
        $notifications[] = [
            'type' => 'pending_requests',
            'count' => $pending_count,
            'message' => "You have $pending_count pending unlock requests",
            'url' => '?action=requests'
        ];
    }
    
    // Recent user registrations (last 24 hours)
    $recent_users = db_query("SELECT COUNT(*) as count FROM users WHERE created_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR)")->fetch()['count'];
    if ($recent_users > 0) {
        $notifications[] = [
            'type' => 'new_users',
            'count' => $recent_users,
            'message' => "$recent_users new users registered in last 24 hours",
            'url' => '?action=users'
        ];
    }
    
    return $notifications;
}

/**
 * Format file size for display
 */
function format_file_size($bytes) {
    if ($bytes >= 1073741824) {
        return number_format($bytes / 1073741824, 2) . ' GB';
    } elseif ($bytes >= 1048576) {
        return number_format($bytes / 1048576, 2) . ' MB';
    } elseif ($bytes >= 1024) {
        return number_format($bytes / 1024, 2) . ' KB';
    } else {
        return $bytes . ' bytes';
    }
}

/**
 * Sanitize input data
 */
function sanitize_input($data) {
    if (is_array($data)) {
        return array_map('sanitize_input', $data);
    }
    return htmlspecialchars(trim($data), ENT_QUOTES, 'UTF-8');
}

/**
 * Validate email format
 */
function validate_email($email) {
    return filter_var($email, FILTER_VALIDATE_EMAIL) !== false;
}

/**
 * Generate random string
 */
function generate_random_string($length = 10) {
    $characters = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    $random_string = '';
    for ($i = 0; $i < $length; $i++) {
        $random_string .= $characters[rand(0, strlen($characters) - 1)];
    }
    return $random_string;
}

/**
 * Check if bot is in maintenance mode
 */
function is_maintenance_mode() {
    return file_exists(__DIR__ . '/../maintenance.lock');
}

/**
 * Get server information
 */
function get_server_info() {
    $info = [];
    
    $info['php_version'] = PHP_VERSION;
    $info['server_software'] = $_SERVER['SERVER_SOFTWARE'] ?? 'Unknown';
    $info['max_upload_size'] = ini_get('upload_max_filesize');
    $info['max_post_size'] = ini_get('post_max_size');
    $info['memory_limit'] = ini_get('memory_limit');
    $info['timezone'] = date_default_timezone_get();
    
    return $info;
}

/**
 * Log error for debugging
 */
function log_error($error_message, $context = []) {
    $log_entry = "[" . date('Y-m-d H:i:s') . "] ERROR: $error_message";
    if (!empty($context)) {
        $log_entry .= " Context: " . json_encode($context);
    }
    $log_entry .= "\n";
    
    file_put_contents(__DIR__ . '/../logs/error.log', $log_entry, FILE_APPEND);
}

/**
 * Check database connection
 */
function check_database_connection() {
    try {
        global $db;
        $pdo = $db->getConnection();
        $pdo->query('SELECT 1');
        return true;
    } catch (Exception $e) {
        return false;
    }
}

/**
 * Get application version
 */
function get_app_version() {
    return '2.0';
}

/**
 * Clean expired temporary files
 */
function clean_temp_files() {
    $temp_dir = __DIR__ . '/../temp/';
    if (!is_dir($temp_dir)) {
        return;
    }
    
    $files = glob($temp_dir . '*');
    $now = time();
    
    foreach ($files as $file) {
        if (is_file($file)) {
            // Delete files older than 1 hour
            if ($now - filemtime($file) >= 3600) {
                unlink($file);
            }
        }
    }
}
?>