<?php
/**
 * Database table definitions and creation.
 *
 * @package Donatico\Database
 */

declare(strict_types=1);

namespace Donatico\Database;

/**
 * Manages the creation and upgrade of Donatico's custom database tables
 * using WordPress's dbDelta() for safe, idempotent schema migrations.
 */
class Schema {

	/** Name of the donations table (without prefix). */
	const TABLE_DONATIONS = 'donatico_donations';

	/** Donations-table schema version. Bump when columns change. */
	const SCHEMA_VERSION = '3';

	/**
	 * Create or upgrade all plugin tables.
	 *
	 * Safe to call on every activation — dbDelta only applies changes.
	 *
	 * @return void
	 */
	public static function createTables(): void {
		global $wpdb;

		$charset_collate = $wpdb->get_charset_collate();

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

		dbDelta( self::donationsTableSql( $wpdb->prefix, $charset_collate ) );

		update_option( 'donatico_db_version', DONATICO_VERSION );
		update_option( 'donatico_db_schema_version', self::SCHEMA_VERSION );
	}

	/**
	 * Return the SQL for the donations table.
	 *
	 * @param string $prefix           WordPress table prefix.
	 * @param string $charset_collate  Charset/collation string from $wpdb.
	 * @return string
	 */
	private static function donationsTableSql( string $prefix, string $charset_collate ): string {
		$table = $prefix . self::TABLE_DONATIONS;

		// dbDelta requires two spaces after PRIMARY KEY and one space before
		// each field definition. Do not reformat this heredoc.
		return "CREATE TABLE {$table} (
  id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  donor_name VARCHAR(255) NOT NULL DEFAULT '',
  donor_email VARCHAR(255) NOT NULL DEFAULT '',
  amount INT(11) NOT NULL DEFAULT 0,
  currency VARCHAR(3) NOT NULL DEFAULT 'USD',
  message TEXT,
  is_anonymous TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
  stripe_session_id VARCHAR(255) DEFAULT NULL,
  stripe_payment_intent_id VARCHAR(255) DEFAULT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'pending',
  campaign_id BIGINT(20) UNSIGNED DEFAULT NULL,
  tier_id BIGINT(20) UNSIGNED DEFAULT NULL,
  tribute_type ENUM('memory','honor') DEFAULT NULL,
  tribute_name VARCHAR(255) DEFAULT NULL,
  tribute_message TEXT DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY  (id),
  KEY status (status),
  KEY donor_email (donor_email),
  KEY created_at (created_at),
  KEY campaign_id (campaign_id)
) {$charset_collate};";
	}

	/**
	 * Return the fully-qualified donations table name.
	 *
	 * @return string
	 */
	public static function donationsTable(): string {
		global $wpdb;
		return $wpdb->prefix . self::TABLE_DONATIONS;
	}

	/**
	 * Upgrade the donations table for installs created before SCHEMA_VERSION.
	 *
	 * Cheap no-op once donatico_db_schema_version is current. Safe to call
	 * on every admin_init.
	 *
	 * @return void
	 */
	public static function maybeUpgrade(): void {
		$current = get_option( 'donatico_db_schema_version', '1' );

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

		self::upgradeColumns();

		update_option( 'donatico_db_schema_version', self::SCHEMA_VERSION );
	}

	/**
	 * Idempotently add columns introduced after the original table schema.
	 *
	 * @return void
	 */
	private static function upgradeColumns(): void {
		global $wpdb;

		$table   = self::donationsTable();
		$columns = array(
			'campaign_id'       => "ALTER TABLE {$table} ADD COLUMN campaign_id BIGINT(20) UNSIGNED DEFAULT NULL",
			'tier_id'           => "ALTER TABLE {$table} ADD COLUMN tier_id BIGINT(20) UNSIGNED DEFAULT NULL",
			'tribute_type'      => "ALTER TABLE {$table} ADD COLUMN tribute_type ENUM('memory','honor') DEFAULT NULL",
			'tribute_name'      => "ALTER TABLE {$table} ADD COLUMN tribute_name VARCHAR(255) DEFAULT NULL",
			'tribute_message'   => "ALTER TABLE {$table} ADD COLUMN tribute_message TEXT DEFAULT NULL",
			'subscription_id'   => "ALTER TABLE {$table} ADD COLUMN subscription_id BIGINT(20) UNSIGNED DEFAULT NULL",
			'stripe_invoice_id' => "ALTER TABLE {$table} ADD COLUMN stripe_invoice_id VARCHAR(255) DEFAULT NULL",
			'is_renewal'        => "ALTER TABLE {$table} ADD COLUMN is_renewal TINYINT(1) UNSIGNED NOT NULL DEFAULT 0",
		);

		foreach ( $columns as $column => $sql ) {
			// Table name is built from $wpdb->prefix and a hardcoded string; safe to interpolate.
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$exists = $wpdb->get_results( $wpdb->prepare( "SHOW COLUMNS FROM {$table} LIKE %s", $column ) );

			if ( empty( $exists ) ) {
				// Table name is built from $wpdb->prefix and a hardcoded string; safe to interpolate.
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
				$wpdb->query( $sql );
			}
		}

		// Table name is built from $wpdb->prefix and a hardcoded string; safe to interpolate.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$index = $wpdb->get_results( "SHOW INDEX FROM {$table} WHERE Key_name = 'campaign_id'" );

		if ( empty( $index ) ) {
			// Table name is built from $wpdb->prefix and a hardcoded string; safe to interpolate.
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$wpdb->query( "ALTER TABLE {$table} ADD INDEX campaign_id (campaign_id)" );
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$inv_index = $wpdb->get_results( "SHOW INDEX FROM {$table} WHERE Key_name = 'stripe_invoice_id'" );

		if ( empty( $inv_index ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$wpdb->query( "ALTER TABLE {$table} ADD INDEX stripe_invoice_id (stripe_invoice_id)" );
		}
	}
}
