import ballerina/io;
import ballerina/config;
import ballerina/log;
import ballerina/mysql;
import ballerina/sql;

endpoint mysql:Client databaseConnector {
    host: "localhost",
    port: 3306,
    name: "github_issues",
    username: "root",
    password: "password",
    poolOptions: { maximumPoolSize:5},
    dbOptions: { useSSL: false }
};

type Person record {
    int id;
    int age = -1;
    float salary;
    string name;
    boolean married;
};

type Oxder record { //I know... Order messes up the SQL
    int personId;
    int orderId;
    string items;
    float amount;
};

type OrderDetails record {
    int orderId;
    string personName;
    string items;
    float amount;
};

public function main() {
    var personTableSelectResult = databaseConnector->select("SELECT * FROM Person", Person);
    match personTableSelectResult {
        table personTable => {
            printTable("SELECT operation success", "Person", personTable);

            var orderTableSelectResult = databaseConnector->select("SELECT * FROM Oxder", Oxder);
            match orderTableSelectResult {
                table orderTable => {
                    printTable("SELECT operation success", "Order", orderTable);
                    table<OrderDetails> joinedTable =
                      from personTable as tempPersonTbl join orderTable as tempOrderTbl
                      on tempPersonTbl.id == tempOrderTbl.personId
                      select tempPersonTbl.name as personName,
                      tempOrderTbl.orderId as orderId,
                      tempOrderTbl.items as items,
                      tempOrderTbl.amount as amount;
                    printTable("Joined tb", "JOINED", joinedTable);
                }
                error e => {
                    log:printError("Error reading ORDER table: " + e.message);
                }
            }
        }
        error e => {
            log:printError("Error reading PERSON table: " + e.message);
        }
    }
}

function printTable(string stmt, string tableName, table returnedTable) {
    var retData = <json>returnedTable;
    io:println(stmt);
    io:print(tableName);
    match retData {
        json jsonRes => io:println(io:sprintf("%s", jsonRes));
        error e => io:println("Error in table to json conversion");
    }
}