/**
 * 將既有 SQLite 資料匯入 PostgreSQL（需先設定 DATABASE_URL，且 Postgres 已跑過 schema 初始化）。
 *
 * 用法（會自動讀取 gp-live/.env）：
 *   npm run db:migrate-to-postgres
 */
import fs from 'node:fs'
import path from 'node:path'
import { open } from 'sqlite'
import sqlite3 from 'sqlite3'
import { Pool, type PoolClient } from 'pg'
import { loadEnvFile } from './loadEnvFile'

loadEnvFile()

const SQLITE_PATH =
    process.env.GP_LIVE_DB_PATH?.trim() || path.join(process.cwd(), 'data', 'gp-live.sqlite')
const DATABASE_URL = process.env.DATABASE_URL?.trim()

/** 依 FK 依賴排序的表（子表在後） */
const TABLE_ORDER = [
    'users',
    'user_site_admin',
    'config_room_gifts',
    'config_avatar_frames',
    'groups',
    'user_vods',
    'room_streams',
    'room_pinned_message',
    'user_board_posts',
    'room_stream_likes',
    'user_actions',
    'room_bans',
    'user_login_history',
    'user_login_sessions',
    'user_allowed_devices',
    'user_login_settings',
    'user_notification_settings',
    'user_notifications',
    'system_notices',
    'index_event_banners',
    'room_mods',
    'catch',
    'catch_likes',
    'user_avatar_frames',
    'rank_rich',
    'rank_stream',
    'user_groups',
    'group_apply',
    'group_logs',
    'catch_comments',
    'catch_comment_likes',
    'user_board_post_comments',
]

type RefSets = {
    userIds: Set<string>
    groupIds: Set<string>
    giftIds: Set<string>
    frameIds: Set<string>
    catchIds: Set<string>
    postIds: Set<string>
    commentIds: Set<string>
}

async function listSqliteTables(sqlite: Awaited<ReturnType<typeof open>>): Promise<string[]> {
    const rows = await sqlite.all<{ name: string }[]>(
        `SELECT name FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'sqlite_%' ORDER BY name`
    )
    return (rows ?? []).map((r) => r.name)
}

async function loadIdSet(sqlite: Awaited<ReturnType<typeof open>>, table: string, col: string): Promise<Set<string>> {
    try {
        const rows = await sqlite.all<{ id: unknown }[]>(`SELECT "${col}" AS id FROM "${table}"`)
        return new Set((rows ?? []).map((r) => String(r.id ?? '').trim()).filter(Boolean))
    } catch {
        return new Set()
    }
}

async function buildRefSetsFromSqlite(sqlite: Awaited<ReturnType<typeof open>>): Promise<RefSets> {
    const [userIds, groupIds, giftIds, frameIds, catchIds, postIds] = await Promise.all([
        loadIdSet(sqlite, 'users', 'user_id'),
        loadIdSet(sqlite, 'groups', 'group_id'),
        loadIdSet(sqlite, 'config_room_gifts', 'gift_id'),
        loadIdSet(sqlite, 'config_avatar_frames', 'item_id'),
        loadIdSet(sqlite, 'catch', 'catch_id'),
        loadIdSet(sqlite, 'user_board_posts', 'post_id'),
    ])
    const commentIds = new Set<string>()
    try {
        const cc = await sqlite.all<{ id: string }[]>(`SELECT comment_id AS id FROM catch_comments`)
        for (const r of cc ?? []) commentIds.add(String(r.id))
    } catch {
        /* ignore */
    }
    try {
        const bc = await sqlite.all<{ id: string }[]>(
            `SELECT comment_id AS id FROM user_board_post_comments`
        )
        for (const r of bc ?? []) commentIds.add(String(r.id))
    } catch {
        /* ignore */
    }
    return { userIds, groupIds, giftIds, frameIds, catchIds, postIds, commentIds }
}

/** 略過 SQLite 中已無父列的孤兒資料，避免 Postgres FK 錯誤 */
function filterOrphanRows(
    table: string,
    rows: Record<string, unknown>[],
    refs: RefSets
): { kept: Record<string, unknown>[]; skipped: number } {
    const kept: Record<string, unknown>[] = []
    let skipped = 0

    const needUser = (id: unknown): boolean => {
        const s = String(id ?? '').trim()
        return s.length > 0 && refs.userIds.has(s)
    }
    const needGroup = (id: unknown): boolean => {
        const s = String(id ?? '').trim()
        return s.length > 0 && refs.groupIds.has(s)
    }

    for (const row of rows) {
        let ok = true

        switch (table) {
            case 'group_logs':
                ok = needGroup(row.group_id) && needUser(row.user_id)
                if (ok && row.target_id != null && String(row.target_id).trim() !== '') {
                    ok = needUser(row.target_id)
                }
                break
            case 'user_groups':
            case 'group_apply':
                ok = needUser(row.user_id) && needGroup(row.group_id)
                break
            case 'user_site_admin':
            case 'user_vods':
            case 'user_login_history':
            case 'user_login_sessions':
            case 'user_allowed_devices':
            case 'user_login_settings':
            case 'user_notification_settings':
            case 'user_notifications':
                ok = needUser(row.user_id)
                break
            case 'room_streams':
            case 'room_pinned_message':
            case 'room_stream_likes':
            case 'room_mods':
                ok = needUser(row.room_id ?? row.user_id)
                if (table === 'room_stream_likes' || table === 'room_mods') {
                    ok = ok && needUser(row.user_id)
                }
                break
            case 'room_bans':
                ok = needUser(row.user_id) && needUser(row.banned_by)
                break
            case 'user_actions':
                ok = needUser(row.user_id) && needUser(row.target_id)
                break
            case 'user_avatar_frames':
                ok =
                    needUser(row.user_id) &&
                    refs.frameIds.has(String(row.frame_id ?? '').trim())
                break
            case 'rank_rich':
                ok = needUser(row.user_id)
                if (ok && row.target_id != null && String(row.target_id).trim() !== '') {
                    ok = needUser(row.target_id)
                }
                if (ok && row.gift_id != null && String(row.gift_id).trim() !== '') {
                    ok = refs.giftIds.has(String(row.gift_id))
                }
                break
            case 'rank_stream':
                ok = needUser(row.user_id)
                if (ok && row.target_id != null && String(row.target_id).trim() !== '') {
                    ok = needUser(row.target_id)
                }
                if (ok && row.gift_id != null && String(row.gift_id).trim() !== '') {
                    ok = refs.giftIds.has(String(row.gift_id))
                }
                break
            case 'catch':
            case 'catch_likes':
                if (table === 'catch') ok = needUser(row.user_id)
                else ok = refs.catchIds.has(String(row.catch_id ?? '')) && needUser(row.user_id)
                break
            case 'catch_comments':
                ok = refs.catchIds.has(String(row.catch_id ?? '')) && needUser(row.user_id)
                break
            case 'catch_comment_likes':
                ok = refs.commentIds.has(String(row.comment_id ?? '')) && needUser(row.user_id)
                break
            case 'user_board_posts':
                ok = needUser(row.user_id)
                break
            case 'user_board_post_comments':
                ok = refs.postIds.has(String(row.post_id ?? '')) && needUser(row.user_id)
                break
            default:
                break
        }

        if (ok) kept.push(row)
        else skipped++
    }

    return { kept, skipped }
}

async function importTable(
    client: PoolClient,
    sqlite: Awaited<ReturnType<typeof open>>,
    table: string,
    refs: RefSets
): Promise<void> {
    const cols = await sqlite.all<{ name: string }[]>(`PRAGMA table_info(${table})`)
    if (!cols?.length) return

    const colNames = cols.map((c) => c.name)
    const rawRows = await sqlite.all<Record<string, unknown>[]>(`SELECT * FROM "${table}"`)
    if (!rawRows?.length) {
        console.log(`[migrate] ${table}: 0 列，略過`)
        return
    }

    const { kept, skipped: orphanSkipped } = filterOrphanRows(table, rawRows ?? [], refs)
    let skipped = orphanSkipped
    if (!kept.length) {
        console.log(`[migrate] ${table}: ${rawRows.length} 列來源，0 列寫入（略過孤兒 ${skipped} 列）`)
        return
    }

    const placeholders = colNames.map((_, i) => `$${i + 1}`).join(', ')
    const insertSql = `INSERT INTO "${table}" (${colNames.map((c) => `"${c}"`).join(', ')}) VALUES (${placeholders})`

    let inserted = 0
    for (const row of kept) {
        const values = colNames.map((c) => row[c] ?? null)
        try {
            const res = await client.query(insertSql, values)
            inserted += res.rowCount ?? 0
        } catch (e: unknown) {
            const code =
                e && typeof e === 'object' && 'code' in e ? String((e as { code: string }).code) : ''
            if (code === '23503' || code === '23505') {
                skipped++
                continue
            }
            throw e
        }
    }

    const skipNote = skipped > 0 ? `，略過孤兒/衝突 ${skipped} 列` : ''
    console.log(`[migrate] ${table}: ${rawRows.length} 列來源，寫入 ${inserted} 列${skipNote}`)
}

async function main() {
    if (!DATABASE_URL) {
        console.error(
            '請在 gp-live/.env 設定 DATABASE_URL，或於執行前設定環境變數。\n' +
                '範例：DATABASE_URL=postgresql://gp_live:密碼@127.0.0.1:5432/gp_live'
        )
        process.exit(1)
    }
    if (!fs.existsSync(SQLITE_PATH)) {
        console.error(`找不到 SQLite 檔案：${SQLITE_PATH}`)
        process.exit(1)
    }

    const sqlite = await open({ filename: SQLITE_PATH, driver: sqlite3.Database })
    const pool = new Pool({ connectionString: DATABASE_URL })
    const client = await pool.connect()

    try {
        const refs = await buildRefSetsFromSqlite(sqlite)
        const existing = new Set(await listSqliteTables(sqlite))
        const tables = TABLE_ORDER.filter((t) => existing.has(t))
        const extras = [...existing].filter((t) => !TABLE_ORDER.includes(t)).sort()
        const allTables = [...tables, ...extras]

        console.log(`[migrate] 將匯入 ${allTables.length} 張表（略過無對應父列的孤兒資料）…`)

        await client.query('BEGIN')
        await client.query('SAVEPOINT migrate_start')
        try {
            await client.query(`SET LOCAL session_replication_role = replica`)
        } catch {
            await client.query('ROLLBACK TO SAVEPOINT migrate_start')
            console.warn('[migrate] 無法停用 FK 檢查，改以過濾孤兒列匯入')
        }

        if (allTables.length > 0) {
            const tableList = allTables.map((t) => `"${t}"`).join(', ')
            await client.query(`TRUNCATE ${tableList} RESTART IDENTITY CASCADE`)
        }

        for (const table of allTables) {
            await importTable(client, sqlite, table, refs)
        }

        await client.query(`SET LOCAL session_replication_role = DEFAULT`).catch(() => {})
        await client.query('COMMIT')

        await pool
            .query(
                `SELECT setval(
                pg_get_serial_sequence('group_logs', 'log_id'),
                COALESCE((SELECT MAX(log_id) FROM group_logs), 1)
            )`
            )
            .catch(() => {})

        console.log('[migrate] 完成')
    } catch (e) {
        await client.query('ROLLBACK').catch(() => {})
        throw e
    } finally {
        client.release()
        await sqlite.close()
        await pool.end()
    }
}

main().catch((e) => {
    console.error(e)
    process.exit(1)
})