<?php
require_once '../config/cors.php';
header('Content-Type: application/json');

require_once '../config/db.php';

$userId = isset($_GET['user_id']) ? (int) $_GET['user_id'] : 0;
$yearParam = isset($_GET['year']) ? $_GET['year'] : '0';
$isAllYears = ($yearParam === 'all' || $yearParam === '0' || $yearParam === 0);
$year = $isAllYears ? 0 : (int) $yearParam;

if ($userId <= 0) {
    echo json_encode(['status' => 'error', 'message' => 'Valid User ID is required']);
    exit;
}

try {
    // 1. Fetch matches from match_points with assigned roles
    // Dynamic role recognition for all assignment tables
    $roleCases = "";
    try {
        $stmtTables = $pdo->query("SHOW TABLES LIKE 'match_%_assignments'");
        $assignmentTables = $stmtTables->fetchAll(PDO::FETCH_COLUMN);

        usort($assignmentTables, function ($a, $b) {
            $priority = ['match_states_assignments' => 1, 'match_stats_assignments' => 2, 'match_chrono_assignments' => 3, 'match_equipment_check_assignments' => 4];
            $pA = $priority[$a] ?? 99;
            $pB = $priority[$b] ?? 99;
            return $pA - $pB;
        });

        foreach ($assignmentTables as $tbl) {
            if ($tbl === 'match_range_master_assignments')
                continue;
            $roleCases .= "                   WHEN (SELECT role FROM `$tbl` WHERE match_id = m.id AND submitted_by = mp.user_id LIMIT 1) > '' 
                        THEN (SELECT role FROM `$tbl` WHERE match_id = m.id AND submitted_by = mp.user_id LIMIT 1)\n";
        }
    } catch (Exception $e) {
    }

    $queryMatchPoints = "
        SELECT mp.*, m.match_name, m.match_level, m.start_date, m.end_date, m.location, m.range_master, m.rm_name,
               msa.states_number, msa.lro_name AS states_lro_name, msa.rm_signature AS states_rm_signature, msa.created_at AS states_signed_at,
               CASE 
                   WHEN m.rm_id = ? THEN 'Range Master'
                   WHEN (SELECT role FROM match_range_master_assignments WHERE match_id = m.id AND submitted_by = mp.user_id LIMIT 1) IS NOT NULL 
                        AND (SELECT role FROM match_range_master_assignments WHERE match_id = m.id AND submitted_by = mp.user_id LIMIT 1) != '' 
                        THEN (CASE 
                            WHEN (SELECT role FROM match_range_master_assignments WHERE match_id = m.id AND submitted_by = mp.user_id LIMIT 1) = 'Area Chief Officer' THEN 'Area Chief Range Officer'
                            ELSE (SELECT role FROM match_range_master_assignments WHERE match_id = m.id AND submitted_by = mp.user_id LIMIT 1)
                        END)
$roleCases                   ELSE '---'
               END as assigned_role,
               0 as is_manual
        FROM match_points mp
        JOIN match_schedules m ON mp.match_id = m.id
        LEFT JOIN match_states_assignments msa ON msa.match_id = mp.match_id AND msa.submitted_by = mp.user_id
        WHERE mp.user_id = ?
    ";

    $paramsMatchPoints = [$userId, $userId];
    if (!$isAllYears) {
        $queryMatchPoints .= " AND YEAR(m.start_date) = ?";
        $paramsMatchPoints[] = $year;
    }
    $queryMatchPoints .= " ORDER BY m.start_date DESC";

    $stmt = $pdo->prepare($queryMatchPoints);
    $stmt->execute($paramsMatchPoints);
    $history = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // 2. Include manual_match_points (Previous matches added by AC/Admin)
    $queryManual = "
        SELECT id as mp_id, points, NULL as rm_signature, created_at,
               NULL as match_id, match_name, level as match_level, 
               CASE 
                   WHEN month IS NOT NULL AND month > 0 THEN CAST(CONCAT(year, '-', LPAD(month, 2, '0'), '-01') AS DATE)
                   ELSE CAST(CONCAT(year, '-01-01') AS DATE)
               END as start_date,
               CASE 
                   WHEN month IS NOT NULL AND month > 0 THEN CAST(CONCAT(year, '-', LPAD(month, 2, '0'), '-01') AS DATE)
                   ELSE CAST(CONCAT(year, '-01-01') AS DATE)
               END as end_date,
               location, discipline as range_master, 'Manual Entry' as rm_name,
               role as assigned_role,
               1 as is_manual,
               NULL as states_number, NULL as states_lro_name, NULL as states_rm_signature, NULL as states_signed_at,
               month, year
        FROM manual_match_points
        WHERE user_id = ?
    ";
    $paramsManual = [$userId];
    if (!$isAllYears) {
        $queryManual .= " AND year = ?";
        $paramsManual[] = $year;
    }

    $stmtManual = $pdo->prepare($queryManual);
    $stmtManual->execute($paramsManual);
    $manualMatches = $stmtManual->fetchAll(PDO::FETCH_ASSOC);
    foreach ($manualMatches as $row) {
        $history[] = $row;
    }

    // 3. Include matches where user submitted RM signature (stage assignment) but has no point card yet
    try {
        $queryMsa = "
            SELECT m.id AS match_id, m.match_name, m.match_level, m.start_date, m.end_date, m.location, m.range_master, m.rm_name,
                   msa.states_number, msa.lro_name AS states_lro_name, msa.rm_signature AS states_rm_signature, msa.created_at AS states_signed_at
            FROM match_states_assignments msa
            JOIN match_schedules m ON m.id = msa.match_id
            LEFT JOIN match_points mp ON mp.match_id = msa.match_id AND mp.user_id = ?
            WHERE msa.submitted_by = ? AND mp.id IS NULL
        ";
        $paramsMsa = [$userId, $userId];
        if (!$isAllYears) {
            $queryMsa .= " AND YEAR(m.start_date) = ?";
            $paramsMsa[] = $year;
        }

        $stmtMsa = $pdo->prepare($queryMsa);
        $stmtMsa->execute($paramsMsa);
        $msaOnly = $stmtMsa->fetchAll(PDO::FETCH_ASSOC);
        foreach ($msaOnly as $row) {
            $history[] = [
                'id' => 0,
                'match_id' => $row['match_id'],
                'user_id' => $userId,
                'points' => 0,
                'rm_signature' => '',
                'created_at' => '',
                'match_name' => $row['match_name'],
                'match_level' => $row['match_level'],
                'start_date' => $row['start_date'],
                'end_date' => $row['end_date'],
                'location' => $row['location'],
                'range_master' => $row['range_master'],
                'rm_name' => $row['rm_name'],
                'states_number' => $row['states_number'],
                'states_lro_name' => $row['states_lro_name'],
                'states_rm_signature' => $row['states_rm_signature'],
                'states_signed_at' => $row['states_signed_at'],
                'assigned_role' => 'Range Officer',
                'is_manual' => 0
            ];
        }
    } catch (Exception $e) {
        // Skip MSA block on error
    }

    // 4. Include matches where user was approved but match has ended and no points submitted yet
    try {
        $today = date('Y-m-d');
        $queryIncomplete = "
            SELECT m.id AS match_id, m.match_name, m.match_level, m.start_date, m.end_date, m.location, m.range_master, m.rm_name
            FROM match_schedules m
            JOIN match_significations ms ON m.id = ms.match_id
            LEFT JOIN match_points mp ON mp.match_id = m.id AND mp.user_id = ?
            WHERE ms.user_id = ? 
            AND ms.status = 'approved'
            AND mp.id IS NULL
        ";
        $paramsIncomplete = [$userId, $userId];
        if (!$isAllYears) {
            $queryIncomplete .= " AND YEAR(m.start_date) = ?";
            $paramsIncomplete[] = $year;
        }

        $stmtIncomplete = $pdo->prepare($queryIncomplete);
        $stmtIncomplete->execute($paramsIncomplete);
        $incompleteMatches = $stmtIncomplete->fetchAll(PDO::FETCH_ASSOC);

        foreach ($incompleteMatches as $row) {
            $history[] = [
                'id' => 0,
                'match_id' => $row['match_id'],
                'user_id' => $userId,
                'points' => 0,
                'rm_signature' => '',
                'created_at' => '',
                'match_name' => $row['match_name'],
                'match_level' => $row['match_level'],
                'start_date' => $row['start_date'],
                'end_date' => $row['end_date'],
                'location' => $row['location'],
                'range_master' => $row['range_master'],
                'rm_name' => $row['rm_name'],
                'is_incomplete_match' => true,
                'assigned_role' => '---',
                'is_manual' => 0
            ];
        }
    } catch (Exception $e) {
        // Skip on error
    }

    // Sort combined history by start date DESC
    usort($history, function ($a, $b) {
        $t1 = strtotime($a['start_date'] ?? '');
        $t2 = strtotime($b['start_date'] ?? '');
        return $t2 - $t1;
    });

    // 5. Total points calculation
    $queryTotalActual = "
        SELECT COALESCE(SUM(mp.points), 0) as total
        FROM match_points mp
        JOIN match_schedules m ON mp.match_id = m.id
        WHERE mp.user_id = ? AND mp.rm_signature IS NOT NULL AND mp.rm_signature != '' AND mp.rm_signature != 'DEMERIT'
    ";
    $paramsTotalActual = [$userId];
    if (!$isAllYears) {
        $queryTotalActual .= " AND YEAR(m.start_date) = ?";
        $paramsTotalActual[] = $year;
    }
    $stmtTotalActual = $pdo->prepare($queryTotalActual);
    $stmtTotalActual->execute($paramsTotalActual);
    $actualTotal = (float) $stmtTotalActual->fetchColumn();

    $queryTotalManual = "SELECT COALESCE(SUM(points), 0) FROM manual_match_points WHERE user_id = ?";
    $paramsTotalManual = [$userId];
    if (!$isAllYears) {
        $queryTotalManual .= " AND year = ?";
        $paramsTotalManual[] = $year;
    }
    $stmtTotalManual = $pdo->prepare($queryTotalManual);
    $stmtTotalManual->execute($paramsTotalManual);
    $manualTotal = (float) $stmtTotalManual->fetchColumn();

    $totalPoints = $actualTotal + $manualTotal;

    echo json_encode([
        'status' => 'success',
        'year' => $year,
        'total_points' => $totalPoints,
        'data' => $history
    ]);

} catch (PDOException $e) {
    echo json_encode([
        'status' => 'error',
        'message' => 'Database error: ' . $e->getMessage()
    ]);
}
?>