<?php
/**
 * Recipe CRUD and related lines/labour.
 *
 * @package RestaurantCosting
 */

declare(strict_types=1);

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

final class RC_Recipe_Repository {

	public static function count( string $search = '', bool $show_inactive = false, string $is_ingredient_filter = '', int $menu_type_id = 0 ): int {
		global $wpdb;
		$table = RC_Database::table( 'recipes' );
		$where = '';
		$args  = array();

		if ( ! $show_inactive ) {
			$where .= ' WHERE is_active = 1';
		}

		if ( '' !== $search ) {
			$like = '%' . $wpdb->esc_like( RC_Name_Matcher::normalize( $search ) ) . '%';
			$where .= ( $where ? ' AND' : ' WHERE' ) . ' name_normalized LIKE %s';
			$args[] = $like;
		}

		if ( 'yes' === $is_ingredient_filter ) {
			$where .= ( $where ? ' AND' : ' WHERE' ) . ' is_ingredient = 1';
		} elseif ( 'no' === $is_ingredient_filter ) {
			$where .= ( $where ? ' AND' : ' WHERE' ) . ' is_ingredient = 0';
		} elseif ( 'none' === $is_ingredient_filter ) {
			$where .= ( $where ? ' AND' : ' WHERE' ) . ' 1 = 0';
		}

		if ( $menu_type_id > 0 ) {
			$where .= ( $where ? ' AND' : ' WHERE' ) . ' menu_type_id = %d';
			$args[] = $menu_type_id;
		}

		if ( empty( $args ) ) {
			return (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table}{$where}" );
		}
		return (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$table}{$where}", ...$args ) );
	}

	public static function list( int $limit = 50, int $offset = 0, string $search = '', string $orderby = 'name', string $order = 'ASC', bool $show_inactive = false, string $is_ingredient_filter = '', int $menu_type_id = 0 ): array {
		global $wpdb;
		$table = RC_Database::table( 'recipes' );

		$allowed_cols = array( 'name', 'portions', 'menu_price', 'created_at', 'updated_at', 'is_ingredient' );
		$orderby = in_array( $orderby, $allowed_cols, true ) ? $orderby : 'name';
		$order   = ( strtoupper( $order ) === 'DESC' ) ? 'DESC' : 'ASC';

		$where = '';
		$args  = array();

		if ( ! $show_inactive ) {
			$where .= ' WHERE is_active = 1';
		}

		if ( '' !== $search ) {
			$like = '%' . $wpdb->esc_like( RC_Name_Matcher::normalize( $search ) ) . '%';
			$where .= ( $where ? ' AND' : ' WHERE' ) . ' name_normalized LIKE %s';
			$args[] = $like;
		}

		if ( 'yes' === $is_ingredient_filter ) {
			$where .= ( $where ? ' AND' : ' WHERE' ) . ' is_ingredient = 1';
		} elseif ( 'no' === $is_ingredient_filter ) {
			$where .= ( $where ? ' AND' : ' WHERE' ) . ' is_ingredient = 0';
		} elseif ( 'none' === $is_ingredient_filter ) {
			$where .= ( $where ? ' AND' : ' WHERE' ) . ' 1 = 0';
		}

		if ( $menu_type_id > 0 ) {
			$where .= ( $where ? ' AND' : ' WHERE' ) . ' menu_type_id = %d';
			$args[] = $menu_type_id;
		}

		$args[] = $limit;
		$args[] = $offset;

		return $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$table}{$where} ORDER BY {$orderby} {$order} LIMIT %d OFFSET %d", ...$args ) ) ?: array();
	}

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

	public static function get_full( int $id ): ?array {
		$recipe = self::get( $id );
		if ( ! $recipe ) return null;
		$lines  = self::get_lines( $id );
		$labor  = self::get_labor( $id );
		$totals = RC_Recipe_Calculator::calculate( $recipe, $lines, $labor );
		return array( 'recipe' => $recipe, 'lines' => $lines, 'labor' => $labor, 'totals' => $totals );
	}

	public static function get_lines( int $recipe_id ): array {
		global $wpdb;
		$table = RC_Database::table( 'recipe_lines' );
		return $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$table} WHERE recipe_id = %d ORDER BY sort_order ASC, id ASC", $recipe_id ) ) ?: array();
	}

	public static function get_labor( int $recipe_id ): array {
		global $wpdb;
		$table = RC_Database::table( 'recipe_labor' );
		return $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$table} WHERE recipe_id = %d ORDER BY sort_order ASC, id ASC", $recipe_id ) ) ?: array();
	}

	public static function check_duplicate_name( string $name, int $exclude_id = 0 ): bool {
		global $wpdb;
		$table      = RC_Database::table( 'recipes' );
		$normalized = RC_Name_Matcher::normalize( $name );
		$sql        = "SELECT id FROM {$table} WHERE name_normalized = %s";
		$params     = array( $normalized );
		if ( $exclude_id > 0 ) {
			$sql      .= " AND id != %d";
			$params[] = $exclude_id;
		}
		$sql   .= " LIMIT 1";
		$exists = $wpdb->get_var( $wpdb->prepare( $sql, $params ) );
		return (bool) $exists;
	}

	public static function save( array $data ): int {
		global $wpdb;
		$table   = RC_Database::table( 'recipes' );
		$id      = isset( $data['id'] ) ? (int) $data['id'] : 0;
		$name    = sanitize_text_field( (string) ( $data['name'] ?? '' ) );
		$normalized = RC_Name_Matcher::normalize( $name );

		// Scenario A: Keep track of the old name to disable is_recipe if renamed
		$old_name = '';
		if ( $id > 0 ) {
			$old_recipe = self::get( $id );
			if ( $old_recipe ) $old_name = $old_recipe->name;
		}

		$row = array(
			'name'            => $name,
			'name_normalized' => $normalized,
			'portions'        => max( 0.0001, (float) ( $data['portions'] ?? 1 ) ),
			'scale_factor'    => max( 0.0001, (float) ( $data['scale_factor'] ?? 1 ) ),
			'menu_price'      => '' === (string) ( $data['menu_price'] ?? '' ) ? null : (float) $data['menu_price'],
			'labor_rate'      => (float) ( $data['labor_rate'] ?? 25 ),
			'method'          => sanitize_textarea_field( (string) ( $data['method'] ?? '' ) ),
			'image_url'       => sanitize_text_field( (string) ( $data['image_url'] ?? '' ) ),
			'print_image'     => ! empty( $data['print_image'] ) ? 1 : 0,
			'is_ingredient'   => ! empty( $data['is_ingredient'] ) ? 1 : 0,
			'is_active'       => ! empty( $data['is_active'] ) ? 1 : 0,
			'menu_type_id'    => ! empty( $data['menu_type_id'] ) ? (int) $data['menu_type_id'] : null,
		);

		$formats = array( '%s', '%s', '%f', '%f', '%f', '%f', '%s', '%s', '%d', '%d', '%d', '%d' );
		if ( $id > 0 ) {
			$wpdb->update( $table, $row, array( 'id' => $id ), $formats, array( '%d' ) );
			RC_Exception::throw_on_db_error( 'update', $table );
		} else {
			$wpdb->insert( $table, $row, $formats );
			RC_Exception::throw_on_db_error( 'insert', $table );
			$id = (int) $wpdb->insert_id;
		}

		self::save_lines( $id, $data['lines'] ?? array() );
		self::save_labor( $id, $data['labor'] ?? array() );

		// --- SUB-RECIPE MIRRORING LOGIC ---
		$is_ingredient = isset( $data['is_ingredient'] ) ? (int) $data['is_ingredient'] : 0;

		// 1. Handle the "Old Name" cleanup (Scenario A)
		if ( $old_name && $old_name !== $name ) {
			RC_Ingredient_Repository::update_is_recipe_status( $old_name, 0 );
		}

		if ( $is_ingredient ) {
			// Get final totals for this recipe
			$full = self::get_full( $id );
			$totals = $full['totals'];
			$lines  = $full['lines'];

			// Calculate Total Batch Weight
			$total_weight = 0.0;
			foreach ( $lines as $line ) {
				$qty = (float) $line->quantity;
				if ( 'ea' === $line->unit && ! empty( $line->ingredient_id ) ) {
					$ing = RC_Ingredient_Repository::get( (int) $line->ingredient_id );
					if ( $ing ) {
						$qty *= (float) $ing->weight_grams;
					}
				}
				$total_weight += $qty;
			}

			// Calculate Total Batch Cost (per-portion × portions = total batch ingredient cost)
			$batch_cost = (float) $totals['ingredient_cost'] * (float) $full['recipe']->portions;

			// Mirror to Ingredients Table
			$ingredient_id = RC_Name_Matcher::find_ingredient_id( $name );
			$result = RC_Ingredient_Repository::upsert( array(
				'name'             => $name,
				'name_normalized'  => $normalized,
				'food_type'        => 'Recipe',
				'size_label'       => '1 Batch',
				'weight_grams'     => $total_weight,
				'food_cost'        => $batch_cost,
				'is_recipe'        => 1,
				'supplier'        => 'In-house',
			), $ingredient_id );

			// Link the recipe to the mirrored ingredient
			$mirror_id = $result['id'];
			if ( $mirror_id > 0 ) {
				$wpdb->update( $table, array( 'ingredient_id' => $mirror_id ), array( 'id' => $id ), array( '%d' ), array( '%d' ) );
			}
		} else {
			// Turn off the flag if the toggle was unchecked; clear the FK link
			RC_Ingredient_Repository::update_is_recipe_status( $name, 0 );
			$wpdb->update( $table, array( 'ingredient_id' => null ), array( 'id' => $id ), array( '%d' ), array( '%d' ) );
		}

		return $id;
	}

	private static function save_lines( int $recipe_id, array $lines ): void {
		global $wpdb;
		$table = RC_Database::table( 'recipe_lines' );
		$wpdb->delete( $table, array( 'recipe_id' => $recipe_id ), array( '%d' ) );
		RC_Exception::throw_on_db_error( 'delete', $table );
		$order = 0;
		foreach ( $lines as $line ) {
			$name = sanitize_text_field( (string) ( $line['ingredient_name'] ?? '' ) );
			if ( '' === $name ) continue;
			$ingredient_id = ! empty( $line['ingredient_id'] ) ? (int) $line['ingredient_id'] : RC_Name_Matcher::find_ingredient_id( $name );
			$density = isset( $line['density_g_per_ml'] ) && '' !== (string) $line['density_g_per_ml'] ? (float) $line['density_g_per_ml'] : null;
			$unit = in_array( $line['unit'] ?? 'g', array( 'g', 'ml', 'ea' ), true ) ? $line['unit'] : 'g';
			$wpdb->insert( $table, array( 'recipe_id' => $recipe_id, 'ingredient_id' => $ingredient_id, 'ingredient_name' => $name, 'sort_order' => $order++, 'quantity' => (float) ( $line['quantity'] ?? 0 ), 'unit' => $unit, 'density_g_per_ml' => $density, 'method_step' => '' === (string) ( $line['method_step'] ?? '' ) ? null : (int) $line['method_step'], 'method_text' => sanitize_text_field( (string) $line['method_text'] ?? '' ) ), array( '%d', '%d', '%s', '%d', '%f', '%s', '%f', '%d', '%s' ) );
			RC_Exception::throw_on_db_error( 'insert', $table );
		}
	}

	private static function save_labor( int $recipe_id, array $labor_rows ): void {
		global $wpdb;
		$table = RC_Database::table( 'recipe_labor' );
		$wpdb->delete( $table, array( 'recipe_id' => $recipe_id ), array( '%d' ) );
		RC_Exception::throw_on_db_error( 'delete', $table );
		$order = 0;
		foreach ( $labor_rows as $lab ) {
			$minutes = (float) ( $lab['minutes'] ?? 0 );
			if ( $minutes <= 0 && '' === trim( (string) ( $lab['item_label'] ?? '' ) ) ) continue;
			$wpdb->insert( $table, array( 'recipe_id' => $recipe_id, 'item_label' => sanitize_text_field( (string) ( $lab['item_label'] ?? '' ) ), 'minutes' => $minutes, 'quantity' => 1, 'sort_order' => $order++ ), array( '%d', '%s', '%f', '%f', '%d' ) );
			RC_Exception::throw_on_db_error( 'insert', $table );
		}
	}

	public static function delete( int $id ): bool {
		global $wpdb;
		$recipe = self::get( $id );
		if ( $recipe ) {
			// Requirement: Set is_recipe to 0 but do NOT delete the ingredient
			RC_Ingredient_Repository::update_is_recipe_status( $recipe->name, 0 );
		}
		$wpdb->delete( RC_Database::table( 'recipe_lines' ), array( 'recipe_id' => $id ), array( '%d' ) );
		RC_Exception::throw_on_db_error( 'delete', RC_Database::table( 'recipe_lines' ) );
		$wpdb->delete( RC_Database::table( 'recipe_labor' ), array( 'recipe_id' => $id ), array( '%d' ) );
		RC_Exception::throw_on_db_error( 'delete', RC_Database::table( 'recipe_labor' ) );
		$deleted = $wpdb->delete( RC_Database::table( 'recipes' ), array( 'id' => $id ), array( '%d' ) );
		RC_Exception::throw_on_db_error( 'delete', RC_Database::table( 'recipes' ) );
		return (bool) $deleted;
	}

	public static function scale_to_portions( int $recipe_id, float $target_portions ): void {
		$recipe = self::get( $recipe_id );
		if ( ! $recipe || (float) $recipe->portions <= 0 ) return;
		$factor = $target_portions / (float) $recipe->portions;
		global $wpdb;
		$table = RC_Database::table( 'recipes' );
		$wpdb->update( $table, array( 'scale_factor' => round( $factor, 4 ) ), array( 'id' => $recipe_id ), array( '%f' ), array( '%d' ) );
		RC_Exception::throw_on_db_error( 'update', $table );
	}
}
