<?php
/**
 * Database schema — custom tables, created/upgraded with dbDelta.
 *
 * Tables (all prefixed {$wpdb->prefix}sentrixa_):
 *   - sentrixa_hits          raw page-view events (kept ~60 days, then pruned)
 *   - sentrixa_sessions      one row per visit, updated as the visit continues
 *   - sentrixa_rollup        per-day / per-dimension pre-aggregated counts
 *   - sentrixa_site_search   on-site search-box terms
 *   - sentrixa_search_console  Google Search Console query data (phase 3)
 */

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

class SXANL_Schema {

	const DB_VERSION_OPTION = 'sxanl_db_version';
	const DB_VERSION        = 5;

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

	/**
	 * Activation: create tables, seed options, schedule cron.
	 */
	public static function activate() {
		self::install();
		SXANL_Settings::seed_defaults();
		SXANL_Rollup::schedule();
		SXANL_Visitor::rotate_salt(); // ensure a salt exists immediately
	}

	/**
	 * Run on every load when the stored DB version is behind (handles upgrades
	 * without needing re-activation).
	 */
	public static function maybe_upgrade() {
		if ( get_option( 'sxanl_db_error' ) ) {
			add_action( 'admin_notices', array( __CLASS__, 'db_error_notice' ) );
		}
		if ( (int) get_option( self::DB_VERSION_OPTION ) < self::DB_VERSION && ! get_transient( 'sxanl_db_retry' ) ) {
			self::install();
		}
	}

	/**
	 * Tell an administrator when the tables could not be created (instead of failing silently).
	 */
	public static function db_error_notice() {
		if ( ! current_user_can( 'manage_options' ) ) {
			return;
		}
		$tables = (string) get_option( 'sxanl_db_error' );
		echo '<div class="notice notice-error"><p>' . esc_html(
			sprintf(
				/* translators: %s: list of database table names */
				__( 'Sentrixa Analytics could not create these database tables: %s. Your database user may lack the CREATE or ALTER privilege. The plugin will retry in an hour.', 'sentrixa-analytics' ),
				$tables
			)
		) . '</p></div>';
	}

	/**
	 * Bring tables created by earlier versions in line with the current index sizes
	 * (every index must fit the legacy 767-byte InnoDB limit). Runs before dbDelta.
	 */
	private static function migrate_legacy() {
		global $wpdb;
		$installed = (int) get_option( self::DB_VERSION_OPTION );
		if ( $installed < 1 || $installed >= 5 ) {
			return; // fresh install, or already migrated
		}
		$exists = static function ( $name ) use ( $wpdb ) {
			return $name === $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $name ) ) ); // phpcs:ignore WordPress.DB
		};

		$rollup = self::table( 'rollup' );
		if ( $exists( $rollup ) ) {
			$wpdb->query( "UPDATE IGNORE $rollup SET dkey = LEFT(dkey, 160) WHERE CHAR_LENGTH(dkey) > 160" ); // phpcs:ignore WordPress.DB
			$wpdb->query( "DELETE FROM $rollup WHERE CHAR_LENGTH(dkey) > 160" ); // phpcs:ignore WordPress.DB
		}

		$ss = self::table( 'site_search' );
		if ( $exists( $ss ) ) {
			$wpdb->query( "UPDATE IGNORE $ss SET term = LEFT(term, 180) WHERE CHAR_LENGTH(term) > 180" ); // phpcs:ignore WordPress.DB
			$wpdb->query( "DELETE FROM $ss WHERE CHAR_LENGTH(term) > 180" ); // phpcs:ignore WordPress.DB
		}

		$sc = self::table( 'search_console' );
		if ( $exists( $sc ) ) {
			if ( ! $wpdb->get_var( "SHOW COLUMNS FROM $sc LIKE 'qp_hash'" ) ) { // phpcs:ignore WordPress.DB
				$wpdb->query( "ALTER TABLE $sc ADD COLUMN qp_hash CHAR(32) NOT NULL DEFAULT ''" ); // phpcs:ignore WordPress.DB
			}
			$wpdb->query( "UPDATE $sc SET qp_hash = MD5(CONCAT(query, CHAR(0), page)) WHERE qp_hash = ''" ); // phpcs:ignore WordPress.DB
			if ( $wpdb->get_var( "SHOW INDEX FROM $sc WHERE Key_name = 'day_query_page'" ) ) { // phpcs:ignore WordPress.DB
				$wpdb->query( "ALTER TABLE $sc DROP INDEX day_query_page" ); // phpcs:ignore WordPress.DB
			}
		}
	}

	/**
	 * Create / alter tables.
	 */
	public static function install() {
		global $wpdb;
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		$charset_collate = $wpdb->get_charset_collate();
		$hits            = self::table( 'hits' );
		$sessions        = self::table( 'sessions' );
		$rollup          = self::table( 'rollup' );
		$site_search     = self::table( 'site_search' );
		$search_console  = self::table( 'search_console' );

		$sql = array();

		$sql[] = "CREATE TABLE $hits (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			created_at DATETIME NOT NULL,
			day DATE NOT NULL,
			session_id CHAR(32) NOT NULL DEFAULT '',
			visitor_hash CHAR(40) NOT NULL DEFAULT '',
			is_new_visitor TINYINT(1) NOT NULL DEFAULT 0,
			is_entry TINYINT(1) NOT NULL DEFAULT 0,
			path VARCHAR(512) NOT NULL DEFAULT '',
			title VARCHAR(255) NOT NULL DEFAULT '',
			referrer_host VARCHAR(255) NOT NULL DEFAULT '',
			source_type VARCHAR(16) NOT NULL DEFAULT 'direct',
			source_name VARCHAR(64) NOT NULL DEFAULT '',
			country_code CHAR(2) NOT NULL DEFAULT '',
			region VARCHAR(80) NOT NULL DEFAULT '',
			device_type VARCHAR(12) NOT NULL DEFAULT 'unknown',
			browser VARCHAR(32) NOT NULL DEFAULT 'Other',
			os VARCHAR(24) NOT NULL DEFAULT 'Other',
			screen_bucket VARCHAR(8) NOT NULL DEFAULT 'unknown',
			lang VARCHAR(12) NOT NULL DEFAULT '',
			utm_source VARCHAR(80) NOT NULL DEFAULT '',
			utm_medium VARCHAR(80) NOT NULL DEFAULT '',
			utm_campaign VARCHAR(120) NOT NULL DEFAULT '',
			engaged_seconds SMALLINT UNSIGNED NOT NULL DEFAULT 0,
			is_bounce TINYINT(1) NOT NULL DEFAULT 1,
			PRIMARY KEY  (id),
			KEY day_idx (day),
			KEY session_idx (session_id),
			KEY created_idx (created_at)
		) $charset_collate;";

		$sql[] = "CREATE TABLE $sessions (
			session_id CHAR(32) NOT NULL,
			visitor_hash CHAR(40) NOT NULL DEFAULT '',
			started_at DATETIME NOT NULL,
			last_at DATETIME NOT NULL,
			day DATE NOT NULL,
			pageviews INT UNSIGNED NOT NULL DEFAULT 1,
			entry_path VARCHAR(512) NOT NULL DEFAULT '',
			exit_path VARCHAR(512) NOT NULL DEFAULT '',
			source_type VARCHAR(16) NOT NULL DEFAULT 'direct',
			source_name VARCHAR(64) NOT NULL DEFAULT '',
			referrer_host VARCHAR(255) NOT NULL DEFAULT '',
			utm_source VARCHAR(80) NOT NULL DEFAULT '',
			utm_medium VARCHAR(80) NOT NULL DEFAULT '',
			utm_campaign VARCHAR(120) NOT NULL DEFAULT '',
			country_code CHAR(2) NOT NULL DEFAULT '',
			region VARCHAR(80) NOT NULL DEFAULT '',
			device_type VARCHAR(12) NOT NULL DEFAULT 'unknown',
			browser VARCHAR(32) NOT NULL DEFAULT 'Other',
			os VARCHAR(24) NOT NULL DEFAULT 'Other',
			is_new_visitor TINYINT(1) NOT NULL DEFAULT 0,
			engaged_seconds INT UNSIGNED NOT NULL DEFAULT 0,
			PRIMARY KEY  (session_id),
			KEY day_idx (day),
			KEY visitor_idx (visitor_hash),
			KEY last_idx (last_at)
		) $charset_collate;";

		// One row per (day, metric, dimension-key). metric e.g. 'totals',
		// 'country', 'source', 'page', 'entry_page', 'exit_page', 'device',
		// 'browser', 'os', 'referrer_domain', 'utm_campaign', 'region'.
		$sql[] = "CREATE TABLE $rollup (
			day DATE NOT NULL,
			metric VARCHAR(24) NOT NULL,
			dkey VARCHAR(160) NOT NULL DEFAULT '',
			visitors INT UNSIGNED NOT NULL DEFAULT 0,
			sessions INT UNSIGNED NOT NULL DEFAULT 0,
			pageviews INT UNSIGNED NOT NULL DEFAULT 0,
			new_visitors INT UNSIGNED NOT NULL DEFAULT 0,
			bounces INT UNSIGNED NOT NULL DEFAULT 0,
			engaged_seconds BIGINT UNSIGNED NOT NULL DEFAULT 0,
			extra VARCHAR(120) NOT NULL DEFAULT '',
			PRIMARY KEY  (day, metric, dkey)
		) $charset_collate;";

		$sql[] = "CREATE TABLE $site_search (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			day DATE NOT NULL,
			term VARCHAR(180) NOT NULL DEFAULT '',
			searches INT UNSIGNED NOT NULL DEFAULT 1,
			results_min INT UNSIGNED NOT NULL DEFAULT 0,
			results_max INT UNSIGNED NOT NULL DEFAULT 0,
			PRIMARY KEY  (id),
			UNIQUE KEY day_term (day, term)
		) $charset_collate;";

		$sql[] = "CREATE TABLE $search_console (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			day DATE NOT NULL,
			query VARCHAR(255) NOT NULL DEFAULT '',
			page VARCHAR(512) NOT NULL DEFAULT '',
			country_code CHAR(3) NOT NULL DEFAULT '',
			clicks INT UNSIGNED NOT NULL DEFAULT 0,
			impressions INT UNSIGNED NOT NULL DEFAULT 0,
			position DECIMAL(5,1) NOT NULL DEFAULT 0,
			qp_hash CHAR(32) NOT NULL DEFAULT '',
			PRIMARY KEY  (id),
			UNIQUE KEY day_qp (day, qp_hash),
			KEY day_idx (day)
		) $charset_collate;";

		self::migrate_legacy();

		foreach ( $sql as $statement ) {
			dbDelta( $statement );
		}

		// Record the schema version only when every table really exists.
		$missing = array();
		foreach ( array( 'hits', 'sessions', 'rollup', 'site_search', 'search_console' ) as $t ) {
			$name = self::table( $t );
			if ( $name !== $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $name ) ) ) ) { // phpcs:ignore WordPress.DB
				$missing[] = $name;
			}
		}
		if ( $missing ) {
			update_option( 'sxanl_db_error', implode( ', ', $missing ), false );
			set_transient( 'sxanl_db_retry', 1, HOUR_IN_SECONDS );
			return false;
		}
		delete_option( 'sxanl_db_error' );
		delete_transient( 'sxanl_db_retry' );
		update_option( self::DB_VERSION_OPTION, self::DB_VERSION, false );
		return true;
	}

	/**
	 * Remove all tables and options — called from uninstall.php.
	 */
	public static function drop_all() {
		global $wpdb;
		$tables = array( 'hits', 'sessions', 'rollup', 'site_search', 'search_console' );
		foreach ( $tables as $t ) {
			$name = self::table( $t );
			$wpdb->query( "DROP TABLE IF EXISTS $name" ); // phpcs:ignore WordPress.DB
		}
	}
}