<?php
/**
 * Database schema, upgrades and retention cleanup.
 *
 * @package Visitrix
 */

defined( 'ABSPATH' ) || exit;

// Custom tables: direct queries are expected here, results are not cacheable.
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange

/**
 * Database layer.
 */
class Visitrix_DB {

	/**
	 * Database of the statistics tables: $wpdb or the external database (see Visitrix_Storage).
	 *
	 * @return wpdb|Visitrix_Remote_DB
	 */
	public static function db() {
		return Visitrix_Storage::db();
	}

	/**
	 * Statistics are stored in a separate database.
	 *
	 * @return bool
	 */
	public static function is_external() {
		return Visitrix_Storage::is_external();
	}

	/**
	 * Visits (sessions) table name.
	 *
	 * @return string
	 */
	public static function visits_table() {
		return self::db()->prefix . 'visitrix_visits';
	}

	/**
	 * Pageviews table name.
	 *
	 * @return string
	 */
	public static function hits_table() {
		return self::db()->prefix . 'visitrix_hits';
	}

	/**
	 * Visitors table name: one row per known visitor (first visit, visit count).
	 *
	 * @return string
	 */
	public static function visitors_table() {
		return self::db()->prefix . 'visitrix_visitors';
	}

	/**
	 * Goals table name: one row per reached goal (form, call, e-mail, messenger, download, outbound link).
	 *
	 * @return string
	 */
	public static function goals_table() {
		return self::db()->prefix . 'visitrix_goals';
	}

	/**
	 * Activation hook.
	 */
	public static function activate() {
		self::install();
		self::backfill_visitors();
		update_option( 'visitrix_db_version', VISITRIX_DB_VERSION, false );
		add_option( Visitrix_Settings::OPTION, Visitrix_Settings::defaults() );
		if ( ! wp_next_scheduled( 'visitrix_daily_cleanup' ) ) {
			wp_schedule_event( time() + HOUR_IN_SECONDS, 'daily', 'visitrix_daily_cleanup' );
		}
	}

	/**
	 * Deactivation hook.
	 */
	public static function deactivate() {
		wp_clear_scheduled_hook( 'visitrix_daily_cleanup' );
	}

	/**
	 * Runs the installer when the schema version changes (e.g. after an update).
	 */
	public static function maybe_upgrade() {
		if ( VISITRIX_DB_VERSION === get_option( 'visitrix_db_version' ) && ( ! self::is_external() || Visitrix_Storage::remote_ready() ) ) {
			return;
		}
		// Only one request runs the upgrade; parallel tracker hits must not repeat it.
		$lock = (int) get_option( 'visitrix_upgrade_lock', 0 );
		if ( $lock && time() - $lock < 10 * MINUTE_IN_SECONDS ) {
			return;
		}
		if ( $lock ) {
			delete_option( 'visitrix_upgrade_lock' );
		}
		if ( ! add_option( 'visitrix_upgrade_lock', time(), '', false ) ) {
			return;
		}
		self::install();
		self::backfill_visitors();
		update_option( 'visitrix_db_version', VISITRIX_DB_VERSION, false );
		delete_option( 'visitrix_upgrade_lock' );
	}

	/**
	 * Creates or updates the tables.
	 */
	public static function install() {
		global $wpdb;
		if ( self::is_external() ) {
			Visitrix_Storage::install_remote();
			return;
		}
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		dbDelta( self::schema( $wpdb->get_charset_collate() ) );
	}

	/**
	 * CREATE TABLE statements of the statistics tables (dbDelta format).
	 *
	 * @param string $charset Charset clause.
	 * @return string
	 */
	public static function schema( $charset ) {
		$visits   = self::visits_table();
		$hits     = self::hits_table();
		$visitors = self::visitors_table();
		$goals    = self::goals_table();

		return "CREATE TABLE {$visits} (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  visit_key char(32) NOT NULL,
  visitor_key char(32) NOT NULL,
  started_at datetime NOT NULL,
  ts_start int(10) unsigned NOT NULL,
  ts_last int(10) unsigned NOT NULL,
  duration int(10) unsigned NOT NULL DEFAULT 0,
  pageviews smallint(5) unsigned NOT NULL DEFAULT 1,
  is_new tinyint(1) unsigned NOT NULL DEFAULT 0,
  engaged tinyint(1) unsigned NOT NULL DEFAULT 1,
  id_type char(1) NOT NULL DEFAULT 's',
  source_type varchar(16) NOT NULL DEFAULT 'direct',
  source_name varchar(100) NOT NULL DEFAULT '',
  referrer varchar(255) NOT NULL DEFAULT '',
  landing varchar(255) NOT NULL DEFAULT '',
  utm_source varchar(100) NOT NULL DEFAULT '',
  utm_medium varchar(100) NOT NULL DEFAULT '',
  utm_campaign varchar(100) NOT NULL DEFAULT '',
  device varchar(10) NOT NULL DEFAULT 'desktop',
  browser varchar(32) NOT NULL DEFAULT '',
  os varchar(32) NOT NULL DEFAULT '',
  PRIMARY KEY  (id),
  UNIQUE KEY visit_key (visit_key),
  KEY started_at (started_at),
  KEY visitor_key (visitor_key),
  KEY ts_last (ts_last)
) {$charset};
CREATE TABLE {$hits} (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  visit_id bigint(20) unsigned NOT NULL,
  created_at datetime NOT NULL,
  path varchar(255) NOT NULL DEFAULT '',
  title varchar(255) NOT NULL DEFAULT '',
  post_id bigint(20) unsigned NOT NULL DEFAULT 0,
  scroll tinyint(3) unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY  (id),
  KEY created_at (created_at),
  KEY visit_id (visit_id),
  KEY post_id (post_id)
) {$charset};
CREATE TABLE {$visitors} (
  visitor_key char(32) NOT NULL,
  first_seen datetime NOT NULL,
  last_seen datetime NOT NULL,
  visits int(10) unsigned NOT NULL DEFAULT 1,
  PRIMARY KEY  (visitor_key),
  KEY last_seen (last_seen)
) {$charset};
CREATE TABLE {$goals} (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  visit_id bigint(20) unsigned NOT NULL,
  created_at datetime NOT NULL,
  type varchar(16) NOT NULL DEFAULT '',
  target varchar(200) NOT NULL DEFAULT '',
  path varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY  (id),
  KEY created_at (created_at),
  KEY visit_id (visit_id)
) {$charset};";
	}

	/**
	 * Fills the visitors table from visits recorded by older versions.
	 */
	public static function backfill_visitors() {
		$db = self::db();
		if ( $db instanceof Visitrix_ClickHouse_DB ) {
			return; // ClickHouse builds the visitors from the events.
		}
		$db->query(
			$db->prepare(
				'INSERT IGNORE INTO %i (visitor_key, first_seen, last_seen, visits)
				SELECT visitor_key, MIN(started_at), MAX(started_at), COUNT(*) FROM %i WHERE id_type <> %s GROUP BY visitor_key',
				self::visitors_table(),
				self::visits_table(),
				'a'
			)
		);
	}

	/**
	 * Deletes data older than the retention period (daily cron).
	 */
	public static function cleanup() {
		$db = self::db();
		if ( $db instanceof Visitrix_ClickHouse_DB ) {
			Visitrix_ClickHouse::maintain( $db ); // Old events expire by TTL.
			return;
		}
		$settings = Visitrix_Settings::get();
		$cutoff   = wp_date( 'Y-m-d H:i:s', time() - (int) $settings['retention_days'] * DAY_IN_SECONDS );

		// Batched deletes keep locks short on large tables.
		$targets = array(
			array( self::hits_table(), 'created_at' ),
			array( self::goals_table(), 'created_at' ),
			array( self::visits_table(), 'started_at' ),
			array( self::visitors_table(), 'last_seen' ),
		);
		foreach ( $targets as $target ) {
			for ( $i = 0; $i < 50; $i++ ) {
				$deleted = $db->query( $db->prepare( 'DELETE FROM %i WHERE %i < %s LIMIT 5000', $target[0], $target[1], $cutoff ) );
				if ( ! $deleted ) {
					break;
				}
			}
		}

		// Visits without any interaction (robots) are kept for a day only.
		for ( $i = 0; $i < 50; $i++ ) {
			$ids = array_map(
				'intval',
				(array) $db->get_col(
					$db->prepare( 'SELECT id FROM %i WHERE engaged = 0 AND ts_last < %d LIMIT 2000', self::visits_table(), time() - DAY_IN_SECONDS )
				)
			);
			if ( ! $ids ) {
				break;
			}
			$in = implode( ',', array_fill( 0, count( $ids ), '%d' ) );
			// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- $in contains only %d placeholders.
			$db->query( $db->prepare( "DELETE FROM %i WHERE visit_id IN ($in)", array_merge( array( self::hits_table() ), $ids ) ) );
			$db->query( $db->prepare( "DELETE FROM %i WHERE visit_id IN ($in)", array_merge( array( self::goals_table() ), $ids ) ) );
			$db->query( $db->prepare( "DELETE FROM %i WHERE id IN ($in)", array_merge( array( self::visits_table() ), $ids ) ) );
			// phpcs:enable
		}
	}

	/**
	 * Removes all collected statistics.
	 */
	public static function truncate() {
		$db = self::db();
		if ( $db instanceof Visitrix_ClickHouse_DB ) {
			Visitrix_ClickHouse::truncate( $db );
			Visitrix_Storage::flush_reports();
			return;
		}
		foreach ( self::tables() as $table ) {
			$db->query( $db->prepare( 'TRUNCATE TABLE %i', $table ) );
		}
		Visitrix_Storage::flush_reports();
	}

	/**
	 * Drops the plugin tables of the WordPress database (used on uninstall). An external database is left as is.
	 */
	public static function drop() {
		global $wpdb;
		foreach ( array( 'visitrix_hits', 'visitrix_visits', 'visitrix_visitors', 'visitrix_goals' ) as $name ) {
			$table = $wpdb->prefix . $name;
			$wpdb->query( $wpdb->prepare( 'DROP TABLE IF EXISTS %i', $table ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- removes the plugin's own tables.
		}
	}

	/**
	 * All plugin tables.
	 *
	 * @return string[]
	 */
	private static function tables() {
		return array( self::hits_table(), self::visits_table(), self::visitors_table(), self::goals_table() );
	}
}