Database Schema Setup

CREATE TABLE public.profiles (
  id UUID REFERENCES auth.users(id) ON DELETE CASCADE PRIMARY KEY,
  username TEXT UNIQUE NOT NULL,
  avatar_url TEXT,
  bio TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL
);

CREATE TABLE public.anime_entries (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  user_id UUID REFERENCES public.profiles(id) ON DELETE CASCADE NOT NULL,
  anime_id INTEGER NOT NULL,
  title TEXT NOT NULL,
  status TEXT NOT NULL CHECK (status IN (
    'watching', 'completed', 'on-hold', 'dropped', 'plan-to-watch'
  )),
  episodes_watched INTEGER DEFAULT 0 NOT NULL,
  score INTEGER CHECK (score >= 1 AND score <= 10),
  start_date DATE,
  finish_date DATE,
  notes TEXT,
  favorite BOOLEAN DEFAULT false NOT NULL,
  -- Additional fields...
  UNIQUE(user_id, anime_id)
);

CREATE TABLE public.reviews (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  user_id UUID REFERENCES public.profiles(id) ON DELETE CASCADE NOT NULL,
  anime_id INTEGER NOT NULL,
  rating INTEGER NOT NULL CHECK (rating >= 1 AND rating <= 10),
  story_rating INTEGER CHECK (story_rating >= 1 AND story_rating <= 10),
  animation_rating INTEGER CHECK (animation_rating >= 1 AND animation_rating <= 10),
  sound_rating INTEGER CHECK (sound_rating >= 1 AND sound_rating <= 10),
  character_rating INTEGER CHECK (character_rating >= 1 AND character_rating <= 10),
  enjoyment_rating INTEGER CHECK (enjoyment_rating >= 1 AND enjoyment_rating <= 10),
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  spoilers BOOLEAN DEFAULT false NOT NULL,
  status TEXT DEFAULT 'draft' NOT NULL CHECK (status IN ('draft', 'published')),
  helpful_votes INTEGER DEFAULT 0 NOT NULL,
  -- Additional fields...
);

-- Everyone can view profiles
CREATE POLICY "Users can view all profiles"
  ON public.profiles FOR SELECT
  USING (true);

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

-- Users can only see their own entries
CREATE POLICY "Users can view their own anime entries"
  ON public.anime_entries FOR SELECT
  USING (auth.uid() = user_id);

-- Users can insert their own entries
CREATE POLICY "Users can insert their own anime entries"
  ON public.anime_entries FOR INSERT
  WITH CHECK (auth.uid() = user_id);

-- Anyone can view published reviews, users can see their own drafts
CREATE POLICY "Anyone can view published reviews"
  ON public.reviews FOR SELECT
  USING (status = 'published' OR auth.uid() = user_id);

CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO public.profiles (id, username)
  VALUES (
    NEW.id,
    COALESCE(NEW.raw_user_meta_data->>'username', split_part(NEW.email, '@', 1))
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();

CREATE OR REPLACE FUNCTION public.handle_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_profiles_updated_at
  BEFORE UPDATE ON public.profiles
  FOR EACH ROW EXECUTE FUNCTION public.handle_updated_at();

CREATE OR REPLACE FUNCTION public.update_review_helpful_votes()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE public.reviews
  SET helpful_votes = (
    SELECT COUNT(*) FILTER (WHERE helpful = true) - 
           COUNT(*) FILTER (WHERE helpful = false)
    FROM public.review_votes
    WHERE review_id = COALESCE(NEW.review_id, OLD.review_id)
  )
  WHERE id = COALESCE(NEW.review_id, OLD.review_id);
  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

-- Anime entries indexes
CREATE INDEX idx_anime_entries_user_id ON public.anime_entries(user_id);
CREATE INDEX idx_anime_entries_anime_id ON public.anime_entries(anime_id);
CREATE INDEX idx_anime_entries_status ON public.anime_entries(status);

-- Reviews indexes
CREATE INDEX idx_reviews_user_id ON public.reviews(user_id);
CREATE INDEX idx_reviews_anime_id ON public.reviews(anime_id);
CREATE INDEX idx_reviews_status ON public.reviews(status);

-- Partial unique index for published reviews
CREATE UNIQUE INDEX idx_reviews_user_anime_published 
ON public.reviews(user_id, anime_id) 
WHERE status = 'published';

import { 
  getAnimeEntries, 
  upsertAnimeEntry,
  deleteAnimeEntry 
} from '@/lib/supabase/queries';

// Get user's anime entries
const entries = await getAnimeEntries(userId);

// Add or update an entry
await upsertAnimeEntry({
  user_id: userId,
  anime_id: 1,
  title: 'Attack on Titan',
  status: 'watching',
  episodes_watched: 5
});

// Delete an entry
await deleteAnimeEntry(userId, animeId);