<?php
namespace Footloom;

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

// Queries in this file read and write the plugin's own custom tables, so they use $wpdb directly and
// are not cached. Table names come from Plugin::table() and every value from the request goes through
// $wpdb->prepare(). The sniffs below cannot see that through the interpolated table names.
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter

// Database tables, activation, upgrades and daily housekeeping.
class Install {

	public static function register() {
		// Keep tables up to date when the plugin is updated without re-activation. Priority 0 so it
		// runs before tracking requests, which are answered and ended at priority 1.
		add_action( 'plugins_loaded', array( self::class, 'maybe_upgrade' ), 0 );
		add_action( 'footloom_daily', array( self::class, 'daily' ) );
	}

	public static function activate() {
		self::create_tables();

		if ( ! wp_next_scheduled( 'footloom_daily' ) ) {
			wp_schedule_event( time() + HOUR_IN_SECONDS, 'daily', 'footloom_daily' );
		}

		add_option( 'footloom_goals', array() );
		add_option( 'footloom_event_rules', array() );
		add_option( 'footloom_redirects', array() );
		add_option( 'footloom_redirect_prefix', 'go' );
		// 0 means keep data forever. Deleting old data is opt-in from Settings.
		add_option( 'footloom_retention_days', 0 );
		add_option( 'footloom_exclude_admins', 1 );
		// The country database is only downloaded after the site owner turns it on in Settings.
		add_option( 'footloom_geo', 0 );
	}

	public static function deactivate() {
		wp_clear_scheduled_hook( 'footloom_daily' );
		wp_clear_scheduled_hook( 'footloom_geo_update' );
	}

	public static function maybe_upgrade() {
		if ( get_option( 'footloom_db_version' ) !== Plugin::DB_VERSION ) {
			self::create_tables();
		}
	}

	public static function create_tables() {
		global $wpdb;
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		$charset = $wpdb->get_charset_collate();

		dbDelta( "CREATE TABLE " . Plugin::table( 'visitors' ) . " (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			uid char(36) NOT NULL,
			first_seen_at datetime NOT NULL,
			last_seen_at datetime NOT NULL,
			session_count int(10) unsigned NOT NULL DEFAULT 0,
			PRIMARY KEY  (id),
			UNIQUE KEY uid (uid),
			KEY last_seen_at (last_seen_at)
		) $charset;" );

		dbDelta( "CREATE TABLE " . Plugin::table( 'sessions' ) . " (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			visitor_id bigint(20) unsigned NOT NULL,
			is_new tinyint(1) NOT NULL DEFAULT 0,
			started_at datetime NOT NULL,
			last_seen_at datetime NOT NULL,
			entry_path varchar(255) NOT NULL DEFAULT '',
			exit_path varchar(255) NOT NULL DEFAULT '',
			referrer_host varchar(191) NOT NULL DEFAULT '',
			referrer_url varchar(255) NOT NULL DEFAULT '',
			utm_source varchar(100) DEFAULT NULL,
			utm_medium varchar(100) DEFAULT NULL,
			utm_campaign varchar(100) DEFAULT NULL,
			country char(2) DEFAULT NULL,
			device varchar(10) NOT NULL DEFAULT '',
			browser varchar(30) NOT NULL DEFAULT '',
			os varchar(30) NOT NULL DEFAULT '',
			screen_width smallint(5) unsigned DEFAULT NULL,
			language varchar(10) NOT NULL DEFAULT '',
			pageview_count smallint(5) unsigned NOT NULL DEFAULT 0,
			last_view_key char(16) NOT NULL DEFAULT '',
			left_at datetime DEFAULT NULL,
			PRIMARY KEY  (id),
			KEY visitor_seen (visitor_id,last_seen_at),
			KEY started_at (started_at),
			KEY last_seen_at (last_seen_at)
		) $charset;" );

		dbDelta( "CREATE TABLE " . Plugin::table( 'pageviews' ) . " (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			session_id bigint(20) unsigned NOT NULL,
			path varchar(255) NOT NULL DEFAULT '',
			query varchar(255) NOT NULL DEFAULT '',
			view_key char(16) NOT NULL DEFAULT '',
			title varchar(255) NOT NULL DEFAULT '',
			post_id bigint(20) unsigned DEFAULT NULL,
			is_404 tinyint(1) NOT NULL DEFAULT 0,
			search_term varchar(191) DEFAULT NULL,
			search_results smallint(5) unsigned DEFAULT NULL,
			created_at datetime NOT NULL,
			duration int(10) unsigned NOT NULL DEFAULT 0,
			PRIMARY KEY  (id),
			KEY created_at (created_at),
			KEY session_id (session_id),
			KEY view_key (view_key),
			KEY path_created (path(191),created_at)
		) $charset;" );

		dbDelta( "CREATE TABLE " . Plugin::table( 'events' ) . " (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			session_id bigint(20) unsigned NOT NULL,
			pageview_id bigint(20) unsigned NOT NULL,
			name varchar(100) NOT NULL,
			label varchar(255) DEFAULT NULL,
			value decimal(12,2) DEFAULT NULL,
			created_at datetime NOT NULL,
			PRIMARY KEY  (id),
			KEY name_created (name,created_at),
			KEY session_id (session_id),
			KEY created_at (created_at)
		) $charset;" );

		dbDelta( "CREATE TABLE " . Plugin::table( 'clicks' ) . " (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			slug varchar(100) NOT NULL,
			created_at datetime NOT NULL,
			referrer_host varchar(191) NOT NULL DEFAULT '',
			country char(2) DEFAULT NULL,
			device varchar(10) NOT NULL DEFAULT '',
			browser varchar(30) NOT NULL DEFAULT '',
			os varchar(30) NOT NULL DEFAULT '',
			PRIMARY KEY  (id),
			KEY slug_created (slug,created_at),
			KEY created_at (created_at)
		) $charset;" );

		update_option( 'footloom_db_version', Plugin::DB_VERSION );
	}

	// Daily housekeeping: delete old data (only if a retention period is set) and refresh the country database
	// once a month (only if it is turned on).
	public static function daily() {
		$days = (int) get_option( 'footloom_retention_days', 0 );
		if ( $days > 0 ) {
			self::delete_before( gmdate( 'Y-m-d H:i:s', time() - $days * DAY_IN_SECONDS ) );
		}

		$file = Geo::file();
		if ( Geo::enabled() && ( ! file_exists( $file ) || filemtime( $file ) < time() - 30 * DAY_IN_SECONDS ) ) {
			Geo::update();
		}
	}

	// Deletes all analytics data, including go link clicks, older than the given UTC datetime.
	public static function delete_before( $cutoff ) {
		global $wpdb;

		$sessions  = Plugin::table( 'sessions' );
		$pageviews = Plugin::table( 'pageviews' );
		$events    = Plugin::table( 'events' );
		$visitors  = Plugin::table( 'visitors' );

		$wpdb->query( $wpdb->prepare( "DELETE FROM $events WHERE created_at < %s", $cutoff ) );
		$wpdb->query( $wpdb->prepare( 'DELETE FROM ' . Plugin::table( 'clicks' ) . ' WHERE created_at < %s', $cutoff ) );
		$wpdb->query( $wpdb->prepare( "DELETE FROM $pageviews WHERE created_at < %s", $cutoff ) );
		$wpdb->query( $wpdb->prepare( "DELETE FROM $sessions WHERE last_seen_at < %s", $cutoff ) );
		// Visitors whose visits have all been deleted.
		$wpdb->query( "DELETE v FROM $visitors v LEFT JOIN $sessions s ON s.visitor_id = v.id WHERE s.id IS NULL" );
	}
}