import assert from "node:assert/strict"
import { randomUUID } from "node:crypto"
import { countSqliteWrites, sqliteDatabase } from "../db/index.js"
import { events } from "../db/schema.js"
import { db } from "../db/index.js"
import {
  attachEngineSqlite,
  bumpPacketMeta,
  configureEngine,
  flushPending,
  ingestParsedFlowsForServerForTests,
  resetEngineForTests,
  setEngineError,
} from "./traffic-flow-engine.js"
import { collectGreBgpSnapshotOnce } from "./gre-bgp-snapshot-collector.js"
import { shouldAppendSchedulerOkEvent, executeSchedulerJob } from "./scheduler.js"
import { savePrevLiveMap, loadPrevLiveMap } from "./alert-engine/prev-live-store.js"
import {
  invalidateFlowCatalogCache,
  loadFlowTopology,
  seedFlowTopologyForTests,
} from "./traffic-flow-topology.js"

resetEngineForTests()
attachEngineSqlite(sqliteDatabase)
seedFlowTopologyForTests(null)
invalidateFlowCatalogCache()

const idle1 = countSqliteWrites(() => {
  flushPending()
})
assert.equal(idle1.stats.walCheckpoint, 0)
assert.ok(idle1.stats.update >= 1, "first idle flush persists listener stats")

const idle2 = countSqliteWrites(() => {
  flushPending()
})
assert.equal(idle2.stats.update, 0, "unchanged listener stats skip UPDATE")
assert.equal(idle2.stats.walCheckpoint, 0)

bumpPacketMeta("203.0.113.9")
const changed = countSqliteWrites(() => {
  flushPending()
})
assert.equal(changed.stats.update, 1, "changed packets persist once")
assert.equal(changed.stats.walCheckpoint, 0)

setEngineError("boom")
const errWrite = countSqliteWrites(() => {
  flushPending()
})
assert.equal(errWrite.stats.update, 1)
setEngineError("")
flushPending()

resetEngineForTests()
configureEngine({ topN: 20 })
ingestParsedFlowsForServerForTests(9, [{
  src: "10.1.1.1",
  dst: "8.8.8.8",
  proto: 6,
  srcPort: 40000,
  dstPort: 443,
  bytes: 100,
  packets: 1,
  inIface: "2",
  outIface: "",
}])
const withData = countSqliteWrites(() => {
  flushPending()
})
assert.equal(withData.stats.walCheckpoint, 0, "flush with data must not TRUNCATE WAL")
assert.ok(withData.stats.insert >= 1, "flow upsert writes")

sqliteDatabase.prepare(`DELETE FROM flow_buckets WHERE server_id = 9`).run()
sqliteDatabase.prepare(`DELETE FROM flow_minute_stats WHERE server_id = 9`).run()
sqliteDatabase.prepare(`DELETE FROM flow_minute_dims WHERE server_id = 9`).run()
sqliteDatabase.prepare(`DELETE FROM flow_daily_dims WHERE server_id = 9`).run()

const plan = sqliteDatabase.prepare(`
  EXPLAIN QUERY PLAN
  SELECT id FROM flow_buckets WHERE bucket_at >= ? ORDER BY bytes DESC LIMIT 100
`).all("2000-01-01T00:00:00.000Z") as Array<{ detail?: string }>
const planText = plan.map((p) => String(p.detail ?? "")).join(" | ")
assert.ok(planText.length > 0, "EXPLAIN QUERY PLAN returned rows")

invalidateFlowCatalogCache()
seedFlowTopologyForTests(null)
const topoA = loadFlowTopology()
const topoB = loadFlowTopology()
assert.equal(topoA, topoB, "topology cache returns same object")
invalidateFlowCatalogCache()
const topoC = loadFlowTopology()
assert.notEqual(topoA, topoC, "invalidate rebuilds topology")

assert.equal(shouldAppendSchedulerOkEvent("traffic"), false)
assert.equal(shouldAppendSchedulerOkEvent("alert_engine"), false)
assert.equal(shouldAppendSchedulerOkEvent("gre_bgp"), false)
assert.equal(shouldAppendSchedulerOkEvent("backups"), true)
assert.equal(shouldAppendSchedulerOkEvent("certificates_renew"), true)
assert.equal(shouldAppendSchedulerOkEvent("internet_path"), true)

savePrevLiveMap("gre", { a: "up" })
const prevSame = countSqliteWrites(() => {
  savePrevLiveMap("gre", { a: "up" })
})
assert.equal(prevSame.stats.update, 0)
assert.equal(prevSame.stats.insert, 0)
savePrevLiveMap("gre", { a: "down" })
assert.equal(loadPrevLiveMap("gre").a, "down")
savePrevLiveMap("gre", { a: "up" })

const greBefore = sqliteDatabase.prepare(`SELECT COUNT(*) AS n FROM alert_gre_tunnel_samples`).get() as { n: number }
const bgpBefore = sqliteDatabase.prepare(`SELECT COUNT(*) AS n FROM alert_bgp_peer_samples`).get() as { n: number }
await collectGreBgpSnapshotOnce()
const greAfter = sqliteDatabase.prepare(`SELECT COUNT(*) AS n FROM alert_gre_tunnel_samples`).get() as { n: number }
const bgpAfter = sqliteDatabase.prepare(`SELECT COUNT(*) AS n FROM alert_bgp_peer_samples`).get() as { n: number }
assert.equal(greAfter.n, greBefore.n)
assert.equal(bgpAfter.n, bgpBefore.n)

const oldId = `evt-old-${randomUUID()}`
db.insert(events).values({
  id: oldId,
  createdAt: new Date(Date.now() - 40 * 24 * 60 * 60 * 1000).toISOString(),
  level: "info",
  eventType: "test.retention",
  sourceModule: "system",
  title: "old",
  message: "old",
}).run()
const eventsBefore = sqliteDatabase.prepare(
  `SELECT COUNT(*) AS n FROM events WHERE event_type = 'scheduler.job.ok' AND entity_id = 'alert_engine'`,
).get() as { n: number }
await executeSchedulerJob("alert_engine")
const oldGone = sqliteDatabase.prepare(`SELECT COUNT(*) AS n FROM events WHERE id = ?`).get(oldId) as { n: number }
assert.equal(oldGone.n, 0, "events older than 30 days are purged")
const eventsAfter = sqliteDatabase.prepare(
  `SELECT COUNT(*) AS n FROM events WHERE event_type = 'scheduler.job.ok' AND entity_id = 'alert_engine'`,
).get() as { n: number }
assert.equal(eventsAfter.n, eventsBefore.n, "quiet jobs do not append scheduler.job.ok")

resetEngineForTests()
console.log("sqlite-write-opt.test.ts: ok")
console.log("EXPLAIN listStoredFlowRows:", planText)
