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 Score
	{
		

		public int FID { get; set; }
		public int H_FS { get; set; }
		public int H_HS { get; set; }
		public int A_FS { get; set; }
		public int A_HS { get; set; }
		public int H_Try { get; set; }
		public int A_Try { get; set; }
		public int H_Convertion { get; set; }
		public int A_Convertion { get; set; }
		public int H_Penalty { get; set; }
		public int A_Penalty { get; set; }
		public int H_Dropgoal { get; set; }
		public int H_PenaltyTry { get; set; }
		public int H_Subs { get; set; }
		public int H_YellowCards { get; set; }
		public int H_RedCards { get; set; }
		public int A_Dropgoal { get; set; }
		public int A_PenaltyTry { get; set; }
		public int A_Subs { get; set; }
		public int A_YellowCards { get; set; }
		public int A_RedCards { get; set; }
		public int H_PorTry { get; set; }
		public int A_PorTry { get; set; }

		public Score GetScore(int CurrentFID)
		{

			Score Row = new Score();
			string sql_string = "SELECT * FROM Scores WHERE FID = '" + CurrentFID + "'";
			//SqlDatabase SQL = new SqlDatabase();
			//SQLiteDataReader DataReader = SQL.GetData(sql_string);


			int count = 0;
			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 = sql_string;

			using (DataReader = sqlite_cmd.ExecuteReader())
			{
				while (DataReader.Read())
				{
					
					Row.FID = Convert.ToInt32(DataReader["FID"]);
					Row.H_FS = Convert.ToInt32(DataReader["H_FS"]);
					Row.H_HS = Convert.ToInt32(DataReader["H_HS"]);
					Row.H_Try = Convert.ToInt32(DataReader["H_Try"]);
					Row.H_Convertion = Convert.ToInt32(DataReader["H_Convertion"]);
					Row.H_Penalty = Convert.ToInt32(DataReader["H_Penalty"]);
					Row.H_Dropgoal = Convert.ToInt32(DataReader["H_Dropgoal"]);
					Row.H_PenaltyTry = Convert.ToInt32(DataReader["H_PenaltyTry"]);
					Row.H_Subs = Convert.ToInt32(DataReader["H_Subs"]);
					Row.H_YellowCards = Convert.ToInt32(DataReader["H_YellowCards"]);
					Row.H_RedCards = Convert.ToInt32(DataReader["H_RedCards"]);
					Row.A_FS = Convert.ToInt32(DataReader["A_FS"]);
					Row.A_HS = Convert.ToInt32(DataReader["A_HS"]);
					Row.A_Try = Convert.ToInt32(DataReader["A_Try"]);
					Row.A_Convertion = Convert.ToInt32(DataReader["A_Convertion"]);
					Row.A_Penalty = Convert.ToInt32(DataReader["A_Penalty"]);
					Row.A_Dropgoal = Convert.ToInt32(DataReader["A_Dropgoal"]);
					Row.A_PenaltyTry = Convert.ToInt32(DataReader["A_PenaltyTry"]);
					Row.A_Subs = Convert.ToInt32(DataReader["A_Subs"]);
					Row.A_YellowCards = Convert.ToInt32(DataReader["A_YellowCards"]);
					Row.A_RedCards = Convert.ToInt32(DataReader["A_RedCards"]);
					Row.H_PorTry = Convert.ToInt32(DataReader["H_PorTry"]);
					Row.A_PorTry = Convert.ToInt32(DataReader["A_PorTry"]);
					count++;




				}
			}
			sqlite_conn.Close();

			if (count == 0) {
				FID = CurrentFID;
				H_FS = 0;
				H_HS = 0;
				H_Try = 0;
				H_Convertion = 0;
				H_Penalty = 0;
				H_Dropgoal = 0;
				H_PenaltyTry = 0;
				H_Subs = 0;
				H_YellowCards = 0;
				H_RedCards = 0;
				A_FS = 0;
				A_HS = 0;
				A_Try = 0;
				A_Convertion = 0;
				A_Penalty = 0;
				A_Dropgoal = 0;
				A_PenaltyTry = 0;
				A_Subs = 0;
				A_YellowCards = 0;
				A_RedCards = 0;
				H_PorTry = 0;
				A_PorTry = 0;
				insert();
			}


			return Row;

		}


		private bool insert()
		{
			try
			{
				string sql_string = "insert into Scores (FID, H_FS,H_HS,H_Try,H_Convertion,H_Penalty," +
					"H_Dropgoal,H_PenaltyTry,H_Subs,H_YellowCards,H_RedCards, A_FS,A_HS,A_Try,A_Convertion,A_Penalty," +
					"A_Dropgoal,A_PenaltyTry,A_Subs,A_YellowCards,A_RedCards,H_PorTry,A_PorTry)" +
					" values ('" + FID + "', '" + H_FS + "','" + H_HS + "','" + H_Try + "'," +
					 "'" + H_Convertion + "','" + H_Penalty + "','" + H_Dropgoal + "','" + H_PenaltyTry + "','" + H_Subs + "'," +
					"'" + H_YellowCards + "','" + H_RedCards + "', '" + A_FS + "','" + A_HS + "','" + A_Try + "'," +
					 "'" + A_Convertion + "','" + A_Penalty + "','" + A_Dropgoal + "','" + A_PenaltyTry + "','" + A_Subs + "'," +
					"'" + A_YellowCards + "','" + A_RedCards + "','" + H_PorTry + "','" + A_PorTry + "')";

				SqlDatabase SQL = new SqlDatabase();
				SQL.Execute(sql_string);
				return true;
			} catch (Exception E) {
				return false;

			}
		}

		public bool Update(string query)
		{
			try
			{
				SqlDatabase SQL = new SqlDatabase();
				SQL.Execute(query);
				return true;
			}
			catch (Exception E)
			{
				return false;

			}
		}



		public bool SetScore(int FID)
		{
			Fixtures FX = new Fixtures();
			Fixtures Fixture = FX.GetFixture(FID);
			
			List<Comments> Commentary = Comments.GetCommentaryData(FID);

			List<int> Home_OnField = Teams.GetLineUP_IDs(FID, Fixture.HomeTeam, Int32.Parse(Fixture.Code));
			List<int> Away_OnField = Teams.GetLineUP_IDs(FID, Fixture.AwayTeam, Int32.Parse(Fixture.Code));

			foreach ( Comments row in Commentary) {

				if (row.PlayerID >0 && row.SID == Fixture.HomeTeam) {
					if (Home_OnField.Contains(row.PlayerID)) {
						Console.WriteLine("Error: "+row.PlayerName+" - "+row.EventName);
					}
				}
			}











			SC TotalTries = GetTotalTries(Commentary, Fixture);
			SC TotalConversions = GetTotalConversion(Commentary, Fixture);
			SC TotalPenalties = GetTotalPenalty(Commentary, Fixture);
			SC TotalDroGoal = GetTotalDropGoal(Commentary, Fixture);
			SC TotalPenaltyTry = GetTotalPenaltyTry(Commentary, Fixture);
			SC TotalPorTry = GetTotalPorTry(Commentary, Fixture);

			//Cards
			SC YellowCards = GetYellowCards(Commentary, Fixture);
			SC RedwCards = GetRedCards(Commentary, Fixture);

			SC Subs = GetSubs(Commentary, Fixture);

			//	SC BlueCards = GetRedCards(Commentary, Fixture);

			int Home_FullTime_Score = GetScore( TotalTries.Home_FTime, TotalConversions.Home_FTime, TotalPenalties.Home_FTime,  TotalDroGoal.Home_FTime,  TotalPenaltyTry.Home_FTime,  TotalPorTry.Home_FTime, Fixture);
			int Away_FullTime_Score = GetScore(TotalTries.Away_FTime, TotalConversions.Away_FTime, TotalPenalties.Away_FTime, TotalDroGoal.Away_FTime, TotalPenaltyTry.Away_FTime, TotalPorTry.Away_FTime, Fixture);

			int Home_HalfTime_Score = GetScore(TotalTries.Home_HTime, TotalConversions.Home_HTime, TotalPenalties.Home_HTime, TotalDroGoal.Home_HTime, TotalPenaltyTry.Home_HTime, TotalPorTry.Home_HTime, Fixture);
			int Away_HalfTime_Score = GetScore(TotalTries.Away_HTime, TotalConversions.Away_HTime, TotalPenalties.Away_HTime, TotalDroGoal.Away_HTime, TotalPenaltyTry.Away_HTime, TotalPorTry.Away_HTime, Fixture);

			//Power Try

			//QUERY is 
			string sql_string = "UPDATE Scores SET " +
					" H_FS = '" + Home_FullTime_Score + "'," +
					" H_HS = '" + Home_HalfTime_Score + "'," +
					" H_Try = '" + TotalTries.Home_FTime + "'," +
					" H_Convertion = '" + TotalConversions.Home_FTime + "'," +
					" H_Penalty = '" + TotalPenalties.Home_FTime + "'," +
					" H_Dropgoal = '" + TotalDroGoal.Home_FTime + "'," +
					" H_PenaltyTry = '" + TotalPenaltyTry.Home_FTime + "'," +
					" H_Subs = '" + Subs.Home_FTime + "'," +
					" H_YellowCards = '" + YellowCards.Home_FTime + "', " +
					" H_RedCards = '" + RedwCards.Home_FTime + "', " +
					" H_PorTry = '" + TotalPorTry.Home_FTime + "', " +

					" A_FS = '" + Away_FullTime_Score + "'," +
					" A_HS = '" + Away_HalfTime_Score + "'," +
					" A_Try = '" + TotalTries.Away_FTime + "'," +
					" A_Convertion = '" + TotalConversions.Away_FTime + "'," +
					" A_Penalty = '" + TotalPenalties.Away_FTime + "'," +
					" A_Dropgoal = '" + TotalDroGoal.Away_FTime + "'," +
					" A_PenaltyTry = '" + TotalPenaltyTry.Away_FTime + "'," +
					" A_Subs = '" + Subs.Away_FTime + "'," +
					" A_YellowCards = '" + YellowCards.Away_FTime + "', " +
					" A_RedCards = '" + RedwCards.Away_FTime + "', " +
					" A_PorTry = '" + TotalPorTry.Away_FTime + "' " +
					
					" WHERE FID = '" + FID + "'; ";
			SqlDatabase SQL = new SqlDatabase();
			bool update = SQL.Execute(sql_string);
			if (update)
			{
				
				return true;
				
			}
			else
			{
				return false;
			}

			
		}

		private int GetScore(int TotalTries, int TotalConversions, int TotalPenalties, int TotalDroGoal, int TotalPenaltyTry, int TotalPorTry,Fixtures Fixture)
		{
			int Total = 0;
			
			int Tries = TotalTries * Fixture.Tries;
			int Conversion = TotalConversions * Fixture.Conversion;
			int Penalties = TotalPenalties * Fixture.Penalties;
			int DropGoal = TotalDroGoal * Fixture.Dropgoal;
			int PenaltyTry = TotalPenaltyTry * Fixture.PenaltyTry;
			int PorTry = TotalPorTry * Fixture.Portry;



			Total = Tries + Conversion + Penalties + DropGoal + PenaltyTry + PorTry;


			return Total;
		}
		private SC GetTotalTries(List<Comments> Commentary, Fixtures Fixture)
		{
			SC total = new SC();
			total.Home_HTime = 0;
			total.Home_FTime = 0;
			total.Away_HTime = 0;
			total.Away_FTime = 0;
			foreach (Comments Comm in Commentary)
			{

				if (Comm.EventID == 2) {
					if (Comm.Session ==0) {
						if (Fixture.HomeTeam == Comm.SID) {
							total.Home_HTime++;
						} else {
							total.Away_HTime++;
						}
						
					}
					if (Fixture.HomeTeam == Comm.SID)
					{
						total.Home_FTime++;
					}
					else
					{
						total.Away_FTime++;
					}
				
				}

			}
			return total;
		}

		private SC GetTotalConversion(List<Comments> Commentary, Fixtures Fixture)
		{

			SC total = new SC();
			total.Home_HTime = 0;
			total.Home_FTime = 0;
			total.Away_HTime = 0;
			total.Away_FTime = 0;
			foreach (Comments Comm in Commentary)
			{

				if (Comm.EventID == 3)
				{
					if (Comm.Session == 0)
					{
						if (Fixture.HomeTeam == Comm.SID)
						{
							total.Home_HTime++;
						}
						else
						{
							total.Away_HTime++;
						}

					}
					if (Fixture.HomeTeam == Comm.SID)
					{
						total.Home_FTime++;
					}
					else
					{
						total.Away_FTime++;
					}
				}

			}
			
			return total;
		}
		private SC GetTotalPenalty(List<Comments> Commentary, Fixtures Fixture)
		{
			SC total = new SC();
			total.Home_HTime = 0;
			total.Home_FTime = 0;
			total.Away_HTime = 0;
			total.Away_FTime = 0;
			foreach (Comments Comm in Commentary)
			{

				if (Comm.EventID == 4)
				{
					if (Comm.Session == 0)
					{
						if (Fixture.HomeTeam == Comm.SID)
						{
							total.Home_HTime++;
						}
						else
						{
							total.Away_HTime++;
						}

					}
					if (Fixture.HomeTeam == Comm.SID)
					{
						total.Home_FTime++;
					}
					else
					{
						total.Away_FTime++;
					}
				}

			}

			return total;
			
		}
		private SC GetTotalDropGoal(List<Comments> Commentary, Fixtures Fixture)
		{
			SC total = new SC();
			total.Home_HTime = 0;
			total.Home_FTime = 0;
			total.Away_HTime = 0;
			total.Away_FTime = 0;
			foreach (Comments Comm in Commentary)
			{

				if (Comm.EventID == 5)
				{
					if (Comm.Session == 0)
					{
						if (Fixture.HomeTeam == Comm.SID)
						{
							total.Home_HTime++;
						}
						else
						{
							total.Away_HTime++;
						}

					}
					if (Fixture.HomeTeam == Comm.SID)
					{
						total.Home_FTime++;
					}
					else
					{
						total.Away_FTime++;
					}
				}

			}

			return total;
		}
		private SC GetTotalPenaltyTry(List<Comments> Commentary, Fixtures Fixture)
		{
			SC total = new SC();
			total.Home_HTime = 0;
			total.Home_FTime = 0;
			total.Away_HTime = 0;
			total.Away_FTime = 0;
			foreach (Comments Comm in Commentary)
			{

				if (Comm.EventID == 6)
				{
					if (Comm.Session == 0)
					{
						if (Fixture.HomeTeam == Comm.SID)
						{
							total.Home_HTime++;
						}
						else
						{
							total.Away_HTime++;
						}

					}
					if (Fixture.HomeTeam == Comm.SID)
					{
						total.Home_FTime++;
					}
					else
					{
						total.Away_FTime++;
					}
				}

			}

			return total;
		}
		private SC GetTotalPorTry(List<Comments> Commentary, Fixtures Fixture)
		{
			SC total = new SC();
			total.Home_HTime = 0;
			total.Home_FTime = 0;
			total.Away_HTime = 0;
			total.Away_FTime = 0;
			foreach (Comments Comm in Commentary)
			{

				if (Comm.EventID == 53)
				{
					if (Comm.Session == 0)
					{
						if (Fixture.HomeTeam == Comm.SID)
						{
							total.Home_HTime++;
						}
						else
						{
							total.Away_HTime++;
						}

					}
					if (Fixture.HomeTeam == Comm.SID)
					{
						total.Home_FTime++;
					}
					else
					{
						total.Away_FTime++;
					}
				}

			}

			return total;
		}

		private SC GetYellowCards(List<Comments> Commentary, Fixtures Fixture)
		{
			SC total = new SC();
			total.Home_HTime = 0;
			total.Home_FTime = 0;
			total.Away_HTime = 0;
			total.Away_FTime = 0;
			foreach (Comments Comm in Commentary)
			{

				if (Comm.EventID == 11)
				{
					if (Comm.Session == 0)
					{
						if (Fixture.HomeTeam == Comm.SID)
						{
							total.Home_HTime++;
						}
						else
						{
							total.Away_HTime++;
						}

					}
					if (Fixture.HomeTeam == Comm.SID)
					{
						total.Home_FTime++;
					}
					else
					{
						total.Away_FTime++;
					}
				}

			}

			return total;
		}
		private SC GetRedCards(List<Comments> Commentary, Fixtures Fixture)
		{
			SC total = new SC();
			total.Home_HTime = 0;
			total.Home_FTime = 0;
			total.Away_HTime = 0;
			total.Away_FTime = 0;
			foreach (Comments Comm in Commentary)
			{

				if (Comm.EventID == 14)
				{
					if (Comm.Session == 0)
					{
						if (Fixture.HomeTeam == Comm.SID)
						{
							total.Home_HTime++;
						}
						else
						{
							total.Away_HTime++;
						}

					}
					if (Fixture.HomeTeam == Comm.SID)
					{
						total.Home_FTime++;
					}
					else
					{
						total.Away_FTime++;
					}
				}

			}

			return total;
		}

		private SC GetBlueCards(List<Comments> Commentary, Fixtures Fixture)
		{
			SC total = new SC();
			total.Home_HTime = 0;
			total.Home_FTime = 0;
			total.Away_HTime = 0;
			total.Away_FTime = 0;
			foreach (Comments Comm in Commentary)
			{

				if (Comm.EventID == 55)
				{
					if (Comm.Session == 0)
					{
						if (Fixture.HomeTeam == Comm.SID)
						{
							total.Home_HTime++;
						}
						else
						{
							total.Away_HTime++;
						}

					}
					if (Fixture.HomeTeam == Comm.SID)
					{
						total.Home_FTime++;
					}
					else
					{
						total.Away_FTime++;
					}
				}

			}

			return total;
		}

		private SC GetSubs(List<Comments> Commentary, Fixtures Fixture)
		{
			SC total = new SC();
			total.Home_HTime = 0;
			total.Home_FTime = 0;
			total.Away_HTime = 0;
			total.Away_FTime = 0;
			foreach (Comments Comm in Commentary)
			{

				if (Comm.EventID == 40 || Comm.EventID == 43 || 
					Comm.EventID == 44 || 
					Comm.EventID == 45 || 
					Comm.EventID == 46 || 
					Comm.EventID == 48 || 
					Comm.EventID == 49 || 
					Comm.EventID == 50 || 
					Comm.EventID == 51)
				{
					if (Comm.Session == 0)
					{
						if (Fixture.HomeTeam == Comm.SID)
						{
							total.Home_HTime++;
						}
						else
						{
							total.Away_HTime++;
						}

					}
					if (Fixture.HomeTeam == Comm.SID)
					{
						total.Home_FTime++;
					}
					else
					{
						total.Away_FTime++;
					}
				}

			}

			return total;
		}

	}

	public struct SC
	{
		public int Home_HTime;
		public int Home_FTime;
		public int Away_HTime;
		public int Away_FTime;
	}
}