declare const plus: any

type JsonValue = unknown

export type MutationRow = {
  id: number
  endpoint: string
  method: 'POST' | 'PUT' | 'DELETE'
  payloadJson: string
  createdAt: number
  status: number
  lastError: string | null
}

const DB_NAME = 'us_app_offline.db'
const CACHE_TABLE = 'offline_cache'
const MUTATION_TABLE = 'offline_mutation_queue'

const FALLBACK_CACHE_KEY = '__offline_cache_json__'
const FALLBACK_MUTATION_KEY = '__offline_mutation_queue_json__'

let initialized = false

function isAppSqliteAvailable(): boolean {
  return typeof plus !== 'undefined' && !!plus?.sqlite
}

function nowTs(): number {
  return Date.now()
}

async function getNetworkType(): Promise<string> {
  return new Promise((resolve) => {
    uni.getNetworkType({
      success: (res) => resolve(String(res.networkType || 'unknown')),
      fail: () => resolve('unknown'),
    })
  })
}

export async function isNetworkOnline(): Promise<boolean> {
  const t = (await getNetworkType()).toLowerCase()
  return t !== 'none' && t !== 'unknown'
}

function getStorageMap<T>(key: string): Record<string, T> {
  try {
    const raw = String(uni.getStorageSync(key) || '').trim()
    if (!raw) return {}
    const parsed = JSON.parse(raw) as unknown
    if (parsed && typeof parsed === 'object') return parsed as Record<string, T>
    return {}
  } catch {
    return {}
  }
}

function setStorageMap<T>(key: string, value: Record<string, T>): void {
  uni.setStorageSync(key, JSON.stringify(value))
}

function getStorageArray<T>(key: string): T[] {
  try {
    const raw = String(uni.getStorageSync(key) || '').trim()
    if (!raw) return []
    const parsed = JSON.parse(raw) as unknown
    if (Array.isArray(parsed)) return parsed as T[]
    return []
  } catch {
    return []
  }
}

function setStorageArray<T>(key: string, value: T[]): void {
  uni.setStorageSync(key, JSON.stringify(value))
}

function cachePk(module: string, cacheKey: string): string {
  return `${module}::${cacheKey}`
}

function sqliteOpen(): Promise<void> {
  if (!isAppSqliteAvailable()) return Promise.resolve()
  return new Promise((resolve, reject) => {
    plus.sqlite.openDatabase({
      name: DB_NAME,
      path: `_doc/${DB_NAME}`,
      success: () => resolve(),
      fail: (e: any) => {
        const msg = String(e?.message || e || '')
        if (msg.toLowerCase().includes('already')) {
          resolve()
          return
        }
        reject(new Error(msg || 'open sqlite failed'))
      },
    })
  })
}

function sqliteExecute(sql: string): Promise<void> {
  return new Promise((resolve, reject) => {
    plus.sqlite.executeSql({
      name: DB_NAME,
      sql,
      success: () => resolve(),
      fail: (e: any) => reject(new Error(String(e?.message || e || 'execute sql failed'))),
    })
  })
}

function sqliteSelect(sql: string): Promise<any[]> {
  return new Promise((resolve, reject) => {
    plus.sqlite.selectSql({
      name: DB_NAME,
      sql,
      success: (rows: any[]) => resolve(Array.isArray(rows) ? rows : []),
      fail: (e: any) => reject(new Error(String(e?.message || e || 'select sql failed'))),
    })
  })
}

function esc(value: string): string {
  return value.replace(/'/g, "''")
}

export async function initOfflineSqlite(): Promise<void> {
  if (initialized) return
  if (!isAppSqliteAvailable()) {
    initialized = true
    return
  }
  await sqliteOpen()
  await sqliteExecute(
    `CREATE TABLE IF NOT EXISTS ${CACHE_TABLE} (
      cache_key TEXT PRIMARY KEY,
      module_name TEXT NOT NULL,
      cache_name TEXT NOT NULL,
      payload_json TEXT NOT NULL,
      updated_at INTEGER NOT NULL
    )`
  )
  await sqliteExecute(
    `CREATE TABLE IF NOT EXISTS ${MUTATION_TABLE} (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      endpoint TEXT NOT NULL,
      method TEXT NOT NULL,
      payload_json TEXT NOT NULL,
      created_at INTEGER NOT NULL,
      status INTEGER NOT NULL DEFAULT 0,
      last_error TEXT
    )`
  )
  initialized = true
}

export async function setOfflineCache(module: string, name: string, payload: JsonValue): Promise<void> {
  const key = cachePk(module, name)
  const payloadJson = JSON.stringify(payload ?? null)
  const updatedAt = nowTs()
  if (!isAppSqliteAvailable()) {
    const map = getStorageMap<{ module: string; name: string; payloadJson: string; updatedAt: number }>(FALLBACK_CACHE_KEY)
    map[key] = { module, name, payloadJson, updatedAt }
    setStorageMap(FALLBACK_CACHE_KEY, map)
    return
  }
  await initOfflineSqlite()
  const sql = `INSERT OR REPLACE INTO ${CACHE_TABLE}
    (cache_key, module_name, cache_name, payload_json, updated_at)
    VALUES ('${esc(key)}', '${esc(module)}', '${esc(name)}', '${esc(payloadJson)}', ${updatedAt})`
  await sqliteExecute(sql)
}

export async function getOfflineCache<T = unknown>(module: string, name: string): Promise<T | null> {
  const key = cachePk(module, name)
  if (!isAppSqliteAvailable()) {
    const map = getStorageMap<{ payloadJson: string }>(FALLBACK_CACHE_KEY)
    const row = map[key]
    if (!row) return null
    try {
      return JSON.parse(String((row as any).payloadJson || 'null')) as T
    } catch {
      return null
    }
  }
  await initOfflineSqlite()
  const rows = await sqliteSelect(
    `SELECT payload_json FROM ${CACHE_TABLE} WHERE cache_key='${esc(key)}' LIMIT 1`
  )
  if (!rows.length) return null
  try {
    return JSON.parse(String(rows[0].payload_json || 'null')) as T
  } catch {
    return null
  }
}

export async function enqueueOfflineMutation(
  endpoint: string,
  method: 'POST' | 'PUT' | 'DELETE',
  payload: JsonValue
): Promise<void> {
  const payloadJson = JSON.stringify(payload ?? null)
  const createdAt = nowTs()
  if (!isAppSqliteAvailable()) {
    const list = getStorageArray<MutationRow>(FALLBACK_MUTATION_KEY)
    const maxId = list.reduce((acc, x) => Math.max(acc, Number(x.id || 0)), 0)
    list.push({
      id: maxId + 1,
      endpoint,
      method,
      payloadJson,
      createdAt,
      status: 0,
      lastError: null,
    })
    setStorageArray(FALLBACK_MUTATION_KEY, list)
    return
  }
  await initOfflineSqlite()
  const sql = `INSERT INTO ${MUTATION_TABLE}
    (endpoint, method, payload_json, created_at, status, last_error)
    VALUES ('${esc(endpoint)}', '${esc(method)}', '${esc(payloadJson)}', ${createdAt}, 0, NULL)`
  await sqliteExecute(sql)
}

export async function getPendingOfflineMutations(): Promise<MutationRow[]> {
  if (!isAppSqliteAvailable()) {
    return getStorageArray<MutationRow>(FALLBACK_MUTATION_KEY).filter((x) => Number(x.status || 0) === 0)
  }
  await initOfflineSqlite()
  const rows = await sqliteSelect(
    `SELECT id, endpoint, method, payload_json, created_at, status, last_error
     FROM ${MUTATION_TABLE}
     WHERE status=0
     ORDER BY id ASC`
  )
  return rows.map((r) => ({
    id: Number(r.id || 0),
    endpoint: String(r.endpoint || ''),
    method: String(r.method || 'POST') as 'POST' | 'PUT' | 'DELETE',
    payloadJson: String(r.payload_json || 'null'),
    createdAt: Number(r.created_at || 0),
    status: Number(r.status || 0),
    lastError: r.last_error == null ? null : String(r.last_error),
  }))
}

export async function getPendingOfflineMutationsPreview(limit = 20): Promise<MutationRow[]> {
  const all = await getPendingOfflineMutations()
  return all.slice(0, Math.max(1, Math.min(100, Number(limit) || 20)))
}

export async function markOfflineMutationDone(id: number): Promise<void> {
  if (!isAppSqliteAvailable()) {
    const list = getStorageArray<MutationRow>(FALLBACK_MUTATION_KEY).filter((x) => Number(x.id) !== Number(id))
    setStorageArray(FALLBACK_MUTATION_KEY, list)
    return
  }
  await initOfflineSqlite()
  await sqliteExecute(`DELETE FROM ${MUTATION_TABLE} WHERE id=${Number(id)}`)
}

export async function markOfflineMutationFailed(id: number, message: string): Promise<void> {
  const m = String(message || '').slice(0, 500)
  if (!isAppSqliteAvailable()) {
    const list = getStorageArray<MutationRow>(FALLBACK_MUTATION_KEY)
    const idx = list.findIndex((x) => Number(x.id) === Number(id))
    if (idx >= 0) {
      list[idx].lastError = m
    }
    setStorageArray(FALLBACK_MUTATION_KEY, list)
    return
  }
  await initOfflineSqlite()
  await sqliteExecute(`UPDATE ${MUTATION_TABLE} SET last_error='${esc(m)}' WHERE id=${Number(id)}`)
}

export async function removeOfflineMutationById(id: number): Promise<void> {
  await markOfflineMutationDone(id)
}

export async function getOfflineMutationById(id: number): Promise<MutationRow | null> {
  const targetId = Number(id)
  if (!Number.isFinite(targetId) || targetId <= 0) return null
  if (!isAppSqliteAvailable()) {
    const row = getStorageArray<MutationRow>(FALLBACK_MUTATION_KEY).find((x) => Number(x.id) === targetId)
    return row ?? null
  }
  await initOfflineSqlite()
  const rows = await sqliteSelect(
    `SELECT id, endpoint, method, payload_json, created_at, status, last_error
     FROM ${MUTATION_TABLE}
     WHERE id=${targetId}
     LIMIT 1`
  )
  if (!rows.length) return null
  const r = rows[0]
  return {
    id: Number(r.id || 0),
    endpoint: String(r.endpoint || ''),
    method: String(r.method || 'POST') as 'POST' | 'PUT' | 'DELETE',
    payloadJson: String(r.payload_json || 'null'),
    createdAt: Number(r.created_at || 0),
    status: Number(r.status || 0),
    lastError: r.last_error == null ? null : String(r.last_error),
  }
}

export async function clearFailedOfflineMutations(): Promise<number> {
  if (!isAppSqliteAvailable()) {
    const list = getStorageArray<MutationRow>(FALLBACK_MUTATION_KEY)
    const failed = list.filter((x) => String(x.lastError || '').trim() !== '')
    const left = list.filter((x) => String(x.lastError || '').trim() === '')
    setStorageArray(FALLBACK_MUTATION_KEY, left)
    return failed.length
  }
  await initOfflineSqlite()
  const rows = await sqliteSelect(
    `SELECT COUNT(1) AS c FROM ${MUTATION_TABLE}
     WHERE status=0 AND last_error IS NOT NULL AND TRIM(last_error) <> ''`
  )
  const count = Number(rows?.[0]?.c || 0)
  await sqliteExecute(
    `DELETE FROM ${MUTATION_TABLE}
     WHERE status=0 AND last_error IS NOT NULL AND TRIM(last_error) <> ''`
  )
  return count
}

export async function getOfflineSyncSummary(): Promise<{
  cacheCount: number
  pendingMutationCount: number
}> {
  if (!isAppSqliteAvailable()) {
    const cacheMap = getStorageMap<any>(FALLBACK_CACHE_KEY)
    const pending = getStorageArray<MutationRow>(FALLBACK_MUTATION_KEY).filter((x) => Number(x.status || 0) === 0)
    return {
      cacheCount: Object.keys(cacheMap).length,
      pendingMutationCount: pending.length,
    }
  }
  await initOfflineSqlite()
  const c1 = await sqliteSelect(`SELECT COUNT(1) AS c FROM ${CACHE_TABLE}`)
  const c2 = await sqliteSelect(`SELECT COUNT(1) AS c FROM ${MUTATION_TABLE} WHERE status=0`)
  return {
    cacheCount: Number(c1?.[0]?.c || 0),
    pendingMutationCount: Number(c2?.[0]?.c || 0),
  }
}

export async function fetchWithOfflineCache<T>(
  module: string,
  name: string,
  fetcher: () => Promise<T>
): Promise<T> {
  const online = await isNetworkOnline()
  if (online) {
    const data = await fetcher()
    await setOfflineCache(module, name, data)
    return data
  }
  const cached = await getOfflineCache<T>(module, name)
  if (cached != null) return cached
  throw new Error('No offline cache available')
}