-- Standard table structure
CREATE TABLE public.{table_name} (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  -- Table-specific columns
  name TEXT NOT NULL,
  data JSONB DEFAULT '{}'::jsonb
);

-- Updated at trigger
CREATE TRIGGER set_updated_at
  BEFORE UPDATE ON public.{table_name}
  FOR EACH ROW
  EXECUTE FUNCTION public.handle_updated_at();

-- Enable RLS
ALTER TABLE public.{table_name} ENABLE ROW LEVEL SECURITY;

-- User can only see their own rows
CREATE POLICY "Users can view own {table_name}"
  ON public.{table_name}
  FOR SELECT
  USING (auth.uid() = user_id);

-- User can insert their own rows
CREATE POLICY "Users can insert own {table_name}"
  ON public.{table_name}
  FOR INSERT
  WITH CHECK (auth.uid() = user_id);

-- User can update their own rows
CREATE POLICY "Users can update own {table_name}"
  ON public.{table_name}
  FOR UPDATE
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

-- User can delete their own rows
CREATE POLICY "Users can delete own {table_name}"
  ON public.{table_name}
  FOR DELETE
  USING (auth.uid() = user_id);

-- User Ideas (Ideas Mode)
CREATE TABLE public.user_ideas (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  content TEXT NOT NULL,
  tags TEXT[] DEFAULT '{}',
  position JSONB DEFAULT '{"x": 0, "y": 0}',
  colour TEXT DEFAULT 'default',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Scout Opportunities
CREATE TABLE public.scout_opportunities (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  type TEXT NOT NULL, -- playlist, blog, radio, etc.
  genres TEXT[] DEFAULT '{}',
  contact_email TEXT,
  website TEXT,
  audience_size TEXT, -- emerging, growing, established
  verified_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Timeline Events
CREATE TABLE public.timeline_events (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  type TEXT NOT NULL, -- release, pitch, content, etc.
  due_date DATE NOT NULL,
  completed BOOLEAN DEFAULT FALSE,
  opportunity_id UUID REFERENCES public.scout_opportunities(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Pitch Drafts
CREATE TABLE public.pitch_drafts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  type TEXT NOT NULL, -- bio-short, bio-long, etc.
  content TEXT NOT NULL,
  version INT DEFAULT 1,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

# Generate types from Supabase schema
npx supabase gen types typescript --project-id <project-id> > packages/schemas/database/types.ts

import { createClient } from '@/lib/supabase/server'

async function getData() {
  const supabase = await createClient()
  const { data, error } = await supabase
    .from('user_ideas')
    .select('*')
    .order('created_at', { ascending: false })

  if (error) throw error
  return data
}

import { createClient } from '@/lib/supabase/client'

function useIdeas() {
  const supabase = createClient()

  const fetchIdeas = async () => {
    const { data, error } = await supabase
      .from('user_ideas')
      .select('*')

    if (error) throw error
    return data
  }

  return { fetchIdeas }
}

// Filtered query with pagination
const { data, count } = await supabase
  .from('scout_opportunities')
  .select('*', { count: 'exact' })
  .in('type', ['playlist', 'blog'])
  .contains('genres', ['indie'])
  .order('verified_at', { ascending: false })
  .range(0, 19) // First 20 results

// Upsert pattern
const { error } = await supabase
  .from('user_ideas')
  .upsert(
    { id: existingId, content: newContent, updated_at: new Date() },
    { onConflict: 'id' }
  )

// Batch insert
const { error } = await supabase
  .from('timeline_events')
  .insert(events)

-- For filtered queries
CREATE INDEX idx_opportunities_type ON public.scout_opportunities(type);
CREATE INDEX idx_opportunities_genres ON public.scout_opportunities USING GIN(genres);
CREATE INDEX idx_ideas_user ON public.user_ideas(user_id);
CREATE INDEX idx_timeline_user_date ON public.timeline_events(user_id, due_date);

# Create new migration
npx supabase migration new add_new_table

# Apply locally
npx supabase db reset

# Push to remote
npx supabase db push