<?php

/**
 * REST API endpoints for KleinBooks
 */
if ( !defined( 'ABSPATH' ) ) {
    exit;
}
class KleinBooks_REST_API {
    const NAMESPACE = 'kleinbooks/v1';

    public static function register_routes() {
        // Dashboard stats
        register_rest_route( self::NAMESPACE, '/dashboard', array(
            'methods'             => 'GET',
            'callback'            => array(__CLASS__, 'get_dashboard_stats'),
            'permission_callback' => array(__CLASS__, 'check_permission'),
        ) );
        // Clients
        register_rest_route( self::NAMESPACE, '/clients', array(array(
            'methods'             => 'GET',
            'callback'            => array(__CLASS__, 'get_clients'),
            'permission_callback' => array(__CLASS__, 'check_permission'),
        ), array(
            'methods'             => 'POST',
            'callback'            => array(__CLASS__, 'create_client'),
            'permission_callback' => array(__CLASS__, 'check_permission'),
        )) );
        register_rest_route( self::NAMESPACE, '/clients/(?P<id>\\d+)', array(array(
            'methods'             => 'GET',
            'callback'            => array(__CLASS__, 'get_client'),
            'permission_callback' => array(__CLASS__, 'check_permission'),
        ), array(
            'methods'             => 'PUT',
            'callback'            => array(__CLASS__, 'update_client'),
            'permission_callback' => array(__CLASS__, 'check_permission'),
        ), array(
            'methods'             => 'DELETE',
            'callback'            => array(__CLASS__, 'delete_client'),
            'permission_callback' => array(__CLASS__, 'check_permission'),
        )) );
        // Invoices
        register_rest_route( self::NAMESPACE, '/invoices', array(array(
            'methods'             => 'GET',
            'callback'            => array(__CLASS__, 'get_invoices'),
            'permission_callback' => array(__CLASS__, 'check_permission'),
        ), array(
            'methods'             => 'POST',
            'callback'            => array(__CLASS__, 'create_invoice'),
            'permission_callback' => array(__CLASS__, 'check_permission'),
        )) );
        register_rest_route( self::NAMESPACE, '/invoices/(?P<id>\\d+)', array(array(
            'methods'             => 'GET',
            'callback'            => array(__CLASS__, 'get_invoice'),
            'permission_callback' => array(__CLASS__, 'check_permission'),
        ), array(
            'methods'             => 'PUT',
            'callback'            => array(__CLASS__, 'update_invoice'),
            'permission_callback' => array(__CLASS__, 'check_permission'),
        ), array(
            'methods'             => 'DELETE',
            'callback'            => array(__CLASS__, 'delete_invoice'),
            'permission_callback' => array(__CLASS__, 'check_permission'),
        )) );
        register_rest_route( self::NAMESPACE, '/invoices/(?P<id>\\d+)/status', array(
            'methods'             => 'PATCH',
            'callback'            => array(__CLASS__, 'update_invoice_status'),
            'permission_callback' => array(__CLASS__, 'check_permission'),
        ) );
        register_rest_route( self::NAMESPACE, '/invoices/(?P<id>\\d+)/pdf', array(
            'methods'             => 'GET',
            'callback'            => array(__CLASS__, 'get_invoice_pdf'),
            'permission_callback' => array(__CLASS__, 'check_permission'),
        ) );
        register_rest_route( self::NAMESPACE, '/invoices/(?P<id>\\d+)/pdf/preview', array(
            'methods'             => 'GET',
            'callback'            => array(__CLASS__, 'preview_invoice_pdf'),
            'permission_callback' => array(__CLASS__, 'check_permission'),
        ) );
        register_rest_route( self::NAMESPACE, '/invoices/(?P<id>\\d+)/pdf/download', array(
            'methods'             => 'GET',
            'callback'            => array(__CLASS__, 'download_invoice_pdf'),
            'permission_callback' => array(__CLASS__, 'check_permission'),
        ) );
        register_rest_route( self::NAMESPACE, '/invoices/(?P<id>\\d+)/send-email', array(
            'methods'             => 'POST',
            'callback'            => array(__CLASS__, 'send_invoice_email'),
            'permission_callback' => array(__CLASS__, 'check_permission'),
        ) );
        register_rest_route( self::NAMESPACE, '/invoices/next-number', array(
            'methods'             => 'GET',
            'callback'            => array(__CLASS__, 'get_next_invoice_number'),
            'permission_callback' => array(__CLASS__, 'check_permission'),
        ) );
        // Settings
        register_rest_route( self::NAMESPACE, '/settings', array(array(
            'methods'             => 'GET',
            'callback'            => array(__CLASS__, 'get_settings'),
            'permission_callback' => array(__CLASS__, 'check_permission'),
        ), array(
            'methods'             => 'PUT',
            'callback'            => array(__CLASS__, 'update_settings'),
            'permission_callback' => array(__CLASS__, 'check_permission'),
        )) );
        // Company logo (upload / remove)
        register_rest_route( self::NAMESPACE, '/settings/logo', array(
            'methods'             => 'POST',
            'callback'            => array(__CLASS__, 'upload_logo'),
            'permission_callback' => array(__CLASS__, 'check_permission'),
        ) );
        register_rest_route( self::NAMESPACE, '/settings/logo', array(
            'methods'             => 'DELETE',
            'callback'            => array(__CLASS__, 'delete_logo'),
            'permission_callback' => array(__CLASS__, 'check_permission'),
        ) );
        // Reports
        register_rest_route( self::NAMESPACE, '/reports/yearly', array(
            'methods'             => 'GET',
            'callback'            => array(__CLASS__, 'get_yearly_report'),
            'permission_callback' => array(__CLASS__, 'check_permission'),
        ) );
        register_rest_route( self::NAMESPACE, '/reports/monthly', array(
            'methods'             => 'GET',
            'callback'            => array(__CLASS__, 'get_monthly_report'),
            'permission_callback' => array(__CLASS__, 'check_permission'),
        ) );
        // Expenses
        register_rest_route( self::NAMESPACE, '/expenses', array(array(
            'methods'             => 'GET',
            'callback'            => array(__CLASS__, 'get_expenses'),
            'permission_callback' => array(__CLASS__, 'check_permission'),
        ), array(
            'methods'             => 'POST',
            'callback'            => array(__CLASS__, 'create_expense'),
            'permission_callback' => array(__CLASS__, 'check_permission'),
        )) );
        register_rest_route( self::NAMESPACE, '/expenses/stats', array(
            'methods'             => 'GET',
            'callback'            => array(__CLASS__, 'get_expenses_stats'),
            'permission_callback' => array(__CLASS__, 'check_permission'),
        ) );
        register_rest_route( self::NAMESPACE, '/expenses/categories', array(
            'methods'             => 'GET',
            'callback'            => array(__CLASS__, 'get_expense_categories'),
            'permission_callback' => array(__CLASS__, 'check_permission'),
        ) );
        register_rest_route( self::NAMESPACE, '/expenses/(?P<id>\\d+)', array(array(
            'methods'             => 'GET',
            'callback'            => array(__CLASS__, 'get_expense'),
            'permission_callback' => array(__CLASS__, 'check_permission'),
        ), array(
            'methods'             => 'PUT',
            'callback'            => array(__CLASS__, 'update_expense'),
            'permission_callback' => array(__CLASS__, 'check_permission'),
        ), array(
            'methods'             => 'DELETE',
            'callback'            => array(__CLASS__, 'delete_expense'),
            'permission_callback' => array(__CLASS__, 'check_permission'),
        )) );
        register_rest_route( self::NAMESPACE, '/expenses/(?P<id>\\d+)/receipt', array(
            'methods'             => 'POST',
            'callback'            => array(__CLASS__, 'upload_expense_receipt'),
            'permission_callback' => array(__CLASS__, 'check_permission'),
        ) );
        register_rest_route( self::NAMESPACE, '/expenses/(?P<id>\\d+)/receipt', array(
            'methods'             => 'DELETE',
            'callback'            => array(__CLASS__, 'delete_expense_receipt'),
            'permission_callback' => array(__CLASS__, 'check_permission'),
        ) );
        // Export endpoints
        register_rest_route( self::NAMESPACE, '/export/expenses', array(
            'methods'             => 'GET',
            'callback'            => array(__CLASS__, 'export_expenses_csv'),
            'permission_callback' => array(__CLASS__, 'check_permission'),
        ) );
        register_rest_route( self::NAMESPACE, '/export/invoices', array(
            'methods'             => 'GET',
            'callback'            => array(__CLASS__, 'export_invoices_csv'),
            'permission_callback' => array(__CLASS__, 'check_permission'),
        ) );
        // DEBUG endpoint - secured (admin only)
        register_rest_route( self::NAMESPACE, '/debug', array(
            'methods'             => 'GET',
            'callback'            => array(__CLASS__, 'get_debug_info'),
            'permission_callback' => array(__CLASS__, 'check_permission'),
        ) );
    }

    public static function check_permission() {
        return current_user_can( 'manage_options' );
    }

    // ============ DEBUG ============
    public static function get_debug_info( $request ) {
        global $wpdb;
        $invoices_table = KleinBooks_Database::get_table_name( 'invoices' );
        $clients_table = KleinBooks_Database::get_table_name( 'clients' );
        // Get all invoices with DATEV-relevant fields
        $all_invoices = $wpdb->get_results( "SELECT id, invoice_number, invoice_year, status, total,\n                    date_issued, sent_at, paid_at, cancelled_at, cancellation_reason\n             FROM {$invoices_table}\n             ORDER BY id DESC", ARRAY_A );
        // Count by status
        $status_counts = $wpdb->get_results( "SELECT status, COUNT(*) as count FROM {$invoices_table} GROUP BY status", ARRAY_A );
        // DATEV export would include (paid invoices with paid_at in range)
        $datev_eligible = $wpdb->get_results( "SELECT invoice_number, status, paid_at, invoice_year\n             FROM {$invoices_table}\n             WHERE status = 'paid' AND paid_at IS NOT NULL", ARRAY_A );
        // Storno entries (cancelled with cancelled_at)
        $storno_eligible = $wpdb->get_results( "SELECT invoice_number, status, cancelled_at, cancellation_reason\n             FROM {$invoices_table}\n             WHERE status = 'cancelled' AND cancelled_at IS NOT NULL", ARRAY_A );
        return rest_ensure_response( array(
            'invoices_table'        => $invoices_table,
            'total_invoices'        => count( $all_invoices ),
            'status_breakdown'      => $status_counts,
            'all_invoices'          => $all_invoices,
            'datev_eligible_paid'   => $datev_eligible,
            'datev_eligible_storno' => $storno_eligible,
            'note'                  => 'DATEV exports: paid invoices (by paid_at month) + storno entries (by cancelled_at month)',
        ) );
    }

    // ============ DASHBOARD ============
    public static function get_dashboard_stats( $request ) {
        global $wpdb;
        $invoices_table = KleinBooks_Database::get_table_name( 'invoices' );
        $clients_table = KleinBooks_Database::get_table_name( 'clients' );
        $expenses_table = KleinBooks_Database::get_table_name( 'expenses' );
        $current_year = date( 'Y' );
        $last_year = $current_year - 1;
        // Current year revenue
        $current_year_revenue = $wpdb->get_var( $wpdb->prepare( "SELECT COALESCE(SUM(total), 0) FROM {$invoices_table}\n             WHERE YEAR(date_issued) = %d AND status IN ('paid', 'pending', 'sent')", $current_year ) );
        // Last year revenue
        $last_year_revenue = $wpdb->get_var( $wpdb->prepare( "SELECT COALESCE(SUM(total), 0) FROM {$invoices_table}\n             WHERE YEAR(date_issued) = %d AND status IN ('paid', 'pending', 'sent')", $last_year ) );
        // Outstanding amount (issued but not yet paid = sent / legacy pending)
        $pending_amount = $wpdb->get_var( "SELECT COALESCE(SUM(total), 0) FROM {$invoices_table} WHERE status IN ('pending', 'sent')" );
        // Paid amount this year
        $paid_amount = $wpdb->get_var( $wpdb->prepare( "SELECT COALESCE(SUM(total), 0) FROM {$invoices_table}\n             WHERE YEAR(date_issued) = %d AND status = 'paid'", $current_year ) );
        // Total invoices this year (exclude cancelled/Storno so the count matches reality)
        $total_invoices = $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$invoices_table} WHERE YEAR(date_issued) = %d AND status != 'cancelled'", $current_year ) );
        // Total clients
        $total_clients = $wpdb->get_var( "SELECT COUNT(*) FROM {$clients_table}" );
        // Monthly revenue for current year
        $monthly_revenue = $wpdb->get_results( $wpdb->prepare( "SELECT MONTH(date_issued) as month, COALESCE(SUM(total), 0) as revenue\n             FROM {$invoices_table}\n             WHERE YEAR(date_issued) = %d AND status IN ('paid', 'pending', 'sent')\n             GROUP BY MONTH(date_issued)\n             ORDER BY month", $current_year ), ARRAY_A );
        // Format monthly data for chart
        $monthly_data = array_fill( 1, 12, 0 );
        foreach ( $monthly_revenue as $row ) {
            $monthly_data[(int) $row['month']] = (float) $row['revenue'];
        }
        // Monthly revenue for last year
        $last_year_monthly_revenue = $wpdb->get_results( $wpdb->prepare( "SELECT MONTH(date_issued) as month, COALESCE(SUM(total), 0) as revenue\n             FROM {$invoices_table}\n             WHERE YEAR(date_issued) = %d AND status IN ('paid', 'pending', 'sent')\n             GROUP BY MONTH(date_issued)\n             ORDER BY month", $last_year ), ARRAY_A );
        // Format last year monthly data for chart
        $last_year_monthly_data = array_fill( 1, 12, 0 );
        foreach ( $last_year_monthly_revenue as $row ) {
            $last_year_monthly_data[(int) $row['month']] = (float) $row['revenue'];
        }
        // Latest invoices
        $latest_invoices = $wpdb->get_results( "SELECT i.*, c.company_name as client_name\n             FROM {$invoices_table} i\n             LEFT JOIN {$clients_table} c ON i.client_id = c.id\n             ORDER BY i.date_issued DESC, i.id DESC\n             LIMIT 5", ARRAY_A );
        // Top clients by revenue (all time)
        $top_clients = $wpdb->get_results( "SELECT c.id, c.company_name, COUNT(i.id) as invoice_count,\n                    COALESCE(SUM(i.total), 0) as total_revenue\n             FROM {$clients_table} c\n             LEFT JOIN {$invoices_table} i ON c.id = i.client_id AND i.status IN ('paid', 'pending', 'sent')\n             GROUP BY c.id\n             ORDER BY total_revenue DESC\n             LIMIT 5", ARRAY_A );
        // Total expenses for current year
        $total_expenses = $wpdb->get_var( $wpdb->prepare( "SELECT COALESCE(SUM(amount), 0) FROM {$expenses_table} WHERE expense_year = %d", $current_year ) );
        // Kleinunternehmerregelung status
        $threshold_current = (float) KleinBooks_Database::get_setting( 'threshold_current_year', 25000 );
        $threshold_previous = (float) KleinBooks_Database::get_setting( 'threshold_previous_year', 22000 );
        // Growth percentage
        $growth = ( $last_year_revenue > 0 ? ($current_year_revenue - $last_year_revenue) / $last_year_revenue * 100 : 0 );
        return rest_ensure_response( array(
            'currentYearRevenue'     => (float) $current_year_revenue,
            'lastYearRevenue'        => (float) $last_year_revenue,
            'pendingAmount'          => (float) $pending_amount,
            'paidAmount'             => (float) $paid_amount,
            'totalInvoices'          => (int) $total_invoices,
            'totalClients'           => (int) $total_clients,
            'totalExpenses'          => (float) $total_expenses,
            'monthlyRevenue'         => array_values( $monthly_data ),
            'lastYearMonthlyRevenue' => array_values( $last_year_monthly_data ),
            'latestInvoices'         => $latest_invoices,
            'topClients'             => $top_clients,
            'growth'                 => round( $growth, 1 ),
            'kleinunternehmer'       => array(
                'currentYear'  => array(
                    'revenue'    => (float) $current_year_revenue,
                    'threshold'  => $threshold_current,
                    'percentage' => ( $threshold_current > 0 ? round( $current_year_revenue / $threshold_current * 100, 1 ) : 0 ),
                ),
                'previousYear' => array(
                    'revenue'    => (float) $last_year_revenue,
                    'threshold'  => $threshold_previous,
                    'percentage' => ( $threshold_previous > 0 ? round( $last_year_revenue / $threshold_previous * 100, 1 ) : 0 ),
                ),
            ),
            'currentYear'            => (int) $current_year,
            'systemStatus'           => array(
                'indexing' => array(
                    'status'    => 'blocked',
                    'message'   => 'Seiten sind NICHT für Google indexiert',
                    'messageEn' => 'Pages are NOT indexed by Google',
                    'pages'     => array(
                        '/kleinbooks'    => 'noindex',
                        '/kleinbooks-v2' => 'noindex',
                        '/accounting'    => 'noindex',
                    ),
                ),
                'mode'     => 'development',
                'version'  => KLEINBOOKS_VERSION,
            ),
        ) );
    }

    // ============ CLIENTS ============
    public static function get_clients( $request ) {
        global $wpdb;
        $clients_table = KleinBooks_Database::get_table_name( 'clients' );
        $invoices_table = KleinBooks_Database::get_table_name( 'invoices' );
        $search = $request->get_param( 'search' );
        $orderby = ( $request->get_param( 'orderby' ) ?: 'company_name' );
        $order = ( $request->get_param( 'order' ) ?: 'ASC' );
        $allowed_orderby = array('company_name', 'created_at', 'total_revenue');
        $orderby = ( in_array( $orderby, $allowed_orderby ) ? $orderby : 'company_name' );
        $order = ( strtoupper( $order ) === 'DESC' ? 'DESC' : 'ASC' );
        $where = '';
        if ( $search ) {
            $search = '%' . $wpdb->esc_like( $search ) . '%';
            $where = $wpdb->prepare(
                " WHERE c.company_name LIKE %s OR c.contact_name LIKE %s OR c.email LIKE %s",
                $search,
                $search,
                $search
            );
        }
        $order_clause = ( $orderby === 'total_revenue' ? "ORDER BY total_revenue {$order}" : "ORDER BY c.{$orderby} {$order}" );
        $clients = $wpdb->get_results( "SELECT c.*,\n                    COUNT(i.id) as invoice_count,\n                    COALESCE(SUM(CASE WHEN i.status IN ('paid', 'pending', 'sent') THEN i.total ELSE 0 END), 0) as total_revenue,\n                    MAX(i.date_issued) as last_invoice_date\n             FROM {$clients_table} c\n             LEFT JOIN {$invoices_table} i ON c.id = i.client_id\n             {$where}\n             GROUP BY c.id\n             {$order_clause}", ARRAY_A );
        return rest_ensure_response( $clients );
    }

    public static function get_client( $request ) {
        global $wpdb;
        $id = (int) $request['id'];
        $clients_table = KleinBooks_Database::get_table_name( 'clients' );
        $invoices_table = KleinBooks_Database::get_table_name( 'invoices' );
        $client = $wpdb->get_row( $wpdb->prepare( "SELECT c.*,\n                    COUNT(i.id) as invoice_count,\n                    COALESCE(SUM(CASE WHEN i.status IN ('paid', 'pending', 'sent') THEN i.total ELSE 0 END), 0) as total_revenue\n             FROM {$clients_table} c\n             LEFT JOIN {$invoices_table} i ON c.id = i.client_id\n             WHERE c.id = %d\n             GROUP BY c.id", $id ), ARRAY_A );
        if ( !$client ) {
            return new WP_Error('not_found', 'Client not found', array(
                'status' => 404,
            ));
        }
        // Get client invoices
        $client['invoices'] = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$invoices_table} WHERE client_id = %d ORDER BY date_issued DESC", $id ), ARRAY_A );
        return rest_ensure_response( $client );
    }

    public static function create_client( $request ) {
        global $wpdb;
        $clients_table = KleinBooks_Database::get_table_name( 'clients' );
        $data = $request->get_json_params();
        // Client code is optional and NOT exposed in the UI. When empty (every new
        // client on a fresh install) the invoice number stays clean — no suffix.
        // Only clients that already carry a code keep their legacy "_CODE" suffix.
        $client_code = sanitize_text_field( $data['client_code'] ?? '' );
        $result = $wpdb->insert( $clients_table, array(
            'company_name'  => sanitize_text_field( $data['company_name'] ?? '' ),
            'client_code'   => $client_code,
            'contact_name'  => sanitize_text_field( $data['contact_name'] ?? '' ),
            'email'         => sanitize_email( $data['email'] ?? '' ),
            'phone'         => sanitize_text_field( $data['phone'] ?? '' ),
            'address_line1' => sanitize_text_field( $data['address_line1'] ?? '' ),
            'address_line2' => sanitize_text_field( $data['address_line2'] ?? '' ),
            'city'          => sanitize_text_field( $data['city'] ?? '' ),
            'postal_code'   => sanitize_text_field( $data['postal_code'] ?? '' ),
            'country'       => sanitize_text_field( $data['country'] ?? 'Deutschland' ),
            'tax_number'    => sanitize_text_field( $data['tax_number'] ?? '' ),
            'notes'         => sanitize_textarea_field( $data['notes'] ?? '' ),
        ) );
        if ( $result === false ) {
            return new WP_Error('db_error', 'Failed to create client', array(
                'status' => 500,
            ));
        }
        return rest_ensure_response( array(
            'id'          => $wpdb->insert_id,
            'client_code' => $client_code,
            'message'     => 'Client created successfully',
        ) );
    }

    public static function update_client( $request ) {
        global $wpdb;
        $id = (int) $request['id'];
        $clients_table = KleinBooks_Database::get_table_name( 'clients' );
        $data = $request->get_json_params();
        $update_data = array();
        $fields = array(
            'company_name',
            'contact_name',
            'email',
            'phone',
            'address_line1',
            'address_line2',
            'city',
            'postal_code',
            'country',
            'tax_number',
            'notes'
        );
        foreach ( $fields as $field ) {
            if ( isset( $data[$field] ) ) {
                $update_data[$field] = ( $field === 'email' ? sanitize_email( $data[$field] ) : (( $field === 'notes' ? sanitize_textarea_field( $data[$field] ) : sanitize_text_field( $data[$field] ) )) );
            }
        }
        if ( empty( $update_data ) ) {
            return new WP_Error('no_data', 'No data to update', array(
                'status' => 400,
            ));
        }
        $result = $wpdb->update( $clients_table, $update_data, array(
            'id' => $id,
        ) );
        if ( $result === false ) {
            return new WP_Error('db_error', 'Failed to update client', array(
                'status' => 500,
            ));
        }
        return rest_ensure_response( array(
            'message' => 'Client updated successfully',
        ) );
    }

    public static function delete_client( $request ) {
        global $wpdb;
        $id = (int) $request['id'];
        $clients_table = KleinBooks_Database::get_table_name( 'clients' );
        $invoices_table = KleinBooks_Database::get_table_name( 'invoices' );
        // Check if client has invoices
        $has_invoices = $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$invoices_table} WHERE client_id = %d", $id ) );
        if ( $has_invoices > 0 ) {
            return new WP_Error('has_invoices', 'Cannot delete client with existing invoices', array(
                'status' => 400,
            ));
        }
        $result = $wpdb->delete( $clients_table, array(
            'id' => $id,
        ) );
        if ( $result === false ) {
            return new WP_Error('db_error', 'Failed to delete client', array(
                'status' => 500,
            ));
        }
        return rest_ensure_response( array(
            'message' => 'Client deleted successfully',
        ) );
    }

    // ============ INVOICES ============
    public static function get_invoices( $request ) {
        global $wpdb;
        $invoices_table = KleinBooks_Database::get_table_name( 'invoices' );
        $clients_table = KleinBooks_Database::get_table_name( 'clients' );
        $offers_table = KleinBooks_Database::get_table_name( 'offers' );
        $status = $request->get_param( 'status' );
        $client_id = $request->get_param( 'client_id' );
        $year = $request->get_param( 'year' );
        $search = $request->get_param( 'search' );
        $orderby = ( $request->get_param( 'orderby' ) ?: 'id' );
        $order = ( $request->get_param( 'order' ) ?: 'DESC' );
        $allowed_orderby = array(
            'id',
            'date_issued',
            'invoice_number',
            'total',
            'status',
            'created_at'
        );
        $orderby = ( in_array( $orderby, $allowed_orderby ) ? $orderby : 'id' );
        $order = ( strtoupper( $order ) === 'ASC' ? 'ASC' : 'DESC' );
        $where = array('1=1');
        $params = array();
        if ( $status ) {
            $where[] = 'i.status = %s';
            $params[] = $status;
        }
        if ( $client_id ) {
            $where[] = 'i.client_id = %d';
            $params[] = (int) $client_id;
        }
        if ( $year ) {
            $where[] = 'YEAR(i.date_issued) = %d';
            $params[] = (int) $year;
        }
        if ( $search ) {
            $search_like = '%' . $wpdb->esc_like( $search ) . '%';
            $where[] = '(i.invoice_number LIKE %s OR c.company_name LIKE %s)';
            $params[] = $search_like;
            $params[] = $search_like;
        }
        $where_clause = implode( ' AND ', $where );
        // Reverse cross-link: also return the source offer's id so the back-link badge
        // can navigate to the offer (J.2 — bidirectional cross-link).
        $query = "SELECT i.*, c.company_name as client_name, c.email as client_email,\n                  (SELECT offer_number FROM {$offers_table} WHERE converted_invoice_id = i.id ORDER BY id DESC LIMIT 1) as source_offer_number,\n                  (SELECT id FROM {$offers_table} WHERE converted_invoice_id = i.id ORDER BY id DESC LIMIT 1) as source_offer_id\n                  FROM {$invoices_table} i\n                  LEFT JOIN {$clients_table} c ON i.client_id = c.id\n                  WHERE {$where_clause}\n                  ORDER BY i.{$orderby} {$order}";
        if ( !empty( $params ) ) {
            $query = $wpdb->prepare( $query, $params );
        }
        $invoices = $wpdb->get_results( $query, ARRAY_A );
        return rest_ensure_response( $invoices );
    }

    public static function get_invoice( $request ) {
        global $wpdb;
        $id = (int) $request['id'];
        $invoices_table = KleinBooks_Database::get_table_name( 'invoices' );
        $invoice_items_table = KleinBooks_Database::get_table_name( 'invoice_items' );
        $clients_table = KleinBooks_Database::get_table_name( 'clients' );
        $invoice = $wpdb->get_row( $wpdb->prepare( "SELECT i.*, c.company_name as client_name, c.email as client_email,\n                    c.address_line1 as client_address, c.city as client_city,\n                    c.postal_code as client_postal_code, c.country as client_country\n             FROM {$invoices_table} i\n             LEFT JOIN {$clients_table} c ON i.client_id = c.id\n             WHERE i.id = %d", $id ), ARRAY_A );
        if ( !$invoice ) {
            return new WP_Error('not_found', 'Invoice not found', array(
                'status' => 404,
            ));
        }
        // Get invoice items
        $invoice['items'] = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$invoice_items_table} WHERE invoice_id = %d ORDER BY sort_order, id", $id ), ARRAY_A );
        // Reverse cross-link: the offer this invoice was converted from, if any.
        // Both id + number so the back-link badge can navigate (J.2).
        $offers_table = KleinBooks_Database::get_table_name( 'offers' );
        $source_offer = $wpdb->get_row( $wpdb->prepare( "SELECT id, offer_number FROM {$offers_table} WHERE converted_invoice_id = %d ORDER BY id DESC LIMIT 1", $id ), ARRAY_A );
        $invoice['source_offer_id'] = ( $source_offer ? (int) $source_offer['id'] : null );
        $invoice['source_offer_number'] = ( $source_offer ? $source_offer['offer_number'] : null );
        return rest_ensure_response( $invoice );
    }

    public static function create_invoice( $request ) {
        global $wpdb;
        $invoices_table = KleinBooks_Database::get_table_name( 'invoices' );
        $invoice_items_table = KleinBooks_Database::get_table_name( 'invoice_items' );
        $clients_table = KleinBooks_Database::get_table_name( 'clients' );
        $data = $request->get_json_params();
        // Get client code for invoice number
        $client_id = (int) $data['client_id'];
        $client = $wpdb->get_row( $wpdb->prepare( "SELECT client_code FROM {$clients_table} WHERE id = %d", $client_id ) );
        $client_code = ( $client ? $client->client_code : '' );
        // Determine invoice year from date_issued
        $date_issued = $data['date_issued'] ?? date( 'Y-m-d' );
        $invoice_year = (int) date( 'Y', strtotime( $date_issued ) );
        // Generate invoice number with yearly reset: MK_XXXXX_CC
        $invoice_sequence = 1;
        if ( empty( $data['invoice_number'] ) ) {
            // Get next sequence number for this year
            $max_sequence = $wpdb->get_var( $wpdb->prepare( "SELECT MAX(invoice_sequence) FROM {$invoices_table} WHERE invoice_year = %d", $invoice_year ) );
            $invoice_sequence = (( $max_sequence ? (int) $max_sequence : 0 )) + 1;
            $prefix = KleinBooks_Database::get_setting( 'invoice_prefix', 'MK_' );
            $data['invoice_number'] = $prefix . str_pad(
                $invoice_sequence,
                5,
                '0',
                STR_PAD_LEFT
            ) . (( $client_code !== '' ? '_' . $client_code : '' ));
        } else {
            // Extract sequence from provided invoice number if possible
            if ( preg_match( '/(\\d{5})/', $data['invoice_number'], $matches ) ) {
                $invoice_sequence = (int) $matches[1];
            }
        }
        // Calculate totals
        $subtotal = 0;
        if ( !empty( $data['items'] ) ) {
            foreach ( $data['items'] as $item ) {
                $subtotal += (float) ($item['total'] ?? (float) ($item['quantity'] ?? 1) * (float) ($item['unit_price'] ?? 0));
            }
        }
        $tax_rate = (float) ($data['tax_rate'] ?? 0);
        $tax_amount = $subtotal * ($tax_rate / 100);
        $total = $subtotal + $tax_amount;
        // Set due date if not provided
        $payment_terms = (int) KleinBooks_Database::get_setting( 'payment_terms_days', 10 );
        $date_due = $data['date_due'] ?? date( 'Y-m-d', strtotime( $date_issued . " + {$payment_terms} days" ) );
        $result = $wpdb->insert( $invoices_table, array(
            'invoice_number'     => sanitize_text_field( $data['invoice_number'] ),
            'client_id'          => $client_id,
            'invoice_year'       => $invoice_year,
            'invoice_sequence'   => $invoice_sequence,
            'date_issued'        => $date_issued,
            'date_due'           => $date_due,
            'status'             => sanitize_text_field( $data['status'] ?? 'draft' ),
            'subtotal'           => $subtotal,
            'tax_rate'           => $tax_rate,
            'tax_amount'         => $tax_amount,
            'total'              => $total,
            'notes'              => sanitize_textarea_field( $data['notes'] ?? '' ),
            'performance_period' => sanitize_text_field( $data['performance_period'] ?? '' ),
        ) );
        if ( $result === false ) {
            return new WP_Error('db_error', 'Failed to create invoice', array(
                'status' => 500,
            ));
        }
        $invoice_id = $wpdb->insert_id;
        // Insert invoice items
        if ( !empty( $data['items'] ) ) {
            foreach ( $data['items'] as $index => $item ) {
                $quantity = (float) ($item['quantity'] ?? 1);
                $unit_price = (float) ($item['unit_price'] ?? 0);
                $item_total = (float) ($item['total'] ?? $quantity * $unit_price);
                $wpdb->insert( $invoice_items_table, array(
                    'invoice_id'  => $invoice_id,
                    'description' => sanitize_text_field( $item['description'] ?? '' ),
                    'details'     => sanitize_textarea_field( $item['details'] ?? '' ),
                    'quantity'    => $quantity,
                    'unit_price'  => $unit_price,
                    'total'       => $item_total,
                    'sort_order'  => $index,
                ) );
            }
        }
        return rest_ensure_response( array(
            'id'             => $invoice_id,
            'invoice_number' => $data['invoice_number'],
            'message'        => 'Invoice created successfully',
        ) );
    }

    public static function update_invoice( $request ) {
        global $wpdb;
        $id = (int) $request['id'];
        $invoices_table = KleinBooks_Database::get_table_name( 'invoices' );
        $invoice_items_table = KleinBooks_Database::get_table_name( 'invoice_items' );
        $data = $request->get_json_params();
        // GoBD Lock Check: Block editing if invoice has been sent
        $existing = $wpdb->get_row( $wpdb->prepare( "SELECT sent_at, status, cancelled_at FROM {$invoices_table} WHERE id = %d", $id ) );
        if ( !$existing ) {
            return new WP_Error('not_found', 'Invoice not found', array(
                'status' => 404,
            ));
        }
        // GoBD edit lock (Pro): a sent, non-draft invoice is immutable. Free leaves it
        // editable — the frontend shows a contextual upgrade nudge instead.
        if ( kb_fs()->can_use_premium_code() && $existing->sent_at !== null && $existing->status !== 'draft' ) {
            return new WP_Error('invoice_locked', __( 'Rechnung ist nach Versand gesperrt (GoBD). Bitte stornieren Sie die Rechnung stattdessen.', 'kleinbooks' ), array(
                'status' => 403,
                'code'   => 'GOBD_LOCKED',
            ));
        }
        // If invoice is cancelled, it cannot be edited
        if ( $existing->cancelled_at !== null || $existing->status === 'cancelled' ) {
            return new WP_Error('invoice_cancelled', __( 'Stornierte Rechnungen können nicht bearbeitet werden.', 'kleinbooks' ), array(
                'status' => 403,
                'code'   => 'CANCELLED',
            ));
        }
        // Cancellation must go through the Storno (/cancel) endpoint. Reject a direct
        // status change to 'cancelled' here so a normal edit can't bypass the Storno
        // workflow or leave a (free) invoice stuck in an un-editable cancelled state.
        if ( isset( $data['status'] ) && $data['status'] === 'cancelled' ) {
            return new WP_Error('use_cancel_endpoint', __( 'Zum Stornieren bitte die Stornierungsfunktion verwenden.', 'kleinbooks' ), array(
                'status' => 400,
            ));
        }
        // Calculate totals if items provided
        if ( !empty( $data['items'] ) ) {
            $subtotal = 0;
            foreach ( $data['items'] as $item ) {
                $subtotal += (float) ($item['total'] ?? (float) ($item['quantity'] ?? 1) * (float) ($item['unit_price'] ?? 0));
            }
            $data['subtotal'] = $subtotal;
            $tax_rate = (float) ($data['tax_rate'] ?? 0);
            $data['tax_amount'] = $subtotal * ($tax_rate / 100);
            $data['total'] = $subtotal + $data['tax_amount'];
        }
        $update_data = array();
        $fields = array(
            'client_id',
            'date_issued',
            'date_due',
            'status',
            'subtotal',
            'tax_rate',
            'tax_amount',
            'total',
            'notes',
            'performance_period'
        );
        foreach ( $fields as $field ) {
            if ( isset( $data[$field] ) ) {
                if ( in_array( $field, array('client_id') ) ) {
                    $update_data[$field] = (int) $data[$field];
                } elseif ( in_array( $field, array(
                    'subtotal',
                    'tax_rate',
                    'tax_amount',
                    'total'
                ) ) ) {
                    $update_data[$field] = (float) $data[$field];
                } elseif ( $field === 'notes' ) {
                    $update_data[$field] = sanitize_textarea_field( $data[$field] );
                } else {
                    $update_data[$field] = sanitize_text_field( $data[$field] );
                }
            }
        }
        if ( !empty( $update_data ) ) {
            $result = $wpdb->update( $invoices_table, $update_data, array(
                'id' => $id,
            ) );
            if ( $result === false ) {
                return new WP_Error('db_error', 'Failed to update invoice', array(
                    'status' => 500,
                ));
            }
        }
        // Update invoice items if provided
        if ( isset( $data['items'] ) ) {
            // Delete existing items
            $wpdb->delete( $invoice_items_table, array(
                'invoice_id' => $id,
            ) );
            // Insert new items
            foreach ( $data['items'] as $index => $item ) {
                $quantity = (float) ($item['quantity'] ?? 1);
                $unit_price = (float) ($item['unit_price'] ?? 0);
                $item_total = (float) ($item['total'] ?? $quantity * $unit_price);
                $wpdb->insert( $invoice_items_table, array(
                    'invoice_id'  => $id,
                    'description' => sanitize_text_field( $item['description'] ?? '' ),
                    'details'     => sanitize_textarea_field( $item['details'] ?? '' ),
                    'quantity'    => $quantity,
                    'unit_price'  => $unit_price,
                    'total'       => $item_total,
                    'sort_order'  => $index,
                ) );
            }
        }
        return rest_ensure_response( array(
            'message' => 'Invoice updated successfully',
        ) );
    }

    public static function update_invoice_status( $request ) {
        global $wpdb;
        $id = (int) $request['id'];
        $invoices_table = KleinBooks_Database::get_table_name( 'invoices' );
        $data = $request->get_json_params();
        $status = sanitize_text_field( $data['status'] ?? '' );
        $valid_statuses = array(
            'draft',
            'pending',
            'sent',
            'paid',
            'overdue',
            'cancelled'
        );
        if ( !in_array( $status, $valid_statuses ) ) {
            return new WP_Error('invalid_status', 'Invalid status', array(
                'status' => 400,
            ));
        }
        // Get current invoice state
        $existing = $wpdb->get_row( $wpdb->prepare( "SELECT sent_at, status, cancelled_at FROM {$invoices_table} WHERE id = %d", $id ) );
        if ( !$existing ) {
            return new WP_Error('not_found', 'Invoice not found', array(
                'status' => 404,
            ));
        }
        // Block status changes on cancelled invoices
        if ( $existing->cancelled_at !== null || $existing->status === 'cancelled' ) {
            return new WP_Error('invoice_cancelled', __( 'Status von stornierten Rechnungen kann nicht geändert werden.', 'kleinbooks' ), array(
                'status' => 403,
            ));
        }
        // Don't allow using 'cancelled' status via this endpoint - use /cancel instead
        if ( $status === 'cancelled' ) {
            return new WP_Error('use_cancel_endpoint', __( 'Bitte nutzen Sie die Storno-Funktion um Rechnungen zu stornieren.', 'kleinbooks' ), array(
                'status' => 400,
            ));
        }
        $update_data = array(
            'status' => $status,
        );
        // sent_at is intentionally NOT stamped on a status change. It is set only by the
        // email-send endpoint (send_invoice_email), so it reflects an actual send attempt
        // — not a manual status flip. Drawer status changes therefore no longer trigger
        // the GoBD edit/delete lock.
        // paid_at tracks the actual payment date: stamp it when marking paid,
        // clear it when reverting away from paid (e.g. "Mark as Unpaid").
        if ( $status === 'paid' ) {
            $update_data['paid_at'] = current_time( 'mysql' );
        } else {
            $update_data['paid_at'] = null;
        }
        $result = $wpdb->update( $invoices_table, $update_data, array(
            'id' => $id,
        ) );
        if ( $result === false ) {
            return new WP_Error('db_error', 'Failed to update status', array(
                'status' => 500,
            ));
        }
        // Return the full status payload so the client can update the row + open drawer
        // live without a reload (status + paid_at, plus the unchanged sent_at/cancelled_at).
        return rest_ensure_response( array(
            'message'      => 'Status updated successfully',
            'status'       => $status,
            'paid_at'      => $update_data['paid_at'],
            'sent_at'      => $existing->sent_at,
            'cancelled_at' => $existing->cancelled_at,
        ) );
    }

    public static function delete_invoice( $request ) {
        global $wpdb;
        $id = (int) $request['id'];
        $invoices_table = KleinBooks_Database::get_table_name( 'invoices' );
        $invoice_items_table = KleinBooks_Database::get_table_name( 'invoice_items' );
        // GoBD Lock Check: Block deletion if invoice has been sent
        $existing = $wpdb->get_row( $wpdb->prepare( "SELECT sent_at, status, cancelled_at FROM {$invoices_table} WHERE id = %d", $id ) );
        if ( !$existing ) {
            return new WP_Error('not_found', 'Invoice not found', array(
                'status' => 404,
            ));
        }
        // GoBD delete lock (Pro): a sent, non-draft invoice can't be deleted. Free
        // leaves it deletable — the frontend shows a contextual upgrade nudge instead.
        if ( kb_fs()->can_use_premium_code() && $existing->sent_at !== null && $existing->status !== 'draft' ) {
            return new WP_Error('invoice_locked', __( 'Gesendete Rechnungen können nicht gelöscht werden (GoBD). Bitte stornieren Sie die Rechnung stattdessen.', 'kleinbooks' ), array(
                'status' => 403,
                'code'   => 'GOBD_LOCKED',
            ));
        }
        // If invoice is cancelled, it cannot be deleted (audit trail)
        if ( $existing->cancelled_at !== null || $existing->status === 'cancelled' ) {
            return new WP_Error('invoice_cancelled', __( 'Stornierte Rechnungen können nicht gelöscht werden (Revisionssicherheit).', 'kleinbooks' ), array(
                'status' => 403,
                'code'   => 'CANCELLED',
            ));
        }
        // Remove the generated PDF file first so it isn't left orphaned
        KleinBooks_PDF_Generator::delete_pdf( $id );
        // Delete invoice items first
        $wpdb->delete( $invoice_items_table, array(
            'invoice_id' => $id,
        ) );
        // Delete the invoice
        $result = $wpdb->delete( $invoices_table, array(
            'id' => $id,
        ) );
        if ( $result === false ) {
            return new WP_Error('db_error', 'Failed to delete invoice', array(
                'status' => 500,
            ));
        }
        return rest_ensure_response( array(
            'message' => 'Invoice deleted successfully',
        ) );
    }

    public static function get_next_invoice_number( $request ) {
        global $wpdb;
        $invoices_table = KleinBooks_Database::get_table_name( 'invoices' );
        $current_year = (int) date( 'Y' );
        $prefix = KleinBooks_Database::get_setting( 'invoice_prefix', 'MK_' );
        // Get next sequence for current year
        $max_sequence = $wpdb->get_var( $wpdb->prepare( "SELECT MAX(invoice_sequence) FROM {$invoices_table} WHERE invoice_year = %d", $current_year ) );
        $next_number = (( $max_sequence ? (int) $max_sequence : 0 )) + 1;
        // Format without client code (will be added when invoice is created)
        $invoice_number = $prefix . str_pad(
            $next_number,
            5,
            '0',
            STR_PAD_LEFT
        );
        return rest_ensure_response( array(
            'next_number'    => $next_number,
            'invoice_number' => $invoice_number,
            'year'           => $current_year,
            'note'           => 'Client code (XX) will be replaced when you select a client',
        ) );
    }

    /**
     * Get PDF URL (generates if not exists)
     */
    public static function get_invoice_pdf( $request ) {
        $id = (int) $request['id'];
        try {
            // Always regenerate to use latest template
            $pdf_url = KleinBooks_PDF_Generator::regenerate( $id );
            // Add cache buster to prevent browser caching
            $pdf_url .= '?v=' . time();
            return rest_ensure_response( array(
                'pdf_url' => $pdf_url,
            ) );
        } catch ( Exception $e ) {
            error_log( '[MK-API] PDF Exception for invoice ' . $id . ': ' . $e->getMessage() );
            return rest_ensure_response( array(
                'error'   => true,
                'message' => $e->getMessage(),
            ) );
        } catch ( Error $e ) {
            error_log( '[MK-API] PDF Error for invoice ' . $id . ': ' . $e->getMessage() . ' in ' . $e->getFile() . ':' . $e->getLine() );
            return rest_ensure_response( array(
                'error'   => true,
                'message' => 'PHP Error: ' . $e->getMessage(),
            ) );
        }
    }

    /**
     * Preview PDF (stream to browser)
     */
    public static function preview_invoice_pdf( $request ) {
        $id = (int) $request['id'];
        try {
            // This will stream the PDF and exit
            KleinBooks_PDF_Generator::generate( $id, 'stream' );
        } catch ( Exception $e ) {
            error_log( '[MK-API] PDF Preview Exception: ' . $e->getMessage() );
            return new WP_Error('pdf_error', $e->getMessage(), array(
                'status' => 500,
            ));
        } catch ( Error $e ) {
            error_log( '[MK-API] PDF Preview Error: ' . $e->getMessage() );
            return new WP_Error('pdf_error', 'PHP Error: ' . $e->getMessage(), array(
                'status' => 500,
            ));
        }
    }

    /**
     * Download PDF
     */
    public static function download_invoice_pdf( $request ) {
        $id = (int) $request['id'];
        try {
            // This will download the PDF and exit
            KleinBooks_PDF_Generator::generate( $id, 'download' );
        } catch ( Exception $e ) {
            error_log( '[MK-API] PDF Download Exception: ' . $e->getMessage() );
            return new WP_Error('pdf_error', $e->getMessage(), array(
                'status' => 500,
            ));
        } catch ( Error $e ) {
            error_log( '[MK-API] PDF Download Error: ' . $e->getMessage() );
            return new WP_Error('pdf_error', 'PHP Error: ' . $e->getMessage(), array(
                'status' => 500,
            ));
        }
    }

    /**
     * Send invoice email with PDF attachment
     */
    public static function send_invoice_email( $request ) {
        global $wpdb;
        $id = (int) $request['id'];
        $invoices_table = KleinBooks_Database::get_table_name( 'invoices' );
        // First, mark invoice as sent (GoBD lock) BEFORE trying to send email
        $existing = $wpdb->get_row( $wpdb->prepare( "SELECT sent_at, status FROM {$invoices_table} WHERE id = %d", $id ) );
        if ( !$existing ) {
            return new WP_Error('not_found', 'Invoice not found', array(
                'status' => 404,
            ));
        }
        // Update sent_at if not already set (this locks the invoice)
        $update_data = array();
        if ( $existing->sent_at === null ) {
            $update_data['sent_at'] = current_time( 'mysql' );
        }
        // Sending makes the invoice "sent" (issued, awaiting payment). The legacy
        // "pending" status means the same thing now, so normalise it to "sent" too.
        if ( $existing->status === 'draft' || $existing->status === 'pending' ) {
            $update_data['status'] = 'sent';
        }
        if ( !empty( $update_data ) ) {
            $wpdb->update( $invoices_table, $update_data, array(
                'id' => $id,
            ) );
        }
        // Now try to send the email
        $email_sent = false;
        $email_error = null;
        try {
            $plugin = KleinBooks_Main::get_instance();
            $result = $plugin->send_invoice_email( $id );
            if ( is_wp_error( $result ) ) {
                $email_error = $result->get_error_message();
            } else {
                $email_sent = true;
            }
        } catch ( Exception $e ) {
            $email_error = $e->getMessage();
        }
        // Return success even if email failed - invoice is still locked
        return rest_ensure_response( array(
            'success'     => true,
            'email_sent'  => $email_sent,
            'email_error' => $email_error,
            'message'     => ( $email_sent ? __( 'Rechnung erfolgreich gesendet und gesperrt.', 'kleinbooks' ) : __( 'Rechnung gesperrt. E-Mail-Versand fehlgeschlagen (SMTP prüfen).', 'kleinbooks' ) ),
            'sent_at'     => $update_data['sent_at'] ?? $existing->sent_at,
            'status'      => $update_data['status'] ?? $existing->status,
        ) );
    }

    // ============ SETTINGS ============
    public static function get_settings( $request ) {
        $settings = KleinBooks_Database::get_all_settings();
        return rest_ensure_response( $settings );
    }

    public static function update_settings( $request ) {
        $data = $request->get_json_params();
        foreach ( $data as $key => $value ) {
            KleinBooks_Database::update_setting( sanitize_key( $key ), $value );
        }
        return rest_ensure_response( array(
            'message' => 'Settings updated successfully',
        ) );
    }

    /**
     * Upload the company logo (PNG/JPG). Stored in uploads/mk-branding/, the public URL
     * kept in the `logo_url` setting. Used additively on the invoice PDF (it never replaces
     * the legally required company name).
     */
    public static function upload_logo( $request ) {
        if ( empty( $_FILES['logo'] ) ) {
            return new WP_Error('no_file', 'No file uploaded', array(
                'status' => 400,
            ));
        }
        $file = $_FILES['logo'];
        // PNG/JPG only — keeps DomPDF + ZUGFeRD/Factur-X (PDF/A-3) rendering reliable.
        $allowed_types = array('image/jpeg', 'image/png');
        if ( !in_array( $file['type'], $allowed_types, true ) ) {
            return new WP_Error('invalid_type', 'Invalid file type. Allowed: PNG, JPG', array(
                'status' => 400,
            ));
        }
        // Max 2MB
        if ( $file['size'] > 2 * 1024 * 1024 ) {
            return new WP_Error('too_large', 'File too large. Maximum 2MB', array(
                'status' => 400,
            ));
        }
        $upload_dir = wp_upload_dir();
        $target_dir = $upload_dir['basedir'] . '/mk-branding/';
        if ( !file_exists( $target_dir ) ) {
            wp_mkdir_p( $target_dir );
        }
        $ext = strtolower( pathinfo( $file['name'], PATHINFO_EXTENSION ) );
        if ( !in_array( $ext, array('png', 'jpg', 'jpeg'), true ) ) {
            $ext = ( $file['type'] === 'image/png' ? 'png' : 'jpg' );
        }
        $filename = 'logo-' . time() . '.' . $ext;
        $target_path = $target_dir . $filename;
        // Remove the previous logo file if present.
        $old_url = KleinBooks_Database::get_setting( 'logo_url', '' );
        if ( !empty( $old_url ) ) {
            $old_path = str_replace( $upload_dir['baseurl'], $upload_dir['basedir'], $old_url );
            if ( file_exists( $old_path ) ) {
                @unlink( $old_path );
            }
        }
        if ( !move_uploaded_file( $file['tmp_name'], $target_path ) ) {
            return new WP_Error('upload_failed', 'Failed to save file', array(
                'status' => 500,
            ));
        }
        $logo_url = $upload_dir['baseurl'] . '/mk-branding/' . $filename;
        KleinBooks_Database::update_setting( 'logo_url', $logo_url );
        return rest_ensure_response( array(
            'logo_url' => $logo_url,
            'message'  => 'Logo uploaded successfully',
        ) );
    }

    /**
     * Remove the company logo: unlink the file and clear the `logo_url` setting.
     */
    public static function delete_logo( $request ) {
        $upload_dir = wp_upload_dir();
        $old_url = KleinBooks_Database::get_setting( 'logo_url', '' );
        if ( !empty( $old_url ) ) {
            $old_path = str_replace( $upload_dir['baseurl'], $upload_dir['basedir'], $old_url );
            if ( file_exists( $old_path ) ) {
                @unlink( $old_path );
            }
        }
        KleinBooks_Database::update_setting( 'logo_url', '' );
        return rest_ensure_response( array(
            'success'  => true,
            'logo_url' => '',
            'message'  => 'Logo removed',
        ) );
    }

    // ============ REPORTS ============
    public static function get_yearly_report( $request ) {
        global $wpdb;
        $invoices_table = KleinBooks_Database::get_table_name( 'invoices' );
        $year = ( $request->get_param( 'year' ) ?: date( 'Y' ) );
        $report = $wpdb->get_results( $wpdb->prepare( "SELECT\n                MONTH(date_issued) as month,\n                COUNT(*) as invoice_count,\n                SUM(CASE WHEN status = 'paid' THEN total ELSE 0 END) as paid_total,\n                SUM(CASE WHEN status IN ('pending', 'sent') THEN total ELSE 0 END) as pending_total,\n                SUM(total) as total\n             FROM {$invoices_table}\n             WHERE YEAR(date_issued) = %d\n             GROUP BY MONTH(date_issued)\n             ORDER BY month", $year ), ARRAY_A );
        return rest_ensure_response( $report );
    }

    public static function get_monthly_report( $request ) {
        global $wpdb;
        $invoices_table = KleinBooks_Database::get_table_name( 'invoices' );
        $clients_table = KleinBooks_Database::get_table_name( 'clients' );
        $year = ( $request->get_param( 'year' ) ?: date( 'Y' ) );
        $month = ( $request->get_param( 'month' ) ?: date( 'm' ) );
        $invoices = $wpdb->get_results( $wpdb->prepare( "SELECT i.*, c.company_name as client_name\n             FROM {$invoices_table} i\n             LEFT JOIN {$clients_table} c ON i.client_id = c.id\n             WHERE YEAR(i.date_issued) = %d AND MONTH(i.date_issued) = %d\n             ORDER BY i.date_issued", $year, $month ), ARRAY_A );
        $summary = $wpdb->get_row( $wpdb->prepare( "SELECT\n                COUNT(*) as invoice_count,\n                SUM(CASE WHEN status = 'paid' THEN total ELSE 0 END) as paid_total,\n                SUM(CASE WHEN status IN ('pending', 'sent') THEN total ELSE 0 END) as pending_total,\n                SUM(total) as total\n             FROM {$invoices_table}\n             WHERE YEAR(date_issued) = %d AND MONTH(date_issued) = %d", $year, $month ), ARRAY_A );
        return rest_ensure_response( array(
            'invoices' => $invoices,
            'summary'  => $summary,
        ) );
    }

    // ============ EXPENSES ============
    /**
     * Get expense categories with colors
     */
    public static function get_expense_categories( $request ) {
        $categories = array(
            array(
                'id'    => 'software',
                'name'  => 'Software & Subscriptions',
                'color' => '#8b5cf6',
            ),
            array(
                'id'    => 'hardware',
                'name'  => 'Hardware & Equipment',
                'color' => '#3b82f6',
            ),
            array(
                'id'    => 'office',
                'name'  => 'Office Supplies',
                'color' => '#10b981',
            ),
            array(
                'id'    => 'travel',
                'name'  => 'Travel & Transport',
                'color' => '#f59e0b',
            ),
            array(
                'id'    => 'services',
                'name'  => 'Professional Services',
                'color' => '#ec4899',
            ),
            array(
                'id'    => 'marketing',
                'name'  => 'Marketing & Advertising',
                'color' => '#06b6d4',
            ),
            array(
                'id'    => 'education',
                'name'  => 'Education & Training',
                'color' => '#84cc16',
            ),
            array(
                'id'    => 'insurance',
                'name'  => 'Insurance',
                'color' => '#6366f1',
            ),
            array(
                'id'    => 'other',
                'name'  => 'Other',
                'color' => '#71717a',
            )
        );
        return rest_ensure_response( $categories );
    }

    /**
     * Get expenses list with filters
     */
    public static function get_expenses( $request ) {
        global $wpdb;
        $expenses_table = KleinBooks_Database::get_table_name( 'expenses' );
        $where = array('1=1');
        $params = array();
        // Filter by year
        if ( $year = $request->get_param( 'year' ) ) {
            $where[] = 'expense_year = %d';
            $params[] = (int) $year;
        }
        // Filter by category
        if ( $category = $request->get_param( 'category' ) ) {
            $where[] = 'category = %s';
            $params[] = sanitize_text_field( $category );
        }
        // Filter by recurring
        if ( $request->get_param( 'recurring' ) !== null ) {
            $where[] = 'is_recurring = %d';
            $params[] = ( $request->get_param( 'recurring' ) ? 1 : 0 );
        }
        // Search
        if ( $search = $request->get_param( 'search' ) ) {
            $where[] = '(description LIKE %s OR vendor LIKE %s)';
            $search_term = '%' . $wpdb->esc_like( $search ) . '%';
            $params[] = $search_term;
            $params[] = $search_term;
        }
        // Filter by frequency
        if ( $frequency = $request->get_param( 'frequency' ) ) {
            if ( $frequency === 'one-time' ) {
                $where[] = '(is_recurring = 0 OR is_recurring IS NULL)';
            } else {
                $where[] = 'is_recurring = 1 AND recurring_frequency = %s';
                $params[] = sanitize_text_field( $frequency );
            }
        }
        $where_clause = implode( ' AND ', $where );
        $sql = "SELECT * FROM {$expenses_table} WHERE {$where_clause} ORDER BY expense_date DESC";
        if ( !empty( $params ) ) {
            $sql = $wpdb->prepare( $sql, $params );
        }
        $expenses = $wpdb->get_results( $sql, ARRAY_A );
        return rest_ensure_response( $expenses );
    }

    /**
     * Get expense stats
     */
    public static function get_expenses_stats( $request ) {
        global $wpdb;
        $expenses_table = KleinBooks_Database::get_table_name( 'expenses' );
        $current_year = (int) date( 'Y' );
        $year_param = $request->get_param( 'year' );
        $all_years = empty( $year_param ) || $year_param === 'all';
        $year = ( $all_years ? null : (int) $year_param );
        if ( $all_years ) {
            // All years - no year filter
            $total_year = ( $wpdb->get_var( "SELECT SUM(amount) FROM {$expenses_table}" ) ?: 0 );
            $total_last_year = 0;
            // Not applicable for all years
            // Monthly recurring: sum of latest amount per unique subscription (by description)
            $recurring_monthly = ( $wpdb->get_var( "SELECT SUM(latest_amount) FROM (\n                    SELECT description, amount as latest_amount\n                    FROM {$expenses_table} e1\n                    WHERE is_recurring = 1\n                    AND recurring_frequency = 'monthly'\n                    AND expense_date = (\n                        SELECT MAX(expense_date)\n                        FROM {$expenses_table} e2\n                        WHERE e2.description = e1.description\n                        AND e2.is_recurring = 1\n                        AND e2.recurring_frequency = 'monthly'\n                    )\n                    GROUP BY description\n                ) as latest_subscriptions" ) ?: 0 );
            // Yearly recurring: sum of latest amount per unique subscription
            $recurring_yearly = ( $wpdb->get_var( "SELECT SUM(latest_amount) FROM (\n                    SELECT description, amount as latest_amount\n                    FROM {$expenses_table} e1\n                    WHERE is_recurring = 1\n                    AND recurring_frequency = 'yearly'\n                    AND expense_date = (\n                        SELECT MAX(expense_date)\n                        FROM {$expenses_table} e2\n                        WHERE e2.description = e1.description\n                        AND e2.is_recurring = 1\n                        AND e2.recurring_frequency = 'yearly'\n                    )\n                    GROUP BY description\n                ) as latest_subscriptions" ) ?: 0 );
            $by_category = $wpdb->get_results( "SELECT category, SUM(amount) as total, COUNT(*) as count\n                 FROM {$expenses_table}\n                 GROUP BY category\n                 ORDER BY total DESC", ARRAY_A );
            $monthly = array();
            // Not meaningful for all years
            $total_count = ( $wpdb->get_var( "SELECT COUNT(*) FROM {$expenses_table}" ) ?: 0 );
            return rest_ensure_response( array(
                'totalYear'        => (float) $total_year,
                'totalLastYear'    => (float) $total_last_year,
                'recurringMonthly' => (float) $recurring_monthly,
                'recurringYearly'  => (float) $recurring_yearly,
                'byCategory'       => $by_category,
                'monthly'          => $monthly,
                'totalCount'       => (int) $total_count,
                'year'             => 'all',
            ) );
        }
        // Specific year
        $total_year = ( $wpdb->get_var( $wpdb->prepare( "SELECT SUM(amount) FROM {$expenses_table} WHERE expense_year = %d", $year ) ) ?: 0 );
        $total_last_year = ( $wpdb->get_var( $wpdb->prepare( "SELECT SUM(amount) FROM {$expenses_table} WHERE expense_year = %d", $year - 1 ) ) ?: 0 );
        // Monthly recurring: sum of latest amount per unique subscription (by description) within this year
        $recurring_monthly = ( $wpdb->get_var( $wpdb->prepare( "SELECT SUM(latest_amount) FROM (\n                SELECT description, amount as latest_amount\n                FROM {$expenses_table} e1\n                WHERE is_recurring = 1\n                AND recurring_frequency = 'monthly'\n                AND expense_year = %d\n                AND expense_date = (\n                    SELECT MAX(expense_date)\n                    FROM {$expenses_table} e2\n                    WHERE e2.description = e1.description\n                    AND e2.is_recurring = 1\n                    AND e2.recurring_frequency = 'monthly'\n                    AND e2.expense_year = %d\n                )\n                GROUP BY description\n            ) as latest_subscriptions", $year, $year ) ) ?: 0 );
        // Yearly recurring: sum of latest amount per unique subscription within this year
        $recurring_yearly = ( $wpdb->get_var( $wpdb->prepare( "SELECT SUM(latest_amount) FROM (\n                SELECT description, amount as latest_amount\n                FROM {$expenses_table} e1\n                WHERE is_recurring = 1\n                AND recurring_frequency = 'yearly'\n                AND expense_year = %d\n                AND expense_date = (\n                    SELECT MAX(expense_date)\n                    FROM {$expenses_table} e2\n                    WHERE e2.description = e1.description\n                    AND e2.is_recurring = 1\n                    AND e2.recurring_frequency = 'yearly'\n                    AND e2.expense_year = %d\n                )\n                GROUP BY description\n            ) as latest_subscriptions", $year, $year ) ) ?: 0 );
        $by_category = $wpdb->get_results( $wpdb->prepare( "SELECT category, SUM(amount) as total, COUNT(*) as count\n             FROM {$expenses_table}\n             WHERE expense_year = %d\n             GROUP BY category\n             ORDER BY total DESC", $year ), ARRAY_A );
        $monthly = $wpdb->get_results( $wpdb->prepare( "SELECT MONTH(expense_date) as month, SUM(amount) as total\n             FROM {$expenses_table}\n             WHERE expense_year = %d\n             GROUP BY MONTH(expense_date)\n             ORDER BY month", $year ), ARRAY_A );
        $total_count = ( $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$expenses_table} WHERE expense_year = %d", $year ) ) ?: 0 );
        return rest_ensure_response( array(
            'totalYear'        => (float) $total_year,
            'totalLastYear'    => (float) $total_last_year,
            'recurringMonthly' => (float) $recurring_monthly,
            'recurringYearly'  => (float) $recurring_yearly,
            'byCategory'       => $by_category,
            'monthly'          => $monthly,
            'totalCount'       => (int) $total_count,
            'year'             => (int) $year,
        ) );
    }

    /**
     * Get single expense
     */
    public static function get_expense( $request ) {
        global $wpdb;
        $id = (int) $request['id'];
        $expenses_table = KleinBooks_Database::get_table_name( 'expenses' );
        $expense = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$expenses_table} WHERE id = %d", $id ), ARRAY_A );
        if ( !$expense ) {
            return new WP_Error('not_found', 'Expense not found', array(
                'status' => 404,
            ));
        }
        return rest_ensure_response( $expense );
    }

    /**
     * Create expense
     */
    public static function create_expense( $request ) {
        global $wpdb;
        $data = $request->get_json_params();
        $expenses_table = KleinBooks_Database::get_table_name( 'expenses' );
        $expense_date = sanitize_text_field( $data['expense_date'] ?? date( 'Y-m-d' ) );
        $expense_year = (int) date( 'Y', strtotime( $expense_date ) );
        $insert_data = array(
            'expense_date'        => $expense_date,
            'category'            => sanitize_text_field( $data['category'] ?? 'other' ),
            'description'         => sanitize_text_field( $data['description'] ?? '' ),
            'amount'              => (float) ($data['amount'] ?? 0),
            'vendor'              => sanitize_text_field( $data['vendor'] ?? '' ),
            'payment_method'      => sanitize_text_field( $data['payment_method'] ?? 'bank' ),
            'is_recurring'        => ( !empty( $data['is_recurring'] ) ? 1 : 0 ),
            'recurring_frequency' => sanitize_text_field( $data['recurring_frequency'] ?? '' ),
            'receipt_path'        => sanitize_text_field( $data['receipt_path'] ?? '' ),
            'notes'               => sanitize_textarea_field( $data['notes'] ?? '' ),
            'expense_year'        => $expense_year,
            'is_asset'            => ( !empty( $data['is_asset'] ) ? 1 : 0 ),
            'asset_type'          => sanitize_text_field( $data['asset_type'] ?? '' ),
            'useful_life_years'   => (int) ($data['useful_life_years'] ?? 0),
        );
        $result = $wpdb->insert( $expenses_table, $insert_data );
        if ( $result === false ) {
            return new WP_Error('db_error', 'Failed to create expense', array(
                'status' => 500,
            ));
        }
        $expense = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$expenses_table} WHERE id = %d", $wpdb->insert_id ), ARRAY_A );
        return rest_ensure_response( $expense );
    }

    /**
     * Update expense
     */
    public static function update_expense( $request ) {
        global $wpdb;
        $id = (int) $request['id'];
        $data = $request->get_json_params();
        $expenses_table = KleinBooks_Database::get_table_name( 'expenses' );
        // Check if expense exists
        $existing = $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$expenses_table} WHERE id = %d", $id ) );
        if ( !$existing ) {
            return new WP_Error('not_found', 'Expense not found', array(
                'status' => 404,
            ));
        }
        $expense_date = sanitize_text_field( $data['expense_date'] ?? date( 'Y-m-d' ) );
        $expense_year = (int) date( 'Y', strtotime( $expense_date ) );
        $update_data = array(
            'expense_date'        => $expense_date,
            'category'            => sanitize_text_field( $data['category'] ?? 'other' ),
            'description'         => sanitize_text_field( $data['description'] ?? '' ),
            'amount'              => (float) ($data['amount'] ?? 0),
            'vendor'              => sanitize_text_field( $data['vendor'] ?? '' ),
            'payment_method'      => sanitize_text_field( $data['payment_method'] ?? 'bank' ),
            'is_recurring'        => ( !empty( $data['is_recurring'] ) ? 1 : 0 ),
            'recurring_frequency' => sanitize_text_field( $data['recurring_frequency'] ?? '' ),
            'receipt_path'        => sanitize_text_field( $data['receipt_path'] ?? '' ),
            'notes'               => sanitize_textarea_field( $data['notes'] ?? '' ),
            'expense_year'        => $expense_year,
            'is_asset'            => ( !empty( $data['is_asset'] ) ? 1 : 0 ),
            'asset_type'          => sanitize_text_field( $data['asset_type'] ?? '' ),
            'useful_life_years'   => (int) ($data['useful_life_years'] ?? 0),
        );
        $result = $wpdb->update( $expenses_table, $update_data, array(
            'id' => $id,
        ) );
        if ( $result === false ) {
            return new WP_Error('db_error', 'Failed to update expense', array(
                'status' => 500,
            ));
        }
        $expense = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$expenses_table} WHERE id = %d", $id ), ARRAY_A );
        return rest_ensure_response( $expense );
    }

    /**
     * Delete expense
     */
    public static function delete_expense( $request ) {
        global $wpdb;
        $id = (int) $request['id'];
        $expenses_table = KleinBooks_Database::get_table_name( 'expenses' );
        // Get expense to delete receipt file if exists
        $expense = $wpdb->get_row( $wpdb->prepare( "SELECT receipt_path FROM {$expenses_table} WHERE id = %d", $id ), ARRAY_A );
        if ( $expense && !empty( $expense['receipt_path'] ) ) {
            // Try to delete the receipt file
            $upload_dir = wp_upload_dir();
            $file_path = str_replace( $upload_dir['baseurl'], $upload_dir['basedir'], $expense['receipt_path'] );
            if ( file_exists( $file_path ) ) {
                unlink( $file_path );
            }
        }
        $result = $wpdb->delete( $expenses_table, array(
            'id' => $id,
        ) );
        if ( $result === false ) {
            return new WP_Error('db_error', 'Failed to delete expense', array(
                'status' => 500,
            ));
        }
        return rest_ensure_response( array(
            'message' => 'Expense deleted successfully',
        ) );
    }

    /**
     * Upload receipt for expense
     */
    public static function upload_expense_receipt( $request ) {
        global $wpdb;
        $id = (int) $request['id'];
        $expenses_table = KleinBooks_Database::get_table_name( 'expenses' );
        // Check if expense exists
        $expense = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$expenses_table} WHERE id = %d", $id ), ARRAY_A );
        if ( !$expense ) {
            return new WP_Error('not_found', 'Expense not found', array(
                'status' => 404,
            ));
        }
        // Check for uploaded file
        if ( empty( $_FILES['receipt'] ) ) {
            return new WP_Error('no_file', 'No file uploaded', array(
                'status' => 400,
            ));
        }
        $file = $_FILES['receipt'];
        // Validate file type
        $allowed_types = array(
            'image/jpeg',
            'image/png',
            'image/gif',
            'application/pdf'
        );
        if ( !in_array( $file['type'], $allowed_types ) ) {
            return new WP_Error('invalid_type', 'Invalid file type. Allowed: JPG, PNG, GIF, PDF', array(
                'status' => 400,
            ));
        }
        // Max 5MB
        if ( $file['size'] > 5 * 1024 * 1024 ) {
            return new WP_Error('too_large', 'File too large. Maximum 5MB', array(
                'status' => 400,
            ));
        }
        // Upload to WordPress uploads directory
        $upload_dir = wp_upload_dir();
        $expense_year = $expense['expense_year'];
        $target_dir = $upload_dir['basedir'] . '/mk-expenses/' . $expense_year . '/';
        if ( !file_exists( $target_dir ) ) {
            wp_mkdir_p( $target_dir );
        }
        // Generate unique filename
        $ext = pathinfo( $file['name'], PATHINFO_EXTENSION );
        $filename = 'expense-' . $id . '-' . time() . '.' . $ext;
        $target_path = $target_dir . $filename;
        // Delete old receipt if exists
        if ( !empty( $expense['receipt_path'] ) ) {
            $old_path = str_replace( $upload_dir['baseurl'], $upload_dir['basedir'], $expense['receipt_path'] );
            if ( file_exists( $old_path ) ) {
                unlink( $old_path );
            }
        }
        // Move uploaded file
        if ( !move_uploaded_file( $file['tmp_name'], $target_path ) ) {
            return new WP_Error('upload_failed', 'Failed to save file', array(
                'status' => 500,
            ));
        }
        // Update expense with receipt path
        $receipt_url = $upload_dir['baseurl'] . '/mk-expenses/' . $expense_year . '/' . $filename;
        $wpdb->update( $expenses_table, array(
            'receipt_path' => $receipt_url,
        ), array(
            'id' => $id,
        ) );
        return rest_ensure_response( array(
            'receipt_path' => $receipt_url,
            'message'      => 'Receipt uploaded successfully',
        ) );
    }

    /**
     * Remove (detach) the receipt from an expense without deleting the expense.
     */
    public static function delete_expense_receipt( $request ) {
        global $wpdb;
        $id = (int) $request['id'];
        $expenses_table = KleinBooks_Database::get_table_name( 'expenses' );
        $expense = $wpdb->get_row( $wpdb->prepare( "SELECT receipt_path FROM {$expenses_table} WHERE id = %d", $id ), ARRAY_A );
        if ( !$expense ) {
            return new WP_Error('not_found', 'Expense not found', array(
                'status' => 404,
            ));
        }
        // Unlink the file if present (mirrors the replace/expense-delete unlink pattern).
        if ( !empty( $expense['receipt_path'] ) ) {
            $upload_dir = wp_upload_dir();
            $file_path = str_replace( $upload_dir['baseurl'], $upload_dir['basedir'], $expense['receipt_path'] );
            if ( file_exists( $file_path ) ) {
                unlink( $file_path );
            }
        }
        $wpdb->update( $expenses_table, array(
            'receipt_path' => '',
        ), array(
            'id' => $id,
        ) );
        return rest_ensure_response( array(
            'success'      => true,
            'receipt_path' => '',
            'message'      => 'Receipt removed successfully',
        ) );
    }

    // ============ EXPORT ============
    /**
     * Export expenses as CSV
     */
    public static function export_expenses_csv( $request ) {
        global $wpdb;
        $expenses_table = KleinBooks_Database::get_table_name( 'expenses' );
        $where = array('1=1');
        $params = array();
        // Filter by year
        if ( $year = $request->get_param( 'year' ) ) {
            $where[] = 'expense_year = %d';
            $params[] = (int) $year;
        }
        // Filter by category
        if ( $category = $request->get_param( 'category' ) ) {
            $where[] = 'category = %s';
            $params[] = sanitize_text_field( $category );
        }
        // Filter by frequency
        if ( $frequency = $request->get_param( 'frequency' ) ) {
            if ( $frequency === 'one-time' ) {
                $where[] = '(is_recurring = 0 OR is_recurring IS NULL)';
            } else {
                $where[] = 'is_recurring = 1 AND recurring_frequency = %s';
                $params[] = sanitize_text_field( $frequency );
            }
        }
        $where_clause = implode( ' AND ', $where );
        $sql = "SELECT * FROM {$expenses_table} WHERE {$where_clause} ORDER BY expense_date DESC";
        if ( !empty( $params ) ) {
            $sql = $wpdb->prepare( $sql, $params );
        }
        $expenses = $wpdb->get_results( $sql, ARRAY_A );
        // Category labels
        $category_labels = array(
            'software'  => 'Software & Subscriptions',
            'hardware'  => 'Hardware & Equipment',
            'office'    => 'Office Supplies',
            'travel'    => 'Travel & Transport',
            'services'  => 'Professional Services',
            'marketing' => 'Marketing & Advertising',
            'education' => 'Education & Training',
            'insurance' => 'Insurance',
            'other'     => 'Other',
        );
        // Payment method labels
        $payment_labels = array(
            'bank'      => 'Bank Transfer',
            'card'      => 'Credit/Debit Card',
            'paypal'    => 'PayPal',
            'klarna'    => 'Klarna',
            'apple_pay' => 'Apple Pay',
            'cash'      => 'Cash',
        );
        // Build CSV
        $csv_data = array();
        $csv_data[] = array(
            'Date',
            'Description',
            'Vendor',
            'Category',
            'Amount (EUR)',
            'Payment Method',
            'Recurring',
            'Frequency',
            'Notes'
        );
        foreach ( $expenses as $exp ) {
            $csv_data[] = array(
                $exp['expense_date'],
                $exp['description'],
                $exp['vendor'],
                $category_labels[$exp['category']] ?? $exp['category'],
                number_format(
                    (float) $exp['amount'],
                    2,
                    '.',
                    ''
                ),
                $payment_labels[$exp['payment_method']] ?? $exp['payment_method'],
                ( $exp['is_recurring'] ? 'Yes' : 'No' ),
                ( $exp['recurring_frequency'] ?: '-' ),
                $exp['notes']
            );
        }
        // Generate filename
        $year_suffix = ( $request->get_param( 'year' ) ?: 'all' );
        $filename = 'expenses-' . $year_suffix . '-' . date( 'Y-m-d' ) . '.csv';
        // Output CSV
        header( 'Content-Type: text/csv; charset=utf-8' );
        header( 'Content-Disposition: attachment; filename="' . $filename . '"' );
        header( 'Pragma: no-cache' );
        header( 'Expires: 0' );
        $output = fopen( 'php://output', 'w' );
        // UTF-8 BOM for Excel compatibility
        fprintf( $output, chr( 0xef ) . chr( 0xbb ) . chr( 0xbf ) );
        foreach ( $csv_data as $row ) {
            fputcsv( $output, $row, ';' );
            // Use semicolon for German Excel
        }
        fclose( $output );
        exit;
    }

    /**
     * Export invoices as CSV
     */
    public static function export_invoices_csv( $request ) {
        global $wpdb;
        $invoices_table = KleinBooks_Database::get_table_name( 'invoices' );
        $clients_table = KleinBooks_Database::get_table_name( 'clients' );
        $where = array('1=1');
        $params = array();
        // Filter by year
        if ( $year = $request->get_param( 'year' ) ) {
            $where[] = 'YEAR(i.date_issued) = %d';
            $params[] = (int) $year;
        }
        // Filter by status
        if ( $status = $request->get_param( 'status' ) ) {
            $where[] = 'i.status = %s';
            $params[] = sanitize_text_field( $status );
        }
        // Filter by client
        if ( $client_id = $request->get_param( 'client_id' ) ) {
            $where[] = 'i.client_id = %d';
            $params[] = (int) $client_id;
        }
        $where_clause = implode( ' AND ', $where );
        $sql = "SELECT i.*, c.company_name as client_name\n                FROM {$invoices_table} i\n                LEFT JOIN {$clients_table} c ON i.client_id = c.id\n                WHERE {$where_clause}\n                ORDER BY i.date_issued DESC";
        if ( !empty( $params ) ) {
            $sql = $wpdb->prepare( $sql, $params );
        }
        $invoices = $wpdb->get_results( $sql, ARRAY_A );
        // Status labels
        $status_labels = array(
            'draft'     => 'Draft',
            'pending'   => 'Pending',
            'paid'      => 'Paid',
            'overdue'   => 'Overdue',
            'cancelled' => 'Cancelled',
        );
        // Build CSV
        $csv_data = array();
        $csv_data[] = array(
            'Invoice Number',
            'Client',
            'Date Issued',
            'Due Date',
            'Status',
            'Subtotal (EUR)',
            'Tax Rate (%)',
            'Tax Amount (EUR)',
            'Total (EUR)',
            'Performance Period',
            'Paid At',
            'Notes'
        );
        foreach ( $invoices as $inv ) {
            $csv_data[] = array(
                $inv['invoice_number'],
                $inv['client_name'],
                $inv['date_issued'],
                $inv['date_due'],
                $status_labels[$inv['status']] ?? $inv['status'],
                number_format(
                    (float) $inv['subtotal'],
                    2,
                    '.',
                    ''
                ),
                number_format(
                    (float) $inv['tax_rate'],
                    2,
                    '.',
                    ''
                ),
                number_format(
                    (float) $inv['tax_amount'],
                    2,
                    '.',
                    ''
                ),
                number_format(
                    (float) $inv['total'],
                    2,
                    '.',
                    ''
                ),
                $inv['performance_period'],
                ( $inv['paid_at'] ?: '-' ),
                $inv['notes']
            );
        }
        // Generate filename
        $year_suffix = ( $request->get_param( 'year' ) ?: 'all' );
        $filename = 'invoices-' . $year_suffix . '-' . date( 'Y-m-d' ) . '.csv';
        // Output CSV
        header( 'Content-Type: text/csv; charset=utf-8' );
        header( 'Content-Disposition: attachment; filename="' . $filename . '"' );
        header( 'Pragma: no-cache' );
        header( 'Expires: 0' );
        $output = fopen( 'php://output', 'w' );
        // UTF-8 BOM for Excel compatibility
        fprintf( $output, chr( 0xef ) . chr( 0xbb ) . chr( 0xbf ) );
        foreach ( $csv_data as $row ) {
            fputcsv( $output, $row, ';' );
            // Use semicolon for German Excel
        }
        fclose( $output );
        exit;
    }

}
