-- Drop existing functions first
    DROP FUNCTION IF EXISTS public.get_tables(text);
    DROP FUNCTION IF EXISTS public.get_columns(text);
    DROP FUNCTION IF EXISTS public.get_table_data(text);
    DROP FUNCTION IF EXISTS public.enum_values(text);
    DROP FUNCTION IF EXISTS public.create_table(text, jsonb);

    -- Create enum_values function with explicit parameter naming and type
    CREATE OR REPLACE FUNCTION public.enum_values(p_enum_name text)
    RETURNS TABLE (enumlabel text)
    LANGUAGE sql
    SECURITY DEFINER
    SET search_path = public
    AS $$
        SELECT e.enumlabel
        FROM pg_type t
        JOIN pg_enum e ON t.oid = e.enumtypid
        JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
        WHERE t.typname = p_enum_name
        ORDER BY e.enumsortorder;
    $$;

    -- Create get_tables function
    CREATE OR REPLACE FUNCTION public.get_tables(p_schema_name text DEFAULT 'public')
    RETURNS TABLE (tablename text) 
    LANGUAGE plpgsql
    SECURITY DEFINER
    SET search_path = public
    AS $get_tables$
    BEGIN
        RETURN QUERY
        SELECT t.table_name::text
        FROM information_schema.tables t
        WHERE t.table_schema = p_schema_name
        AND t.table_type = 'BASE TABLE'
        AND t.table_schema NOT IN ('pg_catalog', 'information_schema')
        ORDER BY t.table_name;
    END;
    $get_tables$;

    -- Create get_columns function
    CREATE OR REPLACE FUNCTION public.get_columns(p_full_table_name text)
    RETURNS TABLE (column_name text, data_type text, is_nullable text)
    LANGUAGE plpgsql
    SECURITY DEFINER
    SET search_path = public
    AS $get_columns$
    DECLARE
        v_schema_name text;
        v_table_name text;
    BEGIN
        v_schema_name := split_part(p_full_table_name, '.', 1);
        v_table_name := split_part(p_full_table_name, '.', 2);
        
        IF v_table_name = '' THEN
            v_table_name := v_schema_name;
            v_schema_name := 'public';
        END IF;

        RETURN QUERY
        SELECT 
            c.column_name::text,
            c.data_type::text,
            c.is_nullable::text
        FROM information_schema.columns c
        WHERE c.table_schema = v_schema_name 
        AND c.table_name = v_table_name
        ORDER BY c.ordinal_position;
    END;
    $get_columns$;

    -- Create get_table_data function with dynamic ordering
    CREATE OR REPLACE FUNCTION public.get_table_data(p_full_table_name text)
    RETURNS SETOF json
    LANGUAGE plpgsql
    SECURITY DEFINER
    SET search_path = public
    AS $get_table_data$
    DECLARE
        v_schema_name text;
        v_table_name text;
        v_sql text;
        v_order_by text;
    BEGIN
        -- Split the table name
        v_schema_name := split_part(p_full_table_name, '.', 1);
        v_table_name := split_part(p_full_table_name, '.', 2);
        
        IF v_table_name = '' THEN
            v_table_name := v_schema_name;
            v_schema_name := 'public';
        END IF;

        -- Check for timestamp columns to use for ordering
        SELECT
            CASE
                WHEN EXISTS (
                    SELECT 1 FROM information_schema.columns 
                    WHERE table_schema = v_schema_name 
                    AND table_name = v_table_name 
                    AND column_name = 'created_at'
                ) THEN 'created_at DESC NULLS LAST'
                WHEN EXISTS (
                    SELECT 1 FROM information_schema.columns 
                    WHERE table_schema = v_schema_name 
                    AND table_name = v_table_name 
                    AND column_name = 'updated_at'
                ) THEN 'updated_at DESC NULLS LAST'
                ELSE 'CTID DESC' -- Fallback to system column
            END INTO v_order_by;

        -- Construct and execute dynamic SQL
        v_sql := format(
            'SELECT row_to_json(t) FROM %I.%I t ORDER BY %s LIMIT 100',
            v_schema_name,
            v_table_name,
            v_order_by
        );
        
        RETURN QUERY EXECUTE v_sql;
    END;
    $get_table_data$;

    -- Create create_table function with better validation
    CREATE OR REPLACE FUNCTION public.create_table(
        p_table_name text,
        p_columns jsonb
    ) RETURNS boolean
    LANGUAGE plpgsql
    SECURITY DEFINER
    SET search_path = public
    AS $$
    DECLARE
        v_column jsonb;
        v_sql text := '';
        v_primary_keys text := '';
    BEGIN
        -- Validate table name
        IF p_table_name IS NULL OR p_table_name = '' THEN
            RAISE EXCEPTION 'Table name cannot be empty' USING ERRCODE = '22023';
        END IF;
        
        -- Validate columns input
        IF p_columns IS NULL OR jsonb_typeof(p_columns) != 'array' THEN
            RAISE EXCEPTION 'columns parameter must be a JSON array' USING ERRCODE = '22023';
        END IF;
        
        IF jsonb_array_length(p_columns) = 0 THEN
            RAISE EXCEPTION 'At least one column must be specified' USING ERRCODE = '22023';
        END IF;

        -- Start CREATE TABLE statement
        v_sql := format('CREATE TABLE IF NOT EXISTS %I (', p_table_name);
        
        -- Add columns
        FOR v_column IN SELECT * FROM jsonb_array_elements(p_columns)
        LOOP
            -- Validate column structure
            IF v_column->>'name' IS NULL OR v_column->>'type' IS NULL THEN
                RAISE EXCEPTION 'Each column must have a name and type' USING ERRCODE = '22023';
            END IF;

            -- Accumulate column definitions
            v_sql := v_sql || format(
                '%I %s %s,',
                (v_column->>'name'),
                (v_column->>'type'),
                CASE 
                    WHEN (v_column->>'isNullable')::boolean THEN 'NULL'
                    ELSE 'NOT NULL'
                END
            );

            -- Collect primary key columns
            IF (v_column->>'isPrimary')::boolean THEN
                IF v_primary_keys != '' THEN
                    v_primary_keys := v_primary_keys || ',';
                END IF;
                v_primary_keys := v_primary_keys || format('%I', (v_column->>'name'));
            END IF;
        END LOOP;

        -- Add primary key constraint if any columns are marked as primary
        IF v_primary_keys != '' THEN
            v_sql := v_sql || format('PRIMARY KEY (%s))', v_primary_keys);
        ELSE
            -- Remove trailing comma and close parentheses
            v_sql := rtrim(v_sql, ',') || ')';
        END IF;

        -- Execute the CREATE TABLE statement
        EXECUTE v_sql;
        
        RETURN true;
    END;
    $$;

    -- Grant permissions for all schemas
    GRANT USAGE ON SCHEMA public TO anon, authenticated;
    GRANT USAGE ON SCHEMA auth TO anon, authenticated;
    GRANT USAGE ON SCHEMA storage TO anon, authenticated;
    GRANT USAGE ON SCHEMA realtime TO anon, authenticated;
    GRANT EXECUTE ON FUNCTION public.get_tables(text) TO anon, authenticated;
    GRANT EXECUTE ON FUNCTION public.get_columns(text) TO anon, authenticated;
    GRANT EXECUTE ON FUNCTION public.get_table_data(text) TO anon, authenticated;
    GRANT EXECUTE ON FUNCTION public.enum_values(text) TO anon, authenticated;
    GRANT EXECUTE ON FUNCTION public.create_table(text, jsonb) TO anon,