<?php
/**
 * Database schema installer and uninstaller.
 *
 * @since   1.0.0
 * @package DonationRouter
 */

declare( strict_types=1 );
namespace DonationRouter;

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

/**
 * Manages the plugin's custom database tables via dbDelta().
 *
 * Tables created:
 *  - {prefix}ador_accounts       — merchant credential records
 *  - {prefix}ador_country_routing — per-country account routing rules
 *
 * @since 1.0.0
 */
class Database_Installer {

	/**
	 * Current schema version.
	 *
	 * @since 1.0.0
	 *
	 * @var string
	 */
	public const SCHEMA_VERSION = '1.1.0';

	/**
	 * WordPress option key that stores the installed schema version.
	 *
	 * @since 1.0.0
	 *
	 * @var string
	 */
	private const VERSION_OPTION = 'ador_db_version';

	/**
	 * Create or upgrade tables to the current schema version.
	 *
	 * @since 1.0.0
	 *
	 * @return void
	 */
	public static function install(): void {
		$installed = get_option( self::VERSION_OPTION, '' );

		if ( version_compare( (string) $installed, self::SCHEMA_VERSION, '>=' ) ) {
			return;
		}

		self::create_tables();
		self::migrate( (string) $installed );

		update_option( self::VERSION_OPTION, self::SCHEMA_VERSION );
	}

	/**
	 * Run incremental migrations from $from_version to SCHEMA_VERSION.
	 *
	 * @since 1.0.0
	 *
	 * @param string $from_version Previously installed schema version.
	 *
	 * @return void
	 */
	private static function migrate( string $from_version ): void {
		unset( $from_version );

		// Idempotent and fully guarded, so it is safe to run on every (re)apply.
		// Running it unconditionally also repairs an environment whose version
		// option was lost while the legacy 1.0.0 table survived — the composite
		// (merchant_id, mode) unique would otherwise never replace the old
		// single-column unique.
		self::migrate_add_account_mode();
	}

	/**
	 * Add the per-account `mode` column and replace the legacy single-column
	 * UNIQUE(merchant_id) with UNIQUE(merchant_id, mode) — the 1.0.0 → 1.1.0
	 * step — so the same merchant can be managed independently in live and
	 * sandbox.
	 *
	 * The dbDelta pass in create_tables() adds the column and the composite index
	 * but cannot drop the legacy index; every step here is guarded so the
	 * migration is idempotent.
	 *
	 * @since 1.1.0
	 *
	 * @return void
	 */
	private static function migrate_add_account_mode(): void {
		global $wpdb;

		$table = $wpdb->prefix . 'ador_accounts';

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$has_mode = $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$table}` LIKE %s", 'mode' ) );
		if ( null === $has_mode ) {
			$wpdb->query( "ALTER TABLE `{$table}` ADD COLUMN mode VARCHAR(20) NOT NULL DEFAULT 'live' AFTER merchant_id" );
		}

		$has_legacy_index = $wpdb->get_var( $wpdb->prepare( "SHOW INDEX FROM `{$table}` WHERE Key_name = %s", 'merchant_id' ) );
		if ( null !== $has_legacy_index ) {
			$wpdb->query( "ALTER TABLE `{$table}` DROP INDEX merchant_id" );
		}

		$has_composite_index = $wpdb->get_var( $wpdb->prepare( "SHOW INDEX FROM `{$table}` WHERE Key_name = %s", 'merchant_id_mode' ) );
		if ( null === $has_composite_index ) {
			$wpdb->query( "ALTER TABLE `{$table}` ADD UNIQUE KEY merchant_id_mode (merchant_id, mode)" );
		}
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	}

	/**
	 * Execute dbDelta() for all plugin tables.
	 *
	 * @since 1.0.0
	 *
	 * @return void
	 */
	private static function create_tables(): void {
		global $wpdb;

		$charset_collate = $wpdb->get_charset_collate();

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

		$accounts_table = $wpdb->prefix . 'ador_accounts';
		$routing_table  = $wpdb->prefix . 'ador_country_routing';

		$sql = "CREATE TABLE {$accounts_table} (
			id                    BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			account_label         VARCHAR(191)        NOT NULL,
			merchant_id           VARCHAR(191)        NOT NULL,
			mode                  VARCHAR(20)         NOT NULL DEFAULT 'live',
			account_country       CHAR(2)             NOT NULL,
			legal_entity_name     VARCHAR(255)        NOT NULL,
			legal_entity_tax_id   VARCHAR(64)         NOT NULL,
			legal_entity_address  TEXT                NOT NULL,
			encrypted_credentials LONGTEXT            NOT NULL,
			webhook_id            VARCHAR(191)        DEFAULT NULL,
			is_active             TINYINT(1)          NOT NULL DEFAULT 1,
			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 merchant_id_mode (merchant_id, mode)
		) {$charset_collate};

		CREATE TABLE {$routing_table} (
			id                  BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			country_code        CHAR(2)             NOT NULL,
			form_id             BIGINT(20) UNSIGNED NOT NULL,
			account_id          BIGINT(20) UNSIGNED NOT NULL,
			display_label       VARCHAR(255)        NOT NULL,
			display_description TEXT,
			sort_order          INT                 NOT NULL DEFAULT 0,
			is_active           TINYINT(1)          NOT NULL DEFAULT 1,
			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 country_code (country_code),
			KEY form_id (form_id),
			KEY account_id (account_id)
		) {$charset_collate};";

		dbDelta( $sql );
	}

	/**
	 * Drop plugin tables and remove plugin options.
	 *
	 * @since 1.0.0
	 *
	 * @return void
	 */
	public static function uninstall(): void {
		global $wpdb;

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->query( "DROP TABLE IF EXISTS {$wpdb->prefix}ador_country_routing" );
		$wpdb->query( "DROP TABLE IF EXISTS {$wpdb->prefix}ador_accounts" );
		// phpcs:enable

		// Fixed option keys the plugin persists.
		$options = array(
			self::VERSION_OPTION,
			'ador_default_form_id',
			'ador_geolocation_enabled',
			'ador_geolocation_provider',
			'ador_pdf_receipts_enabled',
			'ador_popup_default_trigger',
			'ador_popup_dismissible',
			'ador_popup_heading_default',
			'ador_receipt_legal_review_acknowledged',
			'ador_routing_fallbacks',
		);
		foreach ( $options as $option ) {
			delete_option( $option );
		}

		// Dynamic-suffix options (per-account receipt templates + sequences) and
		// every plugin transient (admin notices, webhook idempotency + signature
		// cache) — none of which have fixed keys.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->query(
			$wpdb->prepare(
				"DELETE FROM {$wpdb->options} WHERE option_name LIKE %s OR option_name LIKE %s OR option_name LIKE %s OR option_name LIKE %s",
				$wpdb->esc_like( 'ador_receipt_template_' ) . '%',
				$wpdb->esc_like( 'ador_receipt_seq_' ) . '%',
				$wpdb->esc_like( '_transient_ador_' ) . '%',
				$wpdb->esc_like( '_transient_timeout_ador_' ) . '%'
			)
		);
		// phpcs:enable
	}
}
