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

namespace MarketData.DataAccess
{
// Persistence for Zacks Earnings Announcements
  public class EarningsAnnouncementsDA
  {
    private EarningsAnnouncementsDA()
    {
    }
    public static DateTime? GetNextEarningsDate(String symbol)
    {
      MySqlConnection sqlConnection = null;
      MySqlDataReader sqlDataReader = null;
      MySqlCommand sqlCommand=null;
      String strQuery = null;
      List<DateTime> earningsDates=new List<DateTime>();
      DateTime currentDate=DateTime.Now.Date;
      DateGenerator dateGenerator=new DateGenerator();

      try
      {
        StringBuilder sb = new StringBuilder();
        sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
        sb.Append("select date from earningsannouncements where symbol=").Append(SqlUtils.AddQuotes(symbol)).Append(" order by date desc limit 2");
        strQuery = sb.ToString(); ;
        sqlCommand = new MySqlCommand(strQuery, sqlConnection);
        sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
        sqlDataReader = sqlCommand.ExecuteReader();
        while (sqlDataReader.Read())
        {
          earningsDates.Add(sqlDataReader.GetDateTime(0));
        }
        if(2!=earningsDates.Count)return null;
        DateTime latestAnnouncementDate=earningsDates[0];
        DateTime previousAnnouncementDate=earningsDates[1];
        if(latestAnnouncementDate>currentDate)return latestAnnouncementDate;
        TimeSpan daysBetween=latestAnnouncementDate-previousAnnouncementDate;
        return dateGenerator.GetNextBusinessDay(latestAnnouncementDate+daysBetween);
      }
      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 EarningsAnnouncements GetEarningsAnnouncements(String symbol)
    {
      MySqlConnection sqlConnection = null;
      MySqlDataReader sqlDataReader = null;
      MySqlCommand sqlCommand=null;
      String strQuery = null;
      EarningsAnnouncements earningsAnnouncements=new EarningsAnnouncements();

      try
      {
        StringBuilder sb = new StringBuilder();
        sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
        sb.Append("select symbol,date,period_ending,estimate,reported,surprise,surprise_pct,time from earningsannouncements where symbol=").Append(SqlUtils.AddQuotes(symbol)).Append(" order by date desc");
        strQuery = sb.ToString(); ;
        sqlCommand = new MySqlCommand(strQuery, sqlConnection);
        sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
        sqlDataReader = sqlCommand.ExecuteReader();
        while (sqlDataReader.Read())
        {
          EarningsAnnouncement earningsAnnouncement=new EarningsAnnouncement();
          earningsAnnouncement.Symbol=symbol;
          earningsAnnouncement.Date=sqlDataReader.GetDateTime(1);
          if(!sqlDataReader.IsDBNull(2))earningsAnnouncement.PeriodEnding=sqlDataReader.GetDateTime(2);
          if(!sqlDataReader.IsDBNull(3))earningsAnnouncement.Estimate=sqlDataReader.GetDouble(3);
          if(!sqlDataReader.IsDBNull(4))earningsAnnouncement.Reported=sqlDataReader.GetDouble(4);
          if(!sqlDataReader.IsDBNull(5))earningsAnnouncement.Surprise=sqlDataReader.GetDouble(5);
          if(!sqlDataReader.IsDBNull(6))earningsAnnouncement.SurprisePct=sqlDataReader.GetDouble(6);
          if(!sqlDataReader.IsDBNull(7))earningsAnnouncement.Time=sqlDataReader.GetString(7);
          earningsAnnouncements.Add(earningsAnnouncement);
        }
        return earningsAnnouncements;
      }
      catch (Exception exception)
      {
        MDTrace.WriteLine(LogLevel.DEBUG,exception);
        return earningsAnnouncements;
      }
      finally
      {
        if(null!=sqlCommand)sqlCommand.Dispose();
        if (null != sqlDataReader) {sqlDataReader.Close();sqlDataReader.Dispose();}
        if (null != sqlConnection) sqlConnection.Close();
      }
    }
    public static EarningsAnnouncement GetLatestEarningsAnnouncement(String symbol)
    {
      MySqlConnection sqlConnection = null;
      MySqlDataReader sqlDataReader = null;
      MySqlCommand sqlCommand=null;
      String strQuery = null;
      EarningsAnnouncement earningsAnnouncement=null;

      try
      {
        StringBuilder sb = new StringBuilder();
        sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
        sb.Append("select symbol,date,period_ending,estimate,reported,surprise,surprise_pct,time from earningsannouncements where symbol=").Append(SqlUtils.AddQuotes(symbol));
        sb.Append("and date=(select max(date) from earningsannouncements where symbol=").Append(SqlUtils.AddQuotes(symbol)).Append(")");
        strQuery = sb.ToString(); ;
        sqlCommand = new MySqlCommand(strQuery, sqlConnection);
        sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
        sqlDataReader = sqlCommand.ExecuteReader();
        if(!sqlDataReader.Read())return null;
        earningsAnnouncement=new EarningsAnnouncement();
        earningsAnnouncement.Symbol=symbol;
        earningsAnnouncement.Date=sqlDataReader.GetDateTime(1);
        if(!sqlDataReader.IsDBNull(2))earningsAnnouncement.PeriodEnding=sqlDataReader.GetDateTime(2);
        if(!sqlDataReader.IsDBNull(3))earningsAnnouncement.Estimate=sqlDataReader.GetDouble(3);
        if(!sqlDataReader.IsDBNull(4))earningsAnnouncement.Reported=sqlDataReader.GetDouble(4);
        if(!sqlDataReader.IsDBNull(5))earningsAnnouncement.Surprise=sqlDataReader.GetDouble(5);
        if(!sqlDataReader.IsDBNull(6))earningsAnnouncement.SurprisePct=sqlDataReader.GetDouble(6);
        if(!sqlDataReader.IsDBNull(7))earningsAnnouncement.Time=sqlDataReader.GetString(7);
        return earningsAnnouncement;
      }
      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 bool InsertEarningsAnnouncements(EarningsAnnouncements earningsAnnouncements)
    {
      MySqlCommand sqlCommand=null;
      MySqlConnection sqlConnection=null;
      MySqlTransaction sqlTransaction=null;

      try
      {
        if (null == earningsAnnouncements || 0 == earningsAnnouncements.Count) return false;
        StringBuilder sb = new StringBuilder();
        sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
        sqlTransaction = sqlConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
        foreach(EarningsAnnouncement earningsAnnouncement in earningsAnnouncements)
        {
          if(!EarningsAnnouncementExists(earningsAnnouncement,sqlConnection,sqlTransaction))InsertEarningsAnnouncement(earningsAnnouncement,sqlConnection,sqlTransaction);
        }
        sqlTransaction.Commit();
        return true;
      }
      catch (Exception exception)
      {
        MDTrace.WriteLine(LogLevel.DEBUG,exception);
        return false;
      }
      finally
      {
        if(null!=sqlCommand)sqlCommand.Dispose();
        if(null!=sqlTransaction)sqlTransaction.Dispose();
        if(null!=sqlConnection) sqlConnection.Close();
      }
    }
    private static bool InsertEarningsAnnouncement(EarningsAnnouncement earningsAnnouncement,MySqlConnection sqlConnection,MySqlTransaction sqlTransaction)
    {
      MySqlCommand sqlCommand=null;
      String strQuery = null;

      try
      {
        if (null == earningsAnnouncement || null == earningsAnnouncement.Symbol || Utility.IsEpoch(earningsAnnouncement.Date) || Utility.IsEpoch(earningsAnnouncement.PeriodEnding)) return false;
        StringBuilder sb = new StringBuilder();
        sb.Append("insert into EarningsAnnouncements(symbol,date,period_ending,estimate,reported,surprise,surprise_pct,time,modified) values(");
        sb.Append(SqlUtils.AddQuotes(earningsAnnouncement.Symbol)).Append(",");
        sb.Append(SqlUtils.AddQuotes(SqlUtils.SqlDate(earningsAnnouncement.Date))).Append(",");
        sb.Append(SqlUtils.AddQuotes(SqlUtils.SqlDate(earningsAnnouncement.PeriodEnding))).Append(",");
        if(double.IsNaN(earningsAnnouncement.Estimate))sb.Append("null").Append(",");
        else sb.Append(earningsAnnouncement.Estimate).Append(",");
        if(double.IsNaN(earningsAnnouncement.Reported))sb.Append("null").Append(",");
        else sb.Append(earningsAnnouncement.Reported).Append(",");
        if(double.IsNaN(earningsAnnouncement.Surprise))sb.Append("null").Append(",");
        else sb.Append(earningsAnnouncement.Surprise).Append(",");
        if(double.IsNaN(earningsAnnouncement.SurprisePct))sb.Append("null").Append(",");
        else sb.Append(earningsAnnouncement.SurprisePct).Append(",");
        if(null==earningsAnnouncement.Time)sb.Append("null").Append(",");
        else sb.Append(SqlUtils.AddQuotes(earningsAnnouncement.Time)).Append(",");
        sb.Append(SqlUtils.AddQuotes(SqlUtils.ToSqlDateTime(DateTime.Now)));
        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 EarningsAnnouncementExists(EarningsAnnouncement earningsAnnouncement,MySqlConnection sqlConnection,MySqlTransaction sqlTransaction)
    {
      MySqlDataReader sqlDataReader=null;
      MySqlCommand sqlCommand=null;
      String strQuery = null;

      try
      {
        if (null == earningsAnnouncement || null == earningsAnnouncement.Symbol) return false;
        StringBuilder sb = new StringBuilder();
        sb.Append("select count(*) from earningsannouncements where symbol=").Append(SqlUtils.AddQuotes(earningsAnnouncement.Symbol)).Append(" and ");
        sb.Append(" date=").Append(SqlUtils.AddQuotes(SqlUtils.SqlDate(earningsAnnouncement.Date)));
        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();}
      }
    }
  }
}