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

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

/* 1. OpenAPI contract test runs */
export const openapiContractRuns = pgTable("openapi_contract_runs", {
  id: id(),
  runRef: varchar("run_ref").notNull().unique(),
  totalEndpoints: integer("total_endpoints").notNull(),
  endpointsTested: integer("endpoints_tested").notNull(),
  endpointsPassed: integer("endpoints_passed").notNull(),
  endpointsFailed: integer("endpoints_failed").notNull(),
  driftFindingsJson: jsonb("drift_findings_json").notNull(),
  passed: integer("passed").notNull(),
  ranBy: varchar("ran_by").notNull(),
  ranAt: ts("ran_at"),
});

/* 2. RBAC matrix tests */
export const rbacMatrixRuns = pgTable("rbac_matrix_runs", {
  id: id(),
  runRef: varchar("run_ref").notNull().unique(),
  totalCombos: integer("total_combos").notNull(),
  combosPassed: integer("combos_passed").notNull(),
  combosFailed: integer("combos_failed").notNull(),
  findingsJson: jsonb("findings_json").notNull(),
  passed: integer("passed").notNull(),
  ranBy: varchar("ran_by").notNull(),
  ranAt: ts("ran_at"),
});

/* 3. Cross-pillar smoke runs (the "examiner button") */
export const crossPillarRuns = pgTable("cross_pillar_runs", {
  id: id(),
  runRef: varchar("run_ref").notNull().unique(),
  modulesTotal: integer("modules_total").notNull(),
  modulesPassed: integer("modules_passed").notNull(),
  modulesFailed: integer("modules_failed").notNull(),
  resultsJson: jsonb("results_json").notNull(),
  evidenceHash: varchar("evidence_hash").notNull(),
  signatureHex: varchar("signature_hex").notNull(),
  ranBy: varchar("ran_by").notNull(),
  ranAt: ts("ran_at"),
});

/* 4. Negative-path security scans */
export const negativePathScans = pgTable("negative_path_scans", {
  id: id(),
  scanRef: varchar("scan_ref").notNull().unique(),
  scanType: varchar("scan_type").notNull(), // csrf|auth-bypass|sqli|xss|header-tampering|replay|all
  targetsTested: integer("targets_tested").notNull(),
  vulnsHigh: integer("vulns_high").notNull(),
  vulnsMedium: integer("vulns_medium").notNull(),
  vulnsLow: integer("vulns_low").notNull(),
  gradeLetter: varchar("grade_letter").notNull(),
  scoreOf100: integer("score_of_100").notNull(),
  findingsJson: jsonb("findings_json").notNull(),
  ranBy: varchar("ran_by").notNull(),
  ranAt: ts("ran_at"),
});

/* 5. WCAG 2.1 audits */
export const wcagAudits = pgTable("wcag_audits", {
  id: id(),
  auditRef: varchar("audit_ref").notNull().unique(),
  routePath: varchar("route_path").notNull(),
  wcagLevel: varchar("wcag_level").notNull(), // A|AA|AAA
  violationsCount: integer("violations_count").notNull(),
  passesCount: integer("passes_count").notNull(),
  incompleteCount: integer("incomplete_count").notNull(),
  scoreOf100: integer("score_of_100").notNull(),
  findingsJson: jsonb("findings_json").notNull(),
  ranBy: varchar("ran_by").notNull(),
  ranAt: ts("ran_at"),
});

/* 6. CycloneDX SBOM exports */
export const sbomExports = pgTable("sbom_exports", {
  id: id(),
  exportRef: varchar("export_ref").notNull().unique(),
  format: varchar("format").notNull(), // cyclonedx-1.5|spdx-2.3
  componentsCount: integer("components_count").notNull(),
  vulnerabilitiesCount: integer("vulnerabilities_count").notNull(),
  payloadHash: varchar("payload_hash").notNull(),
  payloadJson: text("payload_json").notNull(),
  exportedBy: varchar("exported_by").notNull(),
  exportedAt: ts("exported_at"),
});

/* 7. DPIA documents */
export const dpiaDocuments = pgTable("dpia_documents", {
  id: id(),
  dpiaRef: varchar("dpia_ref").notNull().unique(),
  systemName: varchar("system_name").notNull(),
  processor: varchar("processor").notNull(),
  controller: varchar("controller").notNull(),
  dataCategoriesJson: jsonb("data_categories_json").notNull(),
  lawfulBasis: varchar("lawful_basis").notNull(),
  risksJson: jsonb("risks_json").notNull(),
  mitigationsJson: jsonb("mitigations_json").notNull(),
  residualRiskScore: integer("residual_risk_score").notNull(),
  payloadHash: varchar("payload_hash").notNull(),
  generatedBy: varchar("generated_by").notNull(),
  generatedAt: ts("generated_at"),
});

/* 8. Tenant data portability bundles */
export const tenantExportBundles = pgTable("tenant_export_bundles", {
  id: id(),
  bundleRef: varchar("bundle_ref").notNull().unique(),
  tenantId: varchar("tenant_id").notNull(),
  recordCountsJson: jsonb("record_counts_json").notNull(),
  encryptedPayloadB64: text("encrypted_payload_b64").notNull(),
  payloadHash: varchar("payload_hash").notNull(),
  signatureHex: varchar("signature_hex").notNull(),
  generatedBy: varchar("generated_by").notNull(),
  generatedAt: ts("generated_at"),
  expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(),
}).enableRLS();

/* 9. Public status page incidents */
export const statusIncidents = pgTable("status_incidents", {
  id: id(),
  incidentRef: varchar("incident_ref").notNull().unique(),
  title: varchar("title").notNull(),
  severity: varchar("severity").notNull(), // operational|degraded|partial-outage|major-outage
  status: varchar("status").notNull(), // investigating|identified|monitoring|resolved
  affectedComponentsJson: jsonb("affected_components_json").notNull(),
  publicMessage: text("public_message").notNull(),
  startedAt: ts("started_at"),
  resolvedAt: timestamp("resolved_at", { withTimezone: true }),
  createdBy: varchar("created_by").notNull(),
});

/* 10. Regulator scenario simulations */
export const regulatorSimulations = pgTable("regulator_simulations", {
  id: id(),
  simRef: varchar("sim_ref").notNull().unique(),
  scenarioCode: varchar("scenario_code").notNull(),
  scenarioName: varchar("scenario_name").notNull(),
  stepsJson: jsonb("steps_json").notNull(),
  expectedOutcomesJson: jsonb("expected_outcomes_json").notNull(),
  actualOutcomesJson: jsonb("actual_outcomes_json").notNull(),
  passed: integer("passed").notNull(),
  evidenceHash: varchar("evidence_hash").notNull(),
  durationMs: integer("duration_ms").notNull(),
  ranBy: varchar("ran_by").notNull(),
  ranAt: ts("ran_at"),
});

/* 11. Examiner read-only tokens */
export const examinerTokens = pgTable("examiner_tokens", {
  id: id(),
  tokenHash: varchar("token_hash").notNull().unique(),
  examinerName: varchar("examiner_name").notNull(),
  examinerEmail: varchar("examiner_email").notNull(),
  // Client reads scopesJson.join(...) — typed so the payload is string[], not `unknown`.
  scopesJson: jsonb("scopes_json").$type<string[]>().notNull(),
  issuedBy: varchar("issued_by").notNull(),
  issuedAt: ts("issued_at"),
  expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(),
  revokedAt: timestamp("revoked_at", { withTimezone: true }),
  lastUsedAt: timestamp("last_used_at", { withTimezone: true }),
  useCount: integer("use_count").notNull().default(0),
});

/* 12. Continuous Pilot-Readiness Score (composite, daily) */
// One row of the readiness-score breakdown. Single source for the jsonb payload:
// computeReadinessScore() writes exactly this shape and the client renders it.
export interface ReadinessBreakdownEntry {
  module: string;
  weight: number;
  score: number;
  passing: boolean;
  /**
   * True only when this module's substrate actually had data to measure. A module scores 0 both
   * when it was measured at zero AND when nothing existed to measure; `score` alone cannot tell
   * those apart, so a coverage figure ("N of 11 measured") must be derived from this, never from
   * a score being non-zero. Rows written before 2026-07-17 predate this field and carry undefined
   * at runtime despite the type; only getLatestReadinessScore returns breakdownJson (the history
   * view omits it), so the gap closes on the next recompute.
   */
  assessed: boolean;
  details: string;
}
/**
 * How much of a readiness score rests on measured substrate. DERIVED from breakdownJson — never a
 * column — so it cannot disagree with the modules it summarises.
 *
 * `null` (not zeroes) means the row cannot say: rows written before `assessed` existed carry
 * undefined at runtime, and counting undefined as not-assessed would report "0 of 11 measured" for
 * a row that measured one. A fabricated-low is a fabricated-high wearing the opposite sign.
 */
export interface ReadinessCoverage {
  modulesAssessed: number;
  modulesTotal: number;
  weightAssessedPct: number;
}
export const readinessScores = pgTable("readiness_scores", {
  id: id(),
  scoreRef: varchar("score_ref").notNull().unique(),
  scoreOf100: integer("score_of_100").notNull(),
  modulesEvaluated: integer("modules_evaluated").notNull(),
  modulesPassing: integer("modules_passing").notNull(),
  // Typed payload so the client's breakdown table reads are checked, not `unknown`.
  breakdownJson: jsonb("breakdown_json").$type<ReadinessBreakdownEntry[]>().notNull(),
  capturedAt: ts("captured_at"),
});

export const insertOpenapiContractRunSchema = createInsertSchema(openapiContractRuns).omit({ id: true, ranAt: true });
export const insertRbacMatrixRunSchema = createInsertSchema(rbacMatrixRuns).omit({ id: true, ranAt: true });
export const insertCrossPillarRunSchema = createInsertSchema(crossPillarRuns).omit({ id: true, ranAt: true });
export const insertNegativePathScanSchema = createInsertSchema(negativePathScans).omit({ id: true, ranAt: true });
export const insertWcagAuditSchema = createInsertSchema(wcagAudits).omit({ id: true, ranAt: true });
export const insertSbomExportSchema = createInsertSchema(sbomExports).omit({ id: true, exportedAt: true });
export const insertDpiaDocumentSchema = createInsertSchema(dpiaDocuments).omit({ id: true, generatedAt: true });
export const insertTenantExportBundleSchema = createInsertSchema(tenantExportBundles).omit({ id: true, generatedAt: true });
export const insertStatusIncidentSchema = createInsertSchema(statusIncidents).omit({ id: true, startedAt: true });
export const insertRegulatorSimulationSchema = createInsertSchema(regulatorSimulations).omit({ id: true, ranAt: true });
export const insertExaminerTokenSchema = createInsertSchema(examinerTokens).omit({ id: true, issuedAt: true, useCount: true });
export const insertReadinessScoreSchema = createInsertSchema(readinessScores).omit({ id: true, capturedAt: true });

// ── Row types (#52 Tier-2 wave9) — one source, imported by the server (which returns
// them) and the client (useQuery<Jsonify<T>>). A field rename on either side is a compile error.
export type OpenapiContractRun = typeof openapiContractRuns.$inferSelect;
export type RbacMatrixRun = typeof rbacMatrixRuns.$inferSelect;
export type CrossPillarRun = typeof crossPillarRuns.$inferSelect;
export type NegativePathScan = typeof negativePathScans.$inferSelect;
export type WcagAudit = typeof wcagAudits.$inferSelect;
export type SbomExport = typeof sbomExports.$inferSelect;
export type DpiaDocument = typeof dpiaDocuments.$inferSelect;
export type TenantExportBundle = typeof tenantExportBundles.$inferSelect;
export type StatusIncident = typeof statusIncidents.$inferSelect;
export type ExaminerToken = typeof examinerTokens.$inferSelect;
export type ReadinessScore = typeof readinessScores.$inferSelect;

// View types that mirror the SERVER'S PROJECTIONS (not the full row), so the shared type
// tells the truth about what the endpoint actually returns:
//  - listExaminerTokens omits tokenHash (the credential verifier stays server-side).
export type ExaminerTokenView = Omit<ExaminerToken, "tokenHash">;
//  - getLatestReadinessScore returns the row PLUS a derived coverage figure. The score alone is
//    unreadable without it: 11/100 reads as failure when the truth is incompleteness, and a module
//    scores 0 whether it measured zero or measured nothing.
export type ReadinessScoreLatest = ReadinessScore & { coverage: ReadinessCoverage | null };
//  - listReadinessScoreHistory omits the breakdown payload (list view carries scalars only) but
//    DERIVES coverage from it before dropping it. Without this the history panel renders the score
//    curve unscoped — 30 beside 11 with no coverage on either reads as a collapse rather than a
//    disclosure, which is the one comparison the figure exists to prevent.
export type ReadinessScoreHistoryRow = Omit<ReadinessScore, "breakdownJson"> & {
  coverage: ReadinessCoverage | null;
};