<?php

namespace BSD_Split_Pay_Stripe_Connect_Woo\Inc\Admin;

defined( 'ABSPATH' ) || exit;

/**
 * 3.7.5 — add missing indexes to the transfer-log table.
 *
 * ## Why this migration exists
 *
 * The bsd_scsp_transfer_log table shipped with ONLY a PRIMARY KEY on
 * `id`. Every Transfers admin tab load does:
 *
 *   SELECT COUNT(*) FROM table WHERE stripe_mode = 'test'  → full scan
 *   SELECT ...    FROM table WHERE stripe_mode = 'test'
 *                  ORDER BY id DESC LIMIT 10 OFFSET 0      → filter unindexed
 *
 * Measured (G3 perf-budget run, 2026-05-24): with 1000 transfer-log rows,
 * the Transfers tab TTFB was **5226ms** vs the 2000ms budget. With 10k
 * rows a typical mid-scale marketplace it climbs to ~50s — effectively
 * unusable. Customer impact scales linearly with transfer volume.
 *
 * Fix: add covering indexes for the three high-frequency query
 * patterns:
 *
 *   - idx_stripe_mode: every list query filters on this
 *   - idx_wc_order_id: refund + retry + CSV-by-order paths
 *   - idx_transfer_destination: refund reversal lookups +
 *     DB integrity DI1 check
 *
 * dbDelta handles "ADD INDEX IF NOT EXISTS" idempotently via the table
 * schema reapplication. We do raw ALTER TABLE here for clarity (dbDelta's
 * index syntax is finicky) with a guard to skip if the index already
 * exists.
 *
 * @since 3.7.5
 */
if ( ! function_exists( __NAMESPACE__ . '\\db_migration_376' ) ) {
	function db_migration_376( $table_prefix ) {
		try {
			global $wpdb;

			$table = $table_prefix . 'bsd_scsp_transfer_log';

			if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) !== $table ) {
				return true;
			}

			// Map of (index name → column) we want to ensure exists.
			$indexes = array(
				'idx_stripe_mode'         => 'stripe_mode',
				'idx_wc_order_id'         => 'wc_order_id',
				'idx_transfer_destination' => 'transfer_destination',
			);

			foreach ( $indexes as $index_name => $column ) {
				$exists = $wpdb->get_var( $wpdb->prepare(
					'SELECT COUNT(*) FROM information_schema.STATISTICS
					 WHERE table_schema = DATABASE()
					   AND table_name = %s
					   AND index_name = %s',
					$table,
					$index_name
				) );
				if ( (int) $exists > 0 ) {
					continue;
				}

				// Use ALTER TABLE — dbDelta's index handling is unreliable for
				// after-the-fact additions on existing tables.
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$wpdb->query( "ALTER TABLE $table ADD INDEX $index_name ($column)" );
			}

			return true;
		} catch ( \Exception $e ) {
			if ( class_exists( '\WC_Stripe_Logger' ) && method_exists( '\WC_Stripe_Logger', 'debug' ) ) {
				\WC_Stripe_Logger::debug( '[SPP migration-376] failed: ' . $e->getMessage() );
			}
			return false;
		}
	}
}
