function openDb() {
  return new Promise((resolve, reject) => {
    const req = indexedDB.open('paper_trading_viz', 2)
    req.onupgradeneeded = () => {
      const db = req.result
      if (!db.objectStoreNames.contains('meta')) db.createObjectStore('meta', { keyPath: 'id' })
      if (!db.objectStoreNames.contains('raw_decisions')) {
        const raw = db.createObjectStore('raw_decisions', { keyPath: 'id' })
        if (!raw.indexNames.contains('group')) raw.createIndex('group', 'group', { unique: false })
        if (!raw.indexNames.contains('updated_at')) raw.createIndex('updated_at', 'updated_at', { unique: false })
      }
    }
    req.onsuccess = () => resolve(req.result)
    req.onerror = () => reject(req.error)
  })
}

// removed aggregates helpers

export async function readSyncMeta() {
  const db = await openDb()
  return new Promise((resolve, reject) => {
    const tx = db.transaction('meta', 'readonly')
    const store = tx.objectStore('meta')
    const req = store.get('raw_sync')
    req.onsuccess = () => resolve(req.result || null)
    req.onerror = () => reject(req.error)
  })
}

export async function writeSyncMeta(remoteUpdatedAt, lastSyncedAt) {
  const db = await openDb()
  return new Promise((resolve, reject) => {
    const tx = db.transaction('meta', 'readwrite')
    const store = tx.objectStore('meta')
    store.put({ id: 'raw_sync', remoteUpdatedAt: remoteUpdatedAt || null, lastSyncedAt: lastSyncedAt || new Date().toISOString() })
    tx.oncomplete = () => resolve(true)
    tx.onerror = () => reject(tx.error)
  })
}

export async function writeRawDecisions(rows) {
  const db = await openDb()
  return new Promise((resolve, reject) => {
    const tx = db.transaction('raw_decisions', 'readwrite')
    const store = tx.objectStore('raw_decisions')
    const clearReq = store.clear()
    clearReq.onsuccess = () => {
      for (const r of rows) {
        const key = r.id
        const group = `${r.agent_name}|${r.asset}|${r.model}`
        store.put({ ...r, id: key, group })
      }
      tx.oncomplete = () => resolve(true)
      tx.onerror = () => reject(tx.error)
    }
    clearReq.onerror = () => reject(clearReq.error)
  })
}

export async function upsertRawDecisions(rows) {
  const db = await openDb()
  return new Promise((resolve, reject) => {
    const tx = db.transaction('raw_decisions', 'readwrite')
    const store = tx.objectStore('raw_decisions')
    for (const r of rows) {
      const key = r.id
      const group = `${r.agent_name}|${r.asset}|${r.model}`
      store.put({ ...r, id: key, group })
    }
    tx.oncomplete = () => resolve(true)
    tx.onerror = () => reject(tx.error)
  })
}

export async function readRawByGroup(groupKey) {
  const db = await openDb()
  return new Promise((resolve, reject) => {
    const tx = db.transaction('raw_decisions', 'readonly')
    const store = tx.objectStore('raw_decisions')
    const idx = store.index('group')
    const req = idx.openCursor(IDBKeyRange.only(groupKey))
    const rows = []
    req.onsuccess = e => {
      const cursor = e.target.result
      if (cursor) { rows.push(cursor.value); cursor.continue() } else { resolve(rows) }
    }
    req.onerror = () => reject(req.error)
  })
}

export async function readAllRawDecisions() {
  const db = await openDb()
  return new Promise((resolve, reject) => {
    const tx = db.transaction('raw_decisions', 'readonly')
    const store = tx.objectStore('raw_decisions')
    const rows = []
    const cursorReq = store.openCursor()
    cursorReq.onsuccess = e => {
      const cursor = e.target.result
      if (cursor) { rows.push(cursor.value); cursor.continue() } else { resolve(rows) }
    }
    cursorReq.onerror = () => reject(cursorReq.error)
  })
}

export async function readRawUpdatedAtMax() {
  const db = await openDb()
  return new Promise((resolve, reject) => {
    const tx = db.transaction('raw_decisions', 'readonly')
    const store = tx.objectStore('raw_decisions')
    const index = store.index('updated_at')
    const req = index.openCursor(null, 'prev')
    req.onsuccess = e => {
      const cursor = e.target.result
      resolve(cursor ? cursor.value.updated_at : null)
    }
    req.onerror = () => reject(req.error)
  })
}

export async function clearAllStores() {
  const db = await openDb()
  return new Promise((resolve, reject) => {
    const stores = []
    if (db.objectStoreNames.contains('meta')) stores.push('meta')
    if (db.objectStoreNames.contains('raw_decisions')) stores.push('raw_decisions')
    if (stores.length === 0) { resolve(true); return }
    const tx = db.transaction(stores, 'readwrite')
    if (db.objectStoreNames.contains('meta')) tx.objectStore('meta').clear()
    if (db.objectStoreNames.contains('raw_decisions')) tx.objectStore('raw_decisions').clear()
    tx.oncomplete = () => resolve(true)
    tx.onerror = () => reject(tx.error)
  })
}