<?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) 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_routes') {
    // print_r($postdata['function_type']);die();
// 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.';
//     }
// }
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 = [];
    
    // First collect all route data and group by route_name and branch_name
    $routesData = [];
    foreach ($rows as $rowNumber => $row) {
        if ($rowNumber == 0) continue; // Skip header row

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

        $routeData = [
            'institution_id' => $institution_id,
            'branch_name' => trim($row[0] ?? ''),
            'route_name' => trim($row[1] ?? ''),
            'stop_name' => trim($row[2] ?? '')
        ];

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

        // Create unique key combining branch and route name
        $routeKey = $routeData['branch_name'] . '|' . $routeData['route_name'];

        if (!isset($routesData[$routeKey])) {
            $routesData[$routeKey] = [
                'institution_id' => $routeData['institution_id'],
                'branch_name' => $routeData['branch_name'],
                'route_name' => $routeData['route_name'],
                'stop_names' => []
            ];
        }
        $routesData[$routeKey]['stop_names'][] = $routeData['stop_name'];
    }

    // Validate branches and routes
    $routesToProcess = [];
    foreach ($routesData as $routeKey => $routeData) {
        // Validate branch exists
        $stmt = $conn_rjllearning_specific->prepare(
            "SELECT id FROM branch WHERE name = ? AND institution_id = ?"
        );
        $stmt->execute([$routeData['branch_name'], $routeData['institution_id']]);
        $branch = $stmt->fetch(PDO::FETCH_ASSOC);

        if (!$branch) {
            $cron_error[] = "Branch '{$routeData['branch_name']}' not found for route '{$routeData['route_name']}'";
        } else {
            $routeData['branch_id'] = $branch['id'];

            // Validate all stop names exist in this specific branch
            $stopIds = [];
            foreach ($routeData['stop_names'] as $stopName) {
                $stmt = $conn_rjllearning_specific->prepare(
                    "SELECT id FROM bus_stops 
                    WHERE stop_names = ? 
                    AND branch_id = ? 
                    AND institution_id = ?"
                );
                $stmt->execute([
                    $stopName,
                    $routeData['branch_id'],
                    $routeData['institution_id']
                ]);
                $stop = $stmt->fetch(PDO::FETCH_ASSOC);

                if (!$stop) {
                    $cron_error[] = "Stop '$stopName' not found in branch '{$routeData['branch_name']}' for route '{$routeData['route_name']}'";
                } else {
                    $stopIds[] = $stop['id'];
                }
            }
            if(empty($cron_error)) {
                 // Check if route exists in this specific branch
                $stmt = $conn_rjllearning_specific->prepare(
                    "SELECT id, stops_name FROM routes 
                    WHERE route_name = ? 
                    AND branch_id = ? 
                    AND institution_id = ?"
                );
                $stmt->execute([
                    $routeData['route_name'],
                    $routeData['branch_id'],
                    $routeData['institution_id']
                ]);
                $existingRoute = $stmt->fetch(PDO::FETCH_ASSOC);
    
                if ($existingRoute) {
                    $existingStops = json_decode($existingRoute['stops_name'], true) ?? [];
                    $mergedStops = array_unique(array_merge($existingStops, $stopIds));
                    $routeData['stop_ids'] = json_encode(array_values($mergedStops));
                    $routeData['route_exists'] = true;
                    $routeData['route_id'] = $existingRoute['id'];
                } else {
                    $routeData['stop_ids'] = json_encode($stopIds);
                    $routeData['route_exists'] = false;
                }
            }
        }
        
        if(empty($cron_error)) {
            $routesToProcess[] = $routeData;
        }

    }
    
    // Check if validation found any errors
    if (!empty($cron_error)) {
        errorreturn(implode(' | ', $cron_error));
    }

    $conn_rjllearning_specific->beginTransaction();

    $changesMade = false;
    $affectedRoutes = 0;

    // Process each route
    foreach ($routesToProcess as $routeData) {
        if ($routeData['route_exists']) {
            // Check if stops have actually changed
            $stmt = $conn_rjllearning_specific->prepare(
                "SELECT stops_name FROM routes WHERE id = ?"
            );
            $stmt->execute([$routeData['route_id']]);
            $currentRoute = $stmt->fetch(PDO::FETCH_ASSOC);

            $currentStops = json_decode($currentRoute['stops_name'], true) ?? [];
            $newStops = json_decode($routeData['stop_ids'], true) ?? [];

            sort($currentStops);
            sort($newStops);

            if ($currentStops !== $newStops) {
                $sql = "UPDATE routes SET 
                    stops_name = ?, 
                    date_created = CURRENT_DATE, 
                    time_created = CURRENT_TIME 
                    WHERE id = ?";
                $stmt = $conn_rjllearning_specific->prepare($sql);
                $stmt->execute([
                    $routeData['stop_ids'],
                    $routeData['route_id']
                ]);
                $changesMade = true;
                $affectedRoutes++;
            }
        } else {
            // Insert new route
            $sql = "INSERT INTO routes (
                institution_id, branch_id, route_name, stops_name, 
                added_by, date_created, time_created, status
            ) VALUES (?, ?, ?, ?, ?, CURRENT_DATE, CURRENT_TIME, 1)";

            $stmt = $conn_rjllearning_specific->prepare($sql);
            $stmt->execute([
                $routeData['institution_id'],
                $routeData['branch_id'],
                $routeData['route_name'],
                $routeData['stop_ids'],
                $username
            ]);
            $changesMade = true;
            $affectedRoutes++;
        }
    }

    if (!$changesMade) {
        $conn_rjllearning_specific->rollBack();
        errorreturn("No changes were made - routes already exist with these stops.");
    }

    $conn_rjllearning_specific->commit();
    // successreturn(array('successmsg' => "Successfully imported bus routes. Affected rows: " . $affectedRoutes));
    return true;
} catch (Exception $e) {
    if ($conn_rjllearning_specific->inTransaction()) {
        $conn_rjllearning_specific->rollBack();
    }
    errorreturn("Import failed: " . $e->getMessage());
   }
}
?>
