#include <ctype.h>
#include <sqlext.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>

SQLHDBC db_connection; // Handle connection

#define BUFFER_SIZE 64

void print_sql_error(SQLHANDLE handle, SQLSMALLINT handle_type) {
    SQLCHAR sql_state[6];
    SQLINTEGER native_error;
    SQLCHAR message_text[SQL_MAX_MESSAGE_LENGTH];
    SQLSMALLINT text_length;
    SQLGetDiagRec(handle_type, handle, 1, sql_state, &native_error, message_text, sizeof(message_text), &text_length);
    printf("SQL Error: %s\nSQL State: %s\n", message_text, sql_state);
}

void trim_trailing_spaces(char *str) {
    size_t len = strlen(str);
    while (len > 0 && isspace((unsigned char)str[len - 1])) {
        str[--len] = '\0';
    }
}

void task1(const SQLHDBC conn) {
    SQLHSTMT stmt;
    SQLAllocHandle(SQL_HANDLE_STMT, conn, &stmt);

    char *sql_query = "select n_nationkey, n_name, n_regionkey, n_comment from nation";
    SQLRETURN result;
    SQLINTEGER nation_key, region_key;
    SQLCHAR name[BUFFER_SIZE], comment[BUFFER_SIZE];

    result = SQLExecDirect(stmt, (SQLCHAR *)sql_query, SQL_NTS);
    if (result != SQL_SUCCESS && result != SQL_SUCCESS_WITH_INFO) {
        print_sql_error(stmt, SQL_HANDLE_STMT);
        SQLFreeHandle(SQL_HANDLE_STMT, stmt);
        return;
    }

    result = SQLFetch(stmt);
    if (result != SQL_SUCCESS && result != SQL_SUCCESS_WITH_INFO && result != SQL_NO_DATA) {
        print_sql_error(stmt, SQL_HANDLE_STMT);
        SQLFreeHandle(SQL_HANDLE_STMT, stmt);
        return;
    }

    printf("%-12s %-15s %-12s %s\n", "nation_key", "name", "region_key", "comment");
    while (result != SQL_NO_DATA) {
        SQLGetData(stmt, 1, SQL_C_SLONG, &nation_key, 0, NULL);
        SQLGetData(stmt, 2, SQL_C_CHAR, name, BUFFER_SIZE, NULL);
        SQLGetData(stmt, 3, SQL_C_SLONG, &region_key, 0, NULL);
        SQLGetData(stmt, 4, SQL_C_CHAR, comment, BUFFER_SIZE, NULL);

        trim_trailing_spaces((char *)name);
        trim_trailing_spaces((char *)comment);
        printf("%-12d %-15s %-12d %s\n", nation_key, name, region_key, comment);

        result = SQLFetch(stmt);
        if (result != SQL_SUCCESS && result != SQL_SUCCESS_WITH_INFO && result != SQL_NO_DATA) {
            print_sql_error(stmt, SQL_HANDLE_STMT);
            break;
        }
    }

    SQLFreeHandle(SQL_HANDLE_STMT, stmt);
}

void task2(SQLHDBC conn) {
    SQLHSTMT stmt;
    SQLAllocHandle(SQL_HANDLE_STMT, conn, &stmt);

    SQLCHAR *sql_insert = (SQLCHAR *)"insert into nation (n_nationkey, n_name, n_regionkey) values (25, 'South Korea', 3)";
    SQLRETURN result = SQLExecDirect(stmt, sql_insert, SQL_NTS);
    if (result != SQL_SUCCESS && result != SQL_SUCCESS_WITH_INFO) {
        print_sql_error(stmt, SQL_HANDLE_STMT);
        SQLFreeHandle(SQL_HANDLE_STMT, stmt);
        return;
    }

    SQLCHAR *sql_query = (SQLCHAR *)"select n_nationkey, n_name, n_regionkey from nation where n_nationkey = 25";
    result = SQLExecDirect(stmt, sql_query, SQL_NTS);
    if (result != SQL_SUCCESS && result != SQL_SUCCESS_WITH_INFO) {
        print_sql_error(stmt, SQL_HANDLE_STMT);
        SQLFreeHandle(SQL_HANDLE_STMT, stmt);
        return;
    }

    result = SQLFetch(stmt);
    if (result != SQL_NO_DATA) {
        SQLINTEGER nation_key, region_key;
        SQLCHAR name[BUFFER_SIZE];
        SQLGetData(stmt, 1, SQL_C_SLONG, &nation_key, 0, NULL);
        SQLGetData(stmt, 2, SQL_C_CHAR, name, BUFFER_SIZE, NULL);
        SQLGetData(stmt, 3, SQL_C_SLONG, &region_key, 0, NULL);

        trim_trailing_spaces((char *)name);
        printf("Inserted: %d, %s, %d\n", nation_key, name, region_key);
    } else
        printf("Insert failed or data not found.\n");

    SQLFreeHandle(SQL_HANDLE_STMT, stmt);
}

void task3(SQLHDBC conn) {
    SQLHSTMT stmt;
    SQLAllocHandle(SQL_HANDLE_STMT, conn, &stmt);

    SQLCHAR *sql_update = (SQLCHAR *)"update nation set n_regionkey = 2 where n_nationkey = 25";
    SQLRETURN result = SQLExecDirect(stmt, sql_update, SQL_NTS);
    if (result != SQL_SUCCESS && result != SQL_SUCCESS_WITH_INFO) {
        print_sql_error(stmt, SQL_HANDLE_STMT);
        SQLFreeHandle(SQL_HANDLE_STMT, stmt);
        return;
    }

    SQLCHAR *sql_query = (SQLCHAR *)"select n_nationkey, n_name, n_regionkey from nation where n_nationkey = 25";
    result = SQLExecDirect(stmt, sql_query, SQL_NTS);
    if (result != SQL_SUCCESS && result != SQL_SUCCESS_WITH_INFO) {
        print_sql_error(stmt, SQL_HANDLE_STMT);
        SQLFreeHandle(SQL_HANDLE_STMT, stmt);
        return;
    }

    result = SQLFetch(stmt);
    if (result != SQL_NO_DATA) {
        SQLINTEGER nation_key, region_key;
        SQLCHAR name[BUFFER_SIZE];
        SQLGetData(stmt, 1, SQL_C_SLONG, &nation_key, 0, NULL);
        SQLGetData(stmt, 2, SQL_C_CHAR, name, BUFFER_SIZE, NULL);
        SQLGetData(stmt, 3, SQL_C_SLONG, &region_key, 0, NULL);

        trim_trailing_spaces((char *)name);
        printf("Updated: %d, %s, %d\n", nation_key, name, region_key);
    } else
        printf("Update failed or data not found.\n");

    SQLFreeHandle(SQL_HANDLE_STMT, stmt);
}

void task4(SQLHDBC conn) {
    SQLHSTMT stmt;
    SQLAllocHandle(SQL_HANDLE_STMT, conn, &stmt);

    SQLCHAR *sql_delete = (SQLCHAR *)"delete from nation where n_nationkey = 25";
    SQLRETURN result = SQLExecDirect(stmt, sql_delete, SQL_NTS);
    if (result != SQL_SUCCESS && result != SQL_SUCCESS_WITH_INFO) {
        print_sql_error(stmt, SQL_HANDLE_STMT);
        SQLFreeHandle(SQL_HANDLE_STMT, stmt);
        return;
    }

    SQLCHAR *sql_query = (SQLCHAR *)"select n_nationkey from nation where n_nationkey = 25";
    result = SQLExecDirect(stmt, sql_query, SQL_NTS);
    if (result != SQL_SUCCESS && result != SQL_SUCCESS_WITH_INFO) {
        print_sql_error(stmt, SQL_HANDLE_STMT);
        SQLFreeHandle(SQL_HANDLE_STMT, stmt);
        return;
    }

    result = SQLFetch(stmt);
    if (result == SQL_NO_DATA)
        printf("Delete successful.\n");
    else {
        SQLINTEGER nation_key;
        SQLGetData(stmt, 1, SQL_C_SLONG, &nation_key, 0, NULL);
        printf("Delete failed: %d\n", nation_key);
    }

    SQLFreeHandle(SQL_HANDLE_STMT, stmt);
}

int main() {
    SQLHENV env;
    SQLINTEGER result;

    // Allocate environment handle
    result = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
    if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO)) {
        printf("Error AllocHandle\n");
        exit(0);
    }

    // Set ODBC version
    SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0);

    // Allocate connection handle
    result = SQLAllocHandle(SQL_HANDLE_DBC, env, &db_connection);
    if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO)) {
        // Release environment handle
        SQLFreeHandle(SQL_HANDLE_ENV, env);
        exit(0);
    }

    // Set autocommit on
    SQLSetConnectAttr(db_connection, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER)SQL_AUTOCOMMIT_ON, 0);

    // Connect to database
    result = SQLConnect(db_connection,
                        (SQLCHAR *)"buptlab_database", // Data source name
                        SQL_NTS,
                        (SQLCHAR *)"",
                        SQL_NTS,
                        (SQLCHAR *)"",
                        SQL_NTS);
    if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO)) {
        printf("Error SQLConnect %d\n", result);
        print_sql_error(db_connection, SQL_HANDLE_DBC);
        // Release environment and connection handles
        SQLFreeHandle(SQL_HANDLE_DBC, db_connection);
        SQLFreeHandle(SQL_HANDLE_ENV, env);
        exit(0);
    }

    printf("Connected!\n");

    task1(db_connection);
    task2(db_connection);
    task3(db_connection);
    task4(db_connection);

    printf("Done!\n");

    SQLDisconnect(db_connection);
    SQLFreeHandle(SQL_HANDLE_DBC, db_connection);
    SQLFreeHandle(SQL_HANDLE_ENV, env);

    return 0;
}
