-- clean up probe rows created during access testing
DELETE FROM public.payroll_items WHERE run_id IN (SELECT id FROM public.payroll_runs WHERE status = 'draft' AND net_total = 0);
DELETE FROM public.payroll_runs WHERE status = 'draft' AND net_total = 0;
DELETE FROM public.staff WHERE name = 'rls probe';

-- helper: edit permission
CREATE OR REPLACE FUNCTION public.can_edit(_module text)
RETURNS boolean LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public AS $$
  SELECT CASE
    WHEN public.is_admin() THEN true
    ELSE EXISTS (
      SELECT 1 FROM public.user_module_access
      WHERE user_id = auth.uid() AND module = _module AND can_edit
    )
  END
$$;

-- ===== HR: employees =====
DROP POLICY IF EXISTS staff_all ON public.staff;
CREATE POLICY staff_read ON public.staff FOR SELECT TO authenticated USING (public.can_access('employees'));
CREATE POLICY staff_write ON public.staff FOR ALL TO authenticated USING (public.can_edit('employees')) WITH CHECK (public.can_edit('employees'));

DROP POLICY IF EXISTS salary_structures_all ON public.salary_structures;
CREATE POLICY salary_structures_read ON public.salary_structures FOR SELECT TO authenticated USING (public.can_access('employees'));
CREATE POLICY salary_structures_write ON public.salary_structures FOR ALL TO authenticated USING (public.can_edit('employees')) WITH CHECK (public.can_edit('employees'));

DROP POLICY IF EXISTS staff_advances_all ON public.staff_advances;
CREATE POLICY staff_advances_read ON public.staff_advances FOR SELECT TO authenticated USING (public.can_access('employees'));
CREATE POLICY staff_advances_write ON public.staff_advances FOR ALL TO authenticated USING (public.can_edit('employees')) WITH CHECK (public.can_edit('employees'));

DROP POLICY IF EXISTS departments_all ON public.departments;
CREATE POLICY departments_read ON public.departments FOR SELECT TO authenticated USING (public.can_access('employees'));
CREATE POLICY departments_write ON public.departments FOR ALL TO authenticated USING (public.can_edit('employees')) WITH CHECK (public.can_edit('employees'));

-- ===== HR: attendance / leave =====
DROP POLICY IF EXISTS attendance_all ON public.attendance;
CREATE POLICY attendance_read ON public.attendance FOR SELECT TO authenticated USING (public.can_access('attendance'));
CREATE POLICY attendance_write ON public.attendance FOR ALL TO authenticated USING (public.can_edit('attendance')) WITH CHECK (public.can_edit('attendance'));

DROP POLICY IF EXISTS leave_requests_all ON public.leave_requests;
CREATE POLICY leave_requests_read ON public.leave_requests FOR SELECT TO authenticated USING (public.can_access('leave'));
CREATE POLICY leave_requests_write ON public.leave_requests FOR ALL TO authenticated USING (public.can_edit('leave')) WITH CHECK (public.can_edit('leave'));

DROP POLICY IF EXISTS holidays_all ON public.holidays;
CREATE POLICY holidays_read ON public.holidays FOR SELECT TO authenticated USING (public.can_access('leave') OR public.can_access('attendance'));
CREATE POLICY holidays_write ON public.holidays FOR ALL TO authenticated USING (public.can_edit('leave')) WITH CHECK (public.can_edit('leave'));

-- ===== HR: payroll =====
DROP POLICY IF EXISTS payroll_runs_all ON public.payroll_runs;
CREATE POLICY payroll_runs_read ON public.payroll_runs FOR SELECT TO authenticated USING (public.can_access('payroll'));
CREATE POLICY payroll_runs_write ON public.payroll_runs FOR ALL TO authenticated USING (public.can_edit('payroll')) WITH CHECK (public.can_edit('payroll'));

DROP POLICY IF EXISTS payroll_items_all ON public.payroll_items;
CREATE POLICY payroll_items_read ON public.payroll_items FOR SELECT TO authenticated USING (public.can_access('payroll'));
CREATE POLICY payroll_items_write ON public.payroll_items FOR ALL TO authenticated USING (public.can_edit('payroll')) WITH CHECK (public.can_edit('payroll'));

-- ===== Accounting: ledger =====
DROP POLICY IF EXISTS "je read" ON public.journal_entries;
DROP POLICY IF EXISTS "je insert" ON public.journal_entries;
CREATE POLICY je_read ON public.journal_entries FOR SELECT TO authenticated USING (public.can_access('accounting'));

DROP POLICY IF EXISTS "jl read" ON public.journal_lines;
DROP POLICY IF EXISTS "jl insert" ON public.journal_lines;
CREATE POLICY jl_read ON public.journal_lines FOR SELECT TO authenticated USING (public.can_access('accounting'));

DROP POLICY IF EXISTS "fiscal all" ON public.fiscal_periods;
CREATE POLICY fiscal_read ON public.fiscal_periods FOR SELECT TO authenticated USING (public.can_access('accounting'));
CREATE POLICY fiscal_write ON public.fiscal_periods FOR ALL TO authenticated USING (public.can_edit('accounting')) WITH CHECK (public.can_edit('accounting'));

DROP POLICY IF EXISTS transfers_all ON public.transfers;
CREATE POLICY transfers_read ON public.transfers FOR SELECT TO authenticated USING (public.can_access('cash-bank'));
CREATE POLICY transfers_write ON public.transfers FOR ALL TO authenticated USING (public.can_edit('cash-bank')) WITH CHECK (public.can_edit('cash-bank'));

-- accounts / account_types stay readable (needed by invoice, expense and payment pickers) but are admin-only to change
DROP POLICY IF EXISTS "accounts all" ON public.accounts;
CREATE POLICY accounts_read ON public.accounts FOR SELECT TO authenticated USING (true);
CREATE POLICY accounts_write ON public.accounts FOR ALL TO authenticated USING (public.can_edit('accounting')) WITH CHECK (public.can_edit('accounting'));

DROP POLICY IF EXISTS "account_types all" ON public.account_types;
CREATE POLICY account_types_read ON public.account_types FOR SELECT TO authenticated USING (true);
CREATE POLICY account_types_write ON public.account_types FOR ALL TO authenticated USING (public.is_admin()) WITH CHECK (public.is_admin());

-- ===== Server-side guards inside privileged routines =====
CREATE OR REPLACE FUNCTION public.require_access(_module text)
RETURNS void LANGUAGE plpgsql STABLE SECURITY DEFINER SET search_path = public AS $$
BEGIN
  IF NOT public.can_edit(_module) THEN
    RAISE EXCEPTION 'You do not have permission to perform this action (% module)', _module;
  END IF;
END; $$;

CREATE OR REPLACE FUNCTION public.generate_payroll_run(_start date, _end date, _pay_date date)
 RETURNS uuid LANGUAGE plpgsql SECURITY DEFINER SET search_path TO 'public'
AS $function$
DECLARE
  _run uuid; _cfg jsonb; _workdays numeric; _hours numeric; _mult numeric; _grace int;
  _s record; _st record; _basic numeric; _hr numeric; _med numeric; _tr numeric; _oth numeric;
  _base numeric; _perday numeric; _hourly numeric; _absent numeric; _late int; _ot numeric;
  _otamt numeric; _absded numeric; _lateded numeric; _gross numeric; _pf numeric; _tax numeric;
  _adv numeric; _outstanding numeric; _ded numeric; _net numeric;
  _g numeric := 0; _d numeric := 0; _n numeric := 0;
BEGIN
  PERFORM public.require_access('payroll');
  IF _end < _start THEN RAISE EXCEPTION 'Period end must be after the start date'; END IF;
  SELECT value INTO _cfg FROM public.settings WHERE key = 'payroll';
  _workdays := COALESCE((_cfg->>'monthly_workdays')::numeric, 26);
  _hours := COALESCE((_cfg->>'daily_hours')::numeric, 8);
  _mult := COALESCE((_cfg->>'overtime_multiplier')::numeric, 2);
  _grace := COALESCE((_cfg->>'late_grace_minutes')::int, 0);

  INSERT INTO public.payroll_runs (run_number, period_start, period_end, pay_date, status)
  VALUES (public.next_number('payroll'), _start, _end, _pay_date, 'draft')
  ON CONFLICT (period_start, period_end) DO UPDATE SET pay_date = EXCLUDED.pay_date, updated_at = now()
  RETURNING id INTO _run;

  IF (SELECT status FROM public.payroll_runs WHERE id = _run) <> 'draft' THEN
    RAISE EXCEPTION 'The payroll for this period is already approved or paid';
  END IF;
  DELETE FROM public.payroll_items WHERE run_id = _run;

  FOR _s IN SELECT * FROM public.staff WHERE is_active ORDER BY name LOOP
    SELECT * INTO _st FROM public.salary_structures
     WHERE staff_id = _s.id AND effective_from <= _end
     ORDER BY effective_from DESC LIMIT 1;

    _basic := COALESCE(_st.basic, _s.salary, 0);
    _hr := COALESCE(_st.house_rent, 0); _med := COALESCE(_st.medical, 0);
    _tr := COALESCE(_st.transport, 0); _oth := COALESCE(_st.other_allowance, 0);
    _base := _basic + _hr + _med + _tr + _oth;
    IF _base <= 0 THEN CONTINUE; END IF;

    _perday := round(_base / NULLIF(_workdays,0), 2);
    _hourly := round(_perday / NULLIF(_hours,0), 2);

    SELECT COALESCE(SUM(CASE WHEN status IN ('absent','leave_unpaid') THEN 1 ELSE 0 END),0),
           COALESCE(SUM(GREATEST(late_minutes - _grace, 0)),0),
           COALESCE(SUM(overtime_hours),0)
      INTO _absent, _late, _ot
    FROM public.attendance WHERE staff_id = _s.id AND work_date BETWEEN _start AND _end;

    _absded := round(_perday * _absent, 2);
    _lateded := round(_hourly * (_late::numeric / 60), 2);
    _otamt := round(_hourly * _mult * _ot, 2);
    _gross := round(_base - _absded - _lateded + _otamt, 2);
    _pf := CASE WHEN _s.pf_enrolled THEN round(_basic * COALESCE(_s.pf_rate,0) / 100, 2) ELSE 0 END;
    _tax := round(COALESCE(_s.monthly_tax,0), 2);

    SELECT COALESCE(SUM(outstanding),0) INTO _outstanding FROM public.staff_advances WHERE staff_id = _s.id;
    _adv := LEAST(COALESCE(_s.advance_installment,0), _outstanding);

    _ded := round(_pf + _tax + _adv, 2);
    _net := round(_gross - _ded, 2);
    IF _net < 0 THEN _net := 0; _ded := _gross; END IF;

    INSERT INTO public.payroll_items (run_id, staff_id, basic, house_rent, medical, transport,
      other_allowance, payable_days, absent_days, late_minutes, overtime_hours, overtime_amount,
      absence_deduction, late_deduction, gross, tax, pf_employee, advance_deduction,
      deduction_total, net_pay)
    VALUES (_run, _s.id, _basic, _hr, _med, _tr, _oth, GREATEST(_workdays - _absent, 0), _absent,
      _late, _ot, _otamt, _absded, _lateded, _gross, _tax, _pf, _adv, _ded, _net);

    _g := _g + _gross; _d := _d + _ded; _n := _n + _net;
  END LOOP;

  UPDATE public.payroll_runs SET gross_total = round(_g,2), deduction_total = round(_d,2),
    net_total = round(_n,2) WHERE id = _run;
  RETURN _run;
END; $function$;

CREATE OR REPLACE FUNCTION public.approve_payroll_run(_id uuid)
 RETURNS uuid LANGUAGE plpgsql SECURITY DEFINER SET search_path TO 'public'
AS $function$
DECLARE
  _run public.payroll_runs; _lines jsonb := '[]'::jsonb; _je uuid;
  _basic numeric; _allow numeric; _ot numeric; _bonus numeric; _tax numeric; _pf numeric;
  _adv numeric; _net numeric; _it record; _left numeric; _take numeric; _a record;
BEGIN
  PERFORM public.require_access('payroll');
  SELECT * INTO _run FROM public.payroll_runs WHERE id = _id FOR UPDATE;
  IF _run.id IS NULL THEN RAISE EXCEPTION 'Payroll run not found'; END IF;
  IF _run.status <> 'draft' THEN RAISE EXCEPTION 'Only draft payroll runs can be approved'; END IF;

  SELECT COALESCE(SUM(basic - absence_deduction - late_deduction),0),
         COALESCE(SUM(house_rent + medical + transport + other_allowance),0),
         COALESCE(SUM(overtime_amount),0), COALESCE(SUM(bonus),0),
         COALESCE(SUM(tax),0), COALESCE(SUM(pf_employee),0),
         COALESCE(SUM(advance_deduction),0), COALESCE(SUM(net_pay),0)
    INTO _basic, _allow, _ot, _bonus, _tax, _pf, _adv, _net
  FROM public.payroll_items WHERE run_id = _id;

  IF COALESCE(_net,0) <= 0 THEN RAISE EXCEPTION 'Payroll has no payable amount'; END IF;

  IF _basic <> 0 THEN _lines := _lines || jsonb_build_object('account_id', public.account_id_by_code('6010'), 'debit', round(_basic,2), 'credit', 0, 'description', 'Basic salary ' || _run.run_number); END IF;
  IF _allow <> 0 THEN _lines := _lines || jsonb_build_object('account_id', public.account_id_by_code('6020'), 'debit', round(_allow,2), 'credit', 0, 'description', 'Allowances ' || _run.run_number); END IF;
  IF _ot <> 0 THEN _lines := _lines || jsonb_build_object('account_id', public.account_id_by_code('6030'), 'debit', round(_ot,2), 'credit', 0, 'description', 'Overtime ' || _run.run_number); END IF;
  IF _bonus <> 0 THEN _lines := _lines || jsonb_build_object('account_id', public.account_id_by_code('6040'), 'debit', round(_bonus,2), 'credit', 0, 'description', 'Bonus ' || _run.run_number); END IF;
  IF _tax <> 0 THEN _lines := _lines || jsonb_build_object('account_id', public.account_id_by_code('2110'), 'debit', 0, 'credit', round(_tax,2), 'description', 'Tax withheld ' || _run.run_number); END IF;
  IF _pf <> 0 THEN _lines := _lines || jsonb_build_object('account_id', public.account_id_by_code('2250'), 'debit', 0, 'credit', round(_pf,2), 'description', 'Provident fund ' || _run.run_number); END IF;
  IF _adv <> 0 THEN _lines := _lines || jsonb_build_object('account_id', public.account_id_by_code('1250'), 'debit', 0, 'credit', round(_adv,2), 'description', 'Advance recovery ' || _run.run_number); END IF;
  _lines := _lines || jsonb_build_object('account_id', public.account_id_by_code('2220'), 'debit', 0, 'credit', round(_net,2), 'description', 'Net salary payable ' || _run.run_number);

  _je := public.post_journal_entry(_run.period_end, 'Payroll ' || _run.run_number, 'payroll', _id, _lines);

  FOR _it IN SELECT staff_id, advance_deduction FROM public.payroll_items
             WHERE run_id = _id AND advance_deduction > 0 LOOP
    _left := _it.advance_deduction;
    FOR _a IN SELECT id, outstanding FROM public.staff_advances
              WHERE staff_id = _it.staff_id AND outstanding > 0 ORDER BY advance_date LOOP
      EXIT WHEN _left <= 0;
      _take := LEAST(_left, _a.outstanding);
      UPDATE public.staff_advances SET recovered = recovered + _take, outstanding = outstanding - _take WHERE id = _a.id;
      _left := _left - _take;
    END LOOP;
  END LOOP;

  UPDATE public.payroll_runs SET status = 'approved', approved_at = now(), journal_entry_id = _je,
    gross_total = round(_basic + _allow + _ot + _bonus, 2),
    deduction_total = round(_tax + _pf + _adv, 2), net_total = round(_net, 2)
  WHERE id = _id;

  INSERT INTO public.audit_logs (user_id, action, entity_type, entity_id, description)
  VALUES (auth.uid(), 'approve', 'payroll_run', _id, 'Approved payroll ' || _run.run_number);
  RETURN _je;
END; $function$;

CREATE OR REPLACE FUNCTION public.pay_payroll_run(_id uuid, _paid_from_account_id uuid, _pay_date date)
 RETURNS uuid LANGUAGE plpgsql SECURITY DEFINER SET search_path TO 'public'
AS $function$
DECLARE _run public.payroll_runs; _je uuid; _bal numeric;
BEGIN
  PERFORM public.require_access('payroll');
  SELECT * INTO _run FROM public.payroll_runs WHERE id = _id FOR UPDATE;
  IF _run.id IS NULL THEN RAISE EXCEPTION 'Payroll run not found'; END IF;
  IF _run.status <> 'approved' THEN RAISE EXCEPTION 'Only approved payroll runs can be paid'; END IF;
  SELECT balance INTO _bal FROM public.v_account_balances WHERE account_id = _paid_from_account_id;
  IF COALESCE(_bal,0) < _run.net_total THEN RAISE EXCEPTION 'Insufficient balance in the selected account'; END IF;

  _je := public.post_journal_entry(_pay_date, 'Salary payment ' || _run.run_number, 'payroll_payment', _id,
    jsonb_build_array(
      jsonb_build_object('account_id', public.account_id_by_code('2220'), 'debit', _run.net_total, 'credit', 0, 'description', 'Salary paid ' || _run.run_number),
      jsonb_build_object('account_id', _paid_from_account_id, 'debit', 0, 'credit', _run.net_total, 'description', 'Salary paid ' || _run.run_number)
    ));
  UPDATE public.payroll_runs SET status = 'paid', paid_at = now(), payment_journal_entry_id = _je, pay_date = _pay_date WHERE id = _id;
  INSERT INTO public.audit_logs (user_id, action, entity_type, entity_id, description)
  VALUES (auth.uid(), 'pay', 'payroll_run', _id, 'Paid payroll ' || _run.run_number);
  RETURN _je;
END; $function$;

CREATE OR REPLACE FUNCTION public.record_staff_advance(_staff_id uuid, _date date, _amount numeric, _paid_from_account_id uuid, _notes text)
 RETURNS uuid LANGUAGE plpgsql SECURITY DEFINER SET search_path TO 'public'
AS $function$
DECLARE _id uuid; _je uuid;
BEGIN
  PERFORM public.require_access('employees');
  IF _amount <= 0 THEN RAISE EXCEPTION 'Advance amount must be greater than zero'; END IF;
  INSERT INTO public.staff_advances (staff_id, advance_date, amount, outstanding, paid_from_account_id, notes)
  VALUES (_staff_id, _date, _amount, _amount, _paid_from_account_id, _notes) RETURNING id INTO _id;
  _je := public.post_journal_entry(_date, 'Staff advance', 'staff_advance', _id,
    jsonb_build_array(
      jsonb_build_object('account_id', public.account_id_by_code('1250'), 'debit', _amount, 'credit', 0, 'description', 'Advance to staff'),
      jsonb_build_object('account_id', _paid_from_account_id, 'debit', 0, 'credit', _amount, 'description', 'Advance to staff')
    ));
  UPDATE public.staff_advances SET journal_entry_id = _je WHERE id = _id;
  RETURN _id;
END; $function$;

CREATE OR REPLACE FUNCTION public.create_transfer(_transfer_date date, _from_account_id uuid, _to_account_id uuid, _amount numeric, _memo text)
 RETURNS uuid LANGUAGE plpgsql SECURITY DEFINER SET search_path TO 'public'
AS $function$
DECLARE _id uuid; _num text; _je uuid;
BEGIN
  PERFORM public.require_access('cash-bank');
  IF _amount <= 0 THEN RAISE EXCEPTION 'Transfer amount must be greater than zero'; END IF;
  IF _from_account_id = _to_account_id THEN RAISE EXCEPTION 'Source and destination must differ'; END IF;
  _num := public.next_number('transfer');
  INSERT INTO public.transfers (transfer_number, transfer_date, from_account_id, to_account_id, amount, memo)
  VALUES (_num, _transfer_date, _from_account_id, _to_account_id, _amount, _memo) RETURNING id INTO _id;
  _je := public.post_journal_entry(_transfer_date, 'Transfer ' || _num, 'transfer', _id,
    jsonb_build_array(
      jsonb_build_object('account_id', _to_account_id, 'debit', _amount, 'credit', 0, 'description', COALESCE(_memo,_num)),
      jsonb_build_object('account_id', _from_account_id, 'debit', 0, 'credit', _amount, 'description', COALESCE(_memo,_num))
    ));
  UPDATE public.transfers SET journal_entry_id = _je WHERE id = _id;
  RETURN _id;
END; $function$;

CREATE OR REPLACE FUNCTION public.reverse_journal_entry(_entry_id uuid, _entry_date date DEFAULT NULL::date, _memo text DEFAULT NULL::text)
 RETURNS uuid LANGUAGE plpgsql SECURITY DEFINER SET search_path TO 'public'
AS $function$
DECLARE _lines jsonb; _new uuid; _src public.journal_entries;
BEGIN
  PERFORM public.require_access('accounting');
  SELECT * INTO _src FROM public.journal_entries WHERE id = _entry_id;
  IF _src.id IS NULL THEN RAISE EXCEPTION 'Journal entry not found'; END IF;
  IF _src.status = 'reversed' THEN RAISE EXCEPTION 'Entry already reversed'; END IF;
  SELECT jsonb_agg(jsonb_build_object('account_id', account_id, 'debit', credit, 'credit', debit, 'description', description,
     'customer_id', customer_id, 'vendor_id', vendor_id, 'owner_id', owner_id))
  INTO _lines FROM public.journal_lines WHERE journal_entry_id = _entry_id;
  _new := public.post_journal_entry(COALESCE(_entry_date, CURRENT_DATE),
    COALESCE(_memo, 'Reversal of ' || _src.entry_number), 'reversal', _entry_id, _lines);
  UPDATE public.journal_entries SET status = 'reversed', reversed_by = _new WHERE id = _entry_id;
  UPDATE public.journal_entries SET reverses_entry_id = _entry_id WHERE id = _new;
  RETURN _new;
END; $function$;

GRANT EXECUTE ON FUNCTION public.can_edit(text) TO authenticated;
GRANT EXECUTE ON FUNCTION public.require_access(text) TO authenticated;