<?php
/**
 * Traffic analysis persistence and read model.
 *
 * @package Niwa_AI
 */

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

class Niwa_AI_Traffic_Repository {

	public function record_events( $events, $server = array() ) {
		Niwa_AI_Traffic_DB::ensure_schema();

		$events = is_array( $events ) ? array_slice( $events, 0, 20 ) : array();
		$count  = 0;
		foreach ( $events as $event ) {
			$normalized = Niwa_AI_Traffic_Attribution::normalize_event( $event, $server );
			if ( ! $normalized ) {
				continue;
			}
			if ( '' === $normalized['event_id'] ) {
				$normalized['event_id'] = $this->build_event_id( $normalized );
			}
			if ( $this->insert_event( $normalized ) ) {
				$this->upsert_session( $normalized );
				$this->maybe_insert_touchpoint( $normalized );
				$count++;
			}
		}

		return $count;
	}

	public function record_order_conversion( $order ) {
		if ( ! is_object( $order ) || ! method_exists( $order, 'get_id' ) ) {
			return false;
		}

		Niwa_AI_Traffic_DB::ensure_schema();

		$order_id   = (int) $order->get_id();
		$session_id = method_exists( $order, 'get_meta' ) ? (string) $order->get_meta( Niwa_AI_Traffic_Conversion_Tracker::ORDER_SESSION_META, true ) : '';
		if ( '' === $session_id ) {
			$session_id = isset( $_COOKIE[ Niwa_AI_Traffic_Attribution::SESSION_COOKIE ] ) ? sanitize_text_field( wp_unslash( $_COOKIE[ Niwa_AI_Traffic_Attribution::SESSION_COOKIE ] ) ) : '';
		}

		$session = '' !== $session_id ? $this->get_session( $session_id ) : array();
		$attr    = $this->get_order_attribution_meta( $order );
		if ( empty( $attr ) ) {
			$attr = Niwa_AI_Traffic_Attribution::attribution_from_cookie();
		}

		$source   = $session['last_source'] ?? ( $attr['source'] ?? '' );
		$medium   = $session['last_medium'] ?? ( $attr['medium'] ?? '' );
		$campaign = $session['last_campaign'] ?? ( $attr['campaign'] ?? '' );
		$channel  = $session['last_channel'] ?? ( $attr['channel'] ?? '' );
		if ( '' === $channel ) {
			$channel = Niwa_AI_Traffic_Attribution::classify_channel( $source, $medium );
		}

		$total    = method_exists( $order, 'get_total' ) ? (float) $order->get_total() : 0.0;
		$status   = method_exists( $order, 'get_status' ) ? (string) $order->get_status() : '';
		$currency = method_exists( $order, 'get_currency' ) ? (string) $order->get_currency() : '';
		$created  = method_exists( $order, 'get_date_created' ) && $order->get_date_created()
			? gmdate( 'Y-m-d H:i:s', $order->get_date_created()->getTimestamp() )
			: current_time( 'mysql' );

		return $this->insert_conversion(
			array(
				'conversion_id'     => 'order:' . $order_id,
				'conversion_type'   => 'order',
				'session_id'        => substr( sanitize_text_field( $session_id ), 0, 96 ),
				'visitor_id'        => isset( $session['visitor_id'] ) ? (string) $session['visitor_id'] : '',
				'order_id'          => $order_id,
				'occurred_at'       => $created,
				'source'            => sanitize_key( $source ),
				'medium'            => sanitize_key( $medium ),
				'campaign'          => sanitize_text_field( $campaign ),
				'channel'           => sanitize_key( $channel ),
				'landing_page_path' => isset( $session['landing_page_path'] ) ? (string) $session['landing_page_path'] : '',
				'revenue'           => $total,
				'currency'          => sanitize_text_field( $currency ),
				'status'            => sanitize_key( $status ),
				'meta'              => array(
					'attribution_model' => 'last_non_direct',
				),
			)
		);
	}

	public function get_snapshot( $range ) {
		$filters = array(
			'start' => isset( $range['start_mysql'] ) ? (string) $range['start_mysql'] : gmdate( 'Y-m-d H:i:s', time() - DAY_IN_SECONDS ),
			'end'   => isset( $range['end_mysql'] ) ? (string) $range['end_mysql'] : current_time( 'mysql' ),
		);

		return array(
			'available'      => $this->table_exists( Niwa_AI_Traffic_DB::EVENTS ),
			'source'         => 'niwa_traffic_analysis',
			'summary'        => $this->get_summary( $filters ),
			'top_channels'   => $this->get_top_dimension( 'channel', $filters, 5 ),
			'top_campaigns'  => $this->get_top_dimension( 'campaign', $filters, 5 ),
			'top_pages'      => $this->get_top_dimension( 'page', $filters, 5 ),
			'attribution'    => array(
				'default_model' => 'last_non_direct',
				'models'        => array( 'last_non_direct', 'first_touch', 'linear', 'position_based', 'time_decay' ),
			),
		);
	}

	public function get_dashboard_data( $days ) {
		$days    = max( 1, min( 365, (int) $days ) );
		$filters = array(
			'start' => gmdate( 'Y-m-d H:i:s', time() - ( $days * DAY_IN_SECONDS ) ),
			'end'   => current_time( 'mysql' ),
		);

		return array(
			'summary'   => $this->get_summary( $filters ),
			'channels'  => $this->get_top_dimension( 'channel', $filters, 8 ),
			'campaigns' => $this->get_top_dimension( 'campaign', $filters, 8 ),
			'pages'     => $this->get_top_dimension( 'page', $filters, 8 ),
		);
	}

	private function insert_event( array $event ) {
		global $wpdb;
		$table = Niwa_AI_Traffic_DB::table( Niwa_AI_Traffic_DB::EVENTS );
		$meta  = wp_json_encode( $event['meta'] );

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$sql = $wpdb->prepare(
			"INSERT IGNORE INTO {$table}
			(event_id,event_type,session_id,visitor_id,occurred_at,received_at,page_url,page_path,page_title,referrer_url,referrer_host,source,medium,campaign,term,content,click_id_name,click_id_value,channel,device_type,user_agent_hash,ip_hash,value,order_id,meta)
			VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%f,%d,%s)",
			$event['event_id'],
			$event['event_type'],
			$event['session_id'],
			$event['visitor_id'],
			$event['occurred_at'],
			current_time( 'mysql' ),
			$event['page_url'],
			$event['page_path'],
			$event['page_title'],
			$event['referrer_url'],
			$event['referrer_host'],
			$event['source'],
			$event['medium'],
			$event['campaign'],
			$event['term'],
			$event['content'],
			$event['click_id_name'],
			$event['click_id_value'],
			$event['channel'],
			$event['device_type'],
			$event['user_agent_hash'],
			$event['ip_hash'],
			null === $event['value'] ? 0 : $event['value'],
			(int) $event['order_id'],
			$meta
		);

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$result = $wpdb->query( $sql );
		return false !== $result && $result > 0;
	}

	private function upsert_session( array $event ) {
		global $wpdb;
		$table       = Niwa_AI_Traffic_DB::table( Niwa_AI_Traffic_DB::SESSIONS );
		$is_pageview = 'pageview' === $event['event_type'] ? 1 : 0;
		$is_engaged  = in_array( $event['event_type'], array( 'engaged_view', 'scroll_depth', 'blog_read', 'lead', 'chat_message' ), true ) ? 1 : 0;
		$is_direct   = 'direct' === $event['channel'];

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$sql = $wpdb->prepare(
			"INSERT INTO {$table}
			(session_id,visitor_id,first_seen_at,last_seen_at,landing_page_url,landing_page_path,referrer_host,first_source,first_medium,first_campaign,first_channel,last_source,last_medium,last_campaign,last_channel,device_type,pageviews,engaged_events,updated_at)
			VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%d,%d,%s)
			ON DUPLICATE KEY UPDATE
				last_seen_at=VALUES(last_seen_at),
				last_source=IF(%d=1 AND last_source <> '', last_source, VALUES(last_source)),
				last_medium=IF(%d=1 AND last_medium <> '', last_medium, VALUES(last_medium)),
				last_campaign=IF(%d=1 AND last_campaign <> '', last_campaign, VALUES(last_campaign)),
				last_channel=IF(%d=1 AND last_channel <> '', last_channel, VALUES(last_channel)),
				pageviews=pageviews+VALUES(pageviews),
				engaged_events=engaged_events+VALUES(engaged_events),
				updated_at=VALUES(updated_at)",
			$event['session_id'],
			$event['visitor_id'],
			$event['occurred_at'],
			$event['occurred_at'],
			$event['page_url'],
			$event['page_path'],
			$event['referrer_host'],
			$event['source'],
			$event['medium'],
			$event['campaign'],
			$event['channel'],
			$event['source'],
			$event['medium'],
			$event['campaign'],
			$event['channel'],
			$event['device_type'],
			$is_pageview,
			$is_engaged,
			current_time( 'mysql' ),
			$is_direct,
			$is_direct,
			$is_direct,
			$is_direct
		);

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$wpdb->query( $sql );
	}

	private function maybe_insert_touchpoint( array $event ) {
		if ( 'direct' === $event['channel'] || ( '' === $event['source'] && '' === $event['campaign'] ) ) {
			return;
		}

		global $wpdb;
		$table = Niwa_AI_Traffic_DB::table( Niwa_AI_Traffic_DB::TOUCHPOINTS );
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$sql = $wpdb->prepare(
			"INSERT IGNORE INTO {$table}
			(session_id,visitor_id,event_id,occurred_at,event_type,page_path,source,medium,campaign,channel)
			VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",
			$event['session_id'],
			$event['visitor_id'],
			$event['event_id'],
			$event['occurred_at'],
			$event['event_type'],
			$event['page_path'],
			$event['source'],
			$event['medium'],
			$event['campaign'],
			$event['channel']
		);
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$wpdb->query( $sql );
	}

	private function insert_conversion( array $conversion ) {
		global $wpdb;
		$table = Niwa_AI_Traffic_DB::table( Niwa_AI_Traffic_DB::CONVERSIONS );
		$meta  = wp_json_encode( $conversion['meta'] );

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$sql = $wpdb->prepare(
			"INSERT INTO {$table}
			(conversion_id,conversion_type,session_id,visitor_id,order_id,occurred_at,source,medium,campaign,channel,landing_page_path,revenue,currency,status,meta)
			VALUES (%s,%s,%s,%s,%d,%s,%s,%s,%s,%s,%s,%f,%s,%s,%s)
			ON DUPLICATE KEY UPDATE revenue=VALUES(revenue), status=VALUES(status), meta=VALUES(meta)",
			$conversion['conversion_id'],
			$conversion['conversion_type'],
			$conversion['session_id'],
			$conversion['visitor_id'],
			(int) $conversion['order_id'],
			$conversion['occurred_at'],
			$conversion['source'],
			$conversion['medium'],
			$conversion['campaign'],
			$conversion['channel'],
			$conversion['landing_page_path'],
			(float) $conversion['revenue'],
			$conversion['currency'],
			$conversion['status'],
			$meta
		);

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$result = $wpdb->query( $sql );
		if ( false !== $result && '' !== $conversion['session_id'] ) {
			$this->mark_session_converted( $conversion['session_id'], (float) $conversion['revenue'] );
		}
		return false !== $result;
	}

	private function get_order_attribution_meta( $order ) {
		if ( ! is_object( $order ) || ! method_exists( $order, 'get_meta' ) ) {
			return array();
		}

		$raw = $order->get_meta( Niwa_AI_Traffic_Conversion_Tracker::ORDER_ATTR_META, true );
		if ( is_array( $raw ) ) {
			return $raw;
		}

		$decoded = json_decode( (string) $raw, true );
		return is_array( $decoded ) ? $decoded : array();
	}

	private function mark_session_converted( $session_id, $revenue ) {
		global $wpdb;
		$table = Niwa_AI_Traffic_DB::table( Niwa_AI_Traffic_DB::SESSIONS );
		$wpdb->query(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				"UPDATE {$table} SET converted = 1, revenue = GREATEST(revenue, %f), updated_at = %s WHERE session_id = %s",
				(float) $revenue,
				current_time( 'mysql' ),
				$session_id
			)
		);
	}

	private function get_session( $session_id ) {
		global $wpdb;
		$table = Niwa_AI_Traffic_DB::table( Niwa_AI_Traffic_DB::SESSIONS );
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE session_id = %s LIMIT 1", $session_id ), ARRAY_A );
		return is_array( $row ) ? $row : array();
	}

	private function get_summary( array $filters ) {
		global $wpdb;
		if ( ! $this->table_exists( Niwa_AI_Traffic_DB::EVENTS ) ) {
			return $this->empty_summary();
		}
		$events      = Niwa_AI_Traffic_DB::table( Niwa_AI_Traffic_DB::EVENTS );
		$conversions = Niwa_AI_Traffic_DB::table( Niwa_AI_Traffic_DB::CONVERSIONS );

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$row = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT COUNT(*) AS events,
					COUNT(DISTINCT session_id) AS sessions,
					COUNT(DISTINCT visitor_id) AS visitors,
					SUM(CASE WHEN event_type = 'pageview' THEN 1 ELSE 0 END) AS pageviews,
					SUM(CASE WHEN event_type IN ('engaged_view','scroll_depth','blog_read','lead','chat_message') THEN 1 ELSE 0 END) AS engaged_events
				FROM {$events}
				WHERE occurred_at BETWEEN %s AND %s",
				$filters['start'],
				$filters['end']
			),
			ARRAY_A
		);

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$conv = $this->table_exists( Niwa_AI_Traffic_DB::CONVERSIONS )
			? $wpdb->get_row(
				$wpdb->prepare(
					"SELECT COUNT(*) AS conversions,
						COUNT(DISTINCT order_id) AS orders,
						COALESCE(SUM(revenue), 0) AS revenue
					FROM {$conversions}
					WHERE occurred_at BETWEEN %s AND %s
						AND status IN ('processing','completed','on-hold')",
					$filters['start'],
					$filters['end']
				),
				ARRAY_A
			)
			: array();

		return array(
			'events'         => (int) ( $row['events'] ?? 0 ),
			'sessions'       => (int) ( $row['sessions'] ?? 0 ),
			'visitors'       => (int) ( $row['visitors'] ?? 0 ),
			'pageviews'      => (int) ( $row['pageviews'] ?? 0 ),
			'engaged_events' => (int) ( $row['engaged_events'] ?? 0 ),
			'conversions'    => (int) ( $conv['conversions'] ?? 0 ),
			'orders'         => (int) ( $conv['orders'] ?? 0 ),
			'revenue'        => (float) ( $conv['revenue'] ?? 0 ),
			'conversion_rate'=> (int) ( $row['sessions'] ?? 0 ) > 0 ? round( ( (int) ( $conv['conversions'] ?? 0 ) / (int) $row['sessions'] ) * 100, 2 ) : 0.0,
		);
	}

	private function get_top_dimension( $dimension, array $filters, $limit ) {
		global $wpdb;
		if ( ! $this->table_exists( Niwa_AI_Traffic_DB::EVENTS ) ) {
			return array();
		}
		$events = Niwa_AI_Traffic_DB::table( Niwa_AI_Traffic_DB::EVENTS );
		$field  = 'channel';
		if ( 'campaign' === $dimension ) {
			$field = 'campaign';
		} elseif ( 'page' === $dimension ) {
			$field = 'page_path';
		}

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT {$field} AS label,
					MAX(channel) AS channel,
					MAX(source) AS source,
					MAX(medium) AS medium,
					MAX(campaign) AS campaign,
					COUNT(DISTINCT session_id) AS sessions,
					SUM(CASE WHEN event_type = 'pageview' THEN 1 ELSE 0 END) AS pageviews,
					SUM(CASE WHEN event_type IN ('engaged_view','scroll_depth','blog_read','lead','chat_message') THEN 1 ELSE 0 END) AS engaged_events
				FROM {$events}
				WHERE occurred_at BETWEEN %s AND %s AND {$field} <> ''
				GROUP BY {$field}
				ORDER BY sessions DESC, pageviews DESC
				LIMIT %d",
				$filters['start'],
				$filters['end'],
				max( 1, (int) $limit )
			),
			ARRAY_A
		);

		$rows = is_array( $rows ) ? $rows : array();
		if ( empty( $rows ) || ! $this->table_exists( Niwa_AI_Traffic_DB::CONVERSIONS ) ) {
			return $rows;
		}

		$conversion_field = 'page' === $dimension ? 'landing_page_path' : $field;
		$conversions      = Niwa_AI_Traffic_DB::table( Niwa_AI_Traffic_DB::CONVERSIONS );
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$conversion_rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT {$conversion_field} AS label,
					COUNT(*) AS conversions,
					COUNT(DISTINCT order_id) AS orders,
					COALESCE(SUM(revenue), 0) AS revenue
				FROM {$conversions}
				WHERE occurred_at BETWEEN %s AND %s
					AND {$conversion_field} <> ''
					AND status IN ('processing','completed','on-hold')
				GROUP BY {$conversion_field}",
				$filters['start'],
				$filters['end']
			),
			ARRAY_A
		);

		$conversion_map = array();
		foreach ( is_array( $conversion_rows ) ? $conversion_rows : array() as $conversion_row ) {
			$label = (string) ( $conversion_row['label'] ?? '' );
			if ( '' === $label ) {
				continue;
			}
			$conversion_map[ $label ] = array(
				'conversions' => (int) ( $conversion_row['conversions'] ?? 0 ),
				'orders'      => (int) ( $conversion_row['orders'] ?? 0 ),
				'revenue'     => round( (float) ( $conversion_row['revenue'] ?? 0 ), 2 ),
			);
		}

		foreach ( $rows as &$row ) {
			$label = (string) ( $row['label'] ?? '' );
			$conv  = $conversion_map[ $label ] ?? array( 'conversions' => 0, 'orders' => 0, 'revenue' => 0.0 );
			$row['conversions']     = $conv['conversions'];
			$row['orders']          = $conv['orders'];
			$row['revenue']         = $conv['revenue'];
			$row['conversion_rate'] = intval( $row['sessions'] ?? 0 ) > 0 ? round( ( $conv['conversions'] / intval( $row['sessions'] ) ) * 100, 2 ) : 0.0;
		}
		unset( $row );

		return $rows;
	}

	private function table_exists( $slug ) {
		global $wpdb;
		$table = Niwa_AI_Traffic_DB::table( $slug );
		return $table === $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) );
	}

	private function empty_summary() {
		return array(
			'events' => 0, 'sessions' => 0, 'visitors' => 0, 'pageviews' => 0,
			'engaged_events' => 0, 'conversions' => 0, 'orders' => 0,
			'revenue' => 0.0, 'conversion_rate' => 0.0,
		);
	}

	private function build_event_id( array $event ) {
		return hash( 'sha256', implode( '|', array( $event['session_id'], $event['event_type'], $event['occurred_at'], $event['page_url'] ) ) );
	}
}
