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

class LiveLang_DB {

    public $table;

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

    public function create_table() {
        global $wpdb;

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';

        $charset_collate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE {$this->table} (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            original_text LONGTEXT NOT NULL,
            translated_text LONGTEXT NOT NULL,
            slug VARCHAR(190) NOT NULL,
            language VARCHAR(10) NOT NULL DEFAULT '',
            is_global TINYINT(1) NOT NULL DEFAULT 0,
            status VARCHAR(20) NOT NULL DEFAULT 'active',
            alignment VARCHAR(20) NOT NULL DEFAULT '',
            created_at DATETIME NOT NULL,
            updated_at DATETIME NOT NULL,
            PRIMARY KEY (id),
            KEY slug (slug),
            KEY language (language),
            KEY is_global (is_global),
            KEY status (status)
        ) $charset_collate;";

        dbDelta( $sql );
    }

    public function get_translations_for_slug( $slug, $language = '' ) {
        global $wpdb;
        // ORDER BY is_global ASC: global (site-wide) rows come first, so that
        // when class-livelang-frontend.php builds its original => translated
        // map by looping and overwriting, a page-specific translation always
        // wins over a global one for the same original text. Without this,
        // precedence was effectively undefined (whatever order MySQL
        // happened to return rows in).
        if ( $language ) {
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $sql = $wpdb->prepare( "SELECT * FROM {$this->table} WHERE (slug = %s OR is_global = 1) AND language = %s AND status = 'active' ORDER BY is_global DESC, id ASC", $slug, $language );
        } else {
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $sql = $wpdb->prepare( "SELECT * FROM {$this->table} WHERE (slug = %s OR is_global = 1) AND status = 'active' ORDER BY is_global DESC, id ASC", $slug );
        }
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        return $wpdb->get_results( $sql );
    }

    public function get_translation_by_original_and_slug( $original_text, $slug, $language = '' ) {
        global $wpdb;
        if ( $language ) {
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $sql = $wpdb->prepare( "SELECT * FROM {$this->table} WHERE original_text = %s AND (slug = %s OR is_global = 1) AND language = %s AND status = 'active' LIMIT 1", $original_text, $slug, $language );
        } else {
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $sql = $wpdb->prepare( "SELECT * FROM {$this->table} WHERE original_text = %s AND (slug = %s OR is_global = 1) AND status = 'active' LIMIT 1", $original_text, $slug );
        }
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        return $wpdb->get_row( $sql );
    }

    public function get_all( $limit = 20, $offset = 0, $args = array() ) {
        global $wpdb;
        $limit  = intval( $limit );
        $offset = intval( $offset );

        list( $where_sql, $where_values ) = $this->build_filter_where( $args );

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $sql = "SELECT * FROM {$this->table} {$where_sql} ORDER BY id DESC LIMIT %d, %d";
        $values = array_merge( $where_values, array( $offset, $limit ) );

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

    public function get_total_count( $args = array() ) {
        global $wpdb;

        list( $where_sql, $where_values ) = $this->build_filter_where( $args );

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $sql = "SELECT COUNT(id) FROM {$this->table} {$where_sql}";

        if ( empty( $where_values ) ) {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
            return intval( $wpdb->get_var( $sql ) );
        }

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

    /**
     * Build a reusable WHERE clause for the translations list table so the
     * same filtering logic powers both the row query and the count query.
     *
     * Supported $args keys: 'search' (matches original/translated text),
     * 'language' (exact language code), 'status' (active/inactive).
     *
     * @return array{0: string, 1: array} [ $where_sql, $prepared_values ]
     */
    protected function build_filter_where( $args = array() ) {
        global $wpdb;

        $clauses = array();
        $values  = array();

        if ( ! empty( $args['search'] ) ) {
            $like = '%' . $wpdb->esc_like( $args['search'] ) . '%';
            $clauses[] = '(original_text LIKE %s OR translated_text LIKE %s)';
            $values[]  = $like;
            $values[]  = $like;
        }

        if ( ! empty( $args['language'] ) ) {
            $clauses[] = 'language = %s';
            $values[]  = $args['language'];
        }

        if ( ! empty( $args['status'] ) ) {
            $clauses[] = 'status = %s';
            $values[]  = $args['status'];
        }

        if ( isset( $args['is_global'] ) && $args['is_global'] !== '' ) {
            $clauses[] = 'is_global = %d';
            $values[]  = (int) $args['is_global'];
        }

        if ( empty( $clauses ) ) {
            return array( '', array() );
        }

        return array( 'WHERE ' . implode( ' AND ', $clauses ), $values );
    }

    /**
     * Bulk update status for a set of translation IDs.
     */
    public function bulk_update_status( $ids, $status ) {
        global $wpdb;
        $ids = array_filter( array_map( 'intval', (array) $ids ) );
        if ( empty( $ids ) || ! in_array( $status, array( 'active', 'inactive' ), true ) ) {
            return 0;
        }
        $placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );
        $params       = array_merge( array( $status, current_time( 'mysql' ) ), $ids );
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $sql = $wpdb->prepare( "UPDATE {$this->table} SET status = %s, updated_at = %s WHERE id IN ({$placeholders})", ...$params );
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $result = $wpdb->query( $sql );
        $this->clear_translation_cache();
        return $result;
    }

    /**
     * Bulk delete a set of translation IDs.
     */
    public function bulk_delete( $ids ) {
        global $wpdb;
        $ids = array_filter( array_map( 'intval', (array) $ids ) );
        if ( empty( $ids ) ) {
            return 0;
        }
        $placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $sql = $wpdb->prepare( "DELETE FROM {$this->table} WHERE id IN ({$placeholders})", ...$ids );
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $result = $wpdb->query( $sql );
        $this->clear_translation_cache();
        return $result;
    }

    /**
     * Distinct list of language codes currently present in the table
     * (used to populate the filter dropdown on the translations screen).
     */
    public function get_distinct_languages() {
        global $wpdb;
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        return $wpdb->get_col( "SELECT DISTINCT language FROM {$this->table} WHERE language != '' ORDER BY language ASC" );
    }

    public function insert( $data ) {
        global $wpdb;

        $defaults = array(
            'original_text'   => '',
            'translated_text' => '',
            'slug'            => '',
            'language'        => '',
            'is_global'       => 0,
            'status'          => 'active',
            'alignment'       => '',
        );
        $data = wp_parse_args( $data, $defaults );
        $now  = current_time( 'mysql' );

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $wpdb->insert(
            $this->table,
            array(
                'original_text'   => $data['original_text'],
                'translated_text' => $data['translated_text'],
                'slug'            => $data['slug'],
                'language'        => $data['language'],
                'is_global'       => (int) $data['is_global'],
                'status'          => $data['status'],
                'alignment'       => $data['alignment'],
                'created_at'      => $now,
                'updated_at'      => $now,
            ),
            array( '%s', '%s', '%s', '%s', '%d', '%s', '%s', '%s', '%s' )
        );

        $this->clear_translation_cache();
        return $wpdb->insert_id;
    }

    public function update( $id, $data ) {
        global $wpdb;

        $id   = intval( $id );
        $now  = current_time( 'mysql' );

        // Build format array explicitly for every field that could be present
        // so wpdb never falls back to calling sanitize_text_field() on HTML content.
        $allowed_fields = array(
            'original_text'   => '%s',
            'translated_text' => '%s',
            'slug'            => '%s',
            'language'        => '%s',
            'is_global'       => '%d',
            'status'          => '%s',
            'alignment'       => '%s',
            'updated_at'      => '%s',
        );

        $update_data    = array();
        $update_formats = array();

        foreach ( $data as $key => $value ) {
            if ( isset( $allowed_fields[ $key ] ) ) {
                $update_data[ $key ]    = $value;
                $update_formats[]       = $allowed_fields[ $key ];
            }
        }

        $update_data['updated_at'] = $now;
        $update_formats[]          = '%s';

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $result = $wpdb->update(
            $this->table,
            $update_data,
            array( 'id' => $id ),
            $update_formats,
            array( '%d' )
        );
        $this->clear_translation_cache();
        return $result;
    }

    public function delete( $id ) {
        global $wpdb;
        $id = intval( $id );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $result = $wpdb->delete( $this->table, array( 'id' => $id ), array( '%d' ) );
        $this->clear_translation_cache();
        return $result;
    }

    public function clear_all() {
        global $wpdb;
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery
        $wpdb->query( "TRUNCATE TABLE {$this->table}" );
        $this->clear_translation_cache();
    }

    /**
     * Delete translations matching a scope (language / status / global vs
     * page-specific) instead of always wiping the entire table. Powers the
     * Danger Zone's scoped-clear options. Returns the number of rows deleted.
     */
    public function clear_filtered( $args = array() ) {
        global $wpdb;

        list( $where_sql, $where_values ) = $this->build_filter_where( $args );

        if ( empty( $where_sql ) ) {
            // No scope given at all - refuse to silently wipe everything here;
            // callers that truly want everything gone should use clear_all()
            // explicitly so that intent is never ambiguous in the code path.
            return 0;
        }

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $sql = "DELETE FROM {$this->table} {$where_sql}";
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $result = $wpdb->query( $wpdb->prepare( $sql, $where_values ) );
        $this->clear_translation_cache();

        return $result;
    }

    public function clear_translation_cache( $slug = null, $language = null ) {
        $version = (int) get_option( 'livelang_cache_version', 0 );
        update_option( 'livelang_cache_version', $version + 1 );
        if ( function_exists( 'wp_cache_flush_group' ) ) {
            call_user_func( 'wp_cache_flush_group', 'livelang' );
        }
    }

}