<?php

/**
 * Migration 0029 — chat columns the MariaDB-only self-heals never added
 *
 * ChatService::createTables() upgraded older installs with
 * `ALTER TABLE ... ADD COLUMN IF NOT EXISTS`. That syntax is MariaDB-only;
 * on MySQL 8 every statement was a silent parse error, so installs created
 * before those columns entered the CREATE never gained them. The widget
 * poll and the inbox read both SELECT `is_note` unconditionally, so every
 * fetch failed: visitors never saw replies (Instagent answers were
 * generated and stored, but undeliverable), and the inbox could not list
 * conversations. Found 2026-06-12 with the error log flooding
 * "Unknown column 'is_note'" on every 3-second poll.
 *
 * Adds (each guarded via INFORMATION_SCHEMA):
 *   - chat_messages.admin_user_id      BIGINT UNSIGNED NULL
 *   - chat_messages.is_note            TINYINT(1) DEFAULT 0
 *   - chat_conversations.visitor_typing_at DATETIME NULL
 *   - chat_agents.display_name/avatar/email (older agents tables)
 *
 * ChatService's self-heals were rewritten the same day to guard via
 * INFORMATION_SCHEMA (ensureColumn), so future columns work on MySQL 8.
 *
 * Backfill: none. New columns default to inert values.
 *
 * @since 2.1.1497
 */

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

return [
    'description' => 'Add chat columns lost to MariaDB-only IF NOT EXISTS ALTERs (is_note broke poll + inbox on MySQL 8)',

    'up' => [],

    'php' => function () {
        $p    = \Instinctor\Core\DB::prefix();
        $cols = [
            [$p . 'instinctor_chat_messages',      'admin_user_id',     'BIGINT UNSIGNED DEFAULT NULL'],
            [$p . 'instinctor_chat_messages',      'is_note',           'TINYINT(1) DEFAULT 0'],
            [$p . 'instinctor_chat_conversations', 'visitor_typing_at', 'DATETIME DEFAULT NULL'],
            [$p . 'instinctor_chat_agents',        'display_name',      "VARCHAR(100) DEFAULT ''"],
            [$p . 'instinctor_chat_agents',        'avatar',            "VARCHAR(500) DEFAULT ''"],
            [$p . 'instinctor_chat_agents',        'email',             "VARCHAR(200) DEFAULT ''"],
        ];

        foreach ($cols as [$table, $column, $ddl]) {
            // Skip tables that don't exist on this install (chat never booted).
            $tableExists = \Instinctor\Core\DB::get_var(
                \Instinctor\Core\DB::prepare(
                    "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
                     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s",
                    $table
                )
            );
            if (!$tableExists) {
                continue;
            }

            $colExists = \Instinctor\Core\DB::get_var(
                \Instinctor\Core\DB::prepare(
                    "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
                     WHERE TABLE_SCHEMA = DATABASE()
                       AND TABLE_NAME   = %s
                       AND COLUMN_NAME  = %s",
                    $table, $column
                )
            );
            if ($colExists) {
                continue;
            }

            \Instinctor\Core\DB::query("ALTER TABLE {$table} ADD COLUMN {$column} {$ddl}");
            if (\Instinctor\Core\DB::last_error() !== '') {
                throw new \RuntimeException(
                    esc_html("{$table}.{$column} add failed: " . \Instinctor\Core\DB::last_error())
                );
            }
        }
    },

    'down' => [
        "ALTER TABLE {prefix}instinctor_chat_messages DROP COLUMN IF EXISTS admin_user_id",
        "ALTER TABLE {prefix}instinctor_chat_messages DROP COLUMN IF EXISTS is_note",
        "ALTER TABLE {prefix}instinctor_chat_conversations DROP COLUMN IF EXISTS visitor_typing_at",
    ],
];
