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

// 1. Regulator Challenge Console — regulator submits synthetic txn, receives full XAI bundle
export const regulatorChallenges = pgTable("regulator_challenges", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  submittedBy: text("submitted_by").notNull(),
  scenarioJson: text("scenario_json").notNull(),
  decision: text("decision").notNull(),
  confidenceScore: integer("confidence_score").notNull(),
  contributingFeaturesJson: text("contributing_features_json").notNull(),
  modelUsed: text("model_used").notNull(),
  modelCardId: text("model_card_id"),
  plainEnglishExplanation: text("plain_english_explanation").notNull(),
  appealEligible: integer("appeal_eligible").notNull().default(1),
  latencyMs: integer("latency_ms").notNull(),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
});

// 2. AML Typology Coverage Matrix — FATF / BoU rule mapping
export const amlTypologies = pgTable("aml_typologies", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  typologyCode: text("typology_code").notNull().unique(),
  name: text("name").notNull(),
  category: text("category").notNull(),
  source: text("source").notNull(),
  description: text("description").notNull(),
  detectionRule: text("detection_rule").notNull(),
  coverageStatus: text("coverage_status").notNull().default("covered"),
  lastTestedAt: timestamp("last_tested_at"),
  lastTestVerdict: text("last_test_verdict"),
  syntheticPositives: integer("synthetic_positives").notNull().default(0),
  syntheticDetected: integer("synthetic_detected").notNull().default(0),
  notes: text("notes"),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
  updatedAt: timestamp("updated_at").notNull().default(sql`NOW()`),
});

// 3. SAR Filing SLA Tracker — Financial Intelligence Authority required SAR latency
export const sarFilings = pgTable("sar_filings", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  sarRef: text("sar_ref").notNull().unique(),
  customerRef: text("customer_ref").notNull(),
  // AS/CYBER/CLIENTDATA-ISOLATION Batch 1b — tenant isolation (nullable Phase-1; SET NOT NULL deferred to Phase-2).
  tenantId: text("tenant_id"),
  suspicionType: text("suspicion_type").notNull(),
  amountUgxMicros: bigint("amount_ugx_micros", { mode: "number" }).notNull().default(0),
  draftedAt: timestamp("drafted_at").notNull().default(sql`NOW()`),
  filedAt: timestamp("filed_at"),
  acknowledgedAt: timestamp("acknowledged_at"),
  slaHours: integer("sla_hours").notNull().default(72),
  withinSla: integer("within_sla").notNull().default(0),
  fiaConfirmationRef: text("fia_confirmation_ref"),
  status: text("status").notNull().default("draft"),
  filedBy: text("filed_by"),
  notes: text("notes"),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
}).enableRLS();

// 4. Vulnerability Disclosure Portal — public no-auth intake
export const vdpReports = pgTable("vdp_reports", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  reporterHandle: text("reporter_handle"),
  reporterEmailHash: text("reporter_email_hash"),
  reportTitle: text("report_title").notNull(),
  reportSummary: text("report_summary").notNull(),
  severity: text("severity").notNull().default("unknown"),
  affectedComponent: text("affected_component"),
  status: text("status").notNull().default("received"),
  slaTriageHours: integer("sla_triage_hours").notNull().default(72),
  slaFixHours: integer("sla_fix_hours").notNull().default(2160),
  triagedAt: timestamp("triaged_at"),
  triageNotes: text("triage_notes"),
  triagedBy: text("triaged_by"),
  resolvedAt: timestamp("resolved_at"),
  resolutionNotes: text("resolution_notes"),
  resolvedBy: text("resolved_by"),
  publicAcknowledged: integer("public_acknowledged").notNull().default(0),
  ipAddress: text("ip_address"),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
});

// 5. Continuous Compliance Score — daily 0-100 aggregate
export const complianceScores = pgTable("compliance_scores", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  totalScore: integer("total_score").notNull(),
  drillsTotal: integer("drills_total").notNull(),
  drillsPassed: integer("drills_passed").notNull(),
  drillsFailed: integer("drills_failed").notNull(),
  drillsMissing: integer("drills_missing").notNull().default(0),
  breakdownJson: text("breakdown_json").notNull(),
  trendDirection: text("trend_direction").notNull().default("flat"),
  computedBy: text("computed_by").notNull(),
  computedAt: timestamp("computed_at").notNull().default(sql`NOW()`),
});

// 6. Mobile Money Fraud Pattern Suite — Uganda-specific MoMo / Airtel scenarios
export const momoFraudTests = pgTable("momo_fraud_tests", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  scenarioCode: text("scenario_code").notNull(),
  scenarioName: text("scenario_name").notNull(),
  provider: text("provider").notNull(),
  syntheticTxnsCount: integer("synthetic_txns_count").notNull(),
  truePositives: integer("true_positives").notNull(),
  falseNegatives: integer("false_negatives").notNull(),
  falsePositives: integer("false_positives").notNull(),
  precisionPct: integer("precision_pct").notNull(),
  recallPct: integer("recall_pct").notNull(),
  passed: integer("passed").notNull(),
  durationMs: integer("duration_ms").notNull(),
  detailsJson: text("details_json").notNull(),
  triggeredBy: text("triggered_by").notNull(),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
});

// 7. Right-to-Explanation — customer-facing decision explanation log
export const decisionExplanations = pgTable("decision_explanations", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  tenantId: text("tenant_id"), // AS/CYBER/CLIENTDATA-ISOLATION Batch 2-3 Pack 1 — nullable Phase-1; RLS via TENANT_TABLES entry; boot DDL backfills then SET NOT NULL
  transactionRef: text("transaction_ref").notNull(),
  customerRef: text("customer_ref").notNull(),
  decision: text("decision").notNull(),
  reasonCode: text("reason_code").notNull(),
  plainEnglishExplanation: text("plain_english_explanation").notNull(),
  contributingFactorsJson: text("contributing_factors_json").notNull(),
  modelCardId: text("model_card_id"),
  appealLink: text("appeal_link").notNull(),
  requestSourceIp: text("request_source_ip"),
  generatedAt: timestamp("generated_at").notNull().default(sql`NOW()`),
}).enableRLS();

export type RegulatorChallenge = typeof regulatorChallenges.$inferSelect;
export type AmlTypology = typeof amlTypologies.$inferSelect;
export type SarFiling = typeof sarFilings.$inferSelect;
export type VdpReport = typeof vdpReports.$inferSelect;
export type ComplianceScore = typeof complianceScores.$inferSelect;
export type MomoFraudTest = typeof momoFraudTests.$inferSelect;
export type DecisionExplanation = typeof decisionExplanations.$inferSelect;