<?php
/**
 * Database schema installer & migrator.
 *
 * Uses dbDelta() for safe, incremental upgrades.
 * No wp_postmeta is touched — ever.
 *
 * @package PulseViews\Database
 */

declare( strict_types=1 );

namespace PulseViews\Database;

if ( ! defined( 'ABSPATH' ) ) {
	exit; // Exit if accessed directly.
}

// phpcs:disable WordPress.DB.DirectDatabaseQuery

final class Schema {

	/** Current schema version stored in wp_options. */
	private const OPTION_KEY = 'pulseviews_db_version';

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

		$collate = $wpdb->has_cap( 'collation' )
			? $wpdb->get_charset_collate()
			: '';

		$prefix = $wpdb->prefix;

		// ── Raw view events ────────────────────────────────────────────────────
		dbDelta( "
			CREATE TABLE {$prefix}pulseviews_views (
				id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
				post_id      BIGINT UNSIGNED NOT NULL,
				viewed_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
				ip_hash      VARCHAR(64)     NOT NULL DEFAULT '',
				session_key  VARCHAR(40)     NOT NULL DEFAULT '',
				user_agent   VARCHAR(255)    NOT NULL DEFAULT '',
				is_bot       TINYINT(1)      NOT NULL DEFAULT 0,
				country_code CHAR(2)         NOT NULL DEFAULT '',
				referrer     VARCHAR(512)    NOT NULL DEFAULT '',
				PRIMARY KEY  (id),
				KEY          idx_post_viewed  (post_id, viewed_at),
				KEY          idx_viewed_at    (viewed_at),
				KEY          idx_ip_post      (ip_hash, post_id, viewed_at),
				KEY          idx_session_post (session_key, post_id)
			) ENGINE=InnoDB $collate;
		" );

		// ── Daily/weekly/monthly aggregates ───────────────────────────────────
		dbDelta( "
			CREATE TABLE {$prefix}pulseviews_aggregates (
				id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
				post_id      BIGINT UNSIGNED NOT NULL,
				period_date  DATE            NOT NULL,
				period_type  ENUM('day','week','month') NOT NULL DEFAULT 'day',
				view_count   INT UNSIGNED    NOT NULL DEFAULT 0,
				unique_count INT UNSIGNED    NOT NULL DEFAULT 0,
				PRIMARY KEY  (id),
				UNIQUE KEY   uniq_post_period (post_id, period_date, period_type),
				KEY          idx_period_date  (period_date, period_type),
				KEY          idx_post_agg     (post_id, period_type, period_date)
			) ENGINE=InnoDB $collate;
		" );

		// ── Per-post totals cache ──────────────────────────────────────────────
		dbDelta( "
			CREATE TABLE {$prefix}pulseviews_post_totals (
				post_id      BIGINT UNSIGNED NOT NULL,
				total_views  BIGINT UNSIGNED NOT NULL DEFAULT 0,
				today_views  INT UNSIGNED    NOT NULL DEFAULT 0,
				week_views   INT UNSIGNED    NOT NULL DEFAULT 0,
				month_views  INT UNSIGNED    NOT NULL DEFAULT 0,
				last_viewed  DATETIME                 DEFAULT NULL,
				updated_at   TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
				PRIMARY KEY  (post_id)
			) ENGINE=InnoDB $collate;
		" );

		// ── Exclusion rules ───────────────────────────────────────────────────
		dbDelta( "
			CREATE TABLE {$prefix}pulseviews_exclusions (
				id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
				type       ENUM('ip','ip_range','role','user_agent','user_id') NOT NULL,
				value      VARCHAR(255)    NOT NULL,
				label      VARCHAR(100)    NOT NULL DEFAULT '',
				created_at TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
				PRIMARY KEY (id),
				KEY         idx_type (type)
			) ENGINE=InnoDB $collate;
		" );

		update_option( self::OPTION_KEY, PULSEVIEWS_DB_VERSION, false );
	}

	public function maybe_upgrade(): void {
		$installed = (string) get_option( self::OPTION_KEY, '0' );

		if ( version_compare( $installed, PULSEVIEWS_DB_VERSION, '<' ) ) {
			$this->install();
		}
	}

	public static function uninstall(): void {
		global $wpdb;

		$tables = [
			$wpdb->prefix . 'pulseviews_views',
			$wpdb->prefix . 'pulseviews_aggregates',
			$wpdb->prefix . 'pulseviews_post_totals',
			$wpdb->prefix . 'pulseviews_exclusions',
		];

		foreach ( $tables as $table ) {
			$wpdb->query( $wpdb->prepare( 'DROP TABLE IF EXISTS %i', $table ) );
		}

		delete_option( self::OPTION_KEY );
		delete_option( 'pulseviews_settings' );
		delete_option( 'pulseviews_license' );

		wp_clear_scheduled_hook( 'pulseviews_daily_aggregate' );
		wp_clear_scheduled_hook( 'pulseviews_cleanup_old_views' );
	}

	public static function table_names(): array {
		global $wpdb;
		return [
			'views'      => $wpdb->prefix . 'pulseviews_views',
			'aggregates' => $wpdb->prefix . 'pulseviews_aggregates',
			'totals'     => $wpdb->prefix . 'pulseviews_post_totals',
			'exclusions' => $wpdb->prefix . 'pulseviews_exclusions',
		];
	}
}