//+------------------------------------------------------------------+
//|                                                           db.mqh |
//|                                  Copyright 2026, MetaQuotes Ltd. |
//|                                             https://www.mql5.com |
//+------------------------------------------------------------------+
#property copyright "Copyright 2026, MetaQuotes Ltd."
#property link      "https://www.mql5.com"
///+------------------------------------------------------------------+
//|                                                                  |
//+------------------------------------------------------------------+
class CMarketDatabase
  {
protected:
   int               m_db_handle;
   string            m_filename;

public:
                     CMarketDatabase(string filename = "MarketData.sqlite") :
                     m_filename(filename), m_db_handle(INVALID_HANDLE) {}
                    ~CMarketDatabase()
     {
      Close();
     }

   // 1. Open the connection using the COMMON flag
   bool              Open()
     {
      // DATABASE_OPEN_COMMON is critical: it points to the same folder Python used
      m_db_handle = DatabaseOpen(m_filename, DATABASE_OPEN_READONLY | DATABASE_OPEN_COMMON);
      if(m_db_handle == INVALID_HANDLE)
        {
         Print("DB: Failed to open ", m_filename, ". Error: ", GetLastError());
         return false;
        }
      return true;
     }

   void              Close()
     {
      if(m_db_handle != INVALID_HANDLE)
         DatabaseClose(m_db_handle);
     }

   // 2. Fetch data into the MqlRates structure for the Sparse Table
   int               GetCleanedRates(string symbol, datetime start, int count, MqlRates &rates[])
     {
      if(m_db_handle == INVALID_HANDLE && !Open())
         return 0;
      // SQL Query: Selecting the cleaned OHLC data
      string query = StringFormat(
                        "SELECT Timestamp, open, high, low, close, spread FROM %s "
                        "WHERE Timestamp >= %lld ORDER BY Timestamp ASC LIMIT %d",
                        symbol, (long)start, count);
      int request = DatabasePrepare(m_db_handle, query);
      if(request == INVALID_HANDLE)
        {
         Print("DB: Query Prep Failed: ", GetLastError());
         return 0;
        }
      int i = 0;
      ArrayFree(rates);
      while(DatabaseRead(request))
        {
         ArrayResize(rates, i + 1);
         long ts;
         DatabaseColumnLong(request, 0, ts);
         rates[i].time = (datetime)ts;
         DatabaseColumnDouble(request, 1, rates[i].open);
         DatabaseColumnDouble(request, 2, rates[i].high);
         DatabaseColumnDouble(request, 3, rates[i].low);
         DatabaseColumnDouble(request, 4, rates[i].close);
         DatabaseColumnInteger(request, 5, rates[i].spread);
         i++;
        }
      DatabaseFinalize(request);
      return i;
     }
  };
//+------------------------------------------------------------------+