<?php

$postdata = $_POST;
$cron_error = [];

// // Validate required fields
// if (!isset($postdata['api_signature']) || empty($postdata['api_signature']) ||
//      !isset($postdata['function_type']) || empty($postdata['function_type']) ) {
//      $cron_error[] = "All required fields (api_signature, function_type, jwt_token) are missing.";
// }

// if (!empty($cron_error)) {
//      errorreturn(implode(' | ', $cron_error));
// }

// validateApiSignature($postdata['api_signature']);
// $userid = $postdata['user_id'];
// $workspace_id = $postdata['workspace_id'];
// $username = $postdata['username'];
// $role = $postdata['role'];


$specific_db_name = validateUserAccess($conn_rjllearning, $userid, $role, $workspace_id, ["superadmin","admin","principal","teacher"], null);
if (!$specific_db_name) {
    $cron_error[] = 'Access denied.';
}

if (!empty($cron_error)) {
    errorreturn(implode(' | ', $cron_error));
}

$conn_rjllearning_specific = dynamicDbConnection($specific_db_name);
$institution_id =2;

if ($postdata['function_type'] == "import_bus_stops") {
    // Initial file validation
    // if (!isset($_FILES['excelFile']) || $_FILES['excelFile']['error'] != UPLOAD_ERR_OK) {
    //      $cron_error[] = 'No file was uploaded or file error occurred.';
    // } else {
    //      $file = $_FILES['excelFile'];
    //      $allowedExtensions = ["xlsx", "xls"];
    //      $fileExtension = pathinfo($file['name'], PATHINFO_EXTENSION);
    //
    //      if (!in_array($fileExtension, $allowedExtensions)) {
    //           $cron_error[] = "Only Excel files (XLSX, XLS) are allowed.";
    //      }
    //
    //      if ($file['size'] > 10 * 1024 * 1024) {
    //           $cron_error[] = 'File size exceeds 10MB limit.';
    //      }
    // }

    // Check for initial file errors
    if (!empty($cron_error)) {
        errorreturn(implode(' | ', $cron_error));
    }
    
    $fullfilePath = $base_path_to_learning . $filePath;
    
    try {
        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($fullfilePath);         
        $worksheet = $spreadsheet->getActiveSheet();
        $rows = $worksheet->toArray();
        $data_to_import = [];

        foreach ($rows as $rowNumber => $row) {
            if ($rowNumber == 0) continue; // Skip header row

            // Skip empty rows
            if (empty(array_filter($row))) continue;

            $busStopData = [
                'institution_id' => $institution_id,
                'branch_name' => $row[0] ?? '',
                'stop_names' => $row[1] ?? '',
                'charges' => $row[2] ?? 0,
                'proximity' => $row[3] ?? 0
            ];

            // Validate required fields
            $requiredFields = ['branch_name', 'stop_names'];
            foreach ($requiredFields as $field) {
                if (empty($busStopData[$field])) {
                    $cron_error[] = "Row " . ($rowNumber + 1) . ": $field is required.";
                }
            }

            // Validate branch exists in the specific institution
            $stmt = $conn_rjllearning_specific->prepare(
                "SELECT id FROM branch WHERE name = ? AND institution_id = ?"
            );
            $stmt->execute([$busStopData['branch_name'], $busStopData['institution_id']]);
            $branch = $stmt->fetch(PDO::FETCH_ASSOC);

            if (!$branch) {
                $cron_error[] = "Row " . ($rowNumber + 1) . ": Branch not found in institution.";
            } else {
                $busStopData['branch_id'] = $branch['id'];
                // Validate stop name uniqueness within branch
                $stmt = $conn_rjllearning_specific->prepare(
                    "SELECT id FROM bus_stops 
                    WHERE stop_names = ? AND branch_id = ? AND institution_id = ?"
                );
                $stmt->execute([
                    $busStopData['stop_names'],
                    $busStopData['branch_id'],
                    $busStopData['institution_id']
                ]);
                
                if ($stmt->fetch()) {
                    $cron_error[] = "Row " . ($rowNumber + 1) . ": Stop name already exists in this branch.";
                }
            }

            // Validate charges is numeric
            if (!is_numeric($busStopData['charges'])) {
                $cron_error[] = "Row " . ($rowNumber + 1) . ": Charges must be a number.";
            }

            // Validate proximity is numeric
            if (!is_numeric($busStopData['proximity'])) {
                $cron_error[] = "Row " . ($rowNumber + 1) . ": Proximity must be a number.";
            }

            // Only add the row to the data_to_import if there are no errors for this row so far
            if (empty($cron_error)) {
                $data_to_import[] = $busStopData;
            }
        }

        if (!empty($cron_error)) {
            errorreturn(implode(' | ', $cron_error));
        }

        // Begin transaction and insert data if there are no errors
        $conn_rjllearning_specific->beginTransaction();
        $inserted_ids = 0;

        foreach ($data_to_import as $busStop) {
            // Insert into database
            $sql = "INSERT INTO bus_stops (
                institution_id, branch_id, stop_names, proximity, charges
            ) VALUES (?, ?, ?, ?, ?)";

            $stmt = $conn_rjllearning_specific->prepare($sql);
            $stmt->execute([
                $busStop['institution_id'],
                $busStop['branch_id'],
                $busStop['stop_names'],
                $busStop['proximity'],
                $busStop['charges']
            ]);
            $inserted_ids++;
        }

        $conn_rjllearning_specific->commit();
        // successreturn(['success' => true, 'message' => "Bus stops imported successfully.", 'inserted_count' => $inserted_ids]);
        return true;
        
    } catch (Exception $e) {
        if ($conn_rjllearning_specific->inTransaction()) {
            $conn_rjllearning_specific->rollBack();
        }
        errorreturn("Import failed: " . $e->getMessage());
    }
} else {
    errorreturn("Invalid function_type.");
}
?>
