-- Deploy kakeibo:insert_item to pg
-- requires: schema
-- requires: items

BEGIN;

CREATE OR REPLACE FUNCTION kakeibo.insert_item(
  p_transaction_id INTEGER,
  p_category TEXT,
  p_subcategory TEXT,
  p_amount INTEGER,
  p_note TEXT
) RETURNS INTEGER AS $$
  DECLARE
    inserted_id INTEGER;
  BEGIN
    INSERT INTO kakeibo.items (transaction_id, category, subcategory, amount, note)
    VALUES (p_transaction_id, p_category, p_subcategory, p_amount, p_note)
           RETURNING id INTO inserted_id;

    RETURN inserted_id;
  END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

COMMIT;
