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 SqlDatabase
	{
		public SQLiteDataReader DataReader = null;
		public SQLiteConnection sqlite_conn;
		public SQLiteCommand sqlite_cmd;

        string dbPath = (Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData)) + "\\Sideline Manager\\";        

		private void OpenCon()
		{
			try
			{
				sqlite_conn = new SQLiteConnection("Data Source="+ dbPath +"Footprint.db;Version=3;");
				sqlite_conn.Open();
			} catch (Exception E) {
				CreateDataBase();
			}
		}



		private void CloseCon()
		{
			sqlite_conn.Close();
		}


		public SQLiteDataReader GetData(string sql)
		{
			SQLiteDataReader DATA = null;
			try
			{
				OpenCon();
				sqlite_cmd = sqlite_conn.CreateCommand();
				sqlite_cmd.CommandText = sql;

				using (DATA = sqlite_cmd.ExecuteReader())
				{
				}
					CloseCon();
			}
			catch (Exception E)
			{
				
			}
			return DATA;
		}

		public bool Execute(string sql)
		{
			try
			{
				OpenCon();
				var command = new SQLiteCommand(sql, sqlite_conn);
				int done = command.ExecuteNonQuery();
				
				CloseCon();
				if (done == 1)
				{
					return true;
				}
				else
				{
					return false;
				}
				
			}
			catch(Exception E)
			{
				//MessageBox.Show(E.ToString());
				return false;
			}
		}



		public void CreateDataBase()
		{
			SQLiteConnection sqlite_conn;
			//SQLiteCommand sqlite_cmd;
			//SQLiteDataReader sqlite_datareader;
			sqlite_conn = new SQLiteConnection("Data Source="+ dbPath +"Footprint.db;Version=3;New=True;Compress=True;");

			sqlite_conn.Open();

			string sql = "CREATE TABLE Users (UserID INT(11) UNIQUE,'FirstName' VARCHAR(200)  ,'LastName' VARCHAR(200)  ,'FullName' , 'UnionID' INT, 'UserName' VARCHAR(200), 'Password' VARCHAR(200),'LoggedIn' INT)";
			SQLiteCommand command = new SQLiteCommand(sql, sqlite_conn);

			command.ExecuteNonQuery();

			sql = "CREATE TABLE Events (CEID  INTEGER(11)       UNIQUE," +
			   "CommName VARCHAR(200)," +
			   "position  INTEGER(11)," +
			   "type  INTEGER(11)," +
			   "Game_Status  INTEGER(11)," +
			   "Subs  INTEGER(11)," +
			   "SubsRequired  INTEGER(11)," +
			   "Card  INTEGER(11)," +
			   "Color VARCHAR(20)," +
				"Session INTEGER(11)," +
			   
			   "Groups  INTEGER(11));";
			command = new SQLiteCommand(sql, sqlite_conn);
			command.ExecuteNonQuery();

			sql = "CREATE TABLE Fixture ( FID           INTEGER (11)  UNIQUE," +
				"FixtureDate VARCHAR(200)," +
				"StartTime VARCHAR(200)," +
				"Location VARCHAR(200)," +
				"HomeTeam INTEGER(11)," +
				"AwayTeam INTEGER(11)," +
				"FixtureNumber VARCHAR(11)," +
				"StageName VARCHAR(200)," +
				"RoundNumber INTEGER(11)," +
                "GameReferee VARCHAR(200)," +
                "AssistantReferee VARCHAR(200)," +
                "TelevisionReferee VARCHAR(200)," +
                "HomeName VARCHAR(200)," +
                "AwayName VARCHAR(200)," +
                "HomeAbbr VARCHAR(20)," +
			   " AwayAbbr VARCHAR(20)," +
				"TID INTEGER(11)," +
				"Allow22MeterTry INTEGER(11)," +
				"Tries INTEGER(11)," +
				"Conversion INTEGER(11)," +
				"Penalties INTEGER(11)," +
				"Dropgoal INTEGER(11)," +
				"PenaltyTry INTEGER(11)," +
				"Portry INTEGER(11)," +
				"Code INTEGER(11)," +
				"Game_Paused INTEGER," +
                "YellowCardCounter INTEGER," +
                "YellowCardTimer INTEGER," +
                "BloodTimer INTEGER," +
                "HIATimer INTEGER," +
                "HalfTime VARCHAR(200)," +
				
				"Time_Started VARCHAR(200)," +
				
				"TournName VARCHAR(200)," +
				"Current INTEGER(11), Game_status     INTEGER,Game_Duration VARCHAR,Playing_Time    VARCHAR,Running_time VARCHAR,Total_Stoppage  VARCHAR); ";
			command = new SQLiteCommand(sql, sqlite_conn);
			command.ExecuteNonQuery();

			sql = "CREATE TABLE Teams ( SID    INTEGER," +
						"FID       INTEGER," +
						"UserID    INTEGER," +
						"FirstName VARCHAR(200)," +
						"LastName VARCHAR(200)," +
						"NickName VARCHAR(200)," +
						"FullName VARCHAR(200)," +
						"Pos INTEGER," +
						"Vice      INTEGER," +
						"Cap INTEGER," +
						"CurrentPos INTEGER(11)," +
						"Location INTEGER," +
						"Started INTEGER," +
						"Returned INTEGER," +
						
						"Time INTEGER," +
                        "Front VARCHAR(200)," +
						"Jersey    INTEGER); ";
			command = new SQLiteCommand(sql, sqlite_conn);
			command.ExecuteNonQuery();


			sql = "CREATE TABLE Scores(" +
					"FID INTEGER(11) UNIQUE," +
					"H_FS INTEGER," +
					"H_HS          INTEGER(11)," +
					"A_FS INTEGER(11)," +
					"A_HS INTEGER(11)," +
					"H_Try INTEGER(11)," +
					"A_Try INTEGER(11)," +
					"H_Convertion INTEGER(11)," +
					"A_Convertion INTEGER(11)," +
					"H_Penalty INTEGER(11)," +
					"A_Penalty INTEGER(11)," +
					"H_Dropgoal INTEGER(11)," +
					"A_Dropgoal INTEGER(11)," +
					"H_PenaltyTry INTEGER(11)," +
					"A_PenaltyTry INTEGER(11)," +
					"H_Subs INTEGER(11)," +
					"A_Subs INTEGER(11)," +
					"H_YellowCards INTEGER(11)," +
					"A_YellowCards INTEGER(11)," +
					"H_RedCards INTEGER(11)," +
					"A_RedCards INTEGER(11), H_PorTry  INTEGER(11) ,A_PorTry  INTEGER(11));  ";
			command = new SQLiteCommand(sql, sqlite_conn);
			command.ExecuteNonQuery();

			sql = "CREATE TABLE Commentary(" +
					 " ID VARCHAR(200) ," +
					"Time            VARCHAR(20)," +
					"FID            INTEGER," +
					"Session         INTEGER,No INTEGER," +
					"SID             INTEGER(11)," +
					"SquadName       VARCHAR(200)," +
					"EventID         INTEGER," +
					"EventName       VARCHAR(200)," +
					"PlayerID        INTEGER(11)," +
					"PlayerName      VARCHAR(200)," +
					"ReplcamentID    INTEGER," +
					"ReplacementName VARCHAR(200)," +
					"Flag  INTEGER," +
					"Comment         TEXT);";


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

			sql = "CREATE TABLE Tournaments(" +
					 " ID VARCHAR(200) ," +
					"Name            VARCHAR(45)," +
					"Status            INTEGER," +
					"StartDate         VARCHAR(45)," +
					"EndDate         VARCHAR(45)," +
					"Image             TEXT);";


			command = new SQLiteCommand(sql, sqlite_conn);
			command.ExecuteNonQuery();
			/*
			sql = "CREATE TABLE YellowCards(" +
					"Time            VARCHAR(20)," +
					"FID            INTEGER," +
					"Session         INTEGER,No INTEGER," +
					"SID             INTEGER(11)," +					
					"PlayerID        INTEGER(11)," +
					"Pos        INTEGER(11)," +
					"Jersey        INTEGER(11)," +
					"Started  INTEGER," +
					"Ended  INTEGER," +
					"Counter VARCHAR(200));";


			command = new SQLiteCommand(sql, sqlite_conn);
			command.ExecuteNonQuery();
			*/
			sql = "CREATE VIEW Commentary_view AS SELECT C.*, E.* FROM Commentary AS C LEFT JOIN Events AS E ON E.CEID = C.EventID GROUP BY ID; ";

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





			sqlite_conn.Close();

			


		}

	}
}