/**
 * AEGIS CYBER — Persistence Schema for 9 In-Memory Modules
 * shared/schema-persistence.ts
 *
 * APPEND these exports to shared/schema.ts (or import from here).
 * Each section corresponds to one module being migrated from Map<> → PostgreSQL.
 *
 * Wire order in shared/schema.ts:
 *   1. Add all pgTable exports below
 *   2. Add corresponding Insert types
 *   3. Run: psql $DATABASE_URL -f schema-persistence-migration.sql
 */

import { sql } from "drizzle-orm";
import {
  pgTable, text, varchar, timestamp, boolean,
  integer, numeric, uniqueIndex, check,
  foreignKey, unique, index,
} from "drizzle-orm/pg-core";
import { createInsertSchema } from "drizzle-zod";

// ─────────────────────────────────────────────────────────────────────────────
// 1. REGULATOR GATEWAY — SARs, Explanation Dossiers, Regulator Sessions
// ─────────────────────────────────────────────────────────────────────────────

export const suspiciousActivityReports = pgTable("suspicious_activity_reports", {
  id: varchar("id").primaryKey(),
  tenantId: text("tenant_id").notNull(), // AS/CYBER/CLIENTDATA-ISOLATION Batch 1a — tenant isolation (RLS via TENANT_TABLES; boot DDL M31)
  referenceNumber: text("reference_number").notNull().unique(),
  status: text("status").notNull().default("DRAFT"),        // DRAFT|PENDING_REVIEW|SUBMITTED|ACKNOWLEDGED|REJECTED|ARCHIVED
  priority: text("priority").notNull().default("ROUTINE"),  // ROUTINE|EXPEDITED|URGENT
  subjectType: text("subject_type").notNull(),              // INDIVIDUAL|ENTITY
  subjectName: text("subject_name").notNull(),
  subjectId: text("subject_id").notNull(),
  activityType: text("activity_type").notNull(),
  activityDate: timestamp("activity_date").notNull(),
  amount: numeric("amount", { precision: 20, scale: 2 }),
  currency: text("currency").notNull().default("UGX"),
  description: text("description").notNull(),
  proofChainHash: text("proof_chain_hash").notNull(),
  kycDecisions: text("kyc_decisions").notNull().default("[]"),     // JSON string[]
  transactionIds: text("transaction_ids").notNull().default("[]"), // JSON string[]
  preparedBy: text("prepared_by").notNull(),
  reviewedBy: text("reviewed_by"),
  filingNotes: text("filing_notes"),
  fiaReference: text("fia_reference"),
  submittedAt: timestamp("submitted_at"),
  acknowledgedAt: timestamp("acknowledged_at"),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
  updatedAt: timestamp("updated_at").notNull().default(sql`NOW()`),
}).enableRLS();

export const explanationDossiers = pgTable("explanation_dossiers", {
  id: varchar("id").primaryKey(),
  type: text("type").notNull(),           // ONBOARDING_REJECTION|LOAN_REJECTION|TRANSACTION_BLOCK|ACCOUNT_FREEZE|KYC_FAILURE
  subjectId: text("subject_id").notNull(),
  subjectName: text("subject_name").notNull(),
  decisionDate: timestamp("decision_date").notNull(),
  decision: text("decision").notNull(),   // REJECTED|BLOCKED|FROZEN|ESCALATED
  decisionConfidence: numeric("decision_confidence", { precision: 4, scale: 3 }).notNull(),
  summary: text("summary").notNull(),
  detailedReasoning: text("detailed_reasoning").notNull(), // JSON string[]
  factorsConsidered: text("factors_considered").notNull(), // JSON ExplanationFactor[]
  appealProcess: text("appeal_process").notNull(),
  appealDeadline: timestamp("appeal_deadline").notNull(),
  contactInformation: text("contact_information").notNull(),
  dataRetentionDays: integer("data_retention_days").notNull().default(365),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
});

export const regulatorSessions = pgTable("regulator_sessions", {
  id: varchar("id").primaryKey(),
  regulatorId: text("regulator_id").notNull(),
  regulatorName: text("regulator_name").notNull(),
  organization: text("organization").notNull(), // BOU|FIA|NITA_U|URA
  accessLevel: text("access_level").notNull(),  // READ_ONLY|SANDBOX|AUDIT
  ipAddress: text("ip_address").notNull(),
  startedAt: timestamp("started_at").notNull().default(sql`NOW()`),
  expiresAt: timestamp("expires_at").notNull(),
  lastActivity: timestamp("last_activity").notNull().default(sql`NOW()`),
  actionsPerformed: text("actions_performed").notNull().default("[]"), // JSON RegulatorAction[]
  sandboxEnvironmentId: text("sandbox_environment_id"),
  isActive: boolean("is_active").notNull().default(true),
});

export const sandboxEnvironments = pgTable("sandbox_environments", {
  id: varchar("id").primaryKey(),
  name: text("name").notNull(),
  createdFor: text("created_for").notNull(),
  isActive: boolean("is_active").notNull().default(true),
  baselineSnapshot: text("baseline_snapshot").notNull(),
  modifiedEntities: text("modified_entities").notNull().default("[]"),
  allowedActions: text("allowed_actions").notNull().default("[]"),
  dataIsolation: boolean("data_isolation").notNull().default(true),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
  expiresAt: timestamp("expires_at").notNull(),
});

// ─────────────────────────────────────────────────────────────────────────────
// 2. SOAR PLAYBOOK — Playbook definitions (executions table already exists)
// ─────────────────────────────────────────────────────────────────────────────

export const soarPlaybooks = pgTable("soar_playbooks", {
  id: varchar("id").primaryKey(),
  name: text("name").notNull(),
  description: text("description").notNull(),
  trigger: text("trigger").notNull(),        // PlaybookTrigger enum value
  enabled: boolean("enabled").notNull().default(true),
  priority: integer("priority").notNull().default(5),
  conditions: text("conditions"),            // JSON { severity?, source?, tags?, riskScore? }
  actions: text("actions").notNull(),        // JSON PlaybookAction[]
  executionCount: integer("execution_count").notNull().default(0),
  lastExecuted: timestamp("last_executed"),
  averageExecutionTimeMs: integer("average_execution_time_ms").notNull().default(0),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
  updatedAt: timestamp("updated_at").notNull().default(sql`NOW()`),
  tenantId: text("tenant_id"),
}).enableRLS();

// soar_executions already exists — we extend it with action_results
export const soarActionResults = pgTable("soar_action_results", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  executionId: varchar("execution_id").notNull(), // → soar_executions.id
  actionId: text("action_id").notNull(),
  actionName: text("action_name").notNull(),
  status: text("status").notNull(),   // pending|running|success|failed|skipped
  startTime: timestamp("start_time"),
  endTime: timestamp("end_time"),
  output: text("output"),             // JSON
  error: text("error"),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
});

// ─────────────────────────────────────────────────────────────────────────────
// 3. JIT ACCESS — Grants and Policies
// ─────────────────────────────────────────────────────────────────────────────

export const jitPolicies = pgTable("jit_policies", {
  id: varchar("id").primaryKey(),
  name: text("name").notNull(),
  description: text("description").notNull(),
  targetRole: text("target_role").notNull(),
  maxDurationMinutes: integer("max_duration_minutes").notNull().default(60),
  requiresApproval: boolean("requires_approval").notNull().default(true),
  approverRoles: text("approver_roles").notNull().default("[]"),    // JSON string[]
  allowedResources: text("allowed_resources").notNull().default("[]"), // JSON string[]
  allowedActions: text("allowed_actions").notNull().default("[]"),
  maxActionsPerGrant: integer("max_actions_per_grant"),
  cooldownPeriodMinutes: integer("cooldown_period_minutes").notNull().default(60),
  enabled: boolean("enabled").notNull().default(true),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
  updatedAt: timestamp("updated_at").notNull().default(sql`NOW()`),
});

export const jitGrants = pgTable("jit_grants", {
  id: varchar("id").primaryKey(),
  userId: text("user_id").notNull(),
  username: text("username").notNull(),
  originalRole: text("original_role").notNull(),
  elevatedRole: text("elevated_role").notNull(),
  reason: text("reason").notNull(),
  approvedBy: text("approved_by"),
  status: text("status").notNull().default("pending"), // pending|active|expired|revoked
  policyId: text("policy_id"),
  resources: text("resources").notNull().default("[]"),  // JSON string[]
  actions: text("actions").notNull().default("[]"),
  maxActions: integer("max_actions"),
  actionsUsed: integer("actions_used").notNull().default(0),
  auditLog: text("audit_log").notNull().default("[]"),  // JSON JITAuditEntry[]
  requestedAt: timestamp("requested_at").notNull().default(sql`NOW()`),
  grantedAt: timestamp("granted_at"),
  expiresAt: timestamp("expires_at").notNull(),
  revokedAt: timestamp("revoked_at"),
  revokedBy: text("revoked_by"),
  tenantId: text("tenant_id"),
}).enableRLS();

// ─────────────────────────────────────────────────────────────────────────────
// 4. PRIVILEGED ACCESS MANAGEMENT — Accounts, Requests, Sessions
// ─────────────────────────────────────────────────────────────────────────────

export const pamAccounts = pgTable("pam_accounts", {
  id: varchar("id").primaryKey(),
  name: text("name").notNull(),
  type: text("type").notNull(),             // system|database|application|network|cloud
  privilegeLevel: text("privilege_level").notNull(), // LEVEL_1 through LEVEL_5
  description: text("description"),
  owner: text("owner").notNull(),
  rotationPolicyDays: integer("rotation_policy_days").notNull().default(90),
  lastRotated: timestamp("last_rotated"),
  nextRotation: timestamp("next_rotation"),
  requiresApproval: boolean("requires_approval").notNull().default(true),
  approvers: text("approvers").notNull().default("[]"),         // JSON string[]
  maxSessionDurationMin: integer("max_session_duration_min").notNull().default(60),
  allowedTimeWindows: text("allowed_time_windows"),             // JSON
  mfaRequired: boolean("mfa_required").notNull().default(true),
  justificationRequired: boolean("justification_required").notNull().default(true),
  checkoutCount: integer("checkout_count").notNull().default(0),
  lastAccessed: timestamp("last_accessed"),
  status: text("status").notNull().default("active"),           // active|locked|disabled
  tenantId: text("tenant_id"),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
}).enableRLS();

export const pamRequests = pgTable("pam_requests", {
  id: varchar("id").primaryKey(),
  accountId: varchar("account_id").notNull(),
  accountName: text("account_name").notNull(),
  requesterId: text("requester_id").notNull(),
  requesterName: text("requester_name").notNull(),
  justification: text("justification").notNull(),
  requestedDurationMin: integer("requested_duration_min").notNull(),
  status: text("status").notNull().default("pending"), // pending|approved|rejected|expired|cancelled
  riskScore: numeric("risk_score", { precision: 4, scale: 3 }), // durable scale 0.000–1.000 (PAM in-memory uses 0–100; savePamRequest normalizes ÷100 to match the platform-wide numeric(4,3) convention — any future DB-hydrate must ×100)
  reviewedBy: text("reviewed_by"),
  reviewedAt: timestamp("reviewed_at"),
  reviewNotes: text("review_notes"),
  expiresAt: timestamp("expires_at"),
  sessionId: varchar("session_id"),
  tenantId: text("tenant_id"),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
}).enableRLS();

export const pamSessions = pgTable("pam_sessions", {
  id: varchar("id").primaryKey(),
  accessRequestId: varchar("access_request_id").notNull(),
  accountId: varchar("account_id").notNull(),
  accountName: text("account_name").notNull(),
  userId: text("user_id").notNull(),
  userName: text("user_name").notNull(),
  startTime: timestamp("start_time").notNull().default(sql`NOW()`),
  endTime: timestamp("end_time"),
  durationMinutes: integer("duration_minutes"),
  status: text("status").notNull().default("active"), // active|completed|terminated|timeout
  commands: text("commands").notNull().default("[]"),     // JSON SessionCommand[]
  riskEvents: text("risk_events").notNull().default("[]"), // JSON riskEvents[]
  recordingUrl: text("recording_url"),
  tenantId: text("tenant_id"),
}).enableRLS();

// ─────────────────────────────────────────────────────────────────────────────
// 5. THREAT INTELLIGENCE — Feeds, Indicators, Matches
// ─────────────────────────────────────────────────────────────────────────────

export const threatFeeds = pgTable("threat_feeds", {
  id: varchar("id").primaryKey(),
  tenantId: text("tenant_id"), // AS/PLATFORM/2026/008 Chunk B — tenant isolation
  name: text("name").notNull(),
  type: text("type").notNull(), // misp|otx|virustotal|abuseipdb|custom
  url: text("url").notNull(),
  encryptedApiKey: text("encrypted_api_key"),
  enabled: boolean("enabled").notNull().default(true),
  refreshIntervalMin: integer("refresh_interval_min").notNull().default(60),
  lastSync: timestamp("last_sync"),
  indicatorCount: integer("indicator_count").notNull().default(0),
  status: text("status").notNull().default("active"), // active|error|syncing|disabled
  errorMessage: text("error_message"),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
  updatedAt: timestamp("updated_at").notNull().default(sql`NOW()`),
}).enableRLS();

export const threatIndicators = pgTable("threat_indicators", {
  id: varchar("id").primaryKey(),
  tenantId: text("tenant_id"), // AS/PLATFORM/2026/008 Chunk B — tenant isolation
  feedId: varchar("feed_id"),
  type: text("type").notNull(), // ip|domain|hash|email|url|cve
  value: text("value").notNull(),
  severity: text("severity").notNull().default("medium"),
  source: text("source").notNull(),
  confidence: integer("confidence").notNull().default(50), // 0–100
  tags: text("tags").notNull().default("[]"),              // JSON string[]
  description: text("description"),
  metadata: text("metadata"),                              // JSON
  firstSeen: timestamp("first_seen").notNull().default(sql`NOW()`),
  lastSeen: timestamp("last_seen").notNull().default(sql`NOW()`),
  expiresAt: timestamp("expires_at"),
  matchCount: integer("match_count").notNull().default(0),
  isActive: boolean("is_active").notNull().default(true),
}).enableRLS();

export const threatMatches = pgTable("threat_matches", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  indicatorId: varchar("indicator_id").notNull(),
  matchedValue: text("matched_value").notNull(),
  matchedContext: text("matched_context").notNull(),
  riskScore: numeric("risk_score", { precision: 4, scale: 3 }).notNull(),
  tenantId: text("tenant_id"),
  matchedAt: timestamp("matched_at").notNull().default(sql`NOW()`),
}).enableRLS();

// ─────────────────────────────────────────────────────────────────────────────
// 6. KYC AGENT ENGINE — Customers, Decisions, Life Events
// ─────────────────────────────────────────────────────────────────────────────

export const kycCustomers = pgTable("kyc_customers", {
  id: varchar("id").primaryKey(),
  // T004a (ii) — .unique() removed: global unique was wrong under multi-tenancy (architect D-Q1:
  // same person legitimately appears in two tenants; global constraint would reject the second
  // onboarding). Per-tenant uniqueness is enforced cryptographically by the unique index on
  // national_id_hmac alone — same individual in two tenants produces different HMAC hashes
  // (B-1 nested construction) so no collision on the index.
  nationalId:    text("national_id").notNull(),
  // T004a (ii) — HMAC-SHA256 lookup key derived via B-1 nested construction.
  // Unique index enforces per-tenant uniqueness; also acts as a tripwire: a broken
  // (non-isolating) HMAC construction would produce duplicate hashes across tenants
  // and fail loudly at onboarding time rather than leaking silently at query time.
  nationalIdHmac: text("national_id_hmac"),
  fullName: text("full_name").notNull(),
  dateOfBirth: text("date_of_birth").notNull(),
  phoneNumber: text("phone_number").notNull(),
  email: text("email"),
  address: text("address"),
  photoUrl: text("photo_url"),
  status: text("status").notNull().default("PENDING"), // PENDING|IN_PROGRESS|APPROVED|REJECTED|ESCALATED|REVIEW_REQUIRED
  riskTier: text("risk_tier").notNull().default("LOW"), // LOW|MEDIUM|HIGH|PEP|SANCTIONS
  tenantId: text("tenant_id"),
  onboardedAt: timestamp("onboarded_at"),
  lastVerifiedAt: timestamp("last_verified_at"),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
  updatedAt: timestamp("updated_at").notNull().default(sql`NOW()`),
}, (table) => ({
  // Unique index on national_id_hmac alone achieves per-tenant uniqueness cryptographically.
  // PostgreSQL UNIQUE ignores NULL values — existing rows (without HMAC yet) are allowed
  // through without collision until T004b migration populates the column for old rows.
  nationalIdHmacUnique: uniqueIndex("kyc_national_id_hmac_unique").on(table.nationalIdHmac),
})).enableRLS();

export const kycAgentDecisions = pgTable("kyc_agent_decisions", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  tenantId: text("tenant_id").notNull(), // AS/CYBER/CLIENTDATA-ISOLATION Batch 1a — tenant isolation (RLS via TENANT_TABLES; boot DDL M32)
  customerId: varchar("customer_id").notNull(),
  agentId: text("agent_id").notNull(),
  agentType: text("agent_type").notNull(), // INGESTION|VERIFICATION|LIVENESS|CRITIC
  decision: text("decision").notNull(),   // PASS|FAIL|REVIEW
  confidence: numeric("confidence", { precision: 4, scale: 3 }).notNull(),
  evidence: text("evidence").notNull(),
  signatureHash: text("signature_hash").notNull(),
  decidedAt: timestamp("decided_at").notNull().default(sql`NOW()`),
}).enableRLS();

export const kycLifeEvents = pgTable("kyc_life_events", {
  id: varchar("id").primaryKey(),
  tenantId: text("tenant_id").notNull(), // AS/CYBER/CLIENTDATA-ISOLATION Batch 1a — tenant isolation (RLS via TENANT_TABLES; boot DDL M33)
  customerId: varchar("customer_id").notNull(),
  eventType: text("event_type").notNull(), // ADDRESS_CHANGE|LARGE_DEPOSIT|PEP_STATUS|SANCTIONS_HIT|SIM_SWAP|REGION_CHANGE
  details: text("details").notNull(),
  triggerReVerification: boolean("trigger_re_verification").notNull().default(false),
  detectedAt: timestamp("detected_at").notNull().default(sql`NOW()`),
}).enableRLS();

// ─────────────────────────────────────────────────────────────────────────────
// 6b. KYC v1 FEATURE 3 — DECISION ENGINE (spec AS/KYC/2026/002)
//
// Two purpose-built, tenant-isolated tables. Their RLS tenant_isolation policy is
// created from boot via TENANT_TABLES membership (server/lib/rls-init.ts) and the
// tables themselves are created idempotently by applyBootSchemaMigrations() M8/M9.
// Deliberately NOT bolted onto kyc_agent_decisions (internal AI-pipeline table:
// PASS/FAIL/REVIEW only, no approver). NOTE (AS/CYBER/CLIENTDATA-ISOLATION
// Batch 1a, 2026-06-19, Rule 8): kyc_agent_decisions is now itself tenant-
// isolated (tenant_id NOT NULL + RLS via TENANT_TABLES) — the earlier "no
// tenant_id, no RLS" note is superseded. It remains a SEPARATE table from this
// decision-outcome table by PURPOSE (AI-pipeline trace vs maker-checker
// decision), not by isolation posture (both are now per-tenant isolated).
//
// Decision model (spec §3/§4 + operator refinement): disposition is one of three
// EXCLUSIVE outcomes (APPROVE / REJECT / ESCALATE_TO_EDD); STR is an INDEPENDENT
// co-occurring flag (str_flag), NOT a fifth disposition — Feature 3 RECORDS "STR
// required", it does not file to the FIA (spec §6). The exact STR shape is settled
// with the architect at the logic stage; str_flag + str_reason keep it open now.
//
// SCHEMA STAGE ONLY — no decision logic, routes, or finalizer here. Architect
// Rule 9 review of this schema diff precedes commit/publish (tenant-isolation +
// security-table schema surfaces).
// ─────────────────────────────────────────────────────────────────────────────

export const kycDecisionOutcomes = pgTable("kyc_decision_outcomes", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  tenantId: text("tenant_id").notNull(),
  customerId: varchar("customer_id").notNull(),
  disposition: text("disposition").notNull(),                    // APPROVE|REJECT|ESCALATE_TO_EDD (exclusive)
  strFlag: boolean("str_flag").notNull().default(false),         // independent co-occurring STR flag
  strReason: text("str_reason"),                                 // nullable; exact shape settled at logic stage
  state: text("state").notNull().default("PROPOSED"),            // PROPOSED|PENDING_SENIOR_APPROVAL|FINAL
  requiresSeniorApproval: boolean("requires_senior_approval").notNull().default(false),
  riskTier: text("risk_tier"),                                   // LOW|MEDIUM|HIGH|PEP|SANCTIONS (derived)
  signalsSnapshot: text("signals_snapshot").notNull(),           // JSON snapshot of F1/F2 signals (no raw PII)
  signalsHash: text("signals_hash").notNull(),                   // SHA-256 of fresh signals; approval must match
  explanation: text("explanation").notNull(),                    // which deterministic rule fired
  decidedByUserId: varchar("decided_by_user_id").notNull(),
  decidedByRole: text("decided_by_role").notNull(),
  auditChainEntryId: integer("audit_chain_entry_id"),            // audit linkage (nullable)
  decidedAt: timestamp("decided_at").notNull().default(sql`NOW()`),
  updatedAt: timestamp("updated_at").notNull().default(sql`NOW()`),
}, (t) => [
  check("kdo_disposition_valid", sql`${t.disposition} IN ('APPROVE','REJECT','ESCALATE_TO_EDD')`),
  check("kdo_state_valid", sql`${t.state} IN ('PROPOSED','PENDING_SENIOR_APPROVAL','FINAL')`),
]).enableRLS();

export const kycDecisionApprovals = pgTable("kyc_decision_approvals", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  tenantId: text("tenant_id").notNull(),
  decisionId: varchar("decision_id").notNull(), // FK → kyc_decision_outcomes(id): modelled in Drizzle as kda_decision_id_fk (AS/PLATFORM/2026/008 Part B, see callback below) AND currently created by boot DDL (rls-init.ts M9); boot DDL is carved to RLS-only in the Part-A-gated step (T5)
  customerId: varchar("customer_id").notNull(),
  approverUserId: varchar("approver_user_id").notNull(),
  approverRole: text("approver_role").notNull(),                 // risk_manager|admin (senior compliance authority)
  approvalType: text("approval_type").notNull().default("SENIOR_AUTHORITY"),
  signalsHash: text("signals_hash").notNull(),                   // must match the decision's fresh signals_hash
  reason: text("reason"),                                        // nullable
  auditChainEntryId: integer("audit_chain_entry_id"),            // audit linkage (nullable)
  approvedAt: timestamp("approved_at").notNull().default(sql`NOW()`),
}, (t) => [
  uniqueIndex("kda_tenant_decision_unique").on(t.tenantId, t.decisionId),
  check("kda_approver_role_valid", sql`${t.approverRole} IN ('risk_manager','admin')`),
  // AS/PLATFORM/2026/008 Part B — single-col FK under Drizzle ownership (decision_id →
  // kyc_decision_outcomes.id); name+definition-identical to the live boot-DDL object.
  foreignKey({
    columns: [t.decisionId],
    foreignColumns: [kycDecisionOutcomes.id],
    name: "kda_decision_id_fk",
  }),
]).enableRLS();

export const insertKycDecisionOutcomeSchema = createInsertSchema(kycDecisionOutcomes).omit({ id: true, decidedAt: true, updatedAt: true });
export const insertKycDecisionApprovalSchema = createInsertSchema(kycDecisionApprovals).omit({ id: true, approvedAt: true });
export type KycDecisionOutcome = typeof kycDecisionOutcomes.$inferSelect;
export type KycDecisionApproval = typeof kycDecisionApprovals.$inferSelect;

// KYC v1 Feature 1 — MANUAL operator identity attestation (Build B: manual-identity path).
// A compliance officer reviews the customer's documents and records a determination; the
// ManualAttestationIdentityAdapter reads the LATEST row per (tenant, customer) and returns
// it as the identity signal. HONEST PROVENANCE: this is a HUMAN attestation, distinct from
// an automated national-register (NIRA) match — the adapter stamps method=MANUAL_ATTESTATION
// so no surface can render it as automated verification. Tenant-scoped (RLS via TENANT_TABLES);
// no raw PII (national id / DOB) is stored here — only the determination + operator + a note.
export const kycIdentityAttestations = pgTable("kyc_identity_attestations", {
  id:               varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  tenantId:         text("tenant_id").notNull(),
  customerId:       varchar("customer_id").notNull(),
  status:           text("status").notNull(),            // VERIFIED | NOT_VERIFIED | REVIEW_REQUIRED (operator's determination)
  evidenceRef:      text("evidence_ref"),                // nullable — a document/case reference
  notes:            text("notes"),                        // nullable — the operator's honest note
  attestedByUserId: varchar("attested_by_user_id").notNull(),
  attestedByRole:   text("attested_by_role").notNull(),  // admin | risk_manager (privileged reviewer)
  attestedAt:       timestamp("attested_at").notNull().default(sql`NOW()`),
  createdAt:        timestamp("created_at").notNull().default(sql`NOW()`),
}, (t) => [
  // A manual attestation records a REAL human determination — only the honest status set.
  check("kia_status_valid", sql`${t.status} IN ('VERIFIED','NOT_VERIFIED','REVIEW_REQUIRED')`),
  check("kia_role_valid", sql`${t.attestedByRole} IN ('admin','risk_manager')`),
  // Latest-per-customer lookup (the adapter reads the most recent attestation).
  index("kia_tenant_customer_idx").on(t.tenantId, t.customerId, t.attestedAt.desc().nullsFirst()),
]).enableRLS();
export const insertKycIdentityAttestationSchema = createInsertSchema(kycIdentityAttestations).omit({ id: true, createdAt: true, attestedAt: true });
export type KycIdentityAttestation = typeof kycIdentityAttestations.$inferSelect;

// ── A (kyc_screening_results) — SCREENS-AND-FORGETS FIX ─────────────────────────────────────
// The platform screens and, until now, forgot: the audit log recorded THAT a screen happened and
// its status, but not the score, the list version, or WHAT matched — so the examiner's real
// question ("when did you last screen this customer, against which list version, and what
// specifically matched?") was unanswerable. This table is the durable answer. INSERT-ONLY
// (immutable by discipline — no update/delete path in storage). records-never-decides: recording a
// screen NEVER writes customer status/risk_tier. The matched-entry columns are a CONTENT SNAPSHOT,
// deliberately NOT an FK: sanctions_entries truncate-reloads on every daily ingest (delete-all +
// bulk-insert), so the referenced row is gone tomorrow and OFAC may delist the entity entirely —
// the snapshot is the only record that survives to answer "what did we see, and when".
//
// CREATED BY BOOT DDL (rls-init M64), NOT by drizzle push — matching the sibling purpose-built
// tenant tables (kyc_decision_outcomes / kyc_decision_approvals / kyc_identity_attestations). This
// was NOT the first plan: push-only was tried first (on the reasoning that TENANT_TABLES enrollment
// would fail loud if push skipped the create). The 2026-07-09 restart-survival gate refuted it —
// `drizzle-kit push --force` cannot create this table in this orphan-laden DB. It first HUNG on an
// interactive create-vs-rename prompt (drizzle paired it against the column-similar runtime orphan
// timestamp_anchors; fixed by excluding the runtime orphans in drizzle.config), then, past that,
// SILENTLY SKIPPED the create (boot then died at the schema-conformance check: tenant_id absent).
// So: this pgTable is the source of truth for drizzle QUERIES + types; rls-init M64 is the source of
// truth for the table's EXISTENCE. The columns below MUST be kept in sync with the M64 raw DDL —
// that duplication is the known, accepted cost of the boot-DDL pattern every sibling table pays.
export const kycScreeningResults = pgTable("kyc_screening_results", {
  id:              varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  tenantId:        text("tenant_id").notNull(),
  customerId:      varchar("customer_id").notNull(),
  screenType:      text("screen_type").notNull(),        // SANCTIONS | PEP
  status:          text("status").notNull(),             // CLEAR|POTENTIAL_MATCH|CONFIRMED_MATCH|PROVIDER_ERROR|NOT_CONFIGURED
  providerId:      text("provider_id").notNull(),        // ofac_sdn | none
  // numeric (exact), NOT real/float4 — a 0.941 stored as float4 reads back 0.9409999847 (a display
  // lie to an adjudicator). node-postgres returns numeric as an exact string. Null for CLEAR / indeterminate.
  score:           numeric("score", { precision: 4, scale: 3 }),
  listSource:      text("list_source"),                  // nullable — e.g. OFAC_SDN
  listVersionHash: text("list_version_hash"),            // nullable — the list VERSION screened against
  listFetchedAt:   timestamp("list_fetched_at"),         // nullable — that list version's freshness stamp
  matchedEntNum:   text("matched_ent_num"),              // nullable SNAPSHOT — OFAC ent_num as recorded (no FK)
  matchedProgram:  text("matched_program"),              // nullable SNAPSHOT — e.g. SDGT
  matchedName:     text("matched_name"),                 // nullable SNAPSHOT — the matched list entry name
  evidenceSummary: text("evidence_summary").notNull(),   // the adapter's honest summary — never empty (NOT_CONFIGURED carries its real reason)
  screenedAt:      timestamp("screened_at").notNull(),   // when the screen ran (adapter checkedAt)
  createdAt:       timestamp("created_at").notNull().default(sql`NOW()`),
}, (t) => [
  check("ksr_screen_type_valid", sql`${t.screenType} IN ('SANCTIONS','PEP')`),
  check("ksr_status_valid", sql`${t.status} IN ('CLEAR','POTENTIAL_MATCH','CONFIRMED_MATCH','PROVIDER_ERROR','NOT_CONFIGURED')`),
  // Latest-per-customer + full history lookup (both reads this index serves).
  index("ksr_tenant_customer_idx").on(t.tenantId, t.customerId, t.screenedAt.desc().nullsFirst()),
]).enableRLS();
export const insertKycScreeningResultSchema = createInsertSchema(kycScreeningResults).omit({ id: true, createdAt: true });
export type KycScreeningResult = typeof kycScreeningResults.$inferSelect;
export type InsertKycScreeningResult = typeof kycScreeningResults.$inferInsert;

// ── APT_MAKE_REAL Layer 1 — REAL EDR telemetry ingestion (APT_MAKE_REAL_SCOPE.md) ──────────
// Replaces the synthetic in-memory ebpf-edr arrays with real events from a bank's SIEM/syslog
// forwarder (or agents). HONEST-FLOOR: every row originates from a REAL ingested event — NEVER
// Math.random / synthesized. Tenant-isolated (RLS via TENANT_TABLES). The dashboard reads these;
// tabs with no real backing yet (probes/campaigns/correlations/MITRE/central-bank-alerts) are
// honestly marked roadmap until Layer 2 detection lands.
export const edrEndpoints = pgTable("edr_endpoints", {
  id:          varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  tenantId:    text("tenant_id").notNull(),
  hostname:    text("hostname").notNull(),          // from the real event source
  ipAddress:   text("ip_address"),
  os:          text("os"),
  agentId:     text("agent_id"),                    // external agent/source id (nullable)
  status:      text("status").notNull().default("active"), // active | isolated | offline
  lastSeenAt:  timestamp("last_seen_at").notNull().default(sql`NOW()`),
  firstSeenAt: timestamp("first_seen_at").notNull().default(sql`NOW()`),
}, (t) => [
  check("edr_ep_status_valid", sql`${t.status} IN ('active','isolated','offline')`),
  uniqueIndex("edr_ep_tenant_host_uniq").on(t.tenantId, t.hostname),
  index("edr_ep_tenant_seen_idx").on(t.tenantId, t.lastSeenAt.desc().nullsFirst()),
]).enableRLS();

export const edrEvents = pgTable("edr_events", {
  id:          varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  tenantId:    text("tenant_id").notNull(),
  endpointId:  varchar("endpoint_id"),              // nullable — resolved from the event host
  source:      text("source").notNull(),            // siem | syslog | agent | cef
  eventType:   text("event_type").notNull(),
  severity:    text("severity").notNull().default("info"), // critical|high|medium|low|info
  message:     text("message").notNull(),
  rawJson:     text("raw_json"),                     // the normalized event payload as received
  mitreTechnique: text("mitre_technique"),           // Layer 2 detection populates
  observedAt:  timestamp("observed_at").notNull(),   // when the event actually happened (from the source)
  ingestedAt:  timestamp("ingested_at").notNull().default(sql`NOW()`),
}, (t) => [
  check("edr_ev_severity_valid", sql`${t.severity} IN ('critical','high','medium','low','info')`),
  index("edr_ev_tenant_observed_idx").on(t.tenantId, t.observedAt.desc().nullsFirst()),
  index("edr_ev_tenant_endpoint_idx").on(t.tenantId, t.endpointId),
]).enableRLS();

export const edrAlerts = pgTable("edr_alerts", {   // Layer 2 detection populates; empty until then
  id:          varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  tenantId:    text("tenant_id").notNull(),
  eventId:     varchar("event_id"),
  ruleId:      text("rule_id").notNull(),
  title:       text("title").notNull(),
  severity:    text("severity").notNull().default("medium"),
  mitreTechnique: text("mitre_technique"),
  status:      text("status").notNull().default("open"), // open|acknowledged|closed
  createdAt:   timestamp("created_at").notNull().default(sql`NOW()`),
}, (t) => [
  index("edr_al_tenant_created_idx").on(t.tenantId, t.createdAt.desc().nullsFirst()),
]).enableRLS();

export const edrIsolationActions = pgTable("edr_isolation_actions", { // Layer 3 wires a real effector
  id:          varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  tenantId:    text("tenant_id").notNull(),
  endpointId:  varchar("endpoint_id").notNull(),
  action:      text("action").notNull(),            // isolate | release
  status:      text("status").notNull().default("SIMULATED"), // SIMULATED until a real agent effector lands
  requestedByUserId: varchar("requested_by_user_id"),
  detail:      text("detail"),
  requestedAt: timestamp("requested_at").notNull().default(sql`NOW()`),
}, (t) => [
  check("edr_iso_status_valid", sql`${t.status} IN ('SIMULATED','EXECUTED','FAILED')`),
  index("edr_iso_tenant_idx").on(t.tenantId, t.requestedAt.desc().nullsFirst()),
]).enableRLS();

export const insertEdrEventSchema = createInsertSchema(edrEvents).omit({ id: true, ingestedAt: true });
export type EdrEndpoint = typeof edrEndpoints.$inferSelect;
export type EdrEvent = typeof edrEvents.$inferSelect;
export type EdrAlert = typeof edrAlerts.$inferSelect;
export type EdrIsolationAction = typeof edrIsolationActions.$inferSelect;

// NOTE: decision_scoring_outputs (the "M7" table) is ALREADY fully modelled in
// shared/schema-xai.ts (table + dso_tenant_decision_unique index + dso checks) — it is
// deliberately NOT re-declared here. An earlier AS/PLATFORM/2026/008 Part B draft
// duplicated it; under `export *` two same-named exports become an ambiguous star
// export that ES modules silently drop from the namespace, which removed the table
// from @shared/schema and tripped the boot schema-conformance guard. Single source of
// truth: schema-xai.ts.

// ─────────────────────────────────────────────────────────────────────────────
// AEGIS SOAR — Financial-crime RESPONSE case spine (spec AS/SOAR/2026/001)
//
// M1 SCHEMA STAGE ONLY — no case logic, ingestion seam, STR lifecycle, or routes
// here. Architect Rule 9 review of this schema diff precedes commit/publish
// (tenant-isolation + security-table schema surfaces). The boot migration
// (rls-init.ts M10/M11) currently creates these tables and their CHECKs/FK/indexes
// in dev AND prod. As of AS/PLATFORM/2026/008 Part B the CHECKs, the composite FK,
// the UNIQUE(tenant_id,id) target, and the indexes are ALSO modelled in Drizzle
// below (name+definition-identical) so the publish-time DB-migration validator sees
// them; boot DDL is carved to RLS-only in the Part-A-gated step (T5).
//
// Prefix is fincrime_ (NOT soar_): the cybersecurity SOAR subsystem already owns
// soar_ (soar_playbooks / soar_executions / soar_action_results — a different
// domain). fincrime_% scopes cleanly to the AML financial-crime domain for
// examiner clarity and least-privilege grant/backup keying. Product name stays
// "AEGIS SOAR"; the table prefix is an internal schema decision (as kyc_* lives
// under AEGIS CYBER).
//
// disposition: 3 EXCLUSIVE outcomes (CLEARED/ESCALATED_TO_EDD/STR_RECOMMENDED),
// nullable until dispositioned; str_filed is an INDEPENDENT co-occurring boolean
// (an STR can be RECOMMENDED-but-not-yet-FILED) — NOT a fourth disposition —
// mirroring the kyc_decision_outcomes disposition + str_flag precedent (spec §3.2).
// subject_ref is OPAQUE-ONLY at M1 (no raw PII on the case spine); a real
// identifier, if ever needed, routes through the existing PII-encryption substrate
// in a later, separately-gated design. event_payload is JSON-text, tamper-evident
// via payload_hash.
// ─────────────────────────────────────────────────────────────────────────────

export const fincrimeCases = pgTable("fincrime_cases", {
  id:                     varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  tenantId:               text("tenant_id").notNull(),
  sourceType:             text("source_type").notNull(),          // ingestion source class (M1: opaque label)
  sourceRef:              text("source_ref"),                     // nullable external reference within the source
  sourceSignalHash:       text("source_signal_hash").notNull(),   // SHA-256 of the triggering signal
  subjectRef:             text("subject_ref").notNull(),          // OPAQUE external reference — no raw PII at M1
  subjectRefKind:         text("subject_ref_kind").notNull().default("OPAQUE_EXTERNAL_REF"),
  state:                  text("state").notNull().default("OPEN"),// OPEN|UNDER_INVESTIGATION|PENDING_MLCO_REVIEW|DISPOSITIONED
  assignedInvestigatorId: varchar("assigned_investigator_id"),    // nullable until assigned
  disposition:            text("disposition"),                    // nullable; CLEARED|ESCALATED_TO_EDD|STR_RECOMMENDED
  dispositionReason:      text("disposition_reason"),             // nullable; required once DISPOSITIONED (criterion-4 CHECK)
  strFiled:               boolean("str_filed").notNull().default(false), // INDEPENDENT co-occurring flag
  auditChainEntryId:      integer("audit_chain_entry_id"),        // audit linkage (nullable)
  openedAt:               timestamp("opened_at").notNull().default(sql`NOW()`),
  updatedAt:              timestamp("updated_at").notNull().default(sql`NOW()`),
  dispositionedAt:        timestamp("dispositioned_at"),          // nullable; non-null iff DISPOSITIONED (criterion-4 CHECK)
}, (t) => [
  check("fcc_state_valid", sql`${t.state} IN ('OPEN','UNDER_INVESTIGATION','PENDING_MLCO_REVIEW','DISPOSITIONED')`),
  check("fcc_disposition_valid", sql`${t.disposition} IS NULL OR ${t.disposition} IN ('CLEARED','ESCALATED_TO_EDD','STR_RECOMMENDED')`),
  // Criterion-4 (no case silently closed) pushed into the schema: DISPOSITIONED
  // REQUIRES disposition + reason + timestamp; a non-DISPOSITIONED case carries
  // NONE of them (disposition is nullable UNTIL dispositioned). The database
  // refuses both a silently-closed case AND a pre-disposition row that already
  // carries an outcome.
  check("fcc_no_silent_close", sql`(${t.state} = 'DISPOSITIONED' AND ${t.disposition} IS NOT NULL AND ${t.dispositionReason} IS NOT NULL AND ${t.dispositionedAt} IS NOT NULL) OR (${t.state} <> 'DISPOSITIONED' AND ${t.disposition} IS NULL AND ${t.dispositionReason} IS NULL AND ${t.dispositionedAt} IS NULL)`),
  // AS/PLATFORM/2026/008 Part B — UNIQUE + index objects brought under Drizzle ownership,
  // name+definition-identical to the live boot-DDL-created objects. fcc_tenant_id_uniq is the
  // composite UNIQUE(tenant_id,id) FK target referenced by the tenant-bound child FKs
  // (events/evidence/str_reports). Boot DDL retains ONLY RLS after the Part-A-gated carve.
  unique("fcc_tenant_id_uniq").on(t.tenantId, t.id),
  unique("fcc_signal_hash_uniq").on(t.tenantId, t.sourceSignalHash),
  index("fcc_tenant_opened_idx").on(t.tenantId, t.openedAt.desc().nullsFirst()),
  index("fcc_tenant_source_idx").on(t.tenantId, t.sourceType, t.sourceRef),
  index("fcc_tenant_state_updated_idx").on(t.tenantId, t.state, t.updatedAt.desc().nullsFirst()),
]).enableRLS();

export const fincrimeCaseEvents = pgTable("fincrime_case_events", {
  id:                varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  tenantId:          text("tenant_id").notNull(),
  caseId:            varchar("case_id").notNull(), // TENANT-BOUND composite FK (tenant_id, case_id) → fincrime_cases(tenant_id, id): modelled in Drizzle as fce_case_id_fk (Part B, see callback below) AND currently created by boot DDL (M10 owns the UNIQUE(tenant_id,id) target, M11 owns the FK); composite so a case is referenceable ONLY from within its own tenant (no cross-tenant reference / case-id existence oracle). Boot DDL carved to RLS-only in the Part-A-gated step (T5)
  eventType:         text("event_type").notNull(),         // transition/annotation class (values settled at M2 logic stage)
  fromState:         text("from_state"),                   // nullable (null for the OPEN-creation event)
  toState:           text("to_state"),                     // nullable
  actorUserId:       varchar("actor_user_id"),             // nullable (system-originated events)
  actorRole:         text("actor_role"),                   // nullable
  eventPayload:      text("event_payload").notNull(),      // JSON-text snapshot (no raw PII at M1)
  payloadHash:       text("payload_hash").notNull(),       // SHA-256 of event_payload — tamper-evidence (criterion 5)
  auditChainEntryId: integer("audit_chain_entry_id"),      // audit linkage (nullable)
  createdAt:         timestamp("created_at").notNull().default(sql`NOW()`),
}, (t) => [
  // AS/PLATFORM/2026/008 Part B — composite tenant-bound FK + index under Drizzle ownership,
  // name+definition-identical to the live boot-DDL objects. The (tenant_id, case_id) FK makes a
  // case referenceable ONLY within its own tenant (closes the cross-tenant reference / case-id
  // existence oracle); boot DDL retains ONLY RLS after the Part-A-gated carve.
  foreignKey({
    columns: [t.tenantId, t.caseId],
    foreignColumns: [fincrimeCases.tenantId, fincrimeCases.id],
    name: "fce_case_id_fk",
  }),
  index("fce_tenant_case_created_idx").on(t.tenantId, t.caseId, t.createdAt),
]).enableRLS();

export const insertFincrimeCaseSchema = createInsertSchema(fincrimeCases).omit({ id: true, openedAt: true, updatedAt: true });
export const insertFincrimeCaseEventSchema = createInsertSchema(fincrimeCaseEvents).omit({ id: true, createdAt: true });
export type FincrimeCase = typeof fincrimeCases.$inferSelect;
export type FincrimeCaseEvent = typeof fincrimeCaseEvents.$inferSelect;

// AEGIS SOAR M2 (investigation/EDD) — evidence + investigation-note store (spec
// AS/SOAR/2026/001, schema sub-gate S1). Durable, separately-audited, PII-safe
// store for the documented-suspicion basis; the case spine does not provide it.
export const fincrimeCaseEvidence = pgTable("fincrime_case_evidence", {
  id:                varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  tenantId:          text("tenant_id").notNull(),
  caseId:            varchar("case_id").notNull(), // TENANT-BOUND composite FK (tenant_id, case_id) → fincrime_cases(tenant_id, id): modelled in Drizzle as fcev_case_fk (Part B, see callback below) AND currently created by boot DDL (M10 owns the UNIQUE(tenant_id,id) target, M12 owns the FK fcev_case_fk); composite so evidence is referenceable ONLY within its own tenant (closes the cross-tenant reference / case-id existence oracle a single-col FK would allow). Boot DDL carved to RLS-only in the Part-A-gated step (T5)
  evidenceType:      text("evidence_type").notNull(),       // INVESTIGATION_NOTE|DOCUMENT_REF|TRANSACTION_REF|KYT_REF|SANCTIONS_REF|ADVERSE_MEDIA_REF (fcev_type_valid CHECK)
  opaqueRef:         text("opaque_ref"),                    // nullable; opaque/external pointer — no raw PII
  encryptedNote:     text("encrypted_note"),                // nullable; AES-256-GCM ciphertext (encryptPii applied at the S3 workflow stage) — never raw note text
  addedByUserId:     varchar("added_by_user_id").notNull(), // actor (set by the storage layer at S3)
  addedByRole:       text("added_by_role").notNull(),       // actor role (set by the storage layer at S3)
  payloadHash:       text("payload_hash").notNull(),        // SHA-256 of the canonical evidence payload — tamper-evidence
  auditChainEntryId: integer("audit_chain_entry_id"),       // audit linkage (nullable; set by the attempt-before-effect write at S3)
  createdAt:         timestamp("created_at").notNull().default(sql`NOW()`),
}, (t) => [
  check("fcev_type_valid", sql`${t.evidenceType} IN ('INVESTIGATION_NOTE','DOCUMENT_REF','TRANSACTION_REF','KYT_REF','SANCTIONS_REF','ADVERSE_MEDIA_REF')`),
  // Content presence: an evidence row must carry at least one content surface —
  // an opaque pointer OR an encrypted note. The DB refuses an empty row.
  check("fcev_has_content", sql`${t.opaqueRef} IS NOT NULL OR ${t.encryptedNote} IS NOT NULL`),
  // AS/PLATFORM/2026/008 Part B — composite tenant-bound FK + index under Drizzle ownership,
  // name+definition-identical to the live boot-DDL objects.
  foreignKey({
    columns: [t.tenantId, t.caseId],
    foreignColumns: [fincrimeCases.tenantId, fincrimeCases.id],
    name: "fcev_case_fk",
  }),
  index("fcev_tenant_case_idx").on(t.tenantId, t.caseId, t.createdAt),
]).enableRLS();

export const insertFincrimeCaseEvidenceSchema = createInsertSchema(fincrimeCaseEvidence).omit({ id: true, createdAt: true });
export type FincrimeCaseEvidence = typeof fincrimeCaseEvidence.$inferSelect;

// ─────────────────────────────────────────────────────────────────────────────
// AEGIS SOAR — M3 STR (Suspicious Transaction Report) substrate (spec AS/SOAR/
// 2026/001, M3 CHUNK 1 — SCHEMA/CONTROL SUBSTRATE ONLY)
//
// SCHEMA STAGE ONLY — NO STR finalizer, NO disposing route, NO goAML Form-B
// generation, NO FIA submission here. Those are M3 chunks 2 and 3, separately
// gated. This stage builds ONLY the durable tables + the structural integrity
// controls (CHECKs/FK/UNIQUE) that make the M3 behaviour provably correct from
// creation. Architect Rule 9 review of this schema diff precedes commit/publish
// (tenant-isolation + security-table schema surfaces).
//
// These are NEW fincrime-spine tables. They are NOT the legacy
// `suspicious_activity_reports` table (section 1 above) — that is the OLD
// regulator-gateway SAR with the fabricated-acknowledgement defect lineage. The
// M3 STR lifecycle belongs to the financial-crime case spine and is tenant-bound
// to fincrime_cases via an ordered composite FK, exactly like fincrime_case_events
// and fincrime_case_evidence.
//
// The boot migration (rls-init.ts M13/M14) currently creates/restores these tables
// and EVERY CHECK / FK / UNIQUE / index in dev AND prod. As of AS/PLATFORM/2026/008
// Part B, the composite FK, the UNIQUE(tenant_id, id) FK target, the indexes, AND
// the CHECKs are ALL modelled in Drizzle below — name+definition-identical to the
// live boot-DDL objects — so the publish-time DB-migration validator stops emitting
// spurious "already exists" diffs. Boot DDL is carved to RLS-only in the Part-A-gated
// step (T5); until then both define the same objects (effect-idempotent).
//
// CONTROL POSTURE (the three M3 binding criteria, structural half):
//   * Criterion 1 (no fabricated FIA acknowledgement): fsr_no_fabricated_ack
//     makes a row that carries fia_reference / filed_at / acknowledged_at WITHOUT
//     the corresponding filing status literally unstorable. A "PREPARED" or
//     "NOT_FILED" STR can carry NONE of them; only FILED carries fia_reference +
//     filed_at; only ACKNOWLEDGED additionally carries acknowledged_at. The DB
//     refuses a forged acknowledgement at the storage layer (the M3 chunk-2
//     finalizer enforces that the status only ever advances via a REAL provider
//     callback — the schema is the structural floor underneath it).
//   * Criterion 2 (two-eyes / MLCO-as-human-filer): the SEPARATE
//     fincrime_str_approvals table is what makes the maker≠checker split
//     structural — the preparer writes the report; a DIFFERENT senior authority
//     (approver_role risk_manager/admin) writes the approval. The maker≠checker
//     comparison itself is enforced by the chunk-2 finalizer (against the report's
//     prepared_by_user_id); this stage builds the substrate it needs.
//   * Retention: 10-year minimum (fsr_retention_min_years) + a no-early-purge
//     guard (fsr_retention_future — a row cannot be created already-purgeable);
//     the actual purge worker is out of M3 scope and reads retention_until.
//
// PII posture: no raw-PII column. subject_ref is OPAQUE (mirrors the case spine);
// encrypted_narrative holds AES-256-GCM ciphertext (encryption applied at the
// chunk-2 finalizer via encryptPii) — never raw narrative text; audit payloads
// carry only narrative_hash. Not in PII_COLUMNS; tenant_id conformance is owned
// by TENANT_TABLES membership (no BOOT_MIGRATION_COLUMNS entry), mirroring M10-M12.
// ─────────────────────────────────────────────────────────────────────────────

export const fincrimeStrReports = pgTable("fincrime_str_reports", {
  id:                   varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  tenantId:             text("tenant_id").notNull(),
  caseId:               varchar("case_id").notNull(), // TENANT-BOUND composite FK (tenant_id, case_id) → fincrime_cases(tenant_id, id): modelled in Drizzle as fsr_case_fk (Part B, see callback below) AND currently created by boot DDL (M13 owns fsr_case_fk; M10 owns the UNIQUE(tenant_id,id) target). Composite so an STR can reference a case ONLY within its own tenant (closes the cross-tenant reference / case-id existence oracle). Boot DDL carved to RLS-only in the Part-A-gated step (T5)
  status:               text("status").notNull().default("PREPARED"), // PREPARED|FILED|ACKNOWLEDGED|NOT_FILED
  subjectRef:           text("subject_ref").notNull(),                // OPAQUE external reference — no raw PII
  subjectRefKind:       text("subject_ref_kind").notNull().default("OPAQUE_EXTERNAL_REF"),
  encryptedNarrative:   text("encrypted_narrative"),                  // nullable; AES-256-GCM ciphertext (encryptPii at chunk-2) — never raw narrative
  narrativeHash:        text("narrative_hash"),                       // nullable; SHA-256 of the canonical narrative payload — tamper-evidence
  fiaReference:         text("fia_reference"),                        // nullable; REAL FIA reference — present ONLY when FILED/ACKNOWLEDGED (fsr_no_fabricated_ack)
  filedAt:              timestamp("filed_at"),                        // nullable; set ONLY when submitted to the real FIA
  acknowledgedAt:       timestamp("acknowledged_at"),                 // nullable; set ONLY from a REAL FIA acknowledgement callback (never fabricated)
  preparedByUserId:     varchar("prepared_by_user_id").notNull(),     // the maker (preparer)
  preparedByRole:       text("prepared_by_role").notNull(),
  retentionYears:       integer("retention_years").notNull().default(10), // 10-year minimum (fsr_retention_min_years)
  retentionUntil:       timestamp("retention_until").notNull(),       // earliest a row may be purged; fsr_retention_future forbids an already-purgeable row at creation
  auditChainEntryId:    integer("audit_chain_entry_id"),              // audit linkage (nullable)
  createdAt:            timestamp("created_at").notNull().default(sql`NOW()`),
  updatedAt:            timestamp("updated_at").notNull().default(sql`NOW()`),
}, (t) => [
  check("fsr_status_valid", sql`${t.status} IN ('PREPARED','FILED','ACKNOWLEDGED','NOT_FILED')`),
  // HEADLINE criterion-1 control (mirrors fcc_no_silent_close's shape): the
  // filing-evidence columns are bound to the status. A non-filed STR carries NONE
  // of them; FILED requires a real reference + filed_at and NO ack; ACKNOWLEDGED
  // requires all three. A forged acknowledgement (e.g. PREPARED + acknowledged_at)
  // is structurally unstorable.
  check("fsr_no_fabricated_ack", sql`(${t.status} IN ('PREPARED','NOT_FILED') AND ${t.fiaReference} IS NULL AND ${t.filedAt} IS NULL AND ${t.acknowledgedAt} IS NULL) OR (${t.status} = 'FILED' AND ${t.fiaReference} IS NOT NULL AND ${t.filedAt} IS NOT NULL AND ${t.acknowledgedAt} IS NULL) OR (${t.status} = 'ACKNOWLEDGED' AND ${t.fiaReference} IS NOT NULL AND ${t.filedAt} IS NOT NULL AND ${t.acknowledgedAt} IS NOT NULL)`),
  check("fsr_retention_min_years", sql`${t.retentionYears} >= 10`),
  check("fsr_retention_future", sql`${t.retentionUntil} >= ${t.createdAt}`),
  // AS/PLATFORM/2026/008 Part B — FK + UNIQUE(tenant_id,id) + indexes under Drizzle ownership,
  // name+definition-identical to the live boot-DDL objects.
  foreignKey({
    columns: [t.tenantId, t.caseId],
    foreignColumns: [fincrimeCases.tenantId, fincrimeCases.id],
    name: "fsr_case_fk",
  }),
  unique("fsr_tenant_id_uniq").on(t.tenantId, t.id),
  index("fsr_tenant_case_idx").on(t.tenantId, t.caseId),
  index("fsr_tenant_retention_idx").on(t.tenantId, t.retentionUntil),
  index("fsr_tenant_status_updated_idx").on(t.tenantId, t.status, t.updatedAt.desc().nullsFirst()),
]).enableRLS();

export const fincrimeStrApprovals = pgTable("fincrime_str_approvals", {
  id:                   varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  tenantId:             text("tenant_id").notNull(),
  strReportId:          varchar("str_report_id").notNull(), // TENANT-BOUND composite FK (tenant_id, str_report_id) → fincrime_str_reports(tenant_id, id): modelled in Drizzle as fsa_report_fk (Part B, see callback below) AND currently created by boot DDL (M14 owns fsa_report_fk; M13 owns the UNIQUE(tenant_id,id) target). Composite (stronger than the kyc_decision_approvals single-col precedent) so an approval can reference a report ONLY within its own tenant. Boot DDL carved to RLS-only in the Part-A-gated step (T5)
  caseId:               varchar("case_id").notNull(),       // denormalized (mirrors kda.customer_id) for case-scoped lookup; integrity flows through the report→case FK chain, so no separate FK here
  approverUserId:       varchar("approver_user_id").notNull(), // the checker (a DIFFERENT actor from the preparer; maker≠checker enforced by the chunk-2 finalizer)
  approverRole:         text("approver_role").notNull(),       // risk_manager|admin (senior compliance authority acting as MLCO; no distinct 'mlco' role exists in the enum)
  approvalType:         text("approval_type").notNull().default("MLCO_AUTHORITY"),
  reportHash:           text("report_hash").notNull(),         // must match the report's fresh narrative_hash at approval time (anti-stale; mirrors kda.signals_hash)
  reason:               text("reason"),                        // nullable
  auditChainEntryId:    integer("audit_chain_entry_id"),       // audit linkage (nullable)
  approvedAt:           timestamp("approved_at").notNull().default(sql`NOW()`),
}, (t) => [
  check("fsa_approver_role_valid", sql`${t.approverRole} IN ('risk_manager','admin')`),
  // AS/PLATFORM/2026/008 Part B — composite FK + UNIQUE index under Drizzle ownership,
  // name+definition-identical to the live boot-DDL objects.
  foreignKey({
    columns: [t.tenantId, t.strReportId],
    foreignColumns: [fincrimeStrReports.tenantId, fincrimeStrReports.id],
    name: "fsa_report_fk",
  }),
  uniqueIndex("fsa_tenant_report_uniq").on(t.tenantId, t.strReportId),
]).enableRLS();

export const insertFincrimeStrReportSchema = createInsertSchema(fincrimeStrReports).omit({ id: true, createdAt: true, updatedAt: true });
export const insertFincrimeStrApprovalSchema = createInsertSchema(fincrimeStrApprovals).omit({ id: true, approvedAt: true });
export type FincrimeStrReport = typeof fincrimeStrReports.$inferSelect;
export type FincrimeStrApproval = typeof fincrimeStrApprovals.$inferSelect;

// ─────────────────────────────────────────────────────────────────────────────
// AEGIS SECURITY IR — Security Incident-Response case spine (Layer 1 + Layer-2 seam)
// Spec: C:\aegis-cyber-backups\SECURITY_IR_WORKFLOW_SCOPE.md
//
// A durable, tenant-isolated, audit-chained SECURITY-incident lifecycle: alert →
// triage → containment → resolution as ONE record. Modelled on the fincrime case
// spine (fincrimeCases above) — a frozen state machine, a single transition
// chokepoint (assertIrTransition in server/lib/security-ir/workflow.ts), strict
// DTOs, RLS via TENANT_TABLES, audit-chained transitions — but it is a DISTINCT
// case TYPE in its OWN tables (the scope doc trap #1: do NOT bolt security states
// onto the fincrime M2/M3 fence).
//
// SLA folded onto the case (scope doc Layer-1 / trap #2 "do not leave two parallel
// incident tables"): severity / slaTargetMinutes / acknowledgedAt / resolvedAt /
// breached / escalatedAt live on the IR case so the lifecycle and SLA tracking are
// ONE system of record. The legacy incident_slas table is left untouched (it is a
// separate, thin SLA tracker) — this case simply carries the SLA concept itself.
//
// ALERT-IN SEAM (Layer 2): originatingAlertRef / originatingAlertSource link a case
// back to the EDR/SIEM alert that opened it. HONEST EDGE: the alert DATA is synthetic
// until the APT-make-real effort lands; this wires the real INTERFACE (alert id +
// source), so nothing in this case changes when the feed becomes real. We do NOT
// claim "detection-driven IR" here.
//
// The boot migration (rls-init.ts M55) is the AUTHORITATIVE creator/restorer of
// these tables + every CHECK / FK / index in BOTH dev and prod (prod publishes code
// only — never db:push). The CHECK constraints below are name+definition-identical
// to the live boot-DDL objects (effect-idempotent), mirroring the fincrime M10-M12
// dual-definition pattern.
//
// PII posture: no raw-PII column (title is an operator-authored incident label, not
// subject PII; assignedTo is an internal user id). Not in PII_COLUMNS; tenant_id
// conformance is owned by TENANT_TABLES membership (no BOOT_MIGRATION_COLUMNS entry).
// ─────────────────────────────────────────────────────────────────────────────

export const securityIncidents = pgTable("security_incidents", {
  id:                     varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  tenantId:               text("tenant_id").notNull(),
  title:                  text("title").notNull(),                  // operator/alert-derived incident label (no subject PII)
  severity:               text("severity").notNull(),               // P1|P2|P3|P4 (sec_inc_severity_valid CHECK)
  status:                 text("status").notNull().default("OPEN"), // OPEN|TRIAGED|CONTAINED|ERADICATED|RECOVERED|CLOSED (sec_inc_status_valid CHECK)
  // ── Alert-in seam (Layer 2): the originating EDR/SIEM alert reference. Nullable —
  // a manually-opened incident has no originating alert. The alert DATA is synthetic
  // until APT-make-real; these columns wire the real INTERFACE only.
  originatingAlertRef:    text("originating_alert_ref"),            // nullable; opaque alert id from the feed
  originatingAlertSource: text("originating_alert_source"),         // nullable; feed/source label (e.g. EDR_CENTRAL_BANK_ALERT)
  assignedTo:             varchar("assigned_to"),                   // nullable; internal user id of the assigned responder
  // ── Folded SLA fields (scope doc: one system of record — do NOT duplicate incident_slas).
  slaTargetMinutes:       integer("sla_target_minutes").notNull(), // resolution SLA target (derived from severity at open)
  acknowledgedAt:         timestamp("acknowledged_at"),             // nullable; set when triaged (MTTA basis)
  resolvedAt:             timestamp("resolved_at"),                 // nullable; set when RECOVERED/CLOSED (MTTR basis)
  breached:               boolean("breached").notNull().default(false), // SLA breach flag
  escalatedAt:            timestamp("escalated_at"),                // nullable; set on escalation
  auditChainEntryId:      integer("audit_chain_entry_id"),          // audit linkage (nullable)
  openedAt:               timestamp("opened_at").notNull().default(sql`NOW()`),
  updatedAt:              timestamp("updated_at").notNull().default(sql`NOW()`),
  closedAt:               timestamp("closed_at"),                   // nullable; non-null iff status = CLOSED (sec_inc_closed_at CHECK)
}, (t) => [
  check("sec_inc_severity_valid", sql`${t.severity} IN ('P1','P2','P3','P4')`),
  check("sec_inc_status_valid", sql`${t.status} IN ('OPEN','TRIAGED','CONTAINED','ERADICATED','RECOVERED','CLOSED')`),
  // closed_at is set IFF the case is CLOSED — the DB refuses a silently-closed case
  // (CLOSED with no closed_at) AND a non-CLOSED case already carrying a closed_at.
  check("sec_inc_closed_at_valid", sql`(${t.status} = 'CLOSED' AND ${t.closedAt} IS NOT NULL) OR (${t.status} <> 'CLOSED' AND ${t.closedAt} IS NULL)`),
  // Composite UNIQUE(tenant_id, id) is the tenant-bound FK target for the child
  // events table (mirrors fcc_tenant_id_uniq) — a child event references a case
  // ONLY within its own tenant (no cross-tenant reference / case-id oracle).
  unique("sec_inc_tenant_id_uniq").on(t.tenantId, t.id),
  index("sec_inc_tenant_opened_idx").on(t.tenantId, t.openedAt.desc().nullsFirst()),
  index("sec_inc_tenant_status_updated_idx").on(t.tenantId, t.status, t.updatedAt.desc().nullsFirst()),
]).enableRLS();

export const securityIncidentEvents = pgTable("security_incident_events", {
  id:                varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  tenantId:          text("tenant_id").notNull(),
  incidentId:        varchar("incident_id").notNull(), // TENANT-BOUND composite FK (tenant_id, incident_id) → security_incidents(tenant_id, id): modelled in Drizzle as sec_inc_evt_fk (below) AND created by boot DDL (M55). Composite so an event references an incident ONLY within its own tenant.
  eventType:         text("event_type").notNull(),     // INCIDENT_OPENED|transition event types (from the workflow transition map)
  fromState:         text("from_state"),               // nullable (null for the OPEN-creation event)
  toState:           text("to_state"),                 // nullable
  actorUserId:       varchar("actor_user_id"),         // nullable (system-/alert-originated events)
  note:              text("note"),                     // nullable free-text operator note (no subject PII)
  auditChainEntryId: integer("audit_chain_entry_id"),  // audit linkage (nullable)
  createdAt:         timestamp("created_at").notNull().default(sql`NOW()`),
}, (t) => [
  foreignKey({
    columns: [t.tenantId, t.incidentId],
    foreignColumns: [securityIncidents.tenantId, securityIncidents.id],
    name: "sec_inc_evt_fk",
  }),
  index("sec_inc_evt_tenant_incident_idx").on(t.tenantId, t.incidentId, t.createdAt),
]).enableRLS();

// ── LAYER-3 SEAM (schema only — NO effectors implemented in this increment) ────
// security_incident_actions records containment steps (isolate endpoint, block IP,
// disable account). This increment provides the TABLE only — there is NO effector
// wiring, NO route, NO auto-containment. Per the scope doc / honesty rule, a
// containment effector that cannot call a REAL agent must not be built as a fake;
// it is a later layer (shared with SOAR make-real + APT make-real). The "simulated"
// status default + the explicit comment make the not-yet-real edge honest. Wire to
// a REAL effector (or keep status='SIMULATED') when that layer lands.
export const securityIncidentActions = pgTable("security_incident_actions", {
  id:                varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  tenantId:          text("tenant_id").notNull(),
  incidentId:        varchar("incident_id").notNull(), // TENANT-BOUND composite FK (tenant_id, incident_id) → security_incidents(tenant_id, id)
  actionType:        text("action_type").notNull(),    // ISOLATE_ENDPOINT|BLOCK_IP|DISABLE_ACCOUNT (catalogue settled when effectors are real)
  status:            text("status").notNull().default("SIMULATED"), // SIMULATED|PENDING|EXECUTED|FAILED — defaults SIMULATED until a real effector is wired (Layer 3)
  requestedByUserId: varchar("requested_by_user_id"),  // nullable; actor who requested the action
  detail:            text("detail"),                   // nullable; non-PII structural detail (e.g. opaque endpoint/IP ref)
  createdAt:         timestamp("created_at").notNull().default(sql`NOW()`),
}, (t) => [
  check("sec_inc_act_status_valid", sql`${t.status} IN ('SIMULATED','PENDING','EXECUTED','FAILED')`),
  foreignKey({
    columns: [t.tenantId, t.incidentId],
    foreignColumns: [securityIncidents.tenantId, securityIncidents.id],
    name: "sec_inc_act_fk",
  }),
  index("sec_inc_act_tenant_incident_idx").on(t.tenantId, t.incidentId, t.createdAt),
]).enableRLS();

export const insertSecurityIncidentSchema = createInsertSchema(securityIncidents).omit({ id: true, openedAt: true, updatedAt: true });
export const insertSecurityIncidentEventSchema = createInsertSchema(securityIncidentEvents).omit({ id: true, createdAt: true });
export const insertSecurityIncidentActionSchema = createInsertSchema(securityIncidentActions).omit({ id: true, createdAt: true });
export type SecurityIncident = typeof securityIncidents.$inferSelect;
export type SecurityIncidentEvent = typeof securityIncidentEvents.$inferSelect;
export type SecurityIncidentAction = typeof securityIncidentActions.$inferSelect;

// ─────────────────────────────────────────────────────────────────────────────
// AEGIS UEBA — Insider-threat / behavioral baselines on PRIVILEGED users
// Spec: C:\aegis-cyber-backups\UEBA_PRIVILEGED_SCOPE.md
//
// FIRST REAL INCREMENT. Two NEW tenant-isolated tables only — no existing table
// is altered or dropped. They sit on top of the REAL sensor already running: the
// audit stream (audit_logs / audit_chain_entries). The baseline builder
// (server/lib/ueba/baseline.ts) computes EVERY statistic from persisted audit_logs
// rows — never Math.random(), never seeded, never synthesized — and every anomaly
// CITES the specific audit_logs id(s) that triggered it (triggeringAuditIds).
//
// THE LOAD-BEARING HONESTY RULE (the trap that deleted 16 engines + Bio-Vault):
//   * Baselines are derived from real audit rows; sampleCount records how many.
//   * A baseline below MIN_BASELINE_SAMPLES is NOT trustworthy (cold-start) — the
//     rules DO NOT fire on it (mirrors biometric_profiles.confidenceScore/sampleCount).
//   * Off-hours means off-hours in KAMPALA (EAT, UTC+3) — the hour histogram is keyed
//     on the Kampala hour (Africa/Kampala) of each real audit timestamp.
//
// The boot migration (rls-init.ts M56) is the AUTHORITATIVE creator/restorer of both
// tables + every CHECK / FK / UNIQUE / index in BOTH dev and prod (Publish deploys
// code only; prod never runs db:push). EFFECT-idempotent (Rule 16), mirroring M55.
// RLS via the two TENANT_TABLES entries (ueba_user_baselines / ueba_anomalies).
//
// PII posture: no raw-PII column. user_id is an internal user id; the profile JSON
// holds only behavioral aggregates (hour/day histograms, resource labels, counts,
// source-IP set). Not in PII_COLUMNS; tenant_id conformance is owned by TENANT_TABLES
// membership (no BOOT_MIGRATION_COLUMNS entry), mirroring M55.
// ─────────────────────────────────────────────────────────────────────────────

// ueba_user_baselines — one durable behavioral baseline per (tenant_id, user_id).
// Every profile field is computed from the user's REAL audit_logs rows over a
// rolling window. sample_count is the number of real audit rows the baseline was
// built from — the cold-start gate reads it (sample_count < MIN_BASELINE_SAMPLES ⇒
// not trustworthy ⇒ no fire). UNIQUE(tenant_id, user_id) is the per-user upsert key.
export const uebaUserBaselines = pgTable("ueba_user_baselines", {
  id:                varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  tenantId:          text("tenant_id").notNull(),
  userId:            varchar("user_id").notNull(),         // internal user id (the privileged subject)
  // Access-time profile: a 24-slot hour-of-day histogram (counts per Kampala hour)
  // and a 7-slot day-of-week histogram, serialized JSON. Off-hours detection reads
  // the learned active hour-window from hourHistogramJson.
  hourHistogramJson: text("hour_histogram_json").notNull().default("{}"), // JSON {"0":n,...,"23":n} — Kampala (EAT) hours
  dowHistogramJson:  text("dow_histogram_json").notNull().default("{}"),   // JSON {"0":n,...,"6":n} — Kampala day-of-week
  // Resource profile: the set of distinct `resource` values this user normally touches.
  resourceProfileJson: text("resource_profile_json").notNull().default("[]"), // JSON string[]
  // Volume profile: typical per-day export/read counts derived from the window.
  exportCountWindow: integer("export_count_window").notNull().default(0), // total export actions in the window
  windowDays:        integer("window_days").notNull().default(30),        // window length the baseline was built over
  avgExportsPerDay:  numeric("avg_exports_per_day", { precision: 10, scale: 3 }).notNull().default("0"), // baseline export volume/day
  // Geo/IP profile: the set of distinct source IPs this user has logged in from.
  ipProfileJson:     text("ip_profile_json").notNull().default("[]"), // JSON string[]
  sampleCount:       integer("sample_count").notNull().default(0),    // # of real audit_logs rows the baseline was built from
  lastRebuiltAt:     timestamp("last_rebuilt_at").notNull().default(sql`NOW()`),
  createdAt:         timestamp("created_at").notNull().default(sql`NOW()`),
  updatedAt:         timestamp("updated_at").notNull().default(sql`NOW()`),
}, (t) => [
  // sample_count is a real count of audit rows — never negative (anti-fabrication).
  check("ueba_baseline_sample_count_nonneg", sql`${t.sampleCount} >= 0`),
  check("ueba_baseline_window_days_pos", sql`${t.windowDays} > 0`),
  // One baseline per user per tenant — the upsert key.
  unique("ueba_baseline_tenant_user_uniq").on(t.tenantId, t.userId),
  index("ueba_baseline_tenant_rebuilt_idx").on(t.tenantId, t.lastRebuiltAt.desc().nullsFirst()),
]).enableRLS();

// ueba_anomalies — one row per detection. EVERY anomaly cites the real audit_logs
// id(s) that triggered it (triggering_audit_ids), so a human can inspect the exact
// rows. opened_incident_id links to the security_incident opened on a high-confidence
// anomaly via the EXISTING IR seam (storage.openSecurityIncident).
export const uebaAnomalies = pgTable("ueba_anomalies", {
  id:                  varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  tenantId:            text("tenant_id").notNull(),
  userId:              varchar("user_id").notNull(),       // the privileged subject the anomaly is about
  rule:                text("rule").notNull(),             // OFF_HOURS_PRIVILEGED_ACCESS | BULK_EXPORT (ueba_anomaly_rule_valid)
  observed:            text("observed").notNull(),         // human-readable observed value (e.g. "login at 03:14 EAT")
  expected:            text("expected").notNull(),         // human-readable expected/baseline value (e.g. "active 08:00–19:00 EAT")
  confidence:          numeric("confidence", { precision: 4, scale: 3 }).notNull(), // 0.000–1.000
  // The cited REAL audit_logs id(s) — the load-bearing traceability field. JSON
  // string[] of audit_logs.id values. Anti-fabrication CHECK requires non-empty.
  triggeringAuditIds:  text("triggering_audit_ids").notNull(), // JSON string[] of audit_logs.id — MUST be non-empty
  openedIncidentId:    varchar("opened_incident_id"),      // nullable; link to the security_incident opened (Layer 3)
  detectedAt:          timestamp("detected_at").notNull().default(sql`NOW()`),
  createdAt:           timestamp("created_at").notNull().default(sql`NOW()`),
}, (t) => [
  check("ueba_anomaly_rule_valid", sql`${t.rule} IN ('OFF_HOURS_PRIVILEGED_ACCESS','BULK_EXPORT')`),
  check("ueba_anomaly_confidence_range", sql`${t.confidence} >= 0 AND ${t.confidence} <= 1`),
  // Anti-fabrication: an anomaly MUST cite at least one real audit row. A bare "[]"
  // (no citation) is structurally unstorable — this is the schema-level enforcement
  // of "every anomaly cites the specific audit row(s) that triggered it".
  check("ueba_anomaly_cites_audit", sql`${t.triggeringAuditIds} <> '[]' AND length(${t.triggeringAuditIds}) > 2`),
  index("ueba_anomaly_tenant_detected_idx").on(t.tenantId, t.detectedAt.desc().nullsFirst()),
  index("ueba_anomaly_tenant_user_idx").on(t.tenantId, t.userId, t.detectedAt.desc().nullsFirst()),
]).enableRLS();

export const insertUebaUserBaselineSchema = createInsertSchema(uebaUserBaselines).omit({ id: true, createdAt: true, updatedAt: true });
export const insertUebaAnomalySchema = createInsertSchema(uebaAnomalies).omit({ id: true, createdAt: true });
export type UebaUserBaseline = typeof uebaUserBaselines.$inferSelect;
export type UebaAnomaly = typeof uebaAnomalies.$inferSelect;

// ─────────────────────────────────────────────────────────────────────────────
// 7. ENTITY RESOLUTION — Entities, Links, Clusters
// ─────────────────────────────────────────────────────────────────────────────

export const erEntities = pgTable("er_entities", {
  id: varchar("id").primaryKey(),
  type: text("type").notNull(), // ACCOUNT|CUSTOMER|DEVICE|IP_ADDRESS|PHONE|EMAIL|ADDRESS
  value: text("value").notNull(),
  attributes: text("attributes").notNull().default("{}"), // JSON
  riskScore: numeric("risk_score", { precision: 4, scale: 3 }).notNull().default("0"),
  linkedEntities: text("linked_entities").notNull().default("[]"), // JSON string[]
  clusterId: text("cluster_id"),
  firstSeen: timestamp("first_seen").notNull().default(sql`NOW()`),
  lastSeen: timestamp("last_seen").notNull().default(sql`NOW()`),
  tenantId: text("tenant_id"),
}).enableRLS();

export const erEntityLinks = pgTable("er_entity_links", {
  id: varchar("id").primaryKey(),
  sourceId: varchar("source_id").notNull(),
  targetId: varchar("target_id").notNull(),
  linkType: text("link_type").notNull(), // SHARED_DEVICE|SHARED_IP|SHARED_PHONE|SHARED_EMAIL|SHARED_ADDRESS|TRANSACTION|SAME_BENEFICIARY|VELOCITY_PATTERN|TEMPORAL_CORRELATION
  strength: numeric("strength", { precision: 4, scale: 3 }).notNull(),
  evidence: text("evidence").notNull().default("[]"), // JSON string[]
  lastConfirmed: timestamp("last_confirmed").notNull().default(sql`NOW()`),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
});

export const erClusters = pgTable("er_clusters", {
  id: varchar("id").primaryKey(),
  name: text("name").notNull(),
  entities: text("entities").notNull().default("[]"), // JSON string[]
  riskScore: numeric("risk_score", { precision: 4, scale: 3 }).notNull().default("0"),
  riskLevel: text("risk_level").notNull().default("LOW"), // LOW|MEDIUM|HIGH|CRITICAL
  indicators: text("indicators").notNull().default("[]"), // JSON string[]
  pattern: text("pattern").notNull(),
  tenantId: text("tenant_id"),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
  updatedAt: timestamp("updated_at").notNull().default(sql`NOW()`),
}).enableRLS();

// ─────────────────────────────────────────────────────────────────────────────
// 8. PEER GROUP ANALYSIS — Groups, Employees, Comparisons
// ─────────────────────────────────────────────────────────────────────────────
// ORPHANED (F-RP-06, 2026-06-20): the peer-analysis application surface (5 routes,
// in-memory readers, dormant DB layer) was DELETED as a vestigial all-synthetic demo
// (operator GO, architect-ratified CODE-ONLY-SURFACE scope; saas/F-RP-04 precedent).
// These 3 table defs + their TENANT_TABLES entries (server/lib/rls-init.ts) are
// intentionally RETAINED — zero-row dev scaffolding, NO callers, NOT physically
// dropped (Rule 11: any physical DROP of peer_groups/peer_employees/peer_comparisons
// is a separate explicit destructive task). They have no boot-DDL, so they do not
// exist in prod.
export const peerGroups = pgTable("peer_groups", {
  id: varchar("id").primaryKey(),
  name: text("name").notNull(),
  description: text("description").notNull(),
  criteriaJson: text("criteria_json").notNull().default("{}"), // JSON { departments, roles, seniority }
  memberCount: integer("member_count").notNull().default(0),
  baselineJson: text("baseline_json").notNull().default("{}"), // JSON BehaviorBaseline
  tenantId: text("tenant_id"),
  lastUpdated: timestamp("last_updated").notNull().default(sql`NOW()`),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
}).enableRLS();

export const peerEmployees = pgTable("peer_employees", {
  id: varchar("id").primaryKey(),
  name: text("name").notNull(),
  department: text("department").notNull(),
  role: text("role").notNull(),
  branch: text("branch").notNull(),
  hireDate: timestamp("hire_date").notNull(),
  peerGroupId: varchar("peer_group_id"),
  behaviorProfile: text("behavior_profile").notNull().default("{}"), // JSON BehaviorProfile
  riskScore: numeric("risk_score", { precision: 4, scale: 3 }).notNull().default("0"),
  lastActivity: timestamp("last_activity"),
  tenantId: text("tenant_id"),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
  updatedAt: timestamp("updated_at").notNull().default(sql`NOW()`),
}).enableRLS();

export const peerComparisons = pgTable("peer_comparisons", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  employeeId: varchar("employee_id").notNull(),
  employeeName: text("employee_name").notNull(),
  peerGroupName: text("peer_group_name").notNull(),
  deviationsJson: text("deviations_json").notNull().default("[]"),  // JSON Deviation[]
  overallDeviationScore: numeric("overall_deviation_score", { precision: 5, scale: 4 }).notNull(),
  riskLevel: text("risk_level").notNull(), // NORMAL|SLIGHT|MODERATE|SIGNIFICANT|CRITICAL
  recommendationsJson: text("recommendations_json").notNull().default("[]"),
  tenantId: text("tenant_id"),
  comparedAt: timestamp("compared_at").notNull().default(sql`NOW()`),
}).enableRLS();

// ─────────────────────────────────────────────────────────────────────────────
// 9. KYT ENGINE — Transaction Scoring Results (audit trail)
// ─────────────────────────────────────────────────────────────────────────────

export const kytResults = pgTable("kyt_results", {
  id: varchar("id").primaryKey(),
  userId: text("user_id").notNull(),
  amount: numeric("amount", { precision: 20, scale: 2 }).notNull(),
  currency: text("currency").notNull().default("UGX"),
  recipientId: text("recipient_id"),
  recipientType: text("recipient_type").notNull(), // KNOWN|NEW|FLAGGED
  channel: text("channel").notNull(),              // MOBILE|WEB|API|AGENT
  riskLevel: text("risk_level").notNull(),         // LOW|MEDIUM|HIGH|CRITICAL
  riskScore: numeric("risk_score", { precision: 4, scale: 3 }).notNull(),
  riskFactors: text("risk_factors").notNull().default("[]"), // JSON string[]
  recommendation: text("recommendation").notNull(),          // APPROVE|STEP_UP|REVIEW|BLOCK
  requiredVerification: text("required_verification").notNull().default("[]"), // JSON string[]
  processingTimeMs: integer("processing_time_ms"),
  tenantId: text("tenant_id"),
  scoredAt: timestamp("scored_at").notNull().default(sql`NOW()`),
}).enableRLS();

// ─────────────────────────────────────────────────────────────────────────────
// Insert schemas & types
// ─────────────────────────────────────────────────────────────────────────────

export const insertSarSchema = createInsertSchema(suspiciousActivityReports).omit({ createdAt: true, updatedAt: true });
export const insertSoarPlaybookSchema = createInsertSchema(soarPlaybooks).omit({ createdAt: true, updatedAt: true });
export const insertJitGrantSchema = createInsertSchema(jitGrants).omit({ requestedAt: true });
export const insertJitPolicySchema = createInsertSchema(jitPolicies).omit({ createdAt: true, updatedAt: true });
export const insertPamAccountSchema = createInsertSchema(pamAccounts).omit({ createdAt: true });
export const insertPamRequestSchema = createInsertSchema(pamRequests).omit({ createdAt: true });
export const insertThreatFeedSchema = createInsertSchema(threatFeeds).omit({ createdAt: true, updatedAt: true });
export const insertThreatIndicatorSchema = createInsertSchema(threatIndicators);
export const insertKycCustomerSchema = createInsertSchema(kycCustomers).omit({ createdAt: true, updatedAt: true });
export const insertErEntitySchema = createInsertSchema(erEntities);
export const insertErLinkSchema = createInsertSchema(erEntityLinks);
export const insertKytResultSchema = createInsertSchema(kytResults);

export type SAR = typeof suspiciousActivityReports.$inferSelect;
export type ExplanationDossier = typeof explanationDossiers.$inferSelect;
export type RegulatorSession = typeof regulatorSessions.$inferSelect;
export type SoarPlaybook = typeof soarPlaybooks.$inferSelect;
export type JitGrant = typeof jitGrants.$inferSelect;
export type JitPolicy = typeof jitPolicies.$inferSelect;
export type PamAccount = typeof pamAccounts.$inferSelect;
export type PamRequest = typeof pamRequests.$inferSelect;
export type PamSession = typeof pamSessions.$inferSelect;
export type ThreatFeed = typeof threatFeeds.$inferSelect;
export type ThreatIndicator = typeof threatIndicators.$inferSelect;
export type KycCustomer = typeof kycCustomers.$inferSelect;
export type ErEntity = typeof erEntities.$inferSelect;
export type ErCluster = typeof erClusters.$inferSelect;
export type ErEntityLink = typeof erEntityLinks.$inferSelect;
export type PeerGroup = typeof peerGroups.$inferSelect;
export type PeerEmployee = typeof peerEmployees.$inferSelect;
export type KytResult = typeof kytResults.$inferSelect;

export const threatFeedSyncLog = pgTable("threat_feed_sync_log", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  tenantId: text("tenant_id"), // AS/PLATFORM/2026/008 Chunk B — tenant isolation
  feedId: text("feed_id").notNull(),
  feedName: text("feed_name").notNull(),
  status: text("status").notNull(),
  indicatorsAdded: integer("indicators_added").notNull().default(0),
  indicatorsUpdated: integer("indicators_updated").notNull().default(0),
  errorMessage: text("error_message"),
  durationMs: integer("duration_ms"),
  syncedAt: timestamp("synced_at").notNull().default(sql`NOW()`),
}).enableRLS();

// ─────────────────────────────────────────────────────────────────────────────
// AEGIS SOAR (AS/SOAR/2026/001) — Machine-API-key ingestion credentials
// ─────────────────────────────────────────────────────────────────────────────
// Scoped, hashed, revocable machine credentials that authorize ONLY automated
// fincrime SIGNAL ingestion (mirror of the examiner-token discipline:
// sha256(salt + raw), TTL, scopes, revocation). The tenant a key ingests for is
// BOUND to the key row (tenant_id) and injected server-side — never taken from
// the request payload, closing the same cross-tenant hole the M1 ingest DTO and
// RLS close. Platform-level credential table: NOT tenant-RLS-scoped (validation
// runs BEFORE tenant context exists — the key DETERMINES the tenant), mirroring
// examiner_tokens. Only the SHA-256 key hash is stored; the raw key is shown
// once at mint and never persisted.
export const fincrimeIngestKeys = pgTable("fincrime_ingest_keys", {
  id:         varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  keyHash:    text("key_hash").notNull().unique(),       // sha256(MACHINE_KEY_SALT + rawKey)
  label:      text("label").notNull(),                   // human label (non-PII, e.g. "kyt-pipeline")
  scopesJson: text("scopes_json").notNull(),             // JSON string array, e.g. ["ingest:fincrime"]
  tenantId:   text("tenant_id").notNull(),               // tenant this key ingests for (bound, not from payload)
  issuedBy:   text("issued_by").notNull(),               // admin username that minted the key
  issuedAt:   timestamp("issued_at").notNull().default(sql`NOW()`),
  expiresAt:  timestamp("expires_at").notNull(),
  revokedAt:  timestamp("revoked_at"),
  lastUsedAt: timestamp("last_used_at"),
  useCount:   integer("use_count").notNull().default(0),
});

export type FincrimeIngestKey = typeof fincrimeIngestKeys.$inferSelect;