backend/supabase_schema.sql

-- Thaät — Supabase schema
-- Run once in Supabase SQL editor.

create table if not exists users (
  id                    uuid primary key,
  display_name          text not null,
  autism_level          text not null check (autism_level in ('Level 1', 'Level 2', 'Level 3')),
  age_range             text not null check (age_range in ('child', 'teen', 'adult')),
  primary_goal          text not null check (primary_goal in ('understand_others', 'express_myself', 'both')),
  hardest_emotions      text[] not null default '{}',
  caregiver_name        text,
  caregiver_email       text,
  notifications_enabled boolean not null default false,
  created_at            timestamptz not null default now(),
  updated_at            timestamptz not null default now()
);

create or replace function update_updated_at()
returns trigger language plpgsql as $$
begin
  new.updated_at = now();
  return new;
end;
$$;

drop trigger if exists users_updated_at on users;
create trigger users_updated_at
before update on users
for each row execute procedure update_updated_at();

create table if not exists sessions (
  id               uuid primary key default gen_random_uuid(),
  user_id          uuid not null references users(id) on delete cascade,
  mode             text not null check (mode in ('live', 'test', 'training')),
  overall_emotion  text,
  duration_seconds int not null default 0,
  created_at       timestamptz not null default now()
);

create index if not exists sessions_user_id_idx on sessions(user_id);
create index if not exists sessions_created_at_idx on sessions(created_at desc);

create table if not exists emotion_records (
  id               uuid primary key default gen_random_uuid(),
  user_id          uuid not null references users(id) on delete cascade,
  session_id       uuid references sessions(id) on delete cascade,
  spoken_text      text,
  text_emotion     text,
  face_emotion     text,
  body_emotion     text,
  fusion_emotion   text,
  conflict_type    text,
  insight          text,
  created_at       timestamptz not null default now()
);

create index if not exists emotion_records_user_id_idx on emotion_records(user_id);

create table if not exists training_logs (
  id                uuid primary key default gen_random_uuid(),
  user_id           uuid not null references users(id) on delete cascade,
  target_emotion    text not null,
  detected_emotion  text not null,
  score             int not null check (score >= 0 and score <= 100),
  success           boolean not null,
  feedback          text,
  created_at        timestamptz not null default now()
);

create index if not exists training_logs_user_id_idx  on training_logs(user_id);
create index if not exists training_logs_emotion_idx  on training_logs(target_emotion);
create index if not exists training_logs_created_idx  on training_logs(created_at desc);

-- ── Row Level Security ──────────────────────────────────────────────────────
-- IMPORTANT: The backend uses the SERVICE ROLE key (SUPABASE_SERVICE_KEY).
-- Service role bypasses RLS entirely — no policy needed for backend writes.
-- RLS here protects against direct client-side access ONLY.
-- Since the Flutter app writes through the backend (not direct Supabase SDK),
-- we only need RLS if you ever add direct Supabase client access from Flutter.
-- For now: enable RLS but add a permissive policy so backend writes work.
-- When you add Supabase Auth to Flutter, replace these with auth.uid() policies.

alter table users enable row level security;
alter table sessions enable row level security;
alter table emotion_records enable row level security;
alter table training_logs enable row level security;

-- Temporary open policies — backend service role bypasses RLS anyway.
-- Replace with auth.uid() = user_id once you add Supabase Auth to Flutter.
drop policy if exists "users: service write" on users;
create policy "users: service write" on users
  for all using (true) with check (true);

drop policy if exists "sessions: service write" on sessions;
create policy "sessions: service write" on sessions
  for all using (true) with check (true);

drop policy if exists "emotion_records: service write" on emotion_records;
create policy "emotion_records: service write" on emotion_records
  for all using (true) with check (true);

drop policy if exists "training_logs: service write" on training_logs;
create policy "training_logs: service write" on training_logs
  for all using (true) with check (true);

-- ── When you add Supabase Auth, replace the above with: ───────────────────
-- drop policy if exists "users: own row" on users;
-- create policy "users: own row" on users
--   for all using (auth.uid() = id) with check (auth.uid() = id);
-- (same pattern for sessions, emotion_records, training_logs)

create or replace view emotion_progress_monthly as
select
  user_id,
  target_emotion,
  date_trunc('month', created_at) as month,
  round(avg(score))               as avg_score,
  count(*)                        as attempts,
  sum(case when success then 1 else 0 end) as successes
from training_logs
group by user_id, target_emotion, date_trunc('month', created_at)
order by user_id, month desc, target_emotion;