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

class Manfiet_Expense_API {
    // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- This REST controller works directly with plugin-owned tables.
    public function __construct() {
        add_action('rest_api_init', [$this, 'register_routes']);
    }

    private function user_can_manage_expenses() {
        return current_user_can('manage_expenses') || current_user_can('manage_options');
    }

    private function categories_table_supports_parent_id() {
        global $wpdb;
        static $has_parent_id = null;

        if ($has_parent_id !== null) {
            return $has_parent_id;
        }

        // phpcs:disable WordPress.DB.DirectDatabaseQuery.SchemaChange -- Schema updates are intentional for plugin-owned tables.
        $table = $wpdb->prefix . 'manfiet_expense_categories';
        $column = $wpdb->get_var($wpdb->prepare("SHOW COLUMNS FROM %i LIKE %s", $table, 'parent_id'));

        if ($column) {
            $has_parent_id = true;
            return true;
        }

        // Try to self-heal older installs where dbDelta did not add the new column.
        $wpdb->query($wpdb->prepare("ALTER TABLE %i ADD COLUMN parent_id INT NOT NULL DEFAULT 0", $table));
        $wpdb->query($wpdb->prepare("ALTER TABLE %i ADD KEY parent_id (parent_id)", $table));

        $column = $wpdb->get_var($wpdb->prepare("SHOW COLUMNS FROM %i LIKE %s", $table, 'parent_id'));
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.SchemaChange
        $has_parent_id = (bool) $column;

        return $has_parent_id;
    }

    private function categories_table_supports_description() {
        global $wpdb;
        static $has_description = null;

        if ($has_description !== null) {
            return $has_description;
        }

        // phpcs:disable WordPress.DB.DirectDatabaseQuery.SchemaChange
        $table = $wpdb->prefix . 'manfiet_expense_categories';
        $column = $wpdb->get_var($wpdb->prepare("SHOW COLUMNS FROM %i LIKE %s", $table, 'description'));

        if ($column) {
            $has_description = true;
            return true;
        }

        $wpdb->query($wpdb->prepare("ALTER TABLE %i ADD COLUMN description TEXT NULL", $table));
        $column = $wpdb->get_var($wpdb->prepare("SHOW COLUMNS FROM %i LIKE %s", $table, 'description'));
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.SchemaChange
        $has_description = (bool) $column;

        return $has_description;
    }

    private function expenses_table_supports_tags() {
        global $wpdb;
        static $has_tags_column = null;

        if ($has_tags_column !== null) {
            return $has_tags_column;
        }

        // phpcs:disable WordPress.DB.DirectDatabaseQuery.SchemaChange -- Schema updates are intentional for plugin-owned tables.
        $table = $wpdb->prefix . 'manfiet_expenses';
        $column = $wpdb->get_var($wpdb->prepare("SHOW COLUMNS FROM %i LIKE %s", $table, 'tags'));

        if ($column) {
            $has_tags_column = true;
            return true;
        }

        $wpdb->query($wpdb->prepare("ALTER TABLE %i ADD COLUMN tags LONGTEXT NULL", $table));
        $column = $wpdb->get_var($wpdb->prepare("SHOW COLUMNS FROM %i LIKE %s", $table, 'tags'));
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.SchemaChange
        $has_tags_column = (bool) $column;

        return $has_tags_column;
    }

    private function expenses_table_supports_remark() {
        global $wpdb;
        static $has_remark_column = null;

        if ($has_remark_column !== null) {
            return $has_remark_column;
        }

        // phpcs:disable WordPress.DB.DirectDatabaseQuery.SchemaChange -- Schema updates are intentional for plugin-owned tables.
        $table = $wpdb->prefix . 'manfiet_expenses';
        $column = $wpdb->get_var($wpdb->prepare("SHOW COLUMNS FROM %i LIKE %s", $table, 'remark'));

        if ($column) {
            $has_remark_column = true;
            return true;
        }

        $wpdb->query($wpdb->prepare("ALTER TABLE %i ADD COLUMN remark TEXT NULL", $table));
        $column = $wpdb->get_var($wpdb->prepare("SHOW COLUMNS FROM %i LIKE %s", $table, 'remark'));
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.SchemaChange
        $has_remark_column = (bool) $column;

        return $has_remark_column;
    }

    private function normalize_tags_array($raw_tags) {
        if (!is_array($raw_tags)) {
            return [];
        }

        $normalized = [];
        foreach ($raw_tags as $tag) {
            $tag_name = sanitize_text_field((string) $tag);
            if ($tag_name !== '') {
                $normalized[] = $tag_name;
            }
        }

        $normalized = array_values(array_unique($normalized));

        return array_slice($normalized, 0, 20);
    }

    private function decode_tags_from_db($value) {
        if (is_array($value)) {
            return $this->normalize_tags_array($value);
        }

        $decoded = json_decode((string) $value, true);
        if (is_array($decoded)) {
            return $this->normalize_tags_array($decoded);
        }

        return [];
    }

    public function register_routes() {
        register_rest_route('manfi-expense-tracker/v1', '/expenses', [
            'methods' => 'GET',
            'callback' => [$this, 'get_expenses'],
            'permission_callback' => [$this, 'is_user_logged_in_permission'],
            'args' => [
                'category' => [
                    'sanitize_callback' => 'sanitize_text_field',
                ],
                'start_date' => [
                    'sanitize_callback' => 'sanitize_text_field',
                    'validate_callback' => [$this, 'validate_date_arg'],
                ],
                'end_date' => [
                    'sanitize_callback' => 'sanitize_text_field',
                    'validate_callback' => [$this, 'validate_date_arg'],
                ],
                'tag_name' => [
                    'sanitize_callback' => 'sanitize_text_field',
                ],
            ],
        ]);

        register_rest_route('manfi-expense-tracker/v1', '/expenses/explore', [
            'methods' => 'GET',
            'callback' => [$this, 'get_expenses_explore'],
            'permission_callback' => [$this, 'is_user_logged_in_permission'],
            'args' => [
                'category_id' => [
                    'required' => false,
                    'sanitize_callback' => 'absint',
                ],
                'include_children' => [
                    'required' => false,
                    'sanitize_callback' => function ($value) {
                        return rest_sanitize_boolean($value);
                    },
                ],
                'tag_name' => [
                    'required' => false,
                    'sanitize_callback' => 'sanitize_text_field',
                ],
                'page' => [
                    'required' => false,
                    'sanitize_callback' => 'absint',
                ],
                'per_page' => [
                    'required' => false,
                    'sanitize_callback' => 'absint',
                ],
            ],
        ]);

        register_rest_route('manfi-expense-tracker/v1', '/expenses', [
            'methods' => 'POST',
            'callback' => [$this, 'add_expense'],
            'permission_callback' => [$this, 'is_user_logged_in_permission'],
            'args' => [
                'category' => [
                    'required' => false,
                    'sanitize_callback' => 'sanitize_text_field',
                ],
                'category_id' => [
                    'required' => false,
                    'sanitize_callback' => 'absint',
                ],
                'amount' => [
                    'required' => true,
                    'sanitize_callback' => function ($value) {
                        return (float) $value;
                    },
                    'validate_callback' => function ($value) {
                        return is_numeric($value) && (float) $value > 0;
                    },
                ],
                'date' => [
                    'required' => true,
                    'sanitize_callback' => 'sanitize_text_field',
                    'validate_callback' => [$this, 'validate_date_arg'],
                ],
                'recurring_interval' => [
                    'sanitize_callback' => 'sanitize_text_field',
                    'validate_callback' => function ($value) {
                        if ($value === null || $value === '') {
                            return true;
                        }

                        return in_array($value, ['day', 'week', 'month', 'year'], true);
                    },
                ],
                'tags' => [
                    'required' => false,
                ],
                'remark' => [
                    'required' => false,
                    'sanitize_callback' => 'sanitize_textarea_field',
                ],
            ],
        ]);

        register_rest_route('manfi-expense-tracker/v1', '/expenses/batch', [
            'methods' => 'POST',
            'callback' => [$this, 'add_expenses_batch'],
            'permission_callback' => [$this, 'is_user_logged_in_permission'],
            'args' => [
                'expenses' => [
                    'required' => true,
                    'type' => 'array',
                ],
            ],
        ]);

        register_rest_route('manfi-expense-tracker/v1', '/expenses/(?P<id>\\d+)', [
            'methods' => 'PUT,PATCH',
            'callback' => [$this, 'update_expense'],
            'permission_callback' => [$this, 'is_user_logged_in_permission'],
            'args' => [
                'id' => [
                    'required' => true,
                    'validate_callback' => function ($value) {
                        return absint($value) > 0;
                    },
                ],
                'category' => [
                    'required' => false,
                    'sanitize_callback' => 'sanitize_text_field',
                ],
                'category_id' => [
                    'required' => false,
                    'sanitize_callback' => 'absint',
                ],
                'amount' => [
                    'required' => true,
                    'sanitize_callback' => function ($value) {
                        return (float) $value;
                    },
                    'validate_callback' => function ($value) {
                        return is_numeric($value) && (float) $value > 0;
                    },
                ],
                'date' => [
                    'required' => true,
                    'sanitize_callback' => 'sanitize_text_field',
                    'validate_callback' => [$this, 'validate_date_arg'],
                ],
                'recurring_interval' => [
                    'sanitize_callback' => 'sanitize_text_field',
                    'validate_callback' => function ($value) {
                        if ($value === null || $value === '') {
                            return true;
                        }

                        return in_array($value, ['day', 'week', 'month', 'year'], true);
                    },
                ],
                'tags' => [
                    'required' => false,
                ],
                'remark' => [
                    'required' => false,
                    'sanitize_callback' => 'sanitize_textarea_field',
                ],
            ],
        ]);

        register_rest_route('manfi-expense-tracker/v1', '/expenses/(?P<id>\\d+)', [
            'methods' => 'DELETE',
            'callback' => [$this, 'delete_expense'],
            'permission_callback' => [$this, 'is_user_logged_in_permission'],
            'args' => [
                'id' => [
                    'required' => true,
                    'validate_callback' => function ($value) {
                        return absint($value) > 0;
                    },
                ],
            ],
        ]);

        register_rest_route('manfi-expense-tracker/v1', '/tags', [
            'methods' => 'GET',
            'callback' => [$this, 'get_user_tags'],
            'permission_callback' => [$this, 'is_user_logged_in_permission'],
        ]);

        register_rest_route('manfi-expense-tracker/v1', '/tags', [
            'methods' => 'POST',
            'callback' => [$this, 'add_tag'],
            'permission_callback' => [$this, 'is_user_logged_in_permission'],
            'args' => [
                'tag_name' => [
                    'required' => true,
                    'sanitize_callback' => 'sanitize_text_field',
                ],
                'is_global' => [
                    'sanitize_callback' => function ($value) {
                        return rest_sanitize_boolean($value);
                    },
                ],
            ],
        ]);

        register_rest_route('manfi-expense-tracker/v1', '/tags/(?P<id>\\d+)', [
            'methods' => 'DELETE',
            'callback' => [$this, 'delete_tag'],
            'permission_callback' => [$this, 'is_user_logged_in_permission'],
            'args' => [
                'id' => [
                    'required' => true,
                    'validate_callback' => function ($value) {
                        return absint($value) > 0;
                    },
                ],
            ],
        ]);

        register_rest_route('manfi-expense-tracker/v1', '/categories', [
            'methods' => 'GET',
            'callback' => [$this, 'get_user_categories'],
            'permission_callback' => [$this, 'is_user_logged_in_permission'],
        ]);

        register_rest_route('manfi-expense-tracker/v1', '/categories', [
            'methods' => 'POST',
            'callback' => [$this, 'add_category'],
            'permission_callback' => [$this, 'is_user_logged_in_permission'],
            'args' => [
                'category_name' => [
                    'required' => true,
                    'sanitize_callback' => 'sanitize_text_field',
                ],
                'parent_id' => [
                    'required' => false,
                    'sanitize_callback' => 'absint',
                ],
                'is_global' => [
                    'sanitize_callback' => function ($value) {
                        return rest_sanitize_boolean($value);
                    },
                ],
                'description' => [
                    'required' => false,
                    'sanitize_callback' => 'sanitize_textarea_field',
                ],
            ],
        ]);

        register_rest_route('manfi-expense-tracker/v1', '/categories/(?P<id>\\d+)', [
            'methods' => 'PUT,PATCH',
            'callback' => [$this, 'update_category'],
            'permission_callback' => [$this, 'is_user_logged_in_permission'],
            'args' => [
                'id' => [
                    'required' => true,
                    'validate_callback' => function ($value) {
                        return absint($value) > 0;
                    },
                ],
                'category_name' => [
                    'required' => true,
                    'sanitize_callback' => 'sanitize_text_field',
                ],
                'parent_id' => [
                    'required' => false,
                    'sanitize_callback' => 'absint',
                ],
                'is_global' => [
                    'sanitize_callback' => function ($value) {
                        return rest_sanitize_boolean($value);
                    },
                ],
                'description' => [
                    'required' => false,
                    'sanitize_callback' => 'sanitize_textarea_field',
                ],
            ],
        ]);

        register_rest_route('manfi-expense-tracker/v1', '/categories/(?P<id>\\d+)', [
            'methods' => 'DELETE',
            'callback' => [$this, 'delete_category'],
            'permission_callback' => [$this, 'is_user_logged_in_permission'],
            'args' => [
                'id' => [
                    'required' => true,
                    'validate_callback' => function ($value) {
                        return absint($value) > 0;
                    },
                ],
            ],
        ]);

        register_rest_route('manfi-expense-tracker/v1', '/settings', [
            'methods' => 'GET',
            'callback' => [$this, 'get_settings'],
            'permission_callback' => [$this, 'is_admin_permission'],
        ]);

        register_rest_route('manfi-expense-tracker/v1', '/settings', [
            'methods' => 'POST',
            'callback' => [$this, 'update_settings'],
            'permission_callback' => [$this, 'is_admin_permission'],
        ]);
    }

    public function is_user_logged_in_permission() {
        return is_user_logged_in() && (current_user_can('read') || current_user_can('manage_options'));
    }

    public function is_admin_permission() {
        return current_user_can('manage_options');
    }

    public function get_settings() {
        $currency_symbols = [
            'USD' => '$',
            'EUR' => '€',
            'GBP' => '£',
            'INR' => '₹',
            'BDT' => '৳',
            'CAD' => 'C$',
            'AUD' => 'A$',
            'JPY' => '¥',
            'CNY' => '¥',
        ];

        return rest_ensure_response([
            'default_budget' => (int) get_option('manfiet_default_budget', 500),
            'default_weekly_budget' => (int) get_option('manfiet_default_weekly_budget', 125),
            'default_yearly_budget' => (int) get_option('manfiet_default_yearly_budget', 6000),
            'export_unknown_user_label' => (string) get_option('manfiet_export_unknown_user_label', 'Unknown User'),
            'expenses_per_page' => (int) get_option('manfiet_expenses_per_page', 20),
            'currency' => (string) get_option('manfiet_currency', 'USD'),
            'enable_invoice_manager' => (int) get_option('manfiet_enable_invoice_manager', 0),
            'invoice_late_fee_enabled' => (int) get_option('manfiet_invoice_late_fee_enabled', 1),
            'invoice_late_fee_pct' => (float) get_option('manfiet_invoice_late_fee_pct', 5.0),
            'invoice_late_fee_type' => (string) get_option('manfiet_invoice_late_fee_type', 'percentage'),
            'invoice_default_terms' => (string) get_option('manfiet_invoice_default_terms', "• Payment is due on or before the specified due date.\n• An overdue/late payment fee of {late_fee}% will apply to any unpaid balance after the due date.\n• Please mention the invoice number as reference when making payments."),
            'enable_gutenberg_blocks' => (int) get_option('manfiet_enable_gutenberg_blocks', 1),
            'enable_elementor_widgets' => (int) get_option('manfiet_enable_elementor_widgets', 0),
            'email_sender_address' => (string) get_option('manfiet_email_sender_address', ''),
            'email_sender_name' => (string) get_option('manfiet_email_sender_name', 'Manfi Expense Tracker'),
            'currency_symbols' => $currency_symbols,
        ]);
    }

    public function update_settings(WP_REST_Request $request) {
        $params = $request->get_params();

        if (isset($params['default_budget'])) {
            update_option('manfiet_default_budget', absint($params['default_budget']));
        }
        if (isset($params['default_weekly_budget'])) {
            update_option('manfiet_default_weekly_budget', absint($params['default_weekly_budget']));
        }
        if (isset($params['default_yearly_budget'])) {
            update_option('manfiet_default_yearly_budget', absint($params['default_yearly_budget']));
        }
        if (isset($params['export_unknown_user_label'])) {
            update_option('manfiet_export_unknown_user_label', sanitize_text_field($params['export_unknown_user_label']));
        }
        if (isset($params['expenses_per_page'])) {
            $per_page = absint($params['expenses_per_page']);
            update_option('manfiet_expenses_per_page', $per_page > 0 ? $per_page : 20);
        }
        if (isset($params['currency'])) {
            $allowed = ['USD', 'EUR', 'GBP', 'INR', 'BDT', 'CAD', 'AUD', 'JPY', 'CNY'];
            if (in_array($params['currency'], $allowed, true)) {
                update_option('manfiet_currency', $params['currency']);
            }
        }
        if (isset($params['enable_invoice_manager'])) {
            update_option('manfiet_enable_invoice_manager', !empty($params['enable_invoice_manager']) ? 1 : 0);
        }
        if (isset($params['invoice_late_fee_enabled'])) {
            update_option('manfiet_invoice_late_fee_enabled', !empty($params['invoice_late_fee_enabled']) ? 1 : 0);
        }
        if (isset($params['invoice_late_fee_pct'])) {
            update_option('manfiet_invoice_late_fee_pct', max(0, (float) $params['invoice_late_fee_pct']));
        }
        if (isset($params['invoice_late_fee_type'])) {
            $fee_type = sanitize_key($params['invoice_late_fee_type']);
            update_option('manfiet_invoice_late_fee_type', in_array($fee_type, ['percentage', 'flat'], true) ? $fee_type : 'percentage');
        }
        if (isset($params['invoice_default_terms'])) {
            update_option('manfiet_invoice_default_terms', sanitize_textarea_field($params['invoice_default_terms']));
        }
        if (isset($params['enable_gutenberg_blocks'])) {
            update_option('manfiet_enable_gutenberg_blocks', !empty($params['enable_gutenberg_blocks']) ? 1 : 0);
        }
        if (isset($params['enable_elementor_widgets'])) {
            update_option('manfiet_enable_elementor_widgets', !empty($params['enable_elementor_widgets']) ? 1 : 0);
        }
        if (isset($params['email_sender_address'])) {
            update_option('manfiet_email_sender_address', sanitize_email($params['email_sender_address']));
        }
        if (isset($params['email_sender_name'])) {
            update_option('manfiet_email_sender_name', sanitize_text_field($params['email_sender_name']));
        }

        return rest_ensure_response([
            'success' => true,
            'message' => __('Settings saved successfully.', 'manfi-expense-tracker'),
        ]);
    }

    public function validate_date_arg($value) {
        if ($value === null || $value === '') {
            return true;
        }

        return $this->is_valid_date($value);
    }

    private function is_valid_date($value) {
        $date = DateTime::createFromFormat('Y-m-d', $value);
        return $date instanceof DateTime && $date->format('Y-m-d') === $value;
    }

    private function get_visible_categories($user_id) {
        global $wpdb;
        $has_parent_id = $this->categories_table_supports_parent_id();
        $has_description = $this->categories_table_supports_description();

        if ($has_parent_id && $has_description) {
            if ($this->user_can_manage_expenses()) {
                $results = $wpdb->get_results(
                    "SELECT id, user_id, category_name, parent_id, description FROM {$wpdb->prefix}manfiet_expense_categories ORDER BY category_name ASC, user_id ASC",
                    ARRAY_A
                );
            } else {
                $results = $wpdb->get_results(
                    $wpdb->prepare(
                        "SELECT id, user_id, category_name, parent_id, description FROM {$wpdb->prefix}manfiet_expense_categories WHERE user_id IN (0, %d) ORDER BY category_name ASC, user_id ASC",
                        $user_id
                    ),
                    ARRAY_A
                );
            }
        } elseif ($has_parent_id && !$has_description) {
            if ($this->user_can_manage_expenses()) {
                $results = $wpdb->get_results(
                    "SELECT id, user_id, category_name, parent_id FROM {$wpdb->prefix}manfiet_expense_categories ORDER BY category_name ASC, user_id ASC",
                    ARRAY_A
                );
            } else {
                $results = $wpdb->get_results(
                    $wpdb->prepare(
                        "SELECT id, user_id, category_name, parent_id FROM {$wpdb->prefix}manfiet_expense_categories WHERE user_id IN (0, %d) ORDER BY category_name ASC, user_id ASC",
                        $user_id
                    ),
                    ARRAY_A
                );
            }
        } elseif (!$has_parent_id && $has_description) {
            if ($this->user_can_manage_expenses()) {
                $results = $wpdb->get_results(
                    "SELECT id, user_id, category_name, 0 AS parent_id, description FROM {$wpdb->prefix}manfiet_expense_categories ORDER BY category_name ASC, user_id ASC",
                    ARRAY_A
                );
            } else {
                $results = $wpdb->get_results(
                    $wpdb->prepare(
                        "SELECT id, user_id, category_name, 0 AS parent_id, description FROM {$wpdb->prefix}manfiet_expense_categories WHERE user_id IN (0, %d) ORDER BY category_name ASC, user_id ASC",
                        $user_id
                    ),
                    ARRAY_A
                );
            }
        } else {
            if ($this->user_can_manage_expenses()) {
                $results = $wpdb->get_results(
                    "SELECT id, user_id, category_name, 0 AS parent_id FROM {$wpdb->prefix}manfiet_expense_categories ORDER BY category_name ASC, user_id ASC",
                    ARRAY_A
                );
            } else {
                $results = $wpdb->get_results(
                    $wpdb->prepare(
                        "SELECT id, user_id, category_name, 0 AS parent_id FROM {$wpdb->prefix}manfiet_expense_categories WHERE user_id IN (0, %d) ORDER BY category_name ASC, user_id ASC",
                        $user_id
                    ),
                    ARRAY_A
                );
            }
        }

        $categories = [];
        foreach ($results as $category) {
            $parent_id = isset($category['parent_id']) ? (int) $category['parent_id'] : 0;
            $key = strtolower((string) $category['category_name']) . '|' . $parent_id;

            if (!isset($categories[$key]) || (int) $category['user_id'] === $user_id) {
                $categories[$key] = [
                    'id' => (int) $category['id'],
                    'user_id' => (int) $category['user_id'],
                    'category_name' => (string) $category['category_name'],
                    'parent_id' => $parent_id,
                    'is_global' => (int) $category['user_id'] === 0,
                    'description' => isset($category['description']) ? (string) $category['description'] : '',
                ];
            }
        }

        return $this->enrich_categories_with_tree(array_values($categories));
    }

    private function get_visible_tags($user_id) {
        global $wpdb;

        if ($this->user_can_manage_expenses()) {
            // Read-only tag listing from plugin-owned table.
            $results = $wpdb->get_results(
                "SELECT id, user_id, tag_name FROM {$wpdb->prefix}manfiet_expense_tags ORDER BY tag_name ASC, user_id ASC",
                ARRAY_A
            );
        } else {
            // Read-only tag listing from plugin-owned table.
            $results = $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT id, user_id, tag_name FROM {$wpdb->prefix}manfiet_expense_tags WHERE user_id IN (0, %d) ORDER BY tag_name ASC, user_id ASC",
                    $user_id
                ),
                ARRAY_A
            );
        }

        $tags = [];
        foreach ($results as $tag) {
            $key = strtolower((string) $tag['tag_name']);

            if (!isset($tags[$key]) || (int) $tag['user_id'] === $user_id) {
                $tags[$key] = [
                    'id' => (int) $tag['id'],
                    'user_id' => (int) $tag['user_id'],
                    'tag_name' => (string) $tag['tag_name'],
                    'is_global' => (int) $tag['user_id'] === 0,
                ];
            }
        }

        return array_values($tags);
    }

    private function ensure_tags_exist(array $tag_names, $owner_id) {
        global $wpdb;

        foreach ($tag_names as $tag_name) {
            // Read-only existence check in plugin-owned tags table.
            $exists = $wpdb->get_var(
                $wpdb->prepare(
                    "SELECT id FROM {$wpdb->prefix}manfiet_expense_tags WHERE user_id = %d AND tag_name = %s",
                    $owner_id,
                    $tag_name
                )
            );

            if ($exists) {
                continue;
            }

            // Insert into plugin-owned tags table.
            $wpdb->insert(
                $wpdb->prefix . 'manfiet_expense_tags',
                [
                    'user_id' => $owner_id,
                    'tag_name' => $tag_name,
                ],
                ['%d', '%s']
            );
        }
    }

    private function enrich_categories_with_tree(array $categories) {
        $indexed = [];
        foreach ($categories as $category) {
            $indexed[(int) $category['id']] = $category;
        }

        $build_path = function ($category_id, $depth = 0) use (&$build_path, &$indexed) {
            if (!isset($indexed[$category_id])) {
                return ['level' => 0, 'path_name' => ''];
            }

            if ($depth > 20) {
                return ['level' => 0, 'path_name' => (string) $indexed[$category_id]['category_name']];
            }

            $node = $indexed[$category_id];
            $parent_id = (int) $node['parent_id'];

            if ($parent_id <= 0 || !isset($indexed[$parent_id])) {
                return ['level' => 0, 'path_name' => (string) $node['category_name']];
            }

            $parent = $build_path($parent_id, $depth + 1);
            return [
                'level' => (int) $parent['level'] + 1,
                'path_name' => (string) $parent['path_name'] . ' > ' . (string) $node['category_name'],
            ];
        };

        foreach ($indexed as $id => $category) {
            $tree = $build_path((int) $id);
            $indexed[$id]['level'] = (int) $tree['level'];
            $indexed[$id]['path_name'] = (string) $tree['path_name'];
            $indexed[$id]['display_name'] = str_repeat('— ', (int) $tree['level']) . (string) $category['category_name'];

            if ((int) $category['parent_id'] > 0 && !isset($indexed[(int) $category['parent_id']])) {
                $indexed[$id]['parent_id'] = 0;
            }
        }

        $result = array_values($indexed);
        usort($result, function ($a, $b) {
            return strcasecmp((string) $a['path_name'], (string) $b['path_name']);
        });

        return $result;
    }

    private function get_accessible_category($category_id, $user_id) {
        $categories = $this->get_visible_categories($user_id);

        foreach ($categories as $category) {
            if ((int) $category['id'] === (int) $category_id) {
                return $category;
            }
        }

        return null;
    }

    private function get_categories_for_owner($owner_id, $has_parent_id = true) {
        global $wpdb;

        if ($has_parent_id) {
            // Read-only owner category listing from plugin-owned table.
            return $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT id, user_id, category_name, parent_id FROM {$wpdb->prefix}manfiet_expense_categories WHERE user_id = %d ORDER BY id ASC",
                    (int) $owner_id
                ),
                ARRAY_A
            );
        }

        // Read-only owner category listing from plugin-owned table.
        return $wpdb->get_results(
            $wpdb->prepare(
                "SELECT id, user_id, category_name, 0 AS parent_id FROM {$wpdb->prefix}manfiet_expense_categories WHERE user_id = %d ORDER BY id ASC",
                (int) $owner_id
            ),
            ARRAY_A
        );
    }

    private function build_category_path_map(array $categories) {
        $by_id = [];
        foreach ($categories as $category) {
            $by_id[(int) $category['id']] = [
                'id' => (int) $category['id'],
                'name' => (string) $category['category_name'],
                'parent_id' => (int) $category['parent_id'],
            ];
        }

        $build_path = function ($id, $depth = 0) use (&$build_path, &$by_id) {
            if (!isset($by_id[$id])) {
                return '';
            }

            if ($depth > 20) {
                return (string) $by_id[$id]['name'];
            }

            $node = $by_id[$id];
            $parent_id = (int) $node['parent_id'];
            if ($parent_id <= 0 || !isset($by_id[$parent_id])) {
                return (string) $node['name'];
            }

            return $build_path($parent_id, $depth + 1) . ' > ' . (string) $node['name'];
        };

        $paths = [];
        foreach ($by_id as $id => $node) {
            $paths[(int) $id] = $build_path((int) $id);
        }

        return $paths;
    }

    private function is_descendant_category($candidate_parent_id, $category_id, array $categories) {
        $parent_lookup = [];
        foreach ($categories as $category) {
            $parent_lookup[(int) $category['id']] = (int) $category['parent_id'];
        }

        $current = (int) $candidate_parent_id;
        $guard = 0;

        while ($current > 0 && $guard < 1000) {
            if ($current === (int) $category_id) {
                return true;
            }

            $current = isset($parent_lookup[$current]) ? (int) $parent_lookup[$current] : 0;
            $guard++;
        }

        return false;
    }

    private function ensure_category_exists($category_name, $owner_id, $parent_id = 0) {
        global $wpdb;
        $has_parent_id = $this->categories_table_supports_parent_id();

        if ($has_parent_id) {
            // Read-only existence check in plugin-owned categories table.
            $existing_id = $wpdb->get_var(
                $wpdb->prepare(
                    "SELECT id FROM {$wpdb->prefix}manfiet_expense_categories WHERE user_id = %d AND parent_id = %d AND category_name = %s",
                    $owner_id,
                    $parent_id,
                    $category_name
                )
            );
        } else {
            // Read-only existence check in plugin-owned categories table.
            $existing_id = $wpdb->get_var(
                $wpdb->prepare(
                    "SELECT id FROM {$wpdb->prefix}manfiet_expense_categories WHERE user_id = %d AND category_name = %s",
                    $owner_id,
                    $category_name
                )
            );
        }

        if ($existing_id) {
            return (int) $existing_id;
        }

        if ($has_parent_id) {
            // Insert into plugin-owned categories table.
            $inserted = $wpdb->insert(
                $wpdb->prefix . 'manfiet_expense_categories',
                [
                    'user_id' => $owner_id,
                    'category_name' => $category_name,
                    'parent_id' => $parent_id,
                ],
                ['%d', '%s', '%d']
            );
        } else {
            // Insert into plugin-owned categories table.
            $inserted = $wpdb->insert(
                $wpdb->prefix . 'manfiet_expense_categories',
                [
                    'user_id' => $owner_id,
                    'category_name' => $category_name,
                ],
                ['%d', '%s']
            );
        }

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

        return (int) $wpdb->insert_id;
    }

    private function resolve_expense_category_value($request, $user_id) {
        $category_id = absint($request->get_param('category_id'));

        if ($category_id > 0) {
            $category = $this->get_accessible_category($category_id, $user_id);
            if (!$category) {
                return new WP_Error(
                    'manfiet_expense_category_not_found',
                    __('Selected category not found.', 'manfi-expense-tracker'),
                    ['status' => 400]
                );
            }

            return [
                'category_name' => (string) $category['path_name'],
                'category_id' => (int) $category['id'],
            ];
        }

        $category_name = sanitize_text_field((string) $request->get_param('category'));
        if ($category_name === '') {
            return new WP_Error(
                'manfiet_empty_expense_category',
                __('Expense category is required.', 'manfi-expense-tracker'),
                ['status' => 400]
            );
        }

        $category_owner_id = $this->user_can_manage_expenses() ? 0 : $user_id;
        $new_category_id = $this->ensure_category_exists($category_name, $category_owner_id, 0);

        if ($new_category_id === false) {
            return new WP_Error(
                'manfiet_expense_category_register_failed',
                __('Failed to register the expense category.', 'manfi-expense-tracker'),
                ['status' => 500]
            );
        }

        return [
            'category_name' => $category_name,
            'category_id' => (int) $new_category_id,
        ];
    }

    public function get_expenses(WP_REST_Request $request) {
        global $wpdb;
        $user_id = get_current_user_id();
        $has_tags_column = $this->expenses_table_supports_tags();
        $has_remark_column = $this->expenses_table_supports_remark();
        $category = (string) $request->get_param('category');
        $start_date = (string) $request->get_param('start_date');
        $end_date = (string) $request->get_param('end_date');
        $tag_name = (string) $request->get_param('tag_name');

        if (($start_date && !$end_date) || (!$start_date && $end_date)) {
            return new WP_Error(
                'manfiet_invalid_date_range',
                __('Both start_date and end_date are required when filtering by date.', 'manfi-expense-tracker'),
                ['status' => 400]
            );
        }

        if ($start_date && $end_date && strtotime($start_date) > strtotime($end_date)) {
            return new WP_Error(
                'manfiet_invalid_date_order',
                __('start_date cannot be later than end_date.', 'manfi-expense-tracker'),
                ['status' => 400]
            );
        }

        // Avoid empty date comparison in MySQL strict mode (Incorrect DATE value: '')
        $start_date_query = !empty($start_date) ? $start_date : '1000-01-01';
        $end_date_query = !empty($end_date) ? $end_date : '9999-12-31';

        $tag_like = !empty($tag_name) ? '%"' . $wpdb->esc_like($tag_name) . '"%' : '';

        // Static SQL: all optional conditions are expressed as short-circuit predicates
        // so no dynamic WHERE building is needed (avoids PluginCheck dynamic-SQL warnings).
        $is_admin = $this->user_can_manage_expenses() ? 1 : 0;
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Fully static parameterised query; table name comes from $wpdb->prefix (trusted).
        $expenses = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT * FROM {$wpdb->prefix}manfiet_expenses WHERE (1=%d OR user_id=%d) AND (%s='' OR category=%s) AND (date>=%s) AND (date<=%s) AND (%s='' OR tags LIKE %s) ORDER BY date DESC, id DESC",
                $is_admin,
                $user_id,
                $category, $category,
                $start_date_query,
                $end_date_query,
                $tag_name, $tag_like
            ),
            ARRAY_A
        );

        foreach ($expenses as &$expense) {
            if ($has_tags_column && array_key_exists('tags', $expense)) {
                $expense['tags'] = $this->decode_tags_from_db($expense['tags']);
            } else {
                $expense['tags'] = [];
            }
        }
        unset($expense);

        return rest_ensure_response($expenses);
    }

    private function get_category_descendant_path_filters($root_category_id, $user_id) {
        $categories = $this->get_visible_categories($user_id);
        $by_id = [];
        $children = [];

        foreach ($categories as $category) {
            $id = (int) $category['id'];
            $parent_id = (int) $category['parent_id'];

            $by_id[$id] = $category;

            if (!isset($children[$parent_id])) {
                $children[$parent_id] = [];
            }
            $children[$parent_id][] = $id;
        }

        $root_category_id = (int) $root_category_id;
        if (!isset($by_id[$root_category_id])) {
            return [];
        }

        $queue = [$root_category_id];
        $visited = [];
        $paths = [];

        while (!empty($queue)) {
            $current_id = array_shift($queue);

            if (isset($visited[$current_id]) || !isset($by_id[$current_id])) {
                continue;
            }

            $visited[$current_id] = true;
            $path = isset($by_id[$current_id]['path_name']) ? (string) $by_id[$current_id]['path_name'] : '';
            if ($path !== '') {
                $paths[] = $path;
            }

            $child_ids = isset($children[$current_id]) ? $children[$current_id] : [];
            foreach ($child_ids as $child_id) {
                if (!isset($visited[$child_id])) {
                    $queue[] = $child_id;
                }
            }
        }

        return array_values(array_unique($paths));
    }

    public function get_expenses_explore(WP_REST_Request $request) {
        global $wpdb;

        $user_id = get_current_user_id();
        $has_tags_column = $this->expenses_table_supports_tags();
        $has_remark_column = $this->expenses_table_supports_remark();
        $category_id = absint($request->get_param('category_id'));
        $include_children = rest_sanitize_boolean($request->get_param('include_children'));
        $tag_name = sanitize_text_field((string) $request->get_param('tag_name'));
        $page = max(1, absint($request->get_param('page')) ?: 1);
        $per_page = max(1, min(200, absint($request->get_param('per_page')) ?: 50));

        if ($category_id <= 0 && $tag_name === '') {
            return new WP_Error(
                'manfiet_explore_filter_missing',
                __('Please provide category_id or tag_name for expense explorer.', 'manfi-expense-tracker'),
                ['status' => 400]
            );
        }

        $count_sql = "SELECT COUNT(*) FROM {$wpdb->prefix}manfiet_expenses WHERE 1=1";
        $list_sql  = "SELECT * FROM {$wpdb->prefix}manfiet_expenses WHERE 1=1";
        $params    = [];

        if (!$this->user_can_manage_expenses()) {
            $count_sql .= ' AND user_id = %d';
            $list_sql  .= ' AND user_id = %d';
            $params[]   = $user_id;
        }

        if ($category_id > 0) {
            $paths = $this->get_category_descendant_path_filters($category_id, $user_id);

            if (empty($paths)) {
                return rest_ensure_response([
                    'items' => [],
                    'pagination' => [
                        'page' => $page,
                        'per_page' => $per_page,
                        'total' => 0,
                        'total_pages' => 0,
                    ],
                ]);
            }

            if ($include_children) {
                $or_clause = implode(' OR ', array_fill(0, count($paths), 'category = %s'));
                $count_sql .= ' AND (' . $or_clause . ')';
                $list_sql  .= ' AND (' . $or_clause . ')';
                foreach ($paths as $path) {
                    $params[] = $path;
                }
            } else {
                $count_sql .= ' AND category = %s';
                $list_sql  .= ' AND category = %s';
                $params[]   = $paths[0];
            }
        }

        if ($tag_name !== '') {
            if (!$has_tags_column) {
                return rest_ensure_response([
                    'items' => [],
                    'pagination' => [
                        'page' => $page,
                        'per_page' => $per_page,
                        'total' => 0,
                        'total_pages' => 0,
                    ],
                ]);
            }

            $count_sql .= ' AND tags LIKE %s';
            $list_sql  .= ' AND tags LIKE %s';
            $params[]   = '%"' . $wpdb->esc_like($tag_name) . '"%';
        }

        // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Dynamic explorer SQL is assembled from fixed fragments and prepared values.
        if (!empty($params)) {
            $total = (int) $wpdb->get_var($wpdb->prepare($count_sql, $params));
        } else {
            $total = (int) $wpdb->get_var($count_sql);
        }

        $offset = ($page - 1) * $per_page;
        $list_sql .= ' ORDER BY date DESC, id DESC LIMIT %d OFFSET %d';
        $list_params = array_merge($params, [$per_page, $offset]);
        $items = $wpdb->get_results($wpdb->prepare($list_sql, $list_params), ARRAY_A);
        // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

        foreach ($items as &$expense) {
            if ($has_tags_column && array_key_exists('tags', $expense)) {
                $expense['tags'] = $this->decode_tags_from_db($expense['tags']);
            } else {
                $expense['tags'] = [];
            }
        }
        unset($expense);

        return rest_ensure_response([
            'items' => $items,
            'pagination' => [
                'page' => $page,
                'per_page' => $per_page,
                'total' => $total,
                'total_pages' => (int) ceil($total / $per_page),
            ],
        ]);
    }

    public function get_user_categories() {
        $user_id = get_current_user_id();

        return rest_ensure_response($this->get_visible_categories($user_id));
    }

    public function get_user_tags() {
        $user_id = get_current_user_id();

        return rest_ensure_response($this->get_visible_tags($user_id));
    }

    public function add_tag(WP_REST_Request $request) {
        global $wpdb;

        $user_id = get_current_user_id();
        $tag_name = sanitize_text_field((string) $request->get_param('tag_name'));
        $is_global = rest_sanitize_boolean($request->get_param('is_global'));

        if ($is_global && !$this->user_can_manage_expenses()) {
            return new WP_Error(
                'manfiet_forbidden_global_tag',
                __('You are not allowed to create global tags.', 'manfi-expense-tracker'),
                ['status' => 403]
            );
        }

        if ($tag_name === '') {
            return new WP_Error(
                'manfiet_empty_tag',
                __('Tag name is required.', 'manfi-expense-tracker'),
                ['status' => 400]
            );
        }

        $owner_id = $is_global ? 0 : $user_id;

        // Read-only existence check in plugin-owned tags table.
        $existing_id = $wpdb->get_var(
            $wpdb->prepare(
                "SELECT id FROM {$wpdb->prefix}manfiet_expense_tags WHERE user_id = %d AND tag_name = %s",
                $owner_id,
                $tag_name
            )
        );

        if ($existing_id) {
            return rest_ensure_response([
                'message' => __('Tag already exists.', 'manfi-expense-tracker'),
                'id' => (int) $existing_id,
                'tag_name' => $tag_name,
                'user_id' => $owner_id,
                'is_global' => $owner_id === 0,
            ]);
        }

        // Insert into plugin-owned tags table.
        $inserted = $wpdb->insert(
            $wpdb->prefix . 'manfiet_expense_tags',
            [
                'user_id' => $owner_id,
                'tag_name' => $tag_name,
            ],
            ['%d', '%s']
        );

        if ($inserted === false) {
            return new WP_Error(
                'manfiet_tag_insert_failed',
                __('Failed to add tag.', 'manfi-expense-tracker'),
                ['status' => 500]
            );
        }

        return rest_ensure_response([
            'message' => __('Tag added successfully.', 'manfi-expense-tracker'),
            'id' => (int) $wpdb->insert_id,
            'tag_name' => $tag_name,
            'user_id' => $owner_id,
            'is_global' => $owner_id === 0,
        ]);
    }

    public function add_category(WP_REST_Request $request) {
        global $wpdb;
        $user_id = get_current_user_id();
        $category_name = sanitize_text_field((string) $request->get_param('category_name'));
        $description = sanitize_textarea_field((string) $request->get_param('description'));
        $is_global = rest_sanitize_boolean($request->get_param('is_global'));
        $parent_id = absint($request->get_param('parent_id'));
        $has_parent_id = $this->categories_table_supports_parent_id();
        $has_description = $this->categories_table_supports_description();

        if ($is_global && !$this->user_can_manage_expenses()) {
            return new WP_Error(
                'manfiet_forbidden_global_category',
                __('You are not allowed to create global categories.', 'manfi-expense-tracker'),
                ['status' => 403]
            );
        }

        $owner_id = $is_global ? 0 : $user_id;

        if ($category_name === '') {
            return new WP_Error(
                'manfiet_empty_category',
                __('Category name is required.', 'manfi-expense-tracker'),
                ['status' => 400]
            );
        }

        if ($has_parent_id && $parent_id > 0) {
            $parent_category = $this->get_accessible_category($parent_id, $user_id);
            if (!$parent_category) {
                return new WP_Error(
                    'manfiet_invalid_parent_category',
                    __('Selected parent category was not found.', 'manfi-expense-tracker'),
                    ['status' => 400]
                );
            }

            if ((int) $parent_category['user_id'] !== (int) $owner_id) {
                return new WP_Error(
                    'manfiet_parent_owner_mismatch',
                    __('Parent and child categories must have the same owner type.', 'manfi-expense-tracker'),
                    ['status' => 400]
                );
            }
        }

        if (!$has_parent_id) {
            $parent_id = 0;
        }

        if ($has_parent_id) {
            // Read-only duplicate check in plugin-owned categories table.
            $exists = $wpdb->get_var($wpdb->prepare(
                "SELECT id FROM {$wpdb->prefix}manfiet_expense_categories WHERE user_id = %d AND parent_id = %d AND category_name = %s",
                $owner_id,
                $parent_id,
                $category_name
            ));
        } else {
            // Read-only duplicate check in plugin-owned categories table.
            $exists = $wpdb->get_var($wpdb->prepare(
                "SELECT id FROM {$wpdb->prefix}manfiet_expense_categories WHERE user_id = %d AND category_name = %s",
                $owner_id,
                $category_name
            ));
        }

        if ($exists) {
            return rest_ensure_response([
                'message' => __('Category already exists.', 'manfi-expense-tracker'),
                'id' => (int) $exists,
            ]);
        }

        $insert_data = [
            'user_id' => $owner_id,
            'category_name' => $category_name,
        ];
        $insert_format = ['%d', '%s'];

        if ($has_parent_id) {
            $insert_data['parent_id'] = $parent_id;
            $insert_format[] = '%d';
        }

        if ($has_description) {
            $insert_data['description'] = $description;
            $insert_format[] = '%s';
        }

        // Insert into categories table.
        $inserted = $wpdb->insert(
            $wpdb->prefix . 'manfiet_expense_categories',
            $insert_data,
            $insert_format
        );

        if ($inserted === false) {
            return new WP_Error(
                'manfiet_category_insert_failed',
                __('Failed to add category.', 'manfi-expense-tracker'),
                ['status' => 500]
            );
        }

        $created = $this->get_accessible_category((int) $wpdb->insert_id, $user_id);

        return rest_ensure_response([
            'message' => __('Category added successfully.', 'manfi-expense-tracker'),
            'id' => (int) $wpdb->insert_id,
            'category_name' => $category_name,
            'parent_id' => $parent_id,
            'user_id' => $owner_id,
            'is_global' => $owner_id === 0,
            'path_name' => $created ? $created['path_name'] : $category_name,
            'display_name' => $created ? $created['display_name'] : $category_name,
            'level' => $created ? (int) $created['level'] : 0,
        ]);
    }

    public function add_expense(WP_REST_Request $request) {
        global $wpdb;

        $user_id = get_current_user_id();
        $has_tags_column = $this->expenses_table_supports_tags();
        $has_remark_column = $this->expenses_table_supports_remark();
        $amount = (float) $request->get_param('amount');
        $date = sanitize_text_field((string) $request->get_param('date'));
        $recurring_interval = sanitize_text_field((string) $request->get_param('recurring_interval'));
        $tag_names = $this->normalize_tags_array($request->get_param('tags'));
        $remark = $request->get_param('remark') !== null ? sanitize_textarea_field((string) $request->get_param('remark')) : null;

        if (!$user_id) {
            return new WP_Error(
                'manfiet_unauthorized',
                __('Unauthorized request.', 'manfi-expense-tracker'),
                ['status' => 401]
            );
        }

        if ($amount <= 0) {
            return new WP_Error(
                'manfiet_invalid_expense_amount',
                __('Expense amount must be greater than zero.', 'manfi-expense-tracker'),
                ['status' => 400]
            );
        }

        if (!$this->is_valid_date($date)) {
            return new WP_Error(
                'manfiet_invalid_expense_date',
                __('Expense date must be in YYYY-MM-DD format.', 'manfi-expense-tracker'),
                ['status' => 400]
            );
        }

        $resolved_category = $this->resolve_expense_category_value($request, $user_id);
        if (is_wp_error($resolved_category)) {
            return $resolved_category;
        }

        if (!empty($tag_names)) {
            $tag_owner_id = $this->user_can_manage_expenses() ? 0 : $user_id;
            $this->ensure_tags_exist($tag_names, $tag_owner_id);
        }

        $expense_data = [
            'user_id' => $user_id,
            'category' => $resolved_category['category_name'],
            'amount' => $amount,
            'date' => $date,
            'recurring_interval' => !empty($recurring_interval) ? $recurring_interval : null,
        ];

        $expense_format = ['%d', '%s', '%f', '%s', '%s'];

        if ($has_tags_column) {
            $expense_data['tags'] = wp_json_encode($tag_names);
            $expense_format[] = '%s';
        }

        if ($has_remark_column) {
            $expense_data['remark'] = $remark;
            $expense_format[] = '%s';
        }

        // Insert into plugin-owned expenses table.
        $inserted = $wpdb->insert(
            $wpdb->prefix . 'manfiet_expenses',
            $expense_data,
            $expense_format
        );

        if ($inserted === false) {
            return new WP_Error(
                'manfiet_expense_insert_failed',
                __('Failed to add expense.', 'manfi-expense-tracker'),
                ['status' => 500]
            );
        }

        return rest_ensure_response([
            'message' => __('Expense added successfully.', 'manfi-expense-tracker'),
            'id' => (int) $wpdb->insert_id,
            'category_id' => (int) $resolved_category['category_id'],
        ]);
    }

    public function add_expenses_batch(WP_REST_Request $request) {
        global $wpdb;
        $user_id = get_current_user_id();

        if (!$user_id) {
            return new WP_Error(
                'manfiet_unauthorized',
                __('Unauthorized request.', 'manfi-expense-tracker'),
                ['status' => 401]
            );
        }

        $expenses = $request->get_param('expenses');
        if (!is_array($expenses) || empty($expenses)) {
            return new WP_Error(
                'manfiet_invalid_batch',
                __('Expenses batch must be a non-empty array.', 'manfi-expense-tracker'),
                ['status' => 400]
            );
        }

        $has_tags_column = $this->expenses_table_supports_tags();
        $has_remark_column = $this->expenses_table_supports_remark();
        $inserted_ids = [];

        // Transaction block to ensure atomic batch insert
        $wpdb->query('START TRANSACTION');

        foreach ($expenses as $index => $expense_item) {
            $amount = isset($expense_item['amount']) ? (float) $expense_item['amount'] : 0.0;
            $date = isset($expense_item['date']) ? sanitize_text_field((string) $expense_item['date']) : '';
            $recurring_interval = isset($expense_item['recurring_interval']) ? sanitize_text_field((string) $expense_item['recurring_interval']) : '';
            $raw_tags = isset($expense_item['tags']) ? $expense_item['tags'] : [];
            $tag_names = $this->normalize_tags_array($raw_tags);
            $remark = isset($expense_item['remark']) ? sanitize_textarea_field((string) $expense_item['remark']) : null;

            if ($amount <= 0) {
                $wpdb->query('ROLLBACK');
                return new WP_Error(
                    'manfiet_invalid_expense_amount',
                    /* translators: %d: Expense item index. */
                    sprintf(__('Expense #%d: Amount must be greater than zero.', 'manfi-expense-tracker'), $index + 1),
                    ['status' => 400]
                );
            }

            if (!$this->is_valid_date($date)) {
                $wpdb->query('ROLLBACK');
                return new WP_Error(
                    'manfiet_invalid_expense_date',
                    /* translators: %d: Expense item index. */
                    sprintf(__('Expense #%d: Date is invalid or not in YYYY-MM-DD format.', 'manfi-expense-tracker'), $index + 1),
                    ['status' => 400]
                );
            }

            // Resolve Category
            $category_id = isset($expense_item['category_id']) ? absint($expense_item['category_id']) : 0;
            $category_name = isset($expense_item['category']) ? sanitize_text_field((string) $expense_item['category']) : '';

            if ($category_id > 0) {
                $category = $this->get_accessible_category($category_id, $user_id);
                if (!$category) {
                    $wpdb->query('ROLLBACK');
                    return new WP_Error(
                        'manfiet_expense_category_not_found',
                        /* translators: %d: Expense item index. */
                        sprintf(__('Expense #%d: Selected category not found.', 'manfi-expense-tracker'), $index + 1),
                        ['status' => 400]
                    );
                }
                $resolved_category_name = (string) $category['path_name'];
            } else {
                if ($category_name === '') {
                    $wpdb->query('ROLLBACK');
                    return new WP_Error(
                        'manfiet_empty_expense_category',
                        /* translators: %d: Expense item index. */
                        sprintf(__('Expense #%d: Category is required.', 'manfi-expense-tracker'), $index + 1),
                        ['status' => 400]
                    );
                }

                $category_owner_id = $this->user_can_manage_expenses() ? 0 : $user_id;
                $new_category_id = $this->ensure_category_exists($category_name, $category_owner_id, 0);

                if ($new_category_id === false) {
                    $wpdb->query('ROLLBACK');
                    return new WP_Error(
                        'manfiet_expense_category_register_failed',
                        /* translators: %d: Expense item index. */
                        sprintf(__('Expense #%d: Failed to register the category.', 'manfi-expense-tracker'), $index + 1),
                        ['status' => 500]
                    );
                }
                $resolved_category_name = $category_name;
            }

            if (!empty($tag_names)) {
                $tag_owner_id = $this->user_can_manage_expenses() ? 0 : $user_id;
                $this->ensure_tags_exist($tag_names, $tag_owner_id);
            }

            $expense_data = [
                'user_id' => $user_id,
                'category' => $resolved_category_name,
                'amount' => $amount,
                'date' => $date,
                'recurring_interval' => !empty($recurring_interval) ? $recurring_interval : null,
            ];

            $expense_format = ['%d', '%s', '%f', '%s', '%s'];

            if ($has_tags_column) {
                $expense_data['tags'] = wp_json_encode($tag_names);
                $expense_format[] = '%s';
            }

            if ($has_remark_column) {
                $expense_data['remark'] = $remark;
                $expense_format[] = '%s';
            }

            $inserted = $wpdb->insert(
                $wpdb->prefix . 'manfiet_expenses',
                $expense_data,
                $expense_format
            );

            if ($inserted === false) {
                $wpdb->query('ROLLBACK');
                return new WP_Error(
                    'manfiet_expense_insert_failed',
                    /* translators: %d: Expense item index. */
                    sprintf(__('Expense #%d: Failed to save to database.', 'manfi-expense-tracker'), $index + 1),
                    ['status' => 500]
                );
            }

            $inserted_ids[] = (int) $wpdb->insert_id;
        }

        $wpdb->query('COMMIT');

        return rest_ensure_response([
            'success' => true,
            'message' => sprintf(
                /* translators: %d: Number of expenses. */
                __('%d expenses added successfully.', 'manfi-expense-tracker'),
                count($inserted_ids)
            ),
            'ids' => $inserted_ids,
        ]);
    }

    public function update_expense(WP_REST_Request $request) {
        global $wpdb;

        $expense_id = absint($request->get_param('id'));
        $user_id = get_current_user_id();
        $has_tags_column = $this->expenses_table_supports_tags();
        $has_remark_column = $this->expenses_table_supports_remark();

        // Read-only lookup in plugin-owned expenses table.
        $expense = $wpdb->get_row(
            $wpdb->prepare(
                "SELECT * FROM {$wpdb->prefix}manfiet_expenses WHERE id = %d",
                $expense_id
            ),
            ARRAY_A
        );

        if (!$expense) {
            return new WP_Error(
                'manfiet_expense_not_found',
                __('Expense not found.', 'manfi-expense-tracker'),
                ['status' => 404]
            );
        }

        if (!$this->user_can_manage_expenses() && (int) $expense['user_id'] !== $user_id) {
            return new WP_Error(
                'manfiet_expense_update_forbidden',
                __('You are not allowed to edit this expense.', 'manfi-expense-tracker'),
                ['status' => 403]
            );
        }

        $amount = (float) $request->get_param('amount');
        $date = sanitize_text_field((string) $request->get_param('date'));
        $recurring_interval = sanitize_text_field((string) $request->get_param('recurring_interval'));
        $raw_tags = $request->get_param('tags');
        $tag_names = $raw_tags === null ? $this->decode_tags_from_db($expense['tags'] ?? '') : $this->normalize_tags_array($raw_tags);
        $remark_param = $request->get_param('remark');
        $remark = $remark_param === null ? ($expense['remark'] ?? null) : sanitize_textarea_field((string) $remark_param);

        if ($amount <= 0) {
            return new WP_Error(
                'manfiet_invalid_expense_amount',
                __('Expense amount must be greater than zero.', 'manfi-expense-tracker'),
                ['status' => 400]
            );
        }

        if (!$this->is_valid_date($date)) {
            return new WP_Error(
                'manfiet_invalid_expense_date',
                __('Expense date must be in YYYY-MM-DD format.', 'manfi-expense-tracker'),
                ['status' => 400]
            );
        }

        $resolved_category = $this->resolve_expense_category_value($request, $user_id);
        if (is_wp_error($resolved_category)) {
            return $resolved_category;
        }

        if (!empty($tag_names)) {
            $tag_owner_id = $this->user_can_manage_expenses() ? 0 : $user_id;
            $this->ensure_tags_exist($tag_names, $tag_owner_id);
        }

        $update_data = [
            'category' => $resolved_category['category_name'],
            'amount' => $amount,
            'date' => $date,
            'recurring_interval' => !empty($recurring_interval) ? $recurring_interval : null,
        ];

        $update_format = ['%s', '%f', '%s', '%s'];

        if ($has_tags_column) {
            $update_data['tags'] = wp_json_encode($tag_names);
            $update_format[] = '%s';
        }

        if ($has_remark_column) {
            $update_data['remark'] = $remark;
            $update_format[] = '%s';
        }

        // Update in plugin-owned expenses table.
        $updated = $wpdb->update(
            $wpdb->prefix . 'manfiet_expenses',
            $update_data,
            ['id' => $expense_id],
            $update_format,
            ['%d']
        );

        if ($updated === false) {
            return new WP_Error(
                'manfiet_expense_update_failed',
                __('Failed to update expense.', 'manfi-expense-tracker'),
                ['status' => 500]
            );
        }

        return rest_ensure_response([
            'message' => __('Expense updated successfully.', 'manfi-expense-tracker'),
            'id' => $expense_id,
            'category_id' => (int) $resolved_category['category_id'],
        ]);
    }

    public function delete_expense(WP_REST_Request $request) {
        global $wpdb;

        $expense_id = absint($request->get_param('id'));
        $user_id = get_current_user_id();

        // Read-only lookup in plugin-owned expenses table.
        $expense = $wpdb->get_row(
            $wpdb->prepare(
                "SELECT id, user_id FROM {$wpdb->prefix}manfiet_expenses WHERE id = %d",
                $expense_id
            ),
            ARRAY_A
        );

        if (!$expense) {
            return new WP_Error(
                'manfiet_expense_not_found',
                __('Expense not found.', 'manfi-expense-tracker'),
                ['status' => 404]
            );
        }

        if (!$this->user_can_manage_expenses() && (int) $expense['user_id'] !== $user_id) {
            return new WP_Error(
                'manfiet_expense_delete_forbidden',
                __('You are not allowed to delete this expense.', 'manfi-expense-tracker'),
                ['status' => 403]
            );
        }

        $deleted = $wpdb->delete(
            $wpdb->prefix . 'manfiet_expenses',
            ['id' => $expense_id],
            ['%d']
        );

        if ($deleted === false) {
            return new WP_Error(
                'manfiet_expense_delete_failed',
                __('Failed to delete expense.', 'manfi-expense-tracker'),
                ['status' => 500]
            );
        }

        return rest_ensure_response([
            'message' => __('Expense deleted successfully.', 'manfi-expense-tracker'),
        ]);
    }

    public function update_category(WP_REST_Request $request) {
        global $wpdb;

        $user_id = get_current_user_id();
        $category_id = absint($request->get_param('id'));
        $new_name = sanitize_text_field((string) $request->get_param('category_name'));
        $description = $request->get_param('description');
        $has_parent_id = $this->categories_table_supports_parent_id();
        $has_description = $this->categories_table_supports_description();

        if ($has_parent_id) {
            // Read-only lookup in plugin-owned categories table.
            $current_category = $wpdb->get_row(
                $wpdb->prepare(
                    "SELECT id, user_id, category_name, parent_id FROM {$wpdb->prefix}manfiet_expense_categories WHERE id = %d",
                    $category_id
                ),
                ARRAY_A
            );
        } else {
            // Read-only lookup in plugin-owned categories table.
            $current_category = $wpdb->get_row(
                $wpdb->prepare(
                    "SELECT id, user_id, category_name, 0 AS parent_id FROM {$wpdb->prefix}manfiet_expense_categories WHERE id = %d",
                    $category_id
                ),
                ARRAY_A
            );
        }

        if (!$current_category) {
            return new WP_Error(
                'manfiet_category_not_found',
                __('Category not found.', 'manfi-expense-tracker'),
                ['status' => 404]
            );
        }

        $owner_id = (int) $current_category['user_id'];
        if (!$this->user_can_manage_expenses() && $owner_id !== $user_id) {
            return new WP_Error(
                'manfiet_category_update_forbidden',
                __('You are not allowed to edit this category.', 'manfi-expense-tracker'),
                ['status' => 403]
            );
        }

        $is_global_param = $request->get_param('is_global');
        if ($is_global_param !== null && (bool) $is_global_param && !$this->user_can_manage_expenses()) {
            return new WP_Error(
                'manfiet_forbidden_global_category',
                __('You are not allowed to set global categories.', 'manfi-expense-tracker'),
                ['status' => 403]
            );
        }

        $new_user_id = $owner_id;
        if ($is_global_param !== null) {
            if ((bool) $is_global_param) {
                $new_user_id = 0;
            } else {
                $new_user_id = ($owner_id === 0) ? $user_id : $owner_id;
            }
        }

        if ($new_name === '') {
            return new WP_Error(
                'manfiet_empty_category',
                __('Category name is required.', 'manfi-expense-tracker'),
                ['status' => 400]
            );
        }

        $new_parent_id = $has_parent_id ? absint($request->get_param('parent_id')) : 0;
        if ($new_parent_id === $category_id) {
            return new WP_Error(
                'manfiet_invalid_parent_category',
                __('A category cannot be its own parent.', 'manfi-expense-tracker'),
                ['status' => 400]
            );
        }

        $owner_categories = $this->get_categories_for_owner($owner_id, $has_parent_id);

        if ($has_parent_id && $new_parent_id > 0) {
            $parent_exists = false;
            foreach ($owner_categories as $owner_category) {
                if ((int) $owner_category['id'] === $new_parent_id) {
                    $parent_exists = true;
                    break;
                }
            }

            if (!$parent_exists) {
                return new WP_Error(
                    'manfiet_invalid_parent_category',
                    __('Selected parent category was not found.', 'manfi-expense-tracker'),
                    ['status' => 400]
                );
            }

            if ($this->is_descendant_category($new_parent_id, $category_id, $owner_categories)) {
                return new WP_Error(
                    'manfiet_category_cycle_detected',
                    __('Cannot move a category under its own child.', 'manfi-expense-tracker'),
                    ['status' => 400]
                );
            }
        }

        if ($has_parent_id) {
            // Read-only duplicate check in plugin-owned categories table.
            $duplicate_id = $wpdb->get_var(
                $wpdb->prepare(
                    "SELECT id FROM {$wpdb->prefix}manfiet_expense_categories WHERE user_id = %d AND parent_id = %d AND category_name = %s AND id != %d",
                    $owner_id,
                    $new_parent_id,
                    $new_name,
                    $category_id
                )
            );
        } else {
            // Read-only duplicate check in plugin-owned categories table.
            $duplicate_id = $wpdb->get_var(
                $wpdb->prepare(
                    "SELECT id FROM {$wpdb->prefix}manfiet_expense_categories WHERE user_id = %d AND category_name = %s AND id != %d",
                    $owner_id,
                    $new_name,
                    $category_id
                )
            );
        }

        if ($duplicate_id) {
            return new WP_Error(
                'manfiet_category_already_exists',
                __('A category with this name already exists under the selected parent.', 'manfi-expense-tracker'),
                ['status' => 400]
            );
        }

        $before_paths = $this->build_category_path_map($owner_categories);

        $update_data = [
            'category_name' => $new_name,
            'user_id' => $new_user_id,
        ];
        $update_format = ['%s', '%d'];

        if ($has_parent_id) {
            $update_data['parent_id'] = $new_parent_id;
            $update_format[] = '%d';
        }

        if ($has_description && $description !== null) {
            $update_data['description'] = sanitize_textarea_field($description);
            $update_format[] = '%s';
        }

        // Update categories table.
        $updated = $wpdb->update(
            $wpdb->prefix . 'manfiet_expense_categories',
            $update_data,
            ['id' => $category_id],
            $update_format,
            ['%d']
        );

        if ($updated === false) {
            return new WP_Error(
                'manfiet_category_update_failed',
                __('Failed to update category.', 'manfi-expense-tracker'),
                ['status' => 500]
            );
        }

        $after_categories = $this->get_categories_for_owner($owner_id, $has_parent_id);
        $after_paths = $this->build_category_path_map($after_categories);

        foreach ($before_paths as $id => $old_path) {
            if (!isset($after_paths[$id])) {
                continue;
            }

            $new_path = (string) $after_paths[$id];
            if ($old_path === $new_path || $old_path === '') {
                continue;
            }

            if ($owner_id === 0) {
                    // Bulk update in plugin-owned expenses table for global category path rename.
                $wpdb->update(
                    $wpdb->prefix . 'manfiet_expenses',
                    ['category' => $new_path],
                    ['category' => $old_path],
                    ['%s'],
                    ['%s']
                );
            } else {
                    // Bulk update in plugin-owned expenses table for owner-scoped category path rename.
                $wpdb->query(
                    $wpdb->prepare(
                        "UPDATE {$wpdb->prefix}manfiet_expenses SET category = %s WHERE category = %s AND user_id = %d",
                        $new_path,
                        $old_path,
                        $owner_id
                    )
                );
            }
        }

        $updated_category = $this->get_accessible_category($category_id, $user_id);

        return rest_ensure_response([
            'message' => __('Category updated successfully.', 'manfi-expense-tracker'),
            'category' => $updated_category,
        ]);
    }

    public function delete_tag(WP_REST_Request $request) {
        global $wpdb;

        $user_id = get_current_user_id();
        $tag_id = absint($request->get_param('id'));

        if (!$tag_id) {
            return new WP_Error(
                'manfiet_invalid_tag_id',
                __('Invalid tag ID.', 'manfi-expense-tracker'),
                ['status' => 400]
            );
        }

        // Read-only lookup in plugin-owned tags table.
        $tag = $wpdb->get_row(
            $wpdb->prepare(
                "SELECT id, user_id, tag_name FROM {$wpdb->prefix}manfiet_expense_tags WHERE id = %d",
                $tag_id
            ),
            ARRAY_A
        );

        if (!$tag) {
            return new WP_Error(
                'manfiet_tag_not_found',
                __('Tag not found.', 'manfi-expense-tracker'),
                ['status' => 404]
            );
        }

        $tag_owner_id = (int) $tag['user_id'];

        if (!$this->user_can_manage_expenses() && $tag_owner_id !== $user_id) {
            return new WP_Error(
                'manfiet_tag_delete_forbidden',
                __('You are not allowed to delete this tag.', 'manfi-expense-tracker'),
                ['status' => 403]
            );
        }

        // Delete from plugin-owned tags table.
        $deleted = $wpdb->delete(
            $wpdb->prefix . 'manfiet_expense_tags',
            ['id' => $tag_id],
            ['%d']
        );

        if ($deleted === false) {
            return new WP_Error(
                'manfiet_tag_delete_failed',
                __('Failed to delete tag.', 'manfi-expense-tracker'),
                ['status' => 500]
            );
        }

        return rest_ensure_response(['message' => __('Tag deleted successfully.', 'manfi-expense-tracker')]);
    }

    public function delete_category(WP_REST_Request $request) {
        global $wpdb;
        $user_id = get_current_user_id();
        $category_id = absint($request->get_param('id'));

        if (!$category_id) {
            return new WP_Error(
                'manfiet_invalid_category_id',
                __('Invalid category ID.', 'manfi-expense-tracker'),
                ['status' => 400]
            );
        }

        // Read-only lookup in plugin-owned categories table.
        $category = $wpdb->get_row(
            $wpdb->prepare(
                "SELECT id, user_id FROM {$wpdb->prefix}manfiet_expense_categories WHERE id = %d",
                $category_id
            ),
            ARRAY_A
        );

        if (!$category) {
            return new WP_Error(
                'manfiet_category_not_found',
                __('Category not found.', 'manfi-expense-tracker'),
                ['status' => 404]
            );
        }

        $category_owner_id = (int) $category['user_id'];

        if (!$this->user_can_manage_expenses() && $category_owner_id !== $user_id) {
            return new WP_Error(
                'manfiet_category_delete_forbidden',
                __('You are not allowed to delete this category.', 'manfi-expense-tracker'),
                ['status' => 403]
            );
        }

        $child_count = 0;
        if ($this->categories_table_supports_parent_id()) {
            // Read-only child count in plugin-owned categories table.
            $child_count = (int) $wpdb->get_var(
                $wpdb->prepare(
                    "SELECT COUNT(*) FROM {$wpdb->prefix}manfiet_expense_categories WHERE parent_id = %d",
                    $category_id
                )
            );
        }

        if ($child_count > 0) {
            return new WP_Error(
                'manfiet_category_has_children',
                __('Please delete or reassign child categories first.', 'manfi-expense-tracker'),
                ['status' => 400]
            );
        }

        // Delete from plugin-owned categories table.
        $deleted = $wpdb->delete(
            $wpdb->prefix . 'manfiet_expense_categories',
            [
                'id' => $category_id,
            ],
            ['%d']
        );

        if ($deleted === false) {
            return new WP_Error(
                'manfiet_category_delete_failed',
                __('Failed to delete category.', 'manfi-expense-tracker'),
                ['status' => 500]
            );
        }

        return rest_ensure_response(['message' => __('Category deleted successfully.', 'manfi-expense-tracker')]);
    }

    // phpcs:enable 
}
