<?php

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

class AIEO_DMM_Contributors {

	const SCHEMA_OPT = 'aieo_contributor_rules_schema';
	const SCHEMA_VER = '2026-07-30a';

	
	const MIN_CONTAINS = 4;

	
	public static function match_fields() {
		return array( 'campaign', 'content', 'term', 'medium', 'source' );
	}

	public static function table() {
		global $wpdb;
		return $wpdb->prefix . 'aieo_dmm_contributor_rules';
	}

	public static function ensure_schema() {
		global $wpdb;
		if ( get_option( self::SCHEMA_OPT ) === self::SCHEMA_VER ) { return true; }
		if ( get_transient( self::SCHEMA_OPT . '_retry' ) ) { return false; }
		set_transient( self::SCHEMA_OPT . '_retry', 1, HOUR_IN_SECONDS );
		if ( ! AIEO_Schema_Lock::acquire( self::SCHEMA_OPT ) ) {
			return get_option( self::SCHEMA_OPT ) === self::SCHEMA_VER;
		}

		$t  = self::table();
		$ok = true;
		if ( (string) $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $t ) ) !== $t ) {
			$collation = (string) $wpdb->get_var( $wpdb->prepare(
				'SELECT TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s',
				$wpdb->posts
			) );
			$charset = '' !== $collation
				? 'DEFAULT CHARACTER SET ' . strtok( $collation, '_' ) . ' COLLATE ' . $collation
				: 'DEFAULT CHARACTER SET utf8mb4';

			$ok = ( false !== $wpdb->query(
				"CREATE TABLE IF NOT EXISTS `{$t}` (
					`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
					`user_id` BIGINT UNSIGNED NOT NULL,
					`match_field` VARCHAR(12) NOT NULL,
					`match_type` VARCHAR(12) NOT NULL DEFAULT 'contains',
					`match_value` VARCHAR(191) NOT NULL,
					`match_canonical` VARCHAR(191) NOT NULL,
					`enabled` TINYINT(1) NOT NULL DEFAULT 1,
					`priority` SMALLINT NOT NULL DEFAULT 0,
					`starts_at` DATETIME NULL,
					`ends_at` DATETIME NULL,
					`label` VARCHAR(190) NULL,
					`note` TEXT NULL,
					`created_by` BIGINT UNSIGNED NOT NULL DEFAULT 0,
					`created_at` DATETIME NOT NULL,
					`updated_at` DATETIME NOT NULL,
					PRIMARY KEY (`id`),
					UNIQUE KEY `uq_rule` (`match_field`, `match_type`, `match_canonical`),
					KEY `idx_live` (`enabled`, `priority`),
					KEY `idx_user` (`user_id`)
				) ENGINE=InnoDB {$charset}"
			) ) && $ok;
		}
		if ( $ok ) {
			update_option( self::SCHEMA_OPT, self::SCHEMA_VER, false );
			delete_transient( self::SCHEMA_OPT . '_retry' );
		}
		return $ok;
	}

	
	
	

	
	public static function save_rule( array $r ) {
		global $wpdb;
		if ( ! self::ensure_schema() ) { return array( 'ok' => false, 'message' => 'schema unavailable' ); }

		$user_id = isset( $r['user_id'] ) ? (int) $r['user_id'] : 0;
		$field   = isset( $r['match_field'] ) ? (string) $r['match_field'] : 'campaign';
		$type    = isset( $r['match_type'] ) ? (string) $r['match_type'] : 'contains';
		$value   = isset( $r['match_value'] ) ? (string) $r['match_value'] : '';

		if ( $user_id <= 0 || ! get_userdata( $user_id ) ) {
			return array( 'ok' => false, 'message' => __( 'Pick an existing user.', 'ai-eshop-optimizer' ) );
		}
		if ( ! in_array( $field, self::match_fields(), true ) ) {
			return array( 'ok' => false, 'message' => __( 'Unknown match field.', 'ai-eshop-optimizer' ) );
		}
		if ( ! in_array( $type, array( 'contains', 'exact', 'starts_with' ), true ) ) {
			return array( 'ok' => false, 'message' => __( 'Unknown match type.', 'ai-eshop-optimizer' ) );
		}

		$canon = AIEO_DMM_Conversions_Registry::fold( $value );
		if ( '' === $canon ) {
			return array( 'ok' => false, 'message' => __( 'Empty value.', 'ai-eshop-optimizer' ) );
		}
		
		
		if ( 'contains' === $type && mb_strlen( $canon ) < self::MIN_CONTAINS ) {
			return array(
				'ok'      => false,
				
				'message' => sprintf( __( 'A "contains" value needs at least %d characters, or it matches everything.', 'ai-eshop-optimizer' ), self::MIN_CONTAINS ),
			);
		}
		$reject = AIEO_DMM_Conversions_Registry::rejection_reason( $canon );
		if ( '' !== $reject ) {
			
			return array( 'ok' => false, 'message' => sprintf( __( 'That value is not creditable (%s).', 'ai-eshop-optimizer' ), $reject ) );
		}

		$t   = self::table();
		$now = gmdate( 'Y-m-d H:i:s' );
		$res = $wpdb->query( $wpdb->prepare(
			"INSERT INTO `{$t}`
				(user_id, match_field, match_type, match_value, match_canonical, enabled, priority, label, note, created_by, created_at, updated_at)
			 VALUES (%d, %s, %s, %s, %s, 1, %d, %s, %s, %d, %s, %s)
			 ON DUPLICATE KEY UPDATE
				user_id = VALUES(user_id), enabled = 1, priority = VALUES(priority),
				label = VALUES(label), note = VALUES(note), updated_at = VALUES(updated_at)",
			$user_id, $field, $type, substr( $value, 0, 191 ), substr( $canon, 0, 191 ),
			isset( $r['priority'] ) ? (int) $r['priority'] : 0,
			isset( $r['label'] ) ? substr( (string) $r['label'], 0, 190 ) : null,
			isset( $r['note'] ) ? (string) $r['note'] : null,
			get_current_user_id(), $now, $now
		) );

		return array(
			'ok'      => false !== $res,
			'message' => false !== $res
				? __( 'Saved. Every matching conversion, past and future, credits to this user from the next page load.', 'ai-eshop-optimizer' )
				: __( 'Database error.', 'ai-eshop-optimizer' ),
		);
	}

	public static function delete_rule( $id ) {
		global $wpdb;
		return false !== $wpdb->delete( self::table(), array( 'id' => (int) $id ), array( '%d' ) );
	}

	public static function rules() {
		global $wpdb;
		if ( ! self::ensure_schema() ) { return array(); }
		$t = self::table();
		return (array) $wpdb->get_results( "SELECT * FROM `{$t}` ORDER BY priority DESC, id ASC", ARRAY_A );
	}

	
	
	

	
	private static function credit_join() {
		$rt = self::table();

		
		$field_rank = array();
		foreach ( self::match_fields() as $i => $f ) {
			$field_rank[] = "WHEN r.match_field = '" . esc_sql( $f ) . "' THEN " . ( $i + 1 );
		}
		$field_rank = 'CASE ' . implode( ' ', $field_rank ) . ' ELSE 99 END';

		
		$val = "CASE r.match_field
					WHEN 'campaign' THEN c.canon_campaign
					WHEN 'content'  THEN c.canon_content
					WHEN 'term'     THEN c.canon_term
					WHEN 'medium'   THEN c.canon_medium
					WHEN 'source'   THEN c.canon_source
					ELSE '' END";

		return "
			LEFT JOIN (
				SELECT x.conversion_id, x.user_id, x.rule_id
				  FROM (
					SELECT c.id AS conversion_id, r.user_id, r.id AS rule_id,
						   ROW_NUMBER() OVER (
							   PARTITION BY c.id
							   ORDER BY {$field_rank},
										CASE r.match_type WHEN 'exact' THEN 1 WHEN 'starts_with' THEN 2 ELSE 3 END,
										r.priority DESC, r.id ASC
						   ) AS rn
					  FROM {CONV} c
					  JOIN `{$rt}` r
						ON r.enabled = 1
					   AND ( r.starts_at IS NULL OR c.occurred_at >= r.starts_at )
					   AND ( r.ends_at   IS NULL OR c.occurred_at <= r.ends_at )
					   AND (
							( r.match_type = 'exact'       AND {$val} = r.match_canonical )
						 OR ( r.match_type = 'starts_with' AND {$val} LIKE CONCAT(REPLACE(REPLACE(r.match_canonical,'\\\\','\\\\\\\\'),'%','\\\\%'), '%') )
						 OR ( r.match_type = 'contains'    AND {$val} LIKE CONCAT('%', REPLACE(REPLACE(r.match_canonical,'\\\\','\\\\\\\\'),'%','\\\\%'), '%') )
					   )
					 WHERE {$val} <> ''
				  ) x
				 WHERE x.rn = 1
			) cr ON cr.conversion_id = c.id";
	}

	
	public static function leaderboard( $since, $until ) {
		global $wpdb;
		if ( ! self::ensure_schema() ) { return array(); }
		$conv = AIEO_DMM_Conversions_Registry::table();
		$join = str_replace( '{CONV}', "`{$conv}`", self::credit_join() );

		$sql = $wpdb->prepare(
			"SELECT cr.user_id,
					COUNT(*)                                                        AS conversions,
					SUM(c.conversion_type = 'order')                                AS orders,
					SUM(c.conversion_type = 'goal')                                 AS leads,
					ROUND(SUM(CASE WHEN c.conversion_type = 'order' THEN c.conversion_value ELSE 0 END), 2) AS revenue,
					MAX(c.occurred_at)                                              AS last_seen,
					COUNT(DISTINCT c.canon_campaign)                                AS campaigns
			   FROM `{$conv}` c
			   {$join}
			  WHERE c.occurred_at BETWEEN %s AND %s
				AND cr.user_id IS NOT NULL
			  GROUP BY cr.user_id
			  ORDER BY revenue DESC, conversions DESC",
			$since, $until
		);
		return (array) $wpdb->get_results( $sql, ARRAY_A );
	}

	
	public static function reconcile( $since, $until ) {
		global $wpdb;
		$conv = AIEO_DMM_Conversions_Registry::table();
		$join = str_replace( '{CONV}', "`{$conv}`", self::credit_join() );

		$rows = (array) $wpdb->get_results( $wpdb->prepare(
			"SELECT c.canon_campaign, c.canon_source, cr.user_id
			   FROM `{$conv}` c
			   {$join}
			  WHERE c.occurred_at BETWEEN %s AND %s
				AND ( c.canon_campaign <> '' OR c.canon_source <> '' )",
			$since, $until
		), ARRAY_A );

		$out = array( 'credited' => 0, 'unassigned' => 0, 'rejected' => 0, 'total' => count( $rows ) );
		foreach ( $rows as $r ) {
			if ( ! empty( $r['user_id'] ) ) { $out['credited']++; continue; }
			$v = '' !== $r['canon_campaign'] ? $r['canon_campaign'] : $r['canon_source'];
			if ( '' !== AIEO_DMM_Conversions_Registry::rejection_reason( $v ) ) { $out['rejected']++; }
			else { $out['unassigned']++; }
		}
		$out['balanced'] = ( $out['credited'] + $out['unassigned'] + $out['rejected'] ) === $out['total'];
		return $out;
	}

	
	public static function unassigned( $since, $until, $limit = 40 ) {
		global $wpdb;
		if ( ! self::ensure_schema() ) { return array(); }
		$conv = AIEO_DMM_Conversions_Registry::table();
		$join = str_replace( '{CONV}', "`{$conv}`", self::credit_join() );

		$rows = (array) $wpdb->get_results( $wpdb->prepare(
			"SELECT c.canon_campaign AS value,
					COUNT(*) AS conversions,
					SUM(c.conversion_type = 'order') AS orders,
					ROUND(SUM(CASE WHEN c.conversion_type='order' THEN c.conversion_value ELSE 0 END),2) AS revenue,
					MIN(c.occurred_at) AS first_seen, MAX(c.occurred_at) AS last_seen
			   FROM `{$conv}` c
			   {$join}
			  WHERE c.occurred_at BETWEEN %s AND %s
				AND c.canon_campaign <> ''
				AND cr.user_id IS NULL
			  GROUP BY c.canon_campaign
			  ORDER BY revenue DESC, conversions DESC
			  LIMIT %d",
			$since, $until, (int) $limit
		), ARRAY_A );

		foreach ( $rows as &$r ) {
			$r['rejected_reason'] = AIEO_DMM_Conversions_Registry::rejection_reason( $r['value'] );
		}
		return $rows;
	}
}