<?php
/**
 * PK Core - schema creation and additive migrations.
 *
 * Two tables, each owned by a module and versioned INDEPENDENTLY:
 *  - {$wpdb->prefix}pkcore_classifications (classify module) - stored
 *    classifications for SITE content only (external corpora are classified
 *    on request and stored by the caller; PK-CORE-SPEC-00 section 6.0/10.2).
 *  - {$wpdb->prefix}pk_alerts (notify module) - the signal-alert audit trail.
 *
 * SCHEMA RULES (spec section 10.3):
 *  - Additive and forward-only. Never rename, retype or drop.
 *  - dbDelta runs when a DB version advances; the stored version is never
 *    lowered, and older cores skip migration when the stored version is
 *    already >= their own.
 *  - Do NOT write CREATE TABLE IF NOT EXISTS here: dbDelta needs the plain
 *    CREATE TABLE form to diff and add columns (the known dbDelta trap).
 *  - The core is a library: it NEVER drops its tables or options on any
 *    plugin's uninstall. Core data is a site asset.
 *
 * WHY TWO VERSION OPTIONS AND NOT ONE. The modules activate independently: a
 * site may run classify without notify, or notify without classify. A single
 * counter gates both tables on one number, so a classify-only site advances
 * it to N, and when a notify-requesting plugin is activated later the guard
 * sees installed >= N and skips - leaving pk_alerts uncreated and every
 * alert write failing silently. That is precisely the "measures but never
 * says so" failure this module exists to end, so each table carries its own
 * option and its own guard.
 *
 * @package PublisherKits\Core
 * @since   0.1.0
 */

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

if ( ! class_exists( 'PKCORE_Schema' ) ) {

	/**
	 * Class PKCORE_Schema
	 */
	class PKCORE_Schema {

		/**
		 * Bump on any (additive-only) change to the classifications table.
		 */
		const DB_VERSION = 1;

		/**
		 * Option holding the installed classifications schema version.
		 */
		const OPTION = 'pkcore_db_version';

		/**
		 * Bump on any (additive-only) change to the alerts table.
		 *
		 * @since 0.9.0
		 */
		const ALERTS_DB_VERSION = 1;

		/**
		 * Option holding the installed alerts schema version.
		 *
		 * @since 0.9.0
		 */
		const ALERTS_OPTION = 'pkcore_alerts_db_version';

		/**
		 * Fully-prefixed classifications table name.
		 *
		 * @return string
		 */
		public static function table_classifications() {
			global $wpdb;
			return $wpdb->prefix . 'pkcore_classifications';
		}

		/**
		 * Fully-prefixed alerts table name.
		 *
		 * Named pk_alerts (not pkcore_alerts) per the build brief: the alert
		 * log is estate furniture rather than core-internal storage, and the
		 * name is what an operator will type at a database prompt months
		 * later when asking "did anything warn us about this?".
		 *
		 * @since 0.9.0
		 * @return string
		 */
		public static function table_alerts() {
			global $wpdb;
			return $wpdb->prefix . 'pk_alerts';
		}

		/**
		 * Create/upgrade the schema when DB_VERSION has advanced. Idempotent.
		 *
		 * @return void
		 */
		public static function maybe_migrate() {
			$installed = (int) get_option( self::OPTION, 0 );
			if ( $installed >= self::DB_VERSION ) {
				return;
			}

			global $wpdb;
			require_once ABSPATH . 'wp-admin/includes/upgrade.php';

			$table   = self::table_classifications();
			$charset = $wpdb->get_charset_collate();

			// dbDelta-compatible: two spaces after PRIMARY KEY, no IF NOT EXISTS.
			$sql = "CREATE TABLE {$table} (
				id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
				object_type VARCHAR(32) NOT NULL DEFAULT 'post',
				object_id BIGINT UNSIGNED NOT NULL,
				topics LONGTEXT NULL,
				method VARCHAR(16) NOT NULL DEFAULT 'none',
				content_type VARCHAR(32) NULL,
				pack_revision INT UNSIGNED NOT NULL DEFAULT 0,
				published_at DATETIME NULL,
				classified_at DATETIME NOT NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY object (object_type, object_id),
				KEY published (published_at)
			) {$charset};";

			dbDelta( $sql );

			// Never lowered (spec 10.3 downgrade tolerance).
			if ( self::DB_VERSION > $installed ) {
				update_option( self::OPTION, self::DB_VERSION, false );
			}
		}

		/**
		 * Create/upgrade the alerts table when ALERTS_DB_VERSION has advanced.
		 * Idempotent. Guarded by its own option - see the class docblock.
		 *
		 * @since 0.9.0
		 * @return void
		 */
		public static function maybe_migrate_alerts() {
			$installed = (int) get_option( self::ALERTS_OPTION, 0 );
			if ( $installed >= self::ALERTS_DB_VERSION ) {
				return;
			}

			global $wpdb;
			require_once ABSPATH . 'wp-admin/includes/upgrade.php';

			$table   = self::table_alerts();
			$charset = $wpdb->get_charset_collate();

			// alert_group, not `group`: GROUP is a reserved word in MySQL and
			// would need back-ticking in every query that ever touches it.
			//
			// Every raised alert lands here, including ones that are never
			// emailed. status/suppressed_reason record WHY an alert did not
			// reach a human, which is the question this table exists to
			// answer after the fact.
			//
			// dbDelta-compatible: two spaces after PRIMARY KEY, no IF NOT EXISTS.
			$sql = "CREATE TABLE {$table} (
				id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
				code VARCHAR(64) NOT NULL DEFAULT '',
				alert_group VARCHAR(64) NOT NULL DEFAULT '',
				severity VARCHAR(16) NOT NULL DEFAULT 'info',
				site VARCHAR(190) NOT NULL DEFAULT '',
				title VARCHAR(255) NOT NULL DEFAULT '',
				detail TEXT NULL,
				context LONGTEXT NULL,
				status VARCHAR(20) NOT NULL DEFAULT 'pending',
				suppressed_reason VARCHAR(40) NOT NULL DEFAULT '',
				recipients TEXT NULL,
				created_at DATETIME NOT NULL,
				notified_at DATETIME NULL,
				PRIMARY KEY  (id),
				KEY code_site (code, site),
				KEY group_site_notified (alert_group, site, notified_at),
				KEY status_severity (status, severity),
				KEY created (created_at)
			) {$charset};";

			dbDelta( $sql );

			if ( self::ALERTS_DB_VERSION > $installed ) {
				update_option( self::ALERTS_OPTION, self::ALERTS_DB_VERSION, false );
			}
		}
	}
}
