<?php

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

require_once __DIR__ . '/dmm/sql/subjects-schema.php';

class AIEO_Customer_Purchases {

	const HOOK = 'aieo_customer_purchases_rollup';

	
	
	
	
	
	const PURCHASE_STATUSES = "('wc-completed','wc-processing','erp-completed')";

	
	private static $need = array( 'CustomerEmail', 'OrderId', 'OrderDate' );

	
	private static $kinds = array(
		'product'  => array( 'ProductId', 'ParentProductId' ),
		'brand'    => array( 'BrandId' ),
		'category' => array( 'MenuId', 'HeadId', 'SubcatId' ), 
	);

	
	private static function term_kinds() {
		return array(
			'tag'  => (string) apply_filters( 'aieo_purchases_tag_taxonomy', 'product_tag' ),
			'need' => (string) apply_filters( 'aieo_purchases_needs_taxonomy', 'pa_need' ),
		);
	}

	
	private static function all_kinds() {
		return array_merge( array_keys( self::$kinds ), array_keys( self::term_kinds() ) );
	}

	
	private static $col_cache = array();

	
	private static $booted = false;

	public static function bootstrap() {
		if ( self::$booted ) {
			return;
		}
		self::$booted = true;
		add_action( self::HOOK, array( __CLASS__, 'rebuild' ) );
		add_action( 'admin_init', array( __CLASS__, 'maybe_schedule' ), 30 );
		add_action( 'init', array( __CLASS__, 'maybe_schedule' ), 30 );
	}

	
	public static function maybe_schedule() {
		if ( class_exists( 'AIEO_Schedule_Gate', false ) && ! AIEO_Schedule_Gate::due( __METHOD__ ) ) {
			return; 
		}
		static $checked = false;
		if ( $checked ) {
			return;
		}
		$checked = true;
		$first = self::next_night_ts();
		if ( function_exists( 'as_next_scheduled_action' ) && function_exists( 'as_schedule_recurring_action' ) ) {
			if ( false === as_next_scheduled_action( self::HOOK, null, 'aieo' ) ) {
				as_schedule_recurring_action( $first, DAY_IN_SECONDS, self::HOOK, array(), 'aieo' );
			}
		} elseif ( ! wp_next_scheduled( self::HOOK ) ) {
			wp_schedule_event( $first, 'daily', self::HOOK );
		}
	}

	
	private static function next_night_ts() {
		$now   = time();
		$off   = (int) ( get_option( 'gmt_offset', 0 ) * HOUR_IN_SECONDS );
		$local = $now + $off;
		$midnight_local = $local - ( $local % DAY_IN_SECONDS );      
		$target_local   = $midnight_local + 3 * HOUR_IN_SECONDS + 15 * MINUTE_IN_SECONDS;
		if ( $target_local <= $local ) {
			$target_local += DAY_IN_SECONDS;                          
		}
		return $target_local - $off;                                 
	}

	
	public static function rebuild() {
		global $wpdb;
		
		
		
		if ( 1 !== (int) $wpdb->get_var( "SELECT GET_LOCK('aieo_purchases_rebuild', 0)" ) ) {
			return array( 'ok' => false, 'reason' => 'locked' );
		}
		try {
			return self::rebuild_locked();
		} finally {
			$wpdb->query( "SELECT RELEASE_LOCK('aieo_purchases_rebuild')" );
		}
	}

	
	private static function rebuild_locked() {
		global $wpdb;
		self::$col_cache = array();
		$live = aieo_customer_purchases_table();
		
		
		
		
		if ( ! self::has_col( $live, 'email_hash' ) ) {
			if ( function_exists( 'aieo_subjects_install_schema' ) ) {
				aieo_subjects_install_schema();
			}
			self::$col_cache = array(); 
			if ( ! self::has_col( $live, 'email_hash' ) ) {
				return array( 'ok' => false, 'reason' => 'no_table' );
			}
		}
		$srcs = self::source_tables();
		if ( empty( $srcs ) ) {
			return array( 'ok' => false, 'reason' => 'no_source' );
		}

		$stag = $live . '_staging';
		
		$wpdb->query( "DROP TABLE IF EXISTS `{$stag}`" );
		
		if ( false === $wpdb->query( "CREATE TABLE `{$stag}` LIKE `{$live}`" ) ) {
			return array( 'ok' => false, 'reason' => 'staging_create_failed' );
		}

		$rows      = 0;
		$processed = array(); 
		foreach ( self::$kinds as $kind => $cols ) {
			$union = self::build_union( $srcs, $cols );
			if ( '' === $union ) {
				continue; 
			}
			$processed[] = $kind;
			
			
			
			$n = $wpdb->query(
				"INSERT INTO `{$stag}` (email_hash, email, kind, ref_id, first_at, last_at, times)
				 SELECT MD5(u.eml), MIN(u.eml), '{$kind}', u.ref,
				        MIN(u.OrderDate), MAX(u.OrderDate), COUNT(DISTINCT u.OrderId)
				 FROM ( {$union} ) u
				 GROUP BY MD5(u.eml), u.ref"
			);
			if ( false === $n ) {
				
				$wpdb->query( "DROP TABLE IF EXISTS `{$stag}`" );
				return array( 'ok' => false, 'reason' => 'insert_failed', 'kind' => $kind );
			}
			$rows += (int) $n;
		}

		
		
		
		$pid_union = self::build_pid_union( $srcs );
		if ( '' !== $pid_union ) {
			foreach ( self::term_kinds() as $kind => $tax ) {
				if ( '' === $tax || ! taxonomy_exists( $tax ) ) {
					continue; 
				}
				$processed[] = $kind;
				$tax_sql = esc_sql( $tax );
				
				
				
				$n = $wpdb->query(
					"INSERT INTO `{$stag}` (email_hash, email, kind, ref_id, first_at, last_at, times)
					 SELECT MD5(u.eml), MIN(u.eml), '{$kind}', tt.term_id,
					        MIN(u.OrderDate), MAX(u.OrderDate), COUNT(DISTINCT u.OrderId)
					 FROM ( {$pid_union} ) u
					 JOIN {$wpdb->term_relationships} tr ON tr.object_id = u.pid
					 JOIN {$wpdb->term_taxonomy} tt ON tt.term_taxonomy_id = tr.term_taxonomy_id AND tt.taxonomy = '{$tax_sql}'
					 GROUP BY MD5(u.eml), tt.term_id"
				);
				if ( false === $n ) {
					
					$wpdb->query( "DROP TABLE IF EXISTS `{$stag}`" );
					return array( 'ok' => false, 'reason' => 'insert_failed', 'kind' => $kind );
				}
				$rows += (int) $n;
			}
		}

		
		$old = $live . '_old';
		
		$wpdb->query( "DROP TABLE IF EXISTS `{$old}`" );
		
		if ( false === $wpdb->query( "RENAME TABLE `{$live}` TO `{$old}`, `{$stag}` TO `{$live}`" ) ) {
			
			$wpdb->query( "DROP TABLE IF EXISTS `{$stag}`" );
			return array( 'ok' => false, 'reason' => 'swap_failed' );
		}
		
		$wpdb->query( "DROP TABLE IF EXISTS `{$old}`" );
		update_option( 'aieo_customer_purchases_built_at', gmdate( 'Y-m-d H:i:s' ), false );
		
		
		
		
		update_option( 'aieo_customer_purchases_kinds', array_values( array_unique( $processed ) ), false );
		return array( 'ok' => true, 'rows' => $rows, 'sources' => count( $srcs ) );
	}

	
	public static function ready_kinds() {
		return (array) get_option( 'aieo_customer_purchases_kinds', array() );
	}

	
	private static function build_union( $srcs, $cols ) {
		$parts = array();
		foreach ( $srcs as $s ) {
			$has_status = self::has_col( $s, 'OrderStatus' );
			foreach ( $cols as $col ) {
				if ( ! self::has_col( $s, $col ) ) {
					continue; 
				}
				$where = "CustomerEmail <> '' AND {$col} > 0";
				if ( $has_status ) {
					$where .= ' AND OrderStatus IN ' . self::PURCHASE_STATUSES;
				}
				
				$parts[] = "SELECT LOWER(TRIM(CustomerEmail)) AS eml, OrderId, OrderDate, {$col} AS ref FROM `{$s}` WHERE {$where}";
			}
		}
		return implode( ' UNION ALL ', $parts );
	}

	
	private static function build_pid_union( $srcs ) {
		$parts = array();
		foreach ( $srcs as $s ) {
			if ( ! self::has_col( $s, 'ProductId' ) ) {
				continue;
			}
			$pid = self::has_col( $s, 'ParentProductId' )
				? 'COALESCE(NULLIF(ParentProductId,0), ProductId)'
				: 'ProductId';
			$where = "CustomerEmail <> '' AND {$pid} > 0";
			if ( self::has_col( $s, 'OrderStatus' ) ) {
				$where .= ' AND OrderStatus IN ' . self::PURCHASE_STATUSES;
			}
			$parts[] = "SELECT LOWER(TRIM(CustomerEmail)) AS eml, OrderId, OrderDate, {$pid} AS pid FROM `{$s}` WHERE {$where}";
		}
		return implode( ' UNION ALL ', $parts );
	}

	
	public static function source_tables() {
		global $wpdb;
		$candidates = array();
		$temp = $wpdb->prefix . 'aieo_temp_orders';
		if ( self::table_exists( $temp ) ) {
			$candidates[] = $temp;
		}
		
		
		$like = $wpdb->esc_like( $wpdb->prefix . 'aieo_' ) . '20%' . $wpdb->esc_like( '_orders' );
		foreach ( (array) $wpdb->get_col( $wpdb->prepare( 'SHOW TABLES LIKE %s', $like ) ) as $tt ) {
			$candidates[] = (string) $tt;
		}
		$out = array();
		foreach ( array_unique( $candidates ) as $tt ) {
			$ok = true;
			foreach ( self::$need as $c ) {
				if ( ! self::has_col( $tt, $c ) ) {
					$ok = false;
					break;
				}
			}
			if ( $ok ) {
				$out[] = $tt;
			}
		}
		return $out;
	}

	
	public static function has_purchased( $email, $kind, $ref_ids ) {
		global $wpdb;
		$email = (string) $email;
		$kind  = (string) $kind;
		if ( '' === trim( $email ) || ! in_array( $kind, self::all_kinds(), true ) ) {
			return false;
		}
		$ids = array_values( array_filter( array_map( 'intval', (array) $ref_ids ) ) );
		if ( empty( $ids ) ) {
			return false;
		}
		$t = aieo_customer_purchases_table();
		if ( ! self::table_exists( $t ) ) {
			return false;
		}
		$in = implode( ',', $ids );
		
		return (int) $wpdb->get_var( $wpdb->prepare(
			"SELECT 1 FROM `{$t}` WHERE email_hash = MD5(LOWER(TRIM(%s))) AND kind = %s AND ref_id IN ({$in}) LIMIT 1",
			$email,
			$kind
		) ) === 1;
	}

	
	private static function has_col( $t, $c ) {
		global $wpdb;
		if ( ! isset( self::$col_cache[ $t ] ) ) {
			if ( ! self::table_exists( $t ) ) {
				self::$col_cache[ $t ] = array();
			} else {
				$map = array();
				
				foreach ( (array) $wpdb->get_col( "SHOW COLUMNS FROM `{$t}`" ) as $col ) {
					$map[ (string) $col ] = true;
				}
				self::$col_cache[ $t ] = $map;
			}
		}
		return isset( self::$col_cache[ $t ][ $c ] );
	}

	private static function table_exists( $t ) {
		global $wpdb;
		return (string) $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $t ) ) === $t;
	}
}