/**
 * AEGIS CYBER — Final Wave Schema
 * shared/schema-final.ts
 * Add: export * from "./schema-final"; in shared/schema.ts
 */

import { sql } from "drizzle-orm";
import { pgTable, text, varchar, timestamp, boolean, integer, numeric, primaryKey } from "drizzle-orm/pg-core";

// ── 1. FIDO2 / WebAuthn Passkey Credentials ──────────────────────────────────

export const fido2Credentials = pgTable("fido2_credentials", {
  id: varchar("id").primaryKey(),
  credentialId: text("credential_id").notNull().unique(),
  publicKey: text("public_key").notNull(),
  counter: integer("counter").notNull().default(0),
  userId: text("user_id").notNull(),
  deviceName: text("device_name").notNull(),
  transports: text("transports").notNull().default("[]"),
  aaguid: text("aaguid"),
  attestationFormat: text("attestation_format"),
  isActive: boolean("is_active").notNull().default(true),
  lastUsed: timestamp("last_used"),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
});

export const fido2Challenges = pgTable("fido2_challenges", {
  id: varchar("id").primaryKey(),
  challenge: text("challenge").notNull(),
  userId: text("user_id"),
  type: text("type").notNull(),
  // timestamptz (not bare `timestamp`): the challenge TTL is checked on BOTH the SQL
  // side (DELETE … WHERE expires_at > NOW()) and the JS side (new Date(row.expires_at)).
  // A bare timestamp round-trips through node-pg as LOCAL time, so in a non-UTC
  // container (Kampala EAT, UTC+3) the JS check reads ~3h in the past and a freshly
  // minted challenge is rejected as "Challenge expired". timestamptz stores an absolute
  // instant so both sides agree. (HA, HIGH_AVAILABILITY_SCOPE.md Blocker 1.)
  expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(),
  createdAt: timestamp("created_at", { withTimezone: true }).notNull().default(sql`NOW()`),
});

// ── HIGH AVAILABILITY — shared rate-limit counter (HIGH_AVAILABILITY_SCOPE.md) ──
// Per-(limit_key, window_start) request counter that backs the now-shared rate
// limiter (server/lib/rate-limiter.ts). limit_key = "<config name>:<client key>"
// (e.g. "login:ip:1.2.3.4"); window_start is the floored fixed-window boundary.
// The limiter increments atomically via INSERT … ON CONFLICT (limit_key,
// window_start) DO UPDATE SET count = count + 1 RETURNING count, so N instances
// behind a load balancer share ONE global counter (the login limit stays 5/15min
// instead of becoming 5×N). SYSTEM table — IP/pre-auth keyed, NOT tenant-RLS (see
// rls-init.ts M57: RLS DISABLED, system-exempt; a pre-auth IP counter must not
// carry a tenant policy). expires_at drives a cheap opportunistic prune.
export const rateLimitCounters = pgTable("rate_limit_counters", {
  limitKey: text("limit_key").notNull(),
  windowStart: timestamp("window_start", { withTimezone: true }).notNull(),
  count: integer("count").notNull().default(0),
  expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(),
  createdAt: timestamp("created_at", { withTimezone: true }).notNull().default(sql`NOW()`),
}, (t) => ({
  pk: primaryKey({ columns: [t.limitKey, t.windowStart] }),
}));

// ── 2. Safety Rails — Agent API Keys + Violations ────────────────────────────

export const safetyRailKeys = pgTable("safety_rail_keys", {
  id: varchar("id").primaryKey(),
  agentType: text("agent_type").notNull(),
  keyHash: text("key_hash").notNull(),
  isRevoked: boolean("is_revoked").notNull().default(false),
  revokedAt: timestamp("revoked_at"),
  revokedReason: text("revoked_reason"),
  expiresAt: timestamp("expires_at").notNull(),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
});

export const safetyViolations = pgTable("safety_violations", {
  id: varchar("id").primaryKey(),
  type: text("type").notNull(),
  severity: text("severity").notNull(),
  agentId: text("agent_id").notNull(),
  description: text("description").notNull(),
  evidence: text("evidence").notNull().default("{}"),
  action: text("action").notNull(),
  handledAt: timestamp("handled_at"),
  detectedAt: timestamp("detected_at").notNull().default(sql`NOW()`),
});

// ── 3. PCI DSS Audit Logs ─────────────────────────────────────────────────────

export const pciAuditLogs = pgTable("pci_audit_logs", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  eventType: text("event_type").notNull(),
  userId: text("user_id").notNull(),
  userName: text("user_name"),
  resourceType: text("resource_type").notNull(),
  resourceId: text("resource_id"),
  action: text("action").notNull(),
  success: boolean("success").notNull(),
  ipAddress: text("ip_address").notNull(),
  userAgent: text("user_agent"),
  sessionId: text("session_id"),
  maskingApplied: boolean("masking_applied").notNull().default(true),
  dataElements: text("data_elements"),
  justification: text("justification"),
  approvedBy: text("approved_by"),
  riskScore: integer("risk_score").notNull().default(0),
  geoLocation: text("geo_location"),
  deviceFingerprint: text("device_fingerprint"),
  tenantId: text("tenant_id"),
  loggedAt: timestamp("logged_at").notNull().default(sql`NOW()`),
}).enableRLS();

// ── 4. SOC2 Evidence Controls ─────────────────────────────────────────────────

export const soc2Controls = pgTable("soc2_controls", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  category: text("category").notNull(),
  controlId: text("control_id").notNull(),
  title: text("title").notNull(),
  description: text("description").notNull(),
  implementationStatus: text("implementation_status").notNull().default("NOT_IMPLEMENTED"),
  evidenceRequired: text("evidence_required").notNull().default("[]"),
  evidenceCollected: text("evidence_collected").notNull().default("[]"),
  testingFrequency: text("testing_frequency").notNull().default("MONTHLY"),
  lastTested: timestamp("last_tested"),
  nextTestDue: timestamp("next_test_due"),
  riskLevel: text("risk_level").notNull().default("MEDIUM"),
  owner: text("owner"),
  notes: text("notes"),
  tenantId: text("tenant_id"),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
  updatedAt: timestamp("updated_at").notNull().default(sql`NOW()`),
}).enableRLS();

// ── ISO 27001:2022 Annex A controls (per-tenant compliance register) ─────────
// Drives the iso27001 framework score in wave-final-services.ts (compliant/total).
// Status starts "not_assessed" so the score honestly reflects real assessment
// state (0% until a control is verified) rather than a fabricated pass.
export const iso27001Controls = pgTable("iso27001_controls", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  tenantId: text("tenant_id"),
  controlId: text("control_id").notNull(),          // e.g. "A.8.24"
  theme: text("theme").notNull(),                    // Organizational | People | Physical | Technological
  title: text("title").notNull(),
  status: text("status").notNull().default("not_assessed"), // not_assessed | compliant | non_compliant | not_applicable
  notes: text("notes"),
  assessedAt: timestamp("assessed_at"),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
  updatedAt: timestamp("updated_at").notNull().default(sql`NOW()`),
}).enableRLS();

// ── SWIFT Customer Security Programme (CSP) controls (per-tenant register) ────
// Drives the swift_csp framework score (mandatory controls compliant / total).
export const swiftCspControls = pgTable("swift_csp_controls", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  tenantId: text("tenant_id"),
  controlId: text("control_id").notNull(),          // e.g. "2.1"
  title: text("title").notNull(),
  mandatory: boolean("mandatory").notNull().default(true),
  status: text("status").notNull().default("not_assessed"),
  notes: text("notes"),
  assessedAt: timestamp("assessed_at"),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
  updatedAt: timestamp("updated_at").notNull().default(sql`NOW()`),
}).enableRLS();

export const soc2Evidence = pgTable("soc2_evidence", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  controlId: varchar("control_id").notNull(),
  type: text("type").notNull(),
  title: text("title").notNull(),
  description: text("description").notNull(),
  collectedBy: text("collected_by").notNull(),
  source: text("source").notNull(),
  content: text("content"),
  attachmentUrl: text("attachment_url"),
  isAutomated: boolean("is_automated").notNull().default(false),
  validUntil: timestamp("valid_until"),
  tenantId: text("tenant_id"),
  collectedAt: timestamp("collected_at").notNull().default(sql`NOW()`),
}).enableRLS();

// ── 5. Compliance Dashboard — Controls + Gaps ─────────────────────────────────

export const complianceControls = pgTable("compliance_controls", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  frameworkId: text("framework_id").notNull(),
  category: text("category").notNull(),
  name: text("name").notNull(),
  description: text("description").notNull(),
  status: text("status").notNull().default("not_applicable"),
  priority: text("priority").notNull().default("medium"),
  lastAssessed: timestamp("last_assessed"),
  nextAssessment: timestamp("next_assessment"),
  assignee: text("assignee"),
  evidence: text("evidence").notNull().default("[]"),
  gaps: text("gaps").notNull().default("[]"),
  remediationPlan: text("remediation_plan"),
  notes: text("notes"),
  tenantId: text("tenant_id"),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
  updatedAt: timestamp("updated_at").notNull().default(sql`NOW()`),
}).enableRLS();

export const complianceGaps = pgTable("compliance_gaps", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  frameworkId: text("framework_id").notNull(),
  controlId: text("control_id").notNull(),
  description: text("description").notNull(),
  severity: text("severity").notNull(),
  identifiedDate: timestamp("identified_date").notNull().default(sql`NOW()`),
  targetRemediationDate: timestamp("target_remediation_date").notNull(),
  status: text("status").notNull().default("open"),
  assignee: text("assignee"),
  remediationSteps: text("remediation_steps").notNull().default("[]"),
  businessImpact: text("business_impact"),
  tenantId: text("tenant_id"),
  updatedAt: timestamp("updated_at").notNull().default(sql`NOW()`),
}).enableRLS();

export const complianceAuditEvents = pgTable("compliance_audit_events", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  frameworkId: text("framework_id").notNull(),
  type: text("type").notNull(),
  description: text("description").notNull(),
  performedBy: text("performed_by").notNull(),
  outcome: text("outcome"),
  attachments: text("attachments").notNull().default("[]"),
  tenantId: text("tenant_id"),
  timestamp: timestamp("timestamp").notNull().default(sql`NOW()`),
}).enableRLS();

// ── 6. Behavioral Biometrics ──────────────────────────────────────────────────

export const biometricProfiles = pgTable("biometric_profiles", {
  // user_id is text (UUID) — matches users.id / session.userId. Was integer, which made every write
  // Number(uuid)=NaN and fail (table stayed 0-rows); retyped in migration M75 (rls-init.ts).
  userId: text("user_id").primaryKey(),
  tenantId: text("tenant_id"), // AS/PLATFORM/2026/008 Chunk B — derived from user home tenant (user_tenant_roles is_primary)
  keystrokeBaselineJson: text("keystroke_baseline_json"),
  mouseBaselineJson: text("mouse_baseline_json"),
  sampleCount: integer("sample_count").notNull().default(0),
  confidenceScore: numeric("confidence_score", { precision: 4, scale: 3 }).notNull().default("0"),
  lastUpdated: timestamp("last_updated").notNull().default(sql`NOW()`),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
}).enableRLS();

// ── 7. Vendor DNA Shield ──────────────────────────────────────────────────────

export const vendorDnaProfiles = pgTable("vendor_dna_profiles", {
  id: varchar("id").primaryKey(),
  vendorName: text("vendor_name").notNull(),
  vendorUrl: text("vendor_url").notNull(),
  category: text("category").notNull(),
  riskLevel: text("risk_level").notNull().default("LOW"),
  baselineJson: text("baseline_json").notNull().default("{}"),
  currentMetricsJson: text("current_metrics_json").notNull().default("{}"),
  status: text("status").notNull().default("MONITORING"),
  lastCheckedAt: timestamp("last_checked_at"),
  tenantId: text("tenant_id"),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
  updatedAt: timestamp("updated_at").notNull().default(sql`NOW()`),
}).enableRLS();

export const vendorDnaAlerts = pgTable("vendor_dna_alerts", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  vendorId: varchar("vendor_id").notNull(),
  alertType: text("alert_type").notNull(),
  severity: text("severity").notNull(),
  description: text("description").notNull(),
  detectedValue: text("detected_value"),
  expectedValue: text("expected_value"),
  status: text("status").notNull().default("OPEN"),
  tenantId: text("tenant_id"),
  detectedAt: timestamp("detected_at").notNull().default(sql`NOW()`),
  resolvedAt: timestamp("resolved_at"),
}).enableRLS();

// ── 8. AI-BOM Components ──────────────────────────────────────────────────────

export const abomComponents = pgTable("abom_components", {
  id: varchar("id").primaryKey(),
  name: text("name").notNull(),
  version: text("version").notNull(),
  type: text("type").notNull(),
  provider: text("provider").notNull(),
  purpose: text("purpose").notNull(),
  riskLevel: text("risk_level").notNull().default("LOW"),
  licenseType: text("license_type"),
  integrityHash: text("integrity_hash"),
  dataAccess: text("data_access").notNull().default("[]"),
  capabilities: text("capabilities").notNull().default("[]"),
  vulnerabilities: text("vulnerabilities").notNull().default("[]"),
  status: text("status").notNull().default("ACTIVE"),
  isApproved: boolean("is_approved").notNull().default(false),
  approvedBy: text("approved_by"),
  approvedAt: timestamp("approved_at"),
  tenantId: text("tenant_id"),
  registeredAt: timestamp("registered_at").notNull().default(sql`NOW()`),
  updatedAt: timestamp("updated_at").notNull().default(sql`NOW()`),
}).enableRLS();

export const abomDataSources = pgTable("abom_data_sources", {
  id: varchar("id").primaryKey(),
  name: text("name").notNull(),
  type: text("type").notNull(),
  classification: text("classification").notNull(),
  retentionDays: integer("retention_days"),
  piiCategories: text("pii_categories").notNull().default("[]"),
  accessControls: text("access_controls").notNull().default("[]"),
  isEncrypted: boolean("is_encrypted").notNull().default(false),
  componentIds: text("component_ids").notNull().default("[]"),
  tenantId: text("tenant_id"),
  registeredAt: timestamp("registered_at").notNull().default(sql`NOW()`),
}).enableRLS();

// ── 9. Cloud Exit State ───────────────────────────────────────────────────────

export const cloudEnvironments = pgTable("cloud_environments", {
  id: varchar("id").primaryKey(),
  name: text("name").notNull(),
  provider: text("provider").notNull(),
  region: text("region").notNull(),
  isPrimary: boolean("is_primary").notNull().default(false),
  isSovereign: boolean("is_sovereign").notNull().default(false),
  status: text("status").notNull().default("ACTIVE"),
  servicesJson: text("services_json").notNull().default("[]"),
  costMonthlyUsd: numeric("cost_monthly_usd", { precision: 12, scale: 2 }),
  tenantId: text("tenant_id"),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
  updatedAt: timestamp("updated_at").notNull().default(sql`NOW()`),
}).enableRLS();

export const cloudMigrationPlans = pgTable("cloud_migration_plans", {
  id: varchar("id").primaryKey(),
  trigger: text("trigger").notNull(),
  status: text("status").notNull().default("PLANNING"),
  sourceEnvironmentId: varchar("source_environment_id").notNull(),
  targetEnvironmentId: varchar("target_environment_id").notNull(),
  estimatedDurationHours: integer("estimated_duration_hours"),
  componentsJson: text("components_json").notNull().default("[]"),
  progressPct: integer("progress_pct").notNull().default(0),
  initiatedBy: text("initiated_by"),
  completedAt: timestamp("completed_at"),
  tenantId: text("tenant_id"),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
}).enableRLS();

export const cloudSovereigntyAlarms = pgTable("cloud_sovereignty_alarms", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  alarmType: text("alarm_type").notNull(),
  severity: text("severity").notNull(),
  description: text("description").notNull(),
  affectedEnvironment: text("affected_environment"),
  status: text("status").notNull().default("ACTIVE"),
  acknowledgedBy: text("acknowledged_by"),
  acknowledgedAt: timestamp("acknowledged_at"),
  tenantId: text("tenant_id"),
  triggeredAt: timestamp("triggered_at").notNull().default(sql`NOW()`),
}).enableRLS();

// ── 10. ZK Threat Exchange ────────────────────────────────────────────────────

export const zkMembers = pgTable("zk_members", {
  id: varchar("id").primaryKey(),
  name: text("name").notNull(),
  memberType: text("member_type").notNull(),
  publicKey: text("public_key").notNull(),
  reputationScore: integer("reputation_score").notNull().default(80),
  contributedFingerprints: integer("contributed_fingerprints").notNull().default(0),
  receivedAlerts: integer("received_alerts").notNull().default(0),
  status: text("status").notNull().default("ACTIVE"),
  region: text("region"),
  joinedAt: timestamp("joined_at").notNull().default(sql`NOW()`),
  lastActiveAt: timestamp("last_active_at"),
});

export const zkThreatFingerprints = pgTable("zk_threat_fingerprints", {
  id: varchar("id").primaryKey(),
  contributorId: varchar("contributor_id").notNull(),
  threatType: text("threat_type").notNull(),
  hashPattern: text("hash_pattern").notNull(),
  proofHash: text("proof_hash").notNull(),
  confidence: integer("confidence").notNull().default(50),
  matchCount: integer("match_count").notNull().default(0),
  isActive: boolean("is_active").notNull().default(true),
  expiresAt: timestamp("expires_at"),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
});

// ── 11. Supply Chain Shield ───────────────────────────────────────────────────

export const supplyChainHashes = pgTable("supply_chain_hashes", {
  packageName: varchar("package_name").primaryKey(),
  expectedHash: text("expected_hash").notNull(),
  algorithm: text("algorithm").notNull().default("sha256"),
  version: text("version").notNull(),
  addedBy: text("added_by"),
  verifiedAt: timestamp("verified_at").notNull().default(sql`NOW()`),
  expiresAt: timestamp("expires_at"),
});

export const supplyChainPolicies = pgTable("supply_chain_policies", {
  id: varchar("id").primaryKey(),
  name: text("name").notNull(),
  description: text("description"),
  rulesJson: text("rules_json").notNull().default("[]"),
  enforcementLevel: text("enforcement_level").notNull().default("WARN"),
  isActive: boolean("is_active").notNull().default(true),
  tenantId: text("tenant_id"),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
  updatedAt: timestamp("updated_at").notNull().default(sql`NOW()`),
}).enableRLS();

// ── 12. SaaS Platform — Tenants + Metering ────────────────────────────────────

export const saasTenantsTable = pgTable("saas_tenants", {
  tenantId: varchar("tenant_id").primaryKey(),
  name: text("name").notNull(),
  industry: text("industry").notNull(),
  tier: text("tier").notNull().default("FREE"),
  isolationLevel: text("isolation_level").notNull().default("SHARED"),
  namespace: text("namespace").notNull(),
  region: text("region").notNull().default("UG"),
  status: text("status").notNull().default("ACTIVE"),
  dataResidency: text("data_residency").notNull().default("UGANDA_ONLY"),
  apiKeyHash: text("api_key_hash").notNull(),
  usageQuotaJson: text("usage_quota_json").notNull().default("{}"),
  resourceQuotaJson: text("resource_quota_json").notNull().default("{}"),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
  updatedAt: timestamp("updated_at").notNull().default(sql`NOW()`),
}).enableRLS();

export const saasUsageEvents = pgTable("saas_usage_events", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  tenantId: text("tenant_id").notNull(),
  metric: text("metric").notNull(),
  units: integer("units").notNull(),
  unitPrice: numeric("unit_price", { precision: 10, scale: 6 }).notNull(),
  totalCost: numeric("total_cost", { precision: 12, scale: 4 }).notNull(),
  metadata: text("metadata"),
  recordedAt: timestamp("recorded_at").notNull().default(sql`NOW()`),
}).enableRLS();

export const saasBillingPeriods = pgTable("saas_billing_periods", {
  id: varchar("id").primaryKey(),
  tenantId: text("tenant_id").notNull(),
  startDate: timestamp("start_date").notNull(),
  endDate: timestamp("end_date").notNull(),
  status: text("status").notNull().default("OPEN"),
  usageJson: text("usage_json").notNull().default("{}"),
  totalCost: numeric("total_cost", { precision: 12, scale: 4 }).notNull().default("0"),
  invoiceUrl: text("invoice_url"),
  updatedAt: timestamp("updated_at").notNull().default(sql`NOW()`),
}).enableRLS();

// ── 13. CBS Transaction Logs (core-banking-gateway) ──────────────────────────

export const cbsTransactionLogs = pgTable("cbs_transaction_logs", {
  id: varchar("id").primaryKey(),
  transactionId: text("transaction_id").notNull().unique(),
  type: text("type").notNull(),
  accountFrom: text("account_from"),
  accountTo: text("account_to"),
  amount: numeric("amount", { precision: 20, scale: 4 }),
  currency: text("currency").notNull().default("UGX"),
  status: text("status").notNull(),
  provider: text("provider"),
  phase: text("phase"),
  errorMessage: text("error_message"),
  requestPayload: text("request_payload"),
  responsePayload: text("response_payload"),
  durationMs: integer("duration_ms"),
  initiatedBy: text("initiated_by"),
  tenantId: text("tenant_id"),
  loggedAt: timestamp("logged_at").notNull().default(sql`NOW()`),
}).enableRLS();

export const cbsKillSwitchLog = pgTable("cbs_kill_switch_log", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  action: text("action").notNull(),
  reason: text("reason").notNull(),
  targetType: text("target_type"),
  targetId: text("target_id"),
  executedBy: text("executed_by").notNull(),
  reversedAt: timestamp("reversed_at"),
  reversedBy: text("reversed_by"),
  // Link the structured enforcement record to the CBS transaction it acted on — the join the
  // general audit_logs entry (ACCOUNT_FROZEN) lacks. cbsReference = the core-banking system's ref;
  // aegisReference = the internal two-phase-commit transaction id (cbs_transaction_logs.transaction_id).
  cbsReference: text("cbs_reference"),
  aegisReference: text("aegis_reference"),
  tenantId: text("tenant_id"),
  executedAt: timestamp("executed_at").notNull().default(sql`NOW()`),
}).enableRLS();

export type Fido2Credential = typeof fido2Credentials.$inferSelect;
export type Soc2Control = typeof soc2Controls.$inferSelect;
export type ComplianceControl = typeof complianceControls.$inferSelect;
export type VendorDnaProfile = typeof vendorDnaProfiles.$inferSelect;
export type AbomComponent = typeof abomComponents.$inferSelect;
export type ZkMember = typeof zkMembers.$inferSelect;
export type SaasTenant = typeof saasTenantsTable.$inferSelect;

// ── Sanctions screening — real list storage (OFAC SDN Increment 1) ───────────
// GLOBAL reference data (a sanctions list is identical for every customer; under
// one-DB-per-bank each deployment is single-tenant, so per-tenant duplication of a
// public list would be wrong). Screening RESULTS stay per-customer via the existing
// KYC screen route. RLS-DISABLED system-exempt (created by boot DDL M58, mirrors M57).
export const sanctionsListVersions = pgTable("sanctions_list_versions", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  source: text("source").notNull(),          // e.g. "OFAC_SDN"
  versionHash: text("version_hash").notNull(), // sha256 of the fetched raw payload
  entryCount: integer("entry_count").notNull(),
  primaryCount: integer("primary_count").notNull(),
  aliasCount: integer("alias_count").notNull(),
  fetchedAt: timestamp("fetched_at", { withTimezone: true }).notNull(),
  createdAt: timestamp("created_at", { withTimezone: true }).notNull().default(sql`NOW()`),
});

export const sanctionsEntries = pgTable("sanctions_entries", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  source: text("source").notNull(),           // "OFAC_SDN"
  entNum: text("ent_num").notNull(),          // OFAC entity number (links primary + aliases)
  entryType: text("entry_type").notNull(),    // "PRIMARY" | "ALIAS"
  originalName: text("original_name").notNull(),
  normalizedKey: text("normalized_key").notNull(), // folded + token-sorted match key
  // Double-Metaphone codes across the name's tokens — the recall-preserving fuzzy BLOCKING
  // key (OFAC Increment 2). A screen fetches candidates by array-overlap on these (GIN-indexed),
  // then Jaro-Winkler-scores them. Nullable: populated on (re)ingest; pre-Increment-2 rows are
  // NULL until the next refresh (exact matching still works meanwhile — degraded-but-safe).
  phoneticCodes: text("phonetic_codes").array(),
  sdnType: text("sdn_type"),                  // individual / entity / vessel / aircraft
  program: text("program"),                   // OFAC program (CUBA, SDGT, ...)
  // Secondary matching (Increment 3): normalized DOB tokens parsed from the sdn.csv remarks
  // field ("YYYY-MM-DD" | "YYYY-MM" | "YYYY" | "~YYYY" circa | "YYYY..YYYY" range). Entity-level
  // — alias rows carry the parent entity's set. Nullable: populated on (re)ingest; NULL rows
  // assess as NO_DOB_DATA at screen time (degraded-but-honest, phonetic_codes precedent).
  // ADJUDICATION AID ONLY — a DOB mismatch NEVER auto-clears a name hit.
  dobs: text("dobs").array(),
  createdAt: timestamp("created_at", { withTimezone: true }).notNull().default(sql`NOW()`),
});

export type SanctionsListVersion = typeof sanctionsListVersions.$inferSelect;
export type SanctionsEntry = typeof sanctionsEntries.$inferSelect;

// Periodic re-screen run evidence (OFAC_RESCREEN_SCOPE.md, boot DDL M62). One row per
// list-update sweep of the KYC customer book. GLOBAL system table like the two above —
// no PII (per-customer HITS live in tenant-isolated kyc_life_events); RLS-DISABLED
// system-exempt. `unscreened` is the fail-closed honesty column: customers the sweep
// could NOT screen (provider error / stale list / undecryptable row) are COUNTED,
// never silently treated as clear.
export const sanctionsRescreenRuns = pgTable("sanctions_rescreen_runs", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  source: text("source").notNull(),            // e.g. "OFAC_SDN"
  versionHash: text("version_hash").notNull(), // the list version this sweep screened against
  status: text("status").notNull(),            // RUNNING | COMPLETE | PARTIAL | FAILED
  tenantsSwept: integer("tenants_swept").notNull().default(0),
  customersScreened: integer("customers_screened").notNull().default(0),
  newHits: integer("new_hits").notNull().default(0),         // new POTENTIAL/CONFIRMED matches surfaced
  dedupedHits: integer("deduped_hits").notNull().default(0), // same-entity hits already on record (not re-flagged)
  unscreened: integer("unscreened").notNull().default(0),    // fail-closed count — NOT screened ≠ clear
  errorNote: text("error_note"),
  startedAt: timestamp("started_at", { withTimezone: true }).notNull(),
  finishedAt: timestamp("finished_at", { withTimezone: true }),
  createdAt: timestamp("created_at", { withTimezone: true }).notNull().default(sql`NOW()`),
});

export type SanctionsRescreenRun = typeof sanctionsRescreenRuns.$inferSelect;