<?php

/**
 * Payment Settings Page
 *
 * @package Softech_Addons_for_Elementor
 * @since 1.0.0
 */

namespace Softech_Addons_for_Elementor\Includes;

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

class SITS_Elementor_Payment_Settings {

	// -----------------------------------------------------------------------
	// FIX 3 - Singleton: one instance, hooks registered exactly once.
	// -----------------------------------------------------------------------
	/** @var self|null */
	private static $instance = null;

	public static function instance() {
		if ( null === self::$instance ) {
			self::$instance = new self();
		}
		return self::$instance;
	}

	// -----------------------------------------------------------------------
	// Properties
	// -----------------------------------------------------------------------
	private $option_name        = 'sits_elementor_addons_options';
	private $option_group       = 'sits_elementor_addons_options_group';
	private $page_slug          = 'softech-addons-for-elementor';
	private $payment_page_slug  = 'softech-addons-payment-settings';

	// -----------------------------------------------------------------------
	// Constructor - private so only instance() can create it.
	// -----------------------------------------------------------------------
	private function __construct() {
		add_action( 'admin_menu', array( $this, 'add_payment_menu' ), 20 );
		add_action( 'admin_init', array( $this, 'register_payment_settings' ) );
		add_action( 'admin_head', array( $this, 'add_submenu_styling' ) );

		// AJAX handlers.
		add_action( 'wp_ajax_sits_delete_transaction',            array( $this, 'ajax_delete_transaction' ) );
		add_action( 'wp_ajax_sits_load_payment_tab',              array( $this, 'ajax_load_payment_tab' ) );
		add_action( 'wp_ajax_sits_filter_transactions',           array( $this, 'ajax_filter_transactions' ) );
		// FIX 1 - dedicated AJAX save; replaces options.php form submit.
		add_action( 'wp_ajax_sits_save_payment_general_settings', array( $this, 'ajax_save_payment_general_settings' ) );
		add_action( 'wp_ajax_sits_export_transactions_csv', [ $this, 'ajax_export_transactions_csv' ] );
	}

	// -----------------------------------------------------------------------
	// Menu
	// -----------------------------------------------------------------------
	public function add_payment_menu() {
		add_submenu_page(
			$this->page_slug,
			esc_html__( 'Transaction Table', 'softech-addons-for-elementor' ),
			esc_html__( 'Transaction Table', 'softech-addons-for-elementor' ),
			'manage_options',
			'admin.php?page=' . $this->page_slug . '&tab=payment-settings&subtab=transactions'
		);
		add_submenu_page(
			$this->page_slug,
			esc_html__( 'Integrations', 'softech-addons-for-elementor' ),
			esc_html__( 'Integrations', 'softech-addons-for-elementor' ),
			'manage_options',
			'admin.php?page=' . $this->page_slug . '&tab=payment-settings&subtab=general'
		);
	}

	// -----------------------------------------------------------------------
	// Sidebar highlight styles / JS
	// -----------------------------------------------------------------------
	public function add_submenu_styling() {
		?>
		<style>
			#adminmenu a[href*="subtab=general"][data-sits-active="true"]      { border-left:3px solid #adff2f !important; color:#adff2f !important; font-weight:600; }
			#adminmenu a[href*="subtab=transactions"][data-sits-active="true"] { color:#fff !important; font-weight:600; }
			#adminmenu a[href*="subtab=general"]:hover                         { color:#adff2f !important; }
			#adminmenu a[href*="subtab=transactions"]:hover                    { color:#fff !important; }
		</style>
		<script type="text/javascript">
		(function () {
			function initSitsMenuHighlight() {
				var url  = window.location.href;
				var iLink = document.querySelector('#adminmenu a[href*="subtab=general"]');
				var tLink = document.querySelector('#adminmenu a[href*="subtab=transactions"]');
				if ( iLink ) iLink.removeAttribute('data-sits-active');
				if ( tLink ) tLink.removeAttribute('data-sits-active');
				if ( iLink && url.includes('subtab=general') )       iLink.setAttribute('data-sits-active','true');
				if ( tLink && url.includes('subtab=transactions') )   tLink.setAttribute('data-sits-active','true');
			}
			if ( document.readyState === 'loading' ) {
				document.addEventListener('DOMContentLoaded', initSitsMenuHighlight);
			} else {
				initSitsMenuHighlight();
			}
		}());
		</script>
		<?php
	}

	// -----------------------------------------------------------------------
	// Settings registration
	// -----------------------------------------------------------------------
	public function register_payment_settings() {
		add_settings_section( 'sits_payment_general_section', esc_html__( 'General Settings', 'softech-addons-for-elementor' ), array( $this, 'stripe_section_intro' ), $this->payment_page_slug );
		add_settings_field( 'stripe_currency',    esc_html__( 'Currency',    'softech-addons-for-elementor' ), array( $this, 'render_stripe_currency_field' ),    $this->payment_page_slug, 'sits_payment_general_section' );
		add_settings_field( 'stripe_success_url', esc_html__( 'Success URL', 'softech-addons-for-elementor' ), array( $this, 'render_stripe_success_url_field' ), $this->payment_page_slug, 'sits_payment_general_section' );
		add_settings_field( 'stripe_cancel_url',  esc_html__( 'Cancel URL',  'softech-addons-for-elementor' ), array( $this, 'render_stripe_cancel_url_field' ),  $this->payment_page_slug, 'sits_payment_general_section' );

		add_settings_section( 'sits_stripe_payment_section', esc_html__( 'Stripe Payment Settings', 'softech-addons-for-elementor' ), array( $this, 'stripe_section_intro' ), $this->payment_page_slug );
		add_settings_field( 'stripe_secret_key',      esc_html__( 'Stripe Secret Key',      'softech-addons-for-elementor' ), array( $this, 'render_stripe_secret_key_field' ),      $this->payment_page_slug, 'sits_stripe_payment_section' );
		add_settings_field( 'stripe_publishable_key', esc_html__( 'Stripe Publishable Key', 'softech-addons-for-elementor' ), array( $this, 'render_stripe_publishable_key_field' ), $this->payment_page_slug, 'sits_stripe_payment_section' );
		add_settings_field( 'stripe_webhook_secret',  esc_html__( 'Stripe Webhook Secret',  'softech-addons-for-elementor' ), array( $this, 'render_stripe_webhook_secret_field' ),  $this->payment_page_slug, 'sits_stripe_payment_section' );
		add_settings_field( 'stripe_webhook_url',     esc_html__( 'Webhook URL',            'softech-addons-for-elementor' ), array( $this, 'render_stripe_webhook_url_field' ),     $this->payment_page_slug, 'sits_stripe_payment_section' );
	}

	public function stripe_section_intro() {}
	
	public function ajax_export_transactions_csv() {
    check_ajax_referer( 'sits_admin_nonce', 'nonce' );
    if ( ! current_user_can( 'manage_options' ) ) { wp_die(); }

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

    $search     = isset( $_POST['search'] )     ? sanitize_text_field( wp_unslash( $_POST['search'] ) )     : '';
    $status     = isset( $_POST['status'] )     ? sanitize_text_field( wp_unslash( $_POST['status'] ) )     : '';
    $provider   = isset( $_POST['provider'] )   ? sanitize_text_field( wp_unslash( $_POST['provider'] ) )   : '';
    $currency   = isset( $_POST['currency'] )   ? sanitize_text_field( wp_unslash( $_POST['currency'] ) )   : '';
    $date_range = isset( $_POST['date_range'] ) ? sanitize_text_field( wp_unslash( $_POST['date_range'] ) ) : '';
    $date_from  = isset( $_POST['date_from'] )  ? sanitize_text_field( wp_unslash( $_POST['date_from'] ) )  : '';
    $date_to    = isset( $_POST['date_to'] )    ? sanitize_text_field( wp_unslash( $_POST['date_to'] ) )    : '';

    $where_conditions = $this->build_where_conditions( $search, $status, $provider, $currency, $date_range, $date_from, $date_to );
    $where_clause     = $this->compile_where( $where_conditions );

    // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
    $transactions = $wpdb->get_results(
        'SELECT id, name, email, price, currency, status, provider, payment_id, button_id, created_at FROM `' . esc_sql( $table ) . '`' . $where_clause . ' ORDER BY created_at DESC',
        ARRAY_A
    );

    $filename = 'transactions-' . gmdate( 'Y-m-d' ) . '.csv';

    header( 'Content-Type: text/csv; charset=utf-8' );
    header( 'Content-Disposition: attachment; filename=' . $filename );
    header( 'Pragma: no-cache' );
    header( 'Expires: 0' );

    $output = fopen( 'php://output', 'w' );
    fputcsv( $output, [ 'ID', 'Name', 'Email', 'Price', 'Currency', 'Status', 'Provider', 'Payment ID', 'Button ID', 'Date' ] );

    $total_csv   = count( $transactions );
    $row_counter = 0;
    foreach ( $transactions as $row ) {
    $row_counter++;
    $row['id'] = $total_csv - $row_counter + 1;
    fputcsv( $output, $row );
}

    fclose( $output );
    exit;
}

	// -----------------------------------------------------------------------
	// FIX 1 - AJAX save handler (replaces options.php form submit).
	// -----------------------------------------------------------------------
	public function ajax_save_payment_general_settings() {
		check_ajax_referer( 'sits_admin_nonce', 'nonce' );

		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error( array( 'message' => esc_html__( 'Unauthorized', 'softech-addons-for-elementor' ) ) );
		}

		$allowed_keys = array(
			'stripe_secret_key', 'stripe_publishable_key', 'stripe_webhook_secret',
			'stripe_currency', 'stripe_success_url', 'stripe_cancel_url',
		);
		$url_keys = array( 'stripe_success_url', 'stripe_cancel_url' );

		$current = get_option( $this->option_name, array() );
		if ( ! is_array( $current ) ) {
			$current = array();
		}

		// phpcs:ignore WordPress.Security.NonceVerification.Missing -- verified above
		$posted = ( isset( $_POST['options'] ) && is_array( $_POST['options'] ) ) ? wp_unslash( $_POST['options'] ) : array(); // phpcs:ignore WordPress.Security.ValidatedSanitizedInput.InputNotSanitized

		foreach ( $allowed_keys as $key ) {
			if ( isset( $posted[ $key ] ) ) {
				if ( in_array( $key, $url_keys, true ) ) {
					$current[ $key ] = esc_url_raw( $posted[ $key ] );
				} elseif ( 'stripe_currency' === $key ) {
					$current[ $key ] = sanitize_key( $posted[ $key ] );
				} else {
					$current[ $key ] = sanitize_text_field( $posted[ $key ] );
				}
			} elseif ( 'stripe_currency' === $key && empty( $current[ $key ] ) ) {
				$current[ $key ] = 'USD';
			}
		}

		update_option( $this->option_name, $current );
		wp_send_json_success( array( 'message' => esc_html__( 'Settings saved successfully.', 'softech-addons-for-elementor' ) ) );
	}

	// -----------------------------------------------------------------------
	// AJAX - load a payment sub-tab
	// -----------------------------------------------------------------------
	public function ajax_load_payment_tab() {
		check_ajax_referer( 'sits_admin_nonce', 'nonce' );
		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error( array( 'message' => esc_html__( 'Unauthorized', 'softech-addons-for-elementor' ) ) );
		}
		if ( ! isset( $_POST['subtab'] ) ) {
			wp_send_json_error( array( 'message' => esc_html__( 'Missing subtab parameter', 'softech-addons-for-elementor' ) ) );
		}
		$subtab = sanitize_key( $_POST['subtab'] );
		ob_start();
		if ( 'general' === $subtab ) {
			$this->render_general_subtab();
		} elseif ( 'transactions' === $subtab ) {
			$this->render_transactions_tab();
		}
		$html = ob_get_clean();
		wp_send_json_success( array( 'html' => $html ) );
	}

	// -----------------------------------------------------------------------
	// FIX 2 - AJAX filter/search.
	// Returns only #sits-transaction-results content so filter UI stays intact.
	// -----------------------------------------------------------------------
	public function ajax_filter_transactions() {
		check_ajax_referer( 'sits_admin_nonce', 'nonce' );
		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error( array( 'message' => esc_html__( 'Unauthorized', 'softech-addons-for-elementor' ) ) );
		}

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

		$search     = isset( $_POST['search'] )     ? sanitize_text_field( wp_unslash( $_POST['search'] ) )     : '';
		$status     = isset( $_POST['status'] )     ? sanitize_text_field( wp_unslash( $_POST['status'] ) )     : '';
		$provider   = isset( $_POST['provider'] )   ? sanitize_text_field( wp_unslash( $_POST['provider'] ) )   : '';
		$currency   = isset( $_POST['currency'] )   ? sanitize_text_field( wp_unslash( $_POST['currency'] ) )   : '';
		$date_range = isset( $_POST['date_range'] ) ? sanitize_text_field( wp_unslash( $_POST['date_range'] ) ) : '';
		$date_from  = isset( $_POST['date_from'] )  ? sanitize_text_field( wp_unslash( $_POST['date_from'] ) )  : '';
		$date_to    = isset( $_POST['date_to'] )    ? sanitize_text_field( wp_unslash( $_POST['date_to'] ) )    : '';
		$paged      = isset( $_POST['paged'] )      ? max( 1, intval( $_POST['paged'] ) )                       : 1;
		$per_page   = 20;
		$offset     = ( $paged - 1 ) * $per_page;

		$where_conditions = $this->build_where_conditions( $search, $status, $provider, $currency, $date_range, $date_from, $date_to );
		$where_clause     = $this->compile_where( $where_conditions );

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$total_items = (int) $wpdb->get_var( 'SELECT COUNT(*) FROM `' . esc_sql( $table ) . '`' . $where_clause );
		$total_pages = (int) ceil( $total_items / $per_page );

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$transactions = $wpdb->get_results(
			'SELECT * FROM `' . esc_sql( $table ) . '`' . $where_clause .
			' ORDER BY created_at DESC, id DESC LIMIT ' . intval( $per_page ) . ' OFFSET ' . intval( $offset )
		);

		$active_filters = $this->build_active_filter_labels( $search, $status, $provider, $currency, $date_range );

		ob_start();
		$this->render_transaction_results( $transactions, $total_items, $total_pages, $paged, $active_filters, $search, $status, $provider, $currency, $date_range );
		$html = ob_get_clean();

		wp_send_json_success( array( 'html' => $html, 'total' => $total_items ) );
	}

	// -----------------------------------------------------------------------
	// Field renderers
	// -----------------------------------------------------------------------
	public function render_stripe_secret_key_field() {
		$options = get_option( $this->option_name, array() );
		$val     = $options['stripe_secret_key'] ?? '';
		echo '<input type="text" name="' . esc_attr( $this->option_name ) . '[stripe_secret_key]" value="' . esc_attr( $val ) . '" class="regular-text" />';
	}

	public function render_stripe_publishable_key_field() {
		$options = get_option( $this->option_name, array() );
		$val     = $options['stripe_publishable_key'] ?? '';
		echo '<input type="text" name="' . esc_attr( $this->option_name ) . '[stripe_publishable_key]" value="' . esc_attr( $val ) . '" class="regular-text" />';
	}

	public function render_stripe_webhook_secret_field() {
		$options = get_option( $this->option_name, array() );
		$val     = $options['stripe_webhook_secret'] ?? '';
		echo '<input type="text" name="' . esc_attr( $this->option_name ) . '[stripe_webhook_secret]" value="' . esc_attr( $val ) . '" class="regular-text" />';
	}

	public function render_stripe_currency_field() {
		$options          = get_option( $this->option_name, array() );
		$val              = $options['stripe_currency'] ?? 'USD';
		$currency_symbols = $this->get_currency_symbols();
		echo '<select name="' . esc_attr( $this->option_name ) . '[stripe_currency]" class="regular-text">';
		foreach ( $currency_symbols as $code => $symbol ) {
			printf( '<option value="%s" %s>%s</option>', esc_attr( $code ), selected( $val, $code, false ), esc_html( $code . ' (' . $symbol . ')' ) );
		}
		echo '</select>';
	}

	public function render_stripe_success_url_field() {
		$options = get_option( $this->option_name, array() );
		$val     = $options['stripe_success_url'] ?? '';
		echo '<input type="url" name="' . esc_attr( $this->option_name ) . '[stripe_success_url]" value="' . esc_attr( $val ) . '" class="regular-text" />';
		echo '<p class="description"><small>' . esc_html__( 'Use shortcode [sitsel_success] to set the payment success page.', 'softech-addons-for-elementor' ) . '</small></p>';
	}

	public function render_stripe_cancel_url_field() {
		$options = get_option( $this->option_name, array() );
		$val     = $options['stripe_cancel_url'] ?? '';
		echo '<input type="url" name="' . esc_attr( $this->option_name ) . '[stripe_cancel_url]" value="' . esc_attr( $val ) . '" class="regular-text" />';
		echo '<p class="description"><small>' . esc_html__( 'Use shortcode [sitsel_failed] to set the payment failed page.', 'softech-addons-for-elementor' ) . '</small></p>';
	}

	public function render_stripe_webhook_url_field() {
		$webhook = home_url( '/wp-json/sitsel/v1/stripe-webhook' );
		echo '<input type="text" readonly value="' . esc_url( $webhook ) . '" class="regular-text" />';
		echo '<p class="description"><small>' . esc_html__( 'Copy this URL to Stripe Dashboard -> Webhooks', 'softech-addons-for-elementor' ) . '</small></p>';
	}

	// -----------------------------------------------------------------------
	// Tab renderer (entry point from admin-settings.php)
	// -----------------------------------------------------------------------
	public function render_payment_settings_tab( $subtab = null ) {
		// phpcs:ignore WordPress.Security.NonceVerification.Recommended -- read-only tab selector.
		$payment_sub_tab = $subtab ?? sanitize_key( wp_unslash( $_GET['subtab'] ?? 'general' ) );
		?>
		<div class="sits-settings-container">
			<nav class="sits-sub-tabs" style="margin-bottom:20px;border-bottom:1px solid #ddd;">
				<a href="#" class="sits-sub-tab sits-payment-subtab-link <?php echo 'general' === $payment_sub_tab ? 'active' : ''; ?>"
				   data-subtab="general"
				   style="display:inline-block;padding:10px 20px;text-decoration:none;
				          color:<?php echo 'general' === $payment_sub_tab ? '#0f3d4a' : '#666'; ?>;
				          border-bottom:2px solid <?php echo 'general' === $payment_sub_tab ? '#0f3d4a' : 'transparent'; ?>;">
					<?php esc_html_e( 'General Settings', 'softech-addons-for-elementor' ); ?>
				</a>
				<a href="#" class="sits-sub-tab sits-payment-subtab-link <?php echo 'transactions' === $payment_sub_tab ? 'active' : ''; ?>"
				   data-subtab="transactions"
				   style="display:inline-block;padding:10px 20px;text-decoration:none;
				          color:<?php echo 'transactions' === $payment_sub_tab ? '#0f3d4a' : '#666'; ?>;
				          border-bottom:2px solid <?php echo 'transactions' === $payment_sub_tab ? '#0f3d4a' : 'transparent'; ?>;">
					<?php esc_html_e( 'Transactions', 'softech-addons-for-elementor' ); ?>
				</a>
			</nav>
			<div id="sits-payment-content">
				<?php if ( 'general' === $payment_sub_tab ) : ?>
					<?php $this->render_general_subtab(); ?>
				<?php else : ?>
					<?php $this->render_transactions_tab(); ?>
				<?php endif; ?>
			</div>
		</div>
		<?php
	}

	// -----------------------------------------------------------------------
	// General sub-tab.
	// FIX 1 - no longer points at options.php; JS intercepts and saves via AJAX.
	// -----------------------------------------------------------------------
	private function render_general_subtab() {
		?>
		<div id="sits-general-settings-notice" style="display:none;" class="notice is-dismissible"></div>
		<form id="sits-general-settings-form" method="post" action="">
			<?php
			do_settings_sections( $this->payment_page_slug );
			submit_button( esc_html__( 'Save Changes', 'softech-addons-for-elementor' ), 'primary', 'sits_save_payment_general' );
			?>
		</form>
		<?php
	}

	// -----------------------------------------------------------------------
	// Transactions tab.
	// FIX 2 - filter UI stays in #sits-filter-area (never replaced).
	//          results live in #sits-transaction-results (replaced by AJAX).
	// -----------------------------------------------------------------------
	private function render_transactions_tab() {
		if ( ! is_admin() || ! current_user_can( 'manage_options' ) ) {
			return;
		}

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

		$cache_key    = 'sits_payments_table_exists';
		$table_exists = wp_cache_get( $cache_key, 'sits' );
		if ( false === $table_exists ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$table_exists = ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) === $table );
			wp_cache_set( $cache_key, $table_exists, 'sits', HOUR_IN_SECONDS );
		}

		if ( ! $table_exists ) {
			echo '<div class="notice notice-warning"><p>' . esc_html__( 'Payments table does not exist. Please activate the payment module first.', 'softech-addons-for-elementor' ) . '</p></div>';
			return;
		}

		// Stats - FIX 4: one consolidated query.
		$stats = $this->get_transaction_stats( $table );

		// Initial filters from GET (page load, no AJAX).
		// phpcs:disable WordPress.Security.NonceVerification.Recommended
		$search     = isset( $_GET['sits_search'] )     ? sanitize_text_field( wp_unslash( $_GET['sits_search'] ) )     : '';
		$status     = isset( $_GET['sits_status'] )     ? sanitize_text_field( wp_unslash( $_GET['sits_status'] ) )     : '';
		$provider   = isset( $_GET['sits_provider'] )   ? sanitize_text_field( wp_unslash( $_GET['sits_provider'] ) )   : '';
		$currency   = isset( $_GET['sits_currency'] )   ? strtoupper( sanitize_text_field( wp_unslash( $_GET['sits_currency'] ) ) ) : '';
		$date_range = isset( $_GET['sits_date_range'] ) ? sanitize_text_field( wp_unslash( $_GET['sits_date_range'] ) ) : '';
		$date_from  = isset( $_GET['sits_date_from'] )  ? sanitize_text_field( wp_unslash( $_GET['sits_date_from'] ) )  : '';
		$date_to    = isset( $_GET['sits_date_to'] )    ? sanitize_text_field( wp_unslash( $_GET['sits_date_to'] ) )    : '';
		$paged      = isset( $_GET['paged'] )           ? max( 1, intval( $_GET['paged'] ) )                            : 1;
		// phpcs:enable WordPress.Security.NonceVerification.Recommended

		$per_page         = 20;
		$offset           = ( $paged - 1 ) * $per_page;
		$where_conditions = $this->build_where_conditions( $search, $status, $provider, $currency, $date_range, $date_from, $date_to );
		$where_clause     = $this->compile_where( $where_conditions );

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$total_items  = (int) $wpdb->get_var( 'SELECT COUNT(*) FROM `' . esc_sql( $table ) . '`' . $where_clause );
		$total_pages  = (int) ceil( $total_items / $per_page );

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$transactions = $wpdb->get_results(
			'SELECT * FROM `' . esc_sql( $table ) . '`' . $where_clause .
			' ORDER BY created_at DESC, id DESC LIMIT ' . intval( $per_page ) . ' OFFSET ' . intval( $offset )
		);

		$active_filters   = $this->build_active_filter_labels( $search, $status, $provider, $currency, $date_range );
		$currency_symbols = $this->get_currency_symbols();
		?>

		<!-- STATS (static, never replaced by AJAX) -->
		<div class="sits-transactions-header" style="display:flex;justify-content:space-between;align-items:center;margin-bottom:20px;">
			<h2><?php esc_html_e( 'Payment Transactions', 'softech-addons-for-elementor' ); ?></h2>
			<div class="sits-transactions-stats" style="display:flex;gap:20px;">
				<div class="sits-stat-box" style="padding:15px;background:#e8f5e9;border-radius:5px;text-align:center;">
					<strong style="display:block;font-size:24px;color:#2e7d32;"><?php echo esc_html( $stats['success'] ); ?></strong>
					<span style="font-size:12px;color:#666;"><?php esc_html_e( 'Successful', 'softech-addons-for-elementor' ); ?></span>
				</div>
				<div class="sits-stat-box" style="padding:15px;background:#fff3e0;border-radius:5px;text-align:center;">
					<strong style="display:block;font-size:24px;color:#ef6c00;"><?php echo esc_html( $stats['pending'] ); ?></strong>
					<span style="font-size:12px;color:#666;"><?php esc_html_e( 'Pending', 'softech-addons-for-elementor' ); ?></span>
				</div>
				<div class="sits-stat-box" style="padding:15px;background:#ffebee;border-radius:5px;text-align:center;">
					<strong style="display:block;font-size:24px;color:#c62828;"><?php echo esc_html( $stats['failed'] ); ?></strong>
					<span style="font-size:12px;color:#666;"><?php esc_html_e( 'Failed', 'softech-addons-for-elementor' ); ?></span>
				</div>
			</div>
		</div>

		<!-- FILTER AREA (static - never replaced by AJAX) -->
		<div id="sits-filter-area">
			<div class="sits-filters-wrapper-main">

				<!-- Filter selects -->
				<div class="sits-filters-wrapper">
					<div class="wp-core-ui main-filter">

						<div>
							<select id="sits-filter-status" name="sits_status">
								<option value=""><?php esc_html_e( 'All Statuses', 'softech-addons-for-elementor' ); ?></option>
								<option value="success" <?php selected( $status, 'success' ); ?>>&#10003; <?php esc_html_e( 'Success', 'softech-addons-for-elementor' ); ?></option>
								<option value="pending" <?php selected( $status, 'pending' ); ?>><?php esc_html_e( 'Pending', 'softech-addons-for-elementor' ); ?></option>
								<option value="failed"  <?php selected( $status, 'failed' ); ?>><?php esc_html_e( 'Failed', 'softech-addons-for-elementor' ); ?></option>
							</select>
						</div>

						<div>
							<select id="sits-filter-provider" name="sits_provider">
								<option value=""><?php esc_html_e( 'All Providers', 'softech-addons-for-elementor' ); ?></option>
								<option value="stripe" <?php selected( $provider, 'stripe' ); ?>><?php esc_html_e( 'Stripe', 'softech-addons-for-elementor' ); ?></option>
							</select>
						</div>

						<div>
							<select id="sits-filter-currency" name="sits_currency">
								<option value=""><?php esc_html_e( 'All Currencies', 'softech-addons-for-elementor' ); ?></option>
								<?php foreach ( $currency_symbols as $code => $symbol ) : ?>
									<option value="<?php echo esc_attr( $code ); ?>" <?php selected( $currency, $code ); ?>>
										<?php echo esc_html( $code . ' (' . $symbol . ')' ); ?>
									</option>
								<?php endforeach; ?>
							</select>
						</div>

						<div>
							<select id="sits-filter-date-range" name="sits_date_range">
								<option value=""><?php esc_html_e( 'All Time', 'softech-addons-for-elementor' ); ?></option>
								<option value="today"        <?php selected( $date_range, 'today' ); ?>><?php esc_html_e( 'Today', 'softech-addons-for-elementor' ); ?></option>
								<option value="yesterday"    <?php selected( $date_range, 'yesterday' ); ?>><?php esc_html_e( 'Yesterday', 'softech-addons-for-elementor' ); ?></option>
								<option value="last_7_days"  <?php selected( $date_range, 'last_7_days' ); ?>><?php esc_html_e( 'Last 7 Days', 'softech-addons-for-elementor' ); ?></option>
								<option value="last_30_days" <?php selected( $date_range, 'last_30_days' ); ?>><?php esc_html_e( 'Last 30 Days', 'softech-addons-for-elementor' ); ?></option>
								<option value="this_month"   <?php selected( $date_range, 'this_month' ); ?>><?php esc_html_e( 'This Month', 'softech-addons-for-elementor' ); ?></option>
								<option value="last_month"   <?php selected( $date_range, 'last_month' ); ?>><?php esc_html_e( 'Last Month', 'softech-addons-for-elementor' ); ?></option>
								<option value="custom"       <?php selected( $date_range, 'custom' ); ?>><?php esc_html_e( 'Custom Range', 'softech-addons-for-elementor' ); ?></option>
							</select>
						</div>

						

					</div><!-- .main-filter -->

					<!-- Custom date range -->
					<div id="sits-custom-date-range" style="display:<?php echo 'custom' === $date_range ? 'block' : 'none'; ?>;background:#f9f9f9;padding:15px;border-radius:5px;margin-top:10px;border:1px solid #e5e5e5;">
						<div style="display:grid;grid-template-columns:1fr 1fr;gap:15px;">
							<div>
								<label style="display:block;margin-bottom:6px;font-weight:600;font-size:13px;color:#555;">
									<?php esc_html_e( 'From Date', 'softech-addons-for-elementor' ); ?>
								</label>
								<input type="date" id="sits-filter-date-from" name="sits_date_from" value="<?php echo esc_attr( $date_from ); ?>" style="width:100%;padding:10px 12px;border:1px solid #ddd;border-radius:5px;font-size:14px;">
							</div>
							<div>
								<label style="display:block;margin-bottom:6px;font-weight:600;font-size:13px;color:#555;">
									<?php esc_html_e( 'To Date', 'softech-addons-for-elementor' ); ?>
								</label>
								<input type="date" id="sits-filter-date-to" name="sits_date_to" value="<?php echo esc_attr( $date_to ); ?>" style="width:100%;padding:10px 12px;border:1px solid #ddd;border-radius:5px;font-size:14px;">
							</div>
						</div>
					</div>
				</div><!-- .sits-filters-wrapper -->

				<!-- Search - FIX 2: no button, placeholder, live search via JS. -->
				<div class="sits-transactions-search">
					<input type="search"
					       id="sits-search-input"
					       value="<?php echo esc_attr( $search ); ?>"
					       placeholder="<?php esc_attr_e( 'Search by name...', 'softech-addons-for-elementor' ); ?>">
				</div>

			</div><!-- .sits-filters-wrapper-main -->
		</div><!-- #sits-filter-area -->
		
        <div style="display:flex;justify-content:flex-end;margin-bottom:10px;">
    <button type="button" id="sits-export-csv" class="button">
        <span class="dashicons dashicons-download" style="vertical-align:middle;margin-top:-2px;"></span>
        <?php esc_html_e( 'Export CSV', 'softech-addons-for-elementor' ); ?>
    </button>
    </div>
    
		<!-- RESULTS AREA (replaced on every AJAX call) -->
		<div id="sits-transaction-results">
			<?php $this->render_transaction_results( $transactions, $total_items, $total_pages, $paged, $active_filters, $search, $status, $provider, $currency, $date_range ); ?>
		</div>

		<?php
	}

	// -----------------------------------------------------------------------
	// Results renderer - shared by render_transactions_tab() and ajax_filter_transactions().
	// Outputs ONLY the active-filters bar, table, and pagination.
	// -----------------------------------------------------------------------
	private function render_transaction_results(
		$transactions,
		$total_items,
		$total_pages,
		$paged,
		$active_filters,
		$search,
		$status,
		$provider,
		$currency,
		$date_range
	) {
		$currency_symbols = $this->get_currency_symbols();

		if ( ! empty( $active_filters ) ) : ?>
			<div class="sits-active-filters-wrapper" style="background:#e8f5e9;border:1px solid #4caf50;border-radius:5px;padding:12px 15px;margin-bottom:15px;display:flex;justify-content:space-between;align-items:center;">
				<div style="display:flex;align-items:center;gap:15px;flex-wrap:wrap;">
					<span style="font-weight:600;color:#2e7d32;">
						<span class="dashicons dashicons-yes-alt" style="color:#4caf50;"></span>
						<?php esc_html_e( 'Active Filters:', 'softech-addons-for-elementor' ); ?>
					</span>
					<?php foreach ( $active_filters as $filter ) : ?>
						<span class="sits-filter-tag" style="background:#fff;padding:5px 12px;border-radius:3px;font-size:13px;border:1px solid #ddd;">
							<?php echo wp_kses_post( $filter ); ?>
						</span>
					<?php endforeach; ?>
					<button type="button" class="button sits-clear-all-filters">
						<?php esc_html_e( 'Clear All Filters', 'softech-addons-for-elementor' ); ?>
					</button>
				</div>
				<span style="color:#666;font-size:13px;">
					<?php
					echo esc_html(
						sprintf(
							/* translators: %d: number of results */
							_n( '%d result found', '%d results found', $total_items, 'softech-addons-for-elementor' ),
							$total_items
						)
					);
					?>
				</span>
			</div>
		<?php endif;

		if ( empty( $transactions ) ) : ?>
			<div style="background:#fff;padding:40px;text-align:center;border:1px solid #ddd;border-radius:5px;">
				<svg width="64" height="64" viewBox="0 0 64 64" fill="none" xmlns="http://www.w3.org/2000/svg" style="margin-bottom:20px;opacity:0.3;">
					<rect width="64" height="64" rx="12" fill="#e0e0e0"/>
					<path d="M20 28h24M20 36h24M20 44h16" stroke="#666" stroke-width="2" stroke-linecap="round"/>
				</svg>
				<h3 style="margin:0 0 10px;color:#666;"><?php esc_html_e( 'No transactions found', 'softech-addons-for-elementor' ); ?></h3>
				<p style="margin:0;color:#999;font-size:14px;"><?php esc_html_e( 'Try adjusting your filters or search term.', 'softech-addons-for-elementor' ); ?></p>
			</div>
		<?php else : ?>
			<table class="wp-list-table widefat fixed striped">
				<thead>
					<tr>
						<th style="padding:10px;"><?php esc_html_e( 'Id', 'softech-addons-for-elementor' ); ?></th>
						<th style="padding:10px;"><?php esc_html_e( 'Name', 'softech-addons-for-elementor' ); ?></th>
						<th style="padding:10px;"><?php esc_html_e( 'Email', 'softech-addons-for-elementor' ); ?></th>
						<th style="padding:10px;"><?php esc_html_e( 'Price', 'softech-addons-for-elementor' ); ?></th>
						<th style="padding:10px;"><?php esc_html_e( 'Currency', 'softech-addons-for-elementor' ); ?></th>
						<th style="padding:10px;"><?php esc_html_e( 'Status', 'softech-addons-for-elementor' ); ?></th>
						<th style="padding:10px;"><?php esc_html_e( 'Provider', 'softech-addons-for-elementor' ); ?></th>
						<th style="padding:10px;"><?php esc_html_e( 'Payment ID', 'softech-addons-for-elementor' ); ?></th>
						<th style="padding:10px;"><?php esc_html_e( 'Payment Page', 'softech-addons-for-elementor' ); ?></th>
						<th style="padding:10px;"><?php esc_html_e( 'Button ID', 'softech-addons-for-elementor' ); ?></th>
						<th style="padding:10px;"><?php esc_html_e( 'Date', 'softech-addons-for-elementor' ); ?></th>
						<th style="padding:10px;"><?php esc_html_e( 'Action', 'softech-addons-for-elementor' ); ?></th>
					</tr>
				</thead>
				<tbody>
					<?php foreach ( $transactions as $row_index => $transaction ) :
                     $row_number = $total_items - ( ( $paged - 1 ) * 20 ) - $row_index;
						$sym          = $currency_symbols[ $transaction->currency ] ?? $transaction->currency;
						$zero_decimal = in_array( $transaction->currency, array( 'JPY', 'KRW', 'VND' ), true );
						$decimals     = $zero_decimal ? 0 : 2;
						$page_data    = json_decode( $transaction->page_data, true );
						$page_id      = $page_data['id']    ?? 0;
						$page_title   = $page_data['title'] ?? 'Unknown Page';
					?>
					<tr>
						<td style="padding:10px;"><?php echo esc_html( $row_number ); ?></td>
						<td style="padding:10px;"><?php echo esc_html( $transaction->name ); ?></td>
						<td style="padding:10px;"><?php echo esc_html( $transaction->email ); ?></td>
						<td style="padding:10px;"><?php echo esc_html( $sym . number_format( $transaction->price, $decimals ) ); ?></td>
						<td style="padding:10px;"><?php echo esc_html( $transaction->currency ); ?></td>
						<td style="padding:10px;">
							<?php if ( 'success' === $transaction->status ) : ?>
								<span style="padding:5px 10px;background:#4caf50;color:white;border-radius:3px;font-size:12px;"><?php esc_html_e( 'Success', 'softech-addons-for-elementor' ); ?></span>
							<?php elseif ( 'pending' === $transaction->status ) : ?>
								<span style="padding:5px 10px;background:#ff9800;color:white;border-radius:3px;font-size:12px;"><?php esc_html_e( 'Pending', 'softech-addons-for-elementor' ); ?></span>
							<?php else : ?>
								<span style="padding:5px 10px;background:#f44336;color:white;border-radius:3px;font-size:12px;"><?php esc_html_e( 'Failed', 'softech-addons-for-elementor' ); ?></span>
							<?php endif; ?>
						</td>
						<td style="padding:10px;"><?php echo esc_html( $transaction->provider ?: 'stripe' ); ?></td>
						<td style="padding:10px;font-family:monospace;font-size:11px;"><?php echo esc_html( $transaction->payment_id ?: 'N/A' ); ?></td>
						<td style="padding:10px;">
							<a href="<?php echo esc_url( get_permalink( $page_id ) ); ?>" target="_blank">
								<?php echo esc_html( $page_title . ' (ID: ' . $page_id . ')' ); ?>
							</a>
						</td>
						<td style="padding:10px;"><?php echo esc_html( $transaction->button_id ?: 'N/A' ); ?></td>
						<td style="padding:10px;"><?php echo esc_html( gmdate( 'Y-m-d H:i:s', strtotime( $transaction->created_at ) ) ); ?></td>
						<td style="padding:10px;">
							<button type="button"
							        class="button button-small sitsel-delete-transaction"
							        data-id="<?php echo esc_attr( $transaction->id ); ?>"
							        style="background:#dc3545;color:#fff;border:none;padding:6px 12px;cursor:pointer;border-radius:4px;">
								<span class="dashicons dashicons-trash" style="font-size:14px;line-height:1.2;vertical-align:bottom !important;"></span>
							</button>
						</td>
					</tr>
					<?php endforeach; ?>
				</tbody>
			</table>

			<?php if ( $total_pages > 1 ) : ?>
				<div class="tablenav" style="margin-top:20px;">
					<div class="tablenav-pages">
						<?php
						for ( $i = 1; $i <= $total_pages; $i++ ) {
							if ( $i === $paged ) {
								echo '<span class="page-numbers current">' . esc_html( $i ) . '</span>';
							} else {
								echo '<a class="page-numbers sits-pagination-link" href="#" data-page="' . esc_attr( $i ) . '">' . esc_html( $i ) . '</a>';
							}
						}
						?>
					</div>
				</div>
			<?php endif; ?>
		<?php endif; ?>
		<?php
	}

	// -----------------------------------------------------------------------
	// AJAX - delete a single transaction
	// -----------------------------------------------------------------------
	public function ajax_delete_transaction() {
		check_ajax_referer( 'sits_admin_nonce', 'nonce' );
		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error( array( 'message' => esc_html__( 'Unauthorized', 'softech-addons-for-elementor' ) ) );
		}

		$transaction_id = isset( $_POST['transaction_id'] ) ? intval( $_POST['transaction_id'] ) : 0;
		if ( $transaction_id <= 0 ) {
			wp_send_json_error( array( 'message' => esc_html__( 'Invalid transaction ID', 'softech-addons-for-elementor' ) ) );
		}

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

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$deleted = $wpdb->delete( $table, array( 'id' => $transaction_id ), array( '%d' ) );
		if ( false === $deleted ) {
			wp_send_json_error( array( 'message' => esc_html__( 'Failed to delete transaction', 'softech-addons-for-elementor' ) ) );
		}

		wp_send_json_success( array(
			'message'    => esc_html__( 'Transaction deleted successfully', 'softech-addons-for-elementor' ),
			'deleted_id' => $transaction_id,
		) );
	}

	// -----------------------------------------------------------------------
	// Private helpers
	// -----------------------------------------------------------------------

	/**
	 * FIX 4 - Index-friendly WHERE conditions.
	 * Uses raw created_at range comparisons instead of DATE(created_at).
	 */
	private function build_where_conditions( $search, $status, $provider, $currency, $date_range, $date_from, $date_to ) {
		global $wpdb;
		$conditions = array();

		if ( ! empty( $search ) ) {
			$conditions[] = $wpdb->prepare(
				'(name LIKE %s OR email LIKE %s OR button_id LIKE %s)',
				'%' . $wpdb->esc_like( $search ) . '%',
				'%' . $wpdb->esc_like( $search ) . '%',
				'%' . $wpdb->esc_like( $search ) . '%'
			);
		}

		if ( ! empty( $status ) ) {
			$conditions[] = $wpdb->prepare( 'status = %s', $status );
		}

		if ( ! empty( $provider ) ) {
			$conditions[] = $wpdb->prepare( 'provider = %s', $provider );
		}

		if ( ! empty( $currency ) ) {
			$conditions[] = $wpdb->prepare( 'currency = %s', strtoupper( $currency ) );
		}

		if ( ! empty( $date_range ) ) {
			$today    = gmdate( 'Y-m-d' );
			$tomorrow = gmdate( 'Y-m-d', strtotime( '+1 day' ) );

			switch ( $date_range ) {
				case 'today':
					$conditions[] = $wpdb->prepare(
						'created_at >= %s AND created_at < %s',
						$today . ' 00:00:00',
						$tomorrow . ' 00:00:00'
					);
					break;

				case 'yesterday':
					$yesterday    = gmdate( 'Y-m-d', strtotime( '-1 day' ) );
					$conditions[] = $wpdb->prepare(
						'created_at >= %s AND created_at < %s',
						$yesterday . ' 00:00:00',
						$today . ' 00:00:00'
					);
					break;

				case 'last_7_days':
					$seven_days_ago = gmdate( 'Y-m-d', strtotime( '-7 days' ) );
					$conditions[]   = $wpdb->prepare( 'created_at >= %s', $seven_days_ago . ' 00:00:00' );
					break;

				case 'last_30_days':
					$thirty_days_ago = gmdate( 'Y-m-d', strtotime( '-30 days' ) );
					$conditions[]    = $wpdb->prepare( 'created_at >= %s', $thirty_days_ago . ' 00:00:00' );
					break;

				case 'this_month':
					$month_start  = gmdate( 'Y-m-01' );
					$conditions[] = $wpdb->prepare( 'created_at >= %s', $month_start . ' 00:00:00' );
					break;

				case 'last_month':
					$last_month_start = gmdate( 'Y-m-01', strtotime( 'first day of last month' ) );
					$this_month_start = gmdate( 'Y-m-01' );
					$conditions[]     = $wpdb->prepare(
						'created_at >= %s AND created_at < %s',
						$last_month_start . ' 00:00:00',
						$this_month_start . ' 00:00:00'
					);
					break;

				case 'custom':
					if ( ! empty( $date_from ) ) {
						$conditions[] = $wpdb->prepare( 'created_at >= %s', $date_from . ' 00:00:00' );
					}
					if ( ! empty( $date_to ) ) {
						$date_to_next = gmdate( 'Y-m-d', strtotime( $date_to . ' +1 day' ) );
						$conditions[] = $wpdb->prepare( 'created_at < %s', $date_to_next . ' 00:00:00' );
					}
					break;
			}
		}

		return $conditions;
	}

	/** Compile a WHERE clause from already-prepared condition strings. */
	private function compile_where( array $conditions ) {
		if ( empty( $conditions ) ) {
			return '';
		}
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		return ' WHERE ' . implode( ' AND ', $conditions );
	}

	/**
	 * FIX 4 - One consolidated stats query instead of 3+ separate ones.
	 */
	private function get_transaction_stats( $table ) {
		global $wpdb;

		$cache_key = 'sits_txn_stats_' . md5( $table );
		$cached    = wp_cache_get( $cache_key, 'sits' );
		if ( false !== $cached ) {
			return $cached;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$row = $wpdb->get_row(
			'SELECT
				SUM(CASE WHEN status = \'success\' THEN 1 ELSE 0 END) AS success_count,
				SUM(CASE WHEN status = \'pending\' THEN 1 ELSE 0 END) AS pending_count,
				SUM(CASE WHEN status = \'failed\'  THEN 1 ELSE 0 END) AS failed_count
			FROM `' . esc_sql( $table ) . '`'
		);

		$stats = array(
			'success' => $row ? (int) $row->success_count : 0,
			'pending' => $row ? (int) $row->pending_count : 0,
			'failed'  => $row ? (int) $row->failed_count  : 0,
		);

		wp_cache_set( $cache_key, $stats, 'sits', 300 );
		return $stats;
	}

	/** Human-readable labels for the active-filters bar. */
	private function build_active_filter_labels( $search, $status, $provider, $currency, $date_range ) {
		$labels = array();
		if ( ! empty( $search ) )     { $labels[] = '<strong>' . esc_html__( 'Search:',   'softech-addons-for-elementor' ) . '</strong> ' . esc_html( $search ); }
		if ( ! empty( $status ) )     { $labels[] = '<strong>' . esc_html__( 'Status:',   'softech-addons-for-elementor' ) . '</strong> ' . esc_html( ucfirst( $status ) ); }
		if ( ! empty( $provider ) )   { $labels[] = '<strong>' . esc_html__( 'Provider:', 'softech-addons-for-elementor' ) . '</strong> ' . esc_html( ucfirst( $provider ) ); }
		if ( ! empty( $currency ) )   { $labels[] = '<strong>' . esc_html__( 'Currency:', 'softech-addons-for-elementor' ) . '</strong> ' . esc_html( $currency ); }
		if ( ! empty( $date_range ) ) { $labels[] = '<strong>' . esc_html__( 'Date:',     'softech-addons-for-elementor' ) . '</strong> ' . esc_html( ucwords( str_replace( '_', ' ', $date_range ) ) ); }
		return $labels;
	}

	/** Central currency symbol map (single source of truth). */
	private function get_currency_symbols() {
		return array(
			'USD' => '$', 'EUR' => '€', 'GBP' => '£', 'INR' => '₹',
			'AUD' => 'A$', 'CAD' => 'C$', 'JPY' => '¥', 'CNY' => '¥',
			'CHF' => 'CHF', 'AED' => 'د.إ',
		);
	}
}

// FIX 3 - Boot via singleton; hooks register exactly once.
SITS_Elementor_Payment_Settings::instance();
