<?php

namespace AdFuz\Database;

use AdFuz\Helpers\Common;
use AdFuz\Helpers\TableName;
use Exception;
use AdFuz\Database\Migrations\MigrationRunner;

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

class Install {
	private const STATS_MONTHS_AHEAD = 24;
	private static $instance = null;
	private $charset_collate;
	private $wpdb;

	public function __construct() {
		global $wpdb;
		add_action( 'init', array( $this, 'adfuz_check_and_install_tables' ) );
		$this->charset_collate = $wpdb->get_charset_collate();
		$this->wpdb            = $wpdb;
	}

	public static function get_instance() {
		if ( self::$instance === null ) {
			self::$instance = new self();
		}

		return self::$instance;
	}

	public function adfuz_check_and_install_tables() {
		$current_version = get_option( 'adfuz_plugin_version', '0' );

		if ( version_compare( ADFUZ_PLUGIN_VERSION, $current_version, '>' ) ) {
			if ( ! function_exists( 'dbDelta' ) ) {
				require_once ABSPATH . 'wp-admin/includes/upgrade.php';
			}

			// Install stats table directly due to partitioning.
			$this->create_stats_table();

			$queries = array(
				$this->table_adfuz_ads(),
				$this->table_adfuz_ads_meta(),
				$this->table_adfuz_ad_spaces(),
				$this->table_adfuz_ad_spaces_meta(),
				$this->table_adfuz_campaigns(),
				$this->table_adfuz_campaign_ad_spaces(),
				$this->table_adfuz_campaign_ads(),
				$this->table_adfuz_campaigns_meta(),
				$this->table_adfuz_campaign_target_countries(),
				$this->table_adfuz_campaign_target_devices(),
				$this->table_adfuz_audit_logs(),
				$this->table_adfuz_audit_logs_meta(),
				$this->table_adfuz_urls(),
				$this->table_adfuz_event_dedup(),
				$this->table_adfuz_stats_aggregation_state(),
				$this->table_adfuz_daily_stats(),
				$this->table_adfuz_daily_stats_country(),
				$this->table_adfuz_daily_stats_device(),
				$this->table_adfuz_stats_export_queue(),
				$this->table_adfuz_jobs(),
			);

			foreach ( $queries as $query ) {
				try {
					dbDelta( $query );
				} catch ( Exception $e ) {
					Common::log_file(
						array(
							'Type'    => 'Install DB Error',
							'Message' => $e->getMessage(),
						)
					);
				}
			}

			try {
				MigrationRunner::run(
					$current_version,
					ADFUZ_PLUGIN_VERSION
				);
			} catch ( \Throwable $e ) {
				Common::log_file(
					array(
						'Type'    => 'Migration Error',
						'Message' => $e->getMessage(),
					)
				);

				// Do not mark the plugin version as completed.
				return;
			}

			update_option(
				'adfuz_plugin_version',
				ADFUZ_PLUGIN_VERSION
			);
		}
	}

	private function table_adfuz_ads() {
		return "CREATE TABLE IF NOT EXISTS {$this->wpdb->prefix}adfuz_ads (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                user_id BIGINT UNSIGNED DEFAULT NULL,
                ad_type VARCHAR(10) NOT NULL,
                title VARCHAR(100) NOT NULL,
                slug VARCHAR(255) NOT NULL,
                hide_title BOOL DEFAULT FALSE,
                description TEXT DEFAULT NULL,
                target_url VARCHAR(2083) DEFAULT NULL,
                media_url VARCHAR(2083) DEFAULT NULL,
                media_id BIGINT UNSIGNED DEFAULT NULL,
                script_html TEXT DEFAULT NULL,
                cta VARCHAR(100) DEFAULT NULL,
                cta_type VARCHAR(10) DEFAULT 'link',
                cta_position VARCHAR(10) DEFAULT 'left',
                link_to VARCHAR(10) DEFAULT 'link',
                status ENUM('active','paused','deleted', 'draft', 'pending') DEFAULT 'draft',
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                KEY idx_ad_type (ad_type),
                KEY idx_status (status),
                UNIQUE KEY idx_slug (slug),
                FOREIGN KEY (user_id) REFERENCES {$this->wpdb->users}(ID) ON DELETE SET NULL
            ) $this->charset_collate;";
	}

	private function table_adfuz_ads_meta() {
		return "CREATE TABLE IF NOT EXISTS {$this->wpdb->prefix}adfuz_ads_meta (
                meta_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                ad_id BIGINT UNSIGNED NOT NULL,
                meta_key VARCHAR(255),
                meta_value LONGTEXT,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (ad_id) REFERENCES {$this->wpdb->prefix}adfuz_ads(id) ON DELETE CASCADE,
                KEY meta_key (meta_key)
            ) $this->charset_collate;";
	}

	private function table_adfuz_ad_spaces() {
		return "CREATE TABLE IF NOT EXISTS {$this->wpdb->prefix}adfuz_ad_spaces (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                user_id BIGINT UNSIGNED DEFAULT NULL,
                fallback_ad_id BIGINT UNSIGNED DEFAULT NULL,
                ad_format VARCHAR(20) DEFAULT NULL,
                ad_sequence VARCHAR(10) DEFAULT NULL,
                title VARCHAR(100) NOT NULL,
                slug VARCHAR(100) UNIQUE NOT NULL,
                width INT NOT NULL,
                height INT NOT NULL,
                availability INT NOT NULL DEFAULT 1,
                status ENUM('active', 'inactive', 'draft', 'deleted') DEFAULT 'active',
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                KEY idx_status (status),
    			KEY idx_slug (slug),
    			KEY idx_ad_format (ad_format),
    			KEY idx_ad_sequence (ad_sequence),
                FOREIGN KEY (user_id) REFERENCES {$this->wpdb->users}(ID) ON DELETE SET NULL,
    			FOREIGN KEY (fallback_ad_id) REFERENCES {$this->wpdb->prefix}adfuz_ads(id) ON DELETE SET NULL
            ) $this->charset_collate;";
	}

	private function table_adfuz_ad_spaces_meta() {
		return "CREATE TABLE IF NOT EXISTS {$this->wpdb->prefix}adfuz_ad_spaces_meta (
		    meta_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
		    ad_space_id BIGINT UNSIGNED NOT NULL,
		    meta_key VARCHAR(255),
		    meta_value LONGTEXT,
		    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
		    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
		    FOREIGN KEY (ad_space_id) REFERENCES {$this->wpdb->prefix}adfuz_ad_spaces(id) ON DELETE CASCADE,
		    KEY meta_key (meta_key)
		) $this->charset_collate;";
	}

	private function table_adfuz_campaigns() {
		return "CREATE TABLE IF NOT EXISTS {$this->wpdb->prefix}adfuz_campaigns (
        id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        user_id BIGINT UNSIGNED DEFAULT NULL,
        slug VARCHAR(255) NOT NULL,
        name VARCHAR(255) NOT NULL,
        expiry_date DATETIME DEFAULT NULL,
        expiry_impressions INT DEFAULT NULL,
        expiry_clicks INT DEFAULT NULL,
        targeting JSON DEFAULT NULL,
        start_date DATETIME DEFAULT NULL,
        end_date DATETIME DEFAULT NULL,
        pricing_model ENUM('flat', 'per_impression', 'per_click', 'mixed') DEFAULT 'flat',
        price DECIMAL(10,2) DEFAULT 0.00,
        currency VARCHAR(5) DEFAULT 'USD',
        fallback_ad_id BIGINT UNSIGNED DEFAULT NULL,
        status ENUM('pending','active','paused','expired','deleted') DEFAULT 'pending',
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        KEY idx_status (status),
        UNIQUE KEY idx_slug (slug),
        FOREIGN KEY (user_id) REFERENCES {$this->wpdb->users}(ID) ON DELETE SET NULL,
        FOREIGN KEY (fallback_ad_id) REFERENCES {$this->wpdb->prefix}adfuz_ads(id) ON DELETE SET NULL
    ) $this->charset_collate;";
	}

	private function table_adfuz_campaign_ad_spaces() {
		return "CREATE TABLE IF NOT EXISTS {$this->wpdb->prefix}adfuz_campaign_ad_spaces (
        id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        campaign_id BIGINT UNSIGNED NOT NULL,
        ad_space_id BIGINT UNSIGNED NOT NULL,
        display_order INT DEFAULT 0,
        status ENUM('active','paused') DEFAULT 'active',
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (campaign_id) REFERENCES {$this->wpdb->prefix}adfuz_campaigns(id) ON DELETE CASCADE,
        FOREIGN KEY (ad_space_id) REFERENCES {$this->wpdb->prefix}adfuz_ad_spaces(id) ON DELETE CASCADE,
        UNIQUE KEY unique_campaign_adspace (campaign_id, ad_space_id)
    ) $this->charset_collate;";
	}

	private function table_adfuz_campaign_ads() {
		return "CREATE TABLE IF NOT EXISTS {$this->wpdb->prefix}adfuz_campaign_ads (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
               	campaign_id BIGINT UNSIGNED NOT NULL,
               	ad_id BIGINT UNSIGNED NOT NULL,
                display_order INT DEFAULT 0,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (campaign_id) REFERENCES {$this->wpdb->prefix}adfuz_campaigns(id) ON DELETE CASCADE,
                FOREIGN KEY (ad_id) REFERENCES {$this->wpdb->prefix}adfuz_ads(id) ON DELETE CASCADE,
                UNIQUE KEY unique_campaign_ad (campaign_id,ad_id)
            ) $this->charset_collate;";
	}

	private function table_adfuz_campaign_target_countries() {
		return "CREATE TABLE IF NOT EXISTS {$this->wpdb->prefix}adfuz_campaign_target_countries (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
               campaign_id BIGINT UNSIGNED NOT NULL,
                country_code CHAR(3) NOT NULL,
                KEY idx_country_code (country_code),
                FOREIGN KEY (campaign_id) REFERENCES {$this->wpdb->prefix}adfuz_campaigns(id) ON DELETE CASCADE
            ) $this->charset_collate;";
	}

	private function table_adfuz_campaign_target_devices() {
		return "CREATE TABLE IF NOT EXISTS {$this->wpdb->prefix}adfuz_campaign_target_devices (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
               campaign_id BIGINT UNSIGNED NOT NULL,
                device_type ENUM('mobile', 'tablet', 'desktop', 'laptop', 'smarttv', 'all') NOT NULL,
                KEY idx_device_type (device_type),
                FOREIGN KEY (campaign_id) REFERENCES {$this->wpdb->prefix}adfuz_campaigns(id) ON DELETE CASCADE
            ) $this->charset_collate;";
	}

	private function table_adfuz_campaigns_meta() {
		return "CREATE TABLE IF NOT EXISTS {$this->wpdb->prefix}adfuz_campaigns_meta (
                meta_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
               campaign_id BIGINT UNSIGNED NOT NULL,
                meta_key VARCHAR(255),
                meta_value LONGTEXT,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (campaign_id) REFERENCES {$this->wpdb->prefix}adfuz_campaigns(id) ON DELETE CASCADE,
                KEY meta_key (meta_key)
            ) $this->charset_collate;";
	}

	private function table_adfuz_audit_logs() {
		return "CREATE TABLE IF NOT EXISTS {$this->wpdb->prefix}adfuz_audit_logs (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                user_id BIGINT UNSIGNED DEFAULT NULL,
                log_action VARCHAR(100) NOT NULL,
                entity_type VARCHAR(100) NOT NULL,
                entity_id BIGINT UNSIGNED NOT NULL,
                change_details JSON DEFAULT NULL,
                ip_address VARCHAR(45),
    			user_agent VARCHAR(500) DEFAULT NULL,
    			log_level ENUM('info', 'warning', 'error', 'critical') DEFAULT 'info',
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (user_id) REFERENCES {$this->wpdb->users}(ID) ON DELETE SET NULL
            ) $this->charset_collate;";
	}

	private function table_adfuz_audit_logs_meta() {
		return "CREATE TABLE IF NOT EXISTS {$this->wpdb->prefix}adfuz_audit_logs_meta (
		    meta_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
		   	audit_log_id BIGINT UNSIGNED NOT NULL,
		    meta_key VARCHAR(255),
		    meta_value LONGTEXT,
		    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
		    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
		    FOREIGN KEY (audit_log_id) REFERENCES {$this->wpdb->prefix}adfuz_audit_logs(id) ON DELETE CASCADE,
		    KEY meta_key (meta_key)
		) $this->charset_collate;";
	}

	private function table_adfuz_urls(): string {
		$table = $this->wpdb->prefix . 'adfuz_urls';

		return "CREATE TABLE IF NOT EXISTS {$table} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			url_hash CHAR(64) NOT NULL,
			url TEXT NOT NULL,
			created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			UNIQUE KEY ux_url_hash (url_hash)
		) ENGINE=InnoDB {$this->charset_collate}";
	}

	private function table_adfuz_event_dedup(): string {
		$table = $this->wpdb->prefix . 'adfuz_event_dedup';

		return "CREATE TABLE IF NOT EXISTS {$table} (
			event_key CHAR(32) NOT NULL,
			interaction_at DATETIME NOT NULL,
			created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (event_key),
			KEY idx_interaction_at (interaction_at)
		) ENGINE=InnoDB {$this->charset_collate}";
	}

	private function table_adfuz_stats(): string {
		$table = $this->wpdb->prefix . 'adfuz_stats';

		return "CREATE TABLE IF NOT EXISTS {$table} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			event_key CHAR(32) DEFAULT NULL,
			ad_id BIGINT UNSIGNED NOT NULL,
			campaign_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
			ad_space_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
			interaction_type VARCHAR(32) NOT NULL,
			is_valid TINYINT(1) NOT NULL DEFAULT 1,
			invalid_reason VARCHAR(64) DEFAULT NULL,
			country_code CHAR(2) NOT NULL DEFAULT '',
			city VARCHAR(100) DEFAULT NULL,
			latitude DECIMAL(10,7) DEFAULT NULL,
			longitude DECIMAL(10,7) DEFAULT NULL,
			device_type VARCHAR(20) NOT NULL DEFAULT 'other',
			browser VARCHAR(40) NOT NULL DEFAULT 'other',
			visitor_hash CHAR(64) DEFAULT NULL,
			session_hash CHAR(64) DEFAULT NULL,
			ip_hash CHAR(64) DEFAULT NULL,
			url_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
			referrer_domain VARCHAR(253) DEFAULT NULL,
			utm_source VARCHAR(100) DEFAULT NULL,
			utm_medium VARCHAR(100) DEFAULT NULL,
			utm_campaign VARCHAR(100) DEFAULT NULL,
			occurred_at DATETIME DEFAULT NULL,
			interaction_at DATETIME NOT NULL,
			received_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (id, interaction_at),
			KEY idx_interaction_at (interaction_at),
			KEY idx_received_at (received_at),
			KEY idx_event_date (event_key, interaction_at),
			KEY idx_ad_date (ad_id, interaction_at),
			KEY idx_campaign_date (campaign_id, interaction_at),
			KEY idx_space_date (ad_space_id, interaction_at),
			KEY idx_campaign_space_date (campaign_id, ad_space_id, interaction_at),
			KEY idx_type_date (interaction_type, interaction_at),
			KEY idx_country_date (country_code, interaction_at),
			KEY idx_device_date (device_type, interaction_at),
			KEY idx_visitor_date (visitor_hash, interaction_at),
			KEY idx_ip_date (ip_hash, interaction_at),
			KEY idx_url_date (url_id, interaction_at)
		) ENGINE=InnoDB {$this->charset_collate}
		{$this->get_stats_partitions_sql()}";
	}

	private function table_adfuz_stats_aggregation_state(): string {
		$table = $this->wpdb->prefix . 'adfuz_stats_aggregation_state';

		return "CREATE TABLE IF NOT EXISTS {$table} (
			state_key VARCHAR(64) NOT NULL,
			last_processed_at DATETIME DEFAULT NULL,
			updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (state_key)
		) ENGINE=InnoDB {$this->charset_collate}";
	}

	private function table_adfuz_daily_stats(): string {
		$table = $this->wpdb->prefix . 'adfuz_daily_stats';

		return "CREATE TABLE IF NOT EXISTS {$table} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			ad_id BIGINT UNSIGNED NOT NULL,
			campaign_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
			ad_space_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
			country_code CHAR(2) NOT NULL DEFAULT '',
			device_type VARCHAR(20) NOT NULL DEFAULT 'other',
			stats_date DATE NOT NULL,
			impressions BIGINT UNSIGNED NOT NULL DEFAULT 0,
			clicks BIGINT UNSIGNED NOT NULL DEFAULT 0,
			plays BIGINT UNSIGNED NOT NULL DEFAULT 0,
			skips BIGINT UNSIGNED NOT NULL DEFAULT 0,
			pauses BIGINT UNSIGNED NOT NULL DEFAULT 0,
			closes BIGINT UNSIGNED NOT NULL DEFAULT 0,
			conversions BIGINT UNSIGNED NOT NULL DEFAULT 0,
			invalid_events BIGINT UNSIGNED NOT NULL DEFAULT 0,
			created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			UNIQUE KEY ux_daily (ad_id, campaign_id, ad_space_id, country_code, device_type, stats_date),
			KEY idx_date (stats_date),
			KEY idx_ad_date (ad_id, stats_date),
			KEY idx_campaign_date (campaign_id, stats_date),
			KEY idx_space_date (ad_space_id, stats_date),
			KEY idx_campaign_space_date (campaign_id, ad_space_id, stats_date),
			KEY idx_country_date (country_code, stats_date),
			KEY idx_device_date (device_type, stats_date)
		) ENGINE=InnoDB {$this->charset_collate}";
	}

	private function table_adfuz_daily_stats_country(): string {
		$table = $this->wpdb->prefix . 'adfuz_daily_stats_country';

		return "CREATE TABLE IF NOT EXISTS {$table} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			ad_id BIGINT UNSIGNED NOT NULL,
			campaign_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
			ad_space_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
			stats_date DATE NOT NULL,
			country_code CHAR(2) NOT NULL,
			impressions BIGINT UNSIGNED NOT NULL DEFAULT 0,
			clicks BIGINT UNSIGNED NOT NULL DEFAULT 0,
			plays BIGINT UNSIGNED NOT NULL DEFAULT 0,
			skips BIGINT UNSIGNED NOT NULL DEFAULT 0,
			pauses BIGINT UNSIGNED NOT NULL DEFAULT 0,
			closes BIGINT UNSIGNED NOT NULL DEFAULT 0,
			conversions BIGINT UNSIGNED NOT NULL DEFAULT 0,
			invalid_events BIGINT UNSIGNED NOT NULL DEFAULT 0,
			PRIMARY KEY (id),
			UNIQUE KEY ux_country_daily (ad_id, campaign_id, ad_space_id, stats_date, country_code),
			KEY idx_date (stats_date),
			KEY idx_campaign_space_date (campaign_id, ad_space_id, stats_date),
			KEY idx_country_date (country_code, stats_date)
		) ENGINE=InnoDB {$this->charset_collate}";
	}

	private function table_adfuz_daily_stats_device(): string {
		$table = $this->wpdb->prefix . 'adfuz_daily_stats_device';

		return "CREATE TABLE IF NOT EXISTS {$table} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			ad_id BIGINT UNSIGNED NOT NULL,
			campaign_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
			ad_space_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
			stats_date DATE NOT NULL,
			device_type VARCHAR(20) NOT NULL,
			impressions BIGINT UNSIGNED NOT NULL DEFAULT 0,
			clicks BIGINT UNSIGNED NOT NULL DEFAULT 0,
			plays BIGINT UNSIGNED NOT NULL DEFAULT 0,
			skips BIGINT UNSIGNED NOT NULL DEFAULT 0,
			pauses BIGINT UNSIGNED NOT NULL DEFAULT 0,
			closes BIGINT UNSIGNED NOT NULL DEFAULT 0,
			conversions BIGINT UNSIGNED NOT NULL DEFAULT 0,
			invalid_events BIGINT UNSIGNED NOT NULL DEFAULT 0,
			PRIMARY KEY (id),
			UNIQUE KEY ux_device_daily (ad_id, campaign_id, ad_space_id, stats_date, device_type),
			KEY idx_date (stats_date),
			KEY idx_campaign_space_date (campaign_id, ad_space_id, stats_date),
			KEY idx_device_date (device_type, stats_date)
		) ENGINE=InnoDB {$this->charset_collate}";
	}

	private function table_adfuz_stats_export_queue(): string {
		$table = $this->wpdb->prefix . 'adfuz_stats_export_queue';

		return "CREATE TABLE IF NOT EXISTS {$table} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			job_key VARCHAR(191) NOT NULL,
			status VARCHAR(20) NOT NULL DEFAULT 'queued',
			filters LONGTEXT,
			file_name VARCHAR(255) DEFAULT NULL,
			file_path TEXT,
			file_url TEXT,
			total_rows BIGINT UNSIGNED NOT NULL DEFAULT 0,
			created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			started_at DATETIME DEFAULT NULL,
			completed_at DATETIME DEFAULT NULL,
			expires_at DATETIME DEFAULT NULL,
			error_message LONGTEXT,
			PRIMARY KEY (id),
			UNIQUE KEY ux_job_key (job_key),
			KEY idx_status_created (status, created_at),
			KEY idx_expires_at (expires_at)
		) ENGINE=InnoDB {$this->charset_collate}";
	}

	private function table_adfuz_jobs(): string {
		$table = $this->wpdb->prefix . 'adfuz_jobs';

		return "CREATE TABLE IF NOT EXISTS {$table} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			job_key VARCHAR(191) NOT NULL,
			job_type VARCHAR(50) NOT NULL,
			status VARCHAR(20) NOT NULL DEFAULT 'queued',
			progress_step VARCHAR(255) DEFAULT NULL,
			payload LONGTEXT,
			result LONGTEXT,
			error_message LONGTEXT,
			created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			started_at DATETIME DEFAULT NULL,
			completed_at DATETIME DEFAULT NULL,
			expires_at DATETIME DEFAULT NULL,
			PRIMARY KEY (id),
			UNIQUE KEY ux_job_key (job_key),
			KEY idx_type_status (job_type, status),
			KEY idx_status_created (status, created_at),
			KEY idx_expires_at (expires_at)
		) ENGINE=InnoDB {$this->charset_collate}";
	}

	private function create_stats_table(): bool {
		$table_exists = $this->wpdb->get_var(
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			$this->wpdb->prepare(
				'SHOW TABLES LIKE %s',
				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
				$this->wpdb->esc_like( $this->wpdb->prefix . 'adfuz_stats' )
			)
		);

		if ( $table_exists ) {
			return true;
		}

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$result = $this->wpdb->query( $this->table_adfuz_stats() );

		if ( false === $result ) {
			try {
				throw new Exception( $this->wpdb->last_error ?: 'Unable to create partitioned stats table.' );
			} catch ( Exception $e ) {
				\AdFuz\Helpers\Common::log_file(
					array(
						'Type' => 'Install Stats DB Error',
						$e->getMessage(),
					)
				);
			}
		}

		return true;
	}

	private function get_stats_partitions_sql( int $months_ahead = self::STATS_MONTHS_AHEAD ): string {
		$months_ahead = max( 1, $months_ahead );
		$timezone     = new \DateTimeZone( 'UTC' );
		$first_month  = new \DateTimeImmutable( 'first day of this month 00:00:00', $timezone );
		$partitions   = array(
			"PARTITION p_past VALUES LESS THAN (TO_DAYS('{$first_month->format( 'Y-m-d' )}'))",
		);

		for ( $offset = 0; $offset < $months_ahead; $offset++ ) {
			$month      = $first_month->modify( "+{$offset} months" );
			$next_month = $month->modify( '+1 month' );
			$partitions[] = "PARTITION p{$month->format( 'Ym' )} VALUES LESS THAN (TO_DAYS('{$next_month->format( 'Y-m-d' )}'))";
		}

		$partitions[] = 'PARTITION p_future VALUES LESS THAN MAXVALUE';

		return "PARTITION BY RANGE (TO_DAYS(interaction_at)) (\n\t\t\t" . implode( ",\n\t\t\t", $partitions ) . "\n\t\t)";
	}

	private function get_table_method_map(): array {
		$map = array();

		foreach ( TableName::adfuz_definitions( true ) as $def ) {
			$table_name         = $def['table'];
			$method             = 'table_' . str_replace( $this->wpdb->prefix, '', $table_name );
			$map[ $table_name ] = $method;
		}

		// Map added stats tables
		$map[ $this->wpdb->prefix . 'adfuz_urls' ] = 'table_adfuz_urls';
		$map[ $this->wpdb->prefix . 'adfuz_event_dedup' ] = 'table_adfuz_event_dedup';
		$map[ $this->wpdb->prefix . 'adfuz_stats_aggregation_state' ] = 'table_adfuz_stats_aggregation_state';
		$map[ $this->wpdb->prefix . 'adfuz_daily_stats' ] = 'table_adfuz_daily_stats';
		$map[ $this->wpdb->prefix . 'adfuz_daily_stats_country' ] = 'table_adfuz_daily_stats_country';
		$map[ $this->wpdb->prefix . 'adfuz_daily_stats_device' ] = 'table_adfuz_daily_stats_device';
		$map[ $this->wpdb->prefix . 'adfuz_stats_export_queue' ] = 'table_adfuz_stats_export_queue';

		return $map;
	}

	/**
	 * (Re-)install a single table by its full table name.
	 * Called by DatabaseToolsController::install_table().
	 */
	public function install_single_table( string $table_name ): bool {
		if ( ! function_exists( 'dbDelta' ) ) {
			require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		}

		// Handle Stats partition specifically
		if ( $this->wpdb->prefix . 'adfuz_stats' === $table_name ) {
			return $this->create_stats_table();
		}

		// 1. Try free-owned tables first.
		$map = $this->get_table_method_map();

		if ( isset( $map[ $table_name ] ) ) {
			$method = $map[ $table_name ];
			try {
				dbDelta( $this->$method() );

				return true;
			} catch ( Exception $e ) {
				Common::log_file(
					array(
						'Type'    => 'Install Single Table Error',
						'Message' => $e->getMessage(),
					)
				);

				return false;
			}
		}

		/**
		 * Filter: adfuz/install/single_table
		 *
		 * Allows Pro (or any add-on) to handle installation of tables
		 * not owned by the free plugin. The free plugin knows nothing
		 * about who handles this — it just returns the result.
		 *
		 * @param  bool  $result  Default false (unhandled).
		 * @param  string  $table_name  Full prefixed table name.
		 */
		return (bool) apply_filters( 'adfuz/install/single_table', false, $table_name );
	}

	public function install_all_tables(): void {
		if ( ! function_exists( 'dbDelta' ) ) {
			require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		}

		// Free tables.
		foreach ( $this->get_table_method_map() as $table_name => $method ) {
			try {
				if ( $this->wpdb->prefix . 'adfuz_stats' === $table_name ) {
					$this->create_stats_table();
					continue;
				}
				dbDelta( $this->$method() );
			} catch ( Exception $e ) {
				Common::log_file(
					array(
						'Type'    => 'Install All Tables Error',
						'Message' => $e->getMessage(),
					)
				);
			}
		}

		/**
		 * Action: adfuz/install/all_tables
		 *
		 * Fired after all free tables are installed. Pro hooks in here
		 * to install its own tables. The free plugin has no knowledge
		 * of what runs on this hook.
		 */
		do_action( 'adfuz/install/all_tables' );
	}
}