scripts/create_tables.sql

-- ─────────────────────────────────────────────────────────────────────────────
-- LabCard AI — Supabase Schema
-- Run this once in Supabase SQL Editor:
--   https://supabase.com → Your project → SQL Editor → New query
-- ─────────────────────────────────────────────────────────────────────────────

-- ── Extensions ────────────────────────────────────────────────────────────────
create extension if not exists "uuid-ossp";
create extension if not exists vector;         -- for future RAG embeddings

-- ── Users (extends Supabase auth.users) ──────────────────────────────────────
create table if not exists public.profiles (
  id         uuid primary key references auth.users(id) on delete cascade,
  name       text,
  age        int,
  gender     text,
  city       text,
  phone      text,
  created_at timestamptz default now(),
  updated_at timestamptz default now()
);

-- ── Reports ───────────────────────────────────────────────────────────────────
create table if not exists public.reports (
  id              uuid primary key default gen_random_uuid(),
  user_id         uuid references public.profiles(id) on delete cascade,
  lab_name        text,
  report_date     date,
  health_score    int check (health_score between 0 and 100),
  biological_age  int,
  grade           text,
  summary_en      text,
  summary_hi      text,
  doctor_note     text,
  top_priority    text,
  has_critical    boolean default false,
  raw_text_hash   text,   -- SHA-256 of raw text — dedup without storing PII
  created_at      timestamptz default now()
);

create index if not exists idx_reports_user_date
  on public.reports (user_id, created_at desc);

-- ── Biomarkers ────────────────────────────────────────────────────────────────
create table if not exists public.biomarkers (
  id              bigserial primary key,
  report_id       uuid references public.reports(id) on delete cascade,
  name            text not null,
  std_name        text,
  value           numeric,
  unit            text,
  normal_low      numeric,
  normal_high     numeric,
  status          text,
  category        text,
  explanation_en  text,
  explanation_hi  text,
  advice          text,
  foods           jsonb default '[]',
  created_at      timestamptz default now()
);

create index if not exists idx_biomarkers_report
  on public.biomarkers (report_id);

create index if not exists idx_biomarkers_trend
  on public.biomarkers (report_id, std_name, created_at);

-- ── Reference Ranges ─────────────────────────────────────────────────────────
create table if not exists public.reference_ranges (
  id             bigserial primary key,
  biomarker      text not null,
  std_name       text,
  gender         text default 'A',
  age_min        int  default 0,
  age_max        int  default 120,
  normal_low     numeric,
  normal_high    numeric,
  critical_low   numeric,
  critical_high  numeric,
  unit           text,
  category       text,
  notes          text,
  updated_at     timestamptz default now(),
  unique (biomarker, gender)
);

-- ── Chat Messages ─────────────────────────────────────────────────────────────
create table if not exists public.chat_messages (
  id         bigserial primary key,
  report_id  uuid references public.reports(id) on delete cascade,
  role       text not null check (role in ('user','assistant')),
  content    text not null,
  lang       text default 'en',
  created_at timestamptz default now()
);

create index if not exists idx_chat_report
  on public.chat_messages (report_id, created_at);

-- ── Medical Knowledge (for future RAG) ───────────────────────────────────────
create table if not exists public.medical_knowledge (
  id         bigserial primary key,
  content    text not null,
  embedding  vector(1536),
  metadata   jsonb default '{}',
  source     text,
  created_at timestamptz default now()
);

create index if not exists idx_medical_knowledge_embedding
  on public.medical_knowledge
  using hnsw (embedding vector_cosine_ops)
  with (m = 16, ef_construction = 64);

-- Similarity search function
create or replace function match_medical_knowledge (
  query_embedding vector(1536),
  match_threshold float default 0.75,
  match_count     int   default 5
)
returns table (id bigint, content text, metadata jsonb, similarity float)
language sql stable as $$
  select id, content, metadata,
         1 - (embedding <=> query_embedding) as similarity
  from   public.medical_knowledge
  where  1 - (embedding <=> query_embedding) > match_threshold
  order  by embedding <=> query_embedding
  limit  match_count;
$$;

-- ── Row Level Security ────────────────────────────────────────────────────────
alter table public.profiles       enable row level security;
alter table public.reports        enable row level security;
alter table public.biomarkers     enable row level security;
alter table public.chat_messages  enable row level security;

-- Profiles: users can only see/edit their own profile
create policy "profiles_own" on public.profiles
  for all using (auth.uid() = id);

-- Reports: users can only see their own reports
create policy "reports_own" on public.reports
  for all using (auth.uid() = user_id);

-- Biomarkers: accessible through report ownership
create policy "biomarkers_own" on public.biomarkers
  for all using (
    report_id in (
      select id from public.reports where user_id = auth.uid()
    )
  );

-- Chat: accessible through report ownership
create policy "chat_own" on public.chat_messages
  for all using (
    report_id in (
      select id from public.reports where user_id = auth.uid()
    )
  );

-- Reference ranges: public read
create policy "ref_ranges_read" on public.reference_ranges
  for select using (true);