<?php

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

class Freefactu_DB {

    /**
     * Get the invoices table name with WordPress prefix.
     *
     * What this function does:
     * - Returns the full table name with WordPress database prefix
     * - Table name: wp_freefactu_invoices (or custom prefix)
     *
     * Where it's called from:
     * - class-freefactu-db.php: All database methods in this class
     * - class-freefactu-client.php: get_chaining_data_from_db() method
     * - class-freefactu-debug.php: render_chaining_restoration_section() method
     * - class-freefactu-debug.php: handle_chaining_restoration() method
     * - class-freefactu-history.php: render_history_page() method
     * - class-freefactu-snapshot-utility.php: bulk_create_snapshots() method
     * - class-freefactu-snapshot-utility.php: get_statistics() method
     *
     * @return string Table name with prefix.
     */
    public static function get_table_name() {
        global $wpdb;
        return $wpdb->prefix . 'freefactu_invoices';
    }

    /**
     * Check if the invoices table exists.
     *
     * What this function does:
     * - Checks if freefactu_invoices table exists in database
     * - Uses transient caching (1 day) to avoid repeated queries
     * - Returns cached result if available
     *
     * Where it's called from:
     * - class-freefactu-client.php: send_invoice_to_verifactu() method before inserting rejected invoices
     *
     * @return bool True if table exists, false otherwise.
     */
    public static function table_exists() {
        // Check transient first
        $exists = get_transient( 'freefactu_table_exists' );
        if ( $exists !== false ) {
            return (bool) $exists;
        }

        global $wpdb;
        $table_name = self::get_table_name();
        $result = $wpdb->get_var( $wpdb->prepare( "SHOW TABLES LIKE %s", $table_name ) ) === $table_name; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

        // Cache result for 1 day
        set_transient( 'freefactu_table_exists', $result ? 1 : 0, DAY_IN_SECONDS );

        return $result;
    }

    /**
     * Create the invoices database table.
     *
     * What this function does:
     * - Creates wp_freefactu_invoices table with all required columns
     * - Sets up indexes for order_id, invoice_number, and status
     * - Uses dbDelta for safe table creation/updates
     * - Clears table existence cache after creation
     *
     * Table columns:
     * - id, order_id, invoice_number, invoice_number_plain, invoice_date
     * - total, csv, hash, pdf_path, status, error_message
     * - invoice_snapshot, created_at
     * - aeat_xml_response (Added: Stores raw XML response from AEAT)
     * - aeat_xml_request (Added: Stores raw XML request sent to AEAT for debugging)
     *
     * Where it's called from:
     * - freefactu.php: freefactu_activate() function during plugin activation
     * - class-freefactu-client.php: send_invoice_to_verifactu() if table doesn't exist
     *
     * @return void
     */
    public static function create_table() {
        global $wpdb;
        $table_name = self::get_table_name();
        $charset_collate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE $table_name (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            order_id bigint(20) NOT NULL,
            invoice_number varchar(50) NOT NULL,
            invoice_number_plain varchar(50) DEFAULT '' NOT NULL,
            invoice_date datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
            total decimal(10,2) NOT NULL,
            csv varchar(100) NOT NULL,
            hash varchar(255) NOT NULL,
            pdf_path varchar(255) NOT NULL,
            status varchar(20) DEFAULT 'success' NOT NULL,
            error_message text,
            error_category varchar(30) DEFAULT '' NOT NULL,
            subsanation_count tinyint(3) DEFAULT 0 NOT NULL,
            aeat_final_status varchar(30) DEFAULT 'pending' NOT NULL,
            invoice_snapshot longtext,
            aeat_xml_response longtext,
            aeat_xml_request longtext,
            tipo_factura varchar(10) DEFAULT 'F1' NOT NULL,
            tipo_rectificativa varchar(5) DEFAULT NULL,
            rectified_invoice_id bigint(20) DEFAULT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
            PRIMARY KEY  (id),
            UNIQUE KEY invoice_number (invoice_number),
            KEY order_id (order_id),
            KEY status (status),
            KEY aeat_final_status (aeat_final_status),
            KEY rectified_invoice_id (rectified_invoice_id)
        ) $charset_collate;";

        require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
        dbDelta( $sql );
        
        // Clear the table exists cache after creating/updating table
        delete_transient( 'freefactu_table_exists' );
    }

    /**
     * Insert a new invoice record into the database.
     *
     * What this function does:
     * - Validates required fields (order_id, invoice_number, invoice_date, total)
     * - Inserts invoice data into freefactu_invoices table
     * - Returns insert ID on success or WP_Error on failure
     * - Logs errors if insertion fails
     *
     * Where it's called from:
     * - class-freefactu-db.php: upsert_invoice() method when invoice doesn't exist
     * - class-freefactu-debug.php: handle_chaining_restoration() method for manual restoration
     *
     * @param array $data Invoice data array.
     * @return int|WP_Error Insert ID on success, WP_Error on failure.
     */
    public static function insert_invoice( $data ) {
        global $wpdb;
        $table_name = self::get_table_name();
        
        // Validate required fields
        if ( ! isset( $data['order_id'] ) || ! isset( $data['invoice_number'] ) || ! isset( $data['invoice_date'] ) || ! isset( $data['total'] ) ) {
            $missing = array();
            if ( ! isset( $data['order_id'] ) ) $missing[] = 'order_id';
            if ( ! isset( $data['invoice_number'] ) ) $missing[] = 'invoice_number';
            if ( ! isset( $data['invoice_date'] ) ) $missing[] = 'invoice_date';
            if ( ! isset( $data['total'] ) ) $missing[] = 'total';
            
            return new WP_Error( 'missing_required_fields', __( 'Missing required fields: ', 'freefactu' ) . implode( ', ', $missing ) );
        }
        
        // Suppress $wpdb error display during insert to prevent HTML output
        // that would corrupt AJAX JSON responses (e.g., duplicate key errors).
        // The error is still captured via $wpdb->last_error for proper handling.
        $wpdb->suppress_errors( true );

        $result = $wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching 
            $table_name, 
            array( 
                'order_id' => $data['order_id'],
                'invoice_number' => $data['invoice_number'],
                'invoice_number_plain' => isset( $data['invoice_number_plain'] ) ? $data['invoice_number_plain'] : '',
                'invoice_date' => $data['invoice_date'],
                'total' => $data['total'],
                'csv' => isset( $data['csv'] ) ? $data['csv'] : '',
                'hash' => isset( $data['hash'] ) ? $data['hash'] : '',
                'pdf_path' => isset( $data['pdf_path'] ) ? $data['pdf_path'] : '',
                'status' => isset( $data['status'] ) ? $data['status'] : 'success',
                'error_message' => isset( $data['error_message'] ) ? $data['error_message'] : '',
                'error_category' => isset( $data['error_category'] ) ? $data['error_category'] : '',
                'subsanation_count' => isset( $data['subsanation_count'] ) ? (int) $data['subsanation_count'] : 0,
                'aeat_final_status' => isset( $data['aeat_final_status'] ) ? $data['aeat_final_status'] : 'pending',
                'invoice_snapshot' => isset( $data['invoice_snapshot'] ) ? $data['invoice_snapshot'] : '',
                'aeat_xml_response' => isset( $data['aeat_xml_response'] ) ? $data['aeat_xml_response'] : '',
                'aeat_xml_request' => isset( $data['aeat_xml_request'] ) ? $data['aeat_xml_request'] : '',
                'tipo_factura' => isset( $data['tipo_factura'] ) ? $data['tipo_factura'] : 'F1',
                'tipo_rectificativa' => isset( $data['tipo_rectificativa'] ) ? $data['tipo_rectificativa'] : null,
                'rectified_invoice_id' => isset( $data['rectified_invoice_id'] ) ? $data['rectified_invoice_id'] : null,
            ), 
            array( 
                '%d', 
                '%s', 
                '%s', 
                '%s', 
                '%f', 
                '%s', 
                '%s', 
                '%s',
                '%s',
                '%s',
                '%s',
                '%d',
                '%s',
                '%s',
                '%s',
                '%s',
                '%s',
                '%s',
                '%d'
            ) 
        );

        // Re-enable error display
        $wpdb->suppress_errors( false );
        
        if ( false === $result ) {
            $error_msg = $wpdb->last_error ? $wpdb->last_error : 'Unknown database error';
            Freefactu_Logger::log( 
                sprintf( 'Database insert failed for order #%d: %s', $data['order_id'], $error_msg ),
                'error'
            );
            return new WP_Error( 'db_insert_error', __( 'Failed to insert invoice into database.', 'freefactu' ), array( 'wpdb_error' => $error_msg ) );
        }
        
        return $wpdb->insert_id;
    }

    /**
     * Update invoice status and error message.
     *
     * What this function does:
     * - Updates status and error_message fields for an invoice
     * - Finds invoice by order_id
     * - Returns number of rows updated or WP_Error on failure
     *
     * Where it's called from:
     * - class-freefactu-client.php: send_invoice_to_verifactu() for alert statuses
     * - class-freefactu-client.php: retry_qr_pdf_generation() for success/alert statuses
     *
     * @param int $order_id Order ID
     * @param string $status Invoice status
     * @param string $error_message Error message (optional)
     * @return int|WP_Error Number of rows updated or WP_Error on failure
     */
    public static function update_invoice_status( $order_id, $status, $error_message = '' ) {
        global $wpdb;
        $table_name = self::get_table_name();
        
        $result = $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $table_name,
            array(
                'status' => $status,
                'error_message' => $error_message,
            ),
            array( 'order_id' => $order_id ),
            array( '%s', '%s' ),
            array( '%d' )
        );
        
        if ( false === $result ) {
            return new WP_Error( 'db_update_error', __( 'Failed to update invoice status in database.', 'freefactu' ), array( 'wpdb_error' => $wpdb->last_error ) );
        }
        
        return $result;
    }

    /**
     * Update invoice status and error message by invoice number.
     *
     * What this function does:
     * - Updates status and error_message fields for an invoice
     * - Finds invoice by invoice_number (unique key)
     * - Useful for rectificativas where multiple invoices may exist for the same order
     * - Returns number of rows updated or WP_Error on failure
     *
     * Where it's called from:
     * - class-freefactu-client.php: send_invoice_to_verifactu() for rectificativa status updates
     *
     * @param string $invoice_number Invoice number (unique)
     * @param string $status Invoice status
     * @param string $error_message Error message (optional)
     * @return int|WP_Error Number of rows updated or WP_Error on failure
     */
    public static function update_invoice_status_by_number( $invoice_number, $status, $error_message = '' ) {
        global $wpdb;
        $table_name = self::get_table_name();
        
        $result = $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $table_name,
            array(
                'status' => $status,
                'error_message' => $error_message,
            ),
            array( 'invoice_number' => $invoice_number ),
            array( '%s', '%s' ),
            array( '%s' )
        );
        
        if ( false === $result ) {
            return new WP_Error( 'db_update_error', __( 'Failed to update invoice status in database.', 'freefactu' ), array( 'wpdb_error' => $wpdb->last_error ) );
        }
        
        return $result;
    }

    /**
     * Update CSV and hash values.
     *
     * What this function does:
     * - Updates csv and hash fields for an invoice
     * - Also sets status to 'success' and clears error_message
     * - Finds invoice by order_id
     * - Returns number of rows updated or WP_Error on failure
     *
     * Where it's called from:
     * - Not currently used in the codebase (available for future use)
     *
     * @param int $order_id Order ID
     * @param string $csv CSV value
     * @param string $hash Hash value
     * @return int|WP_Error Number of rows updated or WP_Error on failure
     */
    public static function update_invoice_csv_hash( $order_id, $csv, $hash ) {
        global $wpdb;
        $table_name = self::get_table_name();
        
        $result = $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $table_name,
            array(
                'csv' => $csv,
                'hash' => $hash,
                'status' => 'success',
                'error_message' => '',
            ),
            array( 'order_id' => $order_id ),
            array( '%s', '%s', '%s', '%s' ),
            array( '%d' )
        );
        
        if ( false === $result ) {
            return new WP_Error( 'db_update_error', __( 'Failed to update invoice CSV and hash in database.', 'freefactu' ), array( 'wpdb_error' => $wpdb->last_error ) );
        }
        
        return $result;
    }

    /**
     * Update PDF path.
     *
     * What this function does:
     * - Updates pdf_path field for an invoice
     * - Also sets status to 'success' and clears error_message
     * - Finds invoice by order_id
     * - Returns number of rows updated or WP_Error on failure
     *
     * Where it's called from:
     * - abstract-base-generator.php: save_pdf() method after saving PDF file
     * - abstract-base-generator.php: save_regenerated_pdf() method
     * - class-freefactu-client.php: save_pdf_snapshot_by_id() method
     *
     * @param int $order_id Order ID
     * @param string $pdf_path PDF file path
     * @return int|WP_Error Number of rows updated or WP_Error on failure
     */
    public static function update_invoice_pdf( $order_id, $pdf_path ) {
        global $wpdb;
        $table_name = self::get_table_name();
        
        $result = $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $table_name,
            array(
                'pdf_path' => $pdf_path,
                'status' => 'success',
                'error_message' => '',
            ),
            array( 'order_id' => $order_id ),
            array( '%s', '%s', '%s' ),
            array( '%d' )
        );
        
        if ( false === $result ) {
            return new WP_Error( 'db_update_error', __( 'Failed to update invoice PDF path in database.', 'freefactu' ), array( 'wpdb_error' => $wpdb->last_error ) );
        }
        
        return $result;
    }
    
    /**
     * Get invoices with pagination.
     *
     * What this function does:
     * - Retrieves invoices ordered by invoice_date descending (newest first)
     * - Supports pagination with limit and offset
     * - Returns array of invoice objects
     *
     * Where it's called from:
     * - class-freefactu-history.php: render_history_page() method for invoice list display
     *
     * @param int $limit Number of invoices to retrieve (default: 20).
     * @param int $offset Offset for pagination (default: 0).
     * @return array Array of invoice objects.
     */
    public static function get_invoices( $limit = 20, $offset = 0 ) {
        global $wpdb;
        $table_name = self::get_table_name();
        return $wpdb->get_results( $wpdb->prepare( "SELECT * FROM " . esc_sql( $table_name ) . " ORDER BY invoice_date DESC LIMIT %d OFFSET %d", $limit, $offset ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
    }

    /**
     * Count total number of invoices.
     *
     * What this function does:
     * - Returns total count of all invoices in the database
     * - Used for pagination calculations
     *
     * Where it's called from:
     * - class-freefactu-history.php: render_history_page() method for pagination
     *
     * @return int Total number of invoices.
     */
    public static function count_invoices() {
        global $wpdb;
        $table_name = self::get_table_name();
        return $wpdb->get_var( "SELECT COUNT(*) FROM " . esc_sql( $table_name ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
    }

    /**
     * Get invoices with filters and pagination.
     *
     * What this function does:
     * - Retrieves invoices with optional date range and status filters
     * - Supports pagination with limit and offset
     * - Returns array of invoice objects
     *
     * Where it's called from:
     * - class-freefactu-history.php: render_history_page() method when filters are applied
     *
     * @param array $filters {
     *     Optional. Filter parameters.
     *     @type string $date_from Start date (Y-m-d format).
     *     @type string $date_to End date (Y-m-d format).
     *     @type string $status Invoice status (success, alert_aeat, alert_qr, alert_pdf).
     * }
     * @param int $limit Number of invoices to retrieve (default: 20).
     * @param int $offset Offset for pagination (default: 0).
     * @return array Array of invoice objects.
     */
    public static function get_invoices_filtered( $filters = array(), $limit = 20, $offset = 0 ) {
        global $wpdb;
        $table_name = self::get_table_name();

        $where = array( '1=1' );
        $params = array();

        // Date from filter
        if ( ! empty( $filters['date_from'] ) ) {
            $where[] = 'invoice_date >= %s';
            $params[] = sanitize_text_field( $filters['date_from'] ) . ' 00:00:00';
        }

        // Date to filter
        if ( ! empty( $filters['date_to'] ) ) {
            $where[] = 'invoice_date <= %s';
            $params[] = sanitize_text_field( $filters['date_to'] ) . ' 23:59:59';
        }

        // Status filter
        if ( ! empty( $filters['status'] ) ) {
            $where[] = 'status = %s';
            $params[] = sanitize_text_field( $filters['status'] );
        }

        $where_clause = implode( ' AND ', $where );

        // Add limit and offset to params
        $params[] = $limit;
        $params[] = $offset;

        if ( count( $params ) > 2 ) {
            // We have filter params
            $sql = $wpdb->prepare( // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter
                "SELECT * FROM " . esc_sql( $table_name ) . " WHERE $where_clause ORDER BY invoice_date DESC LIMIT %d OFFSET %d", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
                $params
            ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        } else {
            // No filter params, just limit/offset
            $sql = $wpdb->prepare(
                "SELECT * FROM " . esc_sql( $table_name ) . " ORDER BY invoice_date DESC LIMIT %d OFFSET %d", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $limit,
                $offset
            ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        }

        return $wpdb->get_results( $sql ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
    }

    /**
     * Count invoices with filters.
     *
     * What this function does:
     * - Returns count of invoices matching the specified filters
     * - Used for pagination calculations with filters
     *
     * Where it's called from:
     * - class-freefactu-history.php: render_history_page() method when filters are applied
     *
     * @param array $filters Same as get_invoices_filtered.
     * @return int Total number of matching invoices.
     */
    public static function count_invoices_filtered( $filters = array() ) {
        global $wpdb;
        $table_name = self::get_table_name();

        $where = array( '1=1' );
        $params = array();

        // Date from filter
        if ( ! empty( $filters['date_from'] ) ) {
            $where[] = 'invoice_date >= %s';
            $params[] = $filters['date_from'] . ' 00:00:00';
        }

        // Date to filter
        if ( ! empty( $filters['date_to'] ) ) {
            $where[] = 'invoice_date <= %s';
            $params[] = $filters['date_to'] . ' 23:59:59';
        }

        // Status filter
        if ( ! empty( $filters['status'] ) ) {
            $where[] = 'status = %s';
            $params[] = $filters['status'];
        }

        $where_clause = implode( ' AND ', $where );

        if ( ! empty( $params ) ) {
            return $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM " . esc_sql( $table_name ) . " WHERE $where_clause", $params ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter
        }

        return $wpdb->get_var( "SELECT COUNT(*) FROM " . esc_sql( $table_name ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
    }

    /**
     * Get invoice by ID.
     *
     * What this function does:
     * - Retrieves a single invoice by its database ID
     * - Returns invoice object or null if not found
     *
     * Where it's called from:
     * - class-aeat-subsanation-service.php: get_subsanation_status() method
     * - class-aeat-subsanation-service.php: reset_subsanation_count() method
     *
     * @param int $id Invoice database ID.
     * @return object|null Invoice object or null if not found.
     */
    public static function get_invoice( $id ) {
        global $wpdb;
        $table_name = self::get_table_name();
        return $wpdb->get_row( $wpdb->prepare( "SELECT * FROM " . esc_sql( $table_name ) . " WHERE id = %d", $id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
    }

    /**
     * Alias for get_invoice() - Get invoice by ID.
     *
     * @param int $id Invoice database ID.
     * @return object|null Invoice object or null if not found.
     */
    public static function get_invoice_by_id( $id ) {
        return self::get_invoice( $id );
    }

    /**
     * Get rectificativa invoices for an original invoice.
     *
     * What this function does:
     * - Retrieves all rectificativa invoices that reference the given invoice ID
     * - Returns array of rectificativa invoice objects
     *
     * Where it's called from:
     * - class-freefactu-history.php: For showing rectificativa chain
     *
     * @param int $original_invoice_id Original invoice database ID.
     * @return array Array of rectificativa invoice objects.
     */
    public static function get_rectificativas_for_invoice( $original_invoice_id ) {
        global $wpdb;
        $table_name = self::get_table_name();
        return $wpdb->get_results( $wpdb->prepare( "SELECT * FROM " . esc_sql( $table_name ) . " WHERE rectified_invoice_id = %d ORDER BY invoice_date ASC", $original_invoice_id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
    }

    /**
     * Check if invoice has been rectified.
     *
     * @param int $invoice_id Invoice database ID.
     * @return bool True if invoice has rectificativas.
     */
    public static function has_rectificativas( $invoice_id ) {
        global $wpdb;
        $table_name = self::get_table_name();
        $count = $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM " . esc_sql( $table_name ) . " WHERE rectified_invoice_id = %d", $invoice_id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        return $count > 0;
    }

    /**
     * Update invoice snapshot data.
     *
     * What this function does:
     * - Updates invoice_snapshot field with JSON encoded data
     * - Finds invoice by order_id
     * - Returns number of rows updated or false on error
     *
     * Where it's called from:
     * - class-freefactu-snapshot-utility.php: create_snapshot_for_order() method
     * - class-freefactu-snapshot-utility.php: delete_snapshot() method
     *
     * @param int $order_id Order ID
     * @param string $snapshot_json JSON encoded snapshot data
     * @return int|false Number of rows updated or false on error
     */
    public static function update_invoice_snapshot( $order_id, $snapshot_json ) {
        global $wpdb;
        $table_name = self::get_table_name();
        
        $result = $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $table_name,
            array( 'invoice_snapshot' => $snapshot_json ),
            array( 'order_id' => $order_id ),
            array( '%s' ),
            array( '%d' )
        );

        if ( false !== $result ) {
            wp_cache_delete( 'freefactu_count_snapshots', 'freefactu_db' );
            wp_cache_delete( 'freefactu_count_needing_snapshots', 'freefactu_db' );
        }

        return $result;
    }

    /**
     * Insert or update invoice record (upsert operation).
     *
     * What this function does:
     * - Checks if invoice exists for the given order_id
     * - Updates existing invoice if found
     * - Inserts new invoice if not found
     * - Logs all operations for debugging
     * - Returns insert/update ID or WP_Error on failure
     *
     * Where it's called from:
     * - class-freefactu-client.php: send_invoice_to_verifactu() method multiple times
     * - class-freefactu-client.php: get_formatted_invoice_number() method
     * - class-freefactu-debug.php: handle_chaining_restoration() method
     *
     * @param array $data Invoice data including all metadata fields
     * @return int|WP_Error Insert ID on success, WP_Error on failure
     */
    public static function upsert_invoice( $data ) {
        if ( ! isset( $data['order_id'] ) ) {
            Freefactu_Logger::log( 'upsert_invoice called without order_id', 'error' );
            return new WP_Error( 'missing_order_id', __( 'Order ID is required for upsert operation.', 'freefactu' ) );
        }

        $order_id = $data['order_id'];
        
        // Check if invoice exists
        $exists = self::invoice_exists( $order_id );
        Freefactu_Logger::log( 
            sprintf( 'upsert_invoice for order #%d - exists: %s', $order_id, $exists ? 'YES' : 'NO' ),
            'debug'
        );
        
        if ( $exists ) {
            // Update existing invoice
            global $wpdb;
            $table_name = self::get_table_name();
            
            // Build update data array (exclude order_id from update)
            $update_data = array();
            $update_format = array();
            
            $field_mapping = array(
                'invoice_number' => '%s',
                'invoice_number_plain' => '%s',
                'invoice_date' => '%s',
                'total' => '%f',
                'csv' => '%s',
                'hash' => '%s',
                'pdf_path' => '%s',
                'status' => '%s',
                'error_message' => '%s',
                'error_category' => '%s',
                'subsanation_count' => '%d',
                'aeat_final_status' => '%s',
                'invoice_snapshot' => '%s',
                'aeat_xml_response' => '%s',
                'aeat_xml_request' => '%s',
            );
            
            foreach ( $field_mapping as $field => $format ) {
                if ( isset( $data[ $field ] ) ) {
                    $update_data[ $field ] = $data[ $field ];
                    $update_format[] = $format;
                }
            }
            
            if ( empty( $update_data ) ) {
                Freefactu_Logger::log( 'upsert_invoice: No update data provided', 'error' );
                return new WP_Error( 'no_update_data', __( 'No data provided for update operation.', 'freefactu' ) );
            }
            
            Freefactu_Logger::log( 
                sprintf( 'Updating invoice for order #%d with fields: %s', $order_id, implode( ', ', array_keys( $update_data ) ) ),
                'debug'
            );
            
            $result = $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                $table_name,
                $update_data,
                array( 'order_id' => $order_id ),
                $update_format,
                array( '%d' )
            );
            
            if ( false === $result ) {
                $error_msg = $wpdb->last_error ? $wpdb->last_error : 'Unknown database error';
                Freefactu_Logger::log( 
                    sprintf( 'Database update failed for order #%d: %s', $order_id, $error_msg ),
                    'error'
                );
                return new WP_Error( 'db_update_error', __( 'Failed to update invoice in database.', 'freefactu' ), array( 'wpdb_error' => $error_msg ) );
            }
            
            Freefactu_Logger::log( 
                sprintf( 'Successfully updated invoice for order #%d (%d rows affected)', $order_id, $result ),
                'debug'
            );
            
            if ( false !== $result ) {
                wp_cache_delete( 'freefactu_count_snapshots', 'freefactu_db' );
                wp_cache_delete( 'freefactu_count_needing_snapshots', 'freefactu_db' );
                // Invalidate potentially affected list caches - difficult to be precise with limits/offsets so we might need a versioning strategy or just accept short TTL for lists.
                // For now, we clear the counts which are most important.
            }

            // Return the existing invoice ID
            $invoice = self::get_invoice_by_order_id( $order_id );
            return $invoice ? $invoice->id : new WP_Error( 'invoice_not_found', __( 'Invoice was updated but could not be retrieved.', 'freefactu' ) );
        } else {
            // Insert new invoice
            Freefactu_Logger::log( 
                sprintf( 'Inserting new invoice for order #%d', $order_id ),
                'debug'
            );
            $insert_id = self::insert_invoice( $data );
            if ( ! is_wp_error( $insert_id ) ) {
                wp_cache_delete( 'freefactu_count_snapshots', 'freefactu_db' );
                wp_cache_delete( 'freefactu_count_needing_snapshots', 'freefactu_db' );
            }
            return $insert_id;
        }
    }

    /**
     * Check if invoice exists for order.
     *
     * What this function does:
     * - Checks if an invoice record exists for the given order_id
     * - Uses COUNT for better performance than fetching full row
     * - Returns true if exists, false otherwise
     *
     * Where it's called from:
     * - class-freefactu-db.php: upsert_invoice() method to determine insert vs update
     *
     * @param int $order_id Order ID
     * @return bool True if invoice exists, false otherwise
     */
    public static function invoice_exists( $order_id ) {
        global $wpdb;
        $table_name = self::get_table_name();
        
        // Use COUNT for better performance than fetching the full row
        $count = $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM " . esc_sql( $table_name ) . " WHERE order_id = %d", $order_id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        
        return $count > 0;
    }

    /**
     * Get invoice by order ID.
     *
     * What this function does:
     * - Retrieves invoice record by WooCommerce order ID
     * - Returns full invoice object with all fields
     * - Returns null if no invoice found for the order
     *
     * Where it's called from:
     * - Used extensively throughout the plugin (50+ locations)
     * - class-freefactu-client.php: send_invoice_to_verifactu(), retry_qr_pdf_generation(), etc.
     * - class-freefactu-admin.php: get_button_state(), render_invoice_meta_box_content()
     * - abstract-base-generator.php: prepare_verifactu_data(), get_invoice_data_from_db()
     * - class-freefactu-snapshot-utility.php: create_snapshot_for_order(), verify_snapshot()
     * - class-freefactu-history.php: render_history_page(), handle_debug_invoice()
     * - And many other locations
     *
     * @param int $order_id Order ID
     * @return object|null Invoice object or null if not found
     */
    public static function get_invoice_by_order_id( $order_id ) {
        global $wpdb;
        $table_name = self::get_table_name();
        
        // Get from invoice table
        $invoice = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM " . esc_sql( $table_name ) . " WHERE order_id = %d", $order_id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        
        return $invoice;
    }

    /**
     * Get invoice by invoice number.
     *
     * What this function does:
     * - Retrieves invoice record by unique invoice_number
     * - Returns full invoice object with all fields
     * - Returns null if no invoice found
     *
     * Where it's called from:
     * - abstract-base-generator.php: For PDF generation with specific invoice number
     * - class-freefactu-client.php: For rectificativa PDF generation
     *
     * @param string $invoice_number Invoice number (unique)
     * @return object|null Invoice object or null if not found
     */
    public static function get_invoice_by_number( $invoice_number ) {
        global $wpdb;
        $table_name = self::get_table_name();
        
        $invoice = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM " . esc_sql( $table_name ) . " WHERE invoice_number = %s", $invoice_number ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        
        return $invoice;
    }

    /**
     * Get the latest invoice for an order.
     *
     * What this function does:
     * - Retrieves the most recently created invoice for an order
     * - Useful when order has multiple invoices (original + rectificativas)
     * - Orders by id DESC to get the latest
     * - Returns null if no invoice found
     *
     * Where it's called from:
     * - abstract-base-generator.php: For PDF generation when order has multiple invoices
     *
     * @param int $order_id Order ID
     * @return object|null Invoice object or null if not found
     */
    public static function get_latest_invoice_by_order_id( $order_id ) {
        global $wpdb;
        $table_name = self::get_table_name();
        
        $invoice = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM " . esc_sql( $table_name ) . " WHERE order_id = %d ORDER BY id DESC LIMIT 1", $order_id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        
        return $invoice;
    }

    // ========================================================================
    // DECLARACIONES RESPONSABLES METHODS
    // ========================================================================

    /**
     * Get the declarations table name with WordPress prefix.
     *
     * @return string Table name with prefix.
     */
    public static function get_declarations_table_name() {
        global $wpdb;
        return $wpdb->prefix . 'freefactu_declarations';
    }

    /**
     * Check if the declarations table exists.
     *
     * @return bool True if table exists, false otherwise.
     */
    public static function declarations_table_exists() {
        $exists = get_transient( 'freefactu_declarations_table_exists' );
        if ( $exists !== false ) {
            return (bool) $exists;
        }

        global $wpdb;
        $table_name = self::get_declarations_table_name();
        $result = $wpdb->get_var( $wpdb->prepare( "SHOW TABLES LIKE %s", $table_name ) ) === $table_name; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

        set_transient( 'freefactu_declarations_table_exists', $result ? 1 : 0, DAY_IN_SECONDS );

        return $result;
    }

    /**
     * Create the declarations database table.
     *
     * @return void
     */
    public static function create_declarations_table() {
        global $wpdb;
        $table_name = self::get_declarations_table_name();
        $charset_collate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE $table_name (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            declaration_date datetime NOT NULL,
            operator_name varchar(255) NOT NULL,
            operator_nif varchar(20) NOT NULL,
            software_name varchar(100) DEFAULT 'Freefactu' NOT NULL,
            software_version varchar(20) NOT NULL,
            software_id_code varchar(20) DEFAULT 'FF' NOT NULL,
            installation_number varchar(10) DEFAULT '001' NOT NULL,
            producer_name varchar(255) NOT NULL,
            producer_nif varchar(20) NOT NULL,
            producer_address varchar(500) DEFAULT '' NOT NULL,
            producer_postal_code varchar(20) DEFAULT '' NOT NULL,
            producer_city varchar(255) DEFAULT '' NOT NULL,
            producer_phone varchar(50) DEFAULT '' NOT NULL,
            producer_email varchar(255) DEFAULT '' NOT NULL,
            producer_website varchar(500) DEFAULT '' NOT NULL,
            declaration_place varchar(255) DEFAULT '' NOT NULL,
            declaration_content longtext NOT NULL,
            pdf_path varchar(500) DEFAULT '' NOT NULL,
            zip_path varchar(500) DEFAULT '' NOT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
            PRIMARY KEY  (id),
            KEY idx_version (software_version),
            KEY idx_date (declaration_date)
        ) $charset_collate;";

        require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
        dbDelta( $sql );
        
        delete_transient( 'freefactu_declarations_table_exists' );
    }

    /**
     * Upgrade declarations table for future schema migrations.
     *
     * This method is a placeholder for future database migrations.
     * When adding new columns to the declarations table after the plugin
     * has been distributed, add the migration logic here.
     *
     * Example usage for future migrations:
     * ```php
     * $columns_to_add = array(
     *     'new_column' => "varchar(255) DEFAULT '' NOT NULL",
     * );
     * foreach ( $columns_to_add as $column_name => $column_definition ) {
     *     $column_exists = $wpdb->get_results( "SHOW COLUMNS FROM {$table_name} LIKE '{$column_name}'" );
     *     if ( empty( $column_exists ) ) {
     *         $wpdb->query( "ALTER TABLE {$table_name} ADD COLUMN {$column_name} {$column_definition}" );
     *     }
     * }
     * ```
     *
     * @return void
     */
    public static function upgrade_declarations_table() {
        // No migrations needed yet - table schema is complete in create_declarations_table()
        // Add future migrations here when needed after plugin distribution
    }

    /**
     * Insert a new declaration record.
     *
     * @param array $data Declaration data array.
     * @return int|WP_Error Insert ID on success, WP_Error on failure.
     */
    public static function insert_declaration( $data ) {
        global $wpdb;
        $table_name = self::get_declarations_table_name();
        
        // Validate required fields
        $required = array( 'operator_name', 'operator_nif', 'software_version', 'declaration_content' );
        $missing = array();
        foreach ( $required as $field ) {
            if ( empty( $data[ $field ] ) ) {
                $missing[] = $field;
            }
        }
        
        if ( ! empty( $missing ) ) {
            return new WP_Error( 'missing_required_fields', __( 'Missing required fields: ', 'freefactu' ) . implode( ', ', $missing ) );
        }
        
        $result = $wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching 
            $table_name, 
            array( 
                'declaration_date'     => isset( $data['declaration_date'] ) ? $data['declaration_date'] : current_time( 'mysql' ),
                'operator_name'        => $data['operator_name'],
                'operator_nif'         => $data['operator_nif'],
                'software_name'        => isset( $data['software_name'] ) ? $data['software_name'] : 'Freefactu',
                'software_version'     => $data['software_version'],
                'software_id_code'     => isset( $data['software_id_code'] ) ? $data['software_id_code'] : 'FF',
                'installation_number'  => isset( $data['installation_number'] ) ? $data['installation_number'] : '001',
                'producer_name'        => isset( $data['producer_name'] ) ? $data['producer_name'] : $data['operator_name'],
                'producer_nif'         => isset( $data['producer_nif'] ) ? $data['producer_nif'] : $data['operator_nif'],
                'producer_address'     => isset( $data['producer_address'] ) ? $data['producer_address'] : '',
                'producer_postal_code' => isset( $data['producer_postal_code'] ) ? $data['producer_postal_code'] : '',
                'producer_city'        => isset( $data['producer_city'] ) ? $data['producer_city'] : '',
                'producer_phone'       => isset( $data['producer_phone'] ) ? $data['producer_phone'] : '',
                'producer_email'       => isset( $data['producer_email'] ) ? $data['producer_email'] : '',
                'producer_website'     => isset( $data['producer_website'] ) ? $data['producer_website'] : '',
                'declaration_place'    => isset( $data['declaration_place'] ) ? $data['declaration_place'] : '',
                'declaration_content'  => $data['declaration_content'],
            ), 
            array( '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s' ) 
        );
        
        if ( false === $result ) {
            $error_msg = $wpdb->last_error ? $wpdb->last_error : 'Unknown database error';
            return new WP_Error( 'db_insert_error', __( 'Failed to insert declaration into database.', 'freefactu' ), array( 'wpdb_error' => $error_msg ) );
        }
        
        return $wpdb->insert_id;
    }

    /**
     * Get declarations with pagination.
     *
     * @param int $limit Number of declarations to retrieve (default: 20).
     * @param int $offset Offset for pagination (default: 0).
     * @return array Array of declaration objects.
     */
    public static function get_declarations( $limit = 20, $offset = 0 ) {
        global $wpdb;
        $table_name = self::get_declarations_table_name();
        return $wpdb->get_results( $wpdb->prepare( "SELECT * FROM " . esc_sql( $table_name ) . " ORDER BY declaration_date DESC LIMIT %d OFFSET %d", $limit, $offset ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
    }

    /**
     * Count total number of declarations.
     *
     * @return int Total number of declarations.
     */
    public static function count_declarations() {
        global $wpdb;
        $table_name = self::get_declarations_table_name();
        return (int) $wpdb->get_var( "SELECT COUNT(*) FROM " . esc_sql( $table_name ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
    }

    /**
     * Get the latest (most recent) declaration.
     *
     * @return object|null Declaration object or null if none exist.
     */
    public static function get_latest_declaration() {
        global $wpdb;
        $table_name = self::get_declarations_table_name();
        return $wpdb->get_row( "SELECT * FROM " . esc_sql( $table_name ) . " ORDER BY declaration_date DESC LIMIT 1" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
    }

    /**
     * Get a single declaration by ID.
     *
     * @param int $id Declaration database ID.
     * @return object|null Declaration object or null if not found.
     */
    public static function get_declaration_by_id( $id ) {
        global $wpdb;
        $table_name = self::get_declarations_table_name();
        return $wpdb->get_row( $wpdb->prepare( "SELECT * FROM " . esc_sql( $table_name ) . " WHERE id = %d", $id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
    }

    /**
     * Get all certified software versions (versions that have declarations).
     *
     * @return array Array of version strings.
     */
    public static function get_certified_versions() {
        global $wpdb;
        $table_name = self::get_declarations_table_name();
        $results = $wpdb->get_col( "SELECT DISTINCT software_version FROM " . esc_sql( $table_name ) . " ORDER BY software_version DESC" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        return $results ? $results : array();
    }

    /**
     * Check if a specific version has been certified (has at least one declaration).
     *
     * @param string $version Software version to check.
     * @return bool True if version is certified, false otherwise.
     */
    public static function is_version_certified( $version ) {
        global $wpdb;
        $table_name = self::get_declarations_table_name();
        $count = $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM " . esc_sql( $table_name ) . " WHERE software_version = %s", $version ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        return $count > 0;
    }

    /**
     * Get declaration for a specific version.
     *
     * @param string $version Software version.
     * @return object|null Latest declaration for this version or null.
     */
    public static function get_declaration_by_version( $version ) {
        global $wpdb;
        $table_name = self::get_declarations_table_name();
        return $wpdb->get_row( $wpdb->prepare( "SELECT * FROM " . esc_sql( $table_name ) . " WHERE software_version = %s ORDER BY declaration_date DESC LIMIT 1", $version ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
    }

    /**
     * Get all unique software versions from existing declarations.
     *
     * @return array Array of version strings, sorted in descending order.
     */
    public static function get_all_declaration_versions() {
        global $wpdb;
        $table_name = self::get_declarations_table_name();
        
        if ( ! self::declarations_table_exists() ) {
            return array();
        }
        
        $versions = $wpdb->get_col( "SELECT DISTINCT software_version FROM " . esc_sql( $table_name ) . " ORDER BY declaration_date DESC" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        return $versions ? $versions : array();
    }

    /**
     * Get the latest (highest) version from declarations using semantic versioning.
     *
     * @return string|null The highest version string or null if no declarations exist.
     */
    public static function get_latest_declaration_version() {
        $versions = self::get_all_declaration_versions();
        
        if ( empty( $versions ) ) {
            return null;
        }
        
        // Sort versions using version_compare for semantic versioning
        usort( $versions, 'version_compare' );
        
        // Return the highest version (last after sorting)
        return end( $versions );
    }

    /**
     * Get invoices that need snapshots.
     *
     * What this function does:
     * - Retrieves invoices that have CSV but no snapshot
     * - Limits result by $limit
     *
     * Where it's called from:
     * - class-freefactu-snapshot-utility.php: bulk_create_snapshots() method
     *
     * @param int $limit Maximum number of invoices to retrieve.
     * @return array Array of invoice objects (order_id only).
     */
    public static function get_invoices_needing_snapshot( $limit = 100 ) {
        global $wpdb;
        $table_name = self::get_table_name();
        
        $cache_key = 'freefactu_invoices_needing_snapshot_limit_' . $limit;
        $results = wp_cache_get( $cache_key, 'freefactu_db' );

        if ( false === $results ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $results = $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT order_id FROM " . esc_sql( $table_name ) . "
                    WHERE (invoice_snapshot IS NULL OR invoice_snapshot = '')
                    AND csv != ''
                    LIMIT %d",
                    $limit
                )
            );
            wp_cache_set( $cache_key, $results, 'freefactu_db', 60 ); // Cache for 1 minute
        }
        
        return $results;
    }

    /**
     * Count invoices with snapshots.
     *
     * Where it's called from:
     * - class-freefactu-snapshot-utility.php: get_statistics() method
     *
     * @return int Count of invoices with snapshots.
     */
    public static function count_invoices_with_snapshots() {
        global $wpdb;
        $table_name = self::get_table_name();
        
        $count = wp_cache_get( 'freefactu_count_snapshots', 'freefactu_db' );

        if ( false === $count ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $count = (int) $wpdb->get_var(
                "SELECT COUNT(*) FROM " . esc_sql( $table_name ) . "
                WHERE invoice_snapshot IS NOT NULL AND invoice_snapshot != ''"
            );
            wp_cache_set( 'freefactu_count_snapshots', $count, 'freefactu_db', 300 ); // Cache for 5 minutes
        }

        return $count;
    }

    /**
     * Count invoices that need snapshots (have CSV but no snapshot).
     *
     * Where it's called from:
     * - class-freefactu-snapshot-utility.php: get_statistics() method
     *
     * @return int Count of invoices needing snapshots.
     */
    public static function count_invoices_needing_snapshots() {
        global $wpdb;
        $table_name = self::get_table_name();
        
        $count = wp_cache_get( 'freefactu_count_needing_snapshots', 'freefactu_db' );
        
        if ( false === $count ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $count = (int) $wpdb->get_var(
                "SELECT COUNT(*) FROM " . esc_sql( $table_name ) . "
                WHERE (invoice_snapshot IS NULL OR invoice_snapshot = '')
                AND csv != ''"
            );
            wp_cache_set( 'freefactu_count_needing_snapshots', $count, 'freefactu_db', 300 ); // Cache for 5 minutes
        }

        return $count;
    }

    /**
     * Get the previous invoice in the chain (with hash).
     *
     * What this function does:
     * - Retrieves the previous invoice based on ID (to find the immediate predecessor)
     * - Ensures the previous invoice has a valid hash
     * - Used to verify chain integrity
     *
     * Where it's called from:
     * - includes/admin/views/debug-invoice-page.php
     *
     * @param int $current_id Current invoice ID.
     * @return object|null Previous invoice object or null.
     */
    public static function get_previous_chained_invoice( $current_id ) {
        global $wpdb;
        $table_name = self::get_table_name();
        
        $cache_key = 'freefactu_prev_invoice_' . $current_id;
        $row = wp_cache_get( $cache_key, 'freefactu_db' );

        if ( false === $row ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $row = $wpdb->get_row( $wpdb->prepare(
                "SELECT invoice_number, hash FROM " . esc_sql( $table_name ) . " WHERE id < %d AND hash != '' ORDER BY id DESC LIMIT 1",
                $current_id
            ) );
            wp_cache_set( $cache_key, $row, 'freefactu_db', DAY_IN_SECONDS );
        }
        
        return $row;
    }

    /**
     * Placeholder for future data migrations.
     *
     * Use this section to add data migration methods after the plugin
     * has been distributed. Data migrations are different from schema
     * migrations (see upgrade_declarations_table) - they transform
     * existing data rather than adding new columns.
     *
     * Example: migrate_pdf_paths_to_relative() to convert absolute paths
     * to relative paths for better portability.
     */
}
