<?xml version="1.0"?>

<sqlgg>
 <stmt name="create_person" sql="CREATE TABLE IF NOT EXISTS person (id INTEGER PRIMARY KEY AUTO_INCREMENT,name TEXT,surname TEXT)" category="DDL" kind="create" target="person" cardinality="0">
  <in/>
  <out/>
 </stmt>
 <stmt name="add_person" sql="INSERT INTO person (name,surname) VALUES (@name,@surname)" category="DML" kind="insert" target="person" cardinality="0">
  <in>
   <value name="name" type="Text" nullable="true"/>
   <value name="surname" type="Text" nullable="true"/>
  </in>
  <out/>
 </stmt>
 <stmt name="create_money" sql="CREATE TABLE IF NOT EXISTS money (src INTEGER, dst INTEGER, amount INTEGER)" category="DDL" kind="create" target="money" cardinality="0">
  <in/>
  <out/>
 </stmt>
 <stmt name="add_money" sql="INSERT INTO money VALUES (@src,@dst,@amount)" category="DML" kind="insert" target="money" cardinality="0">
  <in>
   <value name="src" type="Int" nullable="true"/>
   <value name="dst" type="Int" nullable="true"/>
   <value name="amount" type="Int" nullable="true"/>
  </in>
  <out/>
 </stmt>
 <stmt name="calc_total" sql="SELECT CONCAT(name,' ',surname) AS fullname, SUM(amount) as total FROM person JOIN money ON src = id GROUP BY id" category="DQL" kind="select" cardinality="n">
  <in/>
  <out>
   <value name="fullname" type="Text"/>
   <value name="total" type="Int" nullable="true"/>
  </out>
 </stmt>
 <stmt name="list_donors" sql="SELECT DISTINCT surname FROM person JOIN money ON src = id AND dst = (SELECT id FROM person WHERE surname LIKE @_0) LIMIT @limit" category="DQL" kind="select" cardinality="n">
  <in>
   <value name="_0" type="Text"/>
   <value name="limit" type="Int"/>
  </in>
  <out>
   <value name="surname" type="Text" nullable="true"/>
  </out>
 </stmt>
 <stmt name="drop_person" sql="DROP TABLE IF EXISTS person" category="DDL" kind="drop" target="person" cardinality="0">
  <in/>
  <out/>
 </stmt>
 <stmt name="drop_money" sql="DROP TABLE IF EXISTS money" category="DDL" kind="drop" target="money" cardinality="0">
  <in/>
  <out/>
 </stmt>
</sqlgg>
