import ballerina/io;
import ballerina/jdbc;
import ballerina/mysql;

// Create an endpoint for the first database named testdb1. Since this endpoint
// participates in a distributed transaction, the `isXA` property should be true.
endpoint mysql:Client testDB1 {
    host: "localhost",
    port: 3306,
    name: "customerdb",
    username: "root",
    password: "123",
    poolOptions: { maximumPoolSize: 5 },
    dbOptions: { useSSL: false, isXA: true }
};


// Create an endpoint for the second database named testdb2. Since this endpoint
// participates in a distributed transaction, the `isXA` property should be true.
endpoint jdbc:Client testDB2 {
    url: "jdbc:oracle:thin:@localhost:49161:xe",
    username: "system",
    password: "oracle",
    poolOptions: { maximumPoolSize: 5, isXA: true }
};

function main(string... args) {
    // Create the table named CUSTOMER in the first database.
    var ret = testDB1->update("CREATE TABLE IF NOT EXISTS CUSTOMER (ID INT AUTO_INCREMENT, NAME VARCHAR(30), PRIMARY KEY (ID))");
    handleUpdate(ret, "Create CUSTOMER table");
    // Create the table named SALARY in the second database.
    ret = testDB2->update("CREATE TABLE SALARY (ID INT, VALUE FLOAT)");
    handleUpdate(ret, "Create SALARY table");

    runSuccessfulScenario();
    runFailureScenario();
}

function runSuccessfulScenario() {
    // Begins the transaction.
    transaction with oncommit = onCommitFunction,
    onabort = onAbortFunction {
    // This is the first action to participate in the transaction. It inserts
    // customer name to the first DB and gets the generated key.
        var retWithKey = testDB1->updateWithGeneratedKeys("INSERT INTO CUSTOMER(NAME) VALUES('Anne')", ());
        string generatedKey;
        match retWithKey {
            (int, string[]) y => {
                var (count, ids) = y;
                generatedKey = ids[0];
                io:println("Inserted row count: " + count);
                io:println("Generated key: " + generatedKey);
            }
            error err => io:println("Insert to student table failed: "
                    + err.message);
        }

        //Converte the returned key into integer.
        var ret = <int>generatedKey;
        int key = -1;
        match ret {
            int retInt => key = retInt;
            error err => io:println("Converting key to string failed: "
                    + err.message);
        }
        io:println("Generated key for the inserted row: " + key);
        // This is the second action to participate in the transaction. It inserts the
        // salary info to the second DB along with the key generated in the first DB.
        ret = testDB2->update("INSERT INTO SALARY (ID, VALUE) VALUES (?, ?)",
            key, 2500);
        handleUpdate(ret, "Insert to SALARY table");
    } onretry {
        io:println("Retrying transaction");
    }
}

function runFailureScenario() {
    // Begins the transaction.
    transaction with oncommit = onCommitFunction,
    onabort = onAbortFunction {
    // This is the first action to participate in the transaction. It inserts
    // customer name to the first DB and gets the generated key.
        var retWithKey = testDB1->updateWithGeneratedKeys("INSERT INTO CUSTOMER(NAME) VALUES('Anne')", ());
        string generatedKey;
        match retWithKey {
            (int, string[]) y => {
                var (count, ids) = y;
                generatedKey = ids[0];
                io:println("Inserted row count: " + count);
                io:println("Generated key: " + generatedKey);
            }
            error err => io:println("Insert to student table failed: "
                    + err.message);
        }

        //Converte the returned key into integer.
        var ret = <int>generatedKey;
        int key = -1;
        match ret {
            int retInt => key = retInt;
            error err => io:println("Converting key to string failed: "
                    + err.message);
        }
        io:println("Generated key for the inserted row: " + key);
        // This is the second action to participate in the transaction.
        // We have written a failing query on purpose here.
        // Once this fails, the previous operation also should be rolled-back.
        // To verify this, you can connect to testDB1 and check whether data is inserted.
        ret = testDB2->update("INSERT INTO INVALID_TABLE (ID, VALUE) VALUES (?, ?)",
            key, 2500);
        handleUpdate(ret, "Insert to SALARY table");
    } onretry {
        io:println("Retrying transaction");
    }
}


function onCommitFunction(string transactionId) {
    io:println("Transaction: " + transactionId + " committed");
}

function onAbortFunction(string transactionId) {
    io:println("Transaction: " + transactionId + " aborted");
}

// Function to handle return of the update operation.
function handleUpdate(int|error returned, string message) {
    match returned {
        int retInt => io:println(message + " status: " + retInt);
        error err => io:println(message + " failed: " + err.message);
    }
}