<?php
/**
 * import_attendance.php
 * Accepts an uploaded XLS/XLSX file (previous-year MGM attendance report format)
 * and imports registrations + marks attendance for matched participants.
 *
 * POST /organizer/import_attendance.php
 * Form field: file (XLS/XLSX), event_id (optional override)
 */

require_once __DIR__ . '/../includes/config.php';
require_once __DIR__ . '/../includes/auth.php';
require_once __DIR__ . '/../includes/database.php';
require_once __DIR__ . '/../vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;

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

// Only allow ADMIN or ORGANIZER
requireRole(['ADMIN', 'ORGANIZER']);

// CSRF (token arrives via X-CSRF-Token header from the dashboard fetch)
require_csrf(true);

if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
    http_response_code(405);
    echo json_encode(['error' => 'Method not allowed']);
    exit;
}

if (empty($_FILES['file'])) {
    http_response_code(400);
    echo json_encode(['error' => 'No file uploaded']);
    exit;
}

$uploadedFile = $_FILES['file'];
$allowedExts = ['xls', 'xlsx', 'csv'];
$ext = strtolower(pathinfo($uploadedFile['name'], PATHINFO_EXTENSION));

if (!in_array($ext, $allowedExts)) {
    http_response_code(400);
    echo json_encode(['error' => 'Invalid file type. Only .xls, .xlsx, and .csv are supported.']);
    exit;
}

if ($uploadedFile['error'] !== UPLOAD_ERR_OK) {
    http_response_code(400);
    echo json_encode(['error' => 'File upload error code: ' . $uploadedFile['error']]);
    exit;
}

try {
    $uploadDir = __DIR__ . '/../uploads/';
    if (!is_dir($uploadDir)) {
        mkdir($uploadDir, 0755, true);
    }
    // Prefix the filename with import_date_time_
    $filename = 'import_' . date('Ymd_His') . '_' . preg_replace('/[^a-zA-Z0-9._-]/', '_', basename($uploadedFile['name']));
    $savedFilePath = $uploadDir . $filename;
    
    // Copy the file to the uploads directory
    if (!copy($uploadedFile['tmp_name'], $savedFilePath)) {
        throw new Exception("Failed to save copy to uploads directory.");
    }

    $reader = IOFactory::createReaderForFile($savedFilePath);
    $reader->setReadDataOnly(true);
    $spreadsheet = $reader->load($savedFilePath);
    $sheet = $spreadsheet->getActiveSheet();
    $rows = $sheet->toArray(null, true, true, true);

} catch (Exception $e) {
    http_response_code(500);
    echo json_encode(['error' => 'Failed to read spreadsheet: ' . $e->getMessage()]);
    exit;
}

// ─────────────────────────────────────────────
// Detect header row by searching for known column names
// ─────────────────────────────────────────────
$headerRowNum  = null;
$colMap        = []; // [colLetter => normalizedKey]

$colNormMap = [
    'registration number'    => 'reg_num',
    'reg id'                 => 'reg_num',
    'registration id'        => 'reg_num',
    'name'                   => 'name',
    'participant name'       => 'name',
    'full name'              => 'name',
    'email'                  => 'email',
    'email id'               => 'email',
    'email address'          => 'email',
    'contact number'         => 'mobile',
    'mobile'                 => 'mobile',
    'phone'                  => 'mobile',
    'institute'              => 'college',
    'institute/company'      => 'college',
    'college'                => 'college',
    'organization'           => 'college',
    'payment amount / status'=> 'pay_amount',
    'payment amount'         => 'pay_amount',
    'fees in rs.'            => 'pay_amount',
    'total fees in rs.'      => 'pay_amount',
    'amount'                 => 'pay_amount',
    'transaction number'     => 'txn_id',
    'transaction id'         => 'txn_id',
    'razorpay payment id'    => 'txn_id',
    'sub category'           => 'event_name',
    'event'                  => 'event_name',
    'event name'             => 'event_name',
    'category'               => 'category',
    'class/year'             => 'class_year',
    'transaction date'       => 'txn_date',
    'registration date & time'=> 'reg_date',
    'registration date'      => 'reg_date',
    'check-in time'          => 'checkin_time',
    'check in time'          => 'checkin_time',
    'attendance'             => 'attendance',
    'city'                   => 'city',
];

foreach ($rows as $rowNum => $row) {
    $rowValues = array_values(array_filter($row, fn($v) => $v !== null && $v !== ''));
    if (count($rowValues) < 3) continue;

    // Try to match header row
    $matchCount = 0;
    $tempMap = [];
    foreach ($row as $col => $cell) {
        if ($cell === null || $cell === '') continue;
        $normalized = strtolower(trim(preg_replace('/\s+/', ' ', $cell)));
        if (isset($colNormMap[$normalized])) {
            $tempMap[$col] = $colNormMap[$normalized];
            $matchCount++;
        }
    }
    if ($matchCount >= 3) {
        $headerRowNum = $rowNum;
        $colMap = $tempMap;
        break;
    }
}

if ($headerRowNum === null) {
    // Fallback: Try to auto-detect MGM format (col B=reg_num, C=name, D=email, E=mobile, F=college, G=pay_amount, J=txn_id, L=event_name, N=pay_amount, O=reg_date)
    $colMap = [
        'B' => 'reg_num',
        'C' => 'name',
        'D' => 'email',
        'E' => 'mobile',
        'F' => 'college',
        'G' => 'pay_amount',
        'J' => 'txn_id',
        'L' => 'event_name',
        'N' => 'pay_amount',
        'O' => 'reg_date',
    ];
    $headerRowNum = 9; // MGM format default header row
}

// ─────────────────────────────────────────────
// Load all events from DB for fuzzy matching
// ─────────────────────────────────────────────
$pdo = getDatabaseConnection();

// Ensure importSource column exists in registrations table
try {
    $pdo->query("SELECT importSource FROM registrations LIMIT 1");
} catch (PDOException $e) {
    $pdo->query("ALTER TABLE registrations ADD COLUMN importSource VARCHAR(255) DEFAULT NULL");
}

$eventsStmt = $pdo->query("SELECT id, title, slug FROM events");
$allEvents = $eventsStmt->fetchAll(PDO::FETCH_ASSOC);

/**
 * Find best matching event by title similarity
 */
function findEventMatch(array $allEvents, string $rawEventName): ?array {
    if (empty($rawEventName)) return null;
    $clean = strtolower(preg_replace('/[^a-z0-9\s]/i', '', $rawEventName));

    $best = null;
    $bestScore = 0;

    foreach ($allEvents as $evt) {
        $evtClean = strtolower(preg_replace('/[^a-z0-9\s]/i', '', $evt['title']));
        // Extract first word of each
        $evtWords = explode(' ', $evtClean);
        $rawWords = explode(' ', $clean);

        $commonWords = array_intersect($evtWords, $rawWords);
        $score = count($commonWords);

        // Bonus: exact substring match
        if (str_contains($clean, $evtClean) || str_contains($evtClean, $clean)) {
            $score += 5;
        }
        // Bonus: first word matches
        if (!empty($evtWords[0]) && !empty($rawWords[0]) && $evtWords[0] === $rawWords[0]) {
            $score += 3;
        }

        if ($score > $bestScore) {
            $bestScore = $score;
            $best = $evt;
        }
    }

    return ($bestScore > 0) ? $best : null;
}

// ─────────────────────────────────────────────
// Process rows
// ─────────────────────────────────────────────
$stats = [
    'total_rows'           => 0,
    'imported'             => 0,
    'already_exists'       => 0,
    'skipped_no_event'     => 0,
    'skipped_invalid'      => 0,
    'errors'               => [],
    'preview'              => [],
];

$processedRows = 0;

foreach ($rows as $rowNum => $row) {
    // Skip header row and rows before data
    if ($rowNum <= $headerRowNum) continue;

    // Skip completely empty rows
    $rowValues = array_filter($row, fn($v) => $v !== null && $v !== '');
    if (empty($rowValues)) continue;

    // Skip rows where the first non-empty cell looks like a section header
    $firstVal = null;
    foreach ($row as $v) { if ($v !== null && $v !== '') { $firstVal = $v; break; } }
    if ($firstVal !== null && !is_numeric($firstVal) && strlen($firstVal) < 3) continue;

    // Extract data using colMap
    $data = [];
    foreach ($colMap as $col => $key) {
        $val = $row[$col] ?? null;
        if ($val !== null) {
            if (isset($data[$key])) {
                // prefer later pay_amount (total fees) over earlier (fee amount)
                if ($key === 'pay_amount' && !empty($val)) {
                    $data[$key] = $val;
                }
            } else {
                $data[$key] = $val;
            }
        }
    }

    // Basic required fields
    $name   = trim($data['name'] ?? '');
    $email  = strtolower(trim($data['email'] ?? ''));
    $mobile = preg_replace('/[^0-9]/', '', trim($data['mobile'] ?? ''));
    $regNum = trim($data['reg_num'] ?? '');
    $college = trim($data['college'] ?? 'Unknown');
    $city   = trim($data['city'] ?? 'Unknown');
    $rawEventName = trim($data['event_name'] ?? $data['category'] ?? '');
    $payAmount = floatval(preg_replace('/[^0-9.]/', '', $data['pay_amount'] ?? '0'));
    $txnId  = trim($data['txn_id'] ?? '');
    $checkinTime = $data['checkin_time'] ?? null;
    $attendance = strtolower(trim($data['attendance'] ?? ''));

    // Extract first email from multi-name fields
    if (empty($email) && !empty($name) && str_contains($name, '@')) {
        // sometimes email is in name field
        preg_match('/[\w._%+-]+@[\w.-]+\.[a-z]{2,}/i', $name, $emailMatch);
        $email = strtolower($emailMatch[0] ?? '');
    }

    // Multi-person registrations - take first email
    if (str_contains($email, ',')) {
        $email = trim(explode(',', $email)[0]);
    }

    if (empty($name) || empty($email) || !filter_var($email, FILTER_VALIDATE_EMAIL)) {
        $stats['skipped_invalid']++;
        continue;
    }

    // Normalize mobile (take last 10 digits)
    if (strlen($mobile) > 10) $mobile = substr($mobile, -10);
    if (strlen($mobile) < 10) $mobile = str_pad($mobile, 10, '0', STR_PAD_LEFT);

    $stats['total_rows']++;

    // Find matching event
    $matchedEvent = null;
    if (!empty($rawEventName)) {
        $matchedEvent = findEventMatch($allEvents, $rawEventName);
    }
    // If no event matched, use first published event as fallback (or skip)
    if (!$matchedEvent) {
        // Try to find from category
        foreach ($allEvents as $evt) {
            $matchedEvent = $evt;
            break; // fallback to first event
        }
        if (!$matchedEvent) {
            $stats['skipped_no_event']++;
            $stats['errors'][] = "Row $rowNum: No event matched for '$rawEventName'";
            continue;
        }
    }

    $eventId = $matchedEvent['id'];
    $eventTitle = $matchedEvent['title'];

    try {
        // Check if registration already exists by email + event
        $checkStmt = $pdo->prepare("SELECT id, registrationId FROM registrations WHERE email = ? AND eventId = ?");
        $checkStmt->execute([$email, $eventId]);
        $existingReg = $checkStmt->fetch(PDO::FETCH_ASSOC);

        $registrationDbId = null;
        $regIdCode = null;

        if ($existingReg) {
            // Already registered
            $stats['already_exists']++;
            $registrationDbId = $existingReg['id'];
            $regIdCode = $existingReg['registrationId'];

            // Update registrationId and importSource if Excel has a registration number and it differs
            if (!empty($regNum) && $existingReg['registrationId'] !== $regNum) {
                $updateRegId = $pdo->prepare("UPDATE registrations SET registrationId = ?, importSource = ? WHERE id = ?");
                $updateRegId->execute([$regNum, $filename, $registrationDbId]);
                $regIdCode = $regNum;
            } else {
                $updateImportSource = $pdo->prepare("UPDATE registrations SET importSource = ? WHERE id = ?");
                $updateImportSource->execute([$filename, $registrationDbId]);
            }
        } else {
            // Generate unique Registration ID
            // Use the imported reg number if provided, else generate
            if (!empty($regNum)) {
                $regIdCode = $regNum;
            } else {
                // Generate from event slug prefix + 5 digit number
                $prefix = strtoupper(substr(preg_replace('/[^a-z]/i', '', $matchedEvent['title']), 0, 3));
                do {
                    $regIdCode = $prefix . rand(10000, 99999);
                    $checkReg = $pdo->prepare("SELECT COUNT(*) FROM registrations WHERE registrationId = ?");
                    $checkReg->execute([$regIdCode]);
                } while ($checkReg->fetchColumn() > 0);
            }

            // Insert Registration
            $insertReg = $pdo->prepare("INSERT INTO registrations (id, registrationId, eventId, fullName, email, mobileNumber, college, city, importSource, createdAt) VALUES (UUID(), ?, ?, ?, ?, ?, ?, ?, ?, NOW())");
            $insertReg->execute([$regIdCode, $eventId, $name, $email, $mobile, $college, $city, $filename]);

            // Fetch the inserted row's UUID
            $regIdStmt = $pdo->prepare("SELECT id FROM registrations WHERE registrationId = ?");
            $regIdStmt->execute([$regIdCode]);
            $regRecord = $regIdStmt->fetch(PDO::FETCH_ASSOC);
            $registrationDbId = $regRecord['id'];

            // Check for existing payment record
            $checkPay = $pdo->prepare("SELECT id FROM payments WHERE registrationId = ?");
            $checkPay->execute([$registrationDbId]);
            if (!$checkPay->fetch()) {
                // Insert Payment record with a guaranteed-unique import order ID
                $razorOrderId = !empty($txnId) ? ('import_txn_' . $txnId) : ('import_' . uniqid('', true) . '_' . $regIdCode);
                $payStatus = 'SUCCESSFUL'; // treat imported paid entries as successful
                $insertPay = $pdo->prepare("INSERT INTO payments (id, registrationId, amount, currency, razorpayOrderId, razorpayPaymentId, status, createdAt) VALUES (UUID(), ?, ?, 'INR', ?, ?, ?, NOW())");
                $insertPay->execute([$registrationDbId, $payAmount, $razorOrderId, $txnId ?: null, $payStatus]);
            }

            $stats['imported']++;
        }


        if (count($stats['preview']) < 10) {
            $stats['preview'][] = [
                'name'       => $name,
                'email'      => $email,
                'regId'      => $regIdCode,
                'event'      => $eventTitle,
                'status'     => $existingReg ? 'already_exists' : 'imported'
            ];
        }

    } catch (PDOException $e) {
        $stats['errors'][] = "Row $rowNum ($email): DB error - " . $e->getMessage();
        $stats['skipped_invalid']++;
    }
}

echo json_encode([
    'success' => true,
    'stats'   => $stats,
    'message' => "Import complete. {$stats['imported']} new registrations imported, {$stats['already_exists']} already existed.",
]);
