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

export async function createClient() {
  const cookieStore = await cookies()
  
  return createServerClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
    {
      cookies: {
        getAll() {
          return cookieStore.getAll()
        },
        setAll(cookiesToSet) {
          try {
            cookiesToSet.forEach(({ name, value, options }) =>
              cookieStore.set(name, value, options)
            )
          } catch {
            // Handle Server Component context
          }
        },
      },
    }
  )
}

// lib/services/inventory.ts
import { createClient } from '@/lib/supabase/server'

export interface InventoryStats {
  totalProducts: number
  totalValue: number
  lowStockItems: number
  outOfStockItems: number
}

export async function getInventoryStats(): Promise<InventoryStats> {
  const supabase = await createClient()

  try {
    // Get products with inventory data
    const { data: products } = await supabase
      .from('products')
      .select(`
        id,
        cost_price,
        is_active,
        inventory!inner(
          quantity_on_hand,
          reorder_point
        )
      `)
      .eq('is_active', true)

    if (!products) return getDefaultStats()

    const stats = products.reduce((acc, product) => {
      const inventory = product.inventory[0]
      const value = inventory.quantity_on_hand * (product.cost_price || 0)
      
      acc.totalProducts += 1
      acc.totalValue += value
      
      if (inventory.quantity_on_hand === 0) {
        acc.outOfStockItems += 1
      } else if (inventory.quantity_on_hand <= inventory.reorder_point) {
        acc.lowStockItems += 1
      }
      
      return acc
    }, getDefaultStats())

    return stats
  } catch (error) {
    console.error('Error fetching inventory stats:', error)
    return getDefaultStats()
  }
}

function getDefaultStats(): InventoryStats {
  return {
    totalProducts: 0,
    totalValue: 0,
    lowStockItems: 0,
    outOfStockItems: 0,
  }
}

export async function getProductsWithInventory() {
  const supabase = await createClient()
  
  const { data, error } = await supabase
    .from('products')
    .select(`
      id,
      name,
      sku,
      cost_price,
      selling_price,
      categories!inner(
        id,
        name
      ),
      inventory!left(
        quantity_on_hand,
        reorder_point,
        warehouses!inner(
          id,
          name,
          code
        )
      )
    `)
    .eq('is_active', true)
    .order('name')

  return { data, error }
}

export async function getInventoryAnalytics() {
  const supabase = await createClient()
  
  const { data, error } = await supabase
    .rpc('get_inventory_analytics', {
      start_date: '2024-01-01',
      end_date: '2024-12-31'
    })

  return { data, error }
}

export async function updateMultipleProducts(updates: ProductUpdate[]) {
  const supabase = await createClient()
  
  const results = await Promise.all(
    updates.map(update =>
      supabase
        .from('products')
        .update(update.data)
        .eq('id', update.id)
        .select()
        .single()
    )
  )
  
  return results
}

// Use select() to limit columns
// Use limit() and offset() for pagination
// Use proper indexes on filtered columns
export async function getProductsOptimized(limit = 10, offset = 0) {
  const supabase = await createClient()
  
  const { data, error } = await supabase
    .from('products')
    .select('id, name, sku, price, stock_quantity')
    .eq('is_active', true)
    .order('created_at', { ascending: false })
    .range(offset, offset + limit - 1)

  return { data, error }
}

// Batch operations instead of individual queries
export async function updateProductsPrices(updates: Array<{id: string, price: number}>) {
  const supabase = await createClient()
  
  const { data, error } = await supabase
    .from('products')
    .upsert(updates)
    .select()

  return { data, error }
}

try {
  const { data, error } = await supabase.from('products').select('*')
  if (error) throw error
  return data
} catch (error) {
  console.error('Database error:', error)
  return [] // Return empty array as fallback
}

export interface Product {
  id: string
  name: string
  sku: string
  cost_price: number
  selling_price: number
  is_active: boolean
  created_at: string
  updated_at: string
}