-- PA Studio payroll and time tracking. Run after database-schema.sql,
-- database-account-management.sql, database-workers.sql, and database-finances.sql.
-- Payroll details are manager-only. Marking a payslip paid records the salary
-- expense in the Finance ledger; it does not transfer money.
-- Payroll rates and time tracking are enforced server-side. DJs receive
-- USD 0.30/hour + 5% of settled USD income. Active label artists, secretaries,
-- and songwriters receive USD 0.20/hour. Tracked time replaces manually-entered
-- hours when time entries exist for that worker and period.

create table if not exists public.pa_payroll_slips (
  id uuid primary key default gen_random_uuid(),
  worker_id uuid not null,
  worker_source text not null check (worker_source in ('account','directory')),
  employee_name text not null check (length(btrim(employee_name)) between 1 and 120),
  employee_position text not null check (length(btrim(employee_position)) between 1 and 100),
  employee_email text,
  period_start date not null,
  period_end date not null,
  hours_worked numeric(8,2) not null check (hours_worked > 0 and hours_worked <= 744),
  hourly_rate numeric(14,2) not null check (hourly_rate > 0),
  currency text not null check (currency in ('USD','CDF')),
  commission_base numeric(14,2) not null default 0 check (commission_base >= 0),
  commission_rate numeric(5,2) not null default 0 check (commission_rate between 0 and 100),
  hourly_amount numeric(14,2) generated always as (round(hours_worked * hourly_rate, 2)) stored,
  commission_amount numeric(14,2) generated always as (round(commission_base * commission_rate / 100, 2)) stored,
  bonus_amount numeric(14,2) not null default 0 check (bonus_amount >= 0),
  deduction_amount numeric(14,2) not null default 0 check (deduction_amount >= 0),
  net_amount numeric(14,2) generated always as (
    round(hours_worked * hourly_rate + commission_base * commission_rate / 100 + bonus_amount - deduction_amount, 2)
  ) stored,
  status text not null default 'pending' check (status in ('pending','paid')),
  paid_at date,
  payment_method text,
  notes text not null default '',
  created_by uuid not null references auth.users(id),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint pa_payroll_period_check check (period_end >= period_start and period_end-period_start <= 62),
  constraint pa_payroll_nonnegative_net_check check (net_amount >= 0),
  constraint pa_payroll_paid_fields_check check (
    (status='paid' and paid_at is not null)
    or (status='pending' and paid_at is null)
  ),
  constraint pa_payroll_worker_period_unique unique(worker_source,worker_id,period_start,period_end)
);

create index if not exists pa_payroll_slips_period_idx
  on public.pa_payroll_slips(period_start desc,period_end desc,created_at desc);

alter table public.pa_payroll_slips enable row level security;
revoke all on public.pa_payroll_slips from public, anon, authenticated;

alter table public.pa_payroll_slips drop constraint if exists pa_payroll_slips_worker_source_check;
alter table public.pa_payroll_slips add constraint pa_payroll_slips_worker_source_check
  check (worker_source in ('account','directory'));

alter table public.pa_finance_expenses
  add column if not exists payroll_slip_id uuid references public.pa_payroll_slips(id) on delete set null;
create unique index if not exists pa_finance_expenses_payroll_slip_unique
  on public.pa_finance_expenses(payroll_slip_id) where payroll_slip_id is not null;

drop function if exists public.pa_list_payroll_slips(date,date);
create function public.pa_list_payroll_slips(p_period_start date, p_period_end date)
returns table (
  id uuid,
  worker_id uuid,
  worker_source text,
  employee_name text,
  employee_position text,
  employee_email text,
  period_start date,
  period_end date,
  payable_on date,
  hours_worked numeric,
  hourly_rate numeric,
  currency text,
  hourly_amount numeric,
  commission_base numeric,
  commission_rate numeric,
  commission_amount numeric,
  bonus_amount numeric,
  deduction_amount numeric,
  net_amount numeric,
  status text,
  paid_at date,
  payment_method text,
  notes text,
  created_at timestamptz
)
language plpgsql
security definer
set search_path = ''
as $$
begin
  if coalesce(auth.jwt()->'app_metadata'->>'role','') <> 'manager' then
    raise exception 'Only a Manager can view payroll';
  end if;
  if p_period_start is null or p_period_end is null or p_period_end < p_period_start
     or p_period_end-p_period_start > 370 then
    raise exception 'Invalid payroll period';
  end if;

  return query
    select s.id,s.worker_id,s.worker_source,s.employee_name,s.employee_position,s.employee_email,
           s.period_start,s.period_end,s.period_start+30,s.hours_worked,s.hourly_rate,s.currency,s.hourly_amount,
           s.commission_base,s.commission_rate,s.commission_amount,s.bonus_amount,s.deduction_amount,
           s.net_amount,s.status,s.paid_at,s.payment_method,s.notes,s.created_at
      from public.pa_payroll_slips as s
     where s.period_end >= p_period_start and s.period_start <= p_period_end
     order by s.period_start desc,s.employee_name;
end;
$$;

create or replace function public.pa_create_payroll_slip(
  p_worker_id uuid,
  p_worker_source text,
  p_period_start date,
  p_period_end date,
  p_hours_worked numeric,
  p_hourly_rate numeric,
  p_currency text,
  p_commission_base numeric default 0,
  p_commission_rate numeric default 0,
  p_bonus_amount numeric default 0,
  p_deduction_amount numeric default 0,
  p_notes text default ''
)
returns uuid
language plpgsql
security definer
set search_path = ''
as $$
declare
  v_name text;
  v_position text;
  v_email text;
  v_slip_id uuid;
  v_finance_date date;
  v_net_amount numeric(14,2);
  v_is_commission_role boolean;
  v_is_manager boolean;
  v_is_dj boolean;
  v_hourly_rate numeric(14,2);
  v_commission_base numeric(14,2);
  v_commission_rate numeric(5,2);
  v_is_fixed_rate boolean;
  v_hours_worked numeric(8,2);
  v_tracked_minutes numeric;
begin
  if coalesce(auth.jwt()->'app_metadata'->>'role','') <> 'manager' then
    raise exception 'Only a Manager can create payslips';
  end if;
  if p_worker_id is null or p_worker_source not in ('account','directory')
     or p_period_start is null or p_period_end is null or p_period_end < p_period_start
     or p_period_end-p_period_start > 62
     or coalesce(p_hours_worked,0) < 0 or p_hours_worked > 744
     or coalesce(p_hourly_rate,0) <= 0 or p_currency not in ('USD','CDF')
     or coalesce(p_commission_base,0) < 0 or coalesce(p_commission_rate,0) not between 0 and 100
     or coalesce(p_bonus_amount,0) < 0 or coalesce(p_deduction_amount,0) < 0
     or length(coalesce(p_notes,'')) > 1000 then
    raise exception 'Invalid payslip details';
  end if;

  if p_worker_source='account' then
    select coalesce(nullif(btrim(p.full_name),''),nullif(btrim(u.raw_user_meta_data->>'full_name'),''),
                    nullif(btrim(u.raw_user_meta_data->>'name'),''),u.email,'PA Studio'),
           case coalesce(u.raw_app_meta_data->>'role','')
             when 'manager' then 'manager'
             when 'gestionnaire' then 'gestionnaire'
             when 'dj' then case coalesce(u.raw_app_meta_data->>'dj_type','audio') when 'video' then 'dj_video' else 'dj_audio' end
             else 'Artiste du label'
           end,
           u.email::text
      into v_name,v_position,v_email
      from auth.users as u
      left join public.profiles as p on p.id=u.id
     where u.id=p_worker_id
       and (
         coalesce(u.raw_app_meta_data->>'role','') in ('manager','gestionnaire','dj')
         or (coalesce(u.raw_app_meta_data->>'role','') not in ('manager','gestionnaire','dj') and p.account_status='active')
       );
  else
    select d.full_name,d.position,d.email
      into v_name,v_position,v_email
      from public.pa_staff_directory as d
     where d.id=p_worker_id;
  end if;
  if not found then raise exception 'Worker not found'; end if;

  v_is_manager := lower(btrim(v_position)) in ('manager','manager · propriétaire','manager propriétaire','owner');
  v_is_dj := lower(btrim(v_position)) in ('dj_audio','dj_video','audio dj','video dj')
    or lower(btrim(v_position)) like 'dj %';
  v_is_fixed_rate := lower(btrim(v_position))='artiste du label'
    or lower(btrim(v_position)) like '%secr%'
    or lower(btrim(v_position)) like '%parolier%'
    or lower(btrim(v_position)) like '%songwriter%'
    or lower(btrim(v_position)) like '%lyricist%';
  v_is_commission_role := lower(btrim(v_position))='gestionnaire' or v_is_dj;
  v_hourly_rate := p_hourly_rate;
  v_commission_base := coalesce(p_commission_base,0);
  v_commission_rate := coalesce(p_commission_rate,0);
  v_hours_worked := p_hours_worked;
  select coalesce(sum(greatest(0,floor(extract(epoch from
      least(coalesce(t.stopped_at,now()),(t.work_date+t.shift_end) at time zone 'Africa/Lubumbashi')
      - greatest(t.started_at,(t.work_date+t.shift_start) at time zone 'Africa/Lubumbashi'))/60))),0)
    into v_tracked_minutes
    from public.pa_payroll_time_entries as t
   where t.worker_source=p_worker_source and t.worker_id=p_worker_id
     and t.work_date between p_period_start and p_period_end;
  if v_tracked_minutes > 0 then
    v_hours_worked := round(v_tracked_minutes/60,2);
  end if;
  if v_hours_worked <= 0 then raise exception 'No paid hours were recorded for this period'; end if;
  if v_is_dj then
    v_hourly_rate := 0.30;
    p_currency := 'USD';
    v_commission_rate := 5;
    select coalesce(sum(e.amount),0)::numeric(14,2)
      into v_commission_base
      from public.pa_finance_expenses as e
     where e.transaction_type='income'
       and e.status='settled'
       and e.currency='USD'
       and e.expense_date between p_period_start and p_period_end;
  end if;
  if v_is_fixed_rate then
    v_hourly_rate := 0.20;
    p_currency := 'USD';
    v_commission_base := 0;
    v_commission_rate := 0;
  end if;
  if v_is_manager and (coalesce(p_commission_base,0)<>0 or coalesce(p_commission_rate,0)<>0) then
    raise exception 'Managers are paid without a commission';
  end if;
  if not v_is_manager and not v_is_dj and not v_is_commission_role
     and (coalesce(p_commission_base,0)<>0 or coalesce(p_commission_rate,0)<>0) then
    raise exception 'Commission is only available for DJs and Gestionnaires';
  end if;

  v_net_amount := round(v_hours_worked*v_hourly_rate
    +v_commission_base*v_commission_rate/100
    +coalesce(p_bonus_amount,0)-coalesce(p_deduction_amount,0),2);
  if v_net_amount < 0 then raise exception 'Net pay cannot be negative'; end if;

  insert into public.pa_payroll_slips(
    worker_id,worker_source,employee_name,employee_position,employee_email,period_start,period_end,
    hours_worked,hourly_rate,currency,commission_base,commission_rate,bonus_amount,deduction_amount,notes,created_by
  ) values (
    p_worker_id,p_worker_source,v_name,v_position,v_email,p_period_start,p_period_end,
    v_hours_worked,v_hourly_rate,p_currency,v_commission_base,v_commission_rate,
    coalesce(p_bonus_amount,0),coalesce(p_deduction_amount,0),btrim(coalesce(p_notes,'')),auth.uid()
  ) returning id,period_end,net_amount into v_slip_id,v_finance_date,v_net_amount;

  insert into public.pa_finance_expenses(
    description,transaction_type,category,beneficiary,amount,currency,expense_date,status,
    settled_at,notes,created_by,payroll_slip_id
  ) values (
    'Salaire · '||v_name,'expense','salary',v_name,v_net_amount,p_currency,v_finance_date,'pending',
    null,'Fiche de paie '||p_period_start::text||' – '||p_period_end::text,auth.uid(),v_slip_id
  );
  return v_slip_id;
end;
$$;

create or replace function public.pa_set_payroll_slip_status(
  p_slip_id uuid,
  p_status text,
  p_payment_method text default null,
  p_paid_at date default null
)
returns void
language plpgsql
security definer
set search_path = ''
as $$
declare
  v_paid_at date;
  v_payment_method text;
  v_period_start date;
begin
  if coalesce(auth.jwt()->'app_metadata'->>'role','') <> 'manager' then
    raise exception 'Only a Manager can update payroll';
  end if;
  if p_status not in ('pending','paid') then raise exception 'Invalid payslip status'; end if;
  if p_status='paid' and length(btrim(coalesce(p_payment_method,'')))=0 then
    raise exception 'Select how the salary was paid';
  end if;
  if length(coalesce(p_payment_method,''))>80 then raise exception 'Invalid payment method'; end if;

  v_paid_at := case when p_status='paid' then coalesce(p_paid_at,(now() at time zone 'Africa/Lubumbashi')::date) else null end;
  v_payment_method := case when p_status='paid' then nullif(btrim(coalesce(p_payment_method,'')),'') else null end;
  if p_status='paid' then
    select s.period_start into v_period_start from public.pa_payroll_slips as s where s.id=p_slip_id;
    if not found then raise exception 'Payslip not found'; end if;
    if (now() at time zone 'Africa/Lubumbashi')::date < v_period_start+30 or v_paid_at < v_period_start+30
       or v_paid_at>(now() at time zone 'Africa/Lubumbashi')::date then
      raise exception 'This salary can be paid only after 30 days';
    end if;
  end if;
  update public.pa_payroll_slips
     set status=p_status,paid_at=v_paid_at,payment_method=v_payment_method,updated_at=now()
   where id=p_slip_id;
  if not found then raise exception 'Payslip not found'; end if;

  update public.pa_finance_expenses as e
     set status=case when p_status='paid' then 'settled' else 'pending' end,
         payment_method=v_payment_method,
         settled_at=v_paid_at,
         updated_at=now()
   where e.payroll_slip_id=p_slip_id;
  if not found then raise exception 'Linked salary expense not found'; end if;
end;
$$;

revoke all on function public.pa_list_payroll_slips(date,date) from public, anon;
revoke all on function public.pa_create_payroll_slip(uuid,text,date,date,numeric,numeric,text,numeric,numeric,numeric,numeric,text) from public, anon;
revoke all on function public.pa_set_payroll_slip_status(uuid,text,text,date) from public, anon;
grant execute on function public.pa_list_payroll_slips(date,date) to authenticated;
grant execute on function public.pa_create_payroll_slip(uuid,text,date,date,numeric,numeric,text,numeric,numeric,numeric,numeric,text) to authenticated;
grant execute on function public.pa_set_payroll_slip_status(uuid,text,text,date) to authenticated;

-- Each worker can create and close only their own shift timer. The Manager can
-- view the roster and accumulated time, but cannot start or stop another timer.
create table if not exists public.pa_payroll_time_entries (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  worker_id uuid not null,
  worker_source text not null check (worker_source in ('account','directory')),
  employee_name text not null,
  employee_position text not null,
  work_date date not null,
  shift_start time not null,
  shift_end time not null,
  started_at timestamptz not null default now(),
  stopped_at timestamptz,
  constraint pa_payroll_timer_shift_check check (shift_end > shift_start),
  constraint pa_payroll_timer_stopped_check check (stopped_at is null or stopped_at >= started_at)
);
create index if not exists pa_payroll_time_entries_period_idx
  on public.pa_payroll_time_entries(worker_source,worker_id,work_date);
create unique index if not exists pa_payroll_time_entries_one_open_timer_idx
  on public.pa_payroll_time_entries(user_id) where stopped_at is null;
alter table public.pa_payroll_time_entries enable row level security;
revoke all on public.pa_payroll_time_entries from public,anon,authenticated;

create or replace function public.pa_resolve_my_payroll_worker(p_user_id uuid)
returns table (
  worker_id uuid,
  worker_source text,
  full_name text,
  position text,
  email text,
  shift_start time,
  shift_end time,
  hourly_rate numeric,
  currency text
)
language plpgsql
security definer
set search_path = ''
as $$
declare
  v_role text;
  v_dj_type text;
  v_email text;
  v_name text;
  v_position text;
  v_worker_id uuid;
  v_source text;
  v_status text;
  v_start time := time '08:00';
  v_end time := time '15:00';
  v_rate numeric;
begin
  if auth.uid() is null or p_user_id is distinct from auth.uid() then
    raise exception 'Not authorized';
  end if;

  select coalesce(u.raw_app_meta_data->>'role',''),
         coalesce(u.raw_app_meta_data->>'dj_type','audio'),
         u.email::text,
         coalesce(nullif(btrim(p.full_name),''),nullif(btrim(u.raw_user_meta_data->>'full_name'),''),
                  nullif(btrim(u.raw_user_meta_data->>'name'),''),u.email,'PA Studio'),
         p.account_status
    into v_role,v_dj_type,v_email,v_name,v_status
    from auth.users as u left join public.profiles as p on p.id=u.id
   where u.id=p_user_id;
  if not found then raise exception 'Worker account not found'; end if;

  if v_role='manager' then
    v_worker_id:=p_user_id; v_source:='account'; v_position:='Manager'; v_end:=time '18:00';
  elsif v_role='gestionnaire' then
    v_worker_id:=p_user_id; v_source:='account'; v_position:='Gestionnaire'; v_end:=time '18:00';
  elsif v_role='dj' then
    v_worker_id:=p_user_id; v_source:='account';
    v_position:=case when v_dj_type='video' then 'DJ vidéo' else 'DJ audio' end;
    v_end:=time '21:00'; v_rate:=0.30;
  else
    select d.id,d.full_name,d.position,d.email
      into v_worker_id,v_name,v_position,v_email
      from public.pa_staff_directory as d
     where d.active and lower(coalesce(d.email,''))=lower(coalesce(v_email,''))
     order by d.created_at desc limit 1;
    if found then
      v_source:='directory';
      if lower(v_position) like '%gestionnaire%' then v_end:=time '18:00'; end if;
      if lower(v_position) like 'dj %' or lower(v_position) like '% dj%' then v_end:=time '21:00'; v_rate:=0.30; end if;
      if lower(v_position)='artiste du label' or lower(v_position) like '%secr%'
         or lower(v_position) like '%parolier%' or lower(v_position) like '%songwriter%'
         or lower(v_position) like '%lyricist%' then v_rate:=0.20; end if;
    elsif v_status='active' then
      v_worker_id:=p_user_id; v_source:='account'; v_position:='Artiste du label'; v_rate:=0.20;
    else
      raise exception 'An active PA Studio worker or artist account is required';
    end if;
  end if;
  if v_source='account' and v_role='dj' then v_rate:=0.30; end if;
  if v_source='account' and v_position='Artiste du label' then v_rate:=0.20; end if;

  return query select v_worker_id,v_source,v_name,v_position,v_email,v_start,v_end,v_rate,'USD'::text;
end;
$$;

create or replace function public.pa_my_payroll_timer()
returns table (
  worker_id uuid,
  worker_source text,
  employee_name text,
  employee_position text,
  employee_email text,
  shift_start time,
  shift_end time,
  hourly_rate numeric,
  currency text,
  work_date date,
  today_minutes integer,
  period_minutes integer,
  is_running boolean,
  started_at timestamptz
)
language plpgsql
security definer
set search_path = ''
as $$
declare
  v_worker record;
  v_today date := (now() at time zone 'Africa/Lubumbashi')::date;
begin
  select * into v_worker from public.pa_resolve_my_payroll_worker(auth.uid());
  update public.pa_payroll_time_entries as t
     set stopped_at=(t.work_date+t.shift_end) at time zone 'Africa/Lubumbashi'
   where t.user_id=auth.uid() and t.stopped_at is null
     and (t.work_date<v_today or (now() at time zone 'Africa/Lubumbashi')::time>=t.shift_end);

  return query
    select v_worker.worker_id,v_worker.worker_source,v_worker.full_name,v_worker.position,
           v_worker.email,v_worker.shift_start,v_worker.shift_end,v_worker.hourly_rate,v_worker.currency,
           v_today,
           coalesce(sum(greatest(0,floor(extract(epoch from
             least(coalesce(t.stopped_at,now()),(t.work_date+t.shift_end) at time zone 'Africa/Lubumbashi')
             - greatest(t.started_at,(t.work_date+t.shift_start) at time zone 'Africa/Lubumbashi'))/60)))
             filter(where t.work_date=v_today),0)::integer,
           coalesce(sum(greatest(0,floor(extract(epoch from
             least(coalesce(t.stopped_at,now()),(t.work_date+t.shift_end) at time zone 'Africa/Lubumbashi')
             - greatest(t.started_at,(t.work_date+t.shift_start) at time zone 'Africa/Lubumbashi'))/60)))
             filter(where t.work_date>=date_trunc('month',v_today)::date and t.work_date<=v_today),0)::integer,
           coalesce(bool_or(t.stopped_at is null and now()<((t.work_date+t.shift_end) at time zone 'Africa/Lubumbashi')),false),
           max(t.started_at) filter(where t.work_date=v_today and t.stopped_at is null)
      from public.pa_payroll_time_entries as t
     where t.user_id=auth.uid() and t.work_date>=date_trunc('month',v_today)::date and t.work_date<=v_today;
end;
$$;

create or replace function public.pa_start_my_payroll_timer()
returns uuid
language plpgsql
security definer
set search_path = ''
as $$
declare
  v_worker record;
  v_now timestamptz:=clock_timestamp();
  v_today date;
  v_local_time time;
  v_id uuid;
begin
  select * into v_worker from public.pa_resolve_my_payroll_worker(auth.uid());
  v_today:=(v_now at time zone 'Africa/Lubumbashi')::date;
  v_local_time:=(v_now at time zone 'Africa/Lubumbashi')::time;
  if v_local_time<v_worker.shift_start or v_local_time>=v_worker.shift_end then
    raise exception 'The timer can only start during the scheduled shift';
  end if;
  update public.pa_payroll_time_entries as t
     set stopped_at=(t.work_date+t.shift_end) at time zone 'Africa/Lubumbashi'
   where t.user_id=auth.uid() and t.stopped_at is null
     and (t.work_date<v_today or v_local_time>=t.shift_end);
  if exists(select 1 from public.pa_payroll_time_entries as t where t.user_id=auth.uid() and t.stopped_at is null) then
    raise exception 'Your timer is already running';
  end if;
  insert into public.pa_payroll_time_entries(
    user_id,worker_id,worker_source,employee_name,employee_position,work_date,shift_start,shift_end,started_at
  ) values(
    auth.uid(),v_worker.worker_id,v_worker.worker_source,v_worker.full_name,v_worker.position,
    v_today,v_worker.shift_start,v_worker.shift_end,v_now
  ) returning id into v_id;
  return v_id;
end;
$$;

create or replace function public.pa_stop_my_payroll_timer()
returns void
language plpgsql
security definer
set search_path = ''
as $$
declare
  v_now timestamptz:=clock_timestamp();
begin
  if auth.uid() is null then raise exception 'Sign in to stop your timer'; end if;
  update public.pa_payroll_time_entries as t
     set stopped_at=greatest(t.started_at,least(v_now,(t.work_date+t.shift_end) at time zone 'Africa/Lubumbashi'))
   where t.id=(select latest.id from public.pa_payroll_time_entries as latest
               where latest.user_id=auth.uid() and latest.stopped_at is null
               order by latest.started_at desc limit 1);
  if not found then raise exception 'No running timer was found'; end if;
end;
$$;

create or replace function public.pa_list_payroll_timers(p_period_start date,p_period_end date)
returns table (
  worker_id uuid,
  worker_source text,
  employee_name text,
  employee_position text,
  employee_email text,
  has_login boolean,
  shift_start time,
  shift_end time,
  hourly_rate numeric,
  currency text,
  period_minutes integer,
  today_minutes integer,
  is_running boolean,
  started_at timestamptz
)
language plpgsql
security definer
set search_path = ''
as $$
declare
  v_today date:=(now() at time zone 'Africa/Lubumbashi')::date;
begin
  if coalesce(auth.jwt()->'app_metadata'->>'role','')<>'manager' then raise exception 'Only a Manager can view worker timers'; end if;
  if p_period_start is null or p_period_end is null or p_period_end<p_period_start or p_period_end-p_period_start>370 then
    raise exception 'Invalid timer period';
  end if;
  return query
  with worker_rows as (
    select w.id as wid,w.source as wsource,w.full_name as wname,w.position as wposition,w.email as wemail
      from public.pa_list_workers() as w where w.active
    union all
    select p.id,'account'::text,coalesce(nullif(btrim(p.full_name),''),u.email,'Artiste'),'Artiste du label',u.email::text
      from public.profiles as p join auth.users as u on u.id=p.id
     where p.account_status='active'
       and coalesce(u.raw_app_meta_data->>'role','') not in ('manager','gestionnaire','dj')
       and not exists(select 1 from public.pa_staff_directory as d where d.active and lower(coalesce(d.email,''))=lower(coalesce(u.email,'')))
  ), shaped as (
    select w.*,
      time '08:00' as shift_start,
      case when lower(w.wposition) like '%manager%' or lower(w.wposition) like '%gestionnaire%' then time '18:00'
           when lower(w.wposition) like 'dj %' or lower(w.wposition) like '% dj%' or lower(w.wposition) in ('dj_audio','dj_video') then time '21:00'
           else time '15:00' end as shift_end,
      case when lower(w.wposition) like 'dj %' or lower(w.wposition) like '% dj%' or lower(w.wposition) in ('dj_audio','dj_video') then 0.30::numeric
           when lower(w.wposition)='artiste du label' or lower(w.wposition) like '%secr%'
             or lower(w.wposition) like '%parolier%' or lower(w.wposition) like '%songwriter%' or lower(w.wposition) like '%lyricist%' then 0.20::numeric
           else null::numeric end as hourly_rate
      from worker_rows as w
  )
  select s.wid,s.wsource,s.wname,s.wposition,s.wemail,
         exists(select 1 from auth.users as u where lower(coalesce(u.email,''))=lower(coalesce(s.wemail,''))) as has_login,
         s.shift_start,s.shift_end,s.hourly_rate,'USD'::text,
         coalesce(a.period_minutes,0)::integer,coalesce(a.today_minutes,0)::integer,
         coalesce(a.is_running,false),a.started_at
    from shaped as s
    left join lateral (
      select sum(greatest(0,floor(extract(epoch from
               least(coalesce(t.stopped_at,now()),(t.work_date+t.shift_end) at time zone 'Africa/Lubumbashi')
               - greatest(t.started_at,(t.work_date+t.shift_start) at time zone 'Africa/Lubumbashi'))/60)))::integer as period_minutes,
             sum(greatest(0,floor(extract(epoch from
               least(coalesce(t.stopped_at,now()),(t.work_date+t.shift_end) at time zone 'Africa/Lubumbashi')
               - greatest(t.started_at,(t.work_date+t.shift_start) at time zone 'Africa/Lubumbashi'))/60)))
               filter(where t.work_date=v_today)::integer as today_minutes,
             coalesce(bool_or(t.stopped_at is null and now()<((t.work_date+t.shift_end) at time zone 'Africa/Lubumbashi')),false) as is_running,
             max(t.started_at) filter(where t.work_date=v_today and t.stopped_at is null) as started_at
        from public.pa_payroll_time_entries as t
       where t.worker_id=s.wid and t.worker_source=s.wsource and t.work_date between p_period_start and least(p_period_end,v_today)
    ) as a on true
   order by s.wname;
end;
$$;

revoke all on function public.pa_resolve_my_payroll_worker(uuid) from public,anon,authenticated;
revoke all on function public.pa_my_payroll_timer() from public,anon;
revoke all on function public.pa_start_my_payroll_timer() from public,anon;
revoke all on function public.pa_stop_my_payroll_timer() from public,anon;
revoke all on function public.pa_list_payroll_timers(date,date) from public,anon;
grant execute on function public.pa_my_payroll_timer() to authenticated;
grant execute on function public.pa_start_my_payroll_timer() to authenticated;
grant execute on function public.pa_stop_my_payroll_timer() to authenticated;
grant execute on function public.pa_list_payroll_timers(date,date) to authenticated;