<?php

namespace BSD_Split_Pay_Stripe_Connect_Woo\Inc\Admin;

defined( 'ABSPATH' ) || exit;

/**
 * 3.7.4 — reconstruct product Split Pay meta that was silently erased by the
 * legacy save handler in 3.7.3 and earlier.
 *
 * ## Why this migration exists
 *
 * Pre-3.7.4, BSD_SCSP_Main_Plugin::bsd_product_data_fields_save() unconditionally
 * overwrote / deleted the SPP product meta keys whenever the corresponding
 * $_POST entries were absent. That fired on every WooCommerce product save —
 * including:
 *
 *   - variable products' parent save (the simple-product partial's fields
 *     aren't on the form)
 *   - max_input_vars truncation on large products (PHP silently drops fields
 *     past the configured cap, default 1000)
 *   - JS render failure mid-tab (account dropdown never populated)
 *   - WC REST API / wp_update_post / programmatic third-party saves
 *
 * The result: customers who configured a vendor on a product, then saved that
 * product through any of the above paths, lost the vendor selection AND the
 * percentage / amount / shipping settings. Pre-3.7.3 the inner "if percent >0
 * and amount >0" gate hid this — most lost data looked like "the field just
 * doesn't save." 3.7.3 (sc-7201) removed the inner gate, which made
 * persistence work for the happy path AND made the underlying erasure more
 * visible. A second customer report on 2026-05-21 surfaced this is a
 * long-standing class of bug, not a 3.7.3 regression. See sc-7216.
 *
 * The 3.7.4 prevention is the bsd_spp_product_tab_rendered form-presence
 * marker (see includes/admin/partials/bsd-simple-product-table.php and both
 * save handlers). This migration is the BACKFILL: it scans the transfer log
 * for actual transfers that ran in the past, infers which product → vendor
 * mapping was active at that time, and re-applies it to products whose
 * current SPP meta is empty.
 *
 * ## What it does NOT do
 *
 *   - It does NOT overwrite any product whose current
 *     _bsd_spscwt_product_connected_account meta is non-empty. If the
 *     merchant manually re-set it after the erasure, we leave them alone.
 *   - It does NOT touch global / shipping-only / non-product transfer rows.
 *   - It does NOT guess for orders that had multiple products + multiple
 *     destinations (ambiguous mapping). Those products go on the
 *     unrecoverable list surfaced in the admin notice.
 *   - It does NOT remove the recovered marker (`_bsd_spscwt_recovered_in_374`)
 *     so a forensic audit is always possible.
 *
 * ## Recovery sources, in priority order
 *
 *   1. Orders with a single line item → unambiguous mapping of every
 *      transfer_destination in that order to the single product.
 *   2. Multi-line-item orders whose transfers all went to the same
 *      destination → map every product in the order to that destination.
 *   3. (Skipped) Multi-line, multi-destination orders. Recorded in
 *      `unrecoverable_orders` for admin awareness.
 *
 * When multiple historical mappings exist for the same product (vendor
 * changed mid-life), the MOST RECENT charge_date wins. That matches the
 * pre-erasure state immediately before the destructive save.
 *
 * ## Audit trail per recovered product
 *
 *   _bsd_spscwt_pre_374_state         – snapshot of every SPP meta key
 *                                       BEFORE recovery overwrites
 *   _bsd_spscwt_recovered_in_374      – source-order date + values applied
 *
 * Both are kept indefinitely so the merchant can verify / roll back.
 *
 * ## Stats option
 *
 *   bsd_split_pay_374_recovery_stats  – orders_scanned, products_recovered,
 *                                       products_already_had_data,
 *                                       unrecoverable_orders, recovered_products
 *
 * Surfaced once via dismissible admin_notice (see
 * includes/admin/spp-374-recovery-notice.php).
 *
 * @since 3.7.4
 */

if ( ! function_exists( __NAMESPACE__ . '\\db_migration_374' ) ) {
	function db_migration_374( $table_prefix ) {
		try {
			global $wpdb;

			$log_table = $table_prefix . 'bsd_scsp_transfer_log';

			if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $log_table ) ) !== $log_table ) {
				// No transfer log = no recovery data possible. Nothing to do;
				// safely mark the migration as completed so we don't re-run.
				update_option( 'bsd_split_pay_374_recovery_stats', [
					'ran_at'                 => current_time( 'mysql', true ),
					'transfer_log_present'   => false,
					'orders_scanned'         => 0,
					'products_recovered'     => 0,
					'recovered_products'     => [],
					'unrecoverable_orders'   => [],
				] );

				return true;
			}

			// Defer until WC is loaded — we need wc_get_order() for HPOS-safe
			// line-item access. The db-update controller is called on `init`,
			// which fires after WC has bootstrapped on most stacks; if it
			// hasn't, return false so the controller retries on a later request.
			if ( ! function_exists( 'wc_get_order' ) ) {
				return false;
			}

			$stats = [
				'ran_at'                    => current_time( 'mysql', true ),
				'transfer_log_present'      => true,
				'orders_scanned'            => 0,
				'products_already_had_data' => 0,
				'products_recovered'        => 0,
				'recovered_products'        => [],
				'unrecoverable_orders'      => [],
			];

			/*
			 * Collect the candidate orders. We only consider rows whose
			 * transfer_destination is populated. We sort newest-first so the
			 * "most recent charge_date wins" tiebreak is the natural ORDER BY.
			 * The 5000-row cap is a safety belt for very large logs — the
			 * vast majority of customer installs are well under this. If a
			 * customer needs a full historical sweep, the admin tool lets
			 * Brandon re-run via WP-CLI in chunks.
			 *
			 * transfer_type integer codes (per includes/Core/TransferEngine.php
			 * + legacy main class):
			 *   0  = product-level fixed amount
			 *   1  = product-level / global percentage
			 *   8  = (legacy / unused — observed in some installs but no
			 *        modern semantics, treat as percentage for recovery)
			 *   10 = variable-product percentage
			 *   11 = variable-product fixed amount
			 *   12 = PEWC product-extras transfer
			 *
			 * We INCLUDE product-level rows (0, 1, 10, 11, 12). We EXCLUDE
			 * obvious shipping-only rows by joining on the level / source
			 * fields when present, but since older rows may not have those
			 * columns populated, we use transfer_type code filtering as the
			 * primary discriminator.
			 */
			// $log_table is a constant table name ($table_prefix . 'bsd_scsp_transfer_log'); this fixed SELECT has no bindable user input.
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- constant table identifier, no user input.
			$order_ids = $wpdb->get_col(
				"SELECT DISTINCT wc_order_id
				 FROM {$log_table}
				 WHERE wc_order_id IS NOT NULL
				   AND wc_order_id > 0
				   AND transfer_destination IS NOT NULL
				   AND transfer_destination != ''
				 ORDER BY id DESC
				 LIMIT 5000"
			);

			if ( empty( $order_ids ) ) {
				update_option( 'bsd_split_pay_374_recovery_stats', $stats );
				update_option( 'bsd_split_pay_374_recovery_notice', '1' );

				return true;
			}

			// Build product_id => most-recent recovery candidate map.
			$by_product = [];

			foreach ( $order_ids as $order_id ) {
				$order_id = (int) $order_id;
				$stats['orders_scanned']++;

				// $log_table is a constant table name ($table_prefix . 'bsd_scsp_transfer_log'); $order_id is bound via the %d placeholder in prepare().
				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- constant table identifier; user value bound via %d placeholder.
				$transfers = $wpdb->get_results( $wpdb->prepare(
					"SELECT wc_order_id, transfer_destination, transfer_entered_value,
					        transfer_type, charge_date, transfer_amount
					 FROM {$log_table}
					 WHERE wc_order_id = %d
					   AND transfer_destination IS NOT NULL
					   AND transfer_destination != ''
					   AND transfer_type IN (0, 1, 8, 10, 11, 12)
					 ORDER BY id ASC",
					$order_id
				) );

				if ( empty( $transfers ) ) {
					continue;
				}

				$order = wc_get_order( $order_id );
				if ( ! $order ) {
					// Order was deleted but the log row survived — nothing
					// we can map to a product. Skip silently.
					continue;
				}

				$product_ids = [];
				foreach ( $order->get_items() as $item ) {
					if ( ! is_object( $item ) || ! method_exists( $item, 'get_product_id' ) ) {
						continue;
					}
					$pid = (int) $item->get_product_id();
					if ( $pid > 0 ) {
						$product_ids[] = $pid;
					}
				}
				$product_ids = array_values( array_unique( $product_ids ) );

				if ( empty( $product_ids ) ) {
					continue;
				}

				if ( count( $product_ids ) === 1 ) {
					// Unambiguous single-product order: every transfer maps
					// to this product. If the order produced multiple
					// transfers to different destinations, that's a single
					// product split across multiple accounts — capture all.
					$pid = $product_ids[0];
					foreach ( $transfers as $t ) {
						_spp_374_consider_candidate( $by_product, $pid, $t );
					}
					continue;
				}

				// Multi-product order. Safe to recover only if all transfers
				// in this order went to the same destination AND used the
				// same entered_value + transfer_type — otherwise we can't
				// confidently apportion per product.
				$destinations = array_unique( array_map(
					static function ( $t ) { return $t->transfer_destination; },
					$transfers
				) );
				$entered_vals = array_unique( array_map(
					static function ( $t ) { return (string) $t->transfer_entered_value; },
					$transfers
				) );
				$types        = array_unique( array_map(
					static function ( $t ) { return (string) $t->transfer_type; },
					$transfers
				) );

				if ( count( $destinations ) === 1 && count( $entered_vals ) === 1 && count( $types ) === 1 ) {
					$shared = $transfers[0];
					foreach ( $product_ids as $pid ) {
						_spp_374_consider_candidate( $by_product, $pid, $shared );
					}
					continue;
				}

				// Ambiguous. Record for admin awareness so the merchant can
				// decide manually for these specific orders. Cap the list.
				if ( count( $stats['unrecoverable_orders'] ) < 50 ) {
					$stats['unrecoverable_orders'][] = [
						'order_id'     => $order_id,
						'product_ids'  => $product_ids,
						'destinations' => array_values( $destinations ),
					];
				}
			}

			// Apply recovered candidates to products whose current SPP meta
			// is empty (we never overwrite a non-empty existing setting).
			foreach ( $by_product as $pid => $candidate ) {
				$current = get_post_meta( $pid, '_bsd_spscwt_product_connected_account', true );

				if ( ! empty( $current ) && is_array( $current ) ) {
					$has_value = false;
					foreach ( $current as $v ) {
						if ( ! empty( $v ) ) { $has_value = true; break; }
					}
					if ( $has_value ) {
						$stats['products_already_had_data']++;
						continue;
					}
				}

				// Pre-recovery snapshot for forensic audit.
				update_post_meta( $pid, '_bsd_spscwt_pre_374_state', [
					'snapshot_at' => current_time( 'mysql', true ),
					'connected'   => $current,
					'type'        => get_post_meta( $pid, '_bsd_spscwt_product_type', true ),
					'pct'         => get_post_meta( $pid, '_stripe_connect_split_pay_transfer_percentage', true ),
					'amt'         => get_post_meta( $pid, '_bsd_spscwt_product_amount', true ),
					'ship_type'   => get_post_meta( $pid, '_bsd_spscwt_shipping_type', true ),
					'ship_pct'    => get_post_meta( $pid, '_bsd_prod_shipping_percentage', true ),
					'ship_amt'    => get_post_meta( $pid, '_bsd_spscwt_shipping_amount', true ),
				] );

				// transfer_type codes 1 + 10 are percentages, 0 + 11 are
				// fixed amounts. 8 + 12 are legacy/edge variants for which
				// the entered_value scale matches percentage in practice;
				// treat as percentage but flag in the marker.
				$tt     = (int) $candidate['transfer_type'];
				$is_pct = in_array( $tt, [ 1, 10, 8 ], true );
				$pt     = $is_pct ? 'percentage' : 'amount';
				$value  = (string) $candidate['transfer_entered_value'];

				update_post_meta( $pid, '_bsd_spscwt_product_connected_account', [ 0 => $candidate['transfer_destination'] ] );
				update_post_meta( $pid, '_bsd_spscwt_product_type', [ 0 => $pt ] );

				if ( $is_pct ) {
					update_post_meta( $pid, '_stripe_connect_split_pay_transfer_percentage', [ 0 => $value ] );
				} else {
					update_post_meta( $pid, '_bsd_spscwt_product_amount', [ 0 => $value ] );
				}

				update_post_meta( $pid, '_bsd_spscwt_recovered_in_374', [
					'recovered_at'      => current_time( 'mysql', true ),
					'source_order_id'   => (int) $candidate['source_order_id'],
					'source_charge_date' => $candidate['charge_date'],
					'vendor_acct'       => $candidate['transfer_destination'],
					'entered_value'     => $value,
					'transfer_type_int' => $tt,
					'transfer_type_str' => $pt,
				] );

				$stats['products_recovered']++;

				if ( count( $stats['recovered_products'] ) < 200 ) {
					$stats['recovered_products'][] = $pid;
				}
			}

			update_option( 'bsd_split_pay_374_recovery_stats', $stats );
			update_option( 'bsd_split_pay_374_recovery_notice', '1' );

			return true;
		} catch ( \Exception $e ) {
			error_log( '[BSD Split Pay 3.7.4 recovery migration] ' . $e->getMessage() );

			return false;
		}
	}
}

if ( ! function_exists( __NAMESPACE__ . '\\_spp_374_consider_candidate' ) ) {
	/**
	 * Tracks the best (most recent) recovery candidate per product across
	 * the orders the migration scans. The newest charge_date wins, so the
	 * recovered value matches the vendor configuration that was active
	 * immediately before the erasure.
	 *
	 * @param array              $by_product Reference to the product => candidate map.
	 * @param int                $pid        Product ID.
	 * @param object|array       $transfer   Transfer log row (object or assoc array).
	 *
	 * @return void
	 */
	function _spp_374_consider_candidate( array &$by_product, int $pid, $transfer ): void {
		$t = is_object( $transfer ) ? (array) $transfer : (array) $transfer;

		$candidate = [
			'transfer_destination'   => $t['transfer_destination'] ?? '',
			'transfer_entered_value' => $t['transfer_entered_value'] ?? '',
			'transfer_type'          => $t['transfer_type'] ?? 1,
			'charge_date'            => $t['charge_date'] ?? '0000-00-00 00:00:00',
			'source_order_id'        => $t['wc_order_id'] ?? 0,
		];

		if ( empty( $candidate['transfer_destination'] ) ) {
			return;
		}

		if ( ! isset( $by_product[ $pid ] ) ) {
			$by_product[ $pid ] = $candidate;

			return;
		}

		$existing_ts = strtotime( (string) $by_product[ $pid ]['charge_date'] );
		$new_ts      = strtotime( (string) $candidate['charge_date'] );

		if ( $new_ts !== false && $new_ts > $existing_ts ) {
			$by_product[ $pid ] = $candidate;
		}
	}
}
