<?php
/**
 * Inventario de bodega por codigo + lote (Kardex vs fisico).
 *
 * @package Logistica
 */

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

/**
 * CRUD, ajustes, sincronizacion de lote con cierre de ordenes.
 */
class Logistica_Warehouse_Inventory {

	/**
	 * Decimales visibles para cantidades de bodega.
	 */
	const QUANTITY_DECIMALS = 2;

	/**
	 * Columnas editables desde plantilla Excel (clave codigo + lote).
	 *
	 * @return string[]
	 */
	public static function excel_import_fields() {
		return array(
			'descripcion',
			'materia_prima',
			'medida',
			'lote',
			'saldo_bodega',
			'clase',
		);
	}

	/**
	 * Campos editables desde el modal de configuracion.
	 *
	 * @return array<string, array<string, string>>
	 */
	public static function form_edit_fields() {
		return array(
			'descripcion'       => array(
				'label' => __( 'Descripción', 'logistica' ),
				'type'  => 'text',
			),
			'materia_prima'     => array(
				'label' => __( 'Bodega', 'logistica' ),
				'type'  => 'text',
			),
			'medida'            => array(
				'label' => __( 'Medida', 'logistica' ),
				'type'  => 'text',
			),
			'lote'              => array(
				'label' => __( 'Lote', 'logistica' ),
				'type'  => 'text',
			),
			'saldo_bodega'      => array(
				'label' => __( 'Bodega (Kardex)', 'logistica' ),
				'type'  => 'number',
				'step'  => '0.01',
			),
			'clase'             => array(
				'label' => __( 'Clase', 'logistica' ),
				'type'  => 'text',
			),
			'inventario_fisico' => array(
				'label' => __( 'Inventario físico', 'logistica' ),
				'type'  => 'number',
				'step'  => '0.01',
			),
			'observaciones'     => array(
				'label' => __( 'Observaciones', 'logistica' ),
				'type'  => 'textarea',
			),
		);
	}

	/**
	 * Fila vacia para altas manuales.
	 *
	 * @return array<string, mixed>
	 */
	public static function blank_row() {
		return array(
			'codigo'                   => '',
			'descripcion'              => '',
			'materia_prima'            => '',
			'medida'                   => '',
			'lote'                     => '',
			'saldo_bodega'             => 0,
			'clase'                    => '',
			'inventario_fisico'        => 0,
			'ajuste_acumulado'         => 0,
			'diferencia_ultimo_importe' => 0,
			'observaciones'            => '',
			'estado'                   => 'activo',
		);
	}

	/**
	 * Lista completa ordenada por codigo y fecha de creacion.
	 *
	 * @return array<int, array<string, mixed>>
	 */
	public static function get_all() {
		global $wpdb;

		$table = Logistica_Database::table( 'inventario_bodega' );
		$not_deleted = Logistica_Soft_Delete::sql_not_deleted( 'inventario_bodega' );
		$rows  = $wpdb->get_results( "SELECT * FROM {$table} WHERE {$not_deleted} ORDER BY codigo ASC, created_at ASC", ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		if ( ! is_array( $rows ) ) {
			return array();
		}

		$active_lote_map = self::get_active_lote_map_light();

		return array_map(
			function ( $row ) use ( $active_lote_map ) {
				return self::hydrate_row( $row, $active_lote_map );
			},
			$rows
		);
	}

	/**
	 * Totales globales sin cargar todas las filas.
	 *
	 * @return array{total: int, alert: int, ok: int, improved: int}
	 */
	public static function get_stats() {
		global $wpdb;

		$table       = Logistica_Database::table( 'inventario_bodega' );
		$not_deleted = Logistica_Soft_Delete::sql_not_deleted( 'inventario_bodega' );
		$row         = $wpdb->get_row(
			"SELECT
				COUNT(*) AS total,
				SUM(CASE WHEN ABS(inventario_fisico - saldo_bodega) >= 0.01 THEN 1 ELSE 0 END) AS alert_count,
				SUM(
					CASE
						WHEN ABS(diferencia_ultimo_importe) >= 0.01
							AND ABS(inventario_fisico - saldo_bodega) < ABS(diferencia_ultimo_importe)
						THEN 1 ELSE 0
					END
				) AS improved_count
			FROM {$table}
			WHERE {$not_deleted}", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			ARRAY_A
		);

		$total     = isset( $row['total'] ) ? (int) $row['total'] : 0;
		$alert     = isset( $row['alert_count'] ) ? (int) $row['alert_count'] : 0;
		$improved  = isset( $row['improved_count'] ) ? (int) $row['improved_count'] : 0;
		$ok        = max( 0, $total - $alert );

		return array(
			'total'    => $total,
			'alert'    => $alert,
			'ok'       => $ok,
			'improved' => $improved,
		);
	}

	/**
	 * Opciones distintas para filtros (descripcion / clase).
	 *
	 * @return array{descripciones: array<string, string>, clases: array<string, string>}
	 */
	public static function get_filter_options() {
		global $wpdb;

		$table       = Logistica_Database::table( 'inventario_bodega' );
		$not_deleted = Logistica_Soft_Delete::sql_not_deleted( 'inventario_bodega' );

		$descripciones = $wpdb->get_col(
			"SELECT DISTINCT descripcion FROM {$table} WHERE {$not_deleted} AND descripcion <> '' ORDER BY descripcion ASC LIMIT 2000" // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		);
		$clases = $wpdb->get_col(
			"SELECT DISTINCT clase FROM {$table} WHERE {$not_deleted} AND clase <> '' ORDER BY clase ASC LIMIT 500" // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		);

		$out_desc = array();
		foreach ( (array) $descripciones as $value ) {
			$value = trim( (string) $value );
			if ( '' !== $value ) {
				$out_desc[ $value ] = $value;
			}
		}

		$out_clase = array();
		foreach ( (array) $clases as $value ) {
			$value = trim( (string) $value );
			if ( '' !== $value ) {
				$out_clase[ $value ] = $value;
			}
		}

		return array(
			'descripciones' => $out_desc,
			'clases'        => $out_clase,
		);
	}

	/**
	 * Consulta paginada con filtros y orden.
	 *
	 * @param array<string, mixed> $args Argumentos.
	 * @return array{rows: array<int, array<string, mixed>>, total: int, page: int, per_page: int, total_pages: int}
	 */
	public static function query( $args = array() ) {
		global $wpdb;

		$args = wp_parse_args(
			$args,
			array(
				'search'      => '',
				'descripcion' => '',
				'clase'       => '',
				'status'      => '',
				'saldo'       => '',
				'sort'        => 'codigo',
				'dir'         => 'asc',
				'page'        => 1,
				'per_page'    => 25,
			)
		);

		$page     = max( 1, (int) $args['page'] );
		$per_page = (int) $args['per_page'];
		if ( $per_page < 10 ) {
			$per_page = 10;
		}
		if ( $per_page > 200 ) {
			$per_page = 200;
		}

		$table       = Logistica_Database::table( 'inventario_bodega' );
		$not_deleted = Logistica_Soft_Delete::sql_not_deleted( 'inventario_bodega' );
		$where       = array( $not_deleted );
		$params      = array();

		$search = trim( (string) $args['search'] );
		if ( '' !== $search ) {
			$tokens = preg_split( '/\s+/u', $search, -1, PREG_SPLIT_NO_EMPTY );
			if ( ! is_array( $tokens ) || empty( $tokens ) ) {
				$tokens = array( $search );
			}
			// Limitar tokens para evitar consultas enormes.
			$tokens = array_slice( $tokens, 0, 8 );
			foreach ( $tokens as $token ) {
				$like     = '%' . self::esc_like( (string) $token ) . '%';
				$where[]  = '(codigo LIKE %s OR descripcion LIKE %s OR materia_prima LIKE %s OR medida LIKE %s OR lote LIKE %s OR clase LIKE %s)';
				$params[] = $like;
				$params[] = $like;
				$params[] = $like;
				$params[] = $like;
				$params[] = $like;
				$params[] = $like;
			}
		}

		$descripcion = trim( (string) $args['descripcion'] );
		if ( '' !== $descripcion ) {
			$where[]  = 'descripcion LIKE %s';
			$params[] = '%' . self::esc_like( $descripcion ) . '%';
		}

		$clase = trim( (string) $args['clase'] );
		if ( '' !== $clase ) {
			$where[]  = 'clase = %s';
			$params[] = $clase;
		}

		$status = sanitize_key( (string) $args['status'] );
		switch ( $status ) {
			case 'diff':
				$where[] = 'ABS(inventario_fisico - saldo_bodega) >= 0.01';
				break;
			case 'ok':
				$where[] = 'ABS(inventario_fisico - saldo_bodega) < 0.01';
				break;
			case 'improved':
				$where[] = 'ABS(diferencia_ultimo_importe) >= 0.01 AND ABS(inventario_fisico - saldo_bodega) < ABS(diferencia_ultimo_importe)';
				break;
			case 'registro_activo':
				$where[] = "estado = 'activo'";
				break;
			case 'registro_inactivo':
				$where[] = "estado = 'inactivo'";
				break;
			case 'lote_vigente':
				$where[] = 'estado = \'activo\' AND NOT EXISTS (
					SELECT 1 FROM ' . $table . ' AS ib2
					WHERE ib2.codigo = ' . $table . '.codigo
						AND ib2.estado = \'activo\'
						AND ' . Logistica_Soft_Delete::sql_not_deleted( 'inventario_bodega', 'ib2' ) . '
						AND ' . self::sql_lote_row_is_preferred( 'ib2', $table ) . '
				)';
				break;
		}

		$saldo = sanitize_key( (string) $args['saldo'] );
		if ( 'omit_zero' === $saldo ) {
			$where[] = 'ABS(saldo_bodega) >= 0.01';
		}

		$sort_map = array(
			'codigo'            => 'codigo',
			'descripcion'       => 'descripcion',
			'materia_prima'     => 'materia_prima',
			'medida'            => 'medida',
			'lote'              => 'lote',
			'fecha_creacion'    => 'created_at',
			'saldo_bodega'      => 'saldo_bodega',
			'clase'             => 'clase',
			'inventario_fisico' => 'inventario_fisico',
			'diferencia'        => '(inventario_fisico - saldo_bodega)',
			'estado'            => 'ABS(inventario_fisico - saldo_bodega)',
			'registro'          => 'estado',
		);
		$sort_key = sanitize_key( (string) $args['sort'] );
		$order_by = isset( $sort_map[ $sort_key ] ) ? $sort_map[ $sort_key ] : 'codigo';
		$dir      = ( 'desc' === strtolower( (string) $args['dir'] ) ) ? 'DESC' : 'ASC';

		$where_sql = implode( ' AND ', $where );
		$count_sql = "SELECT COUNT(*) FROM {$table} WHERE {$where_sql}";
		if ( ! empty( $params ) ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			$total = (int) $wpdb->get_var( $wpdb->prepare( $count_sql, $params ) );
		} else {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			$total = (int) $wpdb->get_var( $count_sql );
		}

		$total_pages = max( 1, (int) ceil( $total / $per_page ) );
		if ( $page > $total_pages ) {
			$page = $total_pages;
		}
		$offset = ( $page - 1 ) * $per_page;

		$list_sql = "SELECT * FROM {$table} WHERE {$where_sql} ORDER BY {$order_by} {$dir}, id ASC LIMIT %d OFFSET %d";
		$list_params = array_merge( $params, array( $per_page, $offset ) );
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$rows = $wpdb->get_results( $wpdb->prepare( $list_sql, $list_params ), ARRAY_A );
		if ( ! is_array( $rows ) ) {
			$rows = array();
		}

		$codigos = array();
		foreach ( $rows as $row ) {
			$codigo = self::normalize_codigo( $row['codigo'] ?? '' );
			if ( '' !== $codigo ) {
				$codigos[ $codigo ] = true;
			}
		}
		$active_lote_map = self::get_active_lote_map_for_codigos( array_keys( $codigos ) );
		$hydrated        = array_map(
			function ( $row ) use ( $active_lote_map ) {
				return self::hydrate_row( $row, $active_lote_map );
			},
			$rows
		);

		return array(
			'rows'        => $hydrated,
			'total'       => $total,
			'page'        => $page,
			'per_page'    => $per_page,
			'total_pages' => $total_pages,
		);
	}

	/**
	 * Mapa codigo => lote vigente solo para los códigos pedidos (rápido en páginas).
	 *
	 * @param string[] $codigos Códigos.
	 * @return array<string, string>
	 */
	public static function get_active_lote_map_for_codigos( $codigos ) {
		global $wpdb;

		$codigos = array_values(
			array_unique(
				array_filter(
					array_map(
						function ( $codigo ) {
							return self::normalize_codigo( $codigo );
						},
						(array) $codigos
					)
				)
			)
		);

		if ( empty( $codigos ) ) {
			return array();
		}

		$table       = Logistica_Database::table( 'inventario_bodega' );
		$not_deleted = Logistica_Soft_Delete::sql_not_deleted( 'inventario_bodega' );
		$placeholders = implode( ',', array_fill( 0, count( $codigos ), '%s' ) );
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT id, codigo, lote, estado, saldo_bodega, created_at FROM {$table}
				WHERE {$not_deleted} AND estado = 'activo' AND codigo IN ({$placeholders})",
				$codigos
			),
			ARRAY_A
		);

		return self::compute_active_lote_map( is_array( $rows ) ? $rows : array() );
	}

	/**
	 * Mapa codigo => lote vigente con consulta liviana (cache por request).
	 *
	 * @return array<string, string>
	 */
	public static function get_active_lote_map_light() {
		if ( null !== self::$active_lote_map_cache ) {
			return self::$active_lote_map_cache;
		}

		global $wpdb;

		$table       = Logistica_Database::table( 'inventario_bodega' );
		$not_deleted = Logistica_Soft_Delete::sql_not_deleted( 'inventario_bodega' );
		$rows        = $wpdb->get_results(
			"SELECT id, codigo, lote, estado, saldo_bodega, created_at FROM {$table} WHERE {$not_deleted} AND estado = 'activo'", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			ARRAY_A
		);

		self::$active_lote_map_cache = self::compute_active_lote_map( is_array( $rows ) ? $rows : array() );

		return self::$active_lote_map_cache;
	}

	/**
	 * Invalida el cache del mapa de lotes vigentes.
	 *
	 * @return void
	 */
	public static function clear_active_lote_map_cache() {
		self::$active_lote_map_cache = null;
	}

	/**
	 * @var array<string, string>|null
	 */
	private static $active_lote_map_cache = null;

	/**
	 * Busca por ID de fila.
	 *
	 * @param int $id ID fila.
	 * @return array<string, mixed>|null
	 */
	public static function get_by_id( $id ) {
		global $wpdb;

		$id = (int) $id;
		if ( $id <= 0 ) {
			return null;
		}

		$table = Logistica_Database::table( 'inventario_bodega' );
		$not_deleted = Logistica_Soft_Delete::sql_not_deleted( 'inventario_bodega' );
		$row   = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM {$table} WHERE id = %d AND {$not_deleted} LIMIT 1", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$id
			),
			ARRAY_A
		);

		if ( ! is_array( $row ) ) {
			return null;
		}

		$active_lote_map = self::get_active_lote_map_light();

		return self::hydrate_row( $row, $active_lote_map );
	}

	/**
	 * Busca por codigo + lote.
	 *
	 * @param string $codigo Código insumo.
	 * @param string $lote Lote.
	 * @return array<string, mixed>|null
	 */
	public static function get_by_codigo_lote( $codigo, $lote ) {
		global $wpdb;

		$codigo = self::normalize_codigo( $codigo );
		$lote   = self::normalize_lote( $lote );

		if ( '' === $codigo ) {
			return null;
		}

		$table = Logistica_Database::table( 'inventario_bodega' );
		$not_deleted = Logistica_Soft_Delete::sql_not_deleted( 'inventario_bodega' );
		$row   = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM {$table} WHERE codigo = %s AND lote = %s AND {$not_deleted} LIMIT 1", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$codigo,
				$lote
			),
			ARRAY_A
		);

		if ( ! is_array( $row ) ) {
			return null;
		}

		$active_lote_map = self::get_active_lote_map_light();

		return self::hydrate_row( $row, $active_lote_map );
	}

	/**
	 * Registro activo preferido de un codigo (lote vigente para cierre).
	 * Prefiere lote real con saldo bodega; a igualdad, el mas antiguo.
	 *
	 * @param string $codigo Código insumo.
	 * @return array<string, mixed>|null
	 */
	public static function get_active_row_for_codigo( $codigo ) {
		global $wpdb;

		$codigo = self::normalize_codigo( $codigo );
		if ( '' === $codigo ) {
			return null;
		}

		$table = Logistica_Database::table( 'inventario_bodega' );
		$not_deleted = Logistica_Soft_Delete::sql_not_deleted( 'inventario_bodega' );
		$row   = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM {$table} WHERE codigo = %s AND estado = 'activo' AND {$not_deleted} ORDER BY " . self::sql_lote_vigente_order() . ' LIMIT 1', // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$codigo
			),
			ARRAY_A
		);

		if ( ! is_array( $row ) ) {
			return null;
		}

		$active_lote_map = self::get_active_lote_map_light();

		return self::hydrate_row( $row, $active_lote_map );
	}

	/**
	 * Busca por codigo (compatibilidad: devuelve el lote vigente).
	 *
	 * @param string $codigo Código insumo.
	 * @return array<string, mixed>|null
	 */
	public static function get_by_codigo( $codigo ) {
		return self::get_active_row_for_codigo( $codigo );
	}

	/**
	 * Mapa codigo => lote vigente (lote real con saldo preferido; SIN-LOTE / saldo 0 solo si no hay otro).
	 *
	 * @return array<string, string>
	 */
	public static function lote_map() {
		return self::get_active_lote_map_light();
	}

	/**
	 * Filas sin hidratar (consulta interna).
	 *
	 * @return array<int, array<string, mixed>>
	 */
	private static function get_all_raw() {
		global $wpdb;

		$table = Logistica_Database::table( 'inventario_bodega' );
		$not_deleted = Logistica_Soft_Delete::sql_not_deleted( 'inventario_bodega' );
		$rows  = $wpdb->get_results( "SELECT * FROM {$table} WHERE {$not_deleted}", ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		return is_array( $rows ) ? $rows : array();
	}

	/**
	 * Determina el lote vigente por codigo.
	 * Prioridad: activo, lote real (no SIN-LOTE), con saldo bodega, fecha mas antigua, id menor.
	 *
	 * @param array<int, array<string, mixed>> $rows Filas DB.
	 * @return array<string, string> codigo => lote.
	 */
	private static function compute_active_lote_map( $rows ) {
		$by_codigo = array();

		foreach ( $rows as $row ) {
			if ( ! empty( $row['is_deleted'] ) ) {
				continue;
			}

			if ( 'activo' !== ( $row['estado'] ?? 'activo' ) ) {
				continue;
			}

			$codigo = self::normalize_codigo( $row['codigo'] ?? '' );
			if ( '' === $codigo ) {
				continue;
			}

			if ( ! isset( $by_codigo[ $codigo ] ) || self::is_better_active_lote_row( $row, $by_codigo[ $codigo ] ) ) {
				$by_codigo[ $codigo ] = $row;
			}
		}

		$map = array();
		foreach ( $by_codigo as $codigo => $row ) {
			$map[ $codigo ] = self::normalize_lote( $row['lote'] ?? '' );
		}

		return $map;
	}

	/**
	 * Lote vacio o placeholder SIN-LOTE.
	 *
	 * @param string $lote Lote.
	 * @return bool
	 */
	private static function is_placeholder_lote( $lote ) {
		$lote = self::normalize_lote( $lote );

		return '' === $lote || 'SIN-LOTE' === $lote;
	}

	/**
	 * True si el registro tiene saldo bodega significativo.
	 *
	 * @param array<string, mixed> $row Fila.
	 * @return bool
	 */
	private static function row_has_saldo_bodega( $row ) {
		return abs( (float) ( $row['saldo_bodega'] ?? 0 ) ) >= 0.01;
	}

	/**
	 * True si $candidate debe reemplazar a $current como lote vigente.
	 *
	 * @param array<string, mixed> $candidate Candidato.
	 * @param array<string, mixed> $current   Actual.
	 * @return bool
	 */
	private static function is_better_active_lote_row( $candidate, $current ) {
		$candidate_placeholder = self::is_placeholder_lote( $candidate['lote'] ?? '' ) ? 1 : 0;
		$current_placeholder   = self::is_placeholder_lote( $current['lote'] ?? '' ) ? 1 : 0;

		if ( $candidate_placeholder !== $current_placeholder ) {
			return $candidate_placeholder < $current_placeholder;
		}

		$candidate_stock = self::row_has_saldo_bodega( $candidate ) ? 0 : 1;
		$current_stock   = self::row_has_saldo_bodega( $current ) ? 0 : 1;

		if ( $candidate_stock !== $current_stock ) {
			return $candidate_stock < $current_stock;
		}

		$current_ts   = strtotime( (string) ( $current['created_at'] ?? '' ) ) ?: PHP_INT_MAX;
		$candidate_ts = strtotime( (string) ( $candidate['created_at'] ?? '' ) ) ?: PHP_INT_MAX;

		if ( $candidate_ts !== $current_ts ) {
			return $candidate_ts < $current_ts;
		}

		return (int) ( $candidate['id'] ?? 0 ) < (int) ( $current['id'] ?? 0 );
	}

	/**
	 * ORDER BY SQL para elegir lote vigente.
	 *
	 * @return string
	 */
	private static function sql_lote_vigente_order() {
		return "CASE WHEN lote = '' OR UPPER(lote) = 'SIN-LOTE' THEN 1 ELSE 0 END ASC,
			CASE WHEN ABS(saldo_bodega) >= 0.01 THEN 0 ELSE 1 END ASC,
			created_at ASC,
			id ASC";
	}

	/**
	 * SQL: la fila $preferred_alias es preferible como lote vigente frente a $current_alias.
	 *
	 * @param string $preferred_alias Alias candidato.
	 * @param string $current_alias   Alias fila actual.
	 * @return string
	 */
	private static function sql_lote_row_is_preferred( $preferred_alias, $current_alias ) {
		$preferred_placeholder = "CASE WHEN {$preferred_alias}.lote = '' OR UPPER({$preferred_alias}.lote) = 'SIN-LOTE' THEN 1 ELSE 0 END";
		$current_placeholder   = "CASE WHEN {$current_alias}.lote = '' OR UPPER({$current_alias}.lote) = 'SIN-LOTE' THEN 1 ELSE 0 END";
		$preferred_stock       = "CASE WHEN ABS({$preferred_alias}.saldo_bodega) >= 0.01 THEN 0 ELSE 1 END";
		$current_stock         = "CASE WHEN ABS({$current_alias}.saldo_bodega) >= 0.01 THEN 0 ELSE 1 END";

		return "(
			({$preferred_placeholder}) < ({$current_placeholder})
			OR (
				({$preferred_placeholder}) = ({$current_placeholder})
				AND (
					({$preferred_stock}) < ({$current_stock})
					OR (
						({$preferred_stock}) = ({$current_stock})
						AND (
							{$preferred_alias}.created_at < {$current_alias}.created_at
							OR ({$preferred_alias}.created_at = {$current_alias}.created_at AND {$preferred_alias}.id < {$current_alias}.id)
						)
					)
				)
			)
		)";
	}

	/**
	 * Sobrescribe lotes de cierre con el maestro del inventario bodega (sin efectos secundarios).
	 *
	 * @param array<string, mixed> $config Configuración cierre.
	 * @return array<string, mixed>
	 */
	public static function overlay_warehouse_lotes_on_cierre_config( $config ) {
		if ( empty( $config['bases'] ) || ! is_array( $config['bases'] ) ) {
			return $config;
		}

		foreach ( self::lote_map() as $cod => $lote_master ) {
			foreach ( $config['bases'] as $index => $base ) {
				if ( ! is_array( $base ) ) {
					continue;
				}

				$base_cod  = self::normalize_codigo( $base['cod'] ?? '' );
				$saldo_cod = self::normalize_codigo( $base['cod_saldo'] ?? '' );

				if ( $base_cod === $cod && (string) ( $base['lote'] ?? '' ) !== $lote_master ) {
					$config['bases'][ $index ]['lote'] = $lote_master;
				}

				if ( $saldo_cod === $cod && (string) ( $base['lote_saldo'] ?? '' ) !== $lote_master ) {
					$config['bases'][ $index ]['lote_saldo'] = $lote_master;
				}
			}
		}

		return $config;
	}

	/**
	 * Guarda o actualiza un registro (codigo + lote obligatorios).
	 *
	 * @param array<string, mixed> $data Datos del formulario.
	 * @return int|\WP_Error ID insertado/actualizado.
	 */
	public static function save_row( $data ) {
		global $wpdb;

		$codigo = self::normalize_codigo( $data['codigo'] ?? '' );
		if ( '' === $codigo ) {
			return new WP_Error( 'missing_codigo', __( 'El código es obligatorio.', 'logistica' ) );
		}

		$row_id   = (int) ( $data['id'] ?? 0 );
		$existing = $row_id > 0 ? self::get_by_id( $row_id ) : null;
		$before_raw = $existing ? Logistica_Soft_Delete::get_row( 'inventario_bodega', (int) $existing['id'], true ) : null;

		if ( ! $existing ) {
			$existing = self::get_by_codigo_lote( $codigo, $data['lote'] ?? '' );
		}

		$payload = self::sanitize_row( $data, $existing );
		$lote    = self::normalize_lote( $payload['lote'] ?? '' );
		if ( '' === $lote ) {
			$lote = self::ensure_unique_lote( $codigo, 'SIN-LOTE' );
		}
		$payload['lote'] = $lote;

		if ( $existing && $lote !== self::normalize_lote( $existing['lote'] ?? '' ) ) {
			$conflict = self::get_by_codigo_lote( $codigo, $lote );
			if ( $conflict && (int) $conflict['id'] !== (int) $existing['id'] ) {
				return new WP_Error( 'duplicate_lote', __( 'Ya existe ese código con el mismo lote.', 'logistica' ) );
			}
		}

		$table = Logistica_Database::table( 'inventario_bodega' );
		$now   = current_time( 'mysql' );

		if ( $existing ) {
			$payload['updated_at'] = $now;
			$updated               = $wpdb->update(
				$table,
				$payload,
				array( 'id' => (int) $existing['id'] ),
				self::formats_for( $payload ),
				array( '%d' )
			);

			if ( false === $updated ) {
				return new WP_Error( 'db_error', __( 'No se pudo actualizar el inventario.', 'logistica' ) );
			}

			$row_id = (int) $existing['id'];
		} else {
			$payload['codigo']     = $codigo;
			$payload['created_at'] = $now;
			$payload['updated_at'] = $now;
			$inserted              = $wpdb->insert( $table, $payload, self::formats_for( $payload ) );

			if ( ! $inserted ) {
				$db_message = '' !== (string) $wpdb->last_error
					? ' ' . sanitize_text_field( (string) $wpdb->last_error )
					: '';
				return new WP_Error( 'db_error', __( 'No se pudo crear el inventario.', 'logistica' ) . $db_message );
			}

			$row_id = (int) $wpdb->insert_id;
		}

		self::clear_active_lote_map_cache();

		$saved = self::get_by_id( $row_id );
		if ( $saved && ! empty( $saved['es_lote_activo'] ) ) {
			self::sync_lote_to_cierre_bases( $codigo, (string) ( $saved['lote'] ?? '' ) );
		}

		$after_raw = Logistica_Soft_Delete::get_row( 'inventario_bodega', $row_id, true );
		if ( $before_raw ) {
			Logistica_Entity_Lifecycle::record_update(
				'warehouse',
				'item',
				(string) $row_id,
				$before_raw,
				$after_raw ?: array(),
				sprintf(
					/* translators: %s: warehouse item code */
					__( 'Inventario bodega %s actualizado', 'logistica' ),
					$codigo
				)
			);
		} elseif ( $after_raw ) {
			Logistica_Entity_Lifecycle::record_create(
				'warehouse',
				'item',
				(string) $row_id,
				$after_raw,
				sprintf(
					/* translators: %s: warehouse item code */
					__( 'Inventario bodega %s creado', 'logistica' ),
					$codigo
				)
			);
		}

		return $row_id;
	}

	/**
	 * Actualiza solo inventario fisico (frontend).
	 *
	 * @param int   $id ID fila.
	 * @param float $inventario_fisico Valor fisico.
	 * @return true|\WP_Error
	 */
	public static function update_inventario_fisico( $id, $inventario_fisico ) {
		$row = self::get_by_id( $id );
		if ( ! $row ) {
			return new WP_Error( 'not_found', __( 'Registro no encontrado en inventario.', 'logistica' ) );
		}

		$row['inventario_fisico'] = $inventario_fisico;

		return self::save_row( $row );
	}

	/**
	 * Elimina un registro del inventario de bodega.
	 *
	 * @param int $id ID fila.
	 * @return true|\WP_Error
	 */
	public static function delete_row( $id ) {
		$row = self::get_by_id( $id );
		if ( ! $row ) {
			return new WP_Error( 'not_found', __( 'Registro no encontrado.', 'logistica' ) );
		}

		$codigo     = self::normalize_codigo( $row['codigo'] ?? '' );
		$was_active = ! empty( $row['es_lote_activo'] );
		$raw_row    = Logistica_Soft_Delete::get_row( 'inventario_bodega', (int) $row['id'], true );

		$result = Logistica_Soft_Delete::soft_delete( 'inventario_bodega', (int) $row['id'], $raw_row ?: $row );
		if ( is_wp_error( $result ) ) {
			return $result;
		}

		self::clear_active_lote_map_cache();

		if ( $was_active && '' !== $codigo ) {
			$next = self::get_active_row_for_codigo( $codigo );
			if ( $next ) {
				self::sync_lote_to_cierre_bases( $codigo, (string) ( $next['lote'] ?? '' ) );
			}
		}

		return true;
	}

	/**
	 * Activa o desactiva un registro.
	 *
	 * @param int    $id ID fila.
	 * @param string $estado activo|inactivo.
	 * @return array<string, mixed>|\WP_Error
	 */
	public static function set_row_estado( $id, $estado ) {
		$row = self::get_by_id( $id );
		if ( ! $row ) {
			return new WP_Error( 'not_found', __( 'Registro no encontrado.', 'logistica' ) );
		}

		$estado = sanitize_key( $estado );
		if ( ! in_array( $estado, array( 'activo', 'inactivo' ), true ) ) {
			return new WP_Error( 'invalid_estado', __( 'Estado no válido.', 'logistica' ) );
		}

		$row['estado'] = $estado;
		$result        = self::save_row( $row );
		if ( is_wp_error( $result ) ) {
			return $result;
		}

		$codigo = self::normalize_codigo( $row['codigo'] ?? '' );
		$active = self::get_active_row_for_codigo( $codigo );
		if ( $active ) {
			self::sync_lote_to_cierre_bases( $codigo, (string) ( $active['lote'] ?? '' ) );
		}

		$saved = self::get_by_id( $id );
		return $saved ? $saved : new WP_Error( 'db_error', __( 'No se pudo actualizar el estado.', 'logistica' ) );
	}

	/**
	 * Sugiere un lote libre para duplicar un registro del mismo codigo.
	 *
	 * @param string $codigo Código origen.
	 * @param string $lote Lote origen.
	 * @return string
	 */
	public static function suggest_duplicate_lote( $codigo, $lote = '' ) {
		$base      = self::normalize_lote( $lote );
		if ( '' === $base || 'SIN-LOTE' === $base ) {
			$base = 'LOTE-COPIA';
		} else {
			$base .= '-COPIA';
		}

		$candidate = $base;
		$suffix    = 2;

		while ( self::get_by_codigo_lote( $codigo, $candidate ) ) {
			$candidate = $base . $suffix;
			++$suffix;
		}

		return $candidate;
	}

	/**
	 * Sugiere un codigo libre para duplicar un registro.
	 *
	 * @param string $codigo Código origen.
	 * @return string
	 */
	public static function suggest_duplicate_codigo( $codigo ) {
		$base      = self::normalize_codigo( $codigo );
		$candidate = $base . '-COPIA';
		$suffix    = 2;

		while ( self::get_by_codigo_lote( $candidate, 'SIN-LOTE' ) || self::get_active_row_for_codigo( $candidate ) ) {
			$candidate = $base . '-COPIA' . $suffix;
			++$suffix;
		}

		return $candidate;
	}

	/**
	 * Duplica un registro (mismo codigo, lote nuevo por defecto).
	 *
	 * @param int    $id ID origen.
	 * @param string $new_lote Lote destino.
	 * @param string $new_codigo Código destino opcional.
	 * @return array<string, mixed>|\WP_Error
	 */
	public static function duplicate_row( $id, $new_lote = '', $new_codigo = '' ) {
		$source = self::get_by_id( $id );
		if ( ! $source ) {
			return new WP_Error( 'not_found', __( 'Registro no encontrado.', 'logistica' ) );
		}

		$new_codigo = self::normalize_codigo( $new_codigo );
		if ( '' === $new_codigo ) {
			$new_codigo = self::normalize_codigo( $source['codigo'] ?? '' );
		}

		$new_lote = self::normalize_lote( $new_lote );
		if ( '' === $new_lote ) {
			$new_lote = self::suggest_duplicate_lote( $new_codigo, (string) ( $source['lote'] ?? '' ) );
		}

		if ( self::get_by_codigo_lote( $new_codigo, $new_lote ) ) {
			return new WP_Error( 'duplicate_lote', __( 'Ya existe ese código con el mismo lote.', 'logistica' ) );
		}

		$copy = self::blank_row();
		foreach ( array_keys( self::form_edit_fields() ) as $field ) {
			if ( array_key_exists( $field, $source ) ) {
				$copy[ $field ] = $source[ $field ];
			}
		}

		$copy['codigo']                    = $new_codigo;
		$copy['lote']                      = $new_lote;
		$copy['ajuste_acumulado']          = 0;
		$copy['diferencia_ultimo_importe'] = 0;
		$copy['observaciones']             = (string) ( $source['observaciones'] ?? '' );
		unset( $copy['id'] );

		$result = self::save_row( $copy );
		if ( is_wp_error( $result ) ) {
			return $result;
		}

		$row = self::get_by_id( (int) $result );
		return $row ? $row : new WP_Error( 'db_error', __( 'No se pudo duplicar el registro.', 'logistica' ) );
	}

	/**
	 * Importa filas desde Excel (merge por codigo + lote).
	 *
	 * @param array<int, array<string, string>> $rows Filas parseadas.
	 * @return array{created:int,updated:int,skipped:int,mejorados:int}
	 */
	public static function merge_import_rows( $rows ) {
		$stats = array(
			'created'   => 0,
			'updated'   => 0,
			'skipped'   => 0,
			'mejorados' => 0,
			'error'     => '',
		);

		Logistica_Database::migrate_inventario_bodega_codigo_lote();

		foreach ( $rows as $row ) {
			$codigo = self::normalize_codigo( $row['codigo'] ?? '' );
			if ( '' === $codigo ) {
				++$stats['skipped'];
				continue;
			}

			$lote = isset( $row['lote'] ) ? self::normalize_lote( $row['lote'] ) : '';
			if ( '' === $lote ) {
				$lote = self::ensure_unique_lote( $codigo, 'SIN-LOTE' );
			}

			$existing = self::get_by_codigo_lote( $codigo, $lote );
			$merged   = $existing ? $existing : self::blank_row();
			$merged['codigo'] = $codigo;
			$merged['lote']   = $lote;

			foreach ( self::excel_import_fields() as $field ) {
				if ( 'lote' === $field ) {
					continue;
				}
				if ( array_key_exists( $field, $row ) ) {
					$merged[ $field ] = $row[ $field ];
				}
			}

			if ( isset( $row['inventario_fisico'] ) && '' !== trim( (string) $row['inventario_fisico'] ) ) {
				$merged['inventario_fisico'] = $row['inventario_fisico'];
			}

			$diferencia_previa = $existing ? abs( self::calcular_diferencia( $existing ) ) : null;
			$diferencia_nueva  = abs( self::calcular_diferencia( $merged ) );

			$merged['diferencia_ultimo_importe'] = $diferencia_nueva;

			$result = self::save_row( $merged );
			if ( is_wp_error( $result ) ) {
				++$stats['skipped'];
				if ( '' === $stats['error'] ) {
					$stats['error'] = $result->get_error_message();
				}
				continue;
			}

			if ( $existing ) {
				++$stats['updated'];
				if ( null !== $diferencia_previa && $diferencia_nueva < $diferencia_previa ) {
					++$stats['mejorados'];
				}
			} else {
				++$stats['created'];
			}
		}

		return $stats;
	}

	/**
	 * Sincroniza lote activo a todas las bases de cierre con el mismo codigo.
	 *
	 * @param string $codigo Código insumo.
	 * @param string $lote Lote activo.
	 * @return int Bases actualizadas.
	 */
	public static function sync_lote_to_cierre_bases( $codigo, $lote ) {
		if ( ! class_exists( 'Logistica_Order_Closing', false ) ) {
			return 0;
		}

		return (int) call_user_func( array( 'Logistica_Order_Closing', 'sync_lote_by_codigo' ), $codigo, $lote );
	}

	/**
	 * Propaga lotes del inventario a cierre (todas las bases).
	 *
	 * @return int Total bases actualizadas.
	 */
	public static function sync_all_lotes_to_cierre() {
		$total = 0;
		foreach ( self::lote_map() as $codigo => $lote ) {
			$total += self::sync_lote_to_cierre_bases( $codigo, $lote );
		}
		return $total;
	}

	/**
	 * Aplica lote maestro del inventario al guardar cierre de ordenes.
	 *
	 * @param array<string, mixed> $config Configuración cierre.
	 * @return array<string, mixed>
	 */
	public static function apply_master_lotes_to_cierre_config( $config ) {
		if ( empty( $config['bases'] ) || ! is_array( $config['bases'] ) ) {
			return $config;
		}

		$cod_lotes = array();
		foreach ( $config['bases'] as $base ) {
			$cod = self::normalize_codigo( $base['cod'] ?? '' );
			if ( '' !== $cod ) {
				$cod_lotes[ $cod ] = sanitize_text_field( (string) ( $base['lote'] ?? '' ) );
			}
		}

		$config          = self::overlay_warehouse_lotes_on_cierre_config( $config );
		$warehouse_lotes = self::lote_map();

		foreach ( $cod_lotes as $cod => $lote_cierre ) {
			if ( isset( $warehouse_lotes[ $cod ] ) ) {
				continue;
			}

			self::sync_lote_to_cierre_bases( $cod, $lote_cierre );
		}

		return $config;
	}

	/**
	 * Actualiza lote en inventario desde cierre y sincroniza demas bases.
	 *
	 * @param string $codigo Código.
	 * @param string $lote Lote.
	 * @return true|\WP_Error
	 */
	public static function update_lote_from_cierre( $codigo, $lote ) {
		$row = self::get_active_row_for_codigo( $codigo );
		if ( ! $row ) {
			self::sync_lote_to_cierre_bases( $codigo, $lote );
			return true;
		}

		$row['lote'] = $lote;
		$result      = self::save_row( $row );

		return is_wp_error( $result ) ? $result : true;
	}

	/**
	 * Diferencia inventario fisico - saldo Kardex.
	 *
	 * @param array<string, mixed> $row Fila inventario.
	 * @return float
	 */
	public static function calcular_diferencia( $row ) {
		return round( (float) ( $row['inventario_fisico'] ?? 0 ) - (float) ( $row['saldo_bodega'] ?? 0 ), self::QUANTITY_DECIMALS );
	}

	/**
	 * Formato localizado de cantidades de bodega.
	 *
	 * @param float $value Cantidad.
	 * @return string
	 */
	public static function format_quantity( $value ) {
		return number_format_i18n( (float) $value, self::QUANTITY_DECIMALS );
	}

	/**
	 * Indica si la diferencia mejoro respecto al ultimo importe.
	 *
	 * @param array<string, mixed> $row Fila inventario.
	 * @return bool
	 */
	public static function mejoro_desde_import( $row ) {
		$actual   = abs( self::calcular_diferencia( $row ) );
		$importe  = abs( (float) ( $row['diferencia_ultimo_importe'] ?? 0 ) );

		return $importe >= 0.01 && $actual < $importe;
	}

	/**
	 * @param array<string, mixed>      $row Fila DB.
	 * @param array<string, string>|null $active_lote_map Mapa lote vigente.
	 * @return array<string, mixed>
	 */
	public static function hydrate_row( $row, $active_lote_map = null ) {
		$codigo = self::normalize_codigo( $row['codigo'] ?? '' );
		$lote   = self::normalize_lote( $row['lote'] ?? '' );

		if ( null === $active_lote_map ) {
			$active_lote_map = self::get_active_lote_map_light();
		}

		$row['diferencia']           = self::calcular_diferencia( $row );
		$row['mejoro']               = self::mejoro_desde_import( $row );
		$row['tiene_alerta']         = abs( $row['diferencia'] ) >= 0.01;
		$row['es_lote_activo']       = ( 'activo' === ( $row['estado'] ?? 'activo' ) ) && isset( $active_lote_map[ $codigo ] ) && $active_lote_map[ $codigo ] === $lote;
		$row['registro_activo']      = ( 'activo' === ( $row['estado'] ?? 'activo' ) );
		$row['fecha_creacion']       = (string) ( $row['created_at'] ?? '' );
		$row['fecha_creacion_label'] = self::format_created_at( $row['created_at'] ?? '' );

		return $row;
	}

	/**
	 * @param string $created_at Fecha MySQL.
	 * @return string
	 */
	public static function format_created_at( $created_at ) {
		$timestamp = strtotime( (string) $created_at );
		if ( ! $timestamp ) {
			return '';
		}

		return date_i18n( 'd/m/Y H:i', $timestamp );
	}

	/**
	 * @param array<string, mixed>      $data Datos entrada.
	 * @param array<string, mixed>|null $existing Fila previa.
	 * @return array<string, mixed>
	 */
	private static function sanitize_row( $data, $existing = null ) {
		$out = array(
			'descripcion'               => sanitize_text_field( wp_unslash( $data['descripcion'] ?? '' ) ),
			'materia_prima'             => sanitize_text_field( wp_unslash( $data['materia_prima'] ?? '' ) ),
			'medida'                    => sanitize_text_field( wp_unslash( $data['medida'] ?? '' ) ),
			'lote'                      => sanitize_text_field( wp_unslash( $data['lote'] ?? '' ) ),
			'saldo_bodega'              => (float) ( $data['saldo_bodega'] ?? 0 ),
			'clase'                     => sanitize_text_field( wp_unslash( $data['clase'] ?? '' ) ),
			'inventario_fisico'         => (float) ( $data['inventario_fisico'] ?? 0 ),
			'ajuste_acumulado'          => (float) ( $data['ajuste_acumulado'] ?? ( $existing['ajuste_acumulado'] ?? 0 ) ),
			'diferencia_ultimo_importe' => (float) ( $data['diferencia_ultimo_importe'] ?? ( $existing['diferencia_ultimo_importe'] ?? 0 ) ),
			'observaciones'             => sanitize_textarea_field( wp_unslash( $data['observaciones'] ?? '' ) ),
			'estado'                    => sanitize_key( $data['estado'] ?? 'activo' ),
		);

		if ( ! in_array( $out['estado'], array( 'activo', 'inactivo' ), true ) ) {
			$out['estado'] = 'activo';
		}

		return $out;
	}

	/**
	 * @param string $codigo Código.
	 * @return string
	 */
	/**
	 * Escape seguro para LIKE (no depende de $wpdb->esc_like).
	 *
	 * @param string $text Texto.
	 * @return string
	 */
	private static function esc_like( $text ) {
		global $wpdb;

		if ( isset( $wpdb ) && is_object( $wpdb ) && method_exists( $wpdb, 'esc_like' ) ) {
			return $wpdb->esc_like( (string) $text );
		}

		return addcslashes( (string) $text, '_%\\' );
	}

	private static function normalize_codigo( $codigo ) {
		return strtoupper( trim( sanitize_text_field( (string) $codigo ) ) );
	}

	/**
	 * @param string $lote Lote.
	 * @return string
	 */
	private static function normalize_lote( $lote ) {
		$lote = trim( sanitize_text_field( (string) $lote ) );

		if ( is_numeric( $lote ) ) {
			$number = (float) $lote;
			if ( abs( $number - round( $number ) ) < 0.00001 ) {
				$lote = (string) (int) round( $number );
			}
		}

		return strtoupper( $lote );
	}

	/**
	 * Garantiza un lote unico para un codigo.
	 *
	 * @param string $codigo Código.
	 * @param string $lote Lote base.
	 * @return string
	 */
	private static function ensure_unique_lote( $codigo, $lote ) {
		$lote = self::normalize_lote( $lote );
		if ( ! self::get_by_codigo_lote( $codigo, $lote ) ) {
			return $lote;
		}

		$suffix    = 2;
		$candidate = $lote . '-' . $suffix;
		while ( self::get_by_codigo_lote( $codigo, $candidate ) ) {
			++$suffix;
			$candidate = $lote . '-' . $suffix;
		}

		return $candidate;
	}

	/**
	 * @param array<string, mixed> $payload Datos.
	 * @return string[]
	 */
	private static function formats_for( $payload ) {
		$float_fields = array( 'saldo_bodega', 'inventario_fisico', 'ajuste_acumulado', 'diferencia_ultimo_importe' );
		$formats      = array();

		foreach ( array_keys( $payload ) as $key ) {
			$formats[] = in_array( $key, $float_fields, true ) ? '%f' : '%s';
		}

		return $formats;
	}
}
