-- Verify kakeibo:delete_transaction on pg

BEGIN;

SELECT has_function_privilege('kakeibo.delete_transaction(INTEGER)', 'EXECUTE');

SET search_path = kakeibo;

DO $$
  DECLARE
    v_transaction_id INTEGER;
    v_transaction_id_2 INTEGER;
    v_item_id INTEGER;
  BEGIN
    SELECT insert_transaction(0, '2023-03-15', 'テスト') INTO v_transaction_id;
    SELECT insert_transaction(1, '2023-03-15', 'テスト2') INTO v_transaction_id_2;
    SELECT insert_item(v_transaction_id, 'カテ', 'サブカテ', 298, 'メモ') INTO v_item_id;

    BEGIN
      PERFORM delete_transaction(v_transaction_id);
    EXCEPTION
      WHEN foreign_key_violation THEN
    END;
    PERFORM * FROM transactions WHERE id = v_transaction_id;
    IF NOT FOUND THEN
      RAISE EXCEPTION 'Unexpected delete data.';
    END IF;

    PERFORM delete_item(v_item_id);
    PERFORM delete_transaction(v_transaction_id);
    PERFORM * FROM transactions WHERE id = v_transaction_id;
    IF FOUND THEN
      RAISE EXCEPTION 'Deleted data still exists.';
    END IF;

    PERFORM * FROM transactions WHERE id = v_transaction_id_2;
    IF NOT FOUND THEN
      RAISE EXCEPTION 'Unexpected delete data.';
    END IF;
  END
$$;

ROLLBACK;