<?php
/**
 * Custom table creation, removal and schema versioning.
 *
 * @package PressRoost\AIVE
 */

namespace PressRoost\AIVE;

use PressRoost\AIVE\Utils\Helpers;

/**
 * Owns AIVE's custom database tables.
 *
 * AIVE stores crawler hits, answer-density scores and citations in dedicated
 * tables rather than in post meta: these are high-volume, append-heavy data
 * sets and putting them in `wp_postmeta` would bloat the autoloaded meta cache
 * for every request on the site.
 *
 * Table names are `{$wpdb->prefix}aive_*`. `$wpdb->prefix` already carries its
 * trailing underscore, so a default install yields `wp_aive_crawler_log`.
 *
 * @since 1.0.0
 */
final class Database {

	/**
	 * Option key holding the installed schema version.
	 *
	 * @since 1.0.0
	 * @var string
	 */
	public const VERSION_OPTION = 'aive_db_version';

	/**
	 * Unprefixed table identifiers owned by the plugin.
	 *
	 * @since 1.0.0
	 * @var string[]
	 */
	public const TABLES = array( 'crawler_log', 'answer_density', 'citations' );

	/**
	 * Column holding the canonical UTC instant of a crawler visit.
	 *
	 * @since 1.1.0
	 * @var string
	 */
	public const CRAWLER_CANONICAL_COLUMN = 'visited_at_utc';

	/**
	 * Cached answer to "does the crawler log have its canonical column?".
	 *
	 * Null means "not asked yet". The probe is a `SHOW COLUMNS` round trip and
	 * the answer cannot change within a request unless this class changes it, so
	 * it is remembered here and cleared by the two methods that alter the schema.
	 *
	 * @since 1.1.0
	 * @var bool|null
	 */
	private static $crawler_log_canonical;

	/**
	 * Returns the fully prefixed name of one of the plugin's tables.
	 *
	 * @since 1.0.0
	 *
	 * @param string $table Unprefixed identifier, one of self::TABLES.
	 * @return string Prefixed table name.
	 */
	public static function table_name( $table ) {
		global $wpdb;

		return $wpdb->prefix . 'aive_' . $table;
	}

	/**
	 * Returns every prefixed table name owned by the plugin.
	 *
	 * @since 1.0.0
	 *
	 * @return array<int, string> Prefixed table names.
	 */
	public static function table_names() {
		return array_map( array( self::class, 'table_name' ), self::TABLES );
	}

	/**
	 * Returns the CREATE TABLE statements, keyed by unprefixed identifier.
	 *
	 * Formatting is dictated by dbDelta(), which parses these statements with
	 * regular expressions: one column per line, and two spaces after PRIMARY
	 * KEY. Reformatting this method will silently stop dbDelta from recognising
	 * existing columns and make it attempt to re-add them on every upgrade.
	 *
	 * @since 1.0.0
	 *
	 * @return array<string, string> CREATE TABLE statements.
	 */
	public static function schema() {
		global $wpdb;

		$charset_collate = $wpdb->get_charset_collate();

		$schema = array();

		$schema['crawler_log'] = 'CREATE TABLE ' . self::table_name( 'crawler_log' ) . " (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			bot_name VARCHAR(100) NOT NULL,
			bot_type VARCHAR(50) NOT NULL DEFAULT 'search',
			request_uri VARCHAR(500) NOT NULL,
			request_method VARCHAR(10) NOT NULL DEFAULT 'GET',
			user_agent VARCHAR(500) NOT NULL,
			ip_address VARCHAR(100) DEFAULT NULL,
			status_code INT UNSIGNED DEFAULT NULL,
			visited_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			visited_at_utc DATETIME DEFAULT NULL,
			post_id BIGINT UNSIGNED DEFAULT NULL,
			PRIMARY KEY  (id),
			KEY bot_name (bot_name),
			KEY visited_at (visited_at),
			KEY visited_at_utc (visited_at_utc),
			KEY post_id (post_id)
		) $charset_collate;";

		/*
		 * `state` and `algorithm_version` were added for Module 5. The table
		 * itself predates that module - Module 0 created it ahead of time - but
		 * the two columns a bounded cron sweep needs to tell "never scanned",
		 * "scanned successfully" and "a previous scan errored" apart in SQL did
		 * not exist until now. dbDelta() adds both to an existing installation
		 * without touching the rows already there; a row written before this
		 * change reads as `state = 'scanned'`, `algorithm_version = 0`, which is
		 * deliberately stale under Module 5's version check rather than assumed
		 * current.
		 */
		$schema['answer_density'] = 'CREATE TABLE ' . self::table_name( 'answer_density' ) . " (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			post_id BIGINT UNSIGNED NOT NULL,
			post_type VARCHAR(50) NOT NULL,
			total_headings INT UNSIGNED NOT NULL DEFAULT 0,
			question_headings INT UNSIGNED NOT NULL DEFAULT 0,
			direct_answers INT UNSIGNED NOT NULL DEFAULT 0,
			buried_answers INT UNSIGNED NOT NULL DEFAULT 0,
			derivative_answers INT UNSIGNED NOT NULL DEFAULT 0,
			score INT UNSIGNED NOT NULL DEFAULT 0,
			state VARCHAR(10) NOT NULL DEFAULT 'scanned',
			algorithm_version SMALLINT UNSIGNED NOT NULL DEFAULT 0,
			details LONGTEXT DEFAULT NULL,
			scanned_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY  (id),
			UNIQUE KEY post_id (post_id),
			KEY score (score),
			KEY post_type (post_type)
		) $charset_collate;";

		$schema['citations'] = 'CREATE TABLE ' . self::table_name( 'citations' ) . " (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			engine VARCHAR(50) NOT NULL,
			query TEXT NOT NULL,
			cited TINYINT(1) NOT NULL DEFAULT 0,
			cited_url VARCHAR(500) DEFAULT NULL,
			response_snippet TEXT DEFAULT NULL,
			sentiment VARCHAR(20) DEFAULT NULL,
			checked_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY  (id),
			KEY engine (engine),
			KEY checked_at (checked_at),
			KEY cited (cited)
		) $charset_collate;";

		return $schema;
	}

	/**
	 * Creates or updates the plugin's tables and records the schema version.
	 *
	 * The dbDelta() helper is additive: it adds missing tables, columns and
	 * indexes and leaves existing data alone, which makes it safe to call on
	 * every activation and upgrade.
	 *
	 * Two things happen after it, in this order, and the order is the migration:
	 *
	 * 1. The canonical crawler timestamp column is verified to exist. dbDelta
	 *    reports what it attempted, not what the database accepted, so the
	 *    column is probed directly. If it is absent the schema version is left
	 *    behind deliberately - see `crawler_log_is_canonical()` for what that
	 *    buys - and nothing is deleted.
	 * 2. Crawler log rows written before canonical time are deleted. See
	 *    `purge_precanonical_crawler_log_rows()`.
	 *
	 * @since 1.0.0
	 *
	 * @return void
	 */
	public static function create_tables() {
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		dbDelta( implode( "\n", self::schema() ) );

		self::flush_schema_cache();

		if ( ! self::crawler_log_is_canonical() ) {
			/*
			 * Fail closed rather than half-migrated: the version option stays
			 * behind, so an admin request retries this, and no row is deleted on
			 * the strength of a column that is not there.
			 */
			Helpers::log(
				'The crawler log has no canonical timestamp column after a schema update. Leaving the schema version unchanged and deleting nothing.',
				array( 'table' => self::table_name( 'crawler_log' ) )
			);

			return;
		}

		self::purge_precanonical_crawler_log_rows();

		update_option( self::VERSION_OPTION, self::target_version(), false );
	}

	/**
	 * Deletes crawler log rows that predate canonical UTC timekeeping.
	 *
	 * These are rows holding only an offset-free site-local `visited_at`. That
	 * value cannot be converted to the instant it was written at: a local
	 * reading inside a daylight-saving fall-back hour maps to two instants, and
	 * the site's timezone at the time of the visit may since have changed and is
	 * not recorded anywhere. Inventing an offset would silently fabricate the
	 * measurement, so the rows are deleted instead - the privacy-conservative
	 * resolution the product contract requires, which also guarantees that every
	 * surviving row has exact elapsed-time semantics and a known retention date.
	 *
	 * Only the crawler log is touched. Posts, settings, registry data and the
	 * plugin's other tables are untouched, and this is not part of uninstall.
	 *
	 * Idempotent: after one successful run no row lacks the canonical value, and
	 * every row written since does, so a repeat deletes nothing.
	 *
	 * @since 1.1.0
	 *
	 * @return int|null Rows deleted, or null when the statement failed.
	 */
	public static function purge_precanonical_crawler_log_rows() {
		global $wpdb;

		$table = '`' . str_replace( '`', '', self::table_name( 'crawler_log' ) ) . '`';

		$previous = $wpdb->suppress_errors( true );
		$wpdb->flush();

		// No caller data in the statement: the identifiers are the plugin's own.
		$deleted = $wpdb->query( 'DELETE FROM ' . $table . ' WHERE ' . self::CRAWLER_CANONICAL_COLUMN . ' IS NULL' ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Migration on the plugin's own table; identifiers cannot be prepared and are not caller-supplied.
		$failed  = false === $deleted || '' !== (string) $wpdb->last_error;

		$wpdb->suppress_errors( $previous );

		if ( $failed ) {
			Helpers::log( 'Could not delete pre-canonical crawler log rows.', array( 'table' => self::table_name( 'crawler_log' ) ) );

			return null;
		}

		$deleted = (int) $deleted;

		// Only when something happened: an upgrade of an empty log is not news.
		if ( $deleted > 0 ) {
			Helpers::log(
				'Deleted crawler log rows written before canonical UTC timekeeping; their original instants could not be reconstructed.',
				array( 'rows' => $deleted )
			);
		}

		return $deleted;
	}

	/**
	 * Reports whether the crawler log can carry canonical UTC instants.
	 *
	 * This is the gate the crawler module checks before it logs, cleans up or
	 * aggregates. A database that dbDelta has not reached yet - a file-copy
	 * upgrade on a site whose admin nobody has loaded, or one where the ALTER
	 * failed - would otherwise either write rows with no canonical instant or
	 * evaluate an elapsed-time window against a column that is not there. Both
	 * are worse than doing nothing until the migration completes.
	 *
	 * Answered from the schema rather than from the version option, because the
	 * option records an intention and the column is the fact.
	 *
	 * @since 1.1.0
	 *
	 * @return bool True when the canonical column exists.
	 */
	public static function crawler_log_is_canonical() {
		if ( null === self::$crawler_log_canonical ) {
			self::$crawler_log_canonical = self::column_exists( 'crawler_log', self::CRAWLER_CANONICAL_COLUMN );
		}

		return self::$crawler_log_canonical;
	}

	/**
	 * Forgets the cached schema probe.
	 *
	 * Called by the two methods here that change the schema. A test that alters
	 * these tables by other means needs it too.
	 *
	 * @since 1.1.0
	 *
	 * @return void
	 */
	public static function flush_schema_cache() {
		self::$crawler_log_canonical = null;
	}

	/**
	 * Reports whether one of the plugin's tables has a given column.
	 *
	 * Errors are suppressed so that probing a table which does not exist is an
	 * answer rather than a diagnostic: this runs on frontend requests, where a
	 * printed database error would land in the response.
	 *
	 * @since 1.1.0
	 *
	 * @param string $table  Unprefixed identifier, one of self::TABLES.
	 * @param string $column Column name to look for.
	 * @return bool True when the column exists.
	 */
	private static function column_exists( $table, $column ) {
		global $wpdb;

		$name = '`' . str_replace( '`', '', self::table_name( $table ) ) . '`';

		// SHOW COLUMNS ... LIKE takes a string literal, so the column is bound.
		$sql = $wpdb->prepare( 'SHOW COLUMNS FROM ' . $name . ' LIKE %s', $wpdb->esc_like( $column ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Only the table identifier is interpolated, and it is the plugin's own.

		$previous = $wpdb->suppress_errors( true );

		$found = $wpdb->get_var( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Statement prepared above. Schema probe on the plugin's own table; caching a schema answer would defeat the check.

		$wpdb->suppress_errors( $previous );

		return $column === $found;
	}

	/**
	 * Drops every table owned by the plugin.
	 *
	 * Destructive and therefore only reachable from uninstall, never from
	 * deactivation: Module 0 keeps data across a deactivate/reactivate cycle.
	 *
	 * @since 1.0.0
	 *
	 * @return void
	 */
	public static function drop_tables() {
		global $wpdb;

		self::flush_schema_cache();

		foreach ( self::table_names() as $table ) {
			/*
			 * A table name cannot be bound as a prepared parameter. The value is
			 * built from $wpdb->prefix plus a hard-coded identifier from
			 * self::TABLES, so no caller-supplied data reaches this statement.
			 */
			$wpdb->query( 'DROP TABLE IF EXISTS `' . str_replace( '`', '', $table ) . '`' ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- DDL on the plugin's own tables; identifiers cannot be prepared and are not caller-supplied.
		}
	}

	/**
	 * Reports whether one of the plugin's tables exists.
	 *
	 * @since 1.0.0
	 *
	 * @param string $table Unprefixed identifier, one of self::TABLES.
	 * @return bool True when the table exists.
	 */
	public static function table_exists( $table ) {
		global $wpdb;

		$name = self::table_name( $table );

		// SHOW TABLES LIKE takes a string literal, so the name is bound normally.
		$found = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $name ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Schema probe on the plugin's own table; caching a schema answer would defeat the check.

		return $name === $found;
	}

	/**
	 * Returns the schema version this build expects.
	 *
	 * @since 1.0.0
	 *
	 * @return string Schema version.
	 */
	public static function target_version() {
		return defined( 'AIVE_DB_VERSION' ) ? (string) AIVE_DB_VERSION : '0';
	}

	/**
	 * Returns the schema version currently installed.
	 *
	 * @since 1.0.0
	 *
	 * @return string Installed schema version, or an empty string when absent.
	 */
	public static function installed_version() {
		return (string) get_option( self::VERSION_OPTION, '' );
	}

	/**
	 * Brings the schema up to date when the installed version has fallen behind.
	 *
	 * Called on admin requests so that an upgrade applied by overwriting plugin
	 * files - rather than by reactivating - still gets its tables migrated.
	 *
	 * @since 1.0.0
	 *
	 * @return void
	 */
	public static function maybe_upgrade() {
		if ( version_compare( self::installed_version(), self::target_version(), '>=' ) ) {
			return;
		}

		self::create_tables();
	}
}