<?php
/**
 * App API: For Approval list for AC/AAC (same as web request.php).
 * GET user_id = officer. Returns signified (grouped by match) + withdrawal_requests.
 * Includes local and retroactive matches.
 */
require_once __DIR__ . '/../config/cors.php';
require_once __DIR__ . '/../config/db.php';

header('Content-Type: application/json');

$response = ['success' => false, 'signified' => [], 'withdrawal_requests' => [], 'message' => ''];

try {
    $officerUserId = isset($_GET['user_id']) ? (int) $_GET['user_id'] : 0;
    if ($officerUserId <= 0) {
        $response['message'] = 'User ID is required';
        echo json_encode($response);
        exit;
    }

    $stmt = $pdo->prepare("SELECT id, role, area FROM users WHERE id = ? AND status = 'approved'");
    $stmt->execute([$officerUserId]);
    $officer = $stmt->fetch(PDO::FETCH_ASSOC);

    if (!$officer || !in_array($officer['role'], ['AC', 'AAC'])) {
        $response['message'] = 'Unauthorized. AC or AAC only.';
        echo json_encode($response);
        exit;
    }

    $area = trim($officer['area'] ?? '');
    $roleFilter = " AND (u.role IN ('AC', 'AAC', 'R.O', 'C.R.O', 'RO', 'CRO', 'member') OR u.role IS NULL OR u.role = '')";

    $pdo->exec("
        CREATE TABLE IF NOT EXISTS activity_logs (
            id INT AUTO_INCREMENT PRIMARY KEY,
            reference_type VARCHAR(50) NOT NULL,
            reference_id INT NULL,
            description TEXT NOT NULL,
            status VARCHAR(50) NOT NULL,
            performed_by_role VARCHAR(20) NOT NULL,
            performed_by_user_id INT NULL,
            performed_by_admin_id INT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ");

    $columns = $pdo->query("DESCRIBE match_schedules")->fetchAll(PDO::FETCH_COLUMN);
    $hasRmName = in_array('rm_name', $columns);
    $hasRoLimit = in_array('ro_limit', $columns);
    $rmSelect = $hasRmName ? "m.rm_name" : "'' as rm_name";
    $roSelect = $hasRoLimit ? "m.ro_limit" : "0 as ro_limit";

    // Signified: local/retroactive all statuses; regular only approved (for withdrawal)
    $stmt = $pdo->prepare("
        SELECT 
            ms.*,
            u.nroi_id, u.first_name, u.last_name, u.role, u.rank, u.area, u.profile_photo,
            m.id as match_id, m.match_name, m.start_date, m.end_date, m.location, m.match_level, m.range_master, m.is_local, m.is_retroactive, $rmSelect, $roSelect,
            (SELECT COUNT(*) FROM match_significations WHERE match_id = m.id AND status = 'approved') as approved_count,
            (SELECT COUNT(*) FROM match_significations WHERE user_id = u.id AND status = 'withdrawn') as withdraw_count,
            CASE 
                WHEN mp.rm_signature IS NOT NULL AND mp.rm_signature != '' AND mp.rm_signature != 'DEMERIT' THEN 'served'
                WHEN ms.status != 'approved' THEN NULL
                ELSE 'incomplete'
            END as match_completion_status
        FROM match_significations ms
        JOIN users u ON ms.user_id = u.id
        JOIN match_schedules m ON ms.match_id = m.id
        LEFT JOIN match_points mp ON ms.match_id = mp.match_id AND ms.user_id = mp.user_id
        WHERE TRIM(COALESCE(u.area, '')) = ? 
          $roleFilter
          AND m.end_date >= CURDATE()
          AND (
            ((m.is_local = 1 OR m.is_retroactive = 1) AND ms.status IN ('pending', 'approved', 'standby', 'declined', 'rejected'))
            OR
            ((m.is_local = 0 AND m.is_retroactive = 0) AND ms.status = 'approved')
          )
        ORDER BY m.match_name ASC, 
          CASE ms.status WHEN 'pending' THEN 1 WHEN 'approved' THEN 2 WHEN 'standby' THEN 3 WHEN 'declined' THEN 4 ELSE 5 END,
          ms.created_at DESC
    ");
    $stmt->execute([$area]);
    $signifiedRows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $groupedSignified = [];
    foreach ($signifiedRows as $row) {
        $mid = $row['match_id'];
        if (!isset($groupedSignified[$mid])) {
            $groupedSignified[$mid] = [
                'match_info' => [
                    'match_id' => (int) $row['match_id'],
                    'match_name' => $row['match_name'],
                    'start_date' => $row['start_date'],
                    'end_date' => $row['end_date'],
                    'location' => $row['location'],
                    'match_level' => $row['match_level'],
                    'range_master' => $row['range_master'],
                    'rm_name' => $row['rm_name'] ?? '',
                    'ro_limit' => (int) ($row['ro_limit'] ?? 0),
                    'approved_count' => (int) ($row['approved_count'] ?? 0),
                    'is_local' => !empty($row['is_local']),
                    'is_retroactive' => !empty($row['is_retroactive']),
                ],
                'significations' => [],
            ];
        }
        $groupedSignified[$mid]['significations'][] = [
            'id' => (int) $row['id'],
            'user_id' => (int) $row['user_id'],
            'status' => $row['status'],
            'first_name' => $row['first_name'],
            'last_name' => $row['last_name'],
            'nroi_id' => $row['nroi_id'],
            'profile_photo' => $row['profile_photo'] ?? '',
            'withdraw_count' => (int) ($row['withdraw_count'] ?? 0),
            'remarks' => $row['officer_remarks'] ?? '',
            'withdrawal_reason' => $row['withdrawal_reason'] ?? '',
            'created_at' => $row['created_at'],
            'match_completion_status' => $row['match_completion_status'],
            'role' => $row['role'] ?? '',
            'rank' => $row['rank'] ?? '',
        ];
    }
    $response['signified'] = array_values($groupedSignified);

    // Withdrawal requests
    $stmt = $pdo->prepare("
        SELECT 
            ms.*,
            u.nroi_id, u.first_name, u.last_name, u.role, u.rank, u.area, u.profile_photo,
            m.id as match_id, m.match_name, m.start_date, m.end_date, m.location, m.match_level, m.range_master, m.is_local, m.is_retroactive, $rmSelect,
            (SELECT al.performed_by_role FROM activity_logs al
             WHERE al.reference_type = 'match_signification' AND al.reference_id = ms.id
               AND al.performed_by_role IN ('AC','AAC','ADMIN','RM')
             ORDER BY al.created_at DESC LIMIT 1) as reviewer_role,
            (SELECT COUNT(*) FROM match_significations WHERE user_id = u.id AND status = 'withdrawn') as withdraw_count
        FROM match_significations ms
        JOIN users u ON ms.user_id = u.id
        JOIN match_schedules m ON ms.match_id = m.id
        WHERE ms.status IN ('withdrawal_requested', 'withdrawn') 
          AND TRIM(COALESCE(u.area, '')) = ? 
          $roleFilter
          AND m.end_date >= CURDATE()
        ORDER BY m.match_name ASC, ms.created_at DESC
    ");
    $stmt->execute([$area]);
    $withdrawalRows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $groupedWithdrawals = [];
    foreach ($withdrawalRows as $row) {
        $mid = $row['match_id'];
        if (!isset($groupedWithdrawals[$mid])) {
            $groupedWithdrawals[$mid] = [
                'match_info' => [
                    'match_id' => (int) $row['match_id'],
                    'match_name' => $row['match_name'],
                    'start_date' => $row['start_date'],
                    'end_date' => $row['end_date'],
                    'location' => $row['location'],
                    'match_level' => $row['match_level'],
                    'range_master' => $row['range_master'],
                    'is_local' => !empty($row['is_local']),
                    'is_retroactive' => !empty($row['is_retroactive']),
                    'rm_name' => $row['rm_name'] ?? '',
                ],
                'significations' => [],
            ];
        }
        $groupedWithdrawals[$mid]['significations'][] = [
            'id' => (int) $row['id'],
            'user_id' => (int) $row['user_id'],
            'status' => $row['status'],
            'first_name' => $row['first_name'],
            'last_name' => $row['last_name'],
            'nroi_id' => $row['nroi_id'],
            'profile_photo' => $row['profile_photo'] ?? '',
            'withdraw_count' => (int) ($row['withdraw_count'] ?? 0),
            'remarks' => $row['officer_remarks'] ?? '',
            'withdrawal_reason' => $row['withdrawal_reason'] ?? '',
            'role' => $row['role'] ?? '',
            'rank' => $row['rank'] ?? '',
        ];
    }
    $response['withdrawal_requests'] = array_values($groupedWithdrawals);

    $response['success'] = true;
} catch (Throwable $e) {
    $response['message'] = $e->getMessage();
}

echo json_encode($response);
