//+------------------------------------------------------------------+
//|                                                          Warrior |
//|                                                     AnimateDread |
//|                                             https://www.mql5.com |
//+------------------------------------------------------------------+
#include "..\System\PrintVerbose.mqh"
class CDatabaseOperations
  {
private:
   int               databaseHandle;
   int               queryHandle; // Handle for prepared queries
   bool              transactionActive; // Flag to track transaction state
   bool              verboseMode;
   string            dbPath;
   // Prepare and execute a query
   bool              PrepareAndExecuteQuery(string query)
     {
      if(databaseHandle == INVALID_HANDLE)
        {
         if(!OpenDatabase())
            return false;
        }
      if(!DatabaseExecute(databaseHandle, query))
        {
         Print("DatabaseExecute error:");
         FinalizeQuery();
         CloseDatabase();
         // Stop execution upon encountering an error
         return false;
        }
      return true;
     }
   // Finalize the query handle
   void              FinalizeQuery()
     {
      if(queryHandle != INVALID_HANDLE)
        {
         DatabaseFinalize(queryHandle);
         queryHandle = INVALID_HANDLE;
        }
     }
public:
   // Constructor
                     CDatabaseOperations(string DBP, bool verbose = false)
     {
      databaseHandle = INVALID_HANDLE;
      queryHandle = INVALID_HANDLE;
      dbPath = DBP;
     }
   // Destructor
                    ~CDatabaseOperations()
     {
      FinalizeQuery();
      CommitTransaction();
      CloseDatabase();
     }
   // Begin a transaction
   bool              BeginTransaction()
     {
      if(transactionActive)
        {
         Print("Transaction is already active.");
         return false;
        }
      if(databaseHandle == INVALID_HANDLE)
        {
         if(!OpenDatabase())
            return false;
        }
      if(!DatabaseTransactionBegin(databaseHandle))
        {
         Print("DatabaseTransactionBegin failed.");
         return false;
        }
      transactionActive = true;
      return true;
     }
   // Commit a transaction
   bool              CommitTransaction()
     {
      if(!transactionActive)
        {
         Print("No active transaction to commit.");
         return false;
        }
      if(!DatabaseTransactionCommit(databaseHandle))
        {
         Print("DatabaseTransactionCommit failed.");
         RollbackTransaction();
         return false;
        }
      transactionActive = false;
      return true;
     }
   // Rollback a transaction
   bool              RollbackTransaction()
     {
      if(!transactionActive)
        {
         Print("No active transaction to rollback.");
         return false;
        }
      if(!DatabaseTransactionRollback(databaseHandle))
        {
         Print("DatabaseTransactionRollback failed.");
         return false;
        }
      transactionActive = false;
      return true;
     }     
   // Create the database file
   bool              CreateDatabase(string databasePath)
     {
      if(databaseHandle != INVALID_HANDLE)
        {
         // An instance is already open
         Print("Database is already open.");
         return false;
        }
      databaseHandle = DatabaseOpen(databasePath, DATABASE_OPEN_COMMON | DATABASE_OPEN_CREATE);
      if(databaseHandle == INVALID_HANDLE)
        {
         Print("Database create failed with code");
         FinalizeQuery();
         CloseDatabase();
         // Stop execution upon encountering an error
         return false;
        }
      FinalizeQuery();
      CloseDatabase();
      return true;
     }
   // Open the database connection
   bool              OpenDatabase()
     {
      if(databaseHandle != INVALID_HANDLE)
        {
         // An instance is already open
         Print("Database is already open.");
         return false;
        }
      databaseHandle = DatabaseOpen(dbPath, DATABASE_OPEN_COMMON | DATABASE_OPEN_CREATE | DATABASE_OPEN_READWRITE);
      if(databaseHandle == INVALID_HANDLE)
        {
         Print("Database open failed with code");
         FinalizeQuery();
         CloseDatabase();
         // Stop execution upon encountering an error
         return false;
        }
      return true;
     }
   // Close the database connection
   bool              CloseDatabase()
     {
      if(databaseHandle != INVALID_HANDLE)
        {
         DatabaseClose(databaseHandle);
         databaseHandle = INVALID_HANDLE;
         return(true);
        }
      if(databaseHandle == INVALID_HANDLE)
         return(true);
      else
        {
         // Database is not open
         Print("Database is not open.");
         return(false);
        }
     }
   // Create a table in the database
   bool              CreateTable(string tableName, string tableSchema)
     {
      if(!DatabaseTableExists(databaseHandle, tableName))
        {
         string createTableQuery = "CREATE TABLE " + tableName + " (" + tableSchema + ")";
         if(PrepareAndExecuteQuery(createTableQuery))
           {
            return true;
           }
         else
           {
            Print("Failed to create table " + tableName);
            FinalizeQuery();
            CloseDatabase();
            // Stop execution upon encountering an error
            return false;
           }
        }
      else
        {
         // Table already exists
         return true;
        }
     }
   bool              DeleteTable(string tableName)
     {
      string deleteTableQuery = "DROP TABLE IF EXISTS " + tableName;
      if(PrepareAndExecuteQuery(deleteTableQuery))
        {
         PrintVerbose("Table " + tableName + " deleted successfully.");
         return true;
        }
      else
        {
         Print("Failed to delete table " + tableName);
         FinalizeQuery();
         CloseDatabase();
         // Stop execution upon encountering an error
         return false;
        }
     }
   // Insert a trade record into the database
   bool              InsertTradeRecord(string tableName, const string &columns[], const string &values[])
     {
      string insertQuery = "INSERT INTO " + tableName + " (";
      for(int i = 0; i < ArraySize(columns); i++)
        {
         insertQuery += columns[i];
         if(i < ArraySize(columns) - 1)
            insertQuery += ", ";
        }
      insertQuery += ") VALUES (";
      for(int i = 0; i < ArraySize(values); i++)
        {
         insertQuery += "'" + values[i] + "'";
         if(i < ArraySize(values) - 1)
            insertQuery += ", ";
        }
      insertQuery += ")";
      if(PrepareAndExecuteQuery(insertQuery))
        {
         return true;
        }
      else
        {
         Print("Failed to insert trade record into " + tableName);
         FinalizeQuery();
         CloseDatabase();
         // Stop execution upon encountering an error
         return false;
        }
     }
   // Fetch trade records from specified table and read into a structure, then populate the dynamic array with the data
   template <typename T>
   bool              FetchTradeRecords(string tableName, T &tradeRecordStructure, T &tradeRecords[])
     {
      string selectQuery = "SELECT * FROM " + tableName;
      if(!PrepareAndExecuteQuery(selectQuery))
         return false;
      queryHandle = DatabasePrepare(databaseHandle, selectQuery);
      if(queryHandle == INVALID_HANDLE)
        {
         Print("Failed to prepare SELECT query for " + tableName);
         FinalizeQuery();
         CloseDatabase();
         return false;
        }
      int recordCount = 0; // Initialize the record count
      while(DatabaseReadBind(queryHandle, tradeRecordStructure))
        {
         // Add the fetched trade record directly to the tradeRecords array
         ArrayResize(tradeRecords, recordCount + 1);
         tradeRecords[recordCount] = tradeRecordStructure;
         recordCount++;
        }
      FinalizeQuery();
      return true;
     }
   // Update trade records in the database based on specified criteria
   bool              UpdateTradeRecords(string tableName, const string &columns[], const string &values[], const string &condition)
     {
      string updateQuery = "UPDATE " + tableName + " SET ";
      for(int i = 0; i < ArraySize(columns); i++)
        {
         updateQuery += columns[i] + "='" + values[i] + "'";
         if(i < ArraySize(columns) - 1)
            updateQuery += ", ";
        }
      updateQuery += " WHERE " + condition;
      if(PrepareAndExecuteQuery(updateQuery))
        {
         return true;
        }
      else
        {
         Print("Failed to update trade records in " + tableName);
         FinalizeQuery();
         CloseDatabase();
         // Stop execution upon encountering an error
         return false;
        }
     }
   // Delete trade records from the database based on specified criteria
   bool              DeleteTradeRecords(string tableName, const string &condition)
     {
      string deleteQuery = "DELETE FROM " + tableName;
      deleteQuery += " WHERE " + condition;
      if(PrepareAndExecuteQuery(deleteQuery))
        {
         return true;
        }
      else
        {
         Print("Failed to delete trade records from " + tableName);
         FinalizeQuery();
         CloseDatabase();
         // Stop execution upon encountering an error
         return false;
        }
     }
   // Delete the oldest entry from a specified table
   bool              DeleteOldestEntry(string tableName)
     {
      string deleteQuery = "DELETE FROM " + tableName + " WHERE ROWID = (SELECT MIN(ROWID) FROM " + tableName + ")";
      if(PrepareAndExecuteQuery(deleteQuery))
        {
         PrintVerbose("Oldest entry deleted from " + tableName + " successfully.");
         return true;
        }
      else
        {
         Print("Failed to delete oldest entry from " + tableName);
         FinalizeQuery();
         CloseDatabase();
         return false;
        }
     }
  };
//+------------------------------------------------------------------+