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

// FU-015 / P4.2 Phase 1 (2026-05-24) — UBO erasure state + trigger enums.
// pgEnum (vs string-typed text column) chosen per architect review MEDIUM
// finding: DB-level enforcement guarantees that out-of-band UPDATEs (psql
// runbooks, ad-hoc queries) cannot insert garbage states that bypass TypeScript
// safety. Matches the precedent of userRoleEnum/riskLevelEnum/threatTypeEnum
// in shared/schema.ts. Adding a new lifecycle state in future requires a
// migration; that ceremony is the point — the type system + DB cooperate to
// prevent silent state-machine drift.
export const uboErasureStateEnum = pgEnum("ubo_erasure_state", ["ACTIVE", "SUPERSEDED", "ERASED"]);
export const uboErasureTriggerEnum = pgEnum("ubo_erasure_trigger", ["corporate", "departure", "individual_pending", "regulator_order"]);

// 1. Live Synthetic Canary Suite — every-N-minute end-to-end synthetic txn probe
export const syntheticCanaryRuns = pgTable("synthetic_canary_runs", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  probeCode: text("probe_code").notNull(),
  txnLatencyMs: integer("txn_latency_ms").notNull(),
  kytLatencyMs: integer("kyt_latency_ms").notNull(),
  aiScoringLatencyMs: integer("ai_scoring_latency_ms").notNull(),
  auditWriteLatencyMs: integer("audit_write_latency_ms").notNull(),
  totalLatencyMs: integer("total_latency_ms").notNull(),
  passed: integer("passed").notNull(),
  failureReason: text("failure_reason"),
  ranAt: timestamp("ran_at").notNull().default(sql`NOW()`),
});

// 2. Cash Transaction Reports (CTR) — BoU/FIA mandatory ≥ UGX 20M
export const ctrFilings = pgTable("ctr_filings", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  ctrRef: text("ctr_ref").notNull().unique(),
  customerRef: text("customer_ref").notNull(),
  // AS/CYBER/CLIENTDATA-ISOLATION Batch 1b — tenant isolation (nullable Phase-1; SET NOT NULL deferred to Phase-2).
  tenantId: text("tenant_id"),
  txnType: text("txn_type").notNull(),
  amountUgx: bigint("amount_ugx", { mode: "number" }).notNull(),
  thresholdUgx: bigint("threshold_ugx", { mode: "number" }).notNull().default(20000000),
  branchCode: text("branch_code").notNull(),
  txnAt: timestamp("txn_at").notNull(),
  filedAt: timestamp("filed_at"),
  filedBy: text("filed_by"),
  fiaConfirmationRef: text("fia_confirmation_ref"),
  slaHours: integer("sla_hours").notNull().default(168),
  withinSla: integer("within_sla").notNull().default(1),
  status: text("status").notNull().default("pending"),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
}).enableRLS();

// 3. Beneficial Ownership (UBO) Registry — corporate customer ≥25% owners
//
// FU-015 / P4.2 Phase 1 (2026-05-24): seven new columns added to support the
// UBO-aware erasure path per docs/regulatory/UBO_ERASURE_DESIGN_PROPOSAL.md.
// The four eligibility triggers (corporate-erasure / threshold-departure /
// individual-PDPO-request / regulator-directed) all transition state through
// these columns. Erasure_state lifecycle: ACTIVE -> {SUPERSEDED | ERASED}.
// PII fields (owner_name, owner_national_id, owner_nationality) remain in
// schema but are mutated/nulled on maturation; structural fields (corporate_*,
// ownership_pct, control_type, registered_at) are preserved per §4 of proposal.
export const uboRegistrations = pgTable("ubo_registrations", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  uboRef: text("ubo_ref").notNull().unique(),
  corporateCustomerRef: text("corporate_customer_ref").notNull(),
  corporateName: text("corporate_name").notNull(),
  ownerName: text("owner_name").notNull(),
  ownerNationalId: text("owner_national_id").notNull(),
  ownerNationality: text("owner_nationality").notNull(),
  ownershipPct: integer("ownership_pct").notNull(),
  controlType: text("control_type").notNull(),
  pepStatus: integer("pep_status").notNull().default(0),
  sanctionsStatus: integer("sanctions_status").notNull().default(0),
  verifiedAt: timestamp("verified_at"),
  registeredAt: timestamp("registered_at").notNull().default(sql`NOW()`),
  registeredBy: text("registered_by").notNull(),
  // --- FU-015 Phase 1 erasure-path columns (2026-05-24) ---
  // Multi-tenant isolation — nullable to match precedent of audit_logs /
  // backup_records / similar PII-adjacent tables (architect-review HIGH
  // finding, fixed in-Phase-1 to avoid downstream migration friction).
  tenantId: text("tenant_id"),
  // Lifecycle: 'ACTIVE' (default) | 'SUPERSEDED' (trigger 3.2) | 'ERASED' (trigger 3.1/3.4/post-3.3-corporate-followup)
  erasureState: uboErasureStateEnum("erasure_state").notNull().default("ACTIVE"),
  // Trigger 3.2: timestamp when the natural person dropped below threshold
  supersededAt: timestamp("superseded_at"),
  // Trigger 3.2: ubo_ref of replacement UBO record (null if no replacement)
  supersededBy: text("superseded_by"),
  // Trigger 3.1/3.4: timestamp when PII fields were nulled/hashed
  erasureMaturedAt: timestamp("erasure_matured_at"),
  // Which of the four triggers produced the current erasure_state
  erasureTrigger: uboErasureTriggerEnum("erasure_trigger"),
  // audit_logs.id of the erasure event (cross-ref for forensic reconstruction)
  erasureAuditRef: text("erasure_audit_ref"),
  // Trigger 3.3: dsar_request_id that flagged this row for individual-erasure-pending
  // state. Row PII is NOT mutated on this trigger — it sits until corporate triggers 3.1.
  individualErasurePending: text("individual_erasure_pending"),
  // T004a Tier D (ii): HMAC lookup key for owner_national_id equality lookups.
  // Derived via computeLookupHash(plaintext, tenantId, "ubo_registrations", "owner_national_id").
  // Nullable — existing rows populated by T004b migration; new rows populated at insert.
  // Not cleared on erasure — HMAC is not PII (cannot be reversed without the master key);
  // persisting it allows the flagUboForIndividualErasure erased-row path to locate matured rows.
  ownerNationalIdHmac: text("owner_national_id_hmac"),
}, (t) => ({
  // DSAR worker per-tick scan finds ACTIVE rows efficiently
  erasureStateIdx: index("ubo_registrations_erasure_state_idx").on(t.erasureState, t.registeredAt.desc()),
  // Per-tenant uniqueness enforced via the HMAC itself (tenant-keyed derivation) + this index.
  // Nullable entries (legacy rows pre-T004b) are excluded from the unique constraint by PostgreSQL
  // semantics (multiple NULLs permitted in a unique index).
  ownerNationalIdHmacUniq: uniqueIndex("ubo_owner_national_id_hmac_unique").on(t.ownerNationalIdHmac),
})).enableRLS();

export const insertUboRegistrationSchema = createInsertSchema(uboRegistrations).omit({
  id: true,
  registeredAt: true,
  erasureState: true,
  supersededAt: true,
  supersededBy: true,
  erasureMaturedAt: true,
  erasureTrigger: true,
  erasureAuditRef: true,
  individualErasurePending: true,
  ownerNationalIdHmac: true,   // T004a (ii) — computed by write path, not user-supplied
});
export type InsertUboRegistration = z.infer<typeof insertUboRegistrationSchema>;
export type UboRegistration = typeof uboRegistrations.$inferSelect;
// Convenience union types — narrowed from the pgEnum values for use in
// IStorage method signatures. Source of truth is the pgEnum above; these
// are derived for ergonomic typing of partial-update bags.
export type UboErasureState = (typeof uboErasureStateEnum.enumValues)[number];
export type UboErasureTrigger = (typeof uboErasureTriggerEnum.enumValues)[number];

// 4. Regulator Push-Notification Hook — outbound webhooks to BoU/FIA inboxes
export const regulatorPushEndpoints = pgTable("regulator_push_endpoints", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  endpointCode: text("endpoint_code").notNull().unique(),
  agencyCode: text("agency_code").notNull(),
  webhookUrl: text("webhook_url").notNull(),
  signingSecretHash: text("signing_secret_hash").notNull(),
  enabled: integer("enabled").notNull().default(1),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
  createdBy: text("created_by").notNull(),
});

export const regulatorPushDeliveries = pgTable("regulator_push_deliveries", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  endpointId: varchar("endpoint_id").notNull(),
  alertCode: text("alert_code").notNull(),
  severity: text("severity").notNull(),
  payloadJson: text("payload_json").notNull(),
  signatureHex: text("signature_hex").notNull(),
  attempts: integer("attempts").notNull().default(0),
  status: text("status").notNull().default("queued"),
  lastError: text("last_error"),
  deliveredAt: timestamp("delivered_at"),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
});

// 5. Independent Auditor Read-Only Mode (E&Y/KPMG)
export const auditorCredentials = pgTable("auditor_credentials", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  firmName: text("firm_name").notNull(),
  auditorEmail: text("auditor_email").notNull(),
  scopes: text("scopes").array().notNull(),
  tokenHash: text("token_hash").notNull(),
  expiresAt: timestamp("expires_at").notNull(),
  createdBy: text("created_by").notNull(),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
  lastUsedAt: timestamp("last_used_at"),
  revokedAt: timestamp("revoked_at"),
});

export const auditorAccessLogs = pgTable("auditor_access_logs", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  credentialId: varchar("credential_id").notNull(),
  resourceType: text("resource_type").notNull(),
  resourceRef: text("resource_ref").notNull(),
  ipAddress: text("ip_address"),
  accessedAt: timestamp("accessed_at").notNull().default(sql`NOW()`),
});

// 6. Phishing Simulation + Click-Rate
export const phishingCampaigns = pgTable("phishing_campaigns", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  campaignCode: text("campaign_code").notNull().unique(),
  campaignName: text("campaign_name").notNull(),
  targetGroup: text("target_group").notNull(),
  templateType: text("template_type").notNull(),
  totalTargets: integer("total_targets").notNull(),
  delivered: integer("delivered").notNull().default(0),
  opened: integer("opened").notNull().default(0),
  clicked: integer("clicked").notNull().default(0),
  reported: integer("reported").notNull().default(0),
  clickRatePct: integer("click_rate_pct").notNull().default(0),
  reportRatePct: integer("report_rate_pct").notNull().default(0),
  trainingAssigned: integer("training_assigned").notNull().default(0),
  ranAt: timestamp("ran_at").notNull().default(sql`NOW()`),
  ranBy: text("ran_by").notNull(),
});

// 7. Ransomware Refusal Drill — proves "no ransom" policy + recovery vault test
export const ransomwareDrills = pgTable("ransomware_drills", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  drillRef: text("drill_ref").notNull().unique(),
  scenarioCode: text("scenario_code").notNull(),
  ransomDemandUgx: bigint("ransom_demand_ugx", { mode: "number" }).notNull(),
  refusalLogged: integer("refusal_logged").notNull().default(1),
  vaultRecoveryAttempted: integer("vault_recovery_attempted").notNull().default(0),
  vaultRecoverySuccess: integer("vault_recovery_success").notNull().default(0),
  recoveryRpoMin: integer("recovery_rpo_min").notNull(),
  recoveryRtoMin: integer("recovery_rto_min").notNull(),
  evidenceHashHex: text("evidence_hash_hex").notNull(),
  verdict: text("verdict").notNull(),
  ranAt: timestamp("ran_at").notNull().default(sql`NOW()`),
  ranBy: text("ran_by").notNull(),
});

// 8. Network Segmentation Proof
export const segmentationTests = pgTable("segmentation_tests", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  testRef: text("test_ref").notNull().unique(),
  sourceSegment: text("source_segment").notNull(),
  targetSegment: text("target_segment").notNull(),
  expectedReachable: integer("expected_reachable").notNull(),
  actualReachable: integer("actual_reachable").notNull(),
  passed: integer("passed").notNull(),
  evidenceJson: text("evidence_json").notNull(),
  ranAt: timestamp("ran_at").notNull().default(sql`NOW()`),
  ranBy: text("ran_by").notNull(),
});

// 9. OS-Level Vulnerability Scanner
export const osVulnScans = pgTable("os_vuln_scans", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  scanRef: text("scan_ref").notNull().unique(),
  imageRef: text("image_ref").notNull(),
  totalPackages: integer("total_packages").notNull(),
  vulnsCritical: integer("vulns_critical").notNull().default(0),
  vulnsHigh: integer("vulns_high").notNull().default(0),
  vulnsMedium: integer("vulns_medium").notNull().default(0),
  vulnsLow: integer("vulns_low").notNull().default(0),
  topCveJson: text("top_cve_json").notNull(),
  passed: integer("passed").notNull(),
  ranAt: timestamp("ran_at").notNull().default(sql`NOW()`),
  ranBy: text("ran_by").notNull(),
});

// 10. Performance & Accessibility Budget — Core Web Vitals + WCAG
export const performanceBudgets = pgTable("performance_budgets", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  budgetRef: text("budget_ref").notNull().unique(),
  pageRoute: text("page_route").notNull(),
  lcpMs: integer("lcp_ms").notNull(),
  inpMs: integer("inp_ms").notNull(),
  clsScore: integer("cls_score").notNull(),
  wcagViolations: integer("wcag_violations").notNull(),
  wcagLevel: text("wcag_level").notNull(),
  passed: integer("passed").notNull(),
  ranAt: timestamp("ran_at").notNull().default(sql`NOW()`),
  ranBy: text("ran_by").notNull(),
});

// 11. Quarterly Board Report Generator
export const boardReports = pgTable("board_reports", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  reportRef: text("report_ref").notNull().unique(),
  quarterCode: text("quarter_code").notNull(),
  totalIncidents: integer("total_incidents").notNull(),
  criticalIncidents: integer("critical_incidents").notNull(),
  aiDecisionsOverridden: integer("ai_decisions_overridden").notNull(),
  regulatorInteractions: integer("regulator_interactions").notNull(),
  uptimePctMicros: integer("uptime_pct_micros").notNull(),
  payloadJson: text("payload_json").notNull(),
  signedHashHex: text("signed_hash_hex").notNull(),
  generatedAt: timestamp("generated_at").notNull().default(sql`NOW()`),
  generatedBy: text("generated_by").notNull(),
});

// 12. Public Trust Dashboard snapshot — daily snapshot of customer-facing KPIs
export const trustDashboardSnapshots = pgTable("trust_dashboard_snapshots", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  snapshotRef: text("snapshot_ref").notNull().unique(),
  uptimePct: integer("uptime_pct").notNull(),
  meanBreachResponseMin: integer("mean_breach_response_min").notNull(),
  aiFairnessScore: integer("ai_fairness_score").notNull(),
  sanctionsHitRatePct: integer("sanctions_hit_rate_pct").notNull(),
  customerComplaintsResolved: integer("customer_complaints_resolved").notNull(),
  capturedAt: timestamp("captured_at").notNull().default(sql`NOW()`),
});

// 13. NPS Act 2020 Settlement Window Monitor — T+0/T+1 mobile-money settlements
export const npsSettlementWindows = pgTable("nps_settlement_windows", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  windowRef: text("window_ref").notNull().unique(),
  windowDate: timestamp("window_date").notNull(),
  expectedSettlementSec: integer("expected_settlement_sec").notNull(),
  actualSettlementSec: integer("actual_settlement_sec").notNull(),
  totalTxnCount: integer("total_txn_count").notNull(),
  totalAmountUgx: bigint("total_amount_ugx", { mode: "number" }).notNull(),
  withinSla: integer("within_sla").notNull(),
  bouRefSection: text("bou_ref_section").notNull(),
  capturedAt: timestamp("captured_at").notNull().default(sql`NOW()`),
});

// 14. Tabletop Exercise Scorer — incident-response drills (BoU expects 2/year)
export const tabletopExercises = pgTable("tabletop_exercises", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  exerciseRef: text("exercise_ref").notNull().unique(),
  scenarioCode: text("scenario_code").notNull(),
  scenarioName: text("scenario_name").notNull(),
  participantsJson: text("participants_json").notNull(),
  detectionScore: integer("detection_score").notNull(),
  responseScore: integer("response_score").notNull(),
  recoveryScore: integer("recovery_score").notNull(),
  communicationScore: integer("communication_score").notNull(),
  overallScore: integer("overall_score").notNull(),
  outcomesJson: text("outcomes_json").notNull(),
  facilitator: text("facilitator").notNull(),
  conductedAt: timestamp("conducted_at").notNull().default(sql`NOW()`),
});