import "server-only";

import { mkdirSync } from "node:fs";
import { dirname, resolve } from "node:path";
import Database from "better-sqlite3";
import type { SignedAgentEvent } from "@pons-agent/protocol";

export type StoredAgentEvent = SignedAgentEvent & {
  verification: "signature" | "chain";
  receivedAt: string;
};

type StoredAgentEventRow = Omit<StoredAgentEvent, "payload" | "version"> & { payloadJson: string };

const hydrateEvents = (rows: StoredAgentEventRow[]): StoredAgentEvent[] => rows.map(({ payloadJson, txHash, ...row }) => ({
  ...row,
  version: 1,
  ...(txHash ? { txHash } : {}),
  payload: JSON.parse(payloadJson) as Record<string, unknown>,
}));

let database: Database.Database | null = null;

const getDatabase = () => {
  if (database) return database;

  const databasePath = resolve(process.env.AGENT_EVENT_DB_PATH ?? "data/agent-events.db");
  mkdirSync(dirname(databasePath), { recursive: true });
  database = new Database(databasePath);
  database.pragma("journal_mode = WAL");
  database.pragma("foreign_keys = ON");
  database.exec(`
    CREATE TABLE IF NOT EXISTS agent_events (
      event_id TEXT PRIMARY KEY,
      agent_id TEXT NOT NULL,
      sequence INTEGER NOT NULL,
      type TEXT NOT NULL,
      status TEXT NOT NULL,
      occurred_at TEXT NOT NULL,
      chain_id INTEGER NOT NULL,
      tx_hash TEXT,
      payload_json TEXT NOT NULL,
      signer TEXT NOT NULL,
      signature TEXT NOT NULL,
      verification TEXT NOT NULL CHECK (verification IN ('signature', 'chain')),
      received_at TEXT NOT NULL,
      UNIQUE(signer, agent_id, sequence)
    );
    CREATE INDEX IF NOT EXISTS agent_events_occurred_at_idx
      ON agent_events(occurred_at DESC);
  `);

  return database;
};

export const insertAgentEvent = (event: SignedAgentEvent, verification: StoredAgentEvent["verification"]) => {
  const receivedAt = new Date().toISOString();
  getDatabase().prepare(`
    INSERT INTO agent_events (
      event_id, agent_id, sequence, type, status, occurred_at, chain_id,
      tx_hash, payload_json, signer, signature, verification, received_at
    ) VALUES (
      @eventId, @agentId, @sequence, @type, @status, @occurredAt, @chainId,
      @txHash, @payloadJson, @signer, @signature, @verification, @receivedAt
    )
  `).run({
    ...event,
    txHash: event.txHash ?? null,
    payloadJson: JSON.stringify(event.payload),
    verification,
    receivedAt,
  });

  return { ...event, verification, receivedAt } satisfies StoredAgentEvent;
};

export const listAgentEvents = (limit: number): StoredAgentEvent[] => {
  const rows = getDatabase().prepare(`
    SELECT
      event_id AS eventId,
      agent_id AS agentId,
      sequence,
      type,
      status,
      occurred_at AS occurredAt,
      chain_id AS chainId,
      tx_hash AS txHash,
      payload_json AS payloadJson,
      signer,
      signature,
      verification,
      received_at AS receivedAt
    FROM agent_events
    ORDER BY occurred_at DESC, sequence DESC
    LIMIT ?
  `).all(limit) as StoredAgentEventRow[];

  return hydrateEvents(rows);
};

export const listPipelineAgentEvents = (limit = 100): StoredAgentEvent[] => {
  const rows = getDatabase().prepare(`
    SELECT
      event_id AS eventId,
      agent_id AS agentId,
      sequence,
      type,
      status,
      occurred_at AS occurredAt,
      chain_id AS chainId,
      tx_hash AS txHash,
      payload_json AS payloadJson,
      signer,
      signature,
      verification,
      received_at AS receivedAt
    FROM agent_events
    WHERE type IN (
      'launch.submitted', 'launch.confirmed', 'fees.claimed',
      'buyback.submitted', 'buyback.confirmed'
    )
    ORDER BY occurred_at DESC, sequence DESC
    LIMIT ?
  `).all(limit) as StoredAgentEventRow[];

  return hydrateEvents(rows);
};
