%E6%B3%B0%E9%A2%9D%E7%89%88/Food%20Labeling%20Management%20App%20UniApp/src/utils/sqliteSync.ts

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 AUTH_TABLE = 'offline_auth_account'
const FALLBACK_CACHE_KEY = '__offline_cache_json__'
const FALLBACK_MUTATION_KEY = '__offline_mutation_queue_json__'
const FALLBACK_AUTH_KEY = '__offline_auth_accounts_json__'
export type OfflineAuthAccountRow = {
  email: string
  password: string
  token: string
  refreshToken: string
  locations: import('../types/usAppBound').UsAppBoundLocationDto[]
  displayName: string
  updatedAt: number
}
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
    )`
  )
  await sqliteExecute(
    `CREATE TABLE IF NOT EXISTS ${AUTH_TABLE} (
      email TEXT PRIMARY KEY,
      password TEXT NOT NULL,
      token TEXT NOT NULL,
      refresh_token TEXT,
      locations_json TEXT NOT NULL,
      display_name TEXT,
      updated_at INTEGER NOT NULL
    )`
  )
  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 hasOfflineCache(module: string, name: string): Promise<boolean> {
  const key = cachePk(module, name)
  if (!isAppSqliteAvailable()) {
    const map = getStorageMap<{ payloadJson: string }>(FALLBACK_CACHE_KEY)
    return Object.prototype.hasOwnProperty.call(map, key)
  }
  await initOfflineSqlite()
  const rows = await sqliteSelect(
    `SELECT 1 FROM ${CACHE_TABLE} WHERE cache_key='${esc(key)}' LIMIT 1`
  )
  return rows.length > 0
}
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
  }
  if (await hasOfflineCache(module, name)) {
    return (await getOfflineCache<T>(module, name)) as T
  }
  throw new Error('No offline cache available')
}
function getFallbackAuthAccounts(): OfflineAuthAccountRow[] {
  try {
    const raw = String(uni.getStorageSync(FALLBACK_AUTH_KEY) || '').trim()
    if (!raw) return []
    const parsed = JSON.parse(raw) as unknown
    return Array.isArray(parsed) ? (parsed as OfflineAuthAccountRow[]) : []
  } catch {
    return []
  }
}
function setFallbackAuthAccounts(rows: OfflineAuthAccountRow[]): void {
  uni.setStorageSync(FALLBACK_AUTH_KEY, JSON.stringify(rows))
}
function authRowFromDb(r: Record<string, unknown>): OfflineAuthAccountRow {
  let locations: OfflineAuthAccountRow['locations'] = []
  try {
    locations = JSON.parse(String(r.locations_json || '[]')) as OfflineAuthAccountRow['locations']
  } catch {
    locations = []
  }
  return {
    email: String(r.email || ''),
    password: String(r.password || ''),
    token: String(r.token || ''),
    refreshToken: String(r.refresh_token || ''),
    locations,
    displayName: String(r.display_name || ''),
    updatedAt: Number(r.updated_at || 0),
  }
}
/** 在线登录成功后保存，供断网时用同一账号密码登录 */
export async function saveOfflineAuthAccount(input: {
  email: string
  password: string
  token: string
  refreshToken: string
  locations: OfflineAuthAccountRow['locations']
  displayName: string
}): Promise<void> {
  const email = input.email.trim().toLowerCase()
  if (!email || !input.password || !input.token) return
  const row: OfflineAuthAccountRow = {
    email,
    password: input.password,
    token: input.token,
    refreshToken: input.refreshToken || '',
    locations: input.locations ?? [],
    displayName: input.displayName || email,
    updatedAt: Date.now(),
  }
  if (!isAppSqliteAvailable()) {
    const list = getFallbackAuthAccounts().filter((x) => x.email !== email)
    list.push(row)
    setFallbackAuthAccounts(list)
    return
  }
  await initOfflineSqlite()
  const locationsJson = esc(JSON.stringify(row.locations))
  await sqliteExecute(
    `INSERT OR REPLACE INTO ${AUTH_TABLE}
      (email, password, token, refresh_token, locations_json, display_name, updated_at)
      VALUES (
        '${esc(email)}',
        '${esc(row.password)}',
        '${esc(row.token)}',
        '${esc(row.refreshToken)}',
        '${locationsJson}',
        '${esc(row.displayName)}',
        ${row.updatedAt}
      )`
  )
}
/** 断网登录：匹配 SQLite 中已同步过的账号 */
export async function findOfflineAuthAccount(
  email: string,
  password: string
): Promise<OfflineAuthAccountRow | null> {
  const em = email.trim().toLowerCase()
  const pw = password
  if (!em || !pw) return null
  if (!isAppSqliteAvailable()) {
    return getFallbackAuthAccounts().find((x) => x.email === em && x.password === pw) ?? null
  }
  await initOfflineSqlite()
  const rows = await sqliteSelect(
    `SELECT email, password, token, refresh_token, locations_json, display_name, updated_at
     FROM ${AUTH_TABLE}
     WHERE email='${esc(em)}' AND password='${esc(pw)}'
     LIMIT 1`
  )
  if (!rows.length) return null
  return authRowFromDb(rows[0] as Record<string, unknown>)
}
/** 同步后刷新已存离线账号的 token / 门店，无需再次输入密码 */
export async function refreshOfflineAuthAccountSession(input: {
  email: string
  token: string
  refreshToken: string
  locations: OfflineAuthAccountRow['locations']
  displayName: string
}): Promise<void> {
  const email = input.email.trim().toLowerCase()
  if (!email || !input.token) return
  if (!isAppSqliteAvailable()) {
    const list = getFallbackAuthAccounts()
    const idx = list.findIndex((x) => x.email === email)
    if (idx < 0) return
    list[idx] = {
      ...list[idx],
      token: input.token,
      refreshToken: input.refreshToken || '',
      locations: input.locations ?? [],
      displayName: input.displayName || list[idx].displayName,
      updatedAt: Date.now(),
    }
    setFallbackAuthAccounts(list)
    return
  }
  await initOfflineSqlite()
  const locationsJson = esc(JSON.stringify(input.locations ?? []))
  await sqliteExecute(
    `UPDATE ${AUTH_TABLE}
     SET token='${esc(input.token)}',
         refresh_token='${esc(input.refreshToken || '')}',
         locations_json='${locationsJson}',
         display_name='${esc(input.displayName || email)}',
         updated_at=${Date.now()}
     WHERE email='${esc(email)}'`
  )
}