-- Supabase/Postgres schema for HF Monitoring App
create schema if not exists hf;

-- Patients & enrollment
create table if not exists hf.patients (
  id uuid primary key default gen_random_uuid(),
  given_name text not null,
  family_name text not null,
  birth_date date not null,
  sex text check (sex in ('male','female','other')),
  ehr_id text,
  created_at timestamptz default now()
);

create table if not exists hf.enrollments (
  id uuid primary key default gen_random_uuid(),
  patient_id uuid references hf.patients(id) on delete cascade,
  site text,
  clinician_user_id uuid,
  status text default 'active',
  created_at timestamptz default now()
);

-- Consents & audit
create table if not exists hf.consents (
  id uuid primary key default gen_random_uuid(),
  patient_id uuid references hf.patients(id) on delete cascade,
  version text not null,
  consented_at timestamptz not null,
  revoked_at timestamptz
);

create table if not exists hf.audit_logs (
  id bigserial primary key,
  actor text,
  action text,
  resource text,
  before jsonb,
  after jsonb,
  created_at timestamptz default now()
);

-- Surveys
create table if not exists hf.checkins (
  id uuid primary key default gen_random_uuid(),
  patient_id uuid references hf.patients(id) on delete cascade,
  items jsonb not null,
  speech_seconds int,
  created_at timestamptz default now()
);

create table if not exists hf.kccq_responses (
  id uuid primary key default gen_random_uuid(),
  patient_id uuid references hf.patients(id) on delete cascade,
  metadata jsonb,
  subscales jsonb,
  total numeric,
  created_at timestamptz default now()
);

-- Signals
create table if not exists hf.wearable_metrics (
  id bigserial primary key,
  patient_id uuid references hf.patients(id) on delete cascade,
  ts timestamptz not null,
  steps int,
  hr numeric,
  hrv numeric,
  sleep_minutes int,
  weight_kg numeric,
  source text
);

create table if not exists hf.speech_features (
  id bigserial primary key,
  patient_id uuid references hf.patients(id) on delete cascade,
  ts timestamptz not null,
  features jsonb not null, -- e.g., tempo, pitch_var, pause_ratio
  stored_audio boolean default false
);

create table if not exists hf.environment_readings (
  id bigserial primary key,
  patient_id uuid references hf.patients(id) on delete cascade,
  ts timestamptz not null,
  noise_db numeric,
  light_lux numeric,
  aqi int,
  weather jsonb
);

-- Scores & alerts
create table if not exists hf.qol_scores (
  id bigserial primary key,
  patient_id uuid references hf.patients(id) on delete cascade,
  ts timestamptz default now(),
  version text not null,
  physical numeric,
  emotional_cognitive numeric,
  social_behavioral numeric,
  symptom_burden numeric,
  environmental numeric,
  composite numeric,
  uncertainty numeric
);

create table if not exists hf.alerts (
  id bigserial primary key,
  patient_id uuid references hf.patients(id) on delete cascade,
  created_at timestamptz default now(),
  severity text check (severity in ('FYI','Review','Urgent')) not null,
  rule_id text not null,
  evidence jsonb not null,
  status text default 'open',
  acknowledged_at timestamptz
);

-- FHIR links
create table if not exists hf.fhir_links (
  id bigserial primary key,
  patient_id uuid references hf.patients(id) on delete cascade,
  resource_type text,
  resource_id text,
  payload jsonb,
  created_at timestamptz default now()
);

-- Row Level Security (enable and create simple policies as a starting point)
alter table hf.patients enable row level security;
create policy patients_read on hf.patients for select using (true);
