<?php
/**
 * admin_config.php
 * ============================================================
 * BReport v2 · ADMIN CONFIGURACIÓN (TODO EN UN SOLO ARCHIVO)
 * - Requiere login y rol admin
 * - CRUD:
 *    * Localidades (locations)
 *    * Productos (products) por empresa (erp_id opcional)
 *    * Canales de salida (outbound_channels) por empresa
 *    * Conceptos Calidad (quality_concepts) por empresa (MERMA/SCRAP)
 *    * Usuarios (users) + asignación de roles + acceso empresa/localidad
 * - API en el mismo archivo vía ?action=...
 * - UI Bootstrap responsive (PC/tablet/cel) con cards tipo botón
 * ============================================================
 */

declare(strict_types=1);

require_once __DIR__ . '/includes/auth.php';
require_once __DIR__ . '/includes/helpers.php';
require_once __DIR__ . '/includes/csrf.php';

require_login();
if (!has_role('admin')) {
  http_response_code(403);
  exit('Acceso denegado (admin).');
}

$pdo = pdo();
$action = (string)($_GET['action'] ?? '');


/* ===================== Roles base de BReport ===================== */
function ensure_breport_required_roles(PDO $pdo): void {
  $roles = [
    ['ventas', 'Ventas'],
    ['supervisor', 'Supervisor'],
    ['reparto', 'Reparto'],
  ];

  $st = $pdo->prepare("
    INSERT INTO roles (code, name)
    VALUES (?, ?)
    ON DUPLICATE KEY UPDATE name = VALUES(name)
  ");

  foreach ($roles as [$code, $name]) {
    $st->execute([$code, $name]);
  }
}

ensure_breport_required_roles($pdo);


/* ===================== Reglas de almacén por producto ===================== */
function ensure_product_warehouse_rules_schema(PDO $pdo): void {
  $pdo->exec("
    CREATE TABLE IF NOT EXISTS product_warehouse_rules (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      company_id BIGINT UNSIGNED NOT NULL,
      location_id BIGINT UNSIGNED NOT NULL,
      product_id BIGINT UNSIGNED NOT NULL,
      default_finished_warehouse_id BIGINT UNSIGNED DEFAULT NULL,
      default_sales_warehouse_id BIGINT UNSIGNED DEFAULT NULL,
      merma_warehouse_id BIGINT UNSIGNED DEFAULT NULL,
      aprovechamiento_warehouse_id BIGINT UNSIGNED DEFAULT NULL,
      cubicado_warehouse_id BIGINT UNSIGNED DEFAULT NULL,
      notes VARCHAR(255) DEFAULT NULL,
      active TINYINT(1) NOT NULL DEFAULT 1,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT NULL,
      PRIMARY KEY (id),
      UNIQUE KEY uq_pwr_scope_product (company_id, location_id, product_id),
      KEY idx_pwr_product (product_id),
      KEY idx_pwr_location (location_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
  ");
}
ensure_product_warehouse_rules_schema($pdo);

/* ===================== Auditoría de correcciones inventory_moves ===================== */
function ensure_inventory_move_admin_edits_schema(PDO $pdo): void {
  $pdo->exec("
    CREATE TABLE IF NOT EXISTS inventory_move_admin_edits (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      inventory_move_id BIGINT UNSIGNED NOT NULL,
      company_id BIGINT UNSIGNED NOT NULL,
      location_id BIGINT UNSIGNED NOT NULL,
      old_data JSON NULL,
      new_data JSON NULL,
      reason VARCHAR(255) DEFAULT NULL,
      edited_by BIGINT UNSIGNED NOT NULL,
      edited_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY (id),
      KEY idx_imae_move (inventory_move_id),
      KEY idx_imae_scope_date (company_id, location_id, edited_at),
      KEY idx_imae_user (edited_by)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
  ");
}
ensure_inventory_move_admin_edits_schema($pdo);

function inv_move_snapshot_json(array $row): string {
  return json_encode([
    'id' => (int)($row['id'] ?? 0),
    'company_id' => (int)($row['company_id'] ?? 0),
    'location_id' => (int)($row['location_id'] ?? 0),
    'warehouse_id' => isset($row['warehouse_id']) ? (int)$row['warehouse_id'] : null,
    'work_date' => (string)($row['work_date'] ?? ''),
    'created_at' => (string)($row['created_at'] ?? ''),
    'product_id' => (int)($row['product_id'] ?? 0),
    'qty' => (float)($row['qty'] ?? 0),
    'move_type' => (string)($row['move_type'] ?? ''),
    'ref_type' => (string)($row['ref_type'] ?? ''),
    'ref_id' => $row['ref_id'] ?? null,
    'notes' => (string)($row['notes'] ?? ''),
    'created_by' => isset($row['created_by']) ? (int)$row['created_by'] : null,
  ], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
}


/* ===================== Helpers API ===================== */
function jexit($data, int $code=200): void {
  http_response_code($code);
  header('Content-Type: application/json; charset=utf-8');
  echo json_encode($data, JSON_UNESCAPED_UNICODE);
  exit;
}
function read_json(): array {
  $raw = file_get_contents('php://input');
  $d = json_decode($raw ?: '[]', true);
  return is_array($d) ? $d : [];
}
function is_post_json(): bool {
  return ($_SERVER['REQUEST_METHOD'] ?? '') !== 'GET';
}

/* ============ Helpers DB para Roles/Accesos (self-contained) ============ */
function db_roles_list(PDO $pdo): array {
  $st = $pdo->query("SELECT id, code, name FROM roles ORDER BY name");
  return $st->fetchAll();
}
function db_user_roles(PDO $pdo, int $userId): array {
  $st = $pdo->prepare("
    SELECT r.code
    FROM user_roles ur
    JOIN roles r ON r.id = ur.role_id
    WHERE ur.user_id = ?
  ");
  $st->execute([$userId]);
  return array_map(fn($x) => (string)$x['code'], $st->fetchAll());
}
function db_set_user_roles(PDO $pdo, int $userId, array $roleCodes): void {
  $roleCodes = array_values(array_unique(array_filter(array_map('trim', $roleCodes))));
  $pdo->beginTransaction();
  try {
    $pdo->prepare("DELETE FROM user_roles WHERE user_id=?")->execute([$userId]);
    if ($roleCodes) {
      $in = implode(',', array_fill(0, count($roleCodes), '?'));
      $st = $pdo->prepare("SELECT id, code FROM roles WHERE code IN ($in)");
      $st->execute($roleCodes);
      $rows = $st->fetchAll();
      $ins = $pdo->prepare("INSERT INTO user_roles(user_id, role_id) VALUES(?,?)");
      foreach ($rows as $r) $ins->execute([$userId, (int)$r['id']]);
    }
    $pdo->commit();
  } catch (Throwable $e) {
    $pdo->rollBack();
    throw $e;
  }
}
function db_user_access(PDO $pdo, int $userId): array {
  $st = $pdo->prepare("SELECT company_id FROM user_company_access WHERE user_id=?");
  $st->execute([$userId]);
  $companyIds = array_map('intval', array_column($st->fetchAll(), 'company_id'));

  $st2 = $pdo->prepare("SELECT location_id FROM user_location_access WHERE user_id=?");
  $st2->execute([$userId]);
  $locationIds = array_map('intval', array_column($st2->fetchAll(), 'location_id'));

  return ['company_ids'=>$companyIds, 'location_ids'=>$locationIds];
}
function db_set_user_access(PDO $pdo, int $userId, array $companyIds, array $locationIds): void {
  $companyIds = array_values(array_unique(array_filter(array_map('intval', $companyIds))));
  $locationIds = array_values(array_unique(array_filter(array_map('intval', $locationIds))));
  $pdo->beginTransaction();
  try {
    $pdo->prepare("DELETE FROM user_company_access WHERE user_id=?")->execute([$userId]);
    $pdo->prepare("DELETE FROM user_location_access WHERE user_id=?")->execute([$userId]);

    if ($companyIds) {
      $ins = $pdo->prepare("INSERT INTO user_company_access(user_id, company_id) VALUES(?,?)");
      foreach ($companyIds as $cid) $ins->execute([$userId, $cid]);
    }
    if ($locationIds) {
      $ins2 = $pdo->prepare("INSERT INTO user_location_access(user_id, location_id) VALUES(?,?)");
      foreach ($locationIds as $lid) $ins2->execute([$userId, $lid]);
    }
    $pdo->commit();
  } catch (Throwable $e) {
    $pdo->rollBack();
    throw $e;
  }
}



/* ============ Helpers Inventario/Ajuste Almacenes ============ */
function inv_adjust_code_ok(string $code): bool {
  return hash_equals('Blanquita26', trim($code));
}

function inv_balance_sql(): string {
  return "COALESCE(SUM(CASE WHEN im.move_type IN ('IN','ADJ') THEN im.qty WHEN im.move_type='OUT' THEN -im.qty ELSE 0 END),0)";
}

/* ===================== API ROUTES ===================== */
if ($action !== '') {
  ensure_session();

  if (is_post_json()) {
    $hdr = $_SERVER['HTTP_X_CSRF'] ?? '';
    if ($hdr === '') jexit(['ok'=>false,'error'=>'Falta X-CSRF'], 419);
    csrf_verify($hdr);
  }

  // --------- list companies ----------
  if ($action === 'companies') {
    $st = $pdo->query("SELECT id, name, code, active FROM companies ORDER BY name");
    jexit(['ok'=>true,'rows'=>$st->fetchAll()]);
  }

  // --------- locations list ----------
  if ($action === 'locations_list') {
    $companyId = (int)($_GET['company_id'] ?? 0);
    $st = $pdo->prepare("
      SELECT l.id, l.company_id, c.name AS company_name, l.name, l.code, l.timezone, l.active, l.created_at
      FROM locations l
      JOIN companies c ON c.id=l.company_id
      WHERE (?=0 OR l.company_id=?)
      ORDER BY c.name, l.name
    ");
    $st->execute([$companyId, $companyId]);
    jexit(['ok'=>true,'rows'=>$st->fetchAll()]);
  }

  // --------- locations save ----------
  if ($action === 'locations_save') {
    $d = read_json();
    $id = (int)($d['id'] ?? 0);
    $companyId = (int)($d['company_id'] ?? 0);
    $name = trim((string)($d['name'] ?? ''));
    $code = trim((string)($d['code'] ?? ''));
    $tz   = trim((string)($d['timezone'] ?? 'America/Monterrey'));
    $active = (int)($d['active'] ?? 1) ? 1 : 0;

    if ($companyId<=0 || $name==='' || $code==='') {
      jexit(['ok'=>false,'error'=>'company_id, name y code son requeridos'], 400);
    }

    if ($id>0) {
      $st = $pdo->prepare("UPDATE locations SET company_id=?, name=?, code=?, timezone=?, active=? WHERE id=?");
      $st->execute([$companyId, $name, $code, $tz, $active, $id]);
      jexit(['ok'=>true,'id'=>$id]);
    } else {
      $st = $pdo->prepare("INSERT INTO locations(company_id,name,code,timezone,active) VALUES(?,?,?,?,?)");
      $st->execute([$companyId, $name, $code, $tz, $active]);
      jexit(['ok'=>true,'id'=>(int)$pdo->lastInsertId()]);
    }
  }

  // --------- warehouses list ----------
  if ($action === 'warehouses_list') {
    $companyId = (int)($_GET['company_id'] ?? 0);
    $locationId = (int)($_GET['location_id'] ?? 0);
    $st = $pdo->prepare("
      SELECT w.id, w.company_id, c.name AS company_name,
             w.location_id, l.name AS location_name, l.code AS location_code,
             w.code, w.name, w.active, w.created_at
      FROM warehouses w
      JOIN companies c ON c.id = w.company_id
      JOIN locations l ON l.id = w.location_id
      WHERE (? = 0 OR w.company_id = ?)
        AND (? = 0 OR w.location_id = ?)
      ORDER BY c.name, l.name, FIELD(w.code,'CF1','CF2','CF3','CUB','MERMA','APROV-MERMA','SOB-CUB'), w.code, w.name
    ");
    $st->execute([$companyId, $companyId, $locationId, $locationId]);
    jexit(['ok'=>true,'rows'=>$st->fetchAll()]);
  }

  // --------- warehouses save ----------
  if ($action === 'warehouses_save') {
    $d = read_json();
    $id = (int)($d['id'] ?? 0);
    $companyId = (int)($d['company_id'] ?? 0);
    $locationId = (int)($d['location_id'] ?? 0);
    $code = strtoupper(trim((string)($d['code'] ?? '')));
    $name = trim((string)($d['name'] ?? ''));
    $active = (int)($d['active'] ?? 1) ? 1 : 0;

    if ($companyId <= 0 || $locationId <= 0 || $code === '' || $name === '') {
      jexit(['ok'=>false,'error'=>'Empresa, localidad, código y nombre son requeridos.'], 400);
    }

    $st = $pdo->prepare("SELECT id FROM locations WHERE id=? AND company_id=? LIMIT 1");
    $st->execute([$locationId, $companyId]);
    if (!$st->fetch()) {
      jexit(['ok'=>false,'error'=>'La localidad no pertenece a la empresa seleccionada.'], 400);
    }

    if ($id > 0) {
      $st = $pdo->prepare("
        SELECT id FROM warehouses
        WHERE company_id=? AND location_id=? AND UPPER(code)=UPPER(?) AND id<>?
        LIMIT 1
      ");
      $st->execute([$companyId, $locationId, $code, $id]);
      if ($st->fetch()) jexit(['ok'=>false,'error'=>'Ya existe un almacén con ese código en esta localidad.'], 409);

      $st = $pdo->prepare("
        UPDATE warehouses
        SET company_id=?, location_id=?, code=?, name=?, active=?
        WHERE id=?
      ");
      $st->execute([$companyId, $locationId, $code, $name, $active, $id]);
      jexit(['ok'=>true,'id'=>$id]);
    } else {
      $st = $pdo->prepare("
        SELECT id FROM warehouses
        WHERE company_id=? AND location_id=? AND UPPER(code)=UPPER(?)
        LIMIT 1
      ");
      $st->execute([$companyId, $locationId, $code]);
      if ($st->fetch()) jexit(['ok'=>false,'error'=>'Ya existe un almacén con ese código en esta localidad.'], 409);

      $st = $pdo->prepare("
        INSERT INTO warehouses(company_id, location_id, code, name, active, created_at)
        VALUES(?, ?, ?, ?, ?, NOW())
      ");
      $st->execute([$companyId, $locationId, $code, $name, $active]);
      jexit(['ok'=>true,'id'=>(int)$pdo->lastInsertId()]);
    }
  }

  // --------- products list ----------
  if ($action === 'products_list') {
    $companyId = (int)($_GET['company_id'] ?? 0);
    $st = $pdo->prepare("
      SELECT p.id, p.company_id, c.name AS company_name,
             p.sku, p.name, p.erp_id, p.uom, p.active, p.created_at
      FROM products p
      JOIN companies c ON c.id=p.company_id
      WHERE (?=0 OR p.company_id=?)
      ORDER BY c.name, p.sku
    ");
    $st->execute([$companyId, $companyId]);
    jexit(['ok'=>true,'rows'=>$st->fetchAll()]);
  }

  // --------- products save ----------
  if ($action === 'products_save') {
    $d = read_json();
    $id = (int)($d['id'] ?? 0);
    $companyId = (int)($d['company_id'] ?? 0);
    $sku  = trim((string)($d['sku'] ?? ''));
    $name = trim((string)($d['name'] ?? ''));
    $uom  = trim((string)($d['uom'] ?? 'pz'));
    $active = (int)($d['active'] ?? 1) ? 1 : 0;
    $erpId = trim((string)($d['erp_id'] ?? ''));
    if ($erpId === '') $erpId = null;

    if ($companyId<=0 || $sku==='' || $name==='') {
      jexit(['ok'=>false,'error'=>'company_id, sku y name son requeridos'], 400);
    }

    if ($id>0) {
      $st = $pdo->prepare("UPDATE products SET company_id=?, sku=?, name=?, erp_id=?, uom=?, active=? WHERE id=?");
      $st->execute([$companyId, $sku, $name, $erpId, $uom, $active, $id]);
      jexit(['ok'=>true,'id'=>$id]);
    } else {
      $st = $pdo->prepare("INSERT INTO products(company_id,sku,name,erp_id,uom,active) VALUES(?,?,?,?,?,?)");
      $st->execute([$companyId, $sku, $name, $erpId, $uom, $active]);
      jexit(['ok'=>true,'id'=>(int)$pdo->lastInsertId()]);
    }
  }

  // --------- outbound channels list ----------
  if ($action === 'channels_list') {
    $companyId = (int)($_GET['company_id'] ?? 0);
    $st = $pdo->prepare("
      SELECT oc.id, oc.company_id, c.name AS company_name, oc.name, oc.channel_type, oc.sort_order, oc.active
      FROM outbound_channels oc
      JOIN companies c ON c.id=oc.company_id
      WHERE (?=0 OR oc.company_id=?)
      ORDER BY c.name, oc.sort_order, oc.name
    ");
    $st->execute([$companyId, $companyId]);
    jexit(['ok'=>true,'rows'=>$st->fetchAll()]);
  }

  // --------- outbound channels save ----------
  if ($action === 'channels_save') {
    $d = read_json();
    $id = (int)($d['id'] ?? 0);
    $companyId = (int)($d['company_id'] ?? 0);
    $name = trim((string)($d['name'] ?? ''));
    $type = (string)($d['channel_type'] ?? 'SALE');
    $sort = (int)($d['sort_order'] ?? 0);
    $active = (int)($d['active'] ?? 1) ? 1 : 0;

    $allowed = ['SALE','TRANSFER','INTERNAL'];
    if (!in_array($type, $allowed, true)) $type = 'SALE';

    if ($companyId<=0 || $name==='') {
      jexit(['ok'=>false,'error'=>'company_id y name son requeridos'], 400);
    }

    if ($id>0) {
      $st = $pdo->prepare("UPDATE outbound_channels SET company_id=?, name=?, channel_type=?, sort_order=?, active=? WHERE id=?");
      $st->execute([$companyId, $name, $type, $sort, $active, $id]);
      jexit(['ok'=>true,'id'=>$id]);
    } else {
      $st = $pdo->prepare("INSERT INTO outbound_channels(company_id,name,channel_type,sort_order,active) VALUES(?,?,?,?,?)");
      $st->execute([$companyId, $name, $type, $sort, $active]);
      jexit(['ok'=>true,'id'=>(int)$pdo->lastInsertId()]);
    }
  }

  /* ===================== Calidad: Conceptos (MERMA/SCRAP) ===================== */
  if ($action === 'quality_concepts_list') {
    $companyId = (int)($_GET['company_id'] ?? 0);
    $type = strtoupper(trim((string)($_GET['concept_type'] ?? 'MERMA')));
    if (!in_array($type, ['MERMA','SCRAP'], true)) $type = 'MERMA';

    $st = $pdo->prepare("
      SELECT qc.id, qc.company_id, c.name AS company_name, qc.concept_type, qc.name, qc.sort_order, qc.active
      FROM quality_concepts qc
      JOIN companies c ON c.id=qc.company_id
      WHERE (?=0 OR qc.company_id=?)
        AND qc.concept_type=?
      ORDER BY c.name, qc.sort_order, qc.name
    ");
    $st->execute([$companyId, $companyId, $type]);
    jexit(['ok'=>true,'rows'=>$st->fetchAll()]);
  }

  if ($action === 'quality_concepts_save') {
    $d = read_json();
    $id = (int)($d['id'] ?? 0);
    $companyId = (int)($d['company_id'] ?? 0);
    $type = strtoupper(trim((string)($d['concept_type'] ?? 'MERMA')));
    $name = trim((string)($d['name'] ?? ''));
    $sort = (int)($d['sort_order'] ?? 0);
    $active = (int)($d['active'] ?? 1) ? 1 : 0;

    if (!in_array($type, ['MERMA','SCRAP'], true)) $type = 'MERMA';
    if ($companyId<=0 || $name==='') {
      jexit(['ok'=>false,'error'=>'company_id y name son requeridos'], 400);
    }

    if ($id>0) {
      $st = $pdo->prepare("UPDATE quality_concepts SET company_id=?, concept_type=?, name=?, sort_order=?, active=? WHERE id=?");
      $st->execute([$companyId, $type, $name, $sort, $active, $id]);
      jexit(['ok'=>true,'id'=>$id]);
    } else {
      $st = $pdo->prepare("INSERT INTO quality_concepts(company_id, concept_type, name, sort_order, active) VALUES(?,?,?,?,?)");
      $st->execute([$companyId, $type, $name, $sort, $active]);
      jexit(['ok'=>true,'id'=>(int)$pdo->lastInsertId()]);
    }
  }



  /* ===================== Inventario: Ajuste Almacenes ===================== */
  if ($action === 'inventory_adjust_auth') {
    $d = read_json();
    $code = (string)($d['code'] ?? '');
    if (!inv_adjust_code_ok($code)) {
      jexit(['ok'=>false,'error'=>'Código incorrecto.'], 403);
    }
    jexit(['ok'=>true]);
  }

  if ($action === 'inventory_adjust_locations') {
    $companyId = (int)($_GET['company_id'] ?? 0);
    $st = $pdo->prepare("\n      SELECT l.id, l.company_id, c.name AS company_name, l.name, l.code\n      FROM locations l\n      JOIN companies c ON c.id = l.company_id\n      WHERE l.active = 1\n        AND (? = 0 OR l.company_id = ?)\n      ORDER BY c.name, l.name\n    ");
    $st->execute([$companyId, $companyId]);
    jexit(['ok'=>true,'rows'=>$st->fetchAll()]);
  }

  if ($action === 'inventory_adjust_warehouses') {
    $companyId = (int)($_GET['company_id'] ?? 0);
    $locationId = (int)($_GET['location_id'] ?? 0);
    if ($companyId <= 0 || $locationId <= 0) jexit(['ok'=>false,'error'=>'Empresa y planta requeridas.'], 400);

    $st = $pdo->prepare("\n      SELECT id, code, name\n      FROM warehouses\n      WHERE company_id = ? AND location_id = ? AND active = 1\n      ORDER BY FIELD(code,'CF1','CF2','CF3','CUB','SOB-CUB','APROV-MERMA'), code, name\n    ");
    $st->execute([$companyId, $locationId]);
    jexit(['ok'=>true,'rows'=>$st->fetchAll()]);
  }

  if ($action === 'inventory_adjust_products') {
    $companyId = (int)($_GET['company_id'] ?? 0);
    if ($companyId <= 0) jexit(['ok'=>false,'error'=>'Empresa requerida.'], 400);

    $st = $pdo->prepare("\n      SELECT id, sku, name, uom, active\n      FROM products\n      WHERE company_id = ? AND active = 1\n      ORDER BY sku\n    ");
    $st->execute([$companyId]);
    jexit(['ok'=>true,'rows'=>$st->fetchAll()]);
  }

  if ($action === 'inventory_adjust_snapshot') {
    $companyId = (int)($_GET['company_id'] ?? 0);
    $locationId = (int)($_GET['location_id'] ?? 0);
    if ($companyId <= 0 || $locationId <= 0) jexit(['ok'=>false,'error'=>'Empresa y planta requeridas.'], 400);

    $st = $pdo->prepare("\n      SELECT\n        p.id AS product_id,\n        p.sku,\n        p.name AS product_name,\n        p.uom,\n        w.id AS warehouse_id,\n        w.code AS warehouse_code,\n        w.name AS warehouse_name,\n        " . inv_balance_sql() . " AS qty\n      FROM products p\n      CROSS JOIN warehouses w\n      LEFT JOIN inventory_moves im\n             ON im.product_id = p.id\n            AND im.company_id = p.company_id\n            AND im.location_id = w.location_id\n            AND im.warehouse_id = w.id\n      WHERE p.company_id = ?\n        AND p.active = 1\n        AND w.company_id = ?\n        AND w.location_id = ?\n        AND w.active = 1\n      GROUP BY p.id, p.sku, p.name, p.uom, w.id, w.code, w.name\n      ORDER BY p.sku, FIELD(w.code,'CF1','CF2','CF3','CUB','SOB-CUB','APROV-MERMA'), w.code\n    ");
    $st->execute([$companyId, $companyId, $locationId]);
    $rows = $st->fetchAll();

    $tot = $pdo->prepare("\n      SELECT\n        p.id AS product_id,\n        p.sku,\n        p.name AS product_name,\n        p.uom,\n        " . inv_balance_sql() . " AS qty_total\n      FROM products p\n      LEFT JOIN inventory_moves im\n             ON im.product_id = p.id\n            AND im.company_id = p.company_id\n            AND im.location_id = ?\n      WHERE p.company_id = ?\n        AND p.active = 1\n      GROUP BY p.id, p.sku, p.name, p.uom\n      ORDER BY p.sku\n    ");
    $tot->execute([$locationId, $companyId]);

    jexit(['ok'=>true,'rows'=>$rows,'totals'=>$tot->fetchAll()]);
  }

  if ($action === 'inventory_adjust_save') {
    $d = read_json();
    $code = (string)($d['code'] ?? '');
    if (!inv_adjust_code_ok($code)) jexit(['ok'=>false,'error'=>'Código incorrecto.'], 403);

    $companyId = (int)($d['company_id'] ?? 0);
    $locationId = (int)($d['location_id'] ?? 0);
    $warehouseId = (int)($d['warehouse_id'] ?? 0);
    $productId = (int)($d['product_id'] ?? 0);
    $qty = (float)($d['qty'] ?? 0);
    $kind = strtoupper(trim((string)($d['kind'] ?? 'ADD')));
    $notes = trim((string)($d['notes'] ?? ''));

    if ($companyId <= 0 || $locationId <= 0 || $warehouseId <= 0 || $productId <= 0 || $qty <= 0) {
      jexit(['ok'=>false,'error'=>'Empresa, planta, almacén, producto y cantidad son requeridos.'], 400);
    }
    if (!in_array($kind, ['ADD','SUB'], true)) $kind = 'ADD';

    $st = $pdo->prepare("SELECT id, code FROM warehouses WHERE id=? AND company_id=? AND location_id=? AND active=1 LIMIT 1");
    $st->execute([$warehouseId, $companyId, $locationId]);
    $wh = $st->fetch();
    if (!$wh) jexit(['ok'=>false,'error'=>'Almacén inválido para la planta seleccionada.'], 400);

    $st = $pdo->prepare("SELECT id, sku, name FROM products WHERE id=? AND company_id=? AND active=1 LIMIT 1");
    $st->execute([$productId, $companyId]);
    $prod = $st->fetch();
    if (!$prod) jexit(['ok'=>false,'error'=>'Producto inválido para la empresa seleccionada.'], 400);

    $moveType = $kind === 'SUB' ? 'OUT' : 'ADJ';
    $u = current_user();
    $note = 'Ajuste almacén ' . ($kind === 'SUB' ? 'quita' : 'agrega') . ' | SKU: ' . (string)$prod['sku'] . ' | WH: ' . (string)$wh['code'];
    if ($notes !== '') $note .= ' | ' . $notes;
    $note = mb_substr($note, 0, 255);

    $st = $pdo->prepare("\n      INSERT INTO inventory_moves (\n        company_id, location_id, warehouse_id, work_date, product_id, qty, move_type,\n        channel_id, ref_type, ref_id, notes, created_by, created_at\n      ) VALUES (\n        ?, ?, ?, CURDATE(), ?, ?, ?,\n        NULL, 'AJUSTE_ALMACEN', NULL, ?, ?, NOW()\n      )\n    ");
    $st->execute([$companyId, $locationId, $warehouseId, $productId, $qty, $moveType, $note, (int)$u['id']]);

    jexit(['ok'=>true,'id'=>(int)$pdo->lastInsertId()]);
  }


  if ($action === 'inventory_cut_save') {
    $d = read_json();
    $code = (string)($d['code'] ?? '');
    if (!inv_adjust_code_ok($code)) jexit(['ok'=>false,'error'=>'Código incorrecto.'], 403);

    $companyId = (int)($d['company_id'] ?? 0);
    $locationId = (int)($d['location_id'] ?? 0);
    $warehouseId = (int)($d['warehouse_id'] ?? 0);
    $productId = (int)($d['product_id'] ?? 0);
    $targetQty = (float)($d['target_qty'] ?? 0);
    $notes = trim((string)($d['notes'] ?? ''));

    if ($companyId <= 0 || $locationId <= 0 || $warehouseId <= 0 || $productId <= 0 || $targetQty < 0) {
      jexit(['ok'=>false,'error'=>'Empresa, planta, almacén, producto y cantidad física objetivo son requeridos.'], 400);
    }

    $st = $pdo->prepare("SELECT id, code, name FROM warehouses WHERE id=? AND company_id=? AND location_id=? AND active=1 LIMIT 1");
    $st->execute([$warehouseId, $companyId, $locationId]);
    $wh = $st->fetch();
    if (!$wh) jexit(['ok'=>false,'error'=>'Almacén inválido para la planta seleccionada.'], 400);

    $st = $pdo->prepare("SELECT id, sku, name FROM products WHERE id=? AND company_id=? AND active=1 LIMIT 1");
    $st->execute([$productId, $companyId]);
    $prod = $st->fetch();
    if (!$prod) jexit(['ok'=>false,'error'=>'Producto inválido para la empresa seleccionada.'], 400);

    $st = $pdo->prepare("
      SELECT COALESCE(SUM(CASE
        WHEN move_type IN ('IN','ADJ') THEN qty
        WHEN move_type = 'OUT' THEN -qty
        ELSE 0
      END),0)
      FROM inventory_moves
      WHERE company_id=? AND location_id=? AND product_id=? AND warehouse_id=?
    ");
    $st->execute([$companyId, $locationId, $productId, $warehouseId]);
    $currentQty = round((float)$st->fetchColumn(), 3);
    $targetQty = round($targetQty, 3);
    $delta = round($targetQty - $currentQty, 3);

    if (abs($delta) <= 0.0001) {
      jexit([
        'ok'=>true,
        'id'=>0,
        'message'=>'Sin diferencia. El almacén ya tiene la cantidad física capturada.',
        'current_qty'=>$currentQty,
        'target_qty'=>$targetQty,
        'delta'=>0
      ]);
    }

    $moveType = $delta > 0 ? 'ADJ' : 'OUT';
    $qty = abs($delta);
    $u = current_user();

    $note = 'Corte físico WH | SKU: ' . (string)$prod['sku'] .
      ' | WH: ' . (string)$wh['code'] .
      ' | Ant: ' . number_format($currentQty, 3, '.', '') .
      ' | Obj: ' . number_format($targetQty, 3, '.', '') .
      ' | Dif: ' . number_format($delta, 3, '.', '');
    if ($notes !== '') $note .= ' | ' . $notes;
    $note = mb_substr($note, 0, 255);

    $st = $pdo->prepare("
      INSERT INTO inventory_moves (
        company_id, location_id, warehouse_id, work_date, product_id, qty, move_type,
        channel_id, ref_type, ref_id, notes, created_by, created_at
      ) VALUES (
        ?, ?, ?, CURDATE(), ?, ?, ?,
        NULL, 'CORTE_FISICO_WH', NULL, ?, ?, NOW()
      )
    ");
    $st->execute([$companyId, $locationId, $warehouseId, $productId, $qty, $moveType, $note, (int)$u['id']]);

    jexit([
      'ok'=>true,
      'id'=>(int)$pdo->lastInsertId(),
      'current_qty'=>$currentQty,
      'target_qty'=>$targetQty,
      'delta'=>$delta,
      'move_type'=>$moveType,
      'qty'=>$qty
    ]);
  }



  /* ===================== Auditoría: Kardex / Log operativo ===================== */
  if ($action === 'inventory_audit_log') {
    $companyId = (int)($_GET['company_id'] ?? 0);
    $locationId = (int)($_GET['location_id'] ?? 0);
    $productId = (int)($_GET['product_id'] ?? 0);
    $warehouseId = (int)($_GET['warehouse_id'] ?? 0);
    $moveType = strtoupper(trim((string)($_GET['move_type'] ?? '')));
    $refType = strtoupper(trim((string)($_GET['ref_type'] ?? '')));
    $userId = (int)($_GET['user_id'] ?? 0);
    $q = trim((string)($_GET['q'] ?? ''));
    $dateFrom = trim((string)($_GET['date_from'] ?? date('Y-m-01')));
    $dateTo = trim((string)($_GET['date_to'] ?? date('Y-m-d')));
    $page = max(1, (int)($_GET['page'] ?? 1));
    $perPage = (int)($_GET['per_page'] ?? 50);
    if (!in_array($perPage, [25,50,100,200], true)) $perPage = 50;
    $offset = ($page - 1) * $perPage;

    if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $dateFrom)) $dateFrom = date('Y-m-01');
    if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $dateTo)) $dateTo = date('Y-m-d');
    if ($dateFrom > $dateTo) { $tmp = $dateFrom; $dateFrom = $dateTo; $dateTo = $tmp; }

    $where = ["im.work_date BETWEEN ? AND ?"];
    $params = [$dateFrom, $dateTo];

    if ($companyId > 0) { $where[] = "im.company_id = ?"; $params[] = $companyId; }
    if ($locationId > 0) { $where[] = "im.location_id = ?"; $params[] = $locationId; }
    if ($productId > 0) { $where[] = "im.product_id = ?"; $params[] = $productId; }
    if ($warehouseId > 0) { $where[] = "im.warehouse_id = ?"; $params[] = $warehouseId; }
    if (in_array($moveType, ['IN','OUT','ADJ'], true)) { $where[] = "UPPER(im.move_type) = ?"; $params[] = $moveType; }
    if ($refType !== '') { $where[] = "UPPER(im.ref_type) LIKE ?"; $params[] = '%' . $refType . '%'; }
    if ($userId > 0) { $where[] = "im.created_by = ?"; $params[] = $userId; }
    if ($q !== '') {
      $where[] = "(
        p.sku LIKE ? OR p.name LIKE ? OR COALESCE(w.code,'') LIKE ? OR
        COALESCE(im.ref_type,'') LIKE ? OR CAST(COALESCE(im.ref_id,0) AS CHAR) LIKE ? OR
        COALESCE(im.notes,'') LIKE ? OR COALESCE(u.full_name,'') LIKE ? OR COALESCE(u.username,'') LIKE ?
      )";
      $like = '%' . $q . '%';
      array_push($params, $like, $like, $like, $like, $like, $like, $like, $like);
    }

    $whereSql = implode(' AND ', $where);

    $sqlRows = "
      SELECT
        im.id,
        im.created_at,
        im.work_date,
        im.company_id,
        c.name AS company_name,
        im.location_id,
        l.name AS location_name,
        im.warehouse_id,
        COALESCE(w.code, 'SIN-WH') AS warehouse_code,
        COALESCE(w.name, '') AS warehouse_name,
        im.product_id,
        p.sku,
        p.name AS product_name,
        p.uom,
        UPPER(im.move_type) AS move_type,
        im.qty,
        CASE
          WHEN UPPER(im.move_type) IN ('IN','ADJ') THEN im.qty
          WHEN UPPER(im.move_type) = 'OUT' THEN -im.qty
          ELSE 0
        END AS signed_qty,
        im.ref_type,
        im.ref_id,
        im.created_by,
        COALESCE(u.full_name, u.username, CONCAT('Usuario #', im.created_by)) AS user_name,
        im.notes
      FROM inventory_moves im
      JOIN products p ON p.id = im.product_id
      LEFT JOIN companies c ON c.id = im.company_id
      LEFT JOIN locations l ON l.id = im.location_id
      LEFT JOIN warehouses w ON w.id = im.warehouse_id
      LEFT JOIN users u ON u.id = im.created_by
      WHERE $whereSql
      ORDER BY im.created_at DESC, im.id DESC
      LIMIT $perPage OFFSET $offset
    ";
    $st = $pdo->prepare($sqlRows);
    $st->execute($params);
    $rows = $st->fetchAll();

    $sqlSummary = "
      SELECT
        COALESCE(SUM(CASE WHEN UPPER(im.move_type)='IN' THEN im.qty ELSE 0 END),0) AS total_in,
        COALESCE(SUM(CASE WHEN UPPER(im.move_type)='OUT' THEN im.qty ELSE 0 END),0) AS total_out,
        COALESCE(SUM(CASE WHEN UPPER(im.move_type)='ADJ' THEN im.qty ELSE 0 END),0) AS total_adj,
        COALESCE(SUM(CASE
          WHEN UPPER(im.move_type) IN ('IN','ADJ') THEN im.qty
          WHEN UPPER(im.move_type)='OUT' THEN -im.qty
          ELSE 0
        END),0) AS net_qty,
        COUNT(*) AS moves_count
      FROM inventory_moves im
      JOIN products p ON p.id = im.product_id
      LEFT JOIN warehouses w ON w.id = im.warehouse_id
      LEFT JOIN users u ON u.id = im.created_by
      WHERE $whereSql
    ";
    $st = $pdo->prepare($sqlSummary);
    $st->execute($params);
    $summary = $st->fetch() ?: [];

    $sqlRef = "
      SELECT COALESCE(im.ref_type,'SIN_REF') AS ref_type, COUNT(*) AS moves_count,
             COALESCE(SUM(CASE WHEN UPPER(im.move_type) IN ('IN','ADJ') THEN im.qty WHEN UPPER(im.move_type)='OUT' THEN -im.qty ELSE 0 END),0) AS net_qty
      FROM inventory_moves im
      JOIN products p ON p.id = im.product_id
      LEFT JOIN warehouses w ON w.id = im.warehouse_id
      LEFT JOIN users u ON u.id = im.created_by
      WHERE $whereSql
      GROUP BY COALESCE(im.ref_type,'SIN_REF')
      ORDER BY moves_count DESC, ref_type ASC
      LIMIT 20
    ";
    $st = $pdo->prepare($sqlRef);
    $st->execute($params);
    $byRefType = $st->fetchAll();

    $sqlUser = "
      SELECT COALESCE(u.full_name, u.username, CONCAT('Usuario #', im.created_by)) AS user_name, COUNT(*) AS moves_count,
             COALESCE(SUM(CASE WHEN UPPER(im.move_type) IN ('IN','ADJ') THEN im.qty WHEN UPPER(im.move_type)='OUT' THEN -im.qty ELSE 0 END),0) AS net_qty
      FROM inventory_moves im
      JOIN products p ON p.id = im.product_id
      LEFT JOIN warehouses w ON w.id = im.warehouse_id
      LEFT JOIN users u ON u.id = im.created_by
      WHERE $whereSql
      GROUP BY COALESCE(u.full_name, u.username, CONCAT('Usuario #', im.created_by))
      ORDER BY moves_count DESC, user_name ASC
      LIMIT 20
    ";
    $st = $pdo->prepare($sqlUser);
    $st->execute($params);
    $byUser = $st->fetchAll();

    $totalRows = (int)($summary['moves_count'] ?? 0);
    $totalPages = max(1, (int)ceil($totalRows / max(1, $perPage)));

    jexit([
      'ok'=>true,
      'rows'=>$rows,
      'summary'=>$summary,
      'by_ref_type'=>$byRefType,
      'by_user'=>$byUser,
      'pagination'=>[
        'page'=>$page,
        'per_page'=>$perPage,
        'total_rows'=>$totalRows,
        'total_pages'=>$totalPages,
        'has_prev'=>$page > 1,
        'has_next'=>$page < $totalPages
      ]
    ]);
  }



  /* ===================== Inventario: Corrección controlada de movimientos ===================== */
  if ($action === 'inventory_move_get') {
    $id = (int)($_GET['id'] ?? 0);
    if ($id <= 0) jexit(['ok'=>false,'error'=>'ID de movimiento requerido.'], 400);

    $st = $pdo->prepare("
      SELECT
        im.id, im.created_at, im.work_date, im.company_id, c.name AS company_name,
        im.location_id, l.name AS location_name, im.warehouse_id,
        COALESCE(w.code,'SIN-WH') AS warehouse_code, COALESCE(w.name,'') AS warehouse_name,
        im.product_id, p.sku, p.name AS product_name, p.uom,
        UPPER(im.move_type) AS move_type, im.qty, im.ref_type, im.ref_id,
        im.created_by, COALESCE(u.full_name, u.username, CONCAT('Usuario #', im.created_by)) AS user_name,
        im.notes
      FROM inventory_moves im
      JOIN products p ON p.id = im.product_id
      LEFT JOIN companies c ON c.id = im.company_id
      LEFT JOIN locations l ON l.id = im.location_id
      LEFT JOIN warehouses w ON w.id = im.warehouse_id
      LEFT JOIN users u ON u.id = im.created_by
      WHERE im.id = ?
      LIMIT 1
    ");
    $st->execute([$id]);
    $row = $st->fetch();
    if (!$row) jexit(['ok'=>false,'error'=>'Movimiento no encontrado.'], 404);

    $prod = $pdo->prepare("
      SELECT id, sku, name, uom
      FROM products
      WHERE company_id = ? AND active = 1
      ORDER BY sku
    ");
    $prod->execute([(int)$row['company_id']]);

    $wh = $pdo->prepare("
      SELECT id, code, name
      FROM warehouses
      WHERE company_id = ? AND location_id = ? AND active = 1
      ORDER BY FIELD(code,'CF1','CF2','CF3','CUB','SOB-CUB','MERMA','APROV-MERMA'), code, name
    ");
    $wh->execute([(int)$row['company_id'], (int)$row['location_id']]);

    jexit([
      'ok'=>true,
      'row'=>$row,
      'products'=>$prod->fetchAll(),
      'warehouses'=>$wh->fetchAll()
    ]);
  }

  if ($action === 'inventory_move_update') {
    $d = read_json();
    $code = (string)($d['code'] ?? '');
    if (!inv_adjust_code_ok($code)) jexit(['ok'=>false,'error'=>'Código de autorización incorrecto.'], 403);

    $id = (int)($d['id'] ?? 0);
    $workDate = trim((string)($d['work_date'] ?? ''));
    $createdAt = trim((string)($d['created_at'] ?? ''));
    $productId = (int)($d['product_id'] ?? 0);
    $warehouseIdRaw = $d['warehouse_id'] ?? null;
    $warehouseId = ($warehouseIdRaw === null || $warehouseIdRaw === '' || (int)$warehouseIdRaw <= 0) ? null : (int)$warehouseIdRaw;
    $qty = round((float)($d['qty'] ?? 0), 3);
    $moveType = strtoupper(trim((string)($d['move_type'] ?? '')));
    $notes = trim((string)($d['notes'] ?? ''));
    $reason = trim((string)($d['reason'] ?? ''));

    if ($id <= 0) jexit(['ok'=>false,'error'=>'ID de movimiento requerido.'], 400);
    if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $workDate)) jexit(['ok'=>false,'error'=>'Fecha operativa inválida.'], 400);
    if ($createdAt !== '' && !preg_match('/^\d{4}-\d{2}-\d{2}T?\s?\d{2}:\d{2}(:\d{2})?$/', str_replace('T',' ', $createdAt))) {
      jexit(['ok'=>false,'error'=>'Fecha/hora inválida. Usa YYYY-MM-DD HH:MM:SS.'], 400);
    }
    $createdAt = str_replace('T', ' ', $createdAt);
    if (preg_match('/^\d{4}-\d{2}-\d{2}\s\d{2}:\d{2}$/', $createdAt)) $createdAt .= ':00';
    if ($createdAt === '') $createdAt = $workDate . ' 00:00:00';
    if ($productId <= 0 || $qty <= 0 || !in_array($moveType, ['IN','OUT','ADJ'], true)) {
      jexit(['ok'=>false,'error'=>'Producto, cantidad y tipo de movimiento son requeridos.'], 400);
    }
    if ($reason === '') {
      jexit(['ok'=>false,'error'=>'Indica un motivo de corrección para dejar trazabilidad.'], 400);
    }

    $pdo->beginTransaction();
    try {
      $st = $pdo->prepare("SELECT * FROM inventory_moves WHERE id = ? FOR UPDATE");
      $st->execute([$id]);
      $old = $st->fetch(PDO::FETCH_ASSOC);
      if (!$old) throw new RuntimeException('Movimiento no encontrado.');

      $companyId = (int)$old['company_id'];
      $locationId = (int)$old['location_id'];

      $chk = $pdo->prepare("SELECT id FROM products WHERE id=? AND company_id=? AND active=1 LIMIT 1");
      $chk->execute([$productId, $companyId]);
      if (!$chk->fetch()) throw new RuntimeException('Producto inválido para la empresa del movimiento.');

      if ($warehouseId !== null) {
        $chk = $pdo->prepare("SELECT id FROM warehouses WHERE id=? AND company_id=? AND location_id=? AND active=1 LIMIT 1");
        $chk->execute([$warehouseId, $companyId, $locationId]);
        if (!$chk->fetch()) throw new RuntimeException('Almacén inválido para la planta del movimiento.');
      }

      $notes = $notes !== '' ? mb_substr($notes, 0, 255) : null;

      $upd = $pdo->prepare("
        UPDATE inventory_moves
        SET work_date = ?,
            created_at = ?,
            product_id = ?,
            warehouse_id = ?,
            qty = ?,
            move_type = ?,
            notes = ?
        WHERE id = ?
      ");
      $upd->execute([$workDate, $createdAt, $productId, $warehouseId, $qty, $moveType, $notes, $id]);

      $st = $pdo->prepare("SELECT * FROM inventory_moves WHERE id = ? LIMIT 1");
      $st->execute([$id]);
      $new = $st->fetch(PDO::FETCH_ASSOC) ?: [];

      $log = $pdo->prepare("
        INSERT INTO inventory_move_admin_edits (
          inventory_move_id, company_id, location_id, old_data, new_data, reason, edited_by, edited_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, NOW())
      ");
      $u = current_user();
      $log->execute([
        $id,
        $companyId,
        $locationId,
        inv_move_snapshot_json($old),
        inv_move_snapshot_json($new),
        mb_substr($reason, 0, 255),
        (int)$u['id']
      ]);

      $pdo->commit();
      jexit(['ok'=>true,'id'=>$id]);
    } catch (Throwable $e) {
      if ($pdo->inTransaction()) $pdo->rollBack();
      jexit(['ok'=>false,'error'=>$e->getMessage()], 500);
    }
  }


  /* ===================== Productos: reglas de almacén por localidad ===================== */
  if ($action === 'product_wh_rules_get') {
    $companyId = (int)($_GET['company_id'] ?? 0);
    $productId = (int)($_GET['product_id'] ?? 0);
    if ($companyId <= 0 || $productId <= 0) jexit(['ok'=>false,'error'=>'Empresa y producto requeridos.'], 400);

    $st = $pdo->prepare("SELECT id, sku, name FROM products WHERE id=? AND company_id=? AND active=1 LIMIT 1");
    $st->execute([$productId, $companyId]);
    $product = $st->fetch();
    if (!$product) jexit(['ok'=>false,'error'=>'Producto inválido para la empresa seleccionada.'], 404);

    $locSt = $pdo->prepare("
      SELECT id, name, code
      FROM locations
      WHERE company_id=? AND active=1
      ORDER BY name
    ");
    $locSt->execute([$companyId]);
    $locations = $locSt->fetchAll();

    $ruleSt = $pdo->prepare("
      SELECT *
      FROM product_warehouse_rules
      WHERE company_id=? AND product_id=? AND location_id=?
      LIMIT 1
    ");
    $whSt = $pdo->prepare("
      SELECT id, code, name
      FROM warehouses
      WHERE company_id=? AND location_id=? AND active=1
      ORDER BY FIELD(code,'CF1','CF2','CF3','CUB','MERMA','APROV-MERMA','SOB-CUB'), code, name
    ");

    $rows = [];
    foreach ($locations as $loc) {
      $lid = (int)$loc['id'];
      $ruleSt->execute([$companyId, $productId, $lid]);
      $rule = $ruleSt->fetch() ?: [];
      $whSt->execute([$companyId, $lid]);
      $rows[] = [
        'location_id' => $lid,
        'location_name' => (string)$loc['name'],
        'location_code' => (string)$loc['code'],
        'warehouses' => $whSt->fetchAll(),
        'rule' => [
          'default_finished_warehouse_id' => (int)($rule['default_finished_warehouse_id'] ?? 0),
          'default_sales_warehouse_id' => (int)($rule['default_sales_warehouse_id'] ?? 0),
          'merma_warehouse_id' => (int)($rule['merma_warehouse_id'] ?? 0),
          'aprovechamiento_warehouse_id' => (int)($rule['aprovechamiento_warehouse_id'] ?? 0),
          'cubicado_warehouse_id' => (int)($rule['cubicado_warehouse_id'] ?? 0),
          'notes' => (string)($rule['notes'] ?? ''),
          'active' => (int)($rule['active'] ?? 1),
        ]
      ];
    }

    jexit(['ok'=>true,'product'=>$product,'rows'=>$rows]);
  }

  if ($action === 'product_wh_rules_save') {
    $d = read_json();
    $companyId = (int)($d['company_id'] ?? 0);
    $locationId = (int)($d['location_id'] ?? 0);
    $productId = (int)($d['product_id'] ?? 0);
    $notes = trim((string)($d['notes'] ?? ''));
    $active = (int)($d['active'] ?? 1) ? 1 : 0;

    $fields = [
      'default_finished_warehouse_id',
      'default_sales_warehouse_id',
      'merma_warehouse_id',
      'aprovechamiento_warehouse_id',
      'cubicado_warehouse_id'
    ];
    $vals = [];
    foreach ($fields as $f) $vals[$f] = (int)($d[$f] ?? 0);

    if ($companyId <= 0 || $locationId <= 0 || $productId <= 0) {
      jexit(['ok'=>false,'error'=>'Empresa, localidad y producto requeridos.'], 400);
    }

    $st = $pdo->prepare("SELECT id FROM products WHERE id=? AND company_id=? AND active=1 LIMIT 1");
    $st->execute([$productId, $companyId]);
    if (!$st->fetch()) jexit(['ok'=>false,'error'=>'Producto inválido.'], 400);

    $st = $pdo->prepare("SELECT id FROM locations WHERE id=? AND company_id=? AND active=1 LIMIT 1");
    $st->execute([$locationId, $companyId]);
    if (!$st->fetch()) jexit(['ok'=>false,'error'=>'Localidad inválida.'], 400);

    $whCheck = $pdo->prepare("SELECT id FROM warehouses WHERE id=? AND company_id=? AND location_id=? AND active=1 LIMIT 1");
    foreach ($fields as $f) {
      if ($vals[$f] <= 0) { $vals[$f] = null; continue; }
      $whCheck->execute([$vals[$f], $companyId, $locationId]);
      if (!$whCheck->fetch()) jexit(['ok'=>false,'error'=>'Almacén inválido para la localidad seleccionada en campo '.$f], 400);
    }
    if ($notes === '') $notes = null;

    $st = $pdo->prepare("
      INSERT INTO product_warehouse_rules (
        company_id, location_id, product_id,
        default_finished_warehouse_id, default_sales_warehouse_id, merma_warehouse_id,
        aprovechamiento_warehouse_id, cubicado_warehouse_id,
        notes, active, created_at, updated_at
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())
      ON DUPLICATE KEY UPDATE
        default_finished_warehouse_id = VALUES(default_finished_warehouse_id),
        default_sales_warehouse_id = VALUES(default_sales_warehouse_id),
        merma_warehouse_id = VALUES(merma_warehouse_id),
        aprovechamiento_warehouse_id = VALUES(aprovechamiento_warehouse_id),
        cubicado_warehouse_id = VALUES(cubicado_warehouse_id),
        notes = VALUES(notes),
        active = VALUES(active),
        updated_at = NOW()
    ");
    $st->execute([
      $companyId, $locationId, $productId,
      $vals['default_finished_warehouse_id'], $vals['default_sales_warehouse_id'], $vals['merma_warehouse_id'],
      $vals['aprovechamiento_warehouse_id'], $vals['cubicado_warehouse_id'],
      $notes, $active
    ]);

    jexit(['ok'=>true]);
  }

  /* ===================== Análisis de datos: explorador seguro ===================== */
  function data_safe_identifier(string $name): string {
    $name = trim($name);
    if (!preg_match('/^[A-Za-z0-9_]+$/', $name)) {
      throw new RuntimeException('Identificador inválido: ' . $name);
    }
    return $name;
  }

  function data_table_exists(PDO $pdo, string $table): bool {
    $st = $pdo->prepare("SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = ?");
    $st->execute([$table]);
    return (int)$st->fetchColumn() > 0;
  }

  function data_columns(PDO $pdo, string $table): array {
    data_safe_identifier($table);
    $st = $pdo->prepare("\n      SELECT column_name, column_type, data_type, is_nullable, column_key, column_default, extra\n      FROM information_schema.columns\n      WHERE table_schema = DATABASE() AND table_name = ?\n      ORDER BY ordinal_position\n    ");
    $st->execute([$table]);
    return $st->fetchAll();
  }

  function data_fk_relationships(PDO $pdo, array $tables): array {
    $rels = [];
    if (!$tables) return $rels;
    $in = implode(',', array_fill(0, count($tables), '?'));
    $params = array_merge($tables, $tables);
    $st = $pdo->prepare("\n      SELECT\n        kcu.table_name AS left_table,\n        kcu.column_name AS left_column,\n        kcu.referenced_table_name AS right_table,\n        kcu.referenced_column_name AS right_column,\n        kcu.constraint_name\n      FROM information_schema.key_column_usage kcu\n      WHERE kcu.table_schema = DATABASE()\n        AND kcu.referenced_table_name IS NOT NULL\n        AND kcu.table_name IN ($in)\n        AND kcu.referenced_table_name IN ($in)\n    ");
    $st->execute($params);
    foreach ($st->fetchAll() as $r) {
      $rels[] = [
        'left_table'=>(string)$r['left_table'],
        'left_column'=>(string)$r['left_column'],
        'right_table'=>(string)$r['right_table'],
        'right_column'=>(string)$r['right_column'],
        'confidence'=>'alta',
        'reason'=>'Relación por llave foránea declarada: ' . (string)$r['constraint_name']
      ];
    }
    return $rels;
  }

  function data_known_relationships(array $tables): array {
    $known = [
      ['inventory_moves','company_id','companies','id','Inventario pertenece a empresa'],
      ['inventory_moves','location_id','locations','id','Inventario pertenece a localidad'],
      ['inventory_moves','warehouse_id','warehouses','id','Movimiento apunta al almacén real'],
      ['inventory_moves','product_id','products','id','Movimiento apunta al producto'],
      ['inventory_moves','created_by','users','id','Usuario que registró movimiento'],
      ['products','company_id','companies','id','Producto pertenece a empresa'],
      ['warehouses','company_id','companies','id','Almacén pertenece a empresa'],
      ['warehouses','location_id','locations','id','Almacén pertenece a localidad'],
      ['locations','company_id','companies','id','Localidad pertenece a empresa'],
      ['production_entries','company_id','companies','id','Producción pertenece a empresa'],
      ['production_entries','location_id','locations','id','Producción pertenece a localidad'],
      ['production_entries','product_id','products','id','Producción apunta a producto'],
      ['quality_entries','company_id','companies','id','Calidad pertenece a empresa'],
      ['quality_entries','location_id','locations','id','Calidad pertenece a localidad'],
      ['quality_entries','product_id','products','id','Calidad apunta a producto'],
      ['quality_entries','concept_id','quality_concepts','id','Calidad apunta a concepto'],
      ['warehouse_location_requests','company_id','companies','id','Solicitud pertenece a empresa'],
      ['warehouse_location_requests','request_location_id','locations','id','Localidad solicitante'],
      ['warehouse_location_requests','source_location_id','locations','id','Localidad proveedora'],
      ['warehouse_location_requests','requested_by','users','id','Usuario solicitante'],
      ['warehouse_location_request_items','request_id','warehouse_location_requests','id','Detalle pertenece a solicitud'],
      ['warehouse_location_request_items','product_id','products','id','Detalle apunta a producto'],
      ['warehouse_location_request_items','source_warehouse_id','warehouses','id','Almacén origen transferencia'],
      ['warehouse_location_request_items','destination_warehouse_id','warehouses','id','Almacén destino transferencia'],
      ['outbounds','company_id','companies','id','Salida pertenece a empresa'],
      ['outbounds','location_id','locations','id','Salida pertenece a localidad'],
      ['outbound_items','outbound_id','outbounds','id','Detalle pertenece a salida'],
      ['outbound_items','product_id','products','id','Detalle apunta a producto'],
      ['sales_orders','company_id','companies','id','Pedido pertenece a empresa'],
      ['sales_orders','location_id','locations','id','Pedido pertenece a localidad'],
      ['sales_order_items','order_id','sales_orders','id','Detalle pertenece a pedido'],
      ['sales_order_items','product_id','products','id','Detalle apunta a producto'],
      ['product_warehouse_rules','company_id','companies','id','Regla pertenece a empresa'],
      ['product_warehouse_rules','location_id','locations','id','Regla pertenece a localidad'],
      ['product_warehouse_rules','product_id','products','id','Regla apunta a producto'],
      ['product_warehouse_rules','default_finished_warehouse_id','warehouses','id','Almacén entrada / producto terminado configurado'],
      ['product_warehouse_rules','default_sales_warehouse_id','warehouses','id','Almacén salida comercial configurado'],
      ['product_warehouse_rules','merma_warehouse_id','warehouses','id','Almacén merma configurado'],
      ['product_warehouse_rules','aprovechamiento_warehouse_id','warehouses','id','Almacén aprovechamiento configurado'],
      ['product_warehouse_rules','cubicado_warehouse_id','warehouses','id','Almacén cubicado configurado'],
    ];
    $set = array_flip($tables);
    $rels = [];
    foreach ($known as $k) {
      if (isset($set[$k[0]]) && isset($set[$k[2]])) {
        $rels[] = ['left_table'=>$k[0],'left_column'=>$k[1],'right_table'=>$k[2],'right_column'=>$k[3],'confidence'=>'alta','reason'=>$k[4]];
      }
    }
    return $rels;
  }

  function data_heuristic_relationships(PDO $pdo, array $tables): array {
    $cols = [];
    foreach ($tables as $t) {
      $cols[$t] = array_map(fn($c)=>(string)$c['column_name'], data_columns($pdo, $t));
    }
    $rels = [];
    foreach ($tables as $lt) {
      foreach ($cols[$lt] as $lc) {
        if ($lc === 'id' || !str_ends_with($lc, '_id')) continue;
        $base = substr($lc, 0, -3);
        foreach ($tables as $rt) {
          if ($rt === $lt) continue;
          $candidates = [$base, $base . 's', $base . 'es'];
          $singularRt = rtrim($rt, 's');
          if (in_array($rt, $candidates, true) || $singularRt === $base || str_contains($rt, $base)) {
            if (in_array('id', $cols[$rt] ?? [], true)) {
              $rels[] = ['left_table'=>$lt,'left_column'=>$lc,'right_table'=>$rt,'right_column'=>'id','confidence'=>'media','reason'=>'Sugerida por convención de columna *_id'];
            }
          }
        }
      }
    }
    return $rels;
  }

  function data_relationships(PDO $pdo, array $tables): array {
    $tables = array_values(array_unique(array_filter(array_map('trim', $tables))));
    foreach ($tables as $t) { data_safe_identifier($t); if (!data_table_exists($pdo, $t)) throw new RuntimeException('Tabla no existe: ' . $t); }
    $all = array_merge(data_fk_relationships($pdo, $tables), data_known_relationships($tables), data_heuristic_relationships($pdo, $tables));
    $seen = [];
    $out = [];
    foreach ($all as $r) {
      $key = implode('|', [$r['left_table'],$r['left_column'],$r['right_table'],$r['right_column']]);
      if (isset($seen[$key])) continue;
      $seen[$key] = true;
      $out[] = $r;
    }
    return $out;
  }

  function data_build_query(PDO $pdo, array $tables): string {
    $tables = array_values(array_unique(array_filter(array_map('trim', $tables))));
    if (!$tables) throw new RuntimeException('Selecciona al menos una tabla.');
    foreach ($tables as $t) { data_safe_identifier($t); if (!data_table_exists($pdo, $t)) throw new RuntimeException('Tabla no existe: ' . $t); }
    if (count($tables) === 1) return "SELECT t1.*\nFROM `{$tables[0]}` t1\nLIMIT 100";

    $rels = data_relationships($pdo, $tables);
    $aliases = [];
    foreach ($tables as $i=>$t) $aliases[$t] = 't' . ($i+1);
    $joined = [$tables[0]=>true];
    $sql = "SELECT\n  " . implode(",\n  ", array_map(fn($t)=>$aliases[$t] . '.*', $tables)) . "\nFROM `{$tables[0]}` {$aliases[$tables[0]]}";
    $pending = array_slice($tables, 1);
    while ($pending) {
      $progress = false;
      foreach ($pending as $idx=>$t) {
        $relUse = null; $direction = '';
        foreach ($rels as $r) {
          if ($r['left_table'] === $t && isset($joined[$r['right_table']])) { $relUse=$r; $direction='left_to_joined'; break; }
          if ($r['right_table'] === $t && isset($joined[$r['left_table']])) { $relUse=$r; $direction='right_to_joined'; break; }
        }
        if ($relUse) {
          if ($direction === 'left_to_joined') {
            $sql .= "\nLEFT JOIN `{$t}` {$aliases[$t]} ON {$aliases[$t]}.`{$relUse['left_column']}` = {$aliases[$relUse['right_table']]}.`{$relUse['right_column']}`";
          } else {
            $sql .= "\nLEFT JOIN `{$t}` {$aliases[$t]} ON {$aliases[$relUse['left_table']]}.`{$relUse['left_column']}` = {$aliases[$t]}.`{$relUse['right_column']}`";
          }
          $joined[$t] = true; unset($pending[$idx]); $pending = array_values($pending); $progress = true; break;
        }
      }
      if (!$progress) {
        $t = array_shift($pending);
        $sql .= "\n-- Sin relación confiable detectada para {$t}; se agrega separado para revisión.\n-- JOIN `{$t}` {$aliases[$t]} ON /* define condición */";
        $joined[$t] = true;
      }
    }
    return $sql . "\nLIMIT 100";
  }

  function data_prebuilt_queries(): array {
    return [
      'inventario_actual' => [
        'title'=>'Inventario actual por empresa/localidad/producto/almacén',
        'sql'=>"SELECT c.name AS empresa, l.name AS localidad, p.sku, p.name AS producto, w.code AS almacen, w.name AS almacen_nombre, ROUND(SUM(CASE WHEN im.move_type IN ('IN','ADJ') THEN im.qty WHEN im.move_type='OUT' THEN -im.qty ELSE 0 END),3) AS existencia\nFROM inventory_moves im\nJOIN companies c ON c.id = im.company_id\nJOIN locations l ON l.id = im.location_id\nJOIN products p ON p.id = im.product_id\nLEFT JOIN warehouses w ON w.id = im.warehouse_id\nGROUP BY c.name, l.name, p.sku, p.name, w.code, w.name\nHAVING ABS(existencia) > 0.001\nORDER BY c.name, l.name, p.sku, w.code\nLIMIT 500"
      ],
      'movimientos_recientes' => [
        'title'=>'Últimos movimientos de inventario',
        'sql'=>"SELECT im.id, im.created_at, im.work_date, c.name AS empresa, l.name AS localidad, p.sku, p.name AS producto, COALESCE(w.code,'SIN-WH') AS almacen, im.move_type, im.qty, im.ref_type, im.ref_id, COALESCE(u.full_name,u.username) AS usuario, im.notes\nFROM inventory_moves im\nJOIN companies c ON c.id=im.company_id\nJOIN locations l ON l.id=im.location_id\nJOIN products p ON p.id=im.product_id\nLEFT JOIN warehouses w ON w.id=im.warehouse_id\nLEFT JOIN users u ON u.id=im.created_by\nORDER BY im.id DESC\nLIMIT 200"
      ],
      'produccion_mes' => [
        'title'=>'Producción registrada por mes/producto',
        'sql'=>"SELECT c.name AS empresa, l.name AS localidad, DATE_FORMAT(im.work_date,'%Y-%m') AS mes, p.sku, p.name AS producto, SUM(im.qty) AS cantidad\nFROM inventory_moves im\nJOIN companies c ON c.id=im.company_id\nJOIN locations l ON l.id=im.location_id\nJOIN products p ON p.id=im.product_id\nWHERE im.ref_type IN ('PRODUCTION','PRODUCTION_MERMA') AND im.move_type IN ('IN','ADJ')\nGROUP BY c.name, l.name, DATE_FORMAT(im.work_date,'%Y-%m'), p.sku, p.name\nORDER BY mes DESC, empresa, localidad, p.sku\nLIMIT 300"
      ],
      'solicitudes_localidades' => [
        'title'=>'Solicitudes entre localidades y estatus',
        'sql'=>"SELECT r.id, c.name AS empresa, rl.name AS solicita, sl.name AS provee, r.status, r.request_date, r.required_date, COALESCE(u.full_name,u.username) AS solicitado_por, r.driver_name, r.vehicle_unit, r.reject_reason\nFROM warehouse_location_requests r\nJOIN companies c ON c.id=r.company_id\nJOIN locations rl ON rl.id=r.request_location_id\nJOIN locations sl ON sl.id=r.source_location_id\nLEFT JOIN users u ON u.id=r.requested_by\nORDER BY r.id DESC\nLIMIT 200"
      ],
      'reglas_almacen_producto' => [
        'title'=>'Reglas de almacén configuradas por producto/localidad',
        'sql'=>"SELECT c.name AS empresa, l.name AS localidad, p.sku, p.name AS producto, wi.code AS almacen_entrada, wo.code AS almacen_salida, wm.code AS almacen_merma, wa.code AS almacen_aprov, wc.code AS almacen_cubicado\nFROM product_warehouse_rules r\nJOIN companies c ON c.id=r.company_id\nJOIN locations l ON l.id=r.location_id\nJOIN products p ON p.id=r.product_id\nLEFT JOIN warehouses wi ON wi.id=r.default_finished_warehouse_id\nLEFT JOIN warehouses wo ON wo.id=r.default_sales_warehouse_id\nLEFT JOIN warehouses wm ON wm.id=r.merma_warehouse_id\nLEFT JOIN warehouses wa ON wa.id=r.aprovechamiento_warehouse_id\nLEFT JOIN warehouses wc ON wc.id=r.cubicado_warehouse_id\nORDER BY c.name, l.name, p.sku\nLIMIT 500"
      ]
    ];
  }

  if ($action === 'data_tables') {
    $st = $pdo->query("\n      SELECT table_name, table_rows, engine, create_time, update_time\n      FROM information_schema.tables\n      WHERE table_schema = DATABASE() AND table_type = 'BASE TABLE'\n      ORDER BY table_name\n    ");
    jexit(['ok'=>true,'rows'=>$st->fetchAll()]);
  }

  if ($action === 'data_columns') {
    $table = data_safe_identifier((string)($_GET['table'] ?? ''));
    if (!data_table_exists($pdo, $table)) jexit(['ok'=>false,'error'=>'Tabla no existe.'], 404);
    jexit(['ok'=>true,'rows'=>data_columns($pdo, $table)]);
  }

  if ($action === 'data_relationships') {
    $tables = array_filter(array_map('trim', explode(',', (string)($_GET['tables'] ?? ''))));
    try { jexit(['ok'=>true,'rows'=>data_relationships($pdo, $tables)]); }
    catch(Throwable $e){ jexit(['ok'=>false,'error'=>$e->getMessage()], 400); }
  }

  if ($action === 'data_generate_query') {
    $d = read_json();
    $tables = is_array($d['tables'] ?? null) ? $d['tables'] : [];
    try { jexit(['ok'=>true,'sql'=>data_build_query($pdo, $tables),'relationships'=>data_relationships($pdo, $tables)]); }
    catch(Throwable $e){ jexit(['ok'=>false,'error'=>$e->getMessage()], 400); }
  }

  if ($action === 'data_preset_queries') {
    $rows = [];
    foreach (data_prebuilt_queries() as $key=>$q) $rows[] = ['key'=>$key,'title'=>$q['title'],'sql'=>$q['sql']];
    jexit(['ok'=>true,'rows'=>$rows]);
  }

  if ($action === 'data_query_run') {
    $d = read_json();
    $sql = trim((string)($d['sql'] ?? ''));
    if ($sql === '') jexit(['ok'=>false,'error'=>'Consulta requerida.'], 400);
    $clean = rtrim($sql, " \t\n\r\0\x0B;");
    if (preg_match('/;\s*\S/', $sql)) jexit(['ok'=>false,'error'=>'Solo se permite una consulta por ejecución.'], 400);
    if (!preg_match('/^\s*(SELECT|WITH)\b/i', $clean)) jexit(['ok'=>false,'error'=>'Solo se permiten consultas SELECT/WITH de solo lectura.'], 400);
    if (preg_match('/\b(INSERT|UPDATE|DELETE|DROP|ALTER|CREATE|TRUNCATE|REPLACE|GRANT|REVOKE|CALL|LOAD|INTO\s+OUTFILE|LOCK|UNLOCK)\b/i', $clean)) {
      jexit(['ok'=>false,'error'=>'Consulta bloqueada: contiene palabra reservada de escritura o administración.'], 400);
    }
    if (!preg_match('/\bLIMIT\s+\d+/i', $clean)) $clean .= "\nLIMIT 200";
    try {
      $st = $pdo->query($clean);
      $rows = $st ? $st->fetchAll(PDO::FETCH_ASSOC) : [];
      $cols = $rows ? array_keys($rows[0]) : [];
      jexit(['ok'=>true,'sql'=>$clean,'columns'=>$cols,'rows'=>$rows,'count'=>count($rows)]);
    } catch(Throwable $e) {
      jexit(['ok'=>false,'error'=>$e->getMessage()], 400);
    }
  }




  /* ===================== Usuarios + Roles + Accesos ===================== */
  if ($action === 'roles_list') {
    jexit(['ok'=>true,'rows'=>db_roles_list($pdo)]);
  }

  if ($action === 'users_list') {
    $q = trim((string)($_GET['q'] ?? ''));
    if ($q !== '') {
      $st = $pdo->prepare("
        SELECT id, username, full_name, email, phone, active, created_at
        FROM users
        WHERE username LIKE ? OR full_name LIKE ? OR email LIKE ?
        ORDER BY id DESC
        LIMIT 300
      ");
      $like = "%{$q}%";
      $st->execute([$like,$like,$like]);
      jexit(['ok'=>true,'rows'=>$st->fetchAll()]);
    }

    $st = $pdo->query("
      SELECT id, username, full_name, email, phone, active, created_at
      FROM users
      ORDER BY id DESC
      LIMIT 300
    ");
    jexit(['ok'=>true,'rows'=>$st->fetchAll()]);
  }

  if ($action === 'users_get') {
    $id = (int)($_GET['id'] ?? 0);
    if ($id<=0) jexit(['ok'=>false,'error'=>'id requerido'], 400);

    $st = $pdo->prepare("
      SELECT id, username, full_name, email, phone, active
      FROM users
      WHERE id=?
      LIMIT 1
    ");
    $st->execute([$id]);
    $row = $st->fetch();
    if (!$row) jexit(['ok'=>false,'error'=>'Usuario no existe'], 404);

    $roles = db_user_roles($pdo, $id);
    $access = db_user_access($pdo, $id);
    jexit(['ok'=>true,'row'=>$row,'roles'=>$roles,'access'=>$access]);
  }

  if ($action === 'users_save') {
    $d = read_json();
    $id = (int)($d['id'] ?? 0);

    $username = trim((string)($d['username'] ?? ''));
    $fullName = trim((string)($d['full_name'] ?? ''));
    $email    = trim((string)($d['email'] ?? ''));
    $phone    = trim((string)($d['phone'] ?? ''));
    $active   = (int)($d['active'] ?? 1) ? 1 : 0;

    $password = (string)($d['password'] ?? ''); // opcional (solo si se envía)
    $roleCodes   = is_array($d['role_codes'] ?? null) ? (array)$d['role_codes'] : [];
    $companyIds  = is_array($d['company_ids'] ?? null) ? (array)$d['company_ids'] : [];
    $locationIds = is_array($d['location_ids'] ?? null) ? (array)$d['location_ids'] : [];

    if ($username==='' || $fullName==='') {
      jexit(['ok'=>false,'error'=>'username y full_name son requeridos'], 400);
    }

    // username único
    if ($id>0) {
      $st = $pdo->prepare("SELECT id FROM users WHERE username=? AND id<>? LIMIT 1");
      $st->execute([$username, $id]);
      if ($st->fetch()) jexit(['ok'=>false,'error'=>'username ya existe'], 409);
    } else {
      $st = $pdo->prepare("SELECT id FROM users WHERE username=? LIMIT 1");
      $st->execute([$username]);
      if ($st->fetch()) jexit(['ok'=>false,'error'=>'username ya existe'], 409);
    }

    // Insert/Update
    if ($id>0) {
      if ($password !== '') {
        $hash = password_hash($password, PASSWORD_DEFAULT);
        $st = $pdo->prepare("
          UPDATE users
          SET username=?, full_name=?, email=?, phone=?, active=?, pass_hash=?, updated_at=NOW()
          WHERE id=?
        ");
        $st->execute([$username,$fullName,$email?:null,$phone?:null,$active,$hash,$id]);
      } else {
        $st = $pdo->prepare("
          UPDATE users
          SET username=?, full_name=?, email=?, phone=?, active=?, updated_at=NOW()
          WHERE id=?
        ");
        $st->execute([$username,$fullName,$email?:null,$phone?:null,$active,$id]);
      }
      $userId = $id;
    } else {
      if ($password === '') {
        jexit(['ok'=>false,'error'=>'password requerido al crear usuario'], 400);
      }
      $hash = password_hash($password, PASSWORD_DEFAULT);
      $st = $pdo->prepare("
        INSERT INTO users(username, full_name, email, phone, pass_hash, active, created_at)
        VALUES(?,?,?,?,?,?,NOW())
      ");
      $st->execute([$username,$fullName,$email?:null,$phone?:null,$hash,$active]);
      $userId = (int)$pdo->lastInsertId();
    }

    // Roles + accesos
    try {
      db_set_user_roles($pdo, $userId, $roleCodes);
      db_set_user_access($pdo, $userId, $companyIds, $locationIds);
    } catch (Throwable $e) {
      jexit(['ok'=>false,'error'=>'Guardó usuario pero falló roles/accesos: '.$e->getMessage()], 500);
    }

    jexit(['ok'=>true,'id'=>$userId]);
  }

  jexit(['ok'=>false,'error'=>'Acción no válida'], 404);
}

/* ===================== UI ===================== */
$u = current_user();
$csrf = csrf_token();
?>
<!doctype html>
<html lang="es">
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <title>BReport · Admin Configuración</title>
  <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet">
  <link href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.11.3/font/bootstrap-icons.min.css" rel="stylesheet">
  <style>
    body{background:#f6f7fb}
    .mono{font-family:ui-monospace, SFMono-Regular, Menlo, Monaco, Consolas, "Liberation Mono", "Courier New", monospace;}
    .btn-pill{border-radius:999px}
    .cardHub{
      cursor:pointer;
      border:1px solid rgba(0,0,0,.06);
      transition:transform .12s ease, box-shadow .12s ease, border-color .12s ease;
      border-radius:18px;
    }
    .cardHub:hover{transform:translateY(-2px);box-shadow:0 12px 30px rgba(0,0,0,.08);border-color:rgba(0,0,0,.12);}
    .cardHub.active{border-color:rgba(13,110,253,.35); box-shadow:0 14px 34px rgba(13,110,253,.12);}
    .hubIcon{
      width:46px;height:46px;border-radius:14px;
      display:flex;align-items:center;justify-content:center;
      background:rgba(13,110,253,.08);
      color:#0d6efd;font-size:1.35rem;
      flex:0 0 auto;
    }
    .muted{color:#6c757d}
    .panel{display:none}
    .panel.show{display:block}
    .stickyTop{
      position:relative; top:auto; z-index:1;
      background:#f6f7fb;
      padding-top:10px;
      padding-bottom:16px;
      margin-bottom:22px;
    }
    .adminHub{margin-top:14px;margin-bottom:12px;}
    .mainPanelCard{margin-top:28px !important;position:relative;z-index:0;}
    .table-responsive{background:#fff;}
    #panel-data textarea{resize:vertical;}
    .miniHint{font-size:.85rem;color:#6c757d}
    .checkgrid{max-height:240px;overflow:auto;border:1px solid rgba(0,0,0,.08);border-radius:12px;padding:10px;background:#fff}
    .checkgrid .form-check{margin-bottom:6px}

    .invAdjustLocked{filter:blur(1px);opacity:.45;pointer-events:none}
    .qty-pos{color:#198754;font-weight:700}
    .qty-neg{color:#dc3545;font-weight:700}
    .qty-zero{color:#6c757d;font-weight:700}
    .whRuleCard{border:1px solid rgba(0,0,0,.08);border-radius:16px;background:#fff;padding:14px;margin-bottom:12px}
    .whRuleTitle{font-weight:800;color:#0c2340}
    .whRuleGrid{display:grid;grid-template-columns:repeat(auto-fit,minmax(210px,1fr));gap:12px}
  </style>
</head>
<body>
<nav class="navbar bg-white border-bottom">
  <div class="container-fluid">
    <span class="navbar-brand fw-bold">BReport · Admin</span>
    <div class="ms-auto d-flex gap-2 align-items-center">
      <span class="text-muted small"><?=h($u['full_name'])?></span>
      <a class="btn btn-outline-secondary btn-sm" href="dashboard.php">Dashboard</a>
      <a class="btn btn-outline-danger btn-sm" href="logout.php">Salir</a>
    </div>
  </div>
</nav>

<div class="container py-4">

  <div class="stickyTop">
    <div class="d-flex flex-wrap gap-2 align-items-center">
      <div>
        <div class="fw-bold fs-4">Configuración</div>
        <div class="muted">Selecciona un módulo para administrar catálogos / accesos</div>
      </div>

      <div class="ms-auto d-flex gap-2 align-items-center">
        <select id="companyFilter" class="form-select form-select-sm" style="min-width:260px"></select>
        <button id="btnReload" class="btn btn-outline-primary btn-sm btn-pill">
          <i class="bi bi-arrow-repeat me-1"></i>Recargar
        </button>
      </div>
    </div>

    <!-- HUB Cards -->
    <div class="row g-3 mt-1 adminHub">
      <div class="col-12 col-md-6 col-xl-4">
        <div class="card cardHub active" data-panel="loc">
          <div class="card-body d-flex gap-3 align-items-center">
            <div class="hubIcon"><i class="bi bi-geo-alt"></i></div>
            <div class="flex-grow-1">
              <div class="fw-semibold">Localidades</div>
              <div class="muted small">Altas, edición, timezone, activas/inactivas</div>
            </div>
            <i class="bi bi-chevron-right muted"></i>
          </div>
        </div>
      </div>

      <div class="col-12 col-md-6 col-xl-4">
        <div class="card cardHub" data-panel="wh">
          <div class="card-body d-flex gap-3 align-items-center">
            <div class="hubIcon" style="background:rgba(32,201,151,.12);color:#087f5b"><i class="bi bi-houses"></i></div>
            <div class="flex-grow-1">
              <div class="fw-semibold">Almacenes</div>
              <div class="muted small">Alta por empresa y localidad: CF1, CF2, CF3, MERMA...</div>
            </div>
            <i class="bi bi-chevron-right muted"></i>
          </div>
        </div>
      </div>

      <div class="col-12 col-md-6 col-xl-4">
        <div class="card cardHub" data-panel="prod">
          <div class="card-body d-flex gap-3 align-items-center">
            <div class="hubIcon"><i class="bi bi-box-seam"></i></div>
            <div class="flex-grow-1">
              <div class="fw-semibold">Productos</div>
              <div class="muted small">Artículo, descripción, UOM, ID ERP (#DLAX)</div>
            </div>
            <i class="bi bi-chevron-right muted"></i>
          </div>
        </div>
      </div>

      <div class="col-12 col-md-6 col-xl-4">
        <div class="card cardHub" data-panel="chan">
          <div class="card-body d-flex gap-3 align-items-center">
            <div class="hubIcon"><i class="bi bi-truck"></i></div>
            <div class="flex-grow-1">
              <div class="fw-semibold">Canales</div>
              <div class="muted small">Salida venta/transferencia/interno + orden</div>
            </div>
            <i class="bi bi-chevron-right muted"></i>
          </div>
        </div>
      </div>

      <div class="col-12 col-md-6 col-xl-4">
        <div class="card cardHub" data-panel="qual">
          <div class="card-body d-flex gap-3 align-items-center">
            <div class="hubIcon" style="background:rgba(25,135,84,.08);color:#198754"><i class="bi bi-shield-check"></i></div>
            <div class="flex-grow-1">
              <div class="fw-semibold">Conceptos Calidad</div>
              <div class="muted small">Merma / Scrap (configurable por empresa)</div>
            </div>
            <i class="bi bi-chevron-right muted"></i>
          </div>
        </div>
      </div>

      <div class="col-12 col-md-6 col-xl-4">
        <div class="card cardHub" data-panel="users">
          <div class="card-body d-flex gap-3 align-items-center">
            <div class="hubIcon" style="background:rgba(111,66,193,.08);color:#6f42c1"><i class="bi bi-people"></i></div>
            <div class="flex-grow-1">
              <div class="fw-semibold">Usuarios & Accesos</div>
              <div class="muted small">Alta usuarios + roles + empresas/localidades</div>
            </div>
            <i class="bi bi-chevron-right muted"></i>
          </div>
        </div>
      </div>


      <div class="col-12 col-md-6 col-xl-4">
        <div class="card cardHub" data-panel="invadj">
          <div class="card-body d-flex gap-3 align-items-center">
            <div class="hubIcon" style="background:rgba(255,193,7,.12);color:#b58100"><i class="bi bi-sliders"></i></div>
            <div class="flex-grow-1">
              <div class="fw-semibold">Ajuste almacenes</div>
              <div class="muted small">Ver existencias por planta y agregar/quitar stock</div>
            </div>
            <i class="bi bi-lock-fill muted"></i>
          </div>
        </div>
      </div>


      <div class="col-12 col-md-6 col-xl-4">
        <div class="card cardHub" data-panel="audit">
          <div class="card-body d-flex gap-3 align-items-center">
            <div class="hubIcon" style="background:rgba(13,202,240,.12);color:#087990"><i class="bi bi-clipboard-data"></i></div>
            <div class="flex-grow-1">
              <div class="fw-semibold">Auditoría movimientos</div>
              <div class="muted small">Kardex operativo: inventario, producción, calidad, ventas y transferencias</div>
            </div>
            <i class="bi bi-search muted"></i>
          </div>
        </div>
      </div>

      <div class="col-12 col-md-6 col-xl-4">
        <div class="card cardHub" data-panel="data">
          <div class="card-body d-flex gap-3 align-items-center">
            <div class="hubIcon" style="background:rgba(102,16,242,.10);color:#6610f2"><i class="bi bi-database-check"></i></div>
            <div class="flex-grow-1">
              <div class="fw-semibold">Análisis de datos</div>
              <div class="muted small">Explorador de tablas, relaciones y consultas seguras</div>
            </div>
            <i class="bi bi-braces muted"></i>
          </div>
        </div>
      </div>

    </div>
  </div>

  <!-- Panels -->
  <div class="card shadow-sm rounded-4 mt-4 mainPanelCard">
    <div class="card-body">

      <!-- Localidades -->
      <div class="panel show" id="panel-loc">
        <div class="d-flex flex-wrap gap-2 align-items-center">
          <div>
            <div class="fw-bold fs-5"><i class="bi bi-geo-alt me-2"></i>Localidades</div>
            <div class="muted small">Administra localidades por empresa</div>
          </div>
          <div class="ms-auto">
            <button class="btn btn-primary btn-sm btn-pill" id="addLoc"><i class="bi bi-plus-lg me-1"></i>Nueva localidad</button>
          </div>
        </div>
        <div class="table-responsive mt-3">
          <table class="table table-sm align-middle">
            <thead>
              <tr>
                <th>ID</th><th>Empresa</th><th>Nombre</th><th class="mono">Code</th><th>Timezone</th><th>Activa</th><th></th>
              </tr>
            </thead>
            <tbody id="locBody"></tbody>
          </table>
        </div>
      </div>

      <!-- Almacenes -->
      <div class="panel" id="panel-wh">
        <div class="d-flex flex-wrap gap-2 align-items-center">
          <div>
            <div class="fw-bold fs-5"><i class="bi bi-houses me-2"></i>Almacenes</div>
            <div class="muted small">Administra almacenes por empresa y localidad. Estos almacenes son usados por inventory_moves y reglas de producto.</div>
          </div>
          <div class="ms-auto">
            <button class="btn btn-primary btn-sm btn-pill" id="addWh"><i class="bi bi-plus-lg me-1"></i>Nuevo almacén</button>
          </div>
        </div>
        <div class="alert alert-info mt-3 mb-2 py-2">
          Recomendado: <span class="mono">CF1</span>, <span class="mono">CF2</span>, <span class="mono">CF3</span>, <span class="mono">CUB</span>, <span class="mono">MERMA</span>, <span class="mono">APROV-MERMA</span>, <span class="mono">SOB-CUB</span>.
          Cada localidad debe tener sus propios almacenes activos. No afecta inventario por sí solo.
        </div>
        <div class="table-responsive mt-3">
          <table class="table table-sm align-middle">
            <thead>
              <tr>
                <th>ID</th><th>Empresa</th><th>Localidad</th><th class="mono">Código</th><th>Nombre</th><th>Activo</th><th></th>
              </tr>
            </thead>
            <tbody id="whBody"></tbody>
          </table>
        </div>
      </div>


      <!-- Productos -->
      <div class="panel" id="panel-prod">
        <div class="d-flex flex-wrap gap-2 align-items-center">
          <div>
            <div class="fw-bold fs-5"><i class="bi bi-box-seam me-2"></i>Productos</div>
            <div class="muted small">Catálogo por empresa</div>
          </div>
          <div class="ms-auto">
            <button class="btn btn-primary btn-sm btn-pill" id="addProd"><i class="bi bi-plus-lg me-1"></i>Nuevo producto</button>
          </div>
        </div>
        <div class="table-responsive mt-3">
          <table class="table table-sm align-middle">
            <thead>
              <tr>
                <th>ID</th><th>Empresa</th><th class="mono">Artículo</th><th>Descripción</th><th class="mono"># DLAX</th><th>UOM</th><th>Activo</th><th>Almacenes</th><th></th>
              </tr>
            </thead>
            <tbody id="prodBody"></tbody>
          </table>
        </div>
      </div>

      <!-- Canales -->
      <div class="panel" id="panel-chan">
        <div class="d-flex flex-wrap gap-2 align-items-center">
          <div>
            <div class="fw-bold fs-5"><i class="bi bi-truck me-2"></i>Canales</div>
            <div class="muted small">Canales de salida por empresa</div>
          </div>
          <div class="ms-auto">
            <button class="btn btn-primary btn-sm btn-pill" id="addChan"><i class="bi bi-plus-lg me-1"></i>Nuevo canal</button>
          </div>
        </div>
        <div class="table-responsive mt-3">
          <table class="table table-sm align-middle">
            <thead>
              <tr>
                <th>ID</th><th>Empresa</th><th>Nombre</th><th>Tipo</th><th>Orden</th><th>Activo</th><th></th>
              </tr>
            </thead>
            <tbody id="chanBody"></tbody>
          </table>
        </div>
      </div>

      <!-- Calidad -->
      <div class="panel" id="panel-qual">
        <div class="d-flex flex-wrap gap-2 align-items-center">
          <div>
            <div class="fw-bold fs-5"><i class="bi bi-shield-check me-2"></i>Conceptos Calidad</div>
            <div class="muted small">Configura conceptos por empresa: Merma / Scrap</div>
          </div>
          <div class="ms-auto d-flex gap-2 align-items-center">
            <select id="qcType" class="form-select form-select-sm" style="min-width:160px">
              <option value="MERMA">MERMA</option>
              <option value="SCRAP">SCRAP</option>
            </select>
            <button class="btn btn-success btn-sm btn-pill" id="addQc"><i class="bi bi-plus-lg me-1"></i>Nuevo concepto</button>
          </div>
        </div>
        <div class="miniHint mt-2">Tip: puedes ordenar (sort_order) para controlar el orden en captura.</div>

        <div class="table-responsive mt-3">
          <table class="table table-sm align-middle">
            <thead>
              <tr>
                <th>ID</th><th>Empresa</th><th>Tipo</th><th>Concepto</th><th>Orden</th><th>Activo</th><th></th>
              </tr>
            </thead>
            <tbody id="qcBody"></tbody>
          </table>
        </div>
      </div>

      <!-- Usuarios -->
      <div class="panel" id="panel-users">
        <div class="d-flex flex-wrap gap-2 align-items-center">
          <div>
            <div class="fw-bold fs-5"><i class="bi bi-people me-2"></i>Usuarios & Accesos</div>
            <div class="muted small">Alta/edición + roles + empresas/localidades permitidas</div>
          </div>
          <div class="ms-auto d-flex gap-2 align-items-center">
            <input id="uSearch" class="form-control form-control-sm" placeholder="Buscar usuario..." style="min-width:240px">
            <button class="btn btn-outline-secondary btn-sm btn-pill" id="btnUSearch"><i class="bi bi-search me-1"></i>Buscar</button>
            <button class="btn btn-primary btn-sm btn-pill" id="addUser"><i class="bi bi-person-plus me-1"></i>Nuevo usuario</button>
          </div>
        </div>

        <div class="table-responsive mt-3">
          <table class="table table-sm align-middle">
            <thead>
              <tr>
                <th>ID</th><th>Username</th><th>Nombre</th><th>Email</th><th>Tel</th><th>Activo</th><th></th>
              </tr>
            </thead>
            <tbody id="userBody"></tbody>
          </table>
        </div>
      </div>



      <!-- Ajuste Almacenes -->
      <div class="panel" id="panel-invadj">
        <div class="d-flex flex-wrap gap-2 align-items-center">
          <div>
            <div class="fw-bold fs-5"><i class="bi bi-sliders me-2"></i>Ajuste de almacenes</div>
            <div class="muted small">Consulta la existencia oficial desde inventory_moves y registra entradas/salidas de ajuste.</div>
          </div>
          <div class="ms-auto d-flex gap-2 align-items-center">
            <button class="btn btn-warning btn-sm btn-pill" id="btnInvUnlock"><i class="bi bi-lock me-1"></i>Validar código</button>
            <button class="btn btn-outline-primary btn-sm btn-pill" id="btnInvReload"><i class="bi bi-arrow-repeat me-1"></i>Actualizar</button>
          </div>
        </div>

        <div class="alert alert-warning mt-3 mb-3 py-2">
          <strong>Control:</strong> para modificar inventario se requiere el código autorizado. Todo cambio se registra en <span class="mono">inventory_moves</span>.
          <br><strong>Ajuste manual:</strong> usa <span class="mono">ref_type = AJUSTE_ALMACEN</span> para agregar/quitar una cantidad directa.
          <br><strong>Corte físico:</strong> usa <span class="mono">ref_type = CORTE_FISICO_WH</span>; capturas la cantidad física objetivo y el sistema calcula automáticamente la diferencia por producto + almacén.
        </div>

        <div id="invAdjustArea" class="invAdjustLocked">
          <div class="row g-3 align-items-end">
            <div class="col-12 col-md-4">
              <label class="form-label">Empresa</label>
              <select id="invCompany" class="form-select"></select>
            </div>
            <div class="col-12 col-md-4">
              <label class="form-label">Planta / localidad</label>
              <select id="invLocation" class="form-select"></select>
            </div>
            <div class="col-12 col-md-4">
              <label class="form-label">Almacén</label>
              <select id="invWarehouse" class="form-select"></select>
            </div>

            <div class="col-12 col-md-5">
              <label class="form-label">Producto</label>
              <select id="invProduct" class="form-select"></select>
            </div>
            <div class="col-12 col-md-2">
              <label class="form-label">Movimiento</label>
              <select id="invKind" class="form-select">
                <option value="ADD">Agregar</option>
                <option value="SUB">Quitar</option>
              </select>
            </div>
            <div class="col-12 col-md-2">
              <label class="form-label">Cantidad</label>
              <input id="invQty" class="form-control mono" type="number" min="0" step="0.001" value="0">
            </div>
            <div class="col-12 col-md-3">
              <label class="form-label">Nota</label>
              <input id="invNotes" class="form-control" placeholder="Motivo del ajuste">
            </div>
            <div class="col-12 d-flex justify-content-end">
              <button class="btn btn-primary btn-pill" id="btnInvSave"><i class="bi bi-save me-1"></i>Guardar ajuste manual</button>
            </div>
          </div>

          <div class="border rounded-4 p-3 mt-3 bg-light">
            <div class="d-flex flex-wrap gap-2 align-items-center mb-2">
              <div>
                <div class="fw-bold"><i class="bi bi-clipboard-check me-1"></i>Corte físico por almacén</div>
                <div class="muted small">Selecciona producto + almacén, captura la cantidad física real y el sistema agregará o quitará solo la diferencia necesaria.</div>
              </div>
              <div class="ms-auto">
                <span class="badge text-bg-info">ref_type: CORTE_FISICO_WH</span>
              </div>
            </div>
            <div class="row g-3 align-items-end">
              <div class="col-12 col-md-3">
                <label class="form-label">Cantidad física objetivo</label>
                <input id="invTargetQty" class="form-control mono" type="number" min="0" step="0.001" value="0">
              </div>
              <div class="col-12 col-md-6">
                <label class="form-label">Nota del corte</label>
                <input id="invCutNotes" class="form-control" placeholder="Ej. Corte físico 3 pm / conteo planta">
              </div>
              <div class="col-12 col-md-3 d-grid">
                <button class="btn btn-success btn-pill" id="btnInvCutSave"><i class="bi bi-check2-circle me-1"></i>Aplicar corte</button>
              </div>
            </div>
            <div class="small text-muted mt-2">
              Ejemplo: si CF3 tiene 500 y el conteo físico dice 638, se agrega +138. Si tiene 700, se quitan -62. No borra histórico.
            </div>
          </div>

          <hr>

          <div class="row g-3">
            <div class="col-12 col-xl-5">
              <div class="fw-semibold mb-2">Totales por producto</div>
              <div class="table-responsive" style="max-height:420px;overflow:auto">
                <table class="table table-sm align-middle">
                  <thead><tr><th>SKU</th><th>Producto</th><th class="text-end">Total</th></tr></thead>
                  <tbody id="invTotalsBody"></tbody>
                </table>
              </div>
            </div>
            <div class="col-12 col-xl-7">
              <div class="fw-semibold mb-2">Detalle por almacén</div>
              <div class="table-responsive" style="max-height:420px;overflow:auto">
                <table class="table table-sm align-middle">
                  <thead><tr><th>SKU</th><th>Almacén</th><th class="text-end">Existencia</th></tr></thead>
                  <tbody id="invDetailBody"></tbody>
                </table>
              </div>
            </div>
          </div>
        </div>
      </div>



      <!-- Auditoría de movimientos -->
      <div class="panel" id="panel-audit">
        <div class="d-flex flex-wrap gap-2 align-items-center">
          <div>
            <div class="fw-bold fs-5"><i class="bi bi-clipboard-data me-2"></i>Auditoría de movimientos / Kardex operativo</div>
            <div class="muted small">Consulta movimientos reales de <span class="mono">inventory_moves</span> y corrige casos autorizados con trazabilidad.</div>
          </div>
          <div class="ms-auto d-flex gap-2 align-items-center">
            <button class="btn btn-outline-primary btn-sm btn-pill" id="btnAuditReload"><i class="bi bi-arrow-repeat me-1"></i>Consultar</button>
          </div>
        </div>

        <div class="alert alert-info mt-3 mb-3 py-2">
          Esta sección permite rastrear entradas, salidas, ajustes, producción, calidad, ventas, VentasHielo, transferencias y cortes físicos. Las correcciones requieren código de autorización y quedan registradas en bitácora.
        </div>

        <div class="row g-3 align-items-end">
          <div class="col-12 col-md-3"><label class="form-label">Empresa</label><select id="audCompany" class="form-select"></select></div>
          <div class="col-12 col-md-3"><label class="form-label">Planta</label><select id="audLocation" class="form-select"></select></div>
          <div class="col-6 col-md-2"><label class="form-label">Desde</label><input id="audDateFrom" class="form-control" type="date"></div>
          <div class="col-6 col-md-2"><label class="form-label">Hasta</label><input id="audDateTo" class="form-control" type="date"></div>
          <div class="col-12 col-md-2"><label class="form-label">Movimiento</label><select id="audMoveType" class="form-select"><option value="">Todos</option><option value="IN">IN</option><option value="OUT">OUT</option><option value="ADJ">ADJ</option></select></div>
          <div class="col-12 col-md-3"><label class="form-label">Producto</label><select id="audProduct" class="form-select"><option value="0">Todos</option></select></div>
          <div class="col-12 col-md-3"><label class="form-label">Almacén</label><select id="audWarehouse" class="form-select"><option value="0">Todos</option></select></div>
          <div class="col-12 col-md-2"><label class="form-label">ref_type</label><input id="audRefType" class="form-control mono" placeholder="Ej. PRODUCTION"></div>
          <div class="col-12 col-md-4"><label class="form-label">Búsqueda libre</label><input id="audSearch" class="form-control" placeholder="SKU, nota, ref_id, usuario, almacén..."></div>
          <div class="col-12 col-md-2"><label class="form-label">Filas por página</label><select id="audPerPage" class="form-select"><option value="25">25</option><option value="50" selected>50</option><option value="100">100</option><option value="200">200</option></select></div>
        </div>

        <div class="row g-3 mt-3">
          <div class="col-6 col-xl-2"><div class="card border-0 bg-light rounded-4"><div class="card-body"><div class="muted small">Movimientos</div><div class="fw-bold fs-4 mono" id="audMovesCount">0</div></div></div></div>
          <div class="col-6 col-xl-2"><div class="card border-0 bg-light rounded-4"><div class="card-body"><div class="muted small">Total IN</div><div class="fw-bold fs-4 mono qty-pos" id="audTotalIn">0.000</div></div></div></div>
          <div class="col-6 col-xl-2"><div class="card border-0 bg-light rounded-4"><div class="card-body"><div class="muted small">Total OUT</div><div class="fw-bold fs-4 mono qty-neg" id="audTotalOut">0.000</div></div></div></div>
          <div class="col-6 col-xl-2"><div class="card border-0 bg-light rounded-4"><div class="card-body"><div class="muted small">Total ADJ</div><div class="fw-bold fs-4 mono" id="audTotalAdj">0.000</div></div></div></div>
          <div class="col-12 col-xl-4"><div class="card border-0 bg-light rounded-4"><div class="card-body"><div class="muted small">Saldo neto</div><div class="fw-bold fs-4 mono" id="audNetQty">0.000</div></div></div></div>
        </div>

        <div class="row g-3 mt-3">
          <div class="col-12 col-xl-8">
            <div class="d-flex flex-wrap gap-2 justify-content-between align-items-center mb-2">
              <div>
                <div class="fw-semibold">Movimientos encontrados</div>
                <div class="small text-muted" id="audPageInfo">Página 1 de 1 · 0 registros</div>
              </div>
              <div class="btn-group btn-group-sm" role="group" aria-label="Paginación auditoría">
                <button type="button" class="btn btn-outline-secondary" id="audFirstPage"><i class="bi bi-chevron-double-left"></i></button>
                <button type="button" class="btn btn-outline-secondary" id="audPrevPage"><i class="bi bi-chevron-left"></i> Anterior</button>
                <button type="button" class="btn btn-outline-secondary" id="audNextPage">Siguiente <i class="bi bi-chevron-right"></i></button>
                <button type="button" class="btn btn-outline-secondary" id="audLastPage"><i class="bi bi-chevron-double-right"></i></button>
              </div>
            </div>
            <div class="table-responsive" style="max-height:520px;overflow:auto">
              <table class="table table-sm align-middle">
                <thead><tr><th>Fecha/hora</th><th>Operativa</th><th>Producto</th><th>Almacén</th><th>Mov.</th><th class="text-end">Cant.</th><th>ref_type</th><th>ref_id</th><th>Usuario</th><th>Notas</th><th class="text-end">Acción</th></tr></thead>
                <tbody id="audBody"></tbody>
              </table>
            </div>
          </div>
          <div class="col-12 col-xl-4">
            <div class="fw-semibold mb-2">Resumen por ref_type</div>
            <div class="table-responsive mb-3" style="max-height:245px;overflow:auto">
              <table class="table table-sm align-middle"><thead><tr><th>ref_type</th><th class="text-end">Movs</th><th class="text-end">Neto</th></tr></thead><tbody id="audRefBody"></tbody></table>
            </div>
            <div class="fw-semibold mb-2">Resumen por usuario</div>
            <div class="table-responsive" style="max-height:245px;overflow:auto">
              <table class="table table-sm align-middle"><thead><tr><th>Usuario</th><th class="text-end">Movs</th><th class="text-end">Neto</th></tr></thead><tbody id="audUserBody"></tbody></table>
            </div>
          </div>
        </div>
      </div>



      <!-- Análisis de datos -->
      <div class="panel" id="panel-data">
        <div class="d-flex flex-wrap gap-2 align-items-center">
          <div>
            <div class="fw-bold fs-5"><i class="bi bi-database-check me-2"></i>Análisis de datos</div>
            <div class="muted small">Explorador de tablas, relaciones sugeridas y ejecución segura de consultas SELECT. Solo administradores.</div>
          </div>
          <div class="ms-auto d-flex gap-2 align-items-center">
            <button class="btn btn-outline-primary btn-sm btn-pill" id="btnDataReload"><i class="bi bi-arrow-repeat me-1"></i>Recargar tablas</button>
            <button class="btn btn-success btn-sm btn-pill" id="btnDataRun"><i class="bi bi-play-fill me-1"></i>Ejecutar consulta</button>
          </div>
        </div>

        <div class="alert alert-warning mt-3 mb-3 py-2">
          Esta sección es de <strong>solo lectura</strong>. El servidor bloquea INSERT, UPDATE, DELETE, DROP, ALTER, CREATE, TRUNCATE y otras operaciones de escritura.
        </div>

        <div class="row g-3">
          <div class="col-12 col-xl-3">
            <div class="fw-semibold mb-2">Tablas de la base</div>
            <input id="dataTableSearch" class="form-control form-control-sm mb-2" placeholder="Buscar tabla...">
            <div class="checkgrid" style="max-height:520px" id="dataTablesBox"></div>
          </div>
          <div class="col-12 col-xl-5">
            <div class="d-flex flex-wrap gap-2 align-items-center mb-2">
              <div class="fw-semibold">Consulta generada / editable</div>
              <div class="ms-auto d-flex gap-2">
                <button class="btn btn-outline-secondary btn-sm" id="btnDataDescribe"><i class="bi bi-columns-gap me-1"></i>Ver columnas</button>
                <button class="btn btn-outline-primary btn-sm" id="btnDataGenerate"><i class="bi bi-magic me-1"></i>Generar SELECT</button>
              </div>
            </div>
            <textarea id="dataSql" class="form-control mono" rows="15" placeholder="Selecciona una o más tablas para generar una consulta SELECT..."></textarea>
            <div class="small text-muted mt-2" id="dataQueryInfo">Sin consulta ejecutada.</div>
          </div>
          <div class="col-12 col-xl-4">
            <div class="fw-semibold mb-2">Relaciones sugeridas</div>
            <div class="table-responsive" style="max-height:245px;overflow:auto">
              <table class="table table-sm align-middle">
                <thead><tr><th>Relación</th><th>Conf.</th></tr></thead>
                <tbody id="dataRelBody"></tbody>
              </table>
            </div>
            <hr>
            <div class="fw-semibold mb-2">Consultas relacionadas listas</div>
            <div class="list-group small" id="dataPresetBox" style="max-height:245px;overflow:auto"></div>
          </div>
        </div>

        <hr>
        <div class="d-flex flex-wrap gap-2 align-items-center mb-2">
          <div>
            <div class="fw-semibold">Resultado</div>
            <div class="small text-muted" id="dataResultInfo">0 registros</div>
          </div>
        </div>
        <div class="table-responsive" style="max-height:520px;overflow:auto">
          <table class="table table-sm table-striped align-middle">
            <thead id="dataResultHead"></thead>
            <tbody id="dataResultBody"></tbody>
          </table>
        </div>
      </div>

      <div class="text-muted small mt-3">
        CSRF activo · si algo falla, revisa consola y permisos MySQL.
      </div>
    </div>
  </div>
</div>

<!-- Modal editor (reutilizado para TODOS los módulos) -->
<div class="modal fade" id="editModal" tabindex="-1">
  <div class="modal-dialog modal-dialog-centered modal-lg">
    <form class="modal-content" id="editForm">
      <div class="modal-header">
        <div class="modal-title fw-bold" id="mTitle">Editar</div>
        <button type="button" class="btn-close" data-bs-dismiss="modal"></button>
      </div>
      <div class="modal-body" id="mBody"></div>
      <div class="modal-footer">
        <button type="button" class="btn btn-outline-secondary" data-bs-dismiss="modal">Cancelar</button>
        <button class="btn btn-primary" id="mSave">Guardar</button>
      </div>
    </form>
  </div>

</div>


<!-- Modal corrección controlada de movimiento -->
<div class="modal fade" id="moveEditModal" tabindex="-1">
  <div class="modal-dialog modal-xl modal-dialog-centered modal-dialog-scrollable">
    <form class="modal-content" id="moveEditForm">
      <div class="modal-header">
        <div>
          <div class="modal-title fw-bold"><i class="bi bi-pencil-square me-2"></i>Corregir movimiento de inventario</div>
          <div class="text-muted small" id="moveEditSub">Edición controlada sobre inventory_moves</div>
        </div>
        <button type="button" class="btn-close" data-bs-dismiss="modal"></button>
      </div>
      <div class="modal-body">
        <input type="hidden" id="moveEditId">
        <div class="alert alert-warning py-2">
          Esta acción modifica el kardex oficial. Úsala solo para corregir errores de captura: fecha, artículo, almacén, cantidad, tipo de movimiento o comentario. El cambio queda registrado en <span class="mono">inventory_move_admin_edits</span>.
        </div>
        <div class="row g-3">
          <div class="col-12 col-md-3">
            <label class="form-label">Fecha operativa</label>
            <input id="moveEditWorkDate" type="date" class="form-control" required>
          </div>
          <div class="col-12 col-md-3">
            <label class="form-label">Fecha/hora visible</label>
            <input id="moveEditCreatedAt" type="datetime-local" class="form-control" required>
          </div>
          <div class="col-12 col-md-3">
            <label class="form-label">Movimiento</label>
            <select id="moveEditMoveType" class="form-select" required>
              <option value="IN">IN · Entrada</option>
              <option value="OUT">OUT · Salida</option>
              <option value="ADJ">ADJ · Ajuste positivo</option>
            </select>
          </div>
          <div class="col-12 col-md-3">
            <label class="form-label">Cantidad</label>
            <input id="moveEditQty" type="number" step="0.001" min="0.001" class="form-control mono" required>
          </div>

          <div class="col-12 col-lg-6">
            <label class="form-label">Artículo</label>
            <select id="moveEditProduct" class="form-select" required></select>
          </div>
          <div class="col-12 col-lg-6">
            <label class="form-label">Almacén</label>
            <select id="moveEditWarehouse" class="form-select"></select>
          </div>

          <div class="col-12">
            <label class="form-label">Comentario / notas</label>
            <textarea id="moveEditNotes" class="form-control" rows="3" maxlength="255"></textarea>
          </div>
          <div class="col-12 col-md-6">
            <label class="form-label">Motivo de corrección</label>
            <input id="moveEditReason" class="form-control" maxlength="255" placeholder="Ej. Fecha incorrecta por desfase horario" required>
          </div>
          <div class="col-12 col-md-6">
            <label class="form-label">Código de autorización</label>
            <input id="moveEditCode" type="password" class="form-control mono" autocomplete="off" required>
          </div>

          <div class="col-12">
            <div class="small text-muted" id="moveEditMeta"></div>
          </div>
        </div>
      </div>
      <div class="modal-footer">
        <button type="button" class="btn btn-outline-secondary" data-bs-dismiss="modal">Cancelar</button>
        <button class="btn btn-danger" id="moveEditSave"><i class="bi bi-save me-1"></i>Guardar corrección</button>
      </div>
    </form>
  </div>
</div>

<!-- Modal reglas de almacén por producto/localidad -->
<div class="modal fade" id="whRulesModal" tabindex="-1">
  <div class="modal-dialog modal-xl modal-dialog-centered modal-dialog-scrollable">
    <div class="modal-content">
      <div class="modal-header">
        <div>
          <div class="modal-title fw-bold" id="whRulesTitle">Almacenes por producto</div>
          <div class="text-muted small" id="whRulesSub">Configura reglas por localidad.</div>
        </div>
        <button type="button" class="btn-close" data-bs-dismiss="modal"></button>
      </div>
      <div class="modal-body">
        <div class="alert alert-info py-2">
          Esta configuración define la regla operativa esperada por producto y localidad. No mueve inventario; los movimientos reales seguirán generándose en <span class="mono">inventory_moves</span> desde Producción, Almacén, Calidad, Ventas o Transferencias.
        </div>
        <div id="whRulesBody"></div>
      </div>
      <div class="modal-footer">
        <button type="button" class="btn btn-outline-secondary" data-bs-dismiss="modal">Cerrar</button>
      </div>
    </div>
  </div>
</div>

<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js"></script>
<script>
const CSRF = <?=json_encode($csrf)?>;

async function apiGet(action, params={}){
  const u = new URL(location.href);
  u.search = '';
  u.searchParams.set('action', action);
  for(const [k,v] of Object.entries(params)) u.searchParams.set(k, v);
  const r = await fetch(u.toString(), {credentials:'same-origin'});
  return await r.json();
}
async function apiPost(action, payload){
  const u = new URL(location.href);
  u.search = '';
  u.searchParams.set('action', action);
  const r = await fetch(u.toString(), {
    method:'POST',
    credentials:'same-origin',
    headers:{'Content-Type':'application/json','X-CSRF':CSRF},
    body: JSON.stringify(payload||{})
  });
  return await r.json();
}

function h(s){return String(s??'').replace(/[&<>"]/g,m=>({ '&':'&amp;','<':'&lt;','>':'&gt;','"':'&quot;' }[m]));}
const $ = (id)=>document.getElementById(id);

let companies = [];
let roles = [];
let auditPage = 1;
let auditTotalPages = 1;
let invUnlocked = false;
let invCode = '';
let modal, whRulesModal, moveEditModal, mode='', editing=null;

function companyFilterId(){
  const v = parseInt($('companyFilter').value||'0',10);
  return isNaN(v)?0:v;
}
function boolBadge(v){
  return v ? '<span class="badge text-bg-success">Sí</span>' : '<span class="badge text-bg-secondary">No</span>';
}

/* ===== Loaders ===== */
async function loadCompanies(){
  const j = await apiGet('companies');
  if(!j.ok) throw new Error(j.error||'No companies');
  companies = j.rows || [];
  const sel = $('companyFilter');
  sel.innerHTML = '';
  sel.insertAdjacentHTML('beforeend', `<option value="0">Todas las empresas</option>`);
  for(const c of companies){
    sel.insertAdjacentHTML('beforeend', `<option value="${c.id}">${h(c.name)}</option>`);
  }
}
async function loadRoles(){
  const j = await apiGet('roles_list');
  roles = j.ok ? (j.rows||[]) : [];
}

/* ===== Tables ===== */
async function loadLocations(){
  const j = await apiGet('locations_list', {company_id: companyFilterId()});
  if(!j.ok) return;
  const tb = $('locBody');
  tb.innerHTML = '';
  for(const r of (j.rows||[])){
    tb.insertAdjacentHTML('beforeend', `
      <tr>
        <td class="text-muted">${r.id}</td>
        <td>${h(r.company_name)}</td>
        <td>${h(r.name)}</td>
        <td class="mono">${h(r.code)}</td>
        <td class="mono">${h(r.timezone)}</td>
        <td>${boolBadge(parseInt(r.active,10)===1)}</td>
        <td class="text-end">
          <button class="btn btn-outline-primary btn-sm" data-edit="loc" data-id="${r.id}">
            <i class="bi bi-pencil"></i>
          </button>
        </td>
      </tr>
    `);
  }
}

async function loadWarehouses(){
  const j = await apiGet('warehouses_list', {company_id: companyFilterId()});
  if(!j.ok) return;
  const tb = $('whBody');
  if(!tb) return;
  tb.innerHTML = '';
  for(const r of (j.rows||[])){
    tb.insertAdjacentHTML('beforeend', `
      <tr>
        <td class="text-muted">${r.id}</td>
        <td>${h(r.company_name)}</td>
        <td>${h(r.location_name)} <span class="text-muted small mono">${h(r.location_code||'')}</span></td>
        <td class="mono fw-semibold">${h(r.code)}</td>
        <td>${h(r.name)}</td>
        <td>${boolBadge(parseInt(r.active,10)===1)}</td>
        <td class="text-end">
          <button class="btn btn-outline-primary btn-sm" data-edit="wh" data-id="${r.id}">
            <i class="bi bi-pencil"></i>
          </button>
        </td>
      </tr>
    `);
  }
}


async function loadProducts(){
  const j = await apiGet('products_list', {company_id: companyFilterId()});
  if(!j.ok) return;
  const tb = $('prodBody');
  tb.innerHTML = '';
  for(const r of (j.rows||[])){
    tb.insertAdjacentHTML('beforeend', `
      <tr>
        <td class="text-muted">${r.id}</td>
        <td>${h(r.company_name)}</td>
        <td class="mono">${h(r.sku)}</td>
        <td>${h(r.name)}</td>
        <td class="mono">${h(r.erp_id ?? '')}</td>
        <td class="mono">${h(r.uom)}</td>
        <td>${boolBadge(parseInt(r.active,10)===1)}</td>
        <td>
          <button class="btn btn-outline-info btn-sm btn-pill" data-whrules="1" data-id="${r.id}" data-company-id="${r.company_id}" data-sku="${h(r.sku)}" data-name="${h(r.name)}">
            <i class="bi bi-houses me-1"></i>Ver / configurar
          </button>
        </td>
        <td class="text-end">
          <button class="btn btn-outline-primary btn-sm" data-edit="prod" data-id="${r.id}">
            <i class="bi bi-pencil"></i>
          </button>
        </td>
      </tr>
    `);
  }
}

async function loadChannels(){
  const j = await apiGet('channels_list', {company_id: companyFilterId()});
  if(!j.ok) return;
  const tb = $('chanBody');
  tb.innerHTML = '';
  for(const r of (j.rows||[])){
    tb.insertAdjacentHTML('beforeend', `
      <tr>
        <td class="text-muted">${r.id}</td>
        <td>${h(r.company_name)}</td>
        <td>${h(r.name)}</td>
        <td class="mono">${h(r.channel_type)}</td>
        <td class="mono">${h(r.sort_order)}</td>
        <td>${boolBadge(parseInt(r.active,10)===1)}</td>
        <td class="text-end">
          <button class="btn btn-outline-primary btn-sm" data-edit="chan" data-id="${r.id}">
            <i class="bi bi-pencil"></i>
          </button>
        </td>
      </tr>
    `);
  }
}

async function loadQualityConcepts(){
  const type = $('qcType').value || 'MERMA';
  const j = await apiGet('quality_concepts_list', {company_id: companyFilterId(), concept_type: type});
  if(!j.ok) return;
  const tb = $('qcBody');
  tb.innerHTML = '';
  for(const r of (j.rows||[])){
    tb.insertAdjacentHTML('beforeend', `
      <tr>
        <td class="text-muted">${r.id}</td>
        <td>${h(r.company_name)}</td>
        <td class="mono">${h(r.concept_type)}</td>
        <td>${h(r.name)}</td>
        <td class="mono">${h(r.sort_order)}</td>
        <td>${boolBadge(parseInt(r.active,10)===1)}</td>
        <td class="text-end">
          <button class="btn btn-outline-primary btn-sm" data-edit="qc" data-id="${r.id}">
            <i class="bi bi-pencil"></i>
          </button>
        </td>
      </tr>
    `);
  }
}

async function loadUsers(q=''){
  const j = await apiGet('users_list', q ? {q} : {});
  if(!j.ok) return;
  const tb = $('userBody');
  tb.innerHTML = '';
  for(const r of (j.rows||[])){
    tb.insertAdjacentHTML('beforeend', `
      <tr>
        <td class="text-muted">${r.id}</td>
        <td class="mono">${h(r.username)}</td>
        <td>${h(r.full_name)}</td>
        <td>${h(r.email ?? '')}</td>
        <td class="mono">${h(r.phone ?? '')}</td>
        <td>${boolBadge(parseInt(r.active,10)===1)}</td>
        <td class="text-end">
          <button class="btn btn-outline-primary btn-sm" data-edit="user" data-id="${r.id}">
            <i class="bi bi-pencil"></i>
          </button>
        </td>
      </tr>
    `);
  }
}

async function reloadAll(){
  await loadLocations();
  await loadWarehouses();
  await loadProducts();
  await loadChannels();
  await loadQualityConcepts();
  await loadUsers();
}

/* ===== Modal Builders ===== */
function companySelectHtml(selected){
  let html = `<select class="form-select" name="company_id" required>`;
  for(const c of companies){
    const sel = parseInt(c.id,10)===parseInt(selected||0,10) ? 'selected' : '';
    html += `<option value="${c.id}" ${sel}>${h(c.name)}</option>`;
  }
  html += `</select>`;
  return html;
}

function companySelectHtmlWithId(selected, idAttr='') {
  let html = `<select class="form-select" name="company_id" ${idAttr ? `id="${idAttr}"` : ''} required>`;
  for(const c of companies){
    const sel = parseInt(c.id,10)===parseInt(selected||0,10) ? 'selected' : '';
    html += `<option value="${c.id}" ${sel}>${h(c.name)}</option>`;
  }
  html += `</select>`;
  return html;
}

function locationOptionsHtml(locations, companyId, selectedId){
  companyId = parseInt(companyId||0,10);
  selectedId = parseInt(selectedId||0,10);
  return (locations||[])
    .filter(l => companyId <= 0 || parseInt(l.company_id||0,10) === companyId)
    .map(l => `<option value="${l.id}" ${parseInt(l.id,10)===selectedId?'selected':''}>${h(l.name)} (${h(l.code||'')})</option>`)
    .join('');
}

async function openWarehouseModal(row=null){
  mode = 'wh';
  editing = row || {};
  const isNew = !row;
  $('mTitle').textContent = (isNew ? 'Nuevo ' : 'Editar ') + 'Almacén';
  $('mBody').innerHTML = `<div class="text-muted">Cargando localidades...</div>`;
  $('editForm').dataset.id = String(editing.id||0);
  modal.show();

  const jl = await apiGet('locations_list', {company_id: 0});
  const locs = jl.ok ? (jl.rows||[]) : [];
  const defaultCompany = editing.company_id || companyFilterId() || companies[0]?.id || 0;
  const defaultLocation = editing.location_id || 0;

  $('mBody').innerHTML = `
    <div class="row g-3">
      <div class="col-12 col-md-6">
        <label class="form-label">Empresa</label>
        ${companySelectHtmlWithId(defaultCompany, 'whCompanySelect')}
      </div>
      <div class="col-12 col-md-6">
        <label class="form-label">Localidad</label>
        <select class="form-select" name="location_id" id="whLocationSelect" required>
          ${locationOptionsHtml(locs, defaultCompany, defaultLocation)}
        </select>
      </div>
      <div class="col-12 col-md-4">
        <label class="form-label">Código</label>
        <input class="form-control mono" name="code" required value="${h(editing.code||'')}" placeholder="CF1, CF2, CF3, MERMA...">
        <div class="form-text">Usa códigos cortos y estables. Ej. CF1, CF2, CF3, CUB, MERMA.</div>
      </div>
      <div class="col-12 col-md-8">
        <label class="form-label">Nombre</label>
        <input class="form-control" name="name" required value="${h(editing.name||'')}" placeholder="Cuarto frío 1, Merma pendiente...">
      </div>
      <div class="col-12">
        <div class="form-check">
          <input class="form-check-input" type="checkbox" name="active" ${String(editing.active??1)==='1'?'checked':''}>
          <label class="form-check-label">Activo</label>
        </div>
      </div>
      <div class="col-12">
        <div class="alert alert-warning py-2 mb-0">
          Crear o editar almacenes no mueve inventario. Para mover existencia usa los módulos operativos o Ajuste almacenes.
        </div>
      </div>
    </div>
  `;

  const companySel = $('whCompanySelect');
  const locationSel = $('whLocationSelect');
  companySel.addEventListener('change', () => {
    const cid = parseInt(companySel.value||'0',10);
    locationSel.innerHTML = locationOptionsHtml(locs, cid, 0);
  });
}


function openModal(kind, row=null){
  mode = kind;
  editing = row || {};
  const isNew = !row;

  const titleMap = {loc:'Localidad', wh:'Almacén', prod:'Producto', chan:'Canal', qc:'Concepto Calidad', user:'Usuario'};
  $('mTitle').textContent = (isNew ? 'Nuevo ' : 'Editar ') + (titleMap[kind] || 'Registro');

  let body = '';

  if(kind==='loc'){
    body = `
      <div class="row g-3">
        <div class="col-12 col-md-6">
          <label class="form-label">Empresa</label>
          ${companySelectHtml(editing.company_id || companyFilterId() || companies[0]?.id)}
        </div>
        <div class="col-12 col-md-6">
          <label class="form-label">Timezone</label>
          <input class="form-control mono" name="timezone" value="${h(editing.timezone||'America/Monterrey')}">
        </div>
        <div class="col-12 col-md-6">
          <label class="form-label">Nombre</label>
          <input class="form-control" name="name" required value="${h(editing.name||'')}">
        </div>
        <div class="col-12 col-md-6">
          <label class="form-label">Code</label>
          <input class="form-control mono" name="code" required value="${h(editing.code||'')}">
        </div>
        <div class="col-12">
          <div class="form-check">
            <input class="form-check-input" type="checkbox" name="active" ${String(editing.active??1)==='1'?'checked':''}>
            <label class="form-check-label">Activa</label>
          </div>
        </div>
      </div>
    `;
  }

  if(kind==='prod'){
    body = `
      <div class="row g-3">
        <div class="col-12 col-md-6">
          <label class="form-label">Empresa</label>
          ${companySelectHtml(editing.company_id || companyFilterId() || companies[0]?.id)}
        </div>
        <div class="col-12 col-md-6">
          <label class="form-label">UOM</label>
          <input class="form-control mono" name="uom" value="${h(editing.uom||'pz')}">
        </div>

        <div class="col-12 col-md-6">
          <label class="form-label">Artículo</label>
          <input class="form-control mono" name="sku" required value="${h(editing.sku||'')}">
        </div>
        <div class="col-12 col-md-6">
          <label class="form-label"># DLAX (ID ERP)</label>
          <input class="form-control mono" name="erp_id" value="${h(editing.erp_id||'')}">
          <div class="form-text">Opcional. ID del artículo en ERP (NetSuite, etc.).</div>
        </div>

        <div class="col-12">
          <label class="form-label">Descripción</label>
          <input class="form-control" name="name" required value="${h(editing.name||'')}">
        </div>

        <div class="col-12">
          <div class="form-check">
            <input class="form-check-input" type="checkbox" name="active" ${String(editing.active??1)==='1'?'checked':''}>
            <label class="form-check-label">Activo</label>
          </div>
        </div>
        <div class="col-12">
          <div class="alert alert-info py-2 mb-0">
            Guarda el artículo y después usa el botón <strong>Ver / configurar almacenes</strong> para indicar por localidad dónde entra producto terminado, merma, aprovechamiento y cubicado.
          </div>
        </div>
      </div>
    `;
  }

  if(kind==='chan'){
    body = `
      <div class="row g-3">
        <div class="col-12 col-md-6">
          <label class="form-label">Empresa</label>
          ${companySelectHtml(editing.company_id || companyFilterId() || companies[0]?.id)}
        </div>
        <div class="col-12 col-md-6">
          <label class="form-label">Orden</label>
          <input class="form-control mono" name="sort_order" value="${h(editing.sort_order??0)}">
        </div>
        <div class="col-12 col-md-6">
          <label class="form-label">Nombre</label>
          <input class="form-control" name="name" required value="${h(editing.name||'')}">
        </div>
        <div class="col-12 col-md-6">
          <label class="form-label">Tipo</label>
          <select class="form-select mono" name="channel_type">
            ${['SALE','TRANSFER','INTERNAL'].map(t=>{
              const sel = (editing.channel_type||'SALE')===t?'selected':'';
              return `<option ${sel} value="${t}">${t}</option>`;
            }).join('')}
          </select>
          <div class="form-text">SALE=venta · TRANSFER=transferencia · INTERNAL=uso interno</div>
        </div>
        <div class="col-12">
          <div class="form-check">
            <input class="form-check-input" type="checkbox" name="active" ${String(editing.active??1)==='1'?'checked':''}>
            <label class="form-check-label">Activo</label>
          </div>
        </div>
      </div>
    `;
  }

  if(kind==='qc'){
    const t = editing.concept_type || $('qcType').value || 'MERMA';
    body = `
      <div class="row g-3">
        <div class="col-12 col-md-6">
          <label class="form-label">Empresa</label>
          ${companySelectHtml(editing.company_id || companyFilterId() || companies[0]?.id)}
        </div>
        <div class="col-12 col-md-6">
          <label class="form-label">Tipo</label>
          <select class="form-select mono" name="concept_type">
            ${['MERMA','SCRAP'].map(x=>{
              const sel = String(t)===x?'selected':'';
              return `<option ${sel} value="${x}">${x}</option>`;
            }).join('')}
          </select>
        </div>
        <div class="col-12">
          <label class="form-label">Concepto</label>
          <input class="form-control" name="name" required value="${h(editing.name||'')}">
        </div>
        <div class="col-12 col-md-6">
          <label class="form-label">Orden</label>
          <input class="form-control mono" name="sort_order" value="${h(editing.sort_order??0)}">
        </div>
        <div class="col-12 col-md-6 d-flex align-items-end">
          <div class="form-check">
            <input class="form-check-input" type="checkbox" name="active" ${String(editing.active??1)==='1'?'checked':''}>
            <label class="form-check-label">Activo</label>
          </div>
        </div>
      </div>
    `;
  }

  if(kind==='user'){
    // este modal lo rellenamos completo con fetch de users_get para traer roles/accesos
    body = `<div class="text-muted">Cargando...</div>`;
  }

  $('mBody').innerHTML = body;
  $('editForm').dataset.id = String(editing.id||0);
  modal.show();
}

function formToObj(){
  const fd = new FormData($('editForm'));
  const o = {};
  for(const [k,v] of fd.entries()) o[k]=v;

  // comunes
  o.active = $('editForm').querySelector('[name="active"]')?.checked ? 1 : 0;
  if (o.sort_order !== undefined) o.sort_order = parseInt(o.sort_order||'0',10);
  if (o.company_id !== undefined) o.company_id = parseInt(o.company_id||'0',10);
  if (o.location_id !== undefined) o.location_id = parseInt(o.location_id||'0',10);

  return o;
}

/* ===== Guardar modal según modo ===== */
async function saveModal(){
  const id = parseInt($('editForm').dataset.id||'0',10);

  if (mode === 'user') {
    // Captura usuario + roles + accesos
    const payload = {};
    payload.id = id>0 ? id : 0;
    payload.username  = $('editForm').querySelector('[name="username"]').value.trim();
    payload.full_name = $('editForm').querySelector('[name="full_name"]').value.trim();
    payload.email     = $('editForm').querySelector('[name="email"]').value.trim();
    payload.phone     = $('editForm').querySelector('[name="phone"]').value.trim();
    payload.active    = $('editForm').querySelector('[name="active"]').checked ? 1 : 0;

    const pwd = $('editForm').querySelector('[name="password"]').value;
    if (pwd && pwd.trim() !== '') payload.password = pwd;

    payload.role_codes = Array.from($('editForm').querySelectorAll('input[name="role_codes[]"]:checked')).map(x=>x.value);
    payload.company_ids = Array.from($('editForm').querySelectorAll('input[name="company_ids[]"]:checked')).map(x=>parseInt(x.value,10));
    payload.location_ids = Array.from($('editForm').querySelectorAll('input[name="location_ids[]"]:checked')).map(x=>parseInt(x.value,10));

    const j = await apiPost('users_save', payload);
    if(!j.ok){ alert(j.error||'Error'); return; }

    modal.hide();
    await loadUsers();
    return;
  }

  const payload = formToObj();
  if(id>0) payload.id=id;

  let action = '';
  if(mode==='loc') action = 'locations_save';
  if(mode==='wh') action = 'warehouses_save';
  if(mode==='prod') action = 'products_save';
  if(mode==='chan') action = 'channels_save';
  if(mode==='qc') action = 'quality_concepts_save';

  const j = await apiPost(action, payload);
  if(!j.ok){ alert(j.error || 'Error'); return; }

  modal.hide();
  await reloadAll();
  if($('dataTablesBox')) { dataLoadTables(); dataLoadPresets(); }
}

async function findRow(kind, id){
  const company_id = companyFilterId();
  let action = '';
  if(kind==='loc') action='locations_list';
  if(kind==='wh') action='warehouses_list';
  if(kind==='prod') action='products_list';
  if(kind==='chan') action='channels_list';
  if(kind==='qc') action='quality_concepts_list';

  // qc depende del tipo seleccionado
  let params = {company_id};
  if (kind==='qc') params.concept_type = $('qcType').value || 'MERMA';

  const j = await apiGet(action, params);
  const row = (j.rows||[]).find(x=>parseInt(x.id,10)===parseInt(id,10));
  return row || null;
}

/* ===== HUB navigation ===== */
function showPanel(name){
  const panels = {
    loc: $('panel-loc'),
    wh: $('panel-wh'),
    prod: $('panel-prod'),
    chan: $('panel-chan'),
    qual: $('panel-qual'),
    users: $('panel-users'),
    invadj: $('panel-invadj'),
    audit: $('panel-audit'),
  };
  for(const k of Object.keys(panels)){
    panels[k].classList.toggle('show', k===name);
  }
  document.querySelectorAll('.cardHub').forEach(c=>{
    c.classList.toggle('active', c.dataset.panel===name);
  });
}

/* ===== User modal builder ===== */
function checkListHtml(items, checkedSet, nameAttr, labelFn){
  return items.map(it=>{
    const val = String(it.value);
    const checked = checkedSet.has(val) ? 'checked' : '';
    return `
      <div class="form-check">
        <input class="form-check-input" type="checkbox" name="${nameAttr}" value="${h(val)}" id="${nameAttr}_${h(val)}" ${checked}>
        <label class="form-check-label" for="${nameAttr}_${h(val)}">${labelFn(it)}</label>
      </div>
    `;
  }).join('');
}

async function openUserModal(userId){
  mode = 'user';
  const isNew = !userId;

  $('mTitle').textContent = isNew ? 'Nuevo usuario' : 'Editar usuario';
  $('mBody').innerHTML = `<div class="text-muted">Cargando...</div>`;
  $('editForm').dataset.id = String(userId||0);
  modal.show();

  // base data
  if (!roles.length) await loadRoles();
  if (!companies.length) await loadCompanies();

  let user = {username:'',full_name:'',email:'',phone:'',active:1};
  let userRoleCodes = [];
  let access = {company_ids:[], location_ids:[]};

  if (!isNew) {
    const j = await apiGet('users_get', {id:userId});
    if (!j.ok) { $('mBody').innerHTML = `<div class="alert alert-danger">${h(j.error||'Error')}</div>`; return; }
    user = j.row;
    userRoleCodes = j.roles || [];
    access = j.access || access;
  }

  // locations ALL (para asignación multi-empresa)
  const jl = await apiGet('locations_list', {company_id: 0});
  const allLocs = (jl.ok ? (jl.rows||[]) : []).map(x=>({
    id:x.id, name:x.name, company_name:x.company_name, company_id:x.company_id
  }));

  const roleSet = new Set((userRoleCodes||[]).map(String));
  const compSet = new Set((access.company_ids||[]).map(x=>String(x)));
  const locSet  = new Set((access.location_ids||[]).map(x=>String(x)));

  const roleItems = roles.map(r=>({value:r.code, label:`${r.name} (${r.code})`}));
  const compItems = companies.map(c=>({value:c.id, label:`${c.name}`}));
  const locItems  = allLocs.map(l=>({value:l.id, label:`${l.company_name} · ${l.name}`}));

  const body = `
    <div class="row g-3">
      <div class="col-12 col-md-6">
        <label class="form-label">Username</label>
        <input class="form-control mono" name="username" required value="${h(user.username||'')}">
      </div>
      <div class="col-12 col-md-6">
        <label class="form-label">Nombre</label>
        <input class="form-control" name="full_name" required value="${h(user.full_name||'')}">
      </div>

      <div class="col-12 col-md-6">
        <label class="form-label">Email</label>
        <input class="form-control" name="email" value="${h(user.email||'')}">
      </div>
      <div class="col-12 col-md-6">
        <label class="form-label">Teléfono</label>
        <input class="form-control mono" name="phone" value="${h(user.phone||'')}">
      </div>

      <div class="col-12 col-md-6">
        <label class="form-label">${isNew ? 'Password' : 'Password (opcional)'} </label>
        <input class="form-control mono" name="password" type="password" autocomplete="new-password" placeholder="${isNew ? 'Requerido' : 'Deja vacío para no cambiar'}">
        <div class="form-text">${isNew ? 'Se guardará con password_hash().' : 'Si lo llenas, se actualizará.'}</div>
      </div>

      <div class="col-12 col-md-6 d-flex align-items-end">
        <div class="form-check">
          <input class="form-check-input" type="checkbox" name="active" ${String(user.active??1)==='1'?'checked':''}>
          <label class="form-check-label">Activo</label>
        </div>
      </div>

      <hr class="my-2">

      <div class="col-12 col-lg-4">
        <div class="fw-semibold mb-1"><i class="bi bi-person-badge me-1"></i>Roles</div>
        <div class="checkgrid">
          ${checkListHtml(
            roleItems,
            roleSet,
            'role_codes[]',
            (it)=>h(it.label)
          )}
        </div>
        <div class="miniHint mt-1">Ej: admin, corp, production, quality…</div>
      </div>

      <div class="col-12 col-lg-4">
        <div class="fw-semibold mb-1"><i class="bi bi-building me-1"></i>Empresas permitidas</div>
        <div class="checkgrid">
          ${checkListHtml(
            compItems,
            compSet,
            'company_ids[]',
            (it)=>h(it.label)
          )}
        </div>
        <div class="miniHint mt-1">Controla lo que verá en Select Scope.</div>
      </div>

      <div class="col-12 col-lg-4">
        <div class="fw-semibold mb-1"><i class="bi bi-geo-alt me-1"></i>Localidades permitidas</div>
        <div class="checkgrid">
          ${checkListHtml(
            locItems,
            locSet,
            'location_ids[]',
            (it)=>h(it.label)
          )}
        </div>
        <div class="miniHint mt-1">Puedes asignar varias localidades.</div>
      </div>
    </div>
  `;

  $('mBody').innerHTML = body;
}



/* ===== Ajuste almacenes ===== */
function qtyClass(v){ v=parseFloat(v||0); if(v>0) return 'qty-pos'; if(v<0) return 'qty-neg'; return 'qty-zero'; }
function fmt3(v){ const n=parseFloat(v||0); return (isNaN(n)?0:n).toFixed(3); }

async function invLoadCompanies(){
  const sel = $('invCompany');
  if(!sel) return;
  sel.innerHTML = companies.map(c=>`<option value="${c.id}">${h(c.name)}</option>`).join('');
  if(companyFilterId()>0) sel.value = String(companyFilterId());
  await invLoadLocations();
}
async function invLoadLocations(){
  const companyId = parseInt($('invCompany')?.value||'0',10);
  if(!companyId) return;
  const j = await apiGet('inventory_adjust_locations', {company_id:companyId});
  const rows = j.ok ? (j.rows||[]) : [];
  $('invLocation').innerHTML = rows.map(l=>`<option value="${l.id}">${h(l.name)} (${h(l.code||'')})</option>`).join('');
  await invLoadWarehousesProducts();
}
async function invLoadWarehousesProducts(){
  const companyId = parseInt($('invCompany')?.value||'0',10);
  const locationId = parseInt($('invLocation')?.value||'0',10);
  if(!companyId || !locationId) return;
  const [w,p] = await Promise.all([
    apiGet('inventory_adjust_warehouses', {company_id:companyId, location_id:locationId}),
    apiGet('inventory_adjust_products', {company_id:companyId})
  ]);
  $('invWarehouse').innerHTML = (w.ok?w.rows:[]).map(x=>`<option value="${x.id}">${h(x.code)} · ${h(x.name)}</option>`).join('');
  $('invProduct').innerHTML = (p.ok?p.rows:[]).map(x=>`<option value="${x.id}">${h(x.sku)} · ${h(x.name)}</option>`).join('');
  await invLoadSnapshot();
}
async function invLoadSnapshot(){
  const companyId = parseInt($('invCompany')?.value||'0',10);
  const locationId = parseInt($('invLocation')?.value||'0',10);
  if(!companyId || !locationId) return;
  const j = await apiGet('inventory_adjust_snapshot', {company_id:companyId, location_id:locationId});
  if(!j.ok){ alert(j.error||'Error al cargar inventario'); return; }
  $('invTotalsBody').innerHTML = (j.totals||[]).map(r=>`
    <tr>
      <td class="mono">${h(r.sku)}</td>
      <td>${h(r.product_name)}</td>
      <td class="text-end mono ${qtyClass(r.qty_total)}">${fmt3(r.qty_total)}</td>
    </tr>
  `).join('');
  $('invDetailBody').innerHTML = (j.rows||[]).map(r=>`
    <tr>
      <td class="mono">${h(r.sku)}</td>
      <td><span class="mono">${h(r.warehouse_code)}</span> · ${h(r.warehouse_name)}</td>
      <td class="text-end mono ${qtyClass(r.qty)}">${fmt3(r.qty)}</td>
    </tr>
  `).join('');
}
async function invUnlock(){
  const code = prompt('Código de autorización para ajuste de almacenes:');
  if(code === null) return;
  const j = await apiPost('inventory_adjust_auth', {code});
  if(!j.ok){ alert(j.error||'Código incorrecto'); return; }
  invUnlocked = true;
  invCode = code;
  $('invAdjustArea')?.classList.remove('invAdjustLocked');
  $('btnInvUnlock').innerHTML = '<i class="bi bi-unlock me-1"></i>Código validado';
  $('btnInvUnlock').classList.remove('btn-warning');
  $('btnInvUnlock').classList.add('btn-success');
}
async function invSaveAdjust(){
  if(!invUnlocked){ await invUnlock(); if(!invUnlocked) return; }
  const payload = {
    code: invCode,
    company_id: parseInt($('invCompany').value||'0',10),
    location_id: parseInt($('invLocation').value||'0',10),
    warehouse_id: parseInt($('invWarehouse').value||'0',10),
    product_id: parseInt($('invProduct').value||'0',10),
    kind: $('invKind').value,
    qty: parseFloat(String($('invQty').value||'0').replace(',','.'))||0,
    notes: $('invNotes').value.trim()
  };
  if(payload.qty <= 0){ alert('Captura una cantidad mayor a 0.'); return; }
  const txt = payload.kind === 'SUB' ? 'quitar' : 'agregar';
  if(!confirm(`¿Confirmas ${txt} ${fmt3(payload.qty)} al producto seleccionado?`)) return;
  const j = await apiPost('inventory_adjust_save', payload);
  if(!j.ok){ alert(j.error||'Error al guardar ajuste'); return; }
  $('invQty').value = '0';
  $('invNotes').value = '';
  await invLoadSnapshot();
  alert('Ajuste guardado correctamente.');
}

async function invSaveCut(){
  if(!invUnlocked){ await invUnlock(); if(!invUnlocked) return; }
  const payload = {
    code: invCode,
    company_id: parseInt($('invCompany').value||'0',10),
    location_id: parseInt($('invLocation').value||'0',10),
    warehouse_id: parseInt($('invWarehouse').value||'0',10),
    product_id: parseInt($('invProduct').value||'0',10),
    target_qty: parseFloat(String($('invTargetQty').value||'0').replace(',','.'))||0,
    notes: $('invCutNotes').value.trim()
  };
  if(payload.target_qty < 0){ alert('La cantidad física no puede ser negativa.'); return; }

  const prodText = $('invProduct').options[$('invProduct').selectedIndex]?.text || 'producto seleccionado';
  const whText = $('invWarehouse').options[$('invWarehouse').selectedIndex]?.text || 'almacén seleccionado';
  const msg = `¿Confirmas dejar ${prodText} en ${whText} con existencia física ${fmt3(payload.target_qty)}?\n\nEl sistema calculará y registrará solo la diferencia en inventory_moves.`;
  if(!confirm(msg)) return;

  const j = await apiPost('inventory_cut_save', payload);
  if(!j.ok){ alert(j.error||'Error al aplicar corte físico'); return; }

  $('invCutNotes').value = '';
  await invLoadSnapshot();

  const delta = parseFloat(j.delta||0);
  if(Math.abs(delta) <= 0.0001){
    alert(j.message || 'Sin diferencia.');
  } else {
    alert(`Corte aplicado. Diferencia: ${fmt3(delta)} | Movimiento: ${j.move_type} ${fmt3(j.qty)}`);
  }
}



/* ===== Auditoría movimientos ===== */
function todayIso(){ return new Date().toISOString().slice(0,10); }
function monthStartIso(){ const d=new Date(); d.setDate(1); return d.toISOString().slice(0,10); }
async function auditInit(){
  const c=$('audCompany'); if(!c) return;
  c.innerHTML = '<option value="0">Todas las empresas</option>' + companies.map(x=>`<option value="${x.id}">${h(x.name)}</option>`).join('');
  if(companyFilterId()>0) c.value = String(companyFilterId());
  if(!$('audDateFrom').value) $('audDateFrom').value = monthStartIso();
  if(!$('audDateTo').value) $('audDateTo').value = todayIso();
  auditPage = 1;
  await auditLoadLocationsProductsWarehouses();
  await auditLoad();
}
async function auditLoadLocationsProductsWarehouses(){
  const companyId=parseInt($('audCompany')?.value||'0',10);
  const loc=await apiGet('inventory_adjust_locations',{company_id:companyId});
  $('audLocation').innerHTML = '<option value="0">Todas</option>' + (loc.ok?loc.rows:[]).map(x=>`<option value="${x.id}">${h(x.company_name)} · ${h(x.name)} (${h(x.code||'')})</option>`).join('');

  if(companyId>0){
    const pr=await apiGet('inventory_adjust_products',{company_id:companyId});
    $('audProduct').innerHTML = '<option value="0">Todos</option>' + (pr.ok?pr.rows:[]).map(x=>`<option value="${x.id}">${h(x.sku)} · ${h(x.name)}</option>`).join('');
  } else {
    $('audProduct').innerHTML = '<option value="0">Todos</option>';
  }
  await auditLoadWarehouses();
}
async function auditLoadWarehouses(){
  const companyId=parseInt($('audCompany')?.value||'0',10);
  const locationId=parseInt($('audLocation')?.value||'0',10);
  if(companyId>0 && locationId>0){
    const w=await apiGet('inventory_adjust_warehouses',{company_id:companyId,location_id:locationId});
    $('audWarehouse').innerHTML = '<option value="0">Todos</option>' + (w.ok?w.rows:[]).map(x=>`<option value="${x.id}">${h(x.code)} · ${h(x.name)}</option>`).join('');
  } else {
    $('audWarehouse').innerHTML = '<option value="0">Todos</option>';
  }
}
async function auditLoad(){
  if(!$('audBody')) return;
  const payload={
    company_id:$('audCompany').value||'0',
    location_id:$('audLocation').value||'0',
    product_id:$('audProduct').value||'0',
    warehouse_id:$('audWarehouse').value||'0',
    move_type:$('audMoveType').value||'',
    ref_type:$('audRefType').value.trim(),
    q:$('audSearch').value.trim(),
    date_from:$('audDateFrom').value||monthStartIso(),
    date_to:$('audDateTo').value||todayIso(),
    page:auditPage,
    per_page:$('audPerPage')?.value||'50'
  };
  const j=await apiGet('inventory_audit_log',payload);
  if(!j.ok){ alert(j.error||'Error al consultar auditoría'); return; }
  const s=j.summary||{};
  $('audMovesCount').textContent = String(parseInt(s.moves_count||0,10));
  $('audTotalIn').textContent = fmt3(s.total_in||0);
  $('audTotalOut').textContent = fmt3(s.total_out||0);
  $('audTotalAdj').textContent = fmt3(s.total_adj||0);
  $('audNetQty').textContent = fmt3(s.net_qty||0);
  $('audNetQty').className = 'fw-bold fs-4 mono ' + qtyClass(s.net_qty||0);

  const pg = j.pagination || {page:1,total_pages:1,total_rows:(j.rows||[]).length,per_page:50,has_prev:false,has_next:false};
  auditPage = parseInt(pg.page||1,10);
  auditTotalPages = Math.max(1, parseInt(pg.total_pages||1,10));
  if($('audPageInfo')) $('audPageInfo').textContent = `Página ${auditPage} de ${auditTotalPages} · ${parseInt(pg.total_rows||0,10)} registros · ${parseInt(pg.per_page||50,10)} por página`;
  if($('audFirstPage')) $('audFirstPage').disabled = auditPage <= 1;
  if($('audPrevPage')) $('audPrevPage').disabled = auditPage <= 1;
  if($('audNextPage')) $('audNextPage').disabled = auditPage >= auditTotalPages;
  if($('audLastPage')) $('audLastPage').disabled = auditPage >= auditTotalPages;

  $('audBody').innerHTML = (j.rows||[]).map(r=>`
    <tr>
      <td class="mono small">${h(r.created_at||'')}</td>
      <td class="mono small">${h(r.work_date||'')}</td>
      <td><div class="mono fw-semibold">${h(r.sku||'')}</div><div class="small text-muted">${h(r.product_name||'')}</div></td>
      <td><span class="mono">${h(r.warehouse_code||'SIN-WH')}</span></td>
      <td><span class="badge ${r.move_type==='OUT'?'text-bg-danger':(r.move_type==='ADJ'?'text-bg-warning':'text-bg-success')}">${h(r.move_type)}</span></td>
      <td class="text-end mono ${qtyClass(r.signed_qty)}">${fmt3(r.qty)}</td>
      <td class="mono small">${h(r.ref_type||'')}</td>
      <td class="mono small">${h(r.ref_id??'')}</td>
      <td>${h(r.user_name||'')}</td>
      <td class="small">${h(r.notes||'')}</td>
      <td class="text-end">
        <button class="btn btn-outline-primary btn-sm btn-pill" data-edit-move="${h(r.id)}" title="Corregir movimiento">
          <i class="bi bi-pencil-square"></i>
        </button>
      </td>
    </tr>
  `).join('') || '<tr><td colspan="11" class="text-center text-muted py-4">Sin movimientos con los filtros seleccionados.</td></tr>';

  $('audRefBody').innerHTML = (j.by_ref_type||[]).map(r=>`<tr><td class="mono">${h(r.ref_type)}</td><td class="text-end mono">${h(r.moves_count)}</td><td class="text-end mono ${qtyClass(r.net_qty)}">${fmt3(r.net_qty)}</td></tr>`).join('') || '<tr><td colspan="3" class="text-center text-muted">Sin datos</td></tr>';
  $('audUserBody').innerHTML = (j.by_user||[]).map(r=>`<tr><td>${h(r.user_name)}</td><td class="text-end mono">${h(r.moves_count)}</td><td class="text-end mono ${qtyClass(r.net_qty)}">${fmt3(r.net_qty)}</td></tr>`).join('') || '<tr><td colspan="3" class="text-center text-muted">Sin datos</td></tr>';
}



function toDatetimeLocalValue(s){
  s = String(s || '').trim();
  if(!s) return '';
  return s.replace(' ', 'T').slice(0,16);
}

async function openMoveEditModal(moveId){
  moveId = parseInt(moveId || 0, 10);
  if(moveId <= 0) return;

  $('moveEditSub').textContent = `Movimiento #${moveId}`;
  $('moveEditMeta').innerHTML = '<span class="text-muted">Cargando movimiento...</span>';
  $('moveEditForm').reset();
  $('moveEditId').value = String(moveId);
  moveEditModal.show();

  const j = await apiGet('inventory_move_get', {id: moveId});
  if(!j.ok){
    $('moveEditMeta').innerHTML = `<span class="text-danger">${h(j.error || 'No se pudo cargar el movimiento.')}</span>`;
    return;
  }

  const r = j.row || {};
  $('moveEditWorkDate').value = String(r.work_date || '');
  $('moveEditCreatedAt').value = toDatetimeLocalValue(r.created_at || '');
  $('moveEditMoveType').value = String(r.move_type || 'OUT').toUpperCase();
  $('moveEditQty').value = String(r.qty || '');
  $('moveEditNotes').value = String(r.notes || '');
  $('moveEditReason').value = '';

  $('moveEditProduct').innerHTML = (j.products || []).map(p => {
    const sel = parseInt(p.id,10) === parseInt(r.product_id,10) ? ' selected' : '';
    return `<option value="${p.id}"${sel}>${h(p.sku)} · ${h(p.name)}</option>`;
  }).join('');

  $('moveEditWarehouse').innerHTML = '<option value="0">SIN-WH / sin almacén</option>' + (j.warehouses || []).map(w => {
    const sel = parseInt(w.id,10) === parseInt(r.warehouse_id || 0,10) ? ' selected' : '';
    return `<option value="${w.id}"${sel}>${h(w.code)} · ${h(w.name)}</option>`;
  }).join('');

  $('moveEditMeta').innerHTML = `
    <div><b>Empresa:</b> ${h(r.company_name || '')} · <b>Planta:</b> ${h(r.location_name || '')}</div>
    <div><b>Origen:</b> <span class="mono">${h(r.ref_type || '')} #${h(r.ref_id ?? '')}</span> · <b>Usuario:</b> ${h(r.user_name || '')}</div>
    <div class="text-muted">No se cambia el origen del movimiento; solo los datos operativos corregibles.</div>
  `;
}

async function saveMoveEdit(ev){
  ev.preventDefault();
  const id = parseInt($('moveEditId').value || '0', 10);
  if(id <= 0) return;

  const ok = confirm('Vas a modificar un movimiento real de inventario. ¿Confirmas guardar la corrección?');
  if(!ok) return;

  const payload = {
    id,
    work_date: $('moveEditWorkDate').value,
    created_at: $('moveEditCreatedAt').value,
    move_type: $('moveEditMoveType').value,
    qty: $('moveEditQty').value,
    product_id: $('moveEditProduct').value,
    warehouse_id: $('moveEditWarehouse').value,
    notes: $('moveEditNotes').value,
    reason: $('moveEditReason').value,
    code: $('moveEditCode').value
  };

  const j = await apiPost('inventory_move_update', payload);
  if(!j.ok){
    alert(j.error || 'No se pudo guardar la corrección.');
    return;
  }

  moveEditModal.hide();
  await auditLoad();
}

/* ===== Reglas de almacén por producto/localidad ===== */
function whRuleSelectHtml(fieldName, warehouses, selectedId){
  selectedId = parseInt(selectedId || 0, 10);
  let html = `<select class="form-select form-select-sm" data-rule-field="${h(fieldName)}">`;
  html += `<option value="0">-- No configurado --</option>`;
  for(const w of (warehouses || [])){
    const wid = parseInt(w.id || 0, 10);
    const sel = wid === selectedId ? ' selected' : '';
    html += `<option value="${wid}"${sel}>${h(w.code || '')} · ${h(w.name || '')}</option>`;
  }
  html += `</select>`;
  return html;
}

async function openWhRulesModal(productId, companyId, sku, name){
  productId = parseInt(productId || 0, 10);
  companyId = parseInt(companyId || 0, 10);
  if(productId <= 0 || companyId <= 0){
    alert('Producto o empresa inválida para configurar almacenes.');
    return;
  }

  $('whRulesTitle').textContent = 'Almacenes por producto';
  $('whRulesSub').textContent = `${sku || ''} · ${name || ''}`;
  $('whRulesBody').innerHTML = '<div class="text-center text-muted py-4"><div class="spinner-border spinner-border-sm me-2"></div>Cargando reglas...</div>';
  whRulesModal.show();

  const j = await apiGet('product_wh_rules_get', {company_id: companyId, product_id: productId});
  if(!j.ok){
    $('whRulesBody').innerHTML = `<div class="alert alert-danger">${h(j.error || 'No se pudieron cargar las reglas de almacén.')}</div>`;
    return;
  }

  const rows = j.rows || [];
  if(rows.length === 0){
    $('whRulesBody').innerHTML = '<div class="alert alert-warning mb-0">No hay localidades activas para esta empresa.</div>';
    return;
  }

  $('whRulesBody').innerHTML = rows.map(r => {
    const rule = r.rule || {};
    const whs = r.warehouses || [];
    return `
      <div class="card rounded-4 shadow-sm mb-3" data-rule-location-id="${parseInt(r.location_id || 0, 10)}" data-rule-company-id="${companyId}" data-rule-product-id="${productId}">
        <div class="card-body">
          <div class="d-flex flex-wrap gap-2 align-items-center mb-3">
            <div>
              <div class="fw-bold"><i class="bi bi-geo-alt me-1"></i>${h(r.location_name || '')}</div>
              <div class="small text-muted mono">${h(r.location_code || '')}</div>
            </div>
            <div class="ms-auto form-check form-switch">
              <input class="form-check-input" type="checkbox" data-rule-active ${parseInt(rule.active || 1, 10) === 1 ? 'checked' : ''}>
              <label class="form-check-label small text-muted">Regla activa</label>
            </div>
          </div>

          <div class="row g-3">
            <div class="col-12 col-md-6 col-xl-4">
              <label class="form-label small fw-semibold">Producto terminado / entrada</label>
              ${whRuleSelectHtml('default_finished_warehouse_id', whs, rule.default_finished_warehouse_id)}
            </div>
            <div class="col-12 col-md-6 col-xl-4">
              <label class="form-label small fw-semibold">Salida comercial / venta</label>
              ${whRuleSelectHtml('default_sales_warehouse_id', whs, rule.default_sales_warehouse_id)}
            </div>
            <div class="col-12 col-md-6 col-xl-4">
              <label class="form-label small fw-semibold">Merma pendiente</label>
              ${whRuleSelectHtml('merma_warehouse_id', whs, rule.merma_warehouse_id)}
            </div>
            <div class="col-12 col-md-6 col-xl-4">
              <label class="form-label small fw-semibold">Aprovechamiento merma</label>
              ${whRuleSelectHtml('aprovechamiento_warehouse_id', whs, rule.aprovechamiento_warehouse_id)}
            </div>
            <div class="col-12 col-md-6 col-xl-4">
              <label class="form-label small fw-semibold">Cubicado / proceso</label>
              ${whRuleSelectHtml('cubicado_warehouse_id', whs, rule.cubicado_warehouse_id)}
            </div>
            <div class="col-12 col-xl-4">
              <label class="form-label small fw-semibold">Notas</label>
              <input class="form-control form-control-sm" data-rule-notes value="${h(rule.notes || '')}" placeholder="Regla operativa / comentario">
            </div>
          </div>

          <div class="d-flex justify-content-end mt-3">
            <button type="button" class="btn btn-primary btn-sm btn-pill" data-save-whrule="1">
              <i class="bi bi-save me-1"></i>Guardar regla de esta localidad
            </button>
          </div>
        </div>
      </div>
    `;
  }).join('');
}

async function saveWhRule(card){
  const companyId = parseInt(card.dataset.ruleCompanyId || '0', 10);
  const locationId = parseInt(card.dataset.ruleLocationId || '0', 10);
  const productId = parseInt(card.dataset.ruleProductId || '0', 10);
  if(companyId <= 0 || locationId <= 0 || productId <= 0){
    alert('Datos incompletos para guardar la regla.');
    return;
  }

  const payload = {
    company_id: companyId,
    location_id: locationId,
    product_id: productId,
    active: card.querySelector('[data-rule-active]')?.checked ? 1 : 0,
    notes: card.querySelector('[data-rule-notes]')?.value || ''
  };

  card.querySelectorAll('[data-rule-field]').forEach(sel => {
    payload[sel.dataset.ruleField] = parseInt(sel.value || '0', 10);
  });

  const btn = card.querySelector('[data-save-whrule]');
  const oldHtml = btn ? btn.innerHTML : '';
  if(btn){ btn.disabled = true; btn.innerHTML = '<span class="spinner-border spinner-border-sm me-1"></span>Guardando...'; }

  const j = await apiPost('product_wh_rules_save', payload);

  if(btn){ btn.disabled = false; btn.innerHTML = oldHtml; }

  if(!j.ok){
    alert(j.error || 'No se pudo guardar la regla.');
    return;
  }

  const toast = document.createElement('div');
  toast.className = 'alert alert-success py-2 mt-3 mb-0';
  toast.innerHTML = '<i class="bi bi-check-circle me-1"></i>Regla guardada correctamente.';
  card.querySelector('.card-body')?.appendChild(toast);
  setTimeout(() => toast.remove(), 2200);
}

/* ===== Events ===== */
document.addEventListener('click', async (e)=>{
  // Hub click
  const hub = e.target.closest('.cardHub');
  if (hub) { showPanel(hub.dataset.panel); if(hub.dataset.panel==='invadj') { await invLoadCompanies(); } if(hub.dataset.panel==='audit') { await auditInit(); } if(hub.dataset.panel==='data') { await dataInit(); } return; }

  // product warehouse rules
  const whBtn = e.target.closest('[data-whrules]');
  if (whBtn) {
    await openWhRulesModal(parseInt(whBtn.dataset.id||'0',10), parseInt(whBtn.dataset.companyId||'0',10), whBtn.dataset.sku||'', whBtn.dataset.name||'');
    return;
  }

  const saveRuleBtn = e.target.closest('[data-save-whrule]');
  if (saveRuleBtn) {
    const card = saveRuleBtn.closest('[data-rule-location-id]');
    if(card) await saveWhRule(card);
    return;
  }

  const presetBtn = e.target.closest('[data-preset-key]');
  if (presetBtn) {
    dataUsePreset(presetBtn.dataset.presetKey || '');
    return;
  }

  // controlled inventory movement correction
  const moveBtn = e.target.closest('[data-edit-move]');
  if (moveBtn) {
    await openMoveEditModal(parseInt(moveBtn.dataset.editMove || '0', 10));
    return;
  }

  // edit buttons
  const btn = e.target.closest('[data-edit]');
  if(!btn) return;

  const kind = btn.dataset.edit;
  const id = parseInt(btn.dataset.id||'0',10);

  if (kind === 'user') {
    await openUserModal(id);
    return;
  }

  if (kind === 'wh') {
    const row = await findRow(kind, id);
    await openWarehouseModal(row);
    return;
  }

  const row = await findRow(kind, id);
  openModal(kind, row);
});

/* ===== Análisis de datos ===== */
let dataTables = [];
let dataPresets = [];

function dataSelectedTables(){
  return Array.from(document.querySelectorAll('[data-data-table]:checked')).map(x=>x.value);
}

function dataRenderTables(filter=''){
  const box = $('dataTablesBox');
  if(!box) return;
  const f = String(filter||'').toLowerCase();
  box.innerHTML = '';
  for(const t of dataTables){
    if(f && !String(t.table_name).toLowerCase().includes(f)) continue;
    box.insertAdjacentHTML('beforeend', `
      <label class="form-check d-flex gap-2 align-items-start border-bottom pb-1 mb-1">
        <input class="form-check-input mt-1" type="checkbox" data-data-table value="${h(t.table_name)}">
        <span class="flex-grow-1">
          <span class="mono fw-semibold">${h(t.table_name)}</span><br>
          <span class="text-muted small">Filas aprox: ${h(t.table_rows ?? 0)} · ${h(t.engine||'')}</span>
        </span>
      </label>
    `);
  }
}

async function dataInit(){
  if(dataTables.length === 0) await dataLoadTables();
  if(dataPresets.length === 0) await dataLoadPresets();
}

async function dataLoadTables(){
  const box = $('dataTablesBox');
  if(box) box.innerHTML = '<div class="text-muted small p-2">Cargando tablas...</div>';
  const j = await apiGet('data_tables');
  if(!j.ok){ if(box) box.innerHTML = `<div class="alert alert-danger py-2">${h(j.error||'No se pudieron cargar tablas')}</div>`; return; }
  dataTables = j.rows || [];
  dataRenderTables(($('dataTableSearch')?.value||''));
}

async function dataLoadPresets(){
  const box = $('dataPresetBox');
  if(box) box.innerHTML = '<div class="text-muted small p-2">Cargando consultas...</div>';
  const j = await apiGet('data_preset_queries');
  if(!j.ok){ if(box) box.innerHTML = `<div class="alert alert-danger py-2">${h(j.error||'No se pudieron cargar consultas')}</div>`; return; }
  dataPresets = j.rows || [];
  if(!box) return;
  box.innerHTML = '';
  for(const q of dataPresets){
    box.insertAdjacentHTML('beforeend', `
      <button type="button" class="list-group-item list-group-item-action" data-preset-key="${h(q.key)}">
        <div class="fw-semibold">${h(q.title)}</div>
        <div class="text-muted mono small">${h(q.key)}</div>
      </button>
    `);
  }
}

function dataRenderRelationships(rows){
  const tb = $('dataRelBody');
  if(!tb) return;
  tb.innerHTML = '';
  if(!rows || rows.length === 0){
    tb.innerHTML = '<tr><td colspan="2" class="text-muted">Sin relación detectada.</td></tr>';
    return;
  }
  for(const r of rows){
    tb.insertAdjacentHTML('beforeend', `
      <tr>
        <td>
          <div class="mono small">${h(r.left_table)}.${h(r.left_column)}</div>
          <div class="small text-muted">→ ${h(r.right_table)}.${h(r.right_column)}</div>
          <div class="small">${h(r.reason||'')}</div>
        </td>
        <td><span class="badge ${r.confidence==='alta'?'text-bg-success':'text-bg-warning'}">${h(r.confidence||'')}</span></td>
      </tr>
    `);
  }
}

async function dataRefreshRelationships(){
  const tables = dataSelectedTables();
  if(tables.length === 0){ dataRenderRelationships([]); return; }
  const j = await apiGet('data_relationships', {tables: tables.join(',')});
  dataRenderRelationships(j.ok ? (j.rows||[]) : []);
}

async function dataGenerateQuery(){
  const tables = dataSelectedTables();
  if(tables.length === 0){ alert('Selecciona al menos una tabla.'); return; }
  const j = await apiPost('data_generate_query', {tables});
  if(!j.ok){ alert(j.error || 'No se pudo generar la consulta.'); return; }
  $('dataSql').value = j.sql || '';
  dataRenderRelationships(j.relationships || []);
  $('dataQueryInfo').textContent = 'Consulta generada. Puedes editarla antes de ejecutar.';
}

async function dataDescribeSelected(){
  const tables = dataSelectedTables();
  if(tables.length !== 1){ alert('Selecciona una sola tabla para ver sus columnas.'); return; }
  const j = await apiGet('data_columns', {table: tables[0]});
  if(!j.ok){ alert(j.error || 'No se pudieron cargar columnas.'); return; }
  const lines = (j.rows||[]).map(c => `-- ${c.column_name} ${c.column_type} ${c.is_nullable==='NO'?'NOT NULL':'NULL'} ${c.column_key?('KEY '+c.column_key):''}`);
  $('dataSql').value = `${lines.join('\n')}\n\nSELECT *\nFROM \`${tables[0]}\`\nLIMIT 100`;
  $('dataQueryInfo').textContent = 'Columnas cargadas como comentarios.';
}

function dataRenderResults(columns, rows){
  const th = $('dataResultHead');
  const tb = $('dataResultBody');
  if(!th || !tb) return;
  th.innerHTML = '';
  tb.innerHTML = '';
  if(!columns || columns.length === 0){
    th.innerHTML = '<tr><th>Sin columnas</th></tr>';
    return;
  }
  th.innerHTML = `<tr>${columns.map(c=>`<th class="text-nowrap">${h(c)}</th>`).join('')}</tr>`;
  for(const r of rows || []){
    tb.insertAdjacentHTML('beforeend', `<tr>${columns.map(c=>`<td class="small">${h(r[c])}</td>`).join('')}</tr>`);
  }
}

async function dataRunQuery(){
  const sql = ($('dataSql')?.value || '').trim();
  if(sql === ''){ alert('Escribe o genera una consulta SELECT.'); return; }
  const btn = $('btnDataRun');
  const old = btn ? btn.innerHTML : '';
  if(btn){ btn.disabled = true; btn.innerHTML = '<span class="spinner-border spinner-border-sm me-1"></span>Ejecutando...'; }
  const j = await apiPost('data_query_run', {sql});
  if(btn){ btn.disabled = false; btn.innerHTML = old; }
  if(!j.ok){
    $('dataQueryInfo').innerHTML = `<span class="text-danger">${h(j.error||'Error ejecutando consulta')}</span>`;
    return;
  }
  $('dataSql').value = j.sql || sql;
  $('dataResultInfo').textContent = `${j.count || 0} registros devueltos`;
  $('dataQueryInfo').innerHTML = '<span class="text-success">Consulta ejecutada correctamente.</span>';
  dataRenderResults(j.columns || [], j.rows || []);
}

function dataUsePreset(key){
  const q = dataPresets.find(x => x.key === key);
  if(!q) return;
  $('dataSql').value = q.sql || '';
  $('dataQueryInfo').textContent = 'Consulta relacionada cargada. Puedes ejecutarla o editarla.';
}


document.addEventListener('DOMContentLoaded', async ()=>{
  modal = bootstrap.Modal.getOrCreateInstance(document.getElementById('editModal'));
  whRulesModal = bootstrap.Modal.getOrCreateInstance(document.getElementById('whRulesModal'));
  moveEditModal = bootstrap.Modal.getOrCreateInstance(document.getElementById('moveEditModal'));

  await loadCompanies();
  await loadRoles();
  await reloadAll();

  $('companyFilter').addEventListener('change', async ()=>{
    await reloadAll();
  });
  $('btnReload').addEventListener('click', reloadAll);

  $('addLoc').addEventListener('click', ()=>{ mode='loc'; openModal('loc', null); });
  if($('addWh')) $('addWh').addEventListener('click', ()=>openWarehouseModal(null));
  $('addProd').addEventListener('click', ()=>{ mode='prod'; openModal('prod', null); });
  $('addChan').addEventListener('click', ()=>{ mode='chan'; openModal('chan', null); });

  $('qcType').addEventListener('change', loadQualityConcepts);
  $('addQc').addEventListener('click', ()=>{ mode='qc'; openModal('qc', null); });

  $('addUser').addEventListener('click', ()=>openUserModal(0));
  $('btnUSearch').addEventListener('click', ()=>loadUsers(($('uSearch').value||'').trim()));
  $('uSearch').addEventListener('keydown', (ev)=>{
    if (ev.key === 'Enter') { ev.preventDefault(); loadUsers(($('uSearch').value||'').trim()); }
  });

  $('editForm').addEventListener('submit', (e)=>{e.preventDefault(); saveModal();});
  if($('moveEditForm')) $('moveEditForm').addEventListener('submit', saveMoveEdit);


  if($('btnInvUnlock')) $('btnInvUnlock').addEventListener('click', invUnlock);
  if($('btnInvReload')) $('btnInvReload').addEventListener('click', invLoadSnapshot);
  if($('btnInvSave')) $('btnInvSave').addEventListener('click', invSaveAdjust);
  if($('btnInvCutSave')) $('btnInvCutSave').addEventListener('click', invSaveCut);
  if($('invCompany')) $('invCompany').addEventListener('change', invLoadLocations);
  if($('invLocation')) $('invLocation').addEventListener('change', invLoadWarehousesProducts);

  if($('btnAuditReload')) $('btnAuditReload').addEventListener('click', ()=>{ auditPage=1; auditLoad(); });
  if($('audCompany')) $('audCompany').addEventListener('change', async ()=>{ auditPage=1; await auditLoadLocationsProductsWarehouses(); await auditLoad(); });
  if($('audLocation')) $('audLocation').addEventListener('change', async ()=>{ auditPage=1; await auditLoadWarehouses(); await auditLoad(); });
  if($('audSearch')) $('audSearch').addEventListener('keydown', (ev)=>{ if(ev.key==='Enter'){ ev.preventDefault(); auditPage=1; auditLoad(); } });
  ['audDateFrom','audDateTo','audMoveType','audProduct','audWarehouse','audRefType','audPerPage'].forEach(id=>{
    if($(id)) $(id).addEventListener('change', ()=>{ auditPage=1; auditLoad(); });
  });
  if($('audFirstPage')) $('audFirstPage').addEventListener('click', ()=>{ if(auditPage>1){ auditPage=1; auditLoad(); } });
  if($('audPrevPage')) $('audPrevPage').addEventListener('click', ()=>{ if(auditPage>1){ auditPage--; auditLoad(); } });
  if($('audNextPage')) $('audNextPage').addEventListener('click', ()=>{ if(auditPage<auditTotalPages){ auditPage++; auditLoad(); } });
  if($('audLastPage')) $('audLastPage').addEventListener('click', ()=>{ if(auditPage<auditTotalPages){ auditPage=auditTotalPages; auditLoad(); } });


  if($('btnDataReload')) $('btnDataReload').addEventListener('click', dataLoadTables);
  if($('btnDataGenerate')) $('btnDataGenerate').addEventListener('click', dataGenerateQuery);
  if($('btnDataRun')) $('btnDataRun').addEventListener('click', dataRunQuery);
  if($('btnDataDescribe')) $('btnDataDescribe').addEventListener('click', dataDescribeSelected);
  if($('dataTableSearch')) $('dataTableSearch').addEventListener('input', ()=>dataRenderTables($('dataTableSearch').value||''));
  document.addEventListener('change', (ev)=>{ if(ev.target && ev.target.matches('[data-data-table]')) dataRefreshRelationships(); });

  showPanel('loc');
});
</script>
</body>
</html>