using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Internal;
using Siger.Middlelayer.Common;
using Siger.Middlelayer.Common.Extensions;
using Siger.Middlelayer.Common.FieldEnum;
using Siger.Middlelayer.Common.ModuleEnum;
using Siger.Middlelayer.EsopRepository.Entities;
using Siger.Middlelayer.EsopRepository.Repositories.Interface;
using Siger.Middlelayer.EsopRepository.Request;
using Siger.Middlelayer.EsopRepository.Response;
using Siger.Middlelayer.Repository.Extensions;
using Siger.Middlelayer.Repository.Paged;

namespace Siger.Middlelayer.EsopRepository.Repositories
{
    internal class EsopDocumentBindingRepository : EsopRepositoryBase<siger_project_esop_document_binding>, IEsopDocumentBindingRepository
    {
        private readonly ApiEsopDbContext _context;
        public EsopDocumentBindingRepository(ApiEsopDbContext context) : base(context)
        {
            _context = context;
        }

        public IPagedCollectionResult<ResponseGetDocumentBinding> GetDocumentBindingList(int product_id, int level_id, int page, int pagesize, int projectId)
        {
            var responses = new List<ResponseGetDocumentBinding>();

            var routes = _context.siger_project_product_route.Where(q => q.productId == product_id && q.projectId == projectId
                                          && q.levelId == level_id && q.status == (int) RowState.Valid);
            if (!routes.Any())
            {
                return new PagedCollectionResult<ResponseGetDocumentBinding>(responses, 0);
            }

            var totalCount = routes.Count();
            var entities = routes.Skip((page - 1) * pagesize).Take(pagesize).AsNoTracking().ToList();

            foreach (var route in entities)
            {
                var levelSection = _context.siger_project_machine_attribution.FirstOrDefault(q => q.station == route.sectionId && q.status == (int)RowState.Valid);
                if (levelSection == null)
                {
                    continue;
                }
               
                var machineEntity = _context.siger_project_machine.FirstOrDefault(q => q.id == levelSection.machine && q.category == (int)MachineCategory.NC);
                if (machineEntity == null)
                {
                    continue;
                }
                var binding = _context.siger_project_esop_document_binding.Where(q => q.route_id == route.id && q.machine_id == levelSection.machine);
                if (!binding.Any())
                {
                    var response = new ResponseGetDocumentBinding
                    {
                        id = route.id,
                        product_id = route.productId,
                        serial_number = route.serialNumber,
                        level_id = route.levelId,
                        machine_id = machineEntity.id,
                        machine_name = machineEntity.title,
                        route_name = route.name,
                    };
                    responses.Add(response);
                }
                else
                {
                    var response = new ResponseGetDocumentBinding
                    {
                        id = route.id,
                        product_id = route.productId,
                        serial_number = route.serialNumber,
                        level_id = route.levelId,
                        machine_id = machineEntity.id,
                        machine_name = machineEntity.title,
                        route_name = route.name,
                    };

                    var query = from q in binding
                        join d in _context.siger_project_esop_document on q.document_id equals d.id
                        where q.document_id == d.id && q.status == (int) RowState.Valid && q.document_status != DocumentSignStatus.Saved
                              && q.document_status != DocumentSignStatus.HasRecover
                        select new
                        {
                            id = q.id,
                            name = d.name,
                            document_id = q.document_id,
                        };

                    foreach (var documentBinding in query.ToList())
                    {
                        response.binded_document.Add(new ResponseGetDocument
                        {
                            id = documentBinding.id,
                            document_id = documentBinding.document_id,
                            name = documentBinding.name
                        });
                    }
                    responses.Add(response);
                }
            }

            return new PagedCollectionResult<ResponseGetDocumentBinding>(responses, totalCount);
        }

        public IPagedCollectionResult<GetMachinePagedBinding> GetMachinePagedBinding(int project_id, IEnumerable<int> machineIds, int page, int pagesize)
        {
            if (machineIds.Any())
            {
                var query = from q in _context.siger_project_esop_document_binding
                    join d in _context.siger_project_esop_document on q.document_id equals d.id
                    join h in _context.siger_project_esop_document_history on d.id equals h.document_id
                    join t in _context.siger_project_esop_document_set on d.type_id equals t.id
                    join u in _context.siger_project_user on h.updator_mid equals u.mid
                    where q.project_id == project_id && machineIds.Contains(q.machine_id) && h.is_valid == 1
                          && q.document_status != DocumentSignStatus.Saved
                          && q.document_status != DocumentSignStatus.HasRecover
                    select new GetMachinePagedBinding
                    {
                        id = q.id,
                        name = d.name,
                        remark = d.remark,
                        version = "V" + h.version,
                        approval_name = "",
                        is_sign = t.sign_usergroup_id == 0 ? 1 : (q.sign_mid > 0 ? 1: 0),
                        type_name = t.name,
                        upload_time = h.update_time.ToString("yyyy-MM-dd"),
                        upload_user = u.name
                    };

                var totalCount = query.Count();
                var entities = query.Skip((page - 1) * pagesize).Take(pagesize).AsNoTracking().ToList();
                return new PagedCollectionResult<GetMachinePagedBinding>(entities, totalCount);
            }
            return new PagedCollectionResult<GetMachinePagedBinding>(new List<GetMachinePagedBinding>(), 0);
        }

        public IPagedCollectionResult<ResponseGetRouteBinding> GetRouteDocumentBindingList(string starttime, string endtime, 
            int product_id, int level_id, int projectId, int page, int pagesize)
        {
            var responses = new List<ResponseGetRouteBinding>();

            var routes = _context.siger_project_product_route.Where(q => q.productId == product_id && q.projectId == projectId
                                          && q.levelId == level_id && q.status == (int)RowState.Valid);
            if (!routes.Any())
            {
                return new PagedCollectionResult<ResponseGetRouteBinding>(responses, 0);
            }

            var query = from q in _context.siger_project_esop_document_binding
                join d in _context.siger_project_esop_document on q.document_id equals d.id
                join h in _context.siger_project_esop_document_history on q.id equals h.document_id
                join t in _context.siger_project_esop_document_set on d.type_id equals t.id
                where q.project_id == projectId && h.is_valid == 1
                select new ResponseGetRouteBinding
                {
                    id = q.id,
                    document_name = d.name,
                    version = "V" + h.version,
                    document_typename = t.name,
                    file_url = h.file_url,
                    binding_time = q.create_time.ToString("yyyy-MM-dd HH:mm:ss"),
                    machine_name = q.machine_id.ToStr(),
                };

            var totalCount = query.Count();
            var entities = query.Skip((page - 1) * pagesize).Take(pagesize).AsNoTracking().ToList();
            return new PagedCollectionResult<ResponseGetRouteBinding>(entities, totalCount);
        }

        public IEnumerable<int> GetBindIds(int product_id, int level_id, int projectId)
        {
            var routes = _context.siger_project_product_route.Where(q => q.productId == product_id && q.projectId == projectId
                                                                                                   && q.levelId == level_id && q.status == (int)RowState.Valid);
            if (!routes.Any())
            {
                return new List<int>();
            }

            var routeIds = routes.ToList().Select(q => q.id).ToList();

            var bindIds = _context.siger_project_esop_document_binding.Where(q =>
                    routeIds.Contains(q.route_id) && q.project_id == projectId && q.status == (int) RowState.Valid)
                .Select(m => m.id).ToList();

            return bindIds;
        }

        public bool AddBindDetail(RequestUpdateDocumentStatus request, int projectId, int userId)
        {
            try
            {
                foreach (var update in request.documents)
                {
                    foreach (var documentid in update.documentids)
                    {
                        //看是否需要签收
                        var status = DocumentSignStatus.Signed;
                        var type = (from q in _context.siger_project_esop_document
                            join t in _context.siger_project_esop_document_set on q.type_id equals t.id
                            where q.id == documentid && q.status == (int) RowState.Valid && q.project_id == projectId
                            select t).FirstOrDefault();
                        if (type != null && type.sign_usergroup_id > 0)
                        {
                            status = DocumentSignStatus.Binding;
                        }
                        var detail = new siger_project_esop_document_binding
                        {
                            create_time = DateTime.Now,
                            creator_mid = userId,
                            document_id = documentid,
                            project_id = projectId,
                            remark = request.remark,
                            route_id = update.route_id,
                            machine_id = update.machine_id,
                            document_status = status
                        };
                        _context.siger_project_esop_document_binding.Add(detail);
                    }
                }
                _context.SaveChanges();

                return true;
            }
            catch 
            {
                return false;
            }
        }

        public IPagedCollectionResult<ResponseGetDocumentSignList> GetDocumentSignList(IEnumerable<int> machineIds, int status, int projectId, int userId,int page, int pagesize)
        {//d.product_id
            var query = from q in _context.siger_project_esop_document_binding
                        join d in _context.siger_project_esop_document on q.document_id equals d.id
                        join t in _context.siger_project_esop_document_set on d.type_id equals t.id
                        join h in _context.siger_project_esop_document_history on d.id equals h.document_id
                        join m in _context.siger_project_machine on q.machine_id equals m.id
                        join sm in _context.siger_project_machine_attribution on m.id equals sm.machine
                        join l in _context.siger_project_level_section on sm.station equals l.id
                        join r in _context.siger_project_product_route on q.route_id equals r.id into rr
                        from rr2 in rr.DefaultIfEmpty()
                        join p in _context.siger_project_product on rr2.productId equals p.id into pp
                        from pp2 in pp.DefaultIfEmpty()
                        join u in _context.siger_project_user on q.sign_mid equals u.mid into uu
                        from uu1 in uu.DefaultIfEmpty()
                        join u2 in _context.siger_project_user on userId equals u2.mid into uu2
                        from uu3 in uu2.DefaultIfEmpty()
                        where q.project_id == projectId && q.status == (int)RowState.Valid && h.is_valid == 1
                                                && (q.document_status == DocumentSignStatus.Signed || q.document_status == DocumentSignStatus.Binding)
                              && m.category == (int)MachineCategory.NC && sm.status==(int)RowState.Valid
                        select new ResponseGetDocumentSignList
                        {
                            id = q.id,
                            machine_id = q.machine_id,
                            version = "V" + h.version,
                            status = Convert.ToInt32(q.document_status),
                            name = d.name,
                            remark = d.remark,
                            section_id = l.id,
                            section_name = l.title,
                            sign_username = uu1 != null ? uu1.name : "",
                            route_id = q.route_id,
                            product_id = pp2 != null ? pp2.id : 0,
                            product_name = pp2 != null ? pp2.name : "",
                            product_drawcode = pp2 != null ? pp2.drawingcode : "",
                            binding_remark = q.remark,
                            sign_groupid = t.sign_usergroup_id,
                            user_groupid = uu3 != null ? uu3.usergroupid : "",
                        };
            Expression<Func<ResponseGetDocumentSignList, bool>> sectionExpression = q => true;
            if (machineIds.Any())
            {
                sectionExpression = q => machineIds.Contains(q.machine_id);
            }
            else
            {
                sectionExpression = q => false;
            }
            Expression<Func<ResponseGetDocumentSignList, bool>> statusExpression = q => true;
            if (status > 0)
            {
                statusExpression = q => q.status == status;
            }

            var predicate = sectionExpression.And(statusExpression);
            var entities = query.Where(predicate).AsNoTracking().ToList();
            var responses = new List<ResponseGetDocumentSignList>();

            //创建人或者审核人
            foreach (var entity in entities)
            {
                if (entity.sign_groupid > 0 && entity.sign_groupid == entity.user_groupid.ToInt())
                {
                    responses.Add(entity);
                }
            }

            var totalCount = responses.Count;
            var result = responses.Skip((page - 1) * pagesize).Take(pagesize).ToList();
            return new PagedCollectionResult<ResponseGetDocumentSignList>(result, totalCount);
        }

        public IPagedCollectionResult<ResponseSearchDocumentBinding> SearchDocumentBinding(string starttime, string endtime, int product_id, int level_id,
            int page, int pagesize, int projectId)
        {
            
            var query = from q in _context.siger_project_esop_document_binding
                join d in _context.siger_project_esop_document on q.document_id equals d.id
                join h in _context.siger_project_esop_document_history on d.id equals h.document_id
                join t in _context.siger_project_esop_document_set on d.type_id equals t.id
                join m in _context.siger_project_machine on q.machine_id equals m.id
                join sm in _context.siger_project_machine_attribution on m.id equals sm.machine
                join l in _context.siger_project_level_section on sm.station equals l.id
                where q.project_id == projectId && q.status == (int)RowState.Valid && h.is_valid == 1 && m.category == (int)MachineCategory.NC
                select new ResponseSearchDocumentBinding
                {
                    id = q.id,
                    document_id = d.id,
                    document_name = d.name,
                    version = "V" + h.version,
                    type_name = t.name,
                    file_name = h.file_name,
                    file_url = h.file_url,
                    create_time = q.create_time,
                    downtime = q.create_time.ToString("yyyy-MM-dd HH:mm:ss"),
                    machine_name = m.title,
                    machine_id = m.id,
                    signtime = q.sign_time == DateTime.MinValue ? "" : q.sign_time.ToString("yyyy-MM-dd HH:mm:ss"),
                    recovertime = q.recover_time == DateTime.MinValue ? "" : q.recover_time.ToString("yyyy-MM-dd HH:mm:ss"),
                    status = (int)q.document_status,
                    section_name = l.title,
                    route_id = q.route_id
                };

            Expression<Func<ResponseSearchDocumentBinding, bool>> startExpression = q => true;
            if (!string.IsNullOrWhiteSpace(starttime))
            {
                startExpression = q => q.create_time >= DateTime.Parse(starttime);
            }
            Expression<Func<ResponseSearchDocumentBinding, bool>> endExpression = q => true;
            if (!string.IsNullOrWhiteSpace(endtime))
            {
                endExpression = q => q.create_time <= DateTime.Parse(endtime);
            }
            Expression<Func<ResponseSearchDocumentBinding, bool>> routeExpression = q => true;
            if (product_id > 0 && level_id > 0)
            {
                var routes = _context.siger_project_product_route.Where(q => q.productId == product_id && q.projectId == projectId
                                                                              && q.levelId == level_id && q.status == (int)RowState.Valid);
                var routeIds = routes.Select(m => m.id).ToList();

                routeExpression = q => routeIds.Contains(q.route_id);
            }

            var pre = startExpression.And(endExpression).And(routeExpression);

            var totalCount = query.Count(pre);
            var entities = query.Where(pre).Skip((page - 1) * pagesize).Take(pagesize).AsNoTracking().ToList();
            return new PagedCollectionResult<ResponseSearchDocumentBinding>(entities, totalCount);
        }

        public IPagedCollectionResult<ResponseSearchDocumentBinding> SearchDocumentBindingByMachine(string starttime, string endtime, IEnumerable<int> machineIds, string keyword,
            int page, int pagesize, int projectId)
        {
            var query = from q in _context.siger_project_esop_document_binding
                        join d in _context.siger_project_esop_document on q.document_id equals d.id
                        join h in _context.siger_project_esop_document_history on d.id equals h.document_id
                        join t in _context.siger_project_esop_document_set on d.type_id equals t.id
                        join m in _context.siger_project_machine on q.machine_id equals m.id
                        join sm in _context.siger_project_machine_attribution on m.id equals sm.machine
                        join l in _context.siger_project_level_section on sm.station equals l.id
                        where q.project_id == projectId && machineIds.Contains(q.machine_id) && q.status == (int)RowState.Valid 
                              && h.is_valid == 1 && m.category == (int)MachineCategory.NC && sm.status== (int)RowState.Valid
                        orderby q.create_time descending
                        select new ResponseSearchDocumentBinding
                        {
                            id = q.id,
                            document_id = d.id,
                            document_name = d.name,
                            version = "V" + h.version,
                            type_name = t.name,
                            file_name = h.file_name,
                            file_url = h.file_url,
                            create_time = q.create_time,
                            downtime = q.create_time.ToString("yyyy-MM-dd HH:mm:ss"),
                            machine_name = m.title,
                            machine_id = m.id,
                            signtime = q.sign_time == DateTime.MinValue ? "" : q.sign_time.ToString("yyyy-MM-dd HH:mm:ss"),
                            recovertime = q.recover_time == DateTime.MinValue ? "" : q.recover_time.ToString("yyyy-MM-dd HH:mm:ss"),
                            status = (int)q.document_status,
                            section_name = l.title,
                            route_id = q.route_id
                        };
 
            Expression<Func<ResponseSearchDocumentBinding, bool>> startExpression = q => true;
            if (!string.IsNullOrWhiteSpace(starttime))
            {
                startExpression = q => q.create_time >= DateTime.Parse(starttime);
            }
            Expression<Func<ResponseSearchDocumentBinding, bool>> endExpression = q => true;
            if (!string.IsNullOrWhiteSpace(endtime))
            {
                endExpression = q => q.create_time <= DateTime.Parse(endtime);
            }
            
            Expression<Func<ResponseSearchDocumentBinding, bool>> keywordExpression = q => true;
            if (!string.IsNullOrWhiteSpace(keyword))
            {
                keywordExpression = q => (q.type_name.Contains(keyword) || q.document_name.Contains(keyword));
            }

            var pre = startExpression.And(endExpression).And(keywordExpression);

            var totalCount = query.Count(pre);
            var entities = query.Where(pre).Skip((page - 1) * pagesize).Take(pagesize).AsNoTracking().ToList();
            return new PagedCollectionResult<ResponseSearchDocumentBinding>(entities, totalCount);
        }

        public IEnumerable<ResponseShowDocumentBinding> ShowDocumentBindings(IEnumerable<int> machineIds, int type_id, string document_name, int projectId)
        {
            var query = from q in _context.siger_project_esop_document_binding
                        join d in _context.siger_project_esop_document on q.document_id equals d.id
                        join h in _context.siger_project_esop_document_history on d.id equals h.document_id
                        join t in _context.siger_project_esop_document_set on d.type_id equals t.id
                        join r in _context.siger_project_product_route on q.route_id equals r.id into rr
                        from r1 in rr.DefaultIfEmpty()
                        join p in _context.siger_project_product on d.product_id equals p.id into pp
                        from p1 in pp.DefaultIfEmpty()
                        where q.project_id == projectId && q.status == (int)RowState.Valid && h.is_valid == 1
                                                && (t.sign_usergroup_id == 0 || q.document_status == DocumentSignStatus.Signed)
                        select new ResponseShowDocumentBinding
                        {
                            version = "V" + h.version,
                            name = d.name,
                            product_name = p1 != null ? p1.name : "",
                            type_name = t.name,
                            type_id = t.id,
                            machine_id = q.machine_id,
                            document_id = d.id,
                            file_name = h.file_name,
                            file_url = h.file_url
                        };
            Expression<Func<ResponseShowDocumentBinding, bool>> sectionExpression = q => true;
            if (machineIds.Any())
            {
                sectionExpression = q => machineIds.Contains(q.machine_id);
            }
            else
            {
                sectionExpression = q => false;
            }
            Expression<Func<ResponseShowDocumentBinding, bool>> typeidExpression = q => true;
            if (type_id > 0)
            {
                typeidExpression = q => q.type_id == type_id;
            }
            Expression<Func<ResponseShowDocumentBinding, bool>> nameExpression = q => true;
            if (!string.IsNullOrWhiteSpace(document_name))
            {
                nameExpression = q => q.name.Contains(document_name);
            }

            var pre = sectionExpression.And(typeidExpression).And(nameExpression);
            return query.Where(pre).AsNoTracking().ToList();
        }
    }
}

