schema.sql

-- Echo – Main Database Schema
-- Run this in your Supabase SQL Editor to create all required tables, policies, and helpers.
-- Existing data will be respected where possible (most statements are CREATE IF NOT EXISTS / ALTER / DROP POLICY IF EXISTS).

-- ========================================================
-- 1. Notifications: push_subscriptions table + RLS
-- ========================================================

-- Create table to store push subscriptions
create table if not exists public.push_subscriptions (
    id uuid default gen_random_uuid() primary key,
    user_id uuid references auth.users(id) on delete cascade not null,
    endpoint text not null unique,
    p256dh text not null,
    auth text not null,
    user_agent text,
    created_at timestamp with time zone default timezone('utc'::text, now()) not null,
    updated_at timestamp with time zone default timezone('utc'::text, now()) not null
);

-- Enable RLS
alter table public.push_subscriptions enable row level security;

-- Policies
create policy "Users can view their own subscriptions"
    on public.push_subscriptions for select
    using (auth.uid() = user_id);

create policy "Users can insert their own subscriptions"
    on public.push_subscriptions for insert
    with check (auth.uid() = user_id);

create policy "Users can update their own subscriptions"
    on public.push_subscriptions for update
    using (auth.uid() = user_id);

create policy "Users can delete their own subscriptions"
    on public.push_subscriptions for delete
    using (auth.uid() = user_id);

-- Indexes
create index if not exists push_subscriptions_user_id_idx on public.push_subscriptions(user_id);


-- ========================================================
-- 2. Weekly Check-ins: config, checkins, responses + RLS
-- ========================================================

-- Check-in Configuration
create table if not exists checkin_config (
  family_id uuid references families(id) not null primary key,
  day_of_week int not null check (day_of_week between 0 and 6), -- 0 = Sunday
  time_utc time not null,
  created_at timestamp with time zone default timezone('utc'::text, now()) not null
);

alter table checkin_config enable row level security;

create policy "Users can view checkin config for their family"
  on checkin_config for select
  using ( family_id in (select family_id from profiles where id = auth.uid()) );

create policy "Users can update checkin config for their family"
  on checkin_config for update
  using ( family_id in (select family_id from profiles where id = auth.uid()) );

create policy "Users can insert checkin config for their family"
  on checkin_config for insert
  with check ( family_id in (select family_id from profiles where id = auth.uid()) );

-- Check-ins (The weekly instances)
create table if not exists checkins (
  id uuid default gen_random_uuid() primary key,
  family_id uuid references families(id) not null,
  week_start_date date not null, -- The Monday (or Sunday) of the week this check-in belongs to
  status text check (status in ('pending', 'completed')) default 'pending',
  ai_topic jsonb, -- { "title": "...", "description": "..." }
  created_at timestamp with time zone default timezone('utc'::text, now()) not null,
  unique(family_id, week_start_date)
);

alter table checkins enable row level security;

create policy "Users can view checkins for their family"
  on checkins for select
  using ( family_id in (select family_id from profiles where id = auth.uid()) );

create policy "Service role can manage checkins"
  on checkins
  using ( true );

create policy "Users can insert checkins for their family"
  on checkins for insert
  with check ( family_id in (select family_id from profiles where id = auth.uid()) );

create policy "Users can update checkins for their family"
  on checkins for update
  using ( family_id in (select family_id from profiles where id = auth.uid()) );


-- Check-in Responses
create table if not exists checkin_responses (
  id uuid default gen_random_uuid() primary key,
  checkin_id uuid references checkins(id) not null,
  user_id uuid references auth.users(id) not null,
  temperature int not null check (temperature between 1 and 10),
  notes text,
  created_at timestamp with time zone default timezone('utc'::text, now()) not null,
  unique(checkin_id, user_id)
);

alter table checkin_responses enable row level security;

create policy "Users can view responses for their family"
  on checkin_responses for select
  using (
    exists (
      select 1 from checkins
      where checkins.id = checkin_responses.checkin_id
      and checkins.family_id in (select family_id from profiles where id = auth.uid())
    )
  );

create policy "Users can insert their own response"
  on checkin_responses for insert
  with check ( user_id = auth.uid() );

create policy "Users can update their own response"
  on checkin_responses for update
  using ( user_id = auth.uid() );


-- ========================================================
-- 3. RPC Helpers
-- ========================================================

-- Function to safely reset a check-in week by deleting responses and the check-in itself.
-- Runs with SECURITY DEFINER to bypass RLS restrictions (needed to delete partner's responses).

create or replace function reset_checkin_week(target_checkin_id uuid)
returns void
language plpgsql
security definer
as $$
begin
  -- 1. Delete all responses for this check-in
  delete from checkin_responses
  where checkin_id = target_checkin_id;

  -- 2. Delete the check-in itself
  delete from checkins
  where id = target_checkin_id;
end;
$$;


-- ========================================================
-- 4. Final RLS for families & profiles
-- ========================================================

-- Helper Function (Prevention of Infinite Recursion)
CREATE OR REPLACE FUNCTION get_my_family_id()
RETURNS uuid
LANGUAGE sql
SECURITY DEFINER
SET search_path = public
STABLE
AS $$
  SELECT family_id FROM profiles WHERE id = auth.uid();
$$;

-- FAMILIES Table Policies
ALTER TABLE public.families ENABLE ROW LEVEL SECURITY;

-- Allow INSERT (This was missing/failing)
DROP POLICY IF EXISTS "Users can create families" ON public.families;
CREATE POLICY "Users can create families"
    ON public.families
    FOR INSERT
    TO authenticated
    WITH CHECK (true);

-- Allow SELECT (View your own family)
DROP POLICY IF EXISTS "Users can view their family" ON public.families;
CREATE POLICY "Users can view their family"
    ON public.families
    FOR SELECT
    TO authenticated
    USING (
        id = get_my_family_id()
    );

-- Allow UPDATE (Update your own family)
DROP POLICY IF EXISTS "Users can update their family" ON public.families;
CREATE POLICY "Users can update their family"
    ON public.families
    FOR UPDATE
    TO authenticated
    USING (
        id = get_my_family_id()
    );

-- PROFILES Table Policies
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;

-- Allow INSERT (Create your own profile)
DROP POLICY IF EXISTS "Users can insert their own profile" ON public.profiles;
CREATE POLICY "Users can insert their own profile"
    ON public.profiles
    FOR INSERT
    TO authenticated
    WITH CHECK (id = auth.uid());

-- Allow UPDATE (Edit your own profile)
DROP POLICY IF EXISTS "Users can update their own profile" ON public.profiles;
CREATE POLICY "Users can update their own profile"
    ON public.profiles
    FOR UPDATE
    TO authenticated
    USING (id = auth.uid());

-- Allow SELECT (View yourself AND family members)
DROP POLICY IF EXISTS "Users can view family members" ON public.profiles;
CREATE POLICY "Users can view family members"
    ON public.profiles
    FOR SELECT
    TO authenticated
    USING (
        id = auth.uid()
        OR
        family_id = get_my_family_id()
    );

-- Cleanup old/conflicting policies if they exist (Optional but good hygiene)
DROP POLICY IF EXISTS "Users can view their own subscriptions" ON public.families;


-- ========================================================
-- 5. Realtime publication configuration
-- ========================================================

-- Enable Realtime for the 'glows' table (so new messages appear instantly)
alter publication supabase_realtime add table glows;

-- Enable Realtime for the 'profiles' table (so vibes update instantly)
alter publication supabase_realtime add table profiles;

-- Verify it worked (Optional)
-- select * from pg_publication_tables where pubname = 'supabase_realtime';