supabase-schema.sql

-- ============================================
-- Supabase Database Schema for Refleva
-- ============================================

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- ============================================
-- 1. USER PROFILES
-- ============================================
-- Extends Supabase auth.users with additional profile information
CREATE TABLE IF NOT EXISTS public.profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  email TEXT NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL
);

-- Enable Row Level Security
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;

-- Policy: Users can read their own profile
CREATE POLICY "Users can view own profile" ON public.profiles
  FOR SELECT USING (auth.uid() = id);

-- Policy: Users can update their own profile
CREATE POLICY "Users can update own profile" ON public.profiles
  FOR UPDATE USING (auth.uid() = id);

-- Policy: Users can create their own profile (fallback if trigger fails)
CREATE POLICY "Users can create own profile" ON public.profiles
  FOR INSERT WITH CHECK (auth.uid() = id);

-- ============================================
-- 2. JOURNAL ENTRIES
-- ============================================
CREATE TABLE IF NOT EXISTS public.journal_entries (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL REFERENCES public.profiles(id) ON DELETE CASCADE,
  entry_type TEXT NOT NULL CHECK (entry_type IN ('event', 'reflection', 'core')),
  title TEXT,
  content TEXT NOT NULL,
  date_created TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL
);

-- Index for faster queries
CREATE INDEX IF NOT EXISTS idx_journal_entries_user_id ON public.journal_entries(user_id);
CREATE INDEX IF NOT EXISTS idx_journal_entries_date_created ON public.journal_entries(date_created);
CREATE INDEX IF NOT EXISTS idx_journal_entries_entry_type ON public.journal_entries(entry_type);

-- Enable Row Level Security
ALTER TABLE public.journal_entries ENABLE ROW LEVEL SECURITY;

-- Policy: Users can only access their own journal entries
CREATE POLICY "Users can view own journal entries" ON public.journal_entries
  FOR SELECT USING (auth.uid() = user_id);

CREATE POLICY "Users can create own journal entries" ON public.journal_entries
  FOR INSERT WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can update own journal entries" ON public.journal_entries
  FOR UPDATE USING (auth.uid() = user_id);

CREATE POLICY "Users can delete own journal entries" ON public.journal_entries
  FOR DELETE USING (auth.uid() = user_id);

-- ============================================
-- 3. JOURNAL TAGS
-- ============================================
CREATE TABLE IF NOT EXISTS public.journal_tags (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name TEXT NOT NULL,
  user_id UUID NOT NULL REFERENCES public.profiles(id) ON DELETE CASCADE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
  UNIQUE(user_id, name)
);

CREATE INDEX IF NOT EXISTS idx_journal_tags_user_id ON public.journal_tags(user_id);

ALTER TABLE public.journal_tags ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can manage own tags" ON public.journal_tags
  FOR ALL USING (auth.uid() = user_id);

-- ============================================
-- 4. JOURNAL ENTRY TAGS (Many-to-Many)
-- ============================================
CREATE TABLE IF NOT EXISTS public.journal_entry_tags (
  entry_id UUID NOT NULL REFERENCES public.journal_entries(id) ON DELETE CASCADE,
  tag_id UUID NOT NULL REFERENCES public.journal_tags(id) ON DELETE CASCADE,
  PRIMARY KEY (entry_id, tag_id)
);

CREATE INDEX IF NOT EXISTS idx_journal_entry_tags_entry_id ON public.journal_entry_tags(entry_id);
CREATE INDEX IF NOT EXISTS idx_journal_entry_tags_tag_id ON public.journal_entry_tags(tag_id);

ALTER TABLE public.journal_entry_tags ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can manage entry tags for own entries" ON public.journal_entry_tags
  FOR ALL USING (
    EXISTS (
      SELECT 1 FROM public.journal_entries
      WHERE journal_entries.id = journal_entry_tags.entry_id
      AND journal_entries.user_id = auth.uid()
    )
  );

-- ============================================
-- 5. HABITS (Long-term Goals)
-- ============================================
CREATE TABLE IF NOT EXISTS public.habits (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL REFERENCES public.profiles(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  description TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
  is_active BOOLEAN DEFAULT TRUE NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_habits_user_id ON public.habits(user_id);

ALTER TABLE public.habits ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can manage own habits" ON public.habits
  FOR ALL USING (auth.uid() = user_id);

-- ============================================
-- 6. DAILY TASKS
-- ============================================
CREATE TABLE IF NOT EXISTS public.daily_tasks (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  habit_id UUID NOT NULL REFERENCES public.habits(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES public.profiles(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  task_date DATE NOT NULL,
  is_completed BOOLEAN DEFAULT FALSE NOT NULL,
  completed_at TIMESTAMP WITH TIME ZONE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
  UNIQUE(habit_id, task_date)
);

CREATE INDEX IF NOT EXISTS idx_daily_tasks_user_id ON public.daily_tasks(user_id);
CREATE INDEX IF NOT EXISTS idx_daily_tasks_habit_id ON public.daily_tasks(habit_id);
CREATE INDEX IF NOT EXISTS idx_daily_tasks_task_date ON public.daily_tasks(task_date);

ALTER TABLE public.daily_tasks ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can manage own tasks" ON public.daily_tasks
  FOR ALL USING (auth.uid() = user_id);

-- ============================================
-- 7. TASK HISTORY (for visualization)
-- ============================================
-- This table stores historical task completion data
-- Can be populated from daily_tasks or maintained separately
CREATE TABLE IF NOT EXISTS public.task_history (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL REFERENCES public.profiles(id) ON DELETE CASCADE,
  habit_id UUID NOT NULL REFERENCES public.habits(id) ON DELETE CASCADE,
  task_date DATE NOT NULL,
  is_completed BOOLEAN NOT NULL,
  completed_at TIMESTAMP WITH TIME ZONE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
  UNIQUE(user_id, habit_id, task_date)
);

CREATE INDEX IF NOT EXISTS idx_task_history_user_id ON public.task_history(user_id);
CREATE INDEX IF NOT EXISTS idx_task_history_habit_id ON public.task_history(habit_id);
CREATE INDEX IF NOT EXISTS idx_task_history_task_date ON public.task_history(task_date);

ALTER TABLE public.task_history ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view own task history" ON public.task_history
  FOR SELECT USING (auth.uid() = user_id);

-- ============================================
-- 8. INSIGHTS
-- ============================================
CREATE TABLE IF NOT EXISTS public.insights (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL REFERENCES public.profiles(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  insight_type TEXT NOT NULL CHECK (insight_type IN ('journal', 'habits', 'combined')),
  generated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_insights_user_id ON public.insights(user_id);
CREATE INDEX IF NOT EXISTS idx_insights_generated_at ON public.insights(generated_at);

ALTER TABLE public.insights ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view own insights" ON public.insights
  FOR SELECT USING (auth.uid() = user_id);

CREATE POLICY "Users can create own insights" ON public.insights
  FOR INSERT WITH CHECK (auth.uid() = user_id);

-- ============================================
-- 9. USER PREFERENCES/SETTINGS
-- ============================================
CREATE TABLE IF NOT EXISTS public.user_preferences (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL UNIQUE REFERENCES public.profiles(id) ON DELETE CASCADE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_user_preferences_user_id ON public.user_preferences(user_id);

ALTER TABLE public.user_preferences ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can manage own preferences" ON public.user_preferences
  FOR ALL USING (auth.uid() = user_id);

-- Allow users to create their own preferences row
CREATE POLICY "Users can create own preferences" ON public.user_preferences
  FOR INSERT WITH CHECK (auth.uid() = user_id);

-- ============================================
-- 10. THERAPISTS
-- ============================================
CREATE TABLE IF NOT EXISTS public.therapists (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL UNIQUE REFERENCES auth.users(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  email TEXT NOT NULL,
  license_number TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_therapists_user_id ON public.therapists(user_id);

ALTER TABLE public.therapists ENABLE ROW LEVEL SECURITY;

-- Therapists can view their own profile
CREATE POLICY "Therapists can view own profile" ON public.therapists
  FOR SELECT USING (auth.uid() = user_id);

-- ============================================
-- 11. USER-THERAPIST CONNECTIONS
-- ============================================
CREATE TABLE IF NOT EXISTS public.user_therapist_connections (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL REFERENCES public.profiles(id) ON DELETE CASCADE,
  therapist_id UUID NOT NULL REFERENCES public.therapists(id) ON DELETE CASCADE,
  -- Sharing settings: what the user wants to share with this therapist
  share_journal BOOLEAN DEFAULT FALSE NOT NULL,
  share_habits BOOLEAN DEFAULT FALSE NOT NULL,
  share_insights BOOLEAN DEFAULT FALSE NOT NULL,
  connected_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
  UNIQUE(user_id, therapist_id)
);

CREATE INDEX IF NOT EXISTS idx_user_therapist_connections_user_id ON public.user_therapist_connections(user_id);
CREATE INDEX IF NOT EXISTS idx_user_therapist_connections_therapist_id ON public.user_therapist_connections(therapist_id);

ALTER TABLE public.user_therapist_connections ENABLE ROW LEVEL SECURITY;

-- Users can view and manage their own connections
CREATE POLICY "Users can manage own therapist connections" ON public.user_therapist_connections
  FOR ALL USING (auth.uid() = user_id);

-- Therapists can view connections where they are the therapist
CREATE POLICY "Therapists can view connections" ON public.user_therapist_connections
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM public.therapists
      WHERE therapists.id = user_therapist_connections.therapist_id
      AND therapists.user_id = auth.uid()
    )
  );

-- ============================================
-- 12. PROFILE STATS
-- ============================================
CREATE TABLE IF NOT EXISTS public.profile_stats (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL UNIQUE REFERENCES public.profiles(id) ON DELETE CASCADE,
  -- Computed stats (can be calculated or maintained)
  total_events INTEGER DEFAULT 0 NOT NULL,
  total_reflections INTEGER DEFAULT 0 NOT NULL,
  total_core INTEGER DEFAULT 0 NOT NULL,
  total_tasks_completed INTEGER DEFAULT 0 NOT NULL,
  current_streak INTEGER DEFAULT 0 NOT NULL,
  longest_streak INTEGER DEFAULT 0 NOT NULL,
  last_updated TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_profile_stats_user_id ON public.profile_stats(user_id);

ALTER TABLE public.profile_stats ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view own stats" ON public.profile_stats
  FOR SELECT USING (auth.uid() = user_id);

CREATE POLICY "Users can update own stats" ON public.profile_stats
  FOR UPDATE USING (auth.uid() = user_id);

-- Allow users to create their own stats row
CREATE POLICY "Users can create own stats" ON public.profile_stats
  FOR INSERT WITH CHECK (auth.uid() = user_id);

-- ============================================
-- 13. BADGES
-- ============================================
CREATE TABLE IF NOT EXISTS public.badges (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name TEXT NOT NULL UNIQUE,
  description TEXT NOT NULL,
  icon TEXT,
  criteria TEXT NOT NULL, -- JSON or text describing how to earn this badge
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL
);

-- ============================================
-- 14. USER BADGES (Many-to-Many)
-- ============================================
CREATE TABLE IF NOT EXISTS public.user_badges (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL REFERENCES public.profiles(id) ON DELETE CASCADE,
  badge_id UUID NOT NULL REFERENCES public.badges(id) ON DELETE CASCADE,
  earned_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
  UNIQUE(user_id, badge_id)
);

CREATE INDEX IF NOT EXISTS idx_user_badges_user_id ON public.user_badges(user_id);
CREATE INDEX IF NOT EXISTS idx_user_badges_badge_id ON public.user_badges(badge_id);

ALTER TABLE public.user_badges ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view own badges" ON public.user_badges
  FOR SELECT USING (auth.uid() = user_id);

-- ============================================
-- FUNCTIONS & TRIGGERS
-- ============================================

-- Function to automatically create profile when user signs up
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO public.profiles (id, name, email)
  VALUES (
    NEW.id,
    COALESCE(NEW.raw_user_meta_data->>'name', NEW.email),
    NEW.email
  );
  
  -- Create default preferences
  INSERT INTO public.user_preferences (user_id)
  VALUES (NEW.id);
  
  -- Initialize profile stats
  INSERT INTO public.profile_stats (user_id)
  VALUES (NEW.id);
  
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Trigger to run the function when a new user is created
DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users;
CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();

-- Function to update updated_at timestamp
CREATE OR REPLACE FUNCTION public.update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Add updated_at triggers to relevant tables
CREATE TRIGGER update_profiles_updated_at
  BEFORE UPDATE ON public.profiles
  FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();

CREATE TRIGGER update_journal_entries_updated_at
  BEFORE UPDATE ON public.journal_entries
  FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();

CREATE TRIGGER update_habits_updated_at
  BEFORE UPDATE ON public.habits
  FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();

CREATE TRIGGER update_user_preferences_updated_at
  BEFORE UPDATE ON public.user_preferences
  FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();

CREATE TRIGGER update_user_therapist_connections_updated_at
  BEFORE UPDATE ON public.user_therapist_connections
  FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();

-- Function to update profile stats when journal entry is created
CREATE OR REPLACE FUNCTION public.update_journal_stats()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.entry_type = 'event' THEN
    UPDATE public.profile_stats
    SET total_events = total_events + 1,
        last_updated = NOW()
    WHERE user_id = NEW.user_id;
  ELSIF NEW.entry_type = 'reflection' THEN
    UPDATE public.profile_stats
    SET total_reflections = total_reflections + 1,
        last_updated = NOW()
    WHERE user_id = NEW.user_id;
  ELSIF NEW.entry_type = 'core' THEN
    UPDATE public.profile_stats
    SET total_core = total_core + 1,
        last_updated = NOW()
    WHERE user_id = NEW.user_id;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER update_stats_on_journal_entry
  AFTER INSERT ON public.journal_entries
  FOR EACH ROW EXECUTE FUNCTION public.update_journal_stats();

-- Function to update task completion stats
CREATE OR REPLACE FUNCTION public.update_task_stats()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.is_completed = TRUE AND (OLD.is_completed IS NULL OR OLD.is_completed = FALSE) THEN
    UPDATE public.profile_stats
    SET total_tasks_completed = total_tasks_completed + 1,
        last_updated = NOW()
    WHERE user_id = NEW.user_id;
    
    -- Also insert into task_history
    INSERT INTO public.task_history (user_id, habit_id, task_date, is_completed, completed_at)
    VALUES (NEW.user_id, NEW.habit_id, NEW.task_date, TRUE, NEW.completed_at)
    ON CONFLICT (user_id, habit_id, task_date) 
    DO UPDATE SET is_completed = TRUE, completed_at = NEW.completed_at;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER update_stats_on_task_completion
  AFTER INSERT OR UPDATE ON public.daily_tasks
  FOR EACH ROW EXECUTE FUNCTION public.update_task_stats();

-- ============================================
-- INITIAL BADGES (Optional - seed data)
-- ============================================
INSERT INTO public.badges (name, description, criteria) VALUES
  ('First Steps', 'Created your first journal entry', '{"type": "journal_entry", "count": 1}'),
  ('Reflector', 'Created 10 reflections', '{"type": "reflection", "count": 10}'),
  ('Event Keeper', 'Created 10 events', '{"type": "event", "count": 10}'),
  ('Core Moments', 'Created 5 core memories', '{"type": "core", "count": 5}'),
  ('Consistent', 'Completed tasks for 7 days straight', '{"type": "streak", "days": 7}'),
  ('Dedicated', 'Completed tasks for 30 days straight', '{"type": "streak", "days": 30}'),
  ('Task Master', 'Completed 100 tasks', '{"type": "tasks_completed", "count": 100}')
ON CONFLICT (name) DO NOTHING;