-- 0006: AR review fixes (forward-only — 0005 is already applied to the dev
-- DB, so every fix here is CREATE OR REPLACE / GRANT, never an edit to 0005).
--
-- FIX 1 (critical): over-application race. Two concurrent apply_credit() (or
-- receive_payment()) calls each applying to the SAME invoice could both pass
-- the deferred check_payment_application trigger, because each transaction's
-- own snapshot only sees its own uncommitted application row — both commit,
-- and the invoice ends up over-applied. issue_invoice() already takes
-- SELECT ... FOR UPDATE on its own invoice; receive_payment()/apply_credit()
-- did not lock the invoices they were APPLYING PAYMENT TO. Fixed by locking
-- every targeted invoice (FOR UPDATE) before inserting any application row,
-- so a second concurrent call serializes behind the first and its deferred
-- check then sees the first call's already-committed row.
--
-- FIX 2 (important): ensure_ar_account() picked min(id) among ANY account
-- named 'Accounts Receivable' — no active filter, no ambiguity check. Now
-- requires exactly one ACTIVE match, with loud English errors otherwise.
--
-- FIX 3 (important): payments had only SELECT granted to app, so an UPDATE
-- attempt hit a bare "permission denied" instead of payments_immutable()'s
-- English refusal. Granting UPDATE/DELETE lets the trigger do its job, same
-- as journal_entries/invoices/invoice_lines already do.
--
-- FIX 4 (minor): payment_unapplied_balance() now reports 0 for a voided
-- payment from inside the function itself (defense in depth for any future
-- caller, not just the ones that already filter status='active' themselves).

GRANT UPDATE, DELETE ON payments TO app;

CREATE OR REPLACE FUNCTION ensure_ar_account(p_company_id uuid) RETURNS bigint
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_account_id bigint;
  v_count int;
BEGIN
  SELECT ar_account_id INTO v_account_id FROM company_settings
   WHERE company_id = p_company_id FOR UPDATE;
  IF NOT FOUND THEN
    INSERT INTO company_settings (company_id) VALUES (p_company_id);
    v_account_id := NULL;
  END IF;

  IF v_account_id IS NULL THEN
    SELECT count(*), min(id) INTO v_count, v_account_id FROM accounts
     WHERE company_id = p_company_id AND type = 'asset' AND active
       AND name = 'Accounts Receivable';
    IF v_count = 0 THEN
      RAISE EXCEPTION
        'No account named ''Accounts Receivable'' exists — create one or set it in Settings';
    ELSIF v_count > 1 THEN
      RAISE EXCEPTION
        'More than one active account is named ''Accounts Receivable'' — set the one to use in Settings';
    END IF;
    UPDATE company_settings SET ar_account_id = v_account_id
     WHERE company_id = p_company_id;
  END IF;

  RETURN v_account_id;
END $$;

CREATE OR REPLACE FUNCTION payment_unapplied_balance(p_company_id uuid, p_payment_id bigint)
RETURNS numeric(19,4)
LANGUAGE sql STABLE AS $$
  SELECT CASE WHEN p.status = 'active' THEN
    (p.amount - coalesce((SELECT sum(amount) FROM payment_applications
                            WHERE company_id = p_company_id AND payment_id = p_payment_id), 0))
  ELSE 0 END::numeric(19,4)
    FROM payments p WHERE p.company_id = p_company_id AND p.id = p_payment_id
$$;

CREATE OR REPLACE FUNCTION receive_payment(p_company_id uuid, p_spec jsonb, p_user_id uuid)
RETURNS bigint
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_customer_id bigint;
  v_date date;
  v_amount numeric(19,4);
  v_deposit_account bigint;
  v_deposit_type text;
  v_memo text;
  v_apps jsonb;
  v_app jsonb;
  v_ar_account bigint;
  v_entry_id bigint;
  v_payment_id bigint;
  v_invoice_id bigint;
  v_app_amount numeric(19,4);
  v_inv_customer bigint;
  v_inv_status text;
  v_invoice_ids bigint[];
BEGIN
  IF current_company_id() IS DISTINCT FROM p_company_id THEN
    RAISE EXCEPTION 'session company does not match';
  END IF;

  v_customer_id := (p_spec->>'customer_id')::bigint;
  v_date := (p_spec->>'payment_date')::date;
  v_amount := (p_spec->>'amount')::numeric(19,4);
  v_deposit_account := (p_spec->>'deposit_account_id')::bigint;
  v_memo := p_spec->>'memo';
  v_apps := coalesce(p_spec->'applications', '[]'::jsonb);

  IF v_amount <= 0 THEN
    RAISE EXCEPTION 'payment amount must be positive';
  END IF;
  IF NOT EXISTS (
    SELECT 1 FROM customers WHERE company_id = p_company_id AND id = v_customer_id
  ) THEN
    RAISE EXCEPTION 'customer % not found', v_customer_id;
  END IF;

  SELECT type INTO v_deposit_type FROM accounts
   WHERE company_id = p_company_id AND id = v_deposit_account;
  IF v_deposit_type IS NULL THEN
    RAISE EXCEPTION 'account % not found', v_deposit_account;
  END IF;
  IF v_deposit_type <> 'asset' THEN
    RAISE EXCEPTION 'deposit account must be an asset account (account % is %)',
      v_deposit_account, v_deposit_type;
  END IF;

  v_ar_account := ensure_ar_account(p_company_id);

  INSERT INTO journal_entries (company_id, entry_date, memo, created_by)
  VALUES (p_company_id, v_date, coalesce(v_memo, 'Customer payment'), p_user_id)
  RETURNING id INTO v_entry_id;

  INSERT INTO journal_lines (company_id, entry_id, account_id, debit, credit)
  VALUES (p_company_id, v_entry_id, v_deposit_account, v_amount, 0),
         (p_company_id, v_entry_id, v_ar_account, 0, v_amount);

  PERFORM post_entry(p_company_id, v_entry_id);

  INSERT INTO payments
    (company_id, customer_id, payment_date, amount, deposit_account_id,
     posted_entry_id, memo, created_by)
  VALUES
    (p_company_id, v_customer_id, v_date, v_amount, v_deposit_account,
     v_entry_id, v_memo, p_user_id)
  RETURNING id INTO v_payment_id;

  -- FIX 1: lock every targeted invoice before inserting any application row,
  -- so a concurrent receive_payment()/apply_credit() on the same invoice
  -- serializes behind this transaction instead of racing the deferred check.
  SELECT array_agg(DISTINCT (elem->>'invoice_id')::bigint) INTO v_invoice_ids
    FROM jsonb_array_elements(v_apps) elem;
  IF v_invoice_ids IS NOT NULL THEN
    PERFORM 1 FROM invoices
     WHERE company_id = p_company_id AND id = ANY (v_invoice_ids)
     FOR UPDATE;
  END IF;

  FOR v_app IN SELECT * FROM jsonb_array_elements(v_apps)
  LOOP
    v_invoice_id := (v_app->>'invoice_id')::bigint;
    v_app_amount := (v_app->>'amount')::numeric(19,4);
    IF v_app_amount <= 0 THEN
      RAISE EXCEPTION 'application amount must be positive';
    END IF;

    SELECT customer_id, status INTO v_inv_customer, v_inv_status FROM invoices
     WHERE company_id = p_company_id AND id = v_invoice_id;
    IF NOT FOUND THEN
      RAISE EXCEPTION 'invoice % not found', v_invoice_id;
    END IF;
    IF v_inv_status <> 'open' THEN
      RAISE EXCEPTION 'invoice % is % — only open invoices accept payment',
        v_invoice_id, v_inv_status;
    END IF;
    IF v_inv_customer <> v_customer_id THEN
      RAISE EXCEPTION 'invoice % does not belong to this customer', v_invoice_id;
    END IF;

    INSERT INTO payment_applications (company_id, payment_id, invoice_id, amount)
    VALUES (p_company_id, v_payment_id, v_invoice_id, v_app_amount);
  END LOOP;

  RETURN v_payment_id;
END $$;

CREATE OR REPLACE FUNCTION apply_credit(
  p_company_id uuid, p_payment_id bigint, p_applications jsonb, p_user_id uuid
) RETURNS void
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_payment payments%ROWTYPE;
  v_apps jsonb;
  v_app jsonb;
  v_invoice_id bigint;
  v_amount numeric(19,4);
  v_inv_customer bigint;
  v_inv_status text;
  v_invoice_ids bigint[];
BEGIN
  IF current_company_id() IS DISTINCT FROM p_company_id THEN
    RAISE EXCEPTION 'session company does not match';
  END IF;

  SELECT * INTO v_payment FROM payments
   WHERE company_id = p_company_id AND id = p_payment_id
   FOR UPDATE;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'payment % not found', p_payment_id;
  END IF;
  IF v_payment.status <> 'active' THEN
    RAISE EXCEPTION 'payment % is void', p_payment_id;
  END IF;

  v_apps := coalesce(p_applications, '[]'::jsonb);

  -- FIX 1: same invoice-locking discipline as receive_payment() above.
  SELECT array_agg(DISTINCT (elem->>'invoice_id')::bigint) INTO v_invoice_ids
    FROM jsonb_array_elements(v_apps) elem;
  IF v_invoice_ids IS NOT NULL THEN
    PERFORM 1 FROM invoices
     WHERE company_id = p_company_id AND id = ANY (v_invoice_ids)
     FOR UPDATE;
  END IF;

  FOR v_app IN SELECT * FROM jsonb_array_elements(v_apps)
  LOOP
    v_invoice_id := (v_app->>'invoice_id')::bigint;
    v_amount := (v_app->>'amount')::numeric(19,4);
    IF v_amount <= 0 THEN
      RAISE EXCEPTION 'application amount must be positive';
    END IF;

    SELECT customer_id, status INTO v_inv_customer, v_inv_status FROM invoices
     WHERE company_id = p_company_id AND id = v_invoice_id;
    IF NOT FOUND THEN
      RAISE EXCEPTION 'invoice % not found', v_invoice_id;
    END IF;
    IF v_inv_status <> 'open' THEN
      RAISE EXCEPTION 'invoice % is % — only open invoices accept payment',
        v_invoice_id, v_inv_status;
    END IF;
    IF v_inv_customer <> v_payment.customer_id THEN
      RAISE EXCEPTION 'invoice % does not belong to this customer', v_invoice_id;
    END IF;

    INSERT INTO payment_applications (company_id, payment_id, invoice_id, amount)
    VALUES (p_company_id, p_payment_id, v_invoice_id, v_amount);
  END LOOP;
END $$;
