import { randomInt, randomUUID } from 'node:crypto'
import { isValidGroupDisplayId } from '@/lib/groups/groupDisplayId'
import { isUuidV4, legacyGroupIdAsDisplayId } from '@/server/groups/groupIdUtils'
import type { getDb } from '@/server/db'

type Db = Awaited<ReturnType<typeof getDb>>

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

const GROUPS_WITH_DISPLAY_ID_CREATE_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 (${UNIX_NOW}),
        updated_at INTEGER NOT NULL DEFAULT (${UNIX_NOW})
    );
`

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 pickDisplayId(oldGroupId: string, used: Set<string>): string {
    const legacy = legacyGroupIdAsDisplayId(oldGroupId)
    if (legacy && !used.has(legacy)) {
        return legacy
    }
    for (let i = 0; i < 200; i++) {
        const candidate = String(randomInt(10000000, 999999999))
        if (!used.has(candidate)) {
            return candidate
        }
    }
    return String(Date.now()).slice(-8)
}

/**
 * 為 groups 補 display_id，並將 group_id 改為 UUID v4；同步更新 user_groups / group_apply 外鍵。
 */
export async function migrateGroupsDisplayIdSchema(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?.length) return

    const names = new Set(cols.map((c) => c.name))
    const hasDisplayId = names.has('display_id')

    const rows =
        (await db.all(
            `SELECT
            group_id,
            ${hasDisplayId ? 'display_id' : 'NULL AS display_id'},
            name, avatar_url, banner_url, is_verified, is_can_application,
            group_level_exp, created_at, updated_at
         FROM groups`
        )) as Array<{
            group_id: string
            display_id: string | null
            name: string
            avatar_url: string | null
            banner_url: string | null
            is_verified: number
            is_can_application: number
            group_level_exp: number
            created_at: number
            updated_at: number
        }>

    const needsUuid = rows.some((r) => !isUuidV4(String(r.group_id ?? '')))
    const needsDisplay =
        !hasDisplayId ||
        rows.some((r) => !isValidGroupDisplayId(String(r.display_id ?? '').trim())) ||
        !groupsDisplayIdColumnInOrder(cols)

    if (!needsUuid && !needsDisplay) {
        await db.exec(`CREATE UNIQUE INDEX IF NOT EXISTS idx_groups_display_id ON groups(display_id);`)
        return
    }

    console.log('[gp-live] migrateGroupsDisplayIdSchema: 新增 display_id 並將 group_id 改為 UUID v4…')

    const usedDisplay = new Set<string>()
    const mapping = rows.map((row) => {
        const oldGroupId = String(row.group_id ?? '').trim()
        const newGroupId = randomUUID()
        const existingDisplay = String(row.display_id ?? '').trim()
        let displayId = isValidGroupDisplayId(existingDisplay) ? existingDisplay : ''
        if (!displayId) {
            displayId = pickDisplayId(oldGroupId, usedDisplay)
        }
        while (usedDisplay.has(displayId)) {
            displayId = pickDisplayId(oldGroupId, usedDisplay)
        }
        usedDisplay.add(displayId)
        return { oldGroupId, newGroupId, displayId, row }
    })

    await db.exec(`DROP TABLE IF EXISTS groups__display_mig;`)
    await db.exec(`DROP TABLE IF EXISTS user_groups__display_mig;`)
    await db.exec(`DROP TABLE IF EXISTS group_apply__display_mig;`)
    await db.exec(`DROP TABLE IF EXISTS group_id_map__display_mig;`)

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

        await db.exec(`
            CREATE TABLE group_id_map__display_mig (
                old_group_id TEXT PRIMARY KEY,
                new_group_id TEXT NOT NULL
            );
        `)

        for (const m of mapping) {
            const r = m.row
            await db.run(
                `INSERT INTO groups__display_mig (
                    group_id, display_id, name, avatar_url, banner_url,
                    is_verified, is_can_application, group_level_exp, created_at, updated_at
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
                [
                    m.newGroupId,
                    m.displayId,
                    r.name,
                    r.avatar_url,
                    r.banner_url,
                    r.is_verified ?? 0,
                    r.is_can_application ?? 1,
                    r.group_level_exp ?? 0,
                    r.created_at,
                    r.updated_at,
                ]
            )
            await db.run(
                `INSERT INTO group_id_map__display_mig (old_group_id, new_group_id) VALUES (?, ?)`,
                [m.oldGroupId, m.newGroupId]
            )
        }

        const hasUg = await db.get<{ name: string }>(
            `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'user_groups' LIMIT 1`
        )
        if (hasUg?.name) {
            await db.exec(`
                CREATE TABLE user_groups__display_mig (
                    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__display_mig(group_id) ON DELETE CASCADE
                );
            `)
            await db.exec(`
                INSERT INTO user_groups__display_mig (user_id, group_id, role, contribution, join_at)
                SELECT ug.user_id, m.new_group_id, ug.role, COALESCE(CAST(ug.contribution AS INTEGER), 0), ug.join_at
                FROM user_groups ug
                INNER JOIN group_id_map__display_mig m ON m.old_group_id = ug.group_id
            `)
            await db.exec(`DROP TABLE user_groups;`)
            await db.exec(`ALTER TABLE user_groups__display_mig RENAME TO user_groups;`)
        }

        const hasApply = await db.get<{ name: string }>(
            `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'group_apply' LIMIT 1`
        )
        if (hasApply?.name) {
            await db.exec(`
                CREATE TABLE group_apply__display_mig (
                    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__display_mig(group_id) ON DELETE CASCADE,
                    FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE
                );
            `)
            await db.exec(`
                INSERT INTO group_apply__display_mig (group_id, user_id, application_at)
                SELECT m.new_group_id, ga.user_id, ga.application_at
                FROM group_apply ga
                INNER JOIN group_id_map__display_mig m ON m.old_group_id = ga.group_id
            `)
            await db.exec(`DROP TABLE group_apply;`)
            await db.exec(`ALTER TABLE group_apply__display_mig RENAME TO group_apply;`)
        }

        await db.exec(`DROP TABLE groups;`)
        await db.exec(`ALTER TABLE groups__display_mig RENAME TO groups;`)
        await db.exec(`DROP TABLE group_id_map__display_mig;`)
        await db.exec(`CREATE UNIQUE INDEX IF NOT EXISTS idx_groups_display_id ON groups(display_id);`)

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