import {
    AVATAR_FRAME_CATALOG_PATH,
    buildAvatarFrameIdMigrationMap,
    buildAvatarFrameItemId,
    catalogItemToDbType,
    normalizeCatalogItemName,
    normalizeCatalogItemTag,
    normalizeCatalogItemUrl,
    readAvatarFrameCatalogFromDisk,
} from '@/lib/avatarFrames/catalog'
import type { GpLiveDb } from '@/server/gpLiveDb'

type Db = GpLiveDb

const USER_FRAMES_BACKUP_TABLE = 'user_avatar_frames__sync_backup'

type UserFrameBackupRow = {
    user_id: string
    frame_id: string
    is_used: number
    acquired_at: number
    expiration_at: number | null
}

function resolveFrameId(frameId: string, idMap: Map<string, string>, validIds: Set<string>): string | null {
    const tryResolve = (key: string): string | null => {
        const mapped = idMap.get(key) ?? key
        return validIds.has(mapped) ? mapped : null
    }

    const direct = tryResolve(frameId)
    if (direct) return direct

    const legacy = /^[A-Za-z]{2}:(\d+)$/.exec(String(frameId).trim())
    if (legacy) {
        const byRingId = tryResolve(legacy[1])
        if (byRingId) return byRingId
    }

    return null
}

/** 同一使用者同一框只保留一筆（優先 is_used，其次較新 acquired_at） */
function dedupeUserFrameRows(
    rows: UserFrameBackupRow[],
    idMap: Map<string, string>,
    validIds: Set<string>
): UserFrameBackupRow[] {
    const byKey = new Map<string, UserFrameBackupRow>()
    for (const row of rows) {
        const frameId = resolveFrameId(row.frame_id, idMap, validIds)
        if (!frameId) continue
        const key = `${row.user_id}\0${frameId}`
        const next: UserFrameBackupRow = { ...row, frame_id: frameId }
        const prev = byKey.get(key)
        if (!prev) {
            byKey.set(key, next)
            continue
        }
        if (
            next.is_used > prev.is_used ||
            (next.is_used === prev.is_used && next.acquired_at >= prev.acquired_at)
        ) {
            byKey.set(key, next)
        }
    }
    return [...byKey.values()]
}

async function backupUserAvatarFrames(db: Db): Promise<number> {
    await db.exec(`DROP TABLE IF EXISTS ${USER_FRAMES_BACKUP_TABLE}`)
    await db.exec(
        `CREATE TABLE ${USER_FRAMES_BACKUP_TABLE} AS SELECT user_id, frame_id, is_used, acquired_at, expiration_at FROM user_avatar_frames`
    )
    const row = await db.get<{ c: number }>(`SELECT COUNT(*) AS c FROM ${USER_FRAMES_BACKUP_TABLE}`)
    return row?.c ?? 0
}

async function restoreUserAvatarFramesFromBackup(
    db: Db,
    idMap: Map<string, string>,
    validIds: Set<string>
): Promise<{ restored: number; skipped: number }> {
    const backupRows = await db.all<UserFrameBackupRow>(
        `SELECT user_id, frame_id, is_used, acquired_at, expiration_at FROM ${USER_FRAMES_BACKUP_TABLE}`
    )
    let skipped = 0
    for (const row of backupRows ?? []) {
        if (!resolveFrameId(row.frame_id, idMap, validIds)) skipped++
    }
    const rows = dedupeUserFrameRows(backupRows ?? [], idMap, validIds)

    if (rows.length > 0) {
        const stmt = await db.prepare(
            `INSERT INTO user_avatar_frames (user_id, frame_id, is_used, acquired_at, expiration_at)
             VALUES (?, ?, ?, ?, ?)`
        )
        for (const row of rows) {
            await stmt.run(
                row.user_id,
                row.frame_id,
                row.is_used,
                row.acquired_at,
                row.expiration_at
            )
        }
        await stmt.finalize()
    }

    await db.exec(`DROP TABLE IF EXISTS ${USER_FRAMES_BACKUP_TABLE}`)
    return { restored: rows.length, skipped }
}

async function syncConfigAvatarFramesTable(
    db: Db,
    items: ReturnType<typeof readAvatarFrameCatalogFromDisk>,
    nowSec: number
): Promise<void> {
    const catalogIds = items.map((item) => buildAvatarFrameItemId(item))
    if (catalogIds.length > 0) {
        const placeholders = catalogIds.map(() => '?').join(', ')
        await db.run(
            `DELETE FROM config_avatar_frames WHERE item_id NOT IN (${placeholders})`,
            catalogIds
        )
    } else {
        await db.run('DELETE FROM config_avatar_frames')
    }

    const stmt = await db.prepare(
        `INSERT OR REPLACE INTO config_avatar_frames (item_id, name, img_url, type, tag, amount, created_at)
         VALUES (?, ?, ?, ?, ?, ?, COALESCE(
             (SELECT created_at FROM config_avatar_frames WHERE item_id = ?),
             ?
         ));`
    )
    for (const item of items) {
        const itemId = buildAvatarFrameItemId(item)
        const name = normalizeCatalogItemName(item)
        const imgUrl = normalizeCatalogItemUrl(String(item.url || '').trim())
        const type = catalogItemToDbType(item.type)
        const tag = normalizeCatalogItemTag(item.tag)
        await stmt.run(itemId, name, imgUrl, type, tag, 0, itemId, nowSec)
    }
    await stmt.finalize()
}

/**
 * 從 public/lotties/ring/item.json 同步 config_avatar_frames。
 * 同步前將 user_avatar_frames 備份至臨時表，更新 config 後再還原（含 item_id 對照）。
 */
export async function seedConfigAvatarFramesFromCatalog(
    db: Db,
    catalogPath: string = AVATAR_FRAME_CATALOG_PATH
): Promise<{ inserted: number; backedUp: number; restored: number; skipped: number }> {
    const items = readAvatarFrameCatalogFromDisk(catalogPath)
    if (items.length === 0) {
        return { inserted: 0, backedUp: 0, restored: 0, skipped: 0 }
    }

    const idMap = buildAvatarFrameIdMigrationMap(items)
    const validIds = new Set(items.map((item) => buildAvatarFrameItemId(item)))
    const nowSec = Math.floor(Date.now() / 1000)

    await db.exec('PRAGMA foreign_keys = OFF')
    await db.exec('BEGIN')
    try {
        const backedUp = await backupUserAvatarFrames(db)
        await db.run('DELETE FROM user_avatar_frames')

        await syncConfigAvatarFramesTable(db, items, nowSec)

        const { restored, skipped } = await restoreUserAvatarFramesFromBackup(db, idMap, validIds)

        await db.exec('COMMIT')
        return { inserted: items.length, backedUp, restored, skipped }
    } catch (e) {
        await db.exec('ROLLBACK')
        await db.exec(`DROP TABLE IF EXISTS ${USER_FRAMES_BACKUP_TABLE}`)
        throw e
    } finally {
        await db.exec('PRAGMA foreign_keys = ON')
    }
}

/**
 * 為每位使用者補齊 catalog 中尚未持有的頭像框（不刪除既有、不變更 is_used）。
 */
export async function grantMissingCatalogFramesToAllUsers(db: Db): Promise<{ added: number }> {
    const catalogCount = await db.get<{ c: number }>(
        'SELECT COUNT(*) AS c FROM config_avatar_frames'
    )
    if (!catalogCount?.c) return { added: 0 }

    const userCount = await db.get<{ c: number }>('SELECT COUNT(*) AS c FROM users')
    if (!userCount?.c) return { added: 0 }

    const nowSec = Math.floor(Date.now() / 1000)
    const result = await db.run(
        `INSERT OR IGNORE INTO user_avatar_frames (user_id, frame_id, is_used, acquired_at)
         SELECT u.user_id, c.item_id, 0, ?
         FROM users u
         CROSS JOIN config_avatar_frames c`,
        nowSec
    )
    return { added: result.changes ?? 0 }
}