<?php
/**
 * WP-CLI commands under the `wp spp` namespace.
 *
 * Gives site operators a clean recovery + audit toolkit that doesn't
 * require touching the database by hand:
 *
 *   wp spp verify_integrity          Run the SPP database invariant checks
 *                                     (mirror of tests/e2e/integration/database-integrity.spec.js)
 *                                     and exit non-zero on any violation.
 *
 *   wp spp retry_failed_transfers --order=N [--dry-run]
 *                                    Find every status='failed' transfer_log
 *                                    row for order N and re-fire its
 *                                    StripeService::createTransfer call.
 *                                    Useful when Stripe API was down and
 *                                    SPP's auto-retry didn't pick up.
 *                                    --dry-run lists rows without firing.
 *
 *   wp spp dump-transfer-log [--order=N] [--limit=100] [--format=table|json|csv|…]
 *                                    Pretty-print the most recent transfer
 *                                    log rows (or rows for a specific order)
 *                                    as a table. Faster than the WP admin
 *                                    Transfers tab for bulk audits.
 *
 * 2026-05-24: added under sc-PAYMENTS-GRADE-CLI hardening.
 * 2026-05-26 (3.7.5 audit P5): added SHOW TABLES LIKE guards so fresh
 * installs (before the activator runs) skip cleanly instead of fataling
 * with "Table 'wp_bsd_scsp_transfer_log' doesn't exist". Added
 * `--format=<format>` to dump_transfer_log and `--dry-run` to
 * retry_failed_transfers.
 *
 * @package SplitPay
 * @since 3.7.5
 */

declare( strict_types=1 );

namespace SplitPay\Cli;

defined( 'ABSPATH' ) || exit;

if ( ! defined( 'WP_CLI' ) || ! WP_CLI ) {
	return;
}

class SppCommands {

	/**
	 * Verify the three SPP tables exist before any command queries them.
	 * Centralized so each command has the same fresh-install behavior:
	 * print a warning explaining the activator hasn't run, return false,
	 * and let the caller exit 0 (no violation found because there's
	 * nothing to check yet).
	 *
	 * @param array<int,string> $required_tables Table names (WITH prefix).
	 * @return bool True if all tables exist; false (with WP_CLI warning) otherwise.
	 */
	private static function require_tables( array $required_tables ): bool {
		global $wpdb;
		foreach ( $required_tables as $tbl ) {
			$found = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $tbl ) );
			if ( $found !== $tbl ) {
				\WP_CLI::warning( sprintf(
					'Skipping: required table `%s` does not exist yet (plugin activator has not run). ' .
					'This is expected on fresh installs — exiting cleanly with code 0.',
					$tbl
				) );
				return false;
			}
		}
		return true;
	}

	/**
	 * Run SPP database integrity invariants.
	 *
	 * ## EXAMPLES
	 *
	 *     wp spp verify_integrity
	 *
	 * @when after_wp_load
	 */
	public function verify_integrity( array $args = [], array $assoc = [] ): void {
		global $wpdb;

		$accntTable = $wpdb->prefix . 'bsd_strp_accnt';
		$cfgTable   = $wpdb->prefix . 'bsd_strp_connected_id';
		$logTable   = $wpdb->prefix . 'bsd_scsp_transfer_log';

		// 3.7.5 audit P5: fresh installs land here before the activator
		// has created the tables. Bail cleanly instead of fataling on
		// the SELECTs below.
		if ( ! self::require_tables( [ $accntTable, $cfgTable, $logTable ] ) ) {
			return;
		}

		$violations = 0;

		// DI1: orphan transfer_destination rows
		$di1 = (int) $wpdb->get_var(
			"SELECT COUNT(*) FROM $logTable l
			 LEFT JOIN $accntTable a ON l.transfer_destination = a.bsd_account_id
			 WHERE l.transfer_destination IS NOT NULL
			   AND l.transfer_destination != ''
			   AND a.bsd_account_id IS NULL"
		);
		if ( $di1 > 0 ) {
			\WP_CLI::warning( sprintf( 'DI1: %d transfer_log rows reference non-existent connected accounts.', $di1 ) );
			$violations++;
		}

		// DI2: orphan connected_id rows
		$di2 = (int) $wpdb->get_var(
			"SELECT COUNT(*) FROM $cfgTable c
			 LEFT JOIN $accntTable a ON c.bsd_connected_account_id = a.bsd_account_id
			 WHERE c.bsd_connected_account_id IS NOT NULL
			   AND a.bsd_account_id IS NULL"
		);
		if ( $di2 > 0 ) {
			\WP_CLI::warning( sprintf( 'DI2: %d connected_id rows reference non-existent accounts.', $di2 ) );
			$violations++;
		}

		// DI3: untraceable transfer_log rows (both transfer_id and charge_id empty)
		$di3 = (int) $wpdb->get_var(
			"SELECT COUNT(*) FROM $logTable
			 WHERE (transfer_id IS NULL OR transfer_id = '')
			   AND (charge_id IS NULL OR charge_id = '')"
		);
		if ( $di3 > 0 ) {
			\WP_CLI::warning( sprintf( 'DI3: %d transfer_log rows have no Stripe ids (untraceable orphans).', $di3 ) );
			$violations++;
		}

		// DI4: negative amounts
		$di4 = (int) $wpdb->get_var( "SELECT COUNT(*) FROM $logTable WHERE transfer_amount < 0" );
		if ( $di4 > 0 ) {
			\WP_CLI::warning( sprintf( 'DI4: %d transfer_log rows have NEGATIVE transfer_amount.', $di4 ) );
			$violations++;
		}

		// DI5: invalid stripe_mode
		$di5 = (int) $wpdb->get_var(
			"SELECT COUNT(*) FROM $logTable
			 WHERE stripe_mode NOT IN ('test', 'live')
				OR stripe_mode IS NULL
				OR stripe_mode = ''"
		);
		if ( $di5 > 0 ) {
			\WP_CLI::warning( sprintf( 'DI5: %d transfer_log rows have invalid stripe_mode (re-run migration-375).', $di5 ) );
			$violations++;
		}

		if ( $violations === 0 ) {
			\WP_CLI::success( 'All 5 SPP database invariants satisfied.' );
			return;
		}

		\WP_CLI::error( sprintf( '%d invariant(s) violated. See warnings above.', $violations ) );
	}

	/**
	 * Pretty-print recent transfer-log rows.
	 *
	 * ## OPTIONS
	 *
	 * [--order=<id>]
	 * : Restrict to a single WC order ID.
	 *
	 * [--limit=<n>]
	 * : Max rows to print. Default: 100.
	 *
	 * [--format=<format>]
	 * : Output format. One of: table, csv, json, yaml, ids, count. Default: table.
	 *
	 * ## EXAMPLES
	 *
	 *     wp spp dump-transfer-log
	 *     wp spp dump-transfer-log --order=42
	 *     wp spp dump-transfer-log --limit=10
	 *     wp spp dump-transfer-log --format=json
	 *
	 * @when after_wp_load
	 */
	public function dump_transfer_log( array $args, array $assoc ): void {
		global $wpdb;
		$table  = $wpdb->prefix . 'bsd_scsp_transfer_log';
		$limit  = (int) ( $assoc['limit'] ?? 100 );
		$order  = isset( $assoc['order'] ) ? (int) $assoc['order'] : 0;
		$format = (string) ( $assoc['format'] ?? 'table' );

		// 3.7.5 audit P5: SHOW TABLES LIKE guard for fresh-install safety.
		// When the table doesn't exist we exit 0 with a format-appropriate
		// empty response (so `--format=json` callers get parseable `[]`
		// instead of a stderr warning).
		if ( ! self::require_tables( [ $table ] ) ) {
			if ( $format === 'json' ) {
				echo "[]\n";
			}
			return;
		}

		$sql = "SELECT id, wc_order_id, charge_id, transfer_id, transfer_destination,
				transfer_amount, stripe_mode, date_created
				FROM $table";
		if ( $order > 0 ) {
			$sql .= $wpdb->prepare( ' WHERE wc_order_id = %d', $order );
		}
		$sql .= $wpdb->prepare( ' ORDER BY id DESC LIMIT %d', $limit );

		// $table is a constant name ($wpdb->prefix . 'bsd_scsp_transfer_log'); the only user values ($order, $limit) are bound via $wpdb->prepare() above. Nothing unescaped remains.
		$rows = $wpdb->get_results( $sql, ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- constant table identifier; user values bound via prepare() before concatenation.

		if ( empty( $rows ) ) {
			// 3.7.5 audit P5: JSON callers must get `[]`, not a stderr
			// warning. Other formats fall back to the human-readable
			// "no rows" message.
			if ( $format === 'json' ) {
				echo "[]\n";
				return;
			}
			\WP_CLI::warning( 'No transfer-log rows found' . ( $order ? ' for order ' . $order : '' ) . '.' );
			return;
		}

		\WP_CLI\Utils\format_items( $format, $rows, [
			'id', 'wc_order_id', 'charge_id', 'transfer_id',
			'transfer_destination', 'transfer_amount', 'stripe_mode', 'date_created',
		] );
	}

	/**
	 * Re-fire failed transfer attempts for a specific order.
	 *
	 * SAFE: only re-fires rows where transfer_id IS NULL (i.e. the original
	 * createTransfer call failed). Does NOT touch successful transfers.
	 *
	 * ## OPTIONS
	 *
	 * --order=<id>
	 * : The WC order ID whose failed transfers should be retried.
	 *
	 * [--dry-run]
	 * : List the failed rows that WOULD be retried, but do not fire the
	 * retry action. Use this to inspect what `--no-dry-run` would do.
	 *
	 * ## EXAMPLES
	 *
	 *     wp spp retry_failed_transfers --order=42
	 *     wp spp retry_failed_transfers --order=42 --dry-run
	 *
	 * @when after_wp_load
	 */
	public function retry_failed_transfers( array $args, array $assoc ): void {
		$orderId = (int) ( $assoc['order'] ?? 0 );
		if ( $orderId <= 0 ) {
			\WP_CLI::error( 'Pass --order=<id> with a valid WC order ID.' );
		}

		global $wpdb;
		$table = $wpdb->prefix . 'bsd_scsp_transfer_log';

		// 3.7.5 audit P5: SHOW TABLES LIKE guard for fresh-install safety.
		if ( ! self::require_tables( [ $table ] ) ) {
			return;
		}

		$dryRun = isset( $assoc['dry-run'] );

		$failedRows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM $table
				 WHERE wc_order_id = %d
				   AND (transfer_id IS NULL OR transfer_id = '')
				   AND charge_id IS NOT NULL
				   AND charge_id != ''",
				$orderId
			),
			ARRAY_A
		);

		if ( empty( $failedRows ) ) {
			\WP_CLI::success( sprintf( 'Order %d: no failed transfers to retry.', $orderId ) );
			return;
		}

		if ( $dryRun ) {
			\WP_CLI::log( sprintf(
				'DRY RUN — order %d has %d failed transfer(s) that WOULD be retried (no action taken).',
				$orderId,
				count( $failedRows )
			) );
			\WP_CLI\Utils\format_items( 'table', $failedRows, [
				'id', 'transfer_destination', 'transfer_amount', 'charge_id', 'date_created',
			] );
			return;
		}

		\WP_CLI::log( sprintf( 'Order %d: %d failed transfer(s) found. Triggering retry via woocommerce_order_status_completed action.',
			$orderId, count( $failedRows ) ) );

		// The cleanest re-entry point: re-fire the order-status hook the
		// TransferEngine listens on. Avoids re-implementing the engine's
		// charge-resolution + connected-account-lookup + math.
		do_action( 'woocommerce_order_status_completed', $orderId );

		\WP_CLI::success( sprintf( 'Retry dispatched for order %d. Check `wp spp dump-transfer-log --order=%d` for new rows.',
			$orderId, $orderId ) );
	}
}

\WP_CLI::add_command( 'spp', SppCommands::class );
