<?php
/**
 * 404 Monitor Database Operations
 *
 * @package SEOvaultSEO
 */

namespace SEOvaultSEO\Monitor404;

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

class Db {

	/**
	 * Current schema version. Bump when the CREATE TABLE SQL changes so
	 * {@see \SEOvaultSEO\Core\Upgrade::maybe_upgrade()} can re-run dbDelta().
	 */
	const DB_VERSION = '1.1';

	/**
	 * Option name storing the installed schema version.
	 */
	const DB_VERSION_OPTION = 'seovaultseo_404_logs_db_version';

	/**
	 * Get the full table name with prefix.
	 *
	 * @return string
	 */
	public static function table_name() {
		global $wpdb;
		return $wpdb->prefix . 'seovaultseo_404_logs';
	}

	/**
	 * Create or upgrade the custom database table.
	 * Called on plugin activation and schema upgrades.
	 */
	public static function create_table() {
		global $wpdb;

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

		// DATETIME columns have no zero-date default (MySQL 5.7+/8.0 strict mode).
		// Inserts always supply current_time( 'mysql' ).
		$sql = "CREATE TABLE {$table} (
			id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			uri VARCHAR(255) NOT NULL DEFAULT '',
			referer VARCHAR(255) NOT NULL DEFAULT '',
			user_agent VARCHAR(255) NOT NULL DEFAULT '',
			times_accessed INT(11) UNSIGNED NOT NULL DEFAULT 1,
			accessed DATETIME NOT NULL,
			created DATETIME NOT NULL,
			PRIMARY KEY (id),
			KEY uri (uri(191))
		) {$charset};";

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

		update_option( self::DB_VERSION_OPTION, self::DB_VERSION, false );
	}

	/**
	 * Get log entries with pagination, sorting, and search.
	 *
	 * @param array $args Query arguments.
	 * @return array {
	 *     @type array $logs  Log rows.
	 *     @type int   $count Total found rows.
	 * }
	 */
	public static function get_logs( $args = array() ) {
		global $wpdb;

		$args = wp_parse_args(
			$args,
			array(
				'orderby' => 'id',
				'order'   => 'DESC',
				'limit'   => 20,
				'paged'   => 1,
				'search'  => '',
			)
		);

		$table   = self::table_name();
		$allowed = array( 'id', 'uri', 'accessed', 'times_accessed' );
		$orderby = in_array( $args['orderby'], $allowed, true ) ? $args['orderby'] : 'id';
		$order   = 'ASC' === strtoupper( $args['order'] ) ? 'ASC' : 'DESC';
		$limit   = max( 1, (int) $args['limit'] );
		$offset  = max( 0, ( (int) $args['paged'] - 1 ) * $limit );

		$where_sql  = '';
		$where_args = array();
		if ( ! empty( $args['search'] ) ) {
			$where_sql    = ' WHERE uri LIKE %s';
			$where_args[] = '%' . $wpdb->esc_like( rawurlencode( $args['search'] ) ) . '%';
		}
		$log_args   = array_merge( $where_args, array( $limit, $offset ) );

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter -- WHERE contains a placeholder only; values are passed to prepare; table/orderby/order are plugin-owned or allowlisted.
		$logs = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM {$table}{$where_sql} ORDER BY {$orderby} {$order} LIMIT %d OFFSET %d",
				$log_args
			),
			ARRAY_A
		);

		if ( empty( $where_args ) ) {
			$count = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table}" );
		} else {
			$count = (int) $wpdb->get_var(
				$wpdb->prepare(
					"SELECT COUNT(*) FROM {$table}{$where_sql}",
					$where_args
				)
			);
		}
		// phpcs:enable

		return array(
			'logs'  => $logs,
			'count' => $count,
		);
	}

	/**
	 * Add a new log entry (advanced mode — with referrer and UA).
	 *
	 * @param array $args {
	 *     @type string $uri
	 *     @type string $referer
	 *     @type string $user_agent
	 * }
	 */
	public static function add( $args ) {
		global $wpdb;

		$args = wp_parse_args(
			$args,
			array(
				'uri'        => '',
				'referer'    => '',
				'user_agent' => '',
			)
		);

		$table = self::table_name();
		$now   = current_time( 'mysql' );

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery
		$wpdb->insert(
			$table,
			array(
				'uri'            => $args['uri'],
				'referer'        => $args['referer'],
				'user_agent'     => $args['user_agent'],
				'times_accessed' => 1,
				'accessed'       => $now,
				'created'        => $now,
			),
			array( '%s', '%s', '%s', '%d', '%s', '%s' )
		);
		// phpcs:enable

		$insert_id = (int) $wpdb->insert_id;

		// Keep newest rows within the configured cap (do not wipe the whole table).
		$limit = \SEOvaultSEO\Core\Options::get_monitor404_limit();
		if ( $limit ) {
			self::prune_to_limit( $limit );
		}

		return $insert_id;
	}

	/**
	 * How many oldest rows to delete to bring the table down to $max_rows.
	 *
	 * @param int $current_count Current total row count.
	 * @param int $max_rows      Row-count cap (`0` disables pruning).
	 * @return int Number of oldest rows to delete (never negative).
	 */
	public static function calculate_prune_count( $current_count, $max_rows ) {
		$max_rows = (int) $max_rows;
		if ( $max_rows <= 0 ) {
			return 0;
		}

		return max( 0, (int) $current_count - $max_rows );
	}

	/**
	 * Delete oldest rows until at most $max_rows remain.
	 *
	 * @param int $max_rows Maximum rows to keep.
	 * @return int Number of rows deleted.
	 */
	public static function prune_to_limit( $max_rows ) {
		$delete_count = self::calculate_prune_count( self::get_count(), $max_rows );
		if ( $delete_count <= 0 ) {
			return 0;
		}

		global $wpdb;
		$table = self::table_name();
		if ( ! preg_match( '/^[A-Za-z0-9_]+$/', $table ) ) {
			return 0;
		}

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Plugin-owned table validated above; LIMIT uses prepare placeholder.
		$deleted = (int) $wpdb->query(
			$wpdb->prepare( "DELETE FROM {$table} ORDER BY id ASC LIMIT %d", $delete_count )
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		return $deleted;
	}

	/**
	 * Update an existing entry's hit counter (simple mode),
	 * or insert if the URI doesn't exist yet.
	 *
	 * @param array $args {
	 *     @type string $uri
	 * }
	 */
	public static function update( $args ) {
		global $wpdb;

		$table = self::table_name();
		$uri   = isset( $args['uri'] ) ? $args['uri'] : '';

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Plugin-owned table name.
		$row = $wpdb->get_row(
			$wpdb->prepare( "SELECT id, times_accessed FROM {$table} WHERE uri = %s LIMIT 1", $uri ),
			ARRAY_A
		);

		if ( $row ) {
			$wpdb->update(
				$table,
				array(
					'times_accessed' => (int) $row['times_accessed'] + 1,
					'accessed'       => current_time( 'mysql' ),
				),
				array( 'id' => (int) $row['id'] ),
				array( '%d', '%s' ),
				array( '%d' )
			);
			return (int) $row['id'];
		}
		// phpcs:enable

		return self::add( $args );
	}

	/**
	 * Delete log entries by IDs.
	 *
	 * @param int|array $ids ID or array of IDs.
	 * @return int Number of rows deleted.
	 */
	public static function delete_log( $ids ) {
		global $wpdb;

		$ids = array_map( 'absint', (array) $ids );
		if ( empty( $ids ) ) {
			return 0;
		}

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

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- Plugin-owned table; IN list uses prepare().
		return (int) $wpdb->query(
			$wpdb->prepare( "DELETE FROM {$table} WHERE id IN ({$placehold})", $ids )
		);
		// phpcs:enable
	}

	/**
	 * Get total number of log entries.
	 *
	 * @return int
	 */
	public static function get_count() {
		global $wpdb;
		$table = self::table_name();
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Plugin-owned table name.
		return (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table}" );
		// phpcs:enable
	}

	/**
	 * Clear all log entries.
	 *
	 * @return int Number of rows deleted.
	 */
	public static function clear_logs() {
		global $wpdb;
		$table = self::table_name();
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Plugin-owned table name.
		return (int) $wpdb->query( "TRUNCATE TABLE {$table}" );
		// phpcs:enable
	}

	/**
	 * Get summary stats for dashboard display.
	 *
	 * @return object {
	 *     @type int $total Total unique URLs.
	 *     @type int $hits  Total hit count.
	 * }
	 */
	public static function get_stats() {
		global $wpdb;
		$table = self::table_name();
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Plugin-owned table name.
		$row = $wpdb->get_row(
			"SELECT COUNT(*) AS total, COALESCE(SUM(times_accessed), 0) AS hits FROM {$table}"
		);
		// phpcs:enable

		if ( ! $row ) {
			return (object) array(
				'total' => 0,
				'hits'  => 0,
			);
		}

		return $row;
	}
}
