<?php
/**
 * Backfill de geocodificacion de clientes.
 *
 * Uso recomendado:
 * php scripts/backfill_customer_geocoding.php --dry-run --limit=20
 * php scripts/backfill_customer_geocoding.php --limit=50
 */

require_once __DIR__ . '/../core/config.php';
require_once __DIR__ . '/../core/database.php';
require_once __DIR__ . '/../services/geo/GoogleMapsService.php';

$options = getopt('', array('dry-run', 'limit::', 'offset::'));
$dryRun = array_key_exists('dry-run', $options);
$limit = isset($options['limit']) ? max(1, min(500, (int)$options['limit'])) : 50;
$offset = isset($options['offset']) ? max(0, (int)$options['offset']) : 0;

$maps = new GoogleMapsService();
if (!$maps->isConfigured()) {
    fwrite(STDERR, "GOOGLE_MAPS_API_KEY no está configurada. Abortando.\n");
    exit(1);
}

$pdo = db();
$stmt = $pdo->prepare("\n    SELECT id, customer_name, address, district, city, region, country\n    FROM crm_customers\n    WHERE deleted_at IS NULL\n      AND (latitude IS NULL OR longitude IS NULL)\n      AND (address IS NOT NULL OR city IS NOT NULL OR district IS NOT NULL)\n    ORDER BY id ASC\n    LIMIT {$limit} OFFSET {$offset}\n");
$stmt->execute();
$customers = $stmt->fetchAll(PDO::FETCH_ASSOC);

$updated = 0;
foreach ($customers as $customer) {
    $query = trim(implode(' ', array_filter(array(
        $customer['address'] ?? '',
        $customer['district'] ?? '',
        $customer['city'] ?? '',
        $customer['region'] ?? '',
        $customer['country'] ?? 'Chile'
    ))));

    if ($query === '') continue;

    $place = $maps->geocode($query, $customer['country'] ?? 'Chile');
    if (!$place) {
        echo "SKIP {$customer['id']} {$customer['customer_name']} :: sin resultado\n";
        if (!$dryRun) {
            $fail = $pdo->prepare("
                UPDATE crm_customers
                SET geocode_status = 'failed',
                    geocode_source = 'google_maps_backfill',
                    geocode_query = ?,
                    geocoded_at = NOW(),
                    updated_at = updated_at
                WHERE id = ?
            ");
            $fail->execute(array($query, (int)$customer['id']));
        }
        continue;
    }

    echo ($dryRun ? 'DRY ' : 'UPD ') . $customer['id'] . ' ' . $customer['customer_name'] . ' => ' . $place['formatted_address'] . "\n";

    if (!$dryRun) {
        $update = $pdo->prepare("\n            UPDATE crm_customers\n            SET address = COALESCE(NULLIF(?, ''), address),\n                district = COALESCE(NULLIF(?, ''), district),\n                city = COALESCE(NULLIF(?, ''), city),\n                region = COALESCE(NULLIF(?, ''), region),\n                country = COALESCE(NULLIF(?, ''), country),\n                latitude = ?,\n                longitude = ?,\n                geocode_status = 'ok',\n                geocode_source = 'google_maps_backfill',\n                geocode_query = ?,\n                geocoded_at = NOW(),\n                updated_at = updated_at\n            WHERE id = ?\n        ");
        $update->execute(array(
            $place['address'],
            $place['district'],
            $place['city'],
            $place['region'],
            $place['country'],
            $place['latitude'],
            $place['longitude'],
            $query,
            (int)$customer['id']
        ));
        $updated++;
    }

    usleep(150000);
}

echo "Procesados: " . count($customers) . " | Actualizados: {$updated}" . ($dryRun ? " | DRY-RUN" : "") . "\n";
