/**
 * AEGIS CYBER — Priority 2 + 3 Schema
 * shared/schema-integrations.ts
 *
 * Append to shared/schema.ts:
 *   export * from "./schema-integrations";
 */

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

// ─── ServiceNow ───────────────────────────────────────────────────────────────

export const servicenowConfigs = pgTable("servicenow_configs", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  tenantId: text("tenant_id"),
  instanceUrl: text("instance_url").notNull(),
  username: text("username").notNull(),
  encryptedPassword: text("encrypted_password").notNull(),
  defaultAssignmentGroup: text("default_assignment_group"),
  incidentTable: text("incident_table").notNull().default("incident"),
  changeTable: text("change_table").notNull().default("change_request"),
  autoCreateOnP1: boolean("auto_create_on_p1").notNull().default(true),
  autoCreateOnP2: boolean("auto_create_on_p2").notNull().default(true),
  isActive: boolean("is_active").notNull().default(true),
  lastTestAt: timestamp("last_test_at"),
  lastTestStatus: text("last_test_status"),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
  updatedAt: timestamp("updated_at").notNull().default(sql`NOW()`),
}).enableRLS();

export const servicenowSyncLog = pgTable("servicenow_sync_log", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  tenantId: text("tenant_id"), // AS/CYBER/CLIENTDATA-ISOLATION 2026-06-24 — the triggering tenant (was dropped on insert; latent gap, not platform-scoped). RLS via TENANT_TABLES; writer sets current_tenant_id(); boot-DDL M54 backfills from parent config. Nullable Phase-1.
  configId: varchar("config_id").notNull(),
  direction: text("direction").notNull(),           // outbound | inbound
  recordType: text("record_type").notNull(),        // incident | change_request
  aegisRecordId: text("aegis_record_id").notNull(),
  snowNumber: text("snow_number"),                  // INC0012345
  snowSysId: text("snow_sys_id"),
  status: text("status").notNull(),                 // success | failed
  errorMessage: text("error_message"),
  durationMs: integer("duration_ms"),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
}).enableRLS();

// ─── PagerDuty ────────────────────────────────────────────────────────────────

export const pagerdutyConfigs = pgTable("pagerduty_configs", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  tenantId: text("tenant_id"),
  encryptedApiKey: text("encrypted_api_key").notNull(),
  defaultServiceId: text("default_service_id").notNull(),
  defaultEscalationPolicyId: text("default_escalation_policy_id"),
  // Severity mapping: AEGIS severity → PagerDuty severity
  severityMap: text("severity_map").notNull()
    .default('{"P1":"critical","P2":"error","P3":"warning","P4":"info"}'),
  autoResolveOnClose: boolean("auto_resolve_on_close").notNull().default(true),
  isActive: boolean("is_active").notNull().default(true),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
  updatedAt: timestamp("updated_at").notNull().default(sql`NOW()`),
}).enableRLS();

export const pagerdutyIncidents = pgTable("pagerduty_incidents", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  tenantId: text("tenant_id"), // AS/CYBER/CLIENTDATA-ISOLATION 2026-06-24 — the triggering tenant (was dropped on insert; latent gap, not platform-scoped). RLS via TENANT_TABLES; writer sets current_tenant_id(); boot-DDL M54 backfills from parent config. Nullable Phase-1.
  configId: varchar("config_id").notNull(),
  aegisIncidentId: text("aegis_incident_id"),
  aegisAlertId: text("aegis_alert_id"),
  pdIncidentId: text("pd_incident_id"),
  pdIncidentNumber: integer("pd_incident_number"),
  dedupeKey: text("dedupe_key").notNull().unique(),
  title: text("title").notNull(),
  severity: text("severity").notNull(),
  status: text("status").notNull().default("triggered"), // triggered | acknowledged | resolved
  triggeredAt: timestamp("triggered_at").notNull().default(sql`NOW()`),
  acknowledgedAt: timestamp("acknowledged_at"),
  resolvedAt: timestamp("resolved_at"),
}).enableRLS();

// ─── SCIM 2.0 Provisioning ───────────────────────────────────────────────────

export const scimConfigs = pgTable("scim_configs", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  tenantId: text("tenant_id").notNull().unique(),
  providerName: text("provider_name").notNull(), // azure_ad | okta | google | onelogin
  encryptedBearerToken: text("encrypted_bearer_token").notNull(),
  isActive: boolean("is_active").notNull().default(true),
  lastSyncAt: timestamp("last_sync_at"),
  totalUsersProvisioned: integer("total_users_provisioned").notNull().default(0),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
  updatedAt: timestamp("updated_at").notNull().default(sql`NOW()`),
}).enableRLS();

export const scimProvisions = pgTable("scim_provisions", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  tenantId: text("tenant_id").notNull(),
  externalId: text("external_id").notNull(),      // IdP's identifier
  userName: text("user_name").notNull(),
  displayName: text("display_name"),
  email: text("email"),
  groups: text("groups").notNull().default("[]"), // JSON string[]
  active: boolean("active").notNull().default(true),
  localUserId: varchar("local_user_id"),          // → users.id once mapped
  syncStatus: text("sync_status").notNull().default("synced"),
  lastSyncAt: timestamp("last_sync_at").notNull().default(sql`NOW()`),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
  updatedAt: timestamp("updated_at").notNull().default(sql`NOW()`),
}).enableRLS();

// ─── DSAR Workflow (Priority 3) ───────────────────────────────────────────────

export const dsarRequests = pgTable("dsar_requests", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  reference: text("reference").notNull().unique(),   // DSAR-2025-XXXX
  tenantId: text("tenant_id"),
  // Subject
  subjectName: text("subject_name").notNull(),
  subjectEmail: text("subject_email").notNull(),
  subjectPhone: text("subject_phone"),
  subjectNationalId: text("subject_national_id"),
  // Request
  requestType: text("request_type").notNull(), // access | erasure | portability | rectification | restriction | objection
  requestChannel: text("request_channel").notNull().default("portal"), // portal | email | in_person | phone
  description: text("description"),
  // SLA
  receivedAt: timestamp("received_at").notNull().default(sql`NOW()`),
  slaDeadline: timestamp("sla_deadline").notNull(),  // 30 days per PDPO
  status: text("status").notNull().default("received"),
  // Workflow
  assignedTo: text("assigned_to"),
  dataExtractUrl: text("data_extract_url"),
  responseNotes: text("response_notes"),
  regulatorySubmittedAt: timestamp("regulatory_submitted_at"),
  regulatoryRef: text("regulatory_ref"),
  completedAt: timestamp("completed_at"),
  // Audit
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
  updatedAt: timestamp("updated_at").notNull().default(sql`NOW()`),
}).enableRLS();

export const dsarAuditTrail = pgTable("dsar_audit_trail", {
  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 parent-join backfill then SET NOT NULL
  dsarId: varchar("dsar_id").notNull(),
  action: text("action").notNull(),       // received | assigned | processing | data_located | response_sent | completed | regulator_notified
  performedBy: text("performed_by"),
  notes: text("notes"),
  createdAt: timestamp("created_at").notNull().default(sql`NOW()`),
}).enableRLS();

// ─── DSAR Purge Jobs (added 2026-05-05) ───────────────────────────────────────
//
// Queue + receipt log for the DSAR purge worker. Bridges the gap between a
// `dsar_requests` row (request_type = "erasure") and the destructive cascade
// in `runErasureDrill()` (server/lib/wave13-pilot-readiness.ts:198), which
// writes its receipts to `erasure_drills`.
//
// Forward direction:  dsar_requests.id ──(dsar_id)──> dsar_purge_jobs ──(drill_ref)──> erasure_drills.drill_ref
// Backward direction: erasure_drills.drill_ref ──(drill_ref)──> dsar_purge_jobs.dsar_id ──> dsar_requests
//
// State machine: pending → running → completed | failed | skipped
//   - completed:  drill returned passed=1, DSAR advanced to "completed"
//   - failed:     drill threw or returned passed=0, DSAR reverted to "processing" for DPO review
//   - skipped:    drill detected priorErasureFound=1, DSAR advanced to "completed" without re-running
//                 (prior_drill_ref captures the receipt of the earlier erasure that did the work)
//
// Schema-pinning rule (PLATFORM_BEHAVIOUR_NOTES, 2026-05-05): explicit `_key`
// suffix on the unique constraint matches Postgres' default naming so the
// schema stays in sync with what prod will create at db:push time.
export const dsarPurgeJobs = pgTable("dsar_purge_jobs", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  jobRef: varchar("job_ref", { length: 64 }).notNull().unique("dsar_purge_jobs_job_ref_key"),
  dsarId: varchar("dsar_id").notNull(),                              // logical FK to dsar_requests.id
  customerRef: varchar("customer_ref", { length: 128 }).notNull(),   // resolved by DPO at enqueue time
  status: varchar("status", { length: 32 }).notNull().default("pending"),  // pending|running|completed|failed|skipped
  enqueuedBy: varchar("enqueued_by", { length: 128 }).notNull(),
  enqueuedAt: timestamp("enqueued_at", { withTimezone: true }).notNull().default(sql`NOW()`),
  startedAt: timestamp("started_at", { withTimezone: true }),
  completedAt: timestamp("completed_at", { withTimezone: true }),
  attempt: integer("attempt").notNull().default(0),
  drillRef: varchar("drill_ref", { length: 64 }),                    // links to erasure_drills.drill_ref on completed/failed
  priorDrillRef: varchar("prior_drill_ref", { length: 64 }),         // links to the earlier erasure on skipped
  realRowsErased: integer("real_rows_erased"),
  legallyRetainedRows: integer("legally_retained_rows"),
  priorErasureFound: integer("prior_erasure_found"),
  receiptHash: varchar("receipt_hash", { length: 128 }),             // copy from drill receipt for quick lookup
  signatureHex: varchar("signature_hex", { length: 256 }),           // copy from drill receipt
  errorMessage: text("error_message"),
  detailsJson: jsonb("details_json").notNull().default("{}"),
  // Shape 2a (T003 background-worker bypass remediation): carried from
  // dsar_requests.tenantId at enqueue time so processClaimedJob can open a
  // withTenantRls() phase without a bypass SELECT. Null for jobs enqueued
  // before T003 landed (legacy path: withBypassRls bootstrap SELECT then
  // withTenantRls if resolved, terminal-failed if still null).
  tenantId: varchar("tenant_id"),
}, (t) => ({
  statusIdx: index("dsar_purge_jobs_status_idx").on(t.status),
  enqueuedAtIdx: index("dsar_purge_jobs_enqueued_at_idx").on(t.enqueuedAt.desc()),
  dsarIdIdx: index("dsar_purge_jobs_dsar_id_idx").on(t.dsarId),
  // Spec #1 (2026-05-05): partial index supports backward lookup from an
  // erasure_drills receipt to the DSAR that triggered it. WHERE clause keeps
  // the index from bloating with NULLs (pending/failed-pre-drill rows).
  drillRefIdx: index("dsar_purge_jobs_drill_ref_idx").on(t.drillRef).where(sql`drill_ref IS NOT NULL`),
  // Architect Severe #2 (2026-05-05): the application-layer in-flight check in
  // enqueueDsarPurgeJob() is select-then-insert and therefore racy. This
  // partial unique index is the authoritative gate against concurrent enqueue
  // calls — at most one (pending|running) row per dsar_id. The application
  // check is preserved as the friendly common-path 409; this index catches
  // the race-window case via 23505 unique_violation, also returning 409.
  // Naming: _idx suffix (not _key) — uniqueIndex() vs .unique() distinction
  // per FU-013 convention.
  inFlightUniqueIdx: uniqueIndex("dsar_purge_jobs_in_flight_unique_idx")
    .on(t.dsarId)
    .where(sql`status IN ('pending', 'running')`),
}));

// ─── Insert schemas + types ───────────────────────────────────────────────────

export const insertServicenowConfigSchema = createInsertSchema(servicenowConfigs)
  .omit({ id: true, createdAt: true, updatedAt: true });

export const insertPagerdutyConfigSchema = createInsertSchema(pagerdutyConfigs)
  .omit({ id: true, createdAt: true, updatedAt: true });

export const insertScimConfigSchema = createInsertSchema(scimConfigs)
  .omit({ id: true, createdAt: true, updatedAt: true });

export const insertDsarRequestSchema = createInsertSchema(dsarRequests)
  .omit({ id: true, createdAt: true, updatedAt: true, receivedAt: true, slaDeadline: true });

export const insertDsarPurgeJobSchema = createInsertSchema(dsarPurgeJobs)
  .omit({ id: true, enqueuedAt: true, startedAt: true, completedAt: true });

export type ServicenowConfig = typeof servicenowConfigs.$inferSelect;
export type PagerdutyConfig = typeof pagerdutyConfigs.$inferSelect;
export type PagerdutyIncident = typeof pagerdutyIncidents.$inferSelect;
export type ScimConfig = typeof scimConfigs.$inferSelect;
export type ScimProvision = typeof scimProvisions.$inferSelect;
export type DsarRequest = typeof dsarRequests.$inferSelect;
export type DsarPurgeJob = typeof dsarPurgeJobs.$inferSelect;
export type InsertDsarPurgeJob = typeof insertDsarPurgeJobSchema._type;