using Microsoft.Win32;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Windows;
using System.Windows.Media.Imaging;
using Word = Microsoft.Office.Interop.Word;

namespace mateo
{
    public partial class SysAdmin : Window
    {
        byte[] image; //очищать!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
        int idattraction = 0;
        int idemployees = 0;
        public SysAdmin()
        {
            InitializeComponent();
            var fio = DB.GetContext().Employees.FirstOrDefault(x => x.IDEmployees == DB.Idemployees);

            if (DB.Idemployees != 0)
            {
                txtFio_Admin.Text = DB.GetContext().Role.FirstOrDefault(x => x.IDRole == fio.FKRole).Role1 + "\n" + fio.LastName + " " + fio.FirstName + " " + fio.MiddleName;
                if (fio.FKRole == 2) //если простто админ скрываем доб сотрудников
                    tabEmployees.Visibility = Visibility.Collapsed;
            }

            datagridAttraction.ItemsSource = DB.GetContext().Attraction.ToList();
            datagridEmployees.ItemsSource = DB.GetContext().Employees.Where(x => x.IDEmployees != DB.Idemployees).ToList();
            comboboxChange_StatusAttraction.ItemsSource = comboboxStatus_Attraction.ItemsSource = DB.GetContext().StatusAttraction.ToList();
            comboboxChange_TypeAttraction.ItemsSource = comboboxType_Attraction.ItemsSource = DB.GetContext().Type.ToList();

            comboboxNameAttraction.ItemsSource = DB.GetContext().Attraction.ToList();//для отчета



        }

        #region Для тестов

        public byte[] AddImage()
        {
            string imageLoc;
            OpenFileDialog dld = new OpenFileDialog();
            dld.Filter = "JPG Files (*.jpg)|*.jpg|PNG Files (*.png)|*.png|JPEG Files (*.jpeg)|*.jpeg";
            dld.Title = "Выберите изображение пользователя";
            bool? result = dld.ShowDialog();
            if (result == true)
            {
                imageLoc = dld.FileName;
                imageAdd_Attraction.Source = new BitmapImage(new Uri(imageLoc));
                FileStream fs = new FileStream(imageLoc, FileMode.Open, FileAccess.Read);
                BinaryReader br = new BinaryReader(fs);
                image = br.ReadBytes((int)fs.Length);
            }
            return image;
        }

        public bool AddAttraction(int idemp, string name, string description, string quantity, string priceold, string pricebaby, byte[] image, string statusa, string typea)
        {
            bool tf = false;
            try
            {
                if (name != "" && description != "" && quantity != "" && priceold != "" &&
                    pricebaby != "" && image != null && statusa != "" && typea != "")
                {
                    var status = DB.GetContext().StatusAttraction.Where(x => x.StatusAttraction1 == statusa).FirstOrDefault();
                    var type = DB.GetContext().Type.Where(x => x.Type1 == typea).FirstOrDefault();
                    Attraction attraction = new Attraction
                    {
                        NameAttraction = name,
                        Description = description,
                        QuantityAttraction = Convert.ToInt32(quantity),
                        PriceOld = Convert.ToDecimal(priceold),
                        PriceBaby = Convert.ToDecimal(pricebaby),
                        ImageAttraction = image,
                        FKEmployees = idemp,
                        FKStatusAttraction = status.IDStatusAttraction,
                        FKType = type.IDType
                    };
                    DB.GetContext().Attraction.Add(attraction);
                    DB.GetContext().SaveChanges();

                    MessageBox.Show("Аттракцион добавлен", "Информация", MessageBoxButton.OK, MessageBoxImage.Information);
                    datagridAttraction.ItemsSource = DB.GetContext().Attraction.ToList();
                    gridAddAttraction.Visibility = Visibility.Hidden;

                    tf = true;
                }
                else MessageBox.Show("Пустые поля!", "Информация", MessageBoxButton.OK, MessageBoxImage.Information);
                return tf;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString(), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
                return false;
            }
        }

        public bool AddEmployees(int idemp, string lname, string fname, string mname, string rolea, string statusa)
        {
            bool tf = false;
            try
            {
                if (lname != "" && fname != "" && rolea != "" && statusa != "")
                {
                    var idemployees = DB.GetContext().Employees.OrderByDescending(x => x.IDEmployees).FirstOrDefault();
                    string login = "admin" + (idemployees.IDEmployees + 1).ToString();

                    Random random = new Random();
                    int pass1 = random.Next(100, 999);
                    var list = new List<string> { "a", "b", "c", "d", "e" };
                    int index = random.Next(list.Count);
                    int index1 = random.Next(list.Count);
                    int index2 = random.Next(list.Count);
                    int index3 = random.Next(list.Count);

                    string password = list[index] + list[index1] + pass1 + list[index2] + list[index3];
                    var status = DB.GetContext().StatusEmployees.Where(x => x.StatusEmployees1 == statusa).FirstOrDefault();
                    var role = DB.GetContext().Role.Where(x => x.Role1 == rolea).FirstOrDefault();
                    Employees employees = new Employees
                    {
                        LastName = lname,
                        FirstName = fname,
                        MiddleName = mname,
                        Login = login,
                        Password = password,
                        FKRole = role.IDRole,
                        FKStatusEmployees = status.IDStatusEmployees
                    };
                    DB.GetContext().Employees.Add(employees);
                    DB.GetContext().SaveChanges();
                    MessageBox.Show("Сотрудник добавлен", "Информация", MessageBoxButton.OK, MessageBoxImage.Information);

                    datagridEmployees.ItemsSource = DB.GetContext().Employees.Where(x => x.IDEmployees != idemp).ToList();
                    gridAddEmployees.Visibility = Visibility.Hidden;

                    tf = true;
                }
                else MessageBox.Show("Пустые поля!", "Информация", MessageBoxButton.OK, MessageBoxImage.Information);

                return tf;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString(), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
                return false;
            }
        }

        public bool Report1(string dateP, string nameA)
        {
            DateTime datetime = DateTime.Now;
            if (dateP != "") datetime = Convert.ToDateTime(dateP);


            bool tf = false;
            var ticket = DB.GetContext().Tickets.ToList();

            if (dateP == "" && nameA != "")
                ticket = DB.GetContext().Tickets.Where(x => x.Attraction.NameAttraction == nameA).ToList();


            if (nameA == "" && dateP != "")
                ticket = DB.GetContext().Tickets.Where(x => x.Date == datetime).ToList();

            if (nameA != "" && dateP != "")
                ticket = DB.GetContext().Tickets.Where(x => x.Date == datetime && x.Attraction.NameAttraction == nameA).ToList();

            if (nameA == "" && dateP == "") ticket = DB.GetContext().Tickets.ToList();

            if (ticket.Count() <= 0)
                MessageBox.Show("Невозможно сформировать отчёт, проверьте дату и аттракцион");

            else
            {
                SaveFileDialog saveFileDialog = new SaveFileDialog();
                saveFileDialog.Title = "Выберите место для сохранения отчёта";
                saveFileDialog.FileName = "Отчет по истории за " + DateTime.Now.ToShortDateString() + " - PDF";
                saveFileDialog.Filter = "PDF Files |*.pdf";

                if (saveFileDialog.ShowDialog() == true)
                {
                    var application = new Word.Application();
                    Word.Document document = application.Documents.Add();

                    Word.Paragraph paragraph = document.Paragraphs.Add();
                    Word.Range cellrange = paragraph.Range;
                    Word.InlineShape inlineShape = cellrange.InlineShapes.AddPicture(@"C:\Users\polin\OneDrive\Рабочий стол\mateo\mateo\image\logocheck.png");
                    inlineShape.Width = 400;
                    inlineShape.Height = 100;
                    paragraph.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter;
                    cellrange.InsertParagraphAfter();

                    Word.Paragraph paragraph2 = document.Paragraphs.Add();
                    Word.Range range2 = paragraph2.Range;
                    range2.Text = "Отчёт по истории";
                    paragraph2.set_Style("Заголовок");
                    paragraph2.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter;
                    range2.Font.Size = 18;
                    range2.InsertParagraphAfter();


                    var group = DB.GetContext().Tickets.GroupBy(x => new { x.Attraction.NameAttraction, x.TypeTickets.TypeTickets1 }).
                                                     Select(x => new
                                                     {
                                                         x.Key,
                                                         Sum = x.Sum(y => y.Summ * y.Quantity),
                                                         NameA = x.Select(y => y.Attraction.NameAttraction),
                                                         TypeA = x.Select(y => y.TypeTickets.TypeTickets1),
                                                         Quantity = x.Sum(y => y.Quantity)
                                                     }).ToList();

                    decimal sum = DB.GetContext().Tickets.Where(x => x.FKStatusPuy == 1).Sum(x => x.Quantity * x.Summ);

                    if (dateP != "" && nameA == "")
                    {
                        group = DB.GetContext().Tickets.Where(x => x.Date == datetime).
                                                          GroupBy(x => new { x.Attraction.NameAttraction, x.TypeTickets.TypeTickets1 }).
                                                          Select(x => new
                                                          {
                                                              x.Key,
                                                              Sum = x.Sum(y => y.Summ * y.Quantity),
                                                              NameA = x.Select(y => y.Attraction.NameAttraction),
                                                              TypeA = x.Select(y => y.TypeTickets.TypeTickets1),
                                                              Quantity = x.Sum(y => y.Quantity)
                                                          }).ToList();
                        sum = DB.GetContext().Tickets.Where(x => x.FKStatusPuy == 1 && x.Date == datetime).Sum(x => x.Quantity * x.Summ);


                    }

                    else if (dateP == "" && nameA != "")
                    {
                        group = DB.GetContext().Tickets.Where(x => x.Attraction.NameAttraction == nameA).
                                                         GroupBy(x => new { x.Attraction.NameAttraction, x.TypeTickets.TypeTickets1 }).
                                                         Select(x => new
                                                         {
                                                             x.Key,
                                                             Sum = x.Sum(y => y.Summ * y.Quantity),
                                                             NameA = x.Select(y => y.Attraction.NameAttraction),
                                                             TypeA = x.Select(y => y.TypeTickets.TypeTickets1),
                                                             Quantity = x.Sum(y => y.Quantity)
                                                         }).ToList();

                        sum = DB.GetContext().Tickets.Where(x => x.FKStatusPuy == 1 && x.Attraction.NameAttraction == nameA).Sum(x => x.Quantity * x.Summ);
                    }

                    else if (dateP != "" && nameA != "")
                    {
                        group = DB.GetContext().Tickets.Where(x => x.Date == datetime && x.Attraction.NameAttraction == nameA).
                                                         GroupBy(x => new { x.Attraction.NameAttraction, x.TypeTickets.TypeTickets1 }).
                                                         Select(x => new
                                                         {
                                                             x.Key,
                                                             Sum = x.Sum(y => y.Summ * y.Quantity),
                                                             NameA = x.Select(y => y.Attraction.NameAttraction),
                                                             TypeA = x.Select(y => y.TypeTickets.TypeTickets1),
                                                             Quantity = x.Sum(y => y.Quantity)
                                                         }).ToList();

                        sum = DB.GetContext().Tickets.Where(x => x.FKStatusPuy == 1 && x.Date == datetime && x.Attraction.NameAttraction == nameA).Sum(x => x.Quantity * x.Summ);
                    }

                    Word.Paragraph tableparagraph = document.Paragraphs.Add();
                    Word.Range tablerange = tableparagraph.Range;
                    Word.Table paymentstable = document.Tables.Add(tablerange, group.Count() + 2, 2);
                    paymentstable.Borders.InsideLineStyle = paymentstable.Borders.OutsideLineStyle = Word.WdLineStyle.wdLineStyleSingle;

                    paymentstable.PreferredWidth = 500;
                    paymentstable.Rows.Alignment = Word.WdRowAlignment.wdAlignRowCenter;

                    Word.Range cellRange;

                    cellRange = paymentstable.Cell(1, 2).Range;
                    cellRange.Text = "Дата формирования: " + DateTime.Now;
                    paymentstable.Cell(1, 2).Range.ParagraphFormat.Alignment = Word.WdParagraphAlignment.wdAlignParagraphRight;
                    range2.Font.Size = 18;

                    for (int i = 0; i < group.Count(); i++)
                    {
                        var orders = group[i];

                        cellRange = paymentstable.Cell(i + 2, 1).Range;
                        cellRange.Text = orders.NameA.FirstOrDefault() + " (" + orders.TypeA.FirstOrDefault() + ")" + " x" + orders.Quantity;
                        cellRange.Font.Size = 12;

                        cellRange = paymentstable.Cell(i + 2, 2).Range;
                        cellRange.Text = String.Format("{0:0.00}", orders.Sum).ToString() + " руб.";
                    }

                    cellRange = paymentstable.Cell(group.Count() + 3, 1).Range;
                    cellRange.Text = "Итого: " + String.Format("{0:0.00}", sum) + " руб.";
                    cellRange.set_Style("Заголовок");
                    cellRange.Bold = 1;
                    paymentstable.Cell(group.Count() + 3, 1).Range.ParagraphFormat.Alignment = Word.WdParagraphAlignment.wdAlignParagraphLeft;
                    cellRange.Font.Size = 18;

                    document.SaveAs2(saveFileDialog.FileName, Word.WdExportFormat.wdExportFormatPDF);
                    MessageBox.Show("Отчёт сформирован", "Информация", MessageBoxButton.OK, MessageBoxImage.Information);

                    tf = true;

                }
            }

            return tf;
        }
        #endregion


        #region Аттракционы

        #region Добавление
        private void BtnAddNewAttractionClick(object sender, RoutedEventArgs e)
        {
            gridAddAttraction.Visibility = Visibility.Visible;
        }

        private void BtnAddImage(object sender, RoutedEventArgs e) //выбираем и выводим изображение в imageAdd_Attraction
        {
            AddImage();
        }

        private void BtnAddAttractionClick(object sender, RoutedEventArgs e) //добавляем новый аттракцион в бд
        {
            AddAttraction(DB.Idemployees, txtName_Attraction.Text, txtDescription_Attraction.Text, txtQuantity_Attraction.Text, txtPriceOld_Attraction.Text, txtPriceBaby_Attraction.Text, image, comboboxStatus_Attraction.Text, comboboxType_Attraction.Text);
        }
        #endregion

        #region Изменение
        private void BtnChangeAttractionClick(object sender, RoutedEventArgs e) //выводим данные из datagrid в grid чтобы потом изменить
        {
            if (datagridAttraction.SelectedItem is Attraction attraction)
            {
                idattraction = attraction.IDAttraction;
                txtChange_NameAttraction.Text = attraction.NameAttraction;
                txtChange_Description.Text = attraction.Description;
                txtChange_Quantity.Text = attraction.QuantityAttraction.ToString();

                decimal price = Convert.ToDecimal(attraction.PriceOld);
                txtChange_PriceOldAttraction.Text = String.Format("{0:0.00}", price);
                price = Convert.ToDecimal(attraction.PriceBaby);
                txtChange_PriceBabyAttraction.Text = String.Format("{0:0.00}", price);

                comboboxChange_StatusAttraction.Text = attraction.StatusAttraction.StatusAttraction1;
                comboboxChange_TypeAttraction.Text = attraction.Type.Type1;

                image = attraction.ImageAttraction;
                MemoryStream ms = new MemoryStream(image);
                imageChange_Attraction.Source = BitmapFrame.Create(ms);
                datagridAttraction.Visibility = Visibility.Hidden;

                gridChangeAttraction.Visibility = Visibility.Visible;
            }
        }

        private void BtnChangeImageClick(object sender, RoutedEventArgs e) //выбираем и выводим изображение в imageChange_Attraction
        {
            string imageLoc;
            OpenFileDialog dld = new OpenFileDialog();
            dld.Filter = "JPG Files (*.jpg)|*.jpg|PNG Files (*.png)|*.png|JPEG Files (*.jpeg)|*.jpeg";
            dld.Title = "Выберите изображение пользователя";
            bool? result = dld.ShowDialog();
            if (result == true)
            {
                imageLoc = dld.FileName;
                imageChange_Attraction.Source = new BitmapImage(new Uri(imageLoc));
                FileStream fs = new FileStream(imageLoc, FileMode.Open, FileAccess.Read);
                BinaryReader br = new BinaryReader(fs);
                image = br.ReadBytes((int)fs.Length);
            }
        }

        private void BtnSaveChangeClick(object sender, RoutedEventArgs e) //вносим изменения из gridChangeAttraction в бд
        {
            if (txtChange_NameAttraction.Text != "" && txtChange_Description.Text != "" && txtChange_Quantity.Text != "" && txtChange_PriceOldAttraction.Text != "" &&
                txtChange_PriceBabyAttraction.Text != "" && imageChange_Attraction.Source != null && comboboxChange_StatusAttraction.Text != "" && comboboxChange_TypeAttraction.Text != "")
            {
                var attr = DB.GetContext().Attraction.Where(x => x.IDAttraction == idattraction).FirstOrDefault();
                var type = DB.GetContext().Type.Where(x => x.Type1 == comboboxChange_TypeAttraction.Text).FirstOrDefault();


                attr.NameAttraction = txtChange_NameAttraction.Text;
                attr.Description = txtChange_Description.Text;
                attr.QuantityAttraction = Convert.ToInt32(txtChange_Quantity.Text);
                attr.PriceOld = Convert.ToDecimal(txtChange_PriceOldAttraction.Text);
                attr.PriceBaby = Convert.ToDecimal(txtChange_PriceBabyAttraction.Text);
                //ищем в таблице StatusAttraction столбец с нужным статусом, затем записываем его id в FKStatusAttraction таблицы Attraction
                var status = DB.GetContext().StatusAttraction.Where(x => x.StatusAttraction1 == comboboxChange_StatusAttraction.Text).FirstOrDefault();
                attr.FKStatusAttraction = status.IDStatusAttraction;

                attr.FKType = type.IDType;

                attr.ImageAttraction = image;
                DB.GetContext().SaveChanges();
                MessageBox.Show("Изменения сохранены", "Информация", MessageBoxButton.OK, MessageBoxImage.Information);

                gridChangeAttraction.Visibility = Visibility.Hidden;
                datagridAttraction.Visibility = Visibility.Visible;
                datagridAttraction.ItemsSource = DB.GetContext().Attraction.ToList();
            }
            else MessageBox.Show("Пустые поля!", "Информация", MessageBoxButton.OK, MessageBoxImage.Information);
        }
        #endregion
        private void BtnBackAttractionClick(object sender, RoutedEventArgs e)
        {
            gridAddAttraction.Visibility = Visibility.Hidden;
            gridChangeAttraction.Visibility = Visibility.Hidden;
            datagridAttraction.Visibility = Visibility.Visible;
        }

        #endregion

        #region Сотрудники

        #region Добавление
        private void BtnAddEmployeesClick(object sender, RoutedEventArgs e) //добавляем нового администратора в бд
        {
            AddEmployees(DB.Idemployees, txtLastName_Employyes.Text, txtFirstName_Employyes.Text, txtMiddleName_Employees.Text, comboboxRole_Employees.Text, comboboxStatus_Employees.Text);
        }

        private void BtnAddNewEmployeesClick(object sender, RoutedEventArgs e)
        {
            gridAddEmployees.Visibility = Visibility.Visible;
        }
        #endregion

        #region Изменение
        private void BtnChangeEmployeesClick(object sender, RoutedEventArgs e) //выводим данные из datagrid в grid чтобы потом изменить
        {
            if (datagridEmployees.SelectedItem is Employees employees)
            {
                idemployees = employees.IDEmployees;
                datagridEmployees.Visibility = Visibility.Hidden;
                gridChangeEmployees.Visibility = Visibility.Visible;
                txtLastName_ChangeEmployyes.Text = employees.LastName;
                txtFirstName_ChangeEmployyes.Text = employees.FirstName;
                txtMiddleName_ChangeEmployees.Text = employees.MiddleName;
                comboboxRole_ChangeEmployees.Text = employees.Role.Role1;
                comboboxStatus_ChangeEmployees.Text = employees.StatusEmployees.StatusEmployees1;
            }
        }
        private void BtnSaveChangeEmployeesClick(object sender, RoutedEventArgs e)
        {
            if (txtLastName_ChangeEmployyes.Text != "" && txtFirstName_ChangeEmployyes.Text != "" &&
                 comboboxRole_ChangeEmployees.Text != "" && comboboxStatus_ChangeEmployees.Text != "")
            {
                //вносим изменения из gridChangeEmployees в бд
                var attr = DB.GetContext().Employees.Where(x => x.IDEmployees == idemployees).FirstOrDefault();

                attr.LastName = txtLastName_ChangeEmployyes.Text;
                attr.FirstName = txtFirstName_ChangeEmployyes.Text;
                attr.MiddleName = txtMiddleName_ChangeEmployees.Text;

                //ищем в таблице StatusEmployees столбец с нужным статусом, затем записываем его id в FKStatusEmployees таблицы Employees
                var status = DB.GetContext().StatusEmployees.Where(x => x.StatusEmployees1 == comboboxStatus_ChangeEmployees.Text).FirstOrDefault();
                attr.FKStatusEmployees = status.IDStatusEmployees;
                var role = DB.GetContext().Role.Where(x => x.Role1 == comboboxRole_ChangeEmployees.Text).FirstOrDefault();
                attr.FKRole = role.IDRole;


                DB.GetContext().SaveChanges();

                MessageBox.Show("Изменения сохранены");

                gridChangeEmployees.Visibility = Visibility.Hidden;
                datagridEmployees.Visibility = Visibility.Visible;
                datagridEmployees.ItemsSource = DB.GetContext().Employees.Where(x => x.IDEmployees != DB.Idemployees).ToList();
            }
            else MessageBox.Show("Пустые поля!");
        }
        #endregion        
        private void BtnBackEmployeesClick(object sender, RoutedEventArgs e)
        {
            gridAddEmployees.Visibility = Visibility.Hidden;
            datagridEmployees.Visibility = Visibility.Visible;
            gridChangeEmployees.Visibility = Visibility.Hidden;
        }
        #endregion

        private void BtnReport1(object sender, RoutedEventArgs e) //отчёт по истории
        {
            try
            {
                string dateP = "";
                string nameA = "";

                if (datepicker.SelectedDate == null) dateP = "";
                else dateP = datepicker.SelectedDate.ToString();

                if (comboboxNameAttraction.SelectedItem == null) nameA = "";
                else nameA = comboboxNameAttraction.SelectedItem.ToString();
                Report1(dateP, nameA);

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString(), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        }

        private void btnLogout(object sender, RoutedEventArgs e)
        {
            First first = new First();
            first.Show();
            this.Close();
        }

        private void BtnExit(object sender, RoutedEventArgs e)
        {
            Application.Current.Shutdown();
        }
    }
}
