<?php

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

class AIEO_Product_Affinity {

	
	const ROLLUP_TTL = 21600; 

	
	const SCHEMA_TTL = 43200; 

	
	const MAX_PARENTS = 2000;

	
	private static $schema = null;

	
	private static $sources = null;

	
	
	

	
	public static function variation_leaders( array $parent_ids, $top = 3 ) {
		global $wpdb;

		$top = max( 1, (int) $top );
		$ids = array();
		foreach ( $parent_ids as $pid ) {
			$pid = (int) $pid;
			if ( $pid > 0 ) {
				$ids[ $pid ] = true;
			}
		}
		$ids = array_keys( $ids );
		if ( empty( $ids ) ) {
			return array();
		}
		
		
		
		
		
		sort( $ids, SORT_NUMERIC );
		if ( count( $ids ) > self::MAX_PARENTS ) {
			$ids = array_slice( $ids, 0, self::MAX_PARENTS );
		}
		$in = implode( ',', array_map( 'intval', $ids ) );

		$key    = 'aieo_pa_varlead_' . md5( $in . '|' . $top . '|' . implode( ',', self::sources() ) );
		$cached = get_transient( $key );
		if ( is_array( $cached ) ) {
			return $cached;
		}

		
		
		
		$dedup = self::dedup_select( array( 'parent_in' => $in ) );
		if ( '' === $dedup ) {
			return array();
		}

		
		
		
		
		
		
		
		
		
		
		
		
		$sql = "SELECT g.pid AS pid, g.vid AS vid,
		               MAX(g.units)   AS units,
		               MAX(g.orders_) AS orders_,
		               MAX(g.buyers)  AS buyers,
		               MAX(pm.meta_value + 0) AS image_id
		        FROM (
		            SELECT d.pid AS pid, d.vid AS vid,
		                   SUM(d.q) AS units,
		                   COUNT(DISTINCT d.oid) AS orders_,
		                   COUNT(DISTINCT CASE WHEN d.epo > 0 THEN CONCAT('e:', d.epo) ELSE CONCAT('m:', d.eml) END) AS buyers
		            FROM ( {$dedup} ) d
		            GROUP BY d.pid, d.vid
		        ) g
		        LEFT JOIN `{$wpdb->postmeta}` pm ON pm.post_id = g.vid AND pm.meta_key = '_thumbnail_id'
		        GROUP BY g.pid, g.vid";

		$rows = self::run( $sql );
		
		
		
		
		if ( false === $rows ) {
			return array();
		}
		if ( empty( $rows ) ) {
			set_transient( $key, array(), self::ROLLUP_TTL );
			return array();
		}

		
		
		$by_parent = array();
		$totals    = array();
		foreach ( $rows as $r ) {
			$pid = (int) $r->pid;
			$u   = (int) $r->units;
			if ( ! isset( $by_parent[ $pid ] ) ) {
				$by_parent[ $pid ] = array();
				$totals[ $pid ]    = 0;
			}
			$by_parent[ $pid ][] = array(
				'product_id'   => $pid,
				'variation_id' => (int) $r->vid,
				'units'        => $u,
				'orders'       => (int) $r->orders_,
				'buyers'       => (int) $r->buyers,
				'unit_share'   => 0.0,
				'image_id'     => (int) $r->image_id, 
			);
			$totals[ $pid ] += $u;
		}

		$out = array();
		foreach ( $by_parent as $pid => $list ) {
			usort(
				$list,
				static function ( $a, $b ) {
					if ( $a['units'] === $b['units'] ) {
						return $b['variation_id'] - $a['variation_id']; 
					}
					return $b['units'] - $a['units'];
				}
			);
			$total = max( 1, (int) $totals[ $pid ] );
			
			
			
			
			
			$n_variations = count( $list );
			$list         = array_slice( $list, 0, $top );
			foreach ( $list as $i => $row ) {
				$list[ $i ]['unit_share']   = round( $row['units'] / $total, 4 );
				$list[ $i ]['n_variations'] = $n_variations;
				$list[ $i ]['parent_units'] = $total;
			}
			$out[ $pid ] = $list;
		}

		set_transient( $key, $out, self::ROLLUP_TTL );
		return $out;
	}

	
	private static function dedup_select( array $opts = array() ) {
		$union = self::line_union( $opts );
		if ( '' === $union ) {
			return '';
		}
		
		
		
		
		
		return "SELECT u.oid AS oid, u.vid AS vid,
		               COALESCE(MIN(u.pp), u.vid) AS pid,
		               MAX(u.epo) AS epo,
		               MIN(u.eml) AS eml,
		               MAX(u.q)   AS q
		        FROM ( {$union} ) u
		        GROUP BY u.oid, u.vid";
	}

	
	private static function line_union( array $opts = array() ) {
		$statuses = class_exists( 'AIEO_Customer_Purchases' )
			? AIEO_Customer_Purchases::PURCHASE_STATUSES
			: "('wc-completed','wc-processing','erp-completed')";
		$parent_in = isset( $opts['parent_in'] ) ? (string) $opts['parent_in'] : '';

		$parts = array();
		foreach ( self::sources() as $t ) {
			if ( ! self::has_col( $t, 'OrderId' ) || ! self::has_col( $t, 'ProductId' ) ) {
				continue;
			}
			$has_pp = self::has_col( $t, 'ParentProductId' );

			
			
			
			
			
			
			
			
			
			
			
			$pp  = $has_pp ? 'MIN(NULLIF(ParentProductId, 0))' : 'NULL';
			$epo = self::has_col( $t, 'EponymousID' ) ? 'MAX(COALESCE(EponymousID, 0))' : '0';
			$eml = self::has_col( $t, 'CustomerEmail' )
				? "MIN(NULLIF(LOWER(TRIM(CustomerEmail)), ''))"
				: 'NULL';
			$qty = self::has_col( $t, 'PrimaryQty' ) ? 'SUM(COALESCE(PrimaryQty, 1))' : 'COUNT(*)';

			
			
			
			$where = 'ProductId > 0 AND OrderId > 0';
			if ( self::has_col( $t, 'OrderStatus' ) ) {
				$where .= ' AND OrderStatus IN ' . $statuses;
			}
			if ( '' !== $parent_in ) {
				
				
				
				
				
				
				
				
				
				
				$where .= $has_pp
					? " AND ( ParentProductId IN ({$parent_in})"
						. " OR ( ParentProductId IS NULL AND ProductId IN ({$parent_in}) )"
						. " OR ( ParentProductId = 0 AND ProductId IN ({$parent_in}) ) )"
					: " AND ProductId IN ({$parent_in})";
			}

			
			
			$parts[] = "SELECT OrderId AS oid, ProductId AS vid, {$pp} AS pp, {$epo} AS epo, {$eml} AS eml, {$qty} AS q
			            FROM `{$t}` WHERE {$where} GROUP BY OrderId, ProductId";
		}
		return implode( ' UNION ALL ', $parts );
	}

	
	
	

	
	private static function sources() {
		if ( null !== self::$sources ) {
			return self::$sources;
		}
		$key = 'aieo_pa_srcs';
		$hit = get_transient( $key );
		if ( is_array( $hit ) && $hit ) {
			
			
			
			
			
			
			
			if ( self::tables_exist( $hit ) ) {
				self::$sources = $hit;
				return self::$sources;
			}
			delete_transient( $key );
		}
		self::$sources = array();
		if ( class_exists( 'AIEO_Customer_Purchases' ) && method_exists( 'AIEO_Customer_Purchases', 'source_tables' ) ) {
			foreach ( (array) AIEO_Customer_Purchases::source_tables() as $t ) {
				$t = (string) $t;
				
				
				
				if ( '' !== $t && preg_match( '/^[A-Za-z0-9_]+$/', $t ) ) {
					self::$sources[] = $t;
				}
			}
		}
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		self::$sources = self::newest_only( self::$sources );

		
		
		
		
		if ( ! empty( self::$sources ) ) {
			set_transient( $key, self::$sources, self::SCHEMA_TTL );
		}
		return self::$sources;
	}

	
	
	private static function tables_exist( array $tables ) {
		global $wpdb;
		$tables = array_values( array_filter( array_map( 'strval', $tables ) ) );
		if ( ! $tables ) {
			return false;
		}
		$ph = implode( ',', array_fill( 0, count( $tables ), '%s' ) );
		
		$found = (int) $wpdb->get_var( $wpdb->prepare(
			"SELECT COUNT(*) FROM information_schema.tables
			  WHERE table_schema = DATABASE() AND table_name IN ({$ph})",
			$tables
		) );
		return $found === count( $tables );
	}

	private static function newest_only( array $all ) {
		global $wpdb;
		if ( count( $all ) <= 1 ) {
			return $all;
		}
		$working = $wpdb->prefix . 'aieo_temp_orders';
		$best    = '';
		$best_n  = -1;
		foreach ( $all as $t ) {
			
			$n = (int) $wpdb->get_var( "SELECT COUNT(*) FROM `{$t}`" );
			if ( $n > $best_n || ( $n === $best_n && $t === $working ) ) {
				$best_n = $n;
				$best   = $t;
			}
		}
		return ( '' !== $best ) ? array( $best ) : array();
	}

	
	private static function schema() {
		global $wpdb;
		if ( null !== self::$schema ) {
			return self::$schema;
		}
		$srcs = self::sources();
		$key  = 'aieo_pa_schema_' . md5( implode( ',', $srcs ) );
		$hit  = get_transient( $key );
		if ( is_array( $hit ) ) {
			self::$schema = $hit;
			return self::$schema;
		}
		$map = array();
		if ( empty( $srcs ) ) {
			self::$schema = $map;
			return self::$schema; 
		}
		foreach ( $srcs as $t ) {
			$map[ $t ] = array();
		}
		
		
		$in  = "'" . implode( "','", array_map( 'esc_sql', $srcs ) ) . "'";
		$hid = $wpdb->hide_errors();
		
		$rows = $wpdb->get_results(
			"SELECT TABLE_NAME AS t, COLUMN_NAME AS c
			 FROM information_schema.COLUMNS
			 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME IN ({$in})"
		);
		if ( $hid ) {
			$wpdb->show_errors();
		}
		$found = 0;
		foreach ( (array) $rows as $r ) {
			$tn = (string) $r->t;
			if ( isset( $map[ $tn ] ) ) {
				$map[ $tn ][ (string) $r->c ] = true;
				$found++;
			}
		}
		self::$schema = $map;
		
		
		
		
		
		
		if ( $found > 0 ) {
			set_transient( $key, $map, self::SCHEMA_TTL );
		}
		return self::$schema;
	}

	private static function has_col( $t, $c ) {
		$s = self::schema();
		return isset( $s[ $t ][ $c ] );
	}

	
	private static function run( $sql ) {
		global $wpdb;
		$hid  = $wpdb->hide_errors();
		$rows = $wpdb->get_results( $sql ); 
		$err  = (string) $wpdb->last_error;
		if ( $hid ) {
			$wpdb->show_errors();
		}
		if ( '' !== $err ) {
			return false;
		}
		return is_array( $rows ) ? $rows : array();
	}

}