using System.Text;
using MySql.Data.MySqlClient;
using MarketData.MarketDataModel;
using MarketData.Utils;

namespace MarketData.DataAccess
{
  public class HeadlinesDA
  {
    private HeadlinesDA()
    {
    }
    
    public static DateTime GetMaxHeadlineDate()
    {
      MySqlConnection sqlConnection = null;
      MySqlDataReader sqlDataReader = null;
      MySqlCommand sqlCommand = null;
      String strQuery = null;
      DateTime maxDate = Utility.Epoch;

      try
      {
        StringBuilder sb = new StringBuilder();
        sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
        sb.Append("select max(asof) from headlines");
        strQuery = sb.ToString(); ;
        sqlCommand = new MySqlCommand(strQuery, sqlConnection);
        sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
        sqlDataReader = sqlCommand.ExecuteReader();
        if (sqlDataReader.Read())
        {
          maxDate=sqlDataReader.GetDateTime(0);
        }
        return maxDate;
      }
      catch (Exception exception)
      {
        MDTrace.WriteLine(LogLevel.DEBUG, exception);
        return Utility.Epoch;
      }
      finally
      {
        if (null != sqlCommand) sqlCommand.Dispose();
        if (null != sqlDataReader) {sqlDataReader.Close();sqlDataReader.Dispose();}
        if (null != sqlConnection) sqlConnection.Close();
      }
    }

    public static List<String> GetHeadlineDates()
    {
      MySqlConnection sqlConnection = null;
      MySqlDataReader sqlDataReader = null;
      MySqlCommand sqlCommand=null;
      String strQuery = null;
      List<String> headlineDates=new List<String>();

      try
      {
        StringBuilder sb = new StringBuilder();
        sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
        sb.Append("select distinct asof from headlines order by 1 desc");
        strQuery = sb.ToString(); ;
        sqlCommand = new MySqlCommand(strQuery, sqlConnection);
        sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
        sqlDataReader = sqlCommand.ExecuteReader();
        while (sqlDataReader.Read())
        {
          headlineDates.Add(sqlDataReader.GetDateTime(0).ToShortDateString());
        }
        return headlineDates;
      }
      catch (Exception exception)
      {
        MDTrace.WriteLine(LogLevel.DEBUG,exception);
        return headlineDates;
      }
      finally
      {
        if(null!=sqlCommand)sqlCommand.Dispose();
        if (null != sqlDataReader) {sqlDataReader.Close();sqlDataReader.Dispose();}
        if (null != sqlConnection) sqlConnection.Close();
      }
    }

    public static Headlines GetHeadlines(String symbol)
    {
      MySqlConnection sqlConnection = null;
      MySqlDataReader sqlDataReader = null;
      MySqlCommand sqlCommand=null;
      String strQuery = null;
      Headlines headlines=new Headlines();

      try
      {
        StringBuilder sb = new StringBuilder();
        sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
        sb.Append("select h.symbol, h.asof, h.headline, h.source, h.modified,sm.company from headlines h left outer join securitymaster sm on h.symbol=sm.symbol where h.symbol=").Append(SqlUtils.AddQuotes(symbol));
        sb.Append(" order by h.asof desc, h.modified desc");
        strQuery = sb.ToString(); ;
        sqlCommand = new MySqlCommand(strQuery, sqlConnection);
        sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
        sqlDataReader = sqlCommand.ExecuteReader();
        while (sqlDataReader.Read())
        {
          Headline headline=new Headline();
          headline.Symbol=symbol;
          headline.Date=sqlDataReader.GetDateTime(1);
          headline.Entry=sqlDataReader.GetString(2);
          headline.Source=sqlDataReader.GetString(3);
          headline.Modified=sqlDataReader.GetDateTime(4);
          headline.CompanyName=sqlDataReader.GetString(5);
          headlines.Add(headline);
        }
        return headlines;
      }
      catch (Exception exception)
      {
        MDTrace.WriteLine(LogLevel.DEBUG,exception);
        return headlines;
      }
      finally
      {
        if(null!=sqlCommand)sqlCommand.Dispose();
        if (null != sqlDataReader) {sqlDataReader.Close();sqlDataReader.Dispose();}
        if (null != sqlConnection) sqlConnection.Close();
      }
    }

    public static Headlines GetHeadlines()
    {
      MySqlConnection sqlConnection = null;
      MySqlDataReader sqlDataReader = null;
      MySqlCommand sqlCommand=null;
      String strQuery = null;
      Headlines headlines=new Headlines();

      try
      {
        StringBuilder sb = new StringBuilder();
        sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
        sb.Append("select h.symbol, h.asof, h.headline, h.source, h.modified, sm.company from headlines h left outer join securitymaster sm on h.symbol=sm.symbol");
        sb.Append(" order by h.asof desc,h.symbol,h.modified desc;");
        strQuery = sb.ToString(); ;
        sqlCommand = new MySqlCommand(strQuery, sqlConnection);
        sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
        sqlDataReader = sqlCommand.ExecuteReader();
        while (sqlDataReader.Read())
        {
          Headline headline=new Headline();
          headline.Symbol=sqlDataReader.GetString(0);
          headline.Date=sqlDataReader.GetDateTime(1);
          headline.Entry=sqlDataReader.GetString(2);
          headline.Source=sqlDataReader.GetString(3);
          headline.Modified=sqlDataReader.GetDateTime(4);
          headline.CompanyName=sqlDataReader.GetString(5);
          headlines.Add(headline);
        }
        return headlines;
      }
      catch (Exception exception)
      {
        MDTrace.WriteLine(LogLevel.DEBUG,exception);
        return headlines;
      }
      finally
      {
        if(null!=sqlCommand)sqlCommand.Dispose();
        if (null != sqlDataReader) {sqlDataReader.Close();sqlDataReader.Dispose();}
        if (null != sqlConnection) sqlConnection.Close();
      }
    }

// This was authored for mobile app.  It wants the sorting to match the WPF app.
    public static Headlines GetLatestHeadlines()
    {
      MySqlConnection sqlConnection = null;
      MySqlDataReader sqlDataReader = null;
      MySqlCommand sqlCommand = null;
      String strQuery = null;
      Headlines headlines = new Headlines();

      try
      {
        StringBuilder sb = new StringBuilder();
        sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
        sb.Append("select h.symbol, h.asof, h.headline, h.source, h.modified, sm.company from headlines h left outer join securitymaster sm on h.symbol=sm.symbol");
        sb.Append(" where h.asof=(select max(asof) from headlines)");
        sb.Append(" order by h.asof desc,h.modified desc,h.symbol desc;");
        strQuery = sb.ToString(); ;
        sqlCommand = new MySqlCommand(strQuery, sqlConnection);
        sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
        sqlDataReader = sqlCommand.ExecuteReader();
        while (sqlDataReader.Read())
        {
          Headline headline = new Headline();
          headline.Symbol = sqlDataReader.GetString(0);
          headline.Date = sqlDataReader.GetDateTime(1);
          headline.Entry = sqlDataReader.GetString(2);
          headline.Source = sqlDataReader.GetString(3);
          headline.Modified = sqlDataReader.GetDateTime(4);
          headline.CompanyName = sqlDataReader.GetString(5);
          headlines.Add(headline);
        }
        return headlines;
      }
      catch (Exception exception)
      {
        MDTrace.WriteLine(LogLevel.DEBUG, exception);
        return headlines;
      }
      finally
      {
        if (null != sqlCommand) sqlCommand.Dispose();
        if (null != sqlDataReader) {sqlDataReader.Close();sqlDataReader.Dispose();}
        if (null != sqlConnection) sqlConnection.Close();
      }
    }

    public static Headlines GetHeadlines(String symbol,DateTime dateTime)
    {
      MySqlConnection sqlConnection = null;
      MySqlDataReader sqlDataReader = null;
      MySqlCommand sqlCommand=null;
      String strQuery = null;
      Headlines headlines=new Headlines();

      try
      {
        StringBuilder sb = new StringBuilder();
        sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
        sb.Append("select h.symbol, h.asof, h.headline, h.source, h.modified, sm.company from headlines h left outer join securitymaster sm on h.symbol=sm.symbol where h.asof=").Append(SqlUtils.AddQuotes(SqlUtils.SqlDate(dateTime)));
        sb.Append(" and h.symbol=").Append(SqlUtils.AddQuotes(symbol));
        sb.Append("order by h.asof desc, h.modified desc");
        strQuery = sb.ToString(); ;
        sqlCommand = new MySqlCommand(strQuery, sqlConnection);
        sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
        sqlDataReader = sqlCommand.ExecuteReader();
        while (sqlDataReader.Read())
        {
          Headline headline=new Headline();
          headline.Symbol=sqlDataReader.GetString(0);
          headline.Date=sqlDataReader.GetDateTime(1);
          headline.Entry=sqlDataReader.GetString(2);
          headline.Source=sqlDataReader.GetString(3);
          headline.Modified=sqlDataReader.GetDateTime(4);
          headline.CompanyName=sqlDataReader.GetString(5);
          headlines.Add(headline);
        }
        return headlines;
      }
      catch (Exception exception)
      {
        MDTrace.WriteLine(LogLevel.DEBUG,exception);
        return headlines;
      }
      finally
      {
        if(null!=sqlCommand)sqlCommand.Dispose();
        if (null != sqlDataReader) {sqlDataReader.Close();sqlDataReader.Dispose();}
        if (null != sqlConnection) sqlConnection.Close();
      }
    }

    public static Headlines GetHeadlines(DateTime dateTime)
    {
      MySqlConnection sqlConnection = null;
      MySqlDataReader sqlDataReader = null;
      MySqlCommand sqlCommand=null;
      String strQuery = null;
      Headlines headlines=new Headlines();

      try
      {
        StringBuilder sb = new StringBuilder();
        sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
        sb.Append("select h.symbol, h.asof, h.headline, h.source, h.modified, sm.company from headlines h left outer join securitymaster sm on h.symbol=sm.symbol where h.asof=").Append(SqlUtils.AddQuotes(SqlUtils.SqlDate(dateTime)));
        sb.Append("order by h.modified desc,h.symbol");
        strQuery = sb.ToString(); ;
        sqlCommand = new MySqlCommand(strQuery, sqlConnection);
        sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
        sqlDataReader = sqlCommand.ExecuteReader();
        while (sqlDataReader.Read())
        {
          Headline headline=new Headline();
          headline.Symbol=sqlDataReader.GetString(0);
          headline.Date=sqlDataReader.GetDateTime(1);
          headline.Entry=sqlDataReader.GetString(2);
          headline.Source=sqlDataReader.GetString(3);
          headline.Modified=sqlDataReader.GetDateTime(4);
          headline.CompanyName=sqlDataReader.GetString(5);
          headlines.Add(headline);
        }
        return headlines;
      }
      catch (Exception exception)
      {
        MDTrace.WriteLine(LogLevel.DEBUG,exception);
        return headlines;
      }
      finally
      {
        if(null!=sqlCommand)sqlCommand.Dispose();
        if (null != sqlDataReader) {sqlDataReader.Close();sqlDataReader.Dispose();}
        if (null != sqlConnection) sqlConnection.Close();
      }
    }

    /// <summary>
    /// InsertHeadlines - Inserts headlines by batching them up into groups.
    /// </summary>
    /// <param name="headlines"></param>
    /// <returns></returns>
    public static bool InsertHeadlines(Headlines headlines)
    {
      MySqlConnection sqlConnection=null;
      MySqlTransaction sqlTransaction=null;

      try
      {
        if (null == headlines || 0 == headlines.Count) return false;
        sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
        sqlTransaction = sqlConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
        headlines=new Headlines(headlines.Distinct(new HeadlinesEqualityComparer()).ToList());
        foreach (Headline headline in headlines)
        {
          if(!HeadlineExists(headline,sqlConnection,sqlTransaction))InsertHeadline(headline,sqlConnection,sqlTransaction);
        }
        sqlTransaction.Commit();
        return true;
      }
      catch (Exception exception)
      {
        sqlTransaction?.Rollback();  // This will rollback without explicityl calling Rollback but we'll do this anyway.
        MDTrace.WriteLine(LogLevel.DEBUG,exception);
        return false;
      }
      finally
      {
        if(null!=sqlTransaction)sqlTransaction.Dispose();
        if(null!=sqlConnection) sqlConnection.Close();
      }
    }

    private static bool InsertHeadline(Headline headline,MySqlConnection sqlConnection,MySqlTransaction sqlTransaction)
    {
			MySqlCommand sqlCommand=null;
      String strQuery = null;

      try
      {
        if (null == headline || null == headline.Symbol || null==headline.Entry) return false;
        StringBuilder sb = new StringBuilder();
        sb.Append("insert into Headlines(symbol,asof,headline,source,modified) values(");
        sb.Append(SqlUtils.AddQuotes(headline.Symbol)).Append(",");
        sb.Append(SqlUtils.AddQuotes(SqlUtils.SqlDate(headline.Date))).Append(",");
        sb.Append(SqlUtils.AddQuotes(SqlUtils.SqlString(headline.Entry))).Append(",");
        sb.Append(SqlUtils.AddQuotes(SqlUtils.SqlString(headline.Source))).Append(",");
        if(Utility.IsEpoch(headline.Modified))sb.Append(SqlUtils.AddQuotes(SqlUtils.ToSqlDateTime(DateTime.Now)));
        else sb.Append(SqlUtils.AddQuotes(SqlUtils.ToSqlDateTime(headline.Modified)));
        sb.Append(")");
        strQuery = sb.ToString();
        sqlCommand = new MySqlCommand(strQuery, sqlConnection, sqlTransaction);
        sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
        sqlCommand.ExecuteNonQuery();
        return true;
      }
      catch (Exception exception)
      {
        MDTrace.WriteLine(LogLevel.DEBUG,exception);
        MDTrace.WriteLine(LogLevel.DEBUG,String.Format("Query was '{0}'",strQuery));
        return false;
      }
      finally
      {
				if(null!=sqlCommand)sqlCommand.Dispose();
      }
    }

    private static bool HeadlineExists(Headline headline,MySqlConnection sqlConnection,MySqlTransaction sqlTransaction)
    {
      MySqlDataReader sqlDataReader=null;
			MySqlCommand sqlCommand=null;
      String strQuery = null;

      try
      {
        if (null == headline || null == headline.Symbol) return false;
        StringBuilder sb = new StringBuilder();
        sb.Append("select count(*) from headlines where ");
        sb.Append(" asof=").Append(SqlUtils.AddQuotes(SqlUtils.SqlDate(headline.Date))).Append(" and ");
        sb.Append(" headline=").Append(SqlUtils.AddQuotes(SqlUtils.SqlString(headline.Entry)));
        strQuery = sb.ToString();
        sqlCommand = new MySqlCommand(strQuery, sqlConnection, sqlTransaction);
        sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
        sqlDataReader=sqlCommand.ExecuteReader();
        if(!sqlDataReader.Read())return false;
        return 0==sqlDataReader.GetInt32(0)?false:true;
      }
      catch (Exception exception)
      {
        MDTrace.WriteLine(LogLevel.DEBUG,exception);
        return false;
      }
      finally
      {
				if(null!=sqlCommand)sqlCommand.Dispose();
        if(null!=sqlDataReader){sqlDataReader.Close();sqlDataReader.Dispose();}
      }
    }
  }
}