<?php
if (!defined('ABSPATH')) {
    exit;
}

/**
 * Local wallet manager for project credits.
 *
 * Stores balance, immutable ledger entries and top-up orders in local WP DB.
 */
class Aysa_Wallet_Manager {
    const TABLE_WALLETS = 'aysa_wallets';
    const TABLE_LEDGER = 'aysa_wallet_ledger';
    const TABLE_ORDERS = 'aysa_wallet_orders';
    const TABLE_ENDPOINT_USAGE = 'aysa_endpoint_usage_logs';
    const SCHEMA_VERSION = '1.2.0';
    const CRON_HOOK = 'aysa_wallet_sync_cron';

    /** @var self|null */
    private static $instance = null;

    /**
     * Plan definitions:
     * - monthly_credits_cents: monthly credit budget
     * - included_links: monthly included permanent links
     * - reserved_links_cents: portion of credits earmarked for links
     * - recommended_keywords: recommendation only (no hard limit)
     */
    private $plans = array(
        'starter_29' => array(
            'label' => 'Starter 29',
            'price_eur' => 29,
            'monthly_credits_cents' => 2900,
            'included_links' => 1,
            'reserved_links_cents' => 700,
            'recommended_keywords' => 29,
        ),
        'growth_69' => array(
            'label' => 'Growth 69',
            'price_eur' => 69,
            'monthly_credits_cents' => 6900,
            'included_links' => 2,
            'reserved_links_cents' => 1400,
            'recommended_keywords' => 69,
        ),
        'scale_99' => array(
            'label' => 'Scale 99',
            'price_eur' => 99,
            'monthly_credits_cents' => 9900,
            'included_links' => 3,
            'reserved_links_cents' => 2100,
            'recommended_keywords' => 149,
        ),
    );

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

    private function __construct() {
        $this->maybe_upgrade_schema();
        add_action('wp_ajax_aysa_wallet_get_snapshot', array($this, 'ajax_get_snapshot'));
        add_action('wp_ajax_aysa_wallet_create_order', array($this, 'ajax_create_order'));
        add_action('wp_ajax_aysa_wallet_get_orders', array($this, 'ajax_get_orders'));
        add_action('wp_ajax_aysa_wallet_force_sync', array($this, 'ajax_force_sync'));
        add_action('wp_ajax_aysa_wallet_track_usage', array($this, 'ajax_track_usage'));
        add_action('wp_ajax_aysa_wallet_sync_action_cost_registry', array($this, 'ajax_sync_action_cost_registry'));

        add_filter('cron_schedules', array($this, 'register_cron_schedules'));
        add_action('init', array($this, 'ensure_cron_scheduled'));
        add_action(self::CRON_HOOK, array($this, 'handle_cron_sync'));
    }

    private function sanitize_included_links_total($value, $plan) {
        $fallback = max(0, intval($plan['included_links'] ?? 0));
        $normalized = intval($value);
        if ($normalized < 0) {
            return $fallback;
        }

        // Included monthly links are tiny plan limits, not credit balances.
        if ($normalized > max(25, $fallback * 5)) {
            return $fallback;
        }

        return $normalized;
    }

    private function sanitize_included_links_used($value, $total) {
        $used = max(0, intval($value));
        return min($used, max(0, intval($total)));
    }

    public static function activate() {
        self::get_instance()->create_tables();
        update_option('aysa_wallet_schema_version', self::SCHEMA_VERSION, false);
    }

    private function maybe_upgrade_schema() {
        $current = (string) get_option('aysa_wallet_schema_version', '');
        if ($current !== self::SCHEMA_VERSION) {
            $this->create_tables();
            update_option('aysa_wallet_schema_version', self::SCHEMA_VERSION, false);
        }
    }

    public function create_tables() {
        global $wpdb;

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';

        $charset_collate = $wpdb->get_charset_collate();
        $wallets_table = $this->wallets_table();
        $ledger_table = $this->ledger_table();
        $orders_table = $this->orders_table();
        $endpoint_usage_table = $this->endpoint_usage_table();

        $sql_wallets = "CREATE TABLE {$wallets_table} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            project_id bigint(20) unsigned NOT NULL,
            plan_code varchar(40) NOT NULL,
            plan_price_eur int(11) NOT NULL DEFAULT 29,
            monthly_credits_cents int(11) NOT NULL DEFAULT 2900,
            balance_cents bigint(20) NOT NULL DEFAULT 0,
            included_links tinyint(3) unsigned NOT NULL DEFAULT 1,
            included_links_used int(11) unsigned NOT NULL DEFAULT 0,
            included_links_period_starts_at datetime DEFAULT NULL,
            included_links_period_ends_at datetime DEFAULT NULL,
            reserved_links_cents int(11) NOT NULL DEFAULT 700,
            recommended_keywords int(11) NOT NULL DEFAULT 29,
            is_active tinyint(1) NOT NULL DEFAULT 1,
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
            PRIMARY KEY  (id),
            UNIQUE KEY project_id (project_id),
            KEY plan_code (plan_code)
        ) {$charset_collate};";

        $sql_ledger = "CREATE TABLE {$ledger_table} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            project_id bigint(20) unsigned NOT NULL,
            wallet_id bigint(20) unsigned NOT NULL,
            order_id bigint(20) unsigned DEFAULT NULL,
            entry_type varchar(40) NOT NULL,
            amount_cents int(11) NOT NULL,
            source varchar(80) NOT NULL DEFAULT 'plugin',
            reference varchar(191) DEFAULT NULL,
            meta longtext DEFAULT NULL,
            created_by bigint(20) unsigned DEFAULT NULL,
            created_at datetime NOT NULL,
            PRIMARY KEY  (id),
            KEY project_id (project_id),
            KEY wallet_id (wallet_id),
            KEY order_id (order_id),
            KEY entry_type (entry_type),
            KEY created_at (created_at)
        ) {$charset_collate};";

        $sql_orders = "CREATE TABLE {$orders_table} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            project_id bigint(20) unsigned NOT NULL,
            wallet_id bigint(20) unsigned NOT NULL,
            requester_user_id bigint(20) unsigned DEFAULT NULL,
            status varchar(30) NOT NULL DEFAULT 'pending',
            credits_cents int(11) NOT NULL,
            amount_cents int(11) NOT NULL,
            currency varchar(8) NOT NULL DEFAULT 'EUR',
            payment_link text DEFAULT NULL,
            external_payment_id varchar(191) DEFAULT NULL,
            admin_notes text DEFAULT NULL,
            paid_at datetime DEFAULT NULL,
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
            PRIMARY KEY  (id),
            KEY project_id (project_id),
            KEY wallet_id (wallet_id),
            KEY status (status),
            KEY created_at (created_at)
        ) {$charset_collate};";

        $sql_endpoint_usage = "CREATE TABLE {$endpoint_usage_table} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            project_id bigint(20) unsigned NOT NULL,
            action_key varchar(120) DEFAULT NULL,
            endpoint varchar(191) NOT NULL,
            reference varchar(191) DEFAULT NULL,
            billed_cost_cents int(11) NOT NULL DEFAULT 0,
            provider_cost_cents int(11) NOT NULL DEFAULT 0,
            provider_cost_milli_cents bigint(20) NOT NULL DEFAULT 0,
            provider_cost_usd decimal(12,6) NOT NULL DEFAULT 0.000000,
            provider varchar(60) DEFAULT NULL,
            model varchar(128) DEFAULT NULL,
            input_tokens int(11) NOT NULL DEFAULT 0,
            output_tokens int(11) NOT NULL DEFAULT 0,
            total_tokens int(11) NOT NULL DEFAULT 0,
            raw_usage_json longtext DEFAULT NULL,
            meta longtext DEFAULT NULL,
            created_at datetime NOT NULL,
            PRIMARY KEY  (id),
            KEY project_id (project_id),
            KEY action_key (action_key),
            KEY endpoint (endpoint),
            KEY created_at (created_at)
        ) {$charset_collate};";

        dbDelta($sql_wallets);
        dbDelta($sql_ledger);
        dbDelta($sql_orders);
        dbDelta($sql_endpoint_usage);
    }

    private function wallets_table() {
        global $wpdb;
        return $wpdb->prefix . self::TABLE_WALLETS;
    }

    private function ledger_table() {
        global $wpdb;
        return $wpdb->prefix . self::TABLE_LEDGER;
    }

    private function orders_table() {
        global $wpdb;
        return $wpdb->prefix . self::TABLE_ORDERS;
    }

    private function endpoint_usage_table() {
        global $wpdb;
        return $wpdb->prefix . self::TABLE_ENDPOINT_USAGE;
    }

    public function get_project_id() {
        $project_id = intval(get_option('aysa_project_id', 0));
        if ($project_id > 0) {
            return $project_id;
        }

        if (isset($_REQUEST['project_id'])) {
            $project_id = intval(wp_unslash($_REQUEST['project_id']));
            if ($project_id > 0) {
                return $project_id;
            }
        }

        global $wpdb;
        $table = $this->wallets_table();
        $fallback_project_id = intval($wpdb->get_var(
            "SELECT project_id
             FROM {$table}
             WHERE project_id > 0
             ORDER BY is_active DESC, updated_at DESC, id DESC
             LIMIT 1"
        ));
        if ($fallback_project_id > 0) {
            update_option('aysa_project_id', $fallback_project_id, false);
            return $fallback_project_id;
        }

        return 0;
    }

    private function normalize_plan_code($plan_code) {
        $plan_code = sanitize_key((string) $plan_code);
        if (isset($this->plans[$plan_code])) {
            return $plan_code;
        }
        return 'starter_29';
    }

    private function default_period_start() {
        return $this->now();
    }

    private function default_period_end() {
        return gmdate('Y-m-d H:i:s', strtotime('+1 month', current_time('timestamp', true)));
    }

    private function normalize_period_value($value) {
        $value = trim((string) $value);
        if ($value === '') {
            return null;
        }
        $ts = strtotime($value);
        if ($ts === false || $ts <= 0) {
            return null;
        }
        return gmdate('Y-m-d H:i:s', $ts);
    }

    private function refresh_included_links_period($project_id, $wallet = null) {
        global $wpdb;

        $project_id = intval($project_id);
        if ($project_id <= 0) {
            return null;
        }

        if (!is_array($wallet) || empty($wallet)) {
            $wallet = $this->ensure_wallet($project_id);
        }
        if (!is_array($wallet) || empty($wallet)) {
            return null;
        }

        $wallet_id = intval($wallet['id'] ?? 0);
        if ($wallet_id <= 0) {
            return $wallet;
        }

        $period_end = $this->normalize_period_value($wallet['included_links_period_ends_at'] ?? '');
        $period_start = $this->normalize_period_value($wallet['included_links_period_starts_at'] ?? '') ?: $this->default_period_start();
        $used = max(0, intval($wallet['included_links_used'] ?? 0));
        $now_ts = current_time('timestamp', true);
        $changed = false;

        if ($period_end === null) {
            $period_end = $this->default_period_end();
            $used = 0;
            $changed = true;
        } else {
            $period_end_ts = strtotime($period_end);
            if ($period_end_ts !== false && $period_end_ts <= $now_ts) {
                $period_start = $this->default_period_start();
                $period_end = gmdate('Y-m-d H:i:s', strtotime('+1 month', $now_ts));
                $used = 0;
                $changed = true;
            }
        }

        if ($changed) {
            $wpdb->update(
                $this->wallets_table(),
                array(
                    'included_links_used' => $used,
                    'included_links_period_starts_at' => $period_start,
                    'included_links_period_ends_at' => $period_end,
                    'updated_at' => $this->now(),
                ),
                array('id' => $wallet_id),
                array('%d', '%s', '%s', '%s'),
                array('%d')
            );
            $wallet['included_links_used'] = $used;
            $wallet['included_links_period_starts_at'] = $period_start;
            $wallet['included_links_period_ends_at'] = $period_end;
        }

        return $wallet;
    }

    public function has_included_link_available($project_id) {
        $wallet = $this->refresh_included_links_period($project_id);
        if (!is_array($wallet) || empty($wallet)) {
            return false;
        }
        $total = max(0, intval($wallet['included_links'] ?? 0));
        $used = max(0, intval($wallet['included_links_used'] ?? 0));
        return $total > $used;
    }

    public function consume_included_link($project_id, $reference = '', $meta = array()) {
        global $wpdb;

        $wallet = $this->refresh_included_links_period($project_id);
        if (!is_array($wallet) || empty($wallet)) {
            return false;
        }

        $wallet_id = intval($wallet['id'] ?? 0);
        $total = max(0, intval($wallet['included_links'] ?? 0));
        $used = max(0, intval($wallet['included_links_used'] ?? 0));
        if ($wallet_id <= 0 || $total <= $used) {
            return false;
        }

        $new_used = $used + 1;
        $updated = $wpdb->update(
            $this->wallets_table(),
            array(
                'included_links_used' => $new_used,
                'updated_at' => $this->now(),
            ),
            array(
                'id' => $wallet_id,
                'included_links_used' => $used,
            ),
            array('%d', '%s'),
            array('%d', '%d')
        );

        if ($updated === false || intval($updated) < 1) {
            return false;
        }

        return array(
            'included_links_total' => $total,
            'included_links_used' => $new_used,
            'included_links_remaining' => max(0, $total - $new_used),
            'included_links_period_starts_at' => $wallet['included_links_period_starts_at'] ?? null,
            'included_links_period_ends_at' => $wallet['included_links_period_ends_at'] ?? null,
            'reference' => sanitize_text_field((string) $reference),
            'meta' => is_array($meta) ? $meta : array(),
        );
    }

    private function get_default_plan_code() {
        $stored = get_option('aysa_wallet_default_plan', 'starter_29');
        return $this->normalize_plan_code($stored);
    }

    private function get_sync_ttl_seconds() {
        $ttl = intval(get_option('aysa_wallet_sync_ttl_seconds', 180));
        if ($ttl < 60) {
            $ttl = 60;
        }
        return $ttl;
    }

    private function sync_lock_key($project_id) {
        return 'aysa_wallet_sync_lock_' . intval($project_id);
    }

    private function last_sync_option_key($project_id) {
        return 'aysa_wallet_last_sync_ts_' . intval($project_id);
    }

    private function usage_queue_key($project_id) {
        return 'aysa_wallet_usage_queue_' . intval($project_id);
    }

    private function get_remote_snapshot_endpoint() {
        return trim((string) get_option('aysa_wallet_remote_snapshot_endpoint', 'v1/wallet/sync'));
    }

    private function get_remote_usage_batch_endpoint() {
        return trim((string) get_option('aysa_wallet_remote_usage_batch_endpoint', 'v1/wallet/usage/batch'));
    }

    private function get_remote_order_endpoint() {
        return trim((string) get_option('aysa_wallet_remote_order_endpoint', 'v1/wallet/orders'));
    }

    private function get_app_aysa_topup_endpoint() {
        $override = '';
        if (defined('AYSA_WALLET_APP_TOPUP_ENDPOINT')) {
            $override = (string) AYSA_WALLET_APP_TOPUP_ENDPOINT;
        }
        if ($override === '') {
            $override = (string) get_option('aysa_wallet_app_topup_endpoint', 'https://app.aysa.ai/internal/billing/plugin-topup-checkout');
        }
        return trim($override);
    }

    private function get_app_aysa_internal_secret() {
        if (defined('AYSA_WALLET_APP_INTERNAL_SECRET')) {
            return trim((string) AYSA_WALLET_APP_INTERNAL_SECRET);
        }
        $stored = trim((string) get_option('aysa_wallet_app_internal_secret', ''));
        if ($stored !== '') {
            return $stored;
        }
        return 'aysa-local-internal-secret-2026';
    }

    private function get_remote_action_cost_sync_endpoint() {
        return trim((string) get_option('aysa_wallet_remote_action_cost_sync_endpoint', 'v1/billing/action-costs/sync'));
    }

    private function get_remote_action_cost_audit_endpoint() {
        return trim((string) get_option('aysa_wallet_remote_action_cost_audit_endpoint', 'v1/billing/action-costs/audit'));
    }

    private function get_action_cost_sync_interval_seconds() {
        $seconds = intval(get_option('aysa_action_cost_sync_interval_seconds', 6 * HOUR_IN_SECONDS));
        if ($seconds < HOUR_IN_SECONDS) {
            $seconds = HOUR_IN_SECONDS;
        }
        return $seconds;
    }

    private function get_action_cost_last_hash_option() {
        return 'aysa_action_cost_registry_last_hash';
    }

    private function get_action_cost_last_sync_option() {
        return 'aysa_action_cost_registry_last_sync_ts';
    }

    private function get_action_cost_projection_option() {
        return 'aysa_action_cost_registry_projection';
    }

    private function get_action_cost_projection_last_sync_option() {
        return 'aysa_action_cost_registry_projection_last_sync_ts';
    }

    private function get_usage_batch_size() {
        $size = intval(get_option('aysa_wallet_usage_batch_size', 25));
        if ($size < 1) {
            $size = 1;
        }
        if ($size > 100) {
            $size = 100;
        }
        return $size;
    }

    private function get_action_cost_csv_path() {
        return AYSA_PLUGIN_DIR . 'docs/aysa_actions_registry_wp_ajax_unique.csv';
    }

    private function has_remote_auth() {
        if (!class_exists('Aysa_Auth_Manager')) {
            $auth_file = AYSA_PLUGIN_DIR . 'includes/class-aysa-auth-manager.php';
            if (file_exists($auth_file)) {
                require_once $auth_file;
            }
        }
        if (!class_exists('Aysa_Auth_Manager')) {
            return false;
        }
        try {
            $auth = Aysa_Auth_Manager::get_instance();
            $status = $auth->is_authenticated();
            return is_array($status) && !empty($status['authenticated']);
        } catch (Exception $e) {
            return false;
        }
    }

    private function auth_request($endpoint, $method = 'GET', $data = null) {
        if ($this->has_remote_auth()) {
            try {
                $auth = Aysa_Auth_Manager::get_instance();
                return $auth->make_authenticated_request($endpoint, $method, $data);
            } catch (Exception $e) {
                return false;
            }
        }

        if ($this->is_plugin_bearer_wallet_endpoint($endpoint)) {
            return $this->direct_wallet_request($endpoint, $method, $data);
        }

        return false;
    }

    private function is_plugin_bearer_wallet_endpoint($endpoint) {
        $path = '/' . ltrim((string) $endpoint, '/');
        return in_array($path, array(
            '/v1/wallet/sync',
            '/v1/wallet/usage/batch',
        ), true);
    }

    private function get_remote_broker_base_url() {
        if (class_exists('Aysa_API_Config') && method_exists('Aysa_API_Config', 'get_api_base_url')) {
            return rtrim((string) Aysa_API_Config::get_api_base_url(), '/');
        }
        if (defined('AYSA_API_BASE_URL')) {
            return rtrim((string) AYSA_API_BASE_URL, '/');
        }
        return 'https://api.aysa.ai';
    }

    private function get_plugin_bearer_token() {
        $token = trim((string) get_option('aysa_user_secret_key', ''));
        if ($token !== '') {
            return $token;
        }

        $authData = get_option('aysa_activator_auth_data', array());
        if (is_array($authData)) {
            $token = trim((string) ($authData['secret_key'] ?? ''));
            if ($token !== '') {
                return $token;
            }
        }

        return '';
    }

    private function direct_wallet_request($endpoint, $method = 'GET', $data = null) {
        $base = $this->get_remote_broker_base_url();
        if ($base === '') {
            return false;
        }

        $token = $this->get_plugin_bearer_token();
        if ($token === '') {
            return false;
        }

        $url = $base . '/' . ltrim((string) $endpoint, '/');
        $args = array(
            'timeout' => 30,
            'headers' => array(
                'Accept' => 'application/json',
                'Authorization' => 'Bearer ' . $token,
            ),
        );

        $method = strtoupper((string) $method);
        if ($method !== 'GET') {
            $args['method'] = $method;
            $args['headers']['Content-Type'] = 'application/json';
            $args['body'] = wp_json_encode(is_array($data) ? $data : array());
        }

        $response = wp_remote_request($url, $args);
        if (is_wp_error($response)) {
            return false;
        }

        $code = intval(wp_remote_retrieve_response_code($response));
        $body = json_decode((string) wp_remote_retrieve_body($response), true);
        if ($code < 200 || $code >= 300 || !is_array($body)) {
            return false;
        }

        return $body;
    }

    private function get_remote_aysa_user_id() {
        if (!$this->has_remote_auth()) {
            return 0;
        }
        try {
            $auth = Aysa_Auth_Manager::get_instance();
            $status = $auth->is_authenticated();
            return is_array($status) ? intval($status['aysa_user_id'] ?? 0) : 0;
        } catch (Exception $e) {
            return 0;
        }
    }

    private function now() {
        return current_time('mysql');
    }

    private function request_app_aysa_topup_checkout($project_id, $credits_cents, $extra_payload = array()) {
        $endpoint = $this->get_app_aysa_topup_endpoint();
        $user_id = $this->get_remote_aysa_user_id();
        if ($endpoint === '' || $user_id <= 0) {
            return false;
        }

        $payload = array(
            'project_id' => intval($project_id),
            'aysa_user_id' => intval($user_id),
            'amount_cents' => intval($credits_cents),
        );
        if (is_array($extra_payload)) {
            foreach ($extra_payload as $key => $value) {
                $payload[$key] = $value;
            }
        }

        $response = wp_remote_post($endpoint, array(
            'timeout' => 30,
            'headers' => array(
                'Content-Type' => 'application/json',
                'Accept' => 'application/json',
                'Authorization' => 'Bearer ' . $this->get_app_aysa_internal_secret(),
            ),
            'body' => wp_json_encode($payload),
        ));

        if (is_wp_error($response)) {
            return false;
        }

        $code = intval(wp_remote_retrieve_response_code($response));
        $body = json_decode((string) wp_remote_retrieve_body($response), true);
        if ($code < 200 || $code >= 300 || !is_array($body) || empty($body['ok'])) {
            return false;
        }

        return array(
            'data' => array(
                'order_id' => (string) ($body['billing_order_id'] ?? ''),
                'status' => (string) ($body['status'] ?? 'pending'),
                'payment_link' => (string) ($body['checkout_url'] ?? ''),
                'checkout_url' => (string) ($body['checkout_url'] ?? ''),
                'amount_cents' => intval($body['amount_cents'] ?? $credits_cents),
                'credits_cents' => intval($body['credits_cents'] ?? $credits_cents),
                'currency_code' => (string) ($body['currency_code'] ?? 'EUR'),
            ),
        );
    }

    private function get_site_host_for_registry() {
        $host = wp_parse_url(site_url('/'), PHP_URL_HOST);
        $host = is_string($host) ? strtolower(trim($host)) : '';
        if ($host === '') {
            $host = 'unknown.local';
        }
        return preg_replace('/[^a-z0-9\\.-]/', '', $host);
    }

    private function to_bool_str($value) {
        if (is_bool($value)) {
            return $value ? 'yes' : 'no';
        }
        $raw = strtolower(trim((string) $value));
        return in_array($raw, array('1', 'true', 'yes', 'y', 'on'), true) ? 'yes' : 'no';
    }

    private function parse_action_cost_csv($path) {
        if (!is_string($path) || $path === '' || !file_exists($path)) {
            return array();
        }
        $fh = fopen($path, 'r');
        if ($fh === false) {
            return array();
        }

        $header = fgetcsv($fh);
        if (!is_array($header) || empty($header)) {
            fclose($fh);
            return array();
        }

        $map = array();
        foreach ($header as $idx => $name) {
            $map[strtolower(trim((string) $name))] = intval($idx);
        }

        $rows = array();
        while (($line = fgetcsv($fh)) !== false) {
            if (!is_array($line) || empty($line)) {
                continue;
            }
            $action = isset($map['action_key'], $line[$map['action_key']]) ? sanitize_key((string) $line[$map['action_key']]) : '';
            if ($action === '') {
                continue;
            }
            $cost = isset($map['cost_cents'], $line[$map['cost_cents']]) ? max(0, intval($line[$map['cost_cents']])) : 0;
            $real = isset($map['real_cost_cents'], $line[$map['real_cost_cents']]) ? max(0, intval($line[$map['real_cost_cents']])) : 0;
            $status = isset($map['status'], $line[$map['status']]) ? sanitize_text_field((string) $line[$map['status']]) : 'proposed_v1';
            $notes = isset($map['notes'], $line[$map['notes']]) ? sanitize_text_field((string) $line[$map['notes']]) : '';
            $billing_mode = isset($map['billing_mode'], $line[$map['billing_mode']]) ? sanitize_key((string) $line[$map['billing_mode']]) : '';
            if ($billing_mode === '') {
                $combined = strtolower($status . ' ' . $notes);
                if (strpos($combined, 'display_only') !== false) {
                    $billing_mode = 'display_only';
                } elseif (strpos($combined, 'api_billed') !== false) {
                    $billing_mode = 'api_billed';
                }
            }
            $rows[] = array(
                'action_key' => $action,
                'hooks' => isset($map['hooks'], $line[$map['hooks']]) ? sanitize_text_field((string) $line[$map['hooks']]) : '',
                'handlers' => isset($map['handlers'], $line[$map['handlers']]) ? sanitize_text_field((string) $line[$map['handlers']]) : '',
                'occurrences' => isset($map['occurrences'], $line[$map['occurrences']]) ? max(1, intval($line[$map['occurrences']])) : 1,
                'cost_cents' => $cost,
                'real_cost_cents' => $real,
                'billable' => isset($map['billable'], $line[$map['billable']]) ? $this->to_bool_str($line[$map['billable']]) : ($cost > 0 ? 'yes' : 'no'),
                'status' => $status,
                'notes' => $notes,
                'billing_mode' => $billing_mode,
            );
        }
        fclose($fh);

        return $rows;
    }

    private function get_action_cost_registry_map() {
        static $cache = null;
        if (is_array($cache)) {
            return $cache;
        }

        $cache = array();
        $rows = $this->parse_action_cost_csv($this->get_action_cost_csv_path());
        if (empty($rows)) {
            return $cache;
        }

        foreach ($rows as $row) {
            if (!is_array($row)) {
                continue;
            }
            $action = isset($row['action_key']) ? sanitize_key((string) $row['action_key']) : '';
            if ($action === '') {
                continue;
            }
            $cache[$action] = $row;
        }

        return $cache;
    }

    private function get_action_cost_projection_map() {
        static $projectionCache = null;
        if (is_array($projectionCache)) {
            return $projectionCache;
        }

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

        $projectionCache = array();
        foreach ($stored as $action => $row) {
            $action = sanitize_key((string) $action);
            if ($action === '' || !is_array($row)) {
                continue;
            }
            $projectionCache[$action] = $row;
        }

        return $projectionCache;
    }

    public function get_action_cost_config($action_key) {
        $action_key = sanitize_key((string) $action_key);
        if ($action_key === '') {
            return null;
        }
        $map = $this->get_action_cost_registry_map();
        $base = isset($map[$action_key]) && is_array($map[$action_key]) ? $map[$action_key] : null;
        $projection = $this->get_action_cost_projection_map();
        $remote = isset($projection[$action_key]) && is_array($projection[$action_key]) ? $projection[$action_key] : null;

        if (!is_array($base) && !is_array($remote)) {
            return null;
        }

        $merged = is_array($base) ? $base : array(
            'action_key' => $action_key,
            'cost_cents' => 0,
            'real_cost_cents' => 0,
            'billable' => 'no',
            'billing_mode' => 'free',
        );

        if (is_array($remote)) {
            $merged['authority'] = sanitize_key((string) ($remote['authority'] ?? ''));
            $merged['drift_statuses'] = isset($remote['drift_statuses']) && is_array($remote['drift_statuses']) ? $remote['drift_statuses'] : array();
            $merged['effective_cost_cents'] = max(0, intval($remote['effective_client_cost_cents'] ?? $merged['cost_cents'] ?? 0));
            $merged['effective_cost_rule'] = sanitize_text_field((string) ($remote['effective_client_cost_rule'] ?? ''));

            $canonical = isset($remote['canonical']) && is_array($remote['canonical']) ? $remote['canonical'] : array();
            if (!empty($canonical)) {
                $merged['canonical_billing_mode'] = sanitize_key((string) ($canonical['billing_mode'] ?? ''));
                $merged['provider_multiplier'] = max(1, intval($canonical['provider_multiplier'] ?? 5));
                $merged['fixed_cost_cents'] = max(0, intval($canonical['fixed_cost_cents'] ?? $merged['cost_cents'] ?? 0));
                $merged['canonical_billable'] = !empty($canonical['billable']) ? 'yes' : 'no';
            }
        }

        return $merged;
    }

    public function get_action_cost_cents($action_key, $fallback_cents = 0) {
        $fallback_cents = max(0, intval($fallback_cents));
        $config = $this->get_action_cost_config($action_key);
        if (!is_array($config)) {
            return $fallback_cents;
        }

        $authority = sanitize_key((string) ($config['authority'] ?? ''));
        if ($authority === 'api_wallet' || $authority === 'display_only') {
            return 0;
        }

        $billable = isset($config['billable']) ? ($this->to_bool_str($config['billable']) === 'yes') : false;
        $cost_cents = isset($config['effective_cost_cents'])
            ? max(0, intval($config['effective_cost_cents']))
            : (isset($config['cost_cents']) ? max(0, intval($config['cost_cents'])) : 0);

        if (!$billable || $cost_cents <= 0) {
            return 0;
        }

        return $cost_cents;
    }

    public function track_action_usage($project_id, $action_key, $reference = '', $meta = array(), $fallback_cents = 0, $endpoint = '') {
        $project_id = intval($project_id);
        $action_key = sanitize_key((string) $action_key);
        if ($project_id <= 0 || $action_key === '') {
            return false;
        }

        $meta_payload = is_array($meta) ? $meta : array();
        $override_cost_cents = max(0, intval($meta_payload['override_cost_cents'] ?? 0));
        $cost_cents = $override_cost_cents > 0
            ? $override_cost_cents
            : $this->get_action_cost_cents($action_key, $fallback_cents);
        if ($cost_cents <= 0) {
            return true;
        }

        $event_endpoint = sanitize_text_field((string) $endpoint);
        if ($event_endpoint === '') {
            $event_endpoint = 'action:' . $action_key;
        }

        $meta_payload['action_key'] = $action_key;
        $meta_payload['user_id'] = get_current_user_id();
        $meta_payload['billing_authority'] = $this->is_api_billed_action($action_key) ? 'api_wallet' : 'plugin_wallet';

        if ($this->is_api_billed_action($action_key)) {
            // New flows are billed by the API wallet. The plugin should not enqueue or push
            // local debit events for them, otherwise we risk drift and duplicate usage writes.
            return true;
        }

        $this->enqueue_usage_event($project_id, $event_endpoint, $cost_cents, $meta_payload);

        return (bool) $this->apply_credit_delta(
            $project_id,
            -abs($cost_cents),
            'usage',
            'wp_plugin_action',
            sanitize_text_field((string) $reference),
            null,
            $meta_payload
        );
    }

    public function log_endpoint_usage($project_id, $endpoint, $usage = array(), $action_key = '', $reference = '', $billed_cost_cents = 0, $meta = array()) {
        global $wpdb;

        $project_id = intval($project_id);
        $endpoint = sanitize_text_field((string) $endpoint);
        if ($project_id <= 0 || $endpoint === '' || !is_array($usage) || empty($usage)) {
            return false;
        }

        $usage_payload = $usage;
        $provider = sanitize_key((string) ($usage_payload['provider'] ?? ''));
        $model = sanitize_text_field((string) ($usage_payload['model'] ?? $usage_payload['model_name'] ?? ''));

        $input_tokens = max(0, intval(
            $usage_payload['input_tokens']
            ?? $usage_payload['prompt_tokens']
            ?? 0
        ));
        $output_tokens = max(0, intval(
            $usage_payload['output_tokens']
            ?? $usage_payload['completion_tokens']
            ?? 0
        ));
        $total_tokens = max(0, intval(
            $usage_payload['total_tokens']
            ?? ($input_tokens + $output_tokens)
        ));

        $provider_cost_usd = 0.0;
        if (isset($usage_payload['provider_cost_usd'])) {
            $provider_cost_usd = (float) $usage_payload['provider_cost_usd'];
        } elseif (isset($usage_payload['ai_cost_usd'])) {
            $provider_cost_usd = (float) $usage_payload['ai_cost_usd'];
        } elseif (isset($usage_payload['cost_usd'])) {
            $provider_cost_usd = (float) $usage_payload['cost_usd'];
        } elseif (isset($usage_payload['provider_cost_cents'])) {
            $provider_cost_usd = ((float) $usage_payload['provider_cost_cents']) / 100.0;
        }
        if ($provider_cost_usd < 0) {
            $provider_cost_usd = 0.0;
        }

        $provider_cost_cents = max(0, intval(round($provider_cost_usd * 100)));
        $provider_cost_milli_cents = max(0, intval(round($provider_cost_usd * 100000)));
        $billed_cost_cents = max(0, intval($billed_cost_cents));
        $action_key = sanitize_key((string) $action_key);
        $reference = sanitize_text_field((string) $reference);
        $meta_payload = is_array($meta) ? $meta : array();

        $inserted = $wpdb->insert(
            $this->endpoint_usage_table(),
            array(
                'project_id' => $project_id,
                'action_key' => $action_key !== '' ? $action_key : null,
                'endpoint' => $endpoint,
                'reference' => $reference !== '' ? $reference : null,
                'billed_cost_cents' => $billed_cost_cents,
                'provider_cost_cents' => $provider_cost_cents,
                'provider_cost_milli_cents' => $provider_cost_milli_cents,
                'provider_cost_usd' => round($provider_cost_usd, 6),
                'provider' => $provider !== '' ? $provider : null,
                'model' => $model !== '' ? $model : null,
                'input_tokens' => $input_tokens,
                'output_tokens' => $output_tokens,
                'total_tokens' => $total_tokens,
                'raw_usage_json' => wp_json_encode($usage_payload),
                'meta' => !empty($meta_payload) ? wp_json_encode($meta_payload) : null,
                'created_at' => $this->now(),
            ),
            array('%d', '%s', '%s', '%s', '%d', '%d', '%d', '%f', '%s', '%s', '%d', '%d', '%d', '%s', '%s', '%s')
        );

        return $inserted !== false;
    }

    public function is_api_billed_action($action_key) {
        $action_key = sanitize_key((string) $action_key);
        if ($action_key === '') {
            return false;
        }

        $config = $this->get_action_cost_config($action_key);
        if (is_array($config)) {
            $authority = sanitize_key((string) ($config['authority'] ?? ''));
            if ($authority === 'api_wallet' || $authority === 'display_only') {
                return true;
            }
            $mode = sanitize_key((string) ($config['billing_mode'] ?? ''));
            if ($mode === 'api_billed' || $mode === 'display_only') {
                return true;
            }
            $canonicalMode = sanitize_key((string) ($config['canonical_billing_mode'] ?? ''));
            if (in_array($canonicalMode, array('fixed', 'provider_usage', 'hybrid'), true) && (($config['canonical_billable'] ?? 'no') === 'yes')) {
                return true;
            }
        }

        return in_array($action_key, array(
            'aysa_keyword_mapping_v1_workspace',
            'aysa_keyword_mapping_v1_save_selection',
            'aysa_missing_pages_v1_workspace',
            'aysa_content_strategy_v1_workspace',
            'aysa_content_strategy_generate',
            'aysa_content_strategy_associate_missing',
            'aysa_plan_content_schedule',
            'aysa_run_content_planner_now',
            'aysa_create_post_from_strategy',
            'aysa_missing_pages_send_to_plan',
            'aysa_missing_pages_refactor_titles',
        ), true);
    }

    private function apply_cost_adjustments_to_csv($path, $adjustments) {
        if (!is_string($path) || $path === '' || !file_exists($path) || !is_array($adjustments) || empty($adjustments)) {
            return false;
        }

        $lookup = array();
        foreach ($adjustments as $item) {
            if (!is_array($item)) {
                continue;
            }
            $action = isset($item['action_key']) ? sanitize_key((string) $item['action_key']) : '';
            if ($action === '') {
                continue;
            }
            $effective = isset($item['effective_cost_cents']) ? max(0, intval($item['effective_cost_cents'])) : 0;
            $real = isset($item['real_cost_cents']) ? max(0, intval($item['real_cost_cents'])) : 0;
            $lookup[$action] = array(
                'effective' => $effective,
                'real' => $real,
            );
        }
        if (empty($lookup)) {
            return false;
        }

        $fh = fopen($path, 'r');
        if ($fh === false) {
            return false;
        }

        $header = fgetcsv($fh);
        if (!is_array($header) || empty($header)) {
            fclose($fh);
            return false;
        }
        $map = array();
        foreach ($header as $idx => $name) {
            $map[strtolower(trim((string) $name))] = intval($idx);
        }
        if (!isset($map['action_key']) || !isset($map['cost_cents'])) {
            fclose($fh);
            return false;
        }

        $rows = array();
        while (($line = fgetcsv($fh)) !== false) {
            if (!is_array($line)) {
                continue;
            }
            $action = isset($line[$map['action_key']]) ? sanitize_key((string) $line[$map['action_key']]) : '';
            if ($action !== '' && isset($lookup[$action])) {
                $line[$map['cost_cents']] = (string) $lookup[$action]['effective'];
                if (isset($map['real_cost_cents'])) {
                    $line[$map['real_cost_cents']] = (string) $lookup[$action]['real'];
                }
            }
            $rows[] = $line;
        }
        fclose($fh);

        $fw = fopen($path, 'w');
        if ($fw === false) {
            return false;
        }
        fputcsv($fw, $header);
        foreach ($rows as $line) {
            fputcsv($fw, $line);
        }
        fclose($fw);
        return true;
    }

    public function sync_action_cost_registry_from_csv($force = false) {
        if (!$this->has_remote_auth()) {
            return false;
        }

        $path = $this->get_action_cost_csv_path();
        if (!file_exists($path)) {
            return false;
        }

        $hash = hash_file('sha256', $path);
        if (!is_string($hash) || $hash === '') {
            return false;
        }

        $last_hash = (string) get_option($this->get_action_cost_last_hash_option(), '');
        $last_sync = intval(get_option($this->get_action_cost_last_sync_option(), 0));
        $interval = $this->get_action_cost_sync_interval_seconds();

        if (!$force && $last_hash === $hash && $last_sync > 0 && (time() - $last_sync) < $interval) {
            $this->refresh_action_cost_registry_projection(false);
            return true;
        }

        $rows = $this->parse_action_cost_csv($path);
        if (empty($rows)) {
            return false;
        }

        $endpoint = $this->get_remote_action_cost_sync_endpoint();
        if ($endpoint === '') {
            return false;
        }

        $response = $this->auth_request($endpoint, 'POST', array(
            'site_host' => $this->get_site_host_for_registry(),
            'source_csv' => basename($path),
            'csv_hash' => $hash,
            'min_multiplier' => 5,
            'rows' => $rows,
        ));

        if (!is_array($response) || empty($response['ok'])) {
            return false;
        }

        if (!empty($response['adjustments']) && is_array($response['adjustments'])) {
            $this->apply_cost_adjustments_to_csv($path, $response['adjustments']);
        }

        update_option($this->get_action_cost_last_hash_option(), $hash, false);
        update_option($this->get_action_cost_last_sync_option(), time(), false);
        $this->refresh_action_cost_registry_projection(true);

        return true;
    }

    public function refresh_action_cost_registry_projection($force = false) {
        if (!$this->has_remote_auth()) {
            return false;
        }

        $lastSync = intval(get_option($this->get_action_cost_projection_last_sync_option(), 0));
        $interval = $this->get_action_cost_sync_interval_seconds();
        if (!$force && $lastSync > 0 && (time() - $lastSync) < $interval) {
            return true;
        }

        $endpoint = $this->get_remote_action_cost_audit_endpoint();
        if ($endpoint === '') {
            return false;
        }

        $response = $this->auth_request($endpoint, 'POST', array(
            'site_host' => $this->get_site_host_for_registry(),
        ));
        if (!is_array($response) || empty($response['ok']) || empty($response['items']) || !is_array($response['items'])) {
            return false;
        }

        $projection = array();
        foreach ($response['items'] as $item) {
            if (!is_array($item)) {
                continue;
            }
            $actionKey = sanitize_key((string) ($item['action_key'] ?? ''));
            if ($actionKey === '') {
                continue;
            }
            $projection[$actionKey] = $item;
        }

        update_option($this->get_action_cost_projection_option(), $projection, false);
        update_option($this->get_action_cost_projection_last_sync_option(), time(), false);
        return true;
    }

    public function ensure_wallet($project_id, $plan_code = null) {
        global $wpdb;

        $project_id = intval($project_id);
        if ($project_id <= 0) {
            return null;
        }

        $table = $this->wallets_table();
        $wallet = $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM {$table} WHERE project_id = %d LIMIT 1",
            $project_id
        ), ARRAY_A);

        if (is_array($wallet) && !empty($wallet)) {
            return $wallet;
        }

        $plan_code = $this->normalize_plan_code($plan_code ?: $this->get_default_plan_code());
        $plan = $this->plans[$plan_code];

        $initial_balance = intval($plan['monthly_credits_cents']);

        $now = $this->now();

        $inserted = $wpdb->insert(
            $table,
            array(
                'project_id' => $project_id,
                'plan_code' => $plan_code,
                'plan_price_eur' => intval($plan['price_eur']),
                'monthly_credits_cents' => intval($plan['monthly_credits_cents']),
                'balance_cents' => $initial_balance,
                'included_links' => intval($plan['included_links']),
                'included_links_used' => 0,
                'included_links_period_starts_at' => $this->default_period_start(),
                'included_links_period_ends_at' => $this->default_period_end(),
                'reserved_links_cents' => intval($plan['reserved_links_cents']),
                'recommended_keywords' => intval($plan['recommended_keywords']),
                'is_active' => 1,
                'created_at' => $now,
                'updated_at' => $now,
            ),
            array('%d', '%s', '%d', '%d', '%d', '%d', '%d', '%s', '%s', '%d', '%d', '%s', '%s')
        );

        if ($inserted === false) {
            return null;
        }

        $wallet = $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM {$table} WHERE project_id = %d LIMIT 1",
            $project_id
        ), ARRAY_A);

        if (is_array($wallet) && !empty($wallet)) {
            $this->add_ledger_entry(
                $project_id,
                intval($wallet['id']),
                'wallet_seed',
                $initial_balance,
                'subscription_seed',
                'Initial wallet seed from plan',
                null,
                array('plan_code' => $plan_code)
            );
        }

        return $wallet;
    }

    public function apply_plan_to_project($project_id, $plan_code, $reset_balance = false) {
        global $wpdb;

        $project_id = intval($project_id);
        if ($project_id <= 0) {
            return false;
        }

        $plan_code = $this->normalize_plan_code($plan_code);
        $plan = $this->plans[$plan_code];
        $wallet = $this->ensure_wallet($project_id, $plan_code);
        if (!$wallet) {
            return false;
        }

        $data = array(
            'plan_code' => $plan_code,
            'plan_price_eur' => intval($plan['price_eur']),
            'monthly_credits_cents' => intval($plan['monthly_credits_cents']),
            'included_links' => intval($plan['included_links']),
            'included_links_used' => 0,
            'included_links_period_starts_at' => $this->default_period_start(),
            'included_links_period_ends_at' => $this->default_period_end(),
            'reserved_links_cents' => intval($plan['reserved_links_cents']),
            'recommended_keywords' => intval($plan['recommended_keywords']),
            'updated_at' => $this->now(),
        );
        $formats = array('%s', '%d', '%d', '%d', '%d', '%s', '%s', '%d', '%d', '%s');

        if ($reset_balance) {
            $data['balance_cents'] = intval($plan['monthly_credits_cents']);
            $formats[] = '%d';
        }

        $updated = $wpdb->update(
            $this->wallets_table(),
            $data,
            array('id' => intval($wallet['id'])),
            $formats,
            array('%d')
        );

        if ($updated === false) {
            return false;
        }

        if ($reset_balance) {
            $this->add_ledger_entry(
                $project_id,
                intval($wallet['id']),
                'plan_reset',
                intval($plan['monthly_credits_cents']),
                'access_key',
                'Wallet reset to plan baseline',
                null,
                array('plan_code' => $plan_code)
            );
        }

        return true;
    }

    public function get_wallet_snapshot($project_id, $args = array()) {
        global $wpdb;

        $project_id = intval($project_id);
        $force_remote = !empty($args['force_remote']);
        $allow_remote = !isset($args['allow_remote']) || !empty($args['allow_remote']);
        if ($project_id > 0 && $allow_remote) {
            $this->sync_project_with_remote($project_id, $force_remote);
        }

        $wallet = $this->refresh_included_links_period($project_id, $this->ensure_wallet($project_id));
        $latest_usage = $this->get_latest_usage_entry($project_id);
        if (!$wallet) {
            return array(
                'project_id' => intval($project_id),
                'balance_cents' => 0,
                'spendable_cents' => 0,
                'reserved_links_cents' => 0,
                'plan_code' => null,
                'plan_label' => null,
                'included_links' => 0,
                'included_links_total' => 0,
                'included_links_used' => 0,
                'included_links_remaining' => 0,
                'included_links_period_starts_at' => null,
                'included_links_period_ends_at' => null,
                'recommended_keywords' => 0,
                'sot_source' => 'local',
                'last_sync_ts' => intval(get_option($this->last_sync_option_key($project_id), 0)),
                'latest_usage_amount_cents' => 0,
                'latest_usage_action_key' => '',
                'latest_usage_reference' => '',
                'latest_usage_at' => '',
            );
        }

        $plan_code = (string) ($wallet['plan_code'] ?? 'starter_29');
        $plan = isset($this->plans[$plan_code]) ? $this->plans[$plan_code] : $this->plans['starter_29'];

        $balance_cents = intval($wallet['balance_cents']);
        $reserved_links_cents = intval($wallet['reserved_links_cents']);
        $spendable_cents = max(0, $balance_cents - $reserved_links_cents);
        $included_links_total = $this->sanitize_included_links_total($wallet['included_links'] ?? 0, $plan);
        $included_links_used = $this->sanitize_included_links_used($wallet['included_links_used'] ?? 0, $included_links_total);
        $included_links_remaining = max(0, $included_links_total - $included_links_used);

        $pending_orders = intval($wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$this->orders_table()} WHERE project_id = %d AND status = 'pending'",
            intval($project_id)
        )));

        return array(
            'project_id' => intval($project_id),
            'wallet_id' => intval($wallet['id']),
            'plan_code' => $plan_code,
            'plan_label' => (string) $plan['label'],
            'plan_price_eur' => intval($wallet['plan_price_eur']),
            'monthly_credits_cents' => intval($wallet['monthly_credits_cents']),
            'balance_cents' => $balance_cents,
            'spendable_cents' => $spendable_cents,
            'reserved_links_cents' => $reserved_links_cents,
            'included_links' => $included_links_total,
            'included_links_total' => $included_links_total,
            'included_links_used' => $included_links_used,
            'included_links_remaining' => $included_links_remaining,
            'included_links_period_starts_at' => $wallet['included_links_period_starts_at'] ?? null,
            'included_links_period_ends_at' => $wallet['included_links_period_ends_at'] ?? null,
            'recommended_keywords' => intval($wallet['recommended_keywords']),
            'pending_topup_orders' => $pending_orders,
            'currency' => 'EUR',
            'keywords_recommendation_only' => true,
            'sot_source' => 'endpoint',
            'last_sync_ts' => intval(get_option($this->last_sync_option_key($project_id), 0)),
            'latest_usage_amount_cents' => intval($latest_usage['amount_cents'] ?? 0),
            'latest_usage_action_key' => sanitize_key((string) ($latest_usage['action_key'] ?? '')),
            'latest_usage_reference' => sanitize_text_field((string) ($latest_usage['reference'] ?? '')),
            'latest_usage_at' => sanitize_text_field((string) ($latest_usage['created_at'] ?? '')),
        );
    }

    private function get_latest_usage_entry($project_id) {
        global $wpdb;

        $project_id = intval($project_id);
        if ($project_id <= 0) {
            return array();
        }

        $row = $wpdb->get_row($wpdb->prepare(
            "SELECT amount_cents, reference, created_at, meta
             FROM {$this->ledger_table()}
             WHERE project_id = %d AND entry_type = 'usage'
             ORDER BY id DESC
             LIMIT 1",
            $project_id
        ), ARRAY_A);

        if (!is_array($row) || empty($row)) {
            return array();
        }

        $meta = array();
        if (!empty($row['meta'])) {
            $decoded = json_decode((string) $row['meta'], true);
            if (is_array($decoded)) {
                $meta = $decoded;
            }
        }

        return array(
            'amount_cents' => intval($row['amount_cents'] ?? 0),
            'reference' => (string) ($row['reference'] ?? ''),
            'created_at' => (string) ($row['created_at'] ?? ''),
            'action_key' => (string) ($meta['action_key'] ?? ''),
        );
    }

    private function normalize_remote_snapshot($project_id, $remote) {
        if (!is_array($remote)) {
            return null;
        }
        $data = isset($remote['data']) && is_array($remote['data']) ? $remote['data'] : $remote;

        $plan_code = (string) ($data['plan_code'] ?? $data['planCode'] ?? $this->get_default_plan_code());
        $plan_code = $this->normalize_plan_code($plan_code);
        $plan = $this->plans[$plan_code];

        $normalized = array(
            'project_id' => intval($data['project_id'] ?? $data['projectId'] ?? $project_id),
            'plan_code' => $plan_code,
            'plan_price_eur' => intval($data['plan_price_eur'] ?? $data['planPriceEur'] ?? $plan['price_eur']),
            'monthly_credits_cents' => intval($data['monthly_credits_cents'] ?? $data['monthlyCreditsCents'] ?? $plan['monthly_credits_cents']),
            'balance_cents' => intval($data['balance_cents'] ?? $data['balanceCents'] ?? 0),
            'included_links' => $this->sanitize_included_links_total($data['included_links'] ?? $data['includedLinks'] ?? $plan['included_links'], $plan),
            'included_links_used' => 0,
            'included_links_period_starts_at' => $this->normalize_period_value($data['included_links_period_starts_at'] ?? $data['includedLinksPeriodStartsAt'] ?? ''),
            'included_links_period_ends_at' => $this->normalize_period_value($data['included_links_period_ends_at'] ?? $data['includedLinksPeriodEndsAt'] ?? $data['current_period_ends_at'] ?? ''),
            'reserved_links_cents' => intval($data['reserved_links_cents'] ?? $data['reservedLinksCents'] ?? $plan['reserved_links_cents']),
            'recommended_keywords' => intval($data['recommended_keywords'] ?? $data['recommendedKeywords'] ?? $plan['recommended_keywords']),
        );

        $normalized['included_links_used'] = $this->sanitize_included_links_used(
            $data['included_links_used'] ?? $data['includedLinksUsed'] ?? 0,
            $normalized['included_links']
        );

        if ($normalized['project_id'] <= 0 || $normalized['balance_cents'] < 0) {
            return null;
        }

        return $normalized;
    }

    private function apply_remote_snapshot($project_id, $normalized) {
        global $wpdb;

        if (!is_array($normalized) || intval($normalized['project_id']) <= 0) {
            return false;
        }

        $wallet = $this->ensure_wallet($project_id, $normalized['plan_code']);
        if (!$wallet) {
            return false;
        }

        $wallet_id = intval($wallet['id']);
        $current_period_end = $this->normalize_period_value($wallet['included_links_period_ends_at'] ?? '');
        $incoming_period_end = $this->normalize_period_value($normalized['included_links_period_ends_at'] ?? '');
        $period_changed = $incoming_period_end !== null && $incoming_period_end !== $current_period_end;
        $included_links_used = $period_changed
            ? 0
            : intval($wallet['included_links_used'] ?? $normalized['included_links_used'] ?? 0);
        if ($incoming_period_end === null) {
            $incoming_period_end = $current_period_end ?: $this->default_period_end();
        }
        $incoming_period_start = $this->normalize_period_value($normalized['included_links_period_starts_at'] ?? '') ?: ($period_changed ? $this->default_period_start() : ($wallet['included_links_period_starts_at'] ?? $this->default_period_start()));

        $updated = $wpdb->update(
            $this->wallets_table(),
            array(
                'plan_code' => (string) $normalized['plan_code'],
                'plan_price_eur' => intval($normalized['plan_price_eur']),
                'monthly_credits_cents' => intval($normalized['monthly_credits_cents']),
                'balance_cents' => intval($normalized['balance_cents']),
                'included_links' => intval($normalized['included_links']),
                'included_links_used' => max(0, $included_links_used),
                'included_links_period_starts_at' => $incoming_period_start,
                'included_links_period_ends_at' => $incoming_period_end,
                'reserved_links_cents' => intval($normalized['reserved_links_cents']),
                'recommended_keywords' => intval($normalized['recommended_keywords']),
                'updated_at' => $this->now(),
            ),
            array('id' => $wallet_id),
            array('%s', '%d', '%d', '%d', '%d', '%d', '%s', '%s', '%d', '%d', '%s'),
            array('%d')
        );

        return $updated !== false;
    }

    public function sync_project_with_remote($project_id, $force = false) {
        $project_id = intval($project_id);
        if ($project_id <= 0) {
            return false;
        }
        if (!$this->has_remote_auth()) {
            return false;
        }

        $lock_key = $this->sync_lock_key($project_id);
        if (get_transient($lock_key)) {
            return false;
        }

        $ttl = $this->get_sync_ttl_seconds();
        $last_sync = intval(get_option($this->last_sync_option_key($project_id), 0));
        if (!$force && $last_sync > 0 && (time() - $last_sync) < $ttl) {
            return false;
        }

        set_transient($lock_key, 1, 45);

        try {
            $this->flush_usage_queue($project_id);

            $endpoint = $this->get_remote_snapshot_endpoint();
            if ($endpoint === '') {
                return false;
            }
            $sep = (strpos($endpoint, '?') !== false) ? '&' : '?';
            $response = $this->auth_request($endpoint . $sep . 'project_id=' . intval($project_id), 'GET');
            $normalized = $this->normalize_remote_snapshot($project_id, $response);
            if (!$normalized) {
                return false;
            }

            $ok = $this->apply_remote_snapshot($project_id, $normalized);
            if ($ok) {
                update_option($this->last_sync_option_key($project_id), time(), false);
                return true;
            }
            return false;
        } finally {
            delete_transient($lock_key);
        }
    }

    private function get_usage_queue($project_id) {
        $raw = get_option($this->usage_queue_key($project_id), array());
        if (!is_array($raw)) {
            return array();
        }
        return array_values($raw);
    }

    private function set_usage_queue($project_id, $queue) {
        if (!is_array($queue)) {
            $queue = array();
        }
        update_option($this->usage_queue_key($project_id), array_values($queue), false);
    }

    public function enqueue_usage_event($project_id, $endpoint, $cost_cents, $meta = array(), $ts = null) {
        $project_id = intval($project_id);
        $cost_cents = intval($cost_cents);
        if ($project_id <= 0 || $cost_cents <= 0) {
            return false;
        }

        $endpoint = sanitize_text_field((string) $endpoint);
        $queue = $this->get_usage_queue($project_id);
        $queue[] = array(
            'endpoint' => $endpoint,
            'cost_cents' => $cost_cents,
            'meta' => is_array($meta) ? $meta : array(),
            'ts' => $ts ? intval($ts) : time(),
        );

        if (count($queue) > 500) {
            $queue = array_slice($queue, -500);
        }
        $this->set_usage_queue($project_id, $queue);

        return true;
    }

    private function remove_usage_events($project_id, $events) {
        $project_id = intval($project_id);
        if ($project_id <= 0 || !is_array($events) || empty($events)) {
            return;
        }

        $queue = $this->get_usage_queue($project_id);
        if (empty($queue)) {
            return;
        }

        $remaining = array_values($queue);
        foreach ($events as $event) {
            $endpoint = sanitize_text_field((string) ($event['endpoint'] ?? ''));
            $cost_cents = intval($event['cost_cents'] ?? 0);
            $ts = intval($event['ts'] ?? 0);
            foreach ($remaining as $index => $queued) {
                if (
                    sanitize_text_field((string) ($queued['endpoint'] ?? '')) === $endpoint
                    && intval($queued['cost_cents'] ?? 0) === $cost_cents
                    && intval($queued['ts'] ?? 0) === $ts
                ) {
                    unset($remaining[$index]);
                    break;
                }
            }
        }

        $this->set_usage_queue($project_id, array_values($remaining));
    }

    public function push_usage_events_now($project_id, $events) {
        $project_id = intval($project_id);
        if ($project_id <= 0 || !is_array($events) || empty($events)) {
            return false;
        }
        if (!$this->has_remote_auth() && !$this->get_plugin_bearer_token()) {
            return false;
        }

        $endpoint = $this->get_remote_usage_batch_endpoint();
        if ($endpoint === '') {
            return false;
        }

        $payloadEvents = array();
        foreach ($events as $event) {
            $payloadEvents[] = array(
                'endpoint' => sanitize_text_field((string) ($event['endpoint'] ?? '')),
                'cost_cents' => intval($event['cost_cents'] ?? 0),
                'meta' => isset($event['meta']) && is_array($event['meta']) ? $event['meta'] : array(),
                'ts' => intval($event['ts'] ?? time()),
            );
        }

        $response = $this->auth_request($endpoint, 'POST', array(
            'project_id' => $project_id,
            'events' => $payloadEvents,
        ));
        if ($response === false) {
            return false;
        }

        $normalized = $this->normalize_remote_snapshot($project_id, $response);
        if ($normalized) {
            $this->apply_remote_snapshot($project_id, $normalized);
            update_option($this->last_sync_option_key($project_id), time(), false);
        }

        $this->remove_usage_events($project_id, $payloadEvents);
        return true;
    }

    public function flush_usage_queue($project_id) {
        $project_id = intval($project_id);
        if ($project_id <= 0) {
            return false;
        }
        $queue = $this->get_usage_queue($project_id);
        if (empty($queue)) {
            return true;
        }
        if (!$this->has_remote_auth()) {
            return false;
        }

        $endpoint = $this->get_remote_usage_batch_endpoint();
        if ($endpoint === '') {
            return false;
        }

        $batch_size = $this->get_usage_batch_size();
        $remaining = array_values($queue);
        $processed_any = false;

        while (!empty($remaining)) {
            $batch = array_slice($remaining, 0, $batch_size);
            $pushed = $this->push_usage_events_now($project_id, $batch);
            if (!$pushed) {
                $this->set_usage_queue($project_id, $remaining);
                return $processed_any;
            }

            $processed_any = true;
            $remaining = array_slice($remaining, count($batch));
            $this->set_usage_queue($project_id, $remaining);
        }

        return true;
    }

    public function register_cron_schedules($schedules) {
        if (!isset($schedules['aysa_wallet_10min'])) {
            $schedules['aysa_wallet_10min'] = array(
                'interval' => 10 * 60,
                'display' => __('Every 10 Minutes (AYSA Wallet)', 'aysa'),
            );
        }
        return $schedules;
    }

    public function ensure_cron_scheduled() {
        if (!wp_next_scheduled(self::CRON_HOOK)) {
            wp_schedule_event(time() + 120, 'aysa_wallet_10min', self::CRON_HOOK);
        }
    }

    public function handle_cron_sync() {
        $project_id = intval(get_option('aysa_project_id', 0));
        if ($project_id <= 0) {
            // Even without a resolved project we keep API-side action cost registry fresh.
            $this->sync_action_cost_registry_from_csv(false);
            return;
        }
        $this->sync_project_with_remote($project_id, false);
        $this->sync_action_cost_registry_from_csv(false);
    }

    public function add_ledger_entry($project_id, $wallet_id, $entry_type, $amount_cents, $source = 'plugin', $reference = '', $order_id = null, $meta = array()) {
        global $wpdb;

        $project_id = intval($project_id);
        $wallet_id = intval($wallet_id);
        $amount_cents = intval($amount_cents);
        $entry_type = sanitize_key((string) $entry_type);
        $source = sanitize_key((string) $source);
        $reference = sanitize_text_field((string) $reference);
        $order_id = is_null($order_id) ? null : intval($order_id);

        if ($project_id <= 0 || $wallet_id <= 0 || $entry_type === '' || $amount_cents === 0) {
            return false;
        }

        return $wpdb->insert(
            $this->ledger_table(),
            array(
                'project_id' => $project_id,
                'wallet_id' => $wallet_id,
                'order_id' => $order_id,
                'entry_type' => $entry_type,
                'amount_cents' => $amount_cents,
                'source' => $source,
                'reference' => $reference !== '' ? $reference : null,
                'meta' => !empty($meta) ? wp_json_encode($meta) : null,
                'created_by' => get_current_user_id() ?: null,
                'created_at' => $this->now(),
            ),
            array('%d', '%d', '%d', '%s', '%d', '%s', '%s', '%s', '%d', '%s')
        ) !== false;
    }

    public function apply_credit_delta($project_id, $delta_cents, $entry_type, $source = 'plugin', $reference = '', $order_id = null, $meta = array()) {
        global $wpdb;

        $project_id = intval($project_id);
        $delta_cents = intval($delta_cents);
        if ($project_id <= 0 || $delta_cents === 0) {
            return false;
        }

        $wallet = $this->ensure_wallet($project_id);
        if (!$wallet) {
            return false;
        }

        $wallet_id = intval($wallet['id']);
        $new_balance = intval($wallet['balance_cents']) + $delta_cents;
        if ($new_balance < 0) {
            return false;
        }

        $updated = $wpdb->update(
            $this->wallets_table(),
            array(
                'balance_cents' => $new_balance,
                'updated_at' => $this->now(),
            ),
            array('id' => $wallet_id),
            array('%d', '%s'),
            array('%d')
        );

        if ($updated === false) {
            return false;
        }

        $this->add_ledger_entry($project_id, $wallet_id, $entry_type, $delta_cents, $source, $reference, $order_id, $meta);

        return true;
    }

    private function build_payment_link($order_id, $project_id, $amount_cents) {
        $base = trim((string) get_option('aysa_wallet_payment_base_url', 'https://app.aysa.ai/pay'));
        if ($base === '') {
            $base = 'https://app.aysa.ai/pay';
        }
        $args = array(
            'order_id' => intval($order_id),
            'project_id' => intval($project_id),
            'amount_cents' => intval($amount_cents),
            'source' => 'wp_plugin',
        );
        return add_query_arg($args, $base);
    }

    private function notify_admin_new_order($order) {
        $admin_email = get_option('admin_email');
        if (!is_email($admin_email)) {
            return;
        }

        $subject = sprintf('[AYSA] Credit Top-up Order #%d', intval($order['id']));
        $message = "A new credit top-up order was created.\n\n";
        $message .= 'Order ID: ' . intval($order['id']) . "\n";
        $message .= 'Project ID: ' . intval($order['project_id']) . "\n";
        $message .= 'Credits requested (cents): ' . intval($order['credits_cents']) . "\n";
        $message .= 'Amount: ' . number_format(intval($order['amount_cents']) / 100, 2) . ' EUR' . "\n";
        $message .= 'Payment link: ' . (string) $order['payment_link'] . "\n";
        $message .= 'Status: ' . (string) $order['status'] . "\n";

        wp_mail($admin_email, $subject, $message);
    }

    public function create_topup_order($project_id, $credits_cents, $requested_by = null) {
        global $wpdb;

        $project_id = intval($project_id);
        $credits_cents = intval($credits_cents);
        $requested_by = is_null($requested_by) ? get_current_user_id() : intval($requested_by);

        if ($project_id <= 0 || $credits_cents <= 0) {
            return new WP_Error('invalid_request', 'Invalid project or credits amount.');
        }

        $wallet = $this->ensure_wallet($project_id);
        if (!$wallet) {
            return new WP_Error('wallet_missing', 'Unable to initialize wallet for this project.');
        }

        // Endpoint is source of truth: attempt remote order first.
        $remote_order = false;
        if ($this->has_remote_auth()) {
            $remote_endpoint = $this->get_remote_order_endpoint();
            if ($remote_endpoint !== '') {
                $remote_order = $this->auth_request($remote_endpoint, 'POST', array(
                    'project_id' => $project_id,
                    'aysa_user_id' => $this->get_remote_aysa_user_id(),
                    'credits_cents' => $credits_cents,
                    'amount_cents' => $credits_cents,
                    'currency' => 'EUR',
                    'source' => 'wp_plugin',
                ));
            }
        }
        $remote_data_probe = (is_array($remote_order) && isset($remote_order['data']) && is_array($remote_order['data']))
            ? $remote_order['data']
            : (is_array($remote_order) ? $remote_order : array());
        if (
            $remote_order === false
            || !is_array($remote_order)
            || empty($remote_data_probe['payment_link'])
        ) {
            $remote_order = $this->request_app_aysa_topup_checkout($project_id, $credits_cents);
        }

        $now = $this->now();

        $remote_data = (is_array($remote_order) && isset($remote_order['data']) && is_array($remote_order['data']))
            ? $remote_order['data']
            : (is_array($remote_order) ? $remote_order : array());
        $remote_payment_link = isset($remote_data['payment_link']) ? (string) $remote_data['payment_link'] : '';
        $remote_external_id = isset($remote_data['order_id']) ? sanitize_text_field((string) $remote_data['order_id']) : '';
        $remote_status = isset($remote_data['status']) ? sanitize_key((string) $remote_data['status']) : 'pending';
        if ($remote_status === '') {
            $remote_status = 'pending';
        }

        $ok = $wpdb->insert(
            $this->orders_table(),
            array(
                'project_id' => $project_id,
                'wallet_id' => intval($wallet['id']),
                'requester_user_id' => $requested_by > 0 ? $requested_by : null,
                'status' => $remote_status,
                'credits_cents' => $credits_cents,
                'amount_cents' => $credits_cents,
                'currency' => 'EUR',
                'payment_link' => $remote_payment_link !== '' ? $remote_payment_link : null,
                'external_payment_id' => $remote_external_id !== '' ? $remote_external_id : null,
                'admin_notes' => null,
                'paid_at' => null,
                'created_at' => $now,
                'updated_at' => $now,
            ),
            array('%d', '%d', '%d', '%s', '%d', '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s')
        );

        if ($ok === false) {
            return new WP_Error('db_insert_failed', 'Failed to create order.');
        }

        $order_id = intval($wpdb->insert_id);
        $payment_link = $remote_payment_link !== ''
            ? $remote_payment_link
            : $this->build_payment_link($order_id, $project_id, $credits_cents);

        $wpdb->update(
            $this->orders_table(),
            array(
                'payment_link' => $payment_link,
                'updated_at' => $this->now(),
            ),
            array('id' => $order_id),
            array('%s', '%s'),
            array('%d')
        );

        $order = $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM {$this->orders_table()} WHERE id = %d LIMIT 1",
            $order_id
        ), ARRAY_A);

        if (is_array($order)) {
            if (($order['status'] ?? '') === 'paid') {
                $this->apply_credit_delta(
                    $project_id,
                    $credits_cents,
                    'topup_paid',
                    'wallet_topup',
                    'Remote paid top-up order',
                    $order_id,
                    array('external_payment_id' => (string) ($order['external_payment_id'] ?? ''))
                );
            }
            $this->notify_admin_new_order($order);
            return $order;
        }

        return new WP_Error('order_load_failed', 'Order created but could not be loaded.');
    }

    public function create_supplement_order($project_id, $credits_cents, $requested_by = null, $context = array()) {
        global $wpdb;

        $project_id = intval($project_id);
        $credits_cents = intval($credits_cents);
        $requested_by = is_null($requested_by) ? get_current_user_id() : intval($requested_by);
        $context = is_array($context) ? $context : array();

        if ($project_id <= 0 || $credits_cents <= 0) {
            return new WP_Error('invalid_request', 'Invalid project or credits amount.');
        }

        $wallet = $this->ensure_wallet($project_id);
        if (!$wallet) {
            return new WP_Error('wallet_missing', 'Unable to initialize wallet for this project.');
        }

        $service_type = sanitize_text_field((string) ($context['service_type'] ?? ''));
        $portal_id = sanitize_text_field((string) ($context['portal_id'] ?? ''));
        $portal_domain = sanitize_text_field((string) ($context['portal_domain'] ?? ''));
        $order_context = sanitize_text_field((string) ($context['context'] ?? ''));
        $invoice_label = sanitize_text_field((string) ($context['invoice_label'] ?? ''));

        $remote_payload = array(
            'project_id' => $project_id,
            'aysa_user_id' => $this->get_remote_aysa_user_id(),
            'credits_cents' => $credits_cents,
            'amount_cents' => $credits_cents,
            'currency' => 'EUR',
            'source' => 'wp_plugin_offpage_supplement',
            'order_type' => 'offpage_supplement',
            'service_type' => $service_type,
            'portal_id' => $portal_id,
            'portal_domain' => $portal_domain,
            'context' => $order_context,
            'invoice_label' => $invoice_label,
        );

        $remote_order = false;
        if ($this->has_remote_auth()) {
            $remote_endpoint = $this->get_remote_order_endpoint();
            if ($remote_endpoint !== '') {
                $remote_order = $this->auth_request($remote_endpoint, 'POST', $remote_payload);
            }
        }
        $remote_data_probe = (is_array($remote_order) && isset($remote_order['data']) && is_array($remote_order['data']))
            ? $remote_order['data']
            : (is_array($remote_order) ? $remote_order : array());
        if (
            $remote_order === false
            || !is_array($remote_order)
            || empty($remote_data_probe['payment_link'])
        ) {
            $remote_order = $this->request_app_aysa_topup_checkout($project_id, $credits_cents, array(
                'order_type' => 'offpage_supplement',
                'source' => 'wp_plugin_offpage_supplement',
                'service_type' => $service_type,
                'portal_id' => $portal_id,
                'portal_domain' => $portal_domain,
                'context' => $order_context,
                'invoice_label' => $invoice_label,
            ));
        }

        $now = $this->now();
        $remote_data = (is_array($remote_order) && isset($remote_order['data']) && is_array($remote_order['data']))
            ? $remote_order['data']
            : (is_array($remote_order) ? $remote_order : array());
        $remote_payment_link = isset($remote_data['payment_link']) ? (string) $remote_data['payment_link'] : '';
        $remote_external_id = isset($remote_data['order_id']) ? sanitize_text_field((string) $remote_data['order_id']) : '';
        $remote_status = isset($remote_data['status']) ? sanitize_key((string) ($remote_data['status'] ?? 'pending')) : 'pending';
        if ($remote_status === '') {
            $remote_status = 'pending';
        }

        $ok = $wpdb->insert(
            $this->orders_table(),
            array(
                'project_id' => $project_id,
                'wallet_id' => intval($wallet['id']),
                'requester_user_id' => $requested_by > 0 ? $requested_by : null,
                'status' => $remote_status,
                'credits_cents' => $credits_cents,
                'amount_cents' => $credits_cents,
                'currency' => 'EUR',
                'payment_link' => $remote_payment_link !== '' ? $remote_payment_link : null,
                'external_payment_id' => $remote_external_id !== '' ? $remote_external_id : null,
                'admin_notes' => null,
                'paid_at' => null,
                'created_at' => $now,
                'updated_at' => $now,
            ),
            array('%d', '%d', '%d', '%s', '%d', '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s')
        );

        if ($ok === false) {
            return new WP_Error('db_insert_failed', 'Failed to create order.');
        }

        $order_id = intval($wpdb->insert_id);
        $payment_link = $remote_payment_link !== ''
            ? $remote_payment_link
            : $this->build_payment_link($order_id, $project_id, $credits_cents);

        $wpdb->update(
            $this->orders_table(),
            array(
                'payment_link' => $payment_link,
                'updated_at' => $this->now(),
            ),
            array('id' => $order_id),
            array('%s', '%s'),
            array('%d')
        );

        $order = $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM {$this->orders_table()} WHERE id = %d LIMIT 1",
            $order_id
        ), ARRAY_A);

        if (is_array($order)) {
            if (($order['status'] ?? '') === 'paid') {
                $this->apply_credit_delta(
                    $project_id,
                    $credits_cents,
                    'topup_paid',
                    'wallet_topup',
                    'Remote paid supplement order',
                    $order_id,
                    array('external_payment_id' => (string) ($order['external_payment_id'] ?? ''))
                );
            }
            $this->notify_admin_new_order($order);
            return $order;
        }

        return new WP_Error('order_load_failed', 'Order created but could not be loaded.');
    }

    public function mark_order_paid($order_id, $external_payment_id = '') {
        global $wpdb;

        $order_id = intval($order_id);
        if ($order_id <= 0) {
            return new WP_Error('invalid_order', 'Invalid order id.');
        }

        $order = $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM {$this->orders_table()} WHERE id = %d LIMIT 1",
            $order_id
        ), ARRAY_A);

        if (!$order) {
            return new WP_Error('order_not_found', 'Order not found.');
        }

        if ((string) $order['status'] === 'paid') {
            return $order;
        }

        $updated = $wpdb->update(
            $this->orders_table(),
            array(
                'status' => 'paid',
                'external_payment_id' => sanitize_text_field((string) $external_payment_id),
                'paid_at' => $this->now(),
                'updated_at' => $this->now(),
            ),
            array('id' => $order_id),
            array('%s', '%s', '%s', '%s'),
            array('%d')
        );

        if ($updated === false) {
            return new WP_Error('order_update_failed', 'Failed to mark order as paid.');
        }

        $project_id = intval($order['project_id']);
        $credits_cents = intval($order['credits_cents']);
        $this->apply_credit_delta(
            $project_id,
            $credits_cents,
            'topup_paid',
            'wallet_topup',
            'Paid top-up order #' . $order_id,
            $order_id,
            array('external_payment_id' => sanitize_text_field((string) $external_payment_id))
        );

        return $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM {$this->orders_table()} WHERE id = %d LIMIT 1",
            $order_id
        ), ARRAY_A);
    }

    public function get_orders($project_id, $limit = 20) {
        global $wpdb;

        $project_id = intval($project_id);
        $limit = max(1, min(100, intval($limit)));

        if ($project_id <= 0) {
            return array();
        }

        return $wpdb->get_results($wpdb->prepare(
            "SELECT id, status, credits_cents, amount_cents, currency, payment_link, paid_at, created_at, updated_at
             FROM {$this->orders_table()}
             WHERE project_id = %d
             ORDER BY id DESC
             LIMIT %d",
            $project_id,
            $limit
        ), ARRAY_A);
    }

    public function ajax_get_snapshot() {
        if (!current_user_can('manage_options')) {
            wp_send_json_error(array('message' => 'Insufficient permissions'), 403);
            return;
        }

        $nonce = isset($_POST['nonce']) ? sanitize_text_field(wp_unslash($_POST['nonce'])) : '';
        if (!wp_verify_nonce($nonce, 'aysa_credits_nonce')) {
            wp_send_json_error(array('message' => 'Security check failed'), 403);
            return;
        }

        $project_id = $this->get_project_id();
        if ($project_id <= 0) {
            wp_send_json_success(array(
                'project_id' => 0,
                'credits' => 0,
                'balance_cents' => 0,
                'spendable_cents' => 0,
                'reserved_links_cents' => 0,
                'included_links' => 0,
                'recommended_keywords' => 0,
            ));
            return;
        }

        $force_remote = !empty($_POST['force_remote']);
        $snapshot = $this->get_wallet_snapshot($project_id, array('force_remote' => $force_remote));
        $snapshot['queued_usage_events'] = count($this->get_usage_queue($project_id));
        $snapshot['credits'] = intval($snapshot['balance_cents']);
        wp_send_json_success($snapshot);
    }

    public function ajax_create_order() {
        if (!current_user_can('manage_options')) {
            wp_send_json_error(array('message' => 'Insufficient permissions'), 403);
            return;
        }

        $nonce = isset($_POST['nonce']) ? sanitize_text_field(wp_unslash($_POST['nonce'])) : '';
        if (!wp_verify_nonce($nonce, 'aysa_credits_nonce')) {
            wp_send_json_error(array('message' => 'Security check failed'), 403);
            return;
        }

        $project_id = isset($_POST['project_id']) ? intval(wp_unslash($_POST['project_id'])) : $this->get_project_id();
        $credits_cents = isset($_POST['credits_cents']) ? intval(wp_unslash($_POST['credits_cents'])) : 0;

        if ($project_id <= 0) {
            wp_send_json_error(array('message' => 'Project ID missing. Complete setup first.'), 400);
            return;
        }

        if ($credits_cents <= 0) {
            wp_send_json_error(array('message' => 'Invalid credits amount.'), 400);
            return;
        }

        $order = $this->create_topup_order($project_id, $credits_cents);
        if (is_wp_error($order)) {
            wp_send_json_error(array('message' => $order->get_error_message()), 500);
            return;
        }

        wp_send_json_success(array(
            'message' => 'Credit order created. Complete payment to top up wallet.',
            'order' => $order,
        ));
    }

    public function ajax_get_orders() {
        if (!current_user_can('manage_options')) {
            wp_send_json_error(array('message' => 'Insufficient permissions'), 403);
            return;
        }

        $nonce = isset($_POST['nonce']) ? sanitize_text_field(wp_unslash($_POST['nonce'])) : '';
        if (!wp_verify_nonce($nonce, 'aysa_credits_nonce')) {
            wp_send_json_error(array('message' => 'Security check failed'), 403);
            return;
        }

        $project_id = isset($_POST['project_id']) ? intval(wp_unslash($_POST['project_id'])) : $this->get_project_id();
        $limit = isset($_POST['limit']) ? intval(wp_unslash($_POST['limit'])) : 10;

        if ($project_id <= 0) {
            wp_send_json_success(array());
            return;
        }

        $orders = $this->get_orders($project_id, $limit);
        wp_send_json_success($orders);
    }

    public function ajax_force_sync() {
        if (!current_user_can('manage_options')) {
            wp_send_json_error(array('message' => 'Insufficient permissions'), 403);
            return;
        }

        $nonce = isset($_POST['nonce']) ? sanitize_text_field(wp_unslash($_POST['nonce'])) : '';
        if (!wp_verify_nonce($nonce, 'aysa_credits_nonce') && !wp_verify_nonce($nonce, 'aysa_nonce')) {
            wp_send_json_error(array('message' => 'Security check failed'), 403);
            return;
        }

        $project_id = isset($_POST['project_id']) ? intval(wp_unslash($_POST['project_id'])) : $this->get_project_id();
        if ($project_id <= 0) {
            wp_send_json_error(array('message' => 'Project ID missing.'), 400);
            return;
        }

        $synced = $this->sync_project_with_remote($project_id, true);
        $snapshot = $this->get_wallet_snapshot($project_id, array('allow_remote' => false));
        $snapshot['credits'] = intval($snapshot['balance_cents'] ?? 0);
        $snapshot['sync_executed'] = (bool) $synced;
        wp_send_json_success($snapshot);
    }

    public function ajax_track_usage() {
        if (!current_user_can('manage_options')) {
            wp_send_json_error(array('message' => 'Insufficient permissions'), 403);
            return;
        }

        $nonce = isset($_POST['nonce']) ? sanitize_text_field(wp_unslash($_POST['nonce'])) : '';
        if (!wp_verify_nonce($nonce, 'aysa_nonce') && !wp_verify_nonce($nonce, 'aysa_credits_nonce')) {
            wp_send_json_error(array('message' => 'Security check failed'), 403);
            return;
        }

        $project_id = isset($_POST['project_id']) ? intval(wp_unslash($_POST['project_id'])) : $this->get_project_id();
        $cost_cents = isset($_POST['cost_cents']) ? intval(wp_unslash($_POST['cost_cents'])) : 0;
        $endpoint = isset($_POST['endpoint']) ? sanitize_text_field(wp_unslash($_POST['endpoint'])) : '';
        $name = isset($_POST['name']) ? sanitize_text_field(wp_unslash($_POST['name'])) : '';
        $category = isset($_POST['category']) ? sanitize_text_field(wp_unslash($_POST['category'])) : '';

        if ($project_id <= 0 || $cost_cents <= 0) {
            wp_send_json_error(array('message' => 'Invalid usage payload.'), 400);
            return;
        }

        $ok = $this->enqueue_usage_event($project_id, $endpoint, $cost_cents, array(
            'name' => $name,
            'category' => $category,
        ));

        if (!$ok) {
            wp_send_json_error(array('message' => 'Failed to queue usage.'), 500);
            return;
        }

        wp_send_json_success(array('queued' => true));
    }

    public function ajax_sync_action_cost_registry() {
        if (!current_user_can('manage_options')) {
            wp_send_json_error(array('message' => 'Insufficient permissions'), 403);
            return;
        }

        $nonce = isset($_POST['nonce']) ? sanitize_text_field(wp_unslash($_POST['nonce'])) : '';
        if (!wp_verify_nonce($nonce, 'aysa_nonce') && !wp_verify_nonce($nonce, 'aysa_credits_nonce')) {
            wp_send_json_error(array('message' => 'Security check failed'), 403);
            return;
        }

        $ok = $this->sync_action_cost_registry_from_csv(true);
        if (!$ok) {
            wp_send_json_error(array('message' => 'Action cost registry sync failed.'), 500);
            return;
        }

        wp_send_json_success(array(
            'ok' => true,
            'site_host' => $this->get_site_host_for_registry(),
            'synced_at' => current_time('mysql'),
        ));
    }
}
