//+------------------------------------------------------------------+
//|                                               News Database.mqh  |
//|                           Copyright 2026, Allan Munene Mutiiria. |
//|                                   https://t.me/Forex_Algo_Trader |
//+------------------------------------------------------------------+
#property copyright "Copyright 2026, Allan Munene Mutiiria."
#property link      "https://t.me/Forex_Algo_Trader"

//--- Include guard
#ifndef NEWS_DATABASE_MQH
#define NEWS_DATABASE_MQH

//--- Include core data definitions and state
#include "News Core.mqh"

//--- Declare debugLogging extern when not compiled from main (Logic.mqh uses same guard)
#ifndef NEWS_COMPILED_FROM_MAIN
extern bool debugLogging; // Print debug info to journal
#endif

//+------------------------------------------------------------------+
//| Database State                                                   |
//+------------------------------------------------------------------+
int g_news_db = INVALID_HANDLE; // SQLite database handle (-1 when not open)

//--- Database stored in the COMMON terminal folder so both live chart
//--- and strategy tester agents can access the same file
#define NEWS_DB_PATH "Database\\NewsCalendarEA.db" // Path relative to Common/Files/

//+------------------------------------------------------------------+
//| Open or create database and ensure required tables exist         |
//+------------------------------------------------------------------+
bool News_DbOpen()
  {
//--- Return early if database is already open
   if(g_news_db != INVALID_HANDLE) return true;
//--- Open from Common/Files/ folder for shared live and tester access
   g_news_db = DatabaseOpen(NEWS_DB_PATH,
                            DATABASE_OPEN_READWRITE |
                            DATABASE_OPEN_CREATE    |
                            DATABASE_OPEN_COMMON);
   if(g_news_db == INVALID_HANDLE)
     {
      Print("News_DbOpen: failed to open '", NEWS_DB_PATH, "' error=", GetLastError());
      return false;
     }
//--- Create events table with all calendar value columns
   if(!DatabaseExecute(g_news_db,
      "CREATE TABLE IF NOT EXISTS events ("
      "  value_id     INTEGER PRIMARY KEY,"
      "  event_id     INTEGER NOT NULL,"
      "  event_time   INTEGER NOT NULL,"
      "  currency     TEXT    NOT NULL,"
      "  event_name   TEXT    NOT NULL,"
      "  importance   TEXT    NOT NULL,"
      "  has_actual   INTEGER NOT NULL DEFAULT 0,"
      "  actual       REAL    NOT NULL DEFAULT 0,"
      "  has_forecast INTEGER NOT NULL DEFAULT 0,"
      "  forecast     REAL    NOT NULL DEFAULT 0,"
      "  has_previous INTEGER NOT NULL DEFAULT 0,"
      "  previous     REAL    NOT NULL DEFAULT 0,"
      "  has_revised  INTEGER NOT NULL DEFAULT 0,"
      "  revised_prev REAL    NOT NULL DEFAULT 0,"
      "  unit         INTEGER NOT NULL DEFAULT 0,"
      "  multiplier   INTEGER NOT NULL DEFAULT 0,"
      "  digits       INTEGER NOT NULL DEFAULT 0,"
      "  updated_at   INTEGER NOT NULL DEFAULT 0"
      ");"))
     {
      Print("News_DbOpen: create events table failed: ", GetLastError());
      DatabaseClose(g_news_db);
      g_news_db = INVALID_HANDLE;
      return false;
     }
//--- Create index on event_time for fast time-window queries
   DatabaseExecute(g_news_db,
      "CREATE INDEX IF NOT EXISTS idx_events_time ON events(event_time);");
//--- Create triggered table for trade deduplication across restarts
   if(!DatabaseExecute(g_news_db,
      "CREATE TABLE IF NOT EXISTS triggered ("
      "  event_id     INTEGER PRIMARY KEY,"
      "  triggered_at INTEGER NOT NULL"
      ");"))
     {
      Print("News_DbOpen: create triggered table failed: ", GetLastError());
      DatabaseClose(g_news_db);
      g_news_db = INVALID_HANDLE;
      return false;
     }
   if(debugLogging) Print("News_DbOpen: ready at '", NEWS_DB_PATH, "'");
   return true;
  }

//+------------------------------------------------------------------+
//| Close the database cleanly                                       |
//+------------------------------------------------------------------+
void News_DbClose()
  {
   if(g_news_db == INVALID_HANDLE)
     {
      Print(">>> News DB: Close requested but database was not open");
      return;
     }
   DatabaseClose(g_news_db);
   g_news_db = INVALID_HANDLE;
   Print(">>> News DB: Database closed cleanly");
  }

//+------------------------------------------------------------------+
//| Upsert a single event row into the events table                  |
//+------------------------------------------------------------------+
bool News_DbUpsertEvent(long valueId, const NewsEvent &ev, datetime updatedAt)
  {
//--- Abort if database is not open
   if(g_news_db == INVALID_HANDLE) return false;
//--- Prepare INSERT OR REPLACE statement with all event columns
   int stmt = DatabasePrepare(g_news_db,
      "INSERT OR REPLACE INTO events ("
      "  value_id,event_id,event_time,currency,event_name,importance,"
      "  has_actual,actual,has_forecast,forecast,"
      "  has_previous,previous,has_revised,revised_prev,"
      "  unit,multiplier,digits,updated_at"
      ") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);");
   if(stmt == INVALID_HANDLE) return false;
//--- Bind all parameter values to statement slots
   DatabaseBind(stmt,  0, valueId);
   DatabaseBind(stmt,  1, ev.eventId);
   DatabaseBind(stmt,  2, (long)ev.eventDateTime);
   DatabaseBind(stmt,  3, ev.currency);
   DatabaseBind(stmt,  4, ev.event);
   DatabaseBind(stmt,  5, ev.importance);
   DatabaseBind(stmt,  6, (int)ev.hasActual);
   DatabaseBind(stmt,  7, ev.actual);
   DatabaseBind(stmt,  8, (int)ev.hasForecast);
   DatabaseBind(stmt,  9, ev.forecast);
   DatabaseBind(stmt, 10, (int)ev.hasPrevious);
   DatabaseBind(stmt, 11, ev.previous);
   DatabaseBind(stmt, 12, (int)ev.hasRevised);
   DatabaseBind(stmt, 13, ev.revisedPrevious);
   DatabaseBind(stmt, 14, ev.unit);
   DatabaseBind(stmt, 15, ev.multiplier);
   DatabaseBind(stmt, 16, ev.digits);
   DatabaseBind(stmt, 17, (long)updatedAt);
//--- Execute and finalize the prepared statement
   const bool ok = DatabaseRead(stmt);
   DatabaseFinalize(stmt);
   return ok;
  }

//+------------------------------------------------------------------+
//| Load events for a time window from DB into g_news_allEvents[]    |
//+------------------------------------------------------------------+
int News_DbLoadEventsForWindow(datetime startDt, datetime endDt)
  {
//--- Abort if database is not open
   if(g_news_db == INVALID_HANDLE) return 0;
//--- Prepare time-window query ordered ascending by event time
   int stmt = DatabasePrepare(g_news_db,
      "SELECT event_id,event_time,currency,event_name,importance,"
      "       has_actual,actual,has_forecast,forecast,"
      "       has_previous,previous,has_revised,revised_prev,"
      "       unit,multiplier,digits"
      " FROM events"
      " WHERE event_time >= ? AND event_time <= ?"
      " ORDER BY event_time ASC;");
   if(stmt == INVALID_HANDLE) return 0;
//--- Bind time window bounds
   DatabaseBind(stmt, 0, (long)startDt);
   DatabaseBind(stmt, 1, (long)endDt);
//--- Reset event array and read each row into it
   ArrayResize(g_news_allEvents, 0);
   int idx = 0;
   while(DatabaseRead(stmt))
     {
      //--- Declare temporary locals; MQL5 forbids struct refs from arrays
      NewsEvent e;
      long   evId = 0, evTime = 0;
      int    hasA = 0, hasF = 0, hasP = 0, hasR = 0;
      int    unitV = 0, multV = 0, digV = 0;
      string cur = "", evName = "", imp = "";
      double act = 0, fcast = 0, prev = 0, revPrev = 0;
//--- Read each column from the current row
      DatabaseColumnLong   (stmt,  0, evId);
      DatabaseColumnLong   (stmt,  1, evTime);
      DatabaseColumnText   (stmt,  2, cur);
      DatabaseColumnText   (stmt,  3, evName);
      DatabaseColumnText   (stmt,  4, imp);
      DatabaseColumnInteger(stmt,  5, hasA);
      DatabaseColumnDouble (stmt,  6, act);
      DatabaseColumnInteger(stmt,  7, hasF);
      DatabaseColumnDouble (stmt,  8, fcast);
      DatabaseColumnInteger(stmt,  9, hasP);
      DatabaseColumnDouble (stmt, 10, prev);
      DatabaseColumnInteger(stmt, 11, hasR);
      DatabaseColumnDouble (stmt, 12, revPrev);
      DatabaseColumnInteger(stmt, 13, unitV);
      DatabaseColumnInteger(stmt, 14, multV);
      DatabaseColumnInteger(stmt, 15, digV);
//--- Populate event struct from column values
      e.eventId         = evId;
      e.eventDateTime   = (datetime)evTime;
      e.eventDate       = TimeToString((datetime)evTime, TIME_DATE);
      e.eventTime       = TimeToString((datetime)evTime, TIME_MINUTES);
      e.currency        = cur;
      e.event           = evName;
      e.importance      = imp;
      e.hasActual       = (hasA != 0);
      e.actual          = act;
      e.hasForecast     = (hasF != 0);
      e.forecast        = fcast;
      e.hasPrevious     = (hasP != 0);
      e.previous        = prev;
      e.hasRevised      = (hasR != 0);
      e.revisedPrevious = revPrev;
      e.unit            = unitV;
      e.multiplier      = multV;
      e.digits          = digV;
//--- Append populated struct to the event array
      ArrayResize(g_news_allEvents, idx + 1);
      g_news_allEvents[idx] = e;
      idx++;
     }
   DatabaseFinalize(stmt);
   if(debugLogging) Print("News_DbLoadEventsForWindow: loaded ", idx, " events");
   return idx;
  }

//+------------------------------------------------------------------+
//| Prune events older than keepDays to prevent database bloat       |
//+------------------------------------------------------------------+
void News_DbPruneOldEvents(int keepDays = 7)
  {
//--- Abort if database is not open
   if(g_news_db == INVALID_HANDLE) return;
//--- Compute cutoff timestamp and delete rows older than it
   const long cutoff = (long)TimeCurrent() - (long)keepDays * 86400;
   int stmt = DatabasePrepare(g_news_db,
      "DELETE FROM events WHERE event_time < ?;");
   if(stmt == INVALID_HANDLE) return;
   DatabaseBind(stmt, 0, cutoff);
   DatabaseRead(stmt);
   DatabaseFinalize(stmt);
  }

//+------------------------------------------------------------------+
//| Persist a triggered event ID to DB for restart safety            |
//+------------------------------------------------------------------+
bool News_DbMarkTriggered(long evId)
  {
//--- Abort if database is not open
   if(g_news_db == INVALID_HANDLE) return false;
//--- Insert event ID with current timestamp; ignore duplicate
   int stmt = DatabasePrepare(g_news_db,
      "INSERT OR IGNORE INTO triggered (event_id,triggered_at) VALUES (?,?);");
   if(stmt == INVALID_HANDLE) return false;
   DatabaseBind(stmt, 0, evId);
   DatabaseBind(stmt, 1, (long)TimeCurrent());
   const bool ok = DatabaseRead(stmt);
   DatabaseFinalize(stmt);
   return ok;
  }

//+------------------------------------------------------------------+
//| Load triggered event IDs from last 24h into g_news_triggeredIds[]|
//+------------------------------------------------------------------+
void News_DbLoadTriggered()
  {
//--- Abort if database is not open
   if(g_news_db == INVALID_HANDLE) return;
//--- Query triggered IDs from the past 24 hours
   const long cutoff = (long)TimeCurrent() - 86400;
   int stmt = DatabasePrepare(g_news_db,
      "SELECT event_id FROM triggered WHERE triggered_at >= ?;");
   if(stmt == INVALID_HANDLE) return;
   DatabaseBind(stmt, 0, cutoff);
//--- Populate g_news_triggeredIds array from query results
   ArrayResize(g_news_triggeredIds, 0);
   int n = 0;
   while(DatabaseRead(stmt))
     {
      long id = 0;
      DatabaseColumnLong(stmt, 0, id);
      ArrayResize(g_news_triggeredIds, n + 1);
      g_news_triggeredIds[n++] = id;
     }
   DatabaseFinalize(stmt);
   if(n > 0 && debugLogging)
      Print("News_DbLoadTriggered: restored ", n, " triggered IDs");
  }

//+------------------------------------------------------------------+
//| Check if event ID was already triggered (memory array fast path) |
//+------------------------------------------------------------------+
bool News_DbAlreadyTriggered(long evId)
  {
//--- Linear scan of the in-memory triggered IDs array
   const int n = ArraySize(g_news_triggeredIds);
   for(int i = 0; i < n; i++)
      if(g_news_triggeredIds[i] == evId) return true;
   return false;
  }

//+------------------------------------------------------------------+
//| Check if database already has events for a given time window     |
//+------------------------------------------------------------------+
bool News_DbHasDataForWindow(datetime startDt, datetime endDt)
  {
//--- Abort if database is not open
   if(g_news_db == INVALID_HANDLE) return false;
//--- Count rows in the requested window
   int stmt = DatabasePrepare(g_news_db,
      "SELECT COUNT(*) FROM events WHERE event_time >= ? AND event_time <= ?;");
   if(stmt == INVALID_HANDLE) return false;
   DatabaseBind(stmt, 0, (long)startDt);
   DatabaseBind(stmt, 1, (long)endDt);
   int cnt = 0;
   if(DatabaseRead(stmt)) DatabaseColumnInteger(stmt, 0, cnt);
   DatabaseFinalize(stmt);
   return (cnt > 0);
  }

//+------------------------------------------------------------------+
//| Draw one frame of the download progress bar onto canvas          |
//+------------------------------------------------------------------+
void News_DbDrawProgressBar(CCanvas &canv, int pbW, int pbH,
                             int pct, int saved, int total)
  {
//--- Clear canvas to transparent
   canv.Erase(0x00000000);
//--- Draw outer rounded pill background using theme colors
   News_FillRoundRect(canv, 0, 0, pbW, pbH, 10, ColorToARGB(g_news_bg, 255));
   News_DrawRoundRectBorder(canv, 0, 0, pbW, pbH, 10, 1,
                             ColorToARGB(g_news_borderAccent, 255));
//--- Stamp centered title text with current progress counts
   canv.FontSet("Arial Bold", -100);
   canv.TextOut(pbW / 2, 7,
      "Downloading News Events: " + IntegerToString(pct) +
      "%  (" + IntegerToString(saved) + " / " + IntegerToString(total) + ")",
      ColorToARGB(g_news_titleText, 255), TA_CENTER | TA_TOP);
//--- Draw the track capsule background below the title
   const int padX   = 14;
   const int trackL = padX;
   const int trackR = pbW - padX;
   const int trackW = trackR - trackL;
   const int trackY = pbH - 18;
   const int barH   = 10;
   News_FillRoundRect(canv, trackL, trackY, trackW, barH, barH / 2,
                      ColorToARGB(g_news_panelAlt, 255));
   News_DrawRoundRectBorder(canv, trackL, trackY, trackW, barH, barH / 2, 1,
                             ColorToARGB(g_news_border, 255));
//--- Fill the track from center outward proportional to percent complete
   const int fillW = (int)(trackW * pct / 100.0);
   const int fillL = trackL + (trackW - fillW) / 2;
   if(fillW > 0)
      News_FillRoundRect(canv, fillL, trackY, fillW, barH, barH / 2,
                         ColorToARGB(g_news_accent, 255));
//--- Push canvas frame to chart
   canv.Update();
   ChartRedraw(0);
  }

//+------------------------------------------------------------------+
//| Download calendar data for date range and save to database       |
//+------------------------------------------------------------------+
void News_DbDownloadAndSave(datetime startDt, datetime endDt)
  {
//--- Abort if database is not open
   if(g_news_db == INVALID_HANDLE)
     {
      Print("News_DbDownloadAndSave: DB not open - aborting");
      return;
     }
   Print(">>> News DB: Fetching calendar events from ",
         TimeToString(startDt), " to ", TimeToString(endDt), " ...");
//--- Query all raw calendar values in the requested time window
   MqlCalendarValue values[];
   const int total = CalendarValueHistory(values, startDt, endDt, NULL, NULL);
   if(total <= 0)
     {
      Print(">>> News DB: No events found for specified range");
      return;
     }
   Print(">>> News DB: ", total, " raw calendar records found - saving to DB...");
//--- Create progress bar canvas centered at the top of the chart
   const int chartW  = (int)ChartGetInteger(0, CHART_WIDTH_IN_PIXELS);
   const int PB_W    = 420;
   const int PB_H    = 46;
   const int PB_X    = (chartW - PB_W) / 2;
   const int PB_Y    = 20;
   const string PB_NAME = "NewsDB_ProgressCanvas";
   CCanvas pbCanv;
   if(pbCanv.CreateBitmapLabel(PB_NAME, PB_X, PB_Y, PB_W, PB_H,
                                COLOR_FORMAT_ARGB_NORMALIZE))
     {
      //--- Configure progress bar canvas object properties
      ObjectSetInteger(0, PB_NAME, OBJPROP_SELECTABLE, false);
      ObjectSetInteger(0, PB_NAME, OBJPROP_HIDDEN,     true);
      ObjectSetInteger(0, PB_NAME, OBJPROP_ZORDER,     2000);
     }
//--- Draw initial 0% progress frame
   News_DbDrawProgressBar(pbCanv, PB_W, PB_H, 0, 0, total);
//--- Begin transaction, iterate all values, upsert each event record
   DatabaseTransactionBegin(g_news_db);
   const datetime updatedAt = TimeCurrent();
   int saved = 0;
   for(int i = 0; i < total; i++)
     {
      //--- Fetch associated event metadata and country info
      MqlCalendarEvent ev;
      if(!CalendarEventById(values[i].event_id, ev)) continue;
      MqlCalendarCountry ctry;
      CalendarCountryById(ev.country_id, ctry);
      MqlCalendarValue val;
      CalendarValueById(values[i].id, val);
//--- Populate NewsEvent struct from calendar API data
      NewsEvent ne;
      ne.eventDate       = TimeToString(values[i].time, TIME_DATE);
      ne.eventTime       = TimeToString(values[i].time, TIME_MINUTES);
      ne.currency        = ctry.currency;
      ne.event           = ev.name;
      ne.importance      = News_GetImpactLabel(ev.importance);
      ne.hasActual       = val.HasActualValue();
      ne.hasForecast     = val.HasForecastValue();
      ne.hasPrevious     = val.HasPreviousValue();
      ne.hasRevised      = val.HasRevisedValue();
      ne.actual          = val.HasActualValue()   ? val.GetActualValue()   : 0.0;
      ne.forecast        = val.HasForecastValue() ? val.GetForecastValue() : 0.0;
      ne.previous        = val.HasPreviousValue() ? val.GetPreviousValue() : 0.0;
      ne.revisedPrevious = val.HasRevisedValue()  ? val.GetRevisedValue()  : 0.0;
      ne.unit            = (int)ev.unit;
      ne.multiplier      = (int)ev.multiplier;
      ne.digits          = (int)ev.digits;
      ne.eventDateTime   = values[i].time;
      ne.eventId         = (long)values[i].event_id;
//--- Upsert the event into the database
      News_DbUpsertEvent((long)values[i].id, ne, updatedAt);
      saved++;
//--- Refresh progress bar every 5 records to reduce redraw overhead
      if(i % 5 == 0 || i == total - 1)
        {
         const int pct = (int)((i + 1) * 100.0 / total);
         News_DbDrawProgressBar(pbCanv, PB_W, PB_H, pct, saved, total);
        }
     }
//--- Commit all upserts as a single transaction
   DatabaseTransactionCommit(g_news_db);
//--- Hold at 100% briefly so the user sees completion before canvas disappears
   News_DbDrawProgressBar(pbCanv, PB_W, PB_H, 100, saved, total);
   Sleep(800);
//--- Destroy progress bar canvas and clean up the chart object
   pbCanv.Destroy();
   ObjectDelete(0, PB_NAME);
   ChartRedraw(0);
   Print(">>> News DB: Download complete. ", saved, " events saved to ", NEWS_DB_PATH,
         " for range ", TimeToString(startDt), " to ", TimeToString(endDt),
         ". Use it for offline testing...");
//--- Open the DB file to log its current size on disk
   ResetLastError();
   int fh = FileOpen(NEWS_DB_PATH,
                     FILE_READ | FILE_BIN | FILE_COMMON | FILE_SHARE_READ | FILE_SHARE_WRITE);

   if(fh == INVALID_HANDLE)
     {
      Print(">>> News DB: Could not open DB file to read size (error ", GetLastError(), ")");
     }
   else
     {
      //--- Format byte count into KB or MB for readability
      const ulong dbBytes = FileSize(fh);
      FileClose(fh);
      string sizeStr;
      if(dbBytes >= 1048576)
         sizeStr = DoubleToString((double)dbBytes / 1048576.0, 2) + " MB";
      else if(dbBytes >= 1024)
         sizeStr = DoubleToString((double)dbBytes / 1024.0, 1) + " KB";
      else
         sizeStr = IntegerToString((long)dbBytes) + " bytes";
      Print(">>> News DB: Database file size: ", dbBytes, " bytes (", sizeStr, ")...");
     }
  }

#endif // NEWS_DATABASE_MQH