<?php
/**
 * AnyWareX Clone Translate – Languages Manager
 */

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

class awxct_languages
{
    protected $table;

    public function __construct()
    {
        global $wpdb;
        $this->table = $wpdb->prefix . 'awxct_languages';
    }

    /* ===================== Cache helpers ===================== */

    private function cache_group(): string
    {
        return 'awxct';
    }

    private function cache_key(string $suffix): string
    {
        $blog_id = function_exists('get_current_blog_id') ? (int) get_current_blog_id() : 0;
        return 'awxct_' . $blog_id . '_langs_' . $suffix;
    }

    /**
     * Validate SQL identifier (table/column) to avoid unsafe interpolation.
     */
    private function validate_ident(string $ident): string
    {
        $ident = (string) $ident;
        if (!preg_match('/^[A-Za-z0-9_]+$/', $ident)) {
            return '';
        }

        // Identifiers cannot be passed through $wpdb->prepare(). We validate strictly (alnum/_)
        // and then escape for safe interpolation.
        return esc_sql($ident);
    }

    /**
     * Get validated table identifier for this manager.
     */
    private function table_ident(): string
    {
        return $this->validate_ident((string) $this->table);
    }

    /**
     * Bump a simple version token so dynamic list caches get invalidated.
     */
    private function bump_version(): void
    {
        $k = $this->cache_key('ver');
        $g = $this->cache_group();
        $v = (int) wp_cache_get($k, $g);
        if ($v < 0) {
            $v = 0;
        }
        wp_cache_set($k, $v + 1, $g, HOUR_IN_SECONDS);
    }

    private function get_version(): int
    {
        $v = (int) wp_cache_get($this->cache_key('ver'), $this->cache_group());
        return ($v < 0) ? 0 : $v;
    }

    /* ===================== Public API ===================== */

    /**
     * Returns all languages (optionally only enabled ones)
     */
    public function get_all($only_enabled = false)
    {
        global $wpdb;

        $table_ident = $this->table_ident();
        if ($table_ident === '') {
            return [];
        }

        // Read column list (internal, DB-derived).
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Identifier is strictly validated (alnum/_) and escaped via validate_ident().
        $cols = (array) $wpdb->get_col("SHOW COLUMNS FROM `{$table_ident}`", 0);

        // Whitelist column choices
        $active_col = in_array('is_enabled', $cols, true) ? 'is_enabled'
            : (in_array('active', $cols, true) ? 'active' : '');

        $order_col = in_array('native_name', $cols, true) ? 'native_name'
            : (in_array('english_name', $cols, true) ? 'english_name'
                : (in_array('name', $cols, true) ? 'name' : 'code'));

        // Validate chosen identifiers
        $active_col_ident = $active_col ? $this->validate_ident($active_col) : '';
        $order_col_ident  = $this->validate_ident($order_col);
        if ($order_col_ident === '') {
            $order_col_ident = 'code';
        }

        // Build WHERE only from validated identifier
        $where_sql = '';
        if ($only_enabled && $active_col_ident !== '') {
            $where_sql = "WHERE COALESCE(`{$active_col_ident}`,0)=1";
        }

        $sql = "SELECT * FROM `{$table_ident}` {$where_sql} ORDER BY `{$order_col_ident}` ASC";

        // Identifiers are validated; prepare() can't be used for identifiers.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Identifiers are strictly validated (alnum/_) and escaped via validate_ident().
        $rows = $wpdb->get_results($sql);

        $languages = [];
        foreach ((array) $rows as $row) {
            $languages[] = AWXCT_Clone_Translate_Language::from_db_row($row);
        }
        return $languages;
    }

    /**
     * Returns only enabled languages
     */
    public function get_enabled()
    {
        return $this->get_all(true);
    }

    /**
     * Get a language by ID
     */
    public function get_by_id($id)
    {
        global $wpdb;

        $id = (int) $id;
        if ($id <= 0) {
            return AWXCT_Clone_Translate_Language::from_db_row(null);
        }

        $table_ident = $this->table_ident();
        if ($table_ident === '') {
            return AWXCT_Clone_Translate_Language::from_db_row(null);
        }

        $ver = $this->get_version();
        $ck  = $this->cache_key('byid_v' . $ver . '_' . $id);

        $row = wp_cache_get($ck, $this->cache_group());
        if (false === $row) {
            $prepared = $wpdb->prepare(
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                "SELECT * FROM `{$table_ident}` WHERE id=%d",
                $id
            );

            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Query is generated via $wpdb->prepare(); identifiers are validated/escaped.
            $row = $wpdb->get_row($prepared);

            // Cache null as well
            wp_cache_set($ck, $row ? $row : 0, $this->cache_group(), 300);
        } elseif (0 === $row) {
            $row = null;
        }

        return AWXCT_Clone_Translate_Language::from_db_row($row);
    }

    /**
     * Get a language by its code
     */
    public function get_by_code($code)
    {
        global $wpdb;

        $code = (string) $code;
        $code_norm = strtolower(trim($code));
        if ($code_norm === '') {
            return AWXCT_Clone_Translate_Language::from_db_row(null);
        }

        $table_ident = $this->table_ident();
        if ($table_ident === '') {
            return AWXCT_Clone_Translate_Language::from_db_row(null);
        }

        $ver = $this->get_version();
        $ck  = $this->cache_key('bycode_v' . $ver . '_' . md5($code_norm));

        $row = wp_cache_get($ck, $this->cache_group());
        if (false === $row) {
            $prepared = $wpdb->prepare(
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                "SELECT * FROM `{$table_ident}` WHERE LOWER(code)=LOWER(%s) LIMIT 1",
                $code
            );

            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Query is generated via $wpdb->prepare(); identifiers are validated/escaped.
            $row = $wpdb->get_row($prepared);

            wp_cache_set($ck, $row ? $row : 0, $this->cache_group(), 300);
        } elseif (0 === $row) {
            $row = null;
        }

        return AWXCT_Clone_Translate_Language::from_db_row($row);
    }

    /**
     * Delete a language by ID
     */
    public function delete($id)
    {
        global $wpdb;

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

        // WRITE query: caching does not apply, but we invalidate our caches right after.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $res = $wpdb->delete($table_ident, ['id' => (int) $id], ['%d']);

        // Invalidate caches for language lists/lookups.
        $this->bump_version();

        return $res;
    }
}
