<?php
namespace ToDownload\Data;

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

final class Orders {
	// Table names are derived from $wpdb->prefix and plugin constants, not user input.
	// PHPCS flags variables in SQL strings even when only used for table names.
    // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter
    private static function new_public_id(string $prefix = ''): string {
        $uuid = function_exists('wp_generate_uuid4') ? (string) wp_generate_uuid4() : '';
        if ($uuid === '') {
            $uuid = (string) uniqid(($prefix !== '' ? $prefix : '2dl_'), true);
        }
        return $uuid;
    }

    public static function order_public_id(int $order_id): string {
        global $wpdb;
        $table = esc_sql($wpdb->prefix . 'ToDownload_orders');
        $order_id = (int) $order_id;
        if ($order_id <= 0) {
            return '';
        }
        $public = (string) $wpdb->get_var($wpdb->prepare('SELECT public_id FROM ' . $table . ' WHERE id = %d', $order_id));
        $public = trim($public);
        if ($public !== '') {
            return $public;
        }
        $public = self::new_public_id('ord_');
        $wpdb->update($table, ['public_id' => $public], ['id' => $order_id]);
        return $public;
    }

    public static function customer_public_id(int $customer_id): string {
        global $wpdb;
        $table = esc_sql($wpdb->prefix . 'ToDownload_customers');
        $customer_id = (int) $customer_id;
        if ($customer_id <= 0) {
            return '';
        }
        $public = (string) $wpdb->get_var($wpdb->prepare('SELECT public_id FROM ' . $table . ' WHERE id = %d', $customer_id));
        $public = trim($public);
        if ($public !== '') {
            return $public;
        }
        $public = self::new_public_id('cust_');
        $wpdb->update($table, ['public_id' => $public], ['id' => $customer_id]);
        return $public;
    }

    public static function get_order_by_public_id(string $public_id): ?array {
        global $wpdb;
        $table = esc_sql($wpdb->prefix . 'ToDownload_orders');
        $public_id = sanitize_text_field((string) $public_id);
        $public_id = trim($public_id);
        if ($public_id === '') {
            return null;
        }
        $row = $wpdb->get_row($wpdb->prepare('SELECT * FROM ' . $table . ' WHERE public_id = %s', $public_id), ARRAY_A);
        return is_array($row) ? $row : null;
    }

    public static function find_order_id_by_public_id(string $public_id): int {
        $order = self::get_order_by_public_id($public_id);
        return $order ? (int) ($order['id'] ?? 0) : 0;
    }
    public static function order_has_item(int $order_id, int $download_id): bool {
        global $wpdb;
        $table = esc_sql($wpdb->prefix . 'ToDownload_order_items');

        $order_id = (int) $order_id;
        $download_id = (int) $download_id;
        if ($order_id <= 0 || $download_id <= 0) {
            return false;
        }

        $found = (int) $wpdb->get_var($wpdb->prepare(
            'SELECT COUNT(1) FROM ' . $table . ' WHERE order_id = %d AND download_id = %d',
            $order_id,
            $download_id
        ));

        return $found > 0;
    }

    /**
     * If the order contains an All Access Pass product authored by $seller_user_id, returns that pass product ID.
     * Otherwise returns 0.
     */
    public static function get_all_access_pass_item_for_seller(int $order_id, int $seller_user_id): int {
        global $wpdb;

        $order_items = esc_sql($wpdb->prefix . 'ToDownload_order_items');
        $posts = esc_sql($wpdb->posts);
        $postmeta = esc_sql($wpdb->postmeta);

        $order_id = (int) $order_id;
        $seller_user_id = (int) $seller_user_id;
        if ($order_id <= 0 || $seller_user_id <= 0) {
            return 0;
        }

        $pass_id = (int) $wpdb->get_var($wpdb->prepare(
            'SELECT oi.download_id' . "\n"
                . 'FROM ' . $order_items . " oi\n"
                . 'JOIN ' . $posts . " p ON p.ID = oi.download_id\n"
                . 'JOIN ' . $postmeta . " pm ON pm.post_id = p.ID\n"
                . "WHERE oi.order_id = %d\n"
                . "  AND p.post_author = %d\n"
                . "  AND p.post_type = %s\n"
                . "  AND pm.meta_key = '_ToDownload_is_all_access_pass'\n"
                . "  AND pm.meta_value = '1'\n"
                . 'LIMIT 1',
            $order_id,
            $seller_user_id,
            \ToDownload\PostTypes\DownloadPostType::POST_TYPE
        ));

        return $pass_id > 0 ? $pass_id : 0;
    }
    public static function list_orders(int $limit = 50, int $offset = 0): array {
        global $wpdb;
        $orders = esc_sql($wpdb->prefix . 'ToDownload_orders');
        $customers = esc_sql($wpdb->prefix . 'ToDownload_customers');
        $order_items = esc_sql($wpdb->prefix . 'ToDownload_order_items');

        $limit = max(1, min(200, (int) $limit));
        $offset = max(0, (int) $offset);

        $rows = $wpdb->get_results(
            $wpdb->prepare(
                'SELECT o.*, c.email AS customer_email, c.first_name AS customer_first_name, c.last_name AS customer_last_name, COUNT(oi.id) AS items_count' . "\n"
                    . 'FROM ' . $orders . " o\n"
                    . 'JOIN ' . $customers . " c ON o.customer_id = c.id\n"
                    . 'LEFT JOIN ' . $order_items . " oi ON oi.order_id = o.id\n"
                    . "GROUP BY o.id\n"
                    . "ORDER BY o.created_at DESC\n"
                    . 'LIMIT %d OFFSET %d',
                $limit,
                $offset
            ),
            ARRAY_A
        );

        return is_array($rows) ? $rows : [];
    }

    public static function list_orders_filtered(int $limit, int $offset, array $filters = []): array {
        global $wpdb;

        $limit = max(1, min(5000, (int) $limit));
        $offset = max(0, (int) $offset);
		$q = isset($filters['q']) ? trim(sanitize_text_field((string) $filters['q'])) : '';
		$status = isset($filters['status']) ? sanitize_key(trim((string) $filters['status'])) : '';
        $seller_user_id = isset($filters['seller_user_id']) ? (int) $filters['seller_user_id'] : 0;
        $seller_user_id = max(0, $seller_user_id);

        $orders = esc_sql($wpdb->prefix . 'ToDownload_orders');
        $customers = esc_sql($wpdb->prefix . 'ToDownload_customers');
        $order_items = esc_sql($wpdb->prefix . 'ToDownload_order_items');

        $where = 'WHERE 1=1';
        $args = [];

        if ($status !== '') {
            $where .= ' AND o.status = %s';
            $args[] = $status;
        }

        if ($seller_user_id > 0) {
            $where .= ' AND o.seller_user_id = %d';
            $args[] = $seller_user_id;
        }

        if ($q !== '') {
            if (ctype_digit($q)) {
                $where .= ' AND o.id = %d';
                $args[] = (int) $q;
            } else {
                $where .= ' AND c.email LIKE %s';
                $args[] = '%' . $wpdb->esc_like($q) . '%';
            }
        }

        $sql = "SELECT o.*, c.email AS customer_email, c.first_name AS customer_first_name, c.last_name AS customer_last_name, COUNT(oi.id) AS items_count\n"
            . "FROM " . $orders . " o\n"
            . "JOIN " . $customers . " c ON o.customer_id = c.id\n"
            . "LEFT JOIN " . $order_items . " oi ON oi.order_id = o.id\n"
            . $where . "\n"
            . "GROUP BY o.id\n"
            . "ORDER BY o.created_at DESC\n"
            . "LIMIT %d OFFSET %d";
        $args[] = $limit;
        $args[] = $offset;
        return $wpdb->get_results($wpdb->prepare($sql, ...$args), ARRAY_A) ?: [];
    }

    public static function stats_summary(int $days = 30): array {
        global $wpdb;
        $days = max(1, min(3650, (int) $days));

		$orders = esc_sql($wpdb->prefix . 'ToDownload_orders');
        $since = gmdate('Y-m-d H:i:s', time() - ($days * DAY_IN_SECONDS));

        $all = $wpdb->get_row('SELECT COUNT(*) AS c, COALESCE(SUM(total), 0) AS s FROM ' . $orders . " WHERE status = 'complete'", ARRAY_A);
        $recent = $wpdb->get_row(
			$wpdb->prepare('SELECT COUNT(*) AS c, COALESCE(SUM(total), 0) AS s FROM ' . $orders . " WHERE status = 'complete' AND created_at >= %s", $since),
            ARRAY_A
        );

        return [
            'all_orders' => (int) ($all['c'] ?? 0),
            'all_total' => (float) ($all['s'] ?? 0),
            'last_30_orders' => (int) ($recent['c'] ?? 0),
            'last_30_total' => (float) ($recent['s'] ?? 0),
        ];
    }

    public static function refund_status_label(array $order_row): string {
        $status = (string) ($order_row['status'] ?? '');
        $details = self::refund_sync_details_from_row($order_row);

        if (empty($details)) {
            return $status === 'refunded' ? __('Refunded', '2download-connector') : '—';
        }

        $amount_minor = (int) ($details['amount_minor'] ?? 0);
        $currency = (string) ($details['currency'] ?? 'USD');
        $ts = (string) ($details['ts'] ?? '');
        $event_id = (string) ($details['event_id'] ?? '');
		$refund_id = (string) ($details['refund_id'] ?? '');
        $refunded_kind = (string) ($details['kind'] ?? '');

        $amount_label = __('Refund noted', '2download-connector');
        if ($amount_minor > 0) {
            $amount_major = (float) $amount_minor / 100.0;
            $amount_label = '$' . number_format($amount_major, 2) . ' ' . $currency;
        }

        $label = $refunded_kind === 'full' ? __('Refunded', '2download-connector') : __('Partial', '2download-connector');
        if ($status === 'refunded') {
            $label = __('Refunded', '2download-connector');
        }

        $parts = [$label, $amount_label];
        if ($ts !== '') {
            $parts[] = $ts;
        }
        if ($event_id !== '') {
            $parts[] = $event_id;
        }
		if ($refund_id !== '') {
			$parts[] = $refund_id;
		}
        return implode(' • ', $parts);
    }

    /**
     * Extracts last refund sync details from an order row (expects o.* including notes).
     * @return array{ts?:string,event_id?:string,refund_id?:string,amount_minor?:int,currency?:string,kind?:'full'|'partial'}
     */
    private static function refund_sync_details_from_row(array $order_row): array {
        $notes = isset($order_row['notes']) ? (string) $order_row['notes'] : '';
        if ($notes === '') {
            return [];
        }

        $order_total_minor = (int) round(max(0.0, (float) ($order_row['total'] ?? 0)) * 100);
        $currency = strtoupper((string) ($order_row['currency'] ?? 'USD'));

        $last_ts = '';
        $last_event_id = '';
        $last_refund_id = '';
        $last_amount_minor = null;
        $last_amount_currency = '';

        $lines = preg_split('/\r\n|\r|\n/', $notes) ?: [];
        foreach ($lines as $line) {
            $line = trim((string) $line);
            if ($line === '') {
                continue;
            }
            if (!preg_match('/^\[(?<ts>[^\]]+)\]\s+(?<msg>.+)$/', $line, $m)) {
                continue;
            }

            $ts = isset($m['ts']) ? (string) $m['ts'] : '';
            $msg = isset($m['msg']) ? (string) $m['msg'] : '';

            if (strpos($msg, 'ToDownload_refund_event_id: ') === 0) {
                $last_ts = $ts;
                $last_event_id = trim(substr($msg, strlen('ToDownload_refund_event_id: ')));
            } else if (strpos($msg, 'ToDownload_refund_id: ') === 0) {
                $last_ts = $ts;
                $last_refund_id = trim(substr($msg, strlen('ToDownload_refund_id: ')));
            } else if (strpos($msg, 'ToDownload_refund_amount: ') === 0) {
                $rest = trim(substr($msg, strlen('ToDownload_refund_amount: ')));
                if (preg_match('/^(?<minor>[0-9]+)\s+(?<cur>[A-Z]{3})\b/', $rest, $mm)) {
                    $last_ts = $ts;
                    $last_amount_minor = (int) $mm['minor'];
                    $last_amount_currency = (string) $mm['cur'];
                }
            }
        }

        if ($last_ts === '' && $last_amount_minor === null && $last_refund_id === '' && $last_event_id === '') {
            return [];
        }

        $effective_currency = $last_amount_currency !== '' ? $last_amount_currency : $currency;
        $kind = 'partial';
        if ($last_amount_minor !== null && $order_total_minor > 0 && $last_amount_minor >= $order_total_minor) {
            $kind = 'full';
        }

        $out = [
            'ts' => $last_ts,
            'event_id' => $last_event_id,
            'refund_id' => $last_refund_id,
            'currency' => $effective_currency,
            'kind' => $kind,
        ];
        if ($last_amount_minor !== null) {
            $out['amount_minor'] = (int) $last_amount_minor;
        }
        return $out;
    }

    public static function list_customers(int $limit = 50, int $offset = 0): array {
        global $wpdb;
        $customers = esc_sql($wpdb->prefix . 'ToDownload_customers');
        $orders = esc_sql($wpdb->prefix . 'ToDownload_orders');

        $limit = max(1, min(200, (int) $limit));
        $offset = max(0, (int) $offset);

        $rows = $wpdb->get_results(
            $wpdb->prepare(
                'SELECT c.*, COUNT(o.id) AS orders_count, SUM(o.total) AS total_spent' . "\n"
                    . 'FROM ' . $customers . " c\n"
                    . 'LEFT JOIN ' . $orders . " o ON o.customer_id = c.id\n"
                    . "GROUP BY c.id\n"
                    . "ORDER BY c.created_at DESC\n"
                    . 'LIMIT %d OFFSET %d',
                $limit,
                $offset
            ),
            ARRAY_A
        );

        return is_array($rows) ? $rows : [];
    }

    public static function get_customer(int $customer_id): ?array {
        global $wpdb;
		$table = esc_sql($wpdb->prefix . 'ToDownload_customers');
        $row = $wpdb->get_row($wpdb->prepare('SELECT * FROM ' . $table . ' WHERE id = %d', $customer_id), ARRAY_A);
        return is_array($row) ? $row : null;
    }

    public static function create_customer(array $data): int {
        global $wpdb;
		$table = esc_sql($wpdb->prefix . 'ToDownload_customers');
        $now = current_time('mysql');

        $email = sanitize_email((string) ($data['email'] ?? ''));
        $first = sanitize_text_field((string) ($data['first_name'] ?? ''));
        $last = sanitize_text_field((string) ($data['last_name'] ?? ''));
        $user_id = isset($data['user_id']) ? (int) $data['user_id'] : null;

        $existing = (int) $wpdb->get_var($wpdb->prepare('SELECT id FROM ' . $table . ' WHERE email = %s', $email));
        if ($existing) {
            // Keep updated names if provided. Only set user_id when provided to avoid unlinking.
            $update = [
                'first_name' => $first ?: null,
                'last_name' => $last ?: null,
                'updated_at' => $now,
            ];
            if ($user_id && $user_id > 0) {
                $update['user_id'] = $user_id;
            }
            $wpdb->update($table, $update, ['id' => $existing]);
			// Ensure public_id exists for browser-safe references.
			self::customer_public_id($existing);
            return $existing;
        }

        $wpdb->insert($table, [
			'public_id' => self::new_public_id('cust_'),
            'user_id' => $user_id,
            'email' => $email,
            'first_name' => $first ?: null,
            'last_name' => $last ?: null,
            'created_at' => $now,
            'updated_at' => $now,
        ]);
        return (int) $wpdb->insert_id;
    }

    public static function link_customer_user_by_email(string $email, int $user_id): void {
        global $wpdb;
		$table = esc_sql($wpdb->prefix . 'ToDownload_customers');
        $now = current_time('mysql');

        $email = sanitize_email($email);
        $user_id = (int) $user_id;
        if ($email === '' || !is_email($email) || $user_id <= 0) {
            return;
        }

        $wpdb->update($table, [
            'user_id' => $user_id,
            'updated_at' => $now,
        ], ['email' => $email]);
    }

    public static function find_orders_by_user_id(int $user_id): array {
        global $wpdb;
        $orders = esc_sql($wpdb->prefix . 'ToDownload_orders');
        $customers = esc_sql($wpdb->prefix . 'ToDownload_customers');

        $user_id = (int) $user_id;
        if ($user_id <= 0) {
            return [];
        }

        $rows = $wpdb->get_results($wpdb->prepare(
            'SELECT o.* FROM ' . $orders . ' o JOIN ' . $customers . ' c ON o.customer_id = c.id WHERE c.user_id = %d ORDER BY o.created_at DESC',
            $user_id
        ), ARRAY_A);

        return is_array($rows) ? $rows : [];
    }

    public static function find_order_id_by_transaction_id(string $transaction_id): int {
        global $wpdb;
		$table = esc_sql($wpdb->prefix . 'ToDownload_orders');

        $transaction_id = sanitize_text_field((string) $transaction_id);
        if ($transaction_id === '') {
            return 0;
        }

        $id = (int) $wpdb->get_var($wpdb->prepare(
            'SELECT id FROM ' . $table . ' WHERE transaction_id = %s ORDER BY id DESC LIMIT 1',
            $transaction_id
        ));
        return $id > 0 ? $id : 0;
    }

    public static function create_order(array $data): int {
        global $wpdb;
		$table = esc_sql($wpdb->prefix . 'ToDownload_orders');
        $now = current_time('mysql');

		$seller_user_id = null;
		if (isset($data['seller_user_id'])) {
			$seller_user_id = (int) $data['seller_user_id'];
			if ($seller_user_id <= 0) {
				$seller_user_id = null;
			}
		}

        $wpdb->insert($table, [
			'public_id' => self::new_public_id('ord_'),
            'customer_id' => (int) $data['customer_id'],
			'seller_user_id' => $seller_user_id,
            'status' => sanitize_text_field((string) ($data['status'] ?? 'pending')),
            'currency' => sanitize_text_field((string) ($data['currency'] ?? 'USD')),
            'subtotal' => (float) ($data['subtotal'] ?? 0),
            'tax' => (float) ($data['tax'] ?? 0),
            'discount' => (float) ($data['discount'] ?? 0),
            'total' => (float) ($data['total'] ?? 0),
            'gateway' => sanitize_text_field((string) ($data['gateway'] ?? '')),
            'transaction_id' => sanitize_text_field((string) ($data['transaction_id'] ?? '')) ?: null,
            'notes' => $data['notes'] ?? null,
            'created_at' => $now,
            'updated_at' => $now,
        ]);
        return (int) $wpdb->insert_id;
    }

    public static function add_order_item(int $order_id, int $download_id, float $price): void {
        global $wpdb;
		$table = esc_sql($wpdb->prefix . 'ToDownload_order_items');
        $now = current_time('mysql');

        $wpdb->insert($table, [
            'order_id' => $order_id,
            'download_id' => $download_id,
            'price' => $price,
            'created_at' => $now,
        ]);
    }

    public static function update_status(int $order_id, string $status, ?string $transaction_id = null): void {
        global $wpdb;
		$table = esc_sql($wpdb->prefix . 'ToDownload_orders');
        $now = current_time('mysql');

        $data = [
            'status' => sanitize_text_field($status),
            'updated_at' => $now,
        ];
        if ($transaction_id !== null) {
            $data['transaction_id'] = sanitize_text_field($transaction_id) ?: null;
        }

        $wpdb->update($table, $data, ['id' => $order_id]);
    }

    public static function get_order(int $order_id): ?array {
        global $wpdb;
		$table = esc_sql($wpdb->prefix . 'ToDownload_orders');
        $row = $wpdb->get_row($wpdb->prepare('SELECT * FROM ' . $table . ' WHERE id = %d', $order_id), ARRAY_A);
        return is_array($row) ? $row : null;
    }

    public static function order_notes_contain(int $order_id, string $needle): bool {
        $order = self::get_order($order_id);
        if (!$order) {
            return false;
        }
        $notes = (string) ($order['notes'] ?? '');
        if ($notes === '') {
            return false;
        }
		return strpos($notes, $needle) !== false;
    }

    public static function append_order_note(int $order_id, string $note): void {
        global $wpdb;
		$table = esc_sql($wpdb->prefix . 'ToDownload_orders');
        $now = current_time('mysql');

        $order = self::get_order($order_id);
        if (!$order) {
            return;
        }

        $prefix = '[' . $now . '] ';
        $note = trim($note);
        if ($note === '') {
            return;
        }

        $existing = (string) ($order['notes'] ?? '');
        $combined = $existing !== '' ? (rtrim($existing) . "\n" . $prefix . $note) : ($prefix . $note);

        $wpdb->update($table, [
            'notes' => $combined,
            'updated_at' => $now,
        ], ['id' => $order_id]);
    }

    public static function get_order_items(int $order_id): array {
        global $wpdb;
		$table = esc_sql($wpdb->prefix . 'ToDownload_order_items');
        $rows = $wpdb->get_results($wpdb->prepare('SELECT * FROM ' . $table . ' WHERE order_id = %d', $order_id), ARRAY_A);
        return is_array($rows) ? $rows : [];
    }

    public static function find_orders_by_email(string $email): array {
        global $wpdb;
        $orders = esc_sql($wpdb->prefix . 'ToDownload_orders');
        $customers = esc_sql($wpdb->prefix . 'ToDownload_customers');

        $email = sanitize_email($email);
        $rows = $wpdb->get_results($wpdb->prepare(
            'SELECT o.* FROM ' . $orders . ' o JOIN ' . $customers . ' c ON o.customer_id = c.id WHERE c.email = %s ORDER BY o.created_at DESC',
            $email
        ), ARRAY_A);

        return is_array($rows) ? $rows : [];
    }

    public static function email_has_purchased_download(string $email, int $download_id): bool {
        global $wpdb;
        $orders = esc_sql($wpdb->prefix . 'ToDownload_orders');
        $customers = esc_sql($wpdb->prefix . 'ToDownload_customers');
        $order_items = esc_sql($wpdb->prefix . 'ToDownload_order_items');

        $email = sanitize_email($email);
        $download_id = (int) $download_id;
        if ($email === '' || !is_email($email) || $download_id <= 0) {
            return false;
        }

        $found = (int) $wpdb->get_var($wpdb->prepare(
            'SELECT COUNT(1)' . "\n"
                . 'FROM ' . $order_items . " oi\n"
                . 'JOIN ' . $orders . " o ON o.id = oi.order_id\n"
                . 'JOIN ' . $customers . " c ON c.id = o.customer_id\n"
                . "WHERE c.email = %s AND o.status = 'complete' AND oi.download_id = %d",
            $email,
            $download_id
        ));

        return $found > 0;
    }

    public static function user_has_purchased_download(int $user_id, int $download_id): bool {
        global $wpdb;
        $orders = esc_sql($wpdb->prefix . 'ToDownload_orders');
        $customers = esc_sql($wpdb->prefix . 'ToDownload_customers');
        $order_items = esc_sql($wpdb->prefix . 'ToDownload_order_items');

        $user_id = (int) $user_id;
        $download_id = (int) $download_id;
        if ($user_id <= 0 || $download_id <= 0) {
            return false;
        }

        $found = (int) $wpdb->get_var($wpdb->prepare(
            'SELECT COUNT(1)' . "\n"
                . 'FROM ' . $order_items . " oi\n"
                . 'JOIN ' . $orders . " o ON o.id = oi.order_id\n"
                . 'JOIN ' . $customers . " c ON c.id = o.customer_id\n"
                . "WHERE c.user_id = %d AND o.status = 'complete' AND oi.download_id = %d",
            $user_id,
            $download_id
        ));

        return $found > 0;
    }

    /**
     * Returns orders that contain at least one item whose download post is authored by $seller_user_id.
     * Each row includes: order, customer, items (aggregated qty/line_total for seller items), seller_total.
     */
    public static function find_orders_for_seller(int $seller_user_id, int $limit = 50, int $offset = 0): array {
        global $wpdb;
        $orders = esc_sql($wpdb->prefix . 'ToDownload_orders');
        $customers = esc_sql($wpdb->prefix . 'ToDownload_customers');
        $order_items = esc_sql($wpdb->prefix . 'ToDownload_order_items');
        $posts = esc_sql($wpdb->posts);

        $limit = max(1, min(200, (int) $limit));
        $offset = max(0, (int) $offset);

        $order_rows = $wpdb->get_results(
            $wpdb->prepare(
                'SELECT DISTINCT o.*, c.email AS customer_email, c.first_name AS customer_first_name, c.last_name AS customer_last_name' . "\n"
                    . 'FROM ' . $orders . " o\n"
                    . 'JOIN ' . $customers . " c ON o.customer_id = c.id\n"
                    . 'JOIN ' . $order_items . " oi ON oi.order_id = o.id\n"
                    . 'JOIN ' . $posts . " p ON p.ID = oi.download_id\n"
                    . 'WHERE p.post_author = %d AND p.post_type = %s' . "\n"
                    . 'ORDER BY o.created_at DESC' . "\n"
                    . 'LIMIT %d OFFSET %d',
                $seller_user_id,
                \ToDownload\PostTypes\DownloadPostType::POST_TYPE,
                $limit,
                $offset
            ),
            ARRAY_A
        );

        if (!is_array($order_rows) || empty($order_rows)) {
            return [];
        }

        $out = [];
        foreach ($order_rows as $o) {
            $order_id = (int) ($o['id'] ?? 0);
            if (!$order_id) {
                continue;
            }

            $items = self::get_order_items_for_seller($order_id, $seller_user_id);
            $seller_total = 0.0;
            foreach ($items as $it) {
                $seller_total += (float) ($it['line_total'] ?? 0);
            }

            $out[] = [
                'order' => $o,
                'customer' => [
                    'email' => (string) ($o['customer_email'] ?? ''),
                    'first_name' => (string) ($o['customer_first_name'] ?? ''),
                    'last_name' => (string) ($o['customer_last_name'] ?? ''),
                ],
                'items' => $items,
                'seller_total' => (float) round($seller_total, 2),
            ];
        }

        return $out;
    }

    public static function get_order_items_for_seller(int $order_id, int $seller_user_id): array {
        global $wpdb;
        $order_items = esc_sql($wpdb->prefix . 'ToDownload_order_items');
        $posts = esc_sql($wpdb->posts);

        $rows = $wpdb->get_results(
            $wpdb->prepare(
                'SELECT oi.download_id, COUNT(*) AS qty, SUM(oi.price) AS line_total' . "\n"
                    . 'FROM ' . $order_items . " oi\n"
                    . 'JOIN ' . $posts . " p ON p.ID = oi.download_id\n"
                    . 'WHERE oi.order_id = %d AND p.post_author = %d AND p.post_type = %s' . "\n"
                    . 'GROUP BY oi.download_id' . "\n"
                    . 'ORDER BY oi.download_id ASC',
                $order_id,
                $seller_user_id,
                \ToDownload\PostTypes\DownloadPostType::POST_TYPE
            ),
            ARRAY_A
        );

        return is_array($rows) ? $rows : [];
    }

    /**
     * Returns unique customers who have purchased from a given seller.
     * Includes only completed orders.
     */
    public static function find_customers_for_seller(int $seller_user_id, int $limit = 100, int $offset = 0): array {
        global $wpdb;
        $orders = esc_sql($wpdb->prefix . 'ToDownload_orders');
        $customers = esc_sql($wpdb->prefix . 'ToDownload_customers');
        $order_items = esc_sql($wpdb->prefix . 'ToDownload_order_items');
        $posts = esc_sql($wpdb->posts);

        $limit = max(1, min(500, (int) $limit));
        $offset = max(0, (int) $offset);

        $rows = $wpdb->get_results(
            $wpdb->prepare(
                'SELECT c.email, c.first_name, c.last_name, COUNT(DISTINCT o.id) AS orders_count, SUM(oi.price) AS total' . "\n"
                    . 'FROM ' . $orders . " o\n"
                    . 'JOIN ' . $customers . " c ON o.customer_id = c.id\n"
                    . 'JOIN ' . $order_items . " oi ON oi.order_id = o.id\n"
                    . 'JOIN ' . $posts . " p ON p.ID = oi.download_id\n"
                    . 'WHERE o.status = %s AND p.post_author = %d AND p.post_type = %s' . "\n"
                    . 'GROUP BY o.customer_id' . "\n"
                    . 'ORDER BY total DESC' . "\n"
                    . 'LIMIT %d OFFSET %d',
                'complete',
                $seller_user_id,
                \ToDownload\PostTypes\DownloadPostType::POST_TYPE,
                $limit,
                $offset
            ),
            ARRAY_A
        );

        return is_array($rows) ? $rows : [];
    }

    public static function seller_earnings_total(int $seller_user_id): float {
        global $wpdb;
        $orders = esc_sql($wpdb->prefix . 'ToDownload_orders');
        $order_items = esc_sql($wpdb->prefix . 'ToDownload_order_items');
        $posts = esc_sql($wpdb->posts);

        $val = $wpdb->get_var(
            $wpdb->prepare(
                'SELECT SUM(oi.price)' . "\n"
                    . 'FROM ' . $orders . " o\n"
                    . 'JOIN ' . $order_items . " oi ON oi.order_id = o.id\n"
                    . 'JOIN ' . $posts . " p ON p.ID = oi.download_id\n"
                    . 'WHERE o.status = %s AND p.post_author = %d AND p.post_type = %s',
                'complete',
                $seller_user_id,
                \ToDownload\PostTypes\DownloadPostType::POST_TYPE
            )
        );
        $base = (float) ($val ?? 0);
        $base = (float) round($base, 2);

        $bundle = self::seller_bundle_earnings_from_notes($seller_user_id);
        return (float) round($base + $bundle, 2);
    }

    /**
     * Calculates seller net earnings (USD) after platform fee and processing fee (seller-paid).
     * This uses fee values stored in order notes.
     */
    public static function seller_net_earnings_total(int $seller_user_id): float {
        global $wpdb;
		$orders = esc_sql($wpdb->prefix . 'ToDownload_orders');

        $seller_user_id = (int) $seller_user_id;
        if ($seller_user_id <= 0) {
            return 0.0;
        }

        $rows = $wpdb->get_results(
            $wpdb->prepare(
                'SELECT id, subtotal, notes FROM ' . $orders . ' WHERE status = %s AND seller_user_id = %d',
                'complete',
                $seller_user_id
            ),
            ARRAY_A
        );
        if (!is_array($rows) || empty($rows)) {
            return 0.0;
        }

        $sum = 0.0;
        foreach ($rows as $r) {
            $subtotal = (float) ($r['subtotal'] ?? 0);
            $notes = (string) ($r['notes'] ?? '');

            // If seller pays processing fees (separate_transfer) and the processing fee hasn't been recorded yet,
            // do not include this order in net totals to avoid displaying an incorrect net.
            if (
                $notes !== ''
                && strpos($notes, 'ToDownload_payout_mode: separate_transfer') !== false
                && !self::has_processing_fee_note($notes)
            ) {
                continue;
            }

            $net = self::seller_net_amount_from_notes($subtotal, $notes);
            $sum += $net;
        }

        return (float) round($sum, 2);
    }

    public static function seller_net_earnings_total_estimated(int $seller_user_id, float $fallback_platform_fee_percent): float {
        global $wpdb;
		$orders = esc_sql($wpdb->prefix . 'ToDownload_orders');

        $seller_user_id = (int) $seller_user_id;
        if ($seller_user_id <= 0) {
            return 0.0;
        }

        $rows = $wpdb->get_results(
            $wpdb->prepare(
                'SELECT id, subtotal, notes FROM ' . $orders . ' WHERE status = %s AND seller_user_id = %d',
                'complete',
                $seller_user_id
            ),
            ARRAY_A
        );
        if (!is_array($rows) || empty($rows)) {
            return 0.0;
        }

        $sum = 0.0;
        foreach ($rows as $r) {
            $subtotal = (float) ($r['subtotal'] ?? 0);
            $notes = (string) ($r['notes'] ?? '');
            $sum += self::seller_net_amount_estimated($subtotal, $notes, $fallback_platform_fee_percent);
        }

        return (float) round($sum, 2);
    }

    /**
     * Computes a net amount (USD) from a gross amount and order notes.
     *
     * Notes expected (best effort):
     * - ToDownload_platform_fee_minor: <int>
     * - ToDownload_platform_fee_percent: <float>
     * - ToDownload_studio_processing_fee_minor: <int>
     */
    public static function seller_net_amount_from_notes(float $gross_amount, string $notes): float {
        $gross_amount = (float) round(max(0.0, $gross_amount), 2);
        $gross_minor = (int) round($gross_amount * 100);
        if ($gross_minor <= 0) {
            return 0.0;
        }

        $fee_minor = 0;

        $platform_fee_minor = self::platform_fee_minor_from_notes($notes);
        if ($platform_fee_minor === null) {
            $platform_fee_percent = self::platform_fee_percent_from_notes($notes);
            if ($platform_fee_percent !== null) {
                $platform_fee_minor = (int) round($gross_minor * ($platform_fee_percent / 100.0));
            }
        }
        if ($platform_fee_minor !== null) {
            $fee_minor += max(0, (int) $platform_fee_minor);
        }

        $studio_fee_minor = self::studio_processing_fee_minor_from_notes($notes);
        if ($studio_fee_minor !== null) {
            $fee_minor += max(0, (int) $studio_fee_minor);
        }

        $fee_minor = max(0, min($fee_minor, $gross_minor));
        $net_minor = $gross_minor - $fee_minor;
        return (float) round($net_minor / 100.0, 2);
    }

    /**
     * Estimated seller net (USD) when fee notes are missing.
     *
     * Uses recorded fee notes when present; otherwise falls back to:
     * - platform_fee_percent * gross
     * - Stripe (US) estimate: 2.9% + $0.30
     */
    public static function seller_net_amount_estimated(
        float $gross_amount,
        string $notes,
        float $fallback_platform_fee_percent,
        float $stripe_percent = 2.9,
        float $stripe_fixed_usd = 0.30
    ): float {
        $gross_amount = (float) round(max(0.0, $gross_amount), 2);
        $gross_minor = (int) round($gross_amount * 100);
        if ($gross_minor <= 0) {
            return 0.0;
        }

        // If we have fee notes, prefer the exact computation.
        if (
            $notes !== ''
            && (
                strpos($notes, 'ToDownload_platform_fee_minor:') !== false
                || strpos($notes, 'ToDownload_platform_fee_percent:') !== false
                || strpos($notes, 'ToDownload_studio_processing_fee_minor:') !== false
            )
        ) {
            return self::seller_net_amount_from_notes($gross_amount, $notes);
        }

        $platform_percent = max(0.0, min(100.0, (float) $fallback_platform_fee_percent));
        $platform_fee_minor = (int) round($gross_minor * ($platform_percent / 100.0));
        $platform_fee_minor = max(0, min($platform_fee_minor, $gross_minor));

        $stripe_percent = max(0.0, min(100.0, (float) $stripe_percent));
        $stripe_fixed_minor = (int) round(max(0.0, (float) $stripe_fixed_usd) * 100);
        $stripe_fee_minor = (int) round($gross_minor * ($stripe_percent / 100.0)) + $stripe_fixed_minor;
        $stripe_fee_minor = max(0, min($stripe_fee_minor, $gross_minor));

        $fee_minor = $platform_fee_minor + $stripe_fee_minor;
        $fee_minor = max(0, min($fee_minor, $gross_minor));
        $net_minor = $gross_minor - $fee_minor;
        return (float) round($net_minor / 100.0, 2);
    }

    private static function platform_fee_minor_from_notes(string $notes): ?int {
        if ($notes === '') {
            return null;
        }
        if (preg_match('/ToDownload_platform_fee_minor:\s*([0-9]+)/', $notes, $m)) {
            return (int) ($m[1] ?? 0);
        }
        return null;
    }

    private static function platform_fee_percent_from_notes(string $notes): ?float {
        if ($notes === '') {
            return null;
        }
        if (preg_match('/ToDownload_platform_fee_percent:\s*([0-9]+(?:\.[0-9]+)?)/', $notes, $m)) {
            return (float) ($m[1] ?? 0.0);
        }
        return null;
    }

    public static function has_processing_fee_note(string $notes): bool {
        if ($notes === '') {
            return false;
        }
        return (strpos($notes, 'ToDownload_studio_processing_fee_minor:') !== false);
    }

    private static function studio_processing_fee_minor_from_notes(string $notes): ?int {
        if ($notes === '') {
            return null;
        }
        if (preg_match_all('/ToDownload_studio_processing_fee_minor:\s*([0-9]+)/', $notes, $m)) {
            $vals = isset($m[1]) && is_array($m[1]) ? $m[1] : [];
            if (!empty($vals)) {
                $last = end($vals);
                return (int) ($last !== false ? $last : 0);
            }
        }
        return null;
    }

    private static function seller_bundle_earnings_from_notes(int $seller_user_id): float {
        global $wpdb;
		$orders = esc_sql($wpdb->prefix . 'ToDownload_orders');

        $rows = $wpdb->get_results(
            $wpdb->prepare(
                'SELECT id, notes FROM ' . $orders . ' WHERE status = %s AND notes LIKE %s',
                'complete',
                '%ToDownload_bundle_price:%'
            ),
            ARRAY_A
        );
        if (!is_array($rows) || empty($rows)) {
            return 0.0;
        }

        $sum = 0.0;
        foreach ($rows as $r) {
            $notes = (string) ($r['notes'] ?? '');
            if ($notes === '') {
                continue;
            }
            if (!preg_match_all('/ToDownload_bundle_price:\s*(\d+)\s*:\s*([0-9]+(?:\.[0-9]{1,2})?)/', $notes, $m, PREG_SET_ORDER)) {
                continue;
            }
            foreach ($m as $match) {
                $bundle_id = (int) ($match[1] ?? 0);
                $price = (float) ($match[2] ?? 0);
                if (!$bundle_id || $price <= 0) {
                    continue;
                }
                $p = get_post($bundle_id);
                if (!$p) {
                    continue;
                }
                if ((int) $p->post_author !== (int) $seller_user_id || (string) $p->post_type !== \ToDownload\PostTypes\DownloadPostType::POST_TYPE) {
                    continue;
                }
                $sum += (float) $price;
            }
        }

        return (float) round($sum, 2);
    }

    // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter
}