<?php

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

class AIEO_Remote_Export_Manager {

	const SETTINGS_OPTION = 'aieo_remote_export_settings';

	
	public static function exportable_tables() {
		return array(
			'aieo_addcart_events'        => __( 'Attribution: cart-add events', 'ai-eshop-optimizer' ),
			'aieo_reco_events'           => __( 'Reco impressions / clicks', 'ai-eshop-optimizer' ),
			'aieo_sessions_statics'      => __( 'Session metadata (1 row per session)', 'ai-eshop-optimizer' ),
			'aieo_sessions'              => __( 'Raw session events', 'ai-eshop-optimizer' ),
			'aieo_engagement_daily'      => __( 'Rollup: engagement_daily', 'ai-eshop-optimizer' ),
			'aieo_attribution_daily'     => __( 'Rollup: attribution_daily', 'ai-eshop-optimizer' ),
			'aieo_conversion_buckets'    => __( 'Rollup: conversion_buckets', 'ai-eshop-optimizer' ),
			'aieo_blog_engagement_daily' => __( 'Rollup: blog_engagement_daily', 'ai-eshop-optimizer' ),
			'aieo_cart_removal_daily'    => __( 'Rollup: cart_removal_daily', 'ai-eshop-optimizer' ),
		);
	}

	
	public static function local_table( $slug ) {
		global $wpdb;
		return $wpdb->prefix . preg_replace( '/[^a-zA-Z0-9_]/', '', (string) $slug );
	}

	
	public static function paging_key_for( $slug ) {
		switch ( $slug ) {
			case 'aieo_addcart_events':
			case 'aieo_reco_events':
				return array( 'column' => 'id', 'ts_column' => 'ts_added', 'strategy' => 'id' );
			case 'aieo_sessions':
				return array( 'column' => 'id', 'ts_column' => 'timestamp', 'strategy' => 'id' );
			case 'aieo_sessions_statics':
				return array( 'column' => 'id', 'ts_column' => 'updated_at', 'strategy' => 'id' );
			default:
				
				return array( 'column' => 'date', 'ts_column' => 'created_at', 'strategy' => 'date' );
		}
	}

	
	
	

	public static function default_settings() {
		return array(
			'connection_id'         => 0,
			'tables'                => array(),
			'frequency'             => 'hourly',
			'batch_size'            => 1000,
			'purge_only_on_success' => true,
		);
	}

	public static function get_settings() {
		$saved = get_option( self::SETTINGS_OPTION, array() );
		if ( ! is_array( $saved ) ) { $saved = array(); }
		return wp_parse_args( $saved, self::default_settings() );
	}

	public static function save_settings( array $settings ) {
		$defaults = self::default_settings();
		$clean    = array(
			'connection_id'         => (int) ( isset( $settings['connection_id'] ) ? $settings['connection_id'] : $defaults['connection_id'] ),
			'tables'                => array(),
			'frequency'             => isset( $settings['frequency'] ) && in_array( $settings['frequency'], array( '15min', 'hourly', 'daily' ), true ) ? $settings['frequency'] : 'hourly',
			'batch_size'            => max( 50, min( 50000, (int) ( isset( $settings['batch_size'] ) ? $settings['batch_size'] : $defaults['batch_size'] ) ) ),
			'purge_only_on_success' => ! empty( $settings['purge_only_on_success'] ),
		);
		if ( isset( $settings['tables'] ) && is_array( $settings['tables'] ) ) {
			$allowed = array_keys( self::exportable_tables() );
			foreach ( $settings['tables'] as $slug ) {
				$slug = sanitize_key( (string) $slug );
				if ( in_array( $slug, $allowed, true ) ) {
					$clean['tables'][] = $slug;
				}
			}
			$clean['tables'] = array_values( array_unique( $clean['tables'] ) );
		}
		update_option( self::SETTINGS_OPTION, $clean, false );
		return $clean;
	}

	public static function is_configured() {
		$s = self::get_settings();
		return ( (int) $s['connection_id'] > 0 ) && ! empty( $s['tables'] );
	}

	
	
	

	public static function get_last_exported_state( $table_slug, $connection_id = null ) {
		global $wpdb;
		if ( $connection_id === null ) {
			$s             = self::get_settings();
			$connection_id = (int) $s['connection_id'];
		}
		$state_table = aieo_remote_export_state_table();
		$row         = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT connection_id, table_name, last_exported_id, last_exported_ts, last_run_ts, last_status, last_error
				 FROM {$state_table}
				 WHERE connection_id = %d AND table_name = %s",
				(int) $connection_id,
				$table_slug
			),
			ARRAY_A
		);
		if ( ! $row ) {
			return array(
				'connection_id'    => (int) $connection_id,
				'table_name'       => $table_slug,
				'last_exported_id' => 0,
				'last_exported_ts' => null,
				'last_run_ts'      => null,
				'last_status'      => '',
				'last_error'       => null,
			);
		}
		$row['last_exported_id'] = (int) $row['last_exported_id'];
		return $row;
	}

	public static function mark_exported( $table_slug, $max_id, $max_ts, $connection_id = null, $status = 'ok', $error = null ) {
		global $wpdb;
		if ( $connection_id === null ) {
			$s             = self::get_settings();
			$connection_id = (int) $s['connection_id'];
		}
		$state_table = aieo_remote_export_state_table();
		$existing    = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT 1 FROM {$state_table} WHERE connection_id = %d AND table_name = %s",
				(int) $connection_id,
				$table_slug
			)
		);
		$now = current_time( 'mysql', true );
		$data = array(
			'connection_id'    => (int) $connection_id,
			'table_name'       => $table_slug,
			'last_exported_id' => max( 0, (int) $max_id ),
			'last_exported_ts' => $max_ts ? $max_ts : null,
			'last_run_ts'      => $now,
			'last_status'      => substr( (string) $status, 0, 32 ),
			'last_error'       => $error ? substr( (string) $error, 0, 1000 ) : null,
		);
		if ( $existing ) {
			
			if ( $status !== 'ok' ) {
				unset( $data['last_exported_id'], $data['last_exported_ts'] );
			}
			$wpdb->update(
				$state_table,
				$data,
				array(
					'connection_id' => (int) $connection_id,
					'table_name'    => $table_slug,
				)
			);
		} else {
			$wpdb->insert( $state_table, $data );
		}
	}

	
	
	

	
	public static function can_purge_through( $table_slug, $ts_to_delete ) {
		$s = self::get_settings();
		if ( empty( $s['connection_id'] ) || empty( $s['tables'] ) ) {
			return true; 
		}
		if ( ! in_array( $table_slug, (array) $s['tables'], true ) ) {
			return true; 
		}
		if ( empty( $s['purge_only_on_success'] ) ) {
			return true; 
		}
		$state = self::get_last_exported_state( $table_slug, (int) $s['connection_id'] );
		if ( empty( $state['last_exported_ts'] ) ) {
			return false; 
		}
		
		return strtotime( $state['last_exported_ts'] . ' UTC' ) >= strtotime( $ts_to_delete . ' UTC' );
	}

	
	
	

	
	public static function ensure_remote_schema( $table_slug, $connection_id = null ) {
		if ( $connection_id === null ) {
			$s             = self::get_settings();
			$connection_id = (int) $s['connection_id'];
		}
		$remote = AIEO_ERP::for_connection_id( (int) $connection_id );
		if ( is_wp_error( $remote ) ) { return $remote; }

		$remote_db_name = $remote->dbname;
		$exists         = $remote->get_var(
			$remote->prepare(
				'SELECT 1 FROM information_schema.TABLES WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s LIMIT 1',
				$remote_db_name,
				$table_slug
			)
		);
		if ( $exists ) { return true; }

		
		global $wpdb;
		$local_table = self::local_table( $table_slug );
		$create      = $wpdb->get_row( "SHOW CREATE TABLE {$local_table}", ARRAY_N );
		if ( empty( $create[1] ) ) {
			
			return new WP_Error( 'aieo_remote_export_no_local_table', sprintf( __( 'Local table %s not found.', 'ai-eshop-optimizer' ), $table_slug ) );
		}
		$create_sql = (string) $create[1];

		
		
		
		
		$create_sql = preg_replace(
			'/^CREATE TABLE `?[^`(\s]+`?\s*\(/',
			'CREATE TABLE `' . $table_slug . '` (',
			$create_sql,
			1
		);

		
		
		
		
		$lines  = preg_split( '/\r?\n/', $create_sql );
		$keep   = array();
		$idem_cols = array( '`event_id`', '`session_id`', '`id`' );
		foreach ( $lines as $line ) {
			$ltrim = ltrim( $line );
			if ( stripos( $ltrim, 'KEY ' ) === 0 ) {
				
				continue;
			}
			if ( stripos( $ltrim, 'UNIQUE KEY' ) === 0 ) {
				
				$is_idem = false;
				foreach ( $idem_cols as $c ) {
					if ( stripos( $ltrim, $c ) !== false ) { $is_idem = true; break; }
				}
				if ( ! $is_idem ) { continue; }
			}
			$keep[] = $line;
		}
		$create_sql = implode( "\n", $keep );

		
		
		$create_sql = preg_replace( '/\s+AUTO_INCREMENT=\d+/i', '', $create_sql );

		
		
		
		if ( preg_match( '/\)(\s*ENGINE=|\s*DEFAULT CHARSET=|\s*COLLATE=|\s*;|\s*$)/i', $create_sql, $m, PREG_OFFSET_CAPTURE ) ) {
			$pos        = $m[0][1];
			$inject     = ",\n  `exported_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP\n";
			$create_sql = substr( $create_sql, 0, $pos ) . $inject . substr( $create_sql, $pos );
		}

		$res = $remote->query( $create_sql );
		if ( $res === false ) {
			
			return new WP_Error( 'aieo_remote_export_ddl_failed', sprintf( __( 'Remote DDL failed: %s', 'ai-eshop-optimizer' ), $remote->last_error ) );
		}
		return true;
	}

	
	
	

	
	public static function export_pending_rows( $table_slug, $cutoff_ts = null, $batch_size = 1000, $connection_id = null ) {
		global $wpdb;

		if ( $connection_id === null ) {
			$s             = self::get_settings();
			$connection_id = (int) $s['connection_id'];
		}
		$connection_id = (int) $connection_id;
		if ( $connection_id <= 0 ) {
			return new WP_Error( 'aieo_remote_export_no_connection', __( 'No remote connection configured.', 'ai-eshop-optimizer' ) );
		}

		$schema = self::ensure_remote_schema( $table_slug, $connection_id );
		if ( is_wp_error( $schema ) ) {
			self::mark_exported( $table_slug, 0, null, $connection_id, 'error', $schema->get_error_message() );
			return $schema;
		}

		$remote = AIEO_ERP::for_connection_id( $connection_id );
		if ( is_wp_error( $remote ) ) {
			self::mark_exported( $table_slug, 0, null, $connection_id, 'error', $remote->get_error_message() );
			return $remote;
		}

		$local_table = self::local_table( $table_slug );
		$paging      = self::paging_key_for( $table_slug );
		$state       = self::get_last_exported_state( $table_slug, $connection_id );
		$batch_size  = max( 50, min( 50000, (int) $batch_size ) );

		
		if ( $paging['strategy'] === 'id' ) {
			$last_id = (int) $state['last_exported_id'];
			$rows    = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT * FROM {$local_table} WHERE id > %d ORDER BY id ASC LIMIT %d",
					$last_id,
					$batch_size
				),
				ARRAY_A
			);
		} else {
			
			
			
			$watermark = ! empty( $state['last_exported_ts'] ) ? $state['last_exported_ts'] : '1970-01-01 00:00:00';
			$rows      = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT * FROM {$local_table} WHERE created_at >= %s ORDER BY created_at ASC, date ASC LIMIT %d",
					$watermark,
					$batch_size
				),
				ARRAY_A
			);
		}

		if ( empty( $rows ) ) {
			self::mark_exported(
				$table_slug,
				(int) $state['last_exported_id'],
				$state['last_exported_ts'],
				$connection_id,
				'idle',
				null
			);
			return 0;
		}

		$inserted = self::insert_batch_into_remote( $remote, $table_slug, $rows );
		if ( is_wp_error( $inserted ) ) {
			self::mark_exported( $table_slug, (int) $state['last_exported_id'], $state['last_exported_ts'], $connection_id, 'error', $inserted->get_error_message() );
			return $inserted;
		}

		
		$max_id = (int) $state['last_exported_id'];
		$max_ts = $state['last_exported_ts'];
		$ts_col = isset( $paging['ts_column'] ) ? $paging['ts_column'] : 'created_at';
		foreach ( $rows as $r ) {
			if ( $paging['strategy'] === 'id' && isset( $r['id'] ) && (int) $r['id'] > $max_id ) {
				$max_id = (int) $r['id'];
			}
			if ( ! empty( $r[ $ts_col ] ) ) {
				$ts_val = (string) $r[ $ts_col ];
				if ( $max_ts === null || strtotime( $ts_val . ' UTC' ) > strtotime( ( $max_ts ?: '1970-01-01' ) . ' UTC' ) ) {
					$max_ts = $ts_val;
				}
			}
		}

		self::mark_exported( $table_slug, $max_id, $max_ts, $connection_id, 'ok', null );

		return count( $rows );
	}

	
	private static function insert_batch_into_remote( $remote, $table_slug, array $rows ) {
		if ( empty( $rows ) ) { return 0; }
		$columns      = array_keys( reset( $rows ) );
		$col_sql_list = '`' . implode( '`,`', array_map(
			function ( $c ) { return preg_replace( '/[^a-zA-Z0-9_]/', '', (string) $c ); },
			$columns
		) ) . '`';

		$placeholder_row = '(' . implode( ',', array_fill( 0, count( $columns ), '%s' ) ) . ')';
		$placeholders    = array();
		$values          = array();
		foreach ( $rows as $r ) {
			$placeholders[] = $placeholder_row;
			foreach ( $columns as $c ) {
				$values[] = isset( $r[ $c ] ) ? (string) $r[ $c ] : null;
			}
		}

		$sql = 'INSERT IGNORE INTO `' . $table_slug . '` (' . $col_sql_list . ') VALUES ' . implode( ',', $placeholders );
		$prepared = $remote->prepare( $sql, $values );
		$res      = $remote->query( $prepared );
		if ( $res === false ) {
			
			return new WP_Error( 'aieo_remote_export_insert_failed', sprintf( __( 'Remote insert failed: %s', 'ai-eshop-optimizer' ), $remote->last_error ) );
		}
		return (int) $res;
	}

	
	public static function test_export( $table_slug, $sample_size = 10, $connection_id = null ) {
		global $wpdb;
		if ( $connection_id === null ) {
			$s             = self::get_settings();
			$connection_id = (int) $s['connection_id'];
		}
		$connection_id = (int) $connection_id;
		if ( $connection_id <= 0 ) {
			return array( 'ok' => false, 'rows' => 0, 'error' => __( 'No connection selected.', 'ai-eshop-optimizer' ) );
		}

		$schema = self::ensure_remote_schema( $table_slug, $connection_id );
		if ( is_wp_error( $schema ) ) {
			return array( 'ok' => false, 'rows' => 0, 'error' => $schema->get_error_message() );
		}

		$remote = AIEO_ERP::for_connection_id( $connection_id );
		if ( is_wp_error( $remote ) ) {
			return array( 'ok' => false, 'rows' => 0, 'error' => $remote->get_error_message() );
		}

		$local_table = self::local_table( $table_slug );
		$sample_size = max( 1, min( 100, (int) $sample_size ) );

		$rows = $wpdb->get_results(
			$wpdb->prepare( "SELECT * FROM {$local_table} ORDER BY 1 DESC LIMIT %d", $sample_size ),
			ARRAY_A
		);
		if ( empty( $rows ) ) {
			return array( 'ok' => true, 'rows' => 0, 'error' => __( 'Local table is empty — nothing to test.', 'ai-eshop-optimizer' ) );
		}
		$inserted = self::insert_batch_into_remote( $remote, $table_slug, $rows );
		if ( is_wp_error( $inserted ) ) {
			return array( 'ok' => false, 'rows' => 0, 'error' => $inserted->get_error_message() );
		}
		return array( 'ok' => true, 'rows' => (int) $inserted );
	}

	
	
	

	
	public static function dashboard_rows( $connection_id = null ) {
		global $wpdb;
		if ( $connection_id === null ) {
			$s             = self::get_settings();
			$connection_id = (int) $s['connection_id'];
		}
		$connection_id = (int) $connection_id;
		$out = array();
		foreach ( self::exportable_tables() as $slug => $label ) {
			$local_table = self::local_table( $slug );
			$exists      = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $local_table ) ) === $local_table;
			$count       = $exists ? (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$local_table}" ) : 0;
			$state       = self::get_last_exported_state( $slug, $connection_id );
			$lag         = null;
			if ( ! empty( $state['last_exported_ts'] ) ) {
				$lag = max( 0, time() - strtotime( $state['last_exported_ts'] . ' UTC' ) );
			}
			$out[] = array(
				'slug'             => $slug,
				'label'            => $label,
				'local_rows'       => $count,
				'last_exported_id' => (int) $state['last_exported_id'],
				'last_exported_ts' => $state['last_exported_ts'],
				'last_run_ts'      => $state['last_run_ts'],
				'last_status'      => $state['last_status'],
				'last_error'       => $state['last_error'],
				'lag_seconds'      => $lag,
			);
		}
		return $out;
	}

	
	public static function force_reexport_from( $table_slug, $from_ts, $connection_id = null ) {
		global $wpdb;
		if ( $connection_id === null ) {
			$s             = self::get_settings();
			$connection_id = (int) $s['connection_id'];
		}
		$connection_id = (int) $connection_id;
		if ( $connection_id <= 0 ) {
			return new WP_Error( 'aieo_remote_export_no_connection', __( 'No connection configured.', 'ai-eshop-optimizer' ) );
		}
		$paging      = self::paging_key_for( $table_slug );
		$local_table = self::local_table( $table_slug );
		$from_ts     = (string) $from_ts;

		$new_id = 0;
		if ( $paging['strategy'] === 'id' ) {
			$ts_col = $paging['ts_column'];
			$new_id = (int) $wpdb->get_var( $wpdb->prepare(
				"SELECT COALESCE(MAX(id),0) FROM {$local_table} WHERE {$ts_col} < %s",
				$from_ts
			) );
		}

		self::mark_exported( $table_slug, $new_id, $from_ts, $connection_id, 'reset', null );
		return true;
	}
}