supabase/migrations/006_add_application_functions.sql

-- Migration: Add application-specific functions
-- This migration adds 9 functions that exist on the live database but were
-- not included in earlier schema migrations.

-- =============================================================================
-- 1. setup_initial_admin
-- Sets up an admin user by updating their user_type and username.
-- SECURITY DEFINER: Must be run as service_role or superuser.
-- =============================================================================
CREATE OR REPLACE FUNCTION public.setup_initial_admin(admin_email text)
RETURNS json
LANGUAGE plpgsql
SECURITY DEFINER
AS $function$
declare
    result json;
begin
    -- Only allow this to run as service role or superuser
    if not (
        current_user = 'service_role'
        or exists (
            select 1 from pg_roles
            where rolname = current_user
            and rolsuper
        )
    ) then
        raise exception 'Must be run as service_role or superuser';
    end if;

    -- Update user_type and username for admin
    update public.profiles
    set user_type = 'admin',
        username = coalesce(username, 'superadmin'),
        display_name = coalesce(display_name, 'Super Admin')
    where email = admin_email
    returning json_build_object(
        'id', id,
        'email', email,
        'user_type', user_type,
        'username', username,
        'display_name', display_name
    ) into result;

    if result is null then
        raise exception 'Admin user with email % not found', admin_email;
    end if;

    return result;
end;
$function$;

-- =============================================================================
-- 2. is_admin
-- Returns true if the current user has admin role.
-- =============================================================================
CREATE OR REPLACE FUNCTION public.is_admin()
RETURNS boolean
LANGUAGE sql
SECURITY DEFINER
AS $function$
  select coalesce(
    (select true
     from public.profiles
     where id = auth.uid()
     and user_type = 'admin'),
    false
  );
$function$;

-- =============================================================================
-- 3. is_super_admin
-- Alias for is_admin (same logic).
-- =============================================================================
CREATE OR REPLACE FUNCTION public.is_super_admin()
RETURNS boolean
LANGUAGE sql
SECURITY DEFINER
AS $function$
  select coalesce(
    (select true
     from public.profiles
     where id = auth.uid()
     and user_type = 'admin'),
    false
  );
$function$;

-- =============================================================================
-- 4. check_db_ready
-- Health check: verifies essential schemas, tables, and RLS are in place.
-- =============================================================================
CREATE OR REPLACE FUNCTION public.check_db_ready()
RETURNS boolean
LANGUAGE plpgsql
SECURITY DEFINER
AS $function$
begin
    -- Check if essential schemas exist
    if not exists (
        select 1
        from information_schema.schemata
        where schema_name in ('auth', 'storage', 'public')
    ) then
        return false;
    end if;

    -- Check if essential tables exist
    if not exists (
        select 1
        from information_schema.tables
        where table_schema = 'auth'
        and table_name = 'users'
    ) then
        return false;
    end if;

    -- Check if RLS is enabled on public.profiles
    if not exists (
        select 1
        from pg_tables
        where schemaname = 'public'
        and tablename = 'profiles'
        and rowsecurity = true
    ) then
        return false;
    end if;

    return true;
end;
$function$;

-- =============================================================================
-- 5. match_file_vectors
-- Vector similarity search over file artefacts.
-- NOTE: Requires the `file_vectors` table to exist (vector extension needed).
-- This function was created for a table that may not have been migrated yet.
-- =============================================================================
CREATE OR REPLACE FUNCTION public.match_file_vectors(
    filter jsonb,
    match_count integer,
    query_embedding vector
)
RETURNS TABLE(
    id bigint,
    file_id uuid,
    cabinet_id uuid,
    artefact_type text,
    artefact_is text,
    original_path_prefix text,
    original_filename text,
    content text,
    metadata jsonb,
    similarity double precision
)
LANGUAGE sql
STABLE
AS $function$
  select
    fv.id,
    nullif(fv.metadata->>'file_id','')::uuid              as file_id,
    nullif(fv.metadata->>'cabinet_id','')::uuid           as cabinet_id,
    nullif(fv.metadata->>'artefact_type','')              as artefact_type,
    nullif(fv.metadata->>'artefact_is','')                as artefact_is,
    nullif(fv.metadata->>'original_path_prefix','')       as original_path_prefix,
    nullif(fv.metadata->>'original_filename','')          as original_filename,
    fv.content,
    fv.metadata,
    1 - (fv.embedding <=> query_embedding)                as similarity
  from public.file_vectors fv
  where
    (coalesce(filter ? 'file_id', false)                = false or (fv.metadata->>'file_id')::uuid              = (filter->>'file_id')::uuid)
    and (coalesce(filter ? 'cabinet_id', false)         = false or (fv.metadata->>'cabinet_id')::uuid           = (filter->>'cabinet_id')::uuid)
    and (coalesce(filter ? 'artefact_type', false)      = false or (fv.metadata->>'artefact_type')              = (filter->>'artefact_type'))
    and (coalesce(filter ? 'artefact_id', false)        = false or (fv.metadata->>'artefact_id')                = (filter->>'artefact_id'))
    and (coalesce(filter ? 'original_path_prefix', false)     = false or (fv.metadata->>'original_path_prefix') like  (filter->>'original_path_prefix') || '%')
    and (coalesce(filter ? 'original_path_prefix_ilike', false)= false or (fv.metadata->>'original_path_prefix') ilike (filter->>'original_path_prefix_ilike') || '%')
    and (coalesce(filter ? 'original_filename', false)   = false or (fv.metadata->>'original_filename')         = (filter->>'original_filename'))
    and (coalesce(filter ? 'original_filename_ilike', false)= false or (fv.metadata->>'original_filename') ilike (filter->>'original_filename_ilike'))
  order by fv.embedding <=> query_embedding
  limit greatest(coalesce(match_count, 10), 1)
$function$;

-- =============================================================================
-- 6. set_completed_at
-- Trigger function: sets completed_at when status changes to 'completed'.
-- =============================================================================
CREATE OR REPLACE FUNCTION public.set_completed_at()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
AS $function$
begin
    if NEW.status = 'completed' and OLD.status != 'completed' then
        NEW.completed_at = now();
    end if;
    return NEW;
end;
$function$;

-- =============================================================================
-- 7. handle_updated_at
-- Trigger function: sets updated_at to current UTC time on UPDATE.
-- =============================================================================
CREATE OR REPLACE FUNCTION public.handle_updated_at()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
AS $function$
begin
    new.updated_at = timezone('utc'::text, now());
    return new;
end;
$function$;

-- =============================================================================
-- 8. update_updated_at_column
-- Alternative trigger function: sets updated_at to NOW() on UPDATE.
-- (Duplicate of handle_updated_at with slightly different syntax)
-- =============================================================================
CREATE OR REPLACE FUNCTION public.update_updated_at_column()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$function$;

-- =============================================================================
-- 9. exec_sql
-- Executes arbitrary SQL. SECURITY DEFINER — use with extreme caution.
-- =============================================================================
CREATE OR REPLACE FUNCTION public.exec_sql(query text)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
AS $function$
begin
    execute query;
end;
$function$;