using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Text.RegularExpressions;


namespace OrionGames
{
    public partial class OrionGames : Form
    {
        SqlConnection sql_connection;
        SqlDataReader myReader;
        

        public OrionGames()
        {
            InitializeComponent();
            //Connect to the Orion_Games database.
            String connection_string = "Data Source=localhost; database=Orion_Games; Integrated Security = true; connection timeout=15;"; 
            sql_connection = new SqlConnection(connection_string);
            sql_connection.Open();
        }


        //This method checks that the string argument is comprised only of numbers.
        public bool isNumeric(String s)
        {
            Regex myRegex = new Regex("^[0-9]+$");
            if (myRegex.IsMatch(s))
                return true;
            return false;
        }



        private void btnHireNew_Click(object sender, EventArgs e)
        {
            textHireMemID.ReadOnly = false;
            txtHireGmeID.ReadOnly = false;
            dateHireDate.Enabled = true;
            listHires.Enabled = true;
            btnHireAddGme.Enabled = true;
            btnHireSave.Enabled = true;
            textHireMemID.Text = "";
            txtHireGmeID.Text = "";
            listHires.Items.Clear();
        }



        private void btnHireAddGme_Click(object sender, EventArgs e)
        {
            String[] game = new String[2];
            String game_id = txtHireGmeID.Text;
            SqlCommand find_game;

        
            if (listHires.Items.Count >= 1 && listHires.FindItemWithText(game_id,true,0) != null)
            {
                MessageBox.Show("That game is already in the list");
                return;
            }
            
            //Checks the validity of the game id that was entered. (Is it a number?, IS IT ALREADY IN THE LIST?)
            if (isNumeric(game_id))
            {
                find_game = new SqlCommand("Select gid, gtitle, gstatus from games where gid = " + game_id, sql_connection);
                myReader = find_game.ExecuteReader();
            }
            else
            {
                MessageBox.Show("Invalid Game ID");
                return;
            }

            //There was a resulting row from the SQL query.
            if (myReader.HasRows)
            {
                myReader.Read();
                if (myReader[2].ToString() == "out")
                {
                    MessageBox.Show("The Game is currently being hired by another member!");
                    myReader.Close();
                    return;
                }
                game[0] = myReader[0].ToString();
                game[1] = myReader[1].ToString();
                listHires.Items.Add(new ListViewItem(game));
                txtHireGmeID.Text = "";
            }
            else
                MessageBox.Show("The Game with an ID of " + game_id + " was not found!");
            myReader.Close();
        }



        private void OrionGames_FormClosed(object sender, FormClosedEventArgs e)
        {
            //Closes connection to SQL server
            sql_connection.Close();
        }

        //Takes a string of the form DD/MM/YYYY and returns a DateTime object.
        public DateTime stringToDateTime(String date_string)
        {
            int dob_day = Convert.ToInt32(date_string.Substring(0, 2));
            int dob_month = Convert.ToInt32(date_string.Substring(3, 2));
            int dob_year = Convert.ToInt32(date_string.Substring(6, 4));
            DateTime dob_dt = new DateTime(dob_year, dob_month, dob_day);
            return dob_dt;
        }

        //Calculates members age
        public int getMembersAge(String member_id)
        {
            int age = 0;
            SqlCommand getDOB = new SqlCommand("select mdob from member where mid = " + member_id,sql_connection);
            myReader = getDOB.ExecuteReader();

            if (myReader.HasRows)
            {
                myReader.Read();
                DateTime dob_dt = stringToDateTime(myReader[0].ToString());
  
                // get the difference in years
                age = DateTime.Now.Year - dob_dt.Year;
                
                // subtract a year if the member has not yet had a birthday this year.
                if (DateTime.Now.Month < dob_dt.Month || (DateTime.Now.Month == dob_dt.Month && DateTime.Now.Day < dob_dt.Day))
                    age--;

                MessageBox.Show("The Members Age is : " + age);
            }
            myReader.Close();
            return age;
        }



        //Takes a game classification and a member age and calculates whether the member is old enough to hire the game.
        public bool gameIsAppropriate(String classification, int age)
        {
            const int R18 = 18;
            const int M = 13;

            switch (classification)
            {
                case "R18":
                    return age >= R18;
                case "M":
                    return age >= M;
                default:
                    return true;
            }
        }


        //Searches for a member and returns true if it finds the member or false if not.
        public bool memberExists(String member_id)
        {
            SqlCommand find_member = new SqlCommand("select * from member where mid = " + member_id, sql_connection);
            myReader = find_member.ExecuteReader();
            if (myReader.HasRows)
            {
                myReader.Close();
                return true;
            }
            myReader.Close();
            return false;
        }




        private void btnHireSave_Click(object sender, EventArgs e)
        {
            //Hire SqlParameters
            SqlParameter hissuedate = new SqlParameter("@hissuedate",SqlDbType.Char);
            SqlParameter hduedate = new SqlParameter("@hduedate",SqlDbType.Char);
            SqlParameter mid = new SqlParameter("@mid",SqlDbType.Int);
            SqlParameter identity = new SqlParameter("@identity", SqlDbType.Int); //Output Parameter
            identity.Direction = ParameterDirection.Output;

            //Includes SqlParameters
            SqlParameter hid = new SqlParameter("@hid", SqlDbType.Int);
            SqlParameter gid = new SqlParameter("@gid", SqlDbType.Int);
            SqlParameter irating = new SqlParameter("@irating", SqlDbType.Int);
            SqlParameter icomment = new SqlParameter("@icomment", SqlDbType.VarChar);

            int member_age;
            int games_hired; //Games actually hired. (User was old enough to hire em)
            int num_inappropriate = 0;
            double total_charge; //How much to charge member for the hire.
            String member_id = textHireMemID.Text;

            //Check that the text in textHireMemID only contains numbers.
            if (!isNumeric(member_id))
            {
                MessageBox.Show("Invalid Member ID","Hire Not Saved!");
                return;
            }

            string date_string = dateHireDate.Value.ToShortDateString().PadLeft(10,'0');
            string due_date_string = dateHireDate.Value.AddDays(7).ToShortDateString().PadLeft(10,'0');

            

            //Does The Member Exist?
            if (!memberExists(member_id))
            {
                MessageBox.Show("There is no member with an ID of " + member_id,"Hire Not Saved!");
                return;
            }


            //First we see what games the user is and isn't allowed hiring.
            //Calculate Age
            member_age = getMembersAge(member_id);
            
            //Remove games that the member is too young to play.

            foreach (ListViewItem lvi in listHires.Items)
            {
                SqlCommand get_classification = new SqlCommand("select gclas from games where gid = " + lvi.SubItems[0].Text, sql_connection);
                myReader = get_classification.ExecuteReader();
                myReader.Read();

                if (!gameIsAppropriate(myReader[0].ToString(), member_age))
                {
                    MessageBox.Show("The Member is not old enough to hire " + lvi.SubItems[1].Text,"Game Not Included In Hire");
                    listHires.Items.Remove(lvi);
                    num_inappropriate++;
                }

                myReader.Close();
            }

            games_hired = listHires.Items.Count;
            total_charge = games_hired * 8.00;

            if (games_hired > 0)
            {
                //After removing the inappropriate games, we have some left over.
                try
                {
                    hissuedate.Value = date_string;
                    hduedate.Value = due_date_string;
                    mid.Value = Convert.ToInt32(member_id);
                    SqlCommand hire_insert = new SqlCommand("insert into hire values (@hissuedate,@hduedate,@mid) SET @identity = SCOPE_IDENTITY()", sql_connection);

                    hire_insert.Parameters.Add(hissuedate);
                    hire_insert.Parameters.Add(hduedate);
                    hire_insert.Parameters.Add(mid);
                    hire_insert.Parameters.Add(identity);
                    hire_insert.ExecuteNonQuery();

                    //We need to make an includes row entry for every game in the hire.

                    SqlCommand includes_insert = new SqlCommand("insert into includes values (@hid,@gid,@irating,@icomment)", sql_connection);
                    includes_insert.Parameters.Add(hid);
                    includes_insert.Parameters.Add(gid);
                    includes_insert.Parameters.Add(irating);
                    includes_insert.Parameters.Add(icomment);

                    foreach (ListViewItem lvi in listHires.Items)
                    {
                        hid.Value = (int)identity.Value;
                        gid.Value = lvi.SubItems[0].Text;
                        irating.Value = DBNull.Value;
                        icomment.Value = DBNull.Value;
                        includes_insert.ExecuteNonQuery();
                    }
                }
                catch (SqlException ex)
                {
                    MessageBox.Show(ex.Message);
                }
                txtActionToTake.Text = "Charge the member " + total_charge.ToString("C") + ". Only give the member the " + games_hired.ToString() + " games listed above.";
                btnHireSave.Enabled = false;
                textHireMemID.ReadOnly = true;
                txtHireGmeID.ReadOnly = true;
                btnHireAddGme.Enabled = false;
                dateHireDate.Enabled = false;
            }
            else
            {
                MessageBox.Show("There are no games to be included in the hire.", "Hire Not Saved!");
                return;
            }
                
        }




        private void btnReturnNew_Click(object sender, EventArgs e)
        {
            txtReturnComment.ReadOnly = false;
            txtReturnGmeID.ReadOnly = false;
            txtReturnMemID.ReadOnly = false;
            dateReturnDate.Enabled = true;
            cboRating.Enabled = true;
            btnReturnAddGme.Enabled = true;
            listReturns.Enabled = true;
            btnReturnSave.Enabled = true;

        }




        private void btnReturnAddGme_Click(object sender, EventArgs e)
        {
            String game_id = txtReturnGmeID.Text;
            String[] game = new String[4];
            SqlCommand find_game;

            if (listReturns.Items.Count >= 1 && listReturns.FindItemWithText(game_id, true, 0) != null)
            {
                MessageBox.Show("That game is already in the list!");
                return;
            }

            //Check that a game with this ID exists.
            if (isNumeric(game_id))
            {
                find_game = new SqlCommand("Select gid, gtitle, gstatus from games where gid = " + game_id, sql_connection);
                myReader = find_game.ExecuteReader();
            }
            else
            {
                MessageBox.Show("Invalid Game ID");
                return;
            }

            if (cboRating.SelectedIndex == -1)
            {
                MessageBox.Show("Choose A Rating!");
                myReader.Close();
                return;
            }

            //There was a resulting row from the SQL query.
            if (myReader.HasRows)
            {
                myReader.Read();
                if (myReader[2].ToString() == "in")
                {
                    MessageBox.Show("The Game is not currently being hired!");
                    myReader.Close();
                    return;
                }
                game[0] = myReader[0].ToString();
                game[1] = myReader[1].ToString();
                game[2] = (String)cboRating.Items[cboRating.SelectedIndex];
                game[3] = txtReturnComment.Text;

                listReturns.Items.Add(new ListViewItem(game));
                txtReturnGmeID.Text = "";
                cboRating.SelectedIndex = -1;
                txtReturnComment.Text = "";
            }
            else
                MessageBox.Show("The Game with an ID of " + game_id + " was not found!");
            myReader.Close();
        }




        private void btnReturnSave_Click(object sender, EventArgs e)
        {
            int num_overdue = 0;
            double total_charge;
            String member_id = txtReturnMemID.Text;
            
            //The member_id supplied is only comprised of numbers?
            if (!isNumeric(member_id))
            {
                MessageBox.Show("Invalid Member ID", "Hire Not Saved!");
                return;
            }

            //Does The Member Exist?
            if (!memberExists(member_id))
            {
                MessageBox.Show("There is no member with an ID of " + member_id, "Return Not Saved!");
                return;
            }

            int games_returned = listReturns.Items.Count;

            if (games_returned > 0)
            {
                foreach (ListViewItem lvi in listReturns.Items)
                {
                    String hire_id;
                    
                    //Get the number of the hire that contains the particular game we are looking at.
                    SqlCommand get_hire = new SqlCommand("select max(hid) from includes where gid = " + lvi.SubItems[0].Text, sql_connection);
                    myReader = get_hire.ExecuteReader();
                    myReader.Read();
                    hire_id = myReader[0].ToString();
                    myReader.Close();

                    //Is the game overdue?                   
                    //Get the due date.
                    SqlCommand get_due_date = new SqlCommand("select hduedate from hire where hid = " + hire_id, sql_connection);
                    myReader = get_due_date.ExecuteReader();
                    myReader.Read();
                    DateTime due_date = stringToDateTime(myReader[0].ToString());
                    myReader.Close();
                    
                    //Compare the due date with the current date.
                    if (due_date.Date.CompareTo(dateReturnDate.Value.Date) < 0)
                    {
                        //This game is overdue!
                        num_overdue ++;
                        MessageBox.Show("This Game is Overdue!");
                    }

                    //Update the includes row entry with the new rating/comment.
                    SqlParameter irating = new SqlParameter("@irating", SqlDbType.Int);
                    SqlParameter icomment = new SqlParameter("@icomment", SqlDbType.VarChar);
                    SqlParameter hid = new SqlParameter("@hid", SqlDbType.Int);
                    SqlParameter gid = new SqlParameter("@gid", SqlDbType.Int);
                    
                    irating.Value = Convert.ToInt32(lvi.SubItems[2].Text);
                    icomment.Value = lvi.SubItems[3].Text;
                    hid.Value = hire_id;
                    gid.Value = Convert.ToInt32(lvi.SubItems[0].Text);
                    
                    SqlCommand update_includes = new SqlCommand("update includes set irating = @irating, icomment = @icomment where hid = @hid and gid = @gid",sql_connection);
                    
                    update_includes.Parameters.Add(irating);
                    update_includes.Parameters.Add(icomment);
                    update_includes.Parameters.Add(hid);
                    update_includes.Parameters.Add(gid);
                    
                    update_includes.ExecuteNonQuery();
                }
                if (num_overdue > 0)
                {
                    total_charge = num_overdue * 2.00;
                    txtActionToTake.Text = "Charge member " + total_charge.ToString("C") + " for returning " + num_overdue.ToString() + " overdue games.";
                    
                    //Update Members Overdue Counter (mnum_overdue)
                    SqlParameter mid = new SqlParameter("@mid", SqlDbType.Int);
                    mid.Value = Convert.ToInt32(member_id);
                    SqlCommand update_overdue = new SqlCommand("update member set mnum_overdue = mnum_overdue + " + num_overdue.ToString() + " where mid = @mid", sql_connection);
                    update_overdue.Parameters.Add(mid);
                    update_overdue.ExecuteNonQuery();
                }
                else
                    txtActionToTake.Text = "There were no overdue games returned. Give the member a pat on the back!";

                btnReturnSave.Enabled = false;
                btnReturnAddGme.Enabled = false;
                dateReturnDate.Enabled = false;
                cboRating.Enabled = false;
                txtReturnComment.ReadOnly = true;
                txtReturnGmeID.ReadOnly = true;
                txtReturnMemID.ReadOnly = true;
                txtReturnComment.ReadOnly = true;
                listReturns.Items.Clear();
                listReturns.Enabled = false;

            }
            else
            {
                MessageBox.Show("There are no games to be returned!", "Return Not Saved!");
                return;
            }



        }
    }
}