<?php
if (!defined('SECURE_ACCESS')) die('Direct access not permitted');

function runEndOfMonthPayoutSweep($pdo) {
    $swept_count = 0;
    
    // Load config for fees
    $stmt_config = $pdo->query("SELECT key, value FROM system_settings");
    $configs = $stmt_config->fetchAll(PDO::FETCH_KEY_PAIR) ?: [];
    
    $platform_fee_pct = (float)($configs['payout_platform_fee'] ?? 5.00);
    $tds_with_pan_pct = (float)($configs['payout_tds_with_pan'] ?? 5.00);
    $tds_without_pan_pct = (float)($configs['payout_tds_without_pan'] ?? 20.00);

    // Get all users and their minimum payout criteria
    $stmt_users = $pdo->query("
        SELECT u.id, d.criteria_min_payout 
        FROM users u 
        LEFT JOIN designations d ON u.designation_id = d.id 
        WHERE u.role = 'user' AND u.status = 'active'
    ");
    $users = $stmt_users->fetchAll(PDO::FETCH_ASSOC);
    
    foreach ($users as $u) {
        $user_id = $u['id'];
        
        $pdo->beginTransaction();
        try {
            // Lock user row
            $stmt_lock = $pdo->prepare("SELECT id FROM users WHERE id = ? FOR UPDATE");
            $stmt_lock->execute([$user_id]);
            
            // Calculate available balance
            $stmt_earn = $pdo->prepare("SELECT COALESCE(SUM(amount), 0.00) FROM earnings WHERE user_id = ?");
            $stmt_earn->execute([$user_id]);
            $total_earnings = (float)$stmt_earn->fetchColumn();

            $stmt_pay = $pdo->prepare("SELECT COALESCE(SUM(amount), 0.00) FROM payouts WHERE user_id = ? AND status IN ('pending', 'paid')");
            $stmt_pay->execute([$user_id]);
            $total_withdrawn = (float)$stmt_pay->fetchColumn();

            $available_balance = max(0.00, $total_earnings - $total_withdrawn);
            
            $min_payout = (float)($u['criteria_min_payout'] ?? 10.00); // default fallback
            
            // Only sweep if balance meets minimum payout threshold
            if ($available_balance >= $min_payout) {
                // Check KYC & PAN
                $stmt_kyc = $pdo->prepare("SELECT doc2_status, doc2_number, bank_name, account_number FROM kyc_records WHERE user_id = ?");
                $stmt_kyc->execute([$user_id]);
                $kycRecord = $stmt_kyc->fetch();
                
                $has_pan = ($kycRecord && !empty($kycRecord['doc2_number']) && $kycRecord['doc2_status'] === 'approved');
                $tds_pct = $has_pan ? $tds_with_pan_pct : $tds_without_pan_pct;
                
                $platform_fee = round($available_balance * ($platform_fee_pct / 100), 2);
                $tds_amount = round($available_balance * ($tds_pct / 100), 2);
                $net_amount = max(0.00, $available_balance - $platform_fee - $tds_amount);
                
                // Insert Payout
                $stmt_ins = $pdo->prepare("
                    INSERT INTO payouts (user_id, amount, platform_fee, tds_amount, net_amount, status, bank_name, account_number, created_at) 
                    VALUES (?, ?, ?, ?, ?, 'pending', ?, ?, CURRENT_TIMESTAMP)
                ");
                $stmt_ins->execute([
                    $user_id,
                    $available_balance,
                    $platform_fee,
                    $tds_amount,
                    $net_amount,
                    $kycRecord['bank_name'] ?? 'N/A',
                    $kycRecord['account_number'] ?? 'N/A'
                ]);
                
                $swept_count++;
            }
            $pdo->commit();
        } catch (Exception $e) {
            $pdo->rollBack();
            error_log("Failed to process sweep for user ID {$user_id}: " . $e->getMessage());
        }
    }
    
    return $swept_count;
}
