Supabase Tutorial: Backend Completo em 15 Minutos

-- Database Schema com RLS
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Tabela de perfis de usuário
CREATE TABLE profiles (
  id UUID REFERENCES auth.users(id) ON DELETE CASCADE PRIMARY KEY,
  username TEXT UNIQUE,
  full_name TEXT,
  avatar_url TEXT,
  website TEXT,
  bio TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Habilitar Row Level Security
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;

-- Policy: usuários veem apenas próprios dados
CREATE POLICY "Usuários podem ver próprio perfil" 
ON profiles FOR SELECT 
USING (auth.uid() = id);

-- Policy: usuários podem atualizar próprios dados
CREATE POLICY "Usuários podem atualizar próprio perfil" 
ON profiles FOR UPDATE 
USING (auth.uid() = id);

-- Policy: usuários podem inserir próprios dados
CREATE POLICY "Usuários podem inserir próprio perfil" 
ON profiles FOR INSERT 
WITH CHECK (auth.uid() = id);

-- Tabela de posts
CREATE TABLE posts (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  title TEXT NOT NULL,
  content TEXT,
  author_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  published BOOLEAN DEFAULT false,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- RLS para posts
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

-- Policy: todos podem ver posts publicados
CREATE POLICY "Posts publicados são visíveis" 
ON posts FOR SELECT 
USING (published = true);

-- Policy: autores podem ver próprios posts
CREATE POLICY "Autores podem ver próprios posts" 
ON posts FOR SELECT 
USING (auth.uid() = author_id);

-- Trigger para updated_at automático
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_profiles_updated_at 
BEFORE UPDATE ON profiles 
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_posts_updated_at 
BEFORE UPDATE ON posts 
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

// hooks/useAuth.ts
'use client'

import { useState, useEffect, createContext, useContext } from 'react'
import { User, Session } from '@supabase/supabase-js'
import { supabase } from '@/lib/supabase'

interface AuthContextType {
  user: User | null
  session: Session | null
  loading: boolean
  signIn: (email: string, password: string) => Promise<any>
  signUp: (email: string, password: string) => Promise<any>
  signOut: () => Promise<void>
  signInWithGitHub: () => Promise<any>
  resetPassword: (email: string) => Promise<any>
}

const AuthContext = createContext<AuthContextType | undefined>(undefined)

export function useAuth() {
  const context = useContext(AuthContext)
  if (context === undefined) {
    throw new Error('useAuth deve ser usado dentro de AuthProvider')
  }
  return context
}

export function AuthProvider({ children }: { children: React.ReactNode }) {
  const [user, setUser] = useState<User | null>(null)
  const [session, setSession] = useState<Session | null>(null)
  const [loading, setLoading] = useState(true)

  useEffect(() => {
    // Verificar sessão inicial
    const getSession = async () => {
      const { data: { session }, error } = await supabase.auth.getSession()
      
      if (error) {
        console.error('Erro ao obter sessão:', error)
      } else {
        setSession(session)
        setUser(session?.user ?? null)
      }
      
      setLoading(false)
    }

    getSession()

    // Escutar mudanças de auth
    const { data: { subscription } } = supabase.auth.onAuthStateChange(
      async (event, session) => {
        console.log('Auth event:', event)
        setSession(session)
        setUser(session?.user ?? null)
        setLoading(false)

        // Criar perfil na primeira vez
        if (event === 'SIGNED_IN' && session?.user) {
          await createUserProfile(session.user)
        }
      }
    )

    return () => subscription.unsubscribe()
  }, [])

  // Criar perfil de usuário
  const createUserProfile = async (user: User) => {
    const { data, error } = await supabase
      .from('profiles')
      .select('id')
      .eq('id', user.id)
      .single()

    if (error && error.code === 'PGRST116') {
      // Usuário não existe, criar perfil
      const { error: insertError } = await supabase
        .from('profiles')
        .insert({
          id: user.id,
          username: user.email?.split('@')[0],
          full_name: user.user_metadata?.full_name || '',
          avatar_url: user.user_metadata?.avatar_url || '',
        })

      if (insertError) {
        console.error('Erro ao criar perfil:', insertError)
      }
    }
  }

  // Login com email/senha
  const signIn = async (email: string, password: string) => {
    setLoading(true)
    const { data, error } = await supabase.auth.signInWithPassword({
      email,
      password,
    })
    setLoading(false)
    
    if (error) throw error
    return data
  }

  // Registro
  const signUp = async (email: string, password: string) => {
    setLoading(true)
    const { data, error } = await supabase.auth.signUp({
      email,
      password,
      options: {
        emailRedirectTo: `${window.location.origin}/auth/callback`,
      },
    })
    setLoading(false)
    
    if (error) throw error
    return data
  }

  // Login com GitHub
  const signInWithGitHub = async () => {
    const { data, error } = await supabase.auth.signInWithOAuth({
      provider: 'github',
      options: {
        redirectTo: `${window.location.origin}/auth/callback`,
      }
    })
    
    if (error) throw error
    return data
  }

  // Reset de senha
  const resetPassword = async (email: string) => {
    const { data, error } = await supabase.auth.resetPasswordForEmail(email, {
      redirectTo: `${window.location.origin}/auth/reset-password`,
    })
    
    if (error) throw error
    return data
  }

  // Logout
  const signOut = async () => {
    setLoading(true)
    const { error } = await supabase.auth.signOut()
    setLoading(false)
    
    if (error) throw error
  }

  const value = {
    user,
    session,
    loading,
    signIn,
    signUp,
    signOut,
    signInWithGitHub,
    resetPassword,
  }

  return (
    <AuthContext.Provider value={value}>
      {children}
    </AuthContext.Provider>
  )
}

// middleware.ts
import { createMiddlewareClient } from '@supabase/auth-helpers-nextjs'
import { NextResponse } from 'next/server'
import type { NextRequest } from 'next/server'

export async function middleware(req: NextRequest) {
  const res = NextResponse.next()
  const supabase = createMiddlewareClient({ req, res })

  // Verificar sessão
  const {
    data: { session },
  } = await supabase.auth.getSession()

  // Rotas protegidas
  const protectedPaths = ['/dashboard', '/profile', '/admin']
  const isProtectedPath = protectedPaths.some(path => 
    req.nextUrl.pathname.startsWith(path)
  )

  // Redirecionar se não autenticado
  if (isProtectedPath && !session) {
    const redirectUrl = req.nextUrl.clone()
    redirectUrl.pathname = '/auth/login'
    redirectUrl.searchParams.set('redirectedFrom', req.nextUrl.pathname)
    return NextResponse.redirect(redirectUrl)
  }

  // Redirecionar admin se não for admin
  if (req.nextUrl.pathname.startsWith('/admin') && session) {
    const { data: profile } = await supabase
      .from('profiles')
      .select('role')
      .eq('id', session.user.id)
      .single()

    if (profile?.role !== 'admin') {
      return NextResponse.redirect(new URL('/dashboard', req.url))
    }
  }

  return res
}

export const config = {
  matcher: ['/dashboard/:path*', '/profile/:path*', '/admin/:path*']
}

// hooks/useRealtime.ts
'use client'

import { useEffect, useState } from 'react'
import { supabase } from '@/lib/supabase'
import { Database } from '@/lib/supabase'

type Profile = Database['public']['Tables']['profiles']['Row']
type Post = Database['public']['Tables']['posts']['Row']

export function useRealtimeProfiles() {
  const [profiles, setProfiles] = useState<Profile[]>([])
  const [loading, setLoading] = useState(true)

  useEffect(() => {
    // Buscar dados iniciais
    const fetchProfiles = async () => {
      try {
        const { data, error } = await supabase
          .from('profiles')
          .select('*')
          .order('created_at', { ascending: false })
        
        if (error) throw error
        setProfiles(data || [])
      } catch (error) {
        console.error('Erro ao buscar perfis:', error)
      } finally {
        setLoading(false)
      }
    }

    fetchProfiles()

    // Subscription para mudanças em tempo real
    const subscription = supabase
      .channel('profiles-realtime')
      .on(
        'postgres_changes',
        {
          event: '*',
          schema: 'public',
          table: 'profiles'
        },
        (payload) => {
          console.log('Mudança detectada:', payload)
          
          switch (payload.eventType) {
            case 'INSERT':
              setProfiles(prev => [payload.new as Profile, ...prev])
              break
              
            case 'UPDATE':
              setProfiles(prev => 
                prev.map(profile => 
                  profile.id === payload.new.id 
                    ? { ...profile, ...payload.new as Profile }
                    : profile
                )
              )
              break
              
            case 'DELETE':
              setProfiles(prev => 
                prev.filter(profile => profile.id !== payload.old.id)
              )
              break
          }
        }
      )
      .subscribe((status) => {
        console.log('Subscription status:', status)
      })

    return () => {
      subscription.unsubscribe()
    }
  }, [])

  return { profiles, loading }
}

// Hook para posts em tempo real
export function useRealtimePosts() {
  const [posts, setPosts] = useState<Post[]>([])
  const [loading, setLoading] = useState(true)

  useEffect(() => {
    const fetchPosts = async () => {
      try {
        const { data, error } = await supabase
          .from('posts')
          .select(`
            *,
            author:profiles(username, avatar_url)
          `)
          .eq('published', true)
          .order('created_at', { ascending: false })
        
        if (error) throw error
        setPosts(data || [])
      } catch (error) {
        console.error('Erro ao buscar posts:', error)
      } finally {
        setLoading(false)
      }
    }

    fetchPosts()

    // Subscription com filtro para posts publicados
    const subscription = supabase
      .channel('posts-realtime')
      .on(
        'postgres_changes',
        {
          event: '*',
          schema: 'public',
          table: 'posts',
          filter: 'published=eq.true'
        },
        (payload) => {
          switch (payload.eventType) {
            case 'INSERT':
              // Buscar dados completos com join
              fetchPostWithAuthor(payload.new.id)
              break
              
            case 'UPDATE':
              if (payload.new.published) {
                fetchPostWithAuthor(payload.new.id)
              } else {
                setPosts(prev => 
                  prev.filter(post => post.id !== payload.new.id)
                )
              }
              break
              
            case 'DELETE':
              setPosts(prev => 
                prev.filter(post => post.id !== payload.old.id)
              )
              break
          }
        }
      )
      .subscribe()

    const fetchPostWithAuthor = async (postId: string) => {
      const { data, error } = await supabase
        .from('posts')
        .select(`
          *,
          author:profiles(username, avatar_url)
        `)
        .eq('id', postId)
        .single()

      if (data && !error) {
        setPosts(prev => {
          const exists = prev.find(p => p.id === postId)
          if (exists) {
            return prev.map(p => p.id === postId ? data : p)
          } else {
            return [data, ...prev]
          }
        })
      }
    }

    return () => {
      subscription.unsubscribe()
    }
  }, [])

  return { posts, loading }
}

// utils/storage.ts
import { supabase } from '@/lib/supabase'

export interface UploadResult {
  filePath: string
  publicUrl: string
  fileSize: number
}

// Upload de avatar com validação
export async function uploadAvatar(file: File, userId: string): Promise<UploadResult> {
  // Validações
  const maxSize = 5 * 1024 * 1024 // 5MB
  const allowedTypes = ['image/jpeg', 'image/png', 'image/webp']
  
  if (file.size > maxSize) {
    throw new Error('Arquivo muito grande. Máximo 5MB.')
  }
  
  if (!allowedTypes.includes(file.type)) {
    throw new Error('Tipo de arquivo não permitido. Use JPG, PNG ou WebP.')
  }

  // Gerar nome único
  const fileExt = file.name.split('.').pop()
  const fileName = `${userId}-${Date.now()}.${fileExt}`
  const filePath = `avatars/${fileName}`

  // Upload com metadata
  const { data, error } = await supabase.storage
    .from('avatars')
    .upload(filePath, file, {
      cacheControl: '3600',
      upsert: false,
      metadata: {
        userId,
        originalName: file.name,
        uploadedAt: new Date().toISOString(),
      }
    })

  if (error) throw error

  // Obter URL pública
  const { data: { publicUrl } } = supabase.storage
    .from('avatars')
    .getPublicUrl(filePath)

  return {
    filePath: data.path,
    publicUrl,
    fileSize: file.size,
  }
}

// Upload com resize automático
export async function uploadWithResize(
  file: File, 
  bucket: string, 
  maxWidth: number = 800,
  quality: number = 0.8
): Promise<UploadResult> {
  
  // Criar canvas para resize
  const canvas = document.createElement('canvas')
  const ctx = canvas.getContext('2d')
  const img = new Image()
  
  return new Promise((resolve, reject) => {
    img.onload = async () => {
      // Calcular novas dimensões
      const ratio = Math.min(maxWidth / img.width, maxWidth / img.height)
      canvas.width = img.width * ratio
      canvas.height = img.height * ratio
      
      // Desenhar imagem redimensionada
      ctx?.drawImage(img, 0, 0, canvas.width, canvas.height)
      
      // Converter para blob
      canvas.toBlob(async (blob) => {
        if (!blob) {
          reject(new Error('Falha ao processar imagem'))
          return
        }
        
        try {
          // Upload da imagem processada
          const fileExt = file.name.split('.').pop()
          const fileName = `processed-${Date.now()}.${fileExt}`
          const filePath = `${bucket}/${fileName}`
          
          const { data, error } = await supabase.storage
            .from(bucket)
            .upload(filePath, blob)
            
          if (error) throw error
          
          const { data: { publicUrl } } = supabase.storage
            .from(bucket)
            .getPublicUrl(filePath)
            
          resolve({
            filePath: data.path,
            publicUrl,
            fileSize: blob.size,
          })
        } catch (error) {
          reject(error)
        }
      }, file.type, quality)
    }
    
    img.onerror = () => reject(new Error('Falha ao carregar imagem'))
    img.src = URL.createObjectURL(file)
  })
}

// Gerenciar arquivos do usuário
export async function getUserFiles(userId: string, bucket: string) {
  const { data, error } = await supabase.storage
    .from(bucket)
    .list('', {
      limit: 100,
      offset: 0,
      search: userId,
    })

  if (error) throw error
  
  return data?.map(file => ({
    ...file,
    publicUrl: supabase.storage.from(bucket).getPublicUrl(file.name).data.publicUrl,
  })) || []
}

// Deletar arquivo
export async function deleteFile(bucket: string, filePath: string) {
  const { error } = await supabase.storage
    .from(bucket)
    .remove([filePath])

  if (error) throw error
}

// Listar buckets disponíveis
export async function listBuckets() {
  const { data, error } = await supabase.storage.listBuckets()
  
  if (error) throw error
  return data
}

// Criar bucket programaticamente
export async function createBucket(
  name: string, 
  isPublic: boolean = true,
  allowedMimeTypes?: string[]
) {
  const { data, error } = await supabase.storage.createBucket(name, {
    public: isPublic,
    allowedMimeTypes,
    fileSizeLimit: 10 * 1024 * 1024, // 10MB
  })

  if (error) throw error
  return data
}

// supabase/functions/send-notification/index.ts
import { serve } from "https://deno.land/std@0.168.0/http/server.ts"
import { createClient } from 'https://esm.sh/@supabase/supabase-js@2'

const corsHeaders = {
  'Access-Control-Allow-Origin': '*',
  'Access-Control-Allow-Headers': 'authorization, x-client-info, apikey, content-type',
}

interface NotificationRequest {
  userId: string
  title: string
  message: string
  type: 'email' | 'push' | 'sms'
  metadata?: Record<string, any>
}

serve(async (req) => {
  // Handle CORS
  if (req.method === 'OPTIONS') {
    return new Response('ok', { headers: corsHeaders })
  }

  try {
    // Verificar autenticação
    const authHeader = req.headers.get('Authorization')
    if (!authHeader) {
      throw new Error('Token de autorização necessário')
    }

    // Criar cliente Supabase com service role
    const supabase = createClient(
      Deno.env.get('SUPABASE_URL') ?? '',
      Deno.env.get('SUPABASE_SERVICE_ROLE_KEY') ?? ''
    )

    // Verificar usuário
    const { data: { user }, error: authError } = await supabase.auth.getUser(
      authHeader.replace('Bearer ', '')
    )

    if (authError || !user) {
      throw new Error('Token inválido')
    }

    // Parse do body
    const { userId, title, message, type, metadata }: NotificationRequest = 
      await req.json()

    // Validações
    if (!userId || !title || !message || !type) {
      throw new Error('Dados obrigatórios: userId, title, message, type')
    }

    // Buscar dados do usuário
    const { data: profile, error: profileError } = await supabase
      .from('profiles')
      .select('*')
      .eq('id', userId)
      .single()

    if (profileError || !profile) {
      throw new Error('Usuário não encontrado')
    }

    let result: any = {}

    // Processar notificação baseado no tipo
    switch (type) {
      case 'email':
        result = await sendEmail(profile, title, message, metadata)
        break
        
      case 'push':
        result = await sendPushNotification(profile, title, message, metadata)
        break
        
      case 'sms':
        result = await sendSMS(profile, title, message, metadata)
        break
        
      default:
        throw new Error('Tipo de notificação inválido')
    }

    // Salvar log da notificação
    const { error: logError } = await supabase
      .from('notification_logs')
      .insert({
        user_id: userId,
        type,
        title,
        message,
        status: 'sent',
        metadata: {
          ...metadata,
          result,
          sent_at: new Date().toISOString(),
        }
      })

    if (logError) {
      console.error('Erro ao salvar log:', logError)
    }

    return new Response(
      JSON.stringify({ 
        success: true, 
        message: 'Notificação enviada com sucesso',
        result 
      }),
      { 
        headers: { ...corsHeaders, 'Content-Type': 'application/json' },
        status: 200,
      },
    )

  } catch (error) {
    console.error('Erro na function:', error)
    
    return new Response(
      JSON.stringify({ 
        error: error.message || 'Erro interno do servidor' 
      }),
      { 
        headers: { ...corsHeaders, 'Content-Type': 'application/json' },
        status: 400,
      },
    )
  }
})

// Função para enviar email
async function sendEmail(profile: any, title: string, message: string, metadata?: any) {
  const emailData = {
    personalizations: [{
      to: [{ email: profile.email }],
      subject: title,
    }],
    from: { 
      email: Deno.env.get('FROM_EMAIL') || 'noreply@seuapp.com',
      name: 'Seu App'
    },
    content: [{
      type: 'text/html',
      value: `
        <div style="font-family: Arial, sans-serif; max-width: 600px; margin: 0 auto;">
          <h2 style="color: #333;">${title}</h2>
          <p style="color: #666; line-height: 1.6;">${message}</p>
          ${metadata?.actionUrl ? `
            <a href="${metadata.actionUrl}" 
               style="background: #007bff; color: white; padding: 12px 24px; 
                      text-decoration: none; border-radius: 4px; display: inline-block;">
              ${metadata.actionText || 'Ver Detalhes'}
            </a>
          ` : ''}
        </div>
      `
    }],
  }

  const response = await fetch('https://api.sendgrid.com/v3/mail/send', {
    method: 'POST',
    headers: {
      'Authorization': `Bearer ${Deno.env.get('SENDGRID_API_KEY')}`,
      'Content-Type': 'application/json',
    },
    body: JSON.stringify(emailData),
  })

  if (!response.ok) {
    const error = await response.text()
    throw new Error(`Falha ao enviar email: ${error}`)
  }

  return { provider: 'sendgrid', messageId: response.headers.get('x-message-id') }
}

// Função para push notification
async function sendPushNotification(profile: any, title: string, message: string, metadata?: any) {
  // Implementar com FCM, OneSignal, etc.
  const pushData = {
    to: profile.push_token,
    title,
    body: message,
    data: metadata || {},
  }

  // Exemplo com FCM
  const response = await fetch('https://fcm.googleapis.com/fcm/send', {
    method: 'POST',
    headers: {
      'Authorization': `key=${Deno.env.get('FCM_SERVER_KEY')}`,
      'Content-Type': 'application/json',
    },
    body: JSON.stringify(pushData),
  })

  if (!response.ok) {
    throw new Error('Falha ao enviar push notification')
  }

  const result = await response.json()
  return { provider: 'fcm', messageId: result.multicast_id }
}

// Função para SMS
async function sendSMS(profile: any, title: string, message: string, metadata?: any) {
  if (!profile.phone) {
    throw new Error('Usuário não possui telefone cadastrado')
  }

  const smsData = {
    to: profile.phone,
    body: `${title}

${message}`,
  }

  // Exemplo com Twilio
  const response = await fetch(`https://api.twilio.com/2010-04-01/Accounts/${Deno.env.get('TWILIO_ACCOUNT_SID')}/Messages.json`, {
    method: 'POST',
    headers: {
      'Authorization': `Basic ${btoa(`${Deno.env.get('TWILIO_ACCOUNT_SID')}:${Deno.env.get('TWILIO_AUTH_TOKEN')}`)}`,
      'Content-Type': 'application/x-www-form-urlencoded',
    },
    body: new URLSearchParams({
      To: profile.phone,
      From: Deno.env.get('TWILIO_PHONE_NUMBER') || '',
      Body: smsData.body,
    }),
  })

  if (!response.ok) {
    throw new Error('Falha ao enviar SMS')
  }

  const result = await response.json()
  return { provider: 'twilio', messageId: result.sid }
}

// utils/performance.ts
import { supabase } from '@/lib/supabase'

// Pagination eficiente com cursor
export async function getPaginatedData<T>(
  table: string,
  options: {
    cursor?: string
    limit?: number
    orderBy?: string
    ascending?: boolean
    select?: string
    filters?: Record<string, any>
  } = {}
) {
  const {
    cursor,
    limit = 20,
    orderBy = 'created_at',
    ascending = false,
    select = '*',
    filters = {}
  } = options

  let query = supabase
    .from(table)
    .select(select, { count: 'exact' })
    .order(orderBy, { ascending })
    .limit(limit)

  // Aplicar filtros
  Object.entries(filters).forEach(([key, value]) => {
    if (value !== undefined && value !== null) {
      query = query.eq(key, value)
    }
  })

  // Cursor pagination
  if (cursor) {
    query = ascending 
      ? query.gt(orderBy, cursor)
      : query.lt(orderBy, cursor)
  }

  const { data, error, count } = await query

  if (error) throw error

  const nextCursor = data && data.length > 0 
    ? data[data.length - 1][orderBy]
    : null

  return {
    data: data || [],
    nextCursor,
    hasMore: data ? data.length === limit : false,
    total: count || 0,
  }
}

// Query com cache Redis
export async function getCachedQuery<T>(
  cacheKey: string,
  queryFn: () => Promise<T>,
  ttlSeconds: number = 300
): Promise<T> {
  
  // Tentar buscar do cache primeiro
  try {
    const cached = localStorage.getItem(cacheKey)
    if (cached) {
      const { data, timestamp } = JSON.parse(cached)
      const isValid = Date.now() - timestamp < ttlSeconds * 1000
      
      if (isValid) {
        return data
      }
    }
  } catch (error) {
    console.warn('Erro ao ler cache:', error)
  }

  // Executar query se não está em cache
  const result = await queryFn()

  // Salvar no cache
  try {
    localStorage.setItem(cacheKey, JSON.stringify({
      data: result,
      timestamp: Date.now(),
    }))
  } catch (error) {
    console.warn('Erro ao salvar cache:', error)
  }

  return result
}

// Batch operations para múltiplas queries
export async function batchQueries(operations: Promise<any>[]) {
  const results = await Promise.allSettled(operations)
  
  const successful = results
    .filter((result): result is PromiseFulfilledResult<any> => 
      result.status === 'fulfilled'
    )
    .map(result => result.value)

  const errors = results
    .filter((result): result is PromiseRejectedResult => 
      result.status === 'rejected'
    )
    .map(result => result.reason)

  return { successful, errors }
}

// Connection singleton otimizada
class SupabaseManager {
  private static instance: SupabaseManager
  private connectionPool: Map<string, any> = new Map()

  static getInstance(): SupabaseManager {
    if (!SupabaseManager.instance) {
      SupabaseManager.instance = new SupabaseManager()
    }
    return SupabaseManager.instance
  }

  getConnection(config?: any) {
    const key = JSON.stringify(config || 'default')
    
    if (!this.connectionPool.has(key)) {
      const client = createClient(
        process.env.NEXT_PUBLIC_SUPABASE_URL!,
        process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
        {
          auth: {
            persistSession: true,
            autoRefreshToken: true,
          },
          db: {
            schema: 'public',
          },
          ...config,
        }
      )
      
      this.connectionPool.set(key, client)
    }
    
    return this.connectionPool.get(key)
  }

  clearConnections() {
    this.connectionPool.clear()
  }
}

export const supabaseManager = SupabaseManager.getInstance()

// migrations/20240120_001_create_users_system.sql
-- Migration: Sistema de usuários avançado
-- Autor: DevTeam
-- Data: 2024-01-20

BEGIN;

-- Extensões necessárias
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- Enum para roles de usuário
CREATE TYPE user_role AS ENUM ('user', 'moderator', 'admin', 'super_admin');

-- Enum para status de conta
CREATE TYPE account_status AS ENUM ('active', 'inactive', 'suspended', 'pending_verification');

-- Tabela de organizações
CREATE TABLE organizations (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  name TEXT NOT NULL,
  slug TEXT UNIQUE NOT NULL,
  description TEXT,
  logo_url TEXT,
  website TEXT,
  subscription_plan TEXT DEFAULT 'free' CHECK (subscription_plan IN ('free', 'pro', 'enterprise')),
  max_users INTEGER DEFAULT 5,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Índices para organizações
CREATE INDEX idx_organizations_slug ON organizations(slug);
CREATE INDEX idx_organizations_plan ON organizations(subscription_plan);

-- Tabela de perfis expandida
ALTER TABLE profiles ADD COLUMN IF NOT EXISTS organization_id UUID REFERENCES organizations(id);
ALTER TABLE profiles ADD COLUMN IF NOT EXISTS role user_role DEFAULT 'user';
ALTER TABLE profiles ADD COLUMN IF NOT EXISTS status account_status DEFAULT 'pending_verification';
ALTER TABLE profiles ADD COLUMN IF NOT EXISTS last_login_at TIMESTAMP WITH TIME ZONE;
ALTER TABLE profiles ADD COLUMN IF NOT EXISTS email_verified BOOLEAN DEFAULT false;
ALTER TABLE profiles ADD COLUMN IF NOT EXISTS phone TEXT;
ALTER TABLE profiles ADD COLUMN IF NOT EXISTS timezone TEXT DEFAULT 'UTC';
ALTER TABLE profiles ADD COLUMN IF NOT EXISTS preferences JSONB DEFAULT '{}';

-- Índices para profiles
CREATE INDEX idx_profiles_organization ON profiles(organization_id);
CREATE INDEX idx_profiles_role ON profiles(role);
CREATE INDEX idx_profiles_status ON profiles(status);
CREATE INDEX idx_profiles_email_verified ON profiles(email_verified);

-- Tabela de convites
CREATE TABLE invitations (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  email TEXT NOT NULL,
  role user_role DEFAULT 'user',
  invited_by UUID REFERENCES auth.users(id),
  token TEXT UNIQUE NOT NULL DEFAULT encode(gen_random_bytes(32), 'hex'),
  expires_at TIMESTAMP WITH TIME ZONE DEFAULT (NOW() + INTERVAL '7 days'),
  accepted_at TIMESTAMP WITH TIME ZONE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Índices para invitations
CREATE INDEX idx_invitations_token ON invitations(token);
CREATE INDEX idx_invitations_email ON invitations(email);
CREATE INDEX idx_invitations_org ON invitations(organization_id);

-- Tabela de auditoria
CREATE TABLE audit_logs (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  user_id UUID REFERENCES auth.users(id),
  organization_id UUID REFERENCES organizations(id),
  action TEXT NOT NULL,
  resource_type TEXT NOT NULL,
  resource_id TEXT,
  metadata JSONB DEFAULT '{}',
  ip_address INET,
  user_agent TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Índices para audit_logs
CREATE INDEX idx_audit_logs_user ON audit_logs(user_id);
CREATE INDEX idx_audit_logs_org ON audit_logs(organization_id);
CREATE INDEX idx_audit_logs_action ON audit_logs(action);
CREATE INDEX idx_audit_logs_created_at ON audit_logs(created_at);

-- RLS Policies

-- Organizations
ALTER TABLE organizations ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Usuários podem ver própria organização"
ON organizations FOR SELECT
USING (
  id IN (
    SELECT organization_id 
    FROM profiles 
    WHERE id = auth.uid()
  )
);

CREATE POLICY "Admins podem atualizar organização"
ON organizations FOR UPDATE
USING (
  id IN (
    SELECT organization_id 
    FROM profiles 
    WHERE id = auth.uid() 
    AND role IN ('admin', 'super_admin')
  )
);

-- Invitations
ALTER TABLE invitations ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Admins podem gerenciar convites"
ON invitations FOR ALL
USING (
  organization_id IN (
    SELECT organization_id 
    FROM profiles 
    WHERE id = auth.uid() 
    AND role IN ('admin', 'super_admin')
  )
);

CREATE POLICY "Usuários podem ver próprios convites"
ON invitations FOR SELECT
USING (email = auth.email());

-- Audit Logs
ALTER TABLE audit_logs ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Usuários podem ver próprios logs"
ON audit_logs FOR SELECT
USING (user_id = auth.uid());

CREATE POLICY "Admins podem ver logs da organização"
ON audit_logs FOR SELECT
USING (
  organization_id IN (
    SELECT organization_id 
    FROM profiles 
    WHERE id = auth.uid() 
    AND role IN ('admin', 'super_admin')
  )
);

-- Funções auxiliares

-- Função para atualizar updated_at
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Triggers para updated_at
CREATE TRIGGER update_organizations_updated_at 
BEFORE UPDATE ON organizations 
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Função para criar organização padrão
CREATE OR REPLACE FUNCTION create_default_organization_for_user()
RETURNS TRIGGER AS $$
DECLARE
    org_id UUID;
BEGIN
    -- Criar organização pessoal
    INSERT INTO organizations (name, slug, description)
    VALUES (
        COALESCE(NEW.full_name, 'Organização Pessoal'),
        LOWER(REPLACE(COALESCE(NEW.username, NEW.id::text), ' ', '-')),
        'Organização pessoal criada automaticamente'
    )
    RETURNING id INTO org_id;
    
    -- Atualizar perfil com organização
    NEW.organization_id = org_id;
    NEW.role = 'admin';
    
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Trigger para criar organização automática
CREATE TRIGGER create_org_for_new_profile
BEFORE INSERT ON profiles
FOR EACH ROW 
WHEN (NEW.organization_id IS NULL)
EXECUTE FUNCTION create_default_organization_for_user();

-- Função para log de auditoria
CREATE OR REPLACE FUNCTION log_user_action(
    p_action TEXT,
    p_resource_type TEXT,
    p_resource_id TEXT DEFAULT NULL,
    p_metadata JSONB DEFAULT '{}'
)
RETURNS UUID AS $$
DECLARE
    log_id UUID;
    user_org_id UUID;
BEGIN
    -- Buscar organização do usuário
    SELECT organization_id INTO user_org_id
    FROM profiles 
    WHERE id = auth.uid();
    
    -- Inserir log
    INSERT INTO audit_logs (
        user_id, 
        organization_id, 
        action, 
        resource_type, 
        resource_id, 
        metadata
    )
    VALUES (
        auth.uid(), 
        user_org_id, 
        p_action, 
        p_resource_type, 
        p_resource_id, 
        p_metadata
    )
    RETURNING id INTO log_id;
    
    RETURN log_id;
END;
$$ language 'plpgsql' SECURITY DEFINER;

-- Seeds de dados iniciais
INSERT INTO organizations (id, name, slug, description, subscription_plan) VALUES
('00000000-0000-0000-0000-000000000001', 'CrazyStack', 'crazystack', 'Organização principal da CrazyStack', 'enterprise');

COMMIT;

// scripts/migrate.ts
import { createClient } from '@supabase/supabase-js'
import fs from 'fs/promises'
import path from 'path'

const supabase = createClient(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!
)

interface Migration {
  id: string
  filename: string
  sql: string
  applied_at?: string
}

// Tabela para controle de migrations
const MIGRATIONS_TABLE = `
CREATE TABLE IF NOT EXISTS _migrations (
  id TEXT PRIMARY KEY,
  filename TEXT NOT NULL,
  applied_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
`

async function initMigrationsTable() {
  const { error } = await supabase.rpc('exec_sql', { 
    sql_query: MIGRATIONS_TABLE 
  })
  
  if (error) {
    console.error('Erro ao criar tabela de migrations:', error)
    throw error
  }
}

async function getAppliedMigrations(): Promise<string[]> {
  const { data, error } = await supabase
    .from('_migrations')
    .select('id')
    .order('applied_at')
  
  if (error) throw error
  return data?.map(m => m.id) || []
}

async function getMigrationFiles(): Promise<Migration[]> {
  const migrationsDir = path.join(process.cwd(), 'migrations')
  const files = await fs.readdir(migrationsDir)
  
  const migrations: Migration[] = []
  
  for (const file of files.filter(f => f.endsWith('.sql'))) {
    const id = file.replace('.sql', '')
    const filepath = path.join(migrationsDir, file)
    const sql = await fs.readFile(filepath, 'utf8')
    
    migrations.push({ id, filename: file, sql })
  }
  
  return migrations.sort((a, b) => a.id.localeCompare(b.id))
}

async function runMigration(migration: Migration): Promise<void> {
  console.log(`📦 Aplicando migration: ${migration.filename}`)
  
  try {
    // Executar SQL da migration
    const { error: sqlError } = await supabase.rpc('exec_sql', {
      sql_query: migration.sql
    })
    
    if (sqlError) throw sqlError
    
    // Registrar migration como aplicada
    const { error: recordError } = await supabase
      .from('_migrations')
      .insert({
        id: migration.id,
        filename: migration.filename
      })
    
    if (recordError) throw recordError
    
    console.log(`✅ Migration aplicada: ${migration.filename}`)
    
  } catch (error) {
    console.error(`❌ Erro na migration ${migration.filename}:`, error)
    throw error
  }
}

async function runMigrations() {
  try {
    console.log('🚀 Iniciando migrations...')
    
    // Inicializar tabela de controle
    await initMigrationsTable()
    
    // Buscar migrations aplicadas e disponíveis
    const [appliedMigrations, availableMigrations] = await Promise.all([
      getAppliedMigrations(),
      getMigrationFiles()
    ])
    
    // Filtrar migrations pendentes
    const pendingMigrations = availableMigrations.filter(
      migration => !appliedMigrations.includes(migration.id)
    )
    
    if (pendingMigrations.length === 0) {
      console.log('✨ Nenhuma migration pendente')
      return
    }
    
    console.log(`📋 ${pendingMigrations.length} migrations pendentes`)
    
    // Aplicar migrations em ordem
    for (const migration of pendingMigrations) {
      await runMigration(migration)
    }
    
    console.log('🎉 Todas as migrations foram aplicadas com sucesso!')
    
  } catch (error) {
    console.error('💥 Erro ao executar migrations:', error)
    process.exit(1)
  }
}

// Executar se chamado diretamente
if (require.main === module) {
  runMigrations()
}

// lib/api-client.ts
import { supabase } from './supabase'
import { Database } from './database.types'

type Tables = Database['public']['Tables']
type Profile = Tables['profiles']['Row']
type Post = Tables['posts']['Row']
type Organization = Tables['organizations']['Row']

// Cliente API tipado
export class SupabaseAPI {
  
  // Métodos para Profiles
  static async getProfile(userId: string): Promise<Profile | null> {
    const { data, error } = await supabase
      .from('profiles')
      .select(`
        *,
        organization:organizations(*)
      `)
      .eq('id', userId)
      .single()
    
    if (error) throw new APIError('Erro ao buscar perfil', error)
    return data
  }

  static async updateProfile(
    userId: string, 
    updates: Partial<Profile>
  ): Promise<Profile> {
    const { data, error } = await supabase
      .from('profiles')
      .update(updates)
      .eq('id', userId)
      .select()
      .single()
    
    if (error) throw new APIError('Erro ao atualizar perfil', error)
    
    // Log da ação
    await this.logAction('update', 'profile', userId, { updates })
    
    return data
  }

  // Métodos para Posts com filtros avançados
  static async getPosts(options: {
    authorId?: string
    organizationId?: string
    published?: boolean
    search?: string
    tags?: string[]
    page?: number
    limit?: number
    sortBy?: 'created_at' | 'updated_at' | 'title'
    sortOrder?: 'asc' | 'desc'
  } = {}): Promise<{
    posts: Post[]
    total: number
    page: number
    totalPages: number
  }> {
    const {
      authorId,
      organizationId,
      published = true,
      search,
      tags,
      page = 1,
      limit = 20,
      sortBy = 'created_at',
      sortOrder = 'desc'
    } = options

    let query = supabase
      .from('posts')
      .select(`
        *,
        author:profiles!author_id(username, full_name, avatar_url),
        organization:organizations!organization_id(name, slug)
      `, { count: 'exact' })

    // Aplicar filtros
    if (authorId) query = query.eq('author_id', authorId)
    if (organizationId) query = query.eq('organization_id', organizationId)
    if (published !== undefined) query = query.eq('published', published)
    
    // Busca por texto
    if (search) {
      query = query.or(`title.ilike.%${search}%,content.ilike.%${search}%`)
    }
    
    // Filtro por tags (assumindo coluna tags JSONB)
    if (tags && tags.length > 0) {
      const tagFilters = tags.map(tag => `tags.cs.[""${tag}""]`).join(',')
      query = query.or(tagFilters)
    }

    // Ordenação e paginação
    const offset = (page - 1) * limit
    query = query
      .order(sortBy, { ascending: sortOrder === 'asc' })
      .range(offset, offset + limit - 1)

    const { data, error, count } = await query

    if (error) throw new APIError('Erro ao buscar posts', error)

    return {
      posts: data || [],
      total: count || 0,
      page,
      totalPages: Math.ceil((count || 0) / limit)
    }
  }

  static async createPost(postData: {
    title: string
    content: string
    published?: boolean
    tags?: string[]
    metadata?: Record<string, any>
  }): Promise<Post> {
    const { data: userProfile } = await supabase
      .from('profiles')
      .select('organization_id')
      .eq('id', (await supabase.auth.getUser()).data.user?.id!)
      .single()

    const { data, error } = await supabase
      .from('posts')
      .insert({
        ...postData,
        author_id: (await supabase.auth.getUser()).data.user?.id!,
        organization_id: userProfile?.organization_id,
      })
      .select()
      .single()

    if (error) throw new APIError('Erro ao criar post', error)
    
    await this.logAction('create', 'post', data.id, postData)
    
    return data
  }

  // Métodos para Organizations
  static async getOrganizationMembers(orgId: string): Promise<Profile[]> {
    const { data, error } = await supabase
      .from('profiles')
      .select(`
        *,
        user:auth.users!id(email, created_at)
      `)
      .eq('organization_id', orgId)
      .order('role', { ascending: false })
      .order('created_at', { ascending: true })

    if (error) throw new APIError('Erro ao buscar membros', error)
    return data || []
  }

  static async inviteUserToOrganization(
    email: string,
    role: 'user' | 'moderator' | 'admin' = 'user'
  ): Promise<void> {
    const user = await supabase.auth.getUser()
    if (!user.data.user) throw new Error('Usuário não autenticado')

    const { data: profile } = await supabase
      .from('profiles')
      .select('organization_id, role')
      .eq('id', user.data.user.id)
      .single()

    if (!profile || !['admin', 'super_admin'].includes(profile.role!)) {
      throw new Error('Permissão insuficiente')
    }

    const { error } = await supabase
      .from('invitations')
      .insert({
        organization_id: profile.organization_id,
        email,
        role,
        invited_by: user.data.user.id
      })

    if (error) throw new APIError('Erro ao criar convite', error)
    
    await this.logAction('invite', 'user', email, { role })
  }

  // Métodos para Analytics
  static async getOrganizationStats(orgId: string): Promise<{
    totalMembers: number
    totalPosts: number
    publishedPosts: number
    draftPosts: number
    recentActivity: any[]
  }> {
    const [membersResult, postsResult, activityResult] = await Promise.all([
      supabase
        .from('profiles')
        .select('id', { count: 'exact', head: true })
        .eq('organization_id', orgId),
      
      supabase
        .from('posts')
        .select('published', { count: 'exact' })
        .eq('organization_id', orgId),
      
      supabase
        .from('audit_logs')
        .select(`
          action,
          resource_type,
          created_at,
          user:profiles!user_id(username, full_name)
        `)
        .eq('organization_id', orgId)
        .order('created_at', { ascending: false })
        .limit(10)
    ])

    const totalMembers = membersResult.count || 0
    const allPosts = postsResult.data || []
    const publishedPosts = allPosts.filter(p => p.published).length
    const draftPosts = allPosts.length - publishedPosts

    return {
      totalMembers,
      totalPosts: allPosts.length,
      publishedPosts,
      draftPosts,
      recentActivity: activityResult.data || []
    }
  }

  // Método para logging de ações
  private static async logAction(
    action: string,
    resourceType: string,
    resourceId: string,
    metadata: any = {}
  ): Promise<void> {
    try {
      await supabase.rpc('log_user_action', {
        p_action: action,
        p_resource_type: resourceType,
        p_resource_id: resourceId,
        p_metadata: metadata
      })
    } catch (error) {
      console.warn('Erro ao registrar log:', error)
    }
  }

  // Busca universal com full-text search
  static async searchAll(query: string, filters?: {
    types?: ('posts' | 'profiles' | 'organizations')[]
    organizationId?: string
  }): Promise<{
    posts: Post[]
    profiles: Profile[]
    organizations: Organization[]
  }> {
    const searchTypes = filters?.types || ['posts', 'profiles', 'organizations']
    const results: any = { posts: [], profiles: [], organizations: [] }

    const searches = []

    if (searchTypes.includes('posts')) {
      searches.push(
        supabase
          .from('posts')
          .select('*')
          .textSearch('title', query)
          .eq('published', true)
          .limit(10)
      )
    }

    if (searchTypes.includes('profiles')) {
      searches.push(
        supabase
          .from('profiles')
          .select('*')
          .or(`full_name.ilike.%${query}%,username.ilike.%${query}%`)
          .limit(10)
      )
    }

    if (searchTypes.includes('organizations')) {
      searches.push(
        supabase
          .from('organizations')
          .select('*')
          .or(`name.ilike.%${query}%,description.ilike.%${query}%`)
          .limit(10)
      )
    }

    const searchResults = await Promise.allSettled(searches)
    
    searchTypes.forEach((type, index) => {
      const result = searchResults[index]
      if (result.status === 'fulfilled' && result.value.data) {
        results[type] = result.value.data
      }
    })

    return results
  }
}

// Classe de erro customizada
export class APIError extends Error {
  constructor(message: string, public originalError?: any) {
    super(message)
    this.name = 'APIError'
  }
}

// Hooks React para usar a API
export function useProfile(userId?: string) {
  const [profile, setProfile] = useState<Profile | null>(null)
  const [loading, setLoading] = useState(true)
  const [error, setError] = useState<string | null>(null)

  useEffect(() => {
    if (!userId) return

    const fetchProfile = async () => {
      try {
        setLoading(true)
        const data = await SupabaseAPI.getProfile(userId)
        setProfile(data)
      } catch (err) {
        setError(err instanceof Error ? err.message : 'Erro desconhecido')
      } finally {
        setLoading(false)
      }
    }

    fetchProfile()
  }, [userId])

  return { profile, loading, error }
}