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

class AIWEST_Database {
    public static function tables() {
        global $wpdb;
        return [
            'memory'          => $wpdb->prefix . 'aiwest_memory',
            'goals'           => $wpdb->prefix . 'aiwest_goals',
            'tasks'           => $wpdb->prefix . 'aiwest_tasks',
            'actions'         => $wpdb->prefix . 'aiwest_actions',
            'logs'            => $wpdb->prefix . 'aiwest_logs',
            'chat_history'    => $wpdb->prefix . 'aiwest_chat_history',
            'content_history' => $wpdb->prefix . 'aiwest_content_history',
        ];
    }

    public static function table_exists($table_name) {
        global $wpdb;
        // Schema discovery must reflect installation and upgrade changes immediately.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        return $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $wpdb->esc_like($table_name))) === $table_name;
    }


    public static function column_exists($table_name, $column_name) {
        global $wpdb;
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $column = $wpdb->get_var($wpdb->prepare('SHOW COLUMNS FROM %i LIKE %s', $table_name, $column_name));
        return $column === $column_name;
    }

    public static function status() {
        $tables = self::tables();
        $out = [];
        foreach ($tables as $key => $name) {
            $out[$key] = [
                'name' => $name,
                'exists' => self::table_exists($name),
            ];
        }
        return $out;
    }

    public static function create_tables() {
        global $wpdb;
        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        $charset = $wpdb->get_charset_collate();
        $t = self::tables();

        dbDelta("CREATE TABLE {$t['memory']} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            memory_key VARCHAR(191) NOT NULL,
            memory_value LONGTEXT NULL,
            memory_type VARCHAR(60) DEFAULT 'general',
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY memory_key (memory_key)
        ) $charset;");

        dbDelta("CREATE TABLE {$t['goals']} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            goal_title TEXT NOT NULL,
            goal_description LONGTEXT NULL,
            priority VARCHAR(30) DEFAULT 'normal',
            status VARCHAR(30) DEFAULT 'active',
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id)
        ) $charset;");

        dbDelta("CREATE TABLE {$t['tasks']} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            task_type VARCHAR(100) NOT NULL,
            task_status VARCHAR(40) DEFAULT 'pending',
            task_payload LONGTEXT NULL,
            task_result LONGTEXT NULL,
            risk_level VARCHAR(40) DEFAULT 'safe',
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            completed_at DATETIME NULL,
            PRIMARY KEY (id)
        ) $charset;");

        dbDelta("CREATE TABLE {$t['actions']} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            action_type VARCHAR(100) NOT NULL,
            action_title TEXT NOT NULL,
            action_payload LONGTEXT NULL,
            risk_level VARCHAR(40) DEFAULT 'review',
            status VARCHAR(40) DEFAULT 'pending',
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            approved_at DATETIME NULL,
            PRIMARY KEY (id)
        ) $charset;");

        dbDelta("CREATE TABLE {$t['logs']} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            log_level VARCHAR(30) DEFAULT 'info',
            log_message TEXT NOT NULL,
            context LONGTEXT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id)
        ) $charset;");

        dbDelta("CREATE TABLE {$t['chat_history']} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            user_message LONGTEXT NOT NULL,
            ai_response LONGTEXT NULL,
            model VARCHAR(100) NULL,
            target_post_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
            command_action VARCHAR(24) NOT NULL DEFAULT 'auto',
            mode VARCHAR(40) DEFAULT 'question',
            status VARCHAR(40) DEFAULT 'pending',
            approval_status VARCHAR(40) DEFAULT 'not_required',
            approval_note TEXT NULL,
            applied_at DATETIME NULL,
            error_message TEXT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY status (status),
            KEY created_at (created_at)
        ) $charset;");

        dbDelta("CREATE TABLE {$t['content_history']} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            source VARCHAR(60) DEFAULT 'manual',
            prompt TEXT NULL,
            topic TEXT NOT NULL,
            keyword VARCHAR(191) NULL,
            post_id BIGINT UNSIGNED NULL,
            post_status VARCHAR(40) NULL,
            model VARCHAR(100) NULL,
            status VARCHAR(40) DEFAULT 'pending',
            message TEXT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY source (source),
            KEY status (status),
            KEY post_id (post_id),
            KEY created_at (created_at)
        ) $charset;");
    }
}