-- Create tables directly in Supabase SQL editor
CREATE TABLE articles (
    id          BIGSERIAL PRIMARY KEY,
    title       TEXT NOT NULL,
    slug        TEXT UNIQUE NOT NULL,
    content     TEXT,
    published   BOOLEAN DEFAULT FALSE,
    views       INTEGER DEFAULT 0,
    author_id   UUID REFERENCES auth.users(id) ON DELETE CASCADE,
    created_at  TIMESTAMPTZ DEFAULT NOW(),
    updated_at  TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE tags (
    id   BIGSERIAL PRIMARY KEY,
    name TEXT UNIQUE NOT NULL
);

CREATE TABLE article_tags (
    article_id BIGINT REFERENCES articles(id) ON DELETE CASCADE,
    tag_id     BIGINT REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (article_id, tag_id)
);

-- Create index for performance
CREATE INDEX idx_articles_slug ON articles(slug);
CREATE INDEX idx_articles_published ON articles(published, created_at DESC);

import { createClient } from "@supabase/supabase-js";

const supabase = createClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
);

// SELECT — fetch published articles with tag filter
const { data: articles, error } = await supabase
    .from("articles")
    .select(`
        id,
        title,
        slug,
        created_at,
        author:author_id ( name, avatar_url ),
        tags ( name )
    `)
    .eq("published", true)
    .order("created_at", { ascending: false })
    .range(0, 19);   // Pagination: rows 0–19

// INSERT — create a new article
const { data: newArticle, error } = await supabase
    .from("articles")
    .insert({
        title: "What is Supabase?",
        slug: "what-is-supabase",
        content: "Supabase is...",
        author_id: user.id
    })
    .select()
    .single();

// UPDATE — publish an article
const { error } = await supabase
    .from("articles")
    .update({ published: true })
    .eq("id", articleId)
    .eq("author_id", user.id);   // Extra safety check

// DELETE
const { error } = await supabase
    .from("articles")
    .delete()
    .eq("id", articleId);

// Email and password signup
const { data, error } = await supabase.auth.signUp({
    email: "user@example.com",
    password: "securepassword123",
    options: {
        data: { full_name: "Rahul Sharma" }   // Custom user metadata
    }
});

// Email and password login
const { data, error } = await supabase.auth.signInWithPassword({
    email: "user@example.com",
    password: "securepassword123"
});

// Magic link (passwordless)
const { error } = await supabase.auth.signInWithOtp({
    email: "user@example.com",
    options: { emailRedirectTo: "https://myapp.com/auth/callback" }
});

// OAuth — Google, GitHub, Twitter, Discord, etc.
const { error } = await supabase.auth.signInWithOAuth({
    provider: "google",
    options: { redirectTo: "https://myapp.com/auth/callback" }
});

// Sign out
await supabase.auth.signOut();

// Get current user
const { data: { user } } = await supabase.auth.getUser();

// Listen to auth state changes
supabase.auth.onAuthStateChange((event, session) => {
    if (event === "SIGNED_IN") setUser(session.user);
    if (event === "SIGNED_OUT") setUser(null);
});

// lib/supabase/server.ts
import { createServerClient } from "@supabase/ssr";
import { cookies } from "next/headers";

export function createClient() {
    const cookieStore = cookies();
    return createServerClient(
        process.env.NEXT_PUBLIC_SUPABASE_URL!,
        process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
        {
            cookies: {
                get(name) { return cookieStore.get(name)?.value; },
                set(name, value, options) { cookieStore.set(name, value, options); },
                remove(name, options) { cookieStore.set(name, "", options); }
            }
        }
    );
}

// app/dashboard/page.tsx — Protected Server Component
import { redirect } from "next/navigation";
import { createClient } from "@/lib/supabase/server";

export default async function DashboardPage() {
    const supabase = createClient();
    const { data: { user } } = await supabase.auth.getUser();

    if (!user) redirect("/login");

    const { data: articles } = await supabase
        .from("articles")
        .select("*")
        .eq("author_id", user.id);

    return <ArticleList articles={articles} />;
}

-- Enable RLS on the articles table
ALTER TABLE articles ENABLE ROW LEVEL SECURITY;

-- Policy: Anyone can read published articles
CREATE POLICY "Published articles are publicly readable"
    ON articles FOR SELECT
    USING (published = TRUE);

-- Policy: Users can only read their own draft articles
CREATE POLICY "Authors can read their own drafts"
    ON articles FOR SELECT
    USING (auth.uid() = author_id);

-- Policy: Users can only insert articles as themselves
CREATE POLICY "Authors can create articles"
    ON articles FOR INSERT
    WITH CHECK (auth.uid() = author_id);

-- Policy: Authors can only update their own articles
CREATE POLICY "Authors can update own articles"
    ON articles FOR UPDATE
    USING (auth.uid() = author_id)
    WITH CHECK (auth.uid() = author_id);

-- Policy: Authors can only delete their own articles
CREATE POLICY "Authors can delete own articles"
    ON articles FOR DELETE
    USING (auth.uid() = author_id);

Without RLS:
User A makes a request → Application code checks "is this user A's data?"
→ If developer forgets the check, user B can access user A's data
→ Security depends entirely on every developer never making a mistake

With Supabase RLS:
User A makes a request → Database automatically applies the RLS policy
→ Even if developer forgets an application-level check, RLS blocks it
→ Security enforced at the database level — cannot be bypassed by code

// Subscribe to new messages in a chat room
const channel = supabase
    .channel("chat-room")
    .on(
        "postgres_changes",
        {
            event: "INSERT",
            schema: "public",
            table: "messages",
            filter: "room_id=eq.123"
        },
        (payload) => {
            const newMessage = payload.new;
            setMessages(prev => [...prev, newMessage]);
        }
    )
    .subscribe();

// Subscribe to all changes on a table
const channel = supabase
    .channel("articles")
    .on("postgres_changes",
        { event: "*", schema: "public", table: "articles" },
        (payload) => {
            if (payload.eventType === "INSERT") addArticle(payload.new);
            if (payload.eventType === "UPDATE") updateArticle(payload.new);
            if (payload.eventType === "DELETE") removeArticle(payload.old.id);
        }
    )
    .subscribe();

// Presence — track who is online (great for collaborative tools)
const room = supabase.channel("document-123");

await room.track({ user_id: user.id, user_name: user.name, cursor: null });

room.on("presence", { event: "sync" }, () => {
    const state = room.presenceState();
    const onlineUsers = Object.values(state).flat();
    setCollaborators(onlineUsers);
});

// Unsubscribe when component unmounts
return () => { supabase.removeChannel(channel); };