import { pgTable, varchar, text, timestamp, integer, jsonb, real } 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();

// 1. OWASP API Top-10 deep scan
export const owaspApiScans = pgTable("owasp_api_scans", {
  id: idCol(),
  scanRef: varchar("scan_ref", { length: 64 }).notNull().unique(),
  totalChecks: integer("total_checks").notNull().default(0),
  passedChecks: integer("passed_checks").notNull().default(0),
  failedChecks: integer("failed_checks").notNull().default(0),
  highCount: integer("high_count").notNull().default(0),
  mediumCount: integer("medium_count").notNull().default(0),
  lowCount: integer("low_count").notNull().default(0),
  scoreOf100: integer("score_of_100").notNull().default(0),
  grade: varchar("grade", { length: 4 }).notNull().default("F"),
  findingsJson: jsonb("findings_json").notNull().default("[]"),
  ranBy: varchar("ran_by", { length: 128 }).notNull(),
  ranAt: ts("ran_at"),
});

// 2. Endpoint fuzz harness
export const fuzzTestRuns = pgTable("fuzz_test_runs", {
  id: idCol(),
  runRef: varchar("run_ref", { length: 64 }).notNull().unique(),
  endpointsTested: integer("endpoints_tested").notNull().default(0),
  payloadsTotal: integer("payloads_total").notNull().default(0),
  crashes500: integer("crashes_500").notNull().default(0),
  unhandledErrors: integer("unhandled_errors").notNull().default(0),
  acceptedMalformed: integer("accepted_malformed").notNull().default(0),
  scoreOf100: integer("score_of_100").notNull().default(0),
  findingsJson: jsonb("findings_json").notNull().default("[]"),
  ranBy: varchar("ran_by", { length: 128 }).notNull(),
  ranAt: ts("ran_at"),
});

// 3. Crypto / key rotation drill
export const keyRotationDrills = pgTable("key_rotation_drills", {
  id: idCol(),
  drillRef: varchar("drill_ref", { length: 64 }).notNull().unique(),
  keyType: varchar("key_type", { length: 32 }).notNull(),
  oldKeyId: varchar("old_key_id", { length: 64 }).notNull(),
  newKeyId: varchar("new_key_id", { length: 64 }).notNull(),
  rotationDurationMs: integer("rotation_duration_ms").notNull().default(0),
  zeroDowntime: integer("zero_downtime").notNull().default(1),
  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"),
});

// 4. Backup-restore integrity drill
export const backupRestoreDrills = pgTable("backup_restore_drills", {
  id: idCol(),
  drillRef: varchar("drill_ref", { length: 64 }).notNull().unique(),
  backupRef: varchar("backup_ref", { length: 64 }).notNull(),
  restoreDurationMs: integer("restore_duration_ms").notNull().default(0),
  rpoMinutes: integer("rpo_minutes").notNull().default(0),
  rtoMinutes: integer("rto_minutes").notNull().default(0),
  rowsVerified: integer("rows_verified").notNull().default(0),
  hashMatch: integer("hash_match").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"),
});

// 5. Model card + datasheet (Wave-10 enriched format; existing model_cards table is a legacy minimal one)
export const modelCardsV2 = pgTable("model_cards_v2", {
  id: idCol(),
  modelKey: varchar("model_key", { length: 128 }).notNull().unique(),
  modelName: varchar("model_name", { length: 256 }).notNull(),
  modelVersion: varchar("model_version", { length: 64 }).notNull(),
  modelType: varchar("model_type", { length: 64 }).notNull(),
  vendor: varchar("vendor", { length: 128 }).notNull(),
  purpose: text("purpose").notNull(),
  trainingData: text("training_data").notNull(),
  performanceMetricsJson: jsonb("performance_metrics_json").notNull().default("{}"),
  limitationsJson: jsonb("limitations_json").notNull().$type<string[]>().default([]),
  ethicsConsiderationsJson: jsonb("ethics_considerations_json").notNull().default("[]"),
  driftSignals: text("drift_signals").notNull().default(""),
  ownerEmail: varchar("owner_email", { length: 256 }).notNull(),
  reviewedBy: varchar("reviewed_by", { length: 128 }),
  reviewedAt: timestamp("reviewed_at", { withTimezone: true }),
  publishedAt: ts("published_at"),
});

// 6. Bias / fairness audit
export const biasAuditRuns = pgTable("bias_audit_runs", {
  id: idCol(),
  auditRef: varchar("audit_ref", { length: 64 }).notNull().unique(),
  modelKey: varchar("model_key", { length: 128 }).notNull(),
  protectedAttribute: varchar("protected_attribute", { length: 64 }).notNull(),
  sampleSize: integer("sample_size").notNull().default(0),
  demographicParity: real("demographic_parity").notNull().default(0),
  equalOpportunity: real("equal_opportunity").notNull().default(0),
  disparateImpactRatio: real("disparate_impact_ratio").notNull().default(0),
  passed: integer("passed").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"),
});

// 7. Model rollback drill
export const modelRollbackDrills = pgTable("model_rollback_drills", {
  id: idCol(),
  drillRef: varchar("drill_ref", { length: 64 }).notNull().unique(),
  modelKey: varchar("model_key", { length: 128 }).notNull(),
  fromVersion: varchar("from_version", { length: 64 }).notNull(),
  toVersion: varchar("to_version", { length: 64 }).notNull(),
  rollbackDurationMs: integer("rollback_duration_ms").notNull().default(0),
  slaMinutes: integer("sla_minutes").notNull().default(15),
  withinSla: integer("within_sla").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"),
});

// 8. Chaos engineering runs
export const chaosRuns = pgTable("chaos_runs", {
  id: idCol(),
  runRef: varchar("run_ref", { length: 64 }).notNull().unique(),
  scenario: varchar("scenario", { length: 64 }).notNull(),
  durationMs: integer("duration_ms").notNull().default(0),
  recoveryMs: integer("recovery_ms").notNull().default(0),
  resilienceScore: integer("resilience_score").notNull().default(0),
  passed: integer("passed").notNull().default(0),
  detailsJson: jsonb("details_json").notNull().default("{}"),
  ranBy: varchar("ran_by", { length: 128 }).notNull(),
  ranAt: ts("ran_at"),
});

// 9. Vendor / TPRM register
export const vendorRiskRegister = pgTable("vendor_risk_register", {
  id: idCol(),
  vendorKey: varchar("vendor_key", { length: 128 }).notNull().unique(),
  vendorName: varchar("vendor_name", { length: 256 }).notNull(),
  category: varchar("category", { length: 64 }).notNull(),
  contactEmail: varchar("contact_email", { length: 256 }).notNull(),
  riskScore: integer("risk_score").notNull().default(0),
  riskTier: varchar("risk_tier", { length: 16 }).notNull().default("LOW"),
  contractEndDate: timestamp("contract_end_date", { withTimezone: true }),
  lastReviewedAt: timestamp("last_reviewed_at", { withTimezone: true }),
  nextReviewDueAt: timestamp("next_review_due_at", { withTimezone: true }),
  certificationsJson: jsonb("certifications_json").notNull().$type<string[]>().default([]),
  notes: text("notes").notNull().default(""),
  active: integer("active").notNull().default(1),
  addedAt: ts("added_at"),
});

// 10. KRI metrics
export const kriMetrics = pgTable("kri_metrics", {
  id: idCol(),
  metricKey: varchar("metric_key", { length: 128 }).notNull(),
  metricName: varchar("metric_name", { length: 256 }).notNull(),
  category: varchar("category", { length: 64 }).notNull(),
  currentValue: real("current_value").notNull().default(0),
  thresholdGreen: real("threshold_green").notNull().default(0),
  thresholdAmber: real("threshold_amber").notNull().default(0),
  thresholdRed: real("threshold_red").notNull().default(0),
  unit: varchar("unit", { length: 32 }).notNull().default(""),
  status: varchar("status", { length: 16 }).notNull().default("GREEN"),
  trend: varchar("trend", { length: 16 }).notNull().default("STABLE"),
  capturedAt: ts("captured_at"),
});

// 11. Customer consent ledger v2 (append-only, hash-chained); existing consent_ledger is legacy minimal
export const consentLedgerV2 = pgTable("consent_ledger_v2", {
  id: idCol(),
  entryRef: varchar("entry_ref", { length: 64 }).notNull().unique(),
  customerRef: varchar("customer_ref", { length: 128 }).notNull(),
  // AS/CYBER/CLIENTDATA-ISOLATION Batch 1b — tenant isolation (nullable Phase-1; SET NOT NULL deferred to Phase-2).
  tenantId: text("tenant_id"),
  consentType: varchar("consent_type", { length: 64 }).notNull(),
  action: varchar("action", { length: 16 }).notNull(),
  scope: text("scope").notNull(),
  lawfulBasis: varchar("lawful_basis", { length: 64 }).notNull(),
  capturedBy: varchar("captured_by", { length: 128 }).notNull(),
  prevHash: varchar("prev_hash", { length: 128 }).notNull(),
  entryHash: varchar("entry_hash", { length: 128 }).notNull(),
  capturedAt: ts("captured_at"),
}).enableRLS();

// 12. PDF certificate exports
export const pdfCertificates = pgTable("pdf_certificates", {
  id: idCol(),
  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
  certRef: varchar("cert_ref", { length: 64 }).notNull().unique(),
  certType: varchar("cert_type", { length: 64 }).notNull(),
  subject: varchar("subject", { length: 256 }).notNull(),
  issuedTo: varchar("issued_to", { length: 256 }).notNull(),
  payloadHash: varchar("payload_hash", { length: 128 }).notNull(),
  signatureHex: varchar("signature_hex", { length: 256 }).notNull(),
  pdfBytes: integer("pdf_bytes").notNull().default(0),
  pdfBase64: text("pdf_base64").notNull(),
  generatedBy: varchar("generated_by", { length: 128 }).notNull(),
  generatedAt: ts("generated_at"),
}).enableRLS();

// 13. Custom regulator reports (saved queries)
export const customReports = pgTable("custom_reports", {
  id: idCol(),
  reportKey: varchar("report_key", { length: 128 }).notNull().unique(),
  reportName: varchar("report_name", { length: 256 }).notNull(),
  description: text("description").notNull().default(""),
  dataSource: varchar("data_source", { length: 64 }).notNull(),
  fieldsJson: jsonb("fields_json").notNull().default("[]"),
  filtersJson: jsonb("filters_json").notNull().default("{}"),
  scheduleCron: varchar("schedule_cron", { length: 64 }),
  lastRunAt: timestamp("last_run_at", { withTimezone: true }),
  lastRowCount: integer("last_row_count").notNull().default(0),
  createdBy: varchar("created_by", { length: 128 }).notNull(),
  createdAt: ts("created_at"),
});

// 14. Independent attestation portal
export const attestationUploads = pgTable("attestation_uploads", {
  id: idCol(),
  uploadRef: varchar("upload_ref", { length: 64 }).notNull().unique(),
  attestationType: varchar("attestation_type", { length: 64 }).notNull(),
  issuerName: varchar("issuer_name", { length: 256 }).notNull(),
  scope: text("scope").notNull(),
  issuedAt: timestamp("issued_at", { withTimezone: true }).notNull(),
  expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(),
  fileHashSha256: varchar("file_hash_sha256", { length: 128 }).notNull(),
  fileSizeBytes: integer("file_size_bytes").notNull().default(0),
  fileBase64: text("file_base64").notNull(),
  status: varchar("status", { length: 16 }).notNull().default("ACTIVE"),
  uploadedBy: varchar("uploaded_by", { length: 128 }).notNull(),
  uploadedAt: ts("uploaded_at"),
});

// ============================================================
// Insert schemas
// ============================================================
export const insertOwaspApiScanSchema = createInsertSchema(owaspApiScans).omit({ id: true, ranAt: true });
export type InsertOwaspApiScan = z.infer<typeof insertOwaspApiScanSchema>;
export type OwaspApiScan = typeof owaspApiScans.$inferSelect;

export const insertFuzzTestRunSchema = createInsertSchema(fuzzTestRuns).omit({ id: true, ranAt: true });
export type FuzzTestRun = typeof fuzzTestRuns.$inferSelect;

export const insertKeyRotationDrillSchema = createInsertSchema(keyRotationDrills).omit({ id: true, ranAt: true });
export type KeyRotationDrill = typeof keyRotationDrills.$inferSelect;

export const insertBackupRestoreDrillSchema = createInsertSchema(backupRestoreDrills).omit({ id: true, ranAt: true });
export type BackupRestoreDrill = typeof backupRestoreDrills.$inferSelect;

export const insertModelCardV2Schema = createInsertSchema(modelCardsV2).omit({ id: true, publishedAt: true });
export type ModelCardV2 = typeof modelCardsV2.$inferSelect;

export const insertBiasAuditRunSchema = createInsertSchema(biasAuditRuns).omit({ id: true, ranAt: true });
export type BiasAuditRun = typeof biasAuditRuns.$inferSelect;

export const insertModelRollbackDrillSchema = createInsertSchema(modelRollbackDrills).omit({ id: true, ranAt: true });
export type ModelRollbackDrill = typeof modelRollbackDrills.$inferSelect;

export const insertChaosRunSchema = createInsertSchema(chaosRuns).omit({ id: true, ranAt: true });
export type ChaosRun = typeof chaosRuns.$inferSelect;

export const insertVendorRiskSchema = createInsertSchema(vendorRiskRegister).omit({ id: true, addedAt: true });
export type VendorRisk = typeof vendorRiskRegister.$inferSelect;

export const insertKriMetricSchema = createInsertSchema(kriMetrics).omit({ id: true, capturedAt: true });
export type KriMetric = typeof kriMetrics.$inferSelect;

export const insertConsentLedgerV2Schema = createInsertSchema(consentLedgerV2).omit({ id: true, capturedAt: true, prevHash: true, entryHash: true, entryRef: true });
export type ConsentLedgerV2Entry = typeof consentLedgerV2.$inferSelect;

export const insertPdfCertificateSchema = createInsertSchema(pdfCertificates).omit({ id: true, generatedAt: true, tenantId: true });
export type PdfCertificate = typeof pdfCertificates.$inferSelect;

export const insertCustomReportSchema = createInsertSchema(customReports).omit({ id: true, createdAt: true, lastRunAt: true, lastRowCount: true });
export type CustomReport = typeof customReports.$inferSelect;

export const insertAttestationUploadSchema = createInsertSchema(attestationUploads).omit({ id: true, uploadedAt: true });
export type AttestationUpload = typeof attestationUploads.$inferSelect;