<?php
session_start();
include 'layouts/dbconn.php';

error_reporting(E_ALL);
ini_set('display_errors', 1);

// Check authentication
if (!isset($_SESSION['user_id'])) {
   die(json_encode(['success' => false, 'message' => 'User not logged in.']));
}

$allowedExtensions = ['xlsx', 'csv'];
$allowedImportTypes = ['transit_shipment', 'local_shipment'];

// Validate file upload
if (!isset($_FILES['file']) || $_FILES['file']['error'] !== UPLOAD_ERR_OK) {
   die(json_encode(['success' => false, 'message' => 'File upload failed.']));
}

// Validate import type
if (!isset($_POST['import_type']) || !in_array($_POST['import_type'], $allowedImportTypes)) {
   die(json_encode(['success' => false, 'message' => 'Invalid import type.']));
}

$importType = $_POST['import_type'];
$userId = $_SESSION['user_id'];
$file = $_FILES['file'];
$fileName = $file['name'];
$fileTmpName = $file['tmp_name'];
$fileSize = $file['size'];
$fileExt = strtolower(pathinfo($fileName, PATHINFO_EXTENSION));

// Validate file type and size
if (!in_array($fileExt, $allowedExtensions)) {
   die(json_encode(['success' => false, 'message' => 'Please upload a valid Excel (.xlsx) or CSV (.csv) file.']));
}

if ($fileSize > 10 * 1024 * 1024) {
   die(json_encode(['success' => false, 'message' => 'File size exceeds 10MB limit.']));
}

// Set up upload directory
$uploadDir = 'uploads/imports/';
if (!file_exists($uploadDir)) {
   mkdir($uploadDir, 0777, true);
}

$newFileName = uniqid('import_') . '_' . date('Ymd') . '.' . $fileExt;
$uploadPath = $uploadDir . $newFileName;

if (!move_uploaded_file($fileTmpName, $uploadPath)) {
   die(json_encode(['success' => false, 'message' => 'Failed to save uploaded file.']));
}

try {
   $conn->begin_transaction();

   // Create import batch record
   $stmt = $conn->prepare("
       INSERT INTO import_batches 
       (filename, import_type, status, created_by, created_at) 
       VALUES (?, ?, 'pending', ?, NOW())
   ");
   
   $stmt->bind_param("ssi", $fileName, $importType, $userId);
   if (!$stmt->execute()) {
       throw new Exception("Failed to create import batch: " . $stmt->error);
   }
   
   $batchId = $conn->insert_id;

   // Read file contents
   $rows = [];
   if ($fileExt === 'xlsx') {
       if (($handle = fopen($uploadPath, "r")) !== FALSE) {
           while (($data = fgetcsv($handle)) !== FALSE) {
               $rows[] = $data;
           }
           fclose($handle);
           array_shift($rows); // Remove header
       } else {
           throw new Exception("Could not read XLSX file");
       }
   } else {
       if (($handle = fopen($uploadPath, "r")) !== FALSE) {
           while (($data = fgetcsv($handle)) !== FALSE) {
               $rows[] = $data;
           }
           fclose($handle);
           array_shift($rows); // Remove header
       } else {
           throw new Exception("Could not read CSV file");
       }
   }

   $totalRecords = count($rows);

   // Update batch with record count
   $updateStmt = $conn->prepare("
       UPDATE import_batches 
       SET total_records = ? 
       WHERE batch_id = ?
   ");
   
   $updateStmt->bind_param("ii", $totalRecords, $batchId);
   $updateStmt->execute();

   // Get field configuration
   $configStmt = $conn->prepare("
       SELECT * FROM import_config 
       WHERE import_type = ? 
       ORDER BY ordinal_position
   ");
   
   $configStmt->bind_param("s", $importType); 
   $configStmt->execute();
   $configResult = $configStmt->get_result();
   
   $fieldConfigs = [];
   while ($row = $configResult->fetch_assoc()) {
       $fieldConfigs[$row['field_name']] = $row;
   }

   // Insert records
   $recordStmt = $conn->prepare("
       INSERT INTO import_records 
       (batch_id, row_index, record_data, status) 
       VALUES (?, ?, ?, 'pending')
   ");

   foreach ($rows as $index => $row) {
       $recordData = [];
       foreach ($fieldConfigs as $fieldName => $config) {
           $colMapping = $config['column_mapping'];
           $colIndex = ord(strtoupper($colMapping)) - 65;
           $recordData[$fieldName] = isset($row[$colIndex]) ? $row[$colIndex] : null;
       }
       
       $jsonData = json_encode($recordData);
       $rowNumber = $index + 1;
       
       $recordStmt->bind_param("iis", $batchId, $rowNumber, $jsonData);
       if (!$recordStmt->execute()) {
           throw new Exception("Failed to insert record: " . $recordStmt->error);
       }
   }

   $conn->commit();

   echo json_encode([
       'success' => true,
       'message' => 'File uploaded successfully.',
       'batchId' => $batchId,
       'totalRecords' => $totalRecords
   ]);

} catch (Exception $e) {
   $conn->rollback();
   error_log("Error processing file: " . $e->getMessage());
   
   if (file_exists($uploadPath)) {
       unlink($uploadPath);
   }
   
   die(json_encode([
       'success' => false,
       'message' => 'Error processing file: ' . $e->getMessage()
   ]));
}
?>