packages/db/src/schema.ts

import {
  pgTable,
  uuid,
  text,
  jsonb,
  boolean,
  integer,
  bigint,
  numeric,
  timestamp,
  date,
  vector,
  uniqueIndex,
  index,
  customType,
} from "drizzle-orm/pg-core";
import { sql } from "drizzle-orm";

/** tsvector DB-only column type (Drizzle has no built-in; we use customType). */
const tsvector = customType<{ data: string }>({
  dataType() {
    return "tsvector";
  },
});

/**
 * Phase 1 schema (PHASE_1 §5, arch §9 + §30). Enums are stored as `text`; the
 * canonical vocabularies live in @courtmitra/contracts and are validated in the
 * app/domain layer. Every change here must be a Drizzle migration (CLAUDE.md #8).
 *
 * Embedding dimension targets OpenAI text-embedding-3-small (1536).
 */
const EMBEDDING_DIM = 1536;

// Reusable column builders.
const id = () => uuid("id").primaryKey().defaultRandom();
const createdAt = () => timestamp("created_at", { withTimezone: true }).defaultNow().notNull();
const updatedAt = () => timestamp("updated_at", { withTimezone: true }).defaultNow().notNull();

// ============================================================================
// Swarm / entity resolution (Phase 2 Module 1)
// ============================================================================

export const entities = pgTable(
  "entities",
  {
    id: id(),
    normalizedName: text("normalized_name").notNull(),
    aliases: text("aliases").array(), // e.g. {"Acme Pvt Ltd", "Acme", "acme.com"}
    domain: text("domain").notNull(), // consumer, payment, cyber_fraud
    identifiers: jsonb("identifiers"), // { gst: "...", website: "...", socialHandles: [...] }
    createdAt: createdAt(),
  },
  (t) => [
    uniqueIndex("entities_normalized_name_domain_uq").on(t.normalizedName, t.domain),
    index("entities_domain_idx").on(t.domain),
  ],
);

export const swarms = pgTable(
  "swarms",
  {
    id: id(),
    entityId: uuid("entity_id")
      .notNull()
      .references(() => entities.id),
    issueDomain: text("issue_domain").notNull(), // consumer, payment, cyber_fraud
    patternSignature: jsonb("pattern_signature"), // shared evidence/wording/route features
    caseCount: integer("case_count").default(0).notNull(),
    totalAmount: numeric("total_amount"),
    status: text("status").default("active").notNull(), // active, resolved, archived
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [
    index("swarms_entity_idx").on(t.entityId),
    index("swarms_domain_idx").on(t.issueDomain),
    index("swarms_status_idx").on(t.status),
  ],
);

export const swarmMembers = pgTable(
  "swarm_members",
  {
    swarmId: uuid("swarm_id")
      .notNull()
      .references(() => swarms.id),
    missionId: uuid("mission_id")
      .notNull()
      .references(() => missions.id),
    consentGrantId: uuid("consent_grant_id")
      .notNull()
      .references(() => consentGrants.id), // REQUIRED: per-mission aggregation consent
    contribution: jsonb("contribution"), // what this mission contributes to the swarm dossier
    joinedAt: createdAt(),
  },
  (t) => [
    uniqueIndex("swarm_members_swarm_mission_uq").on(t.swarmId, t.missionId),
    index("swarm_members_mission_idx").on(t.missionId),
  ],
);

// ============================================================================
// ASR / Transcripts (Phase 2 Module 2)
// ============================================================================

export const transcripts = pgTable("transcripts", {
  id: id(),
  evidenceItemId: uuid("evidence_item_id")
    .notNull()
    .references(() => evidenceItems.id),
  language: text("language"), // detected language code (e.g. "hi", "ta", "en")
  text: text("text").notNull(), // transcribed text
  model: text("model").notNull(), // "whisper-1", "whisper-cpp-base", etc.
  confidence: numeric("confidence"), // 0.0–1.0
  durationMs: integer("duration_ms"), // audio duration in milliseconds
  createdAt: createdAt(),
});

// ============================================================================
// Translation / localized texts (Phase 2 Module 3)
// ============================================================================

export const localizedTexts = pgTable("localized_texts", {
  id: id(),
  resourceType: text("resource_type").notNull(), // 'message', 'snapshot', 'prompt', 'evidence'
  resourceId: uuid("resource_id").notNull(), // FK to the source row
  languageCode: text("language_code").notNull(), // 'hi', 'ta', 'en', etc.
  originalText: text("original_text").notNull(), // always preserved (CLAUDE.md: never lose original)
  normalizedText: text("normalized_text"), // cleaned/transliterated form
  translatedText: text("translated_text"), // English working form (for internal use)
  modelVersion: text("model_version"), // 'llm-gemini', 'indictrans2-1b'
  confidence: numeric("confidence"),
  createdAt: createdAt(),
}, (t) => [
  index("localized_texts_resource_idx").on(t.resourceType, t.resourceId),
  index("localized_texts_language_idx").on(t.languageCode),
]);

// ============================================================================
// Identity / session
// ============================================================================

export const users = pgTable("users", {
  id: id(),
  displayName: text("display_name"),
  email: text("email"),
  avatarUrl: text("avatar_url"),
  phone: text("phone"),
  fullName: text("full_name"),
  preferredLanguage: text("preferred_language").default("en"),
  riskFlags: jsonb("risk_flags"),
  createdAt: createdAt(),
  updatedAt: updatedAt(),
});

export const userIdentities = pgTable(
  "user_identities",
  {
    id: id(),
    userId: uuid("user_id")
      .notNull()
      .references(() => users.id),
    provider: text("provider").notNull(), // whatsapp, telegram, google, email, phone, social
    providerSubject: text("provider_subject").notNull(),
    isVerified: boolean("is_verified").default(false).notNull(),
    metadata: jsonb("metadata"),
    createdAt: createdAt(),
  },
  (t) => [uniqueIndex("user_identities_provider_subject_uq").on(t.provider, t.providerSubject)],
);

export const sessions = pgTable("sessions", {
  id: id(),
  userId: uuid("user_id")
    .notNull()
    .references(() => users.id),
  sessionType: text("session_type").notNull(), // web, whatsapp, magic_link, oauth_callback
  channel: text("channel"),
  token: text("token"),
  expiresAt: timestamp("expires_at", { withTimezone: true }),
  createdAt: createdAt(),
});

export const accountLinkChallenges = pgTable("account_link_challenges", {
  id: id(),
  userId: uuid("user_id").references(() => users.id),
  challengeType: text("challenge_type").notNull(), // magic_link, whatsapp_code
  destination: text("destination").notNull(),
  codeHash: text("code_hash").notNull(),
  consumedAt: timestamp("consumed_at", { withTimezone: true }),
  expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(),
  createdAt: createdAt(),
});

export const oauthTokens = pgTable("oauth_tokens", {
  id: id(),
  userId: uuid("user_id")
    .notNull()
    .references(() => users.id),
  provider: text("provider").notNull(),
  accessTokenRef: text("access_token_ref").notNull(), // encrypted ref, never plaintext
  refreshTokenRef: text("refresh_token_ref"),
  scopes: jsonb("scopes"),
  expiresAt: timestamp("expires_at", { withTimezone: true }),
  createdAt: createdAt(),
});

// ============================================================================
// Conversation
// ============================================================================

export const conversations = pgTable(
  "conversations",
  {
    id: id(),
    userId: uuid("user_id").references(() => users.id),
    channel: text("channel").notNull(), // whatsapp, telegram, web, email
    externalThreadId: text("external_thread_id"),
    activeMissionId: uuid("active_mission_id"),
    sessionState: jsonb("session_state"),
    lastMessageAt: timestamp("last_message_at", { withTimezone: true }),
    createdAt: createdAt(),
  },
  // Fix 6: unique index prevents duplicate conversations on concurrent ingest.
  (t) => [uniqueIndex("conversations_channel_thread_uq").on(t.channel, t.externalThreadId)],
);

export const conversationWindows = pgTable("conversation_windows", {
  id: id(),
  conversationId: uuid("conversation_id")
    .notNull()
    .references(() => conversations.id),
  // WhatsApp 24h free service window.
  openedAt: timestamp("opened_at", { withTimezone: true }).notNull(),
  expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(),
  createdAt: createdAt(),
});

export const messageTemplates = pgTable("message_templates", {
  id: id(),
  channel: text("channel").notNull(),
  name: text("name").notNull(),
  language: text("language").default("en").notNull(),
  providerTemplateName: text("provider_template_name"), // no hardcoded ids — looked up by name
  body: text("body").notNull(),
  variables: jsonb("variables"),
  createdAt: createdAt(),
});

export const messages = pgTable(
  "messages",
  {
    id: id(),
    conversationId: uuid("conversation_id")
      .notNull()
      .references(() => conversations.id),
    missionId: uuid("mission_id"),
    direction: text("direction").notNull(), // inbound, outbound
    channel: text("channel").notNull(),
    channelMessageId: text("channel_message_id"),
    idempotencyKey: text("idempotency_key"),
    body: text("body"),
    mediaRefs: jsonb("media_refs"),
    rawPayload: jsonb("raw_payload"),
    createdAt: createdAt(),
  },
  (t) => [
    uniqueIndex("messages_idempotency_key_uq").on(t.idempotencyKey),
    index("messages_conversation_idx").on(t.conversationId, t.createdAt),
  ],
);

// ============================================================================
// Mission
// ============================================================================

export const missions = pgTable("missions", {
  id: id(),
  userId: uuid("user_id")
    .notNull()
    .references(() => users.id),
  title: text("title").notNull(),
  issueDomain: text("issue_domain").notNull(), // consumer, payment, cyber_fraud
  state: text("state").notNull().default("created"),
  jurisdiction: jsonb("jurisdiction"),
  opposingParty: jsonb("opposing_party"),
  reliefRequested: jsonb("relief_requested"),
  facts: jsonb("facts"),
  currentRoutePlanId: uuid("current_route_plan_id"),
  safetyLevel: text("safety_level").notNull().default("normal"), // normal, elevated, emergency
  createdAt: createdAt(),
  updatedAt: updatedAt(),
});

export const missionEvents = pgTable(
  "mission_events",
  {
    id: id(),
    missionId: uuid("mission_id")
      .notNull()
      .references(() => missions.id),
    eventType: text("event_type").notNull(),
    actorType: text("actor_type").notNull(), // user, ai, system, responder, admin
    actorId: text("actor_id"),
    payload: jsonb("payload"),
    createdAt: createdAt(),
  },
  (t) => [index("mission_events_mission_idx").on(t.missionId, t.createdAt)],
);

export const missionStatusSnapshots = pgTable(
  "mission_status_snapshots",
  {
    id: id(),
    missionId: uuid("mission_id")
      .notNull()
      .references(() => missions.id),
    state: text("state").notNull(),
    substate: text("substate"),
    headline: text("headline").notNull(),
    blockedReason: text("blocked_reason"),
    nextUserAction: text("next_user_action"),
    nextSystemAction: text("next_system_action"),
    primaryButton: text("primary_button"),
    danger: boolean("danger").default(false).notNull(),
    confidence: numeric("confidence"),
    createdAt: createdAt(),
  },
  (t) => [index("mission_status_snapshots_mission_idx").on(t.missionId, t.createdAt)],
);

export const missionLocks = pgTable("mission_locks", {
  missionId: uuid("mission_id").primaryKey(),
  lockedBy: text("locked_by").notNull(),
  lockedAt: timestamp("locked_at", { withTimezone: true }).defaultNow().notNull(),
  expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(),
});

// ============================================================================
// Evidence
// ============================================================================

export const files = pgTable(
  "files",
  {
    id: id(),
    missionId: uuid("mission_id").references(() => missions.id),
    uploaderUserId: uuid("uploader_user_id").references(() => users.id),
    storageKey: text("storage_key").notNull(),
    originalFilename: text("original_filename"),
    mimeType: text("mime_type"),
    sha256: text("sha256"),
    sizeBytes: bigint("size_bytes", { mode: "number" }),
    virusScanStatus: text("virus_scan_status").default("pending").notNull(), // pending, clean, infected, skipped
    processingStatus: text("processing_status").default("pending").notNull(), // pending, processing, done, failed
    retentionPolicy: text("retention_policy").default("standard").notNull(),
    deletedAt: timestamp("deleted_at", { withTimezone: true }),
    createdAt: createdAt(),
  },
  (t) => [index("files_mission_idx").on(t.missionId)],
);

export const evidenceItems = pgTable("evidence_items", {
  id: id(),
  missionId: uuid("mission_id")
    .notNull()
    .references(() => missions.id),
  fileId: uuid("file_id").references(() => files.id),
  uploaderUserId: uuid("uploader_user_id").references(() => users.id),
  type: text("type").notNull(), // receipt, screenshot, chat, email, pdf, audio, video, id_doc, other
  captureDate: timestamp("capture_date", { withTimezone: true }),
  sourceChannel: text("source_channel"),
  piiLevel: text("pii_level").default("unknown").notNull(),
  admissibilityNotes: jsonb("admissibility_notes"),
  createdAt: createdAt(),
});

export const evidenceParsedOutputs = pgTable(
  "evidence_parsed_outputs",
  {
    id: id(),
    evidenceItemId: uuid("evidence_item_id")
      .notNull()
      .references(() => evidenceItems.id),
    parserName: text("parser_name").notNull(),
    parserVersion: text("parser_version").notNull(),
    extractedText: text("extracted_text"),
    extractedEntities: jsonb("extracted_entities"),
    extractedDates: jsonb("extracted_dates"),
    extractedMoney: jsonb("extracted_money"),
    // Fix 5: parties column to round-trip ParsedEvidenceDto faithfully.
    extractedParties: jsonb("extracted_parties"),
    ocrConfidence: numeric("ocr_confidence"),
    pageSpans: jsonb("page_spans"),
    createdAt: createdAt(),
  },
  // Fix 7: unique index prevents duplicate rows on Inngest step replay.
  (t) => [
    uniqueIndex("evidence_parsed_outputs_item_parser_uq").on(
      t.evidenceItemId,
      t.parserName,
      t.parserVersion,
    ),
  ],
);

export const evidenceClaims = pgTable("evidence_claims", {
  id: id(),
  missionId: uuid("mission_id")
    .notNull()
    .references(() => missions.id),
  evidenceItemId: uuid("evidence_item_id").references(() => evidenceItems.id),
  claimText: text("claim_text").notNull(),
  claimType: text("claim_type"),
  supportLevel: text("support_level").notNull(), // explicit, inferred, weak, contradicted
  sourceSpan: jsonb("source_span"),
  createdAt: createdAt(),
});

// ============================================================================
// Legal / RAG (curated, small)
// ============================================================================

export const legalSources = pgTable("legal_sources", {
  id: id(),
  sourceType: text("source_type").notNull(), // act, rule, regulator_doc, govt_page, court_judgment, policy
  title: text("title").notNull(),
  tier: text("tier").notNull(), // tier1, tier2, tier3
  jurisdiction: text("jurisdiction").default("IN"),
  authority: text("authority"),
  canonicalUrl: text("canonical_url"),
  versionLabel: text("version_label"),
  effectiveFrom: date("effective_from"),
  effectiveTo: date("effective_to"),
  sha256: text("sha256"),
  fetchedAt: timestamp("fetched_at", { withTimezone: true }),
  metadata: jsonb("metadata"),
  createdAt: createdAt(),
});

export const legalChunks = pgTable(
  "legal_chunks",
  {
    id: id(),
    sourceId: uuid("source_id")
      .notNull()
      .references(() => legalSources.id),
    chunkNo: integer("chunk_no").notNull(),
    actName: text("act_name"),
    sectionNo: text("section_no"),
    heading: text("heading"),
    text: text("text").notNull(),
    tokenCount: integer("token_count"),
    pageStart: integer("page_start"),
    pageEnd: integer("page_end"),
    embedding: vector("embedding", { dimensions: EMBEDDING_DIM }),
    /**
     * DB-generated tsvector for FTS — populated via GENERATED ALWAYS AS
     * (see migration 0002_legal_indexes.sql). Drizzle treats this as a
     * read-only column; writes are handled by Postgres.
     */
    textTsv: tsvector("text_tsv").generatedAlwaysAs(
      sql`to_tsvector('english', coalesce(text, ''))`,
    ),
    metadata: jsonb("metadata"),
    createdAt: createdAt(),
  },
  (t) => [
    index("legal_chunks_source_idx").on(t.sourceId),
    index("legal_chunks_tsv_idx").using("gin", t.textTsv),
    index("legal_chunks_embedding_idx").using("ivfflat", t.embedding.op("vector_cosine_ops")),
  ],
);

export const legalRetrievalLogs = pgTable("legal_retrieval_logs", {
  id: id(),
  missionId: uuid("mission_id")
    .notNull()
    .references(() => missions.id),
  query: text("query").notNull(),
  retrievedChunkIds: uuid("retrieved_chunk_ids").array(),
  rerankedChunkIds: uuid("reranked_chunk_ids").array(),
  scores: jsonb("scores"),
  modelVersions: jsonb("model_versions"),
  createdAt: createdAt(),
});

export const legalClaimVerifications = pgTable("legal_claim_verifications", {
  id: id(),
  missionId: uuid("mission_id")
    .notNull()
    .references(() => missions.id),
  claim: text("claim").notNull(),
  supported: boolean("supported").notNull(),
  citedChunkIds: uuid("cited_chunk_ids").array(),
  quoteSpans: jsonb("quote_spans"),
  verifierScore: numeric("verifier_score"),
  failureReason: text("failure_reason"),
  createdAt: createdAt(),
});

// ============================================================================
// Route / action
// ============================================================================

export const routeRules = pgTable("route_rules", {
  id: id(),
  issueDomain: text("issue_domain").notNull(),
  jurisdictionFilter: jsonb("jurisdiction_filter"),
  triggerConditions: jsonb("trigger_conditions"),
  priority: integer("priority").default(0).notNull(),
  routeGraph: jsonb("route_graph").notNull(),
  requiredEvidence: jsonb("required_evidence"),
  deadlines: jsonb("deadlines"),
  sourceCitations: uuid("source_citations").array(),
  socialTemplate: text("social_template"),
  stopAndRefer: jsonb("stop_and_refer"),
  active: boolean("active").default(true).notNull(),
  createdAt: createdAt(),
  updatedAt: updatedAt(),
});

export const authorities = pgTable("authorities", {
  id: id(),
  name: text("name").notNull(),
  authorityType: text("authority_type").notNull(), // regulator, company, govt_dept, court, ombudsman, police, platform
  jurisdiction: jsonb("jurisdiction"),
  contactChannels: jsonb("contact_channels"),
  officialUrls: jsonb("official_urls"),
  escalationPolicy: jsonb("escalation_policy"),
  verificationStatus: text("verification_status").default("unverified").notNull(),
  lastVerifiedAt: timestamp("last_verified_at", { withTimezone: true }),
  createdAt: createdAt(),
});

export const routePlans = pgTable("route_plans", {
  id: id(),
  missionId: uuid("mission_id")
    .notNull()
    .references(() => missions.id),
  summary: text("summary"),
  steps: jsonb("steps").notNull(),
  citations: jsonb("citations"),
  riskNotes: jsonb("risk_notes"),
  expectedTimeline: jsonb("expected_timeline"),
  confidence: numeric("confidence"),
  supersededAt: timestamp("superseded_at", { withTimezone: true }),
  createdAt: createdAt(),
});

export const consentGrants = pgTable("consent_grants", {
  id: id(),
  missionId: uuid("mission_id")
    .notNull()
    .references(() => missions.id),
  userId: uuid("user_id")
    .notNull()
    .references(() => users.id),
  actionType: text("action_type").notNull(),
  actionPreviewHash: text("action_preview_hash").notNull(),
  consentText: text("consent_text").notNull(),
  channel: text("channel").notNull(),
  grantedAt: timestamp("granted_at", { withTimezone: true }),
  expiresAt: timestamp("expires_at", { withTimezone: true }),
  revokedAt: timestamp("revoked_at", { withTimezone: true }),
  createdAt: createdAt(),
});

export const actions = pgTable(
  "actions",
  {
    id: id(),
    missionId: uuid("mission_id")
      .notNull()
      .references(() => missions.id),
    routePlanId: uuid("route_plan_id").references(() => routePlans.id),
    actionType: text("action_type").notNull(),
    title: text("title").notNull(),
    description: text("description"),
    target: jsonb("target"),
    inputPayload: jsonb("input_payload"),
    canonicalPayloadHash: text("canonical_payload_hash"),
    previewStorageKey: text("preview_storage_key"),
    consentRequired: boolean("consent_required").default(true).notNull(),
    consentGrantId: uuid("consent_grant_id").references(() => consentGrants.id),
    status: text("status").notNull().default("proposed"),
    proofState: text("proof_state"),
    proofRef: jsonb("proof_ref"),
    idempotencyKey: text("idempotency_key"),
    riskLevel: text("risk_level").default("low").notNull(),
    createdBy: text("created_by").notNull(), // user, ai, system, admin
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [
    uniqueIndex("actions_idempotency_key_uq").on(t.idempotencyKey),
    index("actions_mission_idx").on(t.missionId),
  ],
);

export const actionAttempts = pgTable("action_attempts", {
  id: id(),
  actionId: uuid("action_id")
    .notNull()
    .references(() => actions.id),
  adapterName: text("adapter_name").notNull(),
  attemptNo: integer("attempt_no").notNull(),
  requestPayloadHash: text("request_payload_hash"),
  responsePayload: jsonb("response_payload"),
  providerReference: text("provider_reference"),
  status: text("status").notNull(),
  errorCode: text("error_code"),
  errorMessage: text("error_message"),
  startedAt: timestamp("started_at", { withTimezone: true }),
  finishedAt: timestamp("finished_at", { withTimezone: true }),
});

export const submissions = pgTable("submissions", {
  id: id(),
  missionId: uuid("mission_id")
    .notNull()
    .references(() => missions.id),
  routePlanId: uuid("route_plan_id").references(() => routePlans.id),
  actionId: uuid("action_id").references(() => actions.id),
  adapterName: text("adapter_name"),
  targetAuthorityId: uuid("target_authority_id").references(() => authorities.id),
  targetContact: jsonb("target_contact"),
  status: text("status").notNull(),
  proofState: text("proof_state").notNull(),
  externalReference: text("external_reference"),
  receiptStorageKey: text("receipt_storage_key"),
  payloadHash: text("payload_hash"),
  error: jsonb("error"),
  sentAt: timestamp("sent_at", { withTimezone: true }),
  createdAt: createdAt(),
});

// ============================================================================
// Portal assist sessions (Phase 2 Module 5)
// ============================================================================

export const portalAssistSessions = pgTable("portal_assist_sessions", {
  id: id(),
  missionId: uuid("mission_id")
    .notNull()
    .references(() => missions.id),
  actionId: uuid("action_id")
    .notNull()
    .references(() => actions.id),
  portalUrl: text("portal_url").notNull(),
  state: text("state").notNull().default("packet_ready"), // PortalAssistState
  formFields: jsonb("form_fields").default([]), // { selector, label, value, filled }[]
  screenshotStorageKey: text("screenshot_storage_key"),
  receiptStorageKey: text("receipt_storage_key"),
  errorMessage: text("error_message"),
  createdAt: createdAt(),
  updatedAt: updatedAt(),
}, (t) => [
  index("portal_assist_sessions_mission_idx").on(t.missionId),
  index("portal_assist_sessions_state_idx").on(t.state),
]);

// ============================================================================
// Company Resolver Portal (Phase 2 Module 6)
// ============================================================================

export const organizations = pgTable("organizations", {
  id: id(),
  normalizedName: text("normalized_name").notNull().unique(),
  websiteDomain: text("website_domain"),
  officialEmails: text("official_emails").array(), // verified contact emails
  verificationStatus: text("verification_status").notNull().default("unverified"), // unverified, pending, verified, rejected
  metadata: jsonb("metadata"),
  createdAt: createdAt(),
  updatedAt: updatedAt(),
}, (t) => [
  index("organizations_domain_idx").on(t.websiteDomain),
  index("organizations_status_idx").on(t.verificationStatus),
]);

export const organizationMembers = pgTable("organization_members", {
  id: id(),
  organizationId: uuid("organization_id")
    .notNull()
    .references(() => organizations.id),
  userId: uuid("user_id")
    .notNull()
    .references(() => users.id),
  email: text("email").notNull(),
  role: text("role").notNull().default("member"), // admin, member, viewer
  verificationMethod: text("verification_method"), // domain_email, magic_link, manual
  verifiedAt: timestamp("verified_at", { withTimezone: true }),
  createdAt: createdAt(),
}, (t) => [
  uniqueIndex("org_members_org_user_uq").on(t.organizationId, t.userId),
  index("org_members_user_idx").on(t.userId),
]);

export const resolverThreads = pgTable("resolver_threads", {
  id: id(),
  missionId: uuid("mission_id")
    .notNull()
    .references(() => missions.id),
  organizationId: uuid("organization_id")
    .notNull()
    .references(() => organizations.id),
  status: text("status").notNull().default("open"), // open, awaiting_user, resolved, closed
  maskedSummary: text("masked_summary"), // PII-free summary shown to company
  createdAt: createdAt(),
  updatedAt: updatedAt(),
}, (t) => [
  uniqueIndex("resolver_threads_mission_org_uq").on(t.missionId, t.organizationId),
  index("resolver_threads_org_idx").on(t.organizationId),
]);

export const resolverMessages = pgTable("resolver_messages", {
  id: id(),
  threadId: uuid("thread_id")
    .notNull()
    .references(() => resolverThreads.id),
  senderType: text("sender_type").notNull(), // company, user, system
  senderId: uuid("sender_id"), // userId or organizationId
  body: text("body").notNull(),
  visibility: text("visibility").notNull().default("both"), // both, user_only, resolver_only, public
  createdAt: createdAt(),
}, (t) => [
  index("resolver_messages_thread_idx").on(t.threadId, t.createdAt),
]);

// ============================================================================
// Social publishing (Phase 2 Module 7)
// ============================================================================

export const socialAccounts = pgTable("social_accounts", {
  id: id(),
  platform: text("platform").notNull(), // threads, instagram, x
  providerUserId: text("provider_user_id"),
  accessTokenRef: text("access_token_ref"), // encrypted ref, never plaintext (CLAUDE.md)
  refreshTokenRef: text("refresh_token_ref"),
  tokenExpiresAt: timestamp("token_expires_at", { withTimezone: true }),
  scopes: text("scopes").array(),
  status: text("status").notNull().default("active"), // active, expired, revoked
  metadata: jsonb("metadata"),
  createdAt: createdAt(),
}, (t) => [
  index("social_accounts_platform_idx").on(t.platform),
]);

export const socialPosts = pgTable("social_posts", {
  id: id(),
  missionId: uuid("mission_id")
    .notNull()
    .references(() => missions.id),
  actionId: uuid("action_id")
    .notNull()
    .references(() => actions.id),
  socialAccountId: uuid("social_account_id").references(() => socialAccounts.id),
  consentGrantId: uuid("consent_grant_id")
    .notNull()
    .references(() => consentGrants.id),
  platform: text("platform").notNull(), // threads, instagram, x
  draftText: text("draft_text").notNull(),
  redactedText: text("redacted_text").notNull(),
  legalRiskReview: jsonb("legal_risk_review"),
  cardStorageKey: text("card_storage_key"), // generated image storage key
  externalPostId: text("external_post_id"),
  status: text("status").notNull().default("draft"), // draft, approved, published, failed, copy_ready
  postUrl: text("post_url"),
  proofState: text("proof_state").notNull().default("draft_only"),
  publishedAt: timestamp("published_at", { withTimezone: true }),
  createdAt: createdAt(),
}, (t) => [
  index("social_posts_mission_idx").on(t.missionId),
  index("social_posts_status_idx").on(t.status),
]);

// ============================================================================
// eCourts / Case lookups (Phase 2 Module 8)
// ============================================================================

export const caseLookups = pgTable("case_lookups", {
  id: id(),
  missionId: uuid("mission_id")
    .notNull()
    .references(() => missions.id),
  cnrNumber: text("cnr_number").notNull(),
  caseNumber: text("case_number"),
  courtName: text("court_name"),
  petitioner: text("petitioner"),
  respondent: text("respondent"),
  caseStatus: text("case_status"), // pending, disposed, stayed, transferred
  nextHearingDate: date("next_hearing_date"),
  lastOrderDate: date("last_order_date"),
  lastOrderSummary: text("last_order_summary"),
  rawData: jsonb("raw_data"),
  source: text("source").notNull(), // surepass, manual
  lookupStatus: text("lookup_status").default("pending").notNull(), // pending, success, failed, not_found
  errorMessage: text("error_message"),
  createdAt: createdAt(),
  updatedAt: updatedAt(),
}, (t) => [
  index("case_lookups_mission_idx").on(t.missionId),
  index("case_lookups_cnr_idx").on(t.cnrNumber),
]);

// ============================================================================
// Public / resolution
// ============================================================================

export const publicCasePages = pgTable(
  "public_case_pages",
  {
    id: id(),
    missionId: uuid("mission_id")
      .notNull()
      .references(() => missions.id),
    slug: text("slug").notNull(),
    visibility: text("visibility").default("private_link").notNull(), // Phase 1: private_link only
    redactedSummary: text("redacted_summary"),
    consentGrantId: uuid("consent_grant_id").references(() => consentGrants.id),
    takenDownAt: timestamp("taken_down_at", { withTimezone: true }),
    createdAt: createdAt(),
  },
  (t) => [uniqueIndex("public_case_pages_slug_uq").on(t.slug)],
);

export const redactionSpans = pgTable("redaction_spans", {
  id: id(),
  sourceType: text("source_type").notNull(), // public_page, email_body, social_post
  sourceId: uuid("source_id").notNull(),
  start: integer("start").notNull(),
  end: integer("end").notNull(),
  piiType: text("pii_type").notNull(),
  replacement: text("replacement").notNull(),
  reviewed: boolean("reviewed").default(false).notNull(),
  createdAt: createdAt(),
});

export const followups = pgTable("followups", {
  id: id(),
  missionId: uuid("mission_id")
    .notNull()
    .references(() => missions.id),
  type: text("type").notNull(), // reminder, escalation, deadline
  dueAt: timestamp("due_at", { withTimezone: true }).notNull(),
  status: text("status").default("scheduled").notNull(), // scheduled, completed, escalated, snoozed, cancelled
  payload: jsonb("payload"),
  createdAt: createdAt(),
});

export const resolutionOutcomes = pgTable("resolution_outcomes", {
  id: id(),
  missionId: uuid("mission_id")
    .notNull()
    .references(() => missions.id),
  outcomeType: text("outcome_type").notNull(),
  amountRecovered: numeric("amount_recovered"),
  userSatisfaction: integer("user_satisfaction"),
  routeEffectiveness: jsonb("route_effectiveness"),
  anonymizedPublicSummary: text("anonymized_public_summary"),
  publishConsent: boolean("publish_consent").default(false).notNull(),
  createdAt: createdAt(),
});

// ============================================================================
// Reliability
// ============================================================================

export const outboxEvents = pgTable(
  "outbox_events",
  {
    id: id(),
    eventType: text("event_type").notNull(),
    aggregateType: text("aggregate_type").notNull(),
    aggregateId: uuid("aggregate_id").notNull(),
    payload: jsonb("payload"),
    status: text("status").default("pending").notNull(), // pending, processing, sent, failed, dead_letter
    attempts: integer("attempts").default(0).notNull(),
    nextAttemptAt: timestamp("next_attempt_at", { withTimezone: true }).defaultNow().notNull(),
    lockedAt: timestamp("locked_at", { withTimezone: true }),
    createdAt: createdAt(),
  },
  (t) => [index("outbox_events_status_idx").on(t.status, t.nextAttemptAt)],
);

export const idempotencyKeys = pgTable("idempotency_keys", {
  key: text("key").primaryKey(),
  requestHash: text("request_hash").notNull(),
  responseHash: text("response_hash"),
  status: text("status").default("in_progress").notNull(),
  expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(),
  createdAt: createdAt(),
});

export const webhookEvents = pgTable(
  "webhook_events",
  {
    id: id(),
    provider: text("provider").notNull(), // whatsapp, telegram, resend
    providerEventId: text("provider_event_id").notNull(),
    payload: jsonb("payload"),
    processedAt: timestamp("processed_at", { withTimezone: true }),
    createdAt: createdAt(),
  },
  (t) => [uniqueIndex("webhook_events_provider_event_uq").on(t.provider, t.providerEventId)],
);

export const auditLogs = pgTable("audit_logs", {
  id: id(),
  actorType: text("actor_type").notNull(),
  actorId: text("actor_id"),
  action: text("action").notNull(),
  resourceType: text("resource_type").notNull(),
  resourceId: text("resource_id"),
  before: jsonb("before"),
  after: jsonb("after"),
  ipHash: text("ip_hash"),
  userAgentHash: text("user_agent_hash"),
  createdAt: createdAt(),
});

// ============================================================================
// Safety / compliance
// ============================================================================

export const humanReviewItems = pgTable("human_review_items", {
  id: id(),
  missionId: uuid("mission_id").references(() => missions.id),
  reviewType: text("review_type").notNull(), // stop_and_refer, defamation_risk, low_confidence, manual_submit
  priority: text("priority").default("normal").notNull(), // low, normal, high, urgent
  status: text("status").default("open").notNull(), // open, in_review, resolved, dismissed
  context: jsonb("context"),
  createdAt: createdAt(),
  updatedAt: updatedAt(),
});

export const consentNotices = pgTable("consent_notices", {
  id: id(),
  userId: uuid("user_id").references(() => users.id),
  version: text("version").notNull(),
  channel: text("channel").notNull(),
  ackedAt: timestamp("acked_at", { withTimezone: true }),
  createdAt: createdAt(),
});

export const dataSubjectRequests = pgTable("data_subject_requests", {
  id: id(),
  userId: uuid("user_id")
    .notNull()
    .references(() => users.id),
  type: text("type").notNull(), // export, erase
  status: text("status").default("pending").notNull(),
  dueAt: timestamp("due_at", { withTimezone: true }),
  resultRef: jsonb("result_ref"),
  createdAt: createdAt(),
});

export const notificationPrefs = pgTable("notification_prefs", {
  id: id(),
  userId: uuid("user_id")
    .notNull()
    .references(() => users.id),
  channel: text("channel").notNull(),
  timezone: text("timezone").default("Asia/Kolkata").notNull(),
  quietHours: jsonb("quiet_hours"),
  enabled: boolean("enabled").default(true).notNull(),
  createdAt: createdAt(),
});

export const emailEvents = pgTable(
  "email_events",
  {
    id: id(),
    submissionId: uuid("submission_id").references(() => submissions.id),
    type: text("type").notNull(), // delivered, bounced, complained, replied
    providerEventId: text("provider_event_id"),
    payload: jsonb("payload"),
    createdAt: createdAt(),
  },
  (t) => [uniqueIndex("email_events_provider_event_uq").on(t.providerEventId)],
);

export const takedownRequests = pgTable(
  "takedown_requests",
  {
    id: id(),
    missionId: uuid("mission_id").notNull().references(() => missions.id),
    actionId: uuid("action_id").notNull().references(() => actions.id),
    intermediaryName: text("intermediary_name").notNull(),
    grievanceOfficerName: text("grievance_officer_name"),
    grievanceOfficerEmail: text("grievance_officer_email").notNull(),
    contentUrl: text("content_url").notNull(),
    legalGrounds: text("legal_grounds").notNull(),
    status: text("status").default("pending").notNull(), // pending, sent, acknowledged, complied, rejected
    complianceDeadline: timestamp("compliance_deadline", { withTimezone: true }),
    compliedAt: timestamp("complied_at", { withTimezone: true }),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  }
);