<?php
header('Content-Type: application/json');
require_once '../config/config.php';
require_once '../vendor/autoload.php'; // for PhpSpreadsheet

use PhpOffice\PhpSpreadsheet\IOFactory;

$response = ['success' => false, 'message' => '', 'inserted' => 0, 'skipped' => 0];

try {
    if (!isset($_FILES['xls-file']) || $_FILES['xls-file']['error'] !== UPLOAD_ERR_OK) {
        throw new Exception("File upload failed. Please select a valid Excel/CSV file.");
    }

    $fileTmp = $_FILES['xls-file']['tmp_name'];
    $spreadsheet = IOFactory::load($fileTmp);
    $sheet = $spreadsheet->getActiveSheet();
    $rows = $sheet->toArray(null, true, true, true);

    // Check for header row
    if (count($rows) < 2) {
        throw new Exception("The uploaded file appears to be empty or incorrectly formatted.");
    }

    $inserted = 0;
    $skipped = 0;

    // Prepare statements (use separate ones, no reusing inside loop)
    $checkStmt = $conn->prepare("SELECT id FROM calendar WHERE date = ?");
    $insertStmt = $conn->prepare("INSERT INTO calendar (`date`, `day`, `description`, `type`) VALUES (?, ?, ?, ?)");

    // Loop from row 2 onwards (skipping headers)
    foreach ($rows as $index => $row) {
        if ($index == 1)
            continue; // skip header

        $date = trim($row['A']);
        $day = trim($row['B']);
        $description = trim($row['C']);
        $type = trim($row['D']);

        if (empty($date) || empty($description) || empty($type)) {
            $skipped++;
            continue;
        }

        // Convert Excel date format if needed
        $excelTimestamp = strtotime($date);
        if (!$excelTimestamp) {
            // Try to convert Excel serial number
            if (is_numeric($date)) {
                $excelTimestamp = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToTimestamp($date);
            }
        }

        $finalDate = date('Y-m-d', $excelTimestamp);

        // Check if entry exists
        $checkStmt->bind_param('s', $finalDate);
        $checkStmt->execute();
        $checkResult = $checkStmt->get_result();

        if ($checkResult->num_rows > 0) {
            $skipped++;
            continue;
        }

        // Insert
        $insertStmt->bind_param('ssss', $finalDate, $day, $description, $type);
        if ($insertStmt->execute()) {
            $inserted++;
        } else {
            $skipped++;
        }
    }

    $response['success'] = true;
    $response['inserted'] = $inserted;
    $response['skipped'] = $skipped;
    $response['message'] = "Bulk upload completed. Inserted: $inserted, Skipped: $skipped.";

} catch (Exception $e) {
    $response['success'] = false;
    $response['message'] = 'Error: ' . $e->getMessage();
}

// Always close prepared statements
if (isset($checkStmt))
    $checkStmt->close();
if (isset($insertStmt))
    $insertStmt->close();
$conn->close();

echo json_encode($response);
?>