<?php

declare(strict_types=1);

namespace Atdt;

/**
 * Canonical schema + installer/migrator (SPEC §8.2, §8.3, §8.6).
 * Tables are PHP arrays; Db::createTableSql() emits dialect DDL. v1 is
 * schema_version 1; migrate() is the ladder future versions extend (§8.1).
 * Convention (see Db.php header): non-'short' text columns with defaults
 * must still be supplied on INSERT — MySQL can't DDL-default TEXT.
 */
final class Schema
{
    public const VERSION = 13;

    /**
     * Canonical table definitions. Order is creation order.
     * The last four tables are reserved for v2/v3 (SPEC §8.3): the installer
     * creates them, no v1 code touches them.
     */
    public const TABLES = [
        'settings' => [
            'cols' => ['k' => ['text'], 'v' => ['text']],
            'pk' => ['k'],
        ],
        'users' => [
            'cols' => [
                'id' => 'pk',
                'handle' => ['text', 'unique' => true, 'nocase' => true],
                'pass_hash' => ['text', 'short' => true],
                'level' => ['int', 'default' => 10],
                'flags' => ['text', 'short' => true, 'default' => ''],
                'status' => ['text', 'short' => true, 'default' => 'pending'], // pending|active|locked|deleted
                'location' => ['text', 'null' => true],
                'note' => ['text', 'null' => true], // sysop-only note
                'created_at' => 'datetime',
                'last_login' => ['datetime', 'null' => true],
                'calls' => ['int', 'default' => 0],
                'posts' => ['int', 'default' => 0],
                'time_today' => ['int', 'null' => true], // seconds remaining today; NULL = not yet computed
                'time_bank' => ['int', 'default' => 0],
                'theme' => ['text', 'short' => true, 'default' => ''], // empty = board default theme
                'prefs' => ['text', 'default' => '{}'], // JSON: beep, baud, hotkeys, editor, more
                'sig' => ['text', 'default' => ''],
                'email' => ['text', 'short' => true, 'default' => ''], // only the SysOp sees it (1.5.0)
                'last_bull' => ['int', 'default' => 0], // highest bulletin id seen at logon
                'reset_hash' => ['text', 'short' => true, 'default' => ''], // sha256 of the live reset token
                'reset_at' => ['text', 'short' => true, 'default' => ''], // that token's expiry, UTC
                'last_ip' => ['text', 'short' => true, 'default' => ''], // stamped at login; eases IP bans (1.6.0)
            ],
        ],
        'sessions' => [
            'cols' => [
                'id' => 'pk',
                'token' => ['text', 'unique' => true],
                'user_id' => ['int', 'null' => true],
                'node' => 'int',
                'state' => 'text', // JSON screen stack (§9.4)
                'seq' => ['int', 'default' => 0],
                'ip' => ['text', 'short' => true],
                'created_at' => 'datetime',
                'last_seen' => 'datetime',
                'last_input' => ['text', 'short' => true, 'default' => ''], // 1.6.0: idle is measured on input, not polls
                'kick' => ['int', 'default' => 0],                          // 1.6.0: the console can hang up a line
                'active' => ['bool', 'default' => 1],
            ],
            'indexes' => [['active', 'last_seen']],
        ],
        'call_log' => [
            'cols' => [
                'id' => 'pk',
                'user_id' => ['int', 'null' => true],
                'handle' => ['text', 'short' => true],
                'node' => 'int',
                'on_at' => 'datetime',
                'off_at' => ['datetime', 'null' => true],
                'summary' => ['text', 'default' => ''], // "Posted 2 msgs, played HILO"
            ],
            'indexes' => [['on_at']],
        ],
        'message_categories' => [
            'cols' => [
                'id' => 'pk',
                'name' => ['text', 'short' => true],
                'sort' => ['int', 'default' => 0],
            ],
        ],
        'message_areas' => [
            'cols' => [
                'id' => 'pk',
                'tag' => ['text', 'unique' => true], // short key e.g. GENERAL
                'name' => ['text', 'short' => true],
                'descr' => ['text', 'default' => ''],
                'sort' => ['int', 'default' => 0],
                'category_id' => ['int', 'default' => 0], // message_categories.id, 0 = uncategorized
                'acs_read' => ['text', 'short' => true, 'default' => ''],
                'acs_write' => ['text', 'short' => true, 'default' => ''],
                'kind' => ['text', 'short' => true, 'default' => 'local'], // local|mail|echo (echo=v2)
                'echo_tag' => ['text', 'short' => true, 'null' => true],   // v2 reserved
                'network_id' => ['int', 'null' => true],                   // v2 reserved
                'listed' => ['bool', 'default' => 1], // 1.6.5: discovery, never security (HC-3)
            ],
        ],
        'messages' => [
            'cols' => [
                'id' => 'pk',
                'area_id' => 'int',
                'num' => 'int', // per-area sequence number
                'from_user_id' => ['int', 'null' => true],
                'from_name' => ['text', 'short' => true],
                'to_user_id' => ['int', 'null' => true],
                'to_name' => ['text', 'short' => true, 'default' => 'All'],
                'subject' => 'text',
                'body' => 'text',
                'posted_at' => 'datetime',
                'reply_to_id' => ['int', 'null' => true],
                'msgid' => ['text', 'null' => true],       // FTN-ready
                'reply_msgid' => ['text', 'null' => true], // FTN-ready
                'origin' => ['text', 'null' => true],      // FTN-ready
                'kludges' => ['text', 'default' => '{}'],  // FTN-ready
                'is_read' => ['bool', 'default' => 0],     // meaningful for kind=mail only
                'deleted' => ['bool', 'default' => 0],
                // 1.7.0: which import batch wrote this message. NULL for
                // every message posted on the board; undo deletes by it.
                'import_batch_id' => ['int', 'null' => true],
            ],
            // Unique: the §18 lookup index doubles as the per-area sequence
            // guarantee — concurrent posts collide here and retry (Messages).
            'uniques' => [['area_id', 'num']],
            'indexes' => [['import_batch_id']],
        ],
        // 1.7.0 message import: one row per import run. The staging table
        // holds parsed, normalized messages between the confirm click and
        // the chunked write; it drains to zero when the batch completes.
        'import_batches' => [
            'cols' => [
                'id' => 'pk',
                'created_at' => 'datetime',
                'sysop_user_id' => 'int',
                'source_package' => ['text', 'short' => true], // picker display name
                'source_format' => ['text', 'short' => true],  // resolved parser key
                'source_label' => 'text',                      // filename or server path
                'source_dir' => 'text',                        // resolved directory parsed
                'encoding' => ['text', 'short' => true, 'default' => 'cp437'],
                'timezone' => ['text', 'short' => true, 'default' => 'UTC'],
                // scanning|preview|staging|running|paused|complete|failed|undone
                'status' => ['text', 'short' => true, 'default' => 'scanning'],
                'phase' => ['text', 'short' => true, 'default' => 'scan'], // scan|stage|write|link|finish
                'cursor' => ['text', 'default' => '{}'],   // parser resume position (JSON)
                'stats' => ['text', 'default' => '{}'],    // counts, samples, area survey (JSON)
                'mapping' => ['text', 'default' => '{}'],  // source area key => plan (JSON)
                'total_messages' => ['int', 'default' => 0],
                'done_messages' => ['int', 'default' => 0],
                'created_areas' => ['text', 'default' => '[]'], // JSON area ids this batch created
                'error' => ['text', 'null' => true],
            ],
        ],
        'import_staging' => [
            'cols' => [
                'id' => 'pk',
                'batch_id' => 'int',
                'source_area_key' => ['text', 'short' => true],
                'target_area_id' => ['int', 'default' => 0],
                'date_utc' => ['text', 'short' => true, 'default' => ''], // '' = undated, patched before write
                'from_handle' => ['text', 'short' => true],
                'to_handle' => ['text', 'short' => true, 'null' => true],
                'subject' => 'text',
                'body' => 'text',
                'source_msg_id' => ['text', 'short' => true],
                'source_reply_to' => ['text', 'short' => true, 'null' => true],
                'new_msg_id' => ['int', 'default' => 0],  // messages.id after the write pass
                'new_num' => ['int', 'default' => 0],
                'written' => ['int', 'default' => 0],     // 0 staged, 1 written, 2 linked
            ],
            'indexes' => [
                ['batch_id', 'written'],
                ['batch_id', 'date_utc'],
                ['batch_id', 'source_area_key', 'source_msg_id'],
            ],
        ],
        // 1.7.0 DOS doors: registered doors, live in-door sessions, and
        // DOS record locks (SPEC-DOSDOORS §11). The emulator runs in the
        // caller's browser; these tables are the server's whole share.
        'dos_doors' => [
            'cols' => [
                'id' => 'pk',
                'slug' => ['text', 'unique' => true],   // [a-z0-9_-]{2,16}
                'title' => ['text', 'short' => true],
                'enabled' => ['bool', 'default' => 1],
                'acs' => ['text', 'short' => true, 'default' => ''],
                'sort' => ['int', 'default' => 0],
                'max_nodes' => ['int', 'default' => 4],
                'minutes' => ['int', 'default' => 45],
                'manifest' => 'text',
                'debug' => ['bool', 'default' => 0],    // diagnostics ring on/off
                'created_at' => 'datetime',
            ],
        ],
        'dos_sessions' => [
            'cols' => [
                'id' => 'pk',
                'token' => ['text', 'unique' => true],
                'slug' => ['text', 'short' => true],
                'user_id' => 'int',
                'node' => 'int',
                'started_at' => 'datetime',
                'last_seen' => 'datetime',
                'minutes_left' => ['int', 'default' => 0],
            ],
            'indexes' => [['slug'], ['last_seen']],
        ],
        'dos_locks' => [
            'cols' => [
                'id' => 'pk',
                'session_id' => 'int',
                'slug' => ['text', 'short' => true],
                'path' => ['text', 'short' => true],
                'offset' => 'int',
                'length' => 'int',
                'taken_at' => 'datetime',
            ],
            'indexes' => [['slug', 'path'], ['session_id']],
        ],
        'lastread' => [
            'cols' => [
                'user_id' => 'int',
                'area_id' => 'int',
                'last_num' => ['int', 'default' => 0],
            ],
            'pk' => ['user_id', 'area_id'],
        ],
        'read_marks' => [
            // 1.6.1: messages read ABOVE the lastread pointer (a caller who
            // reads a new reply before an older new message). Rows compact
            // into the pointer as the gap below them is walked, so the
            // table stays a sliver, not a per-message ledger.
            'cols' => [
                'user_id' => 'int',
                'area_id' => 'int',
                'num' => 'int',
            ],
            'pk' => ['user_id', 'area_id', 'num'],
        ],
        'oneliners' => [
            'cols' => [
                'id' => 'pk',
                'user_id' => 'int',
                'handle' => ['text', 'short' => true],
                'body' => 'text',
                'at' => 'datetime',
            ],
        ],
        'rumors' => [
            'cols' => [
                'id' => 'pk',
                'user_id' => 'int',
                'handle' => ['text', 'short' => true],
                'body' => 'text',
                'at' => 'datetime',
                'approved' => ['bool', 'default' => 1],
            ],
        ],
        'voting_q' => [
            'cols' => [
                'id' => 'pk',
                'question' => 'text',
                'author' => ['text', 'short' => true],
                'acs' => ['text', 'short' => true, 'default' => ''],
                'created_at' => 'datetime',
                'open' => ['bool', 'default' => 1],
            ],
        ],
        'voting_opt' => [
            'cols' => [
                'id' => 'pk',
                'q_id' => 'int',
                'label' => 'text',
                'votes' => ['int', 'default' => 0],
            ],
            'indexes' => [['q_id']],
        ],
        'voting_cast' => [
            'cols' => [
                'q_id' => 'int',
                'user_id' => 'int',
                'opt_id' => 'int',
            ],
            'pk' => ['q_id', 'user_id'],
        ],
        'bbslist' => [
            'cols' => [
                'id' => 'pk',
                'name' => ['text', 'short' => true],
                'host' => 'text',
                'sysop' => ['text', 'short' => true, 'default' => ''],
                'notes' => ['text', 'default' => ''],
                'added_by' => ['text', 'short' => true],
                'at' => 'datetime',
            ],
        ],
        'bulletins' => [
            'cols' => [
                'id' => 'pk',
                'title' => 'text',
                'body' => 'text',
                'acs' => ['text', 'short' => true, 'default' => ''],
                'sort' => ['int', 'default' => 0],
                'updated_at' => 'datetime',
                // v2: optional theme art slot; when set the caller sees the
                // .ans full-screen instead of the wrapped text body (M7).
                'art' => ['text', 'short' => true, 'default' => ''],
            ],
        ],
        'chat_msgs' => [
            'cols' => [
                'id' => 'pk',
                'from_node' => 'int',
                'from_handle' => ['text', 'short' => true],
                'to_node' => ['int', 'null' => true], // NULL = broadcast/page audience
                'kind' => ['text', 'short' => true, 'default' => 'chat'], // chat|page|broadcast|sysop
                'body' => 'text',
                'at' => 'datetime',
            ],
        ],
        'doors' => [
            'cols' => [
                'id' => 'pk',
                'slug' => ['text', 'unique' => true],
                'title' => ['text', 'short' => true],
                'dir' => ['text', 'short' => true],
                'acs' => ['text', 'short' => true, 'default' => ''],
                'enabled' => ['bool', 'default' => 1],
                'sort' => ['int', 'default' => 0],
                'installed_at' => 'datetime',
            ],
        ],
        'door_kv' => [
            'cols' => [
                'door_slug' => ['text'],
                'k' => ['text'],
                'v' => ['text'],
            ],
            'pk' => ['door_slug', 'k'],
        ],
        'menus' => [
            'cols' => [
                'id' => 'pk',
                'name' => ['text', 'unique' => true], // MAIN, MESSAGE, DOORS, INFO, USER
                'title' => ['text', 'short' => true],
                'art' => ['text', 'short' => true, 'null' => true], // optional .ans in theme art/
                'template' => ['text', 'null' => true], // pipe-code template; NULL = auto-generate
                'fallback_generate' => ['bool', 'default' => 1],
                // 1.6.5 area targeting (HC-2: NULL means legacy, always)
                'area_scope' => ['text', 'short' => true, 'null' => true],   // AreaScope::SCOPE_PATTERN
                'default_area' => ['text', 'short' => true, 'null' => true], // area tag; meaningful only with a scope
            ],
        ],
        'menu_art' => [
            // v2 (M7): per-level menu backdrops. First row (by sort) whose
            // ACS the caller passes wins; menus.art stays the fallback.
            'cols' => [
                'id' => 'pk',
                'menu_id' => 'int',
                'acs' => ['text', 'short' => true, 'default' => ''],
                'art' => ['text', 'short' => true],
                'sort' => ['int', 'default' => 0],
            ],
            'indexes' => [['menu_id', 'sort']],
        ],
        'menu_items' => [
            'cols' => [
                'id' => 'pk',
                'menu_id' => 'int',
                'hotkey' => ['text', 'short' => true],
                'label' => ['text', 'short' => true],
                'acs' => ['text', 'short' => true, 'default' => ''],
                'action' => ['text', 'short' => true], // GOTO|MODULE|DOOR|ART|LOGOFF|BACK
                'arg' => ['text', 'short' => true, 'null' => true],
                // 1.6.5: the generic second argument (area tag or category
                // name for the message modules; future actions may use it).
                'arg2' => ['text', 'short' => true, 'null' => true],
                'sort' => ['int', 'default' => 0],
                'hidden' => ['bool', 'default' => 0],
            ],
            'indexes' => [['menu_id', 'sort']],
        ],
        'events' => [
            'cols' => [
                'id' => 'pk',
                'at' => 'datetime',
                'level' => ['text', 'short' => true], // info|warn|error|security
                'actor' => ['text', 'short' => true, 'default' => ''],
                'msg' => 'text',
            ],
            'indexes' => [['at']],
        ],
        'user_area_scope' => [
            // 1.6.5: one row per caller per menu scope they have used --
            // that scope's current area. Absent row = nothing selected
            // yet. The legacy current area (users.prefs) is untouched
            // and unrelated (HC-2).
            'cols' => [
                'user_id' => 'int',
                'scope' => ['text', 'short' => true],
                'area_tag' => ['text', 'short' => true],
                'updated_at' => 'datetime',
            ],
            'pk' => ['user_id', 'scope'],
            'indexes' => [['user_id']],
        ],
        'push_subs' => [
            // 1.6.1: the SysOp's browser push subscriptions (pages reach a
            // phone or desktop with the console closed). Endpoint URLs run
            // long, so the unique ride is a hash and the URL itself stays
            // full-width text.
            'cols' => [
                'id' => 'pk',
                'endpoint_hash' => ['text', 'short' => true, 'unique' => true],
                'endpoint' => 'text',
                'p256dh' => ['text', 'short' => true],
                'auth' => ['text', 'short' => true],
                'label' => ['text', 'short' => true, 'default' => ''],
                'created_at' => 'datetime',
            ],
        ],
        // ---- Reserved for v2/v3 (SPEC §8.3) — created, never touched by v1 code ----
        'file_areas' => [
            'cols' => [
                'id' => 'pk',
                'tag' => ['text', 'unique' => true],
                'name' => ['text', 'short' => true],
                'descr' => ['text', 'default' => ''],
                'sort' => ['int', 'default' => 0],
                'acs_read' => ['text', 'short' => true, 'default' => ''],
                'acs_write' => ['text', 'short' => true, 'default' => ''],
            ],
        ],
        'files' => [
            'cols' => [
                'id' => 'pk',
                'area_id' => 'int',
                'filename' => ['text', 'short' => true],
                'descr' => ['text', 'default' => ''], // FILE_ID.DIZ
                'size' => ['int', 'default' => 0],
                'sha256' => ['text', 'short' => true, 'null' => true],
                'uploader_id' => ['int', 'null' => true],
                'uploader' => ['text', 'short' => true, 'default' => ''],
                'dl_count' => ['int', 'default' => 0],
                'approved' => ['bool', 'default' => 0],
                'uploaded_at' => 'datetime',
                'deleted' => ['bool', 'default' => 0],
            ],
            'indexes' => [['area_id', 'filename']],
        ],
        'networks' => [
            'cols' => [
                'id' => 'pk',
                'name' => ['text', 'short' => true],
                'kind' => ['text', 'short' => true, 'default' => 'http'], // http|ftn
                'address' => ['text', 'short' => true, 'default' => ''],
                'uplink' => ['text', 'short' => true, 'default' => ''],
                'secret' => ['text', 'short' => true, 'default' => ''],
                'enabled' => ['bool', 'default' => 1],
                'created_at' => 'datetime',
            ],
        ],
        'echo_queue' => [
            'cols' => [
                'id' => 'pk',
                'network_id' => 'int',
                'payload' => ['blob', 'null' => true], // outbound bundle
                'status' => ['text', 'short' => true, 'default' => 'pending'],
                'tries' => ['int', 'default' => 0],
                'created_at' => 'datetime',
                'sent_at' => ['datetime', 'null' => true],
            ],
            'indexes' => [['network_id', 'status']],
        ],
    ];

    /** Create every canonical table + index. */
    public static function create(Db $db): void
    {
        foreach (self::TABLES as $name => $def) {
            foreach ($db->createTableSql($name, $def) as $stmt) {
                $db->exec($stmt);
            }
        }
    }

    /** Drop every canonical table (used by the installer to resume cleanly). */
    public static function dropAll(Db $db): void
    {
        foreach (array_reverse(array_keys(self::TABLES)) as $name) {
            $db->exec($db->dropTableSql($name));
        }
    }

    /**
     * Migration ladder (SPEC §8.1). v1 == 1, so this is a verified no-op today;
     * v2 adds `if ($from < 2) { ... }` steps. Returns the version now current.
     */
    public static function migrate(Db $db): int
    {
        $settings = new Settings($db);
        $from = $settings->int('schema_version', 0);
        if ($from < 1 || $from > self::VERSION) {
            throw new \RuntimeException("Cannot migrate from schema_version $from");
        }
        if ($from < 2) {
            // M7: ANSI bulletins + per-level menu backdrops.
            $db->exec($db->addColumnSql('bulletins', 'art', ['text', 'short' => true, 'default' => '']));
            foreach ($db->createTableSql('menu_art', self::TABLES['menu_art']) as $stmt) {
                $db->exec($stmt);
            }
        }
        if ($from < 3) {
            // Owner feature: message areas grouped under named categories.
            foreach ($db->createTableSql('message_categories', self::TABLES['message_categories']) as $stmt) {
                $db->exec($stmt);
            }
            $db->exec($db->addColumnSql('message_areas', 'category_id', ['int', 'default' => 0]));
        }
        if ($from < 4) {
            // 1.1.0: the art gallery, the selectable default theme, and
            // door games as ordinary menu items. Every step here is
            // idempotent -- a re-run after a mid-upgrade crash is safe.
            if ($settings->get('default_theme', '') === '') {
                $settings->set('default_theme', 'default');
            }
            // An explicit 'default' now means "follow the board default":
            // nobody chose it, it was the only fallback there was.
            $db->q("UPDATE users SET theme = '' WHERE theme = 'default'");
            self::migrateDoorsMenu($db);
            self::migrateArtGallery($db);
        }
        if ($from < 5) {
            // 1.5.0: an email per account (only the SysOp sees it), the
            // unread-bulletin watermark, and the password-reset token
            // fields. Existing callers start with the watermark at the
            // current top so upgrading never replays old bulletins, and
            // allow_user_color is deliberately NOT touched: the new-install
            // default changed, existing boards keep their choice. Column
            // adds are guarded so a mid-upgrade crash can retry the rung.
            foreach ([
                'email' => ['text', 'short' => true, 'default' => ''],
                'last_bull' => ['int', 'default' => 0],
                'reset_hash' => ['text', 'short' => true, 'default' => ''],
                'reset_at' => ['text', 'short' => true, 'default' => ''],
            ] as $col => $spec) {
                if (!self::hasColumn($db, 'users', $col)) {
                    $db->exec($db->addColumnSql('users', $col, $spec));
                }
            }
            $top = (int)$db->val('SELECT COALESCE(MAX(id), 0) FROM bulletins');
            $db->q('UPDATE users SET last_bull = ?', [$top]);
        }
        if ($from < 6) {
            // 1.5.0 follow-up (owner rulings, in order): themes duplicated
            // before 1.5.0 snapshot every string, so the three prompts
            // whose wording changed (the mail room's two, which now
            // advertise the S sent/inbox flip, and the settings key list,
            // which now offers E for email) stay stale in the copy and the
            // new features go unannounced. And theme copies are the
            // SysOp's own files: the board NEVER writes into them, only
            // the shipped default theme is ours to update (via the release
            // zip). So the upgrade just raises a one-time console notice
            // asking the SysOp to touch up their copies by hand; a board
            // with no copies has nothing to do and sees nothing.
            if ((glob(ATDT_ROOT . '/data/themes/*/strings.ini') ?: []) !== []) {
                $settings->set('notice_theme_strings', '1.5.0');
            }
        }
        if ($from < 7) {
            // 1.6.0: sessions learn two things. last_input is when the
            // caller last pressed something (polls do not count), so an
            // abandoned tab stops holding a node; kick lets the console
            // hang up a line. And the standing rule from the owner: any
            // release that adds or rewords default-theme strings arms the
            // one-time console notice, so a SysOp with copies always
            // hears about it (the matrix reset strings from the 1.5.x
            // line are the ones a 1.6.0 upgrade needs).
            foreach ([
                'last_input' => ['text', 'short' => true, 'default' => ''],
                'kick' => ['int', 'default' => 0],
            ] as $col => $spec) {
                if (!self::hasColumn($db, 'sessions', $col)) {
                    $db->exec($db->addColumnSql('sessions', $col, $spec));
                }
            }
            $db->q("UPDATE sessions SET last_input = last_seen WHERE last_input = ''");
            // Keep the OLDEST armed version: a board climbing several
            // rungs at once needs every list since its copies were made,
            // and the console modal shows the union from that point.
            if ((glob(ATDT_ROOT . '/data/themes/*/strings.ini') ?: []) !== []
                && trim((string)($settings->get('notice_theme_strings', '') ?? '')) === '') {
                $settings->set('notice_theme_strings', '1.6.0');
            }
        }
        if ($from < 8) {
            // 1.6.0 refresh: remember each caller's last login address so
            // an IP ban starts from the user record instead of the logs.
            if (!self::hasColumn($db, 'users', 'last_ip')) {
                $db->exec($db->addColumnSql('users', 'last_ip', ['text', 'short' => true, 'default' => '']));
            }
        }
        if ($from < 9) {
            // 1.6.1: read marks above the lastread pointer (reading a reply
            // no longer swallows an older unread message), the SysOp's
            // browser push subscriptions, and the prohibited-words gate for
            // new handles -- seeded with 'sysop' only when the setting has
            // never existed, so a board that emptied the list stays empty.
            foreach (['read_marks', 'push_subs'] as $t) {
                if (self::hasTable($db, $t)) {
                    continue; // a re-run after a mid-upgrade crash is safe
                }
                foreach ($db->createTableSql($t, self::TABLES[$t]) as $stmt) {
                    $db->exec($stmt);
                }
            }
            if ($settings->get('nua_banned_words') === null) {
                $settings->set('nua_banned_words', 'sysop');
            }
            // The standing rule: new default-theme strings arm the one-time
            // console notice for boards that keep theme copies.
            if ((glob(ATDT_ROOT . '/data/themes/*/strings.ini') ?: []) !== []
                && trim((string)($settings->get('notice_theme_strings', '') ?? '')) === '') {
                $settings->set('notice_theme_strings', '1.6.1');
            }
        }
        if ($from < 10) {
            // 1.6.5 area targeting: one rung, additive only (HC-5), each
            // step guarded so a mid-upgrade crash can re-run it. Nothing
            // is copied or backfilled; blank means legacy (HC-2).
            foreach ([
                ['message_areas', 'listed', ['bool', 'default' => 1]],
                ['menus', 'area_scope', ['text', 'short' => true, 'null' => true]],
                ['menus', 'default_area', ['text', 'short' => true, 'null' => true]],
                ['menu_items', 'arg2', ['text', 'short' => true, 'null' => true]],
            ] as [$t10, $col10, $spec10]) {
                if (!self::hasColumn($db, $t10, $col10)) {
                    $db->exec($db->addColumnSql($t10, $col10, $spec10));
                }
            }
            if (!self::hasTable($db, 'user_area_scope')) {
                foreach ($db->createTableSql('user_area_scope', self::TABLES['user_area_scope']) as $stmt) {
                    $db->exec($stmt);
                }
            }
            // The standing rule: new default-theme strings (the two
            // area-denial lines) arm the one-time console notice for
            // boards that keep theme copies.
            if ((glob(ATDT_ROOT . '/data/themes/*/strings.ini') ?: []) !== []
                && trim((string)($settings->get('notice_theme_strings', '') ?? '')) === '') {
                $settings->set('notice_theme_strings', '1.6.5');
            }
        }
        if ($from < 11) {
            // 1.7.0 message import: the batch stamp on messages, the batch
            // and staging tables. Additive only, every step guarded so a
            // mid-upgrade crash can re-run the rung.
            if (!self::hasColumn($db, 'messages', 'import_batch_id')) {
                $db->exec($db->addColumnSql('messages', 'import_batch_id', ['int', 'null' => true]));
            }
            try {
                $db->exec('CREATE INDEX ix_messages_import_batch_id ON messages (import_batch_id)');
            } catch (\Throwable $e) {
                // the index already exists: a re-run after a mid-upgrade crash
            }
            foreach (['import_batches', 'import_staging'] as $t11) {
                if (self::hasTable($db, $t11)) {
                    continue;
                }
                foreach ($db->createTableSql($t11, self::TABLES[$t11]) as $stmt) {
                    $db->exec($stmt);
                }
            }
            // The standing rule: a new default-theme string (the mail
            // pager row) arms the one-time console notice for boards
            // that keep theme copies.
            if ((glob(ATDT_ROOT . '/data/themes/*/strings.ini') ?: []) !== []
                && trim((string)($settings->get('notice_theme_strings', '') ?? '')) === '') {
                $settings->set('notice_theme_strings', '1.7.0');
            }
        }
        if ($from < 12) {
            // 1.7.0 DOS doors: three new tables, nothing else touched.
            // The master switch stays off until the SysOp turns it on.
            foreach (['dos_doors', 'dos_sessions', 'dos_locks'] as $t12) {
                if (self::hasTable($db, $t12)) {
                    continue;
                }
                foreach ($db->createTableSql($t12, self::TABLES[$t12]) as $stmt) {
                    $db->exec($stmt);
                }
            }
        }
        if ($from < 13) {
            // Still 1.7.0: no schema change at all -- this rung exists to
            // re-arm the theme-copies notice for the strings reworded late
            // in the cycle (list > commands grew Home/End, nua > bad_handle
            // now allows [ and ]), so a board that upgraded mid-1.7.0 and
            // dismissed the earlier note still hears about them.
            if ((glob(ATDT_ROOT . '/data/themes/*/strings.ini') ?: []) !== []
                && trim((string)($settings->get('notice_theme_strings', '') ?? '')) === '') {
                $settings->set('notice_theme_strings', '1.7.0');
            }
        }
        if ($from !== self::VERSION) {
            $settings->set('schema_version', (string)self::VERSION);
        }
        return self::VERSION;
    }

    /** True when the table already exists (rung re-run safety). */
    private static function hasTable(Db $db, string $table): bool
    {
        if ($db->dialect() === Db::MYSQL) {
            return $db->one(
                'SELECT table_name FROM information_schema.tables'
                    . ' WHERE table_schema = DATABASE() AND table_name = ?',
                [$table]
            ) !== null;
        }
        return $db->one(
            "SELECT name FROM sqlite_master WHERE type = 'table' AND name = ?",
            [$table]
        ) !== null;
    }

    /** True when the table already carries the column (rung re-run safety). */
    private static function hasColumn(Db $db, string $table, string $col): bool
    {
        if ($db->dialect() === Db::MYSQL) {
            return $db->one(
                'SELECT column_name FROM information_schema.columns'
                    . ' WHERE table_schema = DATABASE() AND table_name = ? AND column_name = ?',
                [$table, $col]
            ) !== null;
        }
        foreach ($db->all("PRAGMA table_info($table)") as $row) {
            if (strcasecmp((string)($row['name'] ?? ''), $col) === 0) {
                return true;
            }
        }
        return false;
    }

    /**
     * v4: the DOORS menu loses its magic. Injected-at-render door rows
     * become real menu_items the designer can edit like any other.
     */
    private static function migrateDoorsMenu(Db $db): void
    {
        $menu = $db->one('SELECT * FROM menus WHERE name = ?', ['DOORS']);
        if ($menu === null) {
            return; // the sysop removed it; nothing to materialize
        }
        $items = $db->all('SELECT * FROM menu_items WHERE menu_id = ?', [(int)$menu['id']]);
        $taken = [];
        $have = [];
        foreach ($items as $it) {
            $taken[strtoupper((string)$it['hotkey'])] = true;
            if ((string)$it['action'] === 'DOOR') {
                $have[strtolower((string)$it['arg'])] = true;
            }
        }
        $sort = 0;
        foreach ($db->all('SELECT * FROM doors WHERE enabled = 1 ORDER BY sort, id') as $door) {
            $slug = strtolower((string)$door['slug']);
            if (isset($have[$slug])) {
                continue;
            }
            $hotkey = null;
            foreach (array_merge(range('1', '9'), range('A', 'Z')) as $k) {
                if (empty($taken[(string)$k])) {
                    $hotkey = (string)$k;
                    break;
                }
            }
            if ($hotkey === null) {
                break; // a full menu; the rest stay reachable via doors admin
            }
            $taken[$hotkey] = true;
            $db->insert('menu_items', [
                'menu_id' => (int)$menu['id'], 'hotkey' => $hotkey,
                'label' => (string)$door['title'], 'acs' => (string)($door['acs'] ?? ''),
                'action' => 'DOOR', 'arg' => $slug,
                'sort' => ++$sort, 'hidden' => 0,
            ]);
        }
    }

    /**
     * v4: per-theme art moves onto the board-wide gallery shelf
     * (data/art). Built-in slots become art.json bindings so every
     * theme keeps its face; menu and bulletin references are renamed
     * when a piece had to take a prefixed name. Shipped theme art (if
     * any) stays put -- Theme::art() still reads it as a fallback.
     */
    private static function migrateArtGallery(Db $db): void
    {
        $galleryDir = ATDT_ROOT . '/data/art';
        if (!is_dir($galleryDir) && !@mkdir($galleryDir, 0755, true)) {
            return; // unwritable host: legacy per-theme lookups still work
        }
        foreach (glob(ATDT_ROOT . '/data/themes/*', GLOB_ONLYDIR) ?: [] as $themeDir) {
            $theme = basename($themeDir);
            $bindings = [];
            foreach (glob($themeDir . '/art/*.ans') ?: [] as $file) {
                $slot = strtolower(basename($file, '.ans'));
                if (preg_match(Art::NAME_RE, $slot) !== 1) {
                    continue;
                }
                $name = $slot;
                if (is_file($galleryDir . '/' . $name . '.ans')) {
                    $name = substr(strtolower($theme) . '-' . $slot, 0, 32);
                    $n = 2;
                    while (is_file($galleryDir . '/' . $name . '.ans')) {
                        $name = substr(strtolower($theme) . '-' . $slot, 0, 30) . $n++;
                    }
                }
                if (!@rename($file, $galleryDir . '/' . $name . '.ans')) {
                    continue;
                }
                if (in_array($slot, Theme::BUILTIN_SLOTS, true)) {
                    $bindings[$slot] = $name;
                }
                if ($name !== $slot) {
                    // Follow the rename wherever the old name was pointed at.
                    $db->q('UPDATE menus SET art = ? WHERE art = ?', [$name, $slot]);
                    $db->q('UPDATE menu_art SET art = ? WHERE art = ?', [$name, $slot]);
                    $db->q('UPDATE bulletins SET art = ? WHERE art = ?', [$name, $slot]);
                }
            }
            if ($bindings !== []) {
                $mapFile = $themeDir . '/art.json';
                $map = is_file($mapFile) ? json_decode((string)@file_get_contents($mapFile), true) : [];
                $map = is_array($map) ? $map : [];
                @file_put_contents($mapFile, json_encode($bindings + $map, JSON_PRETTY_PRINT));
            }
            if (is_dir($themeDir . '/art')) {
                @rmdir($themeDir . '/art'); // only succeeds once emptied
            }
        }
    }

    /**
     * Seed defaults (SPEC §8.6). $o requires: board_name, sysop_handle,
     * sysop_pass, timezone, base_url, cron_key.
     */
    public static function seed(Db $db, array $o): void
    {
        $now = now_utc();
        $db->tx(function (Db $db) use ($o, $now): void {
            $settings = [
                'board_name' => $o['board_name'],
                'base_url' => $o['base_url'],
                'timezone' => $o['timezone'],
                'max_nodes' => '10',
                'ice_colors' => '0',
                'new_user_policy' => 'approve', // approve|auto|closed
                'allow_user_color' => '1', // 1.5.0: on for new installs; upgrades keep their choice
                'connect_ceremony' => '1',
                'default_theme' => 'default',
                'mail_enabled' => '0', // 1.5.0: outgoing mail is opt-in
                'mail_mode' => 'smtp', // smtp | phpmail
                'smtp_host' => '',
                'smtp_port' => '587',
                'smtp_user' => '',
                'smtp_pass' => '',
                'smtp_secure' => 'tls', // tls (STARTTLS) | ssl (implicit) | none
                'mail_from' => '',
                'nua_question' => 'Where did you hear about this board?',
                'nua_banned_words' => 'sysop', // 1.6.1: no caller impersonates the office
                'cron_key' => $o['cron_key'],
                'schema_version' => (string)self::VERSION,
                'board_founded' => gmdate('Y-m-d'),
                'total_calls' => '0',
            ];
            foreach ($settings as $k => $v) {
                $db->insert('settings', ['k' => $k, 'v' => $v]);
            }

            $db->insert('users', [
                'handle' => $o['sysop_handle'],
                'pass_hash' => password_hash($o['sysop_pass'], PASSWORD_DEFAULT),
                'level' => 255,
                'flags' => '',
                'status' => 'active',
                'location' => '',
                'note' => 'Created by the installer',
                'created_at' => $now,
                'calls' => 0,
                'posts' => 0,
                'theme' => '', // empty = follow the board's default theme
                'prefs' => '{}',
                'sig' => '',
            ]);

            $mainCat = $db->insert('message_categories', ['name' => 'Main Boards', 'sort' => 10]);
            $areas = [
                ['GENERAL', 'General Discussion', 'Open discussion for all callers', 10, 'local', $mainCat],
                ['ATDT', 'Board Talk', 'Talk about this board: questions, ideas, gripes', 20, 'local', $mainCat],
                ['_MAIL', 'Private Mail', 'Private caller-to-caller mail', 999, 'mail', 0],
            ];
            foreach ($areas as [$tag, $name, $descr, $sort, $kind, $cat]) {
                $db->insert('message_areas', [
                    'tag' => $tag, 'name' => $name, 'descr' => $descr, 'sort' => $sort,
                    'acs_read' => '', 'acs_write' => '', 'kind' => $kind, 'category_id' => $cat,
                ]);
            }

            self::seedMenus($db);

            $db->insert('bulletins', [
                'title' => 'Welcome aboard',
                'body' => implode("\n", [
                    'Welcome, caller!',
                    '',
                    'This board runs ATDT, a web-native BBS. Everything works the way',
                    'boards did in the dial-up days: message bases, oneliners, door games,',
                    'and a sysop who reads everything.',
                    '',
                    'Be excellent to each other, and leave a oneliner on your way through.',
                ]),
                'acs' => '', 'sort' => 10, 'updated_at' => $now,
            ]);

            foreach ([['hilo', 'HILO', 'hilo', 10], ['starmerchant', 'Star Merchant', 'starmerchant', 20]] as [$slug, $title, $dir, $sort]) {
                $db->insert('doors', [
                    'slug' => $slug, 'title' => $title, 'dir' => $dir,
                    'acs' => '', 'enabled' => 1, 'sort' => $sort, 'installed_at' => $now,
                ]);
            }

            $qId = $db->insert('voting_q', [
                'question' => 'Best era of the BBS scene?',
                'author' => $o['sysop_handle'], 'acs' => '', 'created_at' => $now, 'open' => 1,
            ]);
            $opts = [
                '8-bit pioneers (1978-1985)',
                'The golden age (1986-1992)',
                'The ANSI renaissance (1993-1996)',
                'The long twilight (1997 and on)',
            ];
            foreach ($opts as $label) {
                $db->insert('voting_opt', ['q_id' => $qId, 'label' => $label, 'votes' => 0]);
            }
        });
    }

    /** Default menu set (SPEC §10.3). */
    private static function seedMenus(Db $db): void
    {
        $menus = [
            'MAIN' => ['Main Menu', [
                ['M', 'Message bases', 'GOTO', 'MESSAGE'],
                ['E', 'E-mail', 'MODULE', 'mailbox'],
                ['D', 'Doors', 'GOTO', 'DOORS'],
                ['O', 'Oneliners', 'MODULE', 'oneliners'],
                ['V', 'Voting booth', 'MODULE', 'voting'],
                ['R', 'Rumors', 'MODULE', 'rumors'],
                ['A', 'Auto-message', 'MODULE', 'automsg'],
                ['B', 'Bulletins', 'MODULE', 'bulletins'],
                ['U', 'User list', 'MODULE', 'userlist'],
                ['W', "Who's online", 'MODULE', 'whosonline'],
                ['C', 'Chat', 'MODULE', 'chat'],
                ['P', 'Page sysop', 'MODULE', 'pagesysop'],
                ['L', 'Last callers', 'MODULE', 'lastcallers'],
                ['S', 'Board stats', 'MODULE', 'stats'],
                ['Y', 'Your settings', 'MODULE', 'usersettings'],
                ['I', 'Info desk', 'GOTO', 'INFO'],
                ['G', 'Goodbye (log off)', 'LOGOFF', null],
            ]],
            'MESSAGE' => ['Message Menu', [
                ['A', 'Area change', 'MODULE', 'areachange'],
                ['R', 'Read messages', 'MODULE', 'read'],
                ['P', 'Post a message', 'MODULE', 'post'],
                ['N', 'New scan', 'MODULE', 'newscan'],
                ['Q', 'Quit to main', 'BACK', null],
            ]],
            // Ordinary items like any other menu (1.1.0): edit, reorder,
            // or remove them in the designer. Scanning a new door offers
            // to add its item here.
            'DOORS' => ['Door Games', [
                ['1', 'HILO', 'DOOR', 'hilo'],
                ['2', 'Star Merchant', 'DOOR', 'starmerchant'],
                ['Q', 'Back to main', 'BACK', null],
            ]],
            'INFO' => ['Info Desk', [
                ['B', 'Bulletins', 'MODULE', 'bulletins'],
                ['L', 'BBS list', 'MODULE', 'bbslist'],
                ['S', 'Board stats', 'MODULE', 'stats'],
                ['Q', 'Back to main', 'BACK', null],
            ]],
            'USER' => ['User Menu', []], // reserved, unused in v1
        ];
        foreach ($menus as $name => [$title, $items]) {
            $menuId = $db->insert('menus', [
                'name' => $name, 'title' => $title, 'fallback_generate' => 1,
            ]);
            $sort = 10;
            foreach ($items as [$hotkey, $label, $action, $arg]) {
                $db->insert('menu_items', [
                    'menu_id' => $menuId, 'hotkey' => $hotkey, 'label' => $label,
                    'acs' => '', 'action' => $action, 'arg' => $arg,
                    'sort' => $sort, 'hidden' => 0,
                ]);
                $sort += 10;
            }
        }
    }
}
