using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SQLite;
using System.Diagnostics;
using System.Data;
using System.Reflection;
using System.Collections.ObjectModel;
using System.IO;
using System.Windows;

namespace Sched.CustomClasses
{
    public static class DBI
    {
        public static SQLiteDataAdapter dAdapter;
        public static SQLiteCommandBuilder cmdBuilder;

        public static int ChangeCommand(string commString)    //Insert/Delete/Update row, Create Table
        {
            int rowsChanged = 0;
            try
            {
                using (SQLiteConnection connection = new SQLiteConnection(App.DBFile + ";foreign keys=true;"))
                {
                    connection.Open();
                    SQLiteCommand command = new SQLiteCommand(commString, connection);
                    rowsChanged = command.ExecuteNonQuery();
                    connection.Close();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                rowsChanged = -1;   //error
            }
            return rowsChanged;
        }
        public static int InsertRow(object obj, bool isIdValid = false)
        {
            string tableName = obj.GetType().Name;
            string propNames = string.Empty;
            string propValues = string.Empty;

            int nProperties = obj.GetType().GetProperties().Length;
            PropertyInfo[] pInfoArray = obj.GetType().GetProperties();
            for (int i = 0; i < nProperties; i++)
            {
                PropertyInfo PropInfo = pInfoArray[i];
                string PropName = PropInfo.Name;
                var PropValue = obj.GetType().GetProperty(PropName).GetValue(obj);
                if ((PropName == "Id")&&(!(isIdValid)))
                {
                    continue;   //object is being Inserted without caring about userdefined Id
                }

                if (PropValue.ToString() != string.Empty)
                {
                    if (propNames != string.Empty)
                    {
                        propNames += ",";
                    }

                    if (propValues != string.Empty)
                    {
                        propValues += ",";
                    }

                    propNames += PropName;
                    propValues += $"'{PropValue}'";
                }               
            }

            string InsertCmd = $"INSERT INTO {tableName} ({propNames}) VALUES ({propValues});";
            //Debug.WriteLine("DBI InsertRow:"  + InsertCmd);
            return ChangeCommand(InsertCmd);
        }
        public static int InsertRowReturnId(object obj)
        {
            InsertRow(obj, false);
            string tableName = obj.GetType().Name;
            string getIdCmd = $"SELECT max(Id) as Id FROM {tableName};";

            DataTable dt = SelectRows(getIdCmd, tableName);
            int Id = 0;
            if (dt.Rows.Count > 0)
            {
                DataRow dr = dt.Rows[0];
                string s = dr["Id"].ToString();
                int.TryParse(s, out Id);
            }
            return Id;
        }
        public static int UpdateRow(object obj)
        {
            string tableName = obj.GetType().Name;
            string propNames = string.Empty;
            string propValues = string.Empty;

            int nProperties = obj.GetType().GetProperties().Length;
            PropertyInfo[] pInfoArray = obj.GetType().GetProperties();
            for (int i = 0; i < nProperties; i++)
            {
                PropertyInfo PropInfo = pInfoArray[i];
                string PropName = PropInfo.Name;
                var PropValue = obj.GetType().GetProperty(PropName).GetValue(obj);

                if (PropValue.ToString() != string.Empty)
                {
                    if (propNames != string.Empty)
                    {
                        propNames += ",";
                    }

                    if (propValues != string.Empty)
                    {
                        propValues += ",";
                    }

                    propNames += PropName;
                    propValues += $"'{PropValue}'";
                }
            }

            string updateCmd = $"UPDATE {tableName} SET ({propNames}) = ({propValues}) WHERE Id={obj.GetType().GetProperty("Id").GetValue(obj)};";
            //Debug.WriteLine("DBI UpdateRow:" + updateCmd);
            return ChangeCommand(updateCmd);
        }
        public static int DeleteRow(object obj)
        {
            string deleteCmd = $"DELETE FROM {obj.GetType().Name} WHERE Id={obj.GetType().GetProperty("Id").GetValue(obj)};";
            //Debug.WriteLine("DBI DeleteRow:" + deleteCmd);
            return ChangeCommand(deleteCmd);
        }

        //Selects
        public static DataTable SelectRows(string selectString, string TableName)
        {
            DataTable dt = new DataTable(TableName);

            try
            {
                using (SQLiteConnection connection = new SQLiteConnection(App.DBFile))
                {
                    connection.Open();
                    SQLiteCommand command = new SQLiteCommand(selectString, connection);

                    dAdapter = new SQLiteDataAdapter(command);
                    cmdBuilder = new SQLiteCommandBuilder(dAdapter);
                    dAdapter.Fill(dt);
                    connection.Close();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            return dt;
        }
        public static string SelectReturnSingleValue(string TableName, string TableItem, int Id)
        {
            string selectString = $"SELECT {TableItem} FROM {TableName} WHERE id={Id};";

            DataTable dt = SelectRows(selectString, TableName);
            string s = string.Empty;
            if (dt.Rows.Count > 0)
            {
                DataRow dr = dt.Rows[0];
                s = dr[TableItem].ToString();
            }
            return s;
        }
        public static string SelectFullName(int Id)
        {
            string selectString = $"SELECT Lastname,FirstName,Ranks FROM Teacher WHERE id={Id};";

            DataTable dt = SelectRows(selectString, "Teacher");
            string s = string.Empty;
            if (dt.Rows.Count > 0)
            {
                DataRow dr = dt.Rows[0];
                s = $"{dr["LastName"]} {dr["FirstName"]}";
                string t = dr["Ranks"].ToString();
                if (t.Length > 0)
                {
                    s += ", " + t;
                }
            }
            return s;
        }
        //Saving all lesson changes in MainTimetable to Database
        public static int InsertLesson(Lesson lsn)
        {
            int lessonId = InsertRowReturnId(lsn);
            //Debug.WriteLine("Id " + lessonId.ToString());
            List<string> entityList = new List<string>() { "Teacher", "Room", "StudentGroup" };
            foreach(string entityName in entityList)
            {
                foreach (int entityId in lsn.GetEntities(entityName))
                {
                    string InsertCmd = $"INSERT INTO Lesson{entityName} (LessonId,{entityName}Id) VALUES ({lessonId},{entityId});";
                    //Debug.WriteLine(InsertCmd);
                    ChangeCommand(InsertCmd); 
                }
            }
            return lessonId;
        }
        public static int DeleteLesson(Lesson lsn)
        {
            return DeleteRow(lsn);
        }
        public static int UpdateLesson(Lesson lsn)
        {
            UpdateRow(lsn);
            int lessonId = lsn.Id;

            List<string> entityList = new List<string>() { "Teacher", "Room", "StudentGroup" };
            foreach (string entityName in entityList)
            {
                string deleteCmd = $"DELETE FROM Lesson{entityName} WHERE lessonId={lessonId};";
                //Debug.WriteLine("DELETEcmd: " + deleteCmd);
                ChangeCommand(deleteCmd);

                foreach (int entityId in lsn.GetEntities(entityName))
                {
                    string InsertCmd = $"INSERT INTO Lesson{entityName} (LessonId,{entityName}Id) VALUES ({lessonId},{entityId});";
                    //Debug.WriteLine("INSERTcmd: " + InsertCmd);
                    ChangeCommand(InsertCmd);
                }
            }
            return 0;
        }

        //Loading to MainTimetable and to CollisionTable
        public static ObservableCollection<object> LoadCategoryLessons(int SemesterId,string categoryName, int categoryId) //Assignment panel filtering by Semester and Category
        {
            string selectString =
                $"SELECT * FROM Lesson " +
                $"WHERE Id in (SELECT LessonId FROM Lesson{categoryName} WHERE {categoryName}Id = {categoryId}) " +
                $"AND SemesterId={SemesterId};";

            if (categoryName == "Course")
            {
                selectString = $"SELECT * FROM Lesson WHERE CourseId = {categoryId} " +
                $"AND SemesterId={SemesterId};";
            }

            //Get Lessons
            ObservableCollection<object> lessonList = GetItems("Lesson", selectString);
            //Load Lesson Entities
            LoadLessonEntities(lessonList);

            return lessonList;
        }
        public static void LoadLessonEntities(ObservableCollection<object> lessonList)
        {
            List<string> mnList = new List<string> { "Teacher", "Room", "StudentGroup" };
            foreach (var l in lessonList)
            {
                var lesson = l as Lesson;
                foreach (string mnTableName in mnList)
                {
                    DataTable dt = SelectRows($"SELECT {mnTableName}Id FROM Lesson{mnTableName} WHERE LessonId = {lesson.Id};", $"Lesson{mnTableName}");
                    List<int> ids = new List<int>();
                    if (dt.Rows.Count > 0)
                    {
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            DataRow dr = dt.Rows[i];
                            string s = dr[$"{mnTableName}Id"].ToString();
                            int id = 0;
                            if (Int32.TryParse(s, out id))
                            {
                                ids.Add(id);
                            }
                        }
                    }
                    lesson.SetEntities(mnTableName, ids);
                }
            }
        }
        public static string LoadLessonCollisionsBaseStringCreate(Lesson lssn)
        {
            List<string> mnList = new List<string>() { "Teacher", "Room", "StudentGroup" };
            string selectString = string.Empty;

            if ((lssn.GetEntities("Teacher").Count > 0) || (lssn.GetEntities("Room").Count > 0) || (lssn.GetEntities("StudentGroup").Count > 0))
            {
                selectString = $"SELECT * FROM Lesson WHERE SemesterId={lssn.SemesterId} AND NOT Id={lssn.Id} AND Id IN(SELECT DISTINCT(LessonId) FROM LessonMN WHERE ";

                string subquery = "";
                foreach (string entityName in mnList)
                {
                    if (lssn.GetEntities(entityName).Count > 0)
                    {
                        if (subquery.Length > 0)
                        {
                            subquery += " OR ";
                        }
                        subquery += $"{entityName}Id IN (";

                        string idsString = "";
                        foreach (var id in lssn.GetEntities(entityName))
                        {
                            if (idsString.Length > 0)
                            {
                                idsString += ",";
                            }
                            idsString += id.ToString();
                        }
                        subquery += idsString + ")";
                    }
                }
                selectString += subquery + ")";

            }
            return selectString;
        }
        public static string LoadLessonCollisionsBaseStringCreate(Lesson lssn, bool excludeLesson, bool includeAllFields, string fields)
        {
            List<string> mnList = new List<string>() { "Teacher", "Room", "StudentGroup" };
            string selectString = string.Empty;

            if ((lssn.GetEntities("Teacher").Count > 0) || (lssn.GetEntities("Room").Count > 0) || (lssn.GetEntities("StudentGroup").Count > 0))
            {
                if (includeAllFields)
                {
                    selectString += $"SELECT * ";
                }
                else
                {
                    selectString += $"SELECT {fields} ";
                }

                selectString += $"FROM Lesson WHERE SemesterId={lssn.SemesterId} ";

                if (excludeLesson)
                {
                    selectString += $"AND NOT Id={lssn.Id} ";
                }

                selectString += $"AND Id IN(SELECT DISTINCT(LessonId) FROM LessonMN WHERE ";


                string subquery = "";
                foreach (string entityName in mnList)
                {
                    if (lssn.GetEntities(entityName).Count > 0)
                    {
                        if (subquery.Length > 0)
                        {
                            subquery += " OR ";
                        }
                        subquery += $"{entityName}Id IN (";

                        string idsString = "";
                        foreach (var id in lssn.GetEntities(entityName))
                        {
                            if (idsString.Length > 0)
                            {
                                idsString += ",";
                            }
                            idsString += id.ToString();
                        }
                        subquery += idsString + ")";
                    }
                }
                selectString += subquery + ")";

            }
            return selectString;
        }
        public static ObservableCollection<object> LoadCollisionPanelLessons(Lesson lssn)
        {
            ObservableCollection<object> lessonList = new ObservableCollection<object>();
            string selectString = LoadLessonCollisionsBaseStringCreate(lssn);

            if (selectString.Length > 0)
            {
                selectString += ";";
                //Debug.WriteLine("LoadCollisionPanelLessons:" + selectString);
                lessonList = GetItems("Lesson", selectString);
                LoadLessonEntities(lessonList);
            }

            return lessonList;
        }
        public static ObservableCollection<object> LoadPossibleCollisionLessonsOnlyForDays(Lesson lssn, string datesString = "")
        {
            ObservableCollection<object> lessonList = new ObservableCollection<object>();
            string selectString = LoadLessonCollisionsBaseStringCreate(lssn);

            if (selectString.Length > 0)
            {
                selectString += $" AND Max(0, MIN(PeriodStart + Hours,{lssn.PeriodStart + lssn.Hours}) - max(PeriodStart,{lssn.PeriodStart})) > 0";   //Get only overlapping lessons
                if (datesString.Length > 0)
                {
                    selectString += " AND StartDateDT IN(" + datesString + ")";
                }
                selectString += ";";
                //Debug.WriteLine("LoadPossibleCollison:" + selectString);

                lessonList = GetItems("Lesson", selectString);
                LoadLessonEntities(lessonList);
                //Debug.WriteLine("LoadPossibleCollison:" + lessonList.Count.ToString());
            }
          
            return lessonList;
        }

        //Loading data in Database Window
        public static DataTable GetTableByName(string TableName, string selectionString)
        {
            DataTable dt = new DataTable(TableName);
            using (SQLiteConnection connection = new SQLiteConnection(App.DBFile + ";foreign keys=true;"))
            {
                connection.Open();
                //Get columns in datatable
                string Query = $"SELECT * FROM pragma_table_info('{TableName}');";
                SQLiteCommand command = new SQLiteCommand(Query, connection);
                SQLiteDataReader reader = command.ExecuteReader();

                List<string> primaryKeyColNames = new List<string>();
                while (reader.Read())
                {
                    DataColumn dataCol = new DataColumn();
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        /*ROW
                        cid = column index
                        name = column name
                        type = DATA TYPE
                        notNull = boolean whether it can be null
                        dftl_value = predefined default value
                        pk = 0 - if not primary key, 0< if primary key (and what column it is in the primary key)
                        //Debug.WriteLine($"{reader.GetName(i)} {reader.GetValue(i)}");
                         */

                        if (reader.GetName(i) == "name")
                        {
                            dataCol.ColumnName = reader.GetString(i);
                            dataCol.Caption = dataCol.ColumnName;
                            //Debug.WriteLine(dataCol.ColumnName);
                            continue;
                        }

                        if (reader.GetName(i) == "type")
                        {
                            string colTypeStr = reader.GetString(i);
                            if (colTypeStr == "INTEGER")
                            {
                                dataCol.DataType = typeof(Int32);
                            }
                            else if (colTypeStr == "TEXT")
                            {
                                if (dataCol.ColumnName.Contains("DT"))
                                {
                                    dataCol.DataType = typeof(DateTime);
                                }
                                else
                                {
                                    dataCol.DataType = typeof(string);
                                }
                            }

                            continue;
                        }

                        if (reader.GetName(i) == "notnull")
                        {
                            if (reader.GetBoolean(i) == false)
                            {
                                dataCol.AllowDBNull = true;
                            }
                            else
                            {
                                dataCol.AllowDBNull = false;
                            }
                            continue;
                        }

                        if ((reader.GetName(i) == "pk") && (reader.GetInt32(i) > 0))
                        {
                            primaryKeyColNames.Add(dataCol.ColumnName);
                        }
                    }
                    dt.Columns.Add(dataCol);
                }

                //Set the primary columns
                DataColumn[] primaryKeyCols = new DataColumn[primaryKeyColNames.Count];
                for (int i = 0; i < primaryKeyCols.Length; i++)
                {
                    string colName = primaryKeyColNames[i];
                    primaryKeyCols[i] = dt.Columns[colName];
                }
                dt.PrimaryKey = primaryKeyCols;

                //Fill the dataTable with data
                Query = selectionString;
                command = new SQLiteCommand(Query, connection);

                dAdapter = new SQLiteDataAdapter(command);
                cmdBuilder = new SQLiteCommandBuilder(dAdapter);
                dAdapter.Fill(dt);
                connection.Close();
            }

            return dt;
        }
        public static DataTable GetTableByName(string TableName)
        {
            string selectionStr = $"SELECT * FROM {TableName};";
            return GetTableByName(TableName, selectionStr);
        }
        public static void UpdateTable(DataTable dt)
        {
            try
            {
                using (SQLiteConnection connection = new SQLiteConnection(App.DBFile + ";foreign keys=true;"))
                {
                    connection.Open();
                    SQLiteCommand command = new SQLiteCommand($"SELECT * FROM {dt.TableName};", connection);
                    dAdapter = new SQLiteDataAdapter(command);
                    cmdBuilder = new SQLiteCommandBuilder(dAdapter);
                    dAdapter.Update(dt);
                    //rowsChanged = command.ExecuteNonQuery();
                    connection.Close();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            
        }
        public static void InsertCSVValues(string fileName, object obj, string separator)
        {
            string tableName = obj.GetType().Name;
            string insertCmd = string.Empty;

            using (StreamReader sr = new StreamReader(fileName))
            {
                string fieldList = string.Join(",", ((string)sr.ReadLine()).Split(separator));
                insertCmd = $"INSERT INTO {tableName} ({fieldList}) VALUES ";

                string line;
                string insertVals = "";
                int i = 0;
                while ((line = sr.ReadLine()) != null)
                {
                    i++;
                    if (line.Length == 0)
                    {
                        break;
                    }

                    if (insertVals.Length > 0)
                    {
                        insertVals += ",";
                    }
                    insertVals += "('" + string.Join("','", line.Split(separator)) + "')";

                    if(i > 19)  //tries to insert 20 rows at a time
                    {
                        i = 0;
                        if (DBI.ChangeCommand(insertCmd + insertVals + ";") == -1)  //error occured
                        {
                            break;
                        }
                        insertVals = "";
                    }
                }

                if (i != 0)
                {
                    DBI.ChangeCommand(insertCmd + insertVals + ";");
                    //Debug.WriteLine(insertVals);
                }
            }
        }

        //General GetItems() (for filling comboboxes, etc.)
        public static ObservableCollection<object> GetItems(string className)
        {
            string selectionStr = $"SELECT * FROM {className}";
            return GetItems(className, selectionStr);
        }
        public static ObservableCollection<object> GetItems(string className, string selectString)
        {
            ObservableCollection<object> ObjectItems = new ObservableCollection<object>();

            Type t = typeof(Curriculum);    //All the classes are defined in the same namespace as this class
            Assembly assem = t.Assembly;
   
            DataTable dt = DBI.GetTableByName(className, selectString);
            dt.TableName = className;
            foreach (DataRow dr in dt.Rows)
            {
                object c = assem.CreateInstance(t.Namespace + "." + className);
                int nProperties = c.GetType().GetProperties().Length;
                PropertyInfo[] pInfoArray = c.GetType().GetProperties();
                for (int i = 0; i < nProperties; i++)
                {
                    PropertyInfo PropInfo = pInfoArray[i];
                    string PropName = PropInfo.Name;

                    if ((dr[$"{PropName}"]).GetType() != typeof(System.DBNull))
                    {
                        //Debug.WriteLine("DBI LoadComboItems");
                        //Debug.WriteLine(dr.ToString()); 
                        //Debug.WriteLine(PropName);
                        //Debug.WriteLine(dr[$"{PropName}"]);                       
                        PropInfo.SetValue(c, dr[$"{PropName}"]);
                    }
                }

                //string s = c.ToString();
                //Debug.WriteLine($"Hello: " + s);
                ObjectItems.Add(c);
            }

            return ObjectItems;
        }
        public static object GetItemById(string className, int id)
        {
            string selectionStr = $"SELECT * FROM {className} WHERE Id={id}";
            return GetItems(className, selectionStr)[0];
        }
        public static ObservableCollection<object> GetItemsAndOrderByParameter(string className, string orderByParameter)
        {
            string selectionStr = $"SELECT * FROM {className} ORDER BY {orderByParameter}";
            List<object> items = new List<object>(GetItems(className, selectionStr));
            if (items.Count > 0)
            {
                PropertyInfo pI = items[0].GetType().GetProperty(orderByParameter);
                items.Sort((x, y) => string.Compare(pI.GetValue(x).ToString(), pI.GetValue(y).ToString()));    //To get unicode strings sorted the in correct order
            }
            return new ObservableCollection<object>(items);
            // return GetItems(className, selectionStr);
        }
        public static ObservableCollection<object> GetItemsBySemesterIdAndOrderByParameter(string className, int semesterId, string orderByParameter)
        {
            string selectionStr = $"SELECT * FROM {className} WHERE SemesterId={semesterId} ORDER BY {orderByParameter}";
            List<object> items = new List<object>(GetItems(className, selectionStr));
            if (items.Count > 0)
            {
                PropertyInfo pI = items[0].GetType().GetProperty(orderByParameter);
                items.Sort((x, y) => string.Compare(pI.GetValue(x).ToString(), pI.GetValue(y).ToString()));    //To get unicode strings sorted the in correct order
            }
            return new ObservableCollection<object>(items);
            // return GetItems(className, selectionStr);
        }

        //Specific GetItems() (for filling comboboxes, etc.)
        public static ObservableCollection<object> GetCoursesByStudentGroupId(int studentGroupId)
        {
            string selectStr = $"SELECT * FROM Course WHERE Id IN (SELECT CourseId FROM CurriculumCourse WHERE(CurriculumId, YearOfStudy, SemesterTypeId) IN " +
                                $"(SELECT CurriculumId, YearOfStudy, SemesterTypeId FROM StudentGroup JOIN Semester ON " +
                             $"StudentGroup.SemesterId = Semester.Id WHERE StudentGroup.Id = {studentGroupId}))";
            return GetItems("Course", selectStr);
        }
        public static ObservableCollection<object> GetLessonsByStudentGroupId(int studentGroupId)
        {
            string selectString =
                $"SELECT * FROM Lesson " +
                $"WHERE Id in (SELECT LessonId FROM LessonStudentGroup WHERE StudentGroupId = {studentGroupId})";

            //Get Lessons
            ObservableCollection<object> lessonList = GetItems("Lesson", selectString);

            //Inser Teacher, Room, StudentGroup ids into them
            List<string> mnList = new List<string> { "Teacher", "Room", "StudentGroup" };
            foreach (var l in lessonList)
            {
                var lesson = l as Lesson;
                foreach (string mnTableName in mnList)
                {
                    DataTable dt = SelectRows($"SELECT {mnTableName}Id FROM Lesson{mnTableName} WHERE LessonId = {lesson.Id};", $"Lesson{mnTableName}");
                    List<int> ids = new List<int>();
                    if (dt.Rows.Count > 0)
                    {
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            DataRow dr = dt.Rows[i];
                            string s = dr[$"{mnTableName}Id"].ToString();
                            int id = 0;
                            if (Int32.TryParse(s, out id))
                            {
                                ids.Add(id);
                            }
                        }
                    }
                    lesson.SetEntities(mnTableName, ids);
                }
            }

            return lessonList;
        }

        //CourseChecklist
        public static DataTable GetCourseChecklist(int StudentGroupId)
        {
            string selectString = $"SELECT * FROM CourseCheck WHERE CourseId IN(SELECT CourseId FROM CurriculumCourse WHERE " +
                                $"(CurriculumId, SemesterTypeId, YearOfStudy) IN (SELECT CurriculumId, SemesterTypeId, YearOfStudy FROM StudentGroup " +
                                $"AS sg INNER JOIN Semester AS s ON sg.SemesterId = s.Id  WHERE sg.Id = {StudentGroupId}));";

            DataTable dt = GetTableByName("CourseCheck", selectString);

            dt.Columns.Add("Hours", typeof(int));
            dt.Columns.Add("HoursReached", typeof(bool));
            dt.Columns.Add("HasTeacher", typeof(bool));
            dt.Columns.Add("HasRoom", typeof(bool));

            dt.Columns["HoursReached"].DefaultValue = false;
            dt.Columns["Hours"].DefaultValue = 0;

            return dt;
        }
        public static DataTable GetCourseList(int StudentGroupId)
        {
            string selectString = $"SELECT * FROM CourseCheck WHERE CourseId IN(SELECT CourseId FROM CurriculumCourse WHERE " +
                                $"(CurriculumId, SemesterTypeId, YearOfStudy) IN (SELECT CurriculumId, SemesterTypeId, YearOfStudy FROM StudentGroup " +
                                $"AS sg INNER JOIN Semester AS s ON sg.SemesterId = s.Id  WHERE sg.Id = {StudentGroupId}));";

            DataTable dt = GetTableByName("CourseCheck", selectString);
            return dt;
        }

        //SchedulingAlgorithm
        public static DataTable GetEmptyTableByName(string TableName)
        {
            DataTable dt = new DataTable(TableName);
            using (SQLiteConnection connection = new SQLiteConnection(App.DBFile + ";foreign keys=true;"))
            {
                connection.Open();
                //Get columns in datatable
                string Query = $"SELECT * FROM pragma_table_info('{TableName}');";
                SQLiteCommand command = new SQLiteCommand(Query, connection);
                SQLiteDataReader reader = command.ExecuteReader();

                List<string> primaryKeyColNames = new List<string>();
                while (reader.Read())
                {
                    DataColumn dataCol = new DataColumn();
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        /*ROW
                        cid = column index
                        name = column name
                        type = DATA TYPE
                        notNull = boolean whether it can be null
                        dftl_value = predefined default value
                        pk = 0 - if not primary key, 0< if primary key (and what column it is in the primary key)
                        //Debug.WriteLine($"{reader.GetName(i)} {reader.GetValue(i)}");
                         */

                        if (reader.GetName(i) == "name")
                        {
                            dataCol.ColumnName = reader.GetString(i);
                            dataCol.Caption = dataCol.ColumnName;
                            //Debug.WriteLine(dataCol.ColumnName);
                            continue;
                        }

                        if (reader.GetName(i) == "type")
                        {
                            string colTypeStr = reader.GetString(i);
                            if (colTypeStr == "INTEGER")
                            {
                                dataCol.DataType = typeof(Int32);
                            }
                            else if (colTypeStr == "TEXT")
                            {
                                if (dataCol.ColumnName.Contains("DT"))
                                {
                                    dataCol.DataType = typeof(DateTime);
                                }
                                else
                                {
                                    dataCol.DataType = typeof(string);
                                }
                            }

                            continue;
                        }

                        if (reader.GetName(i) == "notnull")
                        {
                            if (reader.GetBoolean(i) == false)
                            {
                                dataCol.AllowDBNull = true;
                            }
                            else
                            {
                                dataCol.AllowDBNull = false;
                            }
                            continue;
                        }

                        if ((reader.GetName(i) == "pk") && (reader.GetInt32(i) > 0))
                        {
                            primaryKeyColNames.Add(dataCol.ColumnName);
                        }
                    }
                    dt.Columns.Add(dataCol);
                }

                //Set the primary columns
                DataColumn[] primaryKeyCols = new DataColumn[primaryKeyColNames.Count];
                for (int i = 0; i < primaryKeyCols.Length; i++)
                {
                    string colName = primaryKeyColNames[i];
                    primaryKeyCols[i] = dt.Columns[colName];
                }
                dt.PrimaryKey = primaryKeyCols;
                connection.Close();
            }

            return dt;
        }
        public static DataTable GetModifiedEmptyLessonTable()
        {
            DataTable dt = GetEmptyTableByName("Lesson");
            for (int i = dt.Columns.Count - 1; i >= 0; i--)
            {
                DataColumn dataCol = dt.Columns[i];
                if ((dataCol.ColumnName == "StartDateDT") || (dataCol.ColumnName == "PeriodStart") || (dataCol.ColumnName == "SemesterId") || (dataCol.ColumnName == "Note"))
                {
                    dt.Columns.Remove(dataCol);
                }
            }

            return dt;
        }
        public static ObservableCollection<object> GetConflictLessons(Lesson lssn,int periodStart, int periodEnd)
        {
            ObservableCollection<object> lessonColl = new ObservableCollection<object>();
            string selectString = LoadLessonCollisionsBaseStringCreate(lssn, false, true, "*");
            if (selectString.Length > 0)
            {
                selectString += $" AND (PeriodStart + Hours)>{periodStart} AND PeriodStart<{periodEnd};";
                //Debug.WriteLine("GetConflictLessons: " + selectString);
                lessonColl = GetItems("Lesson", selectString);
            }

            return lessonColl;
        }
    }
}
