# PostgreSQL adapter is an optional peer dependency
npm install pg

# You need a PostgreSQL database (local or cloud)
# Example with Docker:
docker run -d --name sap-db -p 5432:5432 \
  -e POSTGRES_USER=sap -e POSTGRES_PASSWORD=sap -e POSTGRES_DB=sap_db \
  postgres:16

import { SapPostgres, SapSyncEngine } from "@oobe-protocol-labs/synapse-sap-sdk/postgres";
import { SapClient } from "@oobe-protocol-labs/synapse-sap-sdk";
import { AnchorProvider } from "@coral-xyz/anchor";
import { Pool } from "pg";

const client = SapClient.from(AnchorProvider.env());

// Create a connection pool.
// DATABASE_URL format: postgresql://user:password@host:port/database
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});

// Create the adapter. It handles schema creation, syncing, and queries.
const pg = new SapPostgres(pool, client);

// Run DDL migrations (idempotent — safe to call on every startup).
// Creates 22 tables, indexes, views, and sync cursor tracking.
await pg.migrate();

const result = await pg.syncAll({
  onProgress: (step, total, type) => {
    console.log(`[${step}/${total}] Syncing ${type}...`);
  },
});

console.log("Sync complete:");
console.log("  Agents:", result.agents);
console.log("  Tools:", result.tools);
console.log("  Escrows:", result.escrows);
console.log("  Total records:", result.totalRecords);
console.log("  Duration:", result.durationMs, "ms");

const sync = new SapSyncEngine(pg, client);

// Sync every 60 seconds (60,000 ms).
// Only syncs accounts that changed since the last sync (cursor-based).
sync.start(60_000);

console.log("Running:", sync.isRunning()); // true

// Stop syncing
await sync.stop();

// Start streaming — events are inserted into the sap_events table.
await sync.startEventStream();

// Events are processed in real-time as they arrive.
// The sap_events table stores: event_name, tx_signature, slot, data (JSONB).

// Stop streaming
await sync.stopEventStream();

// Fetch agent by PDA or wallet address.
const agent = await pg.getAgent("7xKXtg2CW87d97TXJSDpbD...");

// Active agents sorted by reputation (top N).
const topAgents = await pg.getActiveAgents(50);

// Escrow balance for a specific agent-depositor pair.
const balance = await pg.getEscrowBalance(agentPda, depositorWallet);

// All tools published by an agent.
const tools = await pg.getAgentTools(agentPda);

// Recent events filtered by type.
const events = await pg.getRecentEvents(100, "PaymentSettledEvent");

// Sync status per account type (last slot, last signature).
const cursors = await pg.getSyncStatus();

// Top-performing agents by reputation
const { rows } = await pg.query(`
  SELECT name, reputation_score, total_calls_served
  FROM sap_agents
  WHERE is_active = true
  ORDER BY reputation_score DESC
  LIMIT 20
`);

// Escrow volume by agent
const { rows: volume } = await pg.query(`
  SELECT a.name, SUM(e.total_deposited) as total_volume
  FROM sap_escrows e
  JOIN sap_agents a ON a.pda = e.agent
  GROUP BY a.name
  ORDER BY total_volume DESC
`);

// Tool usage analytics
const { rows: tools } = await pg.query(`
  SELECT tool_name, category, total_invocations
  FROM sap_tools
  WHERE is_active = true
  ORDER BY total_invocations DESC
  LIMIT 50
`);