/** 各表 PRIMARY KEY / UNIQUE 衝突欄位，供 INSERT OR IGNORE / REPLACE 轉換 */
export const TABLE_CONFLICT_KEYS: Record<string, string[]> = {
    users: ['user_id'],
    user_site_admin: ['user_id'],
    user_vods: ['vod_id'],
    room_streams: ['room_id'],
    room_pinned_message: ['room_id'],
    room_stream_likes: ['room_id', 'user_id'],
    user_board_posts: ['post_id'],
    user_actions: ['user_id', 'target_id', 'action_type'],
    room_bans: ['room_id', 'user_id'],
    room_mods: ['room_id', 'user_id'],
    user_login_history: ['history_id'],
    user_login_sessions: ['session_id'],
    user_allowed_devices: ['device_id'],
    user_login_settings: ['user_id'],
    user_notification_settings: ['user_id', 'notification_setting_id'],
    user_notifications: ['notification_id'],
    system_notices: ['notice_id'],
    index_event_banners: ['event_id'],
    catch: ['catch_id'],
    catch_likes: ['catch_id', 'user_id'],
    catch_comments: ['comment_id'],
    catch_comment_likes: ['comment_id', 'user_id'],
    config_room_gifts: ['gift_id'],
    config_avatar_frames: ['item_id'],
    user_avatar_frames: ['user_id', 'frame_id'],
    rank_rich: ['rank_id'],
    rank_stream: ['rank_id'],
    groups: ['group_id'],
    user_groups: ['user_id', 'group_id'],
    group_apply: ['user_id', 'group_id'],
    user_board_post_comments: ['comment_id'],
}

const UNIX_NOW_PG = `FLOOR(EXTRACT(EPOCH FROM NOW()))::INTEGER`
const UNIX_NOW_SQLITE = `CAST(strftime('%s','now') AS INTEGER)`

export function unixNowSql(dialect: 'sqlite' | 'postgres'): string {
    return dialect === 'postgres' ? UNIX_NOW_PG : UNIX_NOW_SQLITE
}

/** 將 ? 佔位符轉為 PostgreSQL $1, $2…（略過字串內的 ?） */
export function toPostgresPlaceholders(sql: string): string {
    let out = ''
    let i = 0
    let n = 1
    let inSingle = false
    let inDouble = false

    while (i < sql.length) {
        const ch = sql[i]
        if (ch === "'" && !inDouble) {
            if (inSingle && sql[i + 1] === "'") {
                out += "''"
                i += 2
                continue
            }
            inSingle = !inSingle
            out += ch
            i++
            continue
        }
        if (ch === '"' && !inSingle) {
            inDouble = !inDouble
            out += ch
            i++
            continue
        }
        if (ch === '?' && !inSingle && !inDouble) {
            out += `$${n++}`
            i++
            continue
        }
        out += ch
        i++
    }
    return out
}

function splitColumnList(cols: string): string[] {
    return cols.split(',').map((c) => c.trim().replace(/^["']|["']$/g, ''))
}

function adaptInsertOrIgnore(sql: string): string {
    return sql.replace(
        /INSERT\s+OR\s+IGNORE\s+INTO\s+([a-z_][a-z0-9_]*)\s*\(([^)]+)\)\s*([\s\S]+?)(?=;|$)/gi,
        (_full, table: string, cols: string, rest: string) => {
            const keys = TABLE_CONFLICT_KEYS[table.toLowerCase()]
            const conflict = keys?.length ? `ON CONFLICT (${keys.join(', ')}) DO NOTHING` : 'ON CONFLICT DO NOTHING'
            return `INSERT INTO ${table} (${cols}) ${rest.trim()} ${conflict}`
        }
    )
}

const PG_TABLE_EXISTS_SQL = `SELECT table_name AS name FROM information_schema.tables WHERE table_schema = current_schema() AND table_name = $1 LIMIT 1`

/** 模擬 sqlite_master.sql，供檢查 room_id → users(user_id) 外鍵用 */
const PG_TABLE_FK_SQL_CHECK = `SELECT CASE WHEN EXISTS (
    SELECT 1
    FROM information_schema.table_constraints AS tc
    JOIN information_schema.key_column_usage AS kcu
      ON tc.constraint_name = kcu.constraint_name
      AND tc.table_schema = kcu.table_schema
      AND tc.table_name = kcu.table_name
    JOIN information_schema.constraint_column_usage AS ccu
      ON ccu.constraint_name = tc.constraint_name
      AND ccu.table_schema = tc.constraint_schema
    WHERE tc.constraint_type = 'FOREIGN KEY'
      AND tc.table_schema = current_schema()
      AND tc.table_name = $1
      AND kcu.column_name = 'room_id'
      AND ccu.table_name = 'users'
      AND ccu.column_name = 'user_id'
) THEN 'FOREIGN KEY (room_id) REFERENCES users (user_id)' ELSE '' END AS sql`

function adaptSqliteMasterQueries(s: string): string {
    // SELECT sql ... name = ?
    s = s.replace(
        /SELECT\s+sql\s+FROM\s+sqlite_master\s+WHERE\s+type\s*=\s*'table'\s+AND\s+name\s*=\s*\?\s+LIMIT\s+1/gi,
        PG_TABLE_FK_SQL_CHECK
    )
    // SELECT name ... name = ?
    s = s.replace(
        /SELECT\s+name\s+FROM\s+sqlite_master\s+WHERE\s+type\s*=\s*'table'\s+AND\s+name\s*=\s*\?\s+LIMIT\s+1/gi,
        PG_TABLE_EXISTS_SQL
    )
    // SELECT name ... name = 'literal'
    s = s.replace(
        /SELECT\s+name\s+FROM\s+sqlite_master\s+WHERE\s+type\s*=\s*'table'\s+AND\s+name\s*=\s*'([^']+)'\s+LIMIT\s+1/gi,
        "SELECT table_name AS name FROM information_schema.tables WHERE table_schema = current_schema() AND table_name = '$1' LIMIT 1"
    )
    return s
}

function adaptInsertOrReplace(sql: string): string {
    return sql.replace(
        /INSERT\s+OR\s+REPLACE\s+INTO\s+([a-z_][a-z0-9_]*)\s*\(([^)]+)\)\s*([\s\S]+?)(?=;|$)/gi,
        (_full, table: string, cols: string, rest: string) => {
            const colList = splitColumnList(cols)
            const keys = TABLE_CONFLICT_KEYS[table.toLowerCase()] ?? [colList[0]]
            const updates = colList
                .filter((c) => !keys.includes(c))
                .map((c) => `${c} = EXCLUDED.${c}`)
                .join(', ')
            const updateClause = updates ? `DO UPDATE SET ${updates}` : 'DO NOTHING'
            return `INSERT INTO ${table} (${cols}) ${rest.trim()} ON CONFLICT (${keys.join(', ')}) ${updateClause}`
        }
    )
}

/** camelCase 欄位別名（roomId、displayName）；不含 INTEGER/TEXT 等 SQL 型別關鍵字 */
function isMixedCaseSelectAlias(alias: string): boolean {
    return /[a-z]/.test(alias) && /[A-Z]/.test(alias)
}

const CAMEL_CASE_IDENT_RE = /\b([a-z][a-zA-Z0-9]*[A-Z][a-zA-Z0-9]*)\b/g

function quoteCamelCaseTokens(fragment: string): string {
    return fragment.replace(CAMEL_CASE_IDENT_RE, (id) => `"${id}"`)
}

/**
 * SELECT 別名加引號後，ORDER BY membersCount 會被 PG 折成 memberscount 而找不到欄位。
 * 對 ORDER BY / GROUP BY / HAVING 內的 camelCase 識別符加引號以對齊別名。
 */
export function quoteMixedCaseSortClauseIdentifiers(sql: string): string {
    const sortEnd =
        String.raw`(?=\s*\)\s*AS\b|\s+(?:LIMIT|OFFSET|FETCH|UNION|INTERSECT|EXCEPT|FOR|WINDOW|RETURNING)\b|;|$)`
    const clauseRe = new RegExp(
        `\\b(ORDER\\s+BY|GROUP\\s+BY|HAVING)\\s+([\\s\\S]*?)${sortEnd}`,
        'gi'
    )
    return sql.replace(clauseRe, (_full, clause, expr) => `${clause} ${quoteCamelCaseTokens(expr)}`)
}

/**
 * PostgreSQL 會將未加引號的識別符折成小寫，導致 `AS roomId` 回傳欄位名為 `roomid`，
 * node-pg 讀取 `row.roomId` 會是 undefined。僅對 camelCase 別名加雙引號；
 * 勿對 `CAST(x AS INTEGER)` 內的 INTEGER 加引號（會變成 type "INTEGER" does not exist）。
 */
export function quoteMixedCaseSelectAliases(sql: string): string {
    return sql.replace(/\bAS\s+("([^"]+)"|([a-zA-Z_][a-zA-Z0-9_]*))/gi, (match, _full, quoted, bare) => {
        if (quoted) return match
        const alias = bare as string
        if (isMixedCaseSelectAlias(alias)) {
            return `AS "${alias}"`
        }
        return match
    })
}

/** 將 SQLite 語法轉為 PostgreSQL（僅在 postgres 路徑使用） */
export function adaptSqlForPostgres(sql: string): string {
    let s = sql

    s = s.replace(/PRAGMA\s+foreign_keys\s*=\s*(ON|OFF)\s*;?/gi, 'SELECT 1')
    s = s.replace(/PRAGMA\s+journal_mode\s*=\s*WAL\s*;?/gi, 'SELECT 1')
    s = s.replace(/PRAGMA\s+busy_timeout\s*=\s*\d+\s*;?/gi, 'SELECT 1')

    s = s.replace(/PRAGMA\s+table_info\(([^)]+)\)\s*;?/gi, (_m, rawTable: string) => {
        const table = rawTable.replace(/['"`]/g, '').trim()
        return `SELECT column_name AS name, ordinal_position AS cid FROM information_schema.columns WHERE table_schema = current_schema() AND table_name = '${table}' ORDER BY ordinal_position`
    })

    s = adaptSqliteMasterQueries(s)

    s = s.replace(/\bBEGIN\s+IMMEDIATE\b/gi, 'BEGIN')
    s = s.replace(/INTEGER\s+PRIMARY\s+KEY\s+AUTOINCREMENT/gi, 'BIGSERIAL PRIMARY KEY')
    s = s.replace(/\bAUTOINCREMENT\b/gi, '')
    s = s.replace(/CAST\s*\(\s*strftime\s*\(\s*'%s'\s*,\s*'now'\s*\)\s*AS\s+INTEGER\s*\)/gi, UNIX_NOW_PG)
    s = s.replace(/typeof\s*\(\s*([^)]+)\s*\)\s*=\s*'text'/gi, "pg_typeof($1)::text = 'text'")

    s = adaptInsertOrReplace(s)
    s = adaptInsertOrIgnore(s)
    s = quoteMixedCaseSelectAliases(s)
    s = quoteMixedCaseSortClauseIdentifiers(s)
    s = toPostgresPlaceholders(s)

    return s
}

/** 將多句 SQL 依分號切割（不切割字串內的分號） */
export function splitSqlStatements(sql: string): string[] {
    const parts: string[] = []
    let buf = ''
    let inSingle = false
    let inDouble = false

    for (let i = 0; i < sql.length; i++) {
        const ch = sql[i]
        if (ch === "'" && !inDouble) {
            if (inSingle && sql[i + 1] === "'") {
                buf += "''"
                i++
                continue
            }
            inSingle = !inSingle
            buf += ch
            continue
        }
        if (ch === '"' && !inSingle) {
            inDouble = !inDouble
            buf += ch
            continue
        }
        if (ch === ';' && !inSingle && !inDouble) {
            const t = buf.trim()
            if (t) parts.push(t)
            buf = ''
            continue
        }
        buf += ch
    }
    const tail = buf.trim()
    if (tail) parts.push(tail)
    return parts
}