<?php
/**
 * Class Pixelio_Cart_Abandon_Recovery_More_DB_helper
 *
 * Handles all database operations for the Pixelio Cart Abandonment plugin.
 * This includes table creation, deletion, and option management.
 *
 * @package PixelioCartAbandonRecoveryMore
 * @subpackage Db
 * @since 0.1
 */

namespace PixelioCartAbandonRecoveryMore\Includes\Db;

use PixelioCartAbandonRecoveryMore\Includes\Utils\Pixelio_Cart_Abandon_Recovery_More_Util;

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

/**
 * Class Pixelio_Cart_Abandon_Recovery_More_DB_helper
 *
 * Handles all database operations for the Pixelio Cart Abandonment plugin.
 * This includes table creation, deletion, and option management.
 *
 * @package PixelioCartAbandonRecoveryMore
 * @subpackage Db
 * @since 0.1
 */
class Pixelio_Cart_Abandon_Recovery_More_DB_Helper
{




	/**
	 * Email Templates table name.
	 *
	 * @var string
	 */
	private $email_templates;

	/**
	 * Cart table name.
	 *
	 * @var string
	 */
	private $cart;

	/**
	 * Cart item table name.
	 *
	 * @var string
	 */
	private $cart_item;

	/**
	 * Session data table name.
	 *
	 * @var string
	 */
	private $session_data;

	/**
	 * Email logs table name.
	 *
	 * @var string
	 */
	private $email_logs;

	/**
	 * Coupon table name.
	 *
	 * @var string
	 */
	private $coupon;

	private $wishlist;

	/**
	 * All table names.
	 *
	 * @var array
	 */
	private $table_names = array();

	/**
	 * List of plugin options to delete on uninstall.
	 *
	 * @var array
	 */
	private $all_options = array(
		'pixelio-cart-abandon-recovery-more-newsletters-submit-background-color',
		'pixelio-cart-abandon-recovery-more-newsletters-submit-text-color',
		'pixelio-cart-abandon-recovery-more-newsletters-submit-hover-color',
		'pixelio-cart-abandon-recovery-more-pop-up-no-thanks-exit-popup-text-color',
		'pixelio-cart-abandon-recovery-more-pop-up-yes-thanks-exit-popup-background-color',
		'pixelio-cart-abandon-recovery-more-pop-up-yes-thanks-exit-popup-text-color',
		'pixelio-cart-abandon-recovery-more-pop-up-yes-thanks-exit-hover-color',
		'pixelio-cart-abandon-recovery-more-pop-up-timer-content-text-color',
		'pixelio-cart-abandon-recovery-more-pop-up-header-title-text-color',
		'pixelio-cart-abandon-recovery-more-pop-up-header-title-action-text-color',
		'pixelio-cart-abandon-recovery-more-pop-up-header-background-color',
		'pixelio_first_config_done',
		'pixelio_api_key',
	);
	private mixed $pixelio_util;
	private string $contacts;
	private string $email_preferences;
	private string $consent_logs;
	private string $suppression;
	private string $uuid_to_contact;
	private string $anonymous_events;

	/**
	 * Constructor.
	 *
	 * Initializes table names using $wpdb prefix and sets up charset.
	 */
	public function __construct($pixelio_wordpress_wrapper, $pixelio_util)
	{
		$pixelio_wpdb_prefix = $pixelio_wordpress_wrapper->pixelio_get_wpdb_prefix();
		$pixelio_wordpress_wrapper->pixelio_get_upgrade_db();
		$this->pixelio_util = $pixelio_util;
		$this->cart = $pixelio_wpdb_prefix . Pixelio_Cart_Abandon_Recovery_More_Util::$pixelio_plugin_db_prefix . 'cart';
		$this->cart_item = $pixelio_wpdb_prefix . Pixelio_Cart_Abandon_Recovery_More_Util::$pixelio_plugin_db_prefix . 'cart_item';
		$this->session_data = $pixelio_wpdb_prefix . Pixelio_Cart_Abandon_Recovery_More_Util::$pixelio_plugin_db_prefix . 'session_data';
		$this->anonymous_events = $pixelio_wpdb_prefix . Pixelio_Cart_Abandon_Recovery_More_Util::$pixelio_plugin_db_prefix . 'anonymous_events';
		$this->coupon = $pixelio_wpdb_prefix . Pixelio_Cart_Abandon_Recovery_More_Util::$pixelio_plugin_db_prefix . 'coupons';
		$this->email_logs = $pixelio_wpdb_prefix . Pixelio_Cart_Abandon_Recovery_More_Util::$pixelio_plugin_db_prefix . 'email_logs';
		$this->email_templates = $pixelio_wpdb_prefix . Pixelio_Cart_Abandon_Recovery_More_Util::$pixelio_plugin_db_prefix . 'email_templates';
		$this->contacts = $pixelio_wpdb_prefix . Pixelio_Cart_Abandon_Recovery_More_Util::$pixelio_plugin_db_prefix . 'contacts';
		$this->email_preferences = $pixelio_wpdb_prefix . Pixelio_Cart_Abandon_Recovery_More_Util::$pixelio_plugin_db_prefix . 'email_preferences';
		$this->consent_logs = $pixelio_wpdb_prefix . Pixelio_Cart_Abandon_Recovery_More_Util::$pixelio_plugin_db_prefix . 'consent_logs';
		$this->suppression = $pixelio_wpdb_prefix . Pixelio_Cart_Abandon_Recovery_More_Util::$pixelio_plugin_db_prefix . 'suppression_list';
		$this->uuid_to_contact = $pixelio_wpdb_prefix . Pixelio_Cart_Abandon_Recovery_More_Util::$pixelio_plugin_db_prefix . 'uuid_to_contact';
		$this->wishlist = $pixelio_wpdb_prefix . Pixelio_Cart_Abandon_Recovery_More_Util::$pixelio_plugin_db_prefix . 'wishlist';
		$this->table_names[] = $this->cart;
		$this->table_names[] = $this->cart_item;
		$this->table_names[] = $this->coupon;
		$this->table_names[] = $this->session_data;
		$this->table_names[] = $this->anonymous_events;
		$this->table_names[] = $this->email_logs;
		$this->table_names[] = $this->email_templates;
		$this->table_names[] = $this->contacts;
		$this->table_names[] = $this->email_preferences;
		$this->table_names[] = $this->consent_logs;
		$this->table_names[] = $this->suppression;
		$this->table_names[] = $this->uuid_to_contact;
		$this->table_names[] = $this->wishlist;
	}

	/**
	 * Get email logs table name.
	 *
	 * @return string
	 */
	public function get_email_logs(): string
	{
		return $this->email_logs;
	}


	/**
	 * Delete all plugin data if remove-all-data option is enabled.
	 *
	 * @return void
	 */
	public function pixelio_cart_abandon_recovery_more_delete_all_data()
	{

		if ($this->pixelio_util->pixelio_cart_abandon_recovery_more_dependency_is_checked('pixelio-cart-abandon-recovery-more-remove-all-data-uninstall-checkbox')) {
			$this->pixelio_cart_abandon_recovery_more_delete_all_tables($this->table_names);
			$this->pixelio_cart_abandon_recovery_more_delete_all_options();
		} else {
			$this->pixelio_cart_abandon_recovery_more_delete_only_first_config_options();
		}
	}

	/**
	 * Delete all specified tables.
	 *
	 * @param array $all_tables List of table names to delete.
	 * @return void
	 */
	public function pixelio_cart_abandon_recovery_more_delete_all_tables(array $all_tables): void
	{
		global $wpdb;

		foreach ($all_tables as $table) {
			$esc_table = esc_sql($table);
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- safe: must fetch live data, caching not used. Drop table if user selected to delete all data on deactivation.
			$wpdb->query("DROP TABLE IF EXISTS `{$esc_table}`");
		}
	}

	/**
	 * Delete all plugin options.
	 *
	 * @return void
	 */
	public function pixelio_cart_abandon_recovery_more_delete_all_options(): void
	{
		foreach ($this->all_options as $option) {
			delete_option($option);
		}
	}

	/**
	 * Remove only the first config option in order to no have a "you dont have the right to see this screen" when reactivating
	 * @return void
	 */
	public function pixelio_cart_abandon_recovery_more_delete_only_first_config_options(): void
	{
		delete_option("pixelio_first_config_done");
		delete_option("pixelio_api_key");
	}





	/**
	 * Get cart table name.
	 *
	 * @return string
	 */
	public function get_pixelio_cart_abandon_recovery_more_cart(): string
	{
		return $this->cart;
	}

	/**
	 * Get cart item table name.
	 *
	 * @return string
	 */
	public function get_pixelio_cart_abandon_cart_item(): string
	{
		return $this->cart_item;
	}

	/**
	 * Get session data table name.
	 *
	 * @return string
	 */
	public function get_pixelio_cart_abandon_recovery_more_session_data(): string
	{
		return $this->session_data;
	}

	/**
	 * Get coupon table name.
	 *
	 * @return string
	 */
	public function get_pixelio_cart_abandon_recovery_more_get_coupon(): string
	{
		return $this->coupon;
	}

	/**
	 * Create all plugin database tables using dbDelta.
	 *
	 * @return void
	 */
	public function pixelio_cart_abandon_recovery_more_create_db(): void
	{

		$uuid_to_contact_sql = "CREATE TABLE $this->uuid_to_contact (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_uuid VARCHAR(191) NULL,
    user_id BIGINT UNSIGNED NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY  (id)
);";

		$contacts_sql = "CREATE TABLE $this->contacts (
									id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
									email VARCHAR(191) NOT NULL,
									email_hash CHAR(64) NOT NULL,
									global_unsubscribed TINYINT(1) DEFAULT 0,
									first_utm_source VARCHAR(191) DEFAULT NULL,
									first_utm_medium VARCHAR(191) DEFAULT NULL,
									first_utm_campaign VARCHAR(191) DEFAULT NULL,
									first_utm_content VARCHAR(191) DEFAULT NULL,
									first_utm_term VARCHAR(191) DEFAULT NULL,
									deleted_at DATETIME DEFAULT NULL,
									created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
									PRIMARY KEY (id),
									UNIQUE KEY email_unique (email),
									KEY email_hash_idx (email_hash)
								);";
		$email_preferences_sql = "CREATE TABLE $this->email_preferences (
									id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
									contact_id BIGINT UNSIGNED NOT NULL,
									email_type VARCHAR(100) NOT NULL,
									is_subscribed TINYINT(1) DEFAULT 1,
									updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
									PRIMARY KEY (id),
									UNIQUE KEY contact_email_type (contact_id, email_type),
									KEY contact_id_idx (contact_id)
								)";
		//user id référence ta table contacts
		$whishlist_sql = "CREATE TABLE $this->wishlist (
								id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
								product_id BIGINT UNSIGNED NOT NULL,
								user_id    BIGINT UNSIGNED DEFAULT NULL,
								created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
								reminded_at DATETIME DEFAULT NULL,
								PRIMARY KEY (id),
								INDEX idx_product (product_id),
								INDEX idx_user (user_id),
								INDEX idx_reminded (reminded_at)
								)";

		$consent_logs_sql = "CREATE TABLE $this->consent_logs (
								id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
								contact_id BIGINT UNSIGNED NOT NULL,
								consent_type VARCHAR(100) NOT NULL,
								consent_definition TEXT NULL,
								ip_address VARCHAR(45) NULL,
								user_agent TEXT NULL,
								consent_given TINYINT(1) NOT NULL,
								created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
								PRIMARY KEY (id),
								KEY contact_id_idx (contact_id),
								KEY consent_type_idx (consent_type)
							)";

		$suppression_sql = "CREATE TABLE $this->suppression (
								id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
								email_hash CHAR(64) NOT NULL,
								reason VARCHAR(100) NOT NULL,
								source VARCHAR(100) DEFAULT 'rivio_plugin',
								created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
								PRIMARY KEY (id),
								UNIQUE KEY email_hash_unique (email_hash)
							)";
		$cart_item_sql = "CREATE TABLE $this->cart_item (
                        id int  NOT NULL AUTO_INCREMENT,
                        cart_id int,
                        woo_product_id int,
                        quantity int,
                        price decimal(10,2),
                        discount decimal(10,2),
                        at timestamp,
                        is_removed boolean default 0,
                        synced boolean default 0,
                        PRIMARY KEY (id)
                ) ;";

		$session_data_sql = "CREATE TABLE $this->session_data (
    id int NOT NULL AUTO_INCREMENT,
    uuid varchar(255) default '',
    event_type varchar(80) default '',
    woo_product_id int default 0,
    parent_woo_product_id int default 0,
    page_url varchar(255),
    visit_duration int default 0,
    scroll_percent int default 0,
    tracking_mode varchar(50) default 'anonymous',
    at timestamp DEFAULT CURRENT_TIMESTAMP,
    synced boolean default 0,
    PRIMARY KEY (id),
    INDEX uuid_idx (uuid),
    INDEX product_idx (woo_product_id),
    INDEX event_type_idx (event_type)
);";

		$anonymous_events_sql = "CREATE TABLE {$this->anonymous_events} (
    id int NOT NULL AUTO_INCREMENT,
    event_type varchar(80) NOT NULL,
    woo_product_id int default 0,
    page_type varchar(80) default '',
    event_date date NOT NULL,
    event_hour tinyint default 0,
    counter int default 1,
    synced boolean default 0,
    PRIMARY KEY (id),
    UNIQUE KEY unique_aggregate (event_type, woo_product_id, page_type, event_date, event_hour)
);";

		$cart_data_sql = "CREATE TABLE $this->cart (
                        id int  NOT NULL AUTO_INCREMENT,
                         uuid varchar(255) default '',
                         token_for_recover varchar(255),
                         woo_order_id int,
                         coupon_id varchar(255),
                         converted_template_id varchar(255),
                         recovered boolean default 0,
                         created_at timestamp,
                         email_id int default 2,
                         cart_status_id int default 2,
                         updated_at timestamp,
                         is_removed int default 0,
                    	 synced boolean default 0,
                        PRIMARY KEY (id)
                ) ;";

		$coupon_sql = "CREATE TABLE $this->coupon (
                        id int  NOT NULL AUTO_INCREMENT,
                         coupon_code varchar(255),
                         is_removed boolean default 0,
                         synced boolean default 0,
                        PRIMARY KEY (id)
                ) ;";

		$email_logs = "CREATE TABLE $this->email_logs (
			 		id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
					cart_id INT NOT NULL,
					sent_at DATETIME DEFAULT CURRENT_TIMESTAMP,
					subject VARCHAR(255),
					template_id int not null,
					type int not null,
					recipient VARCHAR(255),
					   synced boolean default 0,
                       PRIMARY KEY  (id)
				);";

		$email_templates = "CREATE TABLE $this->email_templates (
								    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
									domain_id BIGINT UNSIGNED NULL,
									template_name VARCHAR(255) NOT NULL,
									language VARCHAR(10) NOT NULL DEFAULT 'fr',
									subject VARCHAR(255) NOT NULL,
									send_after INT NOT NULL,
									type_of_email INT NOT NULL,
									is_active TINYINT(1) NOT NULL DEFAULT 0,
									discount INT NOT NULL,
									discount_type INT NOT NULL,
									discount_expiration_time INT NOT NULL,
									body_html LONGTEXT NOT NULL,
									body_json LONGTEXT NOT NULL,
									variables JSON NULL,
									created_at DATETIME NOT NULL,
									updated_at DATETIME NOT NULL,
									numberOfOpening INT NOT NULL DEFAULT 0,
									unsubscribe_number_for_this_template INT NOT NULL DEFAULT 0,
									numberOfClick INT NOT NULL DEFAULT 0,
									totalSent INT NOT NULL DEFAULT 0,
									is_deleted INT NOT NULL DEFAULT 0,
									synced INT NOT NULL DEFAULT 0,
									generated_by_ia TINYINT(1) NOT NULL DEFAULT 0,
									PRIMARY KEY (id)
								);";

		$this->pixelio_cart_abandon_recovery_more_db_delta_all_sql(array($suppression_sql,$uuid_to_contact_sql, $whishlist_sql,$consent_logs_sql, $email_preferences_sql, $contacts_sql, $coupon_sql, $cart_item_sql, $session_data_sql, $cart_data_sql, $email_logs, $email_templates,$anonymous_events_sql));
	}

	/**
	 * Execute multiple SQL queries via dbDelta.
	 *
	 * @param array $all_sql Array of SQL strings to execute.
	 * @return void
	 */
	public function pixelio_cart_abandon_recovery_more_db_delta_all_sql($all_sql): void
	{
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		foreach ($all_sql as $sql) {
			dbDelta($sql);
		}
	}
}
