<?php
/**
 * File: Ecre_Tables.php
 *
 * Database table creation and management for ECRE referral system.
 * This class handles only table creation and structure management,
 * following WordPress coding standards and best practices.
 *
 * @package ECRE
 * @since   1.0.0
 */

namespace ECRE;

if ( ! class_exists( 'ECRE\Ecre_Tables' ) ) {
	/**
	 * Handles database table creation and structure management
	 * for the ECRE referral system.
	 *
	 * @since 1.0.0
	 */
	class Ecre_Tables {

		/**
		 * Current database version
		 *
		 * @var string
		 */
		const DB_VERSION = '1.1.0';

		/**
		 * Constructor
		 */
		public function __construct() {
			// Prevent multiple hook registrations.
			static $hooks_added = false;
			if ( ! $hooks_added ) {
				// Create tables immediately when class is instantiated.
				$this->ensure_tables_exist();

				// Check and create tables on admin_init for existing installations.
				add_action( 'admin_init', array( $this, 'check_and_create_tables' ) );

				$hooks_added = true;
			}

			// Hook into plugin activation for new installations.
			register_activation_hook( ECRE_FILE, array( $this, 'create_tables_on_activation' ) );
		}

		/**
		 * Create all referral system tables
		 *
		 * @return void
		 */
		public function create_all_tables() {
			$this->create_ecre_referrers_table();
			$this->create_ecre_referrals_orders_table();
			$this->create_ecre_rewards_orders_table();
			$this->create_ecre_reward_coupons_table();
			$this->create_ecre_referrars_dashboard_summary_table();
			$this->create_ecre_user_reward_settings_table();

			update_option( 'ecre_db_version', self::DB_VERSION );
		}


		/**
		 * Create the ecre_referrars_dashboard_summary table
		 * Initialize with zeros only - no expensive data fetching
		 */
		public function create_ecre_referrars_dashboard_summary_table() {
			global $wpdb;

			$charset_collate = $wpdb->get_charset_collate();
			$table_name      = $wpdb->prefix . 'ecre_referrars_dashboard_summary';

			$sql = "CREATE TABLE $table_name (
				id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
				
				-- Dashboard summary statistics
				total_referrers int(11) DEFAULT 0,
				total_referrals int(11) DEFAULT 0, 
				total_referral_sales decimal(15,2) DEFAULT 0.00,
				total_rewards_issued int(11) DEFAULT 0,
				total_discount_given decimal(15,2) DEFAULT 0.00,
				
				-- Additional dashboard metrics
				active_referrers int(11) DEFAULT 0,
				average_conversion_rate decimal(5,2) DEFAULT 0.00,
				
				-- Period tracking
				summary_period varchar(20) DEFAULT 'all_time',
				
				-- Timestamps
				last_updated datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
				created_at datetime DEFAULT CURRENT_TIMESTAMP,
				
				PRIMARY KEY (id),
				KEY idx_summary_period (summary_period),
				KEY idx_last_updated (last_updated)
			) $charset_collate;";

			require_once ABSPATH . 'wp-admin/includes/upgrade.php';
			dbDelta( $sql );

			if ( $this->table_exists( $table_name ) ) {
				// Just insert a zero record - no data fetching.
				$this->create_empty_dashboard_summary_record();
			}
		}


		/**
		 * Create empty dashboard summary record with zeros
		 * No expensive queries, just insert zeros
		 */
		private function create_empty_dashboard_summary_record() {
			global $wpdb;

			// Check if record already exists.
			$exists = $wpdb->get_var(
				$wpdb->prepare(
					"SELECT COUNT(*) FROM `{$wpdb->prefix}ecre_referrars_dashboard_summary` WHERE summary_period = %s",
					'all_time'
				)
			);

			if ( 0 === (int) $exists ) {
				// Insert zero-initialized record.
				$wpdb->insert(
					$wpdb->prefix . 'ecre_referrars_dashboard_summary',
					array(
						'total_referrers'         => 0,
						'total_referrals'         => 0,
						'total_referral_sales'    => 0.00,
						'total_rewards_issued'    => 0,
						'total_discount_given'    => 0.00,
						'active_referrers'        => 0,
						'average_conversion_rate' => 0.00,
						'summary_period'          => 'all_time',
					),
					array( '%d', '%d', '%f', '%d', '%f', '%d', '%f', '%s' )
				);
			}
		}


		/**
		 * Create the ecre_referrers table
		 *
		 * This table stores referrer summary data with pre-calculated totals
		 * to avoid expensive SUM operations on queries.
		 *
		 * @return void
		 */
		public function create_ecre_referrers_table() {
			global $wpdb;

			$charset_collate = $wpdb->get_charset_collate();
			$table_name      = $wpdb->prefix . 'ecre_referrers';

			$sql = "CREATE TABLE $table_name (
				id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
				user_id bigint(20) UNSIGNED NOT NULL,
				
				-- Referral coupon information
				referral_coupon_code varchar(100) DEFAULT NULL,
				referral_coupon_id bigint(20) UNSIGNED DEFAULT NULL,
				
				-- Pre-calculated totals (updated with +/- operations)
				total_referrals int(11) DEFAULT 0,
				total_sales decimal(10,2) DEFAULT 0.00,
				total_rewards_issued int(11) DEFAULT 0,
				total_discount decimal(10,2) DEFAULT 0.00,
				conversion_rate decimal(5,2) DEFAULT 0.00,

				-- Flags
				custom_rewards tinyint(1) DEFAULT 0,
				custom_referrals tinyint(1) DEFAULT 0,
				
				-- Date tracking
				first_referral_date datetime DEFAULT NULL,
				last_referral_date datetime DEFAULT NULL,
				
				-- Timestamps
				created_at datetime DEFAULT CURRENT_TIMESTAMP,
				updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
				
				PRIMARY KEY (id),
				UNIQUE KEY user_id (user_id),
				KEY idx_referral_coupon_code (referral_coupon_code),
				KEY idx_referral_coupon_id (referral_coupon_id),
				KEY idx_total_sales (total_sales),
				KEY idx_total_referrals (total_referrals),
				KEY idx_updated_at (updated_at)
			) $charset_collate;";

			require_once ABSPATH . 'wp-admin/includes/upgrade.php';
			dbDelta( $sql );
		}


		/**
		 * Create the ecre_referrals_orders table
		 *
		 * This table stores individual referral order records
		 * linking referrers to their referred orders.
		 *
		 * @return void
		 */
		public function create_ecre_referrals_orders_table() {
			global $wpdb;

			$charset_collate = $wpdb->get_charset_collate();
			$table_name      = $wpdb->prefix . 'ecre_referrals_orders';

			$sql = "CREATE TABLE $table_name (
				id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
				
				-- Referrer information
				referrer_user_id bigint(20) UNSIGNED NOT NULL,
				
				-- Order/transaction information  
				order_id bigint(20) UNSIGNED NOT NULL,
				referred_user_id bigint(20) UNSIGNED DEFAULT NULL, -- NULL if guest checkout
				coupon_code varchar(100) NOT NULL,
				coupon_id bigint(20) UNSIGNED DEFAULT NULL,
				
				-- Financial data
				order_value decimal(10,2) DEFAULT 0.00,
				discount_amount decimal(10,2) DEFAULT 0.00,
				
				-- Customer information
				firstname varchar(100) DEFAULT '',
				lastname varchar(100) DEFAULT '',
				fullname varchar(255) DEFAULT '',
				customer_email varchar(255) NOT NULL,
				
				-- Status and dates
				referral_status varchar(50) DEFAULT 'pending',
				order_date datetime DEFAULT NULL,
				
				-- Timestamps
				created_at datetime DEFAULT CURRENT_TIMESTAMP,
				updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
				
				PRIMARY KEY (id),
				UNIQUE KEY unique_order_referrer (order_id, referrer_user_id),
				KEY idx_referrer_user_id (referrer_user_id),
				KEY idx_order_id (order_id),
				KEY idx_coupon_code (coupon_code),
				KEY idx_referral_status (referral_status),
				KEY idx_order_date (order_date)
			) $charset_collate;";

			require_once ABSPATH . 'wp-admin/includes/upgrade.php';
			dbDelta( $sql );
		}

		/**
		 * Create the ecre_rewards_orders table
		 *
		 * This table stores records when reward coupons are actually used in orders,
		 * linking back to the original referral that generated the reward.
		 *
		 * @return void
		 */
		public function create_ecre_rewards_orders_table() {
			global $wpdb;

			$charset_collate = $wpdb->get_charset_collate();
			$table_name      = $wpdb->prefix . 'ecre_rewards_orders';

			$sql = "CREATE TABLE $table_name (
				id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
				
				-- Reward coupon information
				reward_coupon_id bigint(20) UNSIGNED NOT NULL,
				reward_coupon_code varchar(100) NOT NULL,
				
				-- Order where reward was used
				order_id bigint(20) UNSIGNED NOT NULL,
				user_id bigint(20) UNSIGNED DEFAULT NULL, -- User who used the reward (may be different from referrer)
				
				-- Link back to original referral
				referral_user_id bigint(20) UNSIGNED NOT NULL,
				referral_coupon_id bigint(20) UNSIGNED DEFAULT NULL,
				referral_coupon_code varchar(100) DEFAULT NULL,
				
				-- Financial impact
				reward_discount_amount decimal(10,2) DEFAULT 0.00,
				order_total decimal(10,2) DEFAULT 0.00,
				order_subtotal decimal(10,2) DEFAULT 0.00,
				
				-- Customer information (who used the reward)
				customer_email varchar(255) DEFAULT '',
				firstname varchar(100) DEFAULT '',
				lastname varchar(100) DEFAULT '',
				fullname varchar(255) DEFAULT '',
				
				-- Usage details
				usage_date datetime DEFAULT NULL,
				reward_status varchar(50) DEFAULT 'pending',
				
				-- Tracking fields
				reward_type varchar(50) DEFAULT 'referral_reward', -- For future expansion
				notes text DEFAULT NULL,
				
				-- Timestamps
				created_at datetime DEFAULT CURRENT_TIMESTAMP,
				updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
				
				PRIMARY KEY (id),
				UNIQUE KEY unique_reward_order (reward_coupon_id, order_id),
				KEY idx_reward_coupon_code (reward_coupon_code),
				KEY idx_order_id (order_id),
				KEY idx_user_id (user_id),
				KEY idx_referral_user_id (referral_user_id),
				KEY idx_referral_coupon_id (referral_coupon_id),
				KEY idx_referral_coupon_code (referral_coupon_code),
				KEY idx_usage_date (usage_date),
				KEY idx_reward_status (reward_status),
				KEY idx_reward_type (reward_type),
				KEY idx_customer_email (customer_email),
				KEY idx_fullname (fullname),
				KEY idx_created_at (created_at)
			) $charset_collate;";

			require_once ABSPATH . 'wp-admin/includes/upgrade.php';
			dbDelta( $sql );
		}

		/**
		 * Create the ecre_reward_coupons table
		 *
		 * This table stores all generated reward coupons
		 * for tracking and management purposes.
		 *
		 * @return void
		 */
		public function create_ecre_reward_coupons_table() {
			global $wpdb;

			$charset_collate = $wpdb->get_charset_collate();
			$table_name      = $wpdb->prefix . 'ecre_reward_coupons';

			$sql = "CREATE TABLE $table_name (
				id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
				
				-- Referrer information
				referrer_user_id bigint(20) UNSIGNED NOT NULL,
				order_id bigint(20) UNSIGNED NOT NULL,
				
				-- Coupon details
				coupon_code varchar(100) NOT NULL,
				coupon_id bigint(20) UNSIGNED DEFAULT NULL,
				discount_type varchar(20) NOT NULL DEFAULT 'fixed_cart',
				discount_amount decimal(10,2) NOT NULL DEFAULT 0.00,
				
				-- Usage information
				usage_limit int(11) NOT NULL DEFAULT 1,
				usage_count int(11) NOT NULL DEFAULT 0,
				
				-- Status and expiry
				status varchar(20) NOT NULL DEFAULT 'active',
				expires_at datetime DEFAULT NULL,
				
				-- Product/category restrictions (separate columns for better control)
				include_products text DEFAULT NULL,
				exclude_products text DEFAULT NULL,
				include_product_categories text DEFAULT NULL,
				exclude_product_categories text DEFAULT NULL,
				
				-- Timestamps
				created_at datetime DEFAULT CURRENT_TIMESTAMP,
				updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
				
				PRIMARY KEY (id),
				UNIQUE KEY coupon_code (coupon_code),
				KEY idx_referrer_user_id (referrer_user_id),
				KEY idx_order_id (order_id),
				KEY idx_coupon_id (coupon_id),
				KEY idx_status (status),
				KEY idx_created_at (created_at),
				KEY idx_expires_at (expires_at)
			) $charset_collate;";

			require_once ABSPATH . 'wp-admin/includes/upgrade.php';
			dbDelta( $sql );
		}

		/**
		 * Create the ecre_user_reward_settings table
		 *
		 * This table stores user-specific reward settings that override global defaults
		 *
		 * @return void
		 */
		public function create_ecre_user_reward_settings_table() {
			global $wpdb;

			$charset_collate = $wpdb->get_charset_collate();
			$table_name      = $wpdb->prefix . 'ecre_user_reward_settings';

			$sql = "CREATE TABLE $table_name (
				id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
				
				-- User identification
				user_id bigint(20) UNSIGNED NOT NULL,
				
				-- Setting details
				setting_key varchar(100) NOT NULL,
				setting_value text DEFAULT NULL,
				data_type varchar(20) DEFAULT 'string',
				
				-- Metadata
				is_active tinyint(1) DEFAULT 1,
				
				-- Timestamps
				created_at datetime DEFAULT CURRENT_TIMESTAMP,
				updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
				
				PRIMARY KEY (id),
				UNIQUE KEY unique_user_setting (user_id, setting_key),
				KEY idx_user_id (user_id),
				KEY idx_setting_key (setting_key),
				KEY idx_is_active (is_active)
			) $charset_collate;";

			require_once ABSPATH . 'wp-admin/includes/upgrade.php';
			dbDelta( $sql );
		}

		/**
		 * Ensure tables exist - called on init
		 *
		 * @return void
		 */
		public function ensure_tables_exist() {
			$this->check_and_create_tables();
		}

		/**
		 * Create tables on plugin activation
		 *
		 * @return void
		 */
		public function create_tables_on_activation() {
			$this->create_all_tables();
		}


		/**
		 * Check if tables exist and create them if they don't
		 *
		 * @return void
		 */
		public function check_and_create_tables() {
			// Check if we've already run this check recently to avoid running on every admin page load.
			$last_check   = get_option( 'ecre_tables_last_check', 0 );
			$current_time = time();

			// Get table names.
			$referrers_table         = $this->get_referrers_table_name();
			$referrals_table         = $this->get_referrals_orders_table_name();
			$reward_table            = $this->get_rewards_orders_table_name();
			$reward_coupons_table    = $this->get_reward_coupons_table_name();
			$dashboard_summary_table = $this->get_referrars_dashboard_summary_table_name();
			$user_settings_table     = $this->get_user_reward_settings_table_name();

			// Check if tables exist.
			$referrers_exists         = $this->table_exists( $referrers_table );
			$referrals_exists         = $this->table_exists( $referrals_table );
			$rewards_exists           = $this->table_exists( $reward_table );
			$reward_coupons_exists    = $this->table_exists( $reward_coupons_table );
			$dashboard_summary_exists = $this->table_exists( $dashboard_summary_table );
			$user_settings_exists     = $this->table_exists( $user_settings_table );

			// Force check if any table is missing, otherwise use cache.
			$force_check = ! $referrers_exists || ! $referrals_exists || ! $reward_coupons_exists || ! $rewards_exists || ! $dashboard_summary_exists || ! $user_settings_exists;

			// Only check once per hour to avoid performance issues, unless tables are missing.
			if ( ! $force_check && ( $current_time - $last_check ) < 3600 ) {
				return;
			}

			// Create table if it doesn't exist.
			if ( ! $user_settings_exists ) {
				$this->create_ecre_user_reward_settings_table();
			}

			// Create tables if they don't exist.
			if ( ! $referrers_exists ) {
				$this->create_ecre_referrers_table();
			}

			if ( ! $referrals_exists ) {
				$this->create_ecre_referrals_orders_table();
			}

			if ( ! $rewards_exists ) {
				$this->create_ecre_rewards_orders_table();
			}

			if ( ! $reward_coupons_exists ) {
				$this->create_ecre_reward_coupons_table();
			}

			// Add this block for the new dashboard summary table.
			if ( ! $dashboard_summary_exists ) {
				$this->create_ecre_referrars_dashboard_summary_table();
			}

			// Update the last check time.
			update_option( 'ecre_tables_last_check', $current_time );
		}

		/**
		 * Force table creation and clear cache
		 *
		 * @return array Results of table creation
		 */
		public static function force_create_tables() {
			// Clear the cache.
			delete_option( 'ecre_tables_last_check' );

			// Create instance to call instance methods.
			$instance = new self();

			// Force create tables.
			$instance->create_all_tables();

			// Check if tables were created successfully.
			$results = array(
				'referrers_table'                    => $instance->table_exists( $instance->get_referrers_table_name() ),
				'referrals_orders_table'             => $instance->table_exists( $instance->get_referrals_orders_table_name() ),
				'reward_coupons_table'               => $instance->table_exists( $instance->get_reward_coupons_table_name() ),
				'rewards_orders_table'               => $instance->table_exists( $instance->get_rewards_orders_table_name() ),
				'referrars_dashboard_summary_table'  => $instance->table_exists( $instance->get_referrars_dashboard_summary_table_name() ),
				'user_reward_settings_summary_table' => $instance->table_exists( $instance->get_user_reward_settings_table_name() ),
			);

			return $results;
		}

		/**
		 * Clear table check cache to force immediate table check
		 *
		 * @return void
		 */
		public static function clear_table_cache() {
			delete_option( 'ecre_tables_last_check' );
		}

		/**
		 * Get table status for debugging
		 *
		 * @return array Table existence status
		 */
		public function get_table_status() {
			return array(
				'referrers_table'                    => array(
					'name'   => $this->get_referrers_table_name(),
					'exists' => $this->table_exists( $this->get_referrers_table_name() ),
				),
				'referrals_orders_table'             => array(
					'name'   => $this->get_referrals_orders_table_name(),
					'exists' => $this->table_exists( $this->get_referrals_orders_table_name() ),
				),
				'reward_coupons_table'               => array(
					'name'   => $this->get_reward_coupons_table_name(),
					'exists' => $this->table_exists( $this->get_reward_coupons_table_name() ),
				),
				'rewards_orders_table'               => array(
					'name'   => $this->get_rewards_orders_table_name(),
					'exists' => $this->table_exists( $this->get_rewards_orders_table_name() ),
				),
				'referrars_dashboard_summary_table'  => array(
					'name'   => $this->get_referrars_dashboard_summary_table_name(),
					'exists' => $this->table_exists( $this->get_referrars_dashboard_summary_table_name() ),
				),
				'user_reward_settings_summary_table' => array(
					'name'   => $this->get_user_reward_settings_table_name(),
					'exists' => $this->table_exists( $this->get_user_reward_settings_table_name() ),
				),
			);
		}

		/**
		 * Helper method to get referrers table name
		 *
		 * @return string Table name
		 */
		public function get_referrers_table_name() {
			global $wpdb;
			return $wpdb->prefix . 'ecre_referrers';
		}

		/**
		 * Helper method to get referrars dashboard summary table name
		 *
		 * @return string Table name
		 */
		public function get_referrars_dashboard_summary_table_name() {
			global $wpdb;
			return $wpdb->prefix . 'ecre_referrars_dashboard_summary';
		}

		/**
		 * Helper method to get referrars dashboard summary table name
		 *
		 * @return string Table name
		 */
		public function get_user_reward_settings_table_name() {
			global $wpdb;
			return $wpdb->prefix . 'ecre_user_reward_settings';
		}

		/**
		 * Helper method to get rewards orders table name
		 *
		 * @return string Table name
		 */
		public function get_rewards_orders_table_name() {
			global $wpdb;
			return $wpdb->prefix . 'ecre_rewards_orders';
		}

		/**
		 * Helper method to get referrals orders table name
		 *
		 * @return string Table name
		 */
		public function get_referrals_orders_table_name() {
			global $wpdb;
			return $wpdb->prefix . 'ecre_referrals_orders';
		}

		/**
		 * Helper method to get reward coupons table name
		 *
		 * @return string Table name
		 */
		public function get_reward_coupons_table_name() {
			global $wpdb;
			return $wpdb->prefix . 'ecre_reward_coupons';
		}

		/**
		 * Check if a database table exists
		 *
		 * @param string $table_name Table name to check.
		 * @return bool True if table exists, false otherwise
		 */
		private function table_exists( $table_name ) {
			global $wpdb;
			$result = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table_name ) );
			return $result === $table_name;
		}
	}
}
