04 · 技术资料：数据层与中文混合检索

export const preferredRegion = 'hnd1'   // 东京，就近连库

create extension if not exists vector;
create extension if not exists pg_trgm;

-- 维护 updated_at 的公共触发器函数
create or replace function public.bt_set_updated_at()
returns trigger language plpgsql as $$
begin
  new.updated_at = now();
  return new;
end;
$$;

-- ========== 1. 员工 ==========
create table public.bt_employees (
  id uuid primary key default gen_random_uuid(),
  employee_code text not null unique,
  display_name text not null,
  avatar_emoji text,
  title text,
  department text,
  role text not null default 'agent' check (role in ('agent','admin')),
  status text not null default 'active' check (status in ('active','offboarding','offboarded')),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);
comment on table public.bt_employees is '接棒：员工主表，每个员工对应一份严格隔离的知识资料';
comment on column public.bt_employees.employee_code is '工号，前端身份切换用的短标识';
comment on column public.bt_employees.status is 'active=在职 / offboarding=交接中 / offboarded=已离职（资料只读并封存）';

create trigger trg_bt_employees_updated_at before update on public.bt_employees
  for each row execute function public.bt_set_updated_at();

-- ========== 2. 文件 ==========
create table public.bt_files (
  id uuid primary key default gen_random_uuid(),
  owner_employee_id uuid not null references public.bt_employees(id) on delete restrict,
  original_filename text not null,
  storage_provider text not null default 'vercel_blob'
    check (storage_provider in ('vercel_blob','supabase_storage','inline')),
  storage_url text,
  mime_type text not null,
  file_size_bytes bigint not null check (file_size_bytes >= 0),
  source_type text not null check (source_type in ('pdf','docx','xlsx','txt','md','other')),
  page_count int,
  checksum_sha256 text,
  parse_status text not null default 'pending'
    check (parse_status in ('pending','parsing','chunking','embedding','done','failed')),
  parse_error text,
  total_chunks int not null default 0,
  embedded_chunks int not null default 0,
  uploaded_at timestamptz not null default now(),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);
comment on table public.bt_files is '接棒：员工上传的原始文件，归属唯一员工';
comment on column public.bt_files.parse_status is '状态机：pending→parsing→chunking→embedding→done，失败转 failed';
comment on column public.bt_files.storage_provider is '原文件存储后端，三种降级方案都支持';

create trigger trg_bt_files_updated_at before update on public.bt_files
  for each row execute function public.bt_set_updated_at();

create index bt_files_owner_idx on public.bt_files (owner_employee_id);
create index bt_files_status_idx on public.bt_files (parse_status)
  where parse_status not in ('done','failed');

-- ========== 3. 切片 ==========
create table public.bt_chunks (
  id uuid primary key default gen_random_uuid(),
  file_id uuid not null references public.bt_files(id) on delete cascade,
  owner_employee_id uuid not null references public.bt_employees(id) on delete restrict,
  chunk_index int not null,
  page_no int,
  page_label text not null,
  heading_path text,
  content text not null,
  content_norm text not null,
  token_count int,
  char_count int,
  embedding vector(1536),
  embedding_model text not null default 'text-embedding-3-small',
  embedding_status text not null default 'pending'
    check (embedding_status in ('pending','done','failed')),
  embedding_retry_count int not null default 0,
  created_at timestamptz not null default now(),
  unique (file_id, chunk_index)
);
comment on table public.bt_chunks is '接棒：文件切片，检索的最小召回单元，必须保留出处';
comment on column public.bt_chunks.page_label is '展示用出处文案，始终有值：PDF="第3页" / docx=章节路径 / xlsx="Sheet1!12-20行"';
comment on column public.bt_chunks.content_norm is '归一化文本（全角转半角、压缩空白），专供 pg_trgm 索引，不用于展示';
comment on column public.bt_chunks.owner_employee_id is '冗余存归属，避免每次检索都 join files';

create index bt_chunks_file_idx on public.bt_chunks (file_id);
create index bt_chunks_owner_idx on public.bt_chunks (owner_employee_id);
create index bt_chunks_pending_idx on public.bt_chunks (file_id, embedding_status)
  where embedding_status = 'pending';

-- ========== 4. 记忆条目（交接的标的物） ==========
create table public.bt_memories (
  id uuid primary key default gen_random_uuid(),
  owner_employee_id uuid not null references public.bt_employees(id) on delete restrict,
  category text not null check (category in
    ('客户约定','报价底线','供应商渠道','人际雷区','流程习惯')),
  title text not null,
  content text not null,
  source_file_id uuid references public.bt_files(id) on delete set null,
  source_chunk_id uuid references public.bt_chunks(id) on delete set null,
  source_label text,
  is_editable boolean not null default true,
  visible_to_colleagues boolean not null default false,
  include_in_handover_default boolean not null default true,
  embedding vector(1536),
  embedding_model text not null default 'text-embedding-3-small',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);
comment on table public.bt_memories is '接棒：记忆条目，交接的标的物。三个开关分别控制可编辑、跨人可见、交接默认包含';
comment on column public.bt_memories.visible_to_colleagues is 'true 时其他员工的 Agent 可跨人问到（仅问答，不转移归属）';
comment on column public.bt_memories.include_in_handover_default is '发起交接单时是否默认勾选（用户仍可增删）';

create trigger trg_bt_memories_updated_at before update on public.bt_memories
  for each row execute function public.bt_set_updated_at();

create index bt_memories_owner_idx on public.bt_memories (owner_employee_id);
create index bt_memories_cat_idx on public.bt_memories (owner_employee_id, category);
create index bt_memories_visible_idx on public.bt_memories (visible_to_colleagues)
  where visible_to_colleagues = true;

-- ========== 5. 交接单 ==========
create table public.bt_handovers (
  id uuid primary key default gen_random_uuid(),
  from_employee_id uuid not null references public.bt_employees(id) on delete restrict,
  to_employee_id uuid not null references public.bt_employees(id) on delete restrict,
  reason text not null default 'daily_sync' check (reason in ('offboard','role_change','daily_sync')),
  status text not null default 'draft'
    check (status in ('draft','submitted','viewed','completed','cancelled')),
  title text,
  note text,
  created_at timestamptz not null default now(),
  submitted_at timestamptz,
  viewed_at timestamptz,
  completed_at timestamptz,
  constraint bt_handovers_diff_employee check (from_employee_id <> to_employee_id)
);
comment on table public.bt_handovers is '接棒：交接单。一次交接 = 一个 from→to 的可见权授予事件，不搬走数据';
comment on column public.bt_handovers.status is 'draft→submitted→viewed→completed，对应页面上三步进度条';

create index bt_handovers_to_idx on public.bt_handovers (to_employee_id, status);
create index bt_handovers_from_idx on public.bt_handovers (from_employee_id, status);

-- ========== 6. 交接明细 ==========
create table public.bt_handover_items (
  id uuid primary key default gen_random_uuid(),
  handover_id uuid not null references public.bt_handovers(id) on delete cascade,
  item_type text not null check (item_type in ('memory','file')),
  memory_id uuid references public.bt_memories(id) on delete cascade,
  file_id uuid references public.bt_files(id) on delete cascade,
  source_note text,
  included_by text not null default 'default'
    check (included_by in ('default','manual_add')),
  granted_at timestamptz,
  created_at timestamptz not null default now(),
  constraint bt_handover_items_type_consistency check (
    (item_type = 'memory' and memory_id is not null and file_id is null) or
    (item_type = 'file'   and file_id   is not null and memory_id is null)
  )
);
comment on table public.bt_handover_items is '接棒：交接明细。本表即可见权授予记录，原 owner 不变';
comment on column public.bt_handover_items.granted_at is '交接单 completed 时回填，代表可见权正式生效';

create index bt_handover_items_handover_idx on public.bt_handover_items (handover_id);
create unique index bt_handover_items_memory_uk on public.bt_handover_items (handover_id, memory_id)
  where item_type = 'memory';
create unique index bt_handover_items_file_uk on public.bt_handover_items (handover_id, file_id)
  where item_type = 'file';
create index bt_handover_items_memory_idx on public.bt_handover_items (memory_id)
  where memory_id is not null;
create index bt_handover_items_file_idx on public.bt_handover_items (file_id)
  where file_id is not null;

-- ========== 7. 跨 Agent 提问日志 ==========
create table public.bt_agent_queries (
  id uuid primary key default gen_random_uuid(),
  asking_employee_id uuid not null references public.bt_employees(id) on delete restrict,
  target_employee_id uuid references public.bt_employees(id) on delete set null,
  query_text text not null,
  answer_text text,
  matched_memory_ids uuid[] not null default '{}',
  matched_chunk_ids uuid[] not null default '{}',
  was_cross_employee boolean not null default false,
  hop int not null default 0,
  latency_ms int,
  created_at timestamptz not null default now()
);
comment on table public.bt_agent_queries is '接棒：问答与跨人提问日志，记录谁问了谁、命中了什么';
comment on column public.bt_agent_queries.hop is '跳数：0=问自己，1=问同事。⛔ 不允许出现 2（防无限套娃）';

create index bt_agent_queries_asking_idx on public.bt_agent_queries (asking_employee_id, created_at desc);
create index bt_agent_queries_target_idx on public.bt_agent_queries (target_employee_id, created_at desc);

-- 向量：数据量 <10 万，用 HNSW（不用 IVFFlat，后者要按行数调 lists 且数据变化后要重建）
create index bt_chunks_embedding_hnsw on public.bt_chunks
  using hnsw (embedding vector_cosine_ops) with (m = 16, ef_construction = 64);
create index bt_memories_embedding_hnsw on public.bt_memories
  using hnsw (embedding vector_cosine_ops) with (m = 16, ef_construction = 64);

-- 中文模糊匹配：pg_trgm GIN
create index bt_chunks_trgm on public.bt_chunks using gin (content_norm gin_trgm_ops);
create index bt_memories_trgm on public.bt_memories using gin (content gin_trgm_ops);

alter table public.bt_employees      enable row level security;
alter table public.bt_files          enable row level security;
alter table public.bt_chunks         enable row level security;
alter table public.bt_memories       enable row level security;
alter table public.bt_handovers      enable row level security;
alter table public.bt_handover_items enable row level security;
alter table public.bt_agent_queries  enable row level security;

revoke all on public.bt_employees, public.bt_files, public.bt_chunks,
              public.bt_memories, public.bt_handovers, public.bt_handover_items,
              public.bt_agent_queries
  from anon, authenticated;
-- 不给 anon/authenticated 建任何 policy = 默认全部拒绝。
-- service_role 自带 BYPASSRLS，只在服务端使用，是唯一真正能读写的角色。

alter table public.bt_employees enable row level security;
create policy bt_employees_all on public.bt_employees for all to public using (true) with check (true);
-- 其余六张表同样处理（与 Leo-hub 现有表风格一致）

create or replace function public.bt_hybrid_search(
  query_text text,
  query_embedding vector(1536),
  target_employee_id uuid,
  match_count int default 10,
  candidate_pool int default 50
)
returns table (
  item_type text, item_id uuid, file_id uuid,
  page_no int, page_label text, heading_path text,
  title text, snippet text, owner_employee_id uuid,
  vec_score real, trgm_score real, rrf_score double precision
)
language sql stable as $$
with
chunk_vec as (
  select c.id as item_id, c.file_id, c.page_no, c.page_label, c.heading_path,
         null::text as title, c.content as snippet, c.owner_employee_id,
         1 - (c.embedding <=> query_embedding) as score,
         row_number() over (order by c.embedding <=> query_embedding) as rnk
  from public.bt_chunks c
  where c.embedding is not null
    and (c.owner_employee_id = target_employee_id
      or exists (select 1 from public.bt_handover_items hi
                 join public.bt_handovers h on h.id = hi.handover_id
                 where hi.item_type = 'file' and hi.file_id = c.file_id
                   and h.to_employee_id = target_employee_id and h.status = 'completed'))
  order by c.embedding <=> query_embedding
  limit candidate_pool
),
chunk_trgm as (
  select c.id as item_id, c.file_id, c.page_no, c.page_label, c.heading_path,
         null::text as title, c.content as snippet, c.owner_employee_id,
         similarity(c.content_norm, query_text) as score,
         row_number() over (order by similarity(c.content_norm, query_text) desc) as rnk
  from public.bt_chunks c
  where c.content_norm % query_text
    and (c.owner_employee_id = target_employee_id
      or exists (select 1 from public.bt_handover_items hi
                 join public.bt_handovers h on h.id = hi.handover_id
                 where hi.item_type = 'file' and hi.file_id = c.file_id
                   and h.to_employee_id = target_employee_id and h.status = 'completed'))
  order by similarity(c.content_norm, query_text) desc
  limit candidate_pool
),
memory_vec as (
  select m.id as item_id, m.source_file_id as file_id, null::int as page_no,
         m.source_label as page_label, null::text as heading_path,
         m.title, m.content as snippet, m.owner_employee_id,
         1 - (m.embedding <=> query_embedding) as score,
         row_number() over (order by m.embedding <=> query_embedding) as rnk
  from public.bt_memories m
  where m.embedding is not null
    and (m.owner_employee_id = target_employee_id
      or exists (select 1 from public.bt_handover_items hi
                 join public.bt_handovers h on h.id = hi.handover_id
                 where hi.item_type = 'memory' and hi.memory_id = m.id
                   and h.to_employee_id = target_employee_id and h.status = 'completed'))
  order by m.embedding <=> query_embedding
  limit candidate_pool
),
memory_trgm as (
  select m.id as item_id, m.source_file_id as file_id, null::int as page_no,
         m.source_label as page_label, null::text as heading_path,
         m.title, m.content as snippet, m.owner_employee_id,
         similarity(m.content, query_text) as score,
         row_number() over (order by similarity(m.content, query_text) desc) as rnk
  from public.bt_memories m
  where m.content % query_text
    and (m.owner_employee_id = target_employee_id
      or exists (select 1 from public.bt_handover_items hi
                 join public.bt_handovers h on h.id = hi.handover_id
                 where hi.item_type = 'memory' and hi.memory_id = m.id
                   and h.to_employee_id = target_employee_id and h.status = 'completed'))
  order by similarity(m.content, query_text) desc
  limit candidate_pool
),
unioned as (
  select 'chunk'::text as item_type, item_id, file_id, page_no, page_label, heading_path,
         title, snippet, owner_employee_id, score, rnk, 'vec'::text as method from chunk_vec
  union all
  select 'chunk'::text, item_id, file_id, page_no, page_label, heading_path,
         title, snippet, owner_employee_id, score, rnk, 'trgm'::text from chunk_trgm
  union all
  select 'memory'::text, item_id, file_id, page_no, page_label, heading_path,
         title, snippet, owner_employee_id, score, rnk, 'vec'::text from memory_vec
  union all
  select 'memory'::text, item_id, file_id, page_no, page_label, heading_path,
         title, snippet, owner_employee_id, score, rnk, 'trgm'::text from memory_trgm
),
fused as (
  select item_type, item_id,
         max(file_id) as file_id, max(page_no) as page_no, max(page_label) as page_label,
         max(heading_path) as heading_path, max(title) as title, max(snippet) as snippet,
         max(owner_employee_id) as owner_employee_id,
         max(score) filter (where method = 'vec')  as vec_score,
         max(score) filter (where method = 'trgm') as trgm_score,
         sum(1.0 / (60 + rnk)) as rrf_score          -- RRF，k=60 是业界常用默认
  from unioned
  group by item_type, item_id
)
select item_type, item_id, file_id, page_no, page_label, heading_path, title, snippet,
       owner_employee_id, vec_score::real, trgm_score::real, rrf_score
from fused
order by rrf_score desc
limit match_count;
$$;

alter function public.bt_hybrid_search(text, vector, uuid, int, int) set hnsw.ef_search = 40;

create or replace function public.bt_can_access_file(p_employee_id uuid, p_file_id uuid)
returns boolean language sql stable as $$
  select exists (select 1 from public.bt_files f
                 where f.id = p_file_id and f.owner_employee_id = p_employee_id)
      or exists (select 1 from public.bt_handover_items hi
                 join public.bt_handovers h on h.id = hi.handover_id
                 where hi.item_type = 'file' and hi.file_id = p_file_id
                   and h.to_employee_id = p_employee_id and h.status = 'completed');
$$;

create or replace function public.bt_can_access_memory(p_employee_id uuid, p_memory_id uuid)
returns boolean language sql stable as $$
  select exists (select 1 from public.bt_memories m
                 where m.id = p_memory_id and m.owner_employee_id = p_employee_id)
      or exists (select 1 from public.bt_handover_items hi
                 join public.bt_handovers h on h.id = hi.handover_id
                 where hi.item_type = 'memory' and hi.memory_id = p_memory_id
                   and h.to_employee_id = p_employee_id and h.status = 'completed');
$$;

const { data, error } = await supabaseAdmin.rpc('bt_hybrid_search', {
  query_text: q,
  query_embedding: vec,          // number[]，supabase-js 自动序列化成 pgvector
  target_employee_id: employeeId,
  match_count: 10,
})