<?php
/**
 * Local datastore.
 *
 * Every detected AI-crawler hit is written to a custom table first (fast, complete,
 * works offline) which powers the in-wp-admin dashboard instantly. A background sync
 * (class-aiso-sync.php) then ships rows to the central Aiso MongoDB so data
 * accumulates over time across all of an agency's client sites.
 *
 * @package Aiso_AI_Crawler_Analytics
 */

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

/*
 * This class is the single data-access layer for the plugin's own custom table
 * ( {$wpdb->prefix}aiso_hits ). SQL identifiers (table and column names) are
 * interpolated because they cannot be bound via $wpdb->prepare(); those
 * identifiers are always derived from $wpdb->prefix or validated against an
 * internal whitelist, while every user-supplied value is passed through
 * $wpdb->prepare() placeholders. Rows are intentionally read uncached (live
 * analytics). The corresponding sniffs are disabled for this file on that basis.
 */
// phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL, WordPress.DB.PreparedSQLPlaceholders, PluginCheck.Security.DirectDB

class Aiso_Store {

	const DB_VERSION = '1.0.0';

	/**
	 * Fully-qualified hits table name.
	 *
	 * @return string
	 */
	public static function table() {
		global $wpdb;
		return $wpdb->prefix . 'aiso_hits';
	}

	/**
	 * Create / upgrade the hits table. Called on activation and on version bump.
	 */
	public static function install() {
		global $wpdb;

		$table           = self::table();
		$charset_collate = $wpdb->get_charset_collate();

		// dbDelta is picky: two spaces after PRIMARY KEY, lowercase "key", etc.
		$sql = "CREATE TABLE {$table} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			hit_time datetime NOT NULL,
			bot_slug varchar(64) NOT NULL DEFAULT '',
			bot_name varchar(96) NOT NULL DEFAULT '',
			company varchar(64) NOT NULL DEFAULT '',
			platform varchar(32) NOT NULL DEFAULT '',
			function_tag varchar(32) NOT NULL DEFAULT '',
			is_impression tinyint(1) NOT NULL DEFAULT 0,
			path varchar(512) NOT NULL DEFAULT '',
			url varchar(768) NOT NULL DEFAULT '',
			method varchar(8) NOT NULL DEFAULT 'GET',
			status smallint(5) unsigned NOT NULL DEFAULT 0,
			user_agent varchar(512) NOT NULL DEFAULT '',
			ip varchar(64) NOT NULL DEFAULT '',
			ip_hash char(64) NOT NULL DEFAULT '',
			country varchar(8) NOT NULL DEFAULT '',
			referer varchar(512) NOT NULL DEFAULT '',
			verified tinyint(1) DEFAULT NULL,
			synced tinyint(1) NOT NULL DEFAULT 0,
			PRIMARY KEY  (id),
			KEY hit_time (hit_time),
			KEY bot_slug (bot_slug),
			KEY platform (platform),
			KEY function_tag (function_tag),
			KEY synced (synced),
			KEY path (path(191))
		) {$charset_collate};";

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		dbDelta( $sql );

		update_option( 'aiso_db_version', self::DB_VERSION );
	}

	/**
	 * Insert one hit. $data keys map to columns.
	 *
	 * @param array<string,mixed> $data Hit data.
	 * @return int Inserted row id (0 on failure).
	 */
	public static function insert_hit( $data ) {
		global $wpdb;

		$row = array(
			'hit_time'      => isset( $data['hit_time'] ) ? $data['hit_time'] : gmdate( 'Y-m-d H:i:s' ),
			'bot_slug'      => isset( $data['bot_slug'] ) ? substr( (string) $data['bot_slug'], 0, 64 ) : '',
			'bot_name'      => isset( $data['bot_name'] ) ? substr( (string) $data['bot_name'], 0, 96 ) : '',
			'company'       => isset( $data['company'] ) ? substr( (string) $data['company'], 0, 64 ) : '',
			'platform'      => isset( $data['platform'] ) ? substr( (string) $data['platform'], 0, 32 ) : '',
			'function_tag'  => isset( $data['function_tag'] ) ? substr( (string) $data['function_tag'], 0, 32 ) : '',
			'is_impression' => ! empty( $data['is_impression'] ) ? 1 : 0,
			'path'          => isset( $data['path'] ) ? substr( (string) $data['path'], 0, 512 ) : '',
			'url'           => isset( $data['url'] ) ? substr( (string) $data['url'], 0, 768 ) : '',
			'method'        => isset( $data['method'] ) ? substr( (string) $data['method'], 0, 8 ) : 'GET',
			'status'        => isset( $data['status'] ) ? (int) $data['status'] : 0,
			'user_agent'    => isset( $data['user_agent'] ) ? substr( (string) $data['user_agent'], 0, 512 ) : '',
			'ip'            => isset( $data['ip'] ) ? substr( (string) $data['ip'], 0, 64 ) : '',
			'ip_hash'       => isset( $data['ip_hash'] ) ? substr( (string) $data['ip_hash'], 0, 64 ) : '',
			'country'       => isset( $data['country'] ) ? substr( (string) $data['country'], 0, 8 ) : '',
			'referer'       => isset( $data['referer'] ) ? substr( (string) $data['referer'], 0, 512 ) : '',
			'verified'      => array_key_exists( 'verified', $data ) && null !== $data['verified'] ? ( $data['verified'] ? 1 : 0 ) : null,
			'synced'        => 0,
		);

		$formats = array( '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%s', '%s', '%s', '%d', '%s', '%s', '%s', '%s', '%s', ( null === $row['verified'] ? null : '%d' ), '%d' );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom analytics table; no core API exists.
		$ok = $wpdb->insert( self::table(), $row, $formats );

		return $ok ? (int) $wpdb->insert_id : 0;
	}

	/**
	 * UTC datetime string for the start of a range key.
	 *
	 * @param string $range One of 24h|7d|30d|90d|all.
	 * @return string MySQL datetime (UTC) or '' for "all".
	 */
	public static function range_start( $range ) {
		$now = time();
		switch ( $range ) {
			case '24h':
				return gmdate( 'Y-m-d H:i:s', $now - DAY_IN_SECONDS );
			case '7d':
				return gmdate( 'Y-m-d H:i:s', $now - 7 * DAY_IN_SECONDS );
			case '30d':
				return gmdate( 'Y-m-d H:i:s', $now - 30 * DAY_IN_SECONDS );
			case '90d':
				return gmdate( 'Y-m-d H:i:s', $now - 90 * DAY_IN_SECONDS );
			case 'all':
			default:
				return '';
		}
	}

	/**
	 * Build a "WHERE hit_time >= ?" clause + params for a range.
	 *
	 * @param string $range Range key.
	 * @return array{0:string,1:array} [ where_sql, params ]
	 */
	private static function range_where( $range ) {
		$start = self::range_start( $range );
		if ( '' === $start ) {
			return array( '1=1', array() );
		}
		return array( 'hit_time >= %s', array( $start ) );
	}

	/**
	 * Total hits + headline counters for a range.
	 *
	 * @param string $range Range key.
	 * @return array<string,int>
	 */
	public static function totals( $range = '7d' ) {
		global $wpdb;
		$table              = self::table();
		list( $where, $p )  = self::range_where( $range );

		$sql = "SELECT
				COUNT(*) AS total,
				COUNT(DISTINCT bot_slug) AS bots,
				COUNT(DISTINCT path) AS pages,
				SUM(is_impression) AS impressions
			FROM {$table} WHERE {$where}";

		// "all" has no bound params; prepare() must not be called without placeholders.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table internal; values prepared when present.
		$row = empty( $p ) ? $wpdb->get_row( $sql, ARRAY_A ) : $wpdb->get_row( $wpdb->prepare( $sql, $p ), ARRAY_A );

		return array(
			'total'       => isset( $row['total'] ) ? (int) $row['total'] : 0,
			'bots'        => isset( $row['bots'] ) ? (int) $row['bots'] : 0,
			'pages'       => isset( $row['pages'] ) ? (int) $row['pages'] : 0,
			'impressions' => isset( $row['impressions'] ) ? (int) $row['impressions'] : 0,
		);
	}

	/**
	 * Group hits by an arbitrary column (validated against a whitelist).
	 *
	 * @param string $column One of platform|company|bot_name|function_tag|path.
	 * @param string $range  Range key.
	 * @param int    $limit  Max groups.
	 * @return array<int,array{label:string,count:int}>
	 */
	public static function group_by( $column, $range = '7d', $limit = 12 ) {
		global $wpdb;

		$allowed = array( 'platform', 'company', 'bot_name', 'bot_slug', 'function_tag', 'path' );
		if ( ! in_array( $column, $allowed, true ) ) {
			return array();
		}

		$table             = self::table();
		list( $where, $p ) = self::range_where( $range );
		$limit             = max( 1, min( 100, (int) $limit ) );
		$params            = array_merge( $p, array( $limit ) );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $column whitelisted above; values prepared.
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT {$column} AS label, COUNT(*) AS count
				FROM {$table}
				WHERE {$where} AND {$column} <> ''
				GROUP BY {$column}
				ORDER BY count DESC
				LIMIT %d",
				$params
			),
			ARRAY_A
		);

		$out = array();
		foreach ( (array) $rows as $r ) {
			$out[] = array(
				'label' => (string) $r['label'],
				'count' => (int) $r['count'],
			);
		}
		return $out;
	}

	/**
	 * Daily timeseries of total hits and impressions for a range.
	 *
	 * @param string $range Range key.
	 * @return array<int,array{day:string,total:int,impressions:int}>
	 */
	public static function timeseries( $range = '7d' ) {
		global $wpdb;
		$table             = self::table();
		list( $where, $p ) = self::range_where( $range );

		$sql = "SELECT DATE(hit_time) AS day, COUNT(*) AS total, SUM(is_impression) AS impressions
			FROM {$table}
			WHERE {$where}
			GROUP BY DATE(hit_time)
			ORDER BY day ASC";

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table internal; values prepared when present.
		$rows = empty( $p ) ? $wpdb->get_results( $sql, ARRAY_A ) : $wpdb->get_results( $wpdb->prepare( $sql, $p ), ARRAY_A );

		$out = array();
		foreach ( (array) $rows as $r ) {
			$out[] = array(
				'day'         => (string) $r['day'],
				'total'       => (int) $r['total'],
				'impressions' => (int) $r['impressions'],
			);
		}
		return $out;
	}

	/**
	 * Most recent hits for the live log table.
	 *
	 * @param string $range Range key.
	 * @param int    $limit Row count.
	 * @return array<int,array<string,mixed>>
	 */
	public static function recent( $range = '7d', $limit = 25 ) {
		global $wpdb;
		$table             = self::table();
		list( $where, $p ) = self::range_where( $range );
		$limit             = max( 1, min( 200, (int) $limit ) );
		$params            = array_merge( $p, array( $limit ) );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table internal; values prepared.
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT hit_time, bot_name, company, platform, function_tag, path, ip, country, user_agent, verified, status
				FROM {$table}
				WHERE {$where}
				ORDER BY hit_time DESC, id DESC
				LIMIT %d",
				$params
			),
			ARRAY_A
		);

		return (array) $rows;
	}

	/**
	 * Per-page breakdown with the leading crawler and impression count.
	 *
	 * @param string $range Range key.
	 * @param int    $limit Page count.
	 * @return array<int,array<string,mixed>>
	 */
	public static function top_pages( $range = '7d', $limit = 15 ) {
		global $wpdb;
		$table             = self::table();
		list( $where, $p ) = self::range_where( $range );
		$limit             = max( 1, min( 100, (int) $limit ) );
		$params            = array_merge( $p, array( $limit ) );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table internal; values prepared.
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT path,
					COUNT(*) AS hits,
					SUM(is_impression) AS impressions,
					COUNT(DISTINCT bot_slug) AS bots
				FROM {$table}
				WHERE {$where} AND path <> ''
				GROUP BY path
				ORDER BY hits DESC
				LIMIT %d",
				$params
			),
			ARRAY_A
		);

		return (array) $rows;
	}

	/**
	 * Rows not yet shipped to MongoDB.
	 *
	 * @param int $limit Batch size.
	 * @return array<int,array<string,mixed>>
	 */
	public static function unsynced( $limit = 100 ) {
		global $wpdb;
		$table = self::table();
		$limit = max( 1, min( 500, (int) $limit ) );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table internal; value prepared.
		$rows = $wpdb->get_results(
			$wpdb->prepare( "SELECT * FROM {$table} WHERE synced = 0 ORDER BY id ASC LIMIT %d", $limit ),
			ARRAY_A
		);

		return (array) $rows;
	}

	/**
	 * Mark rows as synced.
	 *
	 * @param int[] $ids Row ids.
	 * @return void
	 */
	public static function mark_synced( $ids ) {
		global $wpdb;
		$ids = array_filter( array_map( 'absint', (array) $ids ) );
		if ( empty( $ids ) ) {
			return;
		}

		$table        = self::table();
		$placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table internal; ids prepared.
		$wpdb->query(
			$wpdb->prepare( "UPDATE {$table} SET synced = 1 WHERE id IN ({$placeholders})", $ids )
		);
	}

	/**
	 * Count unsynced rows (for the settings status line).
	 *
	 * @return int
	 */
	public static function unsynced_count() {
		global $wpdb;
		$table = self::table();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table internal; no user input.
		return (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table} WHERE synced = 0" );
	}

	/**
	 * Delete local rows older than $days. 0 keeps everything.
	 *
	 * @param int $days Retention window in days.
	 * @return int Rows removed.
	 */
	public static function prune( $days ) {
		global $wpdb;
		$days = (int) $days;
		if ( $days <= 0 ) {
			return 0;
		}

		$table  = self::table();
		$cutoff = gmdate( 'Y-m-d H:i:s', time() - $days * DAY_IN_SECONDS );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table internal; value prepared.
		return (int) $wpdb->query(
			$wpdb->prepare( "DELETE FROM {$table} WHERE hit_time < %s AND synced = 1", $cutoff )
		);
	}

	/**
	 * Drop the table (used by uninstall.php when "remove data" is enabled).
	 */
	public static function drop() {
		global $wpdb;
		$table = self::table();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table internal; uninstall cleanup.
		$wpdb->query( "DROP TABLE IF EXISTS {$table}" );
	}
}