import { pgTable, varchar, text, timestamp, integer, jsonb, real, index } from "drizzle-orm/pg-core";
import { sql } from "drizzle-orm";
import { createInsertSchema } from "drizzle-zod";
import { z } from "zod";

const idCol = () => varchar("id").primaryKey().default(sql`gen_random_uuid()`);
const ts = (n: string) => timestamp(n, { withTimezone: true }).notNull().defaultNow();

// ============================================================
// Wave-13 — Sandbox Closer Suite (14 modules across Tier 1-4)
// ============================================================

// ---------- TIER 1 — BoU / PDPO direct gaps ----------

// 1. Cross-Border Data Transfer Ledger (PDPO §19)
export const xborderTransfers = pgTable("xborder_transfers", {
  id: idCol(),
  transferRef: varchar("transfer_ref", { length: 64 }).notNull().unique("xborder_transfers_transfer_ref_key"),
  sourceRegion: varchar("source_region", { length: 64 }).notNull().default("UG"),
  destRegion: varchar("dest_region", { length: 64 }).notNull(),
  destVendor: varchar("dest_vendor", { length: 192 }).notNull(),
  dataCategory: varchar("data_category", { length: 64 }).notNull(),
  bytesTransferred: integer("bytes_transferred").notNull().default(0),
  recordCount: integer("record_count").notNull().default(0),
  legalBasis: varchar("legal_basis", { length: 64 }).notNull(), // consent | contract | adequacy | legal_obligation | vital_interest | public_task
  approvalRef: varchar("approval_ref", { length: 96 }),
  approvedBy: varchar("approved_by", { length: 128 }),
  notes: text("notes"),
  loggedBy: varchar("logged_by", { length: 128 }).notNull(),
  loggedAt: ts("logged_at"),
}, (t) => ({
  loggedAtIdx: index("xborder_transfers_logged_at_idx").on(t.loggedAt.desc()),
  destRegionIdx: index("xborder_transfers_dest_region_idx").on(t.destRegion),
}));

// 2. Consent Events Ledger (PDPO §6-7)
export const consentEvents = pgTable("consent_events", {
  id: idCol(),
  eventRef: varchar("event_ref", { length: 64 }).notNull().unique("consent_events_event_ref_key"),
  customerRef: varchar("customer_ref", { length: 128 }).notNull(),
  // AS/CYBER/CLIENTDATA-ISOLATION Batch 1b — tenant isolation (nullable Phase-1; SET NOT NULL deferred to Phase-2).
  tenantId: text("tenant_id"),
  purpose: varchar("purpose", { length: 96 }).notNull(),
  scope: varchar("scope", { length: 192 }).notNull(),
  action: varchar("action", { length: 32 }).notNull(), // grant | revoke | renew | expire
  channel: varchar("channel", { length: 32 }).notNull().default("portal"),
  evidenceHash: varchar("evidence_hash", { length: 128 }).notNull(),
  ipAddress: varchar("ip_address", { length: 64 }),
  capturedBy: varchar("captured_by", { length: 128 }).notNull(),
  capturedAt: ts("captured_at"),
}, (t) => ({
  customerIdx: index("consent_events_customer_idx").on(t.customerRef),
  capturedAtIdx: index("consent_events_captured_at_idx").on(t.capturedAt.desc()),
})).enableRLS();

// 3. Right-to-Erasure Destructive Drill (PDPO §32)
export const erasureDrills = pgTable("erasure_drills", {
  id: idCol(),
  drillRef: varchar("drill_ref", { length: 64 }).notNull().unique("erasure_drills_drill_ref_key"),
  customerRef: varchar("customer_ref", { length: 128 }).notNull(),
  // AS/CYBER/CLIENTDATA-ISOLATION Batch 1b — tenant isolation (nullable Phase-1; SET NOT NULL deferred to Phase-2).
  tenantId: text("tenant_id"),
  scopesAttempted: integer("scopes_attempted").notNull().default(0),
  scopesPurged: integer("scopes_purged").notNull().default(0),
  scopesFailed: integer("scopes_failed").notNull().default(0),
  primaryPurged: integer("primary_purged").notNull().default(0),
  replicaPurged: integer("replica_purged").notNull().default(0),
  backupPurged: integer("backup_purged").notNull().default(0),
  archivePurged: integer("archive_purged").notNull().default(0),
  logsRedacted: integer("logs_redacted").notNull().default(0),
  receiptHash: varchar("receipt_hash", { length: 128 }).notNull(),
  signatureHex: varchar("signature_hex", { length: 256 }).notNull(),
  durationMs: integer("duration_ms").notNull().default(0),
  passed: integer("passed").notNull().default(0),
  detailsJson: jsonb("details_json").notNull().default("{}"),
  triggeredBy: varchar("triggered_by", { length: 128 }).notNull(),
  triggeredAt: ts("triggered_at"),
  // Added 2026-05-04 (FU-011 erasure-drill RESOLUTION + FU-014).
  // Real-wired erasure receipt fields. The legacy 5-scope columns above
  // (primaryPurged/replicaPurged/backupPurged/archivePurged/logsRedacted)
  // are zeroed for post-cutover rows; pre-cutover rows retain their
  // original synthetic values per the immutability principle.
  tablesScanned: integer("tables_scanned").notNull().default(0),
  realRowsErased: integer("real_rows_erased").notNull().default(0),
  legallyRetainedRows: integer("legally_retained_rows").notNull().default(0),
  cacheInvalidationStatus: varchar("cache_invalidation_status", { length: 96 }).notNull().default(""),
  cacheInvalidationVerifiedAt: timestamp("cache_invalidation_verified_at", { withTimezone: true }).notNull().defaultNow(),
  priorErasureFound: integer("prior_erasure_found").notNull().default(0),
}, (t) => ({
  triggeredAtIdx: index("erasure_drills_triggered_at_idx").on(t.triggeredAt.desc()),
})).enableRLS();

// 4a. SoD rules (config)
export const sodRules = pgTable("sod_rules", {
  id: idCol(),
  ruleKey: varchar("rule_key", { length: 96 }).notNull().unique("sod_rules_rule_key_key"),
  description: varchar("description", { length: 255 }).notNull(),
  forbiddenPair: jsonb("forbidden_pair").notNull(), // ["role_or_perm_a","role_or_perm_b"]
  severity: varchar("severity", { length: 16 }).notNull().default("high"),
  enabled: integer("enabled").notNull().default(1),
  updatedAt: ts("updated_at"),
});
// 4b. SoD violation runs
export const sodRuns = pgTable("sod_runs", {
  id: idCol(),
  runRef: varchar("run_ref", { length: 64 }).notNull().unique("sod_runs_run_ref_key"),
  rulesEvaluated: integer("rules_evaluated").notNull().default(0),
  usersScanned: integer("users_scanned").notNull().default(0),
  violationsFound: integer("violations_found").notNull().default(0),
  passed: integer("passed").notNull().default(0),
  detailsJson: jsonb("details_json").notNull().default("{}"),
  triggeredBy: varchar("triggered_by", { length: 128 }).notNull(),
  triggeredAt: ts("triggered_at"),
}, (t) => ({
  triggeredAtIdx: index("sod_runs_triggered_at_idx").on(t.triggeredAt.desc()),
}));

// 5. Sanctions Screening Latency Benchmark
export const sanctionsScreeningRuns = pgTable("sanctions_screening_runs", {
  id: idCol(),
  runRef: varchar("run_ref", { length: 64 }).notNull().unique("sanctions_screening_runs_run_ref_key"),
  listsChecked: integer("lists_checked").notNull().default(0),
  staleListsCount: integer("stale_lists_count").notNull().default(0),
  staleListsThresholdHrs: integer("stale_lists_threshold_hrs").notNull().default(24),
  sampleSize: integer("sample_size").notNull().default(0),
  p50Ms: integer("p50_ms").notNull().default(0),
  p95Ms: integer("p95_ms").notNull().default(0),
  p99Ms: integer("p99_ms").notNull().default(0),
  hitsCount: integer("hits_count").notNull().default(0),
  passed: integer("passed").notNull().default(0),
  detailsJson: jsonb("details_json").notNull().default("{}"),
  triggeredBy: varchar("triggered_by", { length: 128 }).notNull(),
  triggeredAt: ts("triggered_at"),
}, (t) => ({
  triggeredAtIdx: index("sanctions_screening_triggered_at_idx").on(t.triggeredAt.desc()),
}));

// 6a. Vendor register
export const vendorRegister = pgTable("vendor_register", {
  id: idCol(),
  vendorRef: varchar("vendor_ref", { length: 64 }).notNull().unique("vendor_register_vendor_ref_key"),
  vendorName: varchar("vendor_name", { length: 192 }).notNull(),
  serviceCategory: varchar("service_category", { length: 64 }).notNull(),
  criticality: varchar("criticality", { length: 16 }).notNull().default("medium"), // low | medium | high | critical
  country: varchar("country", { length: 64 }).notNull().default("UG"),
  contractStart: timestamp("contract_start", { withTimezone: true }),
  contractEnd: timestamp("contract_end", { withTimezone: true }),
  exitClauseDocumented: integer("exit_clause_documented").notNull().default(0),
  active: integer("active").notNull().default(1),
  registeredBy: varchar("registered_by", { length: 128 }).notNull(),
  registeredAt: ts("registered_at"),
}, (t) => ({
  registeredAtIdx: index("vendor_register_registered_at_idx").on(t.registeredAt.desc()),
  vendorNameIdx: index("vendor_register_vendor_name_idx").on(t.vendorName),
}));
// 6b. Vendor periodic attestations
export const vendorAttestations = pgTable("vendor_attestations", {
  id: idCol(),
  attestationRef: varchar("attestation_ref", { length: 64 }).notNull().unique("vendor_attestations_attestation_ref_key"),
  vendorRef: varchar("vendor_ref", { length: 64 }).notNull(),
  attestationType: varchar("attestation_type", { length: 64 }).notNull(), // soc2 | iso27001 | pci_dss | gdpr | custom
  validFrom: timestamp("valid_from", { withTimezone: true }).notNull(),
  validUntil: timestamp("valid_until", { withTimezone: true }).notNull(),
  evidenceHash: varchar("evidence_hash", { length: 128 }).notNull(),
  signatureHex: varchar("signature_hex", { length: 256 }).notNull(),
  attesterRef: varchar("attester_ref", { length: 128 }).notNull(),
  uploadedBy: varchar("uploaded_by", { length: 128 }).notNull(),
  uploadedAt: ts("uploaded_at"),
}, (t) => ({
  vendorIdx: index("vendor_attestations_vendor_idx").on(t.vendorRef),
  uploadedAtIdx: index("vendor_attestations_uploaded_at_idx").on(t.uploadedAt.desc()),
}));

// ---------- TIER 2 — Cyber-resilience rigor ----------

// 7. MTTD / MTTR per threat class
export const mttrRecords = pgTable("mttr_records", {
  id: idCol(),
  recordRef: varchar("record_ref", { length: 64 }).notNull().unique("mttr_records_record_ref_key"),
  threatClass: varchar("threat_class", { length: 64 }).notNull(),
  occurredAt: timestamp("occurred_at", { withTimezone: true }).notNull(),
  detectedAt: timestamp("detected_at", { withTimezone: true }).notNull(),
  containedAt: timestamp("contained_at", { withTimezone: true }).notNull(),
  recoveredAt: timestamp("recovered_at", { withTimezone: true }),
  mttdSeconds: integer("mttd_seconds").notNull().default(0),
  mttrSeconds: integer("mttr_seconds").notNull().default(0),
  severity: varchar("severity", { length: 16 }).notNull().default("medium"),
  notes: text("notes"),
  recordedBy: varchar("recorded_by", { length: 128 }).notNull(),
  recordedAt: ts("recorded_at"),
}, (t) => ({
  recordedAtIdx: index("mttr_records_recorded_at_idx").on(t.recordedAt.desc()),
  threatClassIdx: index("mttr_records_threat_class_idx").on(t.threatClass),
}));

// 8. Full IR-Cycle Drill (Detect→Contain→Eradicate→Recover→PIR)
export const irDrillRuns = pgTable("ir_drill_runs", {
  id: idCol(),
  drillRef: varchar("drill_ref", { length: 64 }).notNull().unique("ir_drill_runs_drill_ref_key"),
  scenario: varchar("scenario", { length: 96 }).notNull(),
  phasesCompleted: integer("phases_completed").notNull().default(0),
  detectMs: integer("detect_ms").notNull().default(0),
  containMs: integer("contain_ms").notNull().default(0),
  eradicateMs: integer("eradicate_ms").notNull().default(0),
  recoverMs: integer("recover_ms").notNull().default(0),
  pirMs: integer("pir_ms").notNull().default(0),
  totalMs: integer("total_ms").notNull().default(0),
  pirArtifactHash: varchar("pir_artifact_hash", { length: 128 }).notNull(),
  passed: integer("passed").notNull().default(0),
  // FU-011 silent-pass conv #2 (2026-05-06): incidentRef traces this
  // receipt to the underlying real incident_slas row. NULL on pre-cutover
  // synthetic receipts (additive migration, no row modified).
  incidentRef: varchar("incident_ref", { length: 64 }),
  // FU-011 silent-pass conv #2 (2026-05-06): per-phase failures captured
  // here. passed=0 iff perPhaseErrors.length > 0. Empty array on success
  // (refusal-criterion #3: no swallowed errors → false-positive PASS).
  perPhaseErrors: jsonb("per_phase_errors").notNull().default("[]"),
  detailsJson: jsonb("details_json").notNull().default("{}"),
  triggeredBy: varchar("triggered_by", { length: 128 }).notNull(),
  triggeredAt: ts("triggered_at"),
}, (t) => ({
  triggeredAtIdx: index("ir_drill_runs_triggered_at_idx").on(t.triggeredAt.desc()),
}));

// 9. Break-Glass / Dual-Control Emergency Access Drill
export const breakglassDrills = pgTable("breakglass_drills", {
  id: idCol(),
  drillRef: varchar("drill_ref", { length: 64 }).notNull().unique("breakglass_drills_drill_ref_key"),
  primaryOfficer: varchar("primary_officer", { length: 128 }).notNull(),
  approverOfficer: varchar("approver_officer", { length: 128 }).notNull(),
  reason: varchar("reason", { length: 255 }).notNull(),
  ticketRef: varchar("ticket_ref", { length: 64 }).notNull(),
  grantedAt: timestamp("granted_at", { withTimezone: true }).notNull(),
  expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(),
  autoExpired: integer("auto_expired").notNull().default(0),
  alertSent: integer("alert_sent").notNull().default(0),
  dualControlOk: integer("dual_control_ok").notNull().default(0),
  passed: integer("passed").notNull().default(0),
  detailsJson: jsonb("details_json").notNull().default("{}"),
  triggeredBy: varchar("triggered_by", { length: 128 }).notNull(),
  triggeredAt: ts("triggered_at"),
}, (t) => ({
  triggeredAtIdx: index("breakglass_drills_triggered_at_idx").on(t.triggeredAt.desc()),
}));

// 10. Network Segmentation Reachability Matrix
export const segmentationRuns = pgTable("segmentation_runs", {
  id: idCol(),
  runRef: varchar("run_ref", { length: 64 }).notNull().unique("segmentation_runs_run_ref_key"),
  pairsTested: integer("pairs_tested").notNull().default(0),
  expectedReachable: integer("expected_reachable").notNull().default(0),
  expectedBlocked: integer("expected_blocked").notNull().default(0),
  unexpectedReachable: integer("unexpected_reachable").notNull().default(0),
  unexpectedBlocked: integer("unexpected_blocked").notNull().default(0),
  passed: integer("passed").notNull().default(0),
  detailsJson: jsonb("details_json").notNull().default("{}"),
  triggeredBy: varchar("triggered_by", { length: 128 }).notNull(),
  triggeredAt: ts("triggered_at"),
}, (t) => ({
  triggeredAtIdx: index("segmentation_runs_triggered_at_idx").on(t.triggeredAt.desc()),
}));

// ---------- TIER 3 — AI completeness ----------

// 11. Adversarial Example Harness on KYT scoring model (FGSM/PGD-style perturbations)
export const adversarialExampleRuns = pgTable("adversarial_example_runs", {
  id: idCol(),
  runRef: varchar("run_ref", { length: 64 }).notNull().unique("adversarial_example_runs_run_ref_key"),
  modelRef: varchar("model_ref", { length: 96 }).notNull().default("kyt-v1"),
  attackKind: varchar("attack_kind", { length: 32 }).notNull(), // fgsm | pgd | boundary | feature-flip
  samples: integer("samples").notNull().default(0),
  successfulAttacks: integer("successful_attacks").notNull().default(0),
  robustAccuracyPct: real("robust_accuracy_pct").notNull().default(0),
  cleanAccuracyPct: real("clean_accuracy_pct").notNull().default(0),
  meanPerturbationL2: real("mean_perturbation_l2").notNull().default(0),
  passed: integer("passed").notNull().default(0),
  detailsJson: jsonb("details_json").notNull().default("{}"),
  triggeredBy: varchar("triggered_by", { length: 128 }).notNull(),
  triggeredAt: ts("triggered_at"),
}, (t) => ({
  triggeredAtIdx: index("adversarial_example_runs_triggered_at_idx").on(t.triggeredAt.desc()),
}));

// 12. Model Card + Bias/Fairness Audit
export const modelCardAudits = pgTable("model_card_audits", {
  id: idCol(),
  auditRef: varchar("audit_ref", { length: 64 }).notNull().unique("model_card_audits_audit_ref_key"),
  modelRef: varchar("model_ref", { length: 96 }).notNull(),
  modelVersion: varchar("model_version", { length: 32 }).notNull().default("v1"),
  segmentsEvaluated: integer("segments_evaluated").notNull().default(0),
  maxDisparityRatio: real("max_disparity_ratio").notNull().default(1),
  demographicParityPct: real("demographic_parity_pct").notNull().default(0),
  equalOpportunityPct: real("equal_opportunity_pct").notNull().default(0),
  modelCardHash: varchar("model_card_hash", { length: 128 }).notNull(),
  signatureHex: varchar("signature_hex", { length: 256 }).notNull(),
  passed: integer("passed").notNull().default(0),
  detailsJson: jsonb("details_json").notNull().default("{}"),
  triggeredBy: varchar("triggered_by", { length: 128 }).notNull(),
  triggeredAt: ts("triggered_at"),
}, (t) => ({
  triggeredAtIdx: index("model_card_audits_triggered_at_idx").on(t.triggeredAt.desc()),
}));

// ---------- TIER 4 — Supply chain ----------

// 13. Signed SBOM + SLSA Provenance
export const sbomAttestations = pgTable("sbom_attestations", {
  id: idCol(),
  attestationRef: varchar("attestation_ref", { length: 64 }).notNull().unique("sbom_attestations_attestation_ref_key"),
  buildRef: varchar("build_ref", { length: 96 }).notNull(),
  componentCount: integer("component_count").notNull().default(0),
  slsaLevel: integer("slsa_level").notNull().default(0),
  builderId: varchar("builder_id", { length: 192 }).notNull(),
  sourceUri: varchar("source_uri", { length: 255 }).notNull(),
  sourceDigest: varchar("source_digest", { length: 128 }).notNull(),
  sbomHash: varchar("sbom_hash", { length: 128 }).notNull(),
  signatureHex: varchar("signature_hex", { length: 256 }).notNull(),
  passed: integer("passed").notNull().default(0),
  detailsJson: jsonb("details_json").notNull().default("{}"),
  generatedBy: varchar("generated_by", { length: 128 }).notNull(),
  generatedAt: ts("generated_at"),
}, (t) => ({
  generatedAtIdx: index("sbom_attestations_generated_at_idx").on(t.generatedAt.desc()),
}));

// 14. EOL Software Inventory
export const eolInventoryRuns = pgTable("eol_inventory_runs", {
  id: idCol(),
  runRef: varchar("run_ref", { length: 64 }).notNull().unique("eol_inventory_runs_run_ref_key"),
  componentsScanned: integer("components_scanned").notNull().default(0),
  eolCount: integer("eol_count").notNull().default(0),
  nearEolCount: integer("near_eol_count").notNull().default(0), // within 90 days
  unknownCount: integer("unknown_count").notNull().default(0),
  worstEolDaysOverdue: integer("worst_eol_days_overdue").notNull().default(0),
  passed: integer("passed").notNull().default(0),
  detailsJson: jsonb("details_json").notNull().default("{}"),
  triggeredBy: varchar("triggered_by", { length: 128 }).notNull(),
  triggeredAt: ts("triggered_at"),
  // ── Fork C additions (FU-011 amendment, 2026-05-03 evening) ──
  feedStatus: varchar("feed_status", { length: 32 }).notNull().default("fresh"), // "fresh" | "stale-unreachable"
  noEolAnnouncedCount: integer("no_eol_announced_count").notNull().default(0),  // upstream returned eol:false
  notCoveredCount: integer("not_covered_count").notNull().default(0),           // tracked-but-not-on-endoflife.date (vite, typescript)
  currentCount: integer("current_count").notNull().default(0),                  // upstream returned future eol date
}, (t) => ({
  triggeredAtIdx: index("eol_inventory_runs_triggered_at_idx").on(t.triggeredAt.desc()),
}));

// 14b. EOL Feed Cache — endoflife.date lookup cache (24h TTL, 7d hard-stale fallback)
// Component key encodes name+cycle for deterministic lookup, e.g. "nodejs/20", "express/5".
// upstreamSignal records what the upstream feed told us, which drives downstream status mapping.
export const eolFeedCache = pgTable("eol_feed_cache", {
  componentKey: varchar("component_key", { length: 96 }).primaryKey(),
  eolDate: varchar("eol_date", { length: 32 }), // ISO date string or null when upstream said no-eol-announced
  upstreamSignal: varchar("upstream_signal", { length: 32 }).notNull(), // "date" | "no-eol-announced" | "version-not-found" | "fetch-failed"
  source: varchar("source", { length: 32 }).notNull(), // "endoflife.date" | "not-covered"
  fetchedAt: ts("fetched_at"),
}, (t) => ({
  fetchedAtIdx: index("eol_feed_cache_fetched_at_idx").on(t.fetchedAt.desc()),
}));

// ============================================================
// Insert schemas (for runtime Zod validation in routes)
// ============================================================
export const insertXborderTransferSchema = createInsertSchema(xborderTransfers).omit({ id: true, transferRef: true, loggedAt: true });
export type InsertXborderTransfer = z.infer<typeof insertXborderTransferSchema>;
export type XborderTransfer = typeof xborderTransfers.$inferSelect;

export const insertConsentEventSchema = createInsertSchema(consentEvents).omit({ id: true, eventRef: true, evidenceHash: true, capturedAt: true });
export type InsertConsentEvent = z.infer<typeof insertConsentEventSchema>;
export type ConsentEvent = typeof consentEvents.$inferSelect;

export const insertSodRuleSchema = createInsertSchema(sodRules).omit({ id: true, updatedAt: true });
export type InsertSodRule = z.infer<typeof insertSodRuleSchema>;
export type SodRule = typeof sodRules.$inferSelect;

export const insertVendorRegisterSchema = createInsertSchema(vendorRegister).omit({ id: true, vendorRef: true, registeredAt: true });
export type InsertVendorRegister = z.infer<typeof insertVendorRegisterSchema>;
export type VendorRegisterRow = typeof vendorRegister.$inferSelect;

export const insertVendorAttestationSchema = createInsertSchema(vendorAttestations).omit({ id: true, attestationRef: true, evidenceHash: true, signatureHex: true, uploadedAt: true });
export type InsertVendorAttestation = z.infer<typeof insertVendorAttestationSchema>;
export type VendorAttestationRow = typeof vendorAttestations.$inferSelect;

export const insertMttrRecordSchema = createInsertSchema(mttrRecords).omit({ id: true, recordRef: true, mttdSeconds: true, mttrSeconds: true, recordedAt: true });
export type InsertMttrRecord = z.infer<typeof insertMttrRecordSchema>;
export type MttrRecord = typeof mttrRecords.$inferSelect;

// ---------- WAVE-13 EXTENSIONS — Smoke runs + Chaos drills ----------

// Master Pilot-Smoke Run — orchestrates all 14 modules in one signed bundle
export const wave13SmokeRuns = pgTable("wave13_smoke_runs", {
  id: idCol(),
  smokeRef: varchar("smoke_ref", { length: 64 }).notNull().unique("wave13_smoke_runs_smoke_ref_key"),
  modulesAttempted: integer("modules_attempted").notNull().default(0),
  modulesPassed: integer("modules_passed").notNull().default(0),
  modulesFailed: integer("modules_failed").notNull().default(0),
  modulesErrored: integer("modules_errored").notNull().default(0),
  durationMs: integer("duration_ms").notNull().default(0),
  passed: integer("passed").notNull().default(0),
  bundleHash: varchar("bundle_hash", { length: 128 }).notNull(),
  signatureHex: varchar("signature_hex", { length: 256 }).notNull(),
  detailsJson: jsonb("details_json").notNull().default({}),
  // FU-001 option (c) — defence-in-depth hardening landed 2026-05-02.
  // Stores the EXACT canonical bytes that runWave13PilotSmoke fed into HMAC.
  // Nullable so pre-(c) rows continue to round-trip via the reconstruction
  // path in dumpReceipts. New rows get this populated; the dumper prefers
  // it when present, eliminating reconstruction shape drift entirely.
  signedPayload: text("signed_payload"),
  triggeredBy: varchar("triggered_by", { length: 128 }).notNull(),
  triggeredAt: ts("triggered_at"),
}, (t) => ({
  triggeredAtIdx: index("wave13_smoke_runs_triggered_at_idx").on(t.triggeredAt.desc()),
}));
export type Wave13SmokeRun = typeof wave13SmokeRuns.$inferSelect;

// Chaos drill #1 — simulated network partition / split-brain
export const networkPartitionDrills = pgTable("network_partition_drills", {
  id: idCol(),
  drillRef: varchar("drill_ref", { length: 64 }).notNull().unique("network_partition_drills_drill_ref_key"),
  partitionType: varchar("partition_type", { length: 32 }).notNull().default("split-brain"),
  affectedZones: integer("affected_zones").notNull().default(0),
  totalZones: integer("total_zones").notNull().default(0),
  quorumMaintained: integer("quorum_maintained").notNull().default(0),
  detectMs: integer("detect_ms").notNull().default(0),
  recoverMs: integer("recover_ms").notNull().default(0),
  staleReadsBlocked: integer("stale_reads_blocked").notNull().default(0),
  receiptHash: varchar("receipt_hash", { length: 128 }).notNull(),
  signatureHex: varchar("signature_hex", { length: 256 }).notNull(),
  passed: integer("passed").notNull().default(0),
  detailsJson: jsonb("details_json").notNull().default({}),
  triggeredBy: varchar("triggered_by", { length: 128 }).notNull(),
  triggeredAt: ts("triggered_at"),
}, (t) => ({
  triggeredAtIdx: index("network_partition_drills_triggered_at_idx").on(t.triggeredAt.desc()),
}));
export type NetworkPartitionDrill = typeof networkPartitionDrills.$inferSelect;

// Chaos drill #2 — DB primary failover
export const dbFailoverDrills = pgTable("db_failover_drills", {
  id: idCol(),
  drillRef: varchar("drill_ref", { length: 64 }).notNull().unique("db_failover_drills_drill_ref_key"),
  primaryRegion: varchar("primary_region", { length: 32 }).notNull().default("UG-CENTRAL"),
  failoverRegion: varchar("failover_region", { length: 32 }).notNull().default("UG-EAST"),
  rtoSeconds: integer("rto_seconds").notNull().default(0),
  rpoSeconds: integer("rpo_seconds").notNull().default(0),
  rtoTargetSeconds: integer("rto_target_seconds").notNull().default(60),
  rpoTargetSeconds: integer("rpo_target_seconds").notNull().default(5),
  inflightTxnsLost: integer("inflight_txns_lost").notNull().default(0),
  inflightTxnsReplayed: integer("inflight_txns_replayed").notNull().default(0),
  receiptHash: varchar("receipt_hash", { length: 128 }).notNull(),
  signatureHex: varchar("signature_hex", { length: 256 }).notNull(),
  passed: integer("passed").notNull().default(0),
  detailsJson: jsonb("details_json").notNull().default({}),
  triggeredBy: varchar("triggered_by", { length: 128 }).notNull(),
  triggeredAt: ts("triggered_at"),
}, (t) => ({
  triggeredAtIdx: index("db_failover_drills_triggered_at_idx").on(t.triggeredAt.desc()),
}));
export type DbFailoverDrill = typeof dbFailoverDrills.$inferSelect;

// Chaos drill #3 — message-queue back-pressure
export const queueBackpressureDrills = pgTable("queue_backpressure_drills", {
  id: idCol(),
  drillRef: varchar("drill_ref", { length: 64 }).notNull().unique("queue_backpressure_drills_drill_ref_key"),
  queueName: varchar("queue_name", { length: 96 }).notNull().default("threat-events"),
  injectedRatePerSec: integer("injected_rate_per_sec").notNull().default(0),
  consumerRatePerSec: integer("consumer_rate_per_sec").notNull().default(0),
  peakDepth: integer("peak_depth").notNull().default(0),
  shedCount: integer("shed_count").notNull().default(0),
  deadletterCount: integer("deadletter_count").notNull().default(0),
  durationMs: integer("duration_ms").notNull().default(0),
  receiptHash: varchar("receipt_hash", { length: 128 }).notNull(),
  signatureHex: varchar("signature_hex", { length: 256 }).notNull(),
  passed: integer("passed").notNull().default(0),
  detailsJson: jsonb("details_json").notNull().default({}),
  triggeredBy: varchar("triggered_by", { length: 128 }).notNull(),
  triggeredAt: ts("triggered_at"),
}, (t) => ({
  triggeredAtIdx: index("queue_backpressure_drills_triggered_at_idx").on(t.triggeredAt.desc()),
}));
export type QueueBackpressureDrill = typeof queueBackpressureDrills.$inferSelect;

// ---------- WAVE-13 EXPECTED-FINDINGS REGISTER (FU-003-sanctioned hardening) ----------
//
// Per FU-003 "What IS legitimate to do" carve-out (permanent design note):
//   "Build a separate, explicit 'expected findings' register that the dashboard
//    cross-references. A finding present in the register is shown as
//    `expected-fail` rather than `pass`, NEVER as `pass`. This preserves the
//    detection signal while making it possible for an operator to acknowledge
//    known-and-accepted findings. That is hardening; muting is not."
//
// Forbidden uses (per FU-003): do NOT use this register to widen thresholds,
// exclude EOL components, seed-away role conflicts, gate modules out of the
// rolling pass-rate window, or back-date receipts. Underlying FAIL receipts
// must continue to land; only the dashboard interpretation layer changes.
//
// Seed at boot: server/lib/wave13-expected-findings.ts populates the three
// FU-003 modules (sod-checker, eol-inventory, model-card-audit) idempotently
// via ON CONFLICT DO NOTHING on module_name. Future operator-driven additions
// follow the same pattern with acknowledgedBy = actor user id.
export const wave13ExpectedFindings = pgTable("wave13_expected_findings", {
  id: idCol(),
  moduleName: varchar("module_name", { length: 64 }).notNull().unique("wave13_expected_findings_module_name_key"),
  rationale: text("rationale").notNull(),
  rationalePointer: varchar("rationale_pointer", { length: 64 }).notNull(),
  bouNarrativeRef: varchar("bou_narrative_ref", { length: 128 }),
  acknowledgedBy: varchar("acknowledged_by", { length: 128 }).notNull(),
  acknowledgedAt: ts("acknowledged_at"),
}, (t) => ({
  moduleNameIdx: index("wave13_expected_findings_module_name_idx").on(t.moduleName),
}));
export type Wave13ExpectedFinding = typeof wave13ExpectedFindings.$inferSelect;
export const insertWave13ExpectedFindingSchema = createInsertSchema(wave13ExpectedFindings).omit({ id: true, acknowledgedAt: true });
export type InsertWave13ExpectedFinding = z.infer<typeof insertWave13ExpectedFindingSchema>;