// Pilot Tier B — additional regulator-grade drills.
// Each drill table follows the same pattern: drillRef, durationMs, signed
// receipt (receiptHash + signatureHex), passed flag, detailsJson, timestamp.
import { pgTable, serial, text, integer, jsonb, timestamp, index } from "drizzle-orm/pg-core";

// ── (1) Reproducibility / Determinism ────────────────────────────────
export const reproducibilityDrills = pgTable("reproducibility_drills", {
  id: serial("id").primaryKey(),
  drillRef: text("drill_ref").notNull(),
  iterations: integer("iterations").notNull(),
  uniqueScores: integer("unique_scores").notNull(),
  scoresJson: jsonb("scores_json"),
  fixtureRef: text("fixture_ref").notNull(),
  durationMs: integer("duration_ms").notNull(),
  receiptHash: text("receipt_hash").notNull(),
  signatureHex: text("signature_hex").notNull(),
  passed: integer("passed").notNull(),
  detailsJson: jsonb("details_json"),
  triggeredBy: text("triggered_by").notNull(),
  triggeredAt: timestamp("triggered_at").notNull().defaultNow(),
}, (t) => [ index("reproducibility_drills_at_desc_idx").on(t.triggeredAt.desc()) ]);

// ── (2) PII Egress Sentinel ──────────────────────────────────────────
export const piiEgressDrills = pgTable("pii_egress_drills", {
  id: serial("id").primaryKey(),
  drillRef: text("drill_ref").notNull(),
  canariesPlanted: integer("canaries_planted").notNull(),
  channelsScanned: integer("channels_scanned").notNull(),
  leaksFound: integer("leaks_found").notNull(),
  perChannelJson: jsonb("per_channel_json"),
  durationMs: integer("duration_ms").notNull(),
  receiptHash: text("receipt_hash").notNull(),
  signatureHex: text("signature_hex").notNull(),
  passed: integer("passed").notNull(),
  detailsJson: jsonb("details_json"),
  triggeredBy: text("triggered_by").notNull(),
  triggeredAt: timestamp("triggered_at").notNull().defaultNow(),
}, (t) => [ index("pii_egress_drills_at_desc_idx").on(t.triggeredAt.desc()) ]);

// ── (3) Backup-Restore Drill (Tier B variant) ────────────────────────
// NOTE: existing table `backup_restore_drills` (extras-10) has a totally
// different schema, so Tier B uses its own table to avoid colliding.
export const backupRestoreTierbDrills = pgTable("backup_restore_tierb_drills", {
  id: serial("id").primaryKey(),
  drillRef: text("drill_ref").notNull(),
  scratchSchema: text("scratch_schema").notNull(),
  tablesChecked: integer("tables_checked").notNull(),
  rowsBefore: integer("rows_before").notNull(),
  rowsAfter: integer("rows_after").notNull(),
  rowMismatchCount: integer("row_mismatch_count").notNull(),
  perTableJson: jsonb("per_table_json"),
  durationMs: integer("duration_ms").notNull(),
  receiptHash: text("receipt_hash").notNull(),
  signatureHex: text("signature_hex").notNull(),
  passed: integer("passed").notNull(),
  detailsJson: jsonb("details_json"),
  triggeredBy: text("triggered_by").notNull(),
  triggeredAt: timestamp("triggered_at").notNull().defaultNow(),
}, (t) => [ index("backup_restore_tierb_drills_at_desc_idx").on(t.triggeredAt.desc()) ]);

// ── (4) Time-sync / NTP Drift Drill ──────────────────────────────────
export const timeSyncDrills = pgTable("time_sync_drills", {
  id: serial("id").primaryKey(),
  drillRef: text("drill_ref").notNull(),
  source: text("source").notNull(),
  driftMs: integer("drift_ms").notNull(),
  thresholdMs: integer("threshold_ms").notNull(),
  authoritativeIso: text("authoritative_iso"),
  localIso: text("local_iso"),
  durationMs: integer("duration_ms").notNull(),
  receiptHash: text("receipt_hash").notNull(),
  signatureHex: text("signature_hex").notNull(),
  passed: integer("passed").notNull(),
  detailsJson: jsonb("details_json"),
  triggeredBy: text("triggered_by").notNull(),
  triggeredAt: timestamp("triggered_at").notNull().defaultNow(),
}, (t) => [ index("time_sync_drills_at_desc_idx").on(t.triggeredAt.desc()) ]);

// ── (5) Key-Rotation Drill (Tier B variant) ──────────────────────────
// NOTE: existing table `key_rotation_drills` (extras-10) has a totally
// different schema, so Tier B uses its own table to avoid colliding.
export const keyRotationTierbDrills = pgTable("key_rotation_tierb_drills", {
  id: serial("id").primaryKey(),
  drillRef: text("drill_ref").notNull(),
  oldKeyFingerprint: text("old_key_fingerprint").notNull(),
  newKeyFingerprint: text("new_key_fingerprint").notNull(),
  oldKeyStillVerifies: integer("old_key_still_verifies").notNull(),
  newKeySignsAndVerifies: integer("new_key_signs_and_verifies").notNull(),
  crossKeyForgeBlocked: integer("cross_key_forge_blocked").notNull(),
  durationMs: integer("duration_ms").notNull(),
  receiptHash: text("receipt_hash").notNull(),
  signatureHex: text("signature_hex").notNull(),
  passed: integer("passed").notNull(),
  detailsJson: jsonb("details_json"),
  triggeredBy: text("triggered_by").notNull(),
  triggeredAt: timestamp("triggered_at").notNull().defaultNow(),
}, (t) => [ index("key_rotation_tierb_drills_at_desc_idx").on(t.triggeredAt.desc()) ]);

// ── (6) Rate-Limit / DoS Drill ───────────────────────────────────────
export const rateLimitDrills = pgTable("rate_limit_drills", {
  id: serial("id").primaryKey(),
  drillRef: text("drill_ref").notNull(),
  endpoint: text("endpoint").notNull(),
  requestsSent: integer("requests_sent").notNull(),
  successes: integer("successes").notNull(),
  rateLimited: integer("rate_limited").notNull(),
  errors: integer("errors").notNull(),
  rateLimitEngagedAt: integer("rate_limit_engaged_at"),
  durationMs: integer("duration_ms").notNull(),
  receiptHash: text("receipt_hash").notNull(),
  signatureHex: text("signature_hex").notNull(),
  passed: integer("passed").notNull(),
  detailsJson: jsonb("details_json"),
  triggeredBy: text("triggered_by").notNull(),
  triggeredAt: timestamp("triggered_at").notNull().defaultNow(),
}, (t) => [ index("rate_limit_drills_at_desc_idx").on(t.triggeredAt.desc()) ]);

export type ReproducibilityDrill = typeof reproducibilityDrills.$inferSelect;
export type PiiEgressDrill = typeof piiEgressDrills.$inferSelect;
export type BackupRestoreTierbDrill = typeof backupRestoreTierbDrills.$inferSelect;
export type TimeSyncDrill = typeof timeSyncDrills.$inferSelect;
export type KeyRotationTierbDrill = typeof keyRotationTierbDrills.$inferSelect;
export type RateLimitDrill = typeof rateLimitDrills.$inferSelect;