using Pervasive.Data.SqlClient;
using System;
using System.Collections.ObjectModel;
using System.Diagnostics;
using UWP_Sample.Models;
using Windows.UI.Xaml;
using Windows.UI.Xaml.Controls;

// The Blank Page item template is documented at https://go.microsoft.com/fwlink/?LinkId=402352&clcid=0x409

namespace UWP_Sample
{
    /// <summary>
    /// An empty page that can be used on its own or navigated to within a Frame.
    /// </summary>
    public sealed partial class MainPage : Page
    {


        public ObservableCollection<Employee> Employees = new ObservableCollection<Employee>();

        public MainPage()
        {
            this.InitializeComponent();            
            this.DataContext = Employees;
        }

        // Connection String to which the App connects and performs all the operations
        PsqlConnection conn = new PsqlConnection("Host=localhost;Port=1583;ServerDSN=DEMODATA;");
        PsqlCommand cmd = new PsqlCommand();

        // This will be the table on which all the operations takes place
        string UWP_TABLE_NAME = "UWP_Employees";
        string logText = string.Empty;
        string query = string.Empty;

        #region Button_Click Methods
        /// <summary>
        /// Method which is called when the user clicks the Create Table Button/Icon
        /// Creates a Table with name "UWP_Employees"
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Create_Button_Click(object sender, RoutedEventArgs e)
        {
            query = $@"CREATE TABLE {UWP_TABLE_NAME}(EmpId int,EmpName varchar(100),EmpDesignation varchar(50),EmpRole varchar(50),EmpSal int)";

            int recordsAffected = ExecuteNonQuery(query);
            logText += (recordsAffected == -1) ? "Table: '" + UWP_TABLE_NAME + "' Successfully Created !!\n" : string.Empty;
            CreateTable_Log.Text = logText;
        }

        /// <summary>
        /// Called when the user clicks the "Drop" Button on icon
        /// Drops the Table "UWP_Employees" if it already exists. Shows the Exception in UI otherwise
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Drop_Button_Click(object sender, RoutedEventArgs e)
        {
            bool isTableExistInitially = IsTableExist();
            query = $@"DROP TABLE {UWP_TABLE_NAME}";

            ExecuteNonQuery(query);

            if (isTableExistInitially)
            {
                logText += (IsTableExist()) ? String.Empty : "Table " + UWP_TABLE_NAME + " Successfully Dropped !!\n";
            }
            DropTable_Log.Text = logText;
        }

        /// <summary>
        /// Called when the User clicks the "Delete Employee" button from the "Delete Employees " Tab
        /// Deletes the rows corresponding to the Employee ID provided in UI from the "UWP_Employees" Table
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void DelEmp_Button_Click(object sender, RoutedEventArgs e)
        {
            string ID = DelEmpID.Text;
            int recordsAffected = 0;
            logText = string.Empty;

            query = $@"Delete from {UWP_TABLE_NAME} where EmpId={ID}";

            // Check if the table UWP_Employees exists. If not, ask the user to create the same 
            if (!(IsTableExist()))
            {
                DelEmployee_Log.Text = logText + "Table " + UWP_TABLE_NAME + " doesnt exist. Please create the table  from the 'Create Table' tab before deleting the rows";
                return;
            }

            recordsAffected = ExecuteNonQuery(query);

            if (recordsAffected >= 1)
            {
                logText += "Employee(s) with EmpId " + ID + " Successfully Deleted !!\n\n";
            }
            else
            {
                logText += "No Records found with EmpId= " + ID;
            }

            DelEmployee_Log.Text = logText;

        }

        /// <summary>
        /// Called when the User clicks the  "Add Employee" Button from the "Add Employees" Tab
        /// Inserts a new row into "UWP_Employees" Table with all the details provided from the UI
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void AddEmp_Button_Click(object sender, RoutedEventArgs e)
        {
            int recordsAffected = 0;
            string ID = EmpID.Text;
            string Name = EmpName.Text;
            string Designation = EmpDesignation.Text;
            string Role = EmpRole.Text;
            string Sal = string.IsNullOrEmpty(EmpSalary.Text) ? "0" : EmpSalary.Text;

            logText = string.Empty;

            query = $@"INSERT INTO {UWP_TABLE_NAME} VALUES ( { ID },'{ Name }','{ Designation}','{ Role}'," + Sal + " )";

            // Check if the table UWP_Employees exists. If not, ask the user to create the same 
            if (!(IsTableExist()))
            {
                AddEmployee_Log.Text = logText + "Table " + UWP_TABLE_NAME + " doesnt exist. Please create the table from the 'Create Table' tab before adding the employee";
                return;
            }

            recordsAffected = ExecuteNonQuery(query);

            logText = logText + "\nRecords Affected: " + recordsAffected + "\n\n";            
            AddEmployee_Log.Text = logText;
        }

        /// <summary>
        /// Called when the user clicks the "Update Employee" button from the "Update Employees" Tab
        /// Updates the row based on the EmpID provided in the UI from the "UWP_Employees" Table
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void UpdateEmp_Button_Click(object sender, RoutedEventArgs e)
        {
            int recordsAffected = 0;
            string ID = UpdateEmpID.Text;
            string Name = UpdateEmpName.Text;
            string Designation = UpdateEmpDesignation.Text;
            string Role = UpdateEmpRole.Text;
            string Sal = UpdateEmpSalary.Text;
            logText = string.Empty;

            // Check if the table UWP_Employees exists. If not, ask the user to create the same 
            if (!(IsTableExist()))
            {
                UpdateEmployee_Log.Text = "Records Affected: " + recordsAffected + "\n\n";
                UpdateEmployee_Log.Text = "Table " + UWP_TABLE_NAME + " doesnt exist. Please create the table '" + UWP_TABLE_NAME + "' from the 'Create Table', add employees through 'Update Employees' tab before updating "; ;
                return;
            }
            query = $@"UPDATE {UWP_TABLE_NAME} SET EmpName = '{Name}'";
            query += string.IsNullOrEmpty(Designation) ? string.Empty : $", EmpDesignation = '{Designation}'";
            query += string.IsNullOrEmpty(Role) ? string.Empty : $", EmpRole = '{Role}'";
            query += string.IsNullOrEmpty(Sal) ? string.Empty : $", EmpSal = {Sal}";
            query += $" WHERE EmpId = {ID} ";

            recordsAffected = ExecuteNonQuery(query);

            logText += "Records Affected: " + recordsAffected + "\n\n";
            logText += recordsAffected > 0 ? ("Employee with EmpID '" + ID+"' Updated") : "Employee with EmpID '"+ID+"' not updated. Please Check if the record exists";

            UpdateEmployee_Log.Text = logText;
        }

        private void GetEmp_Button_Click(object sender, RoutedEventArgs e)
        {
            logText = string.Empty;
            try
            {
                //lets flush the data already in the list before updating it with the latest retrieval
                Employees.Clear();

                // Check if the table UWP_Employees exists. If not, ask the user to create the same 
                if (!(IsTableExist()))
                {                     
                    logText = "Table " + UWP_TABLE_NAME + " doesnt exist. Please create the table '" + UWP_TABLE_NAME + "' from the 'Create Table', add employees through 'Add Employees' tab before retrieving "; ;
                    GetEmployee_Log.Text = logText;
                    return;
                }

                query = $"SELECT * FROM {UWP_TABLE_NAME}";                

                ExecuteReaderAndUpdateList(query);                
            }

            catch (Exception eSql)
            {
                Debug.WriteLine("\nException: " + eSql.Message);
                logText += "Exception : "+ eSql.Message;
            }
            GetEmployee_Log.Text = logText;
        }

#endregion Button_Click Methods

        #region Helper Methods
        private bool IsTableExist()
        {
            bool result = false;
            int count = 0;
            try
            {
                if ((conn.State != System.Data.ConnectionState.Open))
                {
                    conn.Open();
                    Debug.WriteLine("Connection Opened: ");
                }
                cmd.Connection = conn;
                cmd.CommandText = $"select count(*) from X$File where Xf$Name = '{UWP_TABLE_NAME}'";

                count = (int)cmd.ExecuteScalar();

                if (count >= 1)
                {
                    result = true;
                }
            }
            catch (Exception ex)
            {
                Debug.WriteLine("**** Exception : " + ex.Message + " ****");
            }
            finally
            {
                conn.Close();
            }
            return result;
        }
        private int ExecuteNonQuery(string query)
        {
            int rowsEffected = 0;
            logText = string.Empty;
            try
            {
                if ((conn.State != System.Data.ConnectionState.Open))
                {
                    conn.Open();
                    Debug.WriteLine("Connection Opened: ");
                }
                cmd.Connection = conn;
                cmd.CommandText = query;

                logText = "Query Executed : " + query + "\n\n";
                rowsEffected = cmd.ExecuteNonQuery();
                
            }
            catch (Exception ex)
            {
                logText = logText + "Query execution failed with exception: " + ex.Message;
            }
            finally
            {
                conn.Close();
            }
            return rowsEffected;
        }      

        void ExecuteReaderAndUpdateList(string query)
        {
            PsqlDataReader reader = null;            
            logText = string.Empty;
            try
            {
                if ((conn.State != System.Data.ConnectionState.Open))
                {
                    conn.Open();                    
                }
                cmd.Connection = conn;
                cmd.CommandText = query;

                logText = "Query Executed : " + query + "\n\n";
                reader = cmd.ExecuteReader();
                int rowCount = 0;
                // We will update the Employees Observable collection with the data from each row returned
                // so that the data gets updated to the UI
                while (reader.Read())
                {
                    Employee emp = new Employee();

                    emp.EmpID = (int)reader.GetInt16(0);
                    emp.EmpName = reader.GetString(1);
                    emp.Designation = reader.GetString(2);
                    emp.Role = reader.GetString(3);
                    emp.Sal = (int)reader.GetInt64(4);                    
                    
                    // Add the Employee object to the ObservableCollection of Employee objects
                    Employees.Add(emp);
                    rowCount++;
                }
                logText += "Total Rows :" + rowCount;
            }
            catch (Exception ex)
            {
                logText = logText + "\nQuery execution failed with exception: " + ex.Message;
            }
            finally
            {
                conn.Close();
            }            
        }

        #endregion Helper Methods
    }

}
