-- OB1-compatible Bert memory metadata schema notes
--
-- This does not replace OB1's default setup. It extends the default `thoughts`
-- table approach with metadata discipline and optional helper views/indexes.
-- Run only after reviewing in Supabase SQL Editor.

-- Optional: enforce common metadata keys through documentation + indexes rather than hard constraints.
-- The default OB1 table uses:
--   content text not null
--   embedding vector(1536)
--   metadata jsonb default '{}'
--   created_at, updated_at

-- Helpful JSONB indexes for Bert memory retrieval.
create index if not exists idx_thoughts_metadata_memory_type
  on thoughts ((metadata->>'memory_type'));

create index if not exists idx_thoughts_metadata_review_status
  on thoughts ((metadata->>'review_status'));

create index if not exists idx_thoughts_metadata_project
  on thoughts ((metadata->>'project'));

create index if not exists idx_thoughts_metadata_source
  on thoughts ((metadata->>'source'));

create index if not exists idx_thoughts_metadata_source_chunk_id
  on thoughts ((metadata->>'source_chunk_id'));

create index if not exists idx_thoughts_metadata_sensitivity
  on thoughts ((metadata->>'sensitivity'));

-- Review queue: proposed but not yet approved memory.
create or replace view bert_memory_candidates as
select
  id,
  content,
  metadata,
  created_at,
  updated_at
from thoughts
where coalesce(metadata->>'review_status', '') = 'candidate';

-- Approved durable memories exposed to AI clients by default.
create or replace view bert_approved_memories as
select
  id,
  content,
  metadata,
  created_at,
  updated_at
from thoughts
where coalesce(metadata->>'review_status', '') = 'approved'
  and coalesce(metadata->>'durability', '') in ('long_term', 'medium_term')
  and coalesce(metadata->>'sensitivity', '') not in ('restricted');

-- Optional uniqueness guard: prevent importing the same archive chunk candidate repeatedly.
-- This permits multiple memories from the same chunk only if source_memory_fingerprint differs.
create unique index if not exists idx_thoughts_source_fingerprint_unique
  on thoughts ((metadata->>'source_chunk_id'), (metadata->>'source_memory_fingerprint'))
  where metadata ? 'source_chunk_id' and metadata ? 'source_memory_fingerprint';
