<?php
// bulk_upload.php
header('Content-Type: application/json');
require '../../config/db.php';
session_start();
$userId = $_SESSION['user_id'] ?? 1;

if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
    echo json_encode(['status' => 'error', 'message' => 'Invalid Request']);
    exit;
}

if (!isset($_FILES['bulk_file']) || $_FILES['bulk_file']['error'] !== UPLOAD_ERR_OK) {
    echo json_encode(['status' => 'error', 'message' => 'File upload failed']);
    exit;
}

$fileTmpPath = $_FILES['bulk_file']['tmp_name'];
$fileName = $_FILES['bulk_file']['name'];

require '../../vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\IOFactory;

try {
    $internalErrors = libxml_use_internal_errors(true);
    $spreadsheet = IOFactory::load($fileTmpPath);
    libxml_use_internal_errors($internalErrors);
    $sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, false);
} catch (Exception $e) {
    echo json_encode(['status' => 'error', 'message' => 'Error reading file: ' . $e->getMessage()]);
    exit;
}

if (empty($sheetData) || count($sheetData) < 2) {
    echo json_encode(['status' => 'error', 'message' => 'File is empty or has no data rows']);
    exit;
}

// Create Job Record
try {
    $stmt = $pdo->prepare("INSERT INTO tbl_bulkupload_jobs (filename, status, created_by, created_at) VALUES (:fn, 'Processing', :uid, NOW())");
    $stmt->execute([':fn' => $fileName, ':uid' => $userId]);
    $jobId = $pdo->lastInsertId();
} catch (Exception $e) {
    echo json_encode(['status' => 'error', 'message' => 'Database Error: ' . $e->getMessage()]);
    exit;
}

$headers = array_shift($sheetData);
$headers = array_pad($headers, 31, '');

$successCount = 0;
$failCount = 0;
$resultMap = [];

// Group rows by Ref ID (Index 3)
$groups = [];
foreach ($sheetData as $i => $data) {
    $data = array_pad($data, 31, '');
    if (trim(implode('', $data)) === '')
        continue;

    $refId = trim($data[3]);
    if (empty($refId)) {
        $groupKey = '_AUTO_' . $i . '_' . bin2hex(random_bytes(4));
    } else {
        $groupKey = 'REF_' . $refId;
    }
    $groups[$groupKey][] = ['index' => $i, 'data' => $data];
}

// Use the CORRECT service router from booking module
require_once '../../api/booking/services/courier_service.php';

foreach ($groups as $groupKey => $groupRows) {
    $errorMsg = '';
    $errCol = -1;
    $status = 'Success';
    $waybillNo = '';

    try {
        $firstData = $groupRows[0]['data'];

        $branchName = trim($firstData[0]);
        $bookingType = trim($firstData[1]);
        $date = trim($firstData[2]);
        $refId = trim($firstData[3]);
        if (empty($refId))
            $refId = 'BULK-' . time() . '-' . rand(100, 999);
        $courierName = trim($firstData[4]);

        // Shipper
        $sName = trim($firstData[5]);
        $sPhone = trim($firstData[6]);
        $sPin = trim($firstData[7]);
        $sAddr = trim($firstData[8]);
        $sCity = trim($firstData[9]);
        $sState = trim($firstData[10]);

        // Consignee
        $cName = trim($firstData[11]);
        $cPhone = trim($firstData[12]);
        $cEmail = trim($firstData[13]);
        $cGst = trim($firstData[14]);
        $cAddr = trim($firstData[15]);
        $cPin = trim($firstData[16]);
        $cCity = trim($firstData[17]);
        $cState = trim($firstData[18]);

        $payMode = trim($firstData[19]);
        $codAmt = floatval($firstData[20]);
        $prodDesc = trim($firstData[21]);

        $invNo = trim($firstData[27]);
        $invVal = floatval($firstData[28]);
        $ewayNo = trim($firstData[29]);
        $shipMode = trim($firstData[30]);
        if (empty($shipMode))
            $shipMode = 'Surface';
        $shipMode = ucfirst(strtolower($shipMode));

        // Aggregate Packages
        $totalBoxes = 0;
        $totalActualWeight = 0;
        $totalChargedWeight = 0;
        $maxL = 0;
        $maxW = 0;
        $maxH = 0;
        $packageDetails = [];

        foreach ($groupRows as $gr) {
            $d = $gr['data'];
            $l = floatval($d[22]);
            $w = floatval($d[23]);
            $h = floatval($d[24]);
            $wtPerBox = floatval($d[25]);
            $box = intval($d[26]);
            if ($box < 1)
                $box = 1;

            if ($l <= 0 || $w <= 0 || $h <= 0 || $wtPerBox <= 0) {
                $errCol = 22;
                throw new Exception("Invalid dimensions/weight in packages");
            }

            $volWtPerBox = ($l * $w * $h) / 5000;
            $chgWtPerBox = max($wtPerBox, $volWtPerBox);

            $totalBoxes += $box;
            $totalActualWeight += ($wtPerBox * $box);
            $totalChargedWeight += ($chgWtPerBox * $box);

            if ($l > $maxL)
                $maxL = $l;
            if ($w > $maxW)
                $maxW = $w;
            if ($h > $maxH)
                $maxH = $h;

            $packageDetails[] = [
                'length' => $l,
                'width' => $w,
                'height' => $h,
                'boxes' => $box,
                'actual_weight' => $wtPerBox,
                'charged_weight' => $chgWtPerBox
            ];
        }

        // Validation
        if (!$branchName) {
            $errCol = 0;
            throw new Exception("Missing Branch Name");
        }
        if (!$courierName) {
            $errCol = 4;
            throw new Exception("Missing Courier Name");
        }
        if (!$cName) {
            $errCol = 11;
            throw new Exception("Missing Consignee Name");
        }
        if (!$cPhone) {
            $errCol = 12;
            throw new Exception("Missing Consignee Phone");
        }
        if (!$cAddr) {
            $errCol = 15;
            throw new Exception("Missing Consignee Address");
        }
        if (!$cPin) {
            $errCol = 16;
            throw new Exception("Missing Consignee Pin");
        }

        $tempPayMode = strtoupper($payMode);
        if ($tempPayMode === 'COD')
            $payMode = 'COD';
        elseif ($tempPayMode === 'PREPAID')
            $payMode = 'Prepaid';
        else {
            $errCol = 19;
            throw new Exception("Invalid Payment Mode");
        }
        if ($payMode !== 'COD')
            $codAmt = 0;

        // Resolve IDs
        $stmt = $pdo->prepare("SELECT id FROM tbl_branch WHERE branch_name LIKE :name OR branch_code = :code LIMIT 1");
        $stmt->execute([':name' => "%$branchName%", ':code' => $branchName]);
        $branch = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$branch) {
            $errCol = 0;
            throw new Exception("Branch not found");
        }

        $stmt = $pdo->prepare("SELECT * FROM tbl_courier_partner WHERE partner_name LIKE :name OR partner_code = :code LIMIT 1");
        $stmt->execute([':name' => "%$courierName%", ':code' => $courierName]);
        $courierData = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$courierData) {
            $errCol = 4;
            throw new Exception("Courier not found");
        }

        $stmt = $pdo->prepare("SELECT id, name FROM tbl_pickup_points WHERE branch_id = :bid AND name LIKE :pname LIMIT 1");
        $stmt->execute([':bid' => $branch['id'], ':pname' => "%$sName%"]);
        $pPoint = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$pPoint) {
            $stmt = $pdo->prepare("SELECT id, name FROM tbl_pickup_points WHERE branch_id = :bid LIMIT 1");
            $stmt->execute([':bid' => $branch['id']]);
            $pPoint = $stmt->fetch(PDO::FETCH_ASSOC);
        }
        if (!$pPoint) {
            $errCol = 5;
            throw new Exception("No Pickup Point config found");
        }

        // API Call using proper syncBookingWithCourier
        $shipmentRequest = [
            'booking_ref_id' => $refId,
            'consignee_name' => $cName,
            'consignee_phone' => $cPhone,
            'consignee_email' => $cEmail,
            'consignee_gst' => $cGst,
            'consignee_address' => $cAddr,
            'consignee_pin' => $cPin,
            'consignee_city' => ucfirst(strtolower($cCity)),
            'consignee_state' => ucfirst(strtolower($cState)),
            'consignee_country' => 'India',
            'payment_mode' => $payMode,
            'cod_amount' => $codAmt,
            'product_desc' => $prodDesc,
            'quantity' => $totalBoxes,
            'weight' => round($totalActualWeight * 1000, 2),
            'length' => $maxL,
            'width' => $maxW,
            'height' => $maxH,
            'shipping_mode' => $shipMode,
            'pickup_location_name' => $pPoint['name'],
            'invoice_no' => $invNo,
            'invoice_date' => (!empty($date) && strtotime($date)) ? date('Y-m-d', strtotime($date)) : date('Y-m-d'),
            'invoice_value' => $invVal,
            'ewaybill_no' => $ewayNo,
            'package_details' => $packageDetails
        ];

        $syncResult = syncBookingWithCourier($pdo, $courierData, $shipmentRequest);
        if (empty($syncResult['success'])) {
            throw new Exception($syncResult['message'] ?? 'Courier sync failed');
        }

        $waybillNo = $syncResult['waybill'];
        $apiResponseJson = json_encode($syncResult['api_response'] ?? []);

        // DB Insert
        $sql = "INSERT INTO tbl_bookings (
            booking_ref_id, waybill_no, courier_id, pickup_point_id,
            consignee_name, consignee_phone, consignee_email, consignee_gst, consignee_address, consignee_pin,
            consignee_city, consignee_state, consignee_country,
            shipper_name, shipper_phone, shipper_address, shipper_pin, shipper_city, shipper_state,
            payment_mode, cod_amount, weight, length, width, height,
            shipping_mode, product_desc, package_details, quantity, is_mps,
            invoice_no, invoice_value, ewaybill_no,
            rto_name, rto_phone, rto_address,
            api_response, last_status, created_at, created_by
        ) VALUES (
            :ref, :wb, :cid, :pid,
            :cname, :cphone, :cemail, :cgst, :caddr, :cpin,
            :ccity, :cstate, 'India',
            :sname, :sphone, :saddr, :spin, :scity, :sstate,
            :pmode, :cod, :wt, :len, :wid, :hgt,
            :smode, :desc, :pkg, :qty, :mps,
            :invNo, :invVal, :eway,
            :rtname, :rtphone, :rtaddr,
            :api, 'Created', NOW(), :uid
        )";

        $stmt = $pdo->prepare($sql);
        $stmt->execute([
            ':ref' => $refId,
            ':wb' => $waybillNo,
            ':cid' => $courierData['id'],
            ':pid' => $pPoint['id'],
            ':cname' => $cName,
            ':cphone' => $cPhone,
            ':cemail' => $cEmail,
            ':cgst' => $cGst,
            ':caddr' => $cAddr,
            ':cpin' => $cPin,
            ':ccity' => ucfirst(strtolower($cCity)),
            ':cstate' => ucfirst(strtolower($cState)),
            ':sname' => $sName,
            ':sphone' => $sPhone,
            ':saddr' => $sAddr,
            ':spin' => $sPin,
            ':scity' => $sCity,
            ':sstate' => $sState,
            ':pmode' => $payMode,
            ':cod' => $codAmt,
            ':wt' => round($totalActualWeight * 1000, 2),
            ':len' => $maxL,
            ':wid' => $maxW,
            ':hgt' => $maxH,
            ':smode' => $shipMode,
            ':desc' => $prodDesc,
            ':pkg' => json_encode($packageDetails),
            ':qty' => $totalBoxes,
            ':mps' => ($totalBoxes > 1 ? 1 : 0),
            ':invNo' => $invNo,
            ':invVal' => $invVal,
            ':eway' => $ewayNo,
            ':rtname' => $sName,
            ':rtphone' => $sPhone,
            ':rtaddr' => $sAddr,
            ':api' => $apiResponseJson,
            ':uid' => $userId
        ]);

        $bookingId = $pdo->lastInsertId();
        if ($waybillNo) {
            $pdo->prepare("INSERT INTO tbl_tracking (booking_id, waybill_no, scan_type, remarks) VALUES (?, ?, 'Booking Created', 'Bulk Upload')")->execute([$bookingId, $waybillNo]);
        }
        $successCount += count($groupRows);
        $status = 'Success';
    } catch (Exception $e) {
        $status = 'Failed';
        $errorMsg = $e->getMessage();
        $failCount += count($groupRows);
    }

    foreach ($groupRows as $gr) {
        $idx = $gr['index'];
        $rowData = $gr['data'];
        $rowData[] = $waybillNo;
        $rowData[] = $status;
        $rowData[] = $errorMsg;
        $rowData[] = ($status === 'Failed') ? $errCol : -1;
        $resultMap[$idx] = $rowData;
    }
}

$resultHeader = $headers;
$resultHeader[] = 'Waybill';
$resultHeader[] = 'Status';
$resultHeader[] = 'Remarks';
$resultRows = [$resultHeader];
ksort($resultMap);
foreach ($resultMap as $row) {
    $resultRows[] = $row;
}

$finalStatus = ($failCount > 0) ? (($successCount > 0) ? 'Completed with Errors' : 'Failed') : 'Completed';
$pdo->prepare("UPDATE tbl_bulkupload_jobs SET status = :st, total_records = :tot, success_count = :suc, failure_count = :fail, result_file = :res, updated_at = NOW() WHERE id = :id")
    ->execute([
        ':st' => $finalStatus,
        ':tot' => ($successCount + $failCount),
        ':suc' => $successCount,
        ':fail' => $failCount,
        ':res' => json_encode($resultRows),
        ':id' => $jobId
    ]);

echo json_encode(['status' => 'success', 'message' => "Processed " . ($successCount + $failCount) . " shipments", 'job_id' => $jobId]);
exit;