/*** stored Procedure:

CREATE OR REPLACE PROCEDURE sql_select_data_procedure(
    fa integer,
    hold bool,
    INOUT blksize integer,
    INOUT fadata bytea
) LANGUAGE plpgsql AS 
$$ 
BEGIN 
    IF (hold) THEN
        PERFORM pg_advisory_lock(fa);
    END IF; 
    SELECT blk_size, data INTO blksize, fadata 
    FROM fs_data 
    WHERE file_address = fa; 
END; 
$$;

***/
#include <stdio.h>
#include <stdlib.h>
#include <stdbool.h>
#include <libpq-fe.h>
#include <arpa/inet.h> // For htonl

int main() {

    int   nFields = 0;
    int   nTuples = 0;
    int   blk_size = 0;
    PGconn *shadow_db_conn;
    PGresult *res;
    const char *shadow_db_conn_info = "dbname=shadow_shc_data";
    int is_shadow_db = 1; // Assuming it's a shadow database
    char *blk_size_val;
    char *data_val;

    // Connect to the shadow database
    if (is_shadow_db) {
        shadow_db_conn = PQconnectdb(shadow_db_conn_info);
        if (PQstatus(shadow_db_conn) != CONNECTION_OK) {
            fprintf(stderr, "Connection to shadow database failed: %s\n", PQerrorMessage(shadow_db_conn));
            PQfinish(shadow_db_conn);
            exit(1);
        }

        // Set bytea_output to 'escape'
        res = PQexec(shadow_db_conn, "SET bytea_output = 'escape'");
        if (PQresultStatus(res) != PGRES_COMMAND_OK) {
            fprintf(stderr, "SET bytea_output command failed: %s\n", PQerrorMessage(shadow_db_conn));
            PQclear(res);
            PQfinish(shadow_db_conn);
            exit(1);
        }
        PQclear(res);
        printf("%s() Connection to shadow_shc_data database SUCCESSFUL\n", __func__);
    }

    // Construct the command to prepare
    char SelectDataName[11];
    char SelectDataCommand[150];
    int  SelectDataNParams;
    Oid  SelectDataParamTypes[2];

    sprintf(SelectDataName,"%s","SelectData");
    sprintf(SelectDataCommand, "CALL SQL_select_data_procedure($1, $2, NULL, NULL)");
    SelectDataNParams = 2;
    SelectDataParamTypes[0] = 23;  // int
    SelectDataParamTypes[1] = 16;  // bool

    // Prepare the statement
    if (is_shadow_db) {
        res = PQprepare(shadow_db_conn,
                        SelectDataName,
                        SelectDataCommand,
                        SelectDataNParams,
                        SelectDataParamTypes);
        if (PQresultStatus(res) != PGRES_COMMAND_OK) {
            fprintf(stderr, "Prepare failed: %s\n", PQerrorMessage(shadow_db_conn));
            PQclear(res);
            PQfinish(shadow_db_conn);
            exit(1);
        }
        PQclear(res);
    }

    // Sample input values
    int32_t fa = -335509949;
    bool hold = false;

    uint32_t fa2 = htonl(fa);
    char *fa_val = (char *)&fa2;
    bool hold2 = hold;
    char *hold_val = (char *)&hold2;


    const char *paramValues[2]      = {fa_val, hold_val};
    int   paramLengths[2]           = {sizeof(fa2), sizeof(hold2)};
    int   paramFormats[2]           = {1, 1};       // identify parameter as binary

    // Assuming paramValues, paramLengths, and paramFormats are properly populated
    // Execute the prepared statement
    if (is_shadow_db) {
        //const char *paramValues[2] = {"123", "true"};
        //int paramLengths[2] = {sizeof("123") - 1, sizeof("true") - 1}; // Minus 1 for excluding null terminator
        //int paramFormats[2] = {0, 0}; // Assuming all parameters are in text format
        int resultFormat = 1; // 1 for binary result format

        res = PQexecPrepared(shadow_db_conn,
                             SelectDataName,
                             SelectDataNParams,
                             paramValues,
                             paramLengths,
                             paramFormats,
                             resultFormat);

        if (PQresultStatus(res) == PGRES_TUPLES_OK && PQnfields(res) != 0 && PQntuples(res) > 0) {
            //printf("SELECT operation succeeded on Shadow DB\n");
            // Process the result if needed
            nFields = PQnfields(res);       // number of columns
            nTuples = PQntuples(res);       // number of rows
            printf("%s() nFields(cols)=%i nTuples(rows)=%i\n",__func__,nFields,nTuples);

            blk_size_val = PQgetvalue(res, 0, 0);
            data_val = PQgetvalue(res, 0, 1);
            if(blk_size_val != NULL && data_val != NULL)
            {
                  printf("SELECT operation succeeded on Shadow DB\n");
                  blk_size = ntohl(*(uint32_t *)PQgetvalue (res, 0, 0));
                  printf("%s() blk_size returned is %i\n",__func__,blk_size);
            }

        } else {
            fprintf(stderr, "SELECT failed on Shadow DB: %s\n", PQerrorMessage(shadow_db_conn));
        }
        PQclear(res);
    }

    PQfinish(shadow_db_conn); // Close the connection
    return 0;
}