import type { GpLiveDb } from './gpLiveDb'
import { isPostgresDb } from './gpLiveDb'

type Db = GpLiveDb

const USERS_FINAL_COLUMNS = new Set([
    'user_id',
    'display_id',
    'display_name',
    'account',
    'password_hash',
    'email',
    'avatar_url',
    'banner_url',
    'has_change_nickname',
    'is_verified',
    'coin_balance',
    'streamer_exp',
    'rich_exp',
    'created_at',
    'updated_at',
])

/** PRAGMA table_info 回傳順序（與 db.ts CREATE TABLE 一致；email 在 password_hash 與 avatar_url 之間） */
const USERS_COLUMN_ORDER = [
    'user_id',
    'display_id',
    'display_name',
    'account',
    'password_hash',
    'email',
    'avatar_url',
    'banner_url',
    'has_change_nickname',
    'is_verified',
    'coin_balance',
    'streamer_exp',
    'rich_exp',
    'created_at',
    'updated_at',
] as const

const UNIX_NOW = `CAST(strftime('%s','now') AS INTEGER)`

async function getUsersColumnOrder(db: Db): Promise<string[]> {
    const rows = await db.all<{ name: string; cid: number }>(`PRAGMA table_info(users);`)
    return rows.slice().sort((a, b) => a.cid - b.cid).map((r) => r.name)
}

async function ensureUsersIndexes(db: Db) {
    try {
        await db.exec(`CREATE UNIQUE INDEX IF NOT EXISTS idx_users_account ON users(account);`)
        await db.exec(`CREATE UNIQUE INDEX IF NOT EXISTS idx_users_display_id ON users(display_id);`)
        await db.exec(`CREATE UNIQUE INDEX IF NOT EXISTS idx_users_email ON users(email);`)
    } catch {
        // ignore
    }
}

/**
 * 以正確欄位順序重建 users（資料由同名欄位複製）。
 * SQLite 的 ALTER TABLE ADD COLUMN 只能加在表尾，故先前用 ADD 會讓 email 跑到最後。
 */
async function rebuildUsersToCanonicalColumnOrder(db: Db) {
    const cols = await db.all<{ name: string }>(`PRAGMA table_info(users);`)
    const hasEmail = cols.some((c) => c.name === 'email')
    const emailSelect = hasEmail ? `NULLIF(TRIM(email), '')` : `NULL`
    const hasCoinBalance = cols.some((c) => c.name === 'coin_balance')
    const coinBalanceSelect = hasCoinBalance ? `COALESCE(CAST(coin_balance AS INTEGER), 0)` : '0'

    await db.exec(`PRAGMA foreign_keys = OFF;`)
    await db.exec(`BEGIN;`)
    try {
        await db.exec(`
            CREATE TABLE users__ord (
                user_id TEXT PRIMARY KEY,
                display_id TEXT NOT NULL UNIQUE,
                display_name TEXT NOT NULL,
                account TEXT NOT NULL UNIQUE,
                password_hash TEXT,
                email TEXT UNIQUE,
                avatar_url TEXT,
                banner_url TEXT,
                has_change_nickname INTEGER NOT NULL DEFAULT 0,
                is_verified INTEGER NOT NULL DEFAULT 0,
                coin_balance INTEGER NOT NULL DEFAULT 0,
                streamer_exp INTEGER NOT NULL DEFAULT 0,
                rich_exp INTEGER NOT NULL DEFAULT 0,
                created_at INTEGER NOT NULL,
                updated_at INTEGER NOT NULL
            );
        `)
        await db.exec(`
            INSERT INTO users__ord (
                user_id, display_id, display_name, account, password_hash, email, avatar_url, banner_url,
                has_change_nickname, is_verified, coin_balance, streamer_exp, rich_exp, created_at, updated_at
            )
            SELECT
                user_id, display_id, display_name, account, password_hash,
                ${emailSelect},
                avatar_url, banner_url,
                has_change_nickname, is_verified,
                ${coinBalanceSelect},
                streamer_exp, rich_exp, created_at, updated_at
            FROM users;
        `)
        await db.exec(`DROP TABLE users;`)
        await db.exec(`ALTER TABLE users__ord RENAME TO users;`)
        await db.exec(`COMMIT;`)
    } catch (e) {
        try {
            await db.exec(`ROLLBACK;`)
        } catch {
            // ignore
        }
        throw e
    } finally {
        await db.exec(`PRAGMA foreign_keys = ON;`)
    }
    await ensureUsersIndexes(db)
}

function colExpr(alias: string, candidates: string[], names: Set<string>): string {
    const existing = candidates.filter((n) => names.has(n))
    if (existing.length === 0) return 'NULL'
    // SQLite 的 COALESCE 至少需兩個引數；單一欄位不可包成 COALESCE(col)
    if (existing.length === 1) return `${alias}."${existing[0]}"`
    return `COALESCE(${existing.map((n) => `${alias}."${n}"`).join(', ')})`
}

/**
 * 將 users 整理為固定欄位（含 email 於 password_hash 與 avatar_url 之間）；合併重複欄位並寫入 user_site_admin。
 */
export async function migrateUsersTableSchema(db: Db) {
    let cols = await db.all<{ name: string }>(`PRAGMA table_info(users);`)
    if (!cols?.length) return

    let names = new Set(cols.map((c) => c.name))

    if (names.has('id') && !names.has('user_id')) {
        await db.exec(`ALTER TABLE users RENAME COLUMN id TO user_id;`)
        cols = await db.all(`PRAGMA table_info(users);`)
        names = new Set(cols.map((c) => c.name))
    }

    await db.exec(`
        CREATE TABLE IF NOT EXISTS user_site_admin (
            user_id TEXT PRIMARY KEY,
            -- created_at / updated_at：Unix 秒（INTEGER）
            created_at INTEGER NOT NULL DEFAULT (${UNIX_NOW}),
            updated_at INTEGER NOT NULL DEFAULT (${UNIX_NOW}),
            FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE
        );
    `)

    const columnOrder = await getUsersColumnOrder(db)
    const setMatchesFinal =
        names.size === USERS_FINAL_COLUMNS.size && [...USERS_FINAL_COLUMNS].every((c) => names.has(c))
    const orderMatchesCanonical =
        columnOrder.length === USERS_COLUMN_ORDER.length &&
        USERS_COLUMN_ORDER.every((c, i) => columnOrder[i] === c)

    if (setMatchesFinal && orderMatchesCanonical) {
        await ensureUsersIndexes(db)
        return
    }

    if (setMatchesFinal && !orderMatchesCanonical) {
        await rebuildUsersToCanonicalColumnOrder(db)
        return
    }

    const colsWithoutEmail = new Set([...USERS_FINAL_COLUMNS].filter((c) => c !== 'email'))
    if (
        names.size === colsWithoutEmail.size &&
        [...colsWithoutEmail].every((c) => names.has(c)) &&
        !names.has('email')
    ) {
        await rebuildUsersToCanonicalColumnOrder(db)
        return
    }

    if (names.has('isSuperAdmin')) {
        const createdRef = names.has('createdAt')
            ? `CASE
                    WHEN typeof(u.createdAt)='text' AND CAST(u.createdAt AS INTEGER) > 1000000000000
                        THEN CAST(u.createdAt AS INTEGER) / 1000
                    WHEN typeof(u.createdAt)='text' AND CAST(u.createdAt AS INTEGER) > 0
                        THEN CAST(u.createdAt AS INTEGER)
                    ELSE ${UNIX_NOW}
                END`
            : names.has('created_at')
              ? `CASE WHEN COALESCE(u.created_at, 0) > 0 THEN u.created_at ELSE ${UNIX_NOW} END`
              : UNIX_NOW

        await db.exec(`
            INSERT OR IGNORE INTO user_site_admin (user_id, created_at, updated_at)
            SELECT
                u.user_id,
                ${createdRef},
                ${createdRef}
            FROM users u
            WHERE COALESCE(u.isSuperAdmin, 0) = 1;
        `)
    }

    const uid = colExpr('u', ['user_id'], names)
    const displayName = colExpr('u', ['display_name', 'displayName'], names)
    const pwd = colExpr('u', ['password_hash', 'passwordHash'], names)
    const emailCol = colExpr('u', ['email'], names)
    const avatar = colExpr('u', ['avatar_url', 'avatar'], names)
    const banner = colExpr('u', ['banner_url', 'banner'], names)
    const hcn = colExpr('u', ['has_change_nickname', 'hasChangedNickname'], names)
    const ver = colExpr('u', ['is_verified', 'isVerified'], names)
    const coinBal = colExpr('u', ['coin_balance', 'coinBalance'], names)
    const sexp = colExpr('u', ['streamer_exp'], names)
    const rexp = colExpr('u', ['rich_exp'], names)

    const accountExpr =
        names.has('account') && names.has('user_id')
            ? `COALESCE(NULLIF(TRIM(u.account), ''), u.user_id)`
            : uid

    const displayIdExpr =
        names.has('display_id') && names.has('user_id')
            ? `COALESCE(NULLIF(TRIM(u.display_id), ''), u.user_id)`
            : uid

    const createdBranches: string[] = []
    if (names.has('created_at')) {
        createdBranches.push(`WHEN COALESCE(u.created_at, 0) > 0 THEN u.created_at`)
    }
    if (names.has('createdAt')) {
        createdBranches.push(
            `WHEN typeof(u.createdAt)='text' AND CAST(u.createdAt AS INTEGER) > 1000000000000 THEN CAST(u.createdAt AS INTEGER) / 1000`
        )
        createdBranches.push(
            `WHEN typeof(u.createdAt)='text' AND CAST(u.createdAt AS INTEGER) > 0 THEN CAST(u.createdAt AS INTEGER)`
        )
    }
    createdBranches.push(`ELSE ${UNIX_NOW}`)
    const createdAtExpr = `CASE ${createdBranches.join(' ')} END`

    const updatedBranches: string[] = []
    if (names.has('updated_at')) {
        updatedBranches.push(`WHEN COALESCE(u.updated_at, 0) > 0 THEN u.updated_at`)
    }
    updatedBranches.push(`ELSE (${createdAtExpr})`)
    const updatedAtExpr = `CASE ${updatedBranches.join(' ')} END`

    try {
        await db.exec(`PRAGMA foreign_keys = OFF;`)
        await db.exec(`BEGIN;`)
        await db.exec(`
            CREATE TABLE users__rebuilt (
                user_id TEXT PRIMARY KEY,
                display_id TEXT NOT NULL UNIQUE,
                display_name TEXT NOT NULL,
                account TEXT NOT NULL UNIQUE,
                password_hash TEXT,
                email TEXT UNIQUE,
                avatar_url TEXT,
                banner_url TEXT,
                has_change_nickname INTEGER NOT NULL DEFAULT 0,
                is_verified INTEGER NOT NULL DEFAULT 0,
                coin_balance INTEGER NOT NULL DEFAULT 0,
                streamer_exp INTEGER NOT NULL DEFAULT 0,
                rich_exp INTEGER NOT NULL DEFAULT 0,
                created_at INTEGER NOT NULL,
                updated_at INTEGER NOT NULL
            );
        `)

        await db.exec(`
            INSERT INTO users__rebuilt (
                user_id, display_id, display_name, account, password_hash, email, avatar_url, banner_url,
                has_change_nickname, is_verified, coin_balance, streamer_exp, rich_exp, created_at, updated_at
            )
            SELECT
                ${uid} AS user_id,
                ${displayIdExpr} AS display_id,
                COALESCE(NULLIF(TRIM(${displayName}), ''), ${uid}) AS display_name,
                ${accountExpr} AS account,
                NULLIF(TRIM(${pwd}), '') AS password_hash,
                NULLIF(TRIM(${emailCol}), '') AS email,
                NULLIF(TRIM(${avatar}), '') AS avatar_url,
                NULLIF(TRIM(${banner}), '') AS banner_url,
                COALESCE(CAST(${hcn} AS INTEGER), 0) AS has_change_nickname,
                COALESCE(CAST(${ver} AS INTEGER), 0) AS is_verified,
                COALESCE(CAST(${coinBal} AS INTEGER), 0) AS coin_balance,
                COALESCE(CAST(${sexp} AS INTEGER), 0) AS streamer_exp,
                COALESCE(CAST(${rexp} AS INTEGER), 0) AS rich_exp,
                CAST((${createdAtExpr}) AS INTEGER) AS created_at,
                CAST((${updatedAtExpr}) AS INTEGER) AS updated_at
            FROM users u
            WHERE ${uid} IS NOT NULL AND TRIM(CAST(${uid} AS TEXT)) != '';
        `)

        await db.exec(`DROP TABLE users;`)
        await db.exec(`ALTER TABLE users__rebuilt RENAME TO users;`)
        await db.exec(`COMMIT;`)
    } catch (e) {
        try {
            await db.exec(`ROLLBACK;`)
        } catch {
            // ignore
        }
        throw e
    } finally {
        await db.exec(`PRAGMA foreign_keys = ON;`)
    }

    await ensureUsersIndexes(db)
}

const GROUPS_FINAL_COLUMNS = new Set([
    'group_id',
    'display_id',
    'name',
    'avatar_url',
    'banner_url',
    'is_verified',
    'is_can_application',
    'group_level_exp',
    'created_at',
    'updated_at',
])

const GROUPS_CREATE_TABLE_SQL = `
    CREATE TABLE groups (
        group_id TEXT PRIMARY KEY,
        display_id TEXT NOT NULL UNIQUE,
        name TEXT NOT NULL,
        avatar_url TEXT,
        banner_url TEXT,
        is_verified INTEGER NOT NULL DEFAULT 0,
        is_can_application INTEGER NOT NULL DEFAULT 1,
        group_level_exp INTEGER NOT NULL DEFAULT 0,
        created_at INTEGER NOT NULL DEFAULT (CAST(strftime('%s','now') AS INTEGER)),
        updated_at INTEGER NOT NULL DEFAULT (CAST(strftime('%s','now') AS INTEGER))
    );
`

const GROUPS_DISPLAY_ID_INDEX_SQL = `
    CREATE UNIQUE INDEX IF NOT EXISTS idx_groups_display_id ON groups(display_id);
`

function groupsDisplayIdColumnInOrder(cols: { name: string }[]): boolean {
    const names = cols.map((c) => c.name)
    const gidIdx = names.indexOf('group_id')
    const didIdx = names.indexOf('display_id')
    const nameIdx = names.indexOf('name')
    if (gidIdx < 0 || didIdx < 0 || nameIdx < 0) return false
    return gidIdx < didIdx && didIdx < nameIdx
}

function groupsCanApplicationColumnInOrder(cols: { name: string; cid: number }[]): boolean {
    const names = cols.map((c) => c.name)
    const verifiedIdx = names.indexOf('is_verified')
    const canIdx = names.indexOf('is_can_application')
    const gexpIdx = names.indexOf('group_level_exp')
    if (canIdx < 0) return false
    if (verifiedIdx < 0 || gexpIdx < 0) return false
    return verifiedIdx < canIdx && canIdx < gexpIdx
}

function groupsBannerColumnInOrder(cols: { name: string; cid: number }[]): boolean {
    const names = cols.map((c) => c.name)
    const avatarIdx = names.indexOf('avatar_url')
    const bannerIdx = names.indexOf('banner_url')
    const verifiedIdx = names.indexOf('is_verified')
    if (bannerIdx < 0) return false
    if (avatarIdx < 0 || verifiedIdx < 0) return false
    return avatarIdx < bannerIdx && bannerIdx < verifiedIdx
}

/** 若仍存在這些欄位名，代表尚未完成遷移（避免僅用欄位數判斷而誤判） */
const GROUPS_LEGACY_COLUMN_NAMES = new Set([
    'id',
    'avatar',
    'description',
    'artistsCount',
    'membersCount',
    'createdAt',
    'updatedAt',
])

function groupsTableNeedsRebuild(cols: { name: string; cid: number }[]): boolean {
    const names = new Set(cols.map((c) => c.name))
    if (![...GROUPS_FINAL_COLUMNS].every((c) => names.has(c))) return true
    if (names.size !== GROUPS_FINAL_COLUMNS.size) return true
    for (const legacy of GROUPS_LEGACY_COLUMN_NAMES) {
        if (names.has(legacy)) return true
    }
    if (!groupsDisplayIdColumnInOrder(cols)) return true
    if (!groupsBannerColumnInOrder(cols)) return true
    if (!groupsCanApplicationColumnInOrder(cols)) return true
    return false
}

const USER_GROUPS_FINAL_COLUMNS = new Set([
    'user_id',
    'group_id',
    'role',
    'contribution',
    'join_at',
])
const USER_GROUPS_LEGACY_COLUMN_NAMES = new Set(['userId', 'groupId', 'joinedAt'])

function userGroupsTableNeedsRebuild(names: Set<string>): boolean {
    if (![...USER_GROUPS_FINAL_COLUMNS].every((c) => names.has(c))) return true
    if (names.size !== USER_GROUPS_FINAL_COLUMNS.size) return true
    for (const legacy of USER_GROUPS_LEGACY_COLUMN_NAMES) {
        if (names.has(legacy)) return true
    }
    return false
}

/**
 * 將 user_groups 重建為 user_id / group_id / role / contribution / join_at（Unix 秒）。
 * 呼叫端須已 PRAGMA foreign_keys=OFF 並在交易內（或自行處理）。
 */
async function rebuildUserGroupsToFinalSchema(db: Db) {
    const ugPragma = await db.all<{ name: string }>(`PRAGMA table_info(user_groups);`)
    if (!ugPragma?.length) return

    const ugNames = new Set(ugPragma.map((c) => c.name))
    const uid = colExpr('ug', ['user_id', 'userId'], ugNames)
    const gid = colExpr('ug', ['group_id', 'groupId'], ugNames)
    const roleCol = colExpr('ug', ['role'], ugNames)

    const joinBranches: string[] = []
    if (ugNames.has('join_at')) {
        joinBranches.push(
            `WHEN CAST(ug.join_at AS INTEGER) > 1000000000000 THEN CAST(ug.join_at AS INTEGER) / 1000`
        )
        joinBranches.push(`WHEN CAST(ug.join_at AS INTEGER) > 0 THEN CAST(ug.join_at AS INTEGER)`)
    }
    if (ugNames.has('joinedAt')) {
        joinBranches.push(
            `WHEN CAST(ug.joinedAt AS INTEGER) > 1000000000000 THEN CAST(ug.joinedAt AS INTEGER) / 1000`
        )
        joinBranches.push(`WHEN CAST(ug.joinedAt AS INTEGER) > 0 THEN CAST(ug.joinedAt AS INTEGER)`)
        joinBranches.push(
            `WHEN ug.joinedAt IS NOT NULL AND TRIM(CAST(ug.joinedAt AS TEXT)) != '' AND CAST(strftime('%s', ug.joinedAt) AS INTEGER) > 0 THEN CAST(strftime('%s', ug.joinedAt) AS INTEGER)`
        )
    }
    const joinAtExpr =
        joinBranches.length > 0 ? `CASE ${joinBranches.join(' ')} ELSE ${UNIX_NOW} END` : UNIX_NOW

    const contributionExpr = ugNames.has('contribution')
        ? `COALESCE(CAST(ug.contribution AS INTEGER), 0)`
        : '0'

    await db.exec(`DROP TABLE IF EXISTS user_groups__rebuilt;`)
    await db.exec(`
        CREATE TABLE user_groups__rebuilt (
            user_id TEXT NOT NULL,
            group_id TEXT NOT NULL,
            role TEXT NOT NULL DEFAULT 'member',
            contribution INTEGER NOT NULL DEFAULT 0,
            join_at INTEGER NOT NULL DEFAULT (${UNIX_NOW}),
            PRIMARY KEY (user_id, group_id),
            FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE,
            FOREIGN KEY(group_id) REFERENCES groups(group_id) ON DELETE CASCADE
        );
    `)
    await db.exec(`
        INSERT INTO user_groups__rebuilt (user_id, group_id, role, contribution, join_at)
        SELECT
            ${uid} AS user_id,
            ${gid} AS group_id,
            COALESCE(NULLIF(TRIM(CAST(${roleCol} AS TEXT)), ''), 'member') AS role,
            CAST((${contributionExpr}) AS INTEGER) AS contribution,
            CAST((${joinAtExpr}) AS INTEGER) AS join_at
        FROM user_groups ug
        WHERE ${uid} IS NOT NULL AND TRIM(CAST(${uid} AS TEXT)) != ''
          AND ${gid} IS NOT NULL AND TRIM(CAST(${gid} AS TEXT)) != '';
    `)
    await db.exec(`DROP TABLE user_groups;`)
    await db.exec(`ALTER TABLE user_groups__rebuilt RENAME TO user_groups;`)
}

/** 僅在 groups 已為最終結構、但 user_groups 仍為舊欄位時補遷移。 */
export async function migrateUserGroupsTableSchema(db: Db) {
    const has = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'user_groups' LIMIT 1`
    )
    if (!has?.name) return

    const cols = await db.all<{ name: string }>(`PRAGMA table_info(user_groups);`)
    if (!cols?.length) return
    const names = new Set(cols.map((c) => c.name))
    if (!userGroupsTableNeedsRebuild(names)) return

    console.log('[gp-live] migrateUserGroupsTableSchema: 重建 user_groups（snake_case + join_at 秒）…')

    await db.exec(`PRAGMA foreign_keys = OFF;`)
    await db.exec(`BEGIN;`)
    try {
        await rebuildUserGroupsToFinalSchema(db)
        await db.exec(`COMMIT;`)
        console.log('[gp-live] migrateUserGroupsTableSchema: 完成')
    } catch (e) {
        try {
            await db.exec(`ROLLBACK;`)
        } catch {
            // ignore
        }
        throw e
    } finally {
        await db.exec(`PRAGMA foreign_keys = ON;`)
    }
}

/**
 * 舊版遷移建立的 user_groups 可能 join_at 為 NOT NULL 卻無 DEFAULT（與 db.ts 初始建表不一致），
 * 在圖形工具「新增列」或 INSERT 未帶 join_at 時會報錯。已符合最終欄位時補建 DEFAULT。
 */
export async function migrateUserGroupsJoinAtDefaultColumn(db: Db) {
    const has = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'user_groups' LIMIT 1`
    )
    if (!has?.name) return

    const cols = await db.all<{ name: string; dflt_value: string | null }>(`PRAGMA table_info(user_groups);`)
    if (!cols?.length) return

    const names = new Set(cols.map((c) => c.name))
    if (userGroupsTableNeedsRebuild(names)) return

    const joinRow = cols.find((c) => c.name === 'join_at')
    if (!joinRow) return

    if (joinRow.dflt_value != null && String(joinRow.dflt_value).trim() !== '') return

    console.log('[gp-live] migrateUserGroupsJoinAtDefaultColumn: 為 user_groups.join_at 補上 DEFAULT …')

    await db.exec(`PRAGMA foreign_keys = OFF;`)
    await db.exec(`BEGIN;`)
    try {
        await db.exec(`DROP TABLE IF EXISTS user_groups__dflt;`)
        await db.exec(`
            CREATE TABLE user_groups__dflt (
                user_id TEXT NOT NULL,
                group_id TEXT NOT NULL,
                role TEXT NOT NULL DEFAULT 'member',
                contribution INTEGER NOT NULL DEFAULT 0,
                join_at INTEGER NOT NULL DEFAULT (${UNIX_NOW}),
                PRIMARY KEY (user_id, group_id),
                FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE,
                FOREIGN KEY(group_id) REFERENCES groups(group_id) ON DELETE CASCADE
            );
        `)
        await db.exec(`
            INSERT INTO user_groups__dflt (user_id, group_id, role, contribution, join_at)
            SELECT
                user_id,
                group_id,
                role,
                COALESCE(CAST(contribution AS INTEGER), 0),
                CASE
                    WHEN join_at IS NOT NULL AND CAST(join_at AS INTEGER) > 0 THEN CAST(join_at AS INTEGER)
                    ELSE ${UNIX_NOW}
                END
            FROM user_groups;
        `)
        await db.exec(`DROP TABLE user_groups;`)
        await db.exec(`ALTER TABLE user_groups__dflt RENAME TO user_groups;`)
        await db.exec(`COMMIT;`)
        console.log('[gp-live] migrateUserGroupsJoinAtDefaultColumn: 完成')
    } catch (e) {
        try {
            await db.exec(`ROLLBACK;`)
        } catch {
            // ignore
        }
        throw e
    } finally {
        await db.exec(`PRAGMA foreign_keys = ON;`)
    }
}

/** 申請加入家族：group_id、user_id、application_at（Unix 秒） */
export async function migrateGroupApplyTableSchema(db: Db) {
    await db.exec(`
        CREATE TABLE IF NOT EXISTS group_apply (
            group_id TEXT NOT NULL,
            user_id TEXT NOT NULL,
            application_at INTEGER NOT NULL DEFAULT (${UNIX_NOW}),
            PRIMARY KEY (user_id, group_id),
            FOREIGN KEY(group_id) REFERENCES groups(group_id) ON DELETE CASCADE,
            FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE
        );
    `)
}

/**
 * 將 groups 整理為固定欄位（Unix 秒時間戳）；並重建 user_groups 外鍵改為 references groups(group_id)。
 */
export async function migrateGroupsTableSchema(db: Db) {
    const hasGroups = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'groups' LIMIT 1`
    )
    const cols = await db.all<{ name: string; cid: number }>(`PRAGMA table_info(groups);`)

    if (!hasGroups?.name || !cols?.length) {
        await db.exec(
            GROUPS_CREATE_TABLE_SQL.replace(/^(\s*)CREATE TABLE groups /m, '$1CREATE TABLE IF NOT EXISTS groups ')
        )
        await db.exec(GROUPS_DISPLAY_ID_INDEX_SQL)
        return
    }

    if (!groupsTableNeedsRebuild(cols)) return
    const names = new Set(cols.map((c) => c.name))

    console.log('[gp-live] migrateGroupsTableSchema: 偵測到舊版或非標準 groups，開始重建…')

    const gid = colExpr('g', ['group_id', 'id'], names)
    const avatarCol = colExpr('g', ['avatar_url', 'avatar'], names)
    const bannerCol = names.has('banner_url')
        ? `NULLIF(TRIM(g.banner_url), '')`
        : 'NULL'
    const ver = colExpr('g', ['is_verified', 'isVerified'], names)
    const canApp = names.has('is_can_application')
        ? `COALESCE(CAST(g.is_can_application AS INTEGER), 1)`
        : '1'
    const gexp = colExpr('g', ['group_level_exp', 'groupLevelExp'], names)

    const createdBranches: string[] = []
    if (names.has('created_at')) {
        createdBranches.push(`WHEN COALESCE(g.created_at, 0) > 0 THEN g.created_at`)
    }
    if (names.has('createdAt')) {
        createdBranches.push(
            `WHEN typeof(g.createdAt)='text' AND CAST(g.createdAt AS INTEGER) > 1000000000000 THEN CAST(g.createdAt AS INTEGER) / 1000`
        )
        createdBranches.push(
            `WHEN typeof(g.createdAt)='text' AND CAST(g.createdAt AS INTEGER) > 0 THEN CAST(g.createdAt AS INTEGER)`
        )
    }
    const createdAtExpr =
        createdBranches.length > 0
            ? `CASE ${createdBranches.join(' ')} ELSE ${UNIX_NOW} END`
            : UNIX_NOW

    const updatedBranches: string[] = []
    if (names.has('updated_at')) {
        updatedBranches.push(`WHEN COALESCE(g.updated_at, 0) > 0 THEN g.updated_at`)
    }
    if (names.has('updatedAt')) {
        updatedBranches.push(
            `WHEN typeof(g.updatedAt)='text' AND CAST(g.updatedAt AS INTEGER) > 1000000000000 THEN CAST(g.updatedAt AS INTEGER) / 1000`
        )
        updatedBranches.push(
            `WHEN typeof(g.updatedAt)='text' AND CAST(g.updatedAt AS INTEGER) > 0 THEN CAST(g.updatedAt AS INTEGER)`
        )
    }
    const updatedAtExpr =
        updatedBranches.length > 0
            ? `CASE ${updatedBranches.join(' ')} ELSE (${createdAtExpr}) END`
            : `(${createdAtExpr})`

    await db.exec(`DROP TABLE IF EXISTS groups__rebuilt;`)
    await db.exec(`DROP TABLE IF EXISTS user_groups__rebuilt;`)

    await db.exec(`PRAGMA foreign_keys = OFF;`)
    await db.exec(`BEGIN;`)
    try {
        await db.exec(GROUPS_CREATE_TABLE_SQL.replace('CREATE TABLE groups', 'CREATE TABLE groups__rebuilt'))

        const displayIdCol = names.has('display_id')
            ? `NULLIF(TRIM(CAST(g.display_id AS TEXT)), '')`
            : null
        const displayIdExpr = names.has('display_id')
            ? `COALESCE(${displayIdCol}, CAST(${gid} AS TEXT))`
            : `CAST(${gid} AS TEXT)`

        await db.exec(`
            INSERT INTO groups__rebuilt (
                group_id, display_id, name, avatar_url, banner_url, is_verified, is_can_application, group_level_exp, created_at, updated_at
            )
            SELECT
                ${gid} AS group_id,
                ${displayIdExpr} AS display_id,
                COALESCE(NULLIF(TRIM(g.name), ''), ${gid}) AS name,
                NULLIF(TRIM(${avatarCol}), '') AS avatar_url,
                ${bannerCol} AS banner_url,
                COALESCE(CAST(${ver} AS INTEGER), 0) AS is_verified,
                ${canApp} AS is_can_application,
                COALESCE(CAST(${gexp} AS INTEGER), 0) AS group_level_exp,
                CAST((${createdAtExpr}) AS INTEGER) AS created_at,
                CAST((${updatedAtExpr}) AS INTEGER) AS updated_at
            FROM groups g
            WHERE ${gid} IS NOT NULL AND TRIM(CAST(${gid} AS TEXT)) != '';
        `)

        await db.exec(`DROP TABLE groups;`)
        await db.exec(`ALTER TABLE groups__rebuilt RENAME TO groups;`)
        await db.exec(GROUPS_DISPLAY_ID_INDEX_SQL)

        await rebuildUserGroupsToFinalSchema(db)

        await db.exec(`COMMIT;`)
        console.log('[gp-live] migrateGroupsTableSchema: groups / user_groups 已更新為新結構')
    } catch (e) {
        try {
            await db.exec(`ROLLBACK;`)
        } catch {
            // ignore
        }
        throw e
    } finally {
        await db.exec(`PRAGMA foreign_keys = ON;`)
    }
}

const CONFIG_ROOM_GIFTS_FINAL_COLUMNS = new Set([
    'gift_id',
    'name',
    'is_lottery',
    'lottery_url',
    'amount',
    'created_at',
])

const CONFIG_ROOM_GIFTS_LEGACY_COLUMN_NAMES = new Set([
    'id',
    'image',
    'category',
    'price',
    'createdAt',
])

function configRoomGiftsTableNeedsRebuild(names: Set<string>): boolean {
    if (![...CONFIG_ROOM_GIFTS_FINAL_COLUMNS].every((c) => names.has(c))) return true
    if (names.size !== CONFIG_ROOM_GIFTS_FINAL_COLUMNS.size) return true
    for (const legacy of CONFIG_ROOM_GIFTS_LEGACY_COLUMN_NAMES) {
        if (names.has(legacy)) return true
    }
    return false
}

/**
 * gifts 表改名為 config_room_gifts，欄位改為 gift_id / is_lottery / lottery_url / amount / created_at（Unix 秒）。
 */
export async function migrateConfigRoomGiftsTableSchema(db: Db) {
    const hasGifts = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'gifts' LIMIT 1`
    )
    const hasConfig = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'config_room_gifts' LIMIT 1`
    )

    const pragmaConfig = hasConfig?.name
        ? await db.all<{ name: string }>(`PRAGMA table_info(config_room_gifts);`)
        : []
    const configNames = new Set(pragmaConfig.map((c) => c.name))
    const configOk = Boolean(hasConfig?.name) && !configRoomGiftsTableNeedsRebuild(configNames)

    if (!hasGifts?.name && configOk) {
        await db.exec(`DROP TABLE IF EXISTS gifts;`)
        return
    }

    if (!hasGifts?.name && !hasConfig?.name) {
        await db.exec(`
            CREATE TABLE IF NOT EXISTS config_room_gifts (
                gift_id TEXT PRIMARY KEY,
                name TEXT NOT NULL,
                is_lottery INTEGER NOT NULL DEFAULT 0,
                lottery_url TEXT,
                amount INTEGER NOT NULL DEFAULT 0,
                created_at INTEGER NOT NULL DEFAULT (${UNIX_NOW})
            );
        `)
        return
    }

    const fromTable: 'gifts' | 'config_room_gifts' | null = hasGifts?.name
        ? 'gifts'
        : hasConfig?.name && configRoomGiftsTableNeedsRebuild(configNames)
          ? 'config_room_gifts'
          : null

    if (!fromTable) return

    const cols = await db.all<{ name: string }>(
        `PRAGMA table_info(${fromTable === 'gifts' ? `'gifts'` : `'config_room_gifts'`});`
    )
    const names = new Set(cols.map((c) => c.name))

    const giftId = colExpr('g', ['gift_id', 'id'], names)
    const nameCol = colExpr('g', ['name'], names)
    const lotteryUrlCol = colExpr('g', ['lottery_url', 'image'], names)
    const isLotteryCol = colExpr('g', ['is_lottery', 'isLottery'], names)
    const amountCol = colExpr('g', ['amount', 'price'], names)

    const createdBranches: string[] = []
    if (names.has('created_at')) {
        createdBranches.push(`WHEN COALESCE(g.created_at, 0) > 0 THEN g.created_at`)
    }
    if (names.has('createdAt')) {
        createdBranches.push(
            `WHEN typeof(g.createdAt)='text' AND CAST(g.createdAt AS INTEGER) > 1000000000000 THEN CAST(g.createdAt AS INTEGER) / 1000`
        )
        createdBranches.push(
            `WHEN typeof(g.createdAt)='text' AND CAST(g.createdAt AS INTEGER) > 0 THEN CAST(g.createdAt AS INTEGER)`
        )
    }
    const createdAtExpr =
        createdBranches.length > 0 ? `CASE ${createdBranches.join(' ')} ELSE ${UNIX_NOW} END` : UNIX_NOW

    console.log(`[gp-live] migrateConfigRoomGiftsTableSchema: 重建 config_room_gifts（來源: ${fromTable}）…`)

    await db.exec(`DROP TABLE IF EXISTS config_room_gifts__rebuilt;`)
    await db.exec(`PRAGMA foreign_keys = OFF;`)
    await db.exec(`BEGIN;`)
    try {
        await db.exec(`
            CREATE TABLE config_room_gifts__rebuilt (
                gift_id TEXT PRIMARY KEY,
                name TEXT NOT NULL,
                is_lottery INTEGER NOT NULL DEFAULT 0,
                lottery_url TEXT,
                amount INTEGER NOT NULL DEFAULT 0,
                created_at INTEGER NOT NULL
            );
        `)

        const fromIdent = fromTable === 'gifts' ? `"gifts"` : `"config_room_gifts"`
        await db.exec(`
            INSERT INTO config_room_gifts__rebuilt (
                gift_id, name, is_lottery, lottery_url, amount, created_at
            )
            SELECT
                ${giftId} AS gift_id,
                COALESCE(NULLIF(TRIM(${nameCol}), ''), ${giftId}) AS name,
                COALESCE(CAST(${isLotteryCol} AS INTEGER), 0) AS is_lottery,
                NULLIF(TRIM(${lotteryUrlCol}), '') AS lottery_url,
                COALESCE(CAST(${amountCol} AS INTEGER), 0) AS amount,
                CAST((${createdAtExpr}) AS INTEGER) AS created_at
            FROM ${fromIdent} g
            WHERE ${giftId} IS NOT NULL AND TRIM(CAST(${giftId} AS TEXT)) != '';
        `)

        await db.exec(`DROP TABLE IF EXISTS gifts;`)
        await db.exec(`DROP TABLE IF EXISTS config_room_gifts;`)
        await db.exec(`ALTER TABLE config_room_gifts__rebuilt RENAME TO config_room_gifts;`)
        await db.exec(`COMMIT;`)
        console.log('[gp-live] migrateConfigRoomGiftsTableSchema: 完成')
    } catch (e) {
        try {
            await db.exec(`ROLLBACK;`)
        } catch {
            // ignore
        }
        throw e
    } finally {
        await db.exec(`PRAGMA foreign_keys = ON;`)
    }
}

const RANK_DETAIL_FINAL_COLUMNS = new Set([
    'rank_id',
    'user_id',
    'target_id',
    'gift_id',
    'gift_count',
    'created_at',
])

const RANK_LEGACY_COLUMN_NAMES = new Set([
    'id',
    'rank',
    'userId',
    'name',
    'avatar',
    'giftAmount',
    'giftCount',
    'level',
    'period',
    'createdAt',
    'contributorName',
    'contributorId',
])

function rankDetailTableIsFinal(names: Set<string>): boolean {
    if (![...RANK_DETAIL_FINAL_COLUMNS].every((c) => names.has(c))) return false
    if (names.size !== RANK_DETAIL_FINAL_COLUMNS.size) return false
    for (const legacy of RANK_LEGACY_COLUMN_NAMES) {
        if (names.has(legacy)) return false
    }
    return true
}

function rankCreatedAtUnixExpr(alias: string, names: Set<string>): string {
    const branches: string[] = []
    if (names.has('created_at')) {
        branches.push(
            `WHEN CAST(${alias}.created_at AS INTEGER) > 1000000000000 THEN CAST(${alias}.created_at AS INTEGER) / 1000`
        )
        branches.push(`WHEN CAST(${alias}.created_at AS INTEGER) > 0 THEN CAST(${alias}.created_at AS INTEGER)`)
    }
    if (names.has('createdAt')) {
        branches.push(
            `WHEN CAST(${alias}.createdAt AS INTEGER) > 1000000000000 THEN CAST(${alias}.createdAt AS INTEGER) / 1000`
        )
        branches.push(`WHEN CAST(${alias}.createdAt AS INTEGER) > 0 THEN CAST(${alias}.createdAt AS INTEGER)`)
        branches.push(
            `WHEN ${alias}.createdAt IS NOT NULL AND TRIM(CAST(${alias}.createdAt AS TEXT)) != '' AND CAST(strftime('%s', ${alias}.createdAt) AS INTEGER) > 0 THEN CAST(strftime('%s', ${alias}.createdAt) AS INTEGER)`
        )
    }
    return branches.length > 0 ? `CASE ${branches.join(' ')} ELSE ${UNIX_NOW} END` : UNIX_NOW
}

/**
 * rank_gift / rank_star / rank_rich / rank_group 整理為 rank_stream + rank_rich 明細表。
 * rank_group 不再落表，排行榜 API 由 rank_stream/rank_rich + user_groups + groups 即時計算。
 */
export async function migrateRankTablesSchema(db: Db) {
    const hasRankGift = await tableExists(db, 'rank_gift')
    const hasRankStar = await tableExists(db, 'rank_star')
    const hasRankGroup = await tableExists(db, 'rank_group')
    const hasRankStream = await tableExists(db, 'rank_stream')
    const hasRankRich = await tableExists(db, 'rank_rich')

    const streamCols = hasRankStream ? await db.all<{ name: string }>(`PRAGMA table_info(rank_stream);`) : []
    const richCols = hasRankRich ? await db.all<{ name: string }>(`PRAGMA table_info(rank_rich);`) : []
    const streamOk = hasRankStream && rankDetailTableIsFinal(new Set(streamCols.map((c) => c.name)))
    const richOk = hasRankRich && rankDetailTableIsFinal(new Set(richCols.map((c) => c.name)))

    if (!hasRankGift && !hasRankStar && !hasRankGroup && streamOk && richOk) return

    console.log('[gp-live] migrateRankTablesSchema: 整合排行榜資料表為 rank_stream / rank_rich…')

    type RankSource = { tableName: 'rank_stream' | 'rank_gift' | 'rank_star' | 'rank_rich'; names: Set<string> }
    const streamSources: RankSource[] = []
    const richSources: RankSource[] = []

    if (hasRankStream) streamSources.push({ tableName: 'rank_stream', names: new Set(streamCols.map((c) => c.name)) })
    if (hasRankGift) {
        const cols = await db.all<{ name: string }>(`PRAGMA table_info(rank_gift);`)
        streamSources.push({ tableName: 'rank_gift', names: new Set(cols.map((c) => c.name)) })
    }
    if (hasRankStar) {
        const cols = await db.all<{ name: string }>(`PRAGMA table_info(rank_star);`)
        streamSources.push({ tableName: 'rank_star', names: new Set(cols.map((c) => c.name)) })
    }
    if (hasRankRich) richSources.push({ tableName: 'rank_rich', names: new Set(richCols.map((c) => c.name)) })

    await db.exec(`DROP TABLE IF EXISTS rank_stream__rebuilt;`)
    await db.exec(`DROP TABLE IF EXISTS rank_rich__rebuilt;`)
    await db.exec(`PRAGMA foreign_keys = OFF;`)
    await db.exec(`BEGIN;`)
    try {
        await db.exec(`
            CREATE TABLE rank_stream__rebuilt (
                rank_id TEXT PRIMARY KEY,
                target_id TEXT,
                user_id TEXT NOT NULL,
                gift_id TEXT,
                gift_count INTEGER NOT NULL DEFAULT 0,
                created_at INTEGER NOT NULL DEFAULT (${UNIX_NOW}),
                FOREIGN KEY(target_id) REFERENCES users(user_id) ON DELETE SET NULL,
                FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE,
                FOREIGN KEY(gift_id) REFERENCES config_room_gifts(gift_id) ON DELETE SET NULL
            );

            CREATE TABLE rank_rich__rebuilt (
                rank_id TEXT PRIMARY KEY,
                user_id TEXT NOT NULL,
                target_id TEXT,
                gift_id TEXT,
                gift_count INTEGER NOT NULL DEFAULT 0,
                created_at INTEGER NOT NULL DEFAULT (${UNIX_NOW}),
                FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE,
                FOREIGN KEY(target_id) REFERENCES users(user_id) ON DELETE SET NULL,
                FOREIGN KEY(gift_id) REFERENCES config_room_gifts(gift_id) ON DELETE SET NULL
            );
        `)

        for (const source of streamSources) {
            const names = source.names
            const idExpr = names.has('rank_id')
                ? 'r.rank_id'
                : names.has('id')
                  ? `'${source.tableName}:' || CAST(r.id AS TEXT)`
                  : `'${source.tableName}:' || CAST(r.rowid AS TEXT)`
            const targetId =
                source.tableName === 'rank_star'
                    ? colExpr('r', ['target_id', 'targetId', 'contributorId'], names)
                    : colExpr('r', ['target_id', 'targetId'], names)
            const userId = colExpr('r', ['user_id', 'userId'], names)
            const giftId = colExpr('r', ['gift_id', 'giftId'], names)
            const giftCount = colExpr('r', ['gift_count', 'giftCount', 'giftAmount'], names)
            const createdAt = rankCreatedAtUnixExpr('r', names)

            if (userId === 'NULL') continue

            await db.exec(`
                INSERT OR IGNORE INTO rank_stream__rebuilt (
                    rank_id, target_id, user_id, gift_id, gift_count, created_at
                )
                SELECT
                    CAST(${idExpr} AS TEXT) AS rank_id,
                    NULLIF(TRIM(${targetId}), '') AS target_id,
                    CAST(${userId} AS TEXT) AS user_id,
                    NULLIF(TRIM(${giftId}), '') AS gift_id,
                    COALESCE(CAST(${giftCount} AS INTEGER), 0) AS gift_count,
                    CAST((${createdAt}) AS INTEGER) AS created_at
                FROM "${source.tableName}" r
                WHERE ${userId} IS NOT NULL AND TRIM(CAST(${userId} AS TEXT)) != '';
            `)
        }

        for (const source of richSources) {
            const names = source.names
            const idExpr = names.has('rank_id')
                ? 'r.rank_id'
                : names.has('id')
                  ? `'${source.tableName}:' || CAST(r.id AS TEXT)`
                  : `'${source.tableName}:' || CAST(r.rowid AS TEXT)`
            const userId = colExpr('r', ['user_id', 'userId'], names)
            const targetId = colExpr('r', ['target_id', 'targetId'], names)
            const giftId = colExpr('r', ['gift_id', 'giftId'], names)
            const giftCount = colExpr('r', ['gift_count', 'giftCount', 'giftAmount'], names)
            const createdAt = rankCreatedAtUnixExpr('r', names)

            if (userId === 'NULL') continue

            await db.exec(`
                INSERT OR IGNORE INTO rank_rich__rebuilt (
                    rank_id, user_id, target_id, gift_id, gift_count, created_at
                )
                SELECT
                    CAST(${idExpr} AS TEXT) AS rank_id,
                    CAST(${userId} AS TEXT) AS user_id,
                    NULLIF(TRIM(${targetId}), '') AS target_id,
                    NULLIF(TRIM(${giftId}), '') AS gift_id,
                    COALESCE(CAST(${giftCount} AS INTEGER), 0) AS gift_count,
                    CAST((${createdAt}) AS INTEGER) AS created_at
                FROM "${source.tableName}" r
                WHERE ${userId} IS NOT NULL AND TRIM(CAST(${userId} AS TEXT)) != '';
            `)
        }

        await db.exec(`DROP TABLE IF EXISTS rank_gift;`)
        await db.exec(`DROP TABLE IF EXISTS rank_star;`)
        await db.exec(`DROP TABLE IF EXISTS rank_group;`)
        await db.exec(`DROP TABLE IF EXISTS rank_stream;`)
        await db.exec(`DROP TABLE IF EXISTS rank_rich;`)
        await db.exec(`ALTER TABLE rank_stream__rebuilt RENAME TO rank_stream;`)
        await db.exec(`ALTER TABLE rank_rich__rebuilt RENAME TO rank_rich;`)
        await db.exec(`COMMIT;`)
        console.log('[gp-live] migrateRankTablesSchema: 完成')
    } catch (e) {
        try {
            await db.exec(`ROLLBACK;`)
        } catch {
            // ignore
        }
        throw e
    } finally {
        await db.exec(`PRAGMA foreign_keys = ON;`)
    }
}

const INDEX_EVENT_BANNERS_FINAL_COLUMNS = new Set([
    'event_id',
    'title',
    'subtitle',
    'banner_url',
    'redirect_url',
    'is_active',
    'created_at',
    'updated_at',
])

const INDEX_EVENT_BANNERS_LEGACY_COLUMN_NAMES = new Set([
    'id',
    'date',
    'image',
    'badge',
    'url',
    'orderIndex',
    'isActive',
    'createdAt',
    'createdAtMs',
])

function indexEventBannersTableNeedsRebuild(names: Set<string>): boolean {
    if (![...INDEX_EVENT_BANNERS_FINAL_COLUMNS].every((c) => names.has(c))) return true
    if (names.size !== INDEX_EVENT_BANNERS_FINAL_COLUMNS.size) return true
    for (const legacy of INDEX_EVENT_BANNERS_LEGACY_COLUMN_NAMES) {
        if (names.has(legacy)) return true
    }
    return false
}

/**
 * event_banners 改名為 index_event_banners，欄位改為 snake_case + Unix 秒時間戳。
 */
export async function migrateIndexEventBannersTableSchema(db: Db) {
    const hasOld = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'event_banners' LIMIT 1`
    )
    const hasIndex = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'index_event_banners' LIMIT 1`
    )

    const pragmaIndex = hasIndex?.name
        ? await db.all<{ name: string }>(`PRAGMA table_info(index_event_banners);`)
        : []
    const indexNames = new Set(pragmaIndex.map((c) => c.name))
    const indexOk = Boolean(hasIndex?.name) && !indexEventBannersTableNeedsRebuild(indexNames)

    if (!hasOld?.name && indexOk) {
        await db.exec(`DROP TABLE IF EXISTS event_banners;`)
        return
    }

    if (!hasOld?.name && !hasIndex?.name) {
        await db.exec(`
            CREATE TABLE IF NOT EXISTS index_event_banners (
                event_id TEXT PRIMARY KEY,
                title TEXT NOT NULL,
                subtitle TEXT,
                banner_url TEXT,
                redirect_url TEXT,
                is_active INTEGER NOT NULL DEFAULT 1,
                created_at INTEGER NOT NULL DEFAULT (${UNIX_NOW}),
                updated_at INTEGER NOT NULL DEFAULT (${UNIX_NOW})
            );
        `)
        return
    }

    const fromTable: 'event_banners' | 'index_event_banners' | null = hasOld?.name
        ? 'event_banners'
        : hasIndex?.name && indexEventBannersTableNeedsRebuild(indexNames)
          ? 'index_event_banners'
          : null

    if (!fromTable) return

    const cols = await db.all<{ name: string }>(
        `PRAGMA table_info(${fromTable === 'event_banners' ? `'event_banners'` : `'index_event_banners'`});`
    )
    const names = new Set(cols.map((c) => c.name))

    const eventId = colExpr('g', ['event_id', 'id'], names)
    const titleCol = colExpr('g', ['title'], names)
    const subtitleCol = colExpr('g', ['subtitle'], names)
    const bannerUrlCol = colExpr('g', ['banner_url', 'image'], names)
    const redirectUrlCol = colExpr('g', ['redirect_url', 'url'], names)
    const isActiveCol = colExpr('g', ['is_active', 'isActive'], names)

    const createdBranches: string[] = []
    if (names.has('created_at')) {
        createdBranches.push(`WHEN COALESCE(g.created_at, 0) > 0 THEN g.created_at`)
    }
    if (names.has('createdAtMs')) {
        createdBranches.push(
            `WHEN typeof(g.createdAtMs)='text' AND CAST(g.createdAtMs AS INTEGER) > 1000000000000 THEN CAST(g.createdAtMs AS INTEGER) / 1000`
        )
        createdBranches.push(
            `WHEN typeof(g.createdAtMs)='text' AND CAST(g.createdAtMs AS INTEGER) > 0 THEN CAST(g.createdAtMs AS INTEGER)`
        )
    }
    if (names.has('createdAt')) {
        createdBranches.push(
            `WHEN typeof(g.createdAt)='text' AND g.createdAt GLOB '____-__-__*' THEN CAST(strftime('%s', g.createdAt) AS INTEGER)`
        )
        createdBranches.push(
            `WHEN typeof(g.createdAt)='text' AND CAST(g.createdAt AS INTEGER) > 1000000000000 THEN CAST(g.createdAt AS INTEGER) / 1000`
        )
        createdBranches.push(
            `WHEN typeof(g.createdAt)='text' AND CAST(g.createdAt AS INTEGER) > 0 THEN CAST(g.createdAt AS INTEGER)`
        )
    }
    const createdAtExpr =
        createdBranches.length > 0 ? `CASE ${createdBranches.join(' ')} ELSE ${UNIX_NOW} END` : UNIX_NOW

    const updatedBranches: string[] = []
    if (names.has('updated_at')) {
        updatedBranches.push(`WHEN COALESCE(g.updated_at, 0) > 0 THEN g.updated_at`)
    }
    if (names.has('updatedAt')) {
        updatedBranches.push(
            `WHEN typeof(g.updatedAt)='text' AND CAST(g.updatedAt AS INTEGER) > 1000000000000 THEN CAST(g.updatedAt AS INTEGER) / 1000`
        )
        updatedBranches.push(
            `WHEN typeof(g.updatedAt)='text' AND CAST(g.updatedAt AS INTEGER) > 0 THEN CAST(g.updatedAt AS INTEGER)`
        )
    }
    const updatedAtExpr =
        updatedBranches.length > 0
            ? `CASE ${updatedBranches.join(' ')} ELSE (${createdAtExpr}) END`
            : `(${createdAtExpr})`

    console.log(`[gp-live] migrateIndexEventBannersTableSchema: 重建 index_event_banners（來源: ${fromTable}）…`)

    await db.exec(`DROP TABLE IF EXISTS index_event_banners__rebuilt;`)
    await db.exec(`PRAGMA foreign_keys = OFF;`)
    await db.exec(`BEGIN;`)
    try {
        await db.exec(`
            CREATE TABLE index_event_banners__rebuilt (
                event_id TEXT PRIMARY KEY,
                title TEXT NOT NULL,
                subtitle TEXT,
                banner_url TEXT,
                redirect_url TEXT,
                is_active INTEGER NOT NULL DEFAULT 1,
                created_at INTEGER NOT NULL,
                updated_at INTEGER NOT NULL
            );
        `)

        const fromIdent = fromTable === 'event_banners' ? `"event_banners"` : `"index_event_banners"`
        await db.exec(`
            INSERT INTO index_event_banners__rebuilt (
                event_id, title, subtitle, banner_url, redirect_url, is_active, created_at, updated_at
            )
            SELECT
                ${eventId} AS event_id,
                COALESCE(NULLIF(TRIM(${titleCol}), ''), ${eventId}) AS title,
                NULLIF(TRIM(${subtitleCol}), '') AS subtitle,
                NULLIF(TRIM(${bannerUrlCol}), '') AS banner_url,
                NULLIF(TRIM(${redirectUrlCol}), '') AS redirect_url,
                COALESCE(CAST(${isActiveCol} AS INTEGER), 1) AS is_active,
                CAST((${createdAtExpr}) AS INTEGER) AS created_at,
                CAST((${updatedAtExpr}) AS INTEGER) AS updated_at
            FROM ${fromIdent} g
            WHERE ${eventId} IS NOT NULL AND TRIM(CAST(${eventId} AS TEXT)) != '';
        `)

        await db.exec(`DROP TABLE IF EXISTS event_banners;`)
        await db.exec(`DROP TABLE IF EXISTS index_event_banners;`)
        await db.exec(`ALTER TABLE index_event_banners__rebuilt RENAME TO index_event_banners;`)
        await db.exec(`COMMIT;`)
        console.log('[gp-live] migrateIndexEventBannersTableSchema: 完成')
    } catch (e) {
        try {
            await db.exec(`ROLLBACK;`)
        } catch {
            // ignore
        }
        throw e
    } finally {
        await db.exec(`PRAGMA foreign_keys = ON;`)
    }
}

const USER_ALLOWED_DEVICES_FINAL_COLUMNS = new Set([
    'device_id',
    'user_id',
    'session_id',
    'device_type',
    'device_name',
    'user_agent',
    'register_at',
    'created_at',
])

const USER_ALLOWED_DEVICES_LEGACY_COLUMN_NAMES = new Set([
    'id',
    'deviceId',
    'userId',
    'sessionId',
    'deviceType',
    'deviceName',
    'userAgent',
    'regDate',
    'createdAt',
    'created_att',
])

function userAllowedDevicesTableNeedsRebuild(names: Set<string>): boolean {
    if (![...USER_ALLOWED_DEVICES_FINAL_COLUMNS].every((c) => names.has(c))) return true
    if (names.size !== USER_ALLOWED_DEVICES_FINAL_COLUMNS.size) return true
    for (const legacy of USER_ALLOWED_DEVICES_LEGACY_COLUMN_NAMES) {
        if (names.has(legacy)) return true
    }
    return false
}

function allowedDeviceUnixExpr(alias: string, colNames: Set<string>, candidates: string[], fallback: string): string {
    const branches: string[] = []
    for (const col of candidates) {
        if (!colNames.has(col)) continue
        branches.push(
            `WHEN CAST(${alias}."${col}" AS INTEGER) > 1000000000000 THEN CAST(${alias}."${col}" AS INTEGER) / 1000`
        )
        branches.push(`WHEN CAST(${alias}."${col}" AS INTEGER) > 0 THEN CAST(${alias}."${col}" AS INTEGER)`)
        branches.push(
            `WHEN ${alias}."${col}" IS NOT NULL AND TRIM(CAST(${alias}."${col}" AS TEXT)) != '' AND CAST(strftime('%s', ${alias}."${col}") AS INTEGER) > 0 THEN CAST(strftime('%s', ${alias}."${col}") AS INTEGER)`
        )
    }
    return branches.length > 0 ? `CASE ${branches.join(' ')} ELSE ${fallback} END` : fallback
}

/**
 * allowed_devices / user_allowed_devieces → user_allowed_devices；欄位固定為 snake_case，時間為 Unix 秒。
 */
export async function migrateUserAllowedDevicesTableSchema(db: Db) {
    const hasOld = await tableExists(db, 'allowed_devices')
    const hasMisspelled = await tableExists(db, 'user_allowed_devieces')
    const hasNew = await tableExists(db, 'user_allowed_devices')

    const newCols = hasNew
        ? await db.all<{ name: string }>(`PRAGMA table_info(user_allowed_devices);`)
        : []
    const newNames = new Set(newCols.map((c) => c.name))
    const newOk = hasNew && !userAllowedDevicesTableNeedsRebuild(newNames)

    if (!hasOld && !hasMisspelled && newOk) {
        await db.exec(`DROP TABLE IF EXISTS allowed_devices;`)
        await db.exec(`DROP TABLE IF EXISTS user_allowed_devieces;`)
        return
    }

    console.log('[gp-live] migrateUserAllowedDevicesTableSchema: 重建 user_allowed_devices…')

    const sources: {
        tableName: 'user_allowed_devices' | 'user_allowed_devieces' | 'allowed_devices'
        alias: string
        names: Set<string>
    }[] = []
    if (hasNew) {
        sources.push({ tableName: 'user_allowed_devices', alias: 'd', names: newNames })
    }
    if (hasMisspelled) {
        const typoCols = await db.all<{ name: string }>(`PRAGMA table_info(user_allowed_devieces);`)
        sources.push({
            tableName: 'user_allowed_devieces',
            alias: 'd',
            names: new Set(typoCols.map((c) => c.name)),
        })
    }
    if (hasOld) {
        const oldCols = await db.all<{ name: string }>(`PRAGMA table_info(allowed_devices);`)
        sources.push({
            tableName: 'allowed_devices',
            alias: 'd',
            names: new Set(oldCols.map((c) => c.name)),
        })
    }

    await db.exec(`DROP TABLE IF EXISTS user_allowed_devices__rebuilt;`)
    await db.exec(`PRAGMA foreign_keys = OFF;`)
    await db.exec(`BEGIN;`)
    try {
        await db.exec(`
            CREATE TABLE user_allowed_devices__rebuilt (
                device_id TEXT PRIMARY KEY,
                user_id TEXT NOT NULL,
                session_id TEXT,
                device_type TEXT,
                device_name TEXT,
                user_agent TEXT,
                register_at INTEGER NOT NULL DEFAULT (${UNIX_NOW}),
                created_at INTEGER NOT NULL DEFAULT (${UNIX_NOW}),
                FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE
            );
        `)

        for (const source of sources) {
            const { alias, names, tableName } = source
            const deviceId = colExpr(alias, ['device_id', 'deviceId', 'id', 'session_id', 'sessionId'], names)
            const userId = colExpr(alias, ['user_id', 'userId'], names)
            const sessionId = colExpr(alias, ['session_id', 'sessionId'], names)
            const deviceType = colExpr(alias, ['device_type', 'deviceType'], names)
            const deviceName = colExpr(alias, ['device_name', 'deviceName'], names)
            const userAgent = colExpr(alias, ['user_agent', 'userAgent'], names)
            const registerAt = allowedDeviceUnixExpr(
                alias,
                names,
                ['register_at', 'regDate', 'registered_at', 'created_at', 'createdAt', 'created_att'],
                UNIX_NOW
            )
            const createdAt = allowedDeviceUnixExpr(
                alias,
                names,
                ['created_at', 'createdAt', 'created_att', 'register_at', 'regDate'],
                UNIX_NOW
            )

            if (deviceId === 'NULL' || userId === 'NULL') continue

            await db.exec(`
                INSERT OR IGNORE INTO user_allowed_devices__rebuilt (
                    device_id, user_id, session_id, device_type, device_name, user_agent, register_at, created_at
                )
                SELECT
                    CAST(${deviceId} AS TEXT) AS device_id,
                    CAST(${userId} AS TEXT) AS user_id,
                    NULLIF(TRIM(${sessionId}), '') AS session_id,
                    NULLIF(TRIM(${deviceType}), '') AS device_type,
                    NULLIF(TRIM(${deviceName}), '') AS device_name,
                    NULLIF(TRIM(${userAgent}), '') AS user_agent,
                    CAST((${registerAt}) AS INTEGER) AS register_at,
                    CAST((${createdAt}) AS INTEGER) AS created_at
                FROM "${tableName}" ${alias}
                WHERE ${deviceId} IS NOT NULL AND TRIM(CAST(${deviceId} AS TEXT)) != ''
                  AND ${userId} IS NOT NULL AND TRIM(CAST(${userId} AS TEXT)) != '';
            `)
        }

        await db.exec(`DROP TABLE IF EXISTS allowed_devices;`)
        await db.exec(`DROP TABLE IF EXISTS user_allowed_devieces;`)
        await db.exec(`DROP TABLE IF EXISTS user_allowed_devices;`)
        await db.exec(`ALTER TABLE user_allowed_devices__rebuilt RENAME TO user_allowed_devices;`)
        await db.exec(`COMMIT;`)
        console.log('[gp-live] migrateUserAllowedDevicesTableSchema: 完成')
    } catch (e) {
        try {
            await db.exec(`ROLLBACK;`)
        } catch {
            // ignore
        }
        throw e
    } finally {
        await db.exec(`PRAGMA foreign_keys = ON;`)
    }
}

const USER_LOGIN_SESSIONS_FINAL_COLUMNS = new Set([
    'session_id',
    'user_id',
    'user_agent',
    'device_type',
    'device_name',
    'last_login_at',
    'created_at',
])

const USER_LOGIN_SESSIONS_LEGACY_COLUMN_NAMES = new Set([
    'id',
    'userId',
    'ipAddress',
    'lastLoginDate',
    'deviceType',
    'deviceName',
    'userAgent',
    'createdAt',
    'createdAtMs',
])

function userLoginSessionsTableNeedsRebuild(names: Set<string>): boolean {
    if (![...USER_LOGIN_SESSIONS_FINAL_COLUMNS].every((c) => names.has(c))) return true
    if (names.size !== USER_LOGIN_SESSIONS_FINAL_COLUMNS.size) return true
    for (const legacy of USER_LOGIN_SESSIONS_LEGACY_COLUMN_NAMES) {
        if (names.has(legacy)) return true
    }
    return false
}

/**
 * login_sessions → user_login_sessions；Unix 秒。
 */
export async function migrateUserLoginSessionsTableSchema(db: Db) {
    const hasOld = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'login_sessions' LIMIT 1`
    )
    const hasNew = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'user_login_sessions' LIMIT 1`
    )

    const pragmaNew = hasNew?.name
        ? await db.all<{ name: string }>(`PRAGMA table_info(user_login_sessions);`)
        : []
    const newNames = new Set(pragmaNew.map((c) => c.name))
    const newOk = Boolean(hasNew?.name) && !userLoginSessionsTableNeedsRebuild(newNames)

    if (!hasOld?.name && newOk) {
        await db.exec(`DROP TABLE IF EXISTS login_sessions;`)
        return
    }

    if (!hasOld?.name && !hasNew?.name) {
        await db.exec(`
            CREATE TABLE IF NOT EXISTS user_login_sessions (
                session_id TEXT PRIMARY KEY,
                user_id TEXT NOT NULL,
                user_agent TEXT,
                device_type TEXT,
                device_name TEXT,
                last_login_at INTEGER NOT NULL DEFAULT (${UNIX_NOW}),
                created_at INTEGER NOT NULL DEFAULT (${UNIX_NOW}),
                FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE
            );
        `)
        return
    }

    const fromTable: 'login_sessions' | 'user_login_sessions' | null = hasOld?.name
        ? 'login_sessions'
        : hasNew?.name && userLoginSessionsTableNeedsRebuild(newNames)
          ? 'user_login_sessions'
          : null

    if (!fromTable) return

    const cols = await db.all<{ name: string }>(
        `PRAGMA table_info(${fromTable === 'login_sessions' ? `'login_sessions'` : `'user_login_sessions'`});`
    )
    const names = new Set(cols.map((c) => c.name))

    const sid = colExpr('g', ['session_id', 'id'], names)
    const uid = colExpr('g', ['user_id', 'userId'], names)
    const ua = colExpr('g', ['user_agent', 'userAgent'], names)
    const dt = colExpr('g', ['device_type', 'deviceType'], names)
    const dn = colExpr('g', ['device_name', 'deviceName'], names)

    const lastLoginBranches: string[] = []
    if (names.has('last_login_at')) {
        lastLoginBranches.push(`WHEN COALESCE(g.last_login_at, 0) > 0 THEN g.last_login_at`)
    }
    if (names.has('lastLoginDate')) {
        lastLoginBranches.push(
            `WHEN typeof(g.lastLoginDate)='text' AND g.lastLoginDate GLOB '____-__-__*' THEN CAST(strftime('%s', g.lastLoginDate) AS INTEGER)`
        )
        lastLoginBranches.push(
            `WHEN typeof(g.lastLoginDate)='text' AND CAST(g.lastLoginDate AS INTEGER) > 1000000000000 THEN CAST(g.lastLoginDate AS INTEGER) / 1000`
        )
        lastLoginBranches.push(
            `WHEN typeof(g.lastLoginDate)='text' AND CAST(g.lastLoginDate AS INTEGER) > 0 THEN CAST(g.lastLoginDate AS INTEGER)`
        )
    }
    const lastLoginExpr =
        lastLoginBranches.length > 0
            ? `CASE ${lastLoginBranches.join(' ')} ELSE ${UNIX_NOW} END`
            : UNIX_NOW

    const createdBranches: string[] = []
    if (names.has('created_at')) {
        createdBranches.push(`WHEN COALESCE(g.created_at, 0) > 0 THEN g.created_at`)
    }
    if (names.has('createdAtMs')) {
        createdBranches.push(
            `WHEN typeof(g.createdAtMs)='text' AND CAST(g.createdAtMs AS INTEGER) > 1000000000000 THEN CAST(g.createdAtMs AS INTEGER) / 1000`
        )
        createdBranches.push(
            `WHEN typeof(g.createdAtMs)='text' AND CAST(g.createdAtMs AS INTEGER) > 0 THEN CAST(g.createdAtMs AS INTEGER)`
        )
    }
    if (names.has('createdAt')) {
        createdBranches.push(
            `WHEN typeof(g.createdAt)='text' AND CAST(g.createdAt AS INTEGER) > 1000000000000 THEN CAST(g.createdAt AS INTEGER) / 1000`
        )
        createdBranches.push(
            `WHEN typeof(g.createdAt)='text' AND CAST(g.createdAt AS INTEGER) > 0 THEN CAST(g.createdAt AS INTEGER)`
        )
    }
    const createdAtExpr =
        createdBranches.length > 0 ? `CASE ${createdBranches.join(' ')} ELSE ${UNIX_NOW} END` : UNIX_NOW

    console.log(`[gp-live] migrateUserLoginSessionsTableSchema: 重建 user_login_sessions（來源: ${fromTable}）…`)

    await db.exec(`DROP TABLE IF EXISTS user_login_sessions__rebuilt;`)
    await db.exec(`PRAGMA foreign_keys = OFF;`)
    await db.exec(`BEGIN;`)
    try {
        await db.exec(`
            CREATE TABLE user_login_sessions__rebuilt (
                session_id TEXT PRIMARY KEY,
                user_id TEXT NOT NULL,
                user_agent TEXT,
                device_type TEXT,
                device_name TEXT,
                last_login_at INTEGER NOT NULL,
                created_at INTEGER NOT NULL,
                FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE
            );
        `)

        const fromIdent = fromTable === 'login_sessions' ? `"login_sessions"` : `"user_login_sessions"`
        await db.exec(`
            INSERT INTO user_login_sessions__rebuilt (
                session_id, user_id, user_agent, device_type, device_name, last_login_at, created_at
            )
            SELECT
                ${sid} AS session_id,
                ${uid} AS user_id,
                NULLIF(TRIM(${ua}), '') AS user_agent,
                NULLIF(TRIM(${dt}), '') AS device_type,
                NULLIF(TRIM(${dn}), '') AS device_name,
                CAST((${lastLoginExpr}) AS INTEGER) AS last_login_at,
                CAST((${createdAtExpr}) AS INTEGER) AS created_at
            FROM ${fromIdent} g
            WHERE ${sid} IS NOT NULL AND TRIM(CAST(${sid} AS TEXT)) != ''
              AND ${uid} IS NOT NULL AND TRIM(CAST(${uid} AS TEXT)) != '';
        `)

        await db.exec(`DROP TABLE IF EXISTS login_sessions;`)
        await db.exec(`DROP TABLE IF EXISTS user_login_sessions;`)
        await db.exec(`ALTER TABLE user_login_sessions__rebuilt RENAME TO user_login_sessions;`)
        await db.exec(`COMMIT;`)
        console.log('[gp-live] migrateUserLoginSessionsTableSchema: 完成')
    } catch (e) {
        try {
            await db.exec(`ROLLBACK;`)
        } catch {
            // ignore
        }
        throw e
    } finally {
        await db.exec(`PRAGMA foreign_keys = ON;`)
    }

}

const USER_LOGIN_HISTORY_FINAL_COLUMNS = new Set([
    'history_id',
    'user_id',
    'ip_address',
    'country_code',
    'user_agent',
    'device_type',
    'device_name',
    'connect_at',
    'created_at',
])

const USER_LOGIN_HISTORY_LEGACY_COLUMN_NAMES = new Set([
    'id',
    'userId',
    'ipAddress',
    'country',
    'countryEng',
    'connDate',
    'createdAt',
    'createdAtMs',
    'connect_datetime',
])

function userLoginHistoryTableNeedsRebuild(names: Set<string>): boolean {
    if (![...USER_LOGIN_HISTORY_FINAL_COLUMNS].every((c) => names.has(c))) return true
    if (names.size !== USER_LOGIN_HISTORY_FINAL_COLUMNS.size) return true
    for (const legacy of USER_LOGIN_HISTORY_LEGACY_COLUMN_NAMES) {
        if (names.has(legacy)) return true
    }
    return false
}

/**
 * login_history → user_login_history；欄位與 API（/api/profile/login-history）一致，時間為 Unix 秒。
 */
export async function migrateUserLoginHistoryTableSchema(db: Db) {
    const hasOld = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'login_history' LIMIT 1`
    )
    const hasNew = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'user_login_history' LIMIT 1`
    )

    const pragmaNew = hasNew?.name
        ? await db.all<{ name: string }>(`PRAGMA table_info(user_login_history);`)
        : []
    const newNames = new Set(pragmaNew.map((c) => c.name))
    const newOk = Boolean(hasNew?.name) && !userLoginHistoryTableNeedsRebuild(newNames)

    if (!hasOld?.name && newOk) {
        await db.exec(`DROP TABLE IF EXISTS login_history;`)
        return
    }

    if (!hasOld?.name && !hasNew?.name) {
        await db.exec(`
            CREATE TABLE IF NOT EXISTS user_login_history (
                history_id TEXT PRIMARY KEY,
                user_id TEXT NOT NULL,
                ip_address TEXT NOT NULL,
                country_code TEXT,
                user_agent TEXT,
                device_type TEXT,
                device_name TEXT,
                connect_at INTEGER NOT NULL,
                created_at INTEGER NOT NULL,
                FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE
            );
        `)
        return
    }

    const fromTable: 'login_history' | 'user_login_history' | null = hasOld?.name
        ? 'login_history'
        : hasNew?.name && userLoginHistoryTableNeedsRebuild(newNames)
          ? 'user_login_history'
          : null

    if (!fromTable) return

    const cols = await db.all<{ name: string }>(
        `PRAGMA table_info(${fromTable === 'login_history' ? `'login_history'` : `'user_login_history'`});`
    )
    const names = new Set(cols.map((c) => c.name))

    const hid = colExpr('g', ['history_id', 'id'], names)
    const uid = colExpr('g', ['user_id', 'userId'], names)
    const ip = colExpr('g', ['ip_address', 'ipAddress'], names)
    const cc = colExpr('g', ['country_code', 'country'], names)
    const ua = colExpr('g', ['user_agent', 'userAgent'], names)
    const dt = colExpr('g', ['device_type', 'deviceType'], names)
    const dn = colExpr('g', ['device_name', 'deviceName'], names)

    const createdBranches: string[] = []
    if (names.has('created_at')) {
        createdBranches.push(`WHEN COALESCE(g.created_at, 0) > 0 THEN g.created_at`)
    }
    if (names.has('createdAtMs')) {
        createdBranches.push(
            `WHEN typeof(g.createdAtMs)='text' AND CAST(g.createdAtMs AS INTEGER) > 1000000000000 THEN CAST(g.createdAtMs AS INTEGER) / 1000`
        )
        createdBranches.push(
            `WHEN typeof(g.createdAtMs)='text' AND CAST(g.createdAtMs AS INTEGER) > 0 THEN CAST(g.createdAtMs AS INTEGER)`
        )
    }
    if (names.has('createdAt')) {
        createdBranches.push(
            `WHEN typeof(g.createdAt)='text' AND CAST(g.createdAt AS INTEGER) > 1000000000000 THEN CAST(g.createdAt AS INTEGER) / 1000`
        )
        createdBranches.push(
            `WHEN typeof(g.createdAt)='text' AND CAST(g.createdAt AS INTEGER) > 0 THEN CAST(g.createdAt AS INTEGER)`
        )
    }
    const createdAtExpr =
        createdBranches.length > 0 ? `CASE ${createdBranches.join(' ')} ELSE ${UNIX_NOW} END` : UNIX_NOW

    const connectBranches: string[] = []
    if (names.has('connect_at')) {
        connectBranches.push(`WHEN COALESCE(g.connect_at, 0) > 0 THEN g.connect_at`)
    }
    if (names.has('connect_datetime')) {
        connectBranches.push(`WHEN COALESCE(g.connect_datetime, 0) > 0 THEN g.connect_datetime`)
    }
    if (names.has('connDate')) {
        connectBranches.push(
            `WHEN typeof(g.connDate)='text' AND g.connDate GLOB '____-__-__*' THEN CAST(strftime('%s', g.connDate) AS INTEGER)`
        )
        connectBranches.push(
            `WHEN typeof(g.connDate)='text' AND CAST(g.connDate AS INTEGER) > 1000000000000 THEN CAST(g.connDate AS INTEGER) / 1000`
        )
        connectBranches.push(
            `WHEN typeof(g.connDate)='text' AND CAST(g.connDate AS INTEGER) > 0 THEN CAST(g.connDate AS INTEGER)`
        )
    }
    const connectExpr =
        connectBranches.length > 0
            ? `CASE ${connectBranches.join(' ')} ELSE (${createdAtExpr}) END`
            : `(${createdAtExpr})`

    console.log(`[gp-live] migrateUserLoginHistoryTableSchema: 重建 user_login_history（來源: ${fromTable}）…`)

    await db.exec(`DROP TABLE IF EXISTS user_login_history__rebuilt;`)
    await db.exec(`PRAGMA foreign_keys = OFF;`)
    await db.exec(`BEGIN;`)
    try {
        await db.exec(`
            CREATE TABLE user_login_history__rebuilt (
                history_id TEXT PRIMARY KEY,
                user_id TEXT NOT NULL,
                ip_address TEXT NOT NULL,
                country_code TEXT,
                user_agent TEXT,
                device_type TEXT,
                device_name TEXT,
                connect_at INTEGER NOT NULL,
                created_at INTEGER NOT NULL,
                FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE
            );
        `)

        const fromIdent = fromTable === 'login_history' ? `"login_history"` : `"user_login_history"`
        await db.exec(`
            INSERT INTO user_login_history__rebuilt (
                history_id, user_id, ip_address, country_code, user_agent, device_type, device_name,
                connect_at, created_at
            )
            SELECT
                ${hid} AS history_id,
                ${uid} AS user_id,
                COALESCE(NULLIF(TRIM(${ip}), ''), '0.0.0.0') AS ip_address,
                NULLIF(TRIM(${cc}), '') AS country_code,
                NULLIF(TRIM(${ua}), '') AS user_agent,
                NULLIF(TRIM(${dt}), '') AS device_type,
                NULLIF(TRIM(${dn}), '') AS device_name,
                CAST((${connectExpr}) AS INTEGER) AS connect_at,
                CAST((${createdAtExpr}) AS INTEGER) AS created_at
            FROM ${fromIdent} g
            WHERE ${hid} IS NOT NULL AND TRIM(CAST(${hid} AS TEXT)) != ''
              AND ${uid} IS NOT NULL AND TRIM(CAST(${uid} AS TEXT)) != '';
        `)

        await db.exec(`DROP TABLE IF EXISTS login_history;`)
        await db.exec(`DROP TABLE IF EXISTS user_login_history;`)
        await db.exec(`ALTER TABLE user_login_history__rebuilt RENAME TO user_login_history;`)
        await db.exec(`COMMIT;`)
        console.log('[gp-live] migrateUserLoginHistoryTableSchema: 完成')
    } catch (e) {
        try {
            await db.exec(`ROLLBACK;`)
        } catch {
            // ignore
        }
        throw e
    } finally {
        await db.exec(`PRAGMA foreign_keys = ON;`)
    }
}

const ROOM_MODS_FINAL_COLUMNS = new Set(['room_id', 'user_id', 'added_by', 'added_at'])

const ROOM_MODS_LEGACY_COLUMN_NAMES = new Set(['roomId', 'userId', 'addedBy', 'addedAt'])

function roomModsTableNeedsRebuild(names: Set<string>): boolean {
    if (![...ROOM_MODS_FINAL_COLUMNS].every((c) => names.has(c))) return true
    if (names.size !== ROOM_MODS_FINAL_COLUMNS.size) return true
    for (const legacy of ROOM_MODS_LEGACY_COLUMN_NAMES) {
        if (names.has(legacy)) return true
    }
    return false
}

/**
 * room_mods：欄位改為 room_id / user_id / added_by / added_at（Unix 秒）；room_id 指向 users(user_id)。
 */
export async function migrateRoomModsTableSchema(db: Db) {
    const has = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'room_mods' LIMIT 1`
    )
    if (!has?.name) return

    const cols = await db.all<{ name: string }>(`PRAGMA table_info(room_mods);`)
    if (!cols?.length) return
    const names = new Set(cols.map((c) => c.name))
    if (!roomModsTableNeedsRebuild(names)) return

    console.log('[gp-live] migrateRoomModsTableSchema: 重建 room_mods（snake_case + added_at 秒）…')

    const rid = colExpr('g', ['room_id', 'roomId'], names)
    const uid = colExpr('g', ['user_id', 'userId'], names)
    const ab = colExpr('g', ['added_by', 'addedBy'], names)

    const addedBranches: string[] = []
    if (names.has('added_at')) {
        addedBranches.push(`WHEN COALESCE(g.added_at, 0) > 1000000000000 THEN g.added_at / 1000`)
        addedBranches.push(`WHEN COALESCE(g.added_at, 0) > 0 THEN g.added_at`)
    }
    if (names.has('addedAt')) {
        addedBranches.push(
            `WHEN CAST(g.addedAt AS INTEGER) > 1000000000000 THEN CAST(g.addedAt AS INTEGER) / 1000`
        )
        addedBranches.push(`WHEN CAST(g.addedAt AS INTEGER) > 0 THEN CAST(g.addedAt AS INTEGER)`)
    }
    const addedAtExpr =
        addedBranches.length > 0 ? `CASE ${addedBranches.join(' ')} ELSE ${UNIX_NOW} END` : UNIX_NOW

    await db.exec(`DROP TABLE IF EXISTS room_mods__rebuilt;`)
    await db.exec(`PRAGMA foreign_keys = OFF;`)
    await db.exec(`BEGIN;`)
    try {
        await db.exec(`
            CREATE TABLE room_mods__rebuilt (
                room_id TEXT NOT NULL,
                user_id TEXT NOT NULL,
                added_by TEXT NOT NULL,
                added_at INTEGER NOT NULL,
                PRIMARY KEY (room_id, user_id),
                FOREIGN KEY(room_id) REFERENCES users(user_id) ON DELETE CASCADE,
                FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE,
                FOREIGN KEY(added_by) REFERENCES users(user_id) ON DELETE SET NULL
            );
        `)
        await db.exec(`
            INSERT INTO room_mods__rebuilt (room_id, user_id, added_by, added_at)
            SELECT
                ${rid} AS room_id,
                ${uid} AS user_id,
                ${ab} AS added_by,
                CAST((${addedAtExpr}) AS INTEGER) AS added_at
            FROM room_mods g
            WHERE ${rid} IS NOT NULL AND TRIM(CAST(${rid} AS TEXT)) != ''
              AND ${uid} IS NOT NULL AND TRIM(CAST(${uid} AS TEXT)) != ''
              AND ${ab} IS NOT NULL AND TRIM(CAST(${ab} AS TEXT)) != '';
        `)
        await db.exec(`DROP TABLE room_mods;`)
        await db.exec(`ALTER TABLE room_mods__rebuilt RENAME TO room_mods;`)
        await db.exec(`COMMIT;`)
        console.log('[gp-live] migrateRoomModsTableSchema: 完成')
    } catch (e) {
        try {
            await db.exec(`ROLLBACK;`)
        } catch {
            // ignore
        }
        throw e
    } finally {
        await db.exec(`PRAGMA foreign_keys = ON;`)
    }
}

const ROOM_BANS_FINAL_COLUMNS = new Set([
    'room_id',
    'user_id',
    'banned_by',
    'reason',
    'duration_ms',
    'banned_at',
    'expires_at',
])

const ROOM_BANS_LEGACY_COLUMN_NAMES = new Set([
    'roomId',
    'userId',
    'bannedBy',
    'durationMs',
    'bannedAt',
    'expiresAt',
])

function roomBansTableNeedsRebuild(names: Set<string>): boolean {
    if (![...ROOM_BANS_FINAL_COLUMNS].every((c) => names.has(c))) return true
    if (names.size !== ROOM_BANS_FINAL_COLUMNS.size) return true
    for (const legacy of ROOM_BANS_LEGACY_COLUMN_NAMES) {
        if (names.has(legacy)) return true
    }
    return false
}

function unixSecFromMaybeMsExpr(colSql: string): string {
    return `CASE
        WHEN ${colSql} IS NULL THEN NULL
        WHEN CAST(${colSql} AS INTEGER) > 1000000000000 THEN CAST(${colSql} AS INTEGER) / 1000
        ELSE CAST(${colSql} AS INTEGER)
    END`
}

/**
 * room_bans：欄位改為 snake_case；banned_at / expires_at 為 Unix 秒；duration_ms 仍為毫秒。
 */
export async function migrateRoomBansTableSchema(db: Db) {
    const has = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'room_bans' LIMIT 1`
    )
    if (!has?.name) return

    const cols = await db.all<{ name: string }>(`PRAGMA table_info(room_bans);`)
    if (!cols?.length) return
    const names = new Set(cols.map((c) => c.name))
    if (!roomBansTableNeedsRebuild(names)) return

    console.log('[gp-live] migrateRoomBansTableSchema: 重建 room_bans（snake_case + banned_at/expires_at 秒）…')

    const rid = colExpr('g', ['room_id', 'roomId'], names)
    const uid = colExpr('g', ['user_id', 'userId'], names)
    const bb = colExpr('g', ['banned_by', 'bannedBy'], names)
    const reason = colExpr('g', ['reason'], names)
    const dur = colExpr('g', ['duration_ms', 'durationMs'], names)

    const bannedBranches: string[] = []
    if (names.has('banned_at')) {
        bannedBranches.push(
            `WHEN CAST(g.banned_at AS INTEGER) > 1000000000000 THEN CAST(g.banned_at AS INTEGER) / 1000`
        )
        bannedBranches.push(`WHEN CAST(g.banned_at AS INTEGER) > 0 THEN CAST(g.banned_at AS INTEGER)`)
    }
    if (names.has('bannedAt')) {
        bannedBranches.push(
            `WHEN CAST(g.bannedAt AS INTEGER) > 1000000000000 THEN CAST(g.bannedAt AS INTEGER) / 1000`
        )
        bannedBranches.push(`WHEN CAST(g.bannedAt AS INTEGER) > 0 THEN CAST(g.bannedAt AS INTEGER)`)
    }
    const bannedAtExpr =
        bannedBranches.length > 0 ? `CASE ${bannedBranches.join(' ')} ELSE ${UNIX_NOW} END` : UNIX_NOW

    let expiresAtExpr = 'NULL'
    if (names.has('expires_at') && names.has('expiresAt')) {
        expiresAtExpr = `CASE
            WHEN g.expires_at IS NULL AND g.expiresAt IS NULL THEN NULL
            ELSE ${unixSecFromMaybeMsExpr(`COALESCE(g.expires_at, g.expiresAt)`)}
        END`
    } else if (names.has('expires_at')) {
        expiresAtExpr = `CASE WHEN g.expires_at IS NULL THEN NULL ELSE ${unixSecFromMaybeMsExpr('g.expires_at')} END`
    } else if (names.has('expiresAt')) {
        expiresAtExpr = `CASE WHEN g.expiresAt IS NULL THEN NULL ELSE ${unixSecFromMaybeMsExpr('g.expiresAt')} END`
    }

    await db.exec(`DROP TABLE IF EXISTS room_bans__rebuilt;`)
    await db.exec(`PRAGMA foreign_keys = OFF;`)
    await db.exec(`BEGIN;`)
    try {
        await db.exec(`
            CREATE TABLE room_bans__rebuilt (
                room_id TEXT NOT NULL,
                user_id TEXT NOT NULL,
                banned_by TEXT NOT NULL,
                reason TEXT,
                duration_ms INTEGER,
                banned_at INTEGER NOT NULL,
                expires_at INTEGER,
                PRIMARY KEY (room_id, user_id),
                FOREIGN KEY(room_id) REFERENCES live_streams(roomId) ON DELETE CASCADE,
                FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE,
                FOREIGN KEY(banned_by) REFERENCES users(user_id) ON DELETE SET NULL
            );
        `)
        await db.exec(`
            INSERT INTO room_bans__rebuilt (room_id, user_id, banned_by, reason, duration_ms, banned_at, expires_at)
            SELECT
                ${rid} AS room_id,
                ${uid} AS user_id,
                ${bb} AS banned_by,
                ${reason} AS reason,
                CAST(${dur} AS INTEGER) AS duration_ms,
                CAST((${bannedAtExpr}) AS INTEGER) AS banned_at,
                CAST((${expiresAtExpr}) AS INTEGER) AS expires_at
            FROM room_bans g
            WHERE ${rid} IS NOT NULL AND TRIM(CAST(${rid} AS TEXT)) != ''
              AND ${uid} IS NOT NULL AND TRIM(CAST(${uid} AS TEXT)) != ''
              AND ${bb} IS NOT NULL AND TRIM(CAST(${bb} AS TEXT)) != '';
        `)
        await db.exec(`DROP TABLE room_bans;`)
        await db.exec(`ALTER TABLE room_bans__rebuilt RENAME TO room_bans;`)
        await db.exec(`COMMIT;`)
        console.log('[gp-live] migrateRoomBansTableSchema: 完成')
    } catch (e) {
        try {
            await db.exec(`ROLLBACK;`)
        } catch {
            // ignore
        }
        throw e
    } finally {
        await db.exec(`PRAGMA foreign_keys = ON;`)
    }
}

const ROOM_STREAMS_FINAL_COLUMNS = new Set([
    'room_id',
    'title',
    'description',
    'thumbnail_url',
    'live_input_identifier',
    'stream_token',
    'chat_disabled',
    'stream_status',
    'category',
    'attribute',
    'started_at',
    'created_at',
    'updated_at',
])

const ROOM_STREAMS_PHYSICAL_COLUMN_ORDER = [
    'room_id',
    'title',
    'description',
    'thumbnail_url',
    'live_input_identifier',
    'stream_token',
    'chat_disabled',
    'stream_status',
    'category',
    'attribute',
    'started_at',
    'created_at',
    'updated_at',
] as const

function roomStreamsStreamStatusColumnInOrder(cols: { name: string; cid: number }[]): boolean {
    const byName = new Map(cols.map((c) => [c.name, c.cid]))
    const chatCid = byName.get('chat_disabled')
    const statusCid = byName.get('stream_status')
    const startedCid = byName.get('started_at')
    if (chatCid == null || statusCid == null || startedCid == null) return false
    return statusCid === chatCid + 1 && startedCid === statusCid + 1
}

function roomStreamsLiveInputIdentifierColumnInOrder(cols: { name: string; cid: number }[]): boolean {
    const byName = new Map(cols.map((c) => [c.name, c.cid]))
    const thumbCid = byName.get('thumbnail_url')
    const keyCid = byName.get('live_input_identifier')
    const tokenCid = byName.get('stream_token')
    if (thumbCid == null || keyCid == null || tokenCid == null) return false
    return keyCid === thumbCid + 1 && tokenCid === keyCid + 1
}

/** category 須在 stream_status 與 attribute／started_at 之間 */
function roomStreamsCategoryColumnInOrder(cols: { name: string; cid: number }[]): boolean {
    const byName = new Map(cols.map((c) => [c.name, c.cid]))
    const statusCid = byName.get('stream_status')
    const categoryCid = byName.get('category')
    const attributeCid = byName.get('attribute')
    const startedCid = byName.get('started_at')
    const createdCid = byName.get('created_at')
    if (categoryCid == null) return true
    if (statusCid == null || createdCid == null) return false
    if (!(categoryCid > statusCid && categoryCid < createdCid)) return false
    if (attributeCid != null) {
        if (!(categoryCid < attributeCid)) return false
        if (startedCid != null && !(attributeCid < startedCid)) return false
    } else if (startedCid != null && categoryCid > startedCid) return false
    return true
}

/** description 須在 title 與 thumbnail_url 之間 */
function roomStreamsDescriptionColumnInOrder(cols: { name: string; cid: number }[]): boolean {
    const byName = new Map(cols.map((c) => [c.name, c.cid]))
    const titleCid = byName.get('title')
    const descriptionCid = byName.get('description')
    const thumbCid = byName.get('thumbnail_url')
    if (descriptionCid == null) return true
    if (titleCid == null || thumbCid == null) return false
    return descriptionCid === titleCid + 1 && thumbCid === descriptionCid + 1
}

/** attribute 須在 category 與 started_at 之間（category → attribute → started_at） */
function roomStreamsAttributeColumnInOrder(cols: { name: string; cid: number }[]): boolean {
    const byName = new Map(cols.map((c) => [c.name, c.cid]))
    const categoryCid = byName.get('category')
    const attributeCid = byName.get('attribute')
    const startedCid = byName.get('started_at')
    if (attributeCid == null) return true
    if (startedCid == null) return false
    if (!(attributeCid < startedCid)) return false
    if (categoryCid != null && !(categoryCid < attributeCid)) return false
    return true
}

const ROOM_STREAMS_CREATE_TABLE_SQL = `
    CREATE TABLE room_streams (
        room_id TEXT PRIMARY KEY,
        title TEXT NOT NULL,
        description TEXT,
        thumbnail_url TEXT,
        live_input_identifier TEXT,
        stream_token TEXT,
        chat_disabled INTEGER NOT NULL DEFAULT 0,
        stream_status TEXT NOT NULL DEFAULT 'disconnected',
        category TEXT,
        attribute TEXT,
        started_at INTEGER,
        created_at INTEGER NOT NULL DEFAULT (${UNIX_NOW}),
        updated_at INTEGER,
        FOREIGN KEY(room_id) REFERENCES users(user_id) ON DELETE CASCADE
    );
`

const ROOM_STREAMS_LEGACY_COLUMN_NAMES = new Set([
    'user_id',
    'roomId',
    'userId',
    'isLive',
    'viewers',
    'likes',
    'thumbnail',
    'src',
    'chatDisabled',
    'startedAt',
    'createdAt',
    'uploaded_at',
    'uploadedAt',
    'updatedAt',
    'pinnedMessageId',
    'stream_key',
    'check_live_token',
    'live_token',
])

function roomStreamsTableIsFinal(names: Set<string>): boolean {
    for (const col of ROOM_STREAMS_FINAL_COLUMNS) {
        if (col === 'stream_token') {
            if (!names.has('stream_token') && !names.has('video_stream_url')) return false
            continue
        }
        if (col === 'live_input_identifier') {
            if (!names.has('live_input_identifier')) return false
            continue
        }
        if (!names.has(col)) return false
    }
    for (const legacy of ROOM_STREAMS_LEGACY_COLUMN_NAMES) {
        if (names.has(legacy)) return false
    }
    return true
}

/** @deprecated check_live_token 已廢除，請用 migrateRoomStreamsDropCheckLiveTokenColumn */
export async function migrateRoomStreamsLiveTokenColumn(_db: Db) {
    return
}

/** 移除已廢除的 check_live_token（及舊版 live_token） */
export async function migrateRoomStreamsDropCheckLiveTokenColumn(db: Db) {
    const hasRoom = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'room_streams' LIMIT 1`
    )
    if (!hasRoom?.name) return

    const cols = await db.all<{ name: string }>(`PRAGMA table_info(room_streams);`)
    const names = new Set((cols ?? []).map((c) => c.name))

    if (names.has('check_live_token')) {
        await db.exec(`ALTER TABLE room_streams DROP COLUMN check_live_token;`)
        console.log('[gp-live] migrateRoomStreamsDropCheckLiveTokenColumn: 已移除 check_live_token')
    }

    if (names.has('live_token')) {
        await db.exec(`ALTER TABLE room_streams DROP COLUMN live_token;`)
        console.log('[gp-live] migrateRoomStreamsDropCheckLiveTokenColumn: 已移除 live_token')
    }
}

/** video_stream_url（完整 URL）→ stream_token（32 字元 UID） */
export async function migrateRoomStreamsStreamTokenColumn(db: Db) {
    const hasRoom = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'room_streams' LIMIT 1`
    )
    if (!hasRoom?.name) return

    const cols = await db.all<{ name: string }>(`PRAGMA table_info(room_streams);`)
    const names = new Set((cols ?? []).map((c) => c.name))

    if (names.has('stream_token') && !names.has('video_stream_url')) return
    if (!names.has('video_stream_url')) {
        if (!names.has('stream_token')) {
            await db.exec(`ALTER TABLE room_streams ADD COLUMN stream_token TEXT;`)
            console.log('[gp-live] migrateRoomStreamsStreamTokenColumn: 已新增 room_streams.stream_token')
        }
        return
    }

    const { normalizeStreamToken } = await import('@/lib/streams/cloudflareStreamUrls')
    const rows = await db.all<{ room_id: string; video_stream_url: string | null }>(
        `SELECT room_id, video_stream_url FROM room_streams
         WHERE video_stream_url IS NOT NULL AND TRIM(video_stream_url) != ''`
    )

    for (const row of rows ?? []) {
        const token = normalizeStreamToken(String(row.video_stream_url ?? ''))
        await db.run(`UPDATE room_streams SET video_stream_url = ? WHERE room_id = ?`, [
            token ?? '',
            row.room_id,
        ])
    }

    await db.exec(`ALTER TABLE room_streams RENAME COLUMN video_stream_url TO stream_token;`)
    console.log(
        '[gp-live] migrateRoomStreamsStreamTokenColumn: video_stream_url → stream_token（僅存 UID）'
    )
}

/**
 * room_streams 直播金鑰欄位：正式名稱僅 live_input_identifier。
 * stream_key 為舊名，只做一次性 rename/合併，絕不再新增 stream_key。
 */
export async function ensureRoomStreamsLiveInputIdentifierColumn(db: Db) {
    const hasRoom = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'room_streams' LIMIT 1`
    )
    if (!hasRoom?.name) return

    const cols = await db.all<{ name: string }>(`PRAGMA table_info(room_streams);`)
    const names = new Set((cols ?? []).map((c) => c.name))

    if (names.has('live_input_identifier') && !names.has('stream_key')) {
        return
    }

    if (names.has('stream_key') && !names.has('live_input_identifier')) {
        await db.exec(
            `ALTER TABLE room_streams RENAME COLUMN stream_key TO live_input_identifier;`
        )
        console.log('[gp-live] room_streams: stream_key → live_input_identifier')
        return
    }

    if (names.has('stream_key') && names.has('live_input_identifier')) {
        await db.exec(`
            UPDATE room_streams
            SET live_input_identifier = COALESCE(
                NULLIF(TRIM(live_input_identifier), ''),
                NULLIF(TRIM(stream_key), '')
            )
            WHERE (live_input_identifier IS NULL OR TRIM(live_input_identifier) = '')
              AND stream_key IS NOT NULL AND TRIM(stream_key) != '';
        `)
        await db.exec(`ALTER TABLE room_streams DROP COLUMN stream_key;`)
        console.log('[gp-live] room_streams: 已合併 stream_key 至 live_input_identifier 並刪除 stream_key')
        return
    }

    if (!names.has('live_input_identifier')) {
        await db.exec(`ALTER TABLE room_streams ADD COLUMN live_input_identifier TEXT;`)
        console.log('[gp-live] room_streams: 已新增 live_input_identifier')
    }
}

/** room_streams.category：儀表板分類（JSON：groupId + itemId） */
export async function ensureRoomStreamsCategoryColumn(db: Db) {
    const hasRoom = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'room_streams' LIMIT 1`
    )
    if (!hasRoom?.name) return

    const cols = await db.all<{ name: string }>(`PRAGMA table_info(room_streams);`)
    const names = new Set((cols ?? []).map((c) => c.name))
    if (!names.has('category')) {
        await db.exec(`ALTER TABLE room_streams ADD COLUMN category TEXT;`)
        console.log('[gp-live] room_streams: 已新增 category')
    }

    await migrateRoomStreamsCategoryColumnOrder(db)
}

/** room_streams.description：直播間說明（觀眾頁可展開區塊） */
export async function ensureRoomStreamsDescriptionColumn(db: Db) {
    const hasRoom = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'room_streams' LIMIT 1`
    )
    if (!hasRoom?.name) return

    const cols = await db.all<{ name: string }>(`PRAGMA table_info(room_streams);`)
    const names = new Set((cols ?? []).map((c) => c.name))
    if (!names.has('description')) {
        await db.exec(`ALTER TABLE room_streams ADD COLUMN description TEXT;`)
        console.log('[gp-live] room_streams: 已新增 description')
    }

    await migrateRoomStreamsDescriptionColumnOrder(db)
}

/** 將 description 調整至 title 與 thumbnail_url 之間 */
export async function migrateRoomStreamsDescriptionColumnOrder(db: Db) {
    const hasRoom = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'room_streams' LIMIT 1`
    )
    if (!hasRoom?.name) return

    const cols = await db.all<{ name: string; cid: number }>(`PRAGMA table_info(room_streams);`)
    if (!cols?.length) return
    if (!cols.some((c) => c.name === 'description')) return
    if (roomStreamsDescriptionColumnInOrder(cols)) return

    console.log('[gp-live] migrateRoomStreamsDescriptionColumnOrder: 調整 description 欄位順序…')

    await db.exec(`PRAGMA foreign_keys = OFF;`)
    try {
        await db.exec(`BEGIN IMMEDIATE;`)
        await db.exec(`DROP TABLE IF EXISTS room_streams__desc_order_bak;`)
        await db.exec(`CREATE TABLE room_streams__desc_order_bak AS SELECT * FROM room_streams;`)

        const bakCols = await db.all<{ name: string }>(
            `PRAGMA table_info(room_streams__desc_order_bak);`
        )
        const roomColNames = new Set((bakCols ?? []).map((c) => c.name))

        await db.exec(`DROP TABLE room_streams;`)
        await db.exec(ROOM_STREAMS_CREATE_TABLE_SQL)

        const rid = colExpr('rs', ['room_id', 'roomId'], roomColNames)
        const streamStatus = colExpr('rs', ['stream_status', 'streamStatus'], roomColNames)
        const legacyIsLive = colExpr('rs', ['is_live', 'isLive'], roomColNames)
        const title = colExpr('rs', ['title'], roomColNames)
        const description = colExpr('rs', ['description'], roomColNames)
        const thumbnail = colExpr('rs', ['thumbnail_url', 'thumbnail'], roomColNames)
        const liveInputId = colExpr('rs', ['live_input_identifier', 'stream_key'], roomColNames)
        const src = colExpr('rs', ['stream_token', 'video_stream_url', 'src'], roomColNames)
        const chatDisabled = colExpr('rs', ['chat_disabled', 'chatDisabled'], roomColNames)
        const category = colExpr('rs', ['category'], roomColNames)
        const attribute = colExpr('rs', ['attribute'], roomColNames)
        const startedAt = roomStreamUnixExpr('rs', roomColNames, ['started_at', 'startedAt'], 'NULL')
        const createdAt = roomStreamUnixExpr('rs', roomColNames, ['created_at', 'createdAt'], UNIX_NOW)
        const updatedAt = roomStreamUnixExpr(
            'rs',
            roomColNames,
            ['updated_at', 'updatedAt', 'uploaded_at', 'uploadedAt'],
            'NULL'
        )

        if (rid !== 'NULL') {
            await db.exec(`
                INSERT INTO room_streams (
                    room_id, title, description, thumbnail_url, live_input_identifier, stream_token,
                    chat_disabled, stream_status, category, attribute, started_at, created_at, updated_at
                )
                SELECT
                    CAST(${rid} AS TEXT) AS room_id,
                    COALESCE(NULLIF(TRIM(${title}), ''), '') AS title,
                    NULLIF(TRIM(${description}), '') AS description,
                    NULLIF(TRIM(${thumbnail}), '') AS thumbnail_url,
                    NULLIF(TRIM(${liveInputId}), '') AS live_input_identifier,
                    NULLIF(TRIM(${src}), '') AS stream_token,
                    COALESCE(CAST(${chatDisabled} AS INTEGER), 0) AS chat_disabled,
                    CASE
                        WHEN ${streamStatus} IS NOT NULL AND TRIM(CAST(${streamStatus} AS TEXT)) != ''
                            THEN LOWER(TRIM(CAST(${streamStatus} AS TEXT)))
                        WHEN COALESCE(CAST(${legacyIsLive} AS INTEGER), 0) = 1 THEN 'ready'
                        ELSE 'disconnected'
                    END AS stream_status,
                    NULLIF(TRIM(${category}), '') AS category,
                    NULLIF(TRIM(${attribute}), '') AS attribute,
                    CAST((${startedAt}) AS INTEGER) AS started_at,
                    CAST((${createdAt}) AS INTEGER) AS created_at,
                    CAST((${updatedAt}) AS INTEGER) AS updated_at
                FROM room_streams__desc_order_bak rs
                WHERE ${rid} IS NOT NULL AND TRIM(CAST(${rid} AS TEXT)) != '';
            `)
        }

        await db.exec(`DROP TABLE room_streams__desc_order_bak;`)
        await db.exec(`COMMIT;`)
        console.log('[gp-live] migrateRoomStreamsDescriptionColumnOrder: 完成')
    } catch (e) {
        try {
            await db.exec(`ROLLBACK;`)
        } catch {
            // ignore
        }
        throw e
    } finally {
        await db.exec(`PRAGMA foreign_keys = ON;`)
    }
}

/** room_streams.attribute：直播屬性（JSON，儀表板 broadcastProps 等） */
export async function ensureRoomStreamsAttributeColumn(db: Db) {
    const hasRoom = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'room_streams' LIMIT 1`
    )
    if (!hasRoom?.name) return

    const cols = await db.all<{ name: string }>(`PRAGMA table_info(room_streams);`)
    const names = new Set((cols ?? []).map((c) => c.name))
    if (!names.has('attribute')) {
        await db.exec(`ALTER TABLE room_streams ADD COLUMN attribute TEXT;`)
        console.log('[gp-live] room_streams: 已新增 attribute')
    }

    await migrateRoomStreamsAttributeColumnOrder(db)
}

/** 將 attribute 調整至 category 與 started_at 之間 */
export async function migrateRoomStreamsAttributeColumnOrder(db: Db) {
    const hasRoom = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'room_streams' LIMIT 1`
    )
    if (!hasRoom?.name) return

    const cols = await db.all<{ name: string; cid: number }>(`PRAGMA table_info(room_streams);`)
    if (!cols?.length) return
    if (!cols.some((c) => c.name === 'attribute')) return
    if (roomStreamsAttributeColumnInOrder(cols)) return

    console.log('[gp-live] migrateRoomStreamsAttributeColumnOrder: 調整 attribute 欄位順序…')

    await db.exec(`PRAGMA foreign_keys = OFF;`)
    try {
        await db.exec(`BEGIN IMMEDIATE;`)
        await db.exec(`DROP TABLE IF EXISTS room_streams__attr_order_bak;`)
        await db.exec(`CREATE TABLE room_streams__attr_order_bak AS SELECT * FROM room_streams;`)

        const bakCols = await db.all<{ name: string }>(
            `PRAGMA table_info(room_streams__attr_order_bak);`
        )
        const roomColNames = new Set((bakCols ?? []).map((c) => c.name))

        await db.exec(`DROP TABLE room_streams;`)
        await db.exec(ROOM_STREAMS_CREATE_TABLE_SQL)

        const rid = colExpr('rs', ['room_id', 'roomId'], roomColNames)
        const streamStatus = colExpr('rs', ['stream_status', 'streamStatus'], roomColNames)
        const legacyIsLive = colExpr('rs', ['is_live', 'isLive'], roomColNames)
        const title = colExpr('rs', ['title'], roomColNames)
        const description = colExpr('rs', ['description'], roomColNames)
        const thumbnail = colExpr('rs', ['thumbnail_url', 'thumbnail'], roomColNames)
        const liveInputId = colExpr('rs', ['live_input_identifier', 'stream_key'], roomColNames)
        const src = colExpr('rs', ['stream_token', 'video_stream_url', 'src'], roomColNames)
        const chatDisabled = colExpr('rs', ['chat_disabled', 'chatDisabled'], roomColNames)
        const category = colExpr('rs', ['category'], roomColNames)
        const attribute = colExpr('rs', ['attribute'], roomColNames)
        const startedAt = roomStreamUnixExpr('rs', roomColNames, ['started_at', 'startedAt'], 'NULL')
        const createdAt = roomStreamUnixExpr('rs', roomColNames, ['created_at', 'createdAt'], UNIX_NOW)
        const updatedAt = roomStreamUnixExpr(
            'rs',
            roomColNames,
            ['updated_at', 'updatedAt', 'uploaded_at', 'uploadedAt'],
            'NULL'
        )

        if (rid !== 'NULL') {
            await db.exec(`
                INSERT INTO room_streams (
                    room_id, title, description, thumbnail_url, live_input_identifier, stream_token,
                    chat_disabled, stream_status, category, attribute, started_at, created_at, updated_at
                )
                SELECT
                    CAST(${rid} AS TEXT) AS room_id,
                    COALESCE(NULLIF(TRIM(${title}), ''), '') AS title,
                    NULLIF(TRIM(${description}), '') AS description,
                    NULLIF(TRIM(${thumbnail}), '') AS thumbnail_url,
                    NULLIF(TRIM(${liveInputId}), '') AS live_input_identifier,
                    NULLIF(TRIM(${src}), '') AS stream_token,
                    COALESCE(CAST(${chatDisabled} AS INTEGER), 0) AS chat_disabled,
                    CASE
                        WHEN ${streamStatus} IS NOT NULL AND TRIM(CAST(${streamStatus} AS TEXT)) != ''
                            THEN LOWER(TRIM(CAST(${streamStatus} AS TEXT)))
                        WHEN COALESCE(CAST(${legacyIsLive} AS INTEGER), 0) = 1 THEN 'ready'
                        ELSE 'disconnected'
                    END AS stream_status,
                    NULLIF(TRIM(${category}), '') AS category,
                    NULLIF(TRIM(${attribute}), '') AS attribute,
                    CAST((${startedAt}) AS INTEGER) AS started_at,
                    CAST((${createdAt}) AS INTEGER) AS created_at,
                    CAST((${updatedAt}) AS INTEGER) AS updated_at
                FROM room_streams__attr_order_bak rs
                WHERE ${rid} IS NOT NULL AND TRIM(CAST(${rid} AS TEXT)) != '';
            `)
        }

        await db.exec(`DROP TABLE room_streams__attr_order_bak;`)
        await db.exec(`COMMIT;`)
        console.log('[gp-live] migrateRoomStreamsAttributeColumnOrder: 完成')
    } catch (e) {
        try {
            await db.exec(`ROLLBACK;`)
        } catch {
            // ignore
        }
        throw e
    } finally {
        await db.exec(`PRAGMA foreign_keys = ON;`)
    }
}

/** 廢除 room_streams.tags（分類改由 category 欄位儲存） */
export async function dropRoomStreamsTagsColumn(db: Db) {
    const hasRoom = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'room_streams' LIMIT 1`
    )
    if (!hasRoom?.name) return

    const cols = await db.all<{ name: string }>(`PRAGMA table_info(room_streams);`)
    const names = new Set((cols ?? []).map((c) => c.name))
    if (!names.has('tags')) return

    await db.exec(`ALTER TABLE room_streams DROP COLUMN tags;`)
    console.log('[gp-live] dropRoomStreamsTagsColumn: 已移除 room_streams.tags')
}

/** 將 category 調整至 stream_status 與 created_at 之間 */
export async function migrateRoomStreamsCategoryColumnOrder(db: Db) {
    const hasRoom = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'room_streams' LIMIT 1`
    )
    if (!hasRoom?.name) return

    const cols = await db.all<{ name: string; cid: number }>(`PRAGMA table_info(room_streams);`)
    if (!cols?.length) return
    if (!cols.some((c) => c.name === 'category')) return
    if (roomStreamsCategoryColumnInOrder(cols)) return

    console.log('[gp-live] migrateRoomStreamsCategoryColumnOrder: 調整 category 欄位順序…')

    await db.exec(`PRAGMA foreign_keys = OFF;`)
    try {
        await db.exec(`BEGIN IMMEDIATE;`)
        await db.exec(`DROP TABLE IF EXISTS room_streams__cat_order_bak;`)
        await db.exec(`CREATE TABLE room_streams__cat_order_bak AS SELECT * FROM room_streams;`)

        const bakCols = await db.all<{ name: string }>(
            `PRAGMA table_info(room_streams__cat_order_bak);`
        )
        const roomColNames = new Set((bakCols ?? []).map((c) => c.name))

        await db.exec(`DROP TABLE room_streams;`)
        await db.exec(ROOM_STREAMS_CREATE_TABLE_SQL)

        const rid = colExpr('rs', ['room_id', 'roomId'], roomColNames)
        const streamStatus = colExpr('rs', ['stream_status', 'streamStatus'], roomColNames)
        const legacyIsLive = colExpr('rs', ['is_live', 'isLive'], roomColNames)
        const title = colExpr('rs', ['title'], roomColNames)
        const description = colExpr('rs', ['description'], roomColNames)
        const thumbnail = colExpr('rs', ['thumbnail_url', 'thumbnail'], roomColNames)
        const liveInputId = colExpr('rs', ['live_input_identifier', 'stream_key'], roomColNames)
        const src = colExpr('rs', ['stream_token', 'video_stream_url', 'src'], roomColNames)
        const chatDisabled = colExpr('rs', ['chat_disabled', 'chatDisabled'], roomColNames)
        const category = colExpr('rs', ['category'], roomColNames)
        const attribute = colExpr('rs', ['attribute'], roomColNames)
        const startedAt = roomStreamUnixExpr('rs', roomColNames, ['started_at', 'startedAt'], 'NULL')
        const createdAt = roomStreamUnixExpr('rs', roomColNames, ['created_at', 'createdAt'], UNIX_NOW)
        const updatedAt = roomStreamUnixExpr(
            'rs',
            roomColNames,
            ['updated_at', 'updatedAt', 'uploaded_at', 'uploadedAt'],
            'NULL'
        )

        if (rid !== 'NULL') {
            await db.exec(`
                INSERT INTO room_streams (
                    room_id, title, description, thumbnail_url, live_input_identifier, stream_token,
                    chat_disabled, stream_status, category, attribute, started_at, created_at, updated_at
                )
                SELECT
                    CAST(${rid} AS TEXT) AS room_id,
                    COALESCE(NULLIF(TRIM(${title}), ''), '') AS title,
                    NULLIF(TRIM(${description}), '') AS description,
                    NULLIF(TRIM(${thumbnail}), '') AS thumbnail_url,
                    NULLIF(TRIM(${liveInputId}), '') AS live_input_identifier,
                    NULLIF(TRIM(${src}), '') AS stream_token,
                    COALESCE(CAST(${chatDisabled} AS INTEGER), 0) AS chat_disabled,
                    CASE
                        WHEN ${streamStatus} IS NOT NULL AND TRIM(CAST(${streamStatus} AS TEXT)) != ''
                            THEN LOWER(TRIM(CAST(${streamStatus} AS TEXT)))
                        WHEN COALESCE(CAST(${legacyIsLive} AS INTEGER), 0) = 1 THEN 'ready'
                        ELSE 'disconnected'
                    END AS stream_status,
                    NULLIF(TRIM(${category}), '') AS category,
                    NULLIF(TRIM(${attribute}), '') AS attribute,
                    CAST((${startedAt}) AS INTEGER) AS started_at,
                    CAST((${createdAt}) AS INTEGER) AS created_at,
                    CAST((${updatedAt}) AS INTEGER) AS updated_at
                FROM room_streams__cat_order_bak rs
                WHERE ${rid} IS NOT NULL AND TRIM(CAST(${rid} AS TEXT)) != '';
            `)
        }

        await db.exec(`DROP TABLE room_streams__cat_order_bak;`)
        await db.exec(`COMMIT;`)
        console.log('[gp-live] migrateRoomStreamsCategoryColumnOrder: 完成')
    } catch (e) {
        try {
            await db.exec(`ROLLBACK;`)
        } catch {
            // ignore
        }
        throw e
    } finally {
        await db.exec(`PRAGMA foreign_keys = ON;`)
    }
}

/** @deprecated 請用 ensureRoomStreamsLiveInputIdentifierColumn */
export async function migrateRoomStreamsRenameStreamKeyToLiveInputIdentifier(db: Db) {
    await ensureRoomStreamsLiveInputIdentifierColumn(db)
}

/** @deprecated 請用 ensureRoomStreamsLiveInputIdentifierColumn */
export async function migrateRoomStreamsStreamKeyColumn(db: Db) {
    await ensureRoomStreamsLiveInputIdentifierColumn(db)
}

/** 已停用：SQLite 欄位順序不影響功能，整表重建曾導致 stream_key / live_input_identifier 反覆變動 */
export async function migrateRoomStreamsStreamKeyColumnOrder(_db: Db) {
    return
}

/** 已停用：同上 */
export async function migrateRoomStreamsStreamStatusColumnOrder(_db: Db) {
    return
}

/** 以 lifecycle status 取代 is_live；stream_status 預設 disconnected，ready 表示開播中 */
export async function migrateRoomStreamsStreamStatusDropIsLive(db: Db) {
    const hasRoom = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'room_streams' LIMIT 1`
    )
    if (!hasRoom?.name) return

    const cols = await db.all<{ name: string }>(`PRAGMA table_info(room_streams);`)
    const names = new Set((cols ?? []).map((c) => c.name))

    if (names.has('stream_status') && !names.has('is_live')) return

    if (!names.has('stream_status')) {
        await db.exec(
            `ALTER TABLE room_streams ADD COLUMN stream_status TEXT NOT NULL DEFAULT 'disconnected';`
        )
        names.add('stream_status')
    }

    if (names.has('is_live')) {
        await db.exec(`
            UPDATE room_streams
            SET stream_status = CASE WHEN is_live = 1 THEN 'ready' ELSE 'disconnected' END
            WHERE stream_status IS NULL OR TRIM(stream_status) = '';
        `)
        await db.exec(`ALTER TABLE room_streams DROP COLUMN is_live;`)
        console.log('[gp-live] migrateRoomStreamsStreamStatusDropIsLive: 已移除 is_live，改用 stream_status')
    }
}

async function tableRoomIdReferencesUsers(db: Db, tableName: string): Promise<boolean> {
    const row = await db.get<{ sql: string | null }>(
        `SELECT sql FROM sqlite_master WHERE type = 'table' AND name = ? LIMIT 1`,
        [tableName]
    )
    const sql = row?.sql ?? ''
    return /\bFOREIGN\s+KEY\s*\(\s*room_id\s*\)\s*REFERENCES\s+users\s*\(\s*user_id\s*\)/i.test(sql)
}

async function roomIdForeignKeysNeedRebuild(db: Db): Promise<boolean> {
    const tables = ['room_streams', 'room_stream_likes', 'room_pinned_message', 'room_mods']
    for (const tableName of tables) {
        if ((await tableExists(db, tableName)) && !(await tableRoomIdReferencesUsers(db, tableName))) {
            return true
        }
    }
    return false
}

/** 僅修正 room_id 外鍵指向 users，不重建 room_streams 本體（避免清空 live_input_identifier 等欄位） */
async function migrateRoomStreamsRelatedForeignKeysOnly(db: Db) {
    const needs = await roomIdForeignKeysNeedRebuild(db)
    if (!needs) return

    console.log('[gp-live] migrateRoomStreamsRelatedForeignKeysOnly: 修正 room_id 外鍵…')

    await db.exec(`PRAGMA foreign_keys = OFF;`)
    await db.exec(`BEGIN;`)
    try {
        const hasPinned = await tableExists(db, 'room_pinned_message')
        if (hasPinned && !(await tableRoomIdReferencesUsers(db, 'room_pinned_message'))) {
            await db.exec(`DROP TABLE IF EXISTS room_pinned_message__fk_bak;`)
            await db.exec(`CREATE TABLE room_pinned_message__fk_bak AS SELECT * FROM room_pinned_message;`)
            await db.exec(`DROP TABLE room_pinned_message;`)
            await db.exec(`
                CREATE TABLE room_pinned_message (
                    room_id TEXT PRIMARY KEY,
                    message_id TEXT NOT NULL,
                    user_id TEXT NOT NULL,
                    contents TEXT NOT NULL,
                    created_at INTEGER NOT NULL,
                    FOREIGN KEY(room_id) REFERENCES users(user_id) ON DELETE CASCADE,
                    FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE SET NULL
                );
            `)
            await db.exec(`
                INSERT INTO room_pinned_message (room_id, message_id, user_id, contents, created_at)
                SELECT room_id, message_id, user_id, contents, created_at FROM room_pinned_message__fk_bak;
            `)
            await db.exec(`DROP TABLE room_pinned_message__fk_bak;`)
        }

        const hasLikes = await tableExists(db, 'room_stream_likes')
        if (hasLikes && !(await tableRoomIdReferencesUsers(db, 'room_stream_likes'))) {
            await db.exec(`DROP TABLE IF EXISTS room_stream_likes__fk_bak;`)
            await db.exec(`CREATE TABLE room_stream_likes__fk_bak AS SELECT * FROM room_stream_likes;`)
            await db.exec(`DROP TABLE room_stream_likes;`)
            await db.exec(`
                CREATE TABLE room_stream_likes (
                    room_id TEXT NOT NULL,
                    user_id TEXT NOT NULL,
                    created_at INTEGER NOT NULL,
                    PRIMARY KEY (room_id, user_id),
                    FOREIGN KEY(room_id) REFERENCES users(user_id) ON DELETE CASCADE,
                    FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE
                );
            `)
            await db.exec(`
                INSERT OR IGNORE INTO room_stream_likes (room_id, user_id, created_at)
                SELECT room_id, user_id, created_at FROM room_stream_likes__fk_bak;
            `)
            await db.exec(`DROP TABLE room_stream_likes__fk_bak;`)
        }

        const hasMods = await tableExists(db, 'room_mods')
        if (hasMods && !(await tableRoomIdReferencesUsers(db, 'room_mods'))) {
            await db.exec(`DROP TABLE IF EXISTS room_mods__fk_bak;`)
            await db.exec(`CREATE TABLE room_mods__fk_bak AS SELECT * FROM room_mods;`)
            await db.exec(`DROP TABLE room_mods;`)
            await db.exec(`
                CREATE TABLE room_mods (
                    room_id TEXT NOT NULL,
                    user_id TEXT NOT NULL,
                    added_by TEXT NOT NULL,
                    added_at INTEGER NOT NULL,
                    PRIMARY KEY (room_id, user_id),
                    FOREIGN KEY(room_id) REFERENCES users(user_id) ON DELETE CASCADE,
                    FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE,
                    FOREIGN KEY(added_by) REFERENCES users(user_id) ON DELETE SET NULL
                );
            `)
            await db.exec(`
                INSERT INTO room_mods (room_id, user_id, added_by, added_at)
                SELECT room_id, user_id, added_by, added_at FROM room_mods__fk_bak;
            `)
            await db.exec(`DROP TABLE room_mods__fk_bak;`)
        }

        const hasStreams = await tableExists(db, 'room_streams')
        if (hasStreams && !(await tableRoomIdReferencesUsers(db, 'room_streams'))) {
            const cols = await db.all<{ name: string }>(`PRAGMA table_info(room_streams);`)
            const roomColNames = new Set((cols ?? []).map((c) => c.name))
            await db.exec(`DROP TABLE IF EXISTS room_streams__fk_bak;`)
            await db.exec(`CREATE TABLE room_streams__fk_bak AS SELECT * FROM room_streams;`)
            await db.exec(`DROP TABLE room_streams;`)
            await db.exec(ROOM_STREAMS_CREATE_TABLE_SQL)

            const rid = colExpr('rs', ['room_id', 'roomId'], roomColNames)
            const streamStatus = colExpr('rs', ['stream_status', 'streamStatus'], roomColNames)
            const legacyIsLive = colExpr('rs', ['is_live', 'isLive'], roomColNames)
            const title = colExpr('rs', ['title'], roomColNames)
            const description = colExpr('rs', ['description'], roomColNames)
            const thumbnail = colExpr('rs', ['thumbnail_url', 'thumbnail'], roomColNames)
            const liveInputId = colExpr(
                'rs',
                ['live_input_identifier', 'stream_key'],
                roomColNames
            )
            const src = colExpr('rs', ['stream_token', 'video_stream_url', 'src'], roomColNames)
            const chatDisabled = colExpr('rs', ['chat_disabled', 'chatDisabled'], roomColNames)
            const category = colExpr('rs', ['category'], roomColNames)
            const attribute = colExpr('rs', ['attribute'], roomColNames)
            const startedAt = roomStreamUnixExpr('rs', roomColNames, ['started_at', 'startedAt'], 'NULL')
            const createdAt = roomStreamUnixExpr('rs', roomColNames, ['created_at', 'createdAt'], UNIX_NOW)
            const updatedAt = roomStreamUnixExpr(
                'rs',
                roomColNames,
                ['updated_at', 'updatedAt', 'uploaded_at', 'uploadedAt'],
                'NULL'
            )

            if (rid !== 'NULL') {
                await db.exec(`
                    INSERT INTO room_streams (
                        room_id, title, description, thumbnail_url, live_input_identifier, stream_token,
                        chat_disabled, stream_status, category, attribute, started_at, created_at, updated_at
                    )
                    SELECT
                        CAST(${rid} AS TEXT) AS room_id,
                        COALESCE(NULLIF(TRIM(${title}), ''), '') AS title,
                        NULLIF(TRIM(${description}), '') AS description,
                        NULLIF(TRIM(${thumbnail}), '') AS thumbnail_url,
                        NULLIF(TRIM(${liveInputId}), '') AS live_input_identifier,
                        NULLIF(TRIM(${src}), '') AS stream_token,
                        COALESCE(CAST(${chatDisabled} AS INTEGER), 0) AS chat_disabled,
                        CASE
                            WHEN ${streamStatus} IS NOT NULL AND TRIM(CAST(${streamStatus} AS TEXT)) != ''
                                THEN LOWER(TRIM(CAST(${streamStatus} AS TEXT)))
                            WHEN COALESCE(CAST(${legacyIsLive} AS INTEGER), 0) = 1 THEN 'ready'
                            ELSE 'disconnected'
                        END AS stream_status,
                        NULLIF(TRIM(${category}), '') AS category,
                        NULLIF(TRIM(${attribute}), '') AS attribute,
                        CAST((${startedAt}) AS INTEGER) AS started_at,
                        CAST((${createdAt}) AS INTEGER) AS created_at,
                        CAST((${updatedAt}) AS INTEGER) AS updated_at
                    FROM room_streams__fk_bak rs
                    WHERE ${rid} IS NOT NULL AND TRIM(CAST(${rid} AS TEXT)) != '';
                `)
            }
            await db.exec(`DROP TABLE room_streams__fk_bak;`)
        }

        await db.exec(`COMMIT;`)
        console.log('[gp-live] migrateRoomStreamsRelatedForeignKeysOnly: 完成')
    } catch (e) {
        try {
            await db.exec(`ROLLBACK;`)
        } catch {
            // ignore
        }
        throw e
    } finally {
        await db.exec(`PRAGMA foreign_keys = ON;`)
    }
}

function roomStreamUnixExpr(alias: string, names: Set<string>, candidates: string[], fallback: string): string {
    const branches: string[] = []
    for (const col of candidates) {
        if (!names.has(col)) continue
        branches.push(
            `WHEN ${alias}."${col}" IS NULL THEN NULL`
        )
        branches.push(
            `WHEN CAST(${alias}."${col}" AS INTEGER) > 1000000000000 THEN CAST(${alias}."${col}" AS INTEGER) / 1000`
        )
        branches.push(`WHEN CAST(${alias}."${col}" AS INTEGER) > 0 THEN CAST(${alias}."${col}" AS INTEGER)`)
        branches.push(
            `WHEN TRIM(CAST(${alias}."${col}" AS TEXT)) != '' AND CAST(strftime('%s', ${alias}."${col}") AS INTEGER) > 0 THEN CAST(strftime('%s', ${alias}."${col}") AS INTEGER)`
        )
    }
    return branches.length > 0 ? `CASE ${branches.join(' ')} ELSE ${fallback} END` : fallback
}

/**
 * live_streams → room_streams；likes 改由 room_stream_likes 統計；置頂改 room_pinned_message。
 * 並重建 room_bans / room_mods / room_stream_likes；room_id 相關表改指向 users(user_id)；聊天訊息不再入庫，刪除 chat_messages。
 */
export async function migrateRoomStreamsTableSchema(db: Db) {
    const hasLive = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'live_streams' LIMIT 1`
    )
    const hasRoom = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'room_streams' LIMIT 1`
    )

    let roomColNames = new Set<string>()
    let roomCreatedAtHasDefault = false
    if (hasRoom?.name) {
        const rc = await db.all<{ name: string; notnull?: number; dflt_value?: string | null }>(
            `PRAGMA table_info(room_streams);`
        )
        roomColNames = new Set((rc ?? []).map((c) => c.name))
        const createdAtCol = rc?.find((c) => c.name === 'created_at')
        roomCreatedAtHasDefault = Boolean(createdAtCol?.dflt_value)
    }

    if (!hasLive?.name && hasRoom?.name && roomColNames.has('uploaded_at') && !roomColNames.has('updated_at')) {
        await db.exec(`ALTER TABLE room_streams RENAME COLUMN uploaded_at TO updated_at;`)
        roomColNames.delete('uploaded_at')
        roomColNames.add('updated_at')
    }

    const needsRoomIdFkRebuild = await roomIdForeignKeysNeedRebuild(db)

  /** created_at 有 DEFAULT 即可（NOT NULL + DEFAULT 為正式結構，勿因 notnull=1 反覆重建整表） */
    const roomCreatedAtOk = roomCreatedAtHasDefault

    if (!hasLive?.name && hasRoom?.name && roomStreamsTableIsFinal(roomColNames) && roomCreatedAtOk) {
        if (needsRoomIdFkRebuild) {
            await migrateRoomStreamsRelatedForeignKeysOnly(db)
        }
        return
    }

    console.log('[gp-live] migrateRoomStreamsTableSchema: live_streams → room_streams + room_pinned_message…')

    await db.exec(`PRAGMA foreign_keys = OFF;`)
    await db.exec(`BEGIN;`)
    try {
        const needsRoomSelfMigrate =
            !hasLive?.name &&
            hasRoom?.name &&
            (!roomStreamsTableIsFinal(roomColNames) || !roomCreatedAtOk)
        if (needsRoomSelfMigrate) {
            await db.exec(`DROP TABLE IF EXISTS room_streams__src_bak;`)
            await db.exec(`CREATE TABLE room_streams__src_bak AS SELECT * FROM room_streams;`)
        }

        await db.exec(`DROP TABLE IF EXISTS room_streams;`)

        await db.exec(ROOM_STREAMS_CREATE_TABLE_SQL)

        if (hasLive?.name) {
            await db.exec(`
                INSERT INTO room_streams (
                    room_id, title, description, thumbnail_url, stream_token, chat_disabled,
                    stream_status, category, attribute, started_at, created_at, updated_at
                )
                SELECT
                    ls.roomId AS room_id,
                    COALESCE(ls.title, '') AS title,
                    NULL AS description,
                    ls.thumbnail AS thumbnail_url,
                    ls.src AS stream_token,
                    COALESCE(ls.chatDisabled, 0) AS chat_disabled,
                    CASE WHEN COALESCE(ls.isLive, 0) = 1 THEN 'ready' ELSE 'disconnected' END AS stream_status,
                    NULL AS category,
                    NULL AS attribute,
                    CAST((
                        CASE
                            WHEN ls.startedAt IS NULL THEN NULL
                            WHEN CAST(ls.startedAt AS INTEGER) > 1000000000000 THEN CAST(ls.startedAt AS INTEGER) / 1000
                            WHEN CAST(ls.startedAt AS INTEGER) > 0 THEN CAST(ls.startedAt AS INTEGER)
                            WHEN TRIM(CAST(ls.startedAt AS TEXT)) != '' AND CAST(strftime('%s', ls.startedAt) AS INTEGER) > 0
                                THEN CAST(strftime('%s', ls.startedAt) AS INTEGER)
                            ELSE NULL
                        END
                    ) AS INTEGER) AS started_at,
                    CAST((
                        CASE
                            WHEN CAST(ls.createdAt AS INTEGER) > 1000000000000 THEN CAST(ls.createdAt AS INTEGER) / 1000
                            WHEN CAST(ls.createdAt AS INTEGER) > 0 THEN CAST(ls.createdAt AS INTEGER)
                            WHEN ls.createdAt IS NOT NULL AND TRIM(CAST(ls.createdAt AS TEXT)) != ''
                                AND CAST(strftime('%s', ls.createdAt) AS INTEGER) > 0
                                THEN CAST(strftime('%s', ls.createdAt) AS INTEGER)
                            ELSE ${UNIX_NOW}
                        END
                    ) AS INTEGER) AS created_at,
                    CAST((
                        CASE
                            WHEN ls.uploadedAt IS NULL THEN NULL
                            WHEN CAST(ls.uploadedAt AS INTEGER) > 1000000000000 THEN CAST(ls.uploadedAt AS INTEGER) / 1000
                            WHEN CAST(ls.uploadedAt AS INTEGER) > 0 THEN CAST(ls.uploadedAt AS INTEGER)
                            WHEN TRIM(CAST(ls.uploadedAt AS TEXT)) != '' AND CAST(strftime('%s', ls.uploadedAt) AS INTEGER) > 0
                                THEN CAST(strftime('%s', ls.uploadedAt) AS INTEGER)
                            ELSE NULL
                        END
                    ) AS INTEGER) AS updated_at
                FROM live_streams ls
                WHERE ls.roomId IS NOT NULL AND TRIM(CAST(ls.roomId AS TEXT)) != '';
            `)
        } else if (needsRoomSelfMigrate) {
            const rid = colExpr('rs', ['room_id', 'roomId'], roomColNames)
            const streamStatus = colExpr('rs', ['stream_status', 'streamStatus'], roomColNames)
            const legacyIsLive = colExpr('rs', ['is_live', 'isLive'], roomColNames)
            const title = colExpr('rs', ['title'], roomColNames)
            const description = colExpr('rs', ['description'], roomColNames)
            const thumbnail = colExpr('rs', ['thumbnail_url', 'thumbnail'], roomColNames)
            const liveInputId = colExpr(
                'rs',
                ['live_input_identifier', 'stream_key'],
                roomColNames
            )
            const src = colExpr('rs', ['stream_token', 'video_stream_url', 'src'], roomColNames)
            const chatDisabled = colExpr('rs', ['chat_disabled', 'chatDisabled'], roomColNames)
            const category = colExpr('rs', ['category'], roomColNames)
            const attribute = colExpr('rs', ['attribute'], roomColNames)
            const startedAt = roomStreamUnixExpr('rs', roomColNames, ['started_at', 'startedAt'], 'NULL')
            const createdAt = roomStreamUnixExpr('rs', roomColNames, ['created_at', 'createdAt'], UNIX_NOW)
            const updatedAt = roomStreamUnixExpr(
                'rs',
                roomColNames,
                ['updated_at', 'updatedAt', 'uploaded_at', 'uploadedAt'],
                'NULL'
            )

            if (rid !== 'NULL') {
                await db.exec(`
                    INSERT INTO room_streams (
                        room_id, title, description, thumbnail_url, live_input_identifier, stream_token,
                        chat_disabled, stream_status, category, attribute, started_at, created_at, updated_at
                    )
                    SELECT
                        CAST(${rid} AS TEXT) AS room_id,
                        COALESCE(NULLIF(TRIM(${title}), ''), '') AS title,
                        NULLIF(TRIM(${description}), '') AS description,
                        NULLIF(TRIM(${thumbnail}), '') AS thumbnail_url,
                        NULLIF(TRIM(${liveInputId}), '') AS live_input_identifier,
                        NULLIF(TRIM(${src}), '') AS stream_token,
                        COALESCE(CAST(${chatDisabled} AS INTEGER), 0) AS chat_disabled,
                        CASE
                            WHEN ${streamStatus} IS NOT NULL AND TRIM(CAST(${streamStatus} AS TEXT)) != ''
                                THEN LOWER(TRIM(CAST(${streamStatus} AS TEXT)))
                            WHEN COALESCE(CAST(${legacyIsLive} AS INTEGER), 0) = 1 THEN 'ready'
                            ELSE 'disconnected'
                        END AS stream_status,
                        NULLIF(TRIM(${category}), '') AS category,
                        NULLIF(TRIM(${attribute}), '') AS attribute,
                        CAST((${startedAt}) AS INTEGER) AS started_at,
                        CAST((${createdAt}) AS INTEGER) AS created_at,
                        CAST((${updatedAt}) AS INTEGER) AS updated_at
                    FROM room_streams__src_bak rs
                    WHERE ${rid} IS NOT NULL AND TRIM(CAST(${rid} AS TEXT)) != '';
                `)
            }
            await db.exec(`DROP TABLE IF EXISTS room_streams__src_bak;`)
        }

        await db.exec(`DROP TABLE IF EXISTS room_pinned_message;`)
        await db.exec(`
            CREATE TABLE room_pinned_message (
                room_id TEXT PRIMARY KEY,
                message_id TEXT NOT NULL,
                user_id TEXT NOT NULL,
                contents TEXT NOT NULL,
                created_at INTEGER NOT NULL,
                FOREIGN KEY(room_id) REFERENCES users(user_id) ON DELETE CASCADE,
                FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE SET NULL
            );
        `)

        if (hasLive?.name) {
            const lsCols = await db.all<{ name: string }>(`PRAGMA table_info(live_streams);`)
            const lsNames = new Set((lsCols ?? []).map((c) => c.name))
            if (lsNames.has('pinnedMessageId')) {
                await db.exec(`
                    INSERT INTO room_pinned_message (room_id, message_id, user_id, contents, created_at)
                    SELECT
                        ls.roomId AS room_id,
                        m.id AS message_id,
                        COALESCE(m.userId, ls.userId) AS user_id,
                        m.text AS contents,
                        CAST((
                            CASE
                                WHEN CAST(m.createdAtMs AS INTEGER) > 1000000000000 THEN CAST(m.createdAtMs AS INTEGER) / 1000
                                WHEN CAST(m.createdAtMs AS INTEGER) > 0 THEN CAST(m.createdAtMs AS INTEGER)
                                WHEN m.createdAt IS NOT NULL AND TRIM(CAST(m.createdAt AS TEXT)) != ''
                                    AND CAST(strftime('%s', m.createdAt) AS INTEGER) > 0
                                    THEN CAST(strftime('%s', m.createdAt) AS INTEGER)
                                ELSE ${UNIX_NOW}
                            END
                        ) AS INTEGER) AS created_at
                    FROM live_streams ls
                    INNER JOIN chat_messages m ON m.id = ls.pinnedMessageId
                    WHERE ls.pinnedMessageId IS NOT NULL AND TRIM(CAST(ls.pinnedMessageId AS TEXT)) != '';
                `)
            }
        }

        await db.exec(`
            CREATE TABLE room_stream_likes__mrs (
                room_id TEXT NOT NULL,
                user_id TEXT NOT NULL,
                created_at INTEGER NOT NULL,
                PRIMARY KEY (room_id, user_id),
                FOREIGN KEY(room_id) REFERENCES users(user_id) ON DELETE CASCADE,
                FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE
            );
        `)

        const hasPreRoomStreamLikes = await db.get<{ name: string }>(
            `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'room_stream_likes' LIMIT 1`
        )
        if (hasPreRoomStreamLikes?.name) {
            const rslCols = await db.all<{ name: string }>(`PRAGMA table_info(room_stream_likes);`)
            const rslNames = new Set((rslCols ?? []).map((c) => c.name))
            if (rslNames.has('user_id') && rslNames.has('room_id') && rslNames.has('created_at')) {
                await db.exec(`
                    INSERT OR IGNORE INTO room_stream_likes__mrs (room_id, user_id, created_at)
                    SELECT room_id, user_id, created_at FROM room_stream_likes;
                `)
            }
        }

        const hasLikes = await db.get<{ name: string }>(
            `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'live_stream_likes' LIMIT 1`
        )
        if (hasLikes?.name) {
            const lkCols = await db.all<{ name: string }>(`PRAGMA table_info(live_stream_likes);`)
            const lkNames = new Set((lkCols ?? []).map((c) => c.name))
            if (lkNames.has('user_id') && lkNames.has('room_id')) {
                await db.exec(`
                    INSERT OR IGNORE INTO room_stream_likes__mrs (room_id, user_id, created_at)
                    SELECT
                        room_id,
                        user_id,
                        CAST((
                            CASE
                                WHEN CAST(created_at AS INTEGER) > 1000000000000 THEN CAST(created_at AS INTEGER) / 1000
                                WHEN CAST(created_at AS INTEGER) > 0 THEN CAST(created_at AS INTEGER)
                                WHEN created_at IS NOT NULL AND TRIM(CAST(created_at AS TEXT)) != ''
                                    AND CAST(strftime('%s', created_at) AS INTEGER) > 0
                                    THEN CAST(strftime('%s', created_at) AS INTEGER)
                                ELSE ${UNIX_NOW}
                            END
                        ) AS INTEGER) AS created_at
                    FROM live_stream_likes;
                `)
            }
        }

        const hasRoomLikesLegacy = await db.get<{ name: string }>(
            `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'room_likes' LIMIT 1`
        )
        if (hasRoomLikesLegacy?.name) {
            const rlCols = await db.all<{ name: string }>(`PRAGMA table_info(room_likes);`)
            const rlNames = new Set((rlCols ?? []).map((c) => c.name))
            if (rlNames.has('userId') && rlNames.has('roomId')) {
                await db.exec(`
                    INSERT OR IGNORE INTO room_stream_likes__mrs (room_id, user_id, created_at)
                    SELECT
                        roomId AS room_id,
                        userId AS user_id,
                        CAST((
                            CASE
                                WHEN CAST(createdAt AS INTEGER) > 1000000000000 THEN CAST(createdAt AS INTEGER) / 1000
                                WHEN CAST(createdAt AS INTEGER) > 0 THEN CAST(createdAt AS INTEGER)
                                WHEN createdAt IS NOT NULL AND TRIM(CAST(createdAt AS TEXT)) != ''
                                    AND CAST(strftime('%s', createdAt) AS INTEGER) > 0
                                    THEN CAST(strftime('%s', createdAt) AS INTEGER)
                                ELSE ${UNIX_NOW}
                            END
                        ) AS INTEGER) AS created_at
                    FROM room_likes;
                `)
            }
        }

        await db.exec(`
            CREATE TABLE room_bans__mrs (
                room_id TEXT NOT NULL,
                user_id TEXT NOT NULL,
                banned_by TEXT NOT NULL,
                reason TEXT,
                duration_ms INTEGER,
                banned_at INTEGER NOT NULL,
                expires_at INTEGER,
                PRIMARY KEY (room_id, user_id),
                FOREIGN KEY(room_id) REFERENCES room_streams(room_id) ON DELETE CASCADE,
                FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE,
                FOREIGN KEY(banned_by) REFERENCES users(user_id) ON DELETE SET NULL
            );
        `)
        await db.exec(`
            INSERT INTO room_bans__mrs
            SELECT room_id, user_id, banned_by, reason, duration_ms, banned_at, expires_at FROM room_bans;
        `)

        await db.exec(`
            CREATE TABLE room_mods__mrs (
                room_id TEXT NOT NULL,
                user_id TEXT NOT NULL,
                added_by TEXT NOT NULL,
                added_at INTEGER NOT NULL,
                PRIMARY KEY (room_id, user_id),
                FOREIGN KEY(room_id) REFERENCES users(user_id) ON DELETE CASCADE,
                FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE,
                FOREIGN KEY(added_by) REFERENCES users(user_id) ON DELETE SET NULL
            );
        `)
        await db.exec(`
            INSERT INTO room_mods__mrs
            SELECT room_id, user_id, added_by, added_at FROM room_mods;
        `)

        await db.exec(`DROP TABLE IF EXISTS chat_messages;`)

        await db.exec(`DROP TABLE IF EXISTS live_stream_likes;`)
        await db.exec(`DROP TABLE IF EXISTS room_stream_likes;`)
        await db.exec(`ALTER TABLE room_stream_likes__mrs RENAME TO room_stream_likes;`)

        await db.exec(`DROP TABLE IF EXISTS room_likes;`)

        await db.exec(`DROP TABLE IF EXISTS room_bans;`)
        await db.exec(`ALTER TABLE room_bans__mrs RENAME TO room_bans;`)

        await db.exec(`DROP TABLE IF EXISTS room_mods;`)
        await db.exec(`ALTER TABLE room_mods__mrs RENAME TO room_mods;`)

        if (hasLive?.name) {
            await db.exec(`DROP TABLE IF EXISTS live_streams;`)
        }

        await db.exec(`COMMIT;`)
        console.log('[gp-live] migrateRoomStreamsTableSchema: 完成')
    } catch (e) {
        try {
            await db.exec(`ROLLBACK;`)
        } catch {
            // ignore
        }
        throw e
    } finally {
        await db.exec(`PRAGMA foreign_keys = ON;`)
    }
}

const ROOM_STREAM_LIKES_FINAL_COLUMNS = new Set(['room_id', 'user_id', 'created_at'])

function roomStreamLikesTableIsFinal(names: Set<string>): boolean {
    if (![...ROOM_STREAM_LIKES_FINAL_COLUMNS].every((c) => names.has(c))) return false
    if (names.size !== ROOM_STREAM_LIKES_FINAL_COLUMNS.size) return false
    if (names.has('client_id')) return false
    return true
}

/**
 * live_stream_likes（room_id + client_id）→ room_stream_likes（room_id + user_id + created_at，Unix 秒）。
 * 僅有 client_id 的舊愛心無法對應帳號，遷移時不予保留。
 */
export async function migrateRoomStreamLikesTableSchema(db: Db) {
    const hasOld = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'live_stream_likes' LIMIT 1`
    )
    const hasNew = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'room_stream_likes' LIMIT 1`
    )

    let newColNames = new Set<string>()
    if (hasNew?.name) {
        const rc = await db.all<{ name: string }>(`PRAGMA table_info(room_stream_likes);`)
        newColNames = new Set((rc ?? []).map((c) => c.name))
    }

    if (
        hasNew?.name &&
        roomStreamLikesTableIsFinal(newColNames) &&
        (await tableRoomIdReferencesUsers(db, 'room_stream_likes'))
    ) {
        if (hasOld?.name) {
            await db.exec(`DROP TABLE IF EXISTS live_stream_likes;`)
            console.log('[gp-live] migrateRoomStreamLikesTableSchema: 已移除舊表 live_stream_likes')
        }
        return
    }

    console.log('[gp-live] migrateRoomStreamLikesTableSchema: 重建 room_stream_likes…')

    await db.exec(`PRAGMA foreign_keys = OFF;`)
    await db.exec(`BEGIN;`)
    try {
        await db.exec(`DROP TABLE IF EXISTS room_stream_likes__rsl_mig;`)
        await db.exec(`
            CREATE TABLE room_stream_likes__rsl_mig (
                room_id TEXT NOT NULL,
                user_id TEXT NOT NULL,
                created_at INTEGER NOT NULL,
                PRIMARY KEY (room_id, user_id),
                FOREIGN KEY(room_id) REFERENCES users(user_id) ON DELETE CASCADE,
                FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE
            );
        `)

        if (hasNew?.name && newColNames.has('user_id') && newColNames.has('room_id') && newColNames.has('created_at')) {
            await db.exec(`
                INSERT OR IGNORE INTO room_stream_likes__rsl_mig (room_id, user_id, created_at)
                SELECT room_id, user_id, created_at FROM room_stream_likes;
            `)
        }

        if (hasOld?.name) {
            const lkCols = await db.all<{ name: string }>(`PRAGMA table_info(live_stream_likes);`)
            const lkNames = new Set((lkCols ?? []).map((c) => c.name))
            if (lkNames.has('user_id') && lkNames.has('room_id')) {
                await db.exec(`
                    INSERT OR IGNORE INTO room_stream_likes__rsl_mig (room_id, user_id, created_at)
                    SELECT
                        room_id,
                        user_id,
                        CAST((
                            CASE
                                WHEN CAST(created_at AS INTEGER) > 1000000000000 THEN CAST(created_at AS INTEGER) / 1000
                                WHEN CAST(created_at AS INTEGER) > 0 THEN CAST(created_at AS INTEGER)
                                WHEN created_at IS NOT NULL AND TRIM(CAST(created_at AS TEXT)) != ''
                                    AND CAST(strftime('%s', created_at) AS INTEGER) > 0
                                    THEN CAST(strftime('%s', created_at) AS INTEGER)
                                ELSE ${UNIX_NOW}
                            END
                        ) AS INTEGER) AS created_at
                    FROM live_stream_likes;
                `)
            }
        }

        await db.exec(`DROP TABLE IF EXISTS room_stream_likes;`)
        await db.exec(`DROP TABLE IF EXISTS live_stream_likes;`)
        await db.exec(`ALTER TABLE room_stream_likes__rsl_mig RENAME TO room_stream_likes;`)

        await db.exec(`COMMIT;`)
        console.log('[gp-live] migrateRoomStreamLikesTableSchema: 完成')
    } catch (e) {
        try {
            await db.exec(`ROLLBACK;`)
        } catch {
            // ignore
        }
        throw e
    } finally {
        await db.exec(`PRAGMA foreign_keys = ON;`)
    }
}

/**
 * 聊天改為僅 socket 傳遞、不入庫：刪除舊表 chat_messages（若存在）。
 */
export async function migrateDropChatMessagesTableSchema(db: Db) {
    const row = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'chat_messages' LIMIT 1`
    )
    if (!row?.name) return
    console.log('[gp-live] migrateDropChatMessagesTableSchema: 刪除 chat_messages…')
    await db.exec(`PRAGMA foreign_keys = OFF;`)
    try {
        await db.exec(`DROP TABLE IF EXISTS chat_messages;`)
    } finally {
        await db.exec(`PRAGMA foreign_keys = ON;`)
    }
}

const CATCH_LIKES_FINAL_COLUMNS = new Set(['catch_id', 'user_id', 'created_at'])

function catchLikesTableIsFinal(names: Set<string>): boolean {
    if (![...CATCH_LIKES_FINAL_COLUMNS].every((c) => names.has(c))) return false
    if (names.size !== CATCH_LIKES_FINAL_COLUMNS.size) return false
    if (names.has('clientId') || names.has('catchId') || names.has('createdAt')) return false
    return true
}

/**
 * catch 主表：id/userId/thumb/src/… → catch_id、user_id、thumbnail_url、video_stream_url、is_show、created_at/updated_at（Unix 秒）。
 * 重建時一併備份並還原 catch_likes、catch_comments、catch_comment_likes，並將外鍵改為 REFERENCES catch(catch_id)。
 */
export async function migrateCatchTableSchema(db: Db) {
    const hasCatch = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'catch' LIMIT 1`
    )
    if (!hasCatch?.name) return

    const cols = await db.all<{ name: string }>(`PRAGMA table_info(catch);`)
    const names = new Set((cols ?? []).map((c) => c.name))
    const isFinal =
        names.has('catch_id') &&
        names.has('user_id') &&
        names.has('thumbnail_url') &&
        names.has('video_stream_url') &&
        names.has('is_show') &&
        names.has('created_at') &&
        names.has('updated_at') &&
        !names.has('id')
    if (isFinal) return

    console.log('[gp-live] migrateCatchTableSchema: 重建 catch 及關聯的 likes / 留言表…')

    await db.exec(`PRAGMA foreign_keys = OFF;`)
    await db.exec(`BEGIN;`)
    try {
        await db.exec(`DROP TABLE IF EXISTS __gp_mig_catch_row;`)
        await db.exec(`DROP TABLE IF EXISTS __gp_mig_catch_likes;`)
        await db.exec(`DROP TABLE IF EXISTS __gp_mig_catch_comments;`)
        await db.exec(`DROP TABLE IF EXISTS __gp_mig_catch_comment_likes;`)

        await db.exec(`CREATE TABLE __gp_mig_catch_row AS SELECT * FROM catch;`)

        const hasLikes = await db.get<{ name: string }>(
            `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'catch_likes' LIMIT 1`
        )
        if (hasLikes?.name) {
            await db.exec(`CREATE TABLE __gp_mig_catch_likes AS SELECT * FROM catch_likes;`)
        }

        const hasCc = await db.get<{ name: string }>(
            `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'catch_comments' LIMIT 1`
        )
        if (hasCc?.name) {
            await db.exec(`CREATE TABLE __gp_mig_catch_comments AS SELECT * FROM catch_comments;`)
        }

        const hasCcl = await db.get<{ name: string }>(
            `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'catch_comment_likes' LIMIT 1`
        )
        if (hasCcl?.name) {
            await db.exec(`CREATE TABLE __gp_mig_catch_comment_likes AS SELECT * FROM catch_comment_likes;`)
        }

        await db.exec(`DROP TABLE IF EXISTS catch_comment_likes;`)
        await db.exec(`DROP TABLE IF EXISTS catch_comments;`)
        await db.exec(`DROP TABLE IF EXISTS catch_likes;`)
        await db.exec(`DROP TABLE IF EXISTS catch;`)

        await db.exec(`
            CREATE TABLE catch (
                catch_id TEXT PRIMARY KEY,
                user_id TEXT NOT NULL,
                title TEXT NOT NULL,
                description TEXT,
                thumbnail_url TEXT,
                video_stream_url TEXT,
                tags TEXT,
                is_show INTEGER NOT NULL DEFAULT 1,
                created_at INTEGER NOT NULL DEFAULT (${UNIX_NOW}),
                updated_at INTEGER NOT NULL DEFAULT (${UNIX_NOW}),
                FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE
            );
        `)

        const bakCols = await db.all<{ name: string }>(`PRAGMA table_info(__gp_mig_catch_row);`)
        const bakNames = new Set((bakCols ?? []).map((c) => c.name))

        if (bakNames.has('catch_id')) {
            await db.exec(`
                INSERT INTO catch (catch_id, user_id, title, description, thumbnail_url, video_stream_url, tags, is_show, created_at, updated_at)
                SELECT
                    catch_id,
                    user_id,
                    COALESCE(title, ''),
                    description,
                    thumbnail_url,
                    video_stream_url,
                    tags,
                    COALESCE(is_show, 1),
                    CAST((
                        CASE
                            WHEN CAST(created_at AS INTEGER) > 1000000000000 THEN CAST(created_at AS INTEGER) / 1000
                            WHEN CAST(created_at AS INTEGER) > 0 THEN CAST(created_at AS INTEGER)
                            WHEN created_at IS NOT NULL AND TRIM(CAST(created_at AS TEXT)) != ''
                                AND CAST(strftime('%s', created_at) AS INTEGER) > 0
                                THEN CAST(strftime('%s', created_at) AS INTEGER)
                            ELSE ${UNIX_NOW}
                        END
                    ) AS INTEGER) AS created_at,
                    CAST((
                        CASE
                            WHEN CAST(updated_at AS INTEGER) > 1000000000000 THEN CAST(updated_at AS INTEGER) / 1000
                            WHEN CAST(updated_at AS INTEGER) > 0 THEN CAST(updated_at AS INTEGER)
                            WHEN updated_at IS NOT NULL AND TRIM(CAST(updated_at AS TEXT)) != ''
                                AND CAST(strftime('%s', updated_at) AS INTEGER) > 0
                                THEN CAST(strftime('%s', updated_at) AS INTEGER)
                            ELSE ${UNIX_NOW}
                        END
                    ) AS INTEGER) AS updated_at
                FROM __gp_mig_catch_row;
            `)
        } else {
            await db.exec(`
                INSERT INTO catch (catch_id, user_id, title, description, thumbnail_url, video_stream_url, tags, is_show, created_at, updated_at)
                SELECT
                    id AS catch_id,
                    userId AS user_id,
                    COALESCE(title, ''),
                    description,
                    thumb AS thumbnail_url,
                    src AS video_stream_url,
                    tags,
                    1 AS is_show,
                    CAST((
                        CASE
                            WHEN CAST(createdAt AS INTEGER) > 1000000000000 THEN CAST(createdAt AS INTEGER) / 1000
                            WHEN CAST(createdAt AS INTEGER) > 0 THEN CAST(createdAt AS INTEGER)
                            WHEN createdAt IS NOT NULL AND TRIM(CAST(createdAt AS TEXT)) != ''
                                AND CAST(strftime('%s', createdAt) AS INTEGER) > 0
                                THEN CAST(strftime('%s', createdAt) AS INTEGER)
                            ELSE ${UNIX_NOW}
                        END
                    ) AS INTEGER) AS created_at,
                    CAST((
                        CASE
                            WHEN CAST(updatedAt AS INTEGER) > 1000000000000 THEN CAST(updatedAt AS INTEGER) / 1000
                            WHEN CAST(updatedAt AS INTEGER) > 0 THEN CAST(updatedAt AS INTEGER)
                            WHEN updatedAt IS NOT NULL AND TRIM(CAST(updatedAt AS TEXT)) != ''
                                AND CAST(strftime('%s', updatedAt) AS INTEGER) > 0
                                THEN CAST(strftime('%s', updatedAt) AS INTEGER)
                            ELSE ${UNIX_NOW}
                        END
                    ) AS INTEGER) AS updated_at
                FROM __gp_mig_catch_row;
            `)
        }

        await db.exec(`
            CREATE TABLE catch_likes (
                catch_id TEXT NOT NULL,
                user_id TEXT NOT NULL,
                created_at INTEGER NOT NULL DEFAULT (${UNIX_NOW}),
                PRIMARY KEY (catch_id, user_id),
                FOREIGN KEY(catch_id) REFERENCES catch(catch_id) ON DELETE CASCADE,
                FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE
            );
        `)

        const likesBak = await db.get<{ name: string }>(
            `SELECT name FROM sqlite_master WHERE type = 'table' AND name = '__gp_mig_catch_likes' LIMIT 1`
        )
        if (likesBak?.name) {
            const lkCols = await db.all<{ name: string }>(`PRAGMA table_info(__gp_mig_catch_likes);`)
            const lkNames = new Set((lkCols ?? []).map((c) => c.name))
            if (lkNames.has('catch_id') && lkNames.has('user_id')) {
                await db.exec(`
                    INSERT OR IGNORE INTO catch_likes (catch_id, user_id, created_at)
                    SELECT
                        catch_id,
                        user_id,
                        CAST((
                            CASE
                                WHEN CAST(created_at AS INTEGER) > 1000000000000 THEN CAST(created_at AS INTEGER) / 1000
                                WHEN CAST(created_at AS INTEGER) > 0 THEN CAST(created_at AS INTEGER)
                                WHEN created_at IS NOT NULL AND TRIM(CAST(created_at AS TEXT)) != ''
                                    AND CAST(strftime('%s', created_at) AS INTEGER) > 0
                                    THEN CAST(strftime('%s', created_at) AS INTEGER)
                                ELSE ${UNIX_NOW}
                            END
                        ) AS INTEGER) AS created_at
                    FROM __gp_mig_catch_likes;
                `)
            } else if (lkNames.has('catchId') && lkNames.has('userId')) {
                await db.exec(`
                    INSERT OR IGNORE INTO catch_likes (catch_id, user_id, created_at)
                    SELECT
                        catchId AS catch_id,
                        userId AS user_id,
                        CAST((
                            CASE
                                WHEN CAST(createdAt AS INTEGER) > 1000000000000 THEN CAST(createdAt AS INTEGER) / 1000
                                WHEN CAST(createdAt AS INTEGER) > 0 THEN CAST(createdAt AS INTEGER)
                                WHEN createdAt IS NOT NULL AND TRIM(CAST(createdAt AS TEXT)) != ''
                                    AND CAST(strftime('%s', createdAt) AS INTEGER) > 0
                                    THEN CAST(strftime('%s', createdAt) AS INTEGER)
                                ELSE ${UNIX_NOW}
                            END
                        ) AS INTEGER) AS created_at
                    FROM __gp_mig_catch_likes;
                `)
            }
        }

        await db.exec(`
            CREATE TABLE catch_comments (
                comment_id TEXT PRIMARY KEY,
                catch_id TEXT NOT NULL,
                user_id TEXT NOT NULL,
                contents TEXT NOT NULL,
                parent_id TEXT,
                reply_to TEXT,
                is_pinned INTEGER NOT NULL DEFAULT 0,
                created_at INTEGER NOT NULL,
                edited_at INTEGER,
                FOREIGN KEY(catch_id) REFERENCES catch(catch_id) ON DELETE CASCADE,
                FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE,
                FOREIGN KEY(parent_id) REFERENCES catch_comments(comment_id) ON DELETE CASCADE
            );
        `)

        const ccBak = await db.get<{ name: string }>(
            `SELECT name FROM sqlite_master WHERE type = 'table' AND name = '__gp_mig_catch_comments' LIMIT 1`
        )
        if (ccBak?.name) {
            const cbCols = await db.all<{ name: string }>(`PRAGMA table_info(__gp_mig_catch_comments);`)
            const cbNames = new Set((cbCols ?? []).map((c) => c.name))
            if (cbNames.has('comment_id')) {
                await db.exec(`
                    INSERT INTO catch_comments (
                        comment_id, catch_id, user_id, contents, parent_id, reply_to, is_pinned, created_at, edited_at
                    )
                    SELECT
                        comment_id,
                        catch_id,
                        user_id,
                        contents,
                        parent_id,
                        reply_to,
                        COALESCE(is_pinned, 0),
                        CAST((
                            CASE
                                WHEN CAST(created_at AS INTEGER) > 1000000000000 THEN CAST(created_at AS INTEGER) / 1000
                                WHEN CAST(created_at AS INTEGER) > 0 THEN CAST(created_at AS INTEGER)
                                ELSE ${UNIX_NOW}
                            END
                        ) AS INTEGER) AS created_at,
                        CASE
                            WHEN edited_at IS NULL OR TRIM(CAST(edited_at AS TEXT)) = '' THEN NULL
                            WHEN CAST(edited_at AS INTEGER) > 1000000000000 THEN CAST(edited_at AS INTEGER) / 1000
                            WHEN CAST(edited_at AS INTEGER) > 0 THEN CAST(edited_at AS INTEGER)
                            WHEN CAST(strftime('%s', edited_at) AS INTEGER) > 0 THEN CAST(strftime('%s', edited_at) AS INTEGER)
                            ELSE NULL
                        END AS edited_at
                    FROM __gp_mig_catch_comments;
                `)
            } else {
                const hasIsDeleted = cbNames.has('isDeleted')
                const whereSql = hasIsDeleted ? 'WHERE COALESCE(isDeleted, 0) = 0' : ''
                await db.exec(`
                    INSERT INTO catch_comments (
                        comment_id, catch_id, user_id, contents, parent_id, reply_to, is_pinned, created_at, edited_at
                    )
                    SELECT
                        id AS comment_id,
                        catchId AS catch_id,
                        userId AS user_id,
                        content AS contents,
                        parentId AS parent_id,
                        replyTo AS reply_to,
                        COALESCE(isPinned, 0) AS is_pinned,
                        CAST((
                            CASE
                                WHEN CAST(createdAtMs AS INTEGER) > 1000000000000 THEN CAST(createdAtMs AS INTEGER) / 1000
                                WHEN CAST(createdAtMs AS INTEGER) > 0 THEN CAST(createdAtMs AS INTEGER)
                                WHEN createdAt IS NOT NULL AND TRIM(CAST(createdAt AS TEXT)) != ''
                                    AND CAST(strftime('%s', createdAt) AS INTEGER) > 0
                                    THEN CAST(strftime('%s', createdAt) AS INTEGER)
                                ELSE ${UNIX_NOW}
                            END
                        ) AS INTEGER) AS created_at,
                        CASE
                            WHEN editedAt IS NULL OR TRIM(CAST(editedAt AS TEXT)) = '' THEN NULL
                            WHEN CAST(editedAt AS INTEGER) > 1000000000000 THEN CAST(editedAt AS INTEGER) / 1000
                            WHEN CAST(editedAt AS INTEGER) > 0 THEN CAST(editedAt AS INTEGER)
                            WHEN CAST(strftime('%s', editedAt) AS INTEGER) > 0 THEN CAST(strftime('%s', editedAt) AS INTEGER)
                            ELSE NULL
                        END AS edited_at
                    FROM __gp_mig_catch_comments
                    ${whereSql};
                `)
            }
        }

        await db.exec(`
            CREATE TABLE catch_comment_likes (
                comment_id TEXT NOT NULL,
                user_id TEXT NOT NULL,
                created_at INTEGER NOT NULL DEFAULT (${UNIX_NOW}),
                PRIMARY KEY (comment_id, user_id),
                FOREIGN KEY(comment_id) REFERENCES catch_comments(comment_id) ON DELETE CASCADE,
                FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE
            );
        `)

        const cclBak = await db.get<{ name: string }>(
            `SELECT name FROM sqlite_master WHERE type = 'table' AND name = '__gp_mig_catch_comment_likes' LIMIT 1`
        )
        if (cclBak?.name) {
            const lbCols = await db.all<{ name: string }>(`PRAGMA table_info(__gp_mig_catch_comment_likes);`)
            const lbNames = new Set((lbCols ?? []).map((c) => c.name))
            if (lbNames.has('comment_id') && lbNames.has('user_id')) {
                await db.exec(`
                    INSERT OR IGNORE INTO catch_comment_likes (comment_id, user_id, created_at)
                    SELECT
                        comment_id,
                        user_id,
                        CAST((
                            CASE
                                WHEN CAST(created_at AS INTEGER) > 1000000000000 THEN CAST(created_at AS INTEGER) / 1000
                                WHEN CAST(created_at AS INTEGER) > 0 THEN CAST(created_at AS INTEGER)
                                WHEN created_at IS NOT NULL AND TRIM(CAST(created_at AS TEXT)) != ''
                                    AND CAST(strftime('%s', created_at) AS INTEGER) > 0
                                    THEN CAST(strftime('%s', created_at) AS INTEGER)
                                ELSE ${UNIX_NOW}
                            END
                        ) AS INTEGER) AS created_at
                    FROM __gp_mig_catch_comment_likes;
                `)
            } else if (lbNames.has('commentId') && lbNames.has('userId')) {
                await db.exec(`
                    INSERT OR IGNORE INTO catch_comment_likes (comment_id, user_id, created_at)
                    SELECT
                        commentId AS comment_id,
                        userId AS user_id,
                        CAST((
                            CASE
                                WHEN CAST(createdAt AS INTEGER) > 1000000000000 THEN CAST(createdAt AS INTEGER) / 1000
                                WHEN CAST(createdAt AS INTEGER) > 0 THEN CAST(createdAt AS INTEGER)
                                WHEN createdAt IS NOT NULL AND TRIM(CAST(createdAt AS TEXT)) != ''
                                    AND CAST(strftime('%s', createdAt) AS INTEGER) > 0
                                    THEN CAST(strftime('%s', createdAt) AS INTEGER)
                                ELSE ${UNIX_NOW}
                            END
                        ) AS INTEGER) AS created_at
                    FROM __gp_mig_catch_comment_likes;
                `)
            }
        }

        await db.exec(`DROP TABLE IF EXISTS __gp_mig_catch_comment_likes;`)
        await db.exec(`DROP TABLE IF EXISTS __gp_mig_catch_comments;`)
        await db.exec(`DROP TABLE IF EXISTS __gp_mig_catch_likes;`)
        await db.exec(`DROP TABLE IF EXISTS __gp_mig_catch_row;`)

        await db.exec(`COMMIT;`)
        console.log('[gp-live] migrateCatchTableSchema: 完成')
    } catch (e) {
        try {
            await db.exec(`ROLLBACK;`)
        } catch {
            // ignore
        }
        throw e
    } finally {
        await db.exec(`PRAGMA foreign_keys = ON;`)
    }
}

/**
 * catch_likes：catchId+clientId+createdAt(text) → catch_id+user_id+created_at（Unix 秒）。
 * 僅 clientId 的舊資料無法對應帳號，遷移時不予保留。
 */
export async function migrateCatchLikesTableSchema(db: Db) {
    const hasLikes = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'catch_likes' LIMIT 1`
    )
    if (!hasLikes?.name) return

    const hasCatch = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'catch' LIMIT 1`
    )
    if (!hasCatch?.name) {
        await db.exec(`DROP TABLE IF EXISTS catch_likes;`)
        console.log('[gp-live] migrateCatchLikesTableSchema: 已刪除無主表之 catch_likes')
        return
    }

    const cols = await db.all<{ name: string }>(`PRAGMA table_info(catch_likes);`)
    const names = new Set((cols ?? []).map((c) => c.name))
    if (catchLikesTableIsFinal(names)) return

    console.log('[gp-live] migrateCatchLikesTableSchema: 重建 catch_likes…')

    await db.exec(`PRAGMA foreign_keys = OFF;`)
    await db.exec(`BEGIN;`)
    try {
        await db.exec(`DROP TABLE IF EXISTS catch_likes__mig;`)
        await db.exec(`
            CREATE TABLE catch_likes__mig (
                catch_id TEXT NOT NULL,
                user_id TEXT NOT NULL,
                created_at INTEGER NOT NULL,
                PRIMARY KEY (catch_id, user_id),
                FOREIGN KEY(catch_id) REFERENCES catch(catch_id) ON DELETE CASCADE,
                FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE
            );
        `)

        if (names.has('catch_id') && names.has('user_id')) {
            await db.exec(`
                INSERT OR IGNORE INTO catch_likes__mig (catch_id, user_id, created_at)
                SELECT
                    catch_id,
                    user_id,
                    CAST((
                        CASE
                            WHEN CAST(created_at AS INTEGER) > 1000000000000 THEN CAST(created_at AS INTEGER) / 1000
                            WHEN CAST(created_at AS INTEGER) > 0 THEN CAST(created_at AS INTEGER)
                            WHEN created_at IS NOT NULL AND TRIM(CAST(created_at AS TEXT)) != ''
                                AND CAST(strftime('%s', created_at) AS INTEGER) > 0
                                THEN CAST(strftime('%s', created_at) AS INTEGER)
                            ELSE ${UNIX_NOW}
                        END
                    ) AS INTEGER) AS created_at
                FROM catch_likes;
            `)
        } else if (names.has('catchId') && names.has('userId')) {
            await db.exec(`
                INSERT OR IGNORE INTO catch_likes__mig (catch_id, user_id, created_at)
                SELECT
                    catchId AS catch_id,
                    userId AS user_id,
                    CAST((
                        CASE
                            WHEN CAST(createdAt AS INTEGER) > 1000000000000 THEN CAST(createdAt AS INTEGER) / 1000
                            WHEN CAST(createdAt AS INTEGER) > 0 THEN CAST(createdAt AS INTEGER)
                            WHEN createdAt IS NOT NULL AND TRIM(CAST(createdAt AS TEXT)) != ''
                                AND CAST(strftime('%s', createdAt) AS INTEGER) > 0
                                THEN CAST(strftime('%s', createdAt) AS INTEGER)
                            ELSE ${UNIX_NOW}
                        END
                    ) AS INTEGER) AS created_at
                FROM catch_likes;
            `)
        }

        await db.exec(`DROP TABLE IF EXISTS catch_likes;`)
        await db.exec(`ALTER TABLE catch_likes__mig RENAME TO catch_likes;`)
        await db.exec(`COMMIT;`)
        console.log('[gp-live] migrateCatchLikesTableSchema: 完成')
    } catch (e) {
        try {
            await db.exec(`ROLLBACK;`)
        } catch {
            // ignore
        }
        throw e
    } finally {
        await db.exec(`PRAGMA foreign_keys = ON;`)
    }
}

/**
 * catch_comments：comment_id、catch_id、user_id、contents、parent_id、reply_to、is_pinned、created_at/edited_at（Unix 秒）；
 * catch_comment_likes：comment_id、user_id、created_at（秒）。愛心數由 likes 表統計，不再冗餘在留言表。
 * 舊 clientId 按讚不予保留。
 */
export async function migrateCatchCommentsAndLikesTableSchema(db: Db) {
    const hasCatch = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'catch' LIMIT 1`
    )
    if (!hasCatch?.name) return

    const hasCc = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'catch_comments' LIMIT 1`
    )
    if (!hasCc?.name) return

    const ccCols = await db.all<{ name: string }>(`PRAGMA table_info(catch_comments);`)
    const ccNames = new Set((ccCols ?? []).map((c) => c.name))
    const ccFinal =
        ccNames.has('comment_id') &&
        ccNames.has('catch_id') &&
        ccNames.has('contents') &&
        !ccNames.has('catchId') &&
        !ccNames.has('content')

    const hasCcl = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'catch_comment_likes' LIMIT 1`
    )
    let cclFinal = true
    if (hasCcl?.name) {
        const cclCols = await db.all<{ name: string }>(`PRAGMA table_info(catch_comment_likes);`)
        const cclNames = new Set((cclCols ?? []).map((c) => c.name))
        cclFinal =
            cclNames.has('comment_id') &&
            cclNames.has('user_id') &&
            cclNames.has('created_at') &&
            !cclNames.has('clientId') &&
            !cclNames.has('commentId')
    }

    if (ccFinal && cclFinal) return

    console.log(
        '[gp-live] migrateCatchCommentsAndLikesTableSchema: 重建 catch_comments / catch_comment_likes…'
    )

    await db.exec(`PRAGMA foreign_keys = OFF;`)

    if (ccFinal && !cclFinal && hasCcl?.name) {
        await db.exec(`BEGIN;`)
        try {
            await db.exec(`ALTER TABLE catch_comment_likes RENAME TO catch_comment_likes__cc_mig;`)
            await db.exec(`
                CREATE TABLE catch_comment_likes (
                    comment_id TEXT NOT NULL,
                    user_id TEXT NOT NULL,
                    created_at INTEGER NOT NULL,
                    PRIMARY KEY (comment_id, user_id),
                    FOREIGN KEY(comment_id) REFERENCES catch_comments(comment_id) ON DELETE CASCADE,
                    FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE
                );
            `)
            const lbCols = await db.all<{ name: string }>(`PRAGMA table_info(catch_comment_likes__cc_mig);`)
            const lbNames = new Set((lbCols ?? []).map((c) => c.name))
            if (lbNames.has('comment_id') && lbNames.has('user_id')) {
                await db.exec(`
                    INSERT OR IGNORE INTO catch_comment_likes (comment_id, user_id, created_at)
                    SELECT
                        comment_id,
                        user_id,
                        CAST((
                            CASE
                                WHEN CAST(created_at AS INTEGER) > 1000000000000 THEN CAST(created_at AS INTEGER) / 1000
                                WHEN CAST(created_at AS INTEGER) > 0 THEN CAST(created_at AS INTEGER)
                                WHEN created_at IS NOT NULL AND TRIM(CAST(created_at AS TEXT)) != ''
                                    AND CAST(strftime('%s', created_at) AS INTEGER) > 0
                                    THEN CAST(strftime('%s', created_at) AS INTEGER)
                                ELSE ${UNIX_NOW}
                            END
                        ) AS INTEGER) AS created_at
                    FROM catch_comment_likes__cc_mig;
                `)
            } else if (lbNames.has('commentId') && lbNames.has('userId')) {
                await db.exec(`
                    INSERT OR IGNORE INTO catch_comment_likes (comment_id, user_id, created_at)
                    SELECT
                        commentId AS comment_id,
                        userId AS user_id,
                        CAST((
                            CASE
                                WHEN CAST(createdAt AS INTEGER) > 1000000000000 THEN CAST(createdAt AS INTEGER) / 1000
                                WHEN CAST(createdAt AS INTEGER) > 0 THEN CAST(createdAt AS INTEGER)
                                WHEN createdAt IS NOT NULL AND TRIM(CAST(createdAt AS TEXT)) != ''
                                    AND CAST(strftime('%s', createdAt) AS INTEGER) > 0
                                    THEN CAST(strftime('%s', createdAt) AS INTEGER)
                                ELSE ${UNIX_NOW}
                            END
                        ) AS INTEGER) AS created_at
                    FROM catch_comment_likes__cc_mig;
                `)
            }
            await db.exec(`DROP TABLE IF EXISTS catch_comment_likes__cc_mig;`)
            await db.exec(`COMMIT;`)
            console.log('[gp-live] migrateCatchCommentsAndLikesTableSchema: 完成（僅留言讚表）')
        } catch (e) {
            try {
                await db.exec(`ROLLBACK;`)
            } catch {
                // ignore
            }
            throw e
        } finally {
            await db.exec(`PRAGMA foreign_keys = ON;`)
        }
        return
    }

    await db.exec(`BEGIN;`)
    try {
        await db.exec(`DROP TABLE IF EXISTS catch_comment_likes__cc_mig;`)
        await db.exec(`DROP TABLE IF EXISTS catch_comments__cc_mig;`)

        if (hasCcl?.name) {
            await db.exec(`ALTER TABLE catch_comment_likes RENAME TO catch_comment_likes__cc_mig;`)
        }
        await db.exec(`ALTER TABLE catch_comments RENAME TO catch_comments__cc_mig;`)

        await db.exec(`
            CREATE TABLE catch_comments (
                comment_id TEXT PRIMARY KEY,
                catch_id TEXT NOT NULL,
                user_id TEXT NOT NULL,
                contents TEXT NOT NULL,
                parent_id TEXT,
                reply_to TEXT,
                is_pinned INTEGER NOT NULL DEFAULT 0,
                created_at INTEGER NOT NULL,
                edited_at INTEGER,
                FOREIGN KEY(catch_id) REFERENCES catch(catch_id) ON DELETE CASCADE,
                FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE,
                FOREIGN KEY(parent_id) REFERENCES catch_comments(comment_id) ON DELETE CASCADE
            );
        `)

        const bakCols = await db.all<{ name: string }>(`PRAGMA table_info(catch_comments__cc_mig);`)
        const bakNames = new Set((bakCols ?? []).map((c) => c.name))

        if (bakNames.has('comment_id')) {
            await db.exec(`
                INSERT INTO catch_comments (
                    comment_id, catch_id, user_id, contents, parent_id, reply_to, is_pinned, created_at, edited_at
                )
                SELECT
                    comment_id,
                    catch_id,
                    user_id,
                    contents,
                    parent_id,
                    reply_to,
                    COALESCE(is_pinned, 0),
                    CAST((
                        CASE
                            WHEN CAST(created_at AS INTEGER) > 1000000000000 THEN CAST(created_at AS INTEGER) / 1000
                            WHEN CAST(created_at AS INTEGER) > 0 THEN CAST(created_at AS INTEGER)
                            ELSE ${UNIX_NOW}
                        END
                    ) AS INTEGER) AS created_at,
                    CASE
                        WHEN edited_at IS NULL OR TRIM(CAST(edited_at AS TEXT)) = '' THEN NULL
                        WHEN CAST(edited_at AS INTEGER) > 1000000000000 THEN CAST(edited_at AS INTEGER) / 1000
                        WHEN CAST(edited_at AS INTEGER) > 0 THEN CAST(edited_at AS INTEGER)
                        WHEN CAST(strftime('%s', edited_at) AS INTEGER) > 0 THEN CAST(strftime('%s', edited_at) AS INTEGER)
                        ELSE NULL
                    END AS edited_at
                FROM catch_comments__cc_mig;
            `)
        } else {
            const hasIsDeleted = bakNames.has('isDeleted')
            const whereSql = hasIsDeleted ? 'WHERE COALESCE(isDeleted, 0) = 0' : ''
            await db.exec(`
                INSERT INTO catch_comments (
                    comment_id, catch_id, user_id, contents, parent_id, reply_to, is_pinned, created_at, edited_at
                )
                SELECT
                    id AS comment_id,
                    catchId AS catch_id,
                    userId AS user_id,
                    content AS contents,
                    parentId AS parent_id,
                    replyTo AS reply_to,
                    COALESCE(isPinned, 0) AS is_pinned,
                    CAST((
                        CASE
                            WHEN CAST(createdAtMs AS INTEGER) > 1000000000000 THEN CAST(createdAtMs AS INTEGER) / 1000
                            WHEN CAST(createdAtMs AS INTEGER) > 0 THEN CAST(createdAtMs AS INTEGER)
                            WHEN createdAt IS NOT NULL AND TRIM(CAST(createdAt AS TEXT)) != ''
                                AND CAST(strftime('%s', createdAt) AS INTEGER) > 0
                                THEN CAST(strftime('%s', createdAt) AS INTEGER)
                            ELSE ${UNIX_NOW}
                        END
                    ) AS INTEGER) AS created_at,
                    CASE
                        WHEN editedAt IS NULL OR TRIM(CAST(editedAt AS TEXT)) = '' THEN NULL
                        WHEN CAST(editedAt AS INTEGER) > 1000000000000 THEN CAST(editedAt AS INTEGER) / 1000
                        WHEN CAST(editedAt AS INTEGER) > 0 THEN CAST(editedAt AS INTEGER)
                        WHEN CAST(strftime('%s', editedAt) AS INTEGER) > 0 THEN CAST(strftime('%s', editedAt) AS INTEGER)
                        ELSE NULL
                    END AS edited_at
                FROM catch_comments__cc_mig
                ${whereSql};
            `)
        }

        await db.exec(`
            CREATE TABLE catch_comment_likes (
                comment_id TEXT NOT NULL,
                user_id TEXT NOT NULL,
                created_at INTEGER NOT NULL,
                PRIMARY KEY (comment_id, user_id),
                FOREIGN KEY(comment_id) REFERENCES catch_comments(comment_id) ON DELETE CASCADE,
                FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE
            );
        `)

        const hasLikesBak = await db.get<{ name: string }>(
            `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'catch_comment_likes__cc_mig' LIMIT 1`
        )
        if (hasLikesBak?.name) {
            const lbCols = await db.all<{ name: string }>(`PRAGMA table_info(catch_comment_likes__cc_mig);`)
            const lbNames = new Set((lbCols ?? []).map((c) => c.name))
            if (lbNames.has('comment_id') && lbNames.has('user_id')) {
                await db.exec(`
                    INSERT OR IGNORE INTO catch_comment_likes (comment_id, user_id, created_at)
                    SELECT
                        comment_id,
                        user_id,
                        CAST((
                            CASE
                                WHEN CAST(created_at AS INTEGER) > 1000000000000 THEN CAST(created_at AS INTEGER) / 1000
                                WHEN CAST(created_at AS INTEGER) > 0 THEN CAST(created_at AS INTEGER)
                                WHEN created_at IS NOT NULL AND TRIM(CAST(created_at AS TEXT)) != ''
                                    AND CAST(strftime('%s', created_at) AS INTEGER) > 0
                                    THEN CAST(strftime('%s', created_at) AS INTEGER)
                                ELSE ${UNIX_NOW}
                            END
                        ) AS INTEGER) AS created_at
                    FROM catch_comment_likes__cc_mig;
                `)
            } else if (lbNames.has('commentId') && lbNames.has('userId')) {
                await db.exec(`
                    INSERT OR IGNORE INTO catch_comment_likes (comment_id, user_id, created_at)
                    SELECT
                        commentId AS comment_id,
                        userId AS user_id,
                        CAST((
                            CASE
                                WHEN CAST(createdAt AS INTEGER) > 1000000000000 THEN CAST(createdAt AS INTEGER) / 1000
                                WHEN CAST(createdAt AS INTEGER) > 0 THEN CAST(createdAt AS INTEGER)
                                WHEN createdAt IS NOT NULL AND TRIM(CAST(createdAt AS TEXT)) != ''
                                    AND CAST(strftime('%s', createdAt) AS INTEGER) > 0
                                    THEN CAST(strftime('%s', createdAt) AS INTEGER)
                                ELSE ${UNIX_NOW}
                            END
                        ) AS INTEGER) AS created_at
                    FROM catch_comment_likes__cc_mig;
                `)
            }
            await db.exec(`DROP TABLE IF EXISTS catch_comment_likes__cc_mig;`)
        }

        await db.exec(`DROP TABLE IF EXISTS catch_comments__cc_mig;`)

        await db.exec(`COMMIT;`)
        console.log('[gp-live] migrateCatchCommentsAndLikesTableSchema: 完成')
    } catch (e) {
        try {
            await db.exec(`ROLLBACK;`)
        } catch {
            // ignore
        }
        throw e
    } finally {
        await db.exec(`PRAGMA foreign_keys = ON;`)
    }
}

const USER_NOTIFICATIONS_FINAL_COLUMNS = new Set([
    'notification_id',
    'user_id',
    'type',
    'title',
    'content',
    'redirect_url',
    'is_read',
    'created_at',
])

const USER_NOTIFICATIONS_LEGACY_COLUMN_NAMES = new Set([
    'id',
    'userId',
    'url',
    'isRead',
    'createdAt',
    'createdAtMs',
])

function userNotificationsTableNeedsRebuild(names: Set<string>): boolean {
    if (![...USER_NOTIFICATIONS_FINAL_COLUMNS].every((c) => names.has(c))) return true
    if (names.size !== USER_NOTIFICATIONS_FINAL_COLUMNS.size) return true
    for (const legacy of USER_NOTIFICATIONS_LEGACY_COLUMN_NAMES) {
        if (names.has(legacy)) return true
    }
    return false
}

/**
 * user_notifications：notification_id、user_id、redirect_url、is_read、created_at（Unix 秒）。
 */
export async function migrateUserNotificationsTableSchema(db: Db) {
    const has = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'user_notifications' LIMIT 1`
    )
    if (!has?.name) return

    const cols = await db.all<{ name: string }>(`PRAGMA table_info(user_notifications);`)
    if (!cols?.length) return
    const names = new Set(cols.map((c) => c.name))
    if (!userNotificationsTableNeedsRebuild(names)) return

    console.log(
        '[gp-live] migrateUserNotificationsTableSchema: 重建 user_notifications（snake_case + created_at 秒）…'
    )

    const nid = colExpr('g', ['notification_id', 'id'], names)
    const uid = colExpr('g', ['user_id', 'userId'], names)
    const typ = colExpr('g', ['type'], names)
    const ttl = colExpr('g', ['title'], names)
    const cnt = colExpr('g', ['content'], names)
    const rurl = colExpr('g', ['redirect_url', 'url'], names)

    let isReadSql = '0'
    if (names.has('is_read') && names.has('isRead')) {
        isReadSql = `CASE WHEN COALESCE(CAST(g.is_read AS INTEGER), CAST(g.isRead AS INTEGER), 0) != 0 THEN 1 ELSE 0 END`
    } else if (names.has('is_read')) {
        isReadSql = `CASE WHEN COALESCE(CAST(g.is_read AS INTEGER), 0) != 0 THEN 1 ELSE 0 END`
    } else if (names.has('isRead')) {
        isReadSql = `CASE WHEN COALESCE(CAST(g.isRead AS INTEGER), 0) != 0 THEN 1 ELSE 0 END`
    }

    const createdBranches: string[] = []
    if (names.has('created_at')) {
        createdBranches.push(
            `WHEN CAST(g.created_at AS INTEGER) > 1000000000000 THEN CAST(g.created_at AS INTEGER) / 1000`
        )
        createdBranches.push(`WHEN CAST(g.created_at AS INTEGER) > 0 THEN CAST(g.created_at AS INTEGER)`)
    }
    if (names.has('createdAtMs')) {
        createdBranches.push(
            `WHEN CAST(g.createdAtMs AS INTEGER) > 1000000000000 THEN CAST(g.createdAtMs AS INTEGER) / 1000`
        )
        createdBranches.push(`WHEN CAST(g.createdAtMs AS INTEGER) > 0 THEN CAST(g.createdAtMs AS INTEGER)`)
    }
    if (names.has('createdAt')) {
        createdBranches.push(
            `WHEN g.createdAt IS NOT NULL AND TRIM(CAST(g.createdAt AS TEXT)) != '' AND CAST(strftime('%s', g.createdAt) AS INTEGER) > 0 THEN CAST(strftime('%s', g.createdAt) AS INTEGER)`
        )
    }
    const createdAtExpr =
        createdBranches.length > 0 ? `CASE ${createdBranches.join(' ')} ELSE ${UNIX_NOW} END` : UNIX_NOW

    await db.exec(`DROP TABLE IF EXISTS user_notifications__rebuilt;`)
    await db.exec(`PRAGMA foreign_keys = OFF;`)
    await db.exec(`BEGIN;`)
    try {
        await db.exec(`
            CREATE TABLE user_notifications__rebuilt (
                notification_id TEXT PRIMARY KEY,
                user_id TEXT NOT NULL,
                type TEXT NOT NULL,
                title TEXT NOT NULL,
                content TEXT,
                redirect_url TEXT,
                is_read INTEGER NOT NULL DEFAULT 0,
                created_at INTEGER NOT NULL,
                FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE
            );
        `)
        await db.exec(`
            INSERT INTO user_notifications__rebuilt (
                notification_id, user_id, type, title, content, redirect_url, is_read, created_at
            )
            SELECT
                ${nid} AS notification_id,
                ${uid} AS user_id,
                ${typ} AS type,
                ${ttl} AS title,
                ${cnt} AS content,
                ${rurl} AS redirect_url,
                CAST((${isReadSql}) AS INTEGER) AS is_read,
                CAST((${createdAtExpr}) AS INTEGER) AS created_at
            FROM user_notifications g
            WHERE ${nid} IS NOT NULL AND TRIM(CAST(${nid} AS TEXT)) != ''
              AND ${uid} IS NOT NULL AND TRIM(CAST(${uid} AS TEXT)) != ''
              AND ${typ} IS NOT NULL AND TRIM(CAST(${typ} AS TEXT)) != ''
              AND ${ttl} IS NOT NULL AND TRIM(CAST(${ttl} AS TEXT)) != '';
        `)
        await db.exec(`DROP TABLE user_notifications;`)
        await db.exec(`ALTER TABLE user_notifications__rebuilt RENAME TO user_notifications;`)
        await db.exec(`COMMIT;`)
        console.log('[gp-live] migrateUserNotificationsTableSchema: 完成')
    } catch (e) {
        try {
            await db.exec(`ROLLBACK;`)
        } catch {
            // ignore
        }
        throw e
    } finally {
        await db.exec(`PRAGMA foreign_keys = ON;`)
    }
}

const USER_LOGIN_SETTINGS_FINAL_COLUMNS = new Set([
    'user_id',
    'login_notice',
    'foreign_login_block',
    'updated_at',
])

const USER_LOGIN_SETTINGS_LEGACY_COLUMN_NAMES = new Set([
    'userId',
    'loginNotice',
    'foreignLoginBlock',
    'updatedAt',
])

function userLoginSettingsTableNeedsRebuild(names: Set<string>): boolean {
    if (![...USER_LOGIN_SETTINGS_FINAL_COLUMNS].every((c) => names.has(c))) return true
    if (names.size !== USER_LOGIN_SETTINGS_FINAL_COLUMNS.size) return true
    for (const legacy of USER_LOGIN_SETTINGS_LEGACY_COLUMN_NAMES) {
        if (names.has(legacy)) return true
    }
    return false
}

/**
 * login_settings → user_login_settings；欄位 snake_case，updated_at 為 Unix 秒。
 */
export async function migrateUserLoginSettingsTableSchema(db: Db) {
    const hasOld = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'login_settings' LIMIT 1`
    )
    const hasNew = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'user_login_settings' LIMIT 1`
    )

    let newNames = new Set<string>()
    if (hasNew?.name) {
        const nc = await db.all<{ name: string }>(`PRAGMA table_info(user_login_settings);`)
        newNames = new Set((nc ?? []).map((c) => c.name))
    }
    const newOk = hasNew?.name && !userLoginSettingsTableNeedsRebuild(newNames)

    if (newOk && !hasOld?.name) return

    console.log('[gp-live] migrateUserLoginSettingsTableSchema: login_settings → user_login_settings…')

    await db.exec(`PRAGMA foreign_keys = OFF;`)
    await db.exec(`BEGIN;`)
    try {
        await db.exec(`DROP TABLE IF EXISTS user_login_settings__rebuilt;`)
        await db.exec(`
            CREATE TABLE user_login_settings__rebuilt (
                user_id TEXT PRIMARY KEY,
                login_notice INTEGER NOT NULL DEFAULT -11,
                foreign_login_block INTEGER NOT NULL DEFAULT 0,
                updated_at INTEGER NOT NULL,
                FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE
            );
        `)

        if (hasOld?.name) {
            const ocols = await db.all<{ name: string }>(`PRAGMA table_info(login_settings);`)
            const onames = new Set((ocols ?? []).map((c) => c.name))
            const uid = colExpr('l', ['user_id', 'userId'], onames)
            const ln = colExpr('l', ['login_notice', 'loginNotice'], onames)
            const fb = colExpr('l', ['foreign_login_block', 'foreignLoginBlock'], onames)
            const upd = updatedAtUnixExpr('l', onames)
            await db.exec(`
                INSERT INTO user_login_settings__rebuilt (user_id, login_notice, foreign_login_block, updated_at)
                SELECT
                    ${uid} AS user_id,
                    COALESCE(CAST(${ln} AS INTEGER), -11) AS login_notice,
                    CASE WHEN COALESCE(CAST(${fb} AS INTEGER), 0) != 0 THEN 1 ELSE 0 END AS foreign_login_block,
                    CAST((${upd}) AS INTEGER) AS updated_at
                FROM login_settings l
                WHERE ${uid} IS NOT NULL AND TRIM(CAST(${uid} AS TEXT)) != '';
            `)
        } else if (hasNew?.name) {
            const snames = newNames
            const uid = colExpr('s', ['user_id', 'userId'], snames)
            const ln = colExpr('s', ['login_notice', 'loginNotice'], snames)
            const fb = colExpr('s', ['foreign_login_block', 'foreignLoginBlock'], snames)
            const upd = updatedAtUnixExpr('s', snames)
            await db.exec(`
                INSERT INTO user_login_settings__rebuilt (user_id, login_notice, foreign_login_block, updated_at)
                SELECT
                    ${uid} AS user_id,
                    COALESCE(CAST(${ln} AS INTEGER), -11) AS login_notice,
                    CASE WHEN COALESCE(CAST(${fb} AS INTEGER), 0) != 0 THEN 1 ELSE 0 END AS foreign_login_block,
                    CAST((${upd}) AS INTEGER) AS updated_at
                FROM user_login_settings s
                WHERE ${uid} IS NOT NULL AND TRIM(CAST(${uid} AS TEXT)) != '';
            `)
        }

        await db.exec(`DROP TABLE IF EXISTS login_settings;`)
        await db.exec(`DROP TABLE IF EXISTS user_login_settings;`)
        await db.exec(`ALTER TABLE user_login_settings__rebuilt RENAME TO user_login_settings;`)
        await db.exec(`COMMIT;`)
        console.log('[gp-live] migrateUserLoginSettingsTableSchema: 完成')
    } catch (e) {
        try {
            await db.exec(`ROLLBACK;`)
        } catch {
            // ignore
        }
        throw e
    } finally {
        await db.exec(`PRAGMA foreign_keys = ON;`)
    }
}

/** 已廢棄、專案未再使用；若舊庫仍存在則刪除。 */
export async function dropGiftTransactionsTableIfExists(db: Db) {
    await db.exec(`DROP TABLE IF EXISTS gift_transactions;`)
}

const USER_AVATAR_FRAMES_FINAL_COLUMNS = new Set([
    'user_id',
    'frame_id',
    'is_used',
    'acquired_at',
    'expiration_at',
])

const USER_AVATAR_FRAMES_LEGACY_COLUMN_NAMES = new Set([
    'userId',
    'frameId',
    'isEquipped',
    'acquiredAt',
])

function userAvatarFramesTableNeedsRebuild(names: Set<string>): boolean {
    if (![...USER_AVATAR_FRAMES_FINAL_COLUMNS].every((c) => names.has(c))) return true
    if (names.size !== USER_AVATAR_FRAMES_FINAL_COLUMNS.size) return true
    for (const legacy of USER_AVATAR_FRAMES_LEGACY_COLUMN_NAMES) {
        if (names.has(legacy)) return true
    }
    return false
}

/** 讀取 expiration_at；若舊庫僅有拼錯的 expiraction_at 則一併納入（COALESCE）。 */
function expirationAtUnixExpr(alias: string, colNames: Set<string>): string {
    const p = `${alias}.`
    const perCol = (col: string) =>
        `CASE
            WHEN ${p}${col} IS NULL THEN NULL
            WHEN CAST(${p}${col} AS INTEGER) > 1000000000000 THEN CAST(${p}${col} AS INTEGER) / 1000
            WHEN CAST(${p}${col} AS INTEGER) > 0 THEN CAST(${p}${col} AS INTEGER)
            ELSE NULL
        END`
    const parts: string[] = []
    if (colNames.has('expiration_at')) parts.push(perCol('expiration_at'))
    if (colNames.has('expiraction_at')) parts.push(perCol('expiraction_at'))
    if (parts.length === 0) return 'NULL'
    if (parts.length === 1) return parts[0]
    return `COALESCE(${parts.join(', ')})`
}

const CONFIG_AVATAR_FRAMES_FINAL = new Set([
    'item_id',
    'name',
    'img_url',
    'type',
    'tag',
    'amount',
    'created_at',
])

function configAvatarFramesTableOk(names: Set<string>): boolean {
    if (names.size !== CONFIG_AVATAR_FRAMES_FINAL.size) return false
    return [...CONFIG_AVATAR_FRAMES_FINAL].every((c) => names.has(c))
}

/** 移除 config_avatar_frames.name（item.json 已不再提供名稱） */
export async function migrateConfigAvatarFramesDropNameColumn(db: Db) {
    const hasNew = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'config_avatar_frames' LIMIT 1`
    )
    if (!hasNew?.name) return

    const pragmaNew = await db.all<{ name: string }>(`PRAGMA table_info(config_avatar_frames);`)
    const newNames = new Set(pragmaNew.map((c) => c.name))
    if (!newNames.has('name')) return

    console.log('[gp-live] migrateConfigAvatarFramesDropNameColumn: 移除 config_avatar_frames.name…')

    await db.exec(`DROP TABLE IF EXISTS config_avatar_frames__rebuilt;`)
    await db.exec(`PRAGMA foreign_keys = OFF;`)
    await db.exec(`BEGIN;`)
    try {
        await db.exec(`
            CREATE TABLE config_avatar_frames__rebuilt (
                item_id TEXT PRIMARY KEY,
                img_url TEXT NOT NULL,
                type TEXT NOT NULL CHECK (type IN ('IMG', 'LOTTERY')),
                amount INTEGER NOT NULL DEFAULT 0,
                created_at INTEGER NOT NULL
            );
        `)
        await db.exec(`
            INSERT INTO config_avatar_frames__rebuilt (item_id, img_url, type, amount, created_at)
            SELECT
                c.item_id,
                c.img_url,
                CASE
                    WHEN UPPER(TRIM(COALESCE(c.type, ''))) IN ('LOTTIE', 'LOTTERY') THEN 'LOTTERY'
                    WHEN UPPER(TRIM(COALESCE(c.type, ''))) = 'IMG' THEN 'IMG'
                    ELSE 'IMG'
                END,
                COALESCE(CAST(c.amount AS INTEGER), 0),
                CAST(COALESCE(c.created_at, ${UNIX_NOW}) AS INTEGER)
            FROM config_avatar_frames c;
        `)
        await db.exec(`DROP TABLE config_avatar_frames;`)
        await db.exec(`ALTER TABLE config_avatar_frames__rebuilt RENAME TO config_avatar_frames;`)
        await db.exec(`COMMIT;`)
        console.log('[gp-live] migrateConfigAvatarFramesDropNameColumn: 完成')
    } catch (e) {
        try {
            await db.exec(`ROLLBACK;`)
        } catch {
            // ignore
        }
        throw e
    } finally {
        await db.exec(`PRAGMA foreign_keys = ON;`)
    }
}

/** 新增 config_avatar_frames.tag（對應 item.json tag） */
export async function migrateConfigAvatarFramesAddTagColumn(db: Db) {
    const hasNew = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'config_avatar_frames' LIMIT 1`
    )
    if (!hasNew?.name) return

    const pragmaNew = await db.all<{ name: string }>(`PRAGMA table_info(config_avatar_frames);`)
    const newNames = new Set(pragmaNew.map((c) => c.name))
    if (newNames.has('tag')) return

    console.log('[gp-live] migrateConfigAvatarFramesAddTagColumn: 新增 config_avatar_frames.tag…')
    await db.exec(`ALTER TABLE config_avatar_frames ADD COLUMN tag TEXT NOT NULL DEFAULT '';`)
    console.log('[gp-live] migrateConfigAvatarFramesAddTagColumn: 完成')
}

/** 新增 config_avatar_frames.name（對應 item.json name） */
export async function migrateConfigAvatarFramesAddNameColumn(db: Db) {
    const hasNew = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'config_avatar_frames' LIMIT 1`
    )
    if (!hasNew?.name) return

    const pragmaNew = await db.all<{ name: string }>(`PRAGMA table_info(config_avatar_frames);`)
    const colNames = new Set(pragmaNew.map((c) => c.name))
    if (colNames.has('name')) return

    console.log('[gp-live] migrateConfigAvatarFramesAddNameColumn: 新增 config_avatar_frames.name…')
    await db.exec(`ALTER TABLE config_avatar_frames ADD COLUMN name TEXT NOT NULL DEFAULT '';`)
    console.log('[gp-live] migrateConfigAvatarFramesAddNameColumn: 完成')
}

/**
 * avatar_frames 改名為 config_avatar_frames；欄位 item_id / name / img_url / type（IMG|LOTTERY）/ tag / amount / created_at（Unix 秒）。
 * 舊表 type 為 LOTTIE 者寫入 LOTTERY。完成後刪除 avatar_frames（user_avatar_frames.frame_id 仍對應同一 item_id 字串）。
 */
export async function migrateConfigAvatarFramesTableSchema(db: Db) {
    await migrateConfigAvatarFramesAddTagColumn(db)
    await migrateConfigAvatarFramesAddNameColumn(db)

    const hasOld = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'avatar_frames' LIMIT 1`
    )
    const hasNew = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'config_avatar_frames' LIMIT 1`
    )

    const pragmaNew = hasNew?.name
        ? await db.all<{ name: string }>(`PRAGMA table_info(config_avatar_frames);`)
        : []
    const newNames = new Set(pragmaNew.map((c) => c.name))
    const newOk = Boolean(hasNew?.name) && configAvatarFramesTableOk(newNames)

    if (!hasOld?.name && newOk) {
        await db.exec(`DROP TABLE IF EXISTS avatar_frames;`)
        return
    }

    if (!hasOld?.name && !hasNew?.name) {
        await db.exec(`
            CREATE TABLE IF NOT EXISTS config_avatar_frames (
                item_id TEXT PRIMARY KEY,
                name TEXT NOT NULL DEFAULT '',
                img_url TEXT NOT NULL,
                type TEXT NOT NULL CHECK (type IN ('IMG', 'LOTTERY')),
                tag TEXT NOT NULL DEFAULT '',
                amount INTEGER NOT NULL DEFAULT 0,
                created_at INTEGER NOT NULL DEFAULT (${UNIX_NOW})
            );
        `)
        return
    }

    if (!hasOld?.name && hasNew?.name && !newOk) {
        console.warn(
            '[gp-live] migrateConfigAvatarFramesTableSchema: config_avatar_frames 結構非預期，且無 avatar_frames 可重建，略過'
        )
        return
    }

    const oldCols = await db.all<{ name: string }>(`PRAGMA table_info(avatar_frames);`)
    const oldNames = new Set(oldCols.map((c) => c.name))

    const itemId = colExpr('a', ['id', 'item_id'], oldNames)
    const imgCol = colExpr('a', ['img_url', 'image'], oldNames)
    const typeSql = `CASE
        WHEN UPPER(TRIM(COALESCE(CAST(a.type AS TEXT), ''))) IN ('LOTTIE','LOTTERY') THEN 'LOTTERY'
        ELSE 'IMG' END`
    const amountSql = oldNames.has('price')
        ? `COALESCE(CAST(a.price AS INTEGER), 0)`
        : oldNames.has('amount')
          ? `COALESCE(CAST(a.amount AS INTEGER), 0)`
          : '0'

    const createdBranches: string[] = []
    if (oldNames.has('created_at')) {
        createdBranches.push(`WHEN COALESCE(a.created_at, 0) > 1000000000000 THEN a.created_at / 1000`)
        createdBranches.push(`WHEN COALESCE(a.created_at, 0) > 0 THEN a.created_at`)
    }
    if (oldNames.has('createdAt')) {
        createdBranches.push(
            `WHEN typeof(a.createdAt)='text' AND CAST(a.createdAt AS INTEGER) > 1000000000000 THEN CAST(a.createdAt AS INTEGER) / 1000`
        )
        createdBranches.push(
            `WHEN typeof(a.createdAt)='text' AND CAST(a.createdAt AS INTEGER) > 0 THEN CAST(a.createdAt AS INTEGER)`
        )
    }
    const createdExpr =
        createdBranches.length > 0 ? `CASE ${createdBranches.join(' ')} ELSE ${UNIX_NOW} END` : UNIX_NOW

    const shouldMergeConfig = Boolean(hasNew?.name && newOk)

    console.log(
        '[gp-live] migrateConfigAvatarFramesTableSchema: 匯入 config_avatar_frames 並移除 avatar_frames…'
    )

    await db.exec(`DROP TABLE IF EXISTS config_avatar_frames__rebuilt;`)
    await db.exec(`PRAGMA foreign_keys = OFF;`)
    await db.exec(`BEGIN;`)
    try {
        await db.exec(`
            CREATE TABLE config_avatar_frames__rebuilt (
                item_id TEXT PRIMARY KEY,
                name TEXT NOT NULL DEFAULT '',
                img_url TEXT NOT NULL,
                type TEXT NOT NULL CHECK (type IN ('IMG', 'LOTTERY')),
                tag TEXT NOT NULL DEFAULT '',
                amount INTEGER NOT NULL DEFAULT 0,
                created_at INTEGER NOT NULL
            );
        `)

        if (shouldMergeConfig) {
            const mergeNameSelect = newNames.has('name')
                ? `COALESCE(TRIM(c.name), CAST(c.item_id AS TEXT), '')`
                : `COALESCE(CAST(c.item_id AS TEXT), '')`
            const mergeTagSelect = newNames.has('tag')
                ? `COALESCE(TRIM(c.tag), '')`
                : `''`
            await db.exec(`
                INSERT INTO config_avatar_frames__rebuilt (item_id, name, img_url, type, tag, amount, created_at)
                SELECT
                    c.item_id,
                    ${mergeNameSelect},
                    c.img_url,
                    CASE
                        WHEN UPPER(TRIM(COALESCE(c.type, ''))) IN ('LOTTIE', 'LOTTERY') THEN 'LOTTERY'
                        WHEN UPPER(TRIM(COALESCE(c.type, ''))) = 'IMG' THEN 'IMG'
                        ELSE 'IMG'
                    END,
                    ${mergeTagSelect},
                    COALESCE(CAST(c.amount AS INTEGER), 0),
                    CAST(COALESCE(c.created_at, ${UNIX_NOW}) AS INTEGER)
                FROM config_avatar_frames c;
            `)
        }

        await db.exec(`
            INSERT OR REPLACE INTO config_avatar_frames__rebuilt (item_id, name, img_url, type, tag, amount, created_at)
            SELECT
                CAST(${itemId} AS TEXT) AS item_id,
                COALESCE(NULLIF(TRIM(CAST(${itemId} AS TEXT)), ''), '') AS name,
                COALESCE(NULLIF(TRIM(${imgCol}), ''), '') AS img_url,
                (${typeSql}) AS type,
                '' AS tag,
                ${amountSql} AS amount,
                CAST((${createdExpr}) AS INTEGER) AS created_at
            FROM avatar_frames a
            WHERE ${itemId} IS NOT NULL AND TRIM(CAST(${itemId} AS TEXT)) != '';
        `)

        await db.exec(`DROP TABLE IF EXISTS avatar_frames;`)
        await db.exec(`DROP TABLE IF EXISTS config_avatar_frames;`)
        await db.exec(`ALTER TABLE config_avatar_frames__rebuilt RENAME TO config_avatar_frames;`)
        await db.exec(`COMMIT;`)
        console.log('[gp-live] migrateConfigAvatarFramesTableSchema: 完成')
    } catch (e) {
        try {
            await db.exec(`ROLLBACK;`)
        } catch {
            // ignore
        }
        throw e
    } finally {
        await db.exec(`PRAGMA foreign_keys = ON;`)
    }
}

/**
 * user_avatar_frames：user_id / frame_id / is_used / acquired_at（秒）/ expiration_at（秒，可 NULL）。
 */
export async function migrateUserAvatarFramesTableSchema(db: Db) {
    const has = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'user_avatar_frames' LIMIT 1`
    )
    if (!has?.name) return

    const cols = await db.all<{ name: string }>(`PRAGMA table_info(user_avatar_frames);`)
    if (!cols?.length) return
    const names = new Set(cols.map((c) => c.name))
    if (!userAvatarFramesTableNeedsRebuild(names)) return

    console.log(
        '[gp-live] migrateUserAvatarFramesTableSchema: 重建 user_avatar_frames（snake_case + 時間 Unix 秒）…'
    )

    const uid = colExpr('u', ['user_id', 'userId'], names)
    const fid = colExpr('u', ['frame_id', 'frameId'], names)

    let isUsedSql = '0'
    if (names.has('is_used') && names.has('isEquipped')) {
        isUsedSql = `CASE WHEN COALESCE(CAST(u.is_used AS INTEGER), CAST(u.isEquipped AS INTEGER), 0) != 0 THEN 1 ELSE 0 END`
    } else if (names.has('is_used')) {
        isUsedSql = `CASE WHEN COALESCE(CAST(u.is_used AS INTEGER), 0) != 0 THEN 1 ELSE 0 END`
    } else if (names.has('isEquipped')) {
        isUsedSql = `CASE WHEN COALESCE(CAST(u.isEquipped AS INTEGER), 0) != 0 THEN 1 ELSE 0 END`
    }

    const acquiredExpr = createdAtUnixExpr('u', names)
    const expExpr = expirationAtUnixExpr('u', names)

    await db.exec(`DROP TABLE IF EXISTS user_avatar_frames__rebuilt;`)
    await db.exec(`PRAGMA foreign_keys = OFF;`)
    await db.exec(`BEGIN;`)
    try {
        await db.exec(`
            CREATE TABLE user_avatar_frames__rebuilt (
                user_id TEXT NOT NULL,
                frame_id TEXT NOT NULL,
                is_used INTEGER NOT NULL DEFAULT 0,
                acquired_at INTEGER NOT NULL,
                expiration_at INTEGER,
                PRIMARY KEY (user_id, frame_id),
                FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE,
                FOREIGN KEY(frame_id) REFERENCES config_avatar_frames(item_id) ON DELETE CASCADE
            );
        `)
        await db.exec(`
            INSERT INTO user_avatar_frames__rebuilt (user_id, frame_id, is_used, acquired_at, expiration_at)
            SELECT
                ${uid} AS user_id,
                ${fid} AS frame_id,
                CAST((${isUsedSql}) AS INTEGER) AS is_used,
                CAST((${acquiredExpr}) AS INTEGER) AS acquired_at,
                CAST((${expExpr}) AS INTEGER) AS expiration_at
            FROM user_avatar_frames u
            WHERE ${uid} IS NOT NULL AND TRIM(CAST(${uid} AS TEXT)) != ''
              AND ${fid} IS NOT NULL AND TRIM(CAST(${fid} AS TEXT)) != '';
        `)
        await db.exec(`DROP TABLE user_avatar_frames;`)
        await db.exec(`ALTER TABLE user_avatar_frames__rebuilt RENAME TO user_avatar_frames;`)
        await db.exec(`COMMIT;`)
        console.log('[gp-live] migrateUserAvatarFramesTableSchema: 完成')
    } catch (e) {
        try {
            await db.exec(`ROLLBACK;`)
        } catch {
            // ignore
        }
        throw e
    } finally {
        await db.exec(`PRAGMA foreign_keys = ON;`)
    }
}

function legacyProfileRingTimeExpr(
    alias: string,
    colNames: Set<string>,
    candidates: string[],
    fallback: string
): string {
    const branches: string[] = []
    for (const col of candidates) {
        if (!colNames.has(col)) continue
        branches.push(
            `WHEN CAST(${alias}."${col}" AS INTEGER) > 1000000000000 THEN CAST(${alias}."${col}" AS INTEGER) / 1000`
        )
        branches.push(`WHEN CAST(${alias}."${col}" AS INTEGER) > 0 THEN CAST(${alias}."${col}" AS INTEGER)`)
        branches.push(
            `WHEN ${alias}."${col}" IS NOT NULL AND TRIM(CAST(${alias}."${col}" AS TEXT)) != '' AND CAST(strftime('%s', ${alias}."${col}") AS INTEGER) > 0 THEN CAST(strftime('%s', ${alias}."${col}") AS INTEGER)`
        )
    }
    return branches.length > 0 ? `CASE ${branches.join(' ')} ELSE ${fallback} END` : fallback
}

/**
 * 舊 profile_ring / profile_rings 只作為使用者持有頭像框資料來源；匯入 user_avatar_frames 後移除。
 */
export async function migrateLegacyProfileRingTableSchema(db: Db) {
    const legacyTables = ['profile_ring', 'profile_rings']
    for (const tableName of legacyTables) {
        const has = await tableExists(db, tableName)
        if (!has) continue

        const cols = await db.all<{ name: string }>(`PRAGMA table_info("${tableName}");`)
        const names = new Set((cols ?? []).map((c) => c.name))
        const uid = colExpr('p', ['user_id', 'userId', 'owner_id', 'ownerId'], names)
        const fid = colExpr(
            'p',
            ['frame_id', 'frameId', 'item_id', 'itemId', 'profile_ring_id', 'profileRingId', 'ring_id', 'ringId'],
            names
        )

        console.log(`[gp-live] migrateLegacyProfileRingTableSchema: 匯入並移除 ${tableName}…`)

        await db.exec(`PRAGMA foreign_keys = OFF;`)
        await db.exec(`BEGIN;`)
        try {
            await db.exec(`
                CREATE TABLE IF NOT EXISTS user_avatar_frames (
                    user_id TEXT NOT NULL,
                    frame_id TEXT NOT NULL,
                    is_used INTEGER NOT NULL DEFAULT 0,
                    acquired_at INTEGER NOT NULL DEFAULT (${UNIX_NOW}),
                    expiration_at INTEGER,
                    PRIMARY KEY (user_id, frame_id),
                    FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE,
                    FOREIGN KEY(frame_id) REFERENCES config_avatar_frames(item_id) ON DELETE CASCADE
                );
            `)

            if (uid !== 'NULL' && fid !== 'NULL') {
                let isUsedSql = '1'
                if (names.has('is_used') && names.has('isEquipped')) {
                    isUsedSql = `CASE WHEN COALESCE(CAST(p.is_used AS INTEGER), CAST(p.isEquipped AS INTEGER), 0) != 0 THEN 1 ELSE 0 END`
                } else if (names.has('is_used')) {
                    isUsedSql = `CASE WHEN COALESCE(CAST(p.is_used AS INTEGER), 0) != 0 THEN 1 ELSE 0 END`
                } else if (names.has('isEquipped')) {
                    isUsedSql = `CASE WHEN COALESCE(CAST(p.isEquipped AS INTEGER), 0) != 0 THEN 1 ELSE 0 END`
                } else if (names.has('is_equipped')) {
                    isUsedSql = `CASE WHEN COALESCE(CAST(p.is_equipped AS INTEGER), 0) != 0 THEN 1 ELSE 0 END`
                }

                const acquiredExpr = legacyProfileRingTimeExpr(
                    'p',
                    names,
                    ['acquired_at', 'acquiredAt', 'created_at', 'createdAt'],
                    UNIX_NOW
                )
                const expirationExpr = legacyProfileRingTimeExpr(
                    'p',
                    names,
                    ['expiration_at', 'expiraction_at', 'expires_at', 'expiresAt', 'expired_at', 'expiredAt'],
                    'NULL'
                )

                await db.exec(`
                    INSERT OR IGNORE INTO user_avatar_frames (user_id, frame_id, is_used, acquired_at, expiration_at)
                    SELECT
                        CAST(${uid} AS TEXT) AS user_id,
                        CAST(${fid} AS TEXT) AS frame_id,
                        CAST((${isUsedSql}) AS INTEGER) AS is_used,
                        CAST((${acquiredExpr}) AS INTEGER) AS acquired_at,
                        CAST((${expirationExpr}) AS INTEGER) AS expiration_at
                    FROM "${tableName}" p
                    WHERE ${uid} IS NOT NULL AND TRIM(CAST(${uid} AS TEXT)) != ''
                      AND ${fid} IS NOT NULL AND TRIM(CAST(${fid} AS TEXT)) != '';
                `)
            }

            await db.exec(`DROP TABLE IF EXISTS "${tableName}";`)
            await db.exec(`COMMIT;`)
        } catch (e) {
            try {
                await db.exec(`ROLLBACK;`)
            } catch {
                // ignore
            }
            throw e
        } finally {
            await db.exec(`PRAGMA foreign_keys = ON;`)
        }
    }
}

async function tableExists(db: Db, tableName: string): Promise<boolean> {
    const row = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = ? LIMIT 1`,
        [tableName]
    )
    return !!row?.name
}

const USER_ACTIONS_FINAL_COLUMNS = new Set(['user_id', 'target_id', 'action_type', 'created_at'])

function userActionsTableIsFinal(names: Set<string>): boolean {
    if (names.size !== USER_ACTIONS_FINAL_COLUMNS.size) return false
    return [...USER_ACTIONS_FINAL_COLUMNS].every((c) => names.has(c))
}

function createdAtUnixExpr(alias: string, colNames: Set<string>): string {
    const p = `${alias}.`
    const branches: string[] = []
    if (colNames.has('created_at')) {
        branches.push(
            `WHEN CAST(${p}created_at AS INTEGER) > 1000000000000 THEN CAST(${p}created_at AS INTEGER) / 1000`
        )
        branches.push(`WHEN CAST(${p}created_at AS INTEGER) > 0 THEN CAST(${p}created_at AS INTEGER)`)
    }
    if (colNames.has('createdAt')) {
        branches.push(
            `WHEN CAST(${p}createdAt AS INTEGER) > 1000000000000 THEN CAST(${p}createdAt AS INTEGER) / 1000`
        )
        branches.push(`WHEN CAST(${p}createdAt AS INTEGER) > 0 THEN CAST(${p}createdAt AS INTEGER)`)
        branches.push(
            `WHEN ${p}createdAt IS NOT NULL AND TRIM(CAST(${p}createdAt AS TEXT)) != '' AND CAST(strftime('%s', ${p}createdAt) AS INTEGER) > 0 THEN CAST(strftime('%s', ${p}createdAt) AS INTEGER)`
        )
    }
    return branches.length > 0 ? `CASE ${branches.join(' ')} ELSE ${UNIX_NOW} END` : UNIX_NOW
}

function updatedAtUnixExpr(alias: string, colNames: Set<string>): string {
    const p = `${alias}.`
    const branches: string[] = []
    if (colNames.has('updated_at')) {
        branches.push(
            `WHEN CAST(${p}updated_at AS INTEGER) > 1000000000000 THEN CAST(${p}updated_at AS INTEGER) / 1000`
        )
        branches.push(`WHEN CAST(${p}updated_at AS INTEGER) > 0 THEN CAST(${p}updated_at AS INTEGER)`)
    }
    if (colNames.has('updatedAt')) {
        branches.push(
            `WHEN CAST(${p}updatedAt AS INTEGER) > 1000000000000 THEN CAST(${p}updatedAt AS INTEGER) / 1000`
        )
        branches.push(`WHEN CAST(${p}updatedAt AS INTEGER) > 0 THEN CAST(${p}updatedAt AS INTEGER)`)
        branches.push(
            `WHEN ${p}updatedAt IS NOT NULL AND TRIM(CAST(${p}updatedAt AS TEXT)) != '' AND CAST(strftime('%s', ${p}updatedAt) AS INTEGER) > 0 THEN CAST(strftime('%s', ${p}updatedAt) AS INTEGER)`
        )
    }
    return branches.length > 0 ? `CASE ${branches.join(' ')} ELSE ${UNIX_NOW} END` : UNIX_NOW
}

/**
 * 將 user_follows、user_blocks 合併為 user_actions（follow / block，created_at 為 Unix 秒）。
 */
export async function migrateUserActionsTableSchema(db: Db) {
    const hasFollows = await tableExists(db, 'user_follows')
    const hasBlocks = await tableExists(db, 'user_blocks')
    const hasActions = await tableExists(db, 'user_actions')

    let actionColNames = new Set<string>()
    if (hasActions) {
        const ac = await db.all<{ name: string }>(`PRAGMA table_info(user_actions);`)
        actionColNames = new Set((ac ?? []).map((c) => c.name))
    }
    const actionsOk = hasActions && userActionsTableIsFinal(actionColNames)

    if (actionsOk && !hasFollows && !hasBlocks) return

    console.log('[gp-live] migrateUserActionsTableSchema: 整合 user_follows / user_blocks → user_actions…')

    await db.exec(`PRAGMA foreign_keys = OFF;`)
    await db.exec(`BEGIN;`)
    try {
        await db.exec(`DROP TABLE IF EXISTS user_actions__rebuilt;`)
        await db.exec(`
            CREATE TABLE user_actions__rebuilt (
                user_id TEXT NOT NULL,
                target_id TEXT NOT NULL,
                action_type TEXT NOT NULL CHECK(action_type IN ('follow', 'block')),
                created_at INTEGER NOT NULL,
                PRIMARY KEY (user_id, target_id, action_type),
                FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE,
                FOREIGN KEY(target_id) REFERENCES users(user_id) ON DELETE CASCADE
            );
        `)

        if (actionsOk) {
            await db.exec(`
                INSERT OR IGNORE INTO user_actions__rebuilt (user_id, target_id, action_type, created_at)
                SELECT user_id, target_id, action_type, created_at FROM user_actions;
            `)
        }

        if (hasFollows) {
            const fcols = await db.all<{ name: string }>(`PRAGMA table_info(user_follows);`)
            const fnames = new Set((fcols ?? []).map((c) => c.name))
            let uidSql: string
            let tidSql: string
            if (fnames.has('followerId') && !fnames.has('targetId')) {
                uidSql = 'f.followerId'
                tidSql = 'f.userId'
            } else {
                uidSql = colExpr('f', ['userId'], fnames)
                tidSql = colExpr('f', ['targetId'], fnames)
            }
            const createdExpr = createdAtUnixExpr('f', fnames)
            await db.exec(`
                INSERT OR IGNORE INTO user_actions__rebuilt (user_id, target_id, action_type, created_at)
                SELECT
                    ${uidSql} AS user_id,
                    ${tidSql} AS target_id,
                    'follow' AS action_type,
                    CAST((${createdExpr}) AS INTEGER) AS created_at
                FROM user_follows f
                WHERE ${uidSql} IS NOT NULL AND TRIM(CAST(${uidSql} AS TEXT)) != ''
                  AND ${tidSql} IS NOT NULL AND TRIM(CAST(${tidSql} AS TEXT)) != '';
            `)
        }

        if (hasBlocks) {
            const bcols = await db.all<{ name: string }>(`PRAGMA table_info(user_blocks);`)
            const bnames = new Set((bcols ?? []).map((c) => c.name))
            const uidSql = colExpr('b', ['userId'], bnames)
            const tidSql = colExpr('b', ['targetId'], bnames)
            const createdExpr = createdAtUnixExpr('b', bnames)
            await db.exec(`
                INSERT OR IGNORE INTO user_actions__rebuilt (user_id, target_id, action_type, created_at)
                SELECT
                    ${uidSql} AS user_id,
                    ${tidSql} AS target_id,
                    'block' AS action_type,
                    CAST((${createdExpr}) AS INTEGER) AS created_at
                FROM user_blocks b
                WHERE ${uidSql} IS NOT NULL AND TRIM(CAST(${uidSql} AS TEXT)) != ''
                  AND ${tidSql} IS NOT NULL AND TRIM(CAST(${tidSql} AS TEXT)) != '';
            `)
        }

        await db.exec(`
            DELETE FROM user_actions__rebuilt
            WHERE action_type = 'follow'
              AND EXISTS (
                SELECT 1 FROM user_actions__rebuilt blk
                WHERE blk.user_id = user_actions__rebuilt.user_id
                  AND blk.target_id = user_actions__rebuilt.target_id
                  AND blk.action_type = 'block'
              );
        `)

        await db.exec(`DROP TABLE IF EXISTS user_actions;`)
        await db.exec(`DROP TABLE IF EXISTS user_follows;`)
        await db.exec(`DROP TABLE IF EXISTS user_blocks;`)
        await db.exec(`ALTER TABLE user_actions__rebuilt RENAME TO user_actions;`)
        await db.exec(`COMMIT;`)
        console.log('[gp-live] migrateUserActionsTableSchema: 完成')
    } catch (e) {
        try {
            await db.exec(`ROLLBACK;`)
        } catch {
            // ignore
        }
        throw e
    } finally {
        await db.exec(`PRAGMA foreign_keys = ON;`)
    }
}

/**
 * board_posts → user_board_posts：post_id、user_id、content、thumbnail_url、is_pinned、created_at（Unix 秒）。
 * 留言遷移至 user_board_post_comments（見 migrateUserBoardPostCommentsTableSchema）。
 */
export async function migrateUserBoardPostsTableSchema(db: Db) {
    const hasLegacy = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'board_posts' LIMIT 1`
    )

    async function userBoardPostsTableIsFinal(): Promise<boolean> {
        const has = await db.get<{ name: string }>(
            `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'user_board_posts' LIMIT 1`
        )
        if (!has?.name) return false
        const cols = await db.all<{ name: string }>(`PRAGMA table_info(user_board_posts);`)
        const names = new Set((cols ?? []).map((c) => c.name))
        return (
            names.has('post_id') &&
            names.has('user_id') &&
            names.has('content') &&
            names.has('thumbnail_url') &&
            names.has('is_pinned') &&
            names.has('created_at') &&
            !names.has('id')
        )
    }

    if (!hasLegacy?.name) {
        if (await userBoardPostsTableIsFinal()) return
        return
    }

    console.log('[gp-live] migrateUserBoardPostsTableSchema: board_posts → user_board_posts…')

    await db.exec(`PRAGMA foreign_keys = OFF;`)
    await db.exec(`BEGIN;`)
    try {
        await db.exec(`DROP TABLE IF EXISTS __ubp_mig_comments;`)
        await db.exec(`DROP TABLE IF EXISTS __ubp_mig_posts;`)

        const hasOldComments = await db.get<{ name: string }>(
            `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'board_post_comments' LIMIT 1`
        )
        const hasNewComments = await db.get<{ name: string }>(
            `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'user_board_post_comments' LIMIT 1`
        )
        if (hasOldComments?.name) {
            await db.exec(`CREATE TABLE __ubp_mig_comments AS SELECT * FROM board_post_comments;`)
        } else if (hasNewComments?.name) {
            await db.exec(`CREATE TABLE __ubp_mig_comments AS SELECT * FROM user_board_post_comments;`)
        }

        await db.exec(`CREATE TABLE __ubp_mig_posts AS SELECT * FROM board_posts;`)

        await db.exec(`DROP TABLE IF EXISTS board_post_comments;`)
        await db.exec(`DROP TABLE IF EXISTS user_board_post_comments;`)
        await db.exec(`DROP TABLE IF EXISTS board_posts;`)
        await db.exec(`DROP TABLE IF EXISTS user_board_posts;`)

        await db.exec(`
            CREATE TABLE user_board_posts (
                post_id TEXT PRIMARY KEY,
                user_id TEXT NOT NULL,
                content TEXT NOT NULL,
                thumbnail_url TEXT,
                is_pinned INTEGER NOT NULL DEFAULT 0,
                created_at INTEGER NOT NULL DEFAULT (${UNIX_NOW}),
                FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE
            );
        `)

        await db.exec(`
            INSERT INTO user_board_posts (post_id, user_id, content, thumbnail_url, is_pinned, created_at)
            SELECT
                id AS post_id,
                userId AS user_id,
                content,
                thumb AS thumbnail_url,
                COALESCE(pinned, 0) AS is_pinned,
                CAST((
                    CASE
                        WHEN CAST(createdAt AS INTEGER) > 1000000000000 THEN CAST(createdAt AS INTEGER) / 1000
                        WHEN CAST(createdAt AS INTEGER) > 0 THEN CAST(createdAt AS INTEGER)
                        WHEN createdAt IS NOT NULL AND TRIM(CAST(createdAt AS TEXT)) != ''
                            AND CAST(strftime('%s', createdAt) AS INTEGER) > 0
                            THEN CAST(strftime('%s', createdAt) AS INTEGER)
                        ELSE ${UNIX_NOW}
                    END
                ) AS INTEGER) AS created_at
            FROM __ubp_mig_posts;
        `)

        await db.exec(`
            CREATE TABLE user_board_post_comments (
                comment_id TEXT PRIMARY KEY,
                post_id TEXT NOT NULL,
                user_id TEXT NOT NULL,
                content TEXT NOT NULL,
                parent_id TEXT,
                created_at INTEGER NOT NULL,
                edited_at INTEGER,
                FOREIGN KEY(post_id) REFERENCES user_board_posts(post_id) ON DELETE CASCADE,
                FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE,
                FOREIGN KEY(parent_id) REFERENCES user_board_post_comments(comment_id) ON DELETE CASCADE
            );
        `)

        const cBak = await db.get<{ name: string }>(
            `SELECT name FROM sqlite_master WHERE type = 'table' AND name = '__ubp_mig_comments' LIMIT 1`
        )
        if (cBak?.name) {
            const cbCols = await db.all<{ name: string }>(`PRAGMA table_info(__ubp_mig_comments);`)
            const cbNames = new Set((cbCols ?? []).map((c) => c.name))
            if (cbNames.has('comment_id')) {
                await db.exec(`
                    INSERT INTO user_board_post_comments (comment_id, post_id, user_id, content, parent_id, created_at, edited_at)
                    SELECT
                        comment_id,
                        post_id,
                        user_id,
                        content,
                        parent_id,
                        CAST((
                            CASE
                                WHEN CAST(created_at AS INTEGER) > 1000000000000 THEN CAST(created_at AS INTEGER) / 1000
                                WHEN CAST(created_at AS INTEGER) > 0 THEN CAST(created_at AS INTEGER)
                                ELSE ${UNIX_NOW}
                            END
                        ) AS INTEGER) AS created_at,
                        CASE
                            WHEN edited_at IS NULL OR TRIM(CAST(edited_at AS TEXT)) = '' THEN NULL
                            WHEN CAST(edited_at AS INTEGER) > 1000000000000 THEN CAST(edited_at AS INTEGER) / 1000
                            WHEN CAST(edited_at AS INTEGER) > 0 THEN CAST(edited_at AS INTEGER)
                            WHEN CAST(strftime('%s', edited_at) AS INTEGER) > 0 THEN CAST(strftime('%s', edited_at) AS INTEGER)
                            ELSE NULL
                        END AS edited_at
                    FROM __ubp_mig_comments;
                `)
            } else {
                await db.exec(`
                    INSERT INTO user_board_post_comments (comment_id, post_id, user_id, content, parent_id, created_at, edited_at)
                    SELECT
                        id AS comment_id,
                        postId AS post_id,
                        userId AS user_id,
                        content,
                        parentId AS parent_id,
                        CAST((
                            CASE
                                WHEN CAST(createdAtMs AS INTEGER) > 1000000000000 THEN CAST(createdAtMs AS INTEGER) / 1000
                                WHEN CAST(createdAtMs AS INTEGER) > 0 THEN CAST(createdAtMs AS INTEGER)
                                WHEN createdAt IS NOT NULL AND TRIM(CAST(createdAt AS TEXT)) != ''
                                    AND CAST(strftime('%s', createdAt) AS INTEGER) > 0
                                    THEN CAST(strftime('%s', createdAt) AS INTEGER)
                                ELSE ${UNIX_NOW}
                            END
                        ) AS INTEGER) AS created_at,
                        CASE
                            WHEN editedAt IS NULL OR TRIM(CAST(editedAt AS TEXT)) = '' THEN NULL
                            WHEN CAST(editedAt AS INTEGER) > 1000000000000 THEN CAST(editedAt AS INTEGER) / 1000
                            WHEN CAST(editedAt AS INTEGER) > 0 THEN CAST(editedAt AS INTEGER)
                            WHEN CAST(strftime('%s', editedAt) AS INTEGER) > 0 THEN CAST(strftime('%s', editedAt) AS INTEGER)
                            ELSE NULL
                        END AS edited_at
                    FROM __ubp_mig_comments;
                `)
            }
        }

        await db.exec(`DROP TABLE IF EXISTS __ubp_mig_comments;`)
        await db.exec(`DROP TABLE IF EXISTS __ubp_mig_posts;`)

        await db.exec(`COMMIT;`)
        console.log('[gp-live] migrateUserBoardPostsTableSchema: 完成')
    } catch (e) {
        try {
            await db.exec(`ROLLBACK;`)
        } catch {
            // ignore
        }
        throw e
    } finally {
        await db.exec(`PRAGMA foreign_keys = ON;`)
    }
}

/**
 * board_post_comments → user_board_post_comments：comment_id、post_id、user_id、content、parent_id、created_at/edited_at（Unix 秒）。
 */
export async function migrateUserBoardPostCommentsTableSchema(db: Db) {
    async function commentsTableIsFinal(): Promise<boolean> {
        const has = await db.get<{ name: string }>(
            `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'user_board_post_comments' LIMIT 1`
        )
        if (!has?.name) return false
        const cols = await db.all<{ name: string }>(`PRAGMA table_info(user_board_post_comments);`)
        const names = new Set((cols ?? []).map((c) => c.name))
        return (
            names.has('comment_id') &&
            names.has('post_id') &&
            names.has('user_id') &&
            names.has('content') &&
            names.has('parent_id') &&
            names.has('created_at') &&
            !names.has('id')
        )
    }

    const hasLegacy = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'board_post_comments' LIMIT 1`
    )

    if (!hasLegacy?.name) {
        if (await commentsTableIsFinal()) {
            await db.exec(`DROP TABLE IF EXISTS board_post_comments;`)
        }
        return
    }

    const hasPosts = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'user_board_posts' LIMIT 1`
    )
    if (!hasPosts?.name) return

    console.log(
        '[gp-live] migrateUserBoardPostCommentsTableSchema: board_post_comments → user_board_post_comments…'
    )

    await db.exec(`PRAGMA foreign_keys = OFF;`)
    await db.exec(`BEGIN;`)
    try {
        await db.exec(`DROP TABLE IF EXISTS __ubpc_mig;`)
        await db.exec(`CREATE TABLE __ubpc_mig AS SELECT * FROM board_post_comments;`)

        await db.exec(`DROP TABLE IF EXISTS board_post_comments;`)
        await db.exec(`DROP TABLE IF EXISTS user_board_post_comments;`)

        await db.exec(`
            CREATE TABLE user_board_post_comments (
                comment_id TEXT PRIMARY KEY,
                post_id TEXT NOT NULL,
                user_id TEXT NOT NULL,
                content TEXT NOT NULL,
                parent_id TEXT,
                created_at INTEGER NOT NULL,
                edited_at INTEGER,
                FOREIGN KEY(post_id) REFERENCES user_board_posts(post_id) ON DELETE CASCADE,
                FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE,
                FOREIGN KEY(parent_id) REFERENCES user_board_post_comments(comment_id) ON DELETE CASCADE
            );
        `)

        const migCols = await db.all<{ name: string }>(`PRAGMA table_info(__ubpc_mig);`)
        const migNames = new Set((migCols ?? []).map((c) => c.name))

        if (migNames.has('comment_id')) {
            await db.exec(`
                INSERT INTO user_board_post_comments (comment_id, post_id, user_id, content, parent_id, created_at, edited_at)
                SELECT
                    comment_id,
                    post_id,
                    user_id,
                    content,
                    parent_id,
                    CAST((
                        CASE
                            WHEN CAST(created_at AS INTEGER) > 1000000000000 THEN CAST(created_at AS INTEGER) / 1000
                            WHEN CAST(created_at AS INTEGER) > 0 THEN CAST(created_at AS INTEGER)
                            ELSE ${UNIX_NOW}
                        END
                    ) AS INTEGER) AS created_at,
                    CASE
                        WHEN edited_at IS NULL OR TRIM(CAST(edited_at AS TEXT)) = '' THEN NULL
                        WHEN CAST(edited_at AS INTEGER) > 1000000000000 THEN CAST(edited_at AS INTEGER) / 1000
                        WHEN CAST(edited_at AS INTEGER) > 0 THEN CAST(edited_at AS INTEGER)
                        WHEN CAST(strftime('%s', edited_at) AS INTEGER) > 0 THEN CAST(strftime('%s', edited_at) AS INTEGER)
                        ELSE NULL
                    END AS edited_at
                FROM __ubpc_mig;
            `)
        } else {
            await db.exec(`
                INSERT INTO user_board_post_comments (comment_id, post_id, user_id, content, parent_id, created_at, edited_at)
                SELECT
                    id AS comment_id,
                    postId AS post_id,
                    userId AS user_id,
                    content,
                    parentId AS parent_id,
                    CAST((
                        CASE
                            WHEN CAST(createdAtMs AS INTEGER) > 1000000000000 THEN CAST(createdAtMs AS INTEGER) / 1000
                            WHEN CAST(createdAtMs AS INTEGER) > 0 THEN CAST(createdAtMs AS INTEGER)
                            WHEN createdAt IS NOT NULL AND TRIM(CAST(createdAt AS TEXT)) != ''
                                AND CAST(strftime('%s', createdAt) AS INTEGER) > 0
                                THEN CAST(strftime('%s', createdAt) AS INTEGER)
                            ELSE ${UNIX_NOW}
                        END
                    ) AS INTEGER) AS created_at,
                    CASE
                        WHEN editedAt IS NULL OR TRIM(CAST(editedAt AS TEXT)) = '' THEN NULL
                        WHEN CAST(editedAt AS INTEGER) > 1000000000000 THEN CAST(editedAt AS INTEGER) / 1000
                        WHEN CAST(editedAt AS INTEGER) > 0 THEN CAST(editedAt AS INTEGER)
                        WHEN CAST(strftime('%s', editedAt) AS INTEGER) > 0 THEN CAST(strftime('%s', editedAt) AS INTEGER)
                        ELSE NULL
                    END AS edited_at
                FROM __ubpc_mig;
            `)
        }

        await db.exec(`DROP TABLE IF EXISTS __ubpc_mig;`)
        await db.exec(`COMMIT;`)
        console.log('[gp-live] migrateUserBoardPostCommentsTableSchema: 完成')
    } catch (e) {
        try {
            await db.exec(`ROLLBACK;`)
        } catch {
            // ignore
        }
        throw e
    } finally {
        await db.exec(`PRAGMA foreign_keys = ON;`)
    }
}

function siteAdminTimestampUnixExpr(
    alias: string,
    colNames: Set<string>,
    snake: 'created_at' | 'updated_at',
    camel: 'createdAt' | 'updatedAt'
): string {
    const p = `${alias}.`
    const branches: string[] = []
    if (colNames.has(snake)) {
        branches.push(
            `WHEN CAST(${p}${snake} AS INTEGER) > 1000000000000 THEN CAST(${p}${snake} AS INTEGER) / 1000`
        )
        branches.push(`WHEN CAST(${p}${snake} AS INTEGER) > 0 THEN CAST(${p}${snake} AS INTEGER)`)
    }
    if (colNames.has(camel)) {
        branches.push(
            `WHEN typeof(${p}${camel})='text' AND CAST(${p}${camel} AS INTEGER) > 1000000000000 THEN CAST(${p}${camel} AS INTEGER) / 1000`
        )
        branches.push(
            `WHEN typeof(${p}${camel})='text' AND CAST(${p}${camel} AS INTEGER) > 0 THEN CAST(${p}${camel} AS INTEGER)`
        )
    }
    if (branches.length === 0) return UNIX_NOW
    return `CASE ${branches.join(' ')} ELSE ${UNIX_NOW} END`
}

function userSiteAdminTableNeedsMigration(cols: { name: string; type: string }[], names: Set<string>): boolean {
    if (names.has('createdAt') || names.has('updatedAt')) return true
    if (!names.has('user_id') || !names.has('created_at') || !names.has('updated_at')) return true
    for (const c of cols) {
        if (c.name !== 'user_id' && c.name !== 'created_at' && c.name !== 'updated_at') return true
    }
    for (const key of ['created_at', 'updated_at'] as const) {
        const t = cols.find((c) => c.name === key)?.type?.toUpperCase() ?? ''
        if (t && t !== 'INTEGER') return true
    }
    return false
}

/**
 * user_site_admin：created_at / updated_at 為 INTEGER Unix 秒（例如 2147483647）。
 */
export async function migrateUserSiteAdminTableSchema(db: Db) {
    const has = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'user_site_admin' LIMIT 1`
    )
    if (!has?.name) return

    const cols = await db.all<{ name: string; type: string }>(`PRAGMA table_info(user_site_admin);`)
    const names = new Set(cols.map((c) => c.name))

    let hasMs = false
    if (names.has('created_at') && names.has('updated_at')) {
        try {
            const row = await db.get<{ c: number }>(
                `SELECT 1 AS c FROM user_site_admin WHERE (CAST(created_at AS INTEGER) > 1000000000000 OR CAST(updated_at AS INTEGER) > 1000000000000) LIMIT 1`
            )
            hasMs = row?.c === 1
        } catch {
            hasMs = true
        }
    }

    if (!userSiteAdminTableNeedsMigration(cols, names) && !hasMs) return

    const createdExpr = siteAdminTimestampUnixExpr('s', names, 'created_at', 'createdAt')
    const updatedExpr = siteAdminTimestampUnixExpr('s', names, 'updated_at', 'updatedAt')

    console.log('[gp-live] migrateUserSiteAdminTableSchema: 將 created_at / updated_at 統一為 INTEGER Unix 秒…')

    await db.exec(`DROP TABLE IF EXISTS user_site_admin__rebuilt;`)
    await db.exec(`PRAGMA foreign_keys = OFF;`)
    await db.exec(`BEGIN;`)
    try {
        await db.exec(`
            CREATE TABLE user_site_admin__rebuilt (
                user_id TEXT PRIMARY KEY,
                created_at INTEGER NOT NULL DEFAULT (${UNIX_NOW}),
                updated_at INTEGER NOT NULL DEFAULT (${UNIX_NOW}),
                FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE
            );
        `)
        await db.exec(`
            INSERT INTO user_site_admin__rebuilt (user_id, created_at, updated_at)
            SELECT
                CAST(s.user_id AS TEXT) AS user_id,
                CAST((${createdExpr}) AS INTEGER) AS created_at,
                CAST((${updatedExpr}) AS INTEGER) AS updated_at
            FROM user_site_admin s
            WHERE s.user_id IS NOT NULL AND TRIM(CAST(s.user_id AS TEXT)) != '';
        `)
        await db.exec(`DROP TABLE user_site_admin;`)
        await db.exec(`ALTER TABLE user_site_admin__rebuilt RENAME TO user_site_admin;`)
        await db.exec(`COMMIT;`)
        console.log('[gp-live] migrateUserSiteAdminTableSchema: 完成')
    } catch (e) {
        try {
            await db.exec(`ROLLBACK;`)
        } catch {
            // ignore
        }
        throw e
    } finally {
        await db.exec(`PRAGMA foreign_keys = ON;`)
    }
}

const USER_VODS_FINAL_COLUMNS = new Set([
    'vod_id',
    'user_id',
    'title',
    'thumbnail_url',
    'duration',
    'category',
    'tags',
    'created_at',
    'updated_at',
])

function userVodsTableOk(names: Set<string>): boolean {
    if (names.size !== USER_VODS_FINAL_COLUMNS.size) return false
    return [...USER_VODS_FINAL_COLUMNS].every((c) => names.has(c))
}

function userVodCreatedUnixExpr(alias: string, names: Set<string>): string {
    const p = `${alias}.`
    const branches: string[] = []
    if (names.has('created_at')) {
        branches.push(
            `WHEN CAST(${p}created_at AS INTEGER) > 1000000000000 THEN CAST(${p}created_at AS INTEGER) / 1000`
        )
        branches.push(`WHEN CAST(${p}created_at AS INTEGER) > 0 THEN CAST(${p}created_at AS INTEGER)`)
    }
    if (names.has('createdAt')) {
        branches.push(
            `WHEN typeof(${p}createdAt)='text' AND CAST(strftime('%s', ${p}createdAt) AS INTEGER) > 0 THEN CAST(strftime('%s', ${p}createdAt) AS INTEGER)`
        )
        branches.push(
            `WHEN typeof(${p}createdAt)='text' AND CAST(${p}createdAt AS INTEGER) > 1000000000000 THEN CAST(${p}createdAt AS INTEGER) / 1000`
        )
        branches.push(
            `WHEN typeof(${p}createdAt)='text' AND CAST(${p}createdAt AS INTEGER) > 0 THEN CAST(${p}createdAt AS INTEGER)`
        )
    }
    if (names.has('startedTs')) {
        branches.push(
            `WHEN CAST(${p}startedTs AS INTEGER) > 1000000000000 THEN CAST(${p}startedTs AS INTEGER) / 1000`
        )
        branches.push(`WHEN CAST(${p}startedTs AS INTEGER) > 0 THEN CAST(${p}startedTs AS INTEGER)`)
    }
    if (branches.length === 0) return UNIX_NOW
    return `CASE ${branches.join(' ')} ELSE ${UNIX_NOW} END`
}

function userVodUpdatedUnixExpr(alias: string, names: Set<string>, createdExprSql: string): string {
    const p = `${alias}.`
    const branches: string[] = []
    if (names.has('updated_at')) {
        branches.push(
            `WHEN CAST(${p}updated_at AS INTEGER) > 1000000000000 THEN CAST(${p}updated_at AS INTEGER) / 1000`
        )
        branches.push(`WHEN CAST(${p}updated_at AS INTEGER) > 0 THEN CAST(${p}updated_at AS INTEGER)`)
    }
    if (names.has('updatedAt')) {
        branches.push(
            `WHEN typeof(${p}updatedAt)='text' AND CAST(strftime('%s', ${p}updatedAt) AS INTEGER) > 0 THEN CAST(strftime('%s', ${p}updatedAt) AS INTEGER)`
        )
        branches.push(
            `WHEN typeof(${p}updatedAt)='text' AND CAST(${p}updatedAt AS INTEGER) > 1000000000000 THEN CAST(${p}updatedAt AS INTEGER) / 1000`
        )
        branches.push(
            `WHEN typeof(${p}updatedAt)='text' AND CAST(${p}updatedAt AS INTEGER) > 0 THEN CAST(${p}updatedAt AS INTEGER)`
        )
    }
    if (branches.length === 0) return `(${createdExprSql})`
    return `CASE ${branches.join(' ')} ELSE (${createdExprSql}) END`
}

/**
 * vods → user_vods；欄位 vod_id / user_id / title / thumbnail_url / duration / category / tags / created_at / updated_at（Unix 秒）。
 */
export async function migrateUserVodsTableSchema(db: Db) {
    const hasVods = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'vods' LIMIT 1`
    )
    const hasUv = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'user_vods' LIMIT 1`
    )
    const uvPragma = hasUv?.name
        ? await db.all<{ name: string }>(`PRAGMA table_info(user_vods);`)
        : []
    const uvNames = new Set(uvPragma.map((c) => c.name))
    const uvOk = Boolean(hasUv?.name) && userVodsTableOk(uvNames)

    if (!hasVods?.name && uvOk) {
        await db.exec(`DROP TABLE IF EXISTS vods;`)
        return
    }

    if (!hasVods?.name && !hasUv?.name) {
        await db.exec(`
            CREATE TABLE IF NOT EXISTS user_vods (
                vod_id TEXT PRIMARY KEY,
                user_id TEXT NOT NULL,
                title TEXT NOT NULL,
                thumbnail_url TEXT,
                duration TEXT,
                category TEXT,
                tags TEXT,
                created_at INTEGER NOT NULL DEFAULT (${UNIX_NOW}),
                updated_at INTEGER NOT NULL DEFAULT (${UNIX_NOW}),
                FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE
            );
        `)
        return
    }

    if (!hasVods?.name && hasUv?.name && !uvOk) {
        console.warn('[gp-live] migrateUserVodsTableSchema: user_vods 結構非預期且無 vods 可重建，略過')
        return
    }

    const oldCols = await db.all<{ name: string }>(`PRAGMA table_info(vods);`)
    const oldNames = new Set(oldCols.map((c) => c.name))

    const vodId = colExpr('o', ['vod_id', 'id'], oldNames)
    const userId = colExpr('o', ['user_id', 'userId'], oldNames)
    const titleCol = colExpr('o', ['title'], oldNames)
    const thumbCol = colExpr('o', ['thumbnail_url', 'thumb'], oldNames)
    const durationCol = colExpr('o', ['duration'], oldNames)
    const categoryCol = colExpr('o', ['category'], oldNames)
    const tagsCol = colExpr('o', ['tags'], oldNames)

    const titleSql = `COALESCE(NULLIF(TRIM(${titleCol}), ''), '(vod)')`
    const createdExpr = userVodCreatedUnixExpr('o', oldNames)
    const updatedExpr = userVodUpdatedUnixExpr('o', oldNames, createdExpr)

    const shouldMergeUv = Boolean(hasUv?.name && uvOk)

    console.log('[gp-live] migrateUserVodsTableSchema: 建立 user_vods 並自 vods 匯入…')

    await db.exec(`DROP TABLE IF EXISTS user_vods__rebuilt;`)
    await db.exec(`PRAGMA foreign_keys = OFF;`)
    await db.exec(`BEGIN;`)
    try {
        await db.exec(`
            CREATE TABLE user_vods__rebuilt (
                vod_id TEXT PRIMARY KEY,
                user_id TEXT NOT NULL,
                title TEXT NOT NULL,
                thumbnail_url TEXT,
                duration TEXT,
                category TEXT,
                tags TEXT,
                created_at INTEGER NOT NULL,
                updated_at INTEGER NOT NULL,
                FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE
            );
        `)

        if (shouldMergeUv) {
            await db.exec(`
                INSERT INTO user_vods__rebuilt (vod_id, user_id, title, thumbnail_url, duration, category, tags, created_at, updated_at)
                SELECT
                    u.vod_id,
                    u.user_id,
                    u.title,
                    u.thumbnail_url,
                    u.duration,
                    u.category,
                    u.tags,
                    CAST(CASE
                        WHEN CAST(u.created_at AS INTEGER) > 1000000000000 THEN CAST(u.created_at AS INTEGER) / 1000
                        WHEN CAST(u.created_at AS INTEGER) > 0 THEN CAST(u.created_at AS INTEGER)
                        ELSE ${UNIX_NOW}
                    END AS INTEGER) AS created_at,
                    CAST(CASE
                        WHEN CAST(u.updated_at AS INTEGER) > 1000000000000 THEN CAST(u.updated_at AS INTEGER) / 1000
                        WHEN CAST(u.updated_at AS INTEGER) > 0 THEN CAST(u.updated_at AS INTEGER)
                        WHEN CAST(u.created_at AS INTEGER) > 1000000000000 THEN CAST(u.created_at AS INTEGER) / 1000
                        WHEN CAST(u.created_at AS INTEGER) > 0 THEN CAST(u.created_at AS INTEGER)
                        ELSE ${UNIX_NOW}
                    END AS INTEGER) AS updated_at
                FROM user_vods u
                WHERE u.vod_id IS NOT NULL AND TRIM(CAST(u.vod_id AS TEXT)) != ''
                  AND u.user_id IS NOT NULL AND TRIM(CAST(u.user_id AS TEXT)) != '';
            `)
        }

        await db.exec(`
            INSERT OR REPLACE INTO user_vods__rebuilt (vod_id, user_id, title, thumbnail_url, duration, category, tags, created_at, updated_at)
            SELECT
                CAST(${vodId} AS TEXT) AS vod_id,
                CAST(${userId} AS TEXT) AS user_id,
                ${titleSql} AS title,
                NULLIF(TRIM(${thumbCol}), '') AS thumbnail_url,
                NULLIF(TRIM(${durationCol}), '') AS duration,
                NULLIF(TRIM(${categoryCol}), '') AS category,
                ${tagsCol === 'NULL' ? 'NULL' : tagsCol} AS tags,
                CAST((${createdExpr}) AS INTEGER) AS created_at,
                CAST((${updatedExpr}) AS INTEGER) AS updated_at
            FROM vods o
            WHERE ${vodId} IS NOT NULL AND TRIM(CAST(${vodId} AS TEXT)) != ''
              AND ${userId} IS NOT NULL AND TRIM(CAST(${userId} AS TEXT)) != '';
        `)

        await db.exec(`DROP TABLE IF EXISTS vods;`)
        await db.exec(`DROP TABLE IF EXISTS user_vods;`)
        await db.exec(`ALTER TABLE user_vods__rebuilt RENAME TO user_vods;`)
        await db.exec(`COMMIT;`)
        console.log('[gp-live] migrateUserVodsTableSchema: 完成')
    } catch (e) {
        try {
            await db.exec(`ROLLBACK;`)
        } catch {
            // ignore
        }
        throw e
    } finally {
        await db.exec(`PRAGMA foreign_keys = ON;`)
    }
}

const USER_NOTIFICATION_SETTINGS_FINAL_COLUMNS = new Set([
    'notification_setting_id',
    'user_id',
    'push_enabled',
    'record_enabled',
])

function userNotificationSettingsTableOk(names: Set<string>): boolean {
    return (
        names.size === USER_NOTIFICATION_SETTINGS_FINAL_COLUMNS.size &&
        [...USER_NOTIFICATION_SETTINGS_FINAL_COLUMNS].every((c) => names.has(c))
    )
}

/**
 * notification_settings → user_notification_settings；欄位 notification_setting_id / user_id / push_enabled / record_enabled（0/1）。
 */
export async function migrateUserNotificationSettingsTableSchema(db: Db) {
    const hasOld = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'notification_settings' LIMIT 1`
    )
    const hasNew = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'user_notification_settings' LIMIT 1`
    )
    const newPragma = hasNew?.name
        ? await db.all<{ name: string }>(`PRAGMA table_info(user_notification_settings);`)
        : []
    const newNames = new Set(newPragma.map((c) => c.name))
    const newOk = Boolean(hasNew?.name) && userNotificationSettingsTableOk(newNames)

    if (!hasOld?.name && newOk) {
        await db.exec(`DROP TABLE IF EXISTS notification_settings;`)
        return
    }

    if (!hasOld?.name && !hasNew?.name) {
        await db.exec(`
            CREATE TABLE IF NOT EXISTS user_notification_settings (
                notification_setting_id TEXT NOT NULL,
                user_id TEXT NOT NULL,
                push_enabled INTEGER NOT NULL DEFAULT 0 CHECK (push_enabled IN (0, 1)),
                record_enabled INTEGER NOT NULL DEFAULT 0 CHECK (record_enabled IN (0, 1)),
                PRIMARY KEY (user_id, notification_setting_id),
                FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE
            );
        `)
        return
    }

    if (!hasOld?.name && hasNew?.name && !newOk) {
        console.warn(
            '[gp-live] migrateUserNotificationSettingsTableSchema: user_notification_settings 結構非預期且無舊表可重建，略過'
        )
        return
    }

    const oldCols = await db.all<{ name: string }>(`PRAGMA table_info(notification_settings);`)
    const oldNames = new Set(oldCols.map((c) => c.name))

    const sid = colExpr('o', ['notification_setting_id', 'id'], oldNames)
    const uid = colExpr('o', ['user_id', 'userId'], oldNames)
    const push = colExpr('o', ['push_enabled', 'pushEnabled'], oldNames)
    const rec = colExpr('o', ['record_enabled', 'recordEnabled'], oldNames)

    const pushSql = `CASE WHEN COALESCE(CAST(${push} AS INTEGER), 0) != 0 THEN 1 ELSE 0 END`
    const recSql = `CASE WHEN COALESCE(CAST(${rec} AS INTEGER), 0) != 0 THEN 1 ELSE 0 END`

    const shouldMergeNew = Boolean(hasNew?.name && newOk)

    console.log(
        '[gp-live] migrateUserNotificationSettingsTableSchema: 建立 user_notification_settings 並自 notification_settings 匯入…'
    )

    await db.exec(`DROP TABLE IF EXISTS user_notification_settings__rebuilt;`)
    await db.exec(`PRAGMA foreign_keys = OFF;`)
    await db.exec(`BEGIN;`)
    try {
        await db.exec(`
            CREATE TABLE user_notification_settings__rebuilt (
                notification_setting_id TEXT NOT NULL,
                user_id TEXT NOT NULL,
                push_enabled INTEGER NOT NULL DEFAULT 0 CHECK (push_enabled IN (0, 1)),
                record_enabled INTEGER NOT NULL DEFAULT 0 CHECK (record_enabled IN (0, 1)),
                PRIMARY KEY (user_id, notification_setting_id),
                FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE
            );
        `)

        if (shouldMergeNew) {
            await db.exec(`
                INSERT INTO user_notification_settings__rebuilt (
                    notification_setting_id, user_id, push_enabled, record_enabled
                )
                SELECT
                    u.notification_setting_id,
                    u.user_id,
                    CASE WHEN COALESCE(CAST(u.push_enabled AS INTEGER), 0) != 0 THEN 1 ELSE 0 END,
                    CASE WHEN COALESCE(CAST(u.record_enabled AS INTEGER), 0) != 0 THEN 1 ELSE 0 END
                FROM user_notification_settings u
                WHERE u.notification_setting_id IS NOT NULL
                  AND TRIM(CAST(u.notification_setting_id AS TEXT)) != ''
                  AND u.user_id IS NOT NULL
                  AND TRIM(CAST(u.user_id AS TEXT)) != '';
            `)
        }

        await db.exec(`
            INSERT OR REPLACE INTO user_notification_settings__rebuilt (
                notification_setting_id, user_id, push_enabled, record_enabled
            )
            SELECT
                CAST(${sid} AS TEXT) AS notification_setting_id,
                CAST(${uid} AS TEXT) AS user_id,
                CAST((${pushSql}) AS INTEGER) AS push_enabled,
                CAST((${recSql}) AS INTEGER) AS record_enabled
            FROM notification_settings o
            WHERE ${sid} IS NOT NULL AND TRIM(CAST(${sid} AS TEXT)) != ''
              AND ${uid} IS NOT NULL AND TRIM(CAST(${uid} AS TEXT)) != '';
        `)

        await db.exec(`DROP TABLE IF EXISTS notification_settings;`)
        await db.exec(`DROP TABLE IF EXISTS user_notification_settings;`)
        await db.exec(`ALTER TABLE user_notification_settings__rebuilt RENAME TO user_notification_settings;`)
        await db.exec(`COMMIT;`)
        console.log('[gp-live] migrateUserNotificationSettingsTableSchema: 完成')
    } catch (e) {
        try {
            await db.exec(`ROLLBACK;`)
        } catch {
            // ignore
        }
        throw e
    } finally {
        await db.exec(`PRAGMA foreign_keys = ON;`)
    }
}

const SYSTEM_NOTICES_FINAL_COLUMNS = new Set([
    'notice_id',
    'title',
    'content',
    'redirect_url',
    'category',
    'created_at',
    'updated_at',
])

function systemNoticesTableOk(names: Set<string>): boolean {
    return (
        names.size === SYSTEM_NOTICES_FINAL_COLUMNS.size &&
        [...SYSTEM_NOTICES_FINAL_COLUMNS].every((c) => names.has(c))
    )
}

function systemNoticeCreatedUnixExpr(alias: string, names: Set<string>): string {
    const p = `${alias}.`
    const branches: string[] = []
    if (names.has('created_at')) {
        branches.push(
            `WHEN CAST(${p}created_at AS INTEGER) > 1000000000000 THEN CAST(${p}created_at AS INTEGER) / 1000`
        )
        branches.push(`WHEN CAST(${p}created_at AS INTEGER) > 0 THEN CAST(${p}created_at AS INTEGER)`)
    }
    if (names.has('createdAt')) {
        branches.push(
            `WHEN typeof(${p}createdAt)='text' AND CAST(strftime('%s', ${p}createdAt) AS INTEGER) > 0 THEN CAST(strftime('%s', ${p}createdAt) AS INTEGER)`
        )
        branches.push(
            `WHEN typeof(${p}createdAt)='text' AND CAST(${p}createdAt AS INTEGER) > 1000000000000 THEN CAST(${p}createdAt AS INTEGER) / 1000`
        )
        branches.push(
            `WHEN typeof(${p}createdAt)='text' AND CAST(${p}createdAt AS INTEGER) > 0 THEN CAST(${p}createdAt AS INTEGER)`
        )
    }
    if (names.has('createdAtMs')) {
        branches.push(
            `WHEN CAST(${p}createdAtMs AS INTEGER) > 1000000000000 THEN CAST(${p}createdAtMs AS INTEGER) / 1000`
        )
        branches.push(`WHEN CAST(${p}createdAtMs AS INTEGER) > 0 THEN CAST(${p}createdAtMs AS INTEGER)`)
    }
    if (branches.length === 0) return UNIX_NOW
    return `CASE ${branches.join(' ')} ELSE ${UNIX_NOW} END`
}

function systemNoticeUpdatedUnixExpr(alias: string, names: Set<string>, createdExprSql: string): string {
    const p = `${alias}.`
    const branches: string[] = []
    if (names.has('updated_at')) {
        branches.push(
            `WHEN CAST(${p}updated_at AS INTEGER) > 1000000000000 THEN CAST(${p}updated_at AS INTEGER) / 1000`
        )
        branches.push(`WHEN CAST(${p}updated_at AS INTEGER) > 0 THEN CAST(${p}updated_at AS INTEGER)`)
    }
    if (names.has('updatedAt')) {
        branches.push(
            `WHEN typeof(${p}updatedAt)='text' AND CAST(strftime('%s', ${p}updatedAt) AS INTEGER) > 0 THEN CAST(strftime('%s', ${p}updatedAt) AS INTEGER)`
        )
        branches.push(
            `WHEN typeof(${p}updatedAt)='text' AND CAST(${p}updatedAt AS INTEGER) > 1000000000000 THEN CAST(${p}updatedAt AS INTEGER) / 1000`
        )
        branches.push(
            `WHEN typeof(${p}updatedAt)='text' AND CAST(${p}updatedAt AS INTEGER) > 0 THEN CAST(${p}updatedAt AS INTEGER)`
        )
    }
    if (branches.length === 0) return `(${createdExprSql})`
    return `CASE ${branches.join(' ')} ELSE (${createdExprSql}) END`
}

/**
 * notices → system_notices；欄位 notice_id / title / content / redirect_url / category / created_at / updated_at（Unix 秒）。
 */
export async function migrateSystemNoticesTableSchema(db: Db) {
    const hasOld = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'notices' LIMIT 1`
    )
    const hasNew = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'system_notices' LIMIT 1`
    )
    const newPragma = hasNew?.name
        ? await db.all<{ name: string }>(`PRAGMA table_info(system_notices);`)
        : []
    const newNames = new Set(newPragma.map((c) => c.name))
    const newOk = Boolean(hasNew?.name) && systemNoticesTableOk(newNames)

    if (!hasOld?.name && newOk) {
        await db.exec(`DROP TABLE IF EXISTS notices;`)
        return
    }

    if (!hasOld?.name && !hasNew?.name) {
        await db.exec(`
            CREATE TABLE IF NOT EXISTS system_notices (
                notice_id TEXT PRIMARY KEY,
                title TEXT NOT NULL,
                content TEXT,
                redirect_url TEXT,
                category TEXT NOT NULL DEFAULT 'general',
                created_at INTEGER NOT NULL DEFAULT (${UNIX_NOW}),
                updated_at INTEGER NOT NULL DEFAULT (${UNIX_NOW})
            );
        `)
        return
    }

    if (!hasOld?.name && hasNew?.name && !newOk) {
        console.warn(
            '[gp-live] migrateSystemNoticesTableSchema: system_notices 結構非預期且無 notices 可重建，略過'
        )
        return
    }

    const oldCols = await db.all<{ name: string }>(`PRAGMA table_info(notices);`)
    const oldNames = new Set(oldCols.map((c) => c.name))

    const nid = colExpr('o', ['notice_id', 'id'], oldNames)
    const titleCol = colExpr('o', ['title'], oldNames)
    const contentCol = colExpr('o', ['content'], oldNames)
    const redirectCol = colExpr('o', ['redirect_url', 'url'], oldNames)
    const categoryCol = colExpr('o', ['category'], oldNames)

    const titleSql = `COALESCE(NULLIF(TRIM(${titleCol}), ''), '(notice)')`
    const createdExpr = systemNoticeCreatedUnixExpr('o', oldNames)
    const updatedExpr = systemNoticeUpdatedUnixExpr('o', oldNames, createdExpr)

    const shouldMergeNew = Boolean(hasNew?.name && newOk)

    console.log('[gp-live] migrateSystemNoticesTableSchema: 建立 system_notices 並自 notices 匯入…')

    await db.exec(`DROP TABLE IF EXISTS system_notices__rebuilt;`)
    await db.exec(`PRAGMA foreign_keys = OFF;`)
    await db.exec(`BEGIN;`)
    try {
        await db.exec(`
            CREATE TABLE system_notices__rebuilt (
                notice_id TEXT PRIMARY KEY,
                title TEXT NOT NULL,
                content TEXT,
                redirect_url TEXT,
                category TEXT NOT NULL DEFAULT 'general',
                created_at INTEGER NOT NULL,
                updated_at INTEGER NOT NULL
            );
        `)

        if (shouldMergeNew) {
            await db.exec(`
                INSERT INTO system_notices__rebuilt (
                    notice_id, title, content, redirect_url, category, created_at, updated_at
                )
                SELECT
                    s.notice_id,
                    s.title,
                    s.content,
                    s.redirect_url,
                    COALESCE(NULLIF(TRIM(s.category), ''), 'general'),
                    CAST(CASE
                        WHEN CAST(s.created_at AS INTEGER) > 1000000000000 THEN CAST(s.created_at AS INTEGER) / 1000
                        WHEN CAST(s.created_at AS INTEGER) > 0 THEN CAST(s.created_at AS INTEGER)
                        ELSE ${UNIX_NOW}
                    END AS INTEGER) AS created_at,
                    CAST(CASE
                        WHEN CAST(s.updated_at AS INTEGER) > 1000000000000 THEN CAST(s.updated_at AS INTEGER) / 1000
                        WHEN CAST(s.updated_at AS INTEGER) > 0 THEN CAST(s.updated_at AS INTEGER)
                        WHEN CAST(s.created_at AS INTEGER) > 1000000000000 THEN CAST(s.created_at AS INTEGER) / 1000
                        WHEN CAST(s.created_at AS INTEGER) > 0 THEN CAST(s.created_at AS INTEGER)
                        ELSE ${UNIX_NOW}
                    END AS INTEGER) AS updated_at
                FROM system_notices s
                WHERE s.notice_id IS NOT NULL AND TRIM(CAST(s.notice_id AS TEXT)) != '';
            `)
        }

        await db.exec(`
            INSERT OR REPLACE INTO system_notices__rebuilt (
                notice_id, title, content, redirect_url, category, created_at, updated_at
            )
            SELECT
                CAST(${nid} AS TEXT) AS notice_id,
                ${titleSql} AS title,
                ${contentCol === 'NULL' ? 'NULL' : contentCol} AS content,
                NULLIF(TRIM(${redirectCol}), '') AS redirect_url,
                COALESCE(NULLIF(TRIM(${categoryCol}), ''), 'general') AS category,
                CAST((${createdExpr}) AS INTEGER) AS created_at,
                CAST((${updatedExpr}) AS INTEGER) AS updated_at
            FROM notices o
            WHERE ${nid} IS NOT NULL AND TRIM(CAST(${nid} AS TEXT)) != '';
        `)

        await db.exec(`DROP TABLE IF EXISTS notices;`)
        await db.exec(`DROP TABLE IF EXISTS system_notices;`)
        await db.exec(`ALTER TABLE system_notices__rebuilt RENAME TO system_notices;`)
        await db.exec(`COMMIT;`)
        console.log('[gp-live] migrateSystemNoticesTableSchema: 完成')
    } catch (e) {
        try {
            await db.exec(`ROLLBACK;`)
        } catch {
            // ignore
        }
        throw e
    } finally {
        await db.exec(`PRAGMA foreign_keys = ON;`)
    }
}

/**
 * 刪除歷史上已改名之舊表（僅 DROP IF EXISTS，不影響現用表名）。
 * 於所有 schema 遷移跑完後執行，避免庫內殘留舊表名。
 */
export async function dropRenamedLegacyTables(db: Db) {
    await db.exec(`PRAGMA foreign_keys = OFF;`)
    try {
        await db.exec(`
            DROP TABLE IF EXISTS avatar_frames;
            DROP TABLE IF EXISTS gifts;
            DROP TABLE IF EXISTS event_banners;
            DROP TABLE IF EXISTS login_sessions;
            DROP TABLE IF EXISTS allowed_devices;
            DROP TABLE IF EXISTS user_allowed_devieces;
            DROP TABLE IF EXISTS login_history;
            DROP TABLE IF EXISTS login_settings;
            DROP TABLE IF EXISTS live_streams;
            DROP TABLE IF EXISTS live_stream_likes;
            DROP TABLE IF EXISTS room_likes;
            DROP TABLE IF EXISTS chat_messages;
            DROP TABLE IF EXISTS board_posts;
            DROP TABLE IF EXISTS board_post_comments;
            DROP TABLE IF EXISTS user_follows;
            DROP TABLE IF EXISTS user_blocks;
            DROP TABLE IF EXISTS vods;
            DROP TABLE IF EXISTS notification_settings;
            DROP TABLE IF EXISTS notices;
            DROP TABLE IF EXISTS gift_transactions;
            DROP TABLE IF EXISTS profile_ring;
            DROP TABLE IF EXISTS profile_rings;
            DROP TABLE IF EXISTS rank_gift;
            DROP TABLE IF EXISTS rank_star;
            DROP TABLE IF EXISTS rank_group;
        `)
    } finally {
        await db.exec(`PRAGMA foreign_keys = ON;`)
    }
}

/** 清除中斷遷移殘留的備份表，避免「table already exists」導致登入等 API 失敗 */
async function cleanupRoomStreamsMigrationArtifacts(db: Db) {
    await db.exec(`
        DROP TABLE IF EXISTS room_streams__src_bak;
        DROP TABLE IF EXISTS room_streams__stream_key_order_bak;
        DROP TABLE IF EXISTS room_streams__live_input_id_order_bak;
        DROP TABLE IF EXISTS room_streams__live_input_order_bak;
        DROP TABLE IF EXISTS room_streams__col_order_bak;
        DROP TABLE IF EXISTS room_streams__lifecycle_bak;
        DROP TABLE IF EXISTS room_streams__fk_bak;
        DROP TABLE IF EXISTS room_streams__attr_order_bak;
        DROP TABLE IF EXISTS room_streams__desc_order_bak;
        DROP TABLE IF EXISTS room_pinned_message__fk_bak;
        DROP TABLE IF EXISTS room_stream_likes__fk_bak;
        DROP TABLE IF EXISTS room_mods__fk_bak;
    `)
}

/**
 * 每次取得 DB 連線後呼叫：先遷移 groups（避免 migrateUsers 拋錯時永遠跑不到 groups），再遷移 users。
 * 重複呼叫安全（已符合目標結構時會快速 return）。
 */
export async function runUserAndGroupsSchemaMigrations(db: Db) {
    await cleanupRoomStreamsMigrationArtifacts(db)

    /** PostgreSQL 使用 initCoreSchema 建立最終結構，略過 SQLite 專用歷史遷移 */
    if (isPostgresDb(db)) {
        await dropRenamedLegacyTables(db)
        await dropGiftTransactionsTableIfExists(db)
        return
    }

    await migrateGroupsTableSchema(db)
    await ensureGroupsDisplayIdColumn(db)
    await migrateUserGroupsTableSchema(db)
    await migrateUserGroupsJoinAtDefaultColumn(db)
    await migrateGroupApplyTableSchema(db)
    const { migrateGroupLogsTableSchema } = await import('@/server/groups/migrateGroupLogsSchema')
    await migrateGroupLogsTableSchema(db)
    await migrateUsersTableSchema(db)
    await migrateUserSiteAdminTableSchema(db)
    await migrateUserVodsTableSchema(db)
    await migrateUserNotificationSettingsTableSchema(db)
    await migrateUserActionsTableSchema(db)
    await migrateUserBoardPostsTableSchema(db)
    await migrateUserBoardPostCommentsTableSchema(db)
    await migrateConfigRoomGiftsTableSchema(db)
    await migrateRankTablesSchema(db)
    await migrateIndexEventBannersTableSchema(db)
    await migrateSystemNoticesTableSchema(db)
    await migrateUserLoginSessionsTableSchema(db)
    await migrateUserAllowedDevicesTableSchema(db)
    await migrateUserLoginHistoryTableSchema(db)
    await migrateRoomModsTableSchema(db)
    await migrateRoomBansTableSchema(db)
    await dropRoomStreamsTagsColumn(db)
    await migrateRoomStreamsTableSchema(db)
    await migrateRoomStreamsLiveTokenColumn(db)
    await migrateRoomStreamsDropCheckLiveTokenColumn(db)
    await migrateRoomStreamsStreamStatusDropIsLive(db)
    await migrateRoomStreamsStreamTokenColumn(db)
    await ensureRoomStreamsLiveInputIdentifierColumn(db)
    await ensureRoomStreamsCategoryColumn(db)
    await ensureRoomStreamsAttributeColumn(db)
    await ensureRoomStreamsDescriptionColumn(db)
    await dropRoomStreamsTagsColumn(db)
    await migrateRoomStreamLikesTableSchema(db)
    await migrateDropChatMessagesTableSchema(db)
    await migrateCatchTableSchema(db)
    await migrateCatchLikesTableSchema(db)
    await migrateCatchCommentsAndLikesTableSchema(db)
    await migrateUserNotificationsTableSchema(db)
    await migrateUserLoginSettingsTableSchema(db)
    await migrateConfigAvatarFramesTableSchema(db)
    await migrateUserAvatarFramesTableSchema(db)
    await migrateLegacyProfileRingTableSchema(db)
    await dropGiftTransactionsTableIfExists(db)
    await dropRenamedLegacyTables(db)

    await ensureGroupsDisplayIdColumn(db)
}

/** 補齊 groups.display_id，並將非 8 位展示 ID 修正為合法 display_id */
export async function ensureGroupsDisplayIdColumn(db: Db) {
    const hasGroups = await db.get<{ name: string }>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'groups' LIMIT 1`
    )
    if (!hasGroups?.name) return

    const cols = await db.all<{ name: string }>(`PRAGMA table_info(groups);`)
    if (!cols?.some((c) => c.name === 'display_id')) {
        console.log('[gp-live] ensureGroupsDisplayIdColumn: groups 缺少 display_id，重建表…')
        await migrateGroupsTableSchema(db)
    }

    const { migrateGroupsDisplayIdSchema } = await import('@/server/groups/migrateGroupsDisplayIdSchema')
    await migrateGroupsDisplayIdSchema(db)
}