<?php

namespace Elementor_Pay_Addons\Database;

defined('ABSPATH') || exit;

use Elementor_Pay_Addons\Shared\Utils;
use Exception;
use wpdb;

class Transaction_Query
{
    const TABLE = 'epa_transaction';

    /**
     * @var wpdb
     */
    private $wpdb;

    public function __construct()
    {
        global $wpdb;
        $this->wpdb = $wpdb;
    }

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

    public static function remove_table()
    {
        global $wpdb;
        $table_name = $wpdb->prefix . self::TABLE;
        $wpdb->query("DROP TABLE IF EXISTS $table_name");
    }

    public static function create_table()
    {
        global $wpdb;
        $table_name = $wpdb->prefix . self::TABLE;
        $charset_collate = $wpdb->get_charset_collate();
        $sql = "CREATE TABLE IF NOT EXISTS $table_name (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            transaction_id VARCHAR(100) NOT NULL,
            form_id VARCHAR(50) NULL,
            form_name VARCHAR(200) NULL,
            page_id VARCHAR(50) NULL,
            user_id VARCHAR(50) NULL,
            user_ip VARCHAR(45) NULL,
            user_agent VARCHAR(255) NULL,
            customer_id VARCHAR(100) NULL,
            customer_name VARCHAR(60) NULL,
            customer_email VARCHAR(100) NULL,
            amount DECIMAL NOT NULL,
            refund_amount DECIMAL DEFAULT 0,
            currency CHAR(3) NOT NULL,
            recurring TINYINT(1) DEFAULT 0,
            recurring_interval VARCHAR(50) NULL,
            recurring_count INT NULL,
            payment_status VARCHAR(50) NOT NULL,
            payment_method VARCHAR(50) NULL,
            form_data TEXT NULL,
            meta_data TEXT NULL,
            comment TEXT NULL,
            source VARCHAR(100) NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY unique_transaction (transaction_id),
            KEY transaction_id_idx (transaction_id),
            KEY customer_email_idx (customer_email),
            KEY customer_name_idx (customer_name),
            KEY amount_idx (amount),
            KEY user_id_idx (user_id),
            KEY payment_status_idx (payment_status),
            KEY created_at_idx (created_at)
        ) $charset_collate;";
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);
    }

    public function insert_transaction($transaction_id, $args)
    {
        $defaults = [
            'form_id' => isset( $_POST['form_id'] ) ? sanitize_text_field( wp_unslash( $_POST['form_id'] ) ) : null, // phpcs:ignore WordPress.Security.NonceVerification.Missing
            'form_name' => isset( $_POST['form_name'] ) ? sanitize_text_field( wp_unslash( $_POST['form_name'] ) ) : null, // phpcs:ignore WordPress.Security.NonceVerification.Missing
            'page_id' => isset( $_POST['post_id'] ) ? absint( $_POST['post_id'] ) : null, // phpcs:ignore WordPress.Security.NonceVerification.Missing
            'user_id' => get_current_user_id() ?? null,
            'user_ip' => Utils::get_client_ip(),
			'user_agent' => isset( $_SERVER['HTTP_USER_AGENT'] ) ? sanitize_textarea_field( wp_unslash( $_SERVER['HTTP_USER_AGENT'] ) ) : '',
            'customer_name' => null,
            'customer_email' => null,
            'customer_id' => null,
            'amount' => 0,
            'refund_amount' => 0,
            'currency' => '',
            'recurring' => 0,
            'recurring_interval' => null,
            'recurring_count' => null,
            'payment_status' => '',
            'payment_method' => null,
            'form_data' => null,
            'meta_data' => null,
            'comment' => '',
            'source' => null,
            'transaction_id' => $transaction_id,
            // 'created_at' and 'updated_at' are handled by MySQL defaults
        ];
        $data = wp_parse_args($args, $defaults);
        $data['form_data'] = is_array($data['form_data']) || is_object($data['form_data']) ? json_encode($data['form_data']) : $data['form_data'];
        $data['meta_data'] = is_array($data['meta_data']) || is_object($data['meta_data']) ? json_encode($data['meta_data']) : $data['meta_data'];
        $result = $this->wpdb->insert(
            $this->wpdb->prefix . self::TABLE,
            $data
        );
        if ($result) {
            return $this->wpdb->insert_id;
        }
        return 0;
    }

    public function update_by_transaction_id($transaction_id, $data)
    {
        return $this->wpdb->update(
            $this->wpdb->prefix . self::TABLE,
            $data,
            ['transaction_id' => $transaction_id]
        );
    }

    public function delete_by_id($id) {
        return $this->wpdb->delete($this->wpdb->prefix . self::TABLE, ['id' => $id]);
    }

    public function get_transactions($args = [])
    {
        $defaults = [
            'search' => '',
            'page' => 1,
            'per_page' => 20,
            'order_by' => 'created_at',
            'order_dir' => 'DESC',
        ];
        $args = wp_parse_args($args, $defaults);
        $table = $this->wpdb->prefix . self::TABLE;
        $where = [];
        $params = [];
        if (!empty($args['search'])) {
            $search = '%' . $this->wpdb->esc_like($args['search']) . '%';
            $where[] = "(payment_status LIKE %s OR form_data LIKE %s)";
            $params[] = $search;
            $params[] = $search;
        }
        if (!empty($args['status'])) {
            $where[] = "payment_status = %s";
            $params[] = $args['status'];
        }
        if (!empty($args['date_start'])) {
            $where[] = "created_at >= %s";
            $params[] = $args['date_start'];
        }
        if (!empty($args['date_end'])) {
            $where[] = "created_at <= %s";
            $params[] = $args['date_end'];
        }
        $where_sql = $where ? ('WHERE ' . implode(' AND ', $where)) : '';
        $offset = ($args['page'] - 1) * $args['per_page'];
        // Only allow sorting by whitelisted columns
        $allowed_order_by = ['payment_status', 'amount', 'user_name', 'created_at', 'payment_method'];
        $order_by = in_array($args['order_by'], $allowed_order_by) ? $args['order_by'] : 'created_at';
        $order_dir = strtoupper($args['order_dir']) === 'ASC' ? 'ASC' : 'DESC';
        // Count query
        $count_sql = "SELECT COUNT(*) FROM $table $where_sql";
        if ($params) {
            $total = $this->wpdb->get_var($this->wpdb->prepare($count_sql, ...$params));
        } else {
            $total = $this->wpdb->get_var($count_sql);
        }
        // Data query
        if (!empty($args['no_limit'])) {
            // Return all matching rows (no LIMIT) - useful for charts and exports
            $data_sql = "SELECT * FROM $table $where_sql ORDER BY $order_by $order_dir";
            if ($params) {
                $sql = $this->wpdb->prepare($data_sql, ...$params);
            } else {
                $sql = $data_sql;
            }
            $results = $this->wpdb->get_results($sql, ARRAY_A);
            return [
                'data' => $results,
                'total' => $total,
                'page' => 1,
                'per_page' => $total,
            ];
        }

        $data_sql = "SELECT * FROM $table $where_sql ORDER BY $order_by $order_dir LIMIT %d OFFSET %d";
        $data_params = $params;
        $data_params[] = $args['per_page'];
        $data_params[] = $offset;
        $sql = $this->wpdb->prepare($data_sql, ...$data_params);
        $results = $this->wpdb->get_results($sql, ARRAY_A);
        return [
            'data' => $results,
            'total' => $total,
            'page' => $args['page'],
            'per_page' => $args['per_page'],
        ];
    }

    /**
     * Returns summary statistics for transactions matching $args.
     * Keys returned:
     * - total_revenue (float)
     * - total_transactions (int)
     * - status_counts (assoc array status => count)
     * - revenue_by_day (array of ['day' => 'YYYY-MM-DD', 'revenue' => float]) for last N days
     */
    public function get_summary($args = []) {
        $defaults = [
            'search' => '',
            'status' => '',
            'date_start' => '',
            'date_end' => '',
        ];
        $args = wp_parse_args($args, $defaults);

        $table = $this->wpdb->prefix . self::TABLE;
        $where = [];
        $params = [];
        if (!empty($args['search'])) {
            $search = '%' . $this->wpdb->esc_like($args['search']) . '%';
            $where[] = "(payment_status LIKE %s OR form_data LIKE %s)";
            $params[] = $search;
            $params[] = $search;
        }
        if (!empty($args['status'])) {
            $where[] = "payment_status = %s";
            $params[] = $args['status'];
        }
        if (!empty($args['date_start'])) {
            $where[] = "created_at >= %s";
            $params[] = $args['date_start'];
        }
        if (!empty($args['date_end'])) {
            $where[] = "created_at <= %s";
            $params[] = $args['date_end'];
        }
        $where_sql = $where ? ('WHERE ' . implode(' AND ', $where)) : '';

        // Total revenue (count all amounts minus refunds)
        $revenue_sql = "SELECT COALESCE(SUM(amount - refund_amount),0) FROM $table $where_sql";
        if ($params) {
            $total_revenue = floatval($this->wpdb->get_var($this->wpdb->prepare($revenue_sql, ...$params)));
        } else {
            $total_revenue = floatval($this->wpdb->get_var($revenue_sql));
        }

        // Total transactions count
        $count_sql = "SELECT COUNT(*) FROM $table $where_sql";
        if ($params) {
            $total_tx = intval($this->wpdb->get_var($this->wpdb->prepare($count_sql, ...$params)));
        } else {
            $total_tx = intval($this->wpdb->get_var($count_sql));
        }

        // Status counts
        $status_sql = "SELECT payment_status, COUNT(*) as cnt FROM $table $where_sql GROUP BY payment_status";
        $status_counts = [];
        if ($params) {
            $rows = $this->wpdb->get_results($this->wpdb->prepare($status_sql, ...$params), ARRAY_A);
        } else {
            $rows = $this->wpdb->get_results($status_sql, ARRAY_A);
        }
        foreach (($rows ?: []) as $r) {
            $status_counts[$r['payment_status']] = intval($r['cnt']);
        }

        // Revenue by status
        $revenue_status_sql = "SELECT payment_status, COALESCE(SUM(amount - refund_amount),0) as revenue FROM $table $where_sql GROUP BY payment_status";
        $revenue_by_status = [];
        if ($params) {
            $rrows = $this->wpdb->get_results($this->wpdb->prepare($revenue_status_sql, ...$params), ARRAY_A);
        } else {
            $rrows = $this->wpdb->get_results($revenue_status_sql, ARRAY_A);
        }
        foreach (($rrows ?: []) as $r) {
            $revenue_by_status[$r['payment_status']] = floatval($r['revenue']);
        }

        // NOTE: Daily revenue aggregation removed — chart data is built from the full transactions payload elsewhere.

        return [
            'total_revenue' => $total_revenue,
            'total_transactions' => $total_tx,
            'status_counts' => $status_counts,
            'revenue_by_status' => $revenue_by_status,
        ];
    }

    public function get_transaction($id)
    {
        $table = $this->wpdb->prefix . self::TABLE;
        $txn = $this->wpdb->get_row($this->wpdb->prepare("SELECT * FROM $table WHERE id = %d", $id), ARRAY_A);
        if ($txn) {
            if (isset($txn['form_data'])) {
                $decoded = json_decode($txn['form_data'], true);
                if (json_last_error() === JSON_ERROR_NONE) {
                    $txn['form_data'] = $decoded;
                }
            }
            if (isset($txn['meta_data'])) {
                $decoded = json_decode($txn['meta_data'], true);
                if (json_last_error() === JSON_ERROR_NONE) {
                    $txn['meta_data'] = $decoded;
                }
            }
        }
        return $txn;
    }
}
