<?php

declare(strict_types=1);

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

final class RC_Inventory_Repository {

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

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

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

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

		$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 );
	}

	public static function list( int $limit = 50, int $offset = 0, array $filters = array(), string $orderby = 'created_at', string $order = 'DESC' ): array {
		global $wpdb;
		$table = RC_Database::table( 'inventories' );

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

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

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

		$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();
	}

	public static function create( string $name, array $items ): int {
		global $wpdb;
		$table      = RC_Database::table( 'inventories' );
		$items_table = RC_Database::table( 'inventory_items' );

		$wpdb->insert( $table, array( 'name' => $name ), array( '%s' ) );
		RC_Exception::throw_on_db_error( 'insert', $table );
		$inventory_id = (int) $wpdb->insert_id;

		foreach ( $items as $item ) {
			$ingredient_name = sanitize_text_field( (string) ( $item['ingredient_name'] ?? '' ) );
			if ( '' === $ingredient_name ) {
				continue;
			}
			$wpdb->insert(
				$items_table,
				array(
					'inventory_id'    => $inventory_id,
					'ingredient_name' => $ingredient_name,
					'supplier_name'   => sanitize_text_field( (string) ( $item['supplier_name'] ?? '' ) ),
					'min_level'       => (int) ( $item['min_level'] ?? 0 ),
					'order_qty'       => (float) ( $item['order_qty'] ?? 0 ),
				),
				array( '%d', '%s', '%s', '%d', '%f' )
			);
			RC_Exception::throw_on_db_error( 'insert', $items_table );
		}

		return $inventory_id;
	}

	public static function update( int $id, string $name, array $items ): void {
		global $wpdb;
		$table       = RC_Database::table( 'inventories' );
		$items_table = RC_Database::table( 'inventory_items' );

		$wpdb->update( $table, array( 'name' => $name ), array( 'id' => $id ), array( '%s' ), array( '%d' ) );
		RC_Exception::throw_on_db_error( 'update', $table );
		$wpdb->delete( $items_table, array( 'inventory_id' => $id ), array( '%d' ) );
		RC_Exception::throw_on_db_error( 'delete', $items_table );

		foreach ( $items as $item ) {
			$ingredient_name = sanitize_text_field( (string) ( $item['ingredient_name'] ?? '' ) );
			if ( '' === $ingredient_name ) {
				continue;
			}
			$wpdb->insert(
				$items_table,
				array(
					'inventory_id'    => $id,
					'ingredient_name' => $ingredient_name,
					'supplier_name'   => sanitize_text_field( (string) ( $item['supplier_name'] ?? '' ) ),
					'min_level'       => (int) ( $item['min_level'] ?? 0 ),
					'order_qty'       => (float) ( $item['order_qty'] ?? 0 ),
				),
				array( '%d', '%s', '%s', '%d', '%f' )
			);
			RC_Exception::throw_on_db_error( 'insert', $items_table );
		}
	}

	public static function delete( int $id ): bool {
		global $wpdb;
		$items_table = RC_Database::table( 'inventory_items' );
		$table       = RC_Database::table( 'inventories' );

		$wpdb->delete( $items_table, array( 'inventory_id' => $id ), array( '%d' ) );
		RC_Exception::throw_on_db_error( 'delete', $items_table );
		$result = $wpdb->delete( $table, array( 'id' => $id ), array( '%d' ) );
		RC_Exception::throw_on_db_error( 'delete', $table );
		return (bool) $result;
	}

	public static function mark_emailed( int $inventory_id, string $supplier_name ): void {
		global $wpdb;
		$table = RC_Database::table( 'inventory_items' );
		$wpdb->query(
			$wpdb->prepare(
				"UPDATE {$table} SET emailed = 1 WHERE inventory_id = %d AND supplier_name = %s AND order_qty > 0",
				$inventory_id,
				$supplier_name
			)
		);
		RC_Exception::throw_on_db_error( 'update', $table );
	}

	public static function get_supplier_items( int $inventory_id, string $supplier_name ): array {
		global $wpdb;
		$table = RC_Database::table( 'inventory_items' );
		return $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM {$table} WHERE inventory_id = %d AND supplier_name = %s AND order_qty > 0 ORDER BY ingredient_name ASC",
				$inventory_id,
				$supplier_name
			)
		) ?: array();
	}

	public static function get_unemailed_items( int $inventory_id ): array {
		global $wpdb;
		$table = RC_Database::table( 'inventory_items' );
		return $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM {$table} WHERE inventory_id = %d AND order_qty > 0 AND emailed = 0 ORDER BY supplier_name ASC, ingredient_name ASC",
				$inventory_id
			)
		) ?: array();
	}
}
