supabase_setup.sql

-- ── Supabase Tables and RLS Policies for AniPlay ──
-- Safe to run multiple times — drops old policies/triggers before recreating them.

-- ══════════════════════════════════════════════════════════════
-- 1. USER PROFILES TABLE & AUTOMATIC USER PROFILE TRIGGER
-- ══════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS public.user_profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  nickname TEXT NOT NULL,
  avatar_url TEXT,
  recently_viewed JSONB DEFAULT '[]'::jsonb,
  settings JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ DEFAULT TIMEZONE('utc'::text, NOW()) NOT NULL
);

-- Safely migrate existing tables by adding columns if they do not exist
ALTER TABLE public.user_profiles
  ADD COLUMN IF NOT EXISTS recently_viewed JSONB DEFAULT '[]'::jsonb,
  ADD COLUMN IF NOT EXISTS settings JSONB DEFAULT '{}'::jsonb;

ALTER TABLE public.user_profiles ENABLE ROW LEVEL SECURITY;

-- Drop existing policies first
DROP POLICY IF EXISTS "Allow public read access to profiles"       ON public.user_profiles;
DROP POLICY IF EXISTS "Allow individual insert/update to profiles" ON public.user_profiles;

-- Recreate policies
CREATE POLICY "Allow public read access to profiles"
  ON public.user_profiles FOR SELECT
  TO public
  USING (true);

CREATE POLICY "Allow individual insert/update to profiles"
  ON public.user_profiles FOR ALL
  TO authenticated
  USING (auth.uid() = id)
  WITH CHECK (auth.uid() = id);

-- Trigger: Automatically create profile when a user signs up in auth.users
-- This runs on the database system level (SECURITY DEFINER) to bypass RLS restrictions during signup.
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS trigger AS $$
BEGIN
  INSERT INTO public.user_profiles (id, nickname, created_at)
  VALUES (
    new.id,
    COALESCE(new.raw_user_meta_data->>'nickname', split_part(new.email, '@', 1)),
    NOW()
  );
  RETURN new;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Safely recreate the trigger
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();


-- ══════════════════════════════════════════════════════════════
-- 2. WATCHLIST TABLE
-- ══════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS public.watchlist (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  anime_id TEXT NOT NULL,
  status TEXT DEFAULT 'watching'::text,
  favorite BOOLEAN DEFAULT false,
  progress JSONB DEFAULT '{}'::jsonb,
  updated_at TIMESTAMPTZ DEFAULT TIMEZONE('utc'::text, NOW()) NOT NULL
);

ALTER TABLE public.watchlist ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Allow users to manage their own watchlist" ON public.watchlist;

CREATE POLICY "Allow users to manage their own watchlist"
  ON public.watchlist FOR ALL
  TO authenticated
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

-- Drop redundant index (already covered by unique constraint watchlist_user_anime_unique)
DROP INDEX IF EXISTS public.idx_watchlist_user_anime;

-- Clean up duplicate watchlist items to keep the constraint addition safe
DELETE FROM public.watchlist a USING public.watchlist b
WHERE a.id < b.id AND a.user_id = b.user_id AND a.anime_id = b.anime_id;

-- Add unique constraint (ensures bulk upsert matches rows correctly on user_id + anime_id)
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint WHERE conname = 'watchlist_user_anime_unique'
  ) THEN
    ALTER TABLE public.watchlist ADD CONSTRAINT watchlist_user_anime_unique UNIQUE (user_id, anime_id);
  END IF;
END $$;


-- ══════════════════════════════════════════════════════════════
-- 3. COMMENTS TABLE
-- ══════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS public.comments (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  anime_id TEXT NOT NULL,
  episode INTEGER NOT NULL,
  username TEXT NOT NULL,
  content TEXT NOT NULL,
  user_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
  created_at TIMESTAMPTZ DEFAULT TIMEZONE('utc'::text, NOW()) NOT NULL
);

ALTER TABLE public.comments ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Allow public read access to comments"            ON public.comments;
DROP POLICY IF EXISTS "Allow authenticated users to insert comments"    ON public.comments;

CREATE POLICY "Allow public read access to comments"
  ON public.comments FOR SELECT
  TO public
  USING (true);

CREATE POLICY "Allow authenticated users to insert comments"
  ON public.comments FOR INSERT
  TO public
  WITH CHECK (true);

-- Drop old simple index
DROP INDEX IF EXISTS public.idx_comments_anime_episode;

-- Create composite index optimized for comments query (filtering on anime_id + episode, sorted by date)
CREATE INDEX IF NOT EXISTS idx_comments_anime_episode_created
  ON public.comments(anime_id, episode, created_at DESC);

-- Create index on foreign key user_id to prevent full table scans/locks on user account deletion
CREATE INDEX IF NOT EXISTS idx_comments_user_id
  ON public.comments(user_id);