import { useState, useEffect, useCallback, useMemo } from 'react'
import { getSupabase } from '../lib/supabase'

/**
 * Columns the platform manages automatically (DB defaults: gen_random_uuid(),
 * now(), auth.uid()). They must NEVER be sent in insert/update payloads —
 * doing so causes type errors (e.g. created_at: '') or RLS conflicts. We strip
 * them defensively so generated apps work regardless of what they include.
 */
const MANAGED_COLUMNS = ['id', 'created_at', 'user_id']

function stripManaged<R extends Record<string, unknown>>(row: R): Partial<R> {
  const clean: Record<string, unknown> = { ...row }
  for (const k of MANAGED_COLUMNS) delete clean[k]
  return clean as Partial<R>
}

export interface UseSupabaseTableOptions {
  /** Whether to run the initial fetch (default: true). */
  enabled?: boolean
  /** Column to order by. */
  orderBy?: string
  /** Order ascending (default: true). */
  ascending?: boolean
  /** Maximum number of rows to fetch. */
  limit?: number
}

export interface UseSupabaseTableReturn<T> {
  /** Array of rows (each with its id). */
  data: (T & { id: string })[]
  /** Loading state. */
  loading: boolean
  /** Error message if any. */
  error: string | null
  /** Insert a new row; returns the new id or null on failure. */
  add: (data: Omit<T, 'id' | 'created_at' | 'user_id'>) => Promise<string | null>
  /** Update a row by id; returns success boolean. */
  update: (id: string, data: Partial<T>) => Promise<boolean>
  /** Delete a row by id; returns success boolean. */
  remove: (id: string) => Promise<boolean>
  /** Re-fetch the rows. */
  refresh: () => void
}

/**
 * Schema-scoped CRUD over a Supabase table. Fetch-based (not realtime):
 * an initial select() on mount, then optimistic local updates on add/update/
 * remove. Every call is wrapped — errors set `error` and never throw, so a
 * missing table or permission error leaves data empty instead of crashing.
 */
export function useSupabaseTable<T extends Record<string, any>>(
  table: string,
  options: UseSupabaseTableOptions = {}
): UseSupabaseTableReturn<T> {
  const [data, setData] = useState<(T & { id: string })[]>([])
  const [loading, setLoading] = useState(true)
  const [error, setError] = useState<string | null>(null)
  const [refreshKey, setRefreshKey] = useState(0)

  const enabled = options.enabled ?? true
  const { orderBy, ascending, limit } = options

  useEffect(() => {
    if (!enabled || !table) {
      setLoading(false)
      setData([])
      return
    }

    const client = getSupabase()
    if (!client) {
      setLoading(false)
      setData([])
      setError('Supabase not initialized. Make sure to call initSupabase() first.')
      return
    }

    let cancelled = false
    setLoading(true)
    setError(null)

    const run = async () => {
      try {
        let q: any = client.from(table).select()
        if (orderBy) q = q.order(orderBy, { ascending: ascending ?? true })
        if (typeof limit === 'number') q = q.limit(limit)
        const { data: rows, error: err } = await q
        if (cancelled) return
        if (err) {
          setError(err.message || 'Failed to load data')
          setData([])
        } else {
          setData((rows as (T & { id: string })[]) || [])
        }
      } catch (e) {
        if (cancelled) return
        setError(e instanceof Error ? e.message : 'Failed to load data')
        setData([])
      } finally {
        if (!cancelled) setLoading(false)
      }
    }

    run()
    return () => {
      cancelled = true
    }
  }, [table, enabled, orderBy, ascending, limit, refreshKey])

  const add = useCallback(
    async (row: Omit<T, 'id' | 'created_at' | 'user_id'>): Promise<string | null> => {
      const client = getSupabase()
      if (!client) {
        setError('Supabase not initialized')
        return null
      }
      try {
        const { data: inserted, error: err } = await client.from(table).insert(stripManaged(row as any)).select()
        if (err) {
          setError(err.message || 'Failed to add row')
          return null
        }
        const newRow = (inserted && inserted[0]) as (T & { id: string }) | undefined
        if (newRow) {
          setData((prev) => [...prev, newRow])
          return String(newRow.id)
        }
        return null
      } catch (e) {
        setError(e instanceof Error ? e.message : 'Failed to add row')
        return null
      }
    },
    [table]
  )

  const update = useCallback(
    async (id: string, patch: Partial<T>): Promise<boolean> => {
      const client = getSupabase()
      if (!client) {
        setError('Supabase not initialized')
        return false
      }
      try {
        const { error: err } = await client.from(table).update(stripManaged(patch as any)).eq('id', id)
        if (err) {
          setError(err.message || 'Failed to update row')
          return false
        }
        setData((prev) => prev.map((r) => (String(r.id) === String(id) ? { ...r, ...patch } : r)))
        return true
      } catch (e) {
        setError(e instanceof Error ? e.message : 'Failed to update row')
        return false
      }
    },
    [table]
  )

  const remove = useCallback(
    async (id: string): Promise<boolean> => {
      const client = getSupabase()
      if (!client) {
        setError('Supabase not initialized')
        return false
      }
      try {
        const { error: err } = await client.from(table).delete().eq('id', id)
        if (err) {
          setError(err.message || 'Failed to delete row')
          return false
        }
        setData((prev) => prev.filter((r) => String(r.id) !== String(id)))
        return true
      } catch (e) {
        setError(e instanceof Error ? e.message : 'Failed to delete row')
        return false
      }
    },
    [table]
  )

  const refresh = useCallback(() => {
    setRefreshKey((prev) => prev + 1)
  }, [])

  return useMemo(
    () => ({ data, loading, error, add, update, remove, refresh }),
    [data, loading, error, add, update, remove, refresh]
  )
}