<?php
ob_start();
header('Content-Type: application/json; charset=utf-8');

require_once __DIR__ . '/../../includes/auth.php';
require_once __DIR__ . '/../../classes/Database.php';

$autoloader = __DIR__ . '/../../vendor/autoload.php';
if (file_exists($autoloader)) {
    require_once $autoloader;
}

try {
    if (session_status() === PHP_SESSION_NONE) {
        session_start();
    }

    $database = new Database();
    $conn = $database->getConnection();
    if (!$conn instanceof PDO) {
        throw new RuntimeException('Database connection is unavailable.');
    }

    $auth = new Auth($conn);
    if (!isset($_SESSION['user_id']) || !in_array($_SESSION['role'] ?? '', ['admin', 'warehouse'], true)) {
        http_response_code(403);
        throw new RuntimeException('Unauthorized.');
    }
    if (!class_exists('\\PhpOffice\\PhpSpreadsheet\\IOFactory')) {
        throw new RuntimeException('Excel import support is unavailable.');
    }

    $sourceBranchId = ($_SESSION['role'] ?? '') === 'admin'
        ? 7
        : (int)$auth->getUserBranch($_SESSION['user_id']);
    if ($sourceBranchId <= 0) {
        throw new RuntimeException('Your account is not assigned to a source branch.');
    }

    if (empty($_FILES['excel_file']) || $_FILES['excel_file']['error'] !== UPLOAD_ERR_OK) {
        http_response_code(422);
        throw new RuntimeException('Select a valid Excel or CSV file.');
    }

    $extension = strtolower(pathinfo((string)$_FILES['excel_file']['name'], PATHINFO_EXTENSION));
    if (!in_array($extension, ['xlsx', 'xls', 'csv'], true)) {
        http_response_code(422);
        throw new RuntimeException('Only .xlsx, .xls, and .csv files are supported.');
    }

    $worksheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($_FILES['excel_file']['tmp_name'])->getActiveSheet();
    $highestRow = $worksheet->getHighestDataRow();
    $highestColumn = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($worksheet->getHighestDataColumn());
    if ($highestRow > 10001) {
        http_response_code(422);
        throw new RuntimeException('The import is limited to 10,000 items per shipment.');
    }

    $headers = [];
    for ($column = 1; $column <= $highestColumn; $column++) {
        $header = strtolower(trim((string)$worksheet->getCellByColumnAndRow($column, 1)->getValue()));
        $header = str_replace([' ', '-'], '_', $header);
        if ($header !== '') {
            if (!in_array($header, ['unique_code', 'selling_price'], true)) {
                http_response_code(422);
                throw new RuntimeException('The template may only contain unique_code and selling_price columns.');
            }
            if (isset($headers[$header])) {
                http_response_code(422);
                throw new RuntimeException("The {$header} column appears more than once.");
            }
            $headers[$header] = $column;
        }
    }
    if (!isset($headers['unique_code'], $headers['selling_price'])) {
        http_response_code(422);
        throw new RuntimeException('The file must contain unique_code and selling_price columns.');
    }

    $requested = [];
    $errors = [];
    for ($row = 2; $row <= $highestRow; $row++) {
        $uniqueCode = trim((string)$worksheet->getCellByColumnAndRow($headers['unique_code'], $row)->getFormattedValue());
        $priceValue = $worksheet->getCellByColumnAndRow($headers['selling_price'], $row)->getCalculatedValue();
        if ($uniqueCode === '' && ($priceValue === null || trim((string)$priceValue) === '')) {
            continue;
        }

        $priceText = str_replace([',', 'MWK', 'mwk'], '', trim((string)$priceValue));
        if ($uniqueCode === '') {
            $errors[] = "Row {$row}: unique_code is required.";
            continue;
        }
        if (!is_numeric($priceText) || (float)$priceText <= 0) {
            $errors[] = "Row {$row}: selling_price must be greater than zero.";
            continue;
        }

        $key = strtolower($uniqueCode);
        if (isset($requested[$key])) {
            $errors[] = "Row {$row}: duplicate unique_code {$uniqueCode}.";
            continue;
        }
        $requested[$key] = ['unique_code' => $uniqueCode, 'selling_price' => round((float)$priceText, 2)];
    }

    if (!$requested) {
        http_response_code(422);
        throw new RuntimeException($errors ? implode(' ', array_slice($errors, 0, 5)) : 'The spreadsheet contains no transfer items.');
    }

    $codes = array_column($requested, 'unique_code');
    $placeholders = implode(',', array_fill(0, count($codes), '?'));
    $sql = "SELECT i.item_id, i.item_name, i.item_details, i.unique_code, i.condition_status, i.serial_number,
                   i.warranty_months, i.created_at AS date_entered
            FROM items i
            WHERE i.unique_code IN ({$placeholders})
              AND i.current_location_id = ?
              AND i.shelf_status = 'on_shelf'
              AND NOT EXISTS (SELECT 1 FROM sold_items s WHERE s.item_id = i.item_id)
              AND NOT EXISTS (SELECT 1 FROM missing_items m WHERE m.unique_code = i.unique_code)";
    $stmt = $conn->prepare($sql);
    $params = array_merge($codes, [$sourceBranchId]);
    foreach ($params as $index => $value) {
        $stmt->bindValue($index + 1, $value, $index === count($params) - 1 ? PDO::PARAM_INT : PDO::PARAM_STR);
    }
    $stmt->execute();

    $found = [];
    foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $item) {
        $found[strtolower((string)$item['unique_code'])] = $item;
    }

    $items = [];
    foreach ($requested as $key => $input) {
        if (!isset($found[$key])) {
            $errors[] = "Unique code {$input['unique_code']} is not available in the source branch.";
            continue;
        }
        $item = $found[$key];
        $items[] = [
            'item_id' => (int)$item['item_id'],
            'item_name' => (string)($item['item_name'] ?? ''),
            'item_details' => (string)($item['item_details'] ?? ''),
            'unique_code' => (string)$item['unique_code'],
            'selling_price' => $input['selling_price'],
            'condition_status' => (string)($item['condition_status'] ?? ''),
            'serial_number' => (string)($item['serial_number'] ?? ''),
            'warranty_months' => (int)($item['warranty_months'] ?? 0),
            'date_entered' => !empty($item['date_entered']) ? date('Y-m-d', strtotime($item['date_entered'])) : '',
        ];
    }

    if (!$items) {
        http_response_code(422);
        throw new RuntimeException(implode(' ', array_slice($errors, 0, 8)) ?: 'No import rows matched available inventory.');
    }

    if (ob_get_level() > 0) {
        ob_clean();
    }
    echo json_encode([
        'success' => true,
        'items' => $items,
        'errors' => array_slice($errors, 0, 50),
        'selected_count' => count($items),
    ], JSON_INVALID_UTF8_SUBSTITUTE);
} catch (Throwable $e) {
    error_log('Shipment transfer spreadsheet import failed: ' . $e->getMessage());
    if (ob_get_level() > 0) {
        ob_clean();
    }
    $status = http_response_code();
    $message = $e->getMessage();
    if ($status < 400 || $status >= 500) {
        http_response_code(500);
        $message = 'Unable to process the transfer spreadsheet.';
    }
    echo json_encode(['success' => false, 'message' => $message], JSON_INVALID_UTF8_SUBSTITUTE);
}
