<?php
/**
 * Minutas de producción — almacenamiento y revisión.
 *
 * @package Logistica
 */

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

/**
 * CRUD de minutas importadas desde Excel.
 */
class Logistica_Minutas {

	/**
	 * Columnas de datos (clave interna => etiqueta).
	 *
	 * @return array<string, string>
	 */
	public static function columns() {
		return array(
			'minuta'         => __( 'Minuta', 'logistica' ),
			'fecha'          => __( 'Fecha', 'logistica' ),
			'orden'          => __( 'Orden', 'logistica' ),
			'cod_cliente'    => __( 'Cod. cliente', 'logistica' ),
			'cliente'        => __( 'Cliente', 'logistica' ),
			'cod_referencia' => __( 'Cod. referencia', 'logistica' ),
			'referencia'     => __( 'Referencia', 'logistica' ),
			'cod_maq'        => __( 'Cod. máq.', 'logistica' ),
			'maquina'        => __( 'Máquina', 'logistica' ),
			'cod_pro'        => __( 'Cod. pro.', 'logistica' ),
			'proceso'        => __( 'Proceso', 'logistica' ),
			'hora_inici'     => __( 'Hora inicio', 'logistica' ),
			'hora_final'     => __( 'Hora final', 'logistica' ),
			'minutos'        => __( 'Minutos', 'logistica' ),
			'buenas'         => __( 'Buenas', 'logistica' ),
			'malas'          => __( 'Malas', 'logistica' ),
			'turno'          => __( 'Turno', 'logistica' ),
			'codigo_emp'     => __( 'Cod. empleado', 'logistica' ),
			'empleado'       => __( 'Empleado', 'logistica' ),
			'paso'           => __( 'Paso', 'logistica' ),
			'equipo'         => __( 'Equipo', 'logistica' ),
			'estado'         => __( 'Estado', 'logistica' ),
			'fecha_sys'      => __( 'Fecha sys.', 'logistica' ),
			'observacio'     => __( 'Observación', 'logistica' ),
			'mano_obra'      => __( 'Mano obra', 'logistica' ),
			'maquila'        => __( 'Maquila', 'logistica' ),
			'costo_admn'     => __( 'Costo admn.', 'logistica' ),
			'opeaux'         => __( 'Opeaux', 'logistica' ),
			'revision'       => __( 'Revisión', 'logistica' ),
		);
	}

	/**
	 * Columnas visibles en la pestaña de revisión.
	 *
	 * @return string[]
	 */
	public static function review_column_keys() {
		return array(
			'minuta',
			'fecha',
			'orden',
			'cliente',
			'referencia',
			'maquina',
			'proceso',
			'hora_inici',
			'hora_final',
			'minutos',
			'buenas',
			'malas',
			'empleado',
			'estado',
			'observacio',
			'revision',
		);
	}

	/**
	 * Valores permitidos para revisión.
	 *
	 * @return string[]
	 */
	public static function revision_options() {
		if ( class_exists( 'Logistica_Select_Catalog', false ) ) {
			$options = Logistica_Select_Catalog::get_options( 'minuta_revision' );
			if ( is_array( $options ) && ! empty( $options ) ) {
				return array_values( $options );
			}
		}

		return array(
			'Pendiente',
			'OK',
			'Con observacion',
		);
	}

	/**
	 * @return array<int, array<string, mixed>>
	 */
	public static function get_all() {
		global $wpdb;

		$table = Logistica_Database::table( 'minutas' );
		$not_deleted = Logistica_Soft_Delete::sql_not_deleted( 'minutas' );
		$rows  = $wpdb->get_results(
			"SELECT * FROM {$table} WHERE {$not_deleted} ORDER BY fecha DESC, CAST(minuta AS UNSIGNED) DESC, minuta DESC",
			ARRAY_A
		); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

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

		return array_map( array( __CLASS__, 'normalize_row' ), $rows );
	}

	/**
	 * @param array<int, array<string, mixed>>|null $rows Filas precargadas.
	 * @return array<string, int|float>
	 */
	public static function get_stats( $rows = null ) {
		$rows = is_array( $rows ) ? $rows : self::get_all();

		$stats = array(
			'total'                => count( $rows ),
			'total_minutos'        => 0,
			'total_buenas'         => 0,
			'total_malas'          => 0,
			'sin_hora_final'       => 0,
			'con_malas'            => 0,
			'pendientes_revision'  => 0,
		);

		foreach ( $rows as $row ) {
			$stats['total_minutos'] += self::numeric_value( $row['minutos'] ?? 0 );
			$stats['total_buenas'] += self::numeric_value( $row['buenas'] ?? 0 );
			$stats['total_malas']  += self::numeric_value( $row['malas'] ?? 0 );

			if ( '' === trim( (string) ( $row['hora_final'] ?? '' ) ) ) {
				++$stats['sin_hora_final'];
			}

			if ( self::numeric_value( $row['malas'] ?? 0 ) > 0 ) {
				++$stats['con_malas'];
			}

			if ( 'Pendiente' === (string) ( $row['revision'] ?? '' ) ) {
				++$stats['pendientes_revision'];
			}
		}

		return $stats;
	}

	/**
	 * Reemplaza todas las minutas (importación completa).
	 *
	 * @param array<int, array<string, string>> $rows Filas importadas.
	 * @return array{created:int, skipped:int, error?:string}
	 */
	public static function replace_all( $rows ) {
		global $wpdb;

		$table = Logistica_Database::table( 'minutas' );
		if ( ! $table ) {
			return array(
				'created' => 0,
				'skipped' => 0,
				'error'   => __( 'Tabla de minutas no disponible.', 'logistica' ),
			);
		}

		$existing_revisions = array();
		$current            = $wpdb->get_results(
			'SELECT minuta, revision FROM ' . $table . ' WHERE ' . Logistica_Soft_Delete::sql_not_deleted( 'minutas' ),
			ARRAY_A
		); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		if ( is_array( $current ) ) {
			foreach ( $current as $item ) {
				$key = trim( (string) ( $item['minuta'] ?? '' ) );
				if ( '' !== $key ) {
					$existing_revisions[ $key ] = (string) ( $item['revision'] ?? 'Pendiente' );
				}
			}
		}

		$active_rows = $wpdb->get_results(
			'SELECT * FROM ' . $table . ' WHERE ' . Logistica_Soft_Delete::sql_not_deleted( 'minutas' ),
			ARRAY_A
		); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		if ( is_array( $active_rows ) && ! empty( $active_rows ) ) {
			$import_id = 'bulk-' . gmdate( 'Y-m-d-His' );
			Logistica_Version_History::save(
				array(
					'module_key'  => 'minutas',
					'entity_type' => 'bulk_import',
					'entity_id'   => $import_id,
					'summary'     => __( 'Snapshot previo a importación de minutas', 'logistica' ),
					'payload'     => $active_rows,
				)
			);
			Logistica_Audit::log(
				array(
					'module_key'  => 'minutas',
					'entity_type' => 'bulk_import',
					'entity_id'   => $import_id,
					'action'      => 'import_replace',
					'summary'     => sprintf(
						/* translators: %d: number of rows */
						__( 'Importación reemplazó %d minutas activas', 'logistica' ),
						count( $active_rows )
					),
					'payload_before' => $active_rows,
				)
			);

			Logistica_Soft_Delete::mark_deleted_batch(
				'minutas',
				array_map(
					static function ( $row ) {
						return (int) ( $row['id'] ?? 0 );
					},
					$active_rows
				)
			);
		}

		$created = 0;
		$skipped = 0;
		$now     = current_time( 'mysql' );

		foreach ( $rows as $row ) {
			$row = self::sanitize_import_row( $row );
			if ( '' === $row['minuta'] ) {
				++$skipped;
				continue;
			}

			if ( '' === trim( (string) ( $row['revision'] ?? '' ) ) && isset( $existing_revisions[ $row['minuta'] ] ) ) {
				$row['revision'] = $existing_revisions[ $row['minuta'] ];
			}

			$insert = $row;
			$insert['created_at'] = $now;
			$insert['updated_at'] = $now;

			$formats = array_fill( 0, count( $insert ), '%s' );

			$result = $wpdb->insert( $table, $insert, $formats );
			if ( false === $result ) {
				return array(
					'created' => $created,
					'skipped' => $skipped,
					'error'   => sprintf(
						/* translators: %s: database error message */
						__( 'Error al guardar minutas: %s', 'logistica' ),
						$wpdb->last_error ? $wpdb->last_error : __( 'error desconocido', 'logistica' )
					),
				);
			}

			++$created;
		}

		return array(
			'created' => $created,
			'skipped' => $skipped,
		);
	}

	/**
	 * @param int    $row_id ID.
	 * @param string $revision Estado revisión.
	 * @return true|\WP_Error
	 */
	public static function update_revision( $row_id, $revision ) {
		global $wpdb;

		$row_id   = (int) $row_id;
		$revision = self::sanitize_revision( $revision );

		if ( $row_id <= 0 ) {
			return new WP_Error( 'invalid', __( 'Registro no válido.', 'logistica' ) );
		}

		$table = Logistica_Database::table( 'minutas' );
		$updated = $wpdb->update(
			$table,
			array(
				'revision'   => $revision,
				'updated_at' => current_time( 'mysql' ),
			),
			array( 'id' => $row_id ),
			array( '%s', '%s' ),
			array( '%d' )
		);

		if ( false === $updated ) {
			return new WP_Error( 'db', __( 'No se pudo guardar la revisión.', 'logistica' ) );
		}

		return true;
	}

	/**
	 * @param array<string, mixed> $row Fila DB.
	 * @return array<string, mixed>
	 */
	public static function normalize_row( $row ) {
		$row = is_array( $row ) ? $row : array();

		foreach ( array_keys( self::columns() ) as $key ) {
			if ( ! isset( $row[ $key ] ) ) {
				$row[ $key ] = '';
			}
		}

		if ( '' === trim( (string) ( $row['revision'] ?? '' ) ) ) {
			$row['revision'] = 'Pendiente';
		}

		$row['id'] = (int) ( $row['id'] ?? 0 );

		$row['fecha']     = self::format_fecha_display( $row['fecha'] ?? '' );
		$row['fecha_sys'] = self::format_fecha_display( $row['fecha_sys'] ?? '' );

		$row['flags'] = array(
			'sin_hora_final' => '' === trim( (string) ( $row['hora_final'] ?? '' ) ),
			'con_malas'      => self::numeric_value( $row['malas'] ?? 0 ) > 0,
			'pendiente'      => 'Pendiente' === (string) ( $row['revision'] ?? '' ),
		);

		return $row;
	}

	/**
	 * @param array<string, string> $row Fila importada.
	 * @return array<string, string>
	 */
	private static function sanitize_import_row( $row ) {
		$clean = array();

		foreach ( array_keys( self::columns() ) as $key ) {
			$value = isset( $row[ $key ] ) ? (string) $row[ $key ] : '';
			if ( 'observacio' === $key ) {
				$clean[ $key ] = sanitize_textarea_field( $value );
			} else {
				$clean[ $key ] = sanitize_text_field( $value );
			}
		}

		if ( '' === trim( $clean['revision'] ) ) {
			$clean['revision'] = 'Pendiente';
		} else {
			$clean['revision'] = self::sanitize_revision( $clean['revision'] );
		}

		$clean['fecha']     = self::format_fecha_display( $clean['fecha'] );
		$clean['fecha_sys'] = self::format_fecha_display( $clean['fecha_sys'] );

		return $clean;
	}

	/**
	 * @param string $revision Valor revisión.
	 * @return string
	 */
	private static function sanitize_revision( $revision ) {
		$revision = sanitize_text_field( (string) $revision );
		return in_array( $revision, self::revision_options(), true ) ? $revision : 'Pendiente';
	}

	/**
	 * Fecha visible sin componente de hora.
	 *
	 * @param mixed $value Valor fecha.
	 * @return string
	 */
	public static function format_fecha_display( $value ) {
		$value = trim( (string) $value );

		if ( '' === $value ) {
			return '';
		}

		if ( preg_match( '/^(.+?)\s+\d{1,2}:\d{2}(:\d{2})?$/', $value, $matches ) ) {
			return trim( $matches[1] );
		}

		return $value;
	}

	/**
	 * @param mixed $value Valor numérico.
	 * @return float
	 */
	public static function parse_number( $value ) {
		return self::numeric_value( $value );
	}

	/**
	 * Columnas de texto (alineación izquierda) en la tabla de revisión.
	 *
	 * @return string[]
	 */
	public static function review_text_column_keys() {
		return array(
			'minuta',
			'fecha',
			'orden',
			'cliente',
			'referencia',
			'maquina',
			'proceso',
			'hora_inici',
			'hora_final',
			'empleado',
			'estado',
			'observacio',
			'revision',
		);
	}

	/**
	 * Columnas numéricas con totales en la tabla de revisión.
	 *
	 * @return string[]
	 */
	public static function review_total_column_keys() {
		return array(
			'minutos',
			'buenas',
			'malas',
		);
	}

	/**
	 * Columnas con copia al clic en la tabla de revisión.
	 *
	 * @return string[]
	 */
	public static function review_copy_column_keys() {
		return array(
			'minuta',
			'orden',
		);
	}

	/**
	 * @param mixed $value Valor numérico.
	 * @return float
	 */
	private static function numeric_value( $value ) {
		if ( is_numeric( $value ) ) {
			return (float) $value;
		}

		$normalized = str_replace( array( '.', ',' ), array( '', '.' ), preg_replace( '/[^\d,.\-]/', '', (string) $value ) );
		return is_numeric( $normalized ) ? (float) $normalized : 0.0;
	}
}
