<?php
/**
 * Database layer for Salyut Agents messages
 *
 * Provides a custom table for storing chat messages instead of post_meta,
 * giving proper scalability and query performance.
 *
 * Table: {prefix}salyut_agents_messages
 *
 * @package SalyutAgents
 */

if ( ! defined( 'ABSPATH' ) ) {
    exit;
}

define( 'SALYUT_AGENTS_DB_VERSION', '1.0.0' );
define( 'SALYUT_AGENTS_DB_VERSION_OPTION', 'salyut_agents_db_version' );

/**
 * Return the full table name for salyut-agents messages.
 *
 * Always use this function rather than hardcoding the table name so that
 * non-standard table prefixes are respected automatically.
 *
 * @return string
 */
function salyut_agents_messages_table(): string {
    global $wpdb;
    return $wpdb->prefix . 'salyut_agents_messages';
}

/**
 * Create (or upgrade) the messages table.
 *
 * Safe to call on every activation — dbDelta only applies changes.
 *
 * A NEW install gets exactly this 5-column schema — no content_type/
 * card_data; a free-plugin-only site matches main byte-for-byte. An
 * install that upgraded through an earlier version of this branch and
 * already has those two extra columns keeps them: dbDelta only adds
 * columns present in $sql, it never drops a column this SQL doesn't
 * mention, so re-running this (e.g. via salyut_agents_maybe_upgrade_db()
 * below) is always safe and never touches, let alone destroys, whatever
 * is already stored in them. See salyut_agents_messages_table_has_card_
 * columns() for the read side's own handling of either shape.
 *
 * @return void
 */
function salyut_agents_create_messages_table(): void {
    global $wpdb;

    $table      = salyut_agents_messages_table();
    $charset_collate = $wpdb->get_charset_collate();

    $sql = "CREATE TABLE {$table} (
        id         bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
        session_id bigint(20) UNSIGNED NOT NULL,
        role       varchar(20)         NOT NULL,
        content    longtext            NOT NULL,
        ts         int(11) UNSIGNED    NOT NULL,
        PRIMARY KEY (id),
        KEY session_id (session_id)
    ) ENGINE=InnoDB {$charset_collate};";

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

    update_option( SALYUT_AGENTS_DB_VERSION_OPTION, SALYUT_AGENTS_DB_VERSION );
}

/**
 * Whether this install's messages table still carries the content_type/
 * card_data columns from an earlier version of this branch (added, then
 * removed from the CREATE TABLE statement above). dbDelta never drops a
 * column, so an install that once gained them keeps them indefinitely —
 * this is the read side's own check for which shape it's talking to, so
 * salyut_agents_get_session_messages() can keep surfacing real, already-
 * stored card data on such an install without ever assuming a fresh
 * install has (or needs) the same two columns.
 *
 * Cached for the life of the request — the schema does not change
 * mid-request in real use, and this would otherwise run an
 * information_schema lookup on every single message read.
 *
 * @param bool $refresh  Bypass and replace the cached value. Only ever
 *                        needed by a test that changes the live schema
 *                        within one process (real requests never do).
 *
 * @return bool
 */
function salyut_agents_messages_table_has_card_columns( bool $refresh = false ): bool {
    static $has_columns = null;

    if ( ! $refresh && null !== $has_columns ) {
        return $has_columns;
    }

    global $wpdb;

    $table = salyut_agents_messages_table();

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- one-time-per-request schema check against this plugin's own table; $table is $wpdb->prefix . a literal, never user input.
    $column = $wpdb->get_var(
        $wpdb->prepare(
            'SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME = %s',
            $table,
            'content_type'
        )
    );

    $has_columns = ( null !== $column );

    return $has_columns;
}

/**
 * Drop the messages table.
 *
 * Called on plugin uninstall only — never on deactivation.
 *
 * @return void
 */
function salyut_agents_drop_messages_table(): void {
    global $wpdb;
    $table = salyut_agents_messages_table();
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- one-time DROP of this plugin's own custom table on uninstall; $table is $wpdb->prefix . a literal, not user input.
    $wpdb->query( "DROP TABLE IF EXISTS {$table}" );
    delete_option( SALYUT_AGENTS_DB_VERSION_OPTION );
}

/**
 * Insert a single message into the messages table.
 *
 * @param int    $session_id  The salyut_lead post ID.
 * @param string $role        'user' or 'assistant'.
 * @param string $content     The message text.
 * @param int    $ts          Unix timestamp. Defaults to now.
 *
 * @return int|false  Inserted row ID, or false on failure.
 */
function salyut_agents_insert_message( int $session_id, string $role, string $content, int $ts = 0 ) {
    global $wpdb;

    if ( $ts === 0 ) {
        $ts = time();
    }

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- write to this plugin's own custom messages table; $wpdb->insert() escapes values via the format array.
    $result = $wpdb->insert(
        salyut_agents_messages_table(),
        [
            'session_id' => $session_id,
            'role'       => $role,
            'content'    => $content,
            'ts'         => $ts,
        ],
        [ '%d', '%s', '%s', '%d' ]
    );

    if ( $result === false ) {
        if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) {
            // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- WP_DEBUG-guarded diagnostic only.
            error_log( "Salyut Agents DB: Failed to insert message for session {$session_id} — " . $wpdb->last_error );
        }
        return false;
    }

    return (int) $wpdb->insert_id;
}

/**
 * Retrieve all messages for a session, ordered chronologically.
 *
 * Returns an array of associative arrays in the format expected by the
 * OpenAI messages parameter: [ ['role' => '...', 'content' => '...', 'ts' => 0], ... ]
 * — byte-identical to main on any install whose table never gained the old
 * content_type/card_data columns (a fresh install, or any free-plugin-only
 * site). An install that still has them (salyut_agents_messages_table_has_
 * card_columns()) gets both extra keys back too, so whatever real card data
 * an earlier version of this branch already stored there stays genuinely
 * readable — e.g. by an addon's own privacy-export listener — rather
 * than merely left in the table, unreachable, once this function stopped
 * asking for it. Every caller here reads only 'role'/'content'/'ts' by key
 * and ignores unknown keys, so the extra pair is additive either way.
 *
 * @param int $session_id  The salyut_lead post ID.
 *
 * @return array
 */
function salyut_agents_get_session_messages( int $session_id ): array {
    global $wpdb;

    $table       = salyut_agents_messages_table();
    $has_columns = salyut_agents_messages_table_has_card_columns();
    $columns     = $has_columns ? 'role, content, ts, content_type, card_data' : 'role, content, ts';

    // Custom messages table ($wpdb->prefix . a literal, never user input); $columns is
    // one of the two fixed, hardcoded strings above, never user input; the session_id
    // value is bound via $wpdb->prepare(); per-session transcripts are not cache-appropriate.
    // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
    $rows = $wpdb->get_results(
        $wpdb->prepare(
            "SELECT {$columns} FROM {$table} WHERE session_id = %d ORDER BY id ASC",
            $session_id
        ),
        ARRAY_A
    );
    // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

    if ( $rows === null ) {
        if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) {
            // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- WP_DEBUG-guarded diagnostic only.
            error_log( "Salyut Agents DB: Failed to retrieve messages for session {$session_id} — " . $wpdb->last_error );
        }
        return [];
    }

    if ( ! $has_columns ) {
        return array_map( function( $row ) {
            return [
                'role'    => $row['role'],
                'content' => $row['content'],
                'ts'      => (int) $row['ts'],
            ];
        }, $rows );
    }

    return array_map( function( $row ) {
        return [
            'role'         => $row['role'],
            'content'      => $row['content'],
            'ts'           => (int) $row['ts'],
            'content_type' => $row['content_type'] ?? 'text',
            'card_data'    => $row['card_data'] ?? null,
        ];
    }, $rows );
}

/**
 * Count the number of messages in a session.
 *
 * Used by rate limiting and admin column display.
 *
 * @param int $session_id  The salyut_lead post ID.
 *
 * @return int
 */
function salyut_agents_count_session_messages( int $session_id ): int {
    global $wpdb;

    $table = salyut_agents_messages_table();

    // Custom messages table ($wpdb->prefix . a literal, never user input); the value
    // is bound via $wpdb->prepare(); a live count must not be cached.
    // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
    $count = $wpdb->get_var(
        $wpdb->prepare(
            "SELECT COUNT(*) FROM {$table} WHERE session_id = %d",
            $session_id
        )
    );
    // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

    return (int) $count;
}

/**
 * Delete all messages belonging to a session.
 *
 * Used by cleanup routines and uninstall.
 *
 * @param int $session_id  The salyut_lead post ID.
 *
 * @return int|false  Number of rows deleted, or false on failure.
 */
function salyut_agents_delete_session_messages( int $session_id ) {
    global $wpdb;

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- delete from this plugin's own custom messages table; $wpdb->delete() escapes the where value via the format array.
    return $wpdb->delete(
        salyut_agents_messages_table(),
        [ 'session_id' => $session_id ],
        [ '%d' ]
    );
}

/**
 * Run table upgrades if the DB version is behind.
 *
 * Hooked to plugins_loaded so upgrades apply automatically after
 * the plugin is updated without requiring manual reactivation.
 *
 * @return void
 */
function salyut_agents_maybe_upgrade_db(): void {
    if ( get_option( SALYUT_AGENTS_DB_VERSION_OPTION ) !== SALYUT_AGENTS_DB_VERSION ) {
        salyut_agents_create_messages_table();
    }
}
add_action( 'plugins_loaded', 'salyut_agents_maybe_upgrade_db' );