using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.IO;
using System.Text.RegularExpressions;
using System.Threading;

namespace Bookings_And_Loans.Database
{
    /// <summary>
    /// This file is used to handle all data for my bookings and loans tables
    /// It will load and save tables, and control validation rules
    /// </summary>
    class csDatabase
    {
        //Create the dataset and datatables
       public static DataSet dbBookings = new DataSet("tables");
       public static DataTable dtBookings = new DataTable("bookings");
       public static DataTable dtLoans = new DataTable("loans");
       public static DataTable dtArchivedBookings = new DataTable("archived bookings");
       public static DataTable dtArchivedLoans = new DataTable("archived loans");
        //Set the field seperator for the tables when loading from the file
       static string fieldSeperator = "##";

        public static void SetTableStructures()
        {
            //**********************************************
            //*************** Bookings Table ***************
            //**********************************************
           //Create the structure for bookings table
            //Add all required columns to the table
            dtBookings.Columns.Add("Booking ID", typeof(int));
            dtBookings.Columns.Add("Student Name", typeof(string));
            dtBookings.Columns.Add("Facility", typeof(string));
            dtBookings.Columns.Add("Date", typeof(DateTime));
            dtBookings.Columns.Add("Time", typeof(string));
            dtBookings.Columns.Add("Duration", typeof(string));
            //Add table to database and load the file
            dbBookings.Tables.Add(dtBookings);
            LoadTable("bookings");

            //**********************************************
            //*************** Loans Table ******************
            //**********************************************
            //Create the structure for loans table
            //Add all required columns to the table
            dtLoans.Columns.Add("Loan ID", typeof(int));
            dtLoans.Columns.Add("Student Name", typeof(string));
            dtLoans.Columns.Add("Equipment", typeof(string));
            dtLoans.Columns.Add("Date Taken", typeof(DateTime));
            dtLoans.Columns.Add("Date Expected Back", typeof(DateTime));
            dtLoans.Columns.Add("Overdue", typeof(bool));
            //Add table to database and load the file
            dbBookings.Tables.Add(dtLoans);
            LoadTable("loans");
            //Set the validation rules for the main/current tables
            LoadValidationRules();

            //**********************************************
            //*********** Bookings Archive Table ***********
            //**********************************************
            //Create the structure for bookings archive table
            dtArchivedBookings.Columns.Add("Booking ID", typeof(int));
            dtArchivedBookings.Columns.Add("Student Name", typeof(string));
            dtArchivedBookings.Columns.Add("Facility", typeof(string));
            dtArchivedBookings.Columns.Add("Date", typeof(DateTime));
            dtArchivedBookings.Columns.Add("Time", typeof(string));
            dtArchivedBookings.Columns.Add("Duration", typeof(string));
            dtArchivedBookings.Columns.Add("Date Archived", typeof(DateTime));
            //Add table to database and load the file
            dbBookings.Tables.Add(dtArchivedBookings);
            LoadTable("archived bookings");

            //**********************************************
            //*********** Loans Archive Table ***********
            //**********************************************
            //Create the structure for loans archive table
            dtArchivedLoans.Columns.Add("Loan ID", typeof(int));
            dtArchivedLoans.Columns.Add("Student Name", typeof(string));
            dtArchivedLoans.Columns.Add("Equipment", typeof(string));
            dtArchivedLoans.Columns.Add("Date Taken", typeof(DateTime));
            dtArchivedLoans.Columns.Add("Date Expected Back", typeof(DateTime));
            dtArchivedLoans.Columns.Add("Date Archived", typeof(DateTime));
            //Add table to database and load the file
            dbBookings.Tables.Add(dtArchivedLoans);
            LoadTable("archived loans");
        }

        public static void LoadTable(string tableToLoad)
        {
            //Set a string to the filename to load records from
            string fileToLoadFrom = tableToLoad + ".txt";
            if (File.Exists(fileToLoadFrom))
            {
                //If the file exists, start reading it and place the data into the relevant
                //fields in the table
                StreamReader swFileReader = new StreamReader(fileToLoadFrom);
                string line = "";
                while ((line = swFileReader.ReadLine()) != null)
                {
                    DataRow rowToWriteTo = dbBookings.Tables[tableToLoad].NewRow();
                    string[] fields = Regex.Split(line, fieldSeperator);
                    for (int repeat = 0; repeat < dbBookings.Tables[tableToLoad].Columns.Count; repeat++)
                    {
                        try
                        {
                            string valueToInsert = fields[repeat];
                            string columnDataType = dbBookings.Tables[tableToLoad].Columns[repeat].DataType.ToString();
                            switch (columnDataType)
                            {
                                case "System.DatTime": rowToWriteTo[repeat] = DateTime.Parse(fields[repeat]); break;
                                default: rowToWriteTo[repeat] = fields[repeat]; break;
                            }
                        }
                        catch (Exception error) { }
                    }
                    //Add the record to the table
                    dbBookings.Tables[tableToLoad].Rows.Add(rowToWriteTo);
                }
                swFileReader.Close();
            }
        }

        public static void SaveTable(string tableToSave)
        {
            //Call recursive procedure WriteFile to create the table file and save all records
            WriteFile(tableToSave, 0);
        }

        public static void SaveAllTables()
        {
            //Create a new thread (Multi threading) to avoid slowing down the main application
            new Thread(delegate()
            {
                //Loop through the database, saving each table
                for (int repeat = 0; repeat < dbBookings.Tables.Count; repeat++)
                {
                    string tableToSave = dbBookings.Tables[repeat].ToString();
                    //Call recursive procedure WriteFile to create the table file and save all records
                    WriteFile(tableToSave, 0);
                }
            }).Start();
        }

        static void WriteFile(string tableToWrite, int rowIndexToAppend)
        {
            //Reccursive procedure to save all records of a table to a text file
            //BASE CONDITION, if the row doesn't exist, end the call
            if (rowIndexToAppend >= dbBookings.Tables[tableToWrite].Rows.Count) { return; }
            string lineToAppend = "";
            DataRow rowToAppend = dbBookings.Tables[tableToWrite].Rows[rowIndexToAppend];
            //If this is a new file to be saved, delete the old one to avoid replication of data
            if (rowIndexToAppend == 0) { File.Delete(tableToWrite + ".txt"); }
            //Set writer to append text to file
            StreamWriter swFileWriter = File.AppendText(tableToWrite + ".txt");
            //Loop through each column, adding the data to the string to be write
            for (int repeat = 0; repeat < dbBookings.Tables[tableToWrite].Columns.Count; repeat++)
            {
                string column = dbBookings.Tables[tableToWrite].Columns[repeat].ToString();
                if (repeat == 0) { lineToAppend = rowToAppend[column].ToString(); }
                else { lineToAppend += fieldSeperator + rowToAppend[column].ToString(); }
            }
            //Write the string to the file
            swFileWriter.WriteLine(lineToAppend);
            swFileWriter.Flush();
            swFileWriter.Close();
            //Call procedure in order to process the next row
            WriteFile(tableToWrite, (rowIndexToAppend + 1));
        }

        public static void LoadValidationRules()
        {
            //Set validation rules for each column in the BOOKINGS TABLE
            dbBookings.Tables["bookings"].Columns["Booking ID"].Unique = true;
            dbBookings.Tables["bookings"].Columns["Student Name"].MaxLength = 30;
            //Set regular expressions in the captions, in order to validate on entry
            dbBookings.Tables["bookings"].Columns["Booking ID"].Caption = "[0-9]*";
            dbBookings.Tables["bookings"].Columns["Student Name"].Caption = ".*\\s.*";
            dbBookings.Tables["bookings"].Columns["Facility"].Caption = ".*";
            dbBookings.Tables["bookings"].Columns["Time"].Caption = "[0-2]?[0-9]:[0-5][0-9]";
            dbBookings.Tables["bookings"].Columns["Duration"].Caption = "[0-1]?[0-9]?[0-9]";

            //Set validation rules for each column in the LOANS TABLE
            dbBookings.Tables["loans"].Columns["Loan ID"].Unique = true;
            dbBookings.Tables["loans"].Columns["Student Name"].MaxLength = 30;
            dbBookings.Tables["loans"].Columns["Loan ID"].Caption = "[0-9]*";
            dbBookings.Tables["loans"].Columns["Student Name"].Caption = ".*\\s.*";
            dbBookings.Tables["loans"].Columns["Equipment"].Caption = ".*";
        }

        public static void ClearAllTables()
        {
            for (int repeat = 0; repeat < dbBookings.Tables.Count; repeat++)
            {
                string tableToClear = dbBookings.Tables[repeat].TableName;
                //Clear the table in runtime memory
                Database.csDatabase.dbBookings.Tables[tableToClear].Clear();
                //Delete the file as well
                File.Delete(tableToClear + ".txt");
            }
        }

        public static void ProcessOutdatedRecords()
        {
            //Create a new thread (Multi threading) to avoid slowing down the main application
            new Thread(delegate()
            {
                //Go through all records in the Bookings table in a linear fashion in order to check if the record is outdated
                for (int repeat = 0; repeat < dbBookings.Tables["bookings"].Rows.Count; repeat++)
                {
                    DataRow rowToCheck = dbBookings.Tables["bookings"].Rows[repeat];
                    string dateOfBooking = rowToCheck["Date"].ToString().Split(' ')[0];
                    string timeOfBookingExpiration = DateTime.Parse(rowToCheck["Time"].ToString()).AddMinutes(Convert.ToInt32(rowToCheck["Duration"])).TimeOfDay.ToString();
                    string dateAndTimeOfBookingExpiration = dateOfBooking + " " + timeOfBookingExpiration;
                    DateTime dateTimeOfBookingExpiration = DateTime.Parse(dateAndTimeOfBookingExpiration);
                    DateTime today = DateTime.Now;
                    int compareBookingToNow = DateTime.Compare(dateTimeOfBookingExpiration, today);
                    //If the date and time held by the booking has passed, move the record to the archive table
                    if (compareBookingToNow.Equals(-1))
                    { MoveRecordToArchive(rowToCheck, "bookings"); }
                }
                //Go through all records in the Loans table in a linear fashion in order to check if the records date indicates the equipment is overdue to be returned
                for (int repeat = 0; repeat < dbBookings.Tables["loans"].Rows.Count; repeat++)
                {
                    DataRow rowToCheck = dbBookings.Tables["loans"].Rows[repeat];
                    string expectedReturnDate = rowToCheck["Date Expected Back"].ToString();
                    DateTime dateTimeOfLoanExpiration = DateTime.Parse(expectedReturnDate);
                    DateTime today = DateTime.Now;
                    int compareExpirationToNow = DateTime.Compare(dateTimeOfLoanExpiration, today);
                    //If the date held by the record is of a date earlier than today, set the overdue flag as true
                    if (compareExpirationToNow.Equals(-1))
                    { rowToCheck["Overdue"] = "True"; }
                    else
                    { rowToCheck["Overdue"] = "False"; }
                }
            }).Start();
        }

        public static void MoveRecordToArchive(DataRow rowToArchive, string tableToArchiveFrom)
        {
            //Procedure to move a record from the table containing the original to the equivalent archive table
            string tableToArchiveTo = "archived " + tableToArchiveFrom;
            dbBookings.Tables[tableToArchiveTo].ImportRow(rowToArchive);
            dbBookings.Tables[tableToArchiveFrom].Rows.Remove(rowToArchive);
        }

        public static void GenerateReport(DataView recordsToOutput, string outputFileName)
        {
            //Procedure to generate a report containing a selection of records in the format of a HTML document
            if(File.Exists(outputFileName)) { File.Delete(outputFileName); }
            //Set the files containing the header and footer to be included
            StreamReader swReadHeaderFile = new StreamReader("ReportHeader.txt");
            StreamReader swReadFooterFile = new StreamReader("ReportFooter.txt");
            StreamWriter swWriteReportFile = File.AppendText(outputFileName);
            string readLineFromText = "";
            string tableCaption = "Report to show " + recordsToOutput.Table.TableName;
            int numberOfColumns = recordsToOutput.Table.Columns.Count;

            //Read the header file and write it to the report output file
            while ((readLineFromText = swReadHeaderFile.ReadLine()) != null)
            {
                swWriteReportFile.WriteLine(readLineFromText);
            }
            swWriteReportFile.WriteLine("<font size=\"1\"><i>" + DateTime.Now.ToString("dddd, dd MMMM yyyy HH:mm:ss") + "</i></font>");
            //If there is a filter on the dataview
            if (recordsToOutput.RowFilter.Length > 0)
            {
                string filterApplied = recordsToOutput.RowFilter;
                //Create the table which will contain the criteria for the search
                swWriteReportFile.WriteLine("<table class=\"CriteriaTable\" width=\"200\" border=\"1\">");
                //Create a caption for the table
                swWriteReportFile.WriteLine("<CAPTION CLASS=\"RecordTable\"> <b>Search Criteria</b> </CAPTION>");
                swWriteReportFile.WriteLine("<TR CLASS=\"RecordTable\">");
                Regex rgxFiltersApplied = new Regex(" AND ");
                string[] filtersApplied = rgxFiltersApplied.Split(filterApplied);
                swWriteReportFile.WriteLine("<TR CLASS=\"RecordTable\">");
                swWriteReportFile.WriteLine("<TD CLASS=\"RecordTable\">");
                for (int repeat = 0; repeat < filtersApplied.Length; repeat++)
                {
                    string filterAppliedFormatted = filtersApplied[repeat].Replace("LIKE", "=").Replace("'%", "'").Replace("%'", "'").Replace("[", "").Replace("]", "");
                    swWriteReportFile.WriteLine("<p>" + filterAppliedFormatted + "</p>");
                }
                swWriteReportFile.WriteLine("</td>");
                swWriteReportFile.WriteLine("</tr>");
                swWriteReportFile.WriteLine("</table>");
            }

            //Create the table which will contain the records
            swWriteReportFile.WriteLine("<table class=\"RecordTable\" width=\"600\" border=\"1\">");
            //Create a caption for the table
            swWriteReportFile.WriteLine("<CAPTION CLASS=\"RecordTable\">" + tableCaption + "</CAPTION>");
            swWriteReportFile.WriteLine("<TR CLASS=\"RecordTable\">");

            //Create the cells in the table to display the column names
            for (int a = 0; a < numberOfColumns; a++)
            {
                string columnToCreate = recordsToOutput.Table.Columns[a].ToString();
                int spaceToAllocateColumn = 13 * columnToCreate.Length;
                string lineToAppend = "<th class=\"RecordTable\" width=\"" + spaceToAllocateColumn + "\">" + columnToCreate + "</th>";
                swWriteReportFile.WriteLine(lineToAppend);
            }
            swWriteReportFile.WriteLine("</td>");

            //Create the rows in the table to display the records
            for (int a = 0; a < recordsToOutput.Count; a++)
            {
                DataRowView recordToOutput = recordsToOutput[a];
                swWriteReportFile.WriteLine("<tr>");
                //Create the cells within each row to display the contents of each field in the record
                for(int b=0;b< numberOfColumns;b++)
                {
                    string cellValue;
                    string columnDataType = recordsToOutput.Table.Columns[b].DataType.ToString();
                    //If the value is a date, cut off the time element which will follow (And is not set)
                    switch (columnDataType)
                    {
                        case "System.DateTime":
                            cellValue = recordToOutput[b].ToString().Split(' ')[0];
                            break;
                        default:
                            cellValue = recordToOutput[b].ToString();
                            break;
                    }
                    string lineToAppend = "<TD CLASS=\"RecordTable\">" + cellValue + "</td>";
                    swWriteReportFile.WriteLine(lineToAppend);
                }
                swWriteReportFile.WriteLine("</tr>");
            }

            //Include the tag which closes the table
            swWriteReportFile.WriteLine("</table>");

            //Include the date and time that the report was generated
            //Read the footer file and write it to the report output file
            while ((readLineFromText = swReadFooterFile.ReadLine()) != null)
            {
                swWriteReportFile.WriteLine(readLineFromText);
            }
            swWriteReportFile.Close();
            swReadHeaderFile.Close();
            swReadFooterFile.Close();
        }
    }
}