Importador de tarifas para el plugin «Correos Oficial»

<?php
/**
 * Importador de tarifas para "Correos Oficial"
 * - Modo Reemplazar (borra reglas del método seleccionado)
 * - Modo Añadir (agrega al final sin borrar)
 * - Inserción robusta (SQL preparado + backticks en `from`/`to`) y reporte de errores
 * Autor: Luis Muñoz + GPT
 */

add_action('admin_menu', function () {
    add_submenu_page(
        'woocommerce',
        'Importar tarifas Correos',
        'Correos → Importar tarifas',
        'manage_woocommerce',
        'correos-import-rates',
        'co_import_rates_admin_page'
    );
});

function co_import_rates_admin_page() {
    if ( ! class_exists('WC_Shipping_Zones') ) {
        echo '<div class="notice notice-error"><p>WooCommerce no está activo.</p></div>';
        return;
    }

    // Procesar envío
    if ( isset($_POST['co_import_submit']) && check_admin_referer('co_import_rates') ) {
        co_handle_import();
    }

    // Zonas (incluye "resto del mundo")
    $zones = WC_Shipping_Zones::get_zones();
    $zones[] = array('zone_id' => 0, 'zone_name' => 'Ubicaciones no cubiertas (Resto del mundo)');

    // Zona -> métodos (mostramos todos; podrás elegir el de Correos)
    $zone_methods = [];
    foreach ($zones as $z) {
        $zone = new WC_Shipping_Zone( $z['zone_id'] );
        $methods = $zone->get_shipping_methods();
        foreach ($methods as $m) {
            $zone_methods[$z['zone_id']][] = [
                'instance_id' => $m->instance_id,
                'method_id'   => $m->id,
                'label'       => $m->get_method_title() . ' ['.$m->id.'] (Instancia: ' . $m->instance_id . ')',
            ];
        }
    }

    echo '<div class="wrap"><h1>Importar tarifas Correos</h1>';
    echo '<p>CSV con columnas: <code>from,to,cost</code> (opcional: <code>condition,class</code>). ';
    echo 'Por defecto: <code>condition=weightkg</code>, <code>class=allproducts</code>. Decimales con <strong>punto</strong>.</p>';

    echo '<form method="post" enctype="multipart/form-data">';
    wp_nonce_field('co_import_rates');

    // Zona
    echo '<p><label><strong>Zona:</strong> ';
    echo '<select name="zone_id" required>';
    foreach ($zones as $z) {
        $zid = $z['zone_id'];
        $zname = isset($z['zone_name']) ? $z['zone_name'] : ('Zona ID '.$zid);
        echo '<option value="'.esc_attr($zid).'">'.esc_html($zname).' (ID '.$zid.')</option>';
    }
    echo '</select></label></p>';

    // Método
    echo '<p><label><strong>Método en la zona:</strong> ';
    echo '<select name="instance_id" required>';
    foreach ($zone_methods as $zid => $list) {
        if (empty($list)) continue;
        echo '<optgroup label="Zona ID '.$zid.'">';
        foreach ($list as $m) {
            echo '<option value="'.esc_attr($m['instance_id']).'">'.esc_html($m['label']).'</option>';
        }
        echo '</optgroup>';
    }
    echo '</select></label></p>';

    // Modo de importación
    echo '<p><strong>Modo de importación:</strong><br>';
    echo '<label><input type="radio" name="import_mode" value="append" checked> Añadir (no borrar reglas existentes)</label><br>';
    echo '<label><input type="radio" name="import_mode" value="replace"> Reemplazar (borrar reglas existentes del método seleccionado)</label>';
    echo '</p>';

    // Archivo
    echo '<p><label><strong>Archivo CSV:</strong> <input type="file" name="csv" accept=".csv" required></label></p>';

    echo '<p><button class="button button-primary" type="submit" name="co_import_submit" value="1">Importar</button></p>';
    echo '</form>';

    echo '<h2>Ejemplo CSV</h2><pre>from,to,cost,condition,class
0,0.25,7.20,weightkg,allproducts
0.25,0.5,7.55,weightkg,allproducts
0.5,1,8.25,weightkg,allproducts
</pre>';

    echo '</div>';
}

function co_table_has_column( $table, $col ) {
    global $wpdb;
    $exists = $wpdb->get_var( $wpdb->prepare("SHOW COLUMNS FROM `$table` LIKE %s", $col) );
    return ! empty($exists);
}

function co_handle_import() {
    if ( empty($_FILES['csv']['tmp_name']) ) {
        echo '<div class="notice notice-error"><p>No se recibió archivo.</p></div>';
        return;
    }
    $zone_id     = isset($_POST['zone_id']) ? (int) $_POST['zone_id'] : 0;
    $instance_id = isset($_POST['instance_id']) ? (int) $_POST['instance_id'] : 0;
    $mode        = isset($_POST['import_mode']) ? $_POST['import_mode'] : 'append';

    if ( ! $instance_id ) {
        echo '<div class="notice notice-error"><p>Instance ID inválido.</p></div>';
        return;
    }

    $file   = $_FILES['csv']['tmp_name'];
    $handle = fopen($file, 'r');
    if ( ! $handle ) {
        echo '<div class="notice notice-error"><p>No se pudo abrir el CSV.</p></div>';
        return;
    }

    // Detectar separador
    $first = fgets($handle);
    $delimiter = (substr_count($first, ';') > substr_count($first, ',')) ? ';' : ',';
    rewind($handle);

    $rows = [];
    $line = 0;
    while ( ($data = fgetcsv($handle, 0, $delimiter)) !== false ) {
        $line++;
        // detectar cabecera
        if ($line === 1 && preg_match('/from/i', (string)$data[0])) continue;

        $data = array_pad($data, 5, '');
        list($from, $to, $cost, $condition, $class) = $data;

        $from      = floatval(str_replace(',', '.', trim($from)));
        $to        = floatval(str_replace(',', '.', trim($to)));
        $cost      = floatval(str_replace(',', '.', trim($cost)));
        $condition = $condition ? sanitize_key(trim($condition)) : 'weightkg'; // Cambia a 'weight' si tu plugin lo exige
        $class     = $class ? sanitize_key(trim($class)) : 'allproducts';

        if ($to <= $from) continue;
        if (! in_array($condition, ['weightkg','weight','cost'], true)) $condition = 'weightkg';

        $rows[] = compact('from','to','cost','condition','class');
    }
    fclose($handle);

    if ( empty($rows) ) {
        echo '<div class="notice notice-warning"><p>El CSV no contiene filas válidas.</p></div>';
        return;
    }

    global $wpdb;
    $table = $wpdb->prefix . 'correos_oficial_shipping_method_rules';

    // ¿Existe columna priority?
    $has_priority = co_table_has_column($table, 'priority');

    // Contadores base según modo
    $base_rule   = 0;
    $base_prio   = 0;
    $deleted     = 0;
    $existing    = (int) $wpdb->get_var( $wpdb->prepare("SELECT COUNT(*) FROM `$table` WHERE instance_id=%d", $instance_id) );

    if ( $mode === 'replace' ) {
        $wpdb->delete($table, ['instance_id' => $instance_id]);
        $deleted   = $existing;
        $base_rule = 0;
        $base_prio = 0;
    } else {
        // append: averiguar máximos actuales para no colisionar
        $base_rule = (int) $wpdb->get_var( $wpdb->prepare("SELECT MAX(id_rule) FROM `$table` WHERE instance_id=%d", $instance_id) );
        if ($has_priority) {
            $base_prio = (int) $wpdb->get_var( $wpdb->prepare("SELECT MAX(priority) FROM `$table` WHERE instance_id=%d", $instance_id) );
        } else {
            $base_prio = $base_rule;
        }
    }

    // === Inserción robusta con SQL preparado (backticks en columnas reservadas) ===
    $inserted = 0;
    $rule_num = $base_rule;
    $prio_num = $base_prio;

    foreach ($rows as $r) {
        $rule_num++;
        $prio_num++;

        $cls  = $r['class'] ?: 'allproducts';
        $cond = $r['condition'] ?: 'weightkg';

        if ( $has_priority ) {
            $sql = "
                INSERT INTO `$table`
                (`instance_id`,`id_rule`,`class`,`condition`,`from`,`to`,`cost`,`priority`)
                VALUES (%d,%d,%s,%s,%f,%f,%f,%d)
            ";
            $prepared = $wpdb->prepare($sql,
                $instance_id, $rule_num, $cls, $cond,
                $r['from'], $r['to'], $r['cost'], $prio_num
            );
        } else {
            $sql = "
                INSERT INTO `$table`
                (`instance_id`,`id_rule`,`class`,`condition`,`from`,`to`,`cost`)
                VALUES (%d,%d,%s,%s,%f,%f,%f)
            ";
            $prepared = $wpdb->prepare($sql,
                $instance_id, $rule_num, $cls, $cond,
                $r['from'], $r['to'], $r['cost']
            );
        }

        $ok = $wpdb->query($prepared);
        if ( false === $ok ) {
            echo '<div class="notice notice-error"><p>Error SQL: '.esc_html($wpdb->last_error).'</p></div>';
            // Si quieres parar en el primer error, descomenta:
            // return;
        } else {
            $inserted += (int)$ok;
        }
    }

    $msg  = 'Importación completada. Modo: ' . ( $mode === 'replace' ? 'Reemplazar' : 'Añadir' ) . '. ';
    if ($mode === 'replace') $msg .= 'Reglas eliminadas: ' . intval($deleted) . '. ';
    $msg .= 'Reglas añadidas: ' . intval($inserted) . '. ';
    $msg .= 'Instance ID: ' . intval($instance_id) . ' (Zona ID ' . intval($zone_id) . ').';

    echo '<div class="notice notice-success"><p>'.esc_html($msg).'</p></div>';
}

/* Código para hacer el diagnóstico */

add_action('admin_init', function () {
    if ( ! current_user_can('manage_woocommerce') || ! isset($_GET['correos_diag']) ) return;
    global $wpdb;

    echo "<pre>";
    echo "=== Zonas y métodos (instance_id) ===\n";
    $zones = WC_Shipping_Zones::get_zones();
    $zones[] = ['zone_id' => 0, 'zone_name' => 'Resto del mundo'];
    foreach ($zones as $z) {
        $zone = new WC_Shipping_Zone($z['zone_id']);
        echo "\nZona {$zone->get_zone_name()} (ID {$z['zone_id']})\n";
        foreach ($zone->get_shipping_methods() as $m) {
            echo "- instance_id={$m->instance_id} | id={$m->id} | title={$m->get_method_title()} | enabled={$m->enabled}\n";
        }
    }

    echo "\n=== Tablas que parecen de Correos ===\n";
    $tables = $wpdb->get_col("SHOW TABLES");
    foreach ($tables as $t) {
        if (stripos($t,'correos')!==false) echo "$t\n";
    }

    // Intenta detectar la tabla de reglas más probable
    $candidates = array_filter($tables, fn($t)=>stripos($t,'correos')!==false && stripos($t,'rule')!==false);
    foreach ($candidates as $tbl) {
        echo "\n--- Conteos por instance_id en $tbl ---\n";
        $rows = $wpdb->get_results("SELECT instance_id, COUNT(*) as n FROM `$tbl` GROUP BY instance_id ORDER BY instance_id");
        if ($rows) {
            foreach ($rows as $r) echo "instance_id={$r->instance_id} -> {$r->n} reglas\n";
        } else {
            echo "(sin datos)\n";
        }
    }

    echo "\n=== Fin ===";
    echo "</pre>";
    exit;
});