<?php

/**
 * LolaCore � Schema
 *
 * @package LolaCore
 * @license GPL-2.0-or-later
 */


declare(strict_types=1);

namespace LolaCore\Database;

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.SlowDBQuery.slow_db_query_meta_key -- Required for core functionality, dynamic queries, and performance. Validated inputs.

/**
 * Handles database schema creation and updates via dbDelta.
 *
 * DDL definitions follow the canonical spec in Tech Stack v1.0 section 2.3.
 * dbDelta() rules: each field on its own line, at least 2 spaces between
 * field name and definition, KEY instead of INDEX, PRIMARY KEY with two
 * spaces before opening parenthesis.
 */
class Schema
{
    /**
     * Array of table names for LolaCore (in drop order for referential integrity).
     */
    public const TABLES = [
        'lolacore_messages',
        'lolacore_sessions',
        'lolacore_log',
        'lolacore_memory',
        'lolacore_config',
        'lolacore_skills',
    ];

    /**
     * Generate the DDL strings and run dbDelta.
     */
    public static function create(): void
    {
        global $wpdb;

        if (!function_exists('dbDelta')) {
            require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        }

        $charset = $wpdb->get_charset_collate();
        $prefix  = $wpdb->prefix;

        $tables = self::getTableDefinitions($prefix, $charset);

        foreach ($tables as $sql) {
            dbDelta($sql);
        }

        // Run incremental migrations that dbDelta cannot handle
        self::migrate($prefix);
    }

    /**
     * Run incremental migrations for schema changes that dbDelta does not
     * handle well (e.g., adding columns to existing tables).
     *
     * Each migration is idempotent — it checks before altering.
     *
     * @param string $prefix WordPress table prefix.
     */
    private static function migrate(string $prefix): void
    {
        global $wpdb;
        $memoryTable = $prefix . 'lolacore_memory';

        // v1.2.0: Add is_pinned column for permanent facts
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Schema migration check.
        $columnExists = $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME = %s",
                $memoryTable,
                'is_pinned'
            )
        );

        if ((int) $columnExists === 0) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Schema migration.
            $wpdb->query("ALTER TABLE {$memoryTable} ADD COLUMN is_pinned tinyint(1) NOT NULL DEFAULT 0 AFTER is_archived");
        }

        // v1.4.0: wp_lolacore_skills created via dbDelta in getTableDefinitions().
        // Extension point: future column additions (e.g., last_load_duration_ms,
        // activation_count) go in this method via INFORMATION_SCHEMA guards.

        // SMD-07: collapse the 'draft' status into 'inactive'.
        // The two states were functionally identical (neither fires, both are
        // visible in the admin) — collapsing them removes the redundant
        // "approve" step. dbDelta cannot mutate row data, so we run it here.
        $skillsTable = $prefix . 'lolacore_skills';
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Schema migration.
        $wpdb->query("UPDATE {$skillsTable} SET status = 'inactive' WHERE status = 'draft'");
    }

    /**
     * Drop all LolaCore tables.
     */
    public static function drop(): void
    {
        global $wpdb;
        foreach (self::TABLES as $table) {
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching
            $wpdb->query("DROP TABLE IF EXISTS {$wpdb->prefix}{$table}");
        }
    }

    /**
     * Returns the DDL definitions for all LolaCore tables.
     *
     * @param string $prefix  WordPress table prefix.
     * @param string $charset Charset collate string.
     * @return array<string> Array of CREATE TABLE statements.
     */
    private static function getTableDefinitions(string $prefix, string $charset): array
    {
        return [
            // Memory — Persistent facts with decay scoring
            "CREATE TABLE {$prefix}lolacore_memory (
                id  bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                fact_type  varchar(50) NOT NULL,
                content  text NOT NULL,
                structured_data  json DEFAULT NULL,
                importance  tinyint unsigned NOT NULL DEFAULT 5,
                decay_score  decimal(4,3) NOT NULL DEFAULT 1.000,
                access_count  int unsigned NOT NULL DEFAULT 0,
                last_accessed  datetime DEFAULT NULL,
                source  varchar(20) NOT NULL DEFAULT 'conversation',
                session_id  char(36) DEFAULT NULL,
                is_archived  tinyint(1) NOT NULL DEFAULT 0,
                is_pinned  tinyint(1) 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),
                KEY idx_fact_type (fact_type),
                KEY idx_decay_active (is_archived, decay_score),
                KEY idx_importance (importance),
                KEY idx_source (source),
                KEY idx_session (session_id),
                KEY idx_last_accessed (last_accessed)
            ) {$charset};",

            // Log — Action log with snapshots for reversibility
            "CREATE TABLE {$prefix}lolacore_log (
                id  bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                action_type  varchar(100) NOT NULL,
                description  text NOT NULL,
                status  varchar(20) NOT NULL DEFAULT 'success',
                source  varchar(10) NOT NULL DEFAULT 'chat',
                details  json DEFAULT NULL,
                user_override  tinyint(1) NOT NULL DEFAULT 0,
                lola_objection  text DEFAULT NULL,
                session_id  char(36) DEFAULT NULL,
                executed_at  datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY  (id),
                KEY idx_action_type (action_type),
                KEY idx_status (status),
                KEY idx_source (source),
                KEY idx_session (session_id),
                KEY idx_executed (executed_at),
                KEY idx_override (user_override, executed_at)
            ) {$charset};",

            // Config — Key-value configuration store
            "CREATE TABLE {$prefix}lolacore_config (
                id  bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                config_key  varchar(100) NOT NULL,
                config_value  text DEFAULT NULL,
                updated_at  datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY  (id),
                UNIQUE KEY uk_config_key (config_key)
            ) {$charset};",

            // Sessions — Chat session metadata
            "CREATE TABLE {$prefix}lolacore_sessions (
                id  char(36) NOT NULL,
                title  varchar(255) DEFAULT NULL,
                status  varchar(20) NOT NULL DEFAULT 'active',
                llm_provider  varchar(20) DEFAULT NULL,
                llm_model  varchar(100) DEFAULT NULL,
                token_usage  json DEFAULT NULL,
                started_at  datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
                last_message_at  datetime DEFAULT NULL,
                closed_at  datetime DEFAULT NULL,
                PRIMARY KEY  (id),
                KEY idx_status (status),
                KEY idx_last_message (last_message_at)
            ) {$charset};",

            // Messages — Individual chat messages
            "CREATE TABLE {$prefix}lolacore_messages (
                id  bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                session_id  char(36) NOT NULL,
                role  varchar(20) NOT NULL,
                content  mediumtext NOT NULL,
                token_count  int unsigned DEFAULT NULL,
                metadata  json DEFAULT NULL,
                created_at  datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY  (id),
                KEY idx_session_order (session_id, created_at),
                KEY idx_role (role)
            ) {$charset};",

            // Skills — User-authored and DB-stored skill definitions (Skills.MD v1.4.0)
            // status defaults to 'inactive' as of SMD-07: skills created by Lola
            // are saved off so the user has a chance to review them before they
            // affect responses. The user explicitly activates them via the toggle
            // (admin) or via activateSkill (chat) once approved.
            "CREATE TABLE {$prefix}lolacore_skills (
                id  bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                skill_name  varchar(191) NOT NULL,
                content  longtext NOT NULL,
                status  varchar(20) NOT NULL DEFAULT 'inactive',
                origin  varchar(20) NOT NULL,
                content_hash  char(64) NOT NULL,
                created_at  datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at  datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                last_used_at  datetime DEFAULT NULL,
                load_count  int(10) unsigned NOT NULL DEFAULT 0,
                PRIMARY KEY  (id),
                UNIQUE KEY uk_skill_name (skill_name),
                KEY idx_status_origin (status, origin)
            ) {$charset};",
        ];
    }
}
