// Database.cpp
#include "Database.hpp"
#include <sstream>
#include <algorithm>
#include <filesystem>
#include <iostream>

namespace fs = std::filesystem;

void Database::createTable(const std::string& name, const std::vector<std::string>& columns) {
    if (tables.find(name) != tables.end()) {
        std::cerr << "Error: Table " << name << " already exists.\n";
        return;
    }
    tables[name] = std::make_unique<Table>(name, columns);
    if (!transaction_active) {
        tables[name]->save();
    }
    std::cout << "Table " << name << " created successfully.\n";
}

void Database::loadTable(const std::string& name) {
    if (tables.find(name) != tables.end()) {
        std::cerr << "Error: Table " << name << " is already loaded.\n";
        return;
    }
    // Check if file exists
    std::string filepath = "data/" + name + ".tbl";
    if (!fs::exists(filepath)) {
        std::cerr << "Error: Table " << name << " does not exist.\n";
        return;
    }
    tables[name] = std::make_unique<Table>(name);
    std::cout << "Table " << name << " loaded successfully.\n";
}

Table* Database::getTable(const std::string& name) {
    auto it = tables.find(name);
    if (it != tables.end()) {
        return it->second.get();
    }
    std::cerr << "Error: Table " << name << " not found.\n";
    return nullptr;
}

void Database::showTables() {
    std::cout << "Tables:\n";
    for (const auto& pair : tables) {
        std::cout << "- " << pair.first << "\n";
    }
}

void Database::showTable(const std::string& name) {
    Table* table = getTable(name);
    if (table) {
        std::vector<std::string> all_columns; // Empty vector indicates all columns
        std::vector<std::pair<std::string, std::string>> aggregates;
        table->select(all_columns, aggregates, "", "", {}, {});
    }
}

void Database::describeTable(const std::string& name) {
    Table* table = getTable(name);
    if (table) {
        std::cout << "Table: " << name << "\n";
        std::cout << "Columns:\n";
        for (const auto& col : table->getColumns()) {
            std::cout << "- " << col << "\n";
        }
    }
}

void Database::beginTransaction() {
    if (transaction_active) {
        std::cerr << "Error: Transaction already in progress.\n";
        return;
    }
    // Backup current tables
    for (auto& pair : tables) {
        table_backups[pair.first] = std::make_unique<Table>(*pair.second);
    }
    transaction_active = true;
    std::cout << "Transaction started.\n";
}

void Database::commitTransaction() {
    if (!transaction_active) {
        std::cerr << "Error: No active transaction to commit.\n";
        return;
    }
    // Save all tables
    for (auto& pair : tables) {
        pair.second->save();
    }
    table_backups.clear();
    transaction_active = false;
    std::cout << "Transaction committed.\n";
}

void Database::rollbackTransaction() {
    if (!transaction_active) {
        std::cerr << "Error: No active transaction to rollback.\n";
        return;
    }
    // Restore tables from backups
    for (auto& pair : table_backups) {
        if (tables.find(pair.first) != tables.end()) {
            tables[pair.first] = std::move(pair.second);
        }
    }
    table_backups.clear();
    transaction_active = false;
    std::cout << "Transaction rolled back.\n";
}

void Database::run() {
    std::string input;
    std::cout << "Welcome to MiniDB! Enter SQL commands or 'exit' to quit.\n";
    while (true) {
        std::cout << "MiniDB> ";
        std::getline(std::cin, input);
        if (input.empty()) continue;

        // Exit condition
        if (input == "exit") break;

        // Convert input to uppercase for command identification
        std::stringstream ss(input);
        std::string command;
        ss >> command;
        std::string original_command = command; // Preserve original for case-sensitive parts
        std::transform(command.begin(), command.end(), command.begin(), ::toupper);

        if (command == "CREATE") {
            std::string table_keyword, table_name;
            ss >> table_keyword >> table_name;
            std::transform(table_keyword.begin(), table_keyword.end(), table_keyword.begin(), ::toupper);
            if (table_keyword != "TABLE") {
                std::cerr << "Error: Invalid syntax. Did you mean 'CREATE TABLE'? \n";
                continue;
            }
            // Parse columns
            std::string cols;
            std::getline(ss, cols, '(');
            std::getline(ss, cols, ')');
            std::vector<std::string> columns;
            std::stringstream cols_ss(cols);
            std::string col;
            while (std::getline(cols_ss, col, ',')) {
                // Trim whitespace
                col.erase(col.begin(), std::find_if(col.begin(), col.end(), [](unsigned char ch) {
                    return !std::isspace(ch);
                }));
                col.erase(std::find_if(col.rbegin(), col.rend(), [](unsigned char ch) {
                    return !std::isspace(ch);
                }).base(), col.end());
                columns.push_back(col);
            }
            createTable(table_name, columns);
        }
        else if (command == "INSERT") {
            std::string into_keyword, table_name, values_keyword;
            ss >> into_keyword >> table_name >> values_keyword;
            std::transform(into_keyword.begin(), into_keyword.end(), into_keyword.begin(), ::toupper);
            std::transform(values_keyword.begin(), values_keyword.end(), values_keyword.begin(), ::toupper);
            if (into_keyword != "INTO" || values_keyword != "VALUES") {
                std::cerr << "Error: Invalid syntax. Use 'INSERT INTO table_name VALUES (...)'\n";
                continue;
            }
            std::string vals;
            std::getline(ss, vals, '(');
            std::getline(ss, vals, ')');
            std::vector<std::string> values;
            std::stringstream vals_ss(vals);
            std::string val;
            while (std::getline(vals_ss, val, ',')) {
                // Trim whitespace
                val.erase(val.begin(), std::find_if(val.begin(), val.end(), [](unsigned char ch) {
                    return !std::isspace(ch);
                }));
                val.erase(std::find_if(val.rbegin(), val.rend(), [](unsigned char ch) {
                    return !std::isspace(ch);
                }).base(), val.end());
                // Remove quotes if present
                if (!val.empty() && val.front() == '\'' && val.back() == '\'') {
                    val = val.substr(1, val.size() - 2);
                }
                values.push_back(val);
            }
            Table* table = getTable(table_name);
            if (table) {
                table->insert(values);
                if (!transaction_active) {
                    table->save();
                }
                std::cout << "Record inserted into " << table_name << ".\n";
            }
        }
        else if (command == "SELECT") {
            // Extract selected columns until 'FROM' is found
            std::vector<std::string> selected_columns;
            std::vector<std::pair<std::string, std::string>> aggregates; // aggregate function and column
            std::string token;

            while (ss >> token &&
                   token != "FROM" &&
                   token != "from" &&
                   token != "From") {
                // Handle cases where columns are separated by commas
                if (token.back() == ',') {
                    token.pop_back(); // Remove trailing comma
                }
                // Check for aggregate functions
                size_t pos = token.find('(');
                if (pos != std::string::npos && token.back() == ')') {
                    std::string func = token.substr(0, pos);
                    std::string arg = token.substr(pos + 1, token.size() - pos - 2);
                    std::transform(func.begin(), func.end(), func.begin(), ::toupper);
                    if (func == "COUNT") {
                        aggregates.emplace_back("COUNT", arg);
                    }
                    else {
                        std::cerr << "Error: Unsupported aggregate function '" << func << "'.\n";
                        aggregates.clear();
                        break;
                    }
                }
                else {
                    selected_columns.push_back(token);
                }
            }

            // Check if 'FROM' keyword was found
            if (!(token == "FROM" || token == "from" || token == "From")) {
                std::cerr << "Error: Invalid syntax. Missing 'FROM'.\n";
                continue;
            }

            // Extract table name
            std::string table_name;
            ss >> table_name;
            if (table_name.empty()) {
                std::cerr << "Error: Missing table name after 'FROM'.\n";
                continue;
            }

            // Initialize variables for WHERE, ORDER BY, GROUP BY clauses
            std::string clause;
            std::string where_column, where_value;
            std::vector<std::pair<std::string, std::string>> order_by; // column and direction
            std::vector<std::string> group_by;

            while (ss >> clause) {
                std::string upper_clause = clause;
                std::transform(upper_clause.begin(), upper_clause.end(), upper_clause.begin(), ::toupper);
                if (upper_clause == "WHERE") {
                    ss >> where_column >> where_value;
                    // Remove potential semicolon at the end of where_value
                    if (!where_value.empty() && where_value.back() == ';') {
                        where_value.pop_back();
                    }
                    // Remove quotes if present
                    if (!where_value.empty() && where_value.front() == '\'' && where_value.back() == '\'') {
                        where_value = where_value.substr(1, where_value.size() - 2);
                    }
                }
                else if (upper_clause == "ORDER") {
                    std::string by;
                    ss >> by;
                    std::transform(by.begin(), by.end(), by.begin(), ::toupper);
                    if (by != "BY") {
                        std::cerr << "Error: Invalid syntax after 'ORDER'. Did you mean 'ORDER BY'? \n";
                        break;
                    }
                    std::string order_col, direction = "ASC";
                    ss >> order_col;
                    if (!(ss >> direction)) {
                        direction = "ASC";
                    }
                    else {
                        std::transform(direction.begin(), direction.end(), direction.begin(), ::toupper);
                        if (direction != "ASC" && direction != "DESC") {
                            ss.unget(); // Put back the non-direction token if it's not direction
                            direction = "ASC";
                        }
                    }
                    order_by.emplace_back(order_col, direction);
                }
                else if (upper_clause == "GROUP") {
                    std::string by;
                    ss >> by;
                    std::transform(by.begin(), by.end(), by.begin(), ::toupper);
                    if (by != "BY") {
                        std::cerr << "Error: Invalid syntax after 'GROUP'. Did you mean 'GROUP BY'? \n";
                        break;
                    }
                    std::string group_col;
                    ss >> group_col;
                    group_by.emplace_back(group_col);
                }
                else {
                    std::cerr << "Error: Unrecognized clause '" << clause << "'.\n";
                    break;
                }
            }

            // Handle '*' to select all columns
            if (selected_columns.size() == 1 && selected_columns[0] == "*") {
                selected_columns.clear(); // Passing an empty vector will indicate selecting all columns
            }

            // Retrieve the table and perform the select operation
            Table* table = getTable(table_name);
            if (table) {
                table->select(selected_columns, aggregates, where_column, where_value, order_by, group_by);
            }
        }
        else if (command == "UPDATE") {
            std::string table_name, set_keyword;
            ss >> table_name >> set_keyword;
            std::transform(set_keyword.begin(), set_keyword.end(), set_keyword.begin(), ::toupper);
            if (set_keyword != "SET") {
                std::cerr << "Error: Invalid syntax. Did you mean 'SET'? \n";
                continue;
            }
            std::string set_column, equal_sign, set_value;
            ss >> set_column >> equal_sign >> set_value;
            if (equal_sign != "=") {
                std::cerr << "Error: Invalid syntax for SET. Expected '='.\n";
                continue;
            }

            // Remove quotes if present
            if (!set_value.empty() && set_value.front() == '\'' && set_value.back() == '\'') {
                set_value = set_value.substr(1, set_value.size() - 2);
            }

            // Handle optional WHERE clause
            std::string clause;
            std::string where_column, where_value;
            if (ss >> clause) {
                std::string upper_clause = clause;
                std::transform(upper_clause.begin(), upper_clause.end(), upper_clause.begin(), ::toupper);
                if (upper_clause == "WHERE") {
                    ss >> where_column >> where_value;
                    // Remove potential semicolon at the end of where_value
                    if (!where_value.empty() && where_value.back() == ';') {
                        where_value.pop_back();
                    }
                    // Remove quotes if present
                    if (!where_value.empty() && where_value.front() == '\'' && where_value.back() == '\'') {
                        where_value = where_value.substr(1, where_value.size() - 2);
                    }
                }
                else {
                    std::cerr << "Error: Unrecognized clause '" << clause << "' in UPDATE.\n";
                    continue;
                }
            }

            Table* table = getTable(table_name);
            if (table) {
                table->update(set_column, set_value, where_column, where_value);
                if (!transaction_active) {
                    table->save();
                }
            }
        }
        else if (command == "DELETE") {
            std::string from_keyword, table_name;
            ss >> from_keyword >> table_name;
            std::transform(from_keyword.begin(), from_keyword.end(), from_keyword.begin(), ::toupper);
            if (from_keyword != "FROM") {
                std::cerr << "Error: Invalid syntax. Did you mean 'DELETE FROM'? \n";
                continue;
            }

            // Handle optional WHERE clause
            std::string clause;
            std::string where_column, where_value;
            if (ss >> clause) {
                std::string upper_clause = clause;
                std::transform(upper_clause.begin(), upper_clause.end(), upper_clause.begin(), ::toupper);
                if (upper_clause == "WHERE") {
                    ss >> where_column >> where_value;
                    // Remove potential semicolon at the end of where_value
                    if (!where_value.empty() && where_value.back() == ';') {
                        where_value.pop_back();
                    }
                    // Remove quotes if present
                    if (!where_value.empty() && where_value.front() == '\'' && where_value.back() == '\'') {
                        where_value = where_value.substr(1, where_value.size() - 2);
                    }
                }
                else {
                    std::cerr << "Error: Unrecognized clause '" << clause << "' in DELETE.\n";
                    continue;
                }
            }

            Table* table = getTable(table_name);
            if (table) {
                table->deleteRecords(where_column, where_value);
                if (!transaction_active) {
                    table->save();
                }
            }
        }
        else if (command == "SHOW") {
            std::string target;
            ss >> target;
            std::transform(target.begin(), target.end(), target.begin(), ::toupper);
            if (target == "TABLES") {
                showTables();
            }
            else {
                // Assume it's a table name
                showTable(target);
            }
        }
        else if (command == "DESCRIBE") {
            std::string table_name;
            ss >> table_name;
            if (table_name.empty()) {
                std::cerr << "Error: Missing table name for DESCRIBE.\n";
                continue;
            }
            describeTable(table_name);
        }
        else if (command == "BEGIN") {
            std::string transaction_keyword;
            ss >> transaction_keyword;
            std::transform(transaction_keyword.begin(), transaction_keyword.end(), transaction_keyword.begin(), ::toupper);
            if (transaction_keyword != "TRANSACTION" && transaction_keyword != "TRANSACTION;") {
                std::cerr << "Error: Invalid syntax. Use 'BEGIN TRANSACTION'.\n";
                continue;
            }
            beginTransaction();
        }
        else if (command == "COMMIT") {
            commitTransaction();
        }
        else if (command == "ROLLBACK") {
            rollbackTransaction();
        }
        else {
            std::cerr << "Error: Unrecognized command.\n";
        }
    }
}

// Database.hpp
#ifndef DATABASE_HPP
#define DATABASE_HPP

#include "Table.hpp"
#include <unordered_map>
#include <memory>
#include <vector>
#include <string>

class Database {
private:
    std::unordered_map<std::string, std::unique_ptr<Table>> tables;
    // Transaction support
    bool transaction_active = false;
    std::unordered_map<std::string, std::unique_ptr<Table>> table_backups;

public:
    Database() = default;

    void createTable(const std::string& name, const std::vector<std::string>& columns);
    void loadTable(const std::string& name);
    Table* getTable(const std::string& name);
    void showTables();
    void showTable(const std::string& name);
    void describeTable(const std::string& name);

    // Transaction methods
    void beginTransaction();
    void commitTransaction();
    void rollbackTransaction();

    void run();
};

#endif // DATABASE_HPP

// main.cpp
#include "Database.hpp"
#include <filesystem>
#include <iostream>

int main() {
    // Create data directory if it doesn't exist
    std::string data_dir = "data";
    if (!std::filesystem::exists(data_dir)) {
        std::filesystem::create_directory(data_dir);
    }

    Database db;
    db.run();
    return 0;
}

// Record.cpp
#include "Record.hpp"
// Currently empty as we use default implementations

// Record.hpp
#ifndef RECORD_HPP
#define RECORD_HPP

#include <vector>
#include <string>

class Record {
public:
    std::vector<std::string> fields;

    Record() = default;
    Record(const std::vector<std::string>& fields) : fields(fields) {}
};

#endif // RECORD_HPP// Record.hpp
#ifndef RECORD_HPP
#define RECORD_HPP

#include <vector>
#include <string>

class Record {
public:
    std::vector<std::string> fields;

    Record() = default;
    Record(const std::vector<std::string>& fields) : fields(fields) {}
};

#endif // RECORD_HPP

// Table.cpp
#include "Table.hpp"
#include <sstream>
#include <algorithm>
#include <map>

// Initialize DATA_DIR as a constant
const std::string DATA_DIR = "data/";

Table::Table(const std::string& name, const std::vector<std::string>& columns) : name(name), columns(columns) {
    filepath = DATA_DIR + name + ".tbl";
    save(); // Save table schema
}

Table::Table(const std::string& name) : name(name) {
    filepath = DATA_DIR + name + ".tbl";
    load();
}

void Table::insert(const std::vector<std::string>& fields) {
    if (fields.size() != columns.size()) {
        std::cerr << "Error: Field count doesn't match column count.\n";
        return;
    }
    records.emplace_back(fields);
}

void Table::select(const std::vector<std::string>& select_columns, 
                  const std::vector<std::pair<std::string, std::string>>& aggregates,
                  const std::string& where_column, 
                  const std::string& where_value,
                  const std::vector<std::pair<std::string, std::string>>& order_by,
                  const std::vector<std::string>& group_by) {
    // Determine columns to display
    std::vector<int> col_indices;
    // If selected_columns is empty (SELECT *), use all columns
    if (select_columns.empty()) {
        col_indices.resize(columns.size());
        for (size_t i = 0; i < columns.size(); ++i) {
            col_indices[i] = i;
        }
    } else {
        // Existing code for specific columns...
        for (const auto& col : select_columns) {
            auto it = std::find(columns.begin(), columns.end(), col);
            if (it != columns.end()) {
                col_indices.push_back(std::distance(columns.begin(), it));
            } else {
                std::cerr << "Error: Column " << col << " does not exist.\n";
                return;
            }
        }
    }

    // Handle GROUP BY
    if (!group_by.empty()) {
        // Ensure all group_by columns exist
        std::vector<int> group_indices;
        for (const auto& gb_col : group_by) {
            auto it = std::find(columns.begin(), columns.end(), gb_col);
            if (it != columns.end()) {
                group_indices.push_back(std::distance(columns.begin(), it));
            } else {
                std::cerr << "Error: GROUP BY column " << gb_col << " does not exist.\n";
                return;
            }
        }

        // Prepare aggregate functions
        std::vector<std::pair<std::string, int>> agg_functions; // function name and column index (-1 for COUNT(*))
        for (const auto& agg : aggregates) {
            std::string func = agg.first;
            std::string target = agg.second;
            if (func == "COUNT") {
                if (target == "*") {
                    agg_functions.emplace_back(func, -1);
                } else {
                    auto it = std::find(columns.begin(), columns.end(), target);
                    if (it != columns.end()) {
                        agg_functions.emplace_back(func, std::distance(columns.begin(), it));
                    } else {
                        std::cerr << "Error: COUNT target column " << target << " does not exist.\n";
                        return;
                    }
                }
            }
            else {
                std::cerr << "Error: Unsupported aggregate function '" << func << "'.\n";
                return;
            }
        }

        // Group records
        std::map<std::string, std::vector<Record>> grouped_records;
        for (const auto& record : records) {
            bool match = true;
            if (!where_column.empty()) {
                auto it = std::find(columns.begin(), columns.end(), where_column);
                if (it != columns.end()) {
                    int idx = std::distance(columns.begin(), it);
                    if (record.fields[idx] != where_value) {
                        match = false;
                    }
                } else {
                    std::cerr << "Error: WHERE column " << where_column << " does not exist.\n";
                    return;
                }
            }
            if (match) {
                std::string key;
                for (const auto& idx : group_indices) {
                    key += record.fields[idx] + "_";
                }
                grouped_records[key].emplace_back(record);
            }
        }

        // Print header
        for (size_t i = 0; i < group_by.size(); ++i) {
            std::cout << group_by[i];
            if (i != group_by.size() - 1 || !agg_functions.empty()) std::cout << " | ";
        }
        for (size_t i = 0; i < agg_functions.size(); ++i) {
            std::cout << agg_functions[i].first << "(" << (agg_functions[i].second == -1 ? "*" : columns[agg_functions[i].second]) << ")";
            if (i != agg_functions.size() - 1) std::cout << " | ";
        }
        std::cout << "\n";

        // Print separator
        for (size_t i = 0; i < group_by.size(); ++i) {
            std::cout << "--------";
            if (i != group_by.size() - 1 || !agg_functions.empty()) std::cout << "+";
        }
        for (size_t i = 0; i < agg_functions.size(); ++i) {
            std::cout << "--------";
            if (i != agg_functions.size() - 1) std::cout << "+";
        }
        std::cout << "\n";

        // Print grouped records with aggregates
        for (const auto& pair : grouped_records) {
            std::stringstream ss(pair.first);
            std::string value;
            size_t idx = 0;
            while (std::getline(ss, value, '_')) {
                if (idx < group_by.size()) {
                    std::cout << value;
                    if (idx != group_by.size() - 1 || !agg_functions.empty()) std::cout << " | ";
                }
                idx++;
            }
            for (size_t i = 0; i < agg_functions.size(); ++i) {
                if (agg_functions[i].first == "COUNT") {
                    if (agg_functions[i].second == -1) {
                        std::cout << pair.second.size();
                    }
                    else {
                        // Count non-empty values in the specified column
                        int count = 0;
                        for (const auto& rec : pair.second) {
                            if (!rec.fields[agg_functions[i].second].empty()) {
                                count++;
                            }
                        }
                        std::cout << count;
                    }
                }
                // Future aggregate functions can be handled here
                if (i != agg_functions.size() - 1) std::cout << " | ";
            }
            std::cout << "\n";
        }
        return;
    }

    // Filter records based on WHERE clause
    std::vector<Record> filtered_records;
    for (const auto& record : records) {
        bool match = true;
        if (!where_column.empty()) {
            auto it = std::find(columns.begin(), columns.end(), where_column);
            if (it != columns.end()) {
                int idx = std::distance(columns.begin(), it);
                if (record.fields[idx] != where_value) {
                    match = false;
                }
            } else {
                std::cerr << "Error: WHERE column " << where_column << " does not exist.\n";
                return;
            }
        }
        if (match) {
            filtered_records.emplace_back(record);
        }
    }

    // Handle ORDER BY
    if (!order_by.empty()) {
        // Check if order_by columns exist
        for (const auto& ob : order_by) {
            if (std::find(columns.begin(), columns.end(), ob.first) == columns.end()) {
                std::cerr << "Error: ORDER BY column " << ob.first << " does not exist.\n";
                return;
            }
        }
        // Sort the filtered_records
        std::sort(filtered_records.begin(), filtered_records.end(),
            [&](const Record& a, const Record& b) -> bool {
                for (const auto& ob : order_by) {
                    int idx = std::distance(columns.begin(), std::find(columns.begin(), columns.end(), ob.first));
                    if (a.fields[idx] < b.fields[idx]) {
                        return ob.second == "ASC";
                    }
                    else if (a.fields[idx] > b.fields[idx]) {
                        return ob.second == "DESC";
                    }
                    // If equal, continue to next ordering
                }
                return false;
            }
        );
    }

    // Print header
    if (select_columns.empty()) {
        // For SELECT *
        for (size_t i = 0; i < columns.size(); ++i) {
            std::cout << columns[i];
            if (i != columns.size() - 1) std::cout << " | ";
        }
    } else {
        // Existing code for specific columns
        for (size_t i = 0; i < select_columns.size(); ++i) {
            std::cout << select_columns[i];
            if (i != select_columns.size() - 1) std::cout << " | ";
        }
    }
    for (size_t i = 0; i < aggregates.size(); ++i) {
        std::cout << aggregates[i].first << "(" << aggregates[i].second << ")";
        if (i != aggregates.size() - 1) std::cout << " | ";
    }
    std::cout << "\n";

    // Print separator
    for (size_t i = 0; i < select_columns.size(); ++i) {
        std::cout << "--------";
        if (i != select_columns.size() - 1 || !aggregates.empty()) std::cout << "+";
    }
    for (size_t i = 0; i < aggregates.size(); ++i) {
        std::cout << "--------";
        if (i != aggregates.size() - 1) std::cout << "+";
    }
    std::cout << "\n";

    // Print records
    for (const auto& record : filtered_records) {
        for (size_t i = 0; i < col_indices.size(); ++i) {
            std::cout << record.fields[col_indices[i]];
            if (i != col_indices.size() - 1 || !aggregates.empty()) std::cout << " | ";
        }
        // Handle aggregates (if any without GROUP BY, typically SUM, COUNT, etc. would require aggregation over all records)
        for (size_t i = 0; i < aggregates.size(); ++i) {
            if (aggregates[i].first == "COUNT") {
                if (aggregates[i].second == "*") {
                    std::cout << "1"; // Each record counts as 1
                }
                else {
                    // Count non-empty values in the specified column
                    int count = 0;
                    for (const auto& rec : filtered_records) {
                        auto it = std::find(columns.begin(), columns.end(), aggregates[i].second);
                        if (it != columns.end()) {
                            int idx = std::distance(columns.begin(), it);
                            if (!rec.fields[idx].empty()) {
                                count++;
                            }
                        }
                    }
                    std::cout << count;
                }
            }
            // Future aggregate functions can be handled here
            if (i != aggregates.size() - 1) std::cout << " | ";
        }
        std::cout << "\n";
    }
}

void Table::update(const std::string& set_column, const std::string& set_value, 
                  const std::string& where_column, 
                  const std::string& where_value) {
    auto it = std::find(columns.begin(), columns.end(), set_column);
    if (it == columns.end()) {
        std::cerr << "Error: SET column " << set_column << " does not exist.\n";
        return;
    }
    int set_idx = std::distance(columns.begin(), it);
    int updated_count = 0;

    for (auto& record : records) {
        bool match = true;
        if (!where_column.empty()) {
            auto where_it = std::find(columns.begin(), columns.end(), where_column);
            if (where_it != columns.end()) {
                int where_idx = std::distance(columns.begin(), where_it);
                if (record.fields[where_idx] != where_value) {
                    match = false;
                }
            }
            else {
                std::cerr << "Error: WHERE column " << where_column << " does not exist.\n";
                return;
            }
        }
        if (match) {
            record.fields[set_idx] = set_value;
            updated_count++;
        }
    }
    std::cout << "Updated " << updated_count << " record(s) in " << name << ".\n";
}

void Table::deleteRecords(const std::string& where_column, const std::string& where_value) {
    auto initial_size = records.size();
    records.erase(
        std::remove_if(records.begin(), records.end(),
            [&](const Record& record) -> bool {
                if (where_column.empty()) {
                    return true; // Delete all
                }
                auto it = std::find(columns.begin(), columns.end(), where_column);
                if (it != columns.end()) {
                    int idx = std::distance(columns.begin(), it);
                    return record.fields[idx] == where_value;
                }
                else {
                    std::cerr << "Error: WHERE column " << where_column << " does not exist.\n";
                    return false;
                }
            }),
        records.end()
    );
    auto deleted_count = initial_size - records.size();
    std::cout << "Deleted " << deleted_count << " record(s) from " << name << ".\n";
}

void Table::save() {
    std::ofstream ofs(filepath, std::ios::trunc);
    if (!ofs) {
        std::cerr << "Error: Unable to open file " << filepath << " for writing.\n";
        return;
    }
    // First line: column headers
    for (size_t i = 0; i < columns.size(); ++i) {
        ofs << columns[i];
        if (i != columns.size() - 1) ofs << ",";
    }
    ofs << "\n";

    // Records
    for (const auto& record : records) {
        for (size_t i = 0; i < record.fields.size(); ++i) {
            ofs << record.fields[i];
            if (i != record.fields.size() - 1) ofs << ",";
        }
        ofs << "\n";
    }
    ofs.close();
}

void Table::load() {
    std::ifstream ifs(filepath);
    if (!ifs) {
        std::cerr << "Error: Unable to open file " << filepath << " for reading.\n";
        return;
    }
    std::string line;
    bool is_header = true;
    while (std::getline(ifs, line)) {
        std::stringstream ss(line);
        std::string field;
        std::vector<std::string> fields;
        while (std::getline(ss, field, ',')) {
            fields.push_back(field);
        }
        if (is_header) {
            columns = fields;
            is_header = false;
        } else {
            records.emplace_back(fields);
        }
    }
    ifs.close();
}

// Table.hpp
#ifndef TABLE_HPP
#define TABLE_HPP

#include "Record.hpp"
#include <string>
#include <vector>
#include <fstream>
#include <iostream>
#include <algorithm>
#include <functional>

class Table {
private:
    std::string name;
    std::vector<std::string> columns;
    std::vector<Record> records;
    std::string filepath;

public:
    Table(const std::string& name, const std::vector<std::string>& columns);
    Table(const std::string& name); // Load existing table

    void insert(const std::vector<std::string>& fields);
    void select(const std::vector<std::string>& select_columns, 
               const std::vector<std::pair<std::string, std::string>>& aggregates,
               const std::string& where_column = "", 
               const std::string& where_value = "",
               const std::vector<std::pair<std::string, std::string>>& order_by = {},
               const std::vector<std::string>& group_by = {});
    void update(const std::string& set_column, const std::string& set_value, 
               const std::string& where_column = "", 
               const std::string& where_value = "");
    void deleteRecords(const std::string& where_column = "", const std::string& where_value = "");

    void save();
    void load();
    const std::string& getName() const { return name; }
    const std::vector<std::string>& getColumns() const { return columns; }

    // For transaction backup
    Table(const Table& other) : name(other.name), columns(other.columns), records(other.records), filepath(other.filepath) {}
};

#endif // TABLE_HPP

many function is not working correctly such as deleteion is not working correctly and many more....so correct them and only give updated file nothing else (i dont want explanation)