using SimpleScan.Model;
using SimpleScan.Model.Context;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.Entity;
using System.Data.SQLite;
using System.Diagnostics;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;


namespace SimpleScan.SQL
{
    class SqliteUtils : DbContext
    {
        private string database = "test1.sqlite";

        public SqliteUtils()
        {
            CreateDbEntity();
        }

        private void CreateDbEntity()
        {
            if (!System.IO.File.Exists(database))
            {
                using (var ctx = new BaseContext())
                {
                    var users = new List<Operator>();
                    users.Add(new Operator() { Login = "admin", Admin = true, Password = "6898C672C1A6B96ACF58C12F2B3DEF3E359239ADA08BA17ECFB76F1315D8C4DE", Remove = false });
                    users.Add(new Operator() { Login = "a", Admin = true, Password = "CA978112CA1BBDCAFAC231B39A23DC4DA786EFF8147C4E72B9807785AFEE48BB", Remove = false });
                    ctx.Operators.AddRange(users);

                    var maint = new List<EventMaintenance>();
                    maint.Add(new EventMaintenance() { FromTime = new DateTime(1995, 3, 25, 11, 45, 23), ToTime = new DateTime(1975, 1, 25, 11, 45, 22), ScopeOfWork = "hahha", OperatorId = 1 });
                    maint.Add(new EventMaintenance() { FromTime = DateTime.Now, ToTime = DateTime.Now, ScopeOfWork = "hahha", OperatorId = 1 });

                    ctx.SaveChanges();
                    ctx.Maintenances.AddRange(maint);

                    Operat op = new Operat() { Iemz = "P.1234.5678.90", OperatFilesCount = 23 };
                    op.Box = new Box() { Name = "Skrzynia 1", OperatCount = 2, OperatScanFilesCount = 200 };
                    op.Box.Order = new OrderTbl() { Client = "Zlecenie 1" };
                    try
                    {
                        ctx.Operats.Add(op);
                        ctx.SaveChanges();
                    }
                    catch (Exception e)
                    {
                        Debug.WriteLine(e.ToString());
                        MessageBox.Show(e.ToString());
                    }


                    op = new Operat() { Iemz = "P.1234.6785.99", OperatFilesCount = 23, BoxId = 1 };
                    ctx.Operats.Add(op);
                    ctx.SaveChanges();

                    op = new Operat() { Iemz = "P.5678.1234.123", OperatFilesCount = 23 };
                    op.Box = new Box() { Name = "Skrzynia 2", OperatCount = 2, OperatScanFilesCount = 200, OrderId = 1 };
                    ctx.Operats.Add(op);
                    ctx.SaveChanges();

                    op = new Operat() { Iemz = "P.5678.4321.321", OperatFilesCount = 23, BoxId = 2 };
                    ctx.Operats.Add(op);
                    ctx.SaveChanges();

                    op = new Operat() { Iemz = "P.7890.1234.543", OperatFilesCount = 23 };
                    op.Box = new Box() { Name = "Skrzynia 3", OperatCount = 2, OperatScanFilesCount = 200 };
                    op.Box.Order = new OrderTbl() { Client = "Zlecenie 2" };
                    ctx.Operats.Add(op);
                    ctx.SaveChanges();

                    op = new Operat() { Iemz = "P.7890.4321.345", OperatFilesCount = 23, BoxId = 3 };
                    ctx.Operats.Add(op);
                    ctx.SaveChanges();

                    OperatDetail opd = new OperatDetail()
                    {
                        DirectoryPath = @"C:\Digitalizacja\asd",
                        FilePath = @"C:\Digitalizacja\asd\pod0001.tif",
                        OperatId = 1,
                        PageNumber = 1,
                        Remove = false,
                        ScanTime = DateTime.Now
                    };

                    ctx.OperatDetails.Add(opd);

                    ctx.SaveChanges();

                    List<Operator> data = ctx.Operators.ToList();
                    foreach (Operator item in data)
                        Console.WriteLine(string.Format("ID : {0} Login : {1}", item.Id, item.Login));
                }
            }
        }

        public Operator ValidatePassword(string login)
        {
            using (var ctx = new BaseContext())
            {
                return ctx.Operators.Where(user => user.Login == login && user.Remove == false).First();
            }
        }





        public List<string> GetOrder()
        {
            List<string> order = new List<string>();
            using (var ctx = new BaseContext())
            {
                order.AddRange(ctx.OrderTbls.Select(p => p.Client));
            }
            return order;
        }

        public List<string> GetBox(string order)
        {
            List<string> box = new List<string>();
            using (var ctx = new BaseContext())
            {
                box.AddRange(ctx.Boxes.Where(p => p.Order.Client == order).Select(p => p.Name));
            }
            return box;
        }

        public List<string> GetOperats(string box)
        {
            List<string> operats = new List<string>();
            using (var ctx = new BaseContext())
            {
                operats.AddRange(ctx.Operats.Where(p => p.Box.Name == box).Select(p => p.Iemz));
            }
            return operats;
        }


    }
}