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

// ========================================
// 14 Tier 1-3 regulator-pilot enhancement tables
// ========================================

// Tier 1
export const continuousScheduleRuns = pgTable("continuous_schedule_runs", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  scheduleName: text("schedule_name").notNull(),
  passedCount: integer("passed_count").notNull(),
  failedCount: integer("failed_count").notNull(),
  totalDrills: integer("total_drills").notNull(),
  evidencePackSha256: text("evidence_pack_sha256"),
  durationMs: integer("duration_ms").notNull(),
  detailsJson: text("details_json").notNull(),
  triggeredBy: text("triggered_by").notNull(),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
});

export const tamperDemoRuns = pgTable("tamper_demo_runs", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  passed: integer("passed").notNull(),
  detected: integer("detected").notNull(),
  tamperedRowId: text("tampered_row_id").notNull(),
  detectionMethod: text("detection_method").notNull(),
  durationMs: integer("duration_ms").notNull(),
  detailsJson: text("details_json").notNull(),
  triggeredBy: text("triggered_by").notNull(),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
});

export const restoreDrillRuns = pgTable("restore_drill_runs", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  passed: integer("passed").notNull(),
  rowsRestored: integer("rows_restored").notNull(),
  expectedRows: integer("expected_rows").notNull(),
  canaryFound: integer("canary_found").notNull(),
  rtoMs: integer("rto_ms").notNull(),
  rtoBudgetMs: integer("rto_budget_ms").notNull(),
  durationMs: integer("duration_ms").notNull(),
  detailsJson: text("details_json").notNull(),
  triggeredBy: text("triggered_by").notNull(),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
});

export const breachSlaDrillRuns = pgTable("breach_sla_drill_runs", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  passed: integer("passed").notNull(),
  breachId: text("breach_id").notNull(),
  notificationsSent: integer("notifications_sent").notNull(),
  slaWindowHours: integer("sla_window_hours").notNull(),
  notificationLatencyMs: integer("notification_latency_ms").notNull(),
  templatesValidated: integer("templates_validated").notNull(),
  durationMs: integer("duration_ms").notNull(),
  detailsJson: text("details_json").notNull(),
  triggeredBy: text("triggered_by").notNull(),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
});

// Tier 2
export const biasDrillRuns = pgTable("bias_drill_runs", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  passed: integer("passed").notNull(),
  bucketsTested: integer("buckets_tested").notNull(),
  maxFprDelta: real("max_fpr_delta").notNull(),
  threshold: real("threshold").notNull(),
  totalSamples: integer("total_samples").notNull(),
  durationMs: integer("duration_ms").notNull(),
  detailsJson: text("details_json").notNull(),
  triggeredBy: text("triggered_by").notNull(),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
});

export const modelDriftRuns = pgTable("model_drift_runs", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  passed: integer("passed").notNull(),
  modelName: text("model_name").notNull(),
  driftScore: real("drift_score").notNull(),
  threshold: real("threshold").notNull(),
  baselineMean: real("baseline_mean").notNull(),
  currentMean: real("current_mean").notNull(),
  samplesScored: integer("samples_scored").notNull(),
  durationMs: integer("duration_ms").notNull(),
  detailsJson: text("details_json").notNull(),
  triggeredBy: text("triggered_by").notNull(),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
});

export const piaDocuments = pgTable("pia_documents", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  featureName: text("feature_name").notNull(),
  riskLevel: text("risk_level").notNull(),
  encryptedFields: integer("encrypted_fields").notNull(),
  retentionDays: integer("retention_days").notNull(),
  legalBasis: text("legal_basis").notNull(),
  contentMarkdown: text("content_markdown").notNull(),
  sha256: text("sha256").notNull(),
  triggeredBy: text("triggered_by").notNull(),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
});

export const runbookExecutions = pgTable("runbook_executions", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  runbookName: text("runbook_name").notNull(),
  status: text("status").notNull(),
  stepsCompleted: integer("steps_completed").notNull(),
  totalSteps: integer("total_steps").notNull(),
  durationMs: integer("duration_ms").notNull(),
  timelineJson: text("timeline_json").notNull(),
  signature: text("signature").notNull(),
  operator: text("operator").notNull(),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
  completedAt: timestamp("completed_at"),
});

// Tier 3
export const regulatorInboxMessages = pgTable("regulator_inbox_messages", {
  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
  threadId: text("thread_id").notNull(),
  fromRole: text("from_role").notNull(),
  fromUser: text("from_user").notNull(),
  subject: text("subject").notNull(),
  body: text("body").notNull(),
  evidenceLinks: text("evidence_links"),
  status: text("status").notNull().default("open"),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
}).enableRLS();

export const controlCostSnapshots = pgTable("control_cost_snapshots", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  controlName: text("control_name").notNull(),
  cpuMillis: integer("cpu_millis").notNull(),
  storageBytes: integer("storage_bytes").notNull(),
  alertVolume: integer("alert_volume").notNull(),
  costUsdMicros: integer("cost_usd_micros").notNull(),
  durationMs: integer("duration_ms").notNull(),
  detailsJson: text("details_json").notNull(),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
});

export const failoverDemoRuns = pgTable("failover_demo_runs", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  passed: integer("passed").notNull(),
  fromRegion: text("from_region").notNull(),
  toRegion: text("to_region").notNull(),
  rtoMs: integer("rto_ms").notNull(),
  rpoMs: integer("rpo_ms").notNull(),
  rtoBudgetMs: integer("rto_budget_ms").notNull(),
  rpoBudgetMs: integer("rpo_budget_ms").notNull(),
  durationMs: integer("duration_ms").notNull(),
  detailsJson: text("details_json").notNull(),
  triggeredBy: text("triggered_by").notNull(),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
});

export const businessLogicPentestRuns = pgTable("business_logic_pentest_runs", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  passed: integer("passed").notNull(),
  totalProbes: integer("total_probes").notNull(),
  blockedProbes: integer("blocked_probes").notNull(),
  highSeverityFindings: integer("high_severity_findings").notNull(),
  durationMs: integer("duration_ms").notNull(),
  findingsJson: text("findings_json").notNull(),
  triggeredBy: text("triggered_by").notNull(),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
});

export const accessibilityAudits = pgTable("accessibility_audits", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  passed: integer("passed").notNull(),
  pagesScanned: integer("pages_scanned").notNull(),
  issuesCritical: integer("issues_critical").notNull(),
  issuesSerious: integer("issues_serious").notNull(),
  issuesModerate: integer("issues_moderate").notNull(),
  i18nCoveragePercent: real("i18n_coverage_percent").notNull(),
  languagesCovered: text("languages_covered").notNull(),
  durationMs: integer("duration_ms").notNull(),
  detailsJson: text("details_json").notNull(),
  triggeredBy: text("triggered_by").notNull(),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
});

// Type exports
export type ContinuousScheduleRun = typeof continuousScheduleRuns.$inferSelect;
export type TamperDemoRun = typeof tamperDemoRuns.$inferSelect;
export type RestoreDrillRun = typeof restoreDrillRuns.$inferSelect;
export type BreachSlaDrillRun = typeof breachSlaDrillRuns.$inferSelect;
export type BiasDrillRun = typeof biasDrillRuns.$inferSelect;
export type ModelDriftRun = typeof modelDriftRuns.$inferSelect;
export type PiaDocument = typeof piaDocuments.$inferSelect;
export type RunbookExecution = typeof runbookExecutions.$inferSelect;
export type RegulatorInboxMessage = typeof regulatorInboxMessages.$inferSelect;
export type ControlCostSnapshot = typeof controlCostSnapshots.$inferSelect;
export type FailoverDemoRun = typeof failoverDemoRuns.$inferSelect;
export type BusinessLogicPentestRun = typeof businessLogicPentestRuns.$inferSelect;
export type AccessibilityAudit = typeof accessibilityAudits.$inferSelect;