--Create a TYPE T_FLIGHTS to grab the information from the SPFLI and STICKET tables.
 
DROP TYPE T_FLIGHTS;
CREATE TYPE T_FLIGHTS AS TABLE (
CARRID NVARCHAR(3),
CUSTOMID NVARCHAR(8),
CITYFROM NVARCHAR(20),
CITYTO NVARCHAR(20)
);
 
--Create a TYPE FLIGHT_MODEL_T and a table FLIGHT_MODEL to get and store the model in the database.
 
DROP TYPE FLIGHT_MODEL_T;
CREATE TYPE FLIGHT_MODEL_T AS TABLE (
ID INTEGER,
DESCRIPTION VARCHAR(255),
MODEL BLOB
);
 
DROP TABLE FLIGHT_MODEL;
CREATE COLUMN TABLE FLIGHT_MODEL (
ID INTEGER,
DESCRIPTION VARCHAR(255),
MODEL BLOB
);
 
--This R procedure will receive the T_FLIGHTS information, create table containing a field call FLIGHT_NAME 
--that will contain the concatenation
--of the CARRID, CITYFROM and CITYTO. ie: AA-NEW YORK-SAN FRANCISCO.
--We're going to convert the table into a data.frame, so all the similar values in FLIGHT_NAME are going to 
--be summarized.
--Using the subset() function, we're going to get rid of all the FLIGHT_NAME's that has a frequency lower 
--or equal than 0.
--We're going to use the nrow() function to count all the FLIGHT_NAME occurrences and multiply that by 
--10 (Stored in f_rows)
--We're going to use the sum() function to sum all the frequencies and the divide it by f_rows 
--(Stored in f_sum)
--We're going to use the mapply() function to divide each of the frequencies by f_sum
--We're going to use the order() function to sort by FLIGHT_NAME
--We're going to use the colnames() function to assign names to our data.frame
--We're going to use the lm() function to generate a Linear Regression based on the FLIGHT_NAME 
--and it's frequency
--Finally, we're going to use the generateRobjColumn() custom created function to store the result of the 
--model in the buffer.

DROP PROCEDURE FLIGHT_TRAIN_PROC;
CREATE PROCEDURE FLIGHT_TRAIN_PROC (IN traininput "T_FLIGHTS", OUT modelresult FLIGHT_MODEL_T)
LANGUAGE RLANG AS
BEGIN
generateRobjColumn <- function(...){
          result <- as.data.frame(cbind(
                    lapply(
                              list(...),
                              function(x) if (is.null(x)) NULL else serialize(x, NULL)
                    )
          ))
          names(result) <- NULL
          names(result[[1]]) <- NULL
          result
}
tab<-table(FLIGHT_NAME=paste(traininput$CARRID,traininput$CITYFROM,traininput$CITYTO,sep="-"))
df<-data.frame(tab)
ss<-subset(df,(df$Freq>0))
freq<-ss$Freq
f_rows<-(nrow(ss)) * 10
fsum<-sum(freq) / f_rows
ss$Freq<-mapply("/",ss$Freq, fsum)
flights<-ss[order(ss$FLIGHT_NAME),]
colnames(flights)<-c("FLIGHT_NAME","FREQUENCY")
lmModel<-lm(FREQUENCY ~ FLIGHT_NAME,data=flights)
modelresult<-data.frame(
ID=c(1),
DESCRIPTION=c("Flight Model"),
MODEL=generateRobjColumn(lmModel)
)
END;
 
--This SQLSCRIPT procedure will grab all the needed information from the tables SPFLI and STICKET 
--and will assign it to flights
--We're going to call the R procedure FLIGHT_TRAIN_PROC
--We're going to do an INSERT to finally store the model from the buffer into the database
 
DROP PROCEDURE POPULATE_FLIGHTS;
CREATE PROCEDURE POPULATE_FLIGHTS ()
LANGUAGE SQLSCRIPT AS
BEGIN
flights = SELECT SPFLI.CARRID, CUSTOMID, CITYFROM, CITYTO
             FROM SFLIGHT.SPFLI INNER JOIN SFLIGHT.STICKET
             ON SPFLI.CARRID = STICKET.CARRID
             AND SPFLI.CONNID = STICKET.CONNID;
CALL FLIGHT_TRAIN_PROC(:flights, FLIGHT_MODEL_T);
INSERT INTO "FLIGHT_MODEL" SELECT * FROM :FLIGHT_MODEL_T;
END;
 
CALL POPULATE_FLIGHTS();

--We're going to create a TYPE T_PREDICTED_FLIGHTS and a table PREDICTED_FLIGHTS to store the information
--of the current number of flights and
--the estimated (according to our prediction) number of flights
 
DROP TYPE T_PREDICTED_FLIGHTS;
CREATE TYPE T_PREDICTED_FLIGHTS AS TABLE (
CUSTOMID NVARCHAR(8),
FLIGHT_NAME NVARCHAR(60),
FREQUENCY INTEGER,
PREDICTED INTEGER
);
 
DROP TABLE PREDICTED_FLIGHTS;
CREATE TABLE PREDICTED_FLIGHTS (
CUSTOMID NVARCHAR(8),
FLIGHT_NAME NVARCHAR(60),
FREQUENCY INTEGER,
PREDICTED INTEGER
);
 
--In this R procedure, we're going to receive the flight for a given customer, the model stored in the database 
--and we're going to return the result so it can be
--stored in our PREDICTED_FLIGHTS table.
--We're going to use the unserialize() function to extract the model.
--We're going to create a table containing a field call FLIGHT_NAME that will contain the concatenation of the 
--CARRID, CITYFROM and CITYTO.
--ie: AA-NEW YORK-SAN FRANCISCO. and also the CUSTOMID
--We're going to convert the table into a data.frame, so all the similar values in FLIGHT_NAME are going to be 
--summarized.
--We're going to use the colnames() function to assign names to our data.frame
--We're going to use the nrow() function to get the number of records in the data.frame (Stored in dfrows)
--We're going to use the rep() function to repeat the CUSTOMID value of the first record dfrows times
--We're going to use the predict() function to predict the amount of flights based on our model (retrieved 
--from the database) and the new data that we recovered
--Finally, we're going to create a data.frame containing all the information that should be stored in our 
--table PREDICTED_FLIGHTS

DROP PROCEDURE USE_FLIGHT;
CREATE PROCEDURE USE_FLIGHT(IN flights T_FLIGHTS, IN modeltbl FLIGHT_MODEL_T, OUT out_flights T_PREDICTED_FLIGHTS)
LANGUAGE RLANG AS
BEGIN
lmModel<-unserialize(modeltbl$MODEL[[1]])
tab<-table(FLIGHT_NAME=paste(flights$CARRID,flights$CITYFROM,flights$CITYTO,sep="-"),CUSTOMID=flights$CUSTOMID)
df<-data.frame(tab)
colnames(df)<-c("FLIGHT_NAME","CUSTOMID","FREQUENCY")
dfrows<-nrow(df)
customid<-rep(df$CUSTOMID[1],dfrows)
prediction=predict(lmModel,df,interval="none")
out_flights<-data.frame(CUSTOMID=customid,FLIGHT_NAME=df$FLIGHT_NAME,FREQUENCY=df$FREQUENCY,PREDICTED=prediction)
END;
 
--This SQLSCRIPT procedure will select the information from the FLIGHT_MODEL table and store in the flight_model 
--variable
--We're going to select all the needed information from the table SPFLI and STICKET based on the customer ID number
--We're going to call the R procedure USE_FLIGHT and it will return us the PREDICTED_FLIGHTS that we're going to 
--store in the database
 
DROP PROCEDURE GET_FLIGHTS;
CREATE PROCEDURE GET_FLIGHTS(IN customId NVARCHAR(8))
LANGUAGE SQLSCRIPT AS
BEGIN
flight_model = SELECT * FROM FLIGHT_MODEL;
out_flights = SELECT SPFLI.CARRID, CUSTOMID, CITYFROM, CITYTO FROM SFLIGHT.SPFLI INNER JOIN SFLIGHT.STICKET
                   ON SPFLI.CARRID = STICKET.CARRID AND SPFLI.CONNID = STICKET.CONNID
                   WHERE CUSTOMID = :customId;
CALL USE_FLIGHT(:out_flights, :flight_model, PREDICTED_FLIGHTS);
INSERT INTO "PREDICTED_FLIGHTS" SELECT * FROM :PREDICTED_FLIGHTS;
END;

CALL GET_FLIGHTS('00000156');
CALL GET_FLIGHTS('00002078');
CALL GET_FLIGHTS('00002463');