-- Drop previous signatures to avoid conflicts
drop function if exists public.motie_org_events_list(
    timestamptz,
    integer,
    integer,
    text,
    text,
    text,
    text
) cascade;

drop function if exists public.motie_org_events_list(
    timestamptz,
    integer,
    integer,
    text,
    text,
    text,
    text,
    boolean
) cascade;

create or replace function public.motie_org_events_list(
    p_since timestamptz,
    p_limit integer default 200,
    p_offset integer default 0,
    p_event text default null,
    p_department text default null,
    p_position text default null,
    p_name text default null,
    p_core_only boolean default false
)
returns table (
    key_hash text,
    name text,
    department text,
    "position" text,
    phone text,
    task text,
    valid_from timestamptz,
    valid_to timestamptz,
    changed_fields text[],
    event_type text,
    is_current boolean,
    run_id uuid,
    dep_before text,
    pos_before text,
    phone_before text,
    same_run boolean,
    pair_delta_sec integer,
    total_count bigint
)
language plpgsql
as $$
declare
    v_since timestamptz := coalesce(p_since, now() - interval '14 days');
    v_limit integer := case when p_limit is null or p_limit < 1 then 200 else p_limit end;
    v_offset integer := greatest(coalesce(p_offset, 0), 0);
    v_total bigint := 0;
begin
    perform set_config('statement_timeout', '20000', true);

    select count(*) into v_total
    from public.motie_org_events_mv e
    where e.valid_from >= v_since
      and (p_event is null or e.event_type = p_event)
      and (p_department is null or lower(e.department) like '%' || lower(p_department) || '%')
      and (p_position is null or lower(e."position") like '%' || lower(p_position) || '%')
      and (p_name is null or lower(e.name) like '%' || lower(p_name) || '%')
      and (
            not coalesce(p_core_only, false)
         or e.event_type <> 'updated'
         or 'department' = any(coalesce(e.changed_fields, array[]::text[]))
         or 'position'  = any(coalesce(e.changed_fields, array[]::text[]))
      );

    if v_total = 0 then
        return;
    end if;

    return query
    select
        q.key_hash,
        q.name,
        q.department,
        q."position",
        q.phone,
        q.task,
        q.valid_from,
        q.valid_to,
        q.changed_fields,
        q.event_type,
        q.is_current,
        q.run_id,
        q.dep_before,
        q.pos_before,
        q.phone_before,
        q.same_run,
        q.pair_delta_sec,
        v_total
    from (
        select *
        from public.motie_org_events_mv e
        where e.valid_from >= v_since
          and (p_event is null or e.event_type = p_event)
          and (p_department is null or lower(e.department) like '%' || lower(p_department) || '%')
          and (p_position is null or lower(e."position") like '%' || lower(p_position) || '%')
          and (p_name is null or lower(e.name) like '%' || lower(p_name) || '%')
          and (
                not coalesce(p_core_only, false)
             or e.event_type <> 'updated'
             or 'department' = any(coalesce(e.changed_fields, array[]::text[]))
             or 'position'  = any(coalesce(e.changed_fields, array[]::text[]))
          )
        order by e.valid_from desc
        offset v_offset
        limit v_limit
    ) as q;
end;
$$;
