<?php

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

// phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Repository methods use fixed plugin-owned aggregate tables with prepared value placeholders.
class AVIN_Analytics_Repository {
	public function record_page_view( $context ) {
		global $wpdb;

		$context        = $this->normalize_context( $context );
		$view_date      = current_time( 'Y-m-d' );
		$now            = current_time( 'mysql' );
		$is_product     = 'product' === $context['object_type'] ? 1 : 0;
		$visitor_insert = $this->insert_daily_visitor( $view_date, $context['visitor_hash'], $now );
		$session_insert = $this->insert_daily_session( $view_date, $context, $now );

		$wpdb->query(
			$wpdb->prepare(
				"INSERT INTO {$this->traffic_table()} (view_date, unique_visitors, sessions, page_views, product_views)
				VALUES (%s, %d, %d, 1, %d)
				ON DUPLICATE KEY UPDATE unique_visitors = unique_visitors + VALUES(unique_visitors), sessions = sessions + VALUES(sessions), page_views = page_views + 1, product_views = product_views + VALUES(product_views)",
				$view_date,
				$visitor_insert ? 1 : 0,
				$session_insert ? 1 : 0,
				$is_product
			)
		); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		$wpdb->query(
			$wpdb->prepare(
				"INSERT INTO {$this->device_table()} (view_date, device_type, visitors, sessions, page_views)
				VALUES (%s, %s, %d, %d, 1)
				ON DUPLICATE KEY UPDATE visitors = visitors + VALUES(visitors), sessions = sessions + VALUES(sessions), page_views = page_views + 1",
				$view_date,
				$context['device_type'],
				$visitor_insert ? 1 : 0,
				$session_insert ? 1 : 0
			)
		); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		$wpdb->query(
			$wpdb->prepare(
				"INSERT INTO {$this->source_table()} (view_date, source_type, visits, views)
				VALUES (%s, %s, %d, 1)
				ON DUPLICATE KEY UPDATE visits = visits + VALUES(visits), views = views + 1",
				$view_date,
				$context['source_type'],
				$session_insert ? 1 : 0
			)
		); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		if ( '' !== $context['social_source'] ) {
			$wpdb->query(
				$wpdb->prepare(
					"INSERT INTO {$this->social_table()} (view_date, social_source, visits, views)
					VALUES (%s, %s, %d, 1)
					ON DUPLICATE KEY UPDATE visits = visits + VALUES(visits), views = views + 1",
					$view_date,
					$context['social_source'],
					$session_insert ? 1 : 0
				)
			); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		}

		$wpdb->query(
			$wpdb->prepare(
				"INSERT INTO {$this->country_table()} (view_date, country_code, country_name, visitors, views)
				VALUES (%s, %s, %s, %d, 1)
				ON DUPLICATE KEY UPDATE country_name = VALUES(country_name), visitors = visitors + VALUES(visitors), views = views + 1",
				$view_date,
				$context['country_code'],
				$context['country_name'],
				$visitor_insert ? 1 : 0
			)
		); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		$this->upsert_active_session( $context, $now );
	}

	public function refresh_presence( $context ) {
		$context = $this->normalize_context( $context );
		$this->upsert_active_session( $context, current_time( 'mysql' ), false );
	}

	public function record_conversion_event( $event_type, $context = array() ) {
		global $wpdb;

		$event_type = sanitize_key( $event_type );
		if ( ! in_array( $event_type, array( 'add_to_cart', 'checkout_start', 'order' ), true ) ) {
			return false;
		}

		$context     = $this->normalize_context( $context );
		$view_date   = current_time( 'Y-m-d' );
		$column      = 'order' === $event_type ? 'orders' : ( 'checkout_start' === $event_type ? 'checkout_starts' : 'add_to_cart' );
		$source_type = $context['source_type'];

		$wpdb->query(
			$wpdb->prepare(
				"INSERT INTO {$this->traffic_table()} (view_date, {$column})
				VALUES (%s, 1)
				ON DUPLICATE KEY UPDATE {$column} = {$column} + 1",
				$view_date
			)
		); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		$wpdb->query(
			$wpdb->prepare(
				"INSERT INTO {$this->source_table()} (view_date, source_type, conversions)
				VALUES (%s, %s, 1)
				ON DUPLICATE KEY UPDATE conversions = conversions + 1",
				$view_date,
				$source_type
			)
		); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		if ( '' !== $context['social_source'] ) {
			$wpdb->query(
				$wpdb->prepare(
					"INSERT INTO {$this->social_table()} (view_date, social_source, conversions)
					VALUES (%s, %s, 1)
					ON DUPLICATE KEY UPDATE conversions = conversions + 1",
					$view_date,
					$context['social_source']
				)
			); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		}

		return true;
	}

	public function online_summary( $minutes = 5 ) {
		global $wpdb;

		$cutoff = gmdate( 'Y-m-d H:i:s', current_time( 'timestamp' ) - ( max( 1, absint( $minutes ) ) * MINUTE_IN_SECONDS ) );
		$row    = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT COUNT(DISTINCT visitor_hash) AS visitors, COUNT(*) AS sessions, COUNT(DISTINCT CONCAT(current_object_type, ':', current_object_id)) AS pages
				FROM {$this->active_table()}
				WHERE last_seen_at >= %s",
				$cutoff
			),
			ARRAY_A
		); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		return array(
			'visitors' => absint( $row['visitors'] ?? 0 ),
			'sessions' => absint( $row['sessions'] ?? 0 ),
			'pages'    => absint( $row['pages'] ?? 0 ),
		);
	}

	public function traffic_for_date( $date ) {
		global $wpdb;

		$row = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM {$this->traffic_table()} WHERE view_date = %s",
				sanitize_text_field( $date )
			),
			ARRAY_A
		); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		return wp_parse_args(
			(array) $row,
			array(
				'unique_visitors' => 0,
				'sessions'        => 0,
				'page_views'      => 0,
				'product_views'   => 0,
				'add_to_cart'     => 0,
				'checkout_starts' => 0,
				'orders'          => 0,
			)
		);
	}

	public function traffic_for_days( $days ) {
		global $wpdb;

		$days  = max( 1, absint( $days ) );
		$since = gmdate( 'Y-m-d', strtotime( current_time( 'Y-m-d' ) . ' -' . ( $days - 1 ) . ' days' ) );
		$row   = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT COALESCE(SUM(unique_visitors), 0) AS unique_visitors, COALESCE(SUM(sessions), 0) AS sessions, COALESCE(SUM(page_views), 0) AS page_views, COALESCE(SUM(product_views), 0) AS product_views, COALESCE(SUM(add_to_cart), 0) AS add_to_cart, COALESCE(SUM(checkout_starts), 0) AS checkout_starts, COALESCE(SUM(orders), 0) AS orders
				FROM {$this->traffic_table()}
				WHERE view_date >= %s",
				$since
			),
			ARRAY_A
		); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		return $row ? array_map( 'absint', $row ) : array();
	}

	public function device_breakdown( $date = null ) {
		global $wpdb;

		$date = $date ? sanitize_text_field( $date ) : current_time( 'Y-m-d' );
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT device_type, visitors, sessions, page_views
				FROM {$this->device_table()}
				WHERE view_date = %s
				ORDER BY page_views DESC",
				$date
			),
			ARRAY_A
		); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		return $this->with_percentages( $rows, 'page_views' );
	}

	public function top_sources( $days = 30, $limit = 10 ) {
		return $this->top_dimension_rows( $this->source_table(), 'source_type', $days, $limit );
	}

	public function top_social_sources( $days = 30, $limit = 10 ) {
		return $this->top_dimension_rows( $this->social_table(), 'social_source', $days, $limit );
	}

	public function top_countries( $days = 30, $limit = 10 ) {
		global $wpdb;

		$since = gmdate( 'Y-m-d', strtotime( current_time( 'Y-m-d' ) . ' -' . ( max( 1, absint( $days ) ) - 1 ) . ' days' ) );
		$limit = max( 1, min( 50, absint( $limit ) ) );

		return $wpdb->get_results(
			$wpdb->prepare(
				"SELECT country_code, country_name, COALESCE(SUM(visitors), 0) AS visitors, COALESCE(SUM(views), 0) AS views
				FROM {$this->country_table()}
				WHERE view_date >= %s
				GROUP BY country_code, country_name
				ORDER BY visitors DESC, views DESC
				LIMIT %d",
				$since,
				$limit
			),
			ARRAY_A
		); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	}

	public function delete_daily_older_than( $days ) {
		global $wpdb;

		$cutoff = gmdate( 'Y-m-d', strtotime( current_time( 'Y-m-d' ) . ' -' . absint( $days ) . ' days' ) );
		foreach ( array( $this->traffic_table(), $this->device_table(), $this->source_table(), $this->social_table(), $this->country_table(), $this->daily_visitors_table(), $this->daily_sessions_table() ) as $table ) {
			$wpdb->query(
				$wpdb->prepare(
					"DELETE FROM {$table} WHERE view_date < %s",
					$cutoff
				)
			); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		}
	}

	public function prune_presence( $minutes = 30 ) {
		global $wpdb;

		$cutoff = gmdate( 'Y-m-d H:i:s', current_time( 'timestamp' ) - ( max( 5, absint( $minutes ) ) * MINUTE_IN_SECONDS ) );
		$wpdb->query(
			$wpdb->prepare(
				"DELETE FROM {$this->active_table()} WHERE last_seen_at < %s",
				$cutoff
			)
		); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	}

	private function insert_daily_visitor( $view_date, $visitor_hash, $now ) {
		global $wpdb;

		$wpdb->query(
			$wpdb->prepare(
				"INSERT IGNORE INTO {$this->daily_visitors_table()} (view_date, visitor_hash, created_at) VALUES (%s, %s, %s)",
				$view_date,
				$visitor_hash,
				$now
			)
		); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		return 1 === (int) $wpdb->rows_affected;
	}

	private function insert_daily_session( $view_date, $context, $now ) {
		global $wpdb;

		$wpdb->query(
			$wpdb->prepare(
				"INSERT INTO {$this->daily_sessions_table()} (view_date, session_hash, visitor_hash, device_type, source_type, social_source, country_code, started_at, last_seen_at)
				VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
				ON DUPLICATE KEY UPDATE last_seen_at = VALUES(last_seen_at)",
				$view_date,
				$context['session_hash'],
				$context['visitor_hash'],
				$context['device_type'],
				$context['source_type'],
				$context['social_source'],
				$context['country_code'],
				$now,
				$now
			)
		); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		return 1 === (int) $wpdb->rows_affected;
	}

	private function upsert_active_session( $context, $now, $increment_page_views = true ) {
		global $wpdb;

		$page_views     = $increment_page_views ? 1 : 0;
		$page_views_sql = $increment_page_views ? 'page_views = page_views + 1' : 'page_views = page_views';
		$wpdb->query(
			$wpdb->prepare(
				"INSERT INTO {$this->active_table()} (session_hash, visitor_hash, last_seen_at, current_object_id, current_object_type, current_title, current_url, page_views, device_type, source_type, social_source, country_code, country_name)
				VALUES (%s, %s, %s, %d, %s, %s, %s, %d, %s, %s, %s, %s, %s)
				ON DUPLICATE KEY UPDATE last_seen_at = VALUES(last_seen_at), current_object_id = VALUES(current_object_id), current_object_type = VALUES(current_object_type), current_title = VALUES(current_title), current_url = VALUES(current_url), {$page_views_sql}, device_type = VALUES(device_type), source_type = VALUES(source_type), social_source = VALUES(social_source), country_code = VALUES(country_code), country_name = VALUES(country_name)",
				$context['session_hash'],
				$context['visitor_hash'],
				$now,
				$context['object_id'],
				$context['object_type'],
				$context['object_title'],
				$context['url'],
				$page_views,
				$context['device_type'],
				$context['source_type'],
				$context['social_source'],
				$context['country_code'],
				$context['country_name']
			)
		); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	}

	private function normalize_context( $context ) {
		$context = wp_parse_args(
			(array) $context,
			array(
				'visitor_hash'  => str_repeat( '0', 64 ),
				'session_hash'  => str_repeat( '0', 64 ),
				'object_id'     => 0,
				'object_type'   => '',
				'object_title'  => '',
				'page_label'    => '',
				'url'           => '',
				'device_type'   => 'unknown',
				'source_type'   => 'unknown',
				'social_source' => '',
				'country_code'  => 'ZZ',
				'country_name'  => 'Unknown',
			)
		);

		$context['visitor_hash']  = preg_replace( '/[^a-f0-9]/', '', strtolower( (string) $context['visitor_hash'] ) );
		$context['session_hash']  = preg_replace( '/[^a-f0-9]/', '', strtolower( (string) $context['session_hash'] ) );
		$context['object_id']     = absint( $context['object_id'] );
		$context['object_type']   = sanitize_key( $context['object_type'] );
		$context['object_title']  = sanitize_text_field( $context['object_title'] );
		$context['page_label']    = sanitize_text_field( $context['page_label'] );
		$context['url']           = esc_url_raw( substr( (string) $context['url'], 0, 255 ) );
		$context['device_type']   = sanitize_key( $context['device_type'] );
		$context['source_type']   = sanitize_key( $context['source_type'] );
		$context['social_source'] = sanitize_key( $context['social_source'] );
		$context['country_code']  = strtoupper( substr( preg_replace( '/[^A-Za-z]/', '', (string) $context['country_code'] ), 0, 2 ) );
		$context['country_name']  = sanitize_text_field( $context['country_name'] );

		if ( 64 !== strlen( $context['visitor_hash'] ) ) {
			$context['visitor_hash'] = hash( 'sha256', wp_generate_uuid4() );
		}
		if ( 64 !== strlen( $context['session_hash'] ) ) {
			$context['session_hash'] = hash( 'sha256', wp_generate_uuid4() );
		}
		if ( '' === $context['page_label'] ) {
			$context['page_label'] = $context['object_title'] ? $context['object_title'] : __( 'Unknown page', 'apex-view-insights' );
		}
		if ( '' === $context['country_code'] ) {
			$context['country_code'] = 'ZZ';
		}
		if ( '' === $context['country_name'] ) {
			$context['country_name'] = 'Unknown';
		}

		return $context;
	}

	private function with_percentages( $rows, $value_key ) {
		$total = 0;
		foreach ( $rows as $row ) {
			$total += absint( $row[ $value_key ] ?? 0 );
		}
		foreach ( $rows as &$row ) {
			$row['percentage'] = $total > 0 ? round( ( absint( $row[ $value_key ] ?? 0 ) / $total ) * 100, 1 ) : 0;
		}
		unset( $row );
		return $rows;
	}

	private function top_dimension_rows( $table, $column, $days, $limit ) {
		global $wpdb;

		$since = gmdate( 'Y-m-d', strtotime( current_time( 'Y-m-d' ) . ' -' . ( max( 1, absint( $days ) ) - 1 ) . ' days' ) );
		$limit = max( 1, min( 50, absint( $limit ) ) );

		return $wpdb->get_results(
			$wpdb->prepare(
				"SELECT {$column} AS label, COALESCE(SUM(visits), 0) AS visits, COALESCE(SUM(views), 0) AS views, COALESCE(SUM(conversions), 0) AS conversions
				FROM {$table}
				WHERE view_date >= %s
				GROUP BY {$column}
				ORDER BY visits DESC, views DESC
				LIMIT %d",
				$since,
				$limit
			),
			ARRAY_A
		); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	}

	private function traffic_table() {
		global $wpdb;
		return $wpdb->prefix . 'avin_traffic_daily';
	}

	private function device_table() {
		global $wpdb;
		return $wpdb->prefix . 'avin_device_daily';
	}

	private function source_table() {
		global $wpdb;
		return $wpdb->prefix . 'avin_source_daily';
	}

	private function social_table() {
		global $wpdb;
		return $wpdb->prefix . 'avin_social_daily';
	}

	private function country_table() {
		global $wpdb;
		return $wpdb->prefix . 'avin_country_daily';
	}

	private function daily_visitors_table() {
		global $wpdb;
		return $wpdb->prefix . 'avin_daily_visitors';
	}

	private function daily_sessions_table() {
		global $wpdb;
		return $wpdb->prefix . 'avin_daily_sessions';
	}

	private function active_table() {
		global $wpdb;
		return $wpdb->prefix . 'avin_active_sessions';
	}
}
