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

namespace Live_Scoring_V1
{
	class Teams
	{

		public int FID { get; set; }
		public int SID { get; set; }
		public int UserID { get; set; }
		public string FirstName { get; set; }
		public string LastName { get; set; }

		public string NickName { get; set; }
		public string FullName { get; set; }
		public int Pos { get; set; }
		public int Vice { get; set; }
		public int Cap { get; set; }
		public int Jersey { get; set; }

		public int CurrentPos { get; set; }
		public int Location { get; set; }
		public int Time { get; set; }
        public string Front { get; set; }
        public int Started { get; set; }
		public int Returned { get; set; }
		public List<Teams> GetTeam(int FID, int SID)
		{
			
			List<Teams> List = new List<Teams>();
			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 Teams WHERE FID ='" + FID + "' AND SID ='" + SID + "' ORDER BY CurrentPos ASC ";
			
				using (DataReader = sqlite_cmd.ExecuteReader())
				{
					while (DataReader.Read()) {
					Teams player = new Teams();
					player.FID = Convert.ToInt32(DataReader["FID"]);
					player.SID = Convert.ToInt32(DataReader["SID"]);
					player.UserID = Convert.ToInt32(DataReader["UserID"]);
					player.FirstName = Convert.ToString(DataReader["FirstName"]);
					player.LastName = Convert.ToString(DataReader["LastName"]);
					player.NickName = Convert.ToString(DataReader["NickName"]);
					player.FullName = Convert.ToString(DataReader["FullName"]);
					player.Pos = Convert.ToInt32(DataReader["Pos"]);
					player.Vice = Convert.ToInt32(DataReader["Vice"]);
					player.Cap = Convert.ToInt32(DataReader["Cap"]);
					player.Jersey = Convert.ToInt32(DataReader["Jersey"]);
					player.CurrentPos = Convert.ToInt32(DataReader["CurrentPos"]);
					player.Location = Convert.ToInt32(DataReader["Location"]);
					player.Front = Convert.ToString(DataReader["Front"]);
					player.Time = Convert.ToInt32(DataReader["Time"]);
					List.Add(player);
					}					
				}
			
			sqlite_conn.Close();

			return List;
			
		}

        //public List<Teams> GetTeamLineUp(int FID, int SID)
        //{

        //    List<Teams> List = new List<Teams>();
        //    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 Teams WHERE FID ='" + FID + "' AND SID ='" + SID + "' ORDER BY CurrentPos ASC ";

        //    using (DataReader = sqlite_cmd.ExecuteReader())
        //    {
        //        while (DataReader.Read())
        //        {
        //            Teams player = new Teams();
        //            player.FullName = Convert.ToString(DataReader["FullName"]);
        //            player.Jersey = Convert.ToInt32(DataReader["Jersey"]);
        //            player.Front = Convert.ToString(DataReader["Front"]);
        //            List.Add(player);
        //        }

        //    }

        //    sqlite_conn.Close();

        //    return List;

        //}

        public static List<int> GetLineUP_IDs(int FID, int SID,int Code)
		{

			List<int> List = new List<int>();
			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 Teams WHERE FID ='" + FID + "' AND SID ='" + SID + "' AND Pos <= '" + Code + "' ORDER BY Pos ASC ";
			sqlite_cmd.CommandText = "SELECT * FROM Teams WHERE FID ='" + FID + "' AND SID ='" + SID + "'  ORDER BY CurrentPos ASC ";
			using (DataReader = sqlite_cmd.ExecuteReader())
			{
				while (DataReader.Read())
				{
					
					List.Add(Convert.ToInt32(DataReader["UserID"]));
				}

			}

			sqlite_conn.Close();

			return List;

		}

		public static List<int> GetSubs(int FID, int SID, int Code)
		{

			List<int> List = new List<int>();
			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 Teams WHERE FID ='" + FID + "' AND SID ='" + SID + "' AND Pos <= '" + Code + "' ORDER BY Jersey ASC ";

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

					List.Add(Convert.ToInt32(DataReader["UserID"]));
				}

			}

			sqlite_conn.Close();

			return List;

		}

		public List<Teams> GetYellowCards(int FID, int SID)
		{

			List<Teams> List = new List<Teams>();
			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 Teams WHERE FID ='" + FID + "' AND SID ='" + SID + "' AND Location ='3'   ORDER BY Time ASC, Jersey ASC ";

			using (DataReader = sqlite_cmd.ExecuteReader())
			{
				while (DataReader.Read())
				{
					var Returned = 0;
					if (DataReader["Returned"].ToString() !="") {
						Returned = Convert.ToInt32(DataReader["Returned"].ToString());
					}
					Teams player = new Teams();
					player.FID = Convert.ToInt32(DataReader["FID"]);
					player.SID = Convert.ToInt32(DataReader["SID"]);
					player.UserID = Convert.ToInt32(DataReader["UserID"]);
					player.FirstName = Convert.ToString(DataReader["FirstName"]);
					player.LastName = Convert.ToString(DataReader["LastName"]);
					player.NickName = Convert.ToString(DataReader["NickName"]);
					player.FullName = Convert.ToString(DataReader["FullName"]);
					player.Pos = Convert.ToInt32(DataReader["Pos"]);
					player.Vice = Convert.ToInt32(DataReader["Vice"]);
					player.Cap = Convert.ToInt32(DataReader["Cap"]);
					player.Jersey = Convert.ToInt32(DataReader["Jersey"]);
					player.CurrentPos = Convert.ToInt32(DataReader["CurrentPos"]);
					player.Location = Convert.ToInt32(DataReader["Location"]) as int? ?? default(int);
                    player.Front = Convert.ToString(DataReader["Front"]);
                    player.Time = Convert.ToInt32(DataReader["Time"]);
					player.Started = Convert.ToInt32(DataReader["Started"]);
					player.Returned = Returned;
					List.Add(player);
				}

			}

			sqlite_conn.Close();

			return List;

		}

        public List<Teams> GetRedCards(int FID, int SID)
        {

            List<Teams> List = new List<Teams>();
            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 Teams WHERE FID ='" + FID + "' AND SID ='" + SID + "' AND Location ='4'   ORDER BY Time ASC, Jersey ASC ";

            using (DataReader = sqlite_cmd.ExecuteReader())
            {
                while (DataReader.Read())
                {
					var Returned = 0;
					if (DataReader["Returned"].ToString() != "")
					{
						Returned = Convert.ToInt32(DataReader["Returned"].ToString());
					}
					Teams player = new Teams();
                    player.FID = Convert.ToInt32(DataReader["FID"]);
                    player.SID = Convert.ToInt32(DataReader["SID"]);
                    player.UserID = Convert.ToInt32(DataReader["UserID"]);
                    player.FirstName = Convert.ToString(DataReader["FirstName"]);
                    player.LastName = Convert.ToString(DataReader["LastName"]);
                    player.NickName = Convert.ToString(DataReader["NickName"]);
                    player.FullName = Convert.ToString(DataReader["FullName"]);
                    player.Pos = Convert.ToInt32(DataReader["Pos"]);
                    player.Vice = Convert.ToInt32(DataReader["Vice"]);
                    player.Cap = Convert.ToInt32(DataReader["Cap"]);
                    player.Jersey = Convert.ToInt32(DataReader["Jersey"]);
                    player.CurrentPos = Convert.ToInt32(DataReader["CurrentPos"]);
                    player.Location = Convert.ToInt32(DataReader["Location"]) as int? ?? default(int);
                    player.Front = Convert.ToString(DataReader["Front"]);
                    player.Time = Convert.ToInt32(DataReader["Time"]);
                    //player.Started = Convert.ToInt32(DataReader["Started"]);
                    player.Returned = Returned;
                    List.Add(player);
                }

            }

            sqlite_conn.Close();

            return List;

        }

        public Teams GetPlayer(int FID, int SID,int UserID)
		{

			List<Teams> List = new List<Teams>();
			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();

			Teams player = new Teams();

			sqlite_cmd = sqlite_conn.CreateCommand();
			sqlite_cmd.CommandText = "SELECT * FROM Teams WHERE FID ='" + FID + "' AND SID ='" + SID + "' AND UserID ='" + UserID + "'   ";

			using (DataReader = sqlite_cmd.ExecuteReader())
			{
				while (DataReader.Read())
				{
					
					player.FID = Convert.ToInt32(DataReader["FID"]);
					player.SID = Convert.ToInt32(DataReader["SID"]);
					player.UserID = Convert.ToInt32(DataReader["UserID"]);
					player.FirstName = Convert.ToString(DataReader["FirstName"]);
					player.LastName = Convert.ToString(DataReader["LastName"]);
					player.NickName = Convert.ToString(DataReader["NickName"]);
					player.FullName = Convert.ToString(DataReader["FullName"]);
					player.Pos = Convert.ToInt32(DataReader["Pos"]);
					player.Vice = Convert.ToInt32(DataReader["Vice"]);
					player.Cap = Convert.ToInt32(DataReader["Cap"]);
					player.Jersey = Convert.ToInt32(DataReader["Jersey"]);
					player.CurrentPos = Convert.ToInt32(DataReader["CurrentPos"]);
                    player.Front = Convert.ToString(DataReader["Front"]);
                    player.Location = Convert.ToInt32(DataReader["Location"]) as int? ?? default(int);

				}

			}

			sqlite_conn.Close();

			return player;

		}


		public void DeleteAllTeam()
		{

			
			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 = " DELETE FROM Teams";
			sqlite_cmd.ExecuteReader();
			sqlite_conn.Close();
		}

	}
	class TeamLinupRow
	{

	}
}