using AMESCoreStudio.CommonTools.Result;
using AMESCoreStudio.WebApi.DTO.AMES;
using AMESCoreStudio.WebApi.Extensions;
using AMESCoreStudio.WebApi.Models.AMES;
using Dapper;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;

namespace AMESCoreStudio.WebApi.Controllers.AMES
{
    /// <summary>
    /// 鋼板基本資料檔
    /// </summary>
    [Route("api/[controller]")]
    [ApiController]
    public class SteelPlateInfoController : ControllerBase
    {
        private readonly AMESContext _context;

        public SteelPlateInfoController(AMESContext context)
        {
            _context = context;
        }

        // GET: api/SteelPlateInfo
        [HttpGet]
        public async Task<ActionResult<IEnumerable<SteelPlateInfo>>> GetSteelPlateInfos()
        {
            return await _context.SteelPlateInfos.ToListAsync();
        }

        // GET: api/SteelPlateInfo/5
        [HttpGet("{id}")]
        public async Task<ActionResult<SteelPlateInfo>> GetSteelPlateInfo(int id)
        {
            var steelPlateInfo = await _context.SteelPlateInfos.FindAsync(id);

            if (steelPlateInfo == null)
            {
                return NotFound();
            }

            return steelPlateInfo;
        }

        /// <summary>
        /// By 鋼板編號 查詢
        /// </summary>
        /// <param name="id">鋼板編號</param>
        /// <returns></returns>
        [HttpGet("ByNo/{id}")]
        public async Task<ActionResult<IEnumerable<SteelPlateInfo>>> GetSteelPlateInfoByNo(string id)
        {

            var steelPlateInfos = await _context.SteelPlateInfos.Where(w => w.SteelPlateNo == id.ToUpper())
                                         .ToListAsync();

            return steelPlateInfos;
        }

        /// <summary>
        /// 鋼板資料查詢
        /// </summary>
        /// <param name="steelPlateNo">鋼板編號</param>
        /// <param name="storageLocation">儲存位置</param>
        /// <param name="item">料號</param>
        /// <param name="status">狀態</param>
        /// <param name="date_str">到廠日_起</param>
        /// <param name="date_end">到廠日_迄</param>
        /// <param name="checkOff">鋼板未下線狀態 Y/N</param>
        /// <param name="page">頁數</param>
        /// <param name="limit">筆數</param>
        /// <returns></returns>
        [HttpGet("SteelPlateInfoQuery")]
        public async Task<ResultModel<SteelPlateInfoDto>> GetSteelPlateInfoQuery(string steelPlateNo, string storageLocation, string item,
            string status, string date_str, string date_end, string checkOff, int page = 0, int limit = 10)
        {

            // 需要查詢鋼板未下線資料
            var SteelPlateIDs = new List<int>();
            if (checkOff == "Y")
            {
                SteelPlateIDs = _context.SteelPlateMeasures.Where(w => w.OffDate == null &&
                                                                            w.OnDate != null)
                                                           .Select(s => s.SteelPlateID)
                                                           .Distinct().ToList();
            }

            var q = await _context.SteelPlateInfos.ToListAsync();

            if (SteelPlateIDs.Count() != 0)
            {
                q = q.Where(w => SteelPlateIDs.Contains(w.SteelPlateID)).ToList();
            }

            if (!string.IsNullOrWhiteSpace(steelPlateNo))
            {
                q = q.Where(w => w.SteelPlateNo.Contains(steelPlateNo.Trim().ToUpper())).ToList();
            }

            if (!string.IsNullOrWhiteSpace(item))
            {
                q = q.Where(w => w.Items.Contains(item.Trim().ToUpper())).ToList();
            }

            if (!string.IsNullOrWhiteSpace(storageLocation))
            {
                q = q.Where(w => w.StorageLocation == storageLocation.Trim().ToUpper()).ToList();
            }

            if (!string.IsNullOrWhiteSpace(status))
            {
                q = q.Where(w => w.Status == status).ToList();

            }

            DateTime dateTime = DateTime.Now;
            if (DateTime.TryParse(date_str, out dateTime))
            {
                q = q.Where(w => w.ArrivalDate >= DateTime.Parse(date_str)).ToList();
            }

            if (DateTime.TryParse(date_end, out dateTime))
            {
                q = q.Where(w => w.ArrivalDate <= DateTime.Parse(date_end)).ToList();
            }

            ResultModel<SteelPlateInfoDto> result = new ResultModel<SteelPlateInfoDto>();

            // Table 頁數
            if (page > 0)
            {
                q = q.Skip((page - 1) * limit).Take(limit).ToList();
            }

            // 紀錄筆數
            result.DataTotal = q.Count();

            result.Data = q.Select(s => new SteelPlateInfoDto
            {
                SteelPlateID = s.SteelPlateID,
                SteelPlateNo = s.SteelPlateNo,
                ArrivalDate = s.ArrivalDate,
                Items = s.Items,
                StorageLocation = s.StorageLocation,
                Boards = s.Boards,
                UsedTimes = s.UsedTimes,
                BeUseTimes = s.BeUseTimes,
                TotalTimes = s.TotalTimes,
                StatusName = s.Status == "0" ? "失效" : "有效",
                Remark = s.Remark
            }).ToList();
            return result;
        }

        /// <summary>
        /// 鋼板資料查詢 Report
        /// </summary>
        /// <param name="steelPlateNo">鋼板編號</param>
        /// <param name="storageLocation">儲存位置</param>
        /// <param name="item">料號</param>
        /// <param name="wipNo">工單號碼</param>
        /// <param name="date_str">到廠日_起</param>
        /// <param name="date_end">到廠日_迄</param>
        /// <param name="page">頁數</param>
        /// <param name="limit">筆數</param>
        /// <returns></returns>
        [HttpGet("SteelPlateInfoReport")]
        public ResultModel<PCB016ViewDto> GetSteelPlateInfoReport(string steelPlateNo, string storageLocation, string item, string wipNo
          , string date_str, string date_end, int page = 0, int limit = 10)
        {

            var q = from q1 in _context.SteelPlateInfos
                    join q2 in _context.SteelPlateMeasures on q1.SteelPlateID equals q2.SteelPlateID into s
                    from q2 in s.DefaultIfEmpty()
                    join q3 in _context.UserInfoes on q2.OnUserID equals q3.UserID into s1
                    from q3 in s1.DefaultIfEmpty()
                    join q4 in _context.UserInfoes on q2.OffUserID equals q4.UserID into s2
                    from q4 in s2.DefaultIfEmpty()
                    join q5 in _context.UserInfoes on q1.CreateUserID equals q5.UserID into s3
                    from q5 in s3.DefaultIfEmpty()
                    select new PCB016ViewDto
                    {
                        SteelPlateID = q1.SteelPlateID,
                        SteelPlateNo = q1.SteelPlateNo,
                        Items = q1.Items,
                        StorageLocation = q1.StorageLocation,
                        Boards = q1.Boards,
                        CreateUserName = q3.UserName,
                        Remark = q1.Remark,
                        ArrivalDate = q1.ArrivalDate,
                        BeUseTimes = q1.BeUseTimes,
                        UsedTimes = q1.UsedTimes,
                        WipNo = q2.WipNo,
                        OnTension1 = q2.OnTension1,
                        OnTension2 = q2.OnTension2,
                        OnTension3 = q2.OnTension3,
                        OnTension4 = q2.OnTension4,
                        OnTension5 = q2.OnTension5,
                        OnUserName = q4.UserName,
                        OnDate = q2.OnDate,
                        OffTension1 = q2.OffTension1,
                        OffTension2 = q2.OffTension2,
                        OffTension3 = q2.OffTension3,
                        OffTension4 = q2.OffTension4,
                        OffTension5 = q2.OffTension5,
                        OffUserName = q5.UserName,
                        OffDate = q2.OffDate,
                    };



            if (!string.IsNullOrWhiteSpace(steelPlateNo))
            {
                q = q.Where(w => w.SteelPlateNo.Contains(steelPlateNo.Trim().ToUpper()));
            }

            if (!string.IsNullOrWhiteSpace(item))
            {
                q = q.Where(w => w.Items.Contains(item.Trim().ToUpper()));
            }

            if (!string.IsNullOrWhiteSpace(storageLocation))
            {
                q = q.Where(w => w.StorageLocation == storageLocation.Trim().ToUpper());
            }

            if (!string.IsNullOrWhiteSpace(wipNo))
            {
                q = q.Where(w => w.WipNo.Contains(wipNo.Trim().ToUpper()));
            }

            DateTime dateTime = DateTime.Now;
            if (DateTime.TryParse(date_str, out dateTime))
            {
                q = q.Where(w => w.ArrivalDate >= DateTime.Parse(date_str));
            }

            if (DateTime.TryParse(date_end, out dateTime))
            {
                q = q.Where(w => w.ArrivalDate <= DateTime.Parse(date_end));
            }

            ResultModel<PCB016ViewDto> result = new ResultModel<PCB016ViewDto>();

            // Table 頁數
            if (page > 0)
            {
                q = q.Skip((page - 1) * limit).Take(limit);
            }

            // 紀錄筆數
            result.DataTotal = q.Count();

            result.Data = q.ToList();
            return result;
        }

        /// <summary>
        /// 鋼板資料查詢 Report (未使用)
        /// </summary>
        /// <param name="steelPlateNo">鋼板編號</param>
        /// <param name="storageLocation">儲存位置</param>
        /// <param name="item">料號</param>
        /// <param name="wipNo">工單號碼</param>
        /// <param name="date_str">到廠日_起</param>
        /// <param name="date_end">到廠日_迄</param>
        /// <param name="page">頁數</param>
        /// <param name="limit">筆數</param>
        /// <returns></returns>
        [HttpGet("SteelPlateInfoReportByUnused")]
        public async Task<ResultModel<PCB016ViewDto>> GetSteelPlateInfoReportByUnused(string steelPlateNo, string storageLocation, string item, string wipNo
          , string date_str, string date_end, int page = 0, int limit = 10)
        {
            var query = @$" SELECT I.STEEL_PLATE_ID AS SteelPlateID ,
                                I.STEEL_PLATE_NO AS SteelPlateNo ,
                                I.ITEMS AS Items ,
                                I.BOARDS AS Boards ,
                                I.STORAGE_LOCATION AS StorageLocation ,
                                I.BE_USE_TIMES AS BeUseTimes ,
                                I.USED_TIMES AS UsedTimes ,
                                I.ARRIVAL_DATE AS ArrivalDate
                                FROM JHAMES.STEEL_PLATE_INFO I
                                WHERE NOT EXISTS (
                                  SELECT 1
                                  FROM JHAMES.STEEL_PLATE_MEASURE M
                                  WHERE I.STEEL_PLATE_ID = M.STEEL_PLATE_ID
                                )
                                AND NOT EXISTS (
                                  SELECT 1
                                  FROM JHAMES.STEEL_PLATE_MEASURE_NG G
                                  WHERE I.STEEL_PLATE_ID = G.STEEL_PLATE_ID
                                ) ";


            DynamicParameters p = new DynamicParameters();
            var q = await _context.Database.DapperQueryAsync<PCB016ViewDto>(query, p);

            if (!string.IsNullOrWhiteSpace(steelPlateNo))
            {
                q = q.Where(w => w.SteelPlateNo.Contains(steelPlateNo.Trim().ToUpper()));
            }

            if (!string.IsNullOrWhiteSpace(item))
            {
                q = q.Where(w => w.Items.Contains(item.Trim().ToUpper()));
            }

            if (!string.IsNullOrWhiteSpace(storageLocation))
            {
                q = q.Where(w => w.StorageLocation == storageLocation.Trim().ToUpper());
            }

            DateTime dateTime = DateTime.Now;
            if (DateTime.TryParse(date_str, out dateTime))
            {
                q = q.Where(w => w.ArrivalDate >= DateTime.Parse(date_str));
            }

            if (DateTime.TryParse(date_end, out dateTime))
            {
                q = q.Where(w => w.ArrivalDate <= DateTime.Parse(date_end));
            }

            ResultModel<PCB016ViewDto> result = new ResultModel<PCB016ViewDto>();

            // Table 頁數
            if (page > 0)
            {
                q = q.Skip((page - 1) * limit).Take(limit);
            }

            // 紀錄筆數
            result.DataTotal = q.Count();
            result.Data = q.ToList();
            return result;
        }

        /// <summary>
        /// 更新鋼板基本資料檔
        /// </summary>
        /// <param name="steelPlateInfo"></param>
        /// <returns></returns>
        [HttpPut]
        public async Task<ResultModel<SteelPlateInfo>> PutSteelPlateInfo(SteelPlateInfo steelPlateInfo)
        {
            ResultModel<SteelPlateInfo> result = new ResultModel<SteelPlateInfo>();
            _context.Entry(steelPlateInfo).State = EntityState.Modified;
            _context.Entry<SteelPlateInfo>(steelPlateInfo).Property("CreateDate").IsModified = false;
            _context.Entry<SteelPlateInfo>(steelPlateInfo).Property("CreateUserID").IsModified = false;
            try
            {
                await _context.SaveChangesAsync();
                result.Success = true;
                result.Msg = "OK";
            }
            catch (Exception ex)
            {
                result.Success = false;
                result.Msg = ex.InnerException.Message;
            }
            return result;
        }

        /// <summary>
        /// 更新鋼板狀態
        /// </summary>
        /// <param name="steelPlateInfo">data</param>
        /// <returns></returns>
        [HttpPut("ByStatus")]
        public ResultModel<SteelPlateInfo> PutSteelPlateInfoByStatus(SteelPlateInfo steelPlateInfo)
        {
            ResultModel<SteelPlateInfo> result = new ResultModel<SteelPlateInfo>();

            var query = @" UPDATE JHAMES.STEEL_PLATE_INFO SET STATUS = :status ,
                           UPDATE_USERID = :upuser ,
                           UPDATE_DATE = sysdate
                           WHERE STEEL_PLATE_ID = :id ";

            DynamicParameters p = new DynamicParameters();
            p.Add("id", steelPlateInfo.SteelPlateID, DbType.Int32);
            p.Add("status", steelPlateInfo.Status);
            p.Add("upuser", steelPlateInfo.UpdateUserID, DbType.Int32);
            try
            {
                _context.Database.DapperExecute(query, p);
                result.Success = true;
                result.Msg = "OK";
            }
            catch (Exception ex)
            {
                result.Success = false;
                result.Msg = ex.InnerException.Message;
            }
            return result;
        }

        /// <summary>
        /// 新增鋼板基本資料檔
        /// </summary>
        /// <param name="steelPlateInfo"></param>
        /// <returns></returns>
        [HttpPost]
        public async Task<ResultModel<SteelPlateInfo>> PostSteelPlateInfo(SteelPlateInfo steelPlateInfo)
        {
            ResultModel<SteelPlateInfo> result = new ResultModel<SteelPlateInfo>();

            if (await _context.SteelPlateInfos.Where(w =>
                      w.SteelPlateNo == steelPlateInfo.SteelPlateNo).AnyAsync())
            {
                result.Success = false;
                result.Msg = "鋼板編號有重複";
                return result;
            }

            Helper helper = new Helper(_context);
            steelPlateInfo.SteelPlateID = helper.GetIDKey("STEEL_PLATE_ID").Result;
            _context.SteelPlateInfos.Add(steelPlateInfo);
            try
            {
                await _context.SaveChangesAsync();
                result.Success = true;
                result.Msg = "OK";
            }
            catch (Exception ex)
            {
                result.Success = false;
                result.Msg = ex.InnerException.Message;
            }
            return result;
        }

        // DELETE: api/SteelPlateInfo/5
        [HttpDelete("{id}")]
        public async Task<ActionResult<SteelPlateInfo>> DeleteSteelPlateInfo(int id)
        {
            var steelPlateInfo = await _context.SteelPlateInfos.FindAsync(id);
            if (steelPlateInfo == null)
            {
                return NotFound();
            }

            _context.SteelPlateInfos.Remove(steelPlateInfo);
            await _context.SaveChangesAsync();

            return steelPlateInfo;
        }

        private bool SteelPlateInfoExists(int id)
        {
            return _context.SteelPlateInfos.Any(e => e.SteelPlateID == id);
        }
    }
}