-- Project RiverTalk: Core Database Schema

-- Enable UUID extension
create extension if not exists "uuid-ossp";

-- 1. Users Profile Table (Extends Supabase Auth Auth.users)
create table public.users (
  id uuid references auth.users not null primary key,
  name text,
  email text,
  language_preference text default 'en' check (language_preference in ('en', 'es')),
  voice_clone_id text,
  created_at timestamp with time zone default timezone('utc'::text, now()) not null
);

-- Row Level Security (RLS) for Users
alter table public.users enable row level security;
create policy "Public profiles are viewable by everyone." on users for select using (true);
create policy "Users can insert their own profile." on users for insert with check ((select auth.uid()) = id);
create policy "Users can update own profile." on users for update using ((select auth.uid()) = id);

-- 2. Categories (Level 1)
create table public.categories (
  id uuid default uuid_generate_v4() primary key,
  name text not null,
  type text check (type in ('dm', 'job', 'office')),
  created_at timestamp with time zone default timezone('utc'::text, now()) not null
);
alter table public.categories enable row level security;
create policy "Categories are viewable by everyone." on categories for select using (true);

-- 3. Chats (Level 2)
create table public.chats (
  id uuid default uuid_generate_v4() primary key,
  category_id uuid references public.categories(id) on delete cascade not null,
  name text not null,
  created_at timestamp with time zone default timezone('utc'::text, now()) not null
);
alter table public.chats enable row level security;
create policy "Chats are viewable by everyone." on chats for select using (true);

-- 4. Chat Members (Access Control)
create table public.chat_members (
  user_id uuid references public.users(id) on delete cascade not null,
  chat_id uuid references public.chats(id) on delete cascade not null,
  role text default 'member' check (role in ('admin', 'member')),
  joined_at timestamp with time zone default timezone('utc'::text, now()) not null,
  primary key (user_id, chat_id)
);
alter table public.chat_members enable row level security;
create policy "Members can view members of their chats." on chat_members for select using (
  exists (select 1 from public.chat_members cm where cm.chat_id = chat_members.chat_id and cm.user_id = (select auth.uid()))
);

-- 5. Sub-Chats (Level 3)
create table public.sub_chats (
  id uuid default uuid_generate_v4() primary key,
  chat_id uuid references public.chats(id) on delete cascade not null,
  name text not null,
  created_at timestamp with time zone default timezone('utc'::text, now()) not null
);
alter table public.sub_chats enable row level security;
create policy "Sub-chats viewable by chat members." on sub_chats for select using (
  exists (select 1 from public.chat_members cm where cm.chat_id = sub_chats.chat_id and cm.user_id = (select auth.uid()))
);

-- 6. Threads (Level 4)
create table public.threads (
  id uuid default uuid_generate_v4() primary key,
  parent_id uuid not null, -- Can be chat_id or sub_chat_id
  title text,
  created_at timestamp with time zone default timezone('utc'::text, now()) not null
);
alter table public.threads enable row level security;
create policy "Threads viewable by authenticated users." on threads for select using ((select auth.role()) = 'authenticated');

-- 7. Messages
create table public.messages (
  id uuid default uuid_generate_v4() primary key,
  user_id uuid references public.users(id) on delete set null,
  thread_id uuid references public.threads(id) on delete cascade,
  sub_chat_id uuid references public.sub_chats(id) on delete cascade,
  chat_id uuid references public.chats(id) on delete cascade not null,
  type text default 'text' check (type in ('text', 'audio', 'media', 'pdf')),
  original_language text default 'en',
  content text,
  content_en text,
  media_url text,
  media_url_en text,
  created_at timestamp with time zone default timezone('utc'::text, now()) not null
);
alter table public.messages enable row level security;
create policy "Messages viewable by chat members." on messages for select using (
  exists (select 1 from public.chat_members cm where cm.chat_id = messages.chat_id and cm.user_id = (select auth.uid()))
);
create policy "Members can insert messages." on messages for insert with check (
  (select auth.uid()) = user_id and 
  exists (select 1 from public.chat_members cm where cm.chat_id = messages.chat_id and cm.user_id = (select auth.uid()))
);

-- 8. Audio Metadata
create table public.audio_metadata (
  message_id uuid references public.messages(id) on delete cascade primary key,
  duration numeric,
  waveform_data jsonb,
  transcript text,
  transcript_en text,
  summary text
);
alter table public.audio_metadata enable row level security;
create policy "Audio metadata viewable by authenticated users." on audio_metadata for select using ((select auth.role()) = 'authenticated');

-- Turn on Realtime for Messages
alter publication supabase_realtime add table messages;