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

// 1. Regulator Observer Mode
export const regulatorObserverCredentials = pgTable("regulator_observer_credentials", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  regulatorName: text("regulator_name").notNull(),
  agencyCode: text("agency_code").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 regulatorObserverReplays = pgTable("regulator_observer_replays", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  credentialId: varchar("credential_id").notNull(),
  resourceType: text("resource_type").notNull(),
  resourceRef: text("resource_ref").notNull(),
  examinerNote: text("examiner_note"),
  ipAddress: text("ip_address"),
  viewedAt: timestamp("viewed_at").notNull().default(sql`NOW()`),
});

// 2. BoU-Aligned Reporting Pack
export const bouRegulatoryReports = pgTable("bou_regulatory_reports", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  reportCode: text("report_code").notNull(),
  periodStart: timestamp("period_start").notNull(),
  periodEnd: timestamp("period_end").notNull(),
  payloadJson: text("payload_json").notNull(),
  signedHashHex: text("signed_hash_hex").notNull(),
  status: text("status").notNull().default("draft"),
  generatedAt: timestamp("generated_at").notNull().default(sql`NOW()`),
  generatedBy: text("generated_by").notNull(),
  submittedAt: timestamp("submitted_at"),
  submittedRef: text("submitted_ref"),
});

// 3. Sanctions & PEP Screening
export const sanctionsLists = pgTable("sanctions_lists", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  listCode: text("list_code").notNull(),
  source: text("source").notNull(),
  version: text("version").notNull(),
  listHashHex: text("list_hash_hex").notNull(),
  recordCount: integer("record_count").notNull(),
  refreshedAt: timestamp("refreshed_at").notNull().default(sql`NOW()`),
});

export const sanctionsScreeningTests = pgTable("sanctions_screening_tests", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  listsCheckedJson: text("lists_checked_json").notNull(),
  totalSyntheticTargets: integer("total_synthetic_targets").notNull(),
  truePositives: integer("true_positives").notNull(),
  falseNegatives: integer("false_negatives").notNull(),
  recallPct: integer("recall_pct").notNull(),
  passed: integer("passed").notNull(),
  durationMs: integer("duration_ms").notNull(),
  ranAt: timestamp("ran_at").notNull().default(sql`NOW()`),
  ranBy: text("ran_by").notNull(),
});

// 4. Stress Testing
export const stressTestRuns = pgTable("stress_test_runs", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  scenarioCode: text("scenario_code").notNull(),
  scenarioName: text("scenario_name").notNull(),
  parametersJson: text("parameters_json").notNull(),
  impactScore: integer("impact_score").notNull(),
  capitalImpactPct: integer("capital_impact_pct").notNull(),
  liquidityImpactPct: integer("liquidity_impact_pct").notNull(),
  rtoMs: integer("rto_ms").notNull(),
  verdict: text("verdict").notNull(),
  evidenceHashHex: text("evidence_hash_hex").notNull(),
  ranAt: timestamp("ran_at").notNull().default(sql`NOW()`),
  ranBy: text("ran_by").notNull(),
});

// 5. Insider Threat Scenario Pack
export const insiderThreatRuns = pgTable("insider_threat_runs", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  scenarioCode: text("scenario_code").notNull(),
  scenarioName: text("scenario_name").notNull(),
  totalActions: integer("total_actions").notNull(),
  detectedActions: integer("detected_actions").notNull(),
  falseNegatives: integer("false_negatives").notNull(),
  recallPct: integer("recall_pct").notNull(),
  alertLatencyMs: integer("alert_latency_ms").notNull(),
  passed: integer("passed").notNull(),
  detailsJson: text("details_json").notNull(),
  ranAt: timestamp("ran_at").notNull().default(sql`NOW()`),
  ranBy: text("ran_by").notNull(),
});

// 6. DR Failover Timed Drill
export const drFailoverDrills = pgTable("dr_failover_drills", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  drillRef: text("drill_ref").notNull(),
  regionFrom: text("region_from").notNull(),
  regionTo: text("region_to").notNull(),
  rpoSeconds: integer("rpo_seconds").notNull(),
  rtoSeconds: integer("rto_seconds").notNull(),
  dnsSwitchSeconds: integer("dns_switch_seconds").notNull(),
  healthChecksPassed: integer("health_checks_passed").notNull(),
  totalChecks: integer("total_checks").notNull(),
  verdict: text("verdict").notNull(),
  evidenceHashHex: text("evidence_hash_hex").notNull(),
  ranAt: timestamp("ran_at").notNull().default(sql`NOW()`),
  ranBy: text("ran_by").notNull(),
});

// 7. Code Provenance Portal
export const codeProvenanceRecords = pgTable("code_provenance_records", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  deploymentRef: text("deployment_ref").notNull(),
  gitSha: text("git_sha").notNull(),
  buildManifestHashHex: text("build_manifest_hash_hex").notNull(),
  sbomHashHex: text("sbom_hash_hex").notNull(),
  signedManifestHex: text("signed_manifest_hex").notNull(),
  approversJson: text("approvers_json").notNull(),
  environment: text("environment").notNull(),
  deployedAt: timestamp("deployed_at").notNull().default(sql`NOW()`),
  deployedBy: text("deployed_by").notNull(),
});

// 8. Bug Bounty Rewards
export const bugBountyRewards = pgTable("bug_bounty_rewards", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  vdpReportId: varchar("vdp_report_id").notNull(),
  tier: text("tier").notNull(),
  ugxAmount: bigint("ugx_amount", { mode: "number" }).notNull(),
  awardedAt: timestamp("awarded_at").notNull().default(sql`NOW()`),
  awardedBy: text("awarded_by").notNull(),
  hallOfFameHandle: text("hall_of_fame_handle"),
  publicNote: text("public_note"),
});

// 9. Customer Protection Hub
export const customerComplaints = pgTable("customer_complaints", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  tenantId: text("tenant_id"), // AS/CYBER/CLIENTDATA-ISOLATION Batch 2-3 Pack 1 — nullable Phase-1; RLS via TENANT_TABLES entry; boot DDL backfills then SET NOT NULL
  complaintRef: text("complaint_ref").notNull().unique(),
  customerRef: text("customer_ref").notNull(),
  category: text("category").notNull(),
  description: text("description").notNull(),
  slaHours: integer("sla_hours").notNull().default(72),
  resolution: text("resolution"),
  resolvedAt: timestamp("resolved_at"),
  withinSla: integer("within_sla"),
  escalated: integer("escalated").notNull().default(0),
  openedAt: timestamp("opened_at").notNull().default(sql`NOW()`),
}).enableRLS();

export const feeDisclosures = pgTable("fee_disclosures", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  productCode: text("product_code").notNull().unique(),
  productName: text("product_name").notNull(),
  feeJson: text("fee_json").notNull(),
  effectiveFrom: timestamp("effective_from").notNull(),
  lastUpdatedAt: timestamp("last_updated_at").notNull().default(sql`NOW()`),
  lastUpdatedBy: text("last_updated_by").notNull(),
});

// 10. SWIFT Integrity
export const swiftIntegrityChecks = pgTable("swift_integrity_checks", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  tenantId: text("tenant_id"), // AS/CYBER/CLIENTDATA-ISOLATION Batch 2-3 Pack 1 — nullable Phase-1; RLS via TENANT_TABLES entry; boot DDL backfills then SET NOT NULL
  messageRef: text("message_ref").notNull(),
  messageType: text("message_type").notNull(),
  bicSender: text("bic_sender").notNull(),
  bicReceiver: text("bic_receiver").notNull(),
  amount: bigint("amount", { mode: "number" }).notNull(),
  currency: text("currency").notNull(),
  messageHashHex: text("message_hash_hex").notNull(),
  signatureValid: integer("signature_valid").notNull(),
  cbprCompliant: integer("cbpr_compliant").notNull(),
  fxThresholdBreached: integer("fx_threshold_breached").notNull(),
  notes: text("notes"),
  checkedAt: timestamp("checked_at").notNull().default(sql`NOW()`),
}).enableRLS();

// 12. Mock Supervisory Examination
export const mockSupervisoryExams = pgTable("mock_supervisory_exams", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  examRef: text("exam_ref").notNull().unique(),
  examType: text("exam_type").notNull(),
  durationDays: integer("duration_days").notNull(),
  documentRequestsTotal: integer("document_requests_total").notNull(),
  documentRequestsAnswered: integer("document_requests_answered").notNull(),
  walkthroughsTotal: integer("walkthroughs_total").notNull(),
  walkthroughsCompleted: integer("walkthroughs_completed").notNull(),
  samplesTotal: integer("samples_total").notNull(),
  samplesProvided: integer("samples_provided").notNull(),
  findingsCount: integer("findings_count").notNull(),
  readinessScore: integer("readiness_score").notNull(),
  detailsJson: text("details_json").notNull(),
  ranAt: timestamp("ran_at").notNull().default(sql`NOW()`),
  ranBy: text("ran_by").notNull(),
});

// 13. Privacy Auto-Deletion Drill
export const privacyDeletionDrills = pgTable("privacy_deletion_drills", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  drillRef: text("drill_ref").notNull().unique(),
  retentionPolicyCode: text("retention_policy_code").notNull(),
  recordsTargeted: integer("records_targeted").notNull(),
  recordsDeleted: integer("records_deleted").notNull(),
  deletionProofHashHex: text("deletion_proof_hash_hex").notNull(),
  durationMs: integer("duration_ms").notNull(),
  ranAt: timestamp("ran_at").notNull().default(sql`NOW()`),
  ranBy: text("ran_by").notNull(),
});

// 14. Open Banking Pre-Readiness
export const openBankingConsents = pgTable("open_banking_consents", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  tenantId: text("tenant_id"), // AS/CYBER/CLIENTDATA-ISOLATION Batch 2-3 Pack 1 — nullable Phase-1; RLS via TENANT_TABLES entry; boot DDL backfills then SET NOT NULL
  consentRef: text("consent_ref").notNull().unique(),
  customerRef: text("customer_ref").notNull(),
  tppName: text("tpp_name").notNull(),
  dataCategories: text("data_categories").array().notNull(),
  scopes: text("scopes").array().notNull(),
  grantedAt: timestamp("granted_at").notNull().default(sql`NOW()`),
  expiresAt: timestamp("expires_at").notNull(),
  revokedAt: timestamp("revoked_at"),
  status: text("status").notNull().default("active"),
}).enableRLS();