<?php

namespace SplitPay\Core;

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

class ConnectedAccounts {

	public function getGlobalAccounts(): array {
		global $wpdb;
		$table = $wpdb->prefix . SPP_CONNECTED_ID_TABLE;

		return $wpdb->get_results(
			"SELECT * FROM {$table} ORDER BY bsd_connected_id ASC",
			ARRAY_A
		) ?: [];
	}

	/**
	 * @since 3.7.5 Optional $mode filter ('test'|'live'). When set, returns
	 *        accounts connected in that Stripe mode plus legacy / undetected
	 *        rows (bsd_account_mode '' or 'unknown'), so a mode switch never
	 *        silently hides accounts before the reconciler tags them. Null
	 *        keeps the original "return all" behaviour.
	 *
	 * @param string|null $mode
	 * @return array
	 */
	public function getStoredAccounts( ?string $mode = null ): array {
		global $wpdb;
		$table = $wpdb->prefix . SPP_STRP_ACCNT_TABLE;

		if ( ( $mode === 'test' || $mode === 'live' ) && $this->accountModeColumnExists() ) {
			return $wpdb->get_results(
				$wpdb->prepare(
					"SELECT * FROM {$table} WHERE bsd_account_mode = %s OR bsd_account_mode IN ('', 'unknown')",
					$mode
				),
				ARRAY_A
			) ?: [];
		}

		return $wpdb->get_results(
			"SELECT * FROM {$table}",
			ARRAY_A
		) ?: [];
	}

	/**
	 * Whether bsd_account_mode exists yet (migration-377). Cached per-request.
	 *
	 * @since 3.7.5
	 */
	private function accountModeColumnExists(): bool {
		static $exists = null;
		if ( $exists !== null ) {
			return $exists;
		}
		global $wpdb;
		$table  = $wpdb->prefix . SPP_STRP_ACCNT_TABLE;
		$exists = (bool) $wpdb->get_var( $wpdb->prepare(
			'SELECT COUNT(*) FROM information_schema.COLUMNS
			 WHERE table_schema = DATABASE() AND table_name = %s AND column_name = %s',
			$table,
			'bsd_account_mode'
		) );
		return $exists;
	}

	public function getAccountById( string $accountId ): ?array {
		global $wpdb;
		$table = $wpdb->prefix . SPP_STRP_ACCNT_TABLE;

		$row = $wpdb->get_row(
			$wpdb->prepare( "SELECT * FROM {$table} WHERE bsd_account_id = %s", $accountId ),
			ARRAY_A
		);

		return $row ?: null;
	}

	/**
	 * The Stripe mode a connected account was onboarded in, per the
	 * bsd_account_mode cache column (3.7.5). Returns '' when unknown — the
	 * column doesn't exist yet, the account isn't cached, or its mode hasn't
	 * been detected ('' / 'unknown') — so callers treat "unknown" as
	 * "don't block" and preserve pre-3.7.5 behaviour.
	 *
	 * @since 3.7.5
	 * @param string $accountId
	 * @return string 'test'|'live'|''
	 */
	public function getAccountMode( string $accountId ): string {
		$accountId = trim( $accountId );
		if ( $accountId === '' || ! $this->accountModeColumnExists() ) {
			return '';
		}
		global $wpdb;
		$table = $wpdb->prefix . SPP_STRP_ACCNT_TABLE;
		$mode  = $wpdb->get_var( $wpdb->prepare(
			"SELECT bsd_account_mode FROM {$table} WHERE bsd_account_id = %s",
			$accountId
		) );
		return in_array( $mode, array( 'test', 'live' ), true ) ? $mode : '';
	}

	public function getProductCountByIntegration( string $postType = 'product' ): int {
		global $wpdb;

		return (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT( DISTINCT p.ID )
				FROM {$wpdb->posts} p
				INNER JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id
				WHERE p.post_type = %s
				AND pm.meta_key = '_bsd_spscwt_product_connected_account'
				AND pm.meta_value != ''",
				$postType
			)
		);
	}
}
