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

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery
// phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching
// phpcs:disable WordPress.DB.DirectDatabaseQuery.SchemaChange
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
// phpcs:disable WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter
// -- All $wpdb operations in this file target custom plugin tables
// (e.g. $wpdb->prefix . 'il_pilares') whose names and column lists
// are constants under plugin control. Generic WordPress checks
// for direct DB queries, caching and prepared-SQL interpolation
// don't apply meaningfully here. See docs/LICENSING.md for the
// full architecture rationale.

class FLINK_Pilares {

    private $table;

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

    public function get_all( $args = array() ) {
        global $wpdb;
        $defaults = array(
            'orderby' => 'prioridad',
            'order'   => 'DESC',
            'activo'  => null,
            'idioma'  => '',    // '' = all languages
            'search'  => '',
        );
        $args = wp_parse_args( $args, $defaults );

        $conditions = array();
        if ( ! is_null( $args['activo'] ) ) {
            $conditions[] = $wpdb->prepare( 'activo = %d', (int) $args['activo'] );
        }
        if ( ! empty( $args['idioma'] ) ) {
            $conditions[] = $wpdb->prepare( 'idioma = %s', $args['idioma'] );
        }
        if ( ! empty( $args['search'] ) ) {
            $like = '%' . $wpdb->esc_like( $args['search'] ) . '%';
            $conditions[] = $wpdb->prepare( '(nombre LIKE %s OR url LIKE %s)', $like, $like );
        }

        // Each condition above is individually run through $wpdb->prepare()
        // with its values bound as placeholders, so joining the already-safe
        // fragments here introduces no unescaped user input.
        $where   = $conditions ? 'WHERE ' . implode( ' AND ', $conditions ) : '';
        $orderby = sanitize_sql_orderby( $args['orderby'] . ' ' . $args['order'] );
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $where is built from individually prepared fragments; $orderby is sanitized via sanitize_sql_orderby(); $this->table is a constant plugin table name.
        return $wpdb->get_results( "SELECT * FROM {$this->table} {$where} ORDER BY {$orderby}" );
    }

    public function get( $id ) {
        global $wpdb;
        return $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$this->table} WHERE id = %d", $id ) );
    }

    public function insert( $data ) {
        global $wpdb;
        $data = $this->sanitize( $data );
        $result = $wpdb->insert( $this->table, $data );
        return $result ? $wpdb->insert_id : false;
    }

    public function update( $id, $data ) {
        global $wpdb;
        $data = $this->sanitize( $data );
        return $wpdb->update( $this->table, $data, array( 'id' => (int) $id ) );
    }

    public function delete( $id ) {
        global $wpdb;
        return $wpdb->delete( $this->table, array( 'id' => (int) $id ) );
    }

    public function count() {
        global $wpdb;
        return (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$this->table} WHERE activo = 1" );
    }

    private function sanitize( $data ) {
        $clean = array();
        if ( isset( $data['nombre'] ) )    $clean['nombre']    = sanitize_text_field( $data['nombre'] );
        if ( isset( $data['url'] ) )       $clean['url']       = esc_url_raw( $data['url'] );
        if ( isset( $data['tematica'] ) )  $clean['tematica']  = sanitize_text_field( $data['tematica'] );
        if ( isset( $data['idioma'] ) )    $clean['idioma']    = sanitize_key( $data['idioma'] );
        if ( isset( $data['post_id'] ) )   $clean['post_id']   = absint( $data['post_id'] ) ?: null;
        if ( isset( $data['activo'] ) )    $clean['activo']    = (int) (bool) $data['activo'];
        if ( isset( $data['prioridad'] ) ) {
            $clean['prioridad'] = max( 1, min( 100, (int) $data['prioridad'] ) );
        }
        return $clean;
    }

    /**
     * Get all distinct idioma values used in existing pilares.
     */
    public function get_used_languages() {
        global $wpdb;
        $rows = $wpdb->get_col( "SELECT DISTINCT idioma FROM {$this->table} WHERE idioma != '' ORDER BY idioma ASC" );
        return $rows ?: array();
    }
}