import { createClient } from '@supabase/supabase-js'
import type { Database } from './database.types'

const supabase = createClient<Database>(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_ANON_KEY!
)

// READ: Get single post by ID
export async function getPost(id: string) {
  const { data, error } = await supabase
    .from('posts')
    .select(`
      *,
      author:profiles!author_id(*),
      comments(count)
    `)
    .eq('id', id)
    .single()

  if (error) {
    throw new Error(`Failed to fetch post: ${error.message}`)
  }

  return data
}

// READ: List posts with pagination
export async function listPosts(options: {
  page?: number
  perPage?: number
  published?: boolean
}) {
  const { page = 1, perPage = 20, published } = options
  const start = (page - 1) * perPage
  const end = start + perPage - 1

  let query = supabase
    .from('posts')
    .select('*, author:profiles!author_id(username)', { count: 'exact' })

  if (published !== undefined) {
    query = query.eq('published', published)
  }

  const { data, error, count } = await query
    .order('created_at', { ascending: false })
    .range(start, end)

  if (error) {
    throw new Error(`Failed to list posts: ${error.message}`)
  }

  return {
    data,
    pagination: {
      page,
      perPage,
      total: count,
      totalPages: Math.ceil((count || 0) / perPage)
    }
  }
}

// CREATE: Insert new post
export async function createPost(input: {
  title: string
  content: string
  authorId: string
  published?: boolean
}) {
  const { data, error } = await supabase
    .from('posts')
    .insert({
      title: input.title,
      content: input.content,
      author_id: input.authorId,
      published: input.published ?? false,
      slug: generateSlug(input.title)
    })
    .select()
    .single()

  if (error) {
    if (error.code === '23505') {
      throw new Error('A post with this title already exists')
    }
    throw new Error(`Failed to create post: ${error.message}`)
  }

  return data
}

// UPDATE: Update existing post
export async function updatePost(
  id: string,
  updates: {
    title?: string
    content?: string
    published?: boolean
  }
) {
  const { data, error } = await supabase
    .from('posts')
    .update(updates)
    .eq('id', id)
    .select()
    .single()

  if (error) {
    if (error.code === 'PGRST116') {
      throw new Error('Post not found or access denied')
    }
    throw new Error(`Failed to update post: ${error.message}`)
  }

  return data
}

// DELETE: Remove post
export async function deletePost(id: string) {
  const { error } = await supabase
    .from('posts')
    .delete()
    .eq('id', id)

  if (error) {
    if (error.code === 'PGRST116') {
      throw new Error('Post not found or access denied')
    }
    throw new Error(`Failed to delete post: ${error.message}`)
  }

  return { success: true }
}

function generateSlug(title: string): string {
  return title
    .toLowerCase()
    .replace(/[^a-z0-9]+/g, '-')
    .replace(/^-|-$/g, '')
}