-- 1) Strict validation for account codes / system protection ------------------
CREATE OR REPLACE FUNCTION public.validate_account_row()
RETURNS trigger
LANGUAGE plpgsql
SET search_path = public
AS $$
DECLARE
  _cat text; _block int; _code_num int; _dup text;
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;

  -- code block must match the account type category (1xxx assets .. 5xxx expenses)
  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;
  _block := CASE _cat
    WHEN 'asset' THEN 1 WHEN 'liability' THEN 2 WHEN 'equity' THEN 3
    WHEN 'revenue' THEN 4 WHEN 'expense' THEN 5 ELSE NULL END;
  IF _block IS NOT NULL AND (_code_num / 1000) <> _block THEN
    RAISE EXCEPTION '% accounts must use codes in the %000-%999 range', _cat, _block, _block;
  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;
  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 NOT OLD.is_system AND NEW.is_system THEN
      RAISE EXCEPTION 'Custom accounts cannot be turned into system accounts';
    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; $$;

DROP TRIGGER IF EXISTS trg_accounts_validate ON public.accounts;
CREATE TRIGGER trg_accounts_validate
BEFORE INSERT OR UPDATE ON public.accounts
FOR EACH ROW EXECUTE FUNCTION public.validate_account_row();

CREATE OR REPLACE FUNCTION public.protect_system_account_delete()
RETURNS trigger
LANGUAGE plpgsql
SET search_path = public
AS $$
BEGIN
  IF OLD.is_system OR OLD.balance_locked THEN
    RAISE EXCEPTION 'Account % % is a system account and cannot be deleted', OLD.code, OLD.name;
  END IF;
  IF EXISTS (SELECT 1 FROM public.journal_lines l WHERE l.account_id = OLD.id) THEN
    RAISE EXCEPTION 'Account % % has ledger history — deactivate it instead of deleting', OLD.code, OLD.name;
  END IF;
  RETURN OLD;
END; $$;

DROP TRIGGER IF EXISTS trg_accounts_protect_delete ON public.accounts;
CREATE TRIGGER trg_accounts_protect_delete
BEFORE DELETE ON public.accounts
FOR EACH ROW EXECUTE FUNCTION public.protect_system_account_delete();

-- 2) Audit trail for custom account changes ----------------------------------
CREATE OR REPLACE FUNCTION public.audit_account_change()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  _before jsonb; _after jsonb; _action text; _row public.accounts;
  _changed text[];
BEGIN
  IF TG_OP = 'INSERT' THEN
    _action := 'account_created'; _row := NEW;
    _after := to_jsonb(NEW) - 'created_at' - 'updated_at';
  ELSIF TG_OP = 'UPDATE' THEN
    _action := 'account_updated'; _row := NEW;
    _before := to_jsonb(OLD) - 'created_at' - 'updated_at';
    _after := to_jsonb(NEW) - 'created_at' - 'updated_at';
    IF _before = _after THEN RETURN NEW; END IF;
    SELECT array_agg(key) INTO _changed
      FROM jsonb_each_text(_after) a
     WHERE _before->>a.key IS DISTINCT FROM a.value;
  ELSE
    _action := 'account_deleted'; _row := OLD;
    _before := to_jsonb(OLD) - 'created_at' - 'updated_at';
  END IF;

  INSERT INTO public.audit_logs (user_id, action, entity_type, entity_id, description, metadata)
  VALUES (
    auth.uid(), _action, 'account', _row.id,
    _row.code || ' ' || _row.name
      || CASE WHEN _changed IS NOT NULL THEN ' — changed: ' || array_to_string(_changed, ', ') ELSE '' END,
    jsonb_build_object('before', _before, 'after', _after, 'changed_fields', to_jsonb(COALESCE(_changed, '{}'::text[])))
  );
  RETURN COALESCE(NEW, OLD);
END; $$;

DROP TRIGGER IF EXISTS trg_accounts_audit ON public.accounts;
CREATE TRIGGER trg_accounts_audit
AFTER INSERT OR UPDATE OR DELETE ON public.accounts
FOR EACH ROW EXECUTE FUNCTION public.audit_account_change();

REVOKE ALL ON FUNCTION public.validate_account_row() FROM PUBLIC, anon;
REVOKE ALL ON FUNCTION public.protect_system_account_delete() FROM PUBLIC, anon;
REVOKE ALL ON FUNCTION public.audit_account_change() FROM PUBLIC, anon;

-- 3) Posting eligibility rules ------------------------------------------------
CREATE OR REPLACE FUNCTION public.assert_postable_accounts(_lines jsonb, _source_type text)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE _msg text;
BEGIN
  -- unknown account
  SELECT 'Journal line references an account that no longer exists'
    INTO _msg
    FROM jsonb_array_elements(_lines) l
   WHERE NOT EXISTS (SELECT 1 FROM public.accounts a WHERE a.id = (l->>'account_id')::uuid)
   LIMIT 1;
  IF _msg IS NOT NULL THEN RAISE EXCEPTION '%', _msg; END IF;

  -- inactive
  SELECT 'Account ' || a.code || ' ' || a.name || ' is inactive. Reactivate it in the chart of accounts or pick another account.'
    INTO _msg
    FROM jsonb_array_elements(_lines) l
    JOIN public.accounts a ON a.id = (l->>'account_id')::uuid
   WHERE NOT a.is_active
   LIMIT 1;
  IF _msg IS NOT NULL THEN RAISE EXCEPTION '%', _msg; END IF;

  -- heading / parent accounts never receive postings
  SELECT 'Account ' || a.code || ' ' || a.name || ' is a heading account with sub-accounts. Post to one of its detail accounts instead.'
    INTO _msg
    FROM jsonb_array_elements(_lines) l
    JOIN public.accounts a ON a.id = (l->>'account_id')::uuid
   WHERE EXISTS (SELECT 1 FROM public.accounts c WHERE c.parent_id = a.id)
   LIMIT 1;
  IF _msg IS NOT NULL THEN RAISE EXCEPTION '%', _msg; END IF;

  -- read-only balances only move through engine postings / reversals
  IF COALESCE(_source_type, 'manual') = 'manual' THEN
    SELECT 'Account ' || a.code || ' ' || a.name || ' has a read-only balance. Reverse the original journal entry instead of posting a manual adjustment.'
      INTO _msg
      FROM jsonb_array_elements(_lines) l
      JOIN public.accounts a ON a.id = (l->>'account_id')::uuid
     WHERE a.balance_locked
     LIMIT 1;
    IF _msg IS NOT NULL THEN RAISE EXCEPTION '%', _msg; END IF;
  END IF;
END; $$;

REVOKE ALL ON FUNCTION public.assert_postable_accounts(jsonb, text) FROM PUBLIC, anon;
GRANT EXECUTE ON FUNCTION public.assert_postable_accounts(jsonb, text) TO authenticated, service_role;

CREATE OR REPLACE FUNCTION public.post_journal_entry(_entry_date date, _memo text, _source_type text, _source_id uuid, _lines jsonb)
 RETURNS uuid
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'public'
AS $function$
DECLARE
  _je_id uuid; _dr numeric(18,2) := 0; _cr numeric(18,2) := 0; _line jsonb; _i int := 0;
  _closed boolean;
BEGIN
  PERFORM public.require_access('accounting');
  IF _lines IS NULL OR jsonb_array_length(_lines) < 2 THEN
    RAISE EXCEPTION 'A journal entry requires at least two lines';
  END IF;
  SELECT true INTO _closed FROM public.fiscal_periods
   WHERE status = 'closed' AND _entry_date BETWEEN start_date AND end_date LIMIT 1;
  IF _closed THEN RAISE EXCEPTION 'The fiscal period for % is closed', _entry_date; END IF;

  PERFORM public.assert_postable_accounts(_lines, _source_type);

  FOR _line IN SELECT * FROM jsonb_array_elements(_lines) LOOP
    _dr := _dr + COALESCE((_line->>'debit')::numeric, 0);
    _cr := _cr + COALESCE((_line->>'credit')::numeric, 0);
  END LOOP;
  IF round(_dr,2) <> round(_cr,2) THEN
    RAISE EXCEPTION 'Unbalanced journal entry: debit % <> credit %', _dr, _cr;
  END IF;
  IF round(_dr,2) = 0 THEN RAISE EXCEPTION 'Journal entry total cannot be zero'; END IF;

  INSERT INTO public.journal_entries (entry_number, entry_date, memo, source_type, source_id, status, total_debit, total_credit, created_by)
  VALUES (public.next_number('journal'), _entry_date, _memo, COALESCE(_source_type,'manual'), _source_id, 'posted', round(_dr,2), round(_cr,2), auth.uid())
  RETURNING id INTO _je_id;

  FOR _line IN SELECT * FROM jsonb_array_elements(_lines) LOOP
    _i := _i + 1;
    INSERT INTO public.journal_lines (journal_entry_id, account_id, debit, credit, description, customer_id, vendor_id, owner_id, line_no)
    VALUES (
      _je_id,
      (_line->>'account_id')::uuid,
      round(COALESCE((_line->>'debit')::numeric,0),2),
      round(COALESCE((_line->>'credit')::numeric,0),2),
      _line->>'description',
      NULLIF(_line->>'customer_id','')::uuid,
      NULLIF(_line->>'vendor_id','')::uuid,
      NULLIF(_line->>'owner_id','')::uuid,
      _i
    );
  END LOOP;
  RETURN _je_id;
END; $function$;

REVOKE ALL ON FUNCTION public.post_journal_entry(date, text, text, uuid, jsonb) FROM PUBLIC, anon;
GRANT EXECUTE ON FUNCTION public.post_journal_entry(date, text, text, uuid, jsonb) TO authenticated, service_role;