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

namespace Live_Scoring_V1
{
	class Comments
	{

		public int FID { get; set; }
		public string ID { get; set; }
		public int Session { get; set; }
		public int SID { get; set; }
		public int EventID { get; set; }
		public int PlayerID { get; set; }
		public int ReplacementID { get; set; }
	
		public string Time { get; set; }
		public string SquadName { get; set; }
		public string EventName { get; set; }
		public string PlayerName { get; set; }
		public string ReplacementName { get; set; }

		public string Comment { get; set; }
		public int Subs { get; set; }
		public static Comments GetRow(string ID)
		{
            string dbPath = (Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData)) + "\\Sideline Manager\\";
            Comments Comments = new Comments();



			SQLiteDataReader DataReader = null;
			SQLiteConnection sqlite_conn;
			SQLiteCommand sqlite_cmd;

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

			sqlite_conn.Open();


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

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

					Comments.FID = Convert.ToInt32(DataReader["FID"]);
					Comments.ID = Convert.ToString(DataReader["ID"]);
					Comments.Session = Convert.ToInt32(DataReader["Session"]);
					Comments.EventID = Convert.ToInt32(DataReader["EventID"]);
					Comments.PlayerID = Convert.ToInt32(DataReader["PlayerID"]);
					Comments.ReplacementID = Convert.ToInt32(DataReader["ReplcamentID"]);
					Comments.SID = Convert.ToInt32(DataReader["SID"]);
					Comments.Time = Convert.ToString(DataReader["Time"]);
					Comments.EventName = Convert.ToString(DataReader["EventName"]);
					Comments.SquadName = Convert.ToString(DataReader["SquadName"]);
					Comments.Comment = Convert.ToString(DataReader["Comment"]);

					

				}

			}
			sqlite_conn.Close();




			return Comments;
		}

		public static List <Comments> GetCommentaryData(int _FID)
		{
			List<Comments> Comments = new List<Comments>();



			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 Commentary_view WHERE FID = '" + _FID + "' Order by Session ,No ASC";

			using (DataReader = sqlite_cmd.ExecuteReader())
			{
				try
				{
                    while (DataReader.Read())
                    {
                        Teams DB = new Teams();
                        int Subs = 0;
                        try
                        {
                            Subs = Convert.ToInt32(DataReader["Subs"]);
                        }
                        catch { }

                        string P_Jersey = "";
                        string R_Jersey = "";
                        string P_Name = "";
                        string R_Name = "";

                        try
                        {
                            if (Convert.ToInt32(DataReader["PlayerID"]) > 0)
                            {
                                Teams P_Details = DB.GetPlayer(_FID, Convert.ToInt32(DataReader["SID"]), Convert.ToInt32(DataReader["PlayerID"]));
                                P_Jersey = "(" + P_Details.Jersey.ToString() + ") ";
                                P_Name = Convert.ToString(P_Details.FullName)[0] + " " + P_Details.LastName.ToString();

                                P_Name = P_Jersey + P_Name;
                            }
                            if (Convert.ToInt32(DataReader["ReplcamentID"]) > 0)
                            {
                                Teams R_Details = DB.GetPlayer(_FID, Convert.ToInt32(DataReader["SID"]), Convert.ToInt32(DataReader["ReplcamentID"]));
                                R_Jersey = "(" + R_Details.Jersey.ToString() + ") ";
                                R_Name = R_Details.FullName.ToString()[0] + " " + R_Details.LastName.ToString();

                                R_Name = R_Jersey + R_Name;
                            }
                            Comments.Add(new Comments()
                            {
                                FID = Convert.ToInt32(DataReader["FID"]),
                                ID = Convert.ToString(DataReader["ID"]),
                                Session = Convert.ToInt32(DataReader["Session"]),
                                EventID = Convert.ToInt32(DataReader["EventID"]),
                                PlayerID = Convert.ToInt32(DataReader["PlayerID"]),
                                ReplacementID = Convert.ToInt32(DataReader["ReplcamentID"]),
                                SID = Convert.ToInt32(DataReader["SID"]),
                                Time = Convert.ToString(DataReader["Time"]),
                                EventName = Convert.ToString(DataReader["EventName"]),
                                SquadName = Convert.ToString(DataReader["SquadName"]),
                                PlayerName = P_Name,
                                ReplacementName = R_Name,
                                Subs = Subs,
                                Comment = Convert.ToString(DataReader["Comment"])

                            });
                        }
                        catch (Exception)
                        {
                        }
                    }
                }
				catch (Exception)
				{
				}				
			}
			sqlite_conn.Close();




			return Comments;
		}
		public static List<Comments> GetCommentary(int _FID)
		{
			var Comments = new List<Comments>();


			
			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 Commentary_view WHERE FID = '" + _FID + "' Order by No DESC";

			using (DataReader = sqlite_cmd.ExecuteReader())
			{
				while (DataReader.Read())
				{
					Teams DB = new Teams();
					int Subs = 0;
					try
					{
						Subs = Convert.ToInt32(DataReader["Subs"]);
					}
					catch { }

					string P_Jersey = "";
					string R_Jersey = "";
					string P_Name = "";
					string R_Name = "";

					try
                    {
						if (Convert.ToInt32(DataReader["PlayerID"]) > 0)
						{							
							Teams P_Details = DB.GetPlayer(_FID, Convert.ToInt32(DataReader["SID"]), Convert.ToInt32(DataReader["PlayerID"]));
							P_Jersey = "(" + P_Details.Jersey.ToString() + ") ";
							P_Name = Convert.ToString(P_Details.FullName)[0] + " " + Convert.ToString(P_Details.LastName);

							P_Name = P_Jersey + P_Name;
						}
						if (Convert.ToInt32(DataReader["ReplcamentID"]) > 0)
						{
							Teams R_Details = DB.GetPlayer(_FID, Convert.ToInt32(DataReader["SID"]), Convert.ToInt32(DataReader["ReplcamentID"]));
							R_Jersey = "(" + R_Details.Jersey.ToString() + ") ";
							R_Name = R_Details.FullName.ToString()[0] + " " + R_Details.LastName.ToString();

							R_Name = R_Jersey + R_Name;
						}
						Comments.Add(new Comments()
						{
							FID = Convert.ToInt32(DataReader["FID"]),
							ID = Convert.ToString(DataReader["ID"]),
							Session = Convert.ToInt32(DataReader["Session"]),
							EventID = Convert.ToInt32(DataReader["EventID"]),
							PlayerID = Convert.ToInt32(DataReader["PlayerID"]),
							ReplacementID = Convert.ToInt32(DataReader["ReplcamentID"]),
							SID = Convert.ToInt32(DataReader["SID"]),
							Time = Convert.ToString(DataReader["Time"]),
							EventName = Convert.ToString(DataReader["EventName"]),
							SquadName = Convert.ToString(DataReader["SquadName"]),
							PlayerName = P_Name,
							ReplacementName = Convert.ToString(R_Name),
							Subs = Subs,
							Comment = Convert.ToString(DataReader["Comment"])

						});
					}
					catch
                    {
                    }
				}
			}
			sqlite_conn.Close();



			
			return Comments;
		}

		public  List <Comments> GetYellowCards_Player(int _FID,int Userid)
		{
			var Comments = new List<Comments>();



			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 Commentary WHERE FID = '" + _FID + "' and PlayerID = '" + Userid + "' AND EventID =11  Order by Session ,No DESC";

			using (DataReader = sqlite_cmd.ExecuteReader())
			{
				while (DataReader.Read())
				{
					Teams DB = new Teams();
					string P_Jersey = "";
					string R_Jersey = "";
					if (Convert.ToInt32(DataReader["PlayerID"]) > 0)
					{
						Teams P_Details = DB.GetPlayer(_FID, Convert.ToInt32(DataReader["SID"]), Convert.ToInt32(DataReader["PlayerID"]));
						P_Jersey = " (" + P_Details.Jersey.ToString() + ")";
					}
					if (Convert.ToInt32(DataReader["ReplcamentID"]) > 0)
					{
						Teams R_Details = DB.GetPlayer(_FID, Convert.ToInt32(DataReader["SID"]), Convert.ToInt32(DataReader["ReplcamentID"]));
						R_Jersey = " (" + R_Details.Jersey.ToString() + ")";
					}
					Comments.Add(new Comments()
					{
						FID = Convert.ToInt32(DataReader["FID"]),
						ID = Convert.ToString(DataReader["ID"]),
						Session = Convert.ToInt32(DataReader["Session"]),
						EventID = Convert.ToInt32(DataReader["EventID"]),
						PlayerID = Convert.ToInt32(DataReader["PlayerID"]),
						ReplacementID = Convert.ToInt32(DataReader["ReplcamentID"]),
						SID = Convert.ToInt32(DataReader["SID"]),
						Time = Convert.ToString(DataReader["Time"]),
						EventName = Convert.ToString(DataReader["EventName"]),
						SquadName = Convert.ToString(DataReader["SquadName"]),
						PlayerName = Convert.ToString(DataReader["PlayerName"]) + P_Jersey,
						ReplacementName = Convert.ToString(DataReader["ReplacementName"]) + R_Jersey,
						Comment = Convert.ToString(DataReader["Comment"])

					});

				}

			}
			sqlite_conn.Close();




			return Comments;
		}

		public int Count_YellowCards_Player(int _FID, int Userid)
		{
			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 = "SELECT * FROM Commentary WHERE FID = '" + _FID + "' and PlayerID = '" + Userid + "' AND EventID =11  Order by time ,No DESC";

			using (DataReader = sqlite_cmd.ExecuteReader())
			{
				while (DataReader.Read())
				{
					Count++;

				}

			}
			sqlite_conn.Close();




			return Count;
		}

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

			}
		}


		

	}
}