<?php
/**
 * Conteo de uso de opciones de catálogos de selects.
 *
 * @package Logistica
 */

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

/**
 * Indica cuántos registros usan una opción antes de eliminarla.
 */
class Logistica_Select_Catalog_Usage {

	/**
	 * @param string $catalog_id Catalog id.
	 * @param string $key        Key or label.
	 * @return int
	 */
	public static function count( $catalog_id, $key ) {
		$catalog_id = sanitize_key( (string) $catalog_id );
		if ( '__empty__' === $key ) {
			$key = '';
		}

		switch ( $catalog_id ) {
			case 'tapas_status':
				return self::count_in_conversion_status( $key );
			case 'rotulo_tipos':
				return self::count_db_column( 'rotulo_rollos', 'tipo', strtoupper( (string) $key ) );
			case 'rotulo_tamanos':
				return self::count_db_column( 'rotulo_rollos', 'tamano', (string) $key );
			case 'closing_base_types':
				return self::count_closing_base_types( $key ) + self::count_db_column( 'cierre_caracteristicas', 'tipo', (string) $key );
			case 'closing_variants':
				return self::count_closing_reference_field( 'variante', $key );
			case 'closing_product_types':
				return self::count_closing_reference_field( 'tipo_producto', $key );
			case 'closing_references':
				return self::count_closing_reference_field( 'nombre', $key );
			case 'minuta_revision':
				return self::count_db_column( 'minutas', 'revision', (string) $key );
			case 'pedidos_orden_estado':
				return self::count_db_column( 'pedidos_ordenes_lineas', 'estado', (string) $key );
			case 'priorities':
				return self::count_user_panel_field( array( 'agenda', 'compras' ), 'priority', $key );
			case 'agenda_statuses':
				return self::count_user_panel_field( array( 'agenda' ), 'status', $key );
			case 'day_types':
				return self::count_user_panel_day_types( $key ) + self::count_planeacion_day_types( $key );
			case 'planeacion_statuses':
				return self::count_planeacion_task_field( 'status', $key );
			case 'planeacion_labor_types':
				return self::count_planeacion_task_field( 'labor_type', $key );
			case 'planeacion_complexity':
				return self::count_planeacion_task_field( 'complexity', $key );
			case 'compra_statuses':
				return self::count_user_panel_field( array( 'compras' ), 'status', $key );
			default:
				return 0;
		}
	}

	/**
	 * @param string $catalog_id Catalog id.
	 * @param string $key        Key or label.
	 * @return string
	 */
	public static function message( $catalog_id, $key ) {
		$usage = self::count( $catalog_id, $key );
		if ( $usage <= 0 ) {
			return '';
		}

		$def   = Logistica_Select_Catalog::get_definition( $catalog_id );
		$title = is_array( $def ) ? (string) ( $def['title'] ?? $catalog_id ) : $catalog_id;

		return sprintf(
			/* translators: 1: catalog title, 2: usage count */
			_n(
				'«%1$s» se usa en %2$d registro.',
				'«%1$s» se usa en %2$d registros.',
				$usage,
				'logistica'
			),
			$title,
			$usage
		);
	}

	/**
	 * @param string $key Status key.
	 * @return int
	 */
	private static function count_in_conversion_status( $key ) {
		if ( ! class_exists( 'Logistica_Conversion_Tables', false ) ) {
			return 0;
		}

		$key   = sanitize_key( (string) $key );
		$state = Logistica_Conversion_Tables::get_state();
		$count = 0;
		$sheets = array( 'tapas-poliestireno', 'bolsas-calculo' );

		foreach ( $sheets as $sheet ) {
			$rows = $state[ $sheet ]['rows'] ?? null;
			if ( ! is_array( $rows ) ) {
				continue;
			}
			foreach ( $rows as $row ) {
				if ( ! is_array( $row ) ) {
					continue;
				}
				$status = sanitize_key( (string) ( $row['status'] ?? '' ) );
				if ( $status === $key ) {
					++$count;
				}
			}
		}

		return $count;
	}

	/**
	 * @param string $table_key Table key.
	 * @param string $column    Column name.
	 * @param string $value     Value.
	 * @return int
	 */
	private static function count_db_column( $table_key, $column, $value ) {
		global $wpdb;

		$table = Logistica_Database::table( $table_key );
		if ( ! $table || '' === (string) $value ) {
			return 0;
		}

		$column = preg_replace( '/[^a-z0-9_]/', '', (string) $column );
		if ( '' === $column ) {
			return 0;
		}

		$not_deleted = '';
		if ( class_exists( 'Logistica_Soft_Delete', false ) ) {
			$sql = Logistica_Soft_Delete::sql_not_deleted( $table_key );
			if ( $sql ) {
				$not_deleted = ' AND ' . $sql;
			}
		}

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		return (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$table} WHERE {$column} = %s{$not_deleted}", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				(string) $value
			)
		);
	}

	/**
	 * @param string $key Base type key.
	 * @return int
	 */
	private static function count_closing_base_types( $key ) {
		if ( ! class_exists( 'Logistica_Order_Closing', false ) ) {
			return 0;
		}

		$key    = sanitize_key( (string) $key );
		$config = Logistica_Order_Closing::get_config();
		$count  = 0;

		foreach ( (array) ( $config['bases'] ?? array() ) as $base ) {
			if ( ! is_array( $base ) ) {
				continue;
			}
			if ( sanitize_key( (string) ( $base['tipo'] ?? $base['base_tipo'] ?? '' ) ) === $key ) {
				++$count;
			}
		}

		return $count;
	}

	/**
	 * @param string $field variante|tipo_producto.
	 * @param string $value Label value.
	 * @return int
	 */
	private static function count_closing_reference_field( $field, $value ) {
		if ( ! class_exists( 'Logistica_Order_Closing', false ) ) {
			return 0;
		}

		$value  = trim( (string) $value );
		$config = Logistica_Order_Closing::get_config();
		$count  = 0;
		$needle = strtolower( $value );

		// Variantes también aparecen en bases.
		if ( 'variante' === $field ) {
			foreach ( (array) ( $config['bases'] ?? array() ) as $base ) {
				if ( ! is_array( $base ) ) {
					continue;
				}
				if ( strtolower( trim( (string) ( $base['variante'] ?? '' ) ) ) === $needle ) {
					++$count;
				}
			}
		}

		foreach ( (array) ( $config['referencias'] ?? array() ) as $reference ) {
			if ( ! is_array( $reference ) ) {
				continue;
			}
			$current = strtolower( trim( (string) ( $reference[ $field ] ?? '' ) ) );
			if ( $current === $needle ) {
				++$count;
			}
		}

		return $count;
	}

	/**
	 * @param list<string> $sections Panel sections.
	 * @param string       $field    Field name.
	 * @param string       $key      Value key.
	 * @return int
	 */
	private static function count_user_panel_field( $sections, $field, $key ) {
		$key   = sanitize_key( (string) $key );
		$count = 0;
		$users = get_users(
			array(
				'fields' => array( 'ID' ),
				'number' => 500,
			)
		);

		foreach ( $users as $user ) {
			$user_id = (int) ( is_object( $user ) ? $user->ID : $user );
			$raw     = get_user_meta( $user_id, 'logistica_user_panel_v1', true );
			if ( ! is_array( $raw ) ) {
				continue;
			}
			foreach ( $sections as $section ) {
				$items = $raw[ $section ] ?? null;
				if ( ! is_array( $items ) ) {
					continue;
				}
				foreach ( $items as $item ) {
					if ( ! is_array( $item ) ) {
						continue;
					}
					if ( sanitize_key( (string) ( $item[ $field ] ?? '' ) ) === $key ) {
						++$count;
					}
				}
			}
		}

		return $count;
	}

	/**
	 * @param string $key Day type key (may be empty).
	 * @return int
	 */
	private static function count_user_panel_day_types( $key ) {
		$key   = (string) $key;
		$count = 0;
		$users = get_users(
			array(
				'fields' => array( 'ID' ),
				'number' => 500,
			)
		);

		foreach ( $users as $user ) {
			$user_id = (int) ( is_object( $user ) ? $user->ID : $user );
			$raw     = get_user_meta( $user_id, 'logistica_user_panel_v1', true );
			if ( ! is_array( $raw ) ) {
				continue;
			}
			$checklist = $raw['checklist'] ?? $raw['lista_chequeo'] ?? null;
			if ( ! is_array( $checklist ) ) {
				continue;
			}
			foreach ( $checklist as $day ) {
				if ( ! is_array( $day ) ) {
					continue;
				}
				$day_type = (string) ( $day['day_type'] ?? $day['tipo'] ?? '' );
				if ( $day_type === $key ) {
					++$count;
				}
			}
		}

		return $count;
	}

	/**
	 * @param string $key Day type key.
	 * @return int
	 */
	private static function count_planeacion_day_types( $key ) {
		global $wpdb;

		$key   = (string) $key;
		$count = 0;
		$tables = array( 'planeacion_impresion', 'planeacion_formado' );

		foreach ( $tables as $table_key ) {
			$table = Logistica_Database::table( $table_key );
			if ( ! $table ) {
				continue;
			}
			$rows = $wpdb->get_col( "SELECT day_hours FROM {$table} WHERE day_hours IS NOT NULL AND day_hours <> ''" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			if ( ! is_array( $rows ) ) {
				continue;
			}
			foreach ( $rows as $json ) {
				$data = json_decode( (string) $json, true );
				if ( ! is_array( $data ) ) {
					continue;
				}
				foreach ( $data as $slot ) {
					if ( ! is_array( $slot ) ) {
						continue;
					}
					if ( (string) ( $slot['day_type'] ?? '' ) === $key ) {
						++$count;
					}
				}
			}
		}

		return $count;
	}

	/**
	 * @param string $field Task field.
	 * @param string $key   Value.
	 * @return int
	 */
	private static function count_planeacion_task_field( $field, $key ) {
		global $wpdb;

		$key    = sanitize_key( (string) $key );
		$field  = sanitize_key( (string) $field );
		$count  = 0;
		$tables = array( 'planeacion_impresion', 'planeacion_formado' );

		foreach ( $tables as $table_key ) {
			$table = Logistica_Database::table( $table_key );
			if ( ! $table ) {
				continue;
			}
			$rows = $wpdb->get_col( "SELECT tasks FROM {$table} WHERE tasks IS NOT NULL AND tasks <> ''" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			if ( ! is_array( $rows ) ) {
				continue;
			}
			foreach ( $rows as $json ) {
				$tasks = json_decode( (string) $json, true );
				if ( ! is_array( $tasks ) ) {
					continue;
				}
				foreach ( $tasks as $task ) {
					if ( ! is_array( $task ) ) {
						continue;
					}
					if ( sanitize_key( (string) ( $task[ $field ] ?? '' ) ) === $key ) {
						++$count;
					}
				}
			}
		}

		return $count;
	}
}
