<?php
/**
 * POST /api/products/bulk_import.php
 * multipart/form-data: csv (file)
 *
 * Business-only. Expected CSV header row (order doesn't matter, matched by name):
 *   name, category, description, quantity, original_price, selling_price,
 *   expiry_date, location, city, country, barcode
 *
 * Images are NOT part of this import - CSV can't carry binary files. Each
 * imported listing starts with the Ragreen placeholder thumbnail; add real
 * photos afterward per-listing via My Listings > Edit, same as any other
 * product. Every successfully created row earns the normal per-listing
 * loyalty points, same as creating one manually.
 */

require_once __DIR__ . '/../config/database.php';
require_once __DIR__ . '/../includes/response.php';
require_once __DIR__ . '/../includes/auth.php';
require_once __DIR__ . '/../includes/loyalty.php';

only_post_allowed();
$userId = require_login();
require_role(['business']);

if (empty($_FILES['csv']) || $_FILES['csv']['error'] !== UPLOAD_ERR_OK) {
    send_error('Please choose a CSV file to upload.', 422);
}

$file = $_FILES['csv'];
if ($file['size'] > 2 * 1024 * 1024) {
    send_error('CSV file is too large (2MB max).', 422);
}

$handle = fopen($file['tmp_name'], 'r');
if (!$handle) {
    send_error('Could not read the uploaded file.', 422);
}

$header = fgetcsv($handle);
if (!$header) {
    fclose($handle);
    send_error('The CSV file appears to be empty.', 422);
}
$header = array_map(fn($h) => strtolower(trim($h)), $header);

$required = ['name', 'quantity', 'selling_price'];
foreach ($required as $col) {
    if (!in_array($col, $header, true)) {
        fclose($handle);
        send_error("CSV is missing a required column: $col", 422);
    }
}

$pdo = Database::connect();

// Category name -> id lookup (case-insensitive), for matching the CSV's free-text category column.
$catStmt = $pdo->query('SELECT id, name FROM categories WHERE is_active = 1');
$categoryByName = [];
foreach ($catStmt->fetchAll() as $c) {
    $categoryByName[strtolower($c['name'])] = (int) $c['id'];
}

$MAX_ROWS = 500;
$rowsTotal = 0;
$rowsCreated = 0;
$errors = [];

$insertProduct = $pdo->prepare('
    INSERT INTO products (owner_id, category_id, barcode, name, description, quantity, original_price, selling_price, expiry_date, location, city, country, status)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, "active")
');

while (($row = fgetcsv($handle)) !== false) {
    if (count(array_filter($row, fn($v) => trim((string) $v) !== '')) === 0) {
        continue; // skip fully blank lines
    }
    $rowsTotal++;
    if ($rowsTotal > $MAX_ROWS) {
        $errors[] = "Row $rowsTotal: stopped - file exceeds the $MAX_ROWS row limit.";
        break;
    }

    $data = array_combine($header, array_pad($row, count($header), ''));
    $rowNum = $rowsTotal + 1; // +1 to account for header row, matches what they'd see in a spreadsheet

    $name = trim($data['name'] ?? '');
    $quantity = (int) ($data['quantity'] ?? 0);
    $sellingPrice = (float) ($data['selling_price'] ?? 0);

    if ($name === '') {
        $errors[] = "Row $rowNum: missing product name.";
        continue;
    }
    if ($quantity <= 0) {
        $errors[] = "Row $rowNum ($name): quantity must be greater than 0.";
        continue;
    }
    if ($sellingPrice <= 0) {
        $errors[] = "Row $rowNum ($name): selling_price must be greater than 0.";
        continue;
    }

    $expiryDate = trim($data['expiry_date'] ?? '');
    if ($expiryDate !== '' && !DateTime::createFromFormat('Y-m-d', $expiryDate)) {
        $errors[] = "Row $rowNum ($name): expiry_date must be YYYY-MM-DD - skipped.";
        continue;
    }

    $categoryName = strtolower(trim($data['category'] ?? ''));
    $categoryId = $categoryByName[$categoryName] ?? null;

    $originalPrice = !empty($data['original_price']) ? (float) $data['original_price'] : null;

    try {
        $insertProduct->execute([
            $userId, $categoryId, trim($data['barcode'] ?? '') ?: null,
            $name, trim($data['description'] ?? ''), $quantity, $originalPrice, $sellingPrice,
            $expiryDate ?: null, trim($data['location'] ?? '') ?: null,
            trim($data['city'] ?? '') ?: null, trim($data['country'] ?? '') ?: null,
        ]);
        award_loyalty_points($pdo, $userId, points_per_new_listing($pdo), 'Published a new listing (bulk import)');
        $rowsCreated++;
    } catch (Throwable $e) {
        $errors[] = "Row $rowNum ($name): could not be saved.";
    }
}
fclose($handle);

$rowsFailed = $rowsTotal - $rowsCreated;
$stmt = $pdo->prepare(
    'INSERT INTO product_import_batches (owner_id, filename, rows_total, rows_created, rows_failed, error_summary) VALUES (?, ?, ?, ?, ?, ?)'
);
$stmt->execute([$userId, $file['name'], $rowsTotal, $rowsCreated, $rowsFailed, $errors ? implode("\n", $errors) : null]);

send_json(true, "Imported $rowsCreated of $rowsTotal row(s).", [
    'rows_total' => $rowsTotal,
    'rows_created' => $rowsCreated,
    'rows_failed' => $rowsFailed,
    'errors' => array_slice($errors, 0, 20), // cap what we send back, full list is in error_summary
]);
