<?php

namespace BSD_Split_Pay_Stripe_Connect_Woo\Inc\Admin;

defined( 'ABSPATH' ) || exit;

/**
 * 3.7.5 — add `bsd_account_mode` to the connected-accounts cache table.
 *
 * ## Why this migration exists
 *
 * The bsd_strp_accnt table caches every Stripe Connect account a store has
 * synced/onboarded, but it shipped with NO record of which Stripe **mode**
 * (test vs live) each account was connected in:
 *
 *   bsd_sat_id | bsd_account_id | bsd_account_name | bsd_account_email
 *
 * Consequence (Yuliana / levinarf.com, 2026-06): a store onboards vendor
 * accounts while the gateway is in test mode, later switches the gateway to
 * live, and the SAME stale test-mode accounts stay selectable in every split
 * dropdown. A transfer then fires with the live key against a test-mode
 * destination and Stripe rejects every payout:
 *
 *   "No such destination: … a similar object exists in test mode, but a
 *    live mode key was used."
 *
 * Fix (multi-part, see 3.7.5 changelog): tag each account with its mode,
 * mode-filter the dropdowns, and skip mismatched transfers. This migration
 * is the schema foundation — it adds the column so existing installs can be
 * backfilled by spp_reconcile_account_modes() on the next admin load.
 *
 * Column: `bsd_account_mode VARCHAR(10) NOT NULL DEFAULT ''`
 *   ''        = not yet detected (legacy rows; reconciler will tag them)
 *   'test'    = connected in Stripe test mode
 *   'live'    = connected in Stripe live mode
 *   'unknown' = reconciler could not resolve it with either key
 *
 * Width is 10 (not 5) so the literal 'unknown' (7 chars) fits — a VARCHAR(5)
 * would silently truncate/reject it under MySQL strict mode and the reconciler
 * could never mark an unresolvable account, retrying it forever.
 *
 * We do a raw guarded ALTER TABLE here (mirrors migration-376) rather than
 * dbDelta, whose after-the-fact column handling on existing tables is
 * unreliable. The information_schema guard makes re-runs a no-op.
 *
 * @since 3.7.5
 */
if ( ! function_exists( __NAMESPACE__ . '\\db_migration_377' ) ) {
	function db_migration_377( $table_prefix ) {
		try {
			global $wpdb;

			$table = $table_prefix . 'bsd_strp_accnt';

			// Table may not exist yet on a brand-new install where activate.php
			// already created it WITH the column; nothing to do in that case.
			if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) !== $table ) {
				return true;
			}

			$column = $wpdb->get_row( $wpdb->prepare(
				'SELECT CHARACTER_MAXIMUM_LENGTH AS len FROM information_schema.COLUMNS
				 WHERE table_schema = DATABASE()
				   AND table_name = %s
				   AND column_name = %s',
				$table,
				'bsd_account_mode'
			) );

			if ( null === $column ) {
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$wpdb->query( "ALTER TABLE $table ADD COLUMN bsd_account_mode VARCHAR(10) NOT NULL DEFAULT ''" );
			} elseif ( (int) $column->len < 7 ) {
				// Widen a too-narrow column from an earlier pre-release build so
				// the literal 'unknown' (7 chars) fits.
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$wpdb->query( "ALTER TABLE $table MODIFY COLUMN bsd_account_mode VARCHAR(10) NOT NULL DEFAULT ''" );
			}

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