CREATE TABLE triage_results (
  id UUID PRIMARY KEY,
  message_id TEXT,
  platform TEXT,              -- 'ai_conversation', 'integratewise_session', etc.
  content TEXT,
  priority TEXT,              -- 'low', 'medium', 'high', 'urgent'
  category TEXT,
  confidence REAL,
  reasoning TEXT,
  sentiment TEXT,             -- 'positive', 'neutral', 'negative'
  metadata JSONB,            -- tenant_id, user_id, source, etc.
  approval_status TEXT DEFAULT 'pending'
    CHECK (approval_status IN ('pending', 'approved', 'discarded', 'deferred')),
  approved_by TEXT,
  approved_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ,
  updated_at TIMESTAMPTZ
);

CREATE TABLE consolidated_memories (
  id UUID PRIMARY KEY,
  tenant_id TEXT,
  consolidation_type TEXT,   -- 'topic', 'user', 'account', 'pattern'
  topic TEXT,
  user_id TEXT,
  account_id TEXT,
  summary TEXT,
  key_insights JSON,         -- JSON array
  recurring_themes JSON,     -- JSON array
  action_patterns JSON,      -- JSON array
  session_count INTEGER,
  time_range_start TIMESTAMPTZ,
  time_range_end TIMESTAMPTZ,
  created_at TIMESTAMPTZ
);

CREATE TABLE org_memory (
  id UUID PRIMARY KEY,
  tenant_id TEXT,
  topic TEXT,
  category TEXT,
  content TEXT,
  source_session_id TEXT,
  governance_state TEXT,      -- 'staging' or 'approved'
  confidence REAL,
  embedding TEXT,
  metadata JSON,
  created_at TIMESTAMPTZ,
  updated_at TIMESTAMPTZ,
  related_entity_ids JSON,
  related_departments JSON,
  access_scope TEXT,
  lineage_ref TEXT,
  tier TEXT
);

CREATE TABLE memory_promotion_audit (
  id UUID PRIMARY KEY,
  tenant_id TEXT,
  entity_id TEXT,
  signals JSON,
  score REAL,
  category TEXT,
  promoted BOOLEAN,
  governance_state TEXT,
  rationale TEXT,
  decided_by TEXT,
  created_at TIMESTAMPTZ
);

CREATE TABLE triage_items (
  id TEXT PRIMARY KEY,
  tenant_id TEXT,
  kind TEXT,
  content TEXT,
  route TEXT,
  status TEXT,
  created_at TEXT,
  resolved_at TEXT
);