import { pgTable, serial, integer, text, numeric, timestamp, boolean } from "drizzle-orm/pg-core";
import { createInsertSchema, createSelectSchema } from "drizzle-zod";

export const threatForecastScores = pgTable("threat_forecast_scores", {
  id:                  serial("id").primaryKey(),
  institutionId:       integer("institution_id").notNull(),
  institutionName:     text("institution_name").notNull(),
  compositeScore:      numeric("composite_score", { precision: 5, scale: 2 }).notNull(),
  cveScore:            numeric("cve_score",        { precision: 5, scale: 2 }).notNull(),
  darkWebScore:        numeric("dark_web_score",   { precision: 5, scale: 2 }).notNull(),
  anomalyScore:        numeric("anomaly_score",    { precision: 5, scale: 2 }).notNull(),
  attackCoverageScore: numeric("attack_coverage_score", { precision: 5, scale: 2 }).notNull(),
  riskTier:            text("risk_tier").notNull(),
  isPremium:           boolean("is_premium").notNull().default(true),
  scoredAt:            timestamp("scored_at").notNull().defaultNow(),
  nextUpdateAt:        timestamp("next_update_at").notNull(),
  createdAt:           timestamp("created_at").notNull().defaultNow(),
});

export const insertThreatForecastScoreSchema = createInsertSchema(threatForecastScores);
export const selectThreatForecastScoreSchema = createSelectSchema(threatForecastScores);
export type InsertThreatForecastScore = typeof threatForecastScores.$inferInsert;
export type SelectThreatForecastScore = typeof threatForecastScores.$inferSelect;

export const cveFeedEntries = pgTable("cve_feed_entries", {
  id:              serial("id").primaryKey(),
  cveId:           text("cve_id").notNull().unique(),
  description:     text("description").notNull(),
  cvssScore:       numeric("cvss_score", { precision: 4, scale: 1 }).notNull(),
  severity:        text("severity").notNull(),
  affectedVendors: text("affected_vendors").notNull(),
  attackVector:    text("attack_vector").notNull(),
  attackComplexity:text("attack_complexity").notNull(),
  isExploited:     boolean("is_exploited").notNull().default(false),
  mitreIds:        text("mitre_ids").notNull().default("[]"),
  publishedAt:     timestamp("published_at").notNull(),
  ingestedAt:      timestamp("ingested_at").notNull().defaultNow(),
});

export const insertCveFeedEntrySchema = createInsertSchema(cveFeedEntries);
export const selectCveFeedEntrySchema = createSelectSchema(cveFeedEntries);
export type InsertCveFeedEntry = typeof cveFeedEntries.$inferInsert;
export type SelectCveFeedEntry = typeof cveFeedEntries.$inferSelect;

export const threatForecastSignals = pgTable("threat_forecast_signals", {
  id:              serial("id").primaryKey(),
  institutionId:   integer("institution_id").notNull(),
  source:          text("source").notNull(),
  signalType:      text("signal_type").notNull(),
  severity:        text("severity").notNull(),
  title:           text("title").notNull(),
  summary:         text("summary").notNull(),
  rawIndicators:   text("raw_indicators").notNull().default("[]"),
  isConfirmed:     boolean("is_confirmed").notNull().default(false),
  isAcknowledged:  boolean("is_acknowledged").notNull().default(false),
  detectedAt:      timestamp("detected_at").notNull().defaultNow(),
  expiresAt:       timestamp("expires_at"),
});

export const insertThreatForecastSignalSchema = createInsertSchema(threatForecastSignals);
export const selectThreatForecastSignalSchema = createSelectSchema(threatForecastSignals);
export type InsertThreatForecastSignal = typeof threatForecastSignals.$inferInsert;
export type SelectThreatForecastSignal = typeof threatForecastSignals.$inferSelect;

export const transactionAnomalyRecords = pgTable("transaction_anomaly_records", {
  id:              serial("id").primaryKey(),
  institutionId:   integer("institution_id").notNull(),
  windowStart:     timestamp("window_start").notNull(),
  windowEnd:       timestamp("window_end").notNull(),
  metricName:      text("metric_name").notNull(),
  observedValue:   numeric("observed_value", { precision: 14, scale: 2 }).notNull(),
  meanValue:       numeric("mean_value",     { precision: 14, scale: 2 }).notNull(),
  stdDev:          numeric("std_dev",        { precision: 14, scale: 2 }).notNull(),
  zScore:          numeric("z_score",        { precision: 6, scale: 3 }).notNull(),
  spcRule:         text("spc_rule").notNull(),
  isOutsideControl:boolean("is_outside_control").notNull().default(false),
  severity:        text("severity").notNull(),
  detectedAt:      timestamp("detected_at").notNull().defaultNow(),
  resolvedAt:      timestamp("resolved_at"),
});

export const insertTransactionAnomalyRecordSchema = createInsertSchema(transactionAnomalyRecords);
export const selectTransactionAnomalyRecordSchema = createSelectSchema(transactionAnomalyRecords);
export type InsertTransactionAnomalyRecord = typeof transactionAnomalyRecords.$inferInsert;
export type SelectTransactionAnomalyRecord = typeof transactionAnomalyRecords.$inferSelect;

export const auditChainEntries = pgTable("audit_chain_entries", {
  id: serial("id").primaryKey(),
  hash: text("hash").notNull(),
  previousHash: text("previous_hash").notNull(),
  action: text("action").notNull(),
  userId: text("user_id"),
  resource: text("resource").notNull(),
  details: text("details").notNull().default(""),
  // FU-024 (2026-05-12): persist the two payload fields not previously stored,
  // so verifyChainIntegrity can recompute the SHA-256 hash from row content
  // (not just validate previousHash linkage). Nullable because the rows
  // written before this column existed (the 26 pre-FU-024 rows) cannot be
  // reproduced; the walker performs linkage-only validation on those rows
  // and full linkage+content validation on rows where these are populated.
  // Forward-only design — consistent with the "chain integrity proof
  // applies forward from 2026-05-12 wiring date" disclosure in
  // BOU_NARRATIVE_DRAFTS.md.
  sourceAuditId: text("source_audit_id"),
  entryTimestamp: timestamp("entry_timestamp", { withTimezone: true }),
  createdAt: timestamp("created_at").notNull().defaultNow(),
});

export const insertAuditChainEntrySchema = createInsertSchema(auditChainEntries);
export type InsertAuditChainEntry = typeof auditChainEntries.$inferInsert;
export type SelectAuditChainEntry = typeof auditChainEntries.$inferSelect;

// M5 forgery countermeasure (2026-06-24): signed chain-head snapshots ("notarization").
// The chain is unkeyed SHA-256 (deliberately, so M4's export is independently recomputable) —
// which means a DB superuser could alter history and re-hash the whole chain forward, and it
// would re-verify as valid. A snapshot pins (entry_count, head_hash) at a moment in time and
// SIGNS it with a key held OUTSIDE the database (AUDIT_SNAPSHOT_SIGNING_KEY). A forger who
// re-hashes history changes the head_hash at that entry_count, so it no longer matches the
// signed snapshot — and they cannot forge a new snapshot without the key. System table: no
// tenant_id, no RLS (mirrors audit_chain_entries); created by boot DDL M52 (prod is code-only).
export const auditChainSnapshots = pgTable("audit_chain_snapshots", {
  id: serial("id").primaryKey(),
  entryCount: integer("entry_count").notNull(),
  headHash: text("head_hash").notNull(),
  algorithm: text("algorithm").notNull().default("HMAC-SHA256"),
  signature: text("signature").notNull(),
  createdAt: timestamp("created_at").notNull().defaultNow(),
});

export type InsertAuditChainSnapshot = typeof auditChainSnapshots.$inferInsert;
export type SelectAuditChainSnapshot = typeof auditChainSnapshots.$inferSelect;

// RFC-3161 external timestamp anchors (2026-07-07): the phase-2 external anchoring that
// SNAPSHOT_THREAT_BOUNDARY names. NOTE: the `timestamp_anchors` table is created at RUNTIME via
// CREATE TABLE IF NOT EXISTS in server/lib/rfc3161-anchor.ts (mirroring idempotency_keys), NOT via a
// Drizzle pgTable here — deliberately: a new pgTable that appears while a runtime-created orphan
// (idempotency_keys) exists makes `drizzle-kit push` prompt an ambiguous "create vs rename" and skip
// the create. Keeping this table out of the Drizzle schema keeps boot `drizzle-kit push` unambiguous.