CREATE OR REPLACE FUNCTION public.validate_account_row()
RETURNS trigger
LANGUAGE plpgsql
SET search_path TO 'public'
AS $function$
DECLARE
  _cat text; _blocks int[]; _code_num int; _dup text; _cursor uuid; _depth int := 0;
BEGIN
  NEW.code := btrim(NEW.code);
  NEW.name := btrim(NEW.name);

  IF NEW.name IS NULL OR NEW.name = '' THEN
    RAISE EXCEPTION 'Account name is required';
  END IF;

  IF NEW.code !~ '^[0-9]{4}(-[0-9]{1,3})?$' THEN
    RAISE EXCEPTION 'Account code "%" is invalid. Use 4 digits, optionally with a -1 suffix (e.g. 5310 or 5310-1).', NEW.code;
  END IF;

  SELECT a.code || ' ' || a.name INTO _dup
    FROM public.accounts a
   WHERE lower(a.code) = lower(NEW.code) AND a.id <> NEW.id
   LIMIT 1;
  IF _dup IS NOT NULL THEN
    RAISE EXCEPTION 'Account code % is already used by %', NEW.code, _dup;
  END IF;

  SELECT t.category INTO _cat FROM public.account_types t WHERE t.id = NEW.account_type_id;
  IF _cat IS NULL THEN
    RAISE EXCEPTION 'Pick a valid account type';
  END IF;
  _code_num := (split_part(NEW.code, '-', 1))::int;
  _blocks := CASE _cat
    WHEN 'asset' THEN ARRAY[1] WHEN 'liability' THEN ARRAY[2] WHEN 'equity' THEN ARRAY[3]
    WHEN 'revenue' THEN ARRAY[4] WHEN 'expense' THEN ARRAY[5,6] ELSE NULL END;
  IF _blocks IS NOT NULL AND NOT ((_code_num / 1000) = ANY (_blocks)) THEN
    RAISE EXCEPTION '% accounts must use codes starting with % ', _cat, array_to_string(_blocks, ' or ');
  END IF;

  IF NEW.parent_id IS NOT NULL THEN
    IF NEW.parent_id = NEW.id THEN
      RAISE EXCEPTION 'An account cannot be its own parent';
    END IF;
    IF EXISTS (
      SELECT 1 FROM public.accounts p
       JOIN public.account_types pt ON pt.id = p.account_type_id
       WHERE p.id = NEW.parent_id AND pt.category <> _cat
    ) THEN
      RAISE EXCEPTION 'Parent account must belong to the same category (%)', _cat;
    END IF;
    _cursor := NEW.parent_id;
    WHILE _cursor IS NOT NULL LOOP
      _depth := _depth + 1;
      IF _cursor = NEW.id THEN
        RAISE EXCEPTION 'That parent is a sub-account of this account — it would create a loop';
      END IF;
      IF _depth > 20 THEN
        RAISE EXCEPTION 'Account hierarchy is nested too deeply';
      END IF;
      SELECT parent_id INTO _cursor FROM public.accounts WHERE id = _cursor;
    END LOOP;
  END IF;

  IF TG_OP = 'UPDATE' THEN
    IF OLD.is_system AND NOT (
      NEW.code = OLD.code AND NEW.account_type_id = OLD.account_type_id
      AND NEW.is_system AND NEW.balance_locked = OLD.balance_locked
    ) THEN
      RAISE EXCEPTION 'Account % % is a system control account and cannot be renumbered or unlocked', OLD.code, OLD.name;
    END IF;
    IF (OLD.is_system OR OLD.balance_locked) AND OLD.is_active AND NOT NEW.is_active THEN
      RAISE EXCEPTION 'Account % % is a control account and cannot be deactivated', OLD.code, OLD.name;
    END IF;
    IF NOT OLD.is_system AND NEW.is_system THEN
      RAISE EXCEPTION 'Custom accounts cannot be turned into system accounts';
    END IF;
    IF NOT OLD.balance_locked AND NEW.balance_locked AND NOT public.is_admin() THEN
      RAISE EXCEPTION 'Only an admin can mark an account read-only';
    END IF;
  ELSIF TG_OP = 'INSERT' THEN
    IF NEW.is_system OR NEW.balance_locked THEN
      IF NOT public.is_admin() THEN
        RAISE EXCEPTION 'Only the accounting engine can create system or read-only accounts';
      END IF;
    END IF;
  END IF;

  RETURN NEW;
END; $function$;

REVOKE EXECUTE ON FUNCTION public.validate_account_row() FROM PUBLIC, anon, authenticated;