namespace ServiceCenter.Data.DapperNet
{
    #region Using
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.Linq;
    
    using Dapper;
    using Dapper.Contrib.Extensions;
    using Dapper.Mapper;
    #endregion

    public class DataService
    {
        #region Свойства
        private static string connectionString;
        #endregion

        #region Конструктор
        static DataService()
        {
            DataService.connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ServiceCenter"].ConnectionString;
        }

        public DataService()
        {
            
        }
        #endregion

        #region Тип устройства
        /// <summary>
        /// Возвращает список всех типов устройств
        /// </summary>
        public List<DeviceType> GetDeviceTypes()
        {
            List<DeviceType> result = null;
            
            using (IDbConnection db = new SqlConnection(DataService.connectionString))
            {
                result = db.GetAll<DeviceType>().ToList();
            }

            return result;
        }

        /// <summary>
        /// Возвращает тип устройства
        /// </summary>
        public DeviceType GetDeviceType(int id)
        {
            string sql = @"SELECT dbo.DeviceType.* FROM dbo.DeviceType WHERE (DeviceTypeId = @id) 
                           SELECT dbo.Request.* FROM dbo.Request WHERE (DeviceTypeId = @id);";

            DeviceType result = null;

            using (IDbConnection db = new SqlConnection(DataService.connectionString))
            {
                using (var multi = db.QueryMultiple(sql, new { id = id }))
                {
                    result = multi.Read<DeviceType>().Single();
                    result.Requests = multi.Read<Request>().ToList();
                }
            }

            return result;
        }

        /// <summary>
        /// Сохранить тип устройства
        /// </summary>
        public void SaveDeviceType(DeviceType deviceType)
        {
            using (IDbConnection db = new SqlConnection(DataService.connectionString))
            {
                if (deviceType.DeviceTypeId == 0)
                    db.Insert<DeviceType>(deviceType);
                else
                    db.Update<DeviceType>(deviceType);
            }
        }

        /// <summary>
        /// Удалить тип устройства
        /// </summary>
        public void DeleteDeviceType(DeviceType deviceType)
        {
            using (IDbConnection db = new SqlConnection(DataService.connectionString))
            {
                db.Delete<DeviceType>(deviceType);
            }
        }
        #endregion

        #region Производитель
        /// <summary>
        /// Возвращает список всех производителей
        /// </summary>
        public List<Manufacturer> GetManufacturers()
        {
            List<Manufacturer> result = null;

            using (IDbConnection db = new SqlConnection(DataService.connectionString))
            {
                result = db.GetAll<Manufacturer>().ToList();
            }

            return result;
        }

        /// <summary>
        /// Возвращает производителя
        /// </summary>
        public Manufacturer GetManufacturer(int id)
        {
            string sqlManufacturer = @"SELECT dbo.Manufacturer.* FROM dbo.Manufacturer WHERE (ManufacturerId = @id);";
            string sqlRequests = @"SELECT dbo.Request.*, dbo.Employee.*, dbo.DeviceType.*, dbo.Status.* 
                                   FROM dbo.Request INNER JOIN 
                                   dbo.Employee ON dbo.Request.EmployeeId = dbo.Employee.EmployeeId INNER JOIN 
                                   dbo.DeviceType ON dbo.Request.DeviceTypeId = dbo.DeviceType.DeviceTypeId INNER JOIN 
                                   dbo.Status ON dbo.Request.StatusId = dbo.Status.StatusId 
                                   WHERE (ManufacturerId = @id);";

            Manufacturer result = null;

            using (IDbConnection db = new SqlConnection(DataService.connectionString))
            {
                result = db.Query<Manufacturer>(sqlManufacturer, new { id = id }).Single();
                result.Requests = db.Query<Request, Employee, DeviceType, Status>(sqlRequests, new { id = id }, splitOn: "RequestId, EmployeeId, DeviceTypeId, StatusId").ToList();
            }

            return result;
        }

        /// <summary>
        /// Сохранить производителя
        /// </summary>
        public void SaveManufacturer(Manufacturer manufacturer)
        {
            using (IDbConnection db = new SqlConnection(DataService.connectionString))
            {
                if (manufacturer.ManufacturerId == 0)
                    db.Insert<Manufacturer>(manufacturer);
                else
                    db.Update<Manufacturer>(manufacturer);
            }
        }

        /// <summary>
        /// Удалить производителя
        /// </summary>
        public void DeleteManufacturer(Manufacturer manufacturer)
        {
            using (IDbConnection db = new SqlConnection(DataService.connectionString))
            {
                db.Delete<Manufacturer>(manufacturer);
            }
        }
        #endregion

        #region Должность
        /// <summary>
        /// Возвращает список всех должностей
        /// </summary>
        public List<Position> GetPositions()
        {
            List<Position> result = null;

            using (IDbConnection db = new SqlConnection(DataService.connectionString))
            {
                result = db.GetAll<Position>().ToList();
            }

            return result;
        }

        /// <summary>
        /// Возвращает должность
        /// </summary>
        public Position GetPosition(int id)
        {
            string sql = @"SELECT dbo.Position.* FROM dbo.Position WHERE (PositionId = @id) 
                           SELECT dbo.Employee.* FROM dbo.Employee WHERE (PositionId = @id);";

            Position result = null;

            using (IDbConnection db = new SqlConnection(DataService.connectionString))
            {
                using (var multi = db.QueryMultiple(sql, new { id = id }))
                {
                    result = multi.Read<Position>().Single();
                    result.Employees = multi.Read<Employee>().ToList();
                }
            }

            return result;
        }

        /// <summary>
        /// Сохранить должность
        /// </summary>
        public void SavePosition(Position position)
        {
            using (IDbConnection db = new SqlConnection(DataService.connectionString))
            {
                if (position.PositionId == 0)
                    db.Insert<Position>(position);
                else
                    db.Update<Position>(position);
            }
        }

        /// <summary>
        /// Удалить должность
        /// </summary>
        public void DeletePosition(Position position)
        {
            using (IDbConnection db = new SqlConnection(DataService.connectionString))
            {
                db.Delete<Position>(position);
            }
        }
        #endregion

        #region Сотрудник
        /// <summary>
        /// Возвращает список всех сотрудников
        /// </summary>
        public List<Employee> GetEmployees()
        {
            string sql = @"SELECT dbo.Employee.*, dbo.Position.* FROM dbo.Employee INNER JOIN dbo.Position ON dbo.Employee.PositionId = dbo.Position.PositionId;";

            List<Employee> result = null;

            using (IDbConnection db = new SqlConnection(DataService.connectionString))
            {
                result = db.Query<Employee, Position>(sql, splitOn: "EmployeeId, PositionId").ToList();
            }

            return result;
        }

        /// <summary>
        /// Возвращает сотрудника
        /// </summary>
        public Employee GetEmployee(int id)
        {
            string sqlEmployee = @"SELECT dbo.Employee.*, dbo.Position.* FROM dbo.Employee INNER JOIN dbo.Position ON dbo.Employee.PositionId = dbo.Position.PositionId WHERE (EmployeeId = @id);";
            string sqlRequests = @"SELECT dbo.Request.*, dbo.DeviceType.*, dbo.Manufacturer.*, dbo.Status.* 
                                   FROM dbo.Request INNER JOIN 
                                   dbo.DeviceType ON dbo.Request.DeviceTypeId = dbo.DeviceType.DeviceTypeId INNER JOIN 
                                   dbo.Manufacturer ON dbo.Request.ManufacturerId = dbo.Manufacturer.ManufacturerId INNER JOIN 
                                   dbo.Status ON dbo.Request.StatusId = dbo.Status.StatusId 
                                   WHERE (EmployeeId = @id);";

            Employee result = null;

            using (IDbConnection db = new SqlConnection(DataService.connectionString))
            {
                result = db.Query<Employee, Position>(sqlEmployee, new { id = id }, splitOn: "EmployeeId, PositionId").Single();
                result.Requests = db.Query<Request, DeviceType, Manufacturer, Status>(sqlRequests, new { id = id }, splitOn: "RequestId, DeviceTypeId, ManufacturerId, StatusId").ToList();
            }

            return result;
        }

        /// <summary>
        /// Сохранить сотрудника
        /// </summary>
        public void SaveEmployee(Employee employee)
        {
            using (IDbConnection db = new SqlConnection(DataService.connectionString))
            {
                if (employee.EmployeeId == 0)
                    db.Insert<Employee>(employee);
                else
                    db.Update<Employee>(employee);
            }
        }

        /// <summary>
        /// Удалить сотрудника
        /// </summary>
        public void DeleteEmployee(Employee employee)
        {
            using (IDbConnection db = new SqlConnection(DataService.connectionString))
            {
                db.Delete<Employee>(employee);
            }
        }
        #endregion

        #region Состояние заявки
        /// <summary>
        /// Возвращает список всех состояний заявки
        /// </summary>
        public List<Status> GetStatuses()
        {
            List<Status> result = null;

            using (IDbConnection db = new SqlConnection(DataService.connectionString))
            {
                result = db.GetAll<Status>().ToList();
            }

            return result;
        }

        /// <summary>
        /// Возвращает состояние заявки
        /// </summary>
        public Status GetStatus(int id)
        {
            string sqlStatus = @"SELECT dbo.Status.* FROM dbo.Status WHERE (StatusId = @id);";
            string sqlRequests = @"SELECT dbo.Request.*, dbo.Employee.*, dbo.DeviceType.*, dbo.Manufacturer.* 
                                   FROM dbo.Request INNER JOIN 
                                   dbo.Employee ON dbo.Request.EmployeeId = dbo.Employee.EmployeeId INNER JOIN 
                                   dbo.DeviceType ON dbo.Request.DeviceTypeId = dbo.DeviceType.DeviceTypeId INNER JOIN 
                                   dbo.Manufacturer ON dbo.Request.ManufacturerId = dbo.Manufacturer.ManufacturerId 
                                   WHERE (StatusId = @id);";

            Status result = null;

            using (IDbConnection db = new SqlConnection(DataService.connectionString))
            {
                result = db.Query<Status>(sqlStatus, new { id = id }).Single();
                result.Requests = db.Query<Request, Employee, DeviceType, Manufacturer>(sqlRequests, new { id = id }, splitOn: "RequestId, EmployeeId, DeviceTypeId, ManufacturerId").ToList();
            }

            return result;
        }

        /// <summary>
        /// Сохранить состояние заявки
        /// </summary>
        public void SaveStatus(Status status)
        {
            using (IDbConnection db = new SqlConnection(DataService.connectionString))
            {
                if (status.StatusId == 0)
                    db.Insert<Status>(status);
                else
                    db.Update<Status>(status);
            }
        }

        /// <summary>
        /// Удалить состояние заявки
        /// </summary>
        public void DeleteStatus(Status status)
        {
            using (IDbConnection db = new SqlConnection(DataService.connectionString))
            {
                db.Delete<Status>(status);
            }
        }
        #endregion

        #region Заявка
        /// <summary>
        /// Возвращает список всех заявок
        /// </summary>
        public List<Request> GetRequests()
        {
            string sql = @"SELECT dbo.Request.*, dbo.Employee.*, dbo.DeviceType.*, dbo.Manufacturer.*, dbo.Status.* 
                           FROM dbo.Request INNER JOIN 
                           dbo.Employee ON dbo.Request.EmployeeId = dbo.Employee.EmployeeId INNER JOIN 
                           dbo.DeviceType ON dbo.Request.DeviceTypeId = dbo.DeviceType.DeviceTypeId INNER JOIN 
                           dbo.Manufacturer ON dbo.Request.ManufacturerId = dbo.Manufacturer.ManufacturerId INNER JOIN 
                           dbo.Status ON dbo.Request.StatusId = dbo.Status.StatusId;";

            List<Request> result = null;

            using (IDbConnection db = new SqlConnection(DataService.connectionString))
            {
                result = db.Query<Request, Employee, DeviceType, Manufacturer, Status>(sql, splitOn: "RequestId, EmployeeId, DeviceTypeId, ManufacturerId, StatusId").ToList();
            }

            return result;
        }

        /// <summary>
        /// Возвращает заявку
        /// </summary>
        public Request GetRequest(int id)
        {
            string sql = @"SELECT dbo.Request.*, dbo.Employee.*, dbo.DeviceType.*, dbo.Manufacturer.*, dbo.Status.* 
                           FROM dbo.Request INNER JOIN 
                           dbo.Employee ON dbo.Request.EmployeeId = dbo.Employee.EmployeeId INNER JOIN 
                           dbo.DeviceType ON dbo.Request.DeviceTypeId = dbo.DeviceType.DeviceTypeId INNER JOIN 
                           dbo.Manufacturer ON dbo.Request.ManufacturerId = dbo.Manufacturer.ManufacturerId INNER JOIN 
                           dbo.Status ON dbo.Request.StatusId = dbo.Status.StatusId 
                           WHERE (dbo.Request.RequestId = @id);";

            Request result = null;

            using (IDbConnection db = new SqlConnection(DataService.connectionString))
            {
                result = db.Query<Request, Employee, DeviceType, Manufacturer, Status>(sql, new { id = id }, splitOn: "RequestId, EmployeeId, DeviceTypeId, ManufacturerId, StatusId").Single();
            }

            return result;
        }

        /// <summary>
        /// Сохранить заявку
        /// </summary>
        public void SaveRequest(Request request)
        {
            using (IDbConnection db = new SqlConnection(DataService.connectionString))
            {
                if (request.RequestId == 0)
                    db.Insert<Request>(request);
                else
                    db.Update<Request>(request);
            }
        }

        /// <summary>
        /// Удалить заявку
        /// </summary>
        public void DeleteRequest(Request request)
        {
            using (IDbConnection db = new SqlConnection(DataService.connectionString))
            {
                db.Delete<Request>(request);
            }
        }
        #endregion
    }
}
