// Fetch all financial events for a date range
const events = await shopify.event.list({
  verb: 'confirmed,placed,refunded,voided',
  created_at_min: '2026-01-01T00:00:00Z',
  created_at_max: '2026-12-31T23:59:59Z',
  limit: 250,
});

// Get all orders modified in a date range
const orders = await bigcommerce.get('/v2/orders', {
  min_date_modified: '2026-01-01T00:00:00+00:00',
  max_date_modified: '2026-12-31T23:59:59+00:00',
  status_id: '',  // all statuses
  limit: 250,
});

CREATE TABLE financial_audit_events (
  id             UUID        NOT NULL DEFAULT gen_random_uuid(),
  seq            BIGSERIAL   NOT NULL,          -- Monotonic — gap detection
  occurred_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  event_type     VARCHAR(64) NOT NULL,           -- 'order.total_changed', 'refund.issued'
  aggregate_type VARCHAR(32) NOT NULL,           -- 'order', 'payment', 'refund'
  aggregate_id   VARCHAR(128) NOT NULL,
  actor_id       VARCHAR(128) NOT NULL,          -- user ID or 'system'
  actor_role     VARCHAR(64),
  actor_ip       INET,
  before_state   JSONB,                          -- Snapshot before change
  after_state    JSONB,                          -- Snapshot after change
  delta          JSONB,                          -- Only changed fields
  correlation_id UUID,                           -- Tie events in same request
  hash           VARCHAR(64),                    -- SHA-256 for tamper detection
  PRIMARY KEY (id)
);

-- Grant INSERT and SELECT only — never UPDATE or DELETE
GRANT INSERT, SELECT ON financial_audit_events TO app_role;
REVOKE UPDATE, DELETE ON financial_audit_events FROM app_role;

CREATE INDEX idx_fae_aggregate ON financial_audit_events (aggregate_type, aggregate_id, occurred_at DESC);
CREATE INDEX idx_fae_actor ON financial_audit_events (actor_id, occurred_at DESC);
CREATE INDEX idx_fae_seq ON financial_audit_events (seq);

import { createHash } from 'crypto';

async function recordAuditEvent(input: {
  eventType: string;
  aggregateType: string;
  aggregateId: string;
  actorId: string;
  actorRole?: string;
  actorIp?: string;
  beforeState?: object | null;
  afterState?: object | null;
  correlationId?: string;
}): Promise<void> {
  const prevEvent = await db.financialAuditEvents.findFirst({
    where: { aggregate_type: input.aggregateType, aggregate_id: input.aggregateId },
    orderBy: { seq: 'desc' },
    select: { seq: true, hash: true },
  });

  const occurredAt = new Date().toISOString();
  const hashInput = [
    String(prevEvent?.seq ?? 0),
    occurredAt,
    input.eventType,
    input.aggregateId,
    input.actorId,
    JSON.stringify(input.afterState ?? null),
  ].join('|');

  const hash = createHash('sha256').update(hashInput).digest('hex');

  await db.financialAuditEvents.insert({
    occurred_at: occurredAt,
    event_type: input.eventType,
    aggregate_type: input.aggregateType,
    aggregate_id: input.aggregateId,
    actor_id: input.actorId,
    actor_role: input.actorRole ?? null,
    actor_ip: input.actorIp ?? null,
    before_state: input.beforeState ?? null,
    after_state: input.afterState ?? null,
    correlation_id: input.correlationId ?? null,
    hash,
    prev_hash: prevEvent?.hash ?? null,
  });
}

async function exportAuditTrail(from: Date, to: Date, format: 'json' | 'csv'): Promise<Buffer> {
  const events = await db.financialAuditEvents.findAll({
    where: { occurred_at: { gte: from, lte: to } },
    orderBy: { seq: 'asc' },
  });

  if (format === 'json') return Buffer.from(JSON.stringify(events, null, 2));

  // CSV for auditor delivery
  const rows = events.map(e => ({
    'Date/Time': e.occurred_at,
    'Event Type': e.event_type,
    'Record Type': e.aggregate_type,
    'Record ID': e.aggregate_id,
    'Actor': e.actor_id,
    'Actor Role': e.actor_role ?? '',
    'IP Address': e.actor_ip ?? '',
    'Before': e.before_state ? JSON.stringify(e.before_state) : '',
    'After': e.after_state ? JSON.stringify(e.after_state) : '',
    'Delta': e.delta ? JSON.stringify(e.delta) : '',
  }));
  return buildCsv(rows);
}