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

require_once 'db/config.php';
require_once 'includes/callcenter_test_helpers.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 countryToInitials(string $value): string
{
    $value = trim($value);
    if ($value === '') {
        return '';
    }

    // Normalize accents to ASCII (e.g. 'Perú' -> 'Peru')
    $ascii = @iconv('UTF-8', 'ASCII//TRANSLIT//IGNORE', $value);
    if ($ascii === false || $ascii === null) {
        $ascii = $value;
    }

    $ascii = (string)$ascii;
    $ascii = preg_replace('/[^A-Za-z]/', '', $ascii);
    $initials = substr($ascii, 0, 3);
    return strtoupper($initials);
}

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(PDO $pdo, array $pedido): array
{
    $rows = cc_test_resolve_pedido_product_rows($pdo, $pedido, 'ruta_contraentrega_pedido_id');

    $out = [];
    foreach ($rows as $row) {
        $name = trim((string)($row['nombre'] ?? ''));
        $qty = (int)($row['cantidad'] ?? 0);
        if ($name === '') {
            continue;
        }
        $out[] = ['name' => $name, 'qty' => $qty > 0 ? $qty : 1];
    }

    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';
$is_admin = in_array($user_role, ['Administrador', 'admin'], true);

    $search_query = trim($_GET['q'] ?? '');
    $selected_delivery_date = trim((string)($_GET['fecha_entrega'] ?? ''));
    if ($selected_delivery_date !== '' && !preg_match('/^\d{4}-\d{2}-\d{2}$/', $selected_delivery_date)) {
        $selected_delivery_date = '';
    }
    if ($selected_delivery_date !== '') {
        [$deliveryYear, $deliveryMonth, $deliveryDay] = array_map('intval', explode('-', $selected_delivery_date));
        if (!checkdate($deliveryMonth, $deliveryDay, $deliveryYear)) {
            $selected_delivery_date = '';
        }
    }

    if (!$is_admin) {
        http_response_code(403);
        exit('Acceso denegado.');
    }

    // Exportar pedidos con fecha de entrega seleccionada; si no hay filtro, por defecto mañana
    $sql = "SELECT p.* FROM pedidos p WHERE p.estado IN ('RUTA_CONTRAENTREGA', '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%";
    }

    if ($selected_delivery_date !== '') {
        $sql .= " AND DATE(p.fecha_entrega) = ?";
        $params[] = $selected_delivery_date;
    } else {
        // Siempre: pedidos cuya fecha de entrega sea mañana
        $sql .= " AND DATE(p.fecha_entrega) = DATE_ADD(CURDATE(), INTERVAL 1 DAY)";
    }

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

    $courier_default = 1;
    $fecha_despacho = date('d/m/Y');

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

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

        $pais_raw = (string)($pedido['pais'] ?? 'Perú');
        $pais_code = countryToInitials($pais_raw);

        $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($pdo, $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'] ?? ''),
            $pais_code,
            (string)($pedido['sede_envio'] ?? ''),
            $provincia,
            $distrito,
            (string)($pedido['direccion_exacta'] ?? ''),
            (string)($pedido['referencia_domicilio'] ?? ''),
            (string)($pedido['coordenadas'] ?? ''),
            (string)$eanOut,
            $cantidadOut,
            $precioOut,
            $total,
            $courier_default,
            $nota_adicional_display,
            $fecha_despacho
        ];
    }

    // 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);
    if ($selected_delivery_date !== '') {
        $filename = 'ruta_contraentrega_' . $selected_delivery_date . '.xlsx';
    } else {
        $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.';
}