<?php
defined( 'ABSPATH' ) or die( 'No script kiddies please!' );

/**
 * FILE: database.php
 * * Handles all database interactions, schema definitions, and external data fetching 
 * for the Smart Convert – Currency & Unit Conversion plugin.
 * * Key Responsibilities:
 * 1. Defining Database Schema (via dbDelta).
 * 2. Managing Data Sources with Failover Strategies.
 * 3. Fetching and parsing remote CSV data (Currencies & Units).
 * 4. CRUD operations for Templates.
 * 5. Data retrieval with high-performance Object Caching.
 */

// === CONSTANTS FOR CSV SOURCES (FAILOVER STRATEGY) ===

/**
 * Currency Data Sources Configuration
 * * Implements a prioritized failover strategy for fetching currency exchange rates.
 * The fetcher will attempt sources in the order defined here.
 * * Priority 1: Redirect -> Dynamic API endpoint that handles load balancing.
 * Priority 2: Local Server -> Static fallback file with date-based versioning.
 * Priority 3: Direct Google Sheets -> Raw data source (slowest, used as last resort).
 */
define('SMART_CONVERT_CURRENCY_SOURCES', [
    'redirect' => 'https://petrnovak.cz/plugins/smart-convert-currency-unit-conversion/api/v1/redirect.php?key=50KiFzWcrME6aZDZ6ld2lVsgiUO70bMyhFvNgHej75hYDczdWJqWjeKm2wA1Tzhf',
    'local'    => 'https://petrnovak.cz/plugins/smart-convert-currency-unit-conversion/api/v1/data/rates-[yyyymmdd].csv',
    'direct'   => 'https://docs.google.com/spreadsheets/d/e/2PACX-1vSEbKF0JtKLMnqaUH--maC_c4eFo7OJRsAp9ZtBpA-vInMNm6IYUVhWpe6FSGjSbQi1GAN6Axm2Z7Z5/pub?gid=1632771054&single=true&output=csv'
]);

/**
 * Units Data Sources Configuration
 * * Similar strategy as currencies, but specifically for unit conversion factors.
 * * Priority 1: Redirect -> Main API gateway.
 * Priority 2: Local Redirect -> Secondary gateway for redundancy.
 * Priority 3: Direct Google Sheets -> Raw CSV backup.
 */
define('SMART_CONVERT_UNITS_SOURCES', [
    'redirect'       => 'https://petrnovak.cz/plugins/smart-convert-currency-unit-conversion/api/v1/redirect-units.php?key=Hpms6gtpyzudXiiNPxP019MjyeKM52fSDPRna0nqkxffkCSS',
    'redirect_local' => 'https://petrnovak.cz/plugins/smart-convert-currency-unit-conversion/api/v1/redirect-units-local.php?key=A9MYmW0bX0QXbIRyvhxvdo0jytmPgkKs78f5',
    'direct'         => 'https://docs.google.com/spreadsheets/d/e/2PACX-1vR0ihGbvlg5-LUZYRI88-xpSrbXG0N-yZwWFsbCRlFx0jtycHuqi3bG5ZVobUd37vs_zuq3w5LxjS0R/pub?gid=165849114&single=true&output=csv'
]);

/**
 * Legacy Constants
 * * These are retained strictly for backward compatibility. 
 * External integrations or older add-ons might rely on these specific constant names.
 * @deprecated Use SMART_CONVERT_CURRENCY_SOURCES or SMART_CONVERT_UNITS_SOURCES instead.
 */
define('SMART_CONVERT_CURRENCY_CSV_URL', SMART_CONVERT_CURRENCY_SOURCES['direct']);
define('SMART_CONVERT_UNITS_CSV_URL', SMART_CONVERT_UNITS_SOURCES['direct']);

// === TABLE CREATION ON PLUGIN ACTIVATION ===

register_activation_hook( dirname( dirname( __FILE__ ) ) . '/index.php', 'smart_convert_currency_unit_conversion_create_tables' );

/**
 * Creates or updates the plugin database tables.
 * * Uses WordPress `dbDelta` function. This is critical because `dbDelta` 
 * analyzes the current table structure and compares it to the SQL query. 
 * It then executes only the necessary ALTER commands to update the schema 
 * without losing existing data.
 * * Tables created:
 * 1. smart_convert_currency_unit_conversion_currencies: Stores exchange rates.
 * 2. smart_convert_currency_unit_conversion_units: Stores unit conversion factors (includes support for custom units).
 * 3. smart_convert_currency_unit_conversion_templates: Stores formatting templates for output.
 */
function smart_convert_currency_unit_conversion_create_tables() {
    global $wpdb;
    $charset_collate = $wpdb->get_charset_collate();

    // Table 1: Currency Rates
    // Stores the exchange rate between two currencies.
    // Updated to DECIMAL(30,16) to support high precision crypto rates (e.g. 0.00000001 BTC).
    $sql1 = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}smart_convert_currency_unit_conversion_currencies (
        id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        from_currency varchar(10) NOT NULL,
        to_currency varchar(10) NOT NULL,
        rate decimal(30,16) NOT NULL DEFAULT '0.0000000000000000',
        symbol_before varchar(10) NULL,
        symbol_after varchar(10) NULL,
        updated_at datetime NOT NULL,
        PRIMARY KEY  (id),
        KEY  from_to (from_currency, to_currency)
    ) $charset_collate;";
    
    // Table 2: Unit Conversions
    // Updated structure to support user-defined Custom Units.
    // Updated to DECIMAL(30,16) for consistency and high precision scientific units.
    $sql2 = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}smart_convert_currency_unit_conversion_units (
        id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        unit_from varchar(30) NOT NULL,
        unit_to varchar(30) NOT NULL,
        factor decimal(30,16) NOT NULL DEFAULT '0.0000000000000000',
        offset decimal(30,16) NULL DEFAULT '0.0000000000000000',
        
        -- Legacy column (kept for compatibility with older DB versions)
        symbol_after varchar(50) NULL, 
        
        -- New columns for Custom Units UI and formatting details
        unit_from_name varchar(100) NULL,
        symbol_from_before varchar(20) NULL,
        symbol_from_after varchar(20) NULL,
        
        unit_to_name varchar(100) NULL,
        symbol_to_before varchar(20) NULL,
        symbol_to_after varchar(20) NULL,
        
        -- Flag to distinguish between built-in CSV units and user-created units
        is_custom tinyint(1) NOT NULL DEFAULT 0,
        
        updated_at datetime NOT NULL,
        PRIMARY KEY  (id),
        KEY  unit_from_to (unit_from, unit_to),
        KEY  is_custom (is_custom)
    ) $charset_collate;";

    // Table 3: Output Templates
    // Stores visual formatting strings (e.g., "{FROM} = {TO}").
    // Supports built-in (readonly) and custom (editable) types.
    $sql3 = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}smart_convert_currency_unit_conversion_templates (
        id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        template_key varchar(100) NOT NULL,
        template_name varchar(200) NOT NULL,
        template_output text NOT NULL,
        template_description text NULL,
        template_type varchar(20) NOT NULL DEFAULT 'custom',
        is_default_currency tinyint(1) NOT NULL DEFAULT 0,
        is_default_units tinyint(1) NOT NULL DEFAULT 0,
        created_at datetime NOT NULL,
        modified_at datetime NOT NULL,
        PRIMARY KEY  (id),
        UNIQUE KEY  template_key (template_key),
        KEY  template_type (template_type)
    ) $charset_collate;";

    require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    
    // Execute schema updates
    dbDelta($sql1);
    dbDelta($sql2); // Applies new columns to existing tables if necessary
    dbDelta($sql3);
    
    // Seed database with factory default templates
    smart_convert_currency_unit_conversion_initialize_builtin_templates();
}

/**
 * Seeds the `smart_convert_currency_unit_conversion_templates` table with default (built-in) templates.
 * * This function runs on activation but includes a guard clause to prevent
 * duplication. If built-in templates already exist, it aborts.
 * This ensures we don't overwrite user preferences if they reactivate the plugin.
 */
function smart_convert_currency_unit_conversion_initialize_builtin_templates() {
    global $wpdb;
    
    // Define cache parameters for the existence check
    $cache_key   = 'smart_convert_currency_unit_conversion_builtin_templates_count';
    $cache_group = 'smart_convert_currency_unit_conversion_activation';

    // Attempt to retrieve count from cache to satisfy PCP requirements
    $count = wp_cache_get( $cache_key, $cache_group );

    if ( false === $count ) {
        // Cache miss: Query the database
        // Fixed: Use prepare() even for static strings to satisfy security checkers regarding table variables
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Custom table usage requires direct query.
        $count = $wpdb->get_var( 
            $wpdb->prepare( 
                "SELECT COUNT(*) FROM {$wpdb->prefix}smart_convert_currency_unit_conversion_templates WHERE template_type = %s", 
                'builtin' 
            ) 
        );
        // Cache the result briefly (e.g., 60 seconds is enough for activation scope)
        wp_cache_set( $cache_key, $count, $cache_group, 60 );
    }

    if ($count > 0) {
        return;
    }
    
    $now = current_time('mysql');
    
    // Define factory templates
    $builtin_templates = array(
        array(
            'template_key' => 'default',
            // translators: English: "Original amount (with tooltip and conversion in parentheses)". Usage: Default currency template name reflecting its parenthetical and interactive tooltip structure. This generic interface text belongs to template management and may appear in admin screens or frontend outputs. It contains no dynamic placeholders, usually forms a short noun phrase or sentence, and requires a concise, readable translation.
            'template_name' => __( 'Original amount (with tooltip and conversion in parentheses)', 'smart-convert-currency-unit-conversion' ),
            'template_output' => '{FROM}&nbsp;(||{TO}##{RATE_INFO}##||)',
            // translators: English: "The original amount including the currency symbol is displayed, followed by the converted amount in parentheses. Hovering over the content in parentheses displays a tooltip with exchange rate information.". Usage: Explanatory helper text detailing the default currency template, which shows the original amount alongside an interactive, parenthetical conversion. Addressed to administrators, it appears next to or below form fields in Smart Convert admin screens or block editor controls. It lacks dynamic placeholders, functions as a short phrase or sentence, and can be longer than standard labels while remaining focused.
            'template_description' => __( 'The original amount including the currency symbol is displayed, followed by the converted amount in parentheses. Hovering over the content in parentheses displays a tooltip with exchange rate information.', 'smart-convert-currency-unit-conversion' ),
            'template_type' => 'builtin',
            'is_default_currency' => 1, // Set as default for Currency
            'is_default_units' => 0,
            'created_at' => $now,
            'modified_at' => $now
        ),
        array(
            'template_key' => 'units',
            // translators: English: "Converted value with unit". Name of the default template for units (e.g., "37.9 °C"). This concise phrase belongs to unit and currency management and may appear in admin screens or frontend outputs without dynamic placeholders.
            'template_name' => __( 'Converted value with unit', 'smart-convert-currency-unit-conversion' ),
            'template_output' => '{TO}',
            // translators: English: "Only the converted value with the corresponding unit is displayed. Neither the original value nor the original unit is shown.". Explanatory text for site administrators clarifying the default unit template's behavior. This clear hint belongs to unit and currency management, appearing near form fields in admin screens or block controls without dynamic placeholders.
            'template_description' => __( 'Only the converted value with the corresponding unit is displayed. Neither the original value nor the original unit is shown.', 'smart-convert-currency-unit-conversion' ),
            'template_type' => 'builtin',
            'is_default_currency' => 0,
            'is_default_units' => 1, // Set as default for Units
            'created_at' => $now,
            'modified_at' => $now
        ),
        // Additional stylistic variations
        array(
            'template_key' => 'builtin_1',
            // translators: English: "Original amount with conversion in parentheses". Usage: Template name reflecting its structure: original amount followed by the conversion in parentheses. This generic interface text belongs to template management and may appear in admin screens or frontend outputs. It lacks dynamic placeholders, usually acts as a short noun phrase or sentence, and requires a concise, readable translation without strict length limits.
            'template_name' => __( 'Original amount with conversion in parentheses', 'smart-convert-currency-unit-conversion' ),
            'template_output' => '{FROM} ({TO})',
            // translators: English: "Displays the original amount including the unit, followed by the conversion to the target currency in parentheses. The tooltip and exchange rate information are not displayed.". Usage: Explanatory helper text detailing a template that shows the original amount first, with the conversion in parentheses (omitting tooltips and rate info). Addressed to administrators, it appears near form fields in Smart Convert admin screens or block editor controls. It contains no dynamic placeholders, forms a short phrase or sentence, and can exceed standard label lengths while staying clear.
            'template_description' => __( 'Displays the original amount including the unit, followed by the conversion to the target currency in parentheses. The tooltip and exchange rate information are not displayed.', 'smart-convert-currency-unit-conversion' ),
            'template_type' => 'builtin',
            'is_default_currency' => 0,
            'is_default_units' => 0,
            'created_at' => $now,
            'modified_at' => $now
        ),
        array(
            'template_key' => 'builtin_2',
            // translators: English: "Only conversion with currency symbol". Usage: Template name indicating it only displays the converted amount and symbol. This generic interface string belongs to template management and may appear in admin screens or frontend outputs. It lacks dynamic placeholders, typically functions as a short noun phrase or sentence, and should be kept concise and easy to read without strict length limits.
            'template_name' => __( 'Only conversion with currency symbol', 'smart-convert-currency-unit-conversion' ),
            'template_output' => '{TO}',
            // translators: English: "Only the converted amount with the currency symbol is displayed. The tooltip and exchange rate information are not displayed.". Usage: Explanatory helper text describing a template that exclusively shows the converted amount and symbol (omitting tooltips and original amounts). Addressed to administrators, it appears next to or below form fields in Smart Convert admin screens or block editor controls. It contains no dynamic placeholders, forms a short phrase or sentence, and can be slightly longer than standard labels while remaining focused.
            'template_description' => __( 'Only the converted amount with the currency symbol is displayed. The tooltip and exchange rate information are not displayed.', 'smart-convert-currency-unit-conversion' ),
            'template_type' => 'builtin',
            'is_default_currency' => 0,
            'is_default_units' => 0,
            'created_at' => $now,
            'modified_at' => $now
        ),
        array(
            'template_key' => 'builtin_3',
            // translators: English: "Only original amount with currency symbol". Usage: Template name for a layout displaying solely the original amount and symbol. This generic interface text belongs to template management and may appear in admin screens or frontend outputs. It lacks dynamic placeholders, usually forms a short noun phrase or sentence, and requires a concise, readable translation without strict length limits.
            'template_name' => __( 'Only original amount with currency symbol', 'smart-convert-currency-unit-conversion' ),
            'template_output' => '{FROM}',
            // translators: English: "Only the original amount with the currency symbol is displayed. The tooltip is not displayed.". Usage: Explanatory helper text defining a template that shows only the original amount and symbol (omitting tooltips and conversions). Directed at administrators, it appears near form fields in Smart Convert admin screens or block editor controls. It contains no dynamic placeholders, acts as a short phrase or sentence, and may exceed standard label lengths while staying clear.
            'template_description' => __( 'Only the original amount with the currency symbol is displayed. The tooltip is not displayed.', 'smart-convert-currency-unit-conversion' ),
            'template_type' => 'builtin',
            'is_default_currency' => 0,
            'is_default_units' => 0,
            'created_at' => $now,
            'modified_at' => $now
        ),
        array(
            'template_key' => 'builtin_4',
            // translators: English: "Original amount with conversion in tooltip". Usage: Template name highlighting an interactive layout: original amount visible, conversion hidden in a tooltip. This generic interface text belongs to template management and may appear in admin screens or frontend outputs. It lacks dynamic placeholders, typically acts as a short noun phrase or sentence, and should remain concise and readable.
            'template_name' => __( 'Original amount with conversion in tooltip', 'smart-convert-currency-unit-conversion' ),
            'template_output' => '||{FROM}##<strong>{TO}</strong><br>{RATE_INFO}##||',
            // translators: English: "Displays the original amount including the currency symbol. Hovering over the original amount displays a tooltip showing the conversion and the exchange rate used.". Usage: Explanatory helper text for a template showing the original amount, with an interactive tooltip revealing the conversion and exchange rate upon hover. Addressed to administrators, it appears near form fields in Smart Convert admin screens or block editor controls. It contains no dynamic placeholders, functions as a short phrase or sentence, and can be longer than standard labels while remaining clear and focused.
            'template_description' => __( 'Displays the original amount including the currency symbol. Hovering over the original amount displays a tooltip showing the conversion and the exchange rate used.', 'smart-convert-currency-unit-conversion' ),
            'template_type' => 'builtin',
            'is_default_currency' => 0, 
            'is_default_units' => 0,
            'created_at' => $now,
            'modified_at' => $now
        ),
        array(
            'template_key' => 'builtin_5',
            // translators: English: "Two lines - original on first, conversion on second". Usage: Name of a multiline template displaying original and converted values separately. This generic interface string belongs to template management and may appear in admin screens or frontend outputs. It lacks dynamic placeholders, usually functions as a short noun phrase or sentence, and must be kept concise and readable without strict length limits.
            'template_name' => __( 'Two lines - original on first, conversion on second', 'smart-convert-currency-unit-conversion' ),
            'template_output' => '{FROM}<br>||{TO}##<strong>{TO}</strong><br>{RATE_INFO}##||',
            // translators: English: "Displays two lines. The first line shows the original amount including the unit, the second line shows the conversion. Hovering over the entire second line displays a tooltip with exchange rate information.". Usage: Explanatory helper text detailing a two-line template structure with an interactive tooltip on the second line. Directed at administrators, it appears next to or below form fields in Smart Convert admin screens or block editor controls. It contains no dynamic placeholders, forms a short phrase or sentence, and can exceed standard label lengths while staying clear.
            'template_description' => __( 'Displays two lines. The first line shows the original amount including the unit, the second line shows the conversion. Hovering over the entire second line displays a tooltip with exchange rate information.', 'smart-convert-currency-unit-conversion' ),
            'template_type' => 'builtin',
            'is_default_currency' => 0,
            'is_default_units' => 0,
            'created_at' => $now,
            'modified_at' => $now
        ),
        array(
            'template_key' => 'builtin_6',
            // translators: English: "Conversion with original amount in tooltip". Usage: Template name reflecting a layout focused on the conversion, with the original amount hidden in a tooltip. This generic interface text belongs to template management and may appear in admin screens or frontend outputs. It lacks dynamic placeholders, typically forms a short noun phrase or sentence, and requires a concise, readable translation.
            'template_name' => __( 'Conversion with original amount in tooltip', 'smart-convert-currency-unit-conversion' ),
            'template_output' => '||{TO}##<strong>{FROM}</strong><br>{RATE_INFO}##||',
            // translators: English: "Displays the conversion including the currency symbol. Hovering over the conversion displays a tooltip showing the original amount and the exchange rate used.". Usage: Explanatory helper text describing a template prioritizing the conversion, with an interactive tooltip revealing the original amount and exchange rate. Addressed to administrators, it appears near form fields in Smart Convert admin screens or block editor controls. It contains no dynamic placeholders, acts as a short phrase or sentence, and can be slightly longer than standard labels while remaining focused.
            'template_description' => __( 'Displays the conversion including the currency symbol. Hovering over the conversion displays a tooltip showing the original amount and the exchange rate used.', 'smart-convert-currency-unit-conversion' ),
            'template_type' => 'builtin',
            'is_default_currency' => 0,
            'is_default_units' => 0,
            'created_at' => $now,
            'modified_at' => $now
        )
    );
    
    foreach ($builtin_templates as $template) {
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Custom table insertion requires direct query.
        $wpdb->insert($wpdb->prefix . 'smart_convert_currency_unit_conversion_templates', $template);
    }
    
    // Clear template caches to ensure new data is visible immediately if cached
    wp_cache_delete('smart_convert_currency_unit_conversion_all_templates', 'smart_convert_currency_unit_conversion_templates');
    // Also clear the activation check cache so future checks are accurate
    wp_cache_delete( $cache_key, $cache_group );
}

// === ROBUST CSV FETCHING WITH FAILOVER & RETRY ===

/**
 * Universal CSV Fetcher with Advanced Resilience Strategies.
 * * This function is the core of the external data synchronization. It implements
 * a high-reliability fetching mechanism to handle network instability or 
 * source downtime.
 * * Features:
 * 1. **Multiple Source Failover:** Iterates through a provided list of URLs.
 * 2. **Date Fallback:** Handles `[yyyymmdd]` placeholders to try Today's data, defaulting to Yesterday's if missing.
 * 3. **Retry Logic:** Attempts each specific URL twice before giving up.
 * 4. **Header Spoofing:** Mimics a standard browser/WP User-Agent to bypass basic firewall blocks.
 * 5. **Validation:** Checks content for HTML errors (common when proxies return 403/404 pages instead of CSV).
 * * @param array|string $sources Array of URLs (priority ordered) or a single URL string.
 * @return array|false Returns ['data' => parsed_array, 'source' => used_url] on success, OR false on total failure.
 */
function smart_convert_currency_unit_conversion_fetch_csv_data_robust($sources) {
    if (!is_array($sources)) {
        $sources = [$sources];
    }

    $common_args = [
        'timeout'     => 15, // Extended timeout to account for redirects and slow responses
        'redirection' => 5,
        'httpversion' => '1.1',
        'sslverify'   => true, // Required for secure connections (HTTPS)
        'headers'     => [
            // Standard WordPress User-Agent string: "WordPress/Version; Site URL"
            // Identifies legitimate traffic from the plugin.
            'User-Agent'      => 'WordPress/' . get_bloginfo('version') . '; ' . get_bloginfo('url'),
            'Accept'          => 'text/csv,text/plain;q=0.9,*/*;q=0.8',
            'Cache-Control'   => 'no-cache', // Force fresh request, bypass proxy caches
            'Pragma'          => 'no-cache'
        ]
    ];

    foreach ($sources as $key => $raw_url) {
        // Handle date placeholders in URL [yyyymmdd]
        // Strategy: If the URL expects a date, try Today (Priority), then fallback to Yesterday.
        // Use gmdate() instead of date() to avoid server timezone inconsistencies.
        $urls_to_try = [];
        
        if (strpos($raw_url, '[yyyymmdd]') !== false) {
            $urls_to_try[] = str_replace('[yyyymmdd]', gmdate('Ymd'), $raw_url); // Today (UTC)
            $urls_to_try[] = str_replace('[yyyymmdd]', gmdate('Ymd', strtotime('-1 day')), $raw_url); // Yesterday (UTC)
        } else {
            $urls_to_try[] = $raw_url;
        }

        foreach ($urls_to_try as $url) {
            // RETRY LOGIC: Try each specific URL twice to handle transient network blips
            for ($attempt = 1; $attempt <= 2; $attempt++) {
                
                if ($attempt > 1) {
                    sleep(1); // Brief pause before retry
                }

                $response = wp_remote_get($url, $common_args);

                if (is_wp_error($response)) {
                    continue; // Fail, try next attempt or next URL
                }

                $code = wp_remote_retrieve_response_code($response);
                if ($code !== 200) {
                    continue; // Fail, try next attempt or next URL
                }

                $csv_content = wp_remote_retrieve_body($response);

                // Content Validation:
                // Ensure we received actual data and not an HTML error page 
                // (e.g., Google Sheets error page or Server Default page).
                if (empty($csv_content) || 
                    stripos($csv_content, '<!DOCTYPE') !== false || 
                    stripos($csv_content, '<html') !== false ||
                    stripos($csv_content, '"error":') !== false) {
                    
                    continue;
                }


                // Parse CSV content into an array
                $data = smart_convert_currency_unit_conversion_parse_csv_content($csv_content);
                
                if ($data !== false && count($data) > 0) {
                    // Success! Return data immediately and stop trying other sources.
                    return [
                        'data'   => $data,
                        'source' => $url, // Return which specific URL succeeded for logging/debug
                        'key'    => $key
                    ];
                }
            } // End retry loop
        } // End date variants loop
    } // End sources loop

    return false; // Total failure: All sources and retries failed
}

/**
 * Helper: Parses raw CSV string into an Associative Array.
 * * @param string $csv_string The raw CSV text content.
 * @return array|false Array of associative arrays (Header => Value), or false on failure.
 */
function smart_convert_currency_unit_conversion_parse_csv_content($csv_string) {
    // Break string into rows
    $rows = array_map('str_getcsv', explode("\n", trim($csv_string)));
    
    // Filter out completely empty rows
    $rows = array_filter($rows, function($row) {
        return !empty($row) && !empty(array_filter($row));
    });

    if (empty($rows)) return false;

    // Extract headers from the first row
    $header = array_map('trim', array_shift($rows));
    $data = [];

    // Map remaining rows to the header
    foreach ($rows as $row) {
        if (count($row) !== count($header)) continue; // Skip malformed rows
        $item = array_combine($header, $row);
        // Ensure the primary key column (first column) is not empty
        if ($item && !empty($item[$header[0]])) $data[] = $item;
    }
    return $data;
}

// === WRAPPERS FOR BACKWARD COMPATIBILITY ===

/**
 * Wrapper for fetching Currency CSV data.
 * Abstraction layer that utilizes the defined constant sources list.
 * * @param mixed $unused_url Deprecated parameter, ignored in favor of robust sources.
 * @return array|false Parsed data or false.
 */
function smart_convert_currency_unit_conversion_fetch_currency_csv_data($unused_url = null) {
    // Ignore passed URL, use the centralized robust source list
    $result = smart_convert_currency_unit_conversion_fetch_csv_data_robust(SMART_CONVERT_CURRENCY_SOURCES);
    return ($result) ? $result['data'] : false;
}

/**
 * Wrapper for fetching Units CSV data.
 * * @param mixed $unused_url Deprecated parameter.
 * @return array|false Parsed data or false.
 */
function smart_convert_currency_unit_conversion_fetch_units_csv_data($unused_url = null) {
    $result = smart_convert_currency_unit_conversion_fetch_csv_data_robust(SMART_CONVERT_UNITS_SOURCES);
    return ($result) ? $result['data'] : false;
}

/**
 * Original generic CSV fetcher.
 * Maintained for backward compatibility. If called directly with a URL,
 * it treats it as a single-source request.
 * * @param string $url The URL to fetch.
 * @return array|false
 */
function smart_convert_currency_unit_conversion_fetch_csv_data($url) {
    // Treat the direct URL as a single-item source array
    $result = smart_convert_currency_unit_conversion_fetch_csv_data_robust([$url]);
    return ($result) ? $result['data'] : false;
}


// === DB READ HELPERS (WITH CACHING) ===

/**
 * Retrieves a currency rate row with Object Caching.
 * * Performance Optimization:
 * Instead of querying the database on every page load (which is expensive),
 * this function first checks WordPress Object Cache (RAM/File).
 * * Cache Strategy:
 * - Key: 'smart_convert_currency_unit_conversion_rate_[from]_[to]'
 * - Group: 'smart_convert_currency_unit_conversion'
 * - TTL: 1 Hour (3600 seconds)
 * * @param string $from Source currency code (e.g., USD).
 * @param string $to Target currency code (e.g., EUR).
 * @return array|null Database row or null if not found.
 */
function smart_convert_currency_unit_conversion_get_currency_rate_row($from, $to) {
    global $wpdb;

    // 1. Define Cache Identifiers
    $cache_key = 'smart_convert_currency_unit_conversion_rate_' . $from . '_' . $to;
    $cache_group = 'smart_convert_currency_unit_conversion';

    // 2. Attempt to retrieve from Cache
    $cached = wp_cache_get( $cache_key, $cache_group );
    if ( false !== $cached ) {
        return $cached;
    }

    // 3. Cache Miss: Query the Database
    // Fixed: Combine prepare and get_row, remove intermediate $table and $query vars to satisfy security checker
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Custom table usage requires direct query.
    $row = $wpdb->get_row(
        $wpdb->prepare(
            "SELECT * FROM {$wpdb->prefix}smart_convert_currency_unit_conversion_currencies WHERE from_currency = %s AND to_currency = %s",
            $from, 
            $to
        ), 
        ARRAY_A
    );

    // 4. Save result to Cache
    // We cache the result (even if null) to prevent "cache stampedes" on non-existent pairs.
    wp_cache_set( $cache_key, $row, $cache_group, 3600 );

    return $row;
}

/**
 * Retrieves a unit conversion row with Object Caching.
 * * Follows the same caching strategy as currencies to minimize DB load.
 * * @param string $from Source unit slug.
 * @param string $to Target unit slug.
 * @return array|null Database row or null.
 */
function smart_convert_currency_unit_conversion_get_unit_conversion_row($from, $to) {
    global $wpdb;

    // 1. Define Cache Identifiers
    $cache_key = 'smart_convert_currency_unit_conversion_unit_' . $from . '_' . $to;
    $cache_group = 'smart_convert_currency_unit_conversion';

    // 2. Attempt to retrieve from Cache
    $cached = wp_cache_get( $cache_key, $cache_group );
    if ( false !== $cached ) {
        return $cached;
    }

    // 3. Cache Miss: Query the Database
    // Fixed: Combine prepare and get_row, remove intermediate $table and $query vars
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Custom table usage requires direct query.
    $row = $wpdb->get_row(
        $wpdb->prepare(
            "SELECT * FROM {$wpdb->prefix}smart_convert_currency_unit_conversion_units WHERE unit_from = %s AND unit_to = %s",
            $from,
            $to
        ), 
        ARRAY_A
    );

    // 4. Save result to Cache
    wp_cache_set( $cache_key, $row, $cache_group, 3600 );

    return $row;
}

// === TEMPLATE HELPER FUNCTIONS ===

/**
 * Retrieves all templates, sorted by type (Built-in first) then name.
 * @return array List of template rows.
 */
function smart_convert_currency_unit_conversion_get_all_templates() {
    global $wpdb;
    
    $cache_key = 'smart_convert_currency_unit_conversion_all_templates';
    $cache_group = 'smart_convert_currency_unit_conversion_templates';

    $cached = wp_cache_get($cache_key, $cache_group);
    if (false !== $cached) {
        return $cached;
    }

    // Fixed: Inline table name to avoid "UnescapedDBParameter" on $table variable. 
    // No prepare needed as there are no args, but keeping it direct is key for PCP.
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Custom table usage requires direct query.
    $results = $wpdb->get_results(
        "SELECT * FROM {$wpdb->prefix}smart_convert_currency_unit_conversion_templates ORDER BY template_type ASC, template_name ASC", 
        ARRAY_A
    );

    wp_cache_set($cache_key, $results, $cache_group);
    
    return $results;
}

/**
 * Retrieves a specific template by its unique key string.
 * @param string $template_key
 * @return array|null
 */
function smart_convert_currency_unit_conversion_get_template_by_key($template_key) {
    global $wpdb;
    
    // Cache specific template by key
    $cache_key = 'smart_convert_currency_unit_conversion_template_key_' . $template_key;
    $cache_group = 'smart_convert_currency_unit_conversion_templates';

    $cached = wp_cache_get($cache_key, $cache_group);
    if (false !== $cached) {
        return $cached;
    }

    // Fixed: Nested prepare
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Custom table usage requires direct query.
    $row = $wpdb->get_row(
        $wpdb->prepare(
            "SELECT * FROM {$wpdb->prefix}smart_convert_currency_unit_conversion_templates WHERE template_key = %s",
            $template_key
        ), 
        ARRAY_A
    );

    wp_cache_set($cache_key, $row, $cache_group);

    return $row;
}

/**
 * Retrieves a specific template by its numeric ID.
 * @param int $id
 * @return array|null
 */
function smart_convert_currency_unit_conversion_get_template_by_id($id) {
    global $wpdb;

    // Cache specific template by ID
    $cache_key = 'smart_convert_currency_unit_conversion_template_id_' . $id;
    $cache_group = 'smart_convert_currency_unit_conversion_templates';

    $cached = wp_cache_get($cache_key, $cache_group);
    if (false !== $cached) {
        return $cached;
    }

    // Fixed: Nested prepare
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Custom table usage requires direct query.
    $row = $wpdb->get_row(
        $wpdb->prepare(
            "SELECT * FROM {$wpdb->prefix}smart_convert_currency_unit_conversion_templates WHERE id = %d",
            $id
        ), 
        ARRAY_A
    );

    wp_cache_set($cache_key, $row, $cache_group);

    return $row;
}

/**
 * Gets the designated Default Currency Template.
 * Fallback: If no default is set, returns 'builtin_4'.
 * @return array
 */
function smart_convert_currency_unit_conversion_get_default_currency_template() {
    global $wpdb;

    $cache_key = 'smart_convert_currency_unit_conversion_default_currency_template';
    $cache_group = 'smart_convert_currency_unit_conversion_templates';

    $cached = wp_cache_get($cache_key, $cache_group);
    if (false !== $cached) {
        return $cached;
    }

    // Fixed: Inline table name
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Custom table usage requires direct query.
    $template = $wpdb->get_row(
        "SELECT * FROM {$wpdb->prefix}smart_convert_currency_unit_conversion_templates WHERE is_default_currency = 1 LIMIT 1", 
        ARRAY_A
    );
    
    if (!$template) {
        $template = smart_convert_currency_unit_conversion_get_template_by_key('builtin_4');
    }

    wp_cache_set($cache_key, $template, $cache_group);

    return $template;
}

/**
 * Gets the designated Default Units Template.
 * Fallback: If no default is set, returns 'builtin_4'.
 * @return array
 */
function smart_convert_currency_unit_conversion_get_default_units_template() {
    global $wpdb;

    $cache_key = 'smart_convert_currency_unit_conversion_default_units_template';
    $cache_group = 'smart_convert_currency_unit_conversion_templates';

    $cached = wp_cache_get($cache_key, $cache_group);
    if (false !== $cached) {
        return $cached;
    }

    // Fixed: Inline table name
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Custom table usage requires direct query.
    $template = $wpdb->get_row(
        "SELECT * FROM {$wpdb->prefix}smart_convert_currency_unit_conversion_templates WHERE is_default_units = 1 LIMIT 1", 
        ARRAY_A
    );
    
    if (!$template) {
        $template = smart_convert_currency_unit_conversion_get_template_by_key('builtin_4');
    }

    wp_cache_set($cache_key, $template, $cache_group);

    return $template;
}

/**
 * Validates a template key format.
 * Rules: 1-100 characters, lowercase alphanumeric, dashes, or underscores.
 * * @param string $key
 * @return string|false The sanitized key or false if invalid.
 */
function smart_convert_currency_unit_conversion_validate_template_key($key) {
    $key = strtolower(trim($key));
    if (strlen($key) < 1 || strlen($key) > 100) return false;
    if (!preg_match('/^[a-z0-9_-]+$/', $key)) return false;
    return $key;
}

/**
 * Checks if a template key already exists in the database.
 * * @param string $key The key to check.
 * @param int|null $exclude_id ID to exclude (useful when updating an existing row).
 * @return bool True if key exists, False otherwise.
 */
function smart_convert_currency_unit_conversion_template_key_exists($key, $exclude_id = null) {
    global $wpdb;
    
    $key = strtolower(trim($key));
    
    // Generate a unique cache key for this validation check
    $cache_key = 'smart_convert_currency_unit_conversion_key_exists_' . md5( $key . '_' . (string) $exclude_id );
    $cache_group = 'smart_convert_currency_unit_conversion_validation';

    // Attempt to retrieve from cache
    $cached = wp_cache_get( $cache_key, $cache_group );
    if ( false !== $cached ) {
        return $cached;
    }

    // Fixed: Duplicate logic inside if/else to ensure prepare() is immediately nested in get_var()
    // This is verbose but required to satisfy strict static analysis.
    if ($exclude_id) {
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Custom table usage requires direct query.
        $count = $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COUNT(*) FROM {$wpdb->prefix}smart_convert_currency_unit_conversion_templates WHERE template_key = %s AND id != %d", 
                $key, 
                $exclude_id
            )
        );
    } else {
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Custom table usage requires direct query.
        $count = $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COUNT(*) FROM {$wpdb->prefix}smart_convert_currency_unit_conversion_templates WHERE template_key = %s", 
                $key
            )
        );
    }
    
    $exists = ( $count > 0 );
    
    // Save result to cache with a short TTL (e.g., 10 seconds)
    // This is sufficient for validation flows while satisfying PCP requirements
    wp_cache_set( $cache_key, $exists, $cache_group, 10 );
    
    return $exists;
}

/**
 * Deletes a template from the database.
 * * Includes protection rules:
 * - Cannot delete built-in templates.
 * - Cannot delete the currently active default template.
 * * @param int $id The Template ID.
 * @return true|WP_Error True on success, WP_Error on failure.
 */
function smart_convert_currency_unit_conversion_delete_template($id) {
    global $wpdb;
    
    // Note: smart_convert_currency_unit_conversion_get_template_by_id is now cached, so this check is fast
    $template = smart_convert_currency_unit_conversion_get_template_by_id($id);
    
    if (!$template) {
        // translators: English: "Template not found.". Usage: Declarative error message cleanly explaining a missing template. Belonging to template management, it appears in WordPress admin notices, AJAX responses, or occasionally on the frontend. It contains no dynamic placeholders and requires a concise, readable translation without strict length limits.
        return new WP_Error(
            'not_found',
            __( 'Template not found.', 'smart-convert-currency-unit-conversion' )
        );
    }
    
    // Protection: Prevent deletion of core system templates
    if ($template['template_type'] === 'builtin') {
        // translators: English: "System templates cannot be deleted.". Usage: Declarative error message clearly explaining why an installed default template cannot be removed. Belonging to template management, it is displayed in WordPress admin notices, AJAX responses, or occasionally on the frontend. It contains no dynamic placeholders and must be kept concise and easy to read without strict length limits.
        return new WP_Error(
            'builtin',
            __( 'System templates cannot be deleted.', 'smart-convert-currency-unit-conversion' )
        );
    }
    
    // Protection: Prevent deletion of templates currently active as defaults
    if ($template['is_default_currency'] == 1 || $template['is_default_units'] == 1) {
        // translators: English: "This template cannot be deleted because it is set as default. If you want to delete it, please select another default template first.". Usage: Declarative error message instructing users to select a new default template before attempting deletion. Belonging to template management, it is shown in WordPress admin notices, AJAX responses, or occasionally on the frontend. It lacks dynamic placeholders and requires a concise, readable translation.
        return new WP_Error(
            'is_default',
            __( 'This template cannot be deleted because it is set as default. If you want to delete it, please select another default template first.', 'smart-convert-currency-unit-conversion' )
        );
    }
    
    // Fixed: Use table name directly
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Custom table usage requires direct query.
    $result = $wpdb->delete(
        $wpdb->prefix . 'smart_convert_currency_unit_conversion_templates', 
        array('id' => $id), 
        array('%d')
    );
    
    if ($result === false) {
        // translators: English: "Failed to delete template.". Usage: Declarative error message cleanly explaining a failed template deletion. Belonging to template management, it is displayed in WordPress admin notices, AJAX responses, or occasionally on the frontend. It lacks dynamic placeholders and must be kept concise and easy to read.
        return new WP_Error(
            'db_error',
            __( 'Failed to delete template.', 'smart-convert-currency-unit-conversion' )
        );
    }
    
    // Clear template caches on successful deletion
    // We clear the general list and the specific defaults just in case
    wp_cache_delete('smart_convert_currency_unit_conversion_all_templates', 'smart_convert_currency_unit_conversion_templates');
    wp_cache_delete('smart_convert_currency_unit_conversion_template_id_' . $id, 'smart_convert_currency_unit_conversion_templates');
    wp_cache_delete('smart_convert_currency_unit_conversion_template_key_' . $template['template_key'], 'smart_convert_currency_unit_conversion_templates');
    
    return true;
}
?>