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-11 — Final Pilot-Readiness Suite (9 modules)
// ============================================================

// 1. Nightly Canary Suite + 7-day rolling baseline
export const canaryRuns = pgTable("canary_runs", {
  id: idCol(),
  runRef: varchar("run_ref", { length: 64 }).notNull().unique(),
  trigger: varchar("trigger", { length: 16 }).notNull().default("scheduled"), // scheduled | manual
  scenarioCount: integer("scenario_count").notNull().default(0),
  passedCount: integer("passed_count").notNull().default(0),
  failedCount: integer("failed_count").notNull().default(0),
  p50Ms: integer("p50_ms").notNull().default(0),
  p95Ms: integer("p95_ms").notNull().default(0),
  p99Ms: integer("p99_ms").notNull().default(0),
  baselineP95Ms: integer("baseline_p95_ms").notNull().default(0),
  driftRatio: real("drift_ratio").notNull().default(1),
  alertSeverity: varchar("alert_severity", { length: 16 }).notNull().default("none"), // none | warning | critical
  scoreOf100: integer("score_of_100").notNull().default(0),
  grade: varchar("grade", { length: 4 }).notNull().default("F"),
  detailsJson: jsonb("details_json").notNull().default("{}"),
  ranBy: varchar("ran_by", { length: 128 }).notNull(),
  ranAt: ts("ran_at"),
}, (t) => ({
  ranAtIdx: index("canary_runs_ran_at_idx").on(t.ranAt.desc()),
}));

// 2. Forensic Evidence Bundle Export
export const forensicBundles = pgTable("forensic_bundles", {
  id: idCol(),
  bundleRef: varchar("bundle_ref", { length: 64 }).notNull().unique(),
  threatEventId: varchar("threat_event_id", { length: 128 }).notNull(),
  bundleSizeBytes: integer("bundle_size_bytes").notNull().default(0),
  itemCount: integer("item_count").notNull().default(0),
  manifestHash: varchar("manifest_hash", { length: 128 }).notNull(),
  signatureHex: varchar("signature_hex", { length: 256 }).notNull(),
  bundleZipB64: text("bundle_zip_b64").notNull(), // base64-encoded application/zip
  manifestJson: jsonb("manifest_json").notNull().default("{}"),
  generatedBy: varchar("generated_by", { length: 128 }).notNull(),
  generatedAt: ts("generated_at"),
}, (t) => ({
  threatEvtIdx: index("forensic_bundles_threat_event_idx").on(t.threatEventId),
  generatedAtIdx: index("forensic_bundles_generated_at_idx").on(t.generatedAt.desc()),
}));

// 3a. Closed-loop Production SLA Monitor — thresholds (config)
export const slaThresholds = pgTable("sla_thresholds", {
  id: idCol(),
  metricKey: varchar("metric_key", { length: 96 }).notNull().unique(),
  displayName: varchar("display_name", { length: 192 }).notNull(),
  thresholdMs: integer("threshold_ms").notNull(), // > = breach
  windowSeconds: integer("window_seconds").notNull().default(180), // sustained for N seconds before alert
  enabled: integer("enabled").notNull().default(1),
  notifyOnBreach: integer("notify_on_breach").notNull().default(1),
  updatedBy: varchar("updated_by", { length: 128 }).notNull(),
  updatedAt: ts("updated_at"),
});

// 3b. SLA breach incidents
export const slaBreaches = pgTable("sla_breaches", {
  id: idCol(),
  breachRef: varchar("breach_ref", { length: 64 }).notNull().unique(),
  metricKey: varchar("metric_key", { length: 96 }).notNull(),
  observedMs: integer("observed_ms").notNull(),
  thresholdMs: integer("threshold_ms").notNull(),
  exceedRatio: real("exceed_ratio").notNull().default(1),
  durationSeconds: integer("duration_seconds").notNull().default(0),
  severity: varchar("severity", { length: 16 }).notNull().default("warning"),
  notified: integer("notified").notNull().default(0),
  resolved: integer("resolved").notNull().default(0),
  resolvedAt: timestamp("resolved_at", { withTimezone: true }),
  detailsJson: jsonb("details_json").notNull().default("{}"),
  detectedAt: ts("detected_at"),
}, (t) => ({
  detectedAtIdx: index("sla_breaches_detected_at_idx").on(t.detectedAt.desc()),
  metricKeyIdx: index("sla_breaches_metric_key_idx").on(t.metricKey),
}));

// 4a. Adversarial AI Prompt-Injection corpus (curated test cases)
export const promptInjectionCorpus = pgTable("prompt_injection_corpus", {
  id: idCol(),
  corpusKey: varchar("corpus_key", { length: 96 }).notNull().unique(),
  category: varchar("category", { length: 48 }).notNull(), // jailbreak | indirect | exfil | role-play | unicode | encoded
  severity: varchar("severity", { length: 16 }).notNull().default("medium"),
  payload: text("payload").notNull(),
  expectedBehavior: text("expected_behavior").notNull(), // e.g. "score >= 80 OR refusal"
  active: integer("active").notNull().default(1),
  createdAt: ts("created_at"),
});

// 4b. Adversarial run results
export const promptInjectionRuns = pgTable("prompt_injection_runs", {
  id: idCol(),
  runRef: varchar("run_ref", { length: 64 }).notNull().unique(),
  totalCases: integer("total_cases").notNull().default(0),
  defendedCount: integer("defended_count").notNull().default(0),
  bypassedCount: integer("bypassed_count").notNull().default(0),
  errorCount: integer("error_count").notNull().default(0),
  defenseRatePct: real("defense_rate_pct").notNull().default(0),
  scoreOf100: integer("score_of_100").notNull().default(0),
  grade: varchar("grade", { length: 4 }).notNull().default("F"),
  byCategoryJson: jsonb("by_category_json").notNull().default("{}"),
  bypassesJson: jsonb("bypasses_json").notNull().default("[]"),
  ranBy: varchar("ran_by", { length: 128 }).notNull(),
  ranAt: ts("ran_at"),
}, (t) => ({
  ranAtIdx: index("prompt_injection_runs_ran_at_idx").on(t.ranAt.desc()),
}));

// 5. Data-Residency Monthly Attestation
export const residencyAttestations = pgTable("residency_attestations", {
  id: idCol(),
  attestationRef: varchar("attestation_ref", { length: 64 }).notNull().unique(),
  periodMonth: varchar("period_month", { length: 7 }).notNull(), // YYYY-MM
  jurisdiction: varchar("jurisdiction", { length: 32 }).notNull().default("UG"),
  edgeNodeCount: integer("edge_node_count").notNull().default(0),
  totalRequests: integer("total_requests").notNull().default(0),
  inJurisdictionRequests: integer("in_jurisdiction_requests").notNull().default(0),
  outOfJurisdictionRequests: integer("out_of_jurisdiction_requests").notNull().default(0),
  piiBytesProcessed: integer("pii_bytes_processed").notNull().default(0),
  piiBytesEgressed: integer("pii_bytes_egressed").notNull().default(0),
  compliancePct: real("compliance_pct").notNull().default(100),
  passed: integer("passed").notNull().default(0),
  manifestHash: varchar("manifest_hash", { length: 128 }).notNull(),
  signatureHex: varchar("signature_hex", { length: 256 }).notNull(),
  manifestJson: jsonb("manifest_json").notNull().default("{}"),
  generatedBy: varchar("generated_by", { length: 128 }).notNull(),
  generatedAt: ts("generated_at"),
}, (t) => ({
  periodIdx: index("residency_attestations_period_idx").on(t.periodMonth),
  generatedAtIdx: index("residency_attestations_generated_at_idx").on(t.generatedAt.desc()),
}));

// 6. DR Drill with Auto-Measured RPO/RTO
export const drDrillResults = pgTable("dr_drill_results", {
  id: idCol(),
  drillRef: varchar("drill_ref", { length: 64 }).notNull().unique(),
  drillType: varchar("drill_type", { length: 32 }).notNull().default("scheduled"), // scheduled | adhoc
  triggerAt: timestamp("trigger_at", { withTimezone: true }).notNull(),
  failoverAt: timestamp("failover_at", { withTimezone: true }).notNull(),
  firstWriteAt: timestamp("first_write_at", { withTimezone: true }).notNull(),
  fullSyncAt: timestamp("full_sync_at", { withTimezone: true }).notNull(),
  rtoSeconds: integer("rto_seconds").notNull().default(0), // trigger -> firstWrite
  rpoSeconds: integer("rpo_seconds").notNull().default(0), // last successful sync -> failover
  rtoTargetSeconds: integer("rto_target_seconds").notNull().default(300),
  rpoTargetSeconds: integer("rpo_target_seconds").notNull().default(60),
  rtoMet: integer("rto_met").notNull().default(0),
  rpoMet: integer("rpo_met").notNull().default(0),
  passed: integer("passed").notNull().default(0),
  evidenceHash: varchar("evidence_hash", { length: 128 }).notNull(),
  detailsJson: jsonb("details_json").notNull().default("{}"),
  ranBy: varchar("ran_by", { length: 128 }).notNull(),
  ranAt: ts("ran_at"),
}, (t) => ({
  ranAtIdx: index("dr_drill_results_ran_at_idx").on(t.ranAt.desc()),
}));

// 7. PDPO 72-hour Breach-Notification Drill
export const pdpoBreachDrills = pgTable("pdpo_breach_drills", {
  id: idCol(),
  drillRef: varchar("drill_ref", { length: 64 }).notNull().unique(),
  syntheticBreachAt: timestamp("synthetic_breach_at", { withTimezone: true }).notNull(),
  detectedAt: timestamp("detected_at_ts", { withTimezone: true }).notNull(),
  triagedAt: timestamp("triaged_at", { withTimezone: true }).notNull(),
  notificationDraftedAt: timestamp("notification_drafted_at", { withTimezone: true }).notNull(),
  notificationDeliveredAt: timestamp("notification_delivered_at", { withTimezone: true }).notNull(),
  totalSeconds: integer("total_seconds").notNull().default(0),
  thresholdSeconds: integer("threshold_seconds").notNull().default(259200), // 72h
  withinThreshold: integer("within_threshold").notNull().default(0),
  passed: integer("passed").notNull().default(0),
  recipientsJson: jsonb("recipients_json").notNull().default("[]"),
  detailsJson: jsonb("details_json").notNull().default("{}"),
  ranBy: varchar("ran_by", { length: 128 }).notNull(),
  ranAt: ts("ran_at"),
}, (t) => ({
  ranAtIdx: index("pdpo_breach_drills_ran_at_idx").on(t.ranAt.desc()),
}));

// 8a. Production Traffic Shadow — captured requests
export const trafficShadowCaptures = pgTable("traffic_shadow_captures", {
  id: idCol(),
  captureRef: varchar("capture_ref", { length: 64 }).notNull().unique(),
  method: varchar("method", { length: 8 }).notNull(),
  pathTemplate: varchar("path_template", { length: 256 }).notNull(),
  requestBodyHash: varchar("request_body_hash", { length: 128 }).notNull(),
  responseStatus: integer("response_status").notNull(),
  responseHash: varchar("response_hash", { length: 128 }).notNull(),
  responseDurationMs: integer("response_duration_ms").notNull().default(0),
  redactedBodyJson: jsonb("redacted_body_json").notNull().default("{}"),
  capturedAt: ts("captured_at"),
}, (t) => ({
  capturedAtIdx: index("traffic_shadow_captures_captured_at_idx").on(t.capturedAt.desc()),
}));

// 8b. Shadow replay results
export const trafficShadowReplays = pgTable("traffic_shadow_replays", {
  id: idCol(),
  replayRef: varchar("replay_ref", { length: 64 }).notNull().unique(),
  capturesReplayed: integer("captures_replayed").notNull().default(0),
  matchCount: integer("match_count").notNull().default(0),
  diffCount: integer("diff_count").notNull().default(0),
  errorCount: integer("error_count").notNull().default(0),
  matchRatePct: real("match_rate_pct").notNull().default(0),
  scoreOf100: integer("score_of_100").notNull().default(0),
  grade: varchar("grade", { length: 4 }).notNull().default("F"),
  diffsJson: jsonb("diffs_json").notNull().default("[]"),
  ranBy: varchar("ran_by", { length: 128 }).notNull(),
  ranAt: ts("ran_at"),
}, (t) => ({
  ranAtIdx: index("traffic_shadow_replays_ran_at_idx").on(t.ranAt.desc()),
}));

// 9. Cryptographic Key Inventory
export const keyInventory = pgTable("key_inventory", {
  id: idCol(),
  keyId: varchar("key_id", { length: 96 }).notNull().unique(),
  keyType: varchar("key_type", { length: 32 }).notNull(), // signing | encryption | hmac | jwt | tls | hsm
  algorithm: varchar("algorithm", { length: 64 }).notNull(),
  bitLength: integer("bit_length").notNull().default(0),
  ownerEmail: varchar("owner_email", { length: 192 }).notNull(),
  status: varchar("status", { length: 16 }).notNull().default("active"), // active | rotating | retired | compromised
  storageLocation: varchar("storage_location", { length: 64 }).notNull().default("env"), // env | hsm | kms | vault
  rotationIntervalDays: integer("rotation_interval_days").notNull().default(90),
  lastRotatedAt: timestamp("last_rotated_at", { withTimezone: true }),
  nextRotationDueAt: timestamp("next_rotation_due_at", { withTimezone: true }),
  postQuantum: integer("post_quantum").notNull().default(0),
  notesJson: jsonb("notes_json").notNull().default("{}"),
  createdBy: varchar("created_by", { length: 128 }).notNull(),
  createdAt: ts("created_at"),
  updatedAt: ts("updated_at"),
});

// ============================================================
// Insert schemas + types
// ============================================================
export const insertCanaryRunSchema = createInsertSchema(canaryRuns).omit({ id: true, ranAt: true });
export type InsertCanaryRun = z.infer<typeof insertCanaryRunSchema>;
export type CanaryRun = typeof canaryRuns.$inferSelect;

export const insertForensicBundleSchema = createInsertSchema(forensicBundles).omit({ id: true, generatedAt: true });
export type InsertForensicBundle = z.infer<typeof insertForensicBundleSchema>;
export type ForensicBundle = typeof forensicBundles.$inferSelect;

export const insertSlaThresholdSchema = createInsertSchema(slaThresholds).omit({ id: true, updatedAt: true });
export type InsertSlaThreshold = z.infer<typeof insertSlaThresholdSchema>;
export type SlaThreshold = typeof slaThresholds.$inferSelect;

export const insertSlaBreachSchema = createInsertSchema(slaBreaches).omit({ id: true, detectedAt: true });
export type InsertSlaBreach = z.infer<typeof insertSlaBreachSchema>;
export type SlaBreach = typeof slaBreaches.$inferSelect;

export const insertPromptInjectionCorpusSchema = createInsertSchema(promptInjectionCorpus).omit({ id: true, createdAt: true });
export type InsertPromptInjectionCorpus = z.infer<typeof insertPromptInjectionCorpusSchema>;
export type PromptInjectionCorpus = typeof promptInjectionCorpus.$inferSelect;

export const insertPromptInjectionRunSchema = createInsertSchema(promptInjectionRuns).omit({ id: true, ranAt: true });
export type InsertPromptInjectionRun = z.infer<typeof insertPromptInjectionRunSchema>;
export type PromptInjectionRun = typeof promptInjectionRuns.$inferSelect;

export const insertResidencyAttestationSchema = createInsertSchema(residencyAttestations).omit({ id: true, generatedAt: true });
export type InsertResidencyAttestation = z.infer<typeof insertResidencyAttestationSchema>;
export type ResidencyAttestation = typeof residencyAttestations.$inferSelect;

export const insertDrDrillResultSchema = createInsertSchema(drDrillResults).omit({ id: true, ranAt: true });
export type InsertDrDrillResult = z.infer<typeof insertDrDrillResultSchema>;
export type DrDrillResult = typeof drDrillResults.$inferSelect;

export const insertPdpoBreachDrillSchema = createInsertSchema(pdpoBreachDrills).omit({ id: true, ranAt: true });
export type InsertPdpoBreachDrill = z.infer<typeof insertPdpoBreachDrillSchema>;
export type PdpoBreachDrill = typeof pdpoBreachDrills.$inferSelect;

export const insertTrafficShadowCaptureSchema = createInsertSchema(trafficShadowCaptures).omit({ id: true, capturedAt: true });
export type InsertTrafficShadowCapture = z.infer<typeof insertTrafficShadowCaptureSchema>;
export type TrafficShadowCapture = typeof trafficShadowCaptures.$inferSelect;

export const insertTrafficShadowReplaySchema = createInsertSchema(trafficShadowReplays).omit({ id: true, ranAt: true });
export type InsertTrafficShadowReplay = z.infer<typeof insertTrafficShadowReplaySchema>;
export type TrafficShadowReplay = typeof trafficShadowReplays.$inferSelect;

export const insertKeyInventorySchema = createInsertSchema(keyInventory).omit({ id: true, createdAt: true, updatedAt: true });
export type InsertKeyInventory = z.infer<typeof insertKeyInventorySchema>;
export type KeyInventoryItem = typeof keyInventory.$inferSelect;