<?php
/**
 * Ingredient data access.
 *
 * @package RestaurantCosting
 */

declare(strict_types=1);

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

/**
 * CRUD for rc_ingredients.
 */
final class RC_Ingredient_Repository {

	public static function get( int $id ): ?object {
		global $wpdb;
		$table = RC_Database::table( 'ingredients' );
		$row   = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d", $id ) );
		return $row ?: null;
	}

	/**
	 * Autocomplete search for recipe builder.
	 *
	 * @return array<int, array{id:int, name:string, food_type:string, unit_hint:string}>
	 */
	public static function search( string $term, int $limit = 15 ): array {
		global $wpdb;
		$table = RC_Database::table( 'ingredients' );
		$term  = trim( $term );
		if ( '' === $term ) {
			return array();
		}

		$like = '%' . $wpdb->esc_like( RC_Name_Matcher::normalize( $term ) ) . '%';
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT id, name, food_type, size_label FROM {$table}
				WHERE name_normalized LIKE %s OR name LIKE %s
				ORDER BY name ASC LIMIT %d",
				$like,
				'%' . $wpdb->esc_like( $term ) . '%',
				$limit
			),
			ARRAY_A
		);

		$out = array();
		foreach ( $rows ?: array() as $row ) {
			$hint = (string) $row['size_label'];
			if ( str_contains( strtolower( $hint ), 'ml' ) ) {
				$unit = 'ml';
			} elseif ( str_contains( strtolower( $hint ), 'ea' ) ) {
				$unit = 'ea';
			} else {
				$unit = 'g';
			}
			$out[] = array(
				'id'        => (int) $row['id'],
				'name'      => (string) $row['name'],
				'food_type' => (string) $row['food_type'],
				'unit_hint' => $unit,
			);
		}
		return $out;
	}

	public static function count( array $filters = array() ): int {
		global $wpdb;
		$table = RC_Database::table( 'ingredients' );

		$where   = array();
		$params  = array();

		if ( ! empty( $filters['s'] ) ) {
			$like      = '%' . $wpdb->esc_like( RC_Name_Matcher::normalize( $filters['s'] ) ) . '%';
			$where[]   = " (name_normalized LIKE %s OR name LIKE %s) ";
			$params[]  = $like;
			$params[]  = '%' . $wpdb->esc_like( $filters['s'] ) . '%';
		}

		if ( ! empty( $filters['f_type'] ) ) {
			$where[]  = " food_type = %s ";
			$params[] = $filters['f_type'];
		}

		if ( ! empty( $filters['f_supplier'] ) ) {
			$like_sup = '%' . $wpdb->esc_like( $filters['f_supplier'] ) . '%';
			$where[]  = " supplier LIKE %s ";
			$params[] = $like_sup;
		}

		if ( empty( $filters['show_inactive'] ) ) {
			$where[]  = " is_active = 1 ";
		}

		$where_clause = '';
		if ( ! empty( $where ) ) {
			$where_clause = ' WHERE ' . implode( ' AND ', $where );
		}

		$query = "SELECT COUNT(*) FROM {$table}{$where_clause}";
		if ( ! empty( $params ) ) {
			$query = $wpdb->prepare( $query, $params );
		}

		return (int) $wpdb->get_var( $query );
	}

	/**
	 * @return array<int, object>
	 */
	public static function list( int $limit = 50, int $offset = 0, array $filters = array(), string $orderby = 'name', string $order = 'ASC' ): array {
		global $wpdb;
		$table = RC_Database::table( 'ingredients' );

		$allowed_cols = array( 'name', 'food_type', 'supplier', 'weight_grams', 'food_cost' );
		$orderby = in_array( $orderby, $allowed_cols, true ) ? $orderby : 'name';
		$order   = ( strtoupper( $order ) === 'DESC' ) ? 'DESC' : 'ASC';

		$where = array();
		$params = array();

		if ( ! empty( $filters['s'] ) ) {
			$like = '%' . $wpdb->esc_like( RC_Name_Matcher::normalize( $filters['s'] ) ) . '%';
			$where[] = " (name_normalized LIKE %s OR name LIKE %s) ";
			$params[] = $like;
			$params[] = '%' . $wpdb->esc_like( $filters['s'] ) . '%';
		}

		if ( ! empty( $filters['f_type'] ) ) {
			$where[] = " food_type = %s ";
			$params[] = $filters['f_type'];
		}

		if ( ! empty( $filters['f_supplier'] ) ) {
			$like_sup = '%' . $wpdb->esc_like( $filters['f_supplier'] ) . '%';
			$where[] = " supplier LIKE %s ";
			$params[] = $like_sup;
		}

		if ( empty( $filters['show_inactive'] ) ) {
			$where[] = " is_active = 1 ";
		}

		$where_clause = '';
		if ( ! empty( $where ) ) {
			$where_clause = ' WHERE ' . implode( ' AND ', $where );
		}

		$query = "SELECT * FROM {$table} {$where_clause} ORDER BY {$orderby} {$order} LIMIT %d OFFSET %d";
		$final_params = array_merge( $params, array( $limit, $offset ) );

		return $wpdb->get_results(
			$wpdb->prepare( $query, $final_params )
		) ?: array();
	}

	/**
	 * Food type options for the add-ingredient form.
	 *
	 * @return array<int, string>
	 */
	public static function food_type_options(): array {
		global $wpdb;
		$table = RC_Database::table( 'food_types' );
		$rows  = $wpdb->get_col( "SELECT name FROM {$table} ORDER BY sort_order ASC, name ASC" );
		return $rows ?: array();
	}

	/**
	 * Create or Update ingredient from modal.
	 *
	 * @param array<string, mixed> $input Form values.
	 * @return array{success:bool, id?:int, name?:string, food_type?:string, unit_hint?:string, message?:string}
	 */
	public static function create_quick( array $input ): array {
		$name = sanitize_text_field( (string) ( $input['name'] ?? '' ) );
		if ( '' === $name ) {
			return array(
				'success' => false,
				'message' => __( 'Ingredient name is required.', 'restaurant-costing' ),
			);
		}

		$id = isset( $input['id'] ) && (int) $input['id'] > 0 ? (int) $input['id'] : null;

		// Check for duplicate normalized name
		$dup_id = RC_Name_Matcher::find_ingredient_id( $name );
		if ( $dup_id && ( null === $id || $dup_id !== $id ) ) {
			return array(
				'success' => false,
				'message' => __( 'An ingredient with this name already exists.', 'restaurant-costing' ),
			);
		}

		$unit = strtolower( trim( (string) ( $input['unit'] ?? 'g' ) ) );
		if ( ! in_array( $unit, array( 'g', 'ml', 'ea' ), true ) ) {
			$unit = 'g';
		}

		$weight_grams = max( 0.0001, (float) ( $input['weight_grams'] ?? 0 ) );
		$food_cost    = max( 0, (float) ( $input['food_cost'] ?? 0 ) );

		if ( $weight_grams <= 0 ) {
			return array(
				'success' => false,
				'message' => __( 'Reference weight must be greater than zero.', 'restaurant-costing' ),
			);
		}

		$food_type = sanitize_text_field( (string) ( $input['food_type'] ?? 'Other' ) );
		if ( '' !== $food_type ) {
			RC_Food_Type_Repository::find_or_create( $food_type );
		}
		$supplier  = sanitize_text_field( (string) ( $input['supplier'] ?? '' ) );
		if ( '' !== $supplier ) {
			RC_Supplier_Repository::find_or_create( $supplier );
		}
		$size      = sanitize_text_field( (string) ( $input['size_label'] ?? '' ) );

		if ( '' === $size ) {
			if ( 'ea' === $unit ) {
				$size = '1 ea (' . $weight_grams . ' g)';
			} else {
				$size = $weight_grams . ' ' . $unit;
			}
		}

		$is_recipe = ( 'recipe' === strtolower( $food_type ) ) ? 1 : 0;

		$data = array(
			'name'                => $name,
			'name_normalized'     => RC_Name_Matcher::normalize( $name ),
			'food_type'           => $food_type,
			'area'                => sanitize_text_field( (string) ( $input['area'] ?? '' ) ),
			'size_label'          => $size,
			'weight_grams'        => $weight_grams,
			'calories'            => $input['calories'] ?? null,
			'fat_grams'           => $input['fat_grams'] ?? null,
			'saturated_fat_grams' => $input['saturated_fat_grams'] ?? null,
			'trans_fat_grams'     => $input['trans_fat_grams'] ?? null,
			'carb_grams'          => $input['carb_grams'] ?? null,
			'fiber_grams'         => $input['fiber_grams'] ?? null,
			'sugar_grams'         => $input['sugar_grams'] ?? null,
			'cholesterol_mg'      => $input['cholesterol_mg'] ?? null,
			'protein_grams'       => $input['protein_grams'] ?? null,
			'sodium_mg'           => $input['sodium_mg'] ?? null,
			'vit_a'               => $input['vit_a'] ?? null,
			'vit_c_mg'            => $input['vit_c_mg'] ?? null,
			'potassium_mg'        => $input['potassium_mg'] ?? null,
			'calcium_mg'          => $input['calcium_mg'] ?? null,
			'iron_mg'             => $input['iron_mg'] ?? null,
			'ingredients_text'    => $input['ingredients_text'] ?? null,
			'supplier'            => $supplier,
			'food_cost'           => $food_cost,
			'min_level'           => (int) ( $input['min_level'] ?? 0 ),
			'package_size'        => $input['package_size'] ?? '',
			'package_cost'        => $input['package_cost'] ?? null,
			'is_recipe'           => $is_recipe,
		);

		$result = self::upsert( $data, $id );
		
		// When updating, $result['updated'] may be false if no data changed, 
		// but the operation was still successful.
		if ( ! $result['created'] && ! $result['updated'] && null === $id ) {
			return array(
				'success' => false,
				'message' => __( 'Could not save ingredient.', 'restaurant-costing' ),
			);
		}

		return array(
			'success'      => true,
			'id'           => $result['id'],
			'name'         => $name,
			'food_type'    => $food_type,
			'unit_hint'    => $unit,
			'food_cost'    => $food_cost,
			'weight_grams' => $weight_grams,
			'size_label'   => $size,
			'supplier'     => $supplier,
			'message'      => null !== $id ? __( 'Ingredient updated.', 'restaurant-costing' ) : __( 'Ingredient added.', 'restaurant-costing' ),
		);
	}

	/**
	 * Line cost for a recipe quantity.
	 *
	 * @param string $unit  'g', 'ml', or 'ea'.
	 */
	public static function line_cost( int $ingredient_id, float $quantity, float $portions, string $unit = 'g' ): float {
		global $wpdb;
		$table = RC_Database::table( 'ingredients' );
		$row   = $wpdb->get_row(
			$wpdb->prepare( "SELECT food_cost, weight_grams FROM {$table} WHERE id = %d", $ingredient_id ),
			ARRAY_A
		);

		if ( ! $row || (float) $row['weight_grams'] <= 0 || $portions <= 0 ) {
			return 0.0;
		}

		if ( 'ea' === $unit ) {
			return ( (float) $row['food_cost'] * $quantity ) / $portions;
		}

		$unit_cost = (float) $row['food_cost'] / (float) $row['weight_grams'];
		return $unit_cost * $quantity / $portions;
	}

	/**
	 * @param array<string, mixed> $data Ingredient fields.
	 * @param int|null             $id   Existing ID for update.
	 * @return array{created:bool, updated:bool, id:int}
	 */
	public static function upsert( array $data, ?int $id = null ): array {
		global $wpdb;
		$table = RC_Database::table( 'ingredients' );

		$row = array(
			'name'                => $data['name'],
			'name_normalized'     => $data['name_normalized'],
			'food_type'           => $data['food_type'],
			'area'                => $data['area'] ?? '',
			'size_label'          => $data['size_label'] ?? '',
			'weight_grams'        => $data['weight_grams'],
			'calories'            => $data['calories'] ?? null,
			'fat_grams'           => $data['fat_grams'] ?? null,
			'saturated_fat_grams' => $data['saturated_fat_grams'] ?? null,
			'trans_fat_grams'     => $data['trans_fat_grams'] ?? null,
			'carb_grams'          => $data['carb_grams'] ?? null,
			'fiber_grams'         => $data['fiber_grams'] ?? null,
			'sugar_grams'         => $data['sugar_grams'] ?? null,
			'cholesterol_mg'      => $data['cholesterol_mg'] ?? null,
			'protein_grams'       => $data['protein_grams'] ?? null,
			'sodium_mg'           => $data['sodium_mg'] ?? null,
			'vit_a'               => $data['vit_a'] ?? null,
			'vit_c_mg'            => $data['vit_c_mg'] ?? null,
			'potassium_mg'        => $data['potassium_mg'] ?? null,
			'calcium_mg'          => $data['calcium_mg'] ?? null,
			'iron_mg'             => $data['iron_mg'] ?? null,
			'ingredients_text'    => $data['ingredients_text'] ?? null,
			'supplier'            => $data['supplier'] ?? '',
			'food_cost'           => $data['food_cost'] ?? 0,
			'min_level'           => (int) ( $data['min_level'] ?? 0 ),
			'package_size'        => $data['package_size'] ?? '',
			'package_cost'        => $data['package_cost'] ?? null,
		);

		// Only set is_recipe when caller explicitly provides it
		$formats = array(
			'%s', '%s', '%s', '%s', '%s', '%f',
			'%f', '%f', '%f', '%f', '%f', '%f', '%f', '%f', '%f', '%f',
			'%f', '%f', '%f', '%f', '%f', '%s',
			'%s', '%f', '%d', '%s', '%f',
		);
		$has_is_recipe = array_key_exists( 'is_recipe', $data );
		if ( $has_is_recipe ) {
			$row['is_recipe'] = (int) $data['is_recipe'];
			$formats[] = '%d';
		}

		if ( $id && $id > 0 ) {
			$wpdb->update( $table, $row, array( 'id' => $id ), $formats, array( '%d' ) );
			RC_Exception::throw_on_db_error( 'update', $table );
			return array(
				'created' => false,
				'updated' => true,
				'id'      => $id,
			);
		}

		$wpdb->insert( $table, $row, $formats );
		RC_Exception::throw_on_db_error( 'insert', $table );
		return array(
			'created' => (bool) $wpdb->insert_id,
			'updated' => false,
			'id'      => (int) $wpdb->insert_id,
		);
	}

	/**
	 * Delete an ingredient by ID.
	 *
	 * @param int $id Ingredient ID.
	 * @return bool True on success, false on failure.
	 */
	public static function delete( int $id ): bool {
		global $wpdb;
		$table = RC_Database::table( 'ingredients' );
		$result = $wpdb->delete( $table, array( 'id' => $id ), array( '%d' ) );
		RC_Exception::throw_on_db_error( 'delete', $table );
		return (bool) $result;
	}

	/**
	 * Toggle is_recipe flag by ingredient name.
	 *
	 * @param string $name   Ingredient name.
	 * @param int    $status 1 or 0.
	 */
	public static function update_is_recipe_status( string $name, int $status ): void {
		global $wpdb;
		$table      = RC_Database::table( 'ingredients' );
		$normalized = RC_Name_Matcher::normalize( $name );
		$wpdb->update(
			$table,
			array( 'is_recipe' => $status === 1 ? 1 : 0 ),
			array( 'name_normalized' => $normalized ),
			array( '%d' ),
			array( '%s' )
		);
		RC_Exception::throw_on_db_error( 'update', $table );
	}

	/**
	 * Export ingredients as CSV.
	 */
	public static function export_csv( bool $include_sub_recipes = false ): void {
		global $wpdb;
		$table = RC_Database::table( 'ingredients' );
		$where = $include_sub_recipes ? '' : " WHERE (is_recipe IS NULL OR is_recipe = 0)";
		$rows  = $wpdb->get_results( "SELECT * FROM {$table}{$where} ORDER BY name ASC", ARRAY_A );

		$filename = 'foodmaster-export-' . gmdate( 'Y-m-d' ) . '.csv';
		header( 'Content-Type: text/csv; charset=utf-8' );
		header( 'Content-Disposition: attachment; filename=' . $filename );

		$out = fopen( 'php://output', 'wb' );
		fputcsv(
			$out,
			array(
				'Food', 'FoodType', 'Size', 'WeightGrams', 'Calories', 'FatGrams', 'SaturatedFatGrams', 'Trans Fat', 'CarbGrams', 'FiberGrams', 'Sugar', 'Cholestrol', 'ProteinGrams', 'Sodium', 'VitA (RE)(iu_div_10pt3)', 'VitC (mg)', 'Potassium (mg)', 'Calcium', 'Iron', 'Ingredients', 'Supplier', 'FoodCost', 'Package Size', 'Cost',
			)
		);

		foreach ( $rows ?: array() as $row ) {
			fputcsv(
				$out,
				array(
					$row['name'], $row['food_type'], $row['size_label'], $row['weight_grams'], $row['calories'], $row['fat_grams'], $row['saturated_fat_grams'], $row['trans_fat_grams'], $row['carb_grams'], $row['fiber_grams'], $row['sugar_grams'], $row['cholesterol_mg'], $row['protein_grams'], $row['sodium_mg'], $row['vit_a'], $row['vit_c_mg'], $row['potassium_mg'], $row['calcium_mg'], $row['iron_mg'], $row['ingredients_text'], $row['supplier'], $row['food_cost'], $row['package_size'], $row['package_cost'],
				)
			);
		}

		fclose( $out );
		exit;
	}
}
