using System;
using System.Collections.Generic;
using System.Text;
using System.Linq;
using MySql.Data.MySqlClient;
using MarketData.MarketDataModel;
using MarketData.Utils;

namespace MarketData.DataAccess
{
  public class PremarketDA
  {
// SELECT  market, change_value,change_percent,timestamp, STR_TO_DATE(SUBSTRING(TIMESTAMP,12,11),'%h:%i:%s %p') time_of_day,CONVERT(TIMESTAMP,DATE) AS the_day
// FROM premarket WHERE market='S&P' ORDER BY the_day DESC, time_of_day DESC  
    private PremarketDA()
    {
    }
    public static bool AddElements(PremarketElements premarketElements)
    {
      MySqlConnection sqlConnection=null;
      MySqlTransaction sqlTransaction=null;
      String strQuery=null;

      try
      {
        if(null==premarketElements||0==premarketElements.Count) return false;
        sqlConnection=SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
        sqlTransaction=sqlConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
        for(int index=0;index<premarketElements.Count;index++)
        {
          PremarketElement premarketElement=premarketElements[index];
          if(String.IsNullOrEmpty(premarketElement.Market))continue;
          if(Utility.IsEpoch(premarketElement.Timestamp))continue;
          StringBuilder sb=new StringBuilder();
          sb.Append("insert into premarket (market,change_value,change_percent,timestamp)");
          sb.Append("values(");
          sb.Append("'").Append(premarketElement.Market).Append("'").Append(",");
          if(double.NaN.Equals(premarketElement.ChangeValue)) sb.Append("null").Append(",");
          else sb.Append(premarketElement.ChangeValue).Append(","); ;
          if(double.NaN.Equals(premarketElement.ChangePercent)) sb.Append("null").Append(",");
          else sb.Append(premarketElement.ChangePercent).Append(",");
          sb.Append("'").Append(SqlUtils.ToSqlDateTimeTT(premarketElement.Timestamp)).Append("'"); 

          sb.Append(")");
          strQuery=sb.ToString();
          MySqlCommand sqlCommand=new MySqlCommand(strQuery,sqlConnection,sqlTransaction);
          sqlCommand.CommandTimeout=SqlUtils.COMMAND_TIMEOUT;
          sqlCommand.ExecuteNonQuery();
          sqlCommand.Dispose();
        }
        sqlTransaction.Commit();
        return true;
      }
      catch(Exception exception)
      {
        if(null!=sqlTransaction) sqlTransaction.Rollback();
        MDTrace.WriteLine(LogLevel.DEBUG,exception);
        if(null!=strQuery) MDTrace.WriteLine(LogLevel.DEBUG,String.Format("Query was {0}",strQuery));
        return false;
      }
      finally
      {
        if(null!=sqlTransaction) sqlTransaction.Dispose();
        if(null!=sqlConnection) sqlConnection.Close();
      }
    }
    public static PremarketElements GetLatestPremarketData()
    {
      PremarketElements premarketElements=new PremarketElements();
      List<String> markets=GetDistinctMarkets();
      if(null==markets)return null;
      foreach(String market in markets)
      {
        PremarketElement premarketElement=GetLatestPremarketData(market);
        if(null==premarketElement)continue;
        premarketElements.Add(premarketElement);

      }      
      return premarketElements;
    }
    public static PremarketElement GetLatestPremarketData(String market)
    {
      MySqlConnection sqlConnection=null;
      MySqlDataReader sqlDataReader=null;
      MySqlCommand sqlCommand=null;
      String strQuery=null;
      PremarketElement premarketElement=new PremarketElement();

      try
      {
        List<DateTime> availableMarketDates=GetAvailableMarketDates(market);
        if(null==availableMarketDates || 0==availableMarketDates.Count)return null;
        DateTime marketDate=availableMarketDates.Take(1).FirstOrDefault();

        StringBuilder sb=new StringBuilder();
        sqlConnection=SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
        sb.Append("select market,change_value,change_percent,timestamp,STR_TO_DATE(SUBSTRING(TIMESTAMP,12,11),'%h:%i:%s %p') MILITARY_TIME,CONVERT(TIMESTAMP,DATE) DATE from premarket ");
        sb.Append("WHERE MARKET='").Append(market).Append("' and CONVERT(TIMESTAMP,DATE)=").Append(SqlUtils.AddQuotes(SqlUtils.SqlString(SqlUtils.FormatDate(marketDate))));
        sb.Append("ORDER BY MILITARY_TIME DESC LIMIT 10");
        strQuery=sb.ToString();
        sqlCommand=new MySqlCommand(strQuery,sqlConnection);
        sqlCommand.CommandTimeout=SqlUtils.COMMAND_TIMEOUT;
        sqlDataReader=sqlCommand.ExecuteReader();
        if(!sqlDataReader.Read())return null;
        if(!sqlDataReader.IsDBNull(0)) premarketElement.Market=sqlDataReader.GetString(0);
        if(!sqlDataReader.IsDBNull(1)) premarketElement.ChangeValue=sqlDataReader.GetDouble(1);
        if(!sqlDataReader.IsDBNull(2)) premarketElement.ChangePercent=sqlDataReader.GetDouble(2);
        if(!sqlDataReader.IsDBNull(3)) premarketElement.Timestamp=Utility.ParseDate(sqlDataReader.GetString(3));
        if(String.IsNullOrEmpty(premarketElement.Market)) return null;
        return premarketElement;
      }
      catch(Exception exception)
      {
        MDTrace.WriteLine(LogLevel.DEBUG,exception);
        return null;
      }
      finally
      {
        if(null!=sqlCommand) sqlCommand.Dispose();
        if (null != sqlDataReader) {sqlDataReader.Close();sqlDataReader.Dispose();}
        if(null!=sqlConnection) sqlConnection.Close();
      }
    }
    public static DateTime GetLatestMarketDate()
    {
      MySqlConnection sqlConnection=null;
      MySqlDataReader sqlDataReader=null;
      MySqlCommand sqlCommand=null;
      String strQuery=null;
      DateTime latestMarketDate=Utility.Epoch;
      
      try
      {
        StringBuilder sb=new StringBuilder();
        sqlConnection=SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
        sb.Append("SELECT MAX(CONVERT(TIMESTAMP,DATE)) FROM premarket ");
        strQuery=sb.ToString();
        sqlCommand=new MySqlCommand(strQuery,sqlConnection);
        sqlCommand.CommandTimeout=SqlUtils.COMMAND_TIMEOUT;
        sqlDataReader=sqlCommand.ExecuteReader();
        if(sqlDataReader.Read())
        {
          latestMarketDate=sqlDataReader.GetDateTime(0);
        }
        return latestMarketDate;
      }
      catch(Exception exception)
      {
        MDTrace.WriteLine(LogLevel.DEBUG,exception);
        return latestMarketDate;
      }
      finally
      {
        if(null!=sqlCommand) sqlCommand.Dispose();
        if (null != sqlDataReader) {sqlDataReader.Close();sqlDataReader.Dispose();}
        if(null!=sqlConnection) sqlConnection.Close();
      }
    }
    public static List<DateTime> GetAvailableMarketDates(String market)
    {
      MySqlConnection sqlConnection=null;
      MySqlDataReader sqlDataReader=null;
      MySqlCommand sqlCommand=null;
      String strQuery=null;
      List<DateTime> availableMarketDates=new List<DateTime>();
      DateGenerator dateGenerator=new DateGenerator();
      
      try
      {
        StringBuilder sb=new StringBuilder();
        sqlConnection=SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
        sb.Append("SELECT DISTINCT(CONVERT(TIMESTAMP,DATE)) FROM premarket where market=").Append(SqlUtils.AddQuotes(market)).Append(" ORDER BY 1 DESC");
        strQuery=sb.ToString();
        sqlCommand=new MySqlCommand(strQuery,sqlConnection);
        sqlCommand.CommandTimeout=SqlUtils.COMMAND_TIMEOUT;
        sqlDataReader=sqlCommand.ExecuteReader();
        while(sqlDataReader.Read())
        {
          DateTime marketDate=sqlDataReader.GetDateTime(0);
          if(!dateGenerator.IsMarketOpen(marketDate)) continue;
          availableMarketDates.Add(marketDate);
        }
        return availableMarketDates;
      }
      catch(Exception exception)
      {
        MDTrace.WriteLine(LogLevel.DEBUG,exception);
        return null;
      }
      finally
      {
        if(null!=sqlCommand) sqlCommand.Dispose();
        if (null != sqlDataReader) {sqlDataReader.Close();sqlDataReader.Dispose();}
        if(null!=sqlConnection) sqlConnection.Close();
      }
    }
    public static PremarketElements GetLatestPremarketData(String market,DateTime date,bool constrainToMarketHours=true)
    {
      MySqlConnection sqlConnection=null;
      MySqlDataReader sqlDataReader=null;
      MySqlCommand sqlCommand=null;
      String strQuery=null;
      PremarketElements premarketElements=new PremarketElements();
      DateGenerator dateGenerator=new DateGenerator();

      try
      {
        StringBuilder sb=new StringBuilder();
        sqlConnection=SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
        sb.Append("select market,change_value,change_percent,timestamp,STR_TO_DATE(SUBSTRING(TIMESTAMP,12,11),'%h:%i:%s %p') MILITARY_TIME,CONVERT(TIMESTAMP,DATE) DATE from premarket ");
        sb.Append("WHERE MARKET='").Append(market).Append("' and CONVERT(TIMESTAMP,DATE)=").Append(SqlUtils.AddQuotes(SqlUtils.FormatDate(date)));
				if(constrainToMarketHours)sb.Append(" AND STR_TO_DATE(SUBSTRING(TIMESTAMP,12,11),'%h:%i:%s %p')<'16:30:00' ");
        sb.Append(" ORDER BY MILITARY_TIME ASC");
        strQuery=sb.ToString();
        sqlCommand=new MySqlCommand(strQuery,sqlConnection);
        sqlCommand.CommandTimeout=SqlUtils.COMMAND_TIMEOUT;
        sqlDataReader=sqlCommand.ExecuteReader();
        while(sqlDataReader.Read())
        {
          PremarketElement premarketElement=new PremarketElement(); 
          if(!sqlDataReader.IsDBNull(0)) premarketElement.Market=sqlDataReader.GetString(0);
          if(!sqlDataReader.IsDBNull(1)) premarketElement.ChangeValue=sqlDataReader.GetDouble(1);
          if(!sqlDataReader.IsDBNull(2)) premarketElement.ChangePercent=sqlDataReader.GetDouble(2);
          if(!sqlDataReader.IsDBNull(3)) premarketElement.Timestamp=Utility.ParseDate(sqlDataReader.GetString(3));
          if(String.IsNullOrEmpty(premarketElement.Market)) continue;
          if(!dateGenerator.IsMarketOpen(premarketElement.Timestamp))continue;
          premarketElements.Add(premarketElement);
        }
        return premarketElements;
      }
      catch(Exception exception)
      {
        MDTrace.WriteLine(LogLevel.DEBUG,exception);
        return null;
      }
      finally
      {
        if(null!=sqlCommand) sqlCommand.Dispose();
        if (null != sqlDataReader) {sqlDataReader.Close();sqlDataReader.Dispose();}
        if(null!=sqlConnection) sqlConnection.Close();
      }
    }
    public static List<String> GetDistinctMarkets()
    {
      MySqlConnection sqlConnection=null;
      MySqlDataReader sqlDataReader=null;
      MySqlCommand sqlCommand=null;
      String strQuery=null;
      List<String> markets=new List<String>();

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