<?php

namespace PixelioCartAbandonRecoveryMore\Frontend\Repositories;


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

use Exception;
use PixelioCartAbandonRecoveryMore\Admin\Wrapper\Pixelio_Cart_Abandon_Recovery_More_Wordpress_Wrapper;
use PixelioCartAbandonRecoveryMore\Includes\Utils\Pixelio_Cart_Abandon_Recovery_More_Util;

class Pixelio_Cart_Abandon_Recovery_More_Cart_Repository
{
	private $wpdb;
	private string $table;
	private Pixelio_Cart_Abandon_Recovery_More_Wordpress_Wrapper $pixelio_wordpress_wrapper;

	public function __construct($pixelio_wordpress_wrapper)
	{
		$this->pixelio_wordpress_wrapper = $pixelio_wordpress_wrapper;
		$this->wpdb = $pixelio_wordpress_wrapper->pixelio_get_wpdb();
		$this->table = $this->wpdb->prefix . Pixelio_Cart_Abandon_Recovery_More_Util::$pixelio_plugin_db_prefix . 'cart';
	}

	public function pixelio_find_by_uuid(string $uuid): ?int
	{
		$id = $this->wpdb->get_var(
			$this->wpdb->prepare(
				"SELECT id FROM {$this->table} WHERE uuid = %s LIMIT 1",
				$uuid
			)
		);

		return $id ? (int)$id : null;
	}

	public function pixelio_find_by_order_id(string $order_id): ?int
	{
		$id = $this->wpdb->get_var(
			$this->wpdb->prepare(
				"SELECT id FROM {$this->table} WHERE woo_order_id = %s LIMIT 1",
				$order_id
			)
		);

		return $id ? (int)$id : null;
	}

	public function pixelio_create_new_cart(string $uuid): int
	{
		$this->wpdb->insert(
			$this->table,
			[
				'uuid' => $uuid,
				'created_at' => $this->pixelio_wordpress_wrapper->pixelio_current_time(),
			],
			['%s', '%f', '%s']
		);

		return (int)$this->wpdb->insert_id;
	}

	public function pixelio_update_total_of_cart(int $cartId, float $total): bool
	{
		return $this->wpdb->update(
				$this->table,
				['total' => $total],
				['id' => $cartId],
				['%f'],
				['%d']
			) !== false;
	}

	public function pixelio_update_status(int $cartId, int $status, ?int $orderId = null): bool
	{
		$data = [
			'cart_status_id' => $status
		];

		if ($orderId) {
			$data['order_id'] = $orderId;
		}

		return $this->wpdb->update(
				$this->table,
				$data,
				['id' => $cartId],
				null,
				['%d']
			) !== false;
	}

	public function pixelio_mark_removed($cartId): bool
	{
		return $this->wpdb->update(
				$this->table,
				['is_removed' => 1],
				['id' => $cartId],
				['%d'],
				['%d']
			) !== false;
	}

	public function pixelio_find_expired_cart(string $date): array
	{
		return $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT * FROM {$this->table} WHERE created_at < %s",
				$date
			)
		);
	}

	public function pixelio_delete_expired_cart_by_date(string $date): bool
	{
		return $this->wpdb->query(
				$this->wpdb->prepare(
					"DELETE FROM {$this->table} WHERE created_at < %s",
					$date
				)
			) !== false;
	}

	public function pixelio_cart_abandon_recovery_more_update_cart_by_id($cart_id, $woo_order_id, $status, $template_id = 0): void
	{


		if (!$cart_id) {
			return;
		}



		$table = $this->wpdb->prefix . esc_sql(Pixelio_Cart_Abandon_Recovery_More_Util::$pixelio_plugin_db_prefix) . 'cart';

		if ($template_id !== 0) {
			$results = $this->wpdb->get_results($this->wpdb->prepare("SELECT * FROM %i WHERE id = %d AND converted_template_id = %d", $table, $cart_id, $template_id));
		} else {
			$results = $this->wpdb->get_results($this->wpdb->prepare("SELECT * FROM %i WHERE id = %d", $table, $cart_id));

		}

		$this->pixelio_cart_abandon_recovery_more_change_status($results, $woo_order_id, $status);
	}

	/**
	 * Change the status of a cart.
	 *
	 * @param array|object|null $results Carts to mark.
	 * @param mixed $woo_order_id WooCommerce order ID.
	 */
	public function pixelio_cart_abandon_recovery_more_change_status(array|object|null $results, mixed $woo_order_id, $status): void
	{

		if (empty($results)) {
			return;
		}

		foreach ($results as $cart) {
			if (
				$cart->woo_order_id == $woo_order_id &&
				$cart->cart_status_id == $status
			) {
				continue;
			}
			$cart_id = $cart->id;

			$recovered = ($cart->cart_status_id === 5 || $cart->cart_status_id === 13 || $cart->cart_status_id === 20 || $cart->cart_status_id === 21)
				? 1
				: $cart->recovered;

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery -- safe: must fetch live data, caching not used.
			$this->wpdb->update(
				$this->table,
				[
					'woo_order_id'   => $woo_order_id,
					'synced'         => 0,
					'cart_status_id' => $status,
					'recovered'      => $recovered,
				],
				['id' => $cart_id],
				['%d', '%d', '%d', '%d'],
				['%d']
			);
		}
	}

	public function pixelio_cart_abandon_recovery_more_get_cart_by_status($status)
	{
		return $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT * FROM %i WHERE cart_status_id = %s AND is_removed = %d",
				$this->table, $status, 0
			)
		);
	}

	public function get_status(int $cart_id): ?int
	{
		if (!$cart_id) {
			return false;
		}

		return (int)$this->wpdb->get_var(
			$this->wpdb->prepare(
				"SELECT cart_status_id FROM {$this->table} WHERE id = %d",
				$cart_id
			)
		);
	}

	public function update_cart_status(array $statusData): void
	{
		if (empty($statusData)) {
			return;
		}

		foreach ($statusData as $item) {

			$cartLocalId = (int)($item['cart_id'] ?? 0);
			$emailId = (int)($item['email_id'] ?? 0);
			$consent = (int)($item['consent'] ?? 0);

			if (!$cartLocalId) {
				continue;
			}

			$cart = $this->find_by_local_id($cartLocalId);

			if (!$cart) {
				continue;
			}

			$newStatus = $this->resolve_status((int)$cart->status, $consent);

			$this->update_status(
				(int)$cart->id,
				$newStatus,
				$emailId
			);
		}
	}

	private function find_by_local_id(int $localId)
	{
		return $this->wpdb->get_row(
			$this->wpdb->prepare(
				"SELECT id, status FROM {$this->table} WHERE local_id = %d",
				$localId
			)
		);
	}

	private function resolve_status(int $currentStatus, int $consent): int
	{
		if ($consent === 1) {
			return match ($currentStatus) {
				5 => 14,
				14 => 15,
				15 => 16,
				default => $currentStatus
			};
		}

		if ($currentStatus === 5) {
			return 23;
		}

		return $currentStatus;
	}

	public function update_status(int $cart_id, $woo_order_id, int $status_id)
	{
		return $this->wpdb->update(
			$this->table,
			[
				'woo_order_id' => $woo_order_id,
				'synced' => 0,
				'cart_status_id' => $status_id,
			],
			['id' => $cart_id],
			['%d', '%d', '%d'],
			['%d']
		);
	}

	public function pixelio_get_not_yet_synced(): array
	{
		return $this->wpdb->get_results(
			"SELECT * FROM {$this->table} WHERE synced = 0"
		);
	}

	public function pixelio_mark_synced(array $carts): bool
	{
		foreach ($carts as $cart) {

			if (!isset($cart->id)) {
				continue;
			}

			$id = (int)$cart->id;

			$result = $this->wpdb->update(
				$this->table,
				['synced' => 1],
				['id' => $id],
				['%d'],
				['%d']
			);

			if ($result === false) {
				return false;
			}
		}

		return true;
	}

	public function pixelio_cart_abandon_recovery_more_update_if_exist($id, $token): bool
	{
		$updated = $this->wpdb->update(
			$this->table,
			[
				'token_for_recover' => $this->pixelio_wordpress_wrapper->pixelio_esc_sql($token),
			],
			[
				'id' => $this->pixelio_wordpress_wrapper->pixelio_esc_sql($id),
			],
			[
				'%s',
			],
			[
				'%d',
			]
		);

		return $updated !== false;
	}

	public function pixelio_find_cart_by_token(string $token): array
	{
		return $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT * FROM {$this->table} WHERE token_for_recover = %d AND is_removed = 0",
				$token
			)
		);
	}

	public function pixelio_mark_cart_as_abandoned_carts()
	{
		try {

			// Récupérer le délai configuré ou mettre 60 minutes par défaut
			$setting = $this->pixelio_wordpress_wrapper->pixelio_get_option('pixelio-cart-abandon-recovery-more-abandon-cart-timing');
			$time_to_abandon = $setting ? intval($setting) : 60;
			if(defined('WP_PIXELIO_TEST')){
				$time_to_abandon = 1;
			}

			$threshold = gmdate(
				'Y-m-d H:i:s',
				time() - ($time_to_abandon * 60)
			);

			// Marque les paniers comme abandonnés si status = 2 et délai écoulé
			$updated = $this->wpdb->query($this->wpdb->prepare(
				"UPDATE {$this->table} c
	 SET c.cart_status_id = 5,
	     c.synced = 0
	 WHERE c.cart_status_id = 2
	 AND (
	    (c.updated_at IS NOT NULL AND c.updated_at < %s)
	    OR (c.updated_at IS NULL AND c.created_at < %s)
	 )
	 AND EXISTS (
	    SELECT 1
	    FROM {$this->table}_item i
	    WHERE i.cart_id = c.id and i.is_removed = 0
	    LIMIT 1
	 )",
				$threshold,
				$threshold
			));
			return $updated;
		} catch (Exception $e) {

		}
		return null;
	}

	public function pixelio_mark_cart_as_fully_abandoned_carts()
	{
		try {


			$time_to_fully_abandon_minutes = 60 * 24 * 3; // 4320 minutes

			$threshold = gmdate(
				'Y-m-d H:i:s',
				time() - ($time_to_fully_abandon_minutes * 60)
			);

			$updated = $this->wpdb->query($this->wpdb->prepare(
				"UPDATE {$this->table}
             SET cart_status_id = 17, synced = 0
             WHERE cart_status_id = 5
             AND created_at < %s",
				$threshold
			));

			return $updated;

		} catch (Exception $e) {
			error_log('Error marking fully abandoned carts: ' . $e->getMessage());
		}

		return null;
	}


}
