<?php
/**
 * Database Schema Installer
 *
 * Creates and manages custom database tables for donation tracking.
 *
 * @package DonorCore\Database
 */

namespace DonorCore\Database;

defined( 'ABSPATH' ) || exit;

/**
 * Schema Installer Class
 */
class Schema_Installer {

	/**
	 * Database version key.
	 */
	const DB_VERSION_KEY = 'donorcore_db_version';

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

	/**
	 * Install or upgrade database tables.
	 */
	public function install() {
		$current_version = get_option( self::DB_VERSION_KEY, '0' );

		// Only run if version changed or first install.
		if ( version_compare( $current_version, self::DB_VERSION, '<' ) ) {
			$this->create_tables();
			update_option( self::DB_VERSION_KEY, self::DB_VERSION );
		}
	}

	/**
	 * Create custom database tables using dbDelta.
	 */
	private function create_tables() {
		global $wpdb;

		$charset_collate = $wpdb->get_charset_collate();

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

		// Table 1: Donation Logs - Immutable ledger of all donations.
		$sql_logs = "CREATE TABLE {$wpdb->prefix}donorcore_donation_logs (
			id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			order_id BIGINT(20) UNSIGNED NOT NULL,
			campaign_id BIGINT(20) UNSIGNED DEFAULT 0,
			donor_id BIGINT(20) UNSIGNED DEFAULT 0,
			amount DECIMAL(18,2) NOT NULL,
			currency CHAR(3) NOT NULL DEFAULT 'USD',
			type VARCHAR(20) NOT NULL DEFAULT 'one-time',
			gateway VARCHAR(50) NOT NULL DEFAULT 'unknown',
			created_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			KEY order_id (order_id),
			KEY campaign_id (campaign_id),
			KEY donor_id (donor_id),
			KEY type (type),
			KEY created_at (created_at)
		) $charset_collate;";

		dbDelta( $sql_logs );

		// Donor Analytics - Pre-calculated metrics for fast queries.
		$sql_analytics = "CREATE TABLE {$wpdb->prefix}donorcore_donor_analytics (
			donor_email VARCHAR(100) NOT NULL,
			donor_id BIGINT(20) UNSIGNED DEFAULT 0,
			total_lifetime_value DECIMAL(18,2) NOT NULL DEFAULT 0,
			donation_count INT UNSIGNED NOT NULL DEFAULT 0,
			last_donation_date DATETIME DEFAULT NULL,
			first_donation_date DATETIME DEFAULT NULL,
			PRIMARY KEY  (donor_email),
			KEY donor_id (donor_id),
			KEY total_lifetime_value (total_lifetime_value)
		) $charset_collate;";

		dbDelta( $sql_analytics );
	}

	/**
	 * Drop all custom tables (use with caution - for uninstall only).
	 */
	public function drop_tables() {
		global $wpdb;

		// DROP TABLE IF EXISTS - Note: DDL statements don't support $wpdb->prepare()
		// Using backticks and hardcoded table suffixes for security
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Required for uninstall cleanup
		$wpdb->query( "DROP TABLE IF EXISTS `{$wpdb->prefix}donorcore_donation_logs`" );
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Required for uninstall cleanup
		$wpdb->query( "DROP TABLE IF EXISTS `{$wpdb->prefix}donorcore_donor_analytics`" );
		delete_option( self::DB_VERSION_KEY );
	}
}
