credit-limit-alert.sql

@App:name("CreditLimitReaching")

@App:description("Description of the plan")

@Store(type="rdbms",
       jdbc.url="jdbc:mysql://localhost:3306/foobank?useSSL=false",
       username="root",
       password="*****" ,
       jdbc.driver.name="com.mysql.jdbc.Driver")
@PrimaryKey("id")
define table past_transactions (id int, customer_id int, amount double);

@Store(type="rdbms",
       jdbc.url="jdbc:mysql://localhost:3306/foobank?useSSL=false",
       username="root",
       password="*****" ,
       jdbc.driver.name="com.mysql.jdbc.Driver")
@PrimaryKey("customer_id")
define table customers (customer_id int, credit_limit double);

define stream transactions (id int, customer_id int, amount double);

define stream alertStream(customer_id int, total double, credit_limit double);

@sink(type='log')
define stream smsStream (customer_id int);

@sink(type='log')
define stream emailStream (customer_id int);

@sink(type='log')
define stream callCenterStream (customer_id int);

@info(name='Calculate the total spend so far.')
from past_transactions as p join transactions as t
on p.customer_id == t.customer_id
select p.customer_id as customer_id, t.amount + sum(p.amount) as total
insert into tempStream;

@info(name='Enrich with the credit limit.')
from tempStream as t join customers as c
on t.customer_id == c.customer_id 
select t.customer_id, t.total, c.credit_limit
insert into alertStream;

--beginning of side effects

@info(name='Add to SMS stream')
from alertStream[total > credit_limit*0.5 and total < credit_limit*0.75]
select customer_id
insert into smsStream;

@info(name='Add to Email stream')
from alertStream[total > credit_limit*0.75 and total < credit_limit*0.9]
select customer_id
insert into emailStream;

@info(name='Add to call center stream')
from alertStream[total > credit_limit*0.9]
select customer_id
insert into callCenterStream;