<?php
session_start();
if (!isset($_SESSION['user_id'])) {
    header('Location: login.php');
    exit;
}

require_once 'db/config.php';
require_once 'vendor/autoload.php';

use Shuchkin\SimpleXLSXGen;

function splitProvinciaDistrito(?string $value): array
{
    $value = trim((string)$value);
    if ($value === '') {
        return ['', ''];
    }

    $clean = preg_replace('/\s+/', ' ', $value);
    $parts = preg_split('/\s*[\/\-,|]\s*/', $clean, 2);

    if (is_array($parts) and count($parts) === 2) {
        return [trim($parts[0]), trim($parts[1])];
    }

    return [$clean, ''];
}

function parseTotalQuantity(mixed $value): int
{
    if (is_int($value)) {
        return $value;
    }

    if (is_float($value)) {
        return (int)$value;
    }

    $s = trim((string)$value);
    if ($s === '') {
        return 0;
    }

    // Old/legacy format can be: "1+2+3"
    if (strpos($s, '+') !== false) {
        $sum = 0;
        foreach (explode('+', $s) as $part) {
            $part = trim($part);
            if ($part === '') {
                continue;
            }
            if (is_numeric($part)) {
                $sum += (int)$part;
                continue;
            }
            if (preg_match('/\d+/', $part, $m)) {
                $sum += (int)$m[0];
            }
        }
        return $sum;
    }

    if (is_numeric($s)) {
        return (int)$s;
    }

    if (preg_match('/\d+/', $s, $m)) {
        return (int)$m[0];
    }

    return 0;
}

function formatPriceSegment(float $value): string
{
    $rounded = round($value, 2);
    if (abs($rounded - (int)$rounded) < 0.00001) {
        return (string)(int)$rounded;
    }

    // Keep up to 2 decimals, but remove trailing zeros
    $s = number_format($rounded, 2, '.', '');
    $s = rtrim(rtrim($s, '0'), '.');
    return $s;
}

function extractProductNames(array $pedido): array
{
    $names = [];
    $notas = (string)($pedido['notas'] ?? '');

    if (preg_match('/Detalle de productos:\\s*(.+)$/mi', $notas, $match)) {
        $items = explode(',', $match[1]);
        foreach ($items as $item) {
            $name = preg_replace('/\\s*\\(x\\d+\\)\\s*$/i', '', trim($item));
            if ($name !== '') {
                $names[] = $name;
            }
        }
    }

    if (empty($names) && !empty($pedido['producto'])) {
        foreach (explode(',', (string)$pedido['producto']) as $item) {
            $name = trim($item);
            if ($name !== '') {
                $names[] = $name;
            }
        }
    }

    return array_values(array_unique($names));
}

function extractProductDetailsWithQuantities(array $pedido): array
{
    $notas = (string)($pedido['notas'] ?? '');

    // Prefer parsing from "Detalle de productos:" (it stores quantities as (xN))
    if (preg_match_all('/Detalle de productos:\\s*(.+)$/mi', $notas, $matches) && !empty($matches[1])) {
        $lastLine = trim((string)end($matches[1]));
        if ($lastLine !== '') {
            $parts = preg_split('/\\s*,\\s*/u', $lastLine);
            $out = [];

            if (is_array($parts)) {
                foreach ($parts as $part) {
                    $part = trim((string)$part);
                    if ($part === '') {
                        continue;
                    }

                    // Expected format: "Nombre de producto (x2)"
                    if (preg_match('/^(.*?)\\s*\\(x\\s*(\\d+)\\s*\\)\\s*$/iu', $part, $m)) {
                        $name = trim((string)$m[1]);
                        $qty = (int)$m[2];
                    } else {
                        $name = $part;
                        $qty = 1;
                    }

                    if ($name !== '') {
                        if ($qty <= 0) {
                            $qty = 1;
                        }
                        $out[] = ['name' => $name, 'qty' => $qty];
                    }
                }
            }

            if (!empty($out)) {
                return $out;
            }
        }
    }

    // Fallback: use "producto" + "cantidad" (best-effort)
    $productoStr = (string)($pedido['producto'] ?? '');
    $names = [];
    foreach (preg_split('/\\s*,\\s*/u', $productoStr) as $n) {
        $n = trim((string)$n);
        if ($n !== '') {
            $names[] = $n;
        }
    }

    if (empty($names)) {
        return [];
    }

    $cantidad_total = parseTotalQuantity($pedido['cantidad'] ?? 0);

    // Legacy possibility: "cantidad" can be like "1+2"
    $cantidadField = $pedido['cantidad'] ?? '';
    $qtyParts = [];
    if (is_string($cantidadField) && strpos(trim($cantidadField), '+') !== false) {
        $qtyParts = array_map('trim', explode('+', $cantidadField));
    }

    $out = [];
    $countNames = count($names);
    foreach ($names as $i => $name) {
        if ($countNames === 1) {
            $qty = $cantidad_total > 0 ? $cantidad_total : 1;
        } elseif (!empty($qtyParts) && isset($qtyParts[$i]) && is_numeric($qtyParts[$i])) {
            $qty = (int)$qtyParts[$i];
            if ($qty <= 0) {
                $qty = 1;
            }
        } else {
            $qty = 1;
        }

        $out[] = ['name' => $name, 'qty' => $qty];
    }

    return $out;
}

function getEanMap(PDO $pdo, array $productNames): array
{
    $productNames = array_values(array_filter(array_map('trim', $productNames)));
    if (empty($productNames)) {
        return [];
    }

    $uniqueNames = array_values(array_unique($productNames));
    $placeholders = implode(',', array_fill(0, count($uniqueNames), '?'));
    $stmt = $pdo->prepare("SELECT nombre, ean FROM products WHERE nombre IN ($placeholders)");
    $stmt->execute($uniqueNames);

    $map = [];
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $nombre = (string)($row['nombre'] ?? '');
        $ean = trim((string)($row['ean'] ?? ''));
        if ($nombre !== '' && $ean !== '') {
            $map[$nombre] = $ean;
        }
    }

    return $map;
}

function getEansForProducts(PDO $pdo, array $productNames): string
{
    if (empty($productNames)) {
        return '';
    }

    $placeholders = implode(',', array_fill(0, count($productNames), '?'));
    $stmt = $pdo->prepare("SELECT nombre, ean FROM products WHERE nombre IN ($placeholders)");
    $stmt->execute($productNames);

    $map = [];
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $ean = trim((string)($row['ean'] ?? ''));
        if ($ean !== '') {
            $map[(string)($row['nombre'] ?? '')] = $ean;
        }
    }

    $eans = [];
    foreach ($productNames as $name) {
        if (!empty($map[$name])) {
            $eans[] = $map[$name];
        }
    }

    return implode('|', array_values(array_unique($eans)));
}

try {
    $pdo = db();

    $user_id = $_SESSION['user_id'];
    $user_role = $_SESSION['user_role'] ?? 'Asesor';

    $search_query = trim($_GET['q'] ?? '');

    // Exportar solo los pedidos con fecha de entrega "mañana"
    $date_condition = "DATE(p.fecha_entrega) = DATE_ADD(CURDATE(), INTERVAL 1 DAY)";

    $sql = "SELECT p.* FROM pedidos p WHERE p.estado IN ('RUTA_CONTRAENTREGA', 'PENDIENTE', 'NO CONTESTO, VOLVER A LLAMAR', 'NO CONTESTO, DEVOLVER LLAMADA', 'CANCELADO', 'REPROGRAMADO', 'ENTREGA EXITOSA', 'RETORNADO')";
    $params = [];

    if ($user_role === 'Asesor') {
        $sql .= " AND p.asesor_id = ?";
        $params[] = $user_id;
    }

    if ($search_query !== '') {
        $sql .= " AND (p.nombre_completo LIKE ? OR p.dni_cliente LIKE ? OR p.celular LIKE ?)";
        $params[] = "%$search_query%";
        $params[] = "%$search_query%";
        $params[] = "%$search_query%";
    }

    // Siempre: pedidos cuya fecha de entrega sea mañana
    $sql .= " AND $date_condition";

    $sql .= " ORDER BY p.created_at DESC";
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $pedidos = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $rows = [];
    $rows[] = [
        'Nombre y apellido',
        'Celular',
        'Pais',
        'Departamento',
        'Provincia',
        'Distrito',
        'Direccion',
        'Referencia',
        'Coordenadas',
        'Codigo EAN',
        'Cantidad',
        'Precio',
        'Total',
        'DE DEDICATORIA / OBS.'
    ];

    foreach ($pedidos as $pedido) {
        [$provincia, $distrito] = splitProvinciaDistrito($pedido['codigo_rastreo'] ?? '');

        $nota_adicional = trim((string)($pedido['nota_adicional'] ?? ''));
        if ($nota_adicional === '') {
            $nota_adicional = trim((string)($pedido['descargo'] ?? ''));
        }
        $nota_adicional_display = $nota_adicional !== '' ? $nota_adicional : '';

        $cantidad_total = parseTotalQuantity($pedido['cantidad'] ?? 0);
        $total = (float)($pedido['monto_total'] ?? 0);
        $unit_price_rounded = $cantidad_total > 0 ? round($total / $cantidad_total, 2) : 0;

        $eanOut = '';
        $cantidadOut = $cantidad_total;
        $precioOut = $unit_price_rounded;

        $details = extractProductDetailsWithQuantities($pedido);

        $eanSegments = [];
        $qtySegments = [];
        $priceSegments = [];

        if (!empty($details)) {
            $names = [];
            foreach ($details as $d) {
                if (!empty($d['name'])) {
                    $names[] = (string)$d['name'];
                }
            }

            $eanMap = getEanMap($pdo, $names);

            foreach ($details as $d) {
                $name = (string)($d['name'] ?? '');
                if ($name === '') {
                    continue;
                }

                $qty = (int)($d['qty'] ?? 0);
                if ($qty <= 0) {
                    $qty = 1;
                }

                $ean = trim((string)($eanMap[$name] ?? ''));
                if ($ean === '') {
                    continue;
                }

                $eanSegments[] = $ean;
                $qtySegments[] = $qty;
                $priceSegments[] = formatPriceSegment((float)$unit_price_rounded); // unit price repeated
            }
        }

        // If we have segment data, format with pipes (no spaces)
        if (count($eanSegments) > 1) {
            $eanOut = implode('|', $eanSegments);
            $cantidadOut = implode('|', $qtySegments);
            $precioOut = implode('|', $priceSegments);
        } elseif (count($eanSegments) === 1) {
            $eanOut = $eanSegments[0];
            $cantidadOut = $qtySegments[0] ?? $cantidad_total;
            $precioOut = $unit_price_rounded;
        } else {
            // Fallback (keep old behavior, but with "|" formatting)
            $eanOut = getEansForProducts($pdo, extractProductNames($pedido));
            $cantidadOut = $cantidad_total;
            $precioOut = $cantidad_total > 0 ? round($total / $cantidad_total, 2) : 0;
        }

        $rows[] = [
            (string)($pedido['nombre_completo'] ?? ''),
            (string)($pedido['celular'] ?? ''),
            'Perú',
            (string)($pedido['sede_envio'] ?? ''),
            $provincia,
            $distrito,
            (string)($pedido['direccion_exacta'] ?? ''),
            (string)($pedido['referencia_domicilio'] ?? ''),
            (string)($pedido['coordenadas'] ?? ''),
            (string)$eanOut,
            $cantidadOut,
            $precioOut,
            $total,
            $nota_adicional_display
        ];
    }

    // Right-align certain columns (Codigo EAN, Cantidad, Precio, Total) in the exported Excel
    $rightAlignedCols = [9, 10, 11, 12];
    foreach ($rows as $rIdx => &$row) {
        foreach ($rightAlignedCols as $cIdx) {
            if (!array_key_exists($cIdx, $row)) {
                continue;
            }
            if (is_string($row[$cIdx]) && strpos($row[$cIdx], '<right>') !== false) {
                continue;
            }
            $row[$cIdx] = '<right>' . (string)$row[$cIdx] . '</right>';
        }
    }
    unset($row);
    $filename = 'ruta_contraentrega_' . date('Y-m-d_H-i') . '.xlsx';
    SimpleXLSXGen::fromArray($rows, 'Ruta Contraentrega')->downloadAs($filename);
    exit;
} catch (Throwable $e) {
    error_log('Error exportando Ruta Contraentrega: ' . $e->getMessage());
    header('HTTP/1.1 500 Internal Server Error');
    echo 'Error al generar el Excel de Ruta Contraentrega.';
}