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

const idCol = () => varchar("id").primaryKey().default(sql`gen_random_uuid()`);
const ts = (n: string) => timestamp(n, { withTimezone: true }).notNull().defaultNow();

export const auditTamperDrills = pgTable("audit_tamper_drills", {
  id: idCol(),
  drillRef: varchar("drill_ref", { length: 64 }).notNull().unique("audit_tamper_drills_drill_ref_key"),
  rowsScanned: integer("rows_scanned").notNull().default(0),
  liveChainValid: integer("live_chain_valid").notNull().default(0),
  liveBrokenAt: integer("live_broken_at"),
  tamperDetected: integer("tamper_detected").notNull().default(0),
  durationMs: integer("duration_ms").notNull().default(0),
  receiptHash: varchar("receipt_hash", { length: 128 }).notNull(),
  signatureHex: varchar("signature_hex", { length: 256 }).notNull(),
  passed: integer("passed").notNull().default(0),
  detailsJson: jsonb("details_json").notNull().default({}),
  triggeredBy: varchar("triggered_by", { length: 128 }).notNull(),
  triggeredAt: ts("triggered_at"),
}, (t) => ({
  triggeredAtIdx: index("audit_tamper_drills_triggered_at_idx").on(t.triggeredAt.desc()),
}));
export type AuditTamperDrill = typeof auditTamperDrills.$inferSelect;

export const tenantIsolationDrills = pgTable("tenant_isolation_drills", {
  id: idCol(),
  drillRef: varchar("drill_ref", { length: 64 }).notNull().unique("tenant_isolation_drills_drill_ref_key"),
  tenantsTested: integer("tenants_tested").notNull().default(0),
  endpointsTested: integer("endpoints_tested").notNull().default(0),
  probesRun: integer("probes_run").notNull().default(0),
  leaksFound: integer("leaks_found").notNull().default(0),
  durationMs: integer("duration_ms").notNull().default(0),
  receiptHash: varchar("receipt_hash", { length: 128 }).notNull(),
  signatureHex: varchar("signature_hex", { length: 256 }).notNull(),
  passed: integer("passed").notNull().default(0),
  detailsJson: jsonb("details_json").notNull().default({}),
  triggeredBy: varchar("triggered_by", { length: 128 }).notNull(),
  triggeredAt: ts("triggered_at"),
}, (t) => ({
  triggeredAtIdx: index("tenant_isolation_drills_triggered_at_idx").on(t.triggeredAt.desc()),
}));
export type TenantIsolationDrill = typeof tenantIsolationDrills.$inferSelect;

export const degradationDrills = pgTable("degradation_drills", {
  id: idCol(),
  drillRef: varchar("drill_ref", { length: 64 }).notNull().unique("degradation_drills_drill_ref_key"),
  scenariosRun: integer("scenarios_run").notNull().default(0),
  scenariosPassed: integer("scenarios_passed").notNull().default(0),
  staleSanctionsPassed: integer("stale_sanctions_passed").notNull().default(0),
  aiDownPassed: integer("ai_down_passed").notNull().default(0),
  dbReadonlyPassed: integer("db_readonly_passed").notNull().default(0),
  durationMs: integer("duration_ms").notNull().default(0),
  receiptHash: varchar("receipt_hash", { length: 128 }).notNull(),
  signatureHex: varchar("signature_hex", { length: 256 }).notNull(),
  passed: integer("passed").notNull().default(0),
  detailsJson: jsonb("details_json").notNull().default({}),
  triggeredBy: varchar("triggered_by", { length: 128 }).notNull(),
  triggeredAt: ts("triggered_at"),
}, (t) => ({
  triggeredAtIdx: index("degradation_drills_triggered_at_idx").on(t.triggeredAt.desc()),
}));
export type DegradationDrill = typeof degradationDrills.$inferSelect;

export const bundleVerifications = pgTable("bundle_verifications", {
  id: idCol(),
  verifyRef: varchar("verify_ref", { length: 64 }).notNull().unique("bundle_verifications_verify_ref_key"),
  payloadSha256: varchar("payload_sha256", { length: 128 }).notNull(),
  providedSig: varchar("provided_sig", { length: 256 }).notNull(),
  expectedSig: varchar("expected_sig", { length: 256 }).notNull(),
  valid: integer("valid").notNull().default(0),
  payloadBytes: integer("payload_bytes").notNull().default(0),
  ipHash: varchar("ip_hash", { length: 128 }),
  userAgent: text("user_agent"),
  verifiedAt: ts("verified_at"),
}, (t) => ({
  verifiedAtIdx: index("bundle_verifications_verified_at_idx").on(t.verifiedAt.desc()),
}));
export type BundleVerification = typeof bundleVerifications.$inferSelect;