<?php

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

class AIEO_Contact_Discovery {

	
	const HOOK_CONVERT = 'aieo_contact_convert_batch';

	
	const CONVERT_BATCH = 50;

	
	const OPT_LAST_SCAN = 'aieo_contact_discovery_last_scan';

	
	const OPT_JOB = 'aieo_contact_discovery_job';

	
	const OPT_SETTINGS = 'aieo_contact_discovery_settings';

	
	const META_SOURCE = '_aieo_contact_origin';
	const META_AT     = '_aieo_contact_converted_at';

	
	
	

	
	public static function settings() {
		$s = get_option( self::OPT_SETTINGS, array() );
		return wp_parse_args( is_array( $s ) ? $s : array(), array(
			'conversion_enabled' => false,
			'notify'             => false,
			'sources'            => array(),
		) );
	}

	public static function save_settings( $in ) {
		$s = self::settings();
		if ( isset( $in['conversion_enabled'] ) ) {
			$s['conversion_enabled'] = (bool) $in['conversion_enabled'];
		}
		if ( isset( $in['notify'] ) ) {
			$s['notify'] = (bool) $in['notify'];
		}
		if ( isset( $in['sources'] ) ) {
			$valid         = array_keys( self::sources() );
			$s['sources']  = array_values( array_intersect( $valid, (array) $in['sources'] ) );
		}
		update_option( self::OPT_SETTINGS, $s, false );
		return $s;
	}

	public static function conversion_enabled() {
		$s = self::settings();
		return ! empty( $s['conversion_enabled'] );
	}

	
	public static function sources() {
		global $wpdb;
		$p = $wpdb->prefix;
		$s = array(
			'orders' => array(
				'label'   => __( 'Customers (orders)', 'ai-eshop-optimizer' ),
				'table'   => $p . 'aieo_customer_intelligence',
				'email'   => 'email',
				'user'    => 'wp_user_id',
				'name'    => '',
				'date'    => '',
				'consent' => 'tnp_status',
				'basis'   => 'contract',
				'note'    => __( 'Bought something. The address is on the order.', 'ai-eshop-optimizer' ),
			),
			'competitions' => array(
				'label'   => __( 'Competition entrants', 'ai-eshop-optimizer' ),
				'table'   => $p . 'aieo_competition_participants',
				'email'   => 'email',
				'user'    => 'wp_user_id',
				'name'    => "CONCAT_WS(' ', first_name, last_name)",
				'date'    => 'created_at',
				'consent' => 'status',
				'basis'   => 'explicit',
				'note'    => __( 'Entered a competition and confirmed by double opt-in.', 'ai-eshop-optimizer' ),
			),
			'events' => array(
				'label'   => __( 'Event registrations', 'ai-eshop-optimizer' ),
				'table'   => $p . 'aieo_event_participants',
				'email'   => 'email',
				'user'    => '',
				'name'    => "CONCAT_WS(' ', first_name, last_name)",
				'date'    => 'created_at',
				'consent' => 'rsvp_status',
				'basis'   => 'explicit',
				'note'    => __( 'Registered themselves for an event.', 'ai-eshop-optimizer' ),
			),
			'waitlist' => array(
				'label'   => __( 'Back-in-stock requests', 'ai-eshop-optimizer' ),
				'table'   => $p . 'aieo_waitlist',
				'email'   => 'email',
				'user'    => '',
				'name'    => '',
				'date'    => 'created_at',
				'consent' => 'status',
				'basis'   => 'implied',
				'note'    => __( 'Asked to be told when a product returns. The form has no privacy checkbox, so this is a request to be contacted about one product — not a recorded policy acceptance.', 'ai-eshop-optimizer' ),
			),
			'carts' => array(
				'label'   => __( 'Abandoned & saved carts', 'ai-eshop-optimizer' ),
				'table'   => $p . 'aieo_carts',
				'email'   => 'customer_email',
				'user'    => 'wp_user_id',
				'name'    => '',
				'date'    => 'created_at',
				'consent' => '',
				'basis'   => 'implied',
				'note'    => __( 'Reached checkout and left. The address came from an unfinished order.', 'ai-eshop-optimizer' ),
			),
			'surveys' => array(
				'label'   => __( 'Survey respondents', 'ai-eshop-optimizer' ),
				'table'   => $p . 'aieo_survey_responses',
				'email'   => 'email',
				'user'    => '',
				'name'    => '',
				'date'    => 'created_at',
				'consent' => '',
				'basis'   => 'implied',
				'note'    => __( 'Answered a survey.', 'ai-eshop-optimizer' ),
			),
			'push' => array(
				'label'   => __( 'Identified push subscribers', 'ai-eshop-optimizer' ),
				'table'   => $p . 'aieo_push_subscriptions',
				'email'   => 'identified_email',
				'user'    => 'user_id',
				'name'    => '',
				'date'    => 'identified_at',
				'consent' => 'status',
				'basis'   => 'explicit',
				'note'    => __( 'Gave a verified Google address through One Tap, with personalisation consent granted.', 'ai-eshop-optimizer' ),
			),
		);

		
		$s = apply_filters( 'aieo_contact_discovery_sources', $s );

		
		
		foreach ( array_keys( $s ) as $id ) {
			if ( ! self::table( $s[ $id ]['table'] ) || ! self::column( $s[ $id ]['table'], $s[ $id ]['email'] ) ) {
				unset( $s[ $id ] );
				continue;
			}
			
			foreach ( array( 'user', 'date', 'consent' ) as $k ) {
				if ( ! empty( $s[ $id ][ $k ] ) && ! self::column( $s[ $id ]['table'], $s[ $id ][ $k ] ) ) {
					$s[ $id ][ $k ] = '';
				}
			}
		}
		return $s;
	}

	
	public static function scan() {
		global $wpdb;
		$ci      = $wpdb->prefix . 'aieo_customer_intelligence';
		$has_ci  = self::table( $ci );
		$sources = self::sources();
		$out     = array( 'sources' => array(), 'total' => array(), 'scanned_at' => current_time( 'mysql', true ) );

		foreach ( $sources as $id => $src ) {
			$sel = self::email_expr( $src );
			
			$row = $wpdb->get_row(
				"SELECT COUNT(*) people,
					SUM(" . self::exists_user( 'e.em' ) . ") with_account"
					. ( $has_ci ? ", SUM(" . self::exists_ci( 'e.em' ) . ") known" : ", 0 known" ) . "
				   FROM ( SELECT DISTINCT {$sel} em FROM `{$src['table']}` WHERE `{$src['email']}` <> '' ) e",
				ARRAY_A
			);
			$people = (int) ( $row['people'] ?? 0 );
			$out['sources'][ $id ] = array(
				'label'        => $src['label'],
				'basis'        => $src['basis'],
				'note'         => $src['note'],
				'people'       => $people,
				'with_account' => (int) ( $row['with_account'] ?? 0 ),
				'known'        => (int) ( $row['known'] ?? 0 ),
				'new'          => max( 0, $people - (int) ( $row['with_account'] ?? 0 ) ),
			);
		}

		$union = self::union_sql( array_keys( $sources ) );
		if ( $union ) {
			
			$row = $wpdb->get_row(
				"SELECT COUNT(*) people,
					SUM(" . self::exists_user( 'a.em' ) . ") with_account"
					. ( $has_ci ? ", SUM(" . self::exists_ci( 'a.em' ) . ") known" : ", 0 known" ) . "
				   FROM ( SELECT DISTINCT em FROM ( {$union} ) u0 ) a",
				ARRAY_A
			);
			$people = (int) ( $row['people'] ?? 0 );
			$out['total'] = array(
				'people'       => $people,
				'with_account' => (int) ( $row['with_account'] ?? 0 ),
				'known'        => (int) ( $row['known'] ?? 0 ),
				'new'          => max( 0, $people - (int) ( $row['with_account'] ?? 0 ) ),
			);
		}

		update_option( self::OPT_LAST_SCAN, $out, false );
		return $out;
	}

	
	public static function candidates( $source_ids, $after = '', $limit = 100 ) {
		global $wpdb;
		$union = self::union_sql( $source_ids );
		if ( ! $union ) {
			return array();
		}
		$limit = max( 1, min( 1000, (int) $limit ) );
		
		$rows = $wpdb->get_results( $wpdb->prepare(
			"SELECT a.em email,
				MAX(a.nm) name,
				GROUP_CONCAT(DISTINCT a.src ORDER BY a.src SEPARATOR ',') sources,
				MIN(NULLIF(a.dt, '0000-00-00 00:00:00')) first_seen
			   FROM ( {$union} ) a
			  WHERE a.em > %s AND " . self::no_account( 'a.em' ) . "
			  GROUP BY a.em
			  ORDER BY a.em ASC
			  LIMIT {$limit}",
			(string) $after
		), ARRAY_A );
		return array_map( function ( $r ) {
			return array(
				'email'      => (string) $r['email'],
				'name'       => (string) $r['name'],
				'sources'    => (string) $r['sources'],
				'first_seen' => (string) $r['first_seen'],
			);
		}, (array) $rows );
	}

	
	public static function candidate_count( $source_ids ) {
		global $wpdb;
		$union = self::union_sql( $source_ids );
		if ( ! $union ) {
			return 0;
		}
		
		return (int) $wpdb->get_var(
			"SELECT COUNT(DISTINCT a.em) FROM ( {$union} ) a WHERE " . self::no_account( 'a.em' )
		);
	}

	
	public static function convert( $source_ids, $args = array() ) {
		$args = wp_parse_args( $args, array(
			'dry_run' => true,
			'notify'  => false,
			'after'   => '',
			'limit'   => self::CONVERT_BATCH,
		) );

		
		
		
		if ( ! $args['dry_run'] && ! self::conversion_enabled() ) {
			$args['dry_run'] = true;
		}

		$rows = self::candidates( $source_ids, (string) $args['after'], (int) $args['limit'] );
		$res  = array( 'created' => 0, 'skipped' => 0, 'failed' => 0, 'cursor' => (string) $args['after'], 'done' => false, 'samples' => array() );
		if ( ! $rows ) {
			$res['done'] = true;
			return $res;
		}

		foreach ( $rows as $r ) {
			$res['cursor'] = $r['email'];
			$email         = sanitize_email( $r['email'] );
			if ( ! is_email( $email ) || email_exists( $email ) ) {
				
				
				
				
				$res['skipped']++;
				continue;
			}
			if ( count( $res['samples'] ) < 10 ) {
				$res['samples'][] = array( 'email' => $email, 'name' => $r['name'], 'sources' => $r['sources'] );
			}
			if ( $args['dry_run'] ) {
				$res['created']++;
				continue;
			}

			$uid = self::create_account( $email, $r, (bool) $args['notify'] );
			if ( is_wp_error( $uid ) ) {
				$res['failed']++;
				continue;
			}
			$res['created']++;
		}

		$res['done'] = count( $rows ) < (int) $args['limit'];
		return $res;
	}

	
	private static function create_account( $email, $row, $notify ) {
		$name  = trim( (string) $row['name'] );
		$parts = $name !== '' ? preg_split( '/\s+/', $name, 2 ) : array();
		$first = isset( $parts[0] ) ? $parts[0] : '';
		$last  = isset( $parts[1] ) ? $parts[1] : '';

		if ( function_exists( 'wc_create_new_customer' ) ) {
			
			
			$uid = wc_create_new_customer( $email, '', '', array(
				'first_name' => $first,
				'last_name'  => $last,
			) );
		} else {
			$login = self::unique_login( $email );
			$uid   = wp_insert_user( array(
				'user_login' => $login,
				'user_email' => $email,
				'user_pass'  => wp_generate_password( 24, true, true ),
				'first_name' => $first,
				'last_name'  => $last,
				'role'       => 'customer',
			) );
		}
		if ( is_wp_error( $uid ) || ! $uid ) {
			return is_wp_error( $uid ) ? $uid : new WP_Error( 'aieo_create_failed', 'user insert failed' );
		}

		update_user_meta( $uid, self::META_SOURCE, (string) $row['sources'] );
		update_user_meta( $uid, self::META_AT, current_time( 'mysql', true ) );

		if ( $notify ) {
			wp_new_user_notification( $uid, null, 'user' );
		}

		
		do_action( 'aieo_contact_converted', $uid, $email, (string) $row['sources'] );
		return (int) $uid;
	}

	
	private static function unique_login( $email ) {
		$base = sanitize_user( current( explode( '@', $email ) ), true );
		if ( '' === $base ) {
			$base = 'customer';
		}
		$login = $base;
		$n     = 1;
		while ( username_exists( $login ) ) {
			$login = $base . '-' . ( ++$n );
		}
		return $login;
	}

	
	
	

	
	public static function start_job( $source_ids, $notify = false ) {
		if ( ! self::conversion_enabled() ) {
			return new WP_Error(
				'aieo_conversion_disabled',
				__( 'Turn on “Create accounts for detected contacts” before running a conversion.', 'ai-eshop-optimizer' )
			);
		}
		$job = array(
			'sources' => array_values( (array) $source_ids ),
			'notify'  => (bool) $notify,
			'cursor'  => '',
			'created' => 0,
			'skipped' => 0,
			'failed'  => 0,
			'target'  => self::candidate_count( $source_ids ),
			'started' => current_time( 'mysql', true ),
			'done'    => false,
		);
		update_option( self::OPT_JOB, $job, false );
		self::queue_tick();
		return (int) $job['target'];
	}

	public static function job() {
		$j = get_option( self::OPT_JOB, array() );
		return is_array( $j ) ? $j : array();
	}

	public static function cancel_job() {
		$j = self::job();
		if ( $j ) {
			$j['done']      = true;
			$j['cancelled'] = true;
			update_option( self::OPT_JOB, $j, false );
		}
		if ( function_exists( 'as_unschedule_all_actions' ) ) {
			as_unschedule_all_actions( self::HOOK_CONVERT, array(), 'aieo' );
		}
	}

	private static function queue_tick() {
		if ( function_exists( 'as_schedule_single_action' ) ) {
			as_schedule_single_action( time() + 5, self::HOOK_CONVERT, array(), 'aieo' );
		} else {
			wp_schedule_single_event( time() + 5, self::HOOK_CONVERT );
		}
	}

	
	public static function run_batch() {
		$j = self::job();
		if ( ! $j || ! empty( $j['done'] ) ) {
			return;
		}
		
		
		
		if ( ! self::conversion_enabled() ) {
			$j['done']    = true;
			$j['stopped'] = 'disabled';
			update_option( self::OPT_JOB, $j, false );
			return;
		}
		$res = self::convert( $j['sources'], array(
			'dry_run' => false,
			'notify'  => ! empty( $j['notify'] ),
			'after'   => (string) $j['cursor'],
			'limit'   => self::CONVERT_BATCH,
		) );
		$j['cursor']   = $res['cursor'];
		$j['created'] += $res['created'];
		$j['skipped'] += $res['skipped'];
		$j['failed']  += $res['failed'];
		$j['done']     = (bool) $res['done'];
		update_option( self::OPT_JOB, $j, false );

		if ( ! $j['done'] ) {
			self::queue_tick();
		}
	}

	public static function register_hooks() {
		add_action( self::HOOK_CONVERT, array( __CLASS__, 'run_batch' ) );
	}

	
	
	

	
	private static function email_expr( $src ) {
		return "LOWER(TRIM(`{$src['email']}`)) COLLATE utf8mb4_general_ci";
	}

	
	private static function exists_user( $expr ) {
		global $wpdb;
		return "CASE WHEN EXISTS (SELECT 1 FROM `{$wpdb->users}` u WHERE u.user_email = {$expr}) THEN 1 ELSE 0 END";
	}

	
	private static function no_account( $expr ) {
		global $wpdb;
		return "NOT EXISTS (SELECT 1 FROM `{$wpdb->users}` u WHERE u.user_email = {$expr})";
	}

	private static function exists_ci( $expr ) {
		global $wpdb;
		$ci = $wpdb->prefix . 'aieo_customer_intelligence';
		return "CASE WHEN EXISTS (SELECT 1 FROM `{$ci}` c WHERE c.email = {$expr}) THEN 1 ELSE 0 END";
	}

	
	private static function union_sql( $source_ids ) {
		$sources = self::sources();
		$ids     = array_values( array_intersect( array_keys( $sources ), (array) $source_ids ) );
		$parts   = array();
		foreach ( $ids as $id ) {
			$src  = $sources[ $id ];
			$em   = self::email_expr( $src );
			
			
			
			
			$nm   = ( $src['name'] ? $src['name'] : "''" ) . ' COLLATE utf8mb4_general_ci';
			$dt   = $src['date'] ? "CAST(`{$src['date']}` AS CHAR)" : "''";
			$dt  .= ' COLLATE utf8mb4_general_ci';
			$slug = esc_sql( $id );
			$parts[] = "SELECT DISTINCT {$em} em, {$nm} nm, {$dt} dt, '{$slug}' COLLATE utf8mb4_general_ci src
				FROM `{$src['table']}` WHERE `{$src['email']}` <> '' AND `{$src['email']}` LIKE '%@%'";
		}
		return $parts ? implode( ' UNION ', $parts ) : '';
	}

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

	private static function column( $t, $c ) {
		global $wpdb;
		if ( '' === $c ) {
			return false;
		}
		
		if ( false !== strpos( $c, '(' ) ) {
			return true;
		}
		
		return (bool) $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$t}` LIKE %s", $c ) );
	}
}

if ( defined( 'WP_CLI' ) && WP_CLI ) {

	class AIEO_CLI_Contacts {

		/**
 * @subcommand scan
 */
		public function scan( $args, $assoc ) {
			$r = AIEO_Contact_Discovery::scan();
			$rows = array();
			foreach ( $r['sources'] as $id => $s ) {
				$rows[] = array(
					'source'       => $id,
					'basis'        => $s['basis'],
					'people'       => $s['people'],
					'with_account' => $s['with_account'],
					'no_account'   => $s['new'],
				);
			}
			$t = $r['total'];
			$rows[] = array(
				'source'       => 'TOTAL (deduplicated)',
				'basis'        => '',
				'people'       => $t['people'],
				'with_account' => $t['with_account'],
				'no_account'   => $t['new'],
			);
			WP_CLI\Utils\format_items( 'table', $rows, array( 'source', 'basis', 'people', 'with_account', 'no_account' ) );
		}

		/**
 * @subcommand convert
 */
		public function convert( $args, $assoc ) {
			$ids = isset( $assoc['sources'] )
				? array_map( 'sanitize_key', explode( ',', (string) $assoc['sources'] ) )
				: array_keys( AIEO_Contact_Discovery::sources() );
			$live = ! empty( $assoc['live'] );

			if ( $live && ! AIEO_Contact_Discovery::conversion_enabled() ) {
				WP_CLI::error( 'Account creation is switched off for this shop. Turn on "Create accounts for detected contacts" first (Customer Profiling → Contacts & accounts).' );
			}

			$total = AIEO_Contact_Discovery::candidate_count( $ids );
			WP_CLI::log( sprintf( '%d contacts with no account across: %s', $total, implode( ', ', $ids ) ) );
			if ( ! $live ) {
				WP_CLI::success( 'Dry run — nothing created. Re-run with --live.' );
				return;
			}

			$cursor = '';
			$made   = 0;
			$skip   = 0;
			$fail   = 0;
			$prog   = WP_CLI\Utils\make_progress_bar( 'Creating accounts', max( 1, $total ) );
			do {
				$res = AIEO_Contact_Discovery::convert( $ids, array(
					'dry_run' => false,
					'notify'  => ! empty( $assoc['notify'] ),
					'after'   => $cursor,
					'limit'   => AIEO_Contact_Discovery::CONVERT_BATCH,
				) );
				$cursor = $res['cursor'];
				$made  += $res['created'];
				$skip  += $res['skipped'];
				$fail  += $res['failed'];
				$prog->tick( $res['created'] + $res['skipped'] );
			} while ( ! $res['done'] );
			$prog->finish();
			WP_CLI::success( sprintf( '%d created, %d already had an account, %d failed.', $made, $skip, $fail ) );
		}
	}

	WP_CLI::add_command( 'aieo contacts', 'AIEO_CLI_Contacts' );
}