-- Verify kakeibo:delete_item on pg

BEGIN;

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

SET search_path = kakeibo;

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

    PERFORM * FROM items WHERE id = v_item_id;
    IF NOT FOUND THEN
      RAISE EXCEPTION 'Inserted data is not found.';
    END IF;

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

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

ROLLBACK;