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

// ========================================
// 10 Wave-3 regulator-pilot enhancement tables
// ========================================

// 1. Independent pentest evidence locker
export const pentestReports = pgTable("pentest_reports", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  vendorName: text("vendor_name").notNull(),
  reportTitle: text("report_title").notNull(),
  scope: text("scope").notNull(),
  findingsCritical: integer("findings_critical").notNull().default(0),
  findingsHigh: integer("findings_high").notNull().default(0),
  findingsMedium: integer("findings_medium").notNull().default(0),
  reportDate: text("report_date").notNull(),
  fileName: text("file_name").notNull(),
  fileSha256: text("file_sha256").notNull(),
  fileSizeBytes: integer("file_size_bytes").notNull(),
  signature: text("signature").notNull(),
  uploadedBy: text("uploaded_by").notNull(),
  uploadedAt: timestamp("uploaded_at").notNull().default(sql`NOW()`),
  notes: text("notes"),
});

// 2. Reproducible build manifest
export const buildManifests = pgTable("build_manifests", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  gitCommit: text("git_commit").notNull(),
  gitBranch: text("git_branch").notNull(),
  nodeVersion: text("node_version").notNull(),
  dependencyCount: integer("dependency_count").notNull(),
  dependencyHashJson: text("dependency_hash_json").notNull(),
  manifestSha256: text("manifest_sha256").notNull(),
  signature: text("signature").notNull(),
  capturedBy: text("captured_by").notNull(),
  capturedAt: timestamp("captured_at").notNull().default(sql`NOW()`),
});

// 3. AI Model Cards
export const modelCards = pgTable("model_cards", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  modelName: text("model_name").notNull(),
  modelVersion: text("model_version").notNull(),
  intendedUse: text("intended_use").notNull(),
  trainingDataSummary: text("training_data_summary").notNull(),
  performanceMetricsJson: text("performance_metrics_json").notNull(),
  fairnessMetricsJson: text("fairness_metrics_json").notNull(),
  knownLimitations: text("known_limitations").notNull(),
  ownerTeam: text("owner_team").notNull(),
  lastRetrainAt: text("last_retrain_at"),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
  updatedAt: timestamp("updated_at").notNull().default(sql`NOW()`),
});

// 4. Algorithmic decision appeals
export const aiAppeals = pgTable("ai_appeals", {
  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
  transactionRef: text("transaction_ref").notNull(),
  customerRef: text("customer_ref").notNull(),
  originalDecision: text("original_decision").notNull(),
  originalReason: text("original_reason").notNull(),
  appealReason: text("appeal_reason").notNull(),
  status: text("status").notNull().default("pending"),
  reviewerId: text("reviewer_id"),
  reviewerNotes: text("reviewer_notes"),
  slaDeadline: timestamp("sla_deadline").notNull(),
  decidedAt: timestamp("decided_at"),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
}).enableRLS();

// 5. Capacity / load drill
export const loadDrillRuns = pgTable("load_drill_runs", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  passed: integer("passed").notNull(),
  scenarioName: text("scenario_name").notNull(),
  targetTps: integer("target_tps").notNull(),
  achievedTps: real("achieved_tps").notNull(),
  totalRequests: integer("total_requests").notNull(),
  errorCount: integer("error_count").notNull(),
  errorRate: real("error_rate").notNull(),
  p50LatencyMs: real("p50_latency_ms").notNull(),
  p95LatencyMs: real("p95_latency_ms").notNull(),
  p99LatencyMs: real("p99_latency_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()`),
});

// 6. NTP / time-sync drift checks
export const ntpDriftChecks = pgTable("ntp_drift_checks", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  passed: integer("passed").notNull(),
  ntpSource: text("ntp_source").notNull(),
  // FU-017: epoch-ms must be bigint. JS Date.now() = ~1.78e12 today,
  // ~828× above int32 max (2.147e9). Declaring `integer` here previously
  // caused drizzle-kit's diff against the (correctly bigint) DB column
  // to issue ALTER ... SET DATA TYPE integer, which failed with PG 22003
  // (int8.c:1272 int84 narrowing cast) on every db:push. Mode "number"
  // returns a JS number — safe because epoch-ms stays well under 2^53.
  serverEpochMs: bigint("server_epoch_ms", { mode: "number" }).notNull(),
  ntpEpochMs: bigint("ntp_epoch_ms", { mode: "number" }).notNull(),
  driftMs: integer("drift_ms").notNull(),
  thresholdMs: integer("threshold_ms").notNull(),
  durationMs: integer("duration_ms").notNull(),
  triggeredBy: text("triggered_by").notNull(),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
});

// 7. Sub-processor / vendor register
export const subProcessors = pgTable("sub_processors", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  vendorName: text("vendor_name").notNull(),
  serviceCategory: text("service_category").notNull(),
  dataCategoriesAccessed: text("data_categories_accessed").notNull(),
  region: text("region").notNull(),
  contractStartDate: text("contract_start_date").notNull(),
  contractEndDate: text("contract_end_date"),
  pdpoStatus: text("pdpo_status").notNull(),
  dpaSignedAt: text("dpa_signed_at"),
  lastReviewAt: text("last_review_at"),
  notes: text("notes"),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
  updatedAt: timestamp("updated_at").notNull().default(sql`NOW()`),
});

// 8a. Consent ledger
export const consentLedger = pgTable("consent_ledger", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  customerRef: text("customer_ref").notNull(),
  // AS/CYBER/CLIENTDATA-ISOLATION Batch 1b — tenant isolation (nullable Phase-1; SET NOT NULL deferred to Phase-2).
  tenantId: text("tenant_id"),
  purpose: text("purpose").notNull(),
  scope: text("scope").notNull(),
  granted: integer("granted").notNull(),
  source: text("source").notNull(),
  evidenceLinks: text("evidence_links"),
  grantedAt: timestamp("granted_at").notNull().default(sql`NOW()`),
  revokedAt: timestamp("revoked_at"),
}).enableRLS();

// 8b. Right-to-erasure cascade drill
export const erasureCascadeRuns = pgTable("erasure_cascade_runs", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  passed: integer("passed").notNull(),
  syntheticCustomerRef: text("synthetic_customer_ref").notNull(),
  tablesScanned: integer("tables_scanned").notNull(),
  rowsDeleted: integer("rows_deleted").notNull(),
  residualRowsFound: integer("residual_rows_found").notNull(),
  durationMs: integer("duration_ms").notNull(),
  detailsJson: text("details_json").notNull(),
  triggeredBy: text("triggered_by").notNull(),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
});

// 9. Quarterly self-attestation reports
export const quarterlyAttestations = pgTable("quarterly_attestations", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  year: integer("year").notNull(),
  quarter: integer("quarter").notNull(),
  totalDrillsRun: integer("total_drills_run").notNull(),
  drillsPassed: integer("drills_passed").notNull(),
  drillsFailed: integer("drills_failed").notNull(),
  incidentCount: integer("incident_count").notNull(),
  evidencePackHash: text("evidence_pack_hash").notNull(),
  contentMd: text("content_md").notNull(),
  signature: text("signature").notNull(),
  generatedBy: text("generated_by").notNull(),
  generatedAt: timestamp("generated_at").notNull().default(sql`NOW()`),
});

export type PentestReport = typeof pentestReports.$inferSelect;
export type BuildManifest = typeof buildManifests.$inferSelect;
export type ModelCard = typeof modelCards.$inferSelect;
export type AiAppeal = typeof aiAppeals.$inferSelect;
export type LoadDrillRun = typeof loadDrillRuns.$inferSelect;
export type NtpDriftCheck = typeof ntpDriftChecks.$inferSelect;
export type SubProcessor = typeof subProcessors.$inferSelect;
export type ConsentLedgerEntry = typeof consentLedger.$inferSelect;
export type ErasureCascadeRun = typeof erasureCascadeRuns.$inferSelect;
export type QuarterlyAttestation = typeof quarterlyAttestations.$inferSelect;