<?php
/**
 * WooCommerce Analytics Database Tables
 *
 * @package ShopExplorer\Database
 * @since 1.0.0
 */

namespace SHOPEXPLORER\Database;

if ( ! defined( 'ABSPATH' ) ) {
	exit; // Exit if accessed directly.
}

/**
 * WooAnalytics Tables class
 *
 * Handles creation and management of WooCommerce analytics database tables
 *
 * @since 1.0.0
 */
class WooAnalyticsTables {

	/**
	 * WordPress database instance
	 *
	 * @var \wpdb
	 */
	private $wpdb;

	/**
	 * Constructor
	 */
	public function __construct() {
		global $wpdb;
		$this->wpdb = $wpdb;
	}

	/**
	 * Create all analytics tables
	 */
	public function create_tables() {
		$this->create_incomplete_orders_table();
		$this->create_cart_interactions_table();
		$this->update_table_schema();
	}

	/**
	 * Create incomplete orders table
	 */
	private function create_incomplete_orders_table() {
		$table_name = $this->wpdb->prefix . 'wpnts_incomplete_orders';
		
		$charset_collate = $this->wpdb->get_charset_collate();
		
		$sql = "CREATE TABLE IF NOT EXISTS $table_name (
			id int(11) NOT NULL AUTO_INCREMENT,
			session_key varchar(255) NOT NULL,
			user_id int(11) DEFAULT 0,
			email varchar(255) DEFAULT '',
			name varchar(255) DEFAULT '',
			phone varchar(50) DEFAULT '',
			address text DEFAULT '',
			product_ids longtext DEFAULT '',
			product_names longtext DEFAULT '',
			product_links longtext DEFAULT '',
			cart_data longtext DEFAULT '',
			total_amount decimal(10,2) DEFAULT 0.00,
			cart_interactions longtext DEFAULT '',
			is_abandonment tinyint(1) DEFAULT 0,
			time_on_checkout int(11) DEFAULT 0,
			ip varchar(45) DEFAULT '',
			user_agent text DEFAULT '',
			status varchar(20) DEFAULT 'incomplete',
			order_id int(11) DEFAULT 0,
			contacted_at datetime DEFAULT NULL,
			created_at datetime DEFAULT CURRENT_TIMESTAMP,
			updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY session_key (session_key),
			KEY email (email),
			KEY status (status),
			KEY created_at (created_at),
			KEY user_id (user_id)
		) $charset_collate;";
		
		require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
		dbDelta( $sql );
	}

	/**
	 * Create cart interactions table
	 */
	private function create_cart_interactions_table() {
		$table_name = $this->wpdb->prefix . 'wpnts_cart_interactions';
		
		$charset_collate = $this->wpdb->get_charset_collate();
		
		$sql = "CREATE TABLE IF NOT EXISTS $table_name (
			id int(11) NOT NULL AUTO_INCREMENT,
			session_key varchar(255) NOT NULL,
			interaction_type varchar(50) NOT NULL,
			product_name varchar(255) DEFAULT '',
			product_id int(11) DEFAULT 0,
			quantity int(11) DEFAULT 0,
			value decimal(10,2) DEFAULT 0.00,
			interaction_data longtext DEFAULT '',
			page_url varchar(500) DEFAULT '',
			user_agent text DEFAULT '',
			ip varchar(45) DEFAULT '',
			created_at datetime DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY session_key (session_key),
			KEY interaction_type (interaction_type),
			KEY product_id (product_id),
			KEY created_at (created_at)
		) $charset_collate;";
		
		require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
		dbDelta( $sql );
	}

	/**
	 * Update table schema for existing installations
	 */
	private function update_table_schema() {
		$this->add_missing_columns();
		$this->add_missing_indexes();
	}

	/**
	 * Add missing columns to existing tables
	 */
	private function add_missing_columns() {
		$incomplete_orders_table = $this->wpdb->prefix . 'wpnts_incomplete_orders';
		$cart_interactions_table = $this->wpdb->prefix . 'wpnts_cart_interactions';

		// Check and add columns to incomplete orders table
		$incomplete_columns = $this->wpdb->get_results( "SHOW COLUMNS FROM $incomplete_orders_table" );
		$existing_columns = array_column( $incomplete_columns, 'Field' );

		$required_columns = [
			'cart_interactions' => "ADD COLUMN cart_interactions longtext DEFAULT ''",
			'is_abandonment' => "ADD COLUMN is_abandonment tinyint(1) DEFAULT 0",
			'time_on_checkout' => "ADD COLUMN time_on_checkout int(11) DEFAULT 0",
			'ip' => "ADD COLUMN ip varchar(45) DEFAULT ''",
			'user_agent' => "ADD COLUMN user_agent text DEFAULT ''",
			'order_id' => "ADD COLUMN order_id int(11) DEFAULT 0",
			'contacted_at' => "ADD COLUMN contacted_at datetime DEFAULT NULL",
			'updated_at' => "ADD COLUMN updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"
		];

		foreach ( $required_columns as $column => $sql ) {
			if ( ! in_array( $column, $existing_columns ) ) {
				$this->wpdb->query( "ALTER TABLE $incomplete_orders_table $sql" );
			}
		}

		// Check and add columns to cart interactions table
		$interaction_columns = $this->wpdb->get_results( "SHOW COLUMNS FROM $cart_interactions_table" );
		$existing_interaction_columns = array_column( $interaction_columns, 'Field' );

		$required_interaction_columns = [
			'product_id' => "ADD COLUMN product_id int(11) DEFAULT 0",
			'page_url' => "ADD COLUMN page_url varchar(500) DEFAULT ''",
			'user_agent' => "ADD COLUMN user_agent text DEFAULT ''",
			'ip' => "ADD COLUMN ip varchar(45) DEFAULT ''"
		];

		foreach ( $required_interaction_columns as $column => $sql ) {
			if ( ! in_array( $column, $existing_interaction_columns ) ) {
				$this->wpdb->query( "ALTER TABLE $cart_interactions_table $sql" );
			}
		}
	}

	/**
	 * Add missing indexes to existing tables
	 */
	private function add_missing_indexes() {
		$incomplete_orders_table = $this->wpdb->prefix . 'wpnts_incomplete_orders';
		$cart_interactions_table = $this->wpdb->prefix . 'wpnts_cart_interactions';

		// Add indexes to incomplete orders table
		$incomplete_indexes = $this->wpdb->get_results( "SHOW INDEX FROM $incomplete_orders_table" );
		$existing_indexes = array_column( $incomplete_indexes, 'Key_name' );

		$required_indexes = [
			'user_id' => "ADD INDEX user_id (user_id)",
			'status' => "ADD INDEX status (status)",
			'email' => "ADD INDEX email (email)"
		];

		foreach ( $required_indexes as $index => $sql ) {
			if ( ! in_array( $index, $existing_indexes ) ) {
				$this->wpdb->query( "ALTER TABLE $incomplete_orders_table $sql" );
			}
		}

		// Add indexes to cart interactions table
		$interaction_indexes = $this->wpdb->get_results( "SHOW INDEX FROM $cart_interactions_table" );
		$existing_interaction_indexes = array_column( $interaction_indexes, 'Key_name' );

		$required_interaction_indexes = [
			'product_id' => "ADD INDEX product_id (product_id)",
			'interaction_type' => "ADD INDEX interaction_type (interaction_type)"
		];

		foreach ( $required_interaction_indexes as $index => $sql ) {
			if ( ! in_array( $index, $existing_interaction_indexes ) ) {
				$this->wpdb->query( "ALTER TABLE $cart_interactions_table $sql" );
			}
		}
	}

	/**
	 * Check if tables exist
	 *
	 * @return array
	 */
	public function check_tables_status() {
		$incomplete_orders_table = $this->wpdb->prefix . 'wpnts_incomplete_orders';
		$cart_interactions_table = $this->wpdb->prefix . 'wpnts_cart_interactions';

		return [
			'incomplete_orders' => $this->wpdb->get_var( "SHOW TABLES LIKE '$incomplete_orders_table'" ) === $incomplete_orders_table,
			'cart_interactions' => $this->wpdb->get_var( "SHOW TABLES LIKE '$cart_interactions_table'" ) === $cart_interactions_table
		];
	}

	/**
	 * Get table statistics
	 *
	 * @return array
	 */
	public function get_table_stats() {
		$incomplete_orders_table = $this->wpdb->prefix . 'wpnts_incomplete_orders';
		$cart_interactions_table = $this->wpdb->prefix . 'wpnts_cart_interactions';

		$stats = [
			'incomplete_orders_count' => 0,
			'cart_interactions_count' => 0,
			'incomplete_orders_size' => 0,
			'cart_interactions_size' => 0
		];

		// Get record counts
		$stats['incomplete_orders_count'] = $this->wpdb->get_var( "SELECT COUNT(*) FROM $incomplete_orders_table" );
		$stats['cart_interactions_count'] = $this->wpdb->get_var( "SELECT COUNT(*) FROM $cart_interactions_table" );

		// Get table sizes
		$table_status = $this->wpdb->get_results( 
			"SELECT table_name, data_length, index_length 
			 FROM information_schema.tables 
			 WHERE table_schema = DATABASE() 
			 AND table_name IN ('$incomplete_orders_table', '$cart_interactions_table')"
		);

		foreach ( $table_status as $table ) {
			$size = $table->data_length + $table->index_length;
			if ( $table->table_name === $incomplete_orders_table ) {
				$stats['incomplete_orders_size'] = $size;
			} elseif ( $table->table_name === $cart_interactions_table ) {
				$stats['cart_interactions_size'] = $size;
			}
		}

		return $stats;
	}

	/**
	 * Clean up old records
	 *
	 * @param int $days Number of days to keep
	 * @return array
	 */
	public function cleanup_old_records( $days = 30 ) {
		$incomplete_orders_table = $this->wpdb->prefix . 'wpnts_incomplete_orders';
		$cart_interactions_table = $this->wpdb->prefix . 'wpnts_cart_interactions';

		$cutoff_date = date( 'Y-m-d H:i:s', strtotime( "-{$days} days" ) );

		// Clean up incomplete orders
		$incomplete_deleted = $this->wpdb->query( $this->wpdb->prepare(
			"DELETE FROM $incomplete_orders_table WHERE created_at < %s AND status = 'incomplete'",
			$cutoff_date
		) );

		// Clean up cart interactions
		$interactions_deleted = $this->wpdb->query( $this->wpdb->prepare(
			"DELETE FROM $cart_interactions_table WHERE created_at < %s",
			$cutoff_date
		) );

		return [
			'incomplete_orders_deleted' => $incomplete_deleted,
			'cart_interactions_deleted' => $interactions_deleted
		];
	}

	/**
	 * Drop all analytics tables (for uninstall)
	 */
	public function drop_tables() {
		$incomplete_orders_table = $this->wpdb->prefix . 'wpnts_incomplete_orders';
		$cart_interactions_table = $this->wpdb->prefix . 'wpnts_cart_interactions';

		$this->wpdb->query( "DROP TABLE IF EXISTS $incomplete_orders_table" );
		$this->wpdb->query( "DROP TABLE IF EXISTS $cart_interactions_table" );
	}
}