using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Finisar.SQLite;
using System.Windows;

namespace Live_Scoring_V1
{
	class Fixtures
	{

		public int FID { get; set; }
		public int TID { get; set; }
		public int Allow22MeterTry { get; set; }
		public int Tries { get; set; }
		public int Conversion { get; set; }
		public int Penalties { get; set; }
		public int Dropgoal { get; set; }
		public int PenaltyTry { get; set; }
		public int Portry { get; set; }
		
		public int HomeTeam { get; set; }
		public int AwayTeam { get; set; }
		public string TournName { get; set; }
		public string HomeName { get; set; }
		public string AwayName { get; set; }
		public string StartTime { get; set; }
		public string FixtureDate { get; set; }
		public string Location { get; set; }
		public string Code { get; set; }
		public int Game_status { get; set; }
		public string Game_Duration { get; set; }
		public string Playing_Time { get; set; }
		public string Running_time { get; set; }
		public string Total_Stoppage { get; set; }
		public int Game_Paused { get; set; }
		public int YellowCard_Counter { get; set; }

		public int HIA_Time { get; set; }

		public int YellowCardTimer { get; set; }
		public int BloodTimer { get; set; }
		public int HIATimer { get; set; }

        public string GameReferee { get; set; }
        public string AssistantReferee { get; set; }
        public string TelevisionReferee { get; set; }

        //SCores
        public int H_FS { get; set; }
		public int A_FS { get; set; }
		public int H_HS { get; set; }
		public int A_HS { get; set; }
		public string HalfTime { get; set; }

		public Fixtures GetFixtureOld(int FID)
		{

			Fixtures Evts = new Fixtures();
			SQLiteDataReader DataReader = null;
			SQLiteConnection sqlite_conn;
			SQLiteCommand sqlite_cmd;
            string dbPath = (Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData)) + "\\Sideline Manager\\";
            try
			{
				sqlite_conn = new SQLiteConnection("Data Source="+ dbPath +"Footprint.db;Version=3;");

				sqlite_conn.Open();


				sqlite_cmd = sqlite_conn.CreateCommand();
				sqlite_cmd.CommandText = "SELECT * FROM Fixture WHERE FID = '" + FID + "'";

				using (DataReader = sqlite_cmd.ExecuteReader())
				{
					while (DataReader.Read())
					{


						int Status = 0;
						try
						{
							Status = Convert.ToInt32(DataReader["Game_status"]);
						}
						catch
						{
							Status = 0;
						}
						int Paused = 0;
						try
						{
							Paused = Convert.ToInt32(DataReader["Game_Paused"]);
						}
						catch
						{
							Paused = 0;
						}
						

						Evts.FID = Convert.ToInt32(DataReader["FID"]);
                        Evts.GameReferee = Convert.ToString(DataReader["GameReferee"]);
                        Evts.AssistantReferee = Convert.ToString(DataReader["AssistantReferee"]);
                        Evts.TelevisionReferee = Convert.ToString(DataReader["TelevisionReferee"]);
                        Evts.HomeTeam = Convert.ToInt32(DataReader["HomeTeam"]);
                        Evts.AwayTeam = Convert.ToInt32(DataReader["AwayTeam"]);
						Evts.TournName = Convert.ToString(DataReader["TournName"]);
						Evts.HomeName = Convert.ToString(DataReader["HomeName"]);
						Evts.AwayName = Convert.ToString(DataReader["AwayName"]);
						Evts.StartTime = Convert.ToString(DataReader["StartTime"]);
						Evts.FixtureDate = Convert.ToString(DataReader["FixtureDate"]);
						Evts.Location = Convert.ToString(DataReader["Location"]);
						Evts.TID = Convert.ToInt32(DataReader["TID"]);
						Evts.Allow22MeterTry = Convert.ToInt32(DataReader["Allow22MeterTry"]);
						Evts.Tries = Convert.ToInt32(DataReader["Tries"]);
						Evts.Conversion = Convert.ToInt32(DataReader["Conversion"]);
						Evts.Penalties = Convert.ToInt32(DataReader["Penalties"]);
						Evts.PenaltyTry = Convert.ToInt32(DataReader["PenaltyTry"]);
						Evts.Dropgoal = Convert.ToInt32(DataReader["Dropgoal"]);
						Evts.Portry = Convert.ToInt32(DataReader["Portry"]);
						Evts.Code = Convert.ToString(DataReader["Code"]);
						Evts.Game_status = Status;
						Evts.Game_Duration = Convert.ToString(DataReader["Game_Duration"]);
						Evts.Playing_Time = Convert.ToString(DataReader["Playing_Time"]);
						Evts.Running_time = Convert.ToString(DataReader["Running_time"]);
						Evts.Total_Stoppage = Convert.ToString(DataReader["Total_Stoppage"]);
                        Evts.YellowCard_Counter = Convert.ToInt32(DataReader["YellowCard_Counter"]);
                        Evts.YellowCardTimer = Convert.ToInt32(DataReader["YellowCardTimer"]);
                        Evts.BloodTimer = Convert.ToInt32(DataReader["BloodTimer"]);
                        Evts.HIATimer = Convert.ToInt32(DataReader["HIATimer"]);


                        Evts.Game_Paused = Paused;

					}

				}
				sqlite_conn.Close();
			}
			catch (Exception e)
			{
				MessageBox.Show(e.ToString());
			}


			return Evts;

		}

        public bool IsGamePaused(int FID)
        {
            bool paused = false;
            SQLiteDataReader DataReader = null;
            SQLiteConnection sqlite_conn;
            SQLiteCommand sqlite_cmd;
            string dbPath = (Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData)) + "\\Sideline Manager\\";

            try
            {
                sqlite_conn = new SQLiteConnection("Data Source=" + dbPath + "Footprint.db;Version=3;");
                sqlite_conn.Open();
                sqlite_cmd = sqlite_conn.CreateCommand();
                sqlite_cmd.CommandText = "SELECT * FROM Fixture WHERE FID = '" + FID + "'";

                int Status = 0;

                using (DataReader = sqlite_cmd.ExecuteReader())
                {
                    while (DataReader.Read())
                    {
                        Status = 0;
                        try
                        {
                            Status = Convert.ToInt32(DataReader["Game_Paused"]);
                        }
                        catch
                        {
                            Status = 0;
                        }
                    }
                }
                if (Status == 1)
                {
                    paused = true;
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.ToString());
            }
            return paused;
        }

        public Fixtures GetFixture(int FID)
		{

			Fixtures Evts = new Fixtures();
			SQLiteDataReader DataReader = null;
			SQLiteConnection sqlite_conn;
			SQLiteCommand sqlite_cmd;
            string dbPath = (Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData)) + "\\Sideline Manager\\";
            int count = 0;
			try
			{
				sqlite_conn = new SQLiteConnection("Data Source="+ dbPath +"Footprint.db;Version=3;");

				sqlite_conn.Open();
				

				sqlite_cmd = sqlite_conn.CreateCommand();
				sqlite_cmd.CommandText = "SELECT * FROM Fixture WHERE FID = '" + FID + "'";
				
				using (DataReader = sqlite_cmd.ExecuteReader())
				{
					while (DataReader.Read())
					{
						

						int Status = 0;
						try
						{
							Status = Convert.ToInt32(DataReader["Game_status"]);
						}
						catch
						{
							Status = 0;
						}
						int Paused = 0;
						try
						{
							Paused = Convert.ToInt32(DataReader["Game_Paused"]);
						}
						catch
						{
							Paused = 0;
						}

						//Hard coded defaults if these are not supplied in footprint
                        int YellowCardCounter = 0;
                        try
                        {
                            YellowCardCounter = Convert.ToInt32(DataReader["YellowCardCounter"]);
                        }
                        catch
                        {
                            YellowCardCounter = 10;
                        }

						int HiaTime = 0;
						try
						{
							HiaTime = Convert.ToInt32(DataReader["HIA_Time"]);
						}
						catch
						{
							HiaTime = 10;
						}
						// Game Event Timers
						int YellowCardTimer = 0;
                        try
                        {
                            YellowCardTimer = Convert.ToInt32(DataReader["YellowCardTimer"]);
                        }
                        catch
                        {
                            YellowCardTimer = 10;
                        }
						int BloodTimer = 0;
                        try
                        {
                            BloodTimer = Convert.ToInt32(DataReader["BloodTimer"]);
                        }
                        catch
                        {
                            BloodTimer = 15;
                        }
						int HIATimer = 0;
                        try
                        {
                            HIATimer = Convert.ToInt32(DataReader["HIATimer"]);
                        }
                        catch
                        {
                            HIATimer = 12;
                        }


                        Evts.HalfTime = DataReader["HalfTime"].ToString();
						Evts.FID = Convert.ToInt32(DataReader["FID"]);
                        Evts.HomeTeam = Convert.ToInt32(DataReader["HomeTeam"]);
						Evts.AwayTeam = Convert.ToInt32(DataReader["AwayTeam"]);
						Evts.TournName = Convert.ToString(DataReader["TournName"]);
						Evts.HomeName = Convert.ToString(DataReader["HomeName"]);
						Evts.AwayName = Convert.ToString(DataReader["AwayName"]);
						Evts.StartTime = Convert.ToString(DataReader["StartTime"]);
						Evts.FixtureDate = Convert.ToString(DataReader["FixtureDate"]);
						Evts.Location = Convert.ToString(DataReader["Location"]);
						Evts.TID = Convert.ToInt32(DataReader["TID"]);
						try
						{
                            Evts.Allow22MeterTry = Convert.ToInt32(DataReader["Allow22MeterTry"]);
                        }
						catch (Exception)
						{
                            Evts.Allow22MeterTry = 0;
                        }
                        Evts.Tries = Convert.ToInt32(DataReader["Tries"]);
						Evts.Conversion = Convert.ToInt32(DataReader["Conversion"]);
						Evts.Penalties = Convert.ToInt32(DataReader["Penalties"]);
						Evts.PenaltyTry = Convert.ToInt32(DataReader["PenaltyTry"]);
						Evts.Dropgoal = Convert.ToInt32(DataReader["Dropgoal"]);
						Evts.Portry = Convert.ToInt32(DataReader["Portry"]);
						Evts.Code = Convert.ToString(DataReader["Code"]);
						Evts.Game_status = Status;
						Evts.Game_Duration = Convert.ToString(DataReader["Game_Duration"]);
						Evts.Playing_Time = Convert.ToString(DataReader["Playing_Time"]);
						Evts.Running_time = Convert.ToString(DataReader["Running_time"]);
						Evts.Total_Stoppage = Convert.ToString(DataReader["Total_Stoppage"]);
						Evts.YellowCard_Counter = YellowCardCounter;
						Evts.Game_Paused = Paused;
						Evts.HIA_Time = HiaTime;
                        Evts.YellowCardTimer = YellowCardTimer;
                        Evts.BloodTimer = BloodTimer;
                        Evts.HIATimer = HIATimer;


                        Evts.GameReferee = Convert.ToString(DataReader["GameReferee"]);
                        Evts.AssistantReferee = Convert.ToString(DataReader["AssistantReferee"]);
                        Evts.TelevisionReferee = Convert.ToString(DataReader["TelevisionReferee"]);

                        count++;


					}

				}
				sqlite_conn.Close();
			} catch (Exception e) {
				MessageBox.Show(e.ToString());
			}

			
			return Evts;

		}

		public List<Fixtures> GetFixtures()
		{

			List<Fixtures> List = new List<Fixtures>();
			SQLiteDataReader DataReader = null;
			SQLiteConnection sqlite_conn;
			SQLiteCommand sqlite_cmd;
            string dbPath = (Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData)) + "\\Sideline Manager\\";

            sqlite_conn = new SQLiteConnection("Data Source="+ dbPath +"Footprint.db;Version=3;");

			sqlite_conn.Open();


			sqlite_cmd = sqlite_conn.CreateCommand();
			sqlite_cmd.CommandText = "SELECT * FROM Fixture " +
				" LEFT JOIN Scores ON Scores.FID = Fixture.FID " +
				" Order by FixtureDate ASC , StartTime ASC";

			using (DataReader = sqlite_cmd.ExecuteReader())
			{
				while (DataReader.Read())
				{
					Fixtures Evts = new Fixtures();

					int Status = 0;
					try
					{
						Status = Convert.ToInt32(DataReader["Game_status"]);
					}
					catch
					{
						Status = 0;
					}
					int H_FS = 0;
					if (DataReader["H_FS"].ToString() != "") {
						H_FS = Convert.ToInt32(DataReader["H_FS"]);
					}
					int A_FS = 0;
					if (DataReader["A_FS"].ToString() != "")
					{
						A_FS = Convert.ToInt32(DataReader["A_FS"]);
					}
					int H_HS = 0;
					if (DataReader["H_HS"].ToString() != "")
					{
						H_HS = Convert.ToInt32(DataReader["H_HS"]);
					}
					int A_HS = 0;
					if (DataReader["A_HS"].ToString() != "")
					{
						A_HS = Convert.ToInt32(DataReader["A_HS"]);
					}
                    int YellowCardCounter = 0;
                    try
                    {
                        YellowCardCounter = Convert.ToInt32(DataReader["YellowCardCounter"]);
                    }
                    catch
                    {
                        YellowCardCounter = 0;
                    }
                    int HomeTeam = 0;
                    try
                    {
                        HomeTeam = Convert.ToInt32(DataReader["HomeTeam"]);
                    }
                    catch
                    {
                        HomeTeam = 0;
                    }
                    int AwayTeam = 0;
                    try
                    {
                        AwayTeam = Convert.ToInt32(DataReader["AwayTeam"]);
                    }
                    catch
                    {
                        AwayTeam = 0;
                    }
                    int TID = 0;
                    try
                    {
                        TID = Convert.ToInt32(DataReader["TID"]);
                    }
                    catch
                    {
                        TID = 0;
                    }
					int Allow22MeterTry = 0;
					try
					{
						Allow22MeterTry = Convert.ToInt32(DataReader["Allow22MeterTry"]);
					}
					catch
					{
						Allow22MeterTry = 0;
					}
					int PenaltyTry = 0;
					try
					{
						PenaltyTry = Convert.ToInt32(DataReader["PenaltyTry"]);
					}
					catch
					{
						PenaltyTry = 0;
					}
					int Portry = 0;
					try
					{
						Portry = Convert.ToInt32(DataReader["Portry"]);
					}
					catch
					{
						Portry = 0;
					}

                    // Game Event Timers
                    int YellowCardTimer = 0;
                    try
                    {
                        YellowCardTimer = Convert.ToInt32(DataReader["YellowCardTimer"]);
                    }
                    catch
                    {
                        YellowCardTimer = 10;
                    }
                    int BloodTimer = 0;
                    try
                    {
                        BloodTimer = Convert.ToInt32(DataReader["BloodTimer"]);
                    }
                    catch
                    {
                        BloodTimer = 15;
                    }
                    int HIATimer = 0;
                    try
                    {
                        HIATimer = Convert.ToInt32(DataReader["HIATimer"]);
                    }
                    catch
                    {
                        HIATimer = 12;
                    }


                    Evts.HalfTime = DataReader["HalfTime"].ToString();
					Evts.FID = Convert.ToInt32(DataReader["FID"]);
					Evts.HomeTeam = HomeTeam;
					Evts.AwayTeam = AwayTeam;
                    Evts.TournName = Convert.ToString(DataReader["TournName"]);
					Evts.HomeName = Convert.ToString(DataReader["HomeName"]);
					Evts.AwayName = Convert.ToString(DataReader["AwayName"]);
					Evts.StartTime = Convert.ToString(DataReader["StartTime"]);
					Evts.FixtureDate = Convert.ToString(DataReader["FixtureDate"]);
					Evts.Location = Convert.ToString(DataReader["Location"]);
					Evts.TID = TID;
					Evts.Allow22MeterTry = Allow22MeterTry;
					Evts.Tries = Convert.ToInt32(DataReader["Tries"]);
					Evts.Conversion = Convert.ToInt32(DataReader["Conversion"]);
					Evts.Penalties = Convert.ToInt32(DataReader["Penalties"]);
					Evts.PenaltyTry = PenaltyTry;
					Evts.Dropgoal = Convert.ToInt32(DataReader["Dropgoal"]);
					Evts.Portry = Portry;
					Evts.Code = Convert.ToString(DataReader["Code"]);
					Evts.Game_status = Status;
					Evts.Game_Duration = Convert.ToString(DataReader["Game_Duration"]);
					Evts.Playing_Time = Convert.ToString(DataReader["Playing_Time"]);
					Evts.Running_time = Convert.ToString(DataReader["Running_time"]);
					Evts.Total_Stoppage = Convert.ToString(DataReader["Total_Stoppage"]);
					Evts.YellowCard_Counter = YellowCardCounter;
                    Evts.YellowCardTimer = YellowCardTimer;
                    Evts.BloodTimer = BloodTimer;
                    Evts.HIATimer = HIATimer;
                    Evts.H_FS = H_FS;
					Evts.A_FS = A_FS;
					Evts.H_HS = H_HS;
					Evts.A_HS = A_HS;
                    Evts.GameReferee = Convert.ToString(DataReader["GameReferee"]);
                    Evts.AssistantReferee = Convert.ToString(DataReader["AssistantReferee"]);
                    Evts.TelevisionReferee = Convert.ToString(DataReader["TelevisionReferee"]);

                    List.Add(Evts);
				}

			}
			sqlite_conn.Close();
			//MessageBox.Show(List.ToString());
			return List;

		}

        public void SetOfflineFixture(Dictionary<string, string> Fixture)
        {
            string sql;
            SQLiteConnection sqlite_conn;
            string dbPath = (Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData)) + "\\Sideline Manager\\";

            sqlite_conn = new SQLiteConnection("Data Source="+ dbPath +"Footprint.db;Version=3;");

			sqlite_conn.Open();

            try
            {
                sql = "UPDATE Fixture SET Current = 0  WHERE Current = 1; ";

                var command = new SQLiteCommand(sql, sqlite_conn);
                command.ExecuteNonQuery();

                sql = "insert into Fixture " +
                        "(" +
                        "HalfTime," +
                        "Sanction_Time," +
                        "BloodBin_Time," +
                        "HalfTimeLength," +
                        "HIA_Time," +
                        "Location," +
                        "FixtureDate," +
                        "HomeName," +
                        "AwayName," +
                        "Extra_Time," +
                        "ExtraTimeHalfLength," +
                        "Tries," +
                        "Conversion," +
                        "Penalties," +
                        "Dropgoal," +
                        "Referee," +
                        "AssistantReferee1," +
                        "AssistantReferee2," +
                        "TMO," +
                        "Timekeeper" +
                        "YellowCardTimer," +
                        "BloodTimer," +
                        "HIATimer," +
                        ")" +
                        " values " +
                        "(" + "'" + 
                        Fixture["HalfLength"] + "','" + 
                        Fixture["SanctionTime"] + "','" +
                        Fixture["BloodBinTime"] + "','" +
                        Fixture["HalfTimeLength"] + "','" +
                        Fixture["HIATime"] + "','" +
                        Fixture["MatchVenue"] + "','" +
                        Fixture["MatchDate"] + "','" +
                        Fixture["Team1"] + "','" +
                        Fixture["Team2"] + "','" +
                        Fixture["ExtraTime"] + "','" +
                        Fixture["ExtraTimeHalfLength"] + "','" +
                        Fixture["Try"] + "','" +
                        Fixture["Conversion"] + "','" +
                        Fixture["Penalty"] + "','" +
                        Fixture["DroppedGoal"] + "','" +
                        Fixture["Referee"] + "','" +
                        Fixture["AssistantReferee1"] + "','" +
                        Fixture["AssistantReferee2"] + "','" +
                        Fixture["YellowCardTimer"] + "','" +
                        Fixture["BloodTimer"] + "','" +
                        Fixture["HIATimer"] + "','" +
                        Fixture["TMO"] + "','" +
                        Fixture["Timekeeper"] +
                        "')";

                Console.WriteLine(sql);
                command = new SQLiteCommand(sql, sqlite_conn);
                command.ExecuteNonQuery();
            }
            catch (Exception error)
            {
                //sql = "UPDATE Fixture SET Current = 0  WHERE Current = 1; ";

                //var command = new SQLiteCommand(sql, sqlite_conn);
                //command.ExecuteNonQuery();

                //sql = "UPDATE Fixture SET Current = 1," +
                //    " FID = '" + Fixture.FID + "'," +
                //    " FixtureDate = '" + Fixture.FixtureDate + "'," +
                //    " StartTime = '" + Fixture.StartTime + "'," +
                //    " Location = '" + Fixture.Location + "'," +
                //    " HomeTeam = '" + Fixture.HomeTeam + "'," +
                //    " AwayTeam = '" + Fixture.AwayTeam + "'," +
                //    " FixtureNumber = '" + Fixture.FixtureNumber + "'," +
                //    " StageName = '" + Fixture.StageName + "'," +
                //    " RoundNumber = '" + Fixture.RoundNumber + "'," +
                //    " HomeName = '" + Fixture.HomeName + "', " +
                //    " AwayName = '" + Fixture.AwayName + "', " +
                //    " HomeAbbr = '" + Fixture.HomeAbbr + "', " +
                //    " AwayAbbr = '" + Fixture.AwayAbbr + "', " +
                //    " Allow22MeterTry = '" + Fixture.Allow22MeterTry + "', " +
                //    " Tries = '" + Fixture.Tries_Points + "', " +
                //    " Conversion = '" + Fixture.Conversion_Points + "', " +
                //    " Penalties = '" + Fixture.Penalties_Points + "', " +
                //    " Dropgoal = '" + Fixture.Dropgoal_Points + "', " +
                //    " PenaltyTry = '" + Fixture.PenaltyTry_Points + "', " +
                //    " Portry = '" + Fixture.Portry_Points + "', " +
                //    " Code = '" + Fixture.Code + "', " +
                //    " TID = '" + Fixture.TID + "' ," +
                //    " YellowCardCounter = '" + Fixture.YELLOWCARDCOUNTER + "' ," +
                //    " HalfTime = '" + Fixture.HtimeMin + "' ," +
                //    " TournName = '" + Fixture.TournName + "'  " +
                //    " WHERE FID = '" + Fixture.FID + "'; ";


                //command = new SQLiteCommand(sql, sqlite_conn);
                //command.ExecuteNonQuery();
            }

            sqlite_conn.Close();

        }














    }
}