INSERT INTO public.number_sequences (key, prefix, next_value)
VALUES ('wage', 'WG-', 1)
ON CONFLICT (key) DO NOTHING;

CREATE TABLE IF NOT EXISTS public.wage_payments (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  wage_number text NOT NULL UNIQUE,
  pay_date date NOT NULL DEFAULT current_date,
  staff_id uuid REFERENCES public.staff(id) ON DELETE SET NULL,
  wage_type text NOT NULL DEFAULT 'salary',
  expense_account_id uuid NOT NULL REFERENCES public.accounts(id),
  paid_from_account_id uuid NOT NULL REFERENCES public.accounts(id),
  amount numeric(18,2) NOT NULL,
  method text NOT NULL DEFAULT 'cash',
  reference text,
  description text,
  journal_entry_id uuid REFERENCES public.journal_entries(id),
  created_by uuid,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

GRANT SELECT, INSERT, UPDATE, DELETE ON public.wage_payments TO authenticated;
GRANT ALL ON public.wage_payments TO service_role;

ALTER TABLE public.wage_payments ENABLE ROW LEVEL SECURITY;

CREATE POLICY "wage_payments_read" ON public.wage_payments
  FOR SELECT TO authenticated USING (public.can_access('payroll'));

CREATE POLICY "wage_payments_write" ON public.wage_payments
  TO authenticated USING (public.can_edit('payroll')) WITH CHECK (public.can_edit('payroll'));

DROP TRIGGER IF EXISTS trg_wage_payments_updated ON public.wage_payments;
CREATE TRIGGER trg_wage_payments_updated BEFORE UPDATE ON public.wage_payments
  FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();

CREATE OR REPLACE FUNCTION public.record_wage_payment(
  _pay_date date,
  _expense_account_id uuid,
  _paid_from_account_id uuid,
  _amount numeric,
  _wage_type text DEFAULT 'salary',
  _staff_id uuid DEFAULT NULL,
  _method text DEFAULT 'cash',
  _reference text DEFAULT NULL,
  _description text DEFAULT NULL
) RETURNS uuid
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE _id uuid; _num text; _je uuid;
BEGIN
  PERFORM public.require_access('payroll');
  IF _amount IS NULL OR _amount <= 0 THEN
    RAISE EXCEPTION 'Wage amount must be greater than zero';
  END IF;
  _num := public.next_number('wage');

  INSERT INTO public.wage_payments (wage_number, pay_date, staff_id, wage_type,
    expense_account_id, paid_from_account_id, amount, method, reference, description, created_by)
  VALUES (_num, _pay_date, _staff_id, COALESCE(_wage_type, 'salary'),
    _expense_account_id, _paid_from_account_id, _amount, COALESCE(_method, 'cash'),
    _reference, _description, auth.uid())
  RETURNING id INTO _id;

  _je := public.post_journal_entry(_pay_date, 'Salaries & wages ' || _num, 'wages', _id,
    jsonb_build_array(
      jsonb_build_object('account_id', _expense_account_id, 'debit', _amount, 'credit', 0,
        'description', COALESCE(_description, _num)),
      jsonb_build_object('account_id', _paid_from_account_id, 'debit', 0, 'credit', _amount,
        'description', COALESCE(_description, _num))
    ));

  UPDATE public.wage_payments SET journal_entry_id = _je WHERE id = _id;

  INSERT INTO public.audit_logs (user_id, action, entity_type, entity_id, description)
  VALUES (auth.uid(), 'create', 'wage_payment', _id, 'Recorded wage payment ' || _num);

  RETURN _id;
END;
$$;

REVOKE ALL ON FUNCTION public.record_wage_payment(date, uuid, uuid, numeric, text, uuid, text, text, text) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.record_wage_payment(date, uuid, uuid, numeric, text, uuid, text, text, text) TO authenticated;