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

// ===== 1. End-to-End Test Harness (Playwright-style scenarios) =====
export const e2eTestRuns = pgTable("e2e_test_runs", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  runRef: text("run_ref").notNull().unique(),
  scenarioCode: text("scenario_code").notNull(),
  scenarioName: text("scenario_name").notNull(),
  totalSteps: integer("total_steps").notNull(),
  passedSteps: integer("passed_steps").notNull(),
  failedSteps: integer("failed_steps").notNull(),
  durationMs: integer("duration_ms").notNull(),
  passed: integer("passed").notNull(),
  stepsJson: text("steps_json").notNull(),
  evidenceHash: text("evidence_hash").notNull(),
  ranBy: text("ran_by").notNull(),
  ranAt: timestamp("ran_at").notNull().default(sql`NOW()`),
});

// ===== 2. Load / Soak Test Runner =====
export const wave8LoadTestRuns = pgTable("wave8_load_test_runs", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  runRef: text("run_ref").notNull().unique(),
  targetEndpoint: text("target_endpoint").notNull(),
  concurrency: integer("concurrency").notNull(),
  durationSec: integer("duration_sec").notNull(),
  totalRequests: integer("total_requests").notNull(),
  successCount: integer("success_count").notNull(),
  errorCount: integer("error_count").notNull(),
  p50LatencyMs: integer("p50_latency_ms").notNull(),
  p95LatencyMs: integer("p95_latency_ms").notNull(),
  p99LatencyMs: integer("p99_latency_ms").notNull(),
  maxLatencyMs: integer("max_latency_ms").notNull(),
  throughputRps: integer("throughput_rps").notNull(),
  passed: integer("passed").notNull(),
  sloP95Ms: integer("slo_p95_ms").notNull().default(200),
  ranBy: text("ran_by").notNull(),
  ranAt: timestamp("ran_at").notNull().default(sql`NOW()`),
});

// ===== 3. Security Scan Results (headers, ZAP-style baseline) =====
export const securityScans = pgTable("security_scans", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  scanRef: text("scan_ref").notNull().unique(),
  scanType: text("scan_type").notNull(), // "headers", "zap-baseline", "csp-eval"
  targetUrl: text("target_url").notNull(),
  gradeLetter: text("grade_letter").notNull(), // A+/A/B/C/D/F
  scoreOf100: integer("score_of_100").notNull(),
  highFindings: integer("high_findings").notNull().default(0),
  mediumFindings: integer("medium_findings").notNull().default(0),
  lowFindings: integer("low_findings").notNull().default(0),
  findingsJson: text("findings_json").notNull(),
  ranBy: text("ran_by").notNull(),
  ranAt: timestamp("ran_at").notNull().default(sql`NOW()`),
});

// ===== 4. Regulator Demo Pack — one-click signed evidence bundle =====
export const demoPackGenerations = pgTable("demo_pack_generations", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  packRef: text("pack_ref").notNull().unique(),
  modulesIncluded: integer("modules_included").notNull(),
  modulesPassed: integer("modules_passed").notNull(),
  modulesFailed: integer("modules_failed").notNull(),
  totalEvidenceCount: integer("total_evidence_count").notNull(),
  payloadJson: text("payload_json").notNull(),
  payloadHash: text("payload_hash").notNull(),
  signatureHex: text("signature_hex").notNull(),
  generatedBy: text("generated_by").notNull(),
  generatedAt: timestamp("generated_at").notNull().default(sql`NOW()`),
});

// ===== 5. i18n Translations (en/lg/sw) =====
export const i18nTranslations = pgTable("i18n_translations", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  translationKey: text("translation_key").notNull(),
  locale: text("locale").notNull(), // en, lg, sw
  value: text("value").notNull(),
  category: text("category").notNull().default("ui"),
  updatedAt: timestamp("updated_at").notNull().default(sql`NOW()`),
});

// ===== 6. Mobile-Money Sandbox Adapters (MTN MoMo / Airtel Money) =====
export const momoSandboxTxns = pgTable("momo_sandbox_txns", {
  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
  txnRef: text("txn_ref").notNull().unique(),
  provider: text("provider").notNull(), // "MTN_MOMO" | "AIRTEL_MONEY"
  txnType: text("txn_type").notNull(), // "send", "receive", "cashout", "balance"
  msisdn: text("msisdn").notNull(),
  amountUgx: bigint("amount_ugx", { mode: "number" }).notNull(),
  status: text("status").notNull(), // "pending", "completed", "failed"
  providerRef: text("provider_ref").notNull(),
  riskScore: integer("risk_score").notNull(),
  flagged: integer("flagged").notNull().default(0),
  responseJson: text("response_json").notNull(),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
}).enableRLS();

// ===== 7. DR Failover Drill Runner =====
export const drDrillRuns = pgTable("dr_drill_runs", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  drillRef: text("drill_ref").notNull().unique(),
  scenarioCode: text("scenario_code").notNull(),
  primaryRegion: text("primary_region").notNull(),
  failoverRegion: text("failover_region").notNull(),
  detectionTimeSec: integer("detection_time_sec").notNull(),
  failoverTimeSec: integer("failover_time_sec").notNull(),
  rpoMin: integer("rpo_min").notNull(),
  rtoMin: integer("rto_min").notNull(),
  failbackTimeSec: integer("failback_time_sec").notNull(),
  dataLossRecords: integer("data_loss_records").notNull().default(0),
  passed: integer("passed").notNull(),
  evidenceHash: text("evidence_hash").notNull(),
  ranBy: text("ran_by").notNull(),
  ranAt: timestamp("ran_at").notNull().default(sql`NOW()`),
});

// ===== 8. STIX/TAXII Threat-Intel IoCs =====
export const threatIntelIocs = pgTable("threat_intel_iocs", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  iocId: text("ioc_id").notNull().unique(),
  iocType: text("ioc_type").notNull(), // "ipv4", "domain", "url", "file-hash", "email"
  iocValue: text("ioc_value").notNull(),
  threatType: text("threat_type").notNull(),
  confidence: integer("confidence").notNull(), // 0-100
  severity: text("severity").notNull(), // LOW/MEDIUM/HIGH/CRITICAL
  feedSource: text("feed_source").notNull(), // "MISP", "OTX", "FIA-Uganda"
  validFrom: timestamp("valid_from").notNull(),
  validUntil: timestamp("valid_until"),
  stixObjectJson: text("stix_object_json").notNull(),
  ingestedAt: timestamp("ingested_at").notNull().default(sql`NOW()`),
});

// ===== 9. SLSA / in-toto Build Attestations =====
export const slsaAttestations = pgTable("slsa_attestations", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  attestationRef: text("attestation_ref").notNull().unique(),
  buildId: text("build_id").notNull(),
  gitCommitSha: text("git_commit_sha").notNull(),
  builderId: text("builder_id").notNull(),
  buildType: text("build_type").notNull(),
  slsaLevel: integer("slsa_level").notNull(), // 1-4
  subjectName: text("subject_name").notNull(),
  subjectDigestSha256: text("subject_digest_sha256").notNull(),
  predicateJson: text("predicate_json").notNull(),
  signatureHex: text("signature_hex").notNull(),
  verified: integer("verified").notNull().default(1),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
});

// ===== 10. Ombudsman Complaints (consumer-protection channel) =====
export const ombudsmanComplaints = pgTable("ombudsman_complaints", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  complaintRef: text("complaint_ref").notNull().unique(),
  customerName: text("customer_name").notNull(),
  customerContact: text("customer_contact").notNull(),
  channel: text("channel").notNull(), // web, ussd, branch, ombudsman
  category: text("category").notNull(),
  description: text("description").notNull(),
  severity: text("severity").notNull().default("MEDIUM"),
  status: text("status").notNull().default("open"), // open, investigating, resolved, escalated
  slaHours: integer("sla_hours").notNull().default(72),
  resolvedAt: timestamp("resolved_at"),
  resolvedBy: text("resolved_by"),
  resolution: text("resolution"),
  escalatedToOmbudsman: integer("escalated_to_ombudsman").notNull().default(0),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
});

// ===== 11. Decision Replay Console =====
export const decisionReplays = pgTable("decision_replays", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  replayRef: text("replay_ref").notNull().unique(),
  originalDecisionRef: text("original_decision_ref").notNull(),
  decisionType: text("decision_type").notNull(),
  originalScore: integer("original_score").notNull(),
  replayScore: integer("replay_score").notNull(),
  scoreDeltaMicros: integer("score_delta_micros").notNull(),
  reproducible: integer("reproducible").notNull(),
  modelVersion: text("model_version").notNull(),
  inputsJson: text("inputs_json").notNull(),
  outputsJson: text("outputs_json").notNull(),
  ranBy: text("ran_by").notNull(),
  ranAt: timestamp("ran_at").notNull().default(sql`NOW()`),
});

// ===== 12. Regulator Onboarding Wizard =====
export const regulatorOnboardingSessions = pgTable("regulator_onboarding_sessions", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  sessionRef: text("session_ref").notNull().unique(),
  regulatorAgency: text("regulator_agency").notNull(), // BoU, FIA, UCC
  examinerName: text("examiner_name").notNull(),
  examinerEmail: text("examiner_email").notNull(),
  step1AccountCreated: integer("step1_account_created").notNull().default(0),
  step2AuditorTokenIssued: integer("step2_auditor_token_issued").notNull().default(0),
  step3DrillScheduled: integer("step3_drill_scheduled").notNull().default(0),
  step4DemoPackReviewed: integer("step4_demo_pack_reviewed").notNull().default(0),
  step5CertificateIssued: integer("step5_certificate_issued").notNull().default(0),
  certificateRef: text("certificate_ref"),
  certificateHash: text("certificate_hash"),
  startedBy: text("started_by").notNull(),
  startedAt: timestamp("started_at").notNull().default(sql`NOW()`),
  completedAt: timestamp("completed_at"),
});

// ===== 13. Prometheus Metrics Snapshots (for /metrics evidence) =====
export const metricsSnapshots = pgTable("metrics_snapshots", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  snapshotRef: text("snapshot_ref").notNull().unique(),
  uptimeSec: integer("uptime_sec").notNull(),
  totalRequests: bigint("total_requests", { mode: "number" }).notNull(),
  errorRatePctMicros: integer("error_rate_pct_micros").notNull(),
  p95LatencyMs: integer("p95_latency_ms").notNull(),
  aiDecisionsTotal: bigint("ai_decisions_total", { mode: "number" }).notNull(),
  threatEventsTotal: bigint("threat_events_total", { mode: "number" }).notNull(),
  capturedAt: timestamp("captured_at").notNull().default(sql`NOW()`),
});

// ── #52 API-response row types ──────────────────────────────────────────────
// $inferSelect row types for the wave8 list endpoints. Exported so the client can
// type its useQuery calls against the SERVER's real column set — a client read of a
// field the row doesn't have (or a schema column rename) becomes a compile error.
export type E2eTestRun = typeof e2eTestRuns.$inferSelect;
export type Wave8LoadTestRun = typeof wave8LoadTestRuns.$inferSelect;
export type SecurityScan = typeof securityScans.$inferSelect;
export type MomoSandboxTxn = typeof momoSandboxTxns.$inferSelect;
export type DrDrillRun = typeof drDrillRuns.$inferSelect;
export type ThreatIntelIoc = typeof threatIntelIocs.$inferSelect;
export type SlsaAttestation = typeof slsaAttestations.$inferSelect;
export type OmbudsmanComplaint = typeof ombudsmanComplaints.$inferSelect;
export type DecisionReplay = typeof decisionReplays.$inferSelect;
export type RegulatorOnboardingSession = typeof regulatorOnboardingSessions.$inferSelect;
export type MetricsSnapshot = typeof metricsSnapshots.$inferSelect;
// /api/demo-pack/list returns a PROJECTED subset (no payloadJson) — type it as the
// exact projection, not the full row, so the client type matches the real payload.
export type DemoPackSummary = Pick<
  typeof demoPackGenerations.$inferSelect,
  | "id" | "packRef" | "modulesIncluded" | "modulesPassed" | "modulesFailed"
  | "totalEvidenceCount" | "payloadHash" | "signatureHex" | "generatedBy" | "generatedAt"
>;