<?php
/**
 * DIAGNOSTIC: Survey Revenue Share Debug
 * Upload to /public_html/ and access via browser
 * DELETE THIS FILE AFTER DEBUGGING
 */

error_reporting(E_ALL);
ini_set('display_errors', 1);

echo "<h2>Survey Revenue Share Debug</h2>";
echo "<pre style='background:#f5f5f5;padding:15px;border-radius:8px;font-size:13px;'>";

// Connect to shop DB
try {
    $shopPdo = new PDO("mysql:host=localhost;dbname=u752449863_rrshop;charset=utf8mb4", "u752449863_rradmin", "S@n@h2016");
    $shopPdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $shopPdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
    echo "✅ Shop DB connected\n\n";
} catch (Exception $e) {
    echo "❌ Shop DB connection failed: " . $e->getMessage() . "\n";
    die("</pre>");
}

// 1. Check affiliates
echo "========== AFFILIATES ==========\n";
$stmt = $shopPdo->query("SELECT id, company_name, affiliate_code, signup_reward, survey_reward, total_commission_earned, commission_balance FROM affiliates");
$affiliates = $stmt->fetchAll();
foreach ($affiliates as $a) {
    echo "ID: {$a['id']} | {$a['company_name']} | signup_reward: {$a['signup_reward']} | survey_reward: " . ($a['survey_reward'] ?? 'NULL/missing') . " | earned: {$a['total_commission_earned']} | balance: {$a['commission_balance']}\n";
}

// Check if survey_reward column exists
echo "\n========== SURVEY_REWARD COLUMN CHECK ==========\n";
try {
    $stmt = $shopPdo->query("SHOW COLUMNS FROM affiliates LIKE 'survey_reward'");
    $col = $stmt->fetch();
    if ($col) {
        echo "✅ survey_reward column exists: Type={$col['Type']}, Default={$col['Default']}\n";
    } else {
        echo "❌ survey_reward column DOES NOT EXIST! This is why it shows 0.\n";
        echo "   Run: ALTER TABLE affiliates ADD COLUMN survey_reward DECIMAL(10,2) DEFAULT 5.00;\n";
    }
} catch (Exception $e) {
    echo "❌ Error checking column: " . $e->getMessage() . "\n";
}

// 2. Check affiliate signups with panel_user_id
echo "\n========== AFFILIATE SIGNUPS (with panel_user_id) ==========\n";
$stmt = $shopPdo->query("SELECT id, affiliate_id, email, panel_user_id, email_verified, mobile_verified, signup_completed FROM affiliate_signups WHERE panel_user_id IS NOT NULL");
$signups = $stmt->fetchAll();
if (empty($signups)) {
    echo "❌ No signups with panel_user_id found! Survey matching won't work.\n";
} else {
    foreach ($signups as $s) {
        echo "SignupID: {$s['id']} | AffiliateID: {$s['affiliate_id']} | Email: {$s['email']} | panel_user_id: {$s['panel_user_id']} | email_verified: {$s['email_verified']} | mobile_verified: {$s['mobile_verified']}\n";
    }
}

$panelUserIds = array_column($signups, 'panel_user_id');

// 3. Check projects
echo "\n========== PROJECTS ==========\n";
$stmt = $shopPdo->query("SELECT id, project_id, project_name, status FROM projects ORDER BY id DESC LIMIT 10");
$projects = $stmt->fetchAll();
foreach ($projects as $p) {
    echo "ID: {$p['id']} | {$p['project_id']} | {$p['project_name']} | Status: {$p['status']}\n";
}

$closedProjects = array_filter($projects, fn($p) => $p['status'] === 'Closed');
if (empty($closedProjects)) {
    echo "❌ No projects with status 'Closed' found!\n";
} else {
    echo "✅ " . count($closedProjects) . " closed project(s) found\n";
}

// 4. Check project_selections
echo "\n========== PROJECT SELECTIONS ==========\n";
$stmt = $shopPdo->query("SELECT ps.id, ps.project_id, ps.selection_name, p.status as project_status FROM project_selections ps JOIN projects p ON ps.project_id = p.id ORDER BY ps.id DESC LIMIT 10");
$selections = $stmt->fetchAll();
foreach ($selections as $s) {
    echo "SelectionID: {$s['id']} | ProjectID: {$s['project_id']} | {$s['selection_name']} | Project Status: {$s['project_status']}\n";
}

// 5. Check selection_members
echo "\n========== SELECTION MEMBERS ==========\n";
$stmt = $shopPdo->query("SELECT sm.id, sm.selection_id, sm.user_id, sm.sample_status, sm.completed_at, sm.points_awarded FROM selection_members sm ORDER BY sm.id DESC LIMIT 20");
$members = $stmt->fetchAll();
if (empty($members)) {
    echo "❌ selection_members table is EMPTY! No survey completions recorded.\n";
} else {
    foreach ($members as $m) {
        $isReferred = in_array($m['user_id'], $panelUserIds) ? '⭐ REFERRED' : '';
        echo "ID: {$m['id']} | SelectionID: {$m['selection_id']} | user_id: {$m['user_id']} | status: {$m['sample_status']} | completed: {$m['completed_at']} | points: {$m['points_awarded']} {$isReferred}\n";
    }
}

// 6. THE KEY QUERY - match referred members with completed surveys in closed projects
echo "\n========== KEY QUERY: Referred members with completed surveys in closed projects ==========\n";
if (!empty($panelUserIds)) {
    $placeholders = implode(',', array_fill(0, count($panelUserIds), '?'));
    $stmt = $shopPdo->prepare("
        SELECT sm.user_id, sm.sample_status, sm.completed_at, 
               ps.id as selection_id, ps.selection_name, 
               p.id as project_id, p.project_name, p.status as project_status
        FROM selection_members sm
        JOIN project_selections ps ON sm.selection_id = ps.id
        JOIN projects p ON ps.project_id = p.id
        WHERE sm.user_id IN ($placeholders)
    ");
    $stmt->execute($panelUserIds);
    $results = $stmt->fetchAll();
    
    if (empty($results)) {
        echo "❌ NO MATCHES! None of the referred panel_user_ids appear in selection_members.\n";
        echo "   Referred panel_user_ids: " . implode(', ', $panelUserIds) . "\n";
        echo "   This means:\n";
        echo "   - Either these members haven't been assigned to any survey selections\n";
        echo "   - Or the user_id in selection_members doesn't match panel_user_id in affiliate_signups\n";
    } else {
        echo "Found " . count($results) . " match(es):\n";
        foreach ($results as $r) {
            $match = ($r['sample_status'] === 'complete' && $r['project_status'] === 'Closed') ? '✅ QUALIFIES FOR REWARD' : '❌ Does not qualify';
            echo "  user_id: {$r['user_id']} | Survey: {$r['selection_name']} | Status: {$r['sample_status']} | Project: {$r['project_name']} ({$r['project_status']}) | {$match}\n";
        }
    }
} else {
    echo "❌ No panel_user_ids to check (no verified signups with panel_user_id)\n";
}

// 7. Check partner_commission_log table
echo "\n========== PARTNER_COMMISSION_LOG TABLE ==========\n";
try {
    $stmt = $shopPdo->query("SELECT * FROM partner_commission_log ORDER BY created_at DESC LIMIT 10");
    $logs = $stmt->fetchAll();
    if (empty($logs)) {
        echo "ℹ️ partner_commission_log table exists but is empty\n";
    } else {
        foreach ($logs as $l) {
            echo "ID: {$l['id']} | AffID: {$l['affiliate_id']} | Type: {$l['type']} | Amount: {$l['amount']} | {$l['description']} | {$l['created_at']}\n";
        }
    }
} catch (Exception $e) {
    echo "⚠️ partner_commission_log table doesn't exist yet (will be created by migration_reward_fix.sql)\n";
}

// 8. Check survey_rewards_log table
echo "\n========== SURVEY_REWARDS_LOG TABLE ==========\n";
try {
    $stmt = $shopPdo->query("SELECT * FROM survey_rewards_log ORDER BY created_at DESC LIMIT 10");
    $logs = $stmt->fetchAll();
    if (empty($logs)) {
        echo "ℹ️ survey_rewards_log table exists but is empty\n";
    } else {
        foreach ($logs as $l) {
            echo print_r($l, true) . "\n";
        }
    }
} catch (Exception $e) {
    echo "⚠️ survey_rewards_log table doesn't exist yet\n";
}

echo "\n========== SUMMARY ==========\n";
echo "If you see '❌ NO MATCHES' above, the issue is that referred members\n";
echo "haven't completed any surveys, or their user_id in selection_members\n";
echo "doesn't match the panel_user_id stored in affiliate_signups.\n";
echo "\nThe dashboard code detects ALL closed projects (past + future).\n";
echo "It doesn't require the close-project-rewards.php to run first.\n";

echo "</pre>";
echo "<p style='color:red;font-weight:bold;'>⚠️ DELETE THIS FILE after debugging!</p>";
?>