/**
 * AEGIS CYBER — Wave 5 Schema
 * shared/schema-wave5.ts
 * Add to shared/schema.ts: export * from "./schema-wave5";
 */

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

// ── 1. Ghost Core (CBS shadow ledger) ─────────────────────────────────────────

export const ghostShadowTransactions = pgTable("ghost_shadow_transactions", {
  id: varchar("id").primaryKey(),
  accountFrom: text("account_from").notNull(),
  accountTo: text("account_to").notNull(),
  amount: numeric("amount", { precision: 20, scale: 4 }).notNull(),
  currency: text("currency").notNull().default("UGX"),
  type: text("type").notNull(),
  status: text("status").notNull().default("PENDING"),
  merkleRoot: text("merkle_root").notNull(),
  sequenceNumber: integer("sequence_number").notNull(),
  tenantId: text("tenant_id"),
  timestamp: timestamp("timestamp").notNull().default(sql`NOW()`),
  reconciledAt: timestamp("reconciled_at"),
}).enableRLS();

export const ghostShadowBalances = pgTable("ghost_shadow_balances", {
  accountId: varchar("account_id").primaryKey(),
  availableBalance: numeric("available_balance", { precision: 20, scale: 4 }).notNull(),
  holdBalance: numeric("hold_balance", { precision: 20, scale: 4 }).notNull().default("0"),
  currency: text("currency").notNull().default("UGX"),
  lastSyncTime: timestamp("last_sync_time").notNull().default(sql`NOW()`),
  syncSequence: integer("sync_sequence").notNull().default(0),
  tenantId: text("tenant_id"),
  updatedAt: timestamp("updated_at").notNull().default(sql`NOW()`),
}).enableRLS();

// ── 2. Account Lockout ────────────────────────────────────────────────────────

export const accountLockouts = pgTable("account_lockouts", {
  key: varchar("key").primaryKey(),          // "username:ipAddress"
  failedAttempts: integer("failed_attempts").notNull().default(0),
  firstFailureAt: timestamp("first_failure_at").notNull().default(sql`NOW()`),
  lastFailureAt: timestamp("last_failure_at").notNull().default(sql`NOW()`),
  lockedUntil: timestamp("locked_until"),
  lockoutCount: integer("lockout_count").notNull().default(0),
  updatedAt: timestamp("updated_at").notNull().default(sql`NOW()`),
});

// ── 3. Two-Factor Auth TOTP Secrets ──────────────────────────────────────────

export const totpSecrets = pgTable("totp_secrets", {
  userId: text("user_id").primaryKey(),
  encryptedSecret: text("encrypted_secret").notNull(),
  enabled: boolean("enabled").notNull().default(false),
  backupCodes: text("backup_codes").notNull().default("[]"),   // JSON encrypted[]
  usedBackupCodes: text("used_backup_codes").notNull().default("[]"),
  lastUsed: timestamp("last_used"),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
  updatedAt: timestamp("updated_at").notNull().default(sql`NOW()`),
});

// ── 4. Login Anomaly Profiles — REMOVED 2026-07-26 ───────────────────────────
// The loginProfiles pgTable ("login_profiles") was removed as a superseded orphan
// duplicate. The live login-anomaly baseline is M68 login_anomaly_baseline
// (login-anomaly-baseline-store.ts loadBaseline/saveBaseline, wired to
// analyzeLoginAttempt/recordLogin on the login path, populated). login_profiles had
// 0 rows, 0 writers (saveLoginProfile never called), one dead-ended reader
// (getLoginProfile <- the removed GET /api/security/login-profiles/:userId); its
// denormalized columns (avgSessionDuration/anomalyCount/lastKnownIp) were read by no
// live code. Table DEF removed so nothing tracks/recreates it; the empty physical
// relation is left UNTRACKED (like login_anomaly_baseline) — the boot push --force
// drop is intentionally NOT exercised; drop later by a deliberate migration if wanted.

// ── 5. Data Retention Policies ────────────────────────────────────────────────

export const retentionPolicies = pgTable("retention_policies", {
  id: varchar("id").primaryKey(),
  name: text("name").notNull(),
  description: text("description"),
  dataCategory: text("data_category").notNull(),
  retentionDays: integer("retention_days").notNull(),
  archiveAfterDays: integer("archive_after_days"),
  deleteAfterDays: integer("delete_after_days"),
  legalBasis: text("legal_basis"),
  regulation: text("regulation"),
  isActive: boolean("is_active").notNull().default(true),
  exceptionsJson: text("exceptions_json").notNull().default("[]"),
  tenantId: text("tenant_id"),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
  updatedAt: timestamp("updated_at").notNull().default(sql`NOW()`),
}).enableRLS();

export const retentionExecutions = pgTable("retention_executions", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  tenantId: text("tenant_id"), // AS/CYBER/CLIENTDATA-ISOLATION 2026-06-24 — child of retention_policies; RLS via TENANT_TABLES. Nullable Phase-1 (boot-DDL M53 backfills from parent; RLS WITH CHECK rejects NULL/foreign); SET NOT NULL deferred to Phase-2.
  policyId: varchar("policy_id").notNull(),
  status: text("status").notNull(),
  recordsScanned: integer("records_scanned").notNull().default(0),
  recordsArchived: integer("records_archived").notNull().default(0),
  recordsDeleted: integer("records_deleted").notNull().default(0),
  errors: text("errors").notNull().default("[]"),
  durationMs: integer("duration_ms"),
  executedAt: timestamp("executed_at").notNull().default(sql`NOW()`),
}).enableRLS();

// ── 6. Observer Agent (AI safety audit trail) ─────────────────────────────────

export const agentCollisionEvents = pgTable("agent_collision_events", {
  id: varchar("id").primaryKey(),
  agents: text("agents").notNull().default("[]"),
  collisionType: text("collision_type").notNull(),
  severity: text("severity").notNull(),
  description: text("description").notNull(),
  resolution: text("resolution").notNull(),
  contextJson: text("context_json").notNull().default("{}"),
  resolvedAt: timestamp("resolved_at"),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
});

export const agentOperationalPauses = pgTable("agent_operational_pauses", {
  id: varchar("id").primaryKey(),
  requestedBy: text("requested_by").notNull(),
  reason: text("reason").notNull(),
  affectedAgents: text("affected_agents").notNull().default("[]"),
  severity: text("severity").notNull(),
  status: text("status").notNull().default("ACTIVE"),
  autoResumeAt: timestamp("auto_resume_at"),
  resolvedAt: timestamp("resolved_at"),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
});

// ── 7. Tenant Billing ─────────────────────────────────────────────────────────

export const billingTenants = pgTable("billing_tenants", {
  id: varchar("id").primaryKey(),
  name: text("name").notNull(),
  tier: text("tier").notNull(),
  industry: text("industry").notNull(),
  contractStart: timestamp("contract_start").notNull(),
  contractEnd: timestamp("contract_end").notNull(),
  monthlyCommitmentUsd: numeric("monthly_commitment_usd", { precision: 12, scale: 2 }).notNull(),
  departmentsJson: text("departments_json").notNull().default("[]"),
  isActive: boolean("is_active").notNull().default(true),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
  updatedAt: timestamp("updated_at").notNull().default(sql`NOW()`),
});

export const billingInvoices = pgTable("billing_invoices", {
  id: varchar("id").primaryKey(),
  tenantId: varchar("tenant_id").notNull(),
  period: text("period").notNull(),          // e.g. "2025-03"
  status: text("status").notNull().default("DRAFT"),
  lineItemsJson: text("line_items_json").notNull().default("[]"),
  subtotalUsd: numeric("subtotal_usd", { precision: 12, scale: 2 }).notNull(),
  taxUsd: numeric("tax_usd", { precision: 12, scale: 2 }).notNull().default("0"),
  totalUsd: numeric("total_usd", { precision: 12, scale: 2 }).notNull(),
  dueAt: timestamp("due_at"),
  paidAt: timestamp("paid_at"),
  generatedAt: timestamp("generated_at").notNull().default(sql`NOW()`),
}).enableRLS();

export const billingUsageRecords = pgTable("billing_usage_records", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  tenantId: varchar("tenant_id").notNull(),
  departmentId: text("department_id"),
  category: text("category").notNull(),
  units: integer("units").notNull(),
  costUsd: numeric("cost_usd", { precision: 12, scale: 4 }).notNull(),
  metadata: text("metadata"),
  recordedAt: timestamp("recorded_at").notNull().default(sql`NOW()`),
}).enableRLS();

// ── 8. Mesh Network Nodes ─────────────────────────────────────────────────────

export const meshNodes = pgTable("mesh_nodes", {
  id: varchar("id").primaryKey(),
  name: text("name").notNull(),
  region: text("region").notNull(),
  type: text("type").notNull(),
  status: text("status").notNull().default("ONLINE"),
  ipAddress: text("ip_address"),
  latencyMs: integer("latency_ms"),
  trustScore: numeric("trust_score", { precision: 4, scale: 3 }).notNull().default("1"),
  lastSyncAt: timestamp("last_sync_at"),
  connectionsJson: text("connections_json").notNull().default("[]"),
  capabilitiesJson: text("capabilities_json").notNull().default("[]"),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
  updatedAt: timestamp("updated_at").notNull().default(sql`NOW()`),
});

// ── 9. HSM Keys & Operations ──────────────────────────────────────────────────

export const hsmKeys = pgTable("hsm_keys", {
  id: varchar("id").primaryKey(),
  purpose: text("purpose").notNull(),
  algorithm: text("algorithm").notNull(),
  keySize: integer("key_size").notNull(),
  publicKey: text("public_key"),
  status: text("status").notNull().default("ACTIVE"),
  version: integer("version").notNull().default(1),
  attestationHash: text("attestation_hash"),
  expiresAt: timestamp("expires_at"),
  rotatedFrom: varchar("rotated_from"),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
});

export const hsmOperations = pgTable("hsm_operations", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  keyId: varchar("key_id").notNull(),
  operation: text("operation").notNull(),
  requesterId: text("requester_id"),
  dataHash: text("data_hash"),
  success: boolean("success").notNull(),
  durationMs: integer("duration_ms"),
  errorMessage: text("error_message"),
  performedAt: timestamp("performed_at").notNull().default(sql`NOW()`),
});

// ── 10. Model Drift History ───────────────────────────────────────────────────

export const modelDriftHistory = pgTable("model_drift_history", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  modelId: text("model_id").notNull(),
  modelVersion: text("model_version").notNull(),
  psiScore: numeric("psi_score", { precision: 6, scale: 4 }).notNull(),
  ksScore: numeric("ks_score", { precision: 6, scale: 4 }).notNull(),
  accuracy: numeric("accuracy", { precision: 5, scale: 4 }),
  falsePositiveRate: numeric("false_positive_rate", { precision: 5, scale: 4 }),
  falseNegativeRate: numeric("false_negative_rate", { precision: 5, scale: 4 }),
  featureDriftsJson: text("feature_drifts_json").notNull().default("[]"),
  status: text("status").notNull(),
  retrainingTriggered: boolean("retraining_triggered").notNull().default(false),
  tenantId: text("tenant_id"), // AS/PLATFORM/2026/008 Tenant-Sep Chunk A — RLS-enrolled (nullable in Drizzle; DB NOT NULL via boot-DDL precheck-guarded)
  recordedAt: timestamp("recorded_at").notNull().default(sql`NOW()`),
}).enableRLS();

// ── 11. Agentic Commerce Agent Registry ──────────────────────────────────────

export const agentRegistry = pgTable("agent_registry", {
  id: varchar("id").primaryKey(),
  name: text("name").notNull(),
  type: text("type").notNull(),
  encryptedApiKey: text("encrypted_api_key").notNull(),
  permissions: text("permissions").notNull().default("[]"),
  status: text("status").notNull().default("ACTIVE"),
  requestsToday: integer("requests_today").notNull().default(0),
  totalRequests: integer("total_requests").notNull().default(0),
  lastActivityAt: timestamp("last_activity_at"),
  suspendedAt: timestamp("suspended_at"),
  suspendReason: text("suspend_reason"),
  tenantId: text("tenant_id"),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
  updatedAt: timestamp("updated_at").notNull().default(sql`NOW()`),
}).enableRLS();

// ── 12. Adaptive Thresholds ───────────────────────────────────────────────────

export const adaptiveThresholdConfigs = pgTable("adaptive_threshold_configs", {
  id: varchar("id").primaryKey(),
  name: text("name").notNull(),
  currentValue: numeric("current_value", { precision: 12, scale: 4 }).notNull(),
  minValue: numeric("min_value", { precision: 12, scale: 4 }).notNull(),
  maxValue: numeric("max_value", { precision: 12, scale: 4 }).notNull(),
  defaultValue: numeric("default_value", { precision: 12, scale: 4 }).notNull(),
  autoTuneEnabled: boolean("auto_tune_enabled").notNull().default(true),
  autoTuneSettingsJson: text("auto_tune_settings_json").notNull().default("{}"),
  metricsJson: text("metrics_json").notNull().default("{}"),
  historyJson: text("history_json").notNull().default("[]"),
  tenantId: text("tenant_id"),
  lastUpdated: timestamp("last_updated").notNull().default(sql`NOW()`),
  updatedBy: text("updated_by").notNull().default("SYSTEM"),
}).enableRLS();

// ═══════════════════════════════════════════════════════════════════════════════
// NEW FEATURES
// ═══════════════════════════════════════════════════════════════════════════════

// ── A. API Changelog / Versioning ─────────────────────────────────────────────

export const apiChangelog = pgTable("api_changelog", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  version: text("version").notNull(),           // e.g. "v2.3.1"
  changeType: text("change_type").notNull(),     // breaking|feature|fix|deprecation|security
  endpoint: text("endpoint"),
  method: text("method"),
  title: text("title").notNull(),
  description: text("description").notNull(),
  migrationGuide: text("migration_guide"),
  affectedClients: text("affected_clients").notNull().default("[]"),
  deprecatedAt: timestamp("deprecated_at"),
  removedAt: timestamp("removed_at"),
  publishedAt: timestamp("published_at").notNull().default(sql`NOW()`),
  authorId: text("author_id"),
});

// ── B. Revenue Analytics (real ARR/MRR engine) ────────────────────────────────

export const revenueSnapshots = pgTable("revenue_snapshots", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  period: text("period").notNull(),             // "2025-03" ISO month
  mrr: numeric("mrr", { precision: 14, scale: 2 }).notNull(),
  arr: numeric("arr", { precision: 14, scale: 2 }).notNull(),
  newMrr: numeric("new_mrr", { precision: 14, scale: 2 }).notNull().default("0"),
  expansionMrr: numeric("expansion_mrr", { precision: 14, scale: 2 }).notNull().default("0"),
  contractionMrr: numeric("contraction_mrr", { precision: 14, scale: 2 }).notNull().default("0"),
  churnMrr: numeric("churn_mrr", { precision: 14, scale: 2 }).notNull().default("0"),
  netNewMrr: numeric("net_new_mrr", { precision: 14, scale: 2 }).notNull().default("0"),
  activeCustomers: integer("active_customers").notNull().default(0),
  churnedCustomers: integer("churned_customers").notNull().default(0),
  newCustomers: integer("new_customers").notNull().default(0),
  avgRevenuePerCustomer: numeric("avg_revenue_per_customer", { precision: 12, scale: 2 }),
  growthRatePct: numeric("growth_rate_pct", { precision: 6, scale: 2 }),
  snapshotAt: timestamp("snapshot_at").notNull().default(sql`NOW()`),
});

// ── C. Series B Readiness Tracker ─────────────────────────────────────────────

export const seriesBMetrics = pgTable("series_b_metrics", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  category: text("category").notNull(),         // revenue|product|team|market|compliance|security
  metricName: text("metric_name").notNull(),
  currentValue: numeric("current_value", { precision: 14, scale: 4 }),
  targetValue: numeric("target_value", { precision: 14, scale: 4 }),
  unit: text("unit").notNull().default(""),
  status: text("status").notNull().default("in_progress"),  // achieved|in_progress|at_risk|not_started
  notes: text("notes"),
  evidenceLinks: text("evidence_links").notNull().default("[]"),
  updatedBy: text("updated_by"),
  period: text("period"),                       // which quarter/milestone this tracks
  updatedAt: timestamp("updated_at").notNull().default(sql`NOW()`),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
});

// ── D. Penetration Test Scheduler ─────────────────────────────────────────────

export const penTests = pgTable("pen_tests", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  name: text("name").notNull(),
  testType: text("test_type").notNull(),         // internal|external|social_engineering|red_team|api|cloud
  scope: text("scope").notNull(),
  methodology: text("methodology").notNull().default("OWASP"),
  status: text("status").notNull().default("scheduled"),
  vendor: text("vendor"),
  leadTester: text("lead_tester"),
  scheduledStart: timestamp("scheduled_start"),
  scheduledEnd: timestamp("scheduled_end"),
  actualStart: timestamp("actual_start"),
  actualEnd: timestamp("actual_end"),
  criticalFindings: integer("critical_findings").notNull().default(0),
  highFindings: integer("high_findings").notNull().default(0),
  mediumFindings: integer("medium_findings").notNull().default(0),
  lowFindings: integer("low_findings").notNull().default(0),
  reportUrl: text("report_url"),
  executiveSummary: text("executive_summary"),
  remediationDeadline: timestamp("remediation_deadline"),
  remediatedAt: timestamp("remediated_at"),
  tenantId: text("tenant_id"),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
  updatedAt: timestamp("updated_at").notNull().default(sql`NOW()`),
}).enableRLS();

export const penTestFindings = pgTable("pen_test_findings", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  tenantId: text("tenant_id"), // AS/CYBER/CLIENTDATA-ISOLATION 2026-06-24 — child of pen_tests; RLS via TENANT_TABLES. Nullable Phase-1 (boot-DDL M53 backfills from parent; RLS WITH CHECK rejects NULL/foreign); SET NOT NULL deferred to Phase-2.
  penTestId: varchar("pen_test_id").notNull(),
  title: text("title").notNull(),
  severity: text("severity").notNull(),
  cvssScore: numeric("cvss_score", { precision: 4, scale: 1 }),
  cweId: text("cwe_id"),
  affectedAsset: text("affected_asset"),
  description: text("description").notNull(),
  proof: text("proof"),
  remediation: text("remediation"),
  status: text("status").notNull().default("open"),  // open|in_remediation|fixed|accepted
  fixedAt: timestamp("fixed_at"),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
}).enableRLS();

// ── E. SBOM Version History ───────────────────────────────────────────────────

export const sbomSnapshots = pgTable("sbom_snapshots", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  snapshotName: text("snapshot_name").notNull(),
  componentCount: integer("component_count").notNull().default(0),
  // null = NOT SCANNED (mirrors ai-bom.ts:102 "null = NOT SCANNED. Never 0."). generateSbom
  // runs no vuln/license scan and writes null; SbomVersionService derives real counts from the
  // vulnerabilityFindings register and writes numbers. A 0 here means "scanned, found none" —
  // it must never be a stand-in for "not scanned" (M70 made these nullable).
  criticalVulns: integer("critical_vulns"),
  highVulns: integer("high_vulns"),
  outdatedComponents: integer("outdated_components"),
  licenseIssues: integer("license_issues"),
  trigger: text("trigger").notNull().default("manual"),   // manual|ci_push|scheduled
  commitHash: text("commit_hash"),
  branchName: text("branch_name"),
  componentsJson: text("components_json").notNull().default("[]"),
  tenantId: text("tenant_id"),
  snapshotAt: timestamp("snapshot_at").notNull().default(sql`NOW()`),
}).enableRLS();

export const sbomDiffs = pgTable("sbom_diffs", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  tenantId: text("tenant_id"), // AS/CYBER/CLIENTDATA-ISOLATION 2026-06-24 — child of sbom_snapshots; RLS via TENANT_TABLES. Nullable Phase-1 (boot-DDL M53 backfills from from_snapshot's tenant; RLS WITH CHECK rejects NULL/foreign); SET NOT NULL deferred to Phase-2.
  fromSnapshotId: varchar("from_snapshot_id").notNull(),
  toSnapshotId: varchar("to_snapshot_id").notNull(),
  added: text("added").notNull().default("[]"),
  removed: text("removed").notNull().default("[]"),
  updated: text("updated").notNull().default("[]"),
  newVulns: text("new_vulns").notNull().default("[]"),
  resolvedVulns: text("resolved_vulns").notNull().default("[]"),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
}).enableRLS();

export type GhostShadowTx = typeof ghostShadowTransactions.$inferSelect;
export type TotpSecret = typeof totpSecrets.$inferSelect;
export type RetentionPolicy = typeof retentionPolicies.$inferSelect;
export type BillingInvoice = typeof billingInvoices.$inferSelect;
export type RevenueSnapshot = typeof revenueSnapshots.$inferSelect;
export type SeriesBMetric = typeof seriesBMetrics.$inferSelect;
export type PenTest = typeof penTests.$inferSelect;
export type SbomSnapshot = typeof sbomSnapshots.$inferSelect;