<?php
declare(strict_types=1);

namespace WebCalendar\WP\Database;

defined( 'ABSPATH' ) || exit;

/**
 * Handles database operations and migrations.
 */
class Database {

	/**
	 * Option name for the database version.
	 */
	const VERSION_OPTION = 'webcalendar_wp_db_version';

	/**
	 * Current database version.
	 */
	const DB_VERSION = '1.5.2';

	/**
	 * Initialize the database.
	 */
	public function init(): void {
		$installed_version = get_option( self::VERSION_OPTION );

		if ( $installed_version !== self::DB_VERSION ) {
			$this->migrate( is_string( $installed_version ) ? $installed_version : '0.0.0' );
			update_option( self::VERSION_OPTION, self::DB_VERSION );
		}
	}

	/**
	 * Run database migrations.
	 *
	 * @param string $from_version Version being upgraded from.
	 */
	public function migrate( string $from_version = '0.0.0' ): void {
		global $wpdb;

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

		$tables = Schema::get_mvp_tables( $wpdb->prefix );

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

		if ( version_compare( $from_version, '1.0.0', '<' ) ) {
			$this->migrate_holiday_creator( $wpdb );
		}

		if ( version_compare( $from_version, '1.0.0', '<' ) ) {
			\WebCalendar\WP\Activator::register_capabilities();
		}

		if ( version_compare( $from_version, '1.3.0', '<' ) ) {
			$this->add_cal_image_column( $wpdb );
		}

		if ( version_compare( $from_version, '1.4.0', '<' ) ) {
			$this->migrate_empty_owner_junction_rows( $wpdb );
		}

		// Gated at 1.5.1, not 1.5.0: 1.5.0 shipped nowhere, but dev and
		// staging installs are stamped with it and lack cat_is_tag, which
		// breaks every category save. The step is idempotent, so letting
		// them back through costs one SHOW COLUMNS per column.
		if ( version_compare( $from_version, '1.5.1', '<' ) ) {
			$this->add_parity_columns( $wpdb );
		}
	}

	/**
	 * Add the core-4.4+/4.6 columns if missing: venue and organizer
	 * references (Epic 22) and the conference label (Epic 26) on
	 * webcal_entry, plus cat_is_tag on webcal_categories (core Epic 23 —
	 * PdoCategoryRepository::save() names it in every INSERT/UPDATE, so
	 * without it saving a category fails outright).
	 *
	 * The five new tables come from dbDelta; columns on an existing table
	 * use explicit ALTERs per the post-release-schema-change guideline.
	 *
	 * @param \wpdb $wpdb WordPress database object.
	 */
	private function add_parity_columns( $wpdb ): void {
		$tables = array(
			'webcal_entry'      => array(
				'cal_conference_label' => 'VARCHAR(60) DEFAULT NULL',
				'cal_venue_id'         => 'INT DEFAULT NULL',
				'cal_organizer_id'     => 'INT DEFAULT NULL',
			),
			'webcal_categories' => array(
				'cat_is_tag' => "CHAR(1) DEFAULT 'N'",
			),
		);

		foreach ( $tables as $suffix => $columns ) {
			$this->add_columns_if_missing( $wpdb, $wpdb->prefix . $suffix, $columns );
		}
	}

	/**
	 * Add each column to $table if it is not already present.
	 *
	 * Idempotent: a SHOW COLUMNS check guards every ALTER, so re-running a
	 * migration on an already-upgraded install is a no-op.
	 *
	 * The column map is typed literal-string so the concatenated ALTER stays
	 * a literal-string for static analysis — it is never built from input.
	 *
	 * @param \wpdb                               $wpdb    WordPress database object.
	 * @param string                              $table   Fully prefixed table name.
	 * @param array<literal-string, literal-string> $columns Column name => definition.
	 */
	private function add_columns_if_missing( $wpdb, string $table, array $columns ): void {
		foreach ( $columns as $column => $definition ) {
			$check = $wpdb->prepare( 'SHOW COLUMNS FROM %i LIKE %s', $table, $column );
			if ( ! is_string( $check ) ) {
				continue;
			}
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- $check is from $wpdb->prepare() above
			$existing = $wpdb->get_var( $check );
			if ( is_string( $existing ) && '' !== $existing ) {
				continue;
			}

			// Column names and definitions come from hardcoded maps, never input.
			$alter = $wpdb->prepare( 'ALTER TABLE %i ADD COLUMN ' . $column . ' ' . $definition, $table ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			if ( is_string( $alter ) ) {
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- $alter is from $wpdb->prepare() above
				$wpdb->query( $alter );
			}
		}
	}

	/**
	 * Add the cal_image column to webcal_entry if missing.
	 *
	 * webcalendar-core's PdoEventRepository writes to cal_image in every
	 * event save. Early plugin releases shipped without the column, which
	 * caused "Unknown column 'cal_image'" errors on every event save.
	 *
	 * Idempotent: checks for the column before attempting the ALTER.
	 * Uses $wpdb->query() per the post-release-schema-change guideline
	 * in CLAUDE.md (dbDelta is unreliable for ALTER TABLE).
	 *
	 * @param \wpdb $wpdb WordPress database object.
	 */
	private function add_cal_image_column( $wpdb ): void {
		$table = $wpdb->prefix . 'webcal_entry';

		$check = $wpdb->prepare(
			'SHOW COLUMNS FROM %i LIKE %s',
			$table,
			'cal_image'
		);
		if ( ! is_string( $check ) ) {
			return;
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- $check is from $wpdb->prepare() above
		$existing = $wpdb->get_var( $check );
		if ( is_string( $existing ) && $existing !== '' ) {
			return;
		}

		$alter = $wpdb->prepare(
			'ALTER TABLE %i ADD COLUMN cal_image VARCHAR(2048) DEFAULT NULL',
			$table
		);
		if ( is_string( $alter ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- $alter is from $wpdb->prepare() above
			$wpdb->query( $alter );
		}
	}

	/**
	 * Migrate legacy webcal_entry_categories rows with cat_owner=''.
	 *
	 * webcalendar-core's assignToEvent() always writes the junction row
	 * with cat_owner = the assigning user's login. But real-world data
	 * from early installs (imports, legacy WebCalendar DBs, and some
	 * direct-SQL seed paths) contains rows where cat_owner is an empty
	 * string. Those rows are invisible to core's hardened
	 * reassignEvents() and getEventCountByOwner() methods, which scope
	 * by a non-empty user login.
	 *
	 * This one-shot migration attributes every empty-owner junction row
	 * to the event creator (webcal_entry.cal_create_by).
	 *
	 * Safety:
	 *   - Pre-deletes empty-owner rows that would collide on the composite
	 *     PK (cal_id, cat_id, cat_order, cat_owner) with an existing
	 *     non-empty-owner row.
	 *   - Uses a single UPDATE with a JOIN for the remaining rows.
	 *   - Idempotent: guarded by the DB_VERSION gate in migrate().
	 *
	 * @param \wpdb $wpdb WordPress database object.
	 */
	private function migrate_empty_owner_junction_rows( $wpdb ): void {
		$junction = $wpdb->prefix . 'webcal_entry_categories';
		$entry    = $wpdb->prefix . 'webcal_entry';

		// Step 1: delete empty-owner rows that would collide with an
		// existing non-empty-owner row at the same (cal_id, cat_id,
		// cat_order). Those were duplicate assignments; the non-empty
		// version (written through assignToEvent) is the canonical one.
		$dedupe = $wpdb->prepare(
			'DELETE ec_empty FROM %i AS ec_empty
			 JOIN %i AS e ON e.cal_id = ec_empty.cal_id
			 JOIN %i AS ec_exist
			   ON ec_exist.cal_id   = ec_empty.cal_id
			  AND ec_exist.cat_id   = ec_empty.cat_id
			  AND ec_exist.cat_order = ec_empty.cat_order
			  AND ec_exist.cat_owner = e.cal_create_by
			 WHERE ec_empty.cat_owner = %s',
			$junction,
			$entry,
			$junction,
			''
		);
		if ( is_string( $dedupe ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- prepared above
			$wpdb->query( $dedupe );
		}

		// Step 2: rewrite the remaining empty-owner rows to carry the
		// event creator's login.
		/** @psalm-suppress TooManyArguments -- wpdb::prepare is variadic; Psalm stub is fixed-arity. */
		$migrate = $wpdb->prepare(
			'UPDATE %i AS ec
			 JOIN %i AS e ON e.cal_id = ec.cal_id
			 SET ec.cat_owner = e.cal_create_by
			 WHERE ec.cat_owner = %s
			   AND e.cal_create_by IS NOT NULL
			   AND e.cal_create_by <> %s',
			$junction,
			$entry,
			'',
			''
		);
		if ( is_string( $migrate ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- prepared above
			$wpdb->query( $migrate );
		}
	}

	/**
	 * Migrate holiday events from __webcal_global__ to __holidays__ nonuser_cal.
	 *
	 * One-time migration added in 1.0.0. Safe to re-run (idempotent UPDATE).
	 *
	 * @param \wpdb $wpdb WordPress database object.
	 */
	private function migrate_holiday_creator( $wpdb ): void {
		/** @psalm-suppress TooManyArguments -- wpdb::prepare is variadic; Psalm stub is fixed-arity. */
		$q1 = $wpdb->prepare(
			'UPDATE %i SET cal_create_by = %s WHERE cal_create_by = %s',
			$wpdb->prefix . 'webcal_entry',
			'__holidays__',
			'__webcal_global__'
		);
		if ( is_string( $q1 ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- $q1 is from $wpdb->prepare() above
			$wpdb->query( $q1 );
		}

		/** @psalm-suppress TooManyArguments -- wpdb::prepare is variadic; Psalm stub is fixed-arity. */
		$q2 = $wpdb->prepare(
			'UPDATE %i SET cal_login = %s WHERE cal_login = %s',
			$wpdb->prefix . 'webcal_entry_user',
			'__holidays__',
			'__webcal_global__'
		);
		if ( is_string( $q2 ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- $q2 is from $wpdb->prepare() above
			$wpdb->query( $q2 );
		}
	}
}