<?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_Database {

    public static function install() {
        global $wpdb;
        $charset = $wpdb->get_charset_collate();

        $sql_pilares = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}il_pilares (
            id          BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            nombre      VARCHAR(255) NOT NULL,
            url         VARCHAR(500) NOT NULL,
            tematica    VARCHAR(255) DEFAULT '',
            idioma      VARCHAR(10) DEFAULT '',
            prioridad   TINYINT(3) UNSIGNED NOT NULL DEFAULT 50,
            post_id     BIGINT(20) UNSIGNED DEFAULT NULL,
            activo      TINYINT(1) NOT NULL DEFAULT 1,
            created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id)
        ) $charset;";

        $sql_reglas = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}il_reglas (
            id              BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            nombre          VARCHAR(255) NOT NULL,
            descripcion     TEXT DEFAULT '',
            origen_tipo     VARCHAR(100) NOT NULL DEFAULT 'any',
            origen_valor    VARCHAR(255) DEFAULT '',
            destino_tipo    VARCHAR(100) NOT NULL DEFAULT 'pilar',
            destino_valor   VARCHAR(255) DEFAULT '',
            pilar_id        BIGINT(20) UNSIGNED DEFAULT NULL,
            prioridad       TINYINT(3) UNSIGNED NOT NULL DEFAULT 50,
            max_enlaces     TINYINT(3) UNSIGNED NOT NULL DEFAULT 3,
            anchor_text     VARCHAR(255) DEFAULT '',
            activo          TINYINT(1) NOT NULL DEFAULT 1,
            created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id)
        ) $charset;";

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        dbDelta( $sql_pilares );
        dbDelta( $sql_reglas );

        // Image prompt templates — separate table, no relation to pilares/reglas
        $sql_img_templates = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}il_image_templates (
            id             BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            name           VARCHAR(160) NOT NULL,
            description    TEXT DEFAULT '',
            instructions   LONGTEXT DEFAULT '',
            image_filename VARCHAR(255) DEFAULT '',
            is_predefined  TINYINT(1) NOT NULL DEFAULT 0,
            sort_order     INT NOT NULL DEFAULT 0,
            created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id)
        ) $charset;";
        dbDelta( $sql_img_templates );

        update_option( 'flink_db_version', FLINK_VERSION );
    }

    /**
     * Upgrade existing tables for installs on older schemas.
     * Called on plugins_loaded via version check.
     */
    public static function maybe_upgrade() {
        global $wpdb;

        // ── Upgrade il_pilares — add idioma column ─────────────
        $ptable  = $wpdb->prefix . 'il_pilares';
        if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $ptable ) ) ) {
            $pcols = $wpdb->get_col( "DESCRIBE {$ptable}", 0 );
            if ( ! in_array( 'idioma', $pcols, true ) ) {
                $wpdb->query( "ALTER TABLE {$ptable} ADD COLUMN idioma VARCHAR(10) DEFAULT '' AFTER tematica" );
            }
        }

        // ── Upgrade il_reglas — add idioma + lang_variantes columns ─
        $reglas_table   = $wpdb->prefix . 'il_reglas';
        if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $reglas_table ) ) ) {
            $reglas_columns = $wpdb->get_col( "DESCRIBE {$reglas_table}", 0 );
            if ( ! in_array( 'idioma', $reglas_columns, true ) ) {
                $wpdb->query(
                    "ALTER TABLE {$reglas_table}
                     ADD COLUMN idioma VARCHAR(10) NOT NULL DEFAULT ''
                     AFTER max_enlaces"
                );
            }
            // lang_variantes stores JSON: {"en":{"keyword":"beach","sinonimos":"shore","pilar_id":5,"url":""},...}
            if ( ! in_array( 'lang_variantes', $reglas_columns, true ) ) {
                $wpdb->query(
                    "ALTER TABLE {$reglas_table}
                     ADD COLUMN lang_variantes LONGTEXT DEFAULT NULL
                     AFTER idioma"
                );
            }
        }

        // ── Upgrade il_image_templates — add is_predefined column ─────
        $img_tpl_table = $wpdb->prefix . 'il_image_templates';
        if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $img_tpl_table ) ) ) {
            $img_tpl_cols = $wpdb->get_col( "DESCRIBE {$img_tpl_table}", 0 );
            if ( ! in_array( 'is_predefined', $img_tpl_cols, true ) ) {
                $wpdb->query(
                    "ALTER TABLE {$img_tpl_table}
                     ADD COLUMN is_predefined TINYINT(1) NOT NULL DEFAULT 0
                     AFTER image_filename"
                );
            }
            // sort_order: manual display order for predefined templates.
            // User templates leave it at 0 and fall back to alphabetical.
            if ( ! in_array( 'sort_order', $img_tpl_cols, true ) ) {
                $wpdb->query(
                    "ALTER TABLE {$img_tpl_table}
                     ADD COLUMN sort_order INT NOT NULL DEFAULT 0
                     AFTER is_predefined"
                );
            }
        }
    }

    public static function deactivate() {}
}