<?php
namespace Pay4All\Pro\Domain;

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

/**
 * Domain helpers for the abandoned-carts table.
 *
 * Schema (see Activator::install_abandoned_carts_table) — one row per
 * (form_id, email). When a customer completes a form up to the validation
 * step but doesn't actually submit, we stash their cart here and email
 * them a recovery link N days later if they haven't come back.
 */
final class AbandonedCart {

	public static function table() : string {
		global $wpdb;
		return $wpdb->prefix . 'p4all_abandoned_carts';
	}

	/**
	 * Inserts a fresh row OR updates the existing one for the same
	 * (form_id, email) couple. Returns the row id.
	 *
	 * Resets email_sent_at to NULL on every save so that if the customer
	 * comes back, edits their cart and abandons again, they get a fresh
	 * recovery email N days after the new save (not the original one).
	 */
	public static function save( array $data ) : int {
		global $wpdb;
		$table = self::table();
		$form_id = (int) ( $data['form_id'] ?? 0 );
		$email   = strtolower( trim( (string) ( $data['email'] ?? '' ) ) );
		if ( $form_id <= 0 || $email === '' ) { return 0; }

		$now = gmdate( 'Y-m-d H:i:s' );

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- table name is built from $wpdb->prefix + literal suffix; custom table, no WP-cached equivalent.
		$existing = $wpdb->get_row( $wpdb->prepare( "SELECT id, token FROM {$table} WHERE form_id = %d AND email = %s LIMIT 1", $form_id, $email ), ARRAY_A );

		$payload = [
			'form_id'        => $form_id,
			'email'          => $email,
			'customer_data'  => wp_json_encode( (array) ( $data['customer_data'] ?? [] ) ),
			'items'          => wp_json_encode( (array) ( $data['items'] ?? [] ) ),
			'delivery_id'    => isset( $data['delivery_id'] ) ? (int) $data['delivery_id'] : null,
			'payment_method' => (string) ( $data['payment_method'] ?? '' ),
			'subtotal_cents' => (int) ( $data['subtotal_cents'] ?? 0 ),
			'delivery_cents' => (int) ( $data['delivery_cents'] ?? 0 ),
			'total_cents'    => (int) ( $data['total_cents']    ?? 0 ),
			'currency'       => (string) ( $data['currency'] ?? 'CHF' ),
			'lang'           => (string) ( $data['lang'] ?? '' ),
			'client_ip'      => (string) ( $data['client_ip'] ?? '' ),
			'user_agent'     => (string) ( $data['user_agent'] ?? '' ),
			'last_updated_at'=> $now,
			'email_sent_at'  => null,
		];

		if ( $existing ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$wpdb->update( $table, $payload, [ 'id' => (int) $existing['id'] ] );
			return (int) $existing['id'];
		}

		$payload['token']      = bin2hex( random_bytes( 20 ) );
		$payload['created_at'] = $now;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->insert( $table, $payload );
		return (int) $wpdb->insert_id;
	}

	public static function find_by_token( string $token ) : ?array {
		global $wpdb;
		$table = self::table();
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- table name is built from $wpdb->prefix + literal suffix; custom table, no WP-cached equivalent.
		$row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE token = %s LIMIT 1", $token ), ARRAY_A );
		return $row ?: null;
	}

	public static function find_by_id( int $id ) : ?array {
		global $wpdb;
		$table = self::table();
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- table name is built from $wpdb->prefix + literal suffix; custom table, no WP-cached equivalent.
		$row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d LIMIT 1", $id ), ARRAY_A );
		return $row ?: null;
	}

	public static function mark_recovered( int $id ) : void {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->update( self::table(), [ 'recovered_at' => gmdate( 'Y-m-d H:i:s' ) ], [ 'id' => $id ] );
	}

	public static function mark_email_sent( int $id ) : void {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->update( self::table(), [ 'email_sent_at' => gmdate( 'Y-m-d H:i:s' ) ], [ 'id' => $id ] );
	}

	/**
	 * Marks every cart matching the given email + form_id as converted to
	 * the given order. Called from Submission after wp_insert_post().
	 */
	public static function mark_converted_for( int $form_id, string $email, int $order_id ) : void {
		global $wpdb;
		$email = strtolower( trim( $email ) );
		if ( $form_id <= 0 || $email === '' || $order_id <= 0 ) { return; }
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->update(
			self::table(),
			[ 'converted_to_order_id' => $order_id ],
			[ 'form_id' => $form_id, 'email' => $email, 'converted_to_order_id' => null ]
		);
	}

	/**
	 * Compte les paniers « en attente » (= pas de mail envoyé, pas récupérés,
	 * pas convertis). Utilisé par Menu::reorder_submenu() pour le badge.
	 */
	public static function count_pending() : int {
		global $wpdb;
		$table = self::table();
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- table name is built from $wpdb->prefix + literal suffix; custom table, no WP-cached equivalent.
		return (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table} WHERE email_sent_at IS NULL AND recovered_at IS NULL AND converted_to_order_id IS NULL" );
	}

	public static function delete( int $id ) : void {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->delete( self::table(), [ 'id' => $id ] );
	}

	/**
	 * Returns the rows for the admin list. Supports a `status` filter:
	 *   '' / 'pending' / 'sent' / 'recovered' / 'converted'.
	 */
	public static function list_rows( string $status = '', int $limit = 200 ) : array {
		global $wpdb;
		$table = self::table();
		$where = '1=1';
		switch ( $status ) {
			case 'pending':
				$where = 'email_sent_at IS NULL AND recovered_at IS NULL AND converted_to_order_id IS NULL';
				break;
			case 'sent':
				$where = 'email_sent_at IS NOT NULL AND recovered_at IS NULL AND converted_to_order_id IS NULL';
				break;
			case 'recovered':
				$where = 'recovered_at IS NOT NULL AND converted_to_order_id IS NULL';
				break;
			case 'converted':
				$where = 'converted_to_order_id IS NOT NULL';
				break;
		}
		$limit = max( 1, min( 1000, $limit ) );
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $table built from $wpdb->prefix + literal suffix, $where from controlled whitelist above, $limit clamped.
		$rows = $wpdb->get_results( "SELECT * FROM {$table} WHERE {$where} ORDER BY last_updated_at DESC LIMIT {$limit}", ARRAY_A );
		return is_array( $rows ) ? $rows : [];
	}

	/**
	 * Cron tick : returns the carts that need a recovery email, i.e. older
	 * than N days, not yet emailed, not yet recovered, not yet converted.
	 */
	public static function due_for_email( int $delay_days ) : array {
		global $wpdb;
		$table = self::table();
		if ( $delay_days <= 0 ) { return []; }
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- table name is built from $wpdb->prefix + literal suffix; custom table, no WP-cached equivalent.
		$rows = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$table} WHERE email <> '' AND email_sent_at IS NULL AND recovered_at IS NULL AND converted_to_order_id IS NULL AND created_at <= (UTC_TIMESTAMP() - INTERVAL %d DAY) ORDER BY id ASC LIMIT 100", $delay_days ), ARRAY_A );
		return is_array( $rows ) ? $rows : [];
	}

	/**
	 * Cron purge: removes rows older than 90 days regardless of status.
	 * Keeps the table from growing unbounded and respects GDPR (no PII
	 * retention beyond what's useful for the recovery flow + a buffer).
	 */
	public static function purge_old( int $keep_days = 90 ) : int {
		global $wpdb;
		$table = self::table();
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- table name is built from $wpdb->prefix + literal suffix; custom table, no WP-cached equivalent.
		return (int) $wpdb->query( $wpdb->prepare( "DELETE FROM {$table} WHERE created_at < (UTC_TIMESTAMP() - INTERVAL %d DAY)", $keep_days ) );
	}
}
