supabase-schema.sql

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

-- snippets main table (English only)
CREATE TABLE IF NOT EXISTS public.snippets (
  id          UUID          PRIMARY KEY DEFAULT uuid_generate_v4(),
  created_at  TIMESTAMPTZ   DEFAULT NOW(),
  user_id     UUID          REFERENCES auth.users,
  code        TEXT          NOT NULL,
  language    TEXT          NOT NULL,
  title       TEXT,
  description TEXT,
  god_count   BIGINT        DEFAULT 0,
  shit_count  BIGINT        DEFAULT 0
);

-- God/Shit votes table
CREATE TABLE IF NOT EXISTS public.snippet_votes (
  id          UUID          PRIMARY KEY DEFAULT uuid_generate_v4(),
  snippet_id  UUID          REFERENCES public.snippets(id) ON DELETE CASCADE,
  user_id     UUID          REFERENCES auth.users NOT NULL,
  vote_type   TEXT          NOT NULL CHECK (vote_type IN ('god', 'shit')),
  created_at  TIMESTAMPTZ   DEFAULT NOW(),
  UNIQUE(snippet_id, user_id, vote_type)
);

-- Create indexes
CREATE INDEX IF NOT EXISTS idx_snippets_created_at ON public.snippets(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_snippets_language ON public.snippets(language);
CREATE INDEX IF NOT EXISTS idx_snippet_votes_snippet_id ON public.snippet_votes(snippet_id);
CREATE INDEX IF NOT EXISTS idx_snippet_votes_user_id ON public.snippet_votes(user_id);

-- Row Level Security (RLS)
ALTER TABLE public.snippets ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.snippet_votes ENABLE ROW LEVEL SECURITY;

-- Snippets table RLS policies
DROP POLICY IF EXISTS "Allow public read access to snippets" ON public.snippets;
CREATE POLICY "Allow public read access to snippets"
  ON public.snippets FOR SELECT
  USING (true);

-- can_submit_snippet(): 当前登录用户当天投稿次数 < 5 时返回 true
CREATE OR REPLACE FUNCTION public.can_submit_snippet()
RETURNS BOOLEAN
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  submissions_today BIGINT;
BEGIN
  SELECT COUNT(*)
    INTO submissions_today
  FROM public.snippets
  WHERE user_id = auth.uid()
    AND created_at >= date_trunc('day', now());

  RETURN submissions_today < 5;
END;
$$;

DROP POLICY IF EXISTS "Allow anonymous insert to snippets" ON public.snippets;
DROP POLICY IF EXISTS "Allow authenticated insert to snippets" ON public.snippets;
CREATE POLICY "Allow authenticated insert to snippets"
  ON public.snippets FOR INSERT
  WITH CHECK (auth.uid() = user_id AND public.can_submit_snippet());

DROP POLICY IF EXISTS "Allow owner update snippets" ON public.snippets;
CREATE POLICY "Allow owner update snippets"
  ON public.snippets FOR UPDATE
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

DROP POLICY IF EXISTS "Allow owner delete snippets" ON public.snippets;
CREATE POLICY "Allow owner delete snippets"
  ON public.snippets FOR DELETE
  USING (auth.uid() = user_id);

-- Admin management
CREATE TABLE IF NOT EXISTS public.admin_users (
  user_id     UUID          PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  created_at  TIMESTAMPTZ   DEFAULT NOW()
);

ALTER TABLE public.admin_users ENABLE ROW LEVEL SECURITY;

CREATE OR REPLACE FUNCTION public.is_admin()
RETURNS BOOLEAN
LANGUAGE sql
SECURITY DEFINER
SET search_path = public
AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.admin_users WHERE user_id = auth.uid()
  );
$$;

DROP POLICY IF EXISTS "Allow admin insert to snippets" ON public.snippets;
CREATE POLICY "Allow admin insert to snippets"
  ON public.snippets FOR INSERT
  WITH CHECK (public.is_admin() AND auth.uid() = user_id);

DROP POLICY IF EXISTS "Allow admin update snippets" ON public.snippets;
CREATE POLICY "Allow admin update snippets"
  ON public.snippets FOR UPDATE
  USING (public.is_admin())
  WITH CHECK (public.is_admin());

DROP POLICY IF EXISTS "Allow admin delete snippets" ON public.snippets;
CREATE POLICY "Allow admin delete snippets"
  ON public.snippets FOR DELETE
  USING (public.is_admin());

-- Snippet votes table RLS policies
DROP POLICY IF EXISTS "Allow public read access to snippet_votes" ON public.snippet_votes;
CREATE POLICY "Allow public read access to snippet_votes"
  ON public.snippet_votes FOR SELECT
  USING (true);

DROP POLICY IF EXISTS "Allow authenticated users to vote" ON public.snippet_votes;
CREATE POLICY "Allow authenticated users to vote"
  ON public.snippet_votes FOR INSERT
  WITH CHECK (auth.uid() = user_id);

DROP POLICY IF EXISTS "Allow users to delete their own votes" ON public.snippet_votes;
CREATE POLICY "Allow users to delete their own votes"
  ON public.snippet_votes FOR DELETE
  USING (auth.uid() = user_id);

-- Create trigger to automatically update vote counts
CREATE OR REPLACE FUNCTION public.update_snippet_vote_count()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    IF NEW.vote_type = 'god' THEN
      UPDATE public.snippets SET god_count = god_count + 1 WHERE id = NEW.snippet_id;
    ELSIF NEW.vote_type = 'shit' THEN
      UPDATE public.snippets SET shit_count = shit_count + 1 WHERE id = NEW.snippet_id;
    END IF;
    RETURN NEW;
  ELSIF TG_OP = 'DELETE' THEN
    IF OLD.vote_type = 'god' THEN
      UPDATE public.snippets SET god_count = god_count - 1 WHERE id = OLD.snippet_id;
    ELSIF OLD.vote_type = 'shit' THEN
      UPDATE public.snippets SET shit_count = shit_count - 1 WHERE id = OLD.snippet_id;
    END IF;
    RETURN OLD;
  END IF;
END;
$$ LANGUAGE plpgsql;

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM pg_trigger
    WHERE tgname = 'snippet_vote_count_trigger'
  ) THEN
    EXECUTE '
      CREATE TRIGGER snippet_vote_count_trigger
        AFTER INSERT OR DELETE ON public.snippet_votes
        FOR EACH ROW
        EXECUTE FUNCTION public.update_snippet_vote_count();
    ';
  END IF;
END;
$$;

-- Realtime subscription configuration
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM pg_publication_tables
    WHERE pubname = 'supabase_realtime'
      AND schemaname = 'public'
      AND tablename = 'snippets'
  ) THEN
    ALTER PUBLICATION supabase_realtime ADD TABLE public.snippets;
  END IF;
END;
$$;