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

export const TENANT_ROLES = ["admin", "risk_manager", "auditor", "viewer"] as const;
export type TenantRole = typeof TENANT_ROLES[number];

export const userTenantRoles = pgTable("user_tenant_roles", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  userId: varchar("user_id").notNull(),
  tenantId: varchar("tenant_id").notNull(),
  role: text("role").notNull(),
  grantedBy: varchar("granted_by"),
  grantedAt: timestamp("granted_at").notNull().default(sql`NOW()`),
  revokedAt: timestamp("revoked_at"),
  isActive: boolean("is_active").notNull().default(true),
  isPrimary: boolean("is_primary").notNull().default(false),
  notes: text("notes"),
}, (t) => ({
  utrPrimaryPerUser: uniqueIndex("utr_primary_per_user")
    .on(t.userId)
    .where(sql`is_active = true AND is_primary = true`),
})).enableRLS();

export const rbacAuditLog = pgTable("rbac_audit_log", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  userId: varchar("user_id").notNull(),
  tenantId: varchar("tenant_id"),
  action: text("action").notNull(),
  fromRole: text("from_role"),
  toRole: text("to_role"),
  performedBy: varchar("performed_by").notNull(),
  reason: text("reason"),
  ipAddress: text("ip_address"),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
}).enableRLS();

export const insertUserTenantRoleSchema = createInsertSchema(userTenantRoles).extend({
  role: z.enum(TENANT_ROLES),
}).omit({ id: true, grantedAt: true });

export type UserTenantRole = typeof userTenantRoles.$inferSelect;
export type InsertUserTenantRole = typeof userTenantRoles.$inferInsert;
export type RbacAuditEntry = typeof rbacAuditLog.$inferSelect;

export const loadTestRuns = pgTable("load_test_runs", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  scenario: text("scenario").notNull(),
  concurrency: integer("concurrency").notNull(),
  durationMs: integer("duration_ms").notNull(),
  totalRequests: integer("total_requests").notNull(),
  successCount: integer("success_count").notNull(),
  errorCount: integer("error_count").notNull(),
  throughputPerSec: real("throughput_per_sec").notNull(),
  latencyAvgMs: real("latency_avg_ms").notNull(),
  latencyMinMs: real("latency_min_ms").notNull(),
  latencyMaxMs: real("latency_max_ms").notNull(),
  latencyP50Ms: real("latency_p50_ms").notNull(),
  latencyP95Ms: real("latency_p95_ms").notNull(),
  latencyP99Ms: real("latency_p99_ms").notNull(),
  cpuLoadAvg: real("cpu_load_avg"),
  memoryRssMb: real("memory_rss_mb"),
  triggeredBy: text("triggered_by").notNull(),
  notes: text("notes"),
  startedAt: timestamp("started_at").notNull().default(sql`NOW()`),
  finishedAt: timestamp("finished_at").notNull().default(sql`NOW()`),
}, (t) => ({
  // Backs the regression-alert baseline lookup in /api/load-test/suite:
  //   WHERE scenario = ? AND error_count = 0 ORDER BY started_at DESC LIMIT 10
  // Composite index keeps that query on an index scan as run history grows.
  baselineLookupIdx: index("load_test_runs_scenario_err_started_idx")
    .on(t.scenario, t.errorCount, t.startedAt.desc()),
}));

export const insertLoadTestRunSchema = createInsertSchema(loadTestRuns).omit({ id: true, startedAt: true, finishedAt: true });
export type LoadTestRun = typeof loadTestRuns.$inferSelect;
export type InsertLoadTestRun = z.infer<typeof insertLoadTestRunSchema>;

// ========================================
// Pilot-Readiness Tables (regulator sandbox evidence)
// ========================================

export const auditChainVerifications = pgTable("audit_chain_verifications", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  valid: integer("valid").notNull(),
  totalEntries: integer("total_entries").notNull(),
  brokenAt: integer("broken_at"),
  lastHash: text("last_hash").notNull(),
  durationMs: integer("duration_ms").notNull(),
  triggeredBy: text("triggered_by").notNull(),
  proofJson: text("proof_json"),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
});

export const securityTestRuns = pgTable("security_test_runs", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  testType: text("test_type").notNull(),
  passed: integer("passed").notNull(),
  totalChecks: integer("total_checks").notNull(),
  failedChecks: integer("failed_checks").notNull(),
  summary: text("summary").notNull(),
  detailsJson: text("details_json").notNull(),
  durationMs: integer("duration_ms").notNull(),
  triggeredBy: text("triggered_by").notNull(),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
});

export const breachDrillRuns = pgTable("breach_drill_runs", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  scenario: text("scenario").notNull(),
  totalDurationMs: integer("total_duration_ms").notNull(),
  stepsCompleted: integer("steps_completed").notNull(),
  totalSteps: integer("total_steps").notNull(),
  within72h: integer("within_72h").notNull(),
  stepTimingsJson: text("step_timings_json").notNull(),
  triggeredBy: text("triggered_by").notNull(),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
});

export const backupRestoreTests = pgTable("backup_restore_tests", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  passed: integer("passed").notNull(),
  tablesChecked: integer("tables_checked").notNull(),
  tablesMatched: integer("tables_matched").notNull(),
  totalRowsCopied: integer("total_rows_copied").notNull(),
  durationMs: integer("duration_ms").notNull(),
  resultsJson: text("results_json").notNull(),
  triggeredBy: text("triggered_by").notNull(),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
});

export type AuditChainVerification = typeof auditChainVerifications.$inferSelect;
export type SecurityTestRun = typeof securityTestRuns.$inferSelect;
export type BreachDrillRun = typeof breachDrillRuns.$inferSelect;
export type BackupRestoreTest = typeof backupRestoreTests.$inferSelect;