using Microsoft.EntityFrameworkCore.Query.Internal;
using Microsoft.IdentityModel.Tokens;
using rgbc_sds.dal.DB;
using rgbc_sds.services.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net.Http;
using System.Text;
using System.Threading.Tasks;

namespace rgbc_sds.services.Services
{
    public class ShipmentService
    {
        #region [ Variables ]

        private readonly rgbc_sdsContext _context;
        //rgbc_sdsContext _context = new rgbc_sdsContext();

        #endregion [ Variables ]

        #region [ Constructor ]

        public ShipmentService(rgbc_sdsContext context)
        { 
            _context = context;
        }

        #endregion [ Constructor ]

        public ShipmentModel GetShipmentList()
        {
            // setup returning model
            var model = new ShipmentModel();

            // get the list of shipments
            // by default only show "SAVED" shipments
            var shipmentList = from s in _context.sdsShipment
                               join supp in _context.sdsSupplier on s.supplier_id equals supp.id
                               //where s.shipment_status == "SAVED"
                               select new
                               {
                                   Id = s.id,
                                   IndentNumber = s.indent_number,
                                   SupplierName = supp.name,
                                   ShipshapeNumber = !s.shipshape_number.IsNullOrEmpty() ? s.shipshape_number : "N/A",
                                   MotherVessel = !s.mother_vessel.IsNullOrEmpty() ? s.mother_vessel : "N/A",
                                   ETA = s.eta != null ? s.eta.Value.ToString("yyyy/MM/dd") : "N/A",
                                   BillOfEntryNumber = !s.bill_of_entry_number.IsNullOrEmpty() ? s.bill_of_entry_number : "N/A",
                                   ContainerNumber = !s.container_number.IsNullOrEmpty() ? s.container_number : "N/A",
                                   PortId = s.port_id,
                                   ContainerReceived = s.container_received != null ? s.container_received.Value.ToString("yyyy/MM/dd") : "N/A",
                                   NoOfFlatcases = !s.no_of_flatcases.IsNullOrEmpty() ? s.no_of_flatcases : "N/A",
                                   Voyage = !s.voyage.IsNullOrEmpty() ? s.voyage : "N/A",
                                   MBLNumber = !s.mbl_number.IsNullOrEmpty() ? s.mbl_number : "N/A",
                                   RedirectToSACD = s.redirect_to_SACD != null ? s.redirect_to_SACD.Value.ToString("yyyy/MM/dd") : "N/A",
                                   RequestedBy = !s.created_by.IsNullOrEmpty() ? s.created_by : "N/A",
                                   Date = s.created_at != null ? s.created_at.Value.ToString("yyyy/MM/dd") : "N/A",
                                   Status = s.shipment_status,
                                   CancelReason = !s.cancel_reason.IsNullOrEmpty() ? s.cancel_reason : "N/A",
                                   EditedBy = !s.updated_by.IsNullOrEmpty() ? s.updated_by : s.created_by,
                                   LastEdit = s.updated_at != null ? s.updated_at.Value.ToString("yyyy/MM/dd HH:mm") : s.created_at.Value.ToString("yyyy/MM/dd HH:mm")
                               };

            // populate shipment list model
            foreach (var shipment in shipmentList)
            {
                model.ShipmentListModel.Add(new ShipmentListItemModel
                {
                    Id = shipment.Id,
                    IndentNumber = shipment.IndentNumber,
                    SupplierName = shipment.SupplierName,
                    ShipshapeNumber = shipment.ShipshapeNumber,
                    MotherVessel = shipment.MotherVessel,
                    ETA = shipment.ETA,
                    BillOfEntryNumber = shipment.BillOfEntryNumber,
                    ContainerNumber = shipment.ContainerNumber,
                    PortId = shipment.PortId,
                    ContainerReceived = shipment.ContainerReceived,
                    NoOfFlatcases = shipment.NoOfFlatcases,
                    Voyage = shipment.Voyage,
                    MBLNumber = shipment.MBLNumber,
                    RedirectToSACD = shipment.RedirectToSACD,
                    RequestedBy = shipment.RequestedBy,
                    Date = shipment.Date,
                    Status = shipment.Status,
                    CancelReason = shipment.CancelReason,
                    EditedBy = shipment.EditedBy,
                    LastEdit = shipment.LastEdit
                });
            }

            // return the model
            return model;
        }

        public sdsShipment GetShipment(int id)
        {
            // check if shipment exists
            var existingShipment = _context.sdsShipment.Where(shipment => shipment.id == id).FirstOrDefault();

            if (existingShipment != null) { return existingShipment; }

            // check if shipment can be submitted

            return new sdsShipment();
        }

        public bool GetShipmentByIndentNumber(string indent_number)
        {
            var shipment = _context.sdsShipment.Where(x => x.indent_number == indent_number).FirstOrDefault();

            if (shipment != null) { return true; }

            return false;
        }

        public ShipmentModel FilterShipmentList(string searchString, string status)
        {
            // setup returning model
            var model = new ShipmentModel();
            var filterStatus = "";

            switch (status)
            {
                case "1":
                    filterStatus = "SAVED";
                    break;
                case "2":
                    filterStatus = "SUBMITTED";
                    break;
                case "3":
                    filterStatus = "CANCELLED";
                    break;
            }

            // get the list of shipments
            if (!searchString.IsNullOrEmpty() && !filterStatus.IsNullOrEmpty())
            {
                // get the list of shipments
                var shipmentList = from s in _context.sdsShipment
                                   join supp in _context.sdsSupplier on s.supplier_id equals supp.id
                                   where s.indent_number.Contains(searchString) && s.shipment_status == filterStatus
                                   select new
                                   {
                                       Id = s.id,
                                       IndentNumber = s.indent_number,
                                       SupplierName = supp.name,
                                       ShipshapeNumber = !s.shipshape_number.IsNullOrEmpty() ? s.shipshape_number : "N/A",
                                       MotherVessel = !s.mother_vessel.IsNullOrEmpty() ? s.mother_vessel : "N/A",
                                       ETA = s.eta != null ? s.eta.Value.ToString("yyyy/MM/dd") : "N/A",
                                       BillOfEntryNumber = !s.bill_of_entry_number.IsNullOrEmpty() ? s.bill_of_entry_number : "N/A",
                                       ContainerNumber = !s.container_number.IsNullOrEmpty() ? s.container_number : "N/A",
                                       PortId = s.port_id,
                                       ContainerReceived = s.container_received != null ? s.container_received.Value.ToString("yyyy/MM/dd") : "N/A",
                                       NoOfFlatcases = !s.no_of_flatcases.IsNullOrEmpty() ? s.no_of_flatcases : "N/A",
                                       Voyage = !s.voyage.IsNullOrEmpty() ? s.voyage : "N/A",
                                       MBLNumber = !s.mbl_number.IsNullOrEmpty() ? s.mbl_number : "N/A",
                                       RedirectToSACD = s.redirect_to_SACD != null ? s.redirect_to_SACD.Value.ToString("yyyy/MM/dd") : "N/A",
                                       RequestedBy = !s.created_by.IsNullOrEmpty() ? s.created_by : "N/A",
                                       Date = s.created_at != null ? s.created_at.Value.ToString("yyyy/MM/dd") : "N/A",
                                       Status = s.shipment_status,
                                       CancelReason = !s.cancel_reason.IsNullOrEmpty() ? s.cancel_reason : "N/A",
                                       EditedBy = !s.updated_by.IsNullOrEmpty() ? s.updated_by : s.created_by,
                                       LastEdit = s.updated_at != null ? s.updated_at.Value.ToString("yyyy/MM/dd HH:mm") : s.created_at.Value.ToString("yyyy/MM/dd HH:mm")
                                   };

                // populate shipment list model
                foreach (var shipment in shipmentList)
                {
                    model.ShipmentListModel.Add(new ShipmentListItemModel
                    {
                        Id = shipment.Id,
                        IndentNumber = shipment.IndentNumber,
                        SupplierName = shipment.SupplierName,
                        RequestedBy = shipment.RequestedBy,
                        Date = shipment.Date,
                        Status = shipment.Status,
                        CancelReason = shipment.CancelReason,
                        EditedBy = shipment.EditedBy,
                        LastEdit = shipment.LastEdit
                    });
                }
            }
            else if (!searchString.IsNullOrEmpty())
            {
                // get the list of shipments
                var shipmentList = from s in _context.sdsShipment
                                   join supp in _context.sdsSupplier on s.supplier_id equals supp.id
                                   where s.indent_number.Contains(searchString)
                                   select new
                                   {
                                       Id = s.id,
                                       IndentNumber = s.indent_number,
                                       SupplierName = supp.name,
                                       ShipshapeNumber = !s.shipshape_number.IsNullOrEmpty() ? s.shipshape_number : "N/A",
                                       MotherVessel = !s.mother_vessel.IsNullOrEmpty() ? s.mother_vessel : "N/A",
                                       ETA = s.eta != null ? s.eta.Value.ToString("yyyy/MM/dd") : "N/A",
                                       BillOfEntryNumber = !s.bill_of_entry_number.IsNullOrEmpty() ? s.bill_of_entry_number : "N/A",
                                       ContainerNumber = !s.container_number.IsNullOrEmpty() ? s.container_number : "N/A",
                                       PortId = s.port_id,
                                       ContainerReceived = s.container_received != null ? s.container_received.Value.ToString("yyyy/MM/dd") : "N/A",
                                       NoOfFlatcases = !s.no_of_flatcases.IsNullOrEmpty() ? s.no_of_flatcases : "N/A",
                                       Voyage = !s.voyage.IsNullOrEmpty() ? s.voyage : "N/A",
                                       MBLNumber = !s.mbl_number.IsNullOrEmpty() ? s.mbl_number : "N/A",
                                       RedirectToSACD = s.redirect_to_SACD != null ? s.redirect_to_SACD.Value.ToString("yyyy/MM/dd") : "N/A",
                                       RequestedBy = !s.created_by.IsNullOrEmpty() ? s.created_by : "N/A",
                                       Date = s.created_at != null ? s.created_at.Value.ToString("yyyy/MM/dd") : "N/A",
                                       Status = s.shipment_status,
                                       CancelReason = !s.cancel_reason.IsNullOrEmpty() ? s.cancel_reason : "N/A",
                                       EditedBy = !s.updated_by.IsNullOrEmpty() ? s.updated_by : s.created_by,
                                       LastEdit = s.updated_at != null ? s.updated_at.Value.ToString("yyyy/MM/dd HH:mm") : s.created_at.Value.ToString("yyyy/MM/dd HH:mm")
                                   };

                // populate shipment list model
                foreach (var shipment in shipmentList)
                {
                    model.ShipmentListModel.Add(new ShipmentListItemModel
                    {
                        Id = shipment.Id,
                        IndentNumber = shipment.IndentNumber,
                        SupplierName = shipment.SupplierName,
                        ShipshapeNumber = shipment.ShipshapeNumber,
                        MotherVessel = shipment.MotherVessel,
                        ETA = shipment.ETA,
                        BillOfEntryNumber = shipment.BillOfEntryNumber,
                        ContainerNumber = shipment.ContainerNumber,
                        PortId = shipment.PortId,
                        ContainerReceived = shipment.ContainerReceived,
                        NoOfFlatcases = shipment.NoOfFlatcases,
                        Voyage = shipment.Voyage,
                        MBLNumber = shipment.MBLNumber,
                        RedirectToSACD = shipment.RedirectToSACD,
                        RequestedBy = shipment.RequestedBy,
                        Date = shipment.Date,
                        Status = shipment.Status,
                        CancelReason = shipment.CancelReason,
                        EditedBy = shipment.EditedBy,
                        LastEdit = shipment.LastEdit
                    });
                }
            }
            else if (!filterStatus.IsNullOrEmpty())
            {
                // get the list of shipments
                var shipmentList = from s in _context.sdsShipment
                                   join supp in _context.sdsSupplier on s.supplier_id equals supp.id
                                   where s.shipment_status == filterStatus
                                   select new
                                   {
                                       Id = s.id,
                                       IndentNumber = s.indent_number,
                                       SupplierName = supp.name,
                                       ShipshapeNumber = !s.shipshape_number.IsNullOrEmpty() ? s.shipshape_number : "N/A",
                                       MotherVessel = !s.mother_vessel.IsNullOrEmpty() ? s.mother_vessel : "N/A",
                                       ETA = s.eta != null ? s.eta.Value.ToString("yyyy/MM/dd") : "N/A",
                                       BillOfEntryNumber = !s.bill_of_entry_number.IsNullOrEmpty() ? s.bill_of_entry_number : "N/A",
                                       ContainerNumber = !s.container_number.IsNullOrEmpty() ? s.container_number : "N/A",
                                       PortId = s.port_id,
                                       ContainerReceived = s.container_received != null ? s.container_received.Value.ToString("yyyy/MM/dd") : "N/A",
                                       NoOfFlatcases = !s.no_of_flatcases.IsNullOrEmpty() ? s.no_of_flatcases : "N/A",
                                       Voyage = !s.voyage.IsNullOrEmpty() ? s.voyage : "N/A",
                                       MBLNumber = !s.mbl_number.IsNullOrEmpty() ? s.mbl_number : "N/A",
                                       RedirectToSACD = s.redirect_to_SACD != null ? s.redirect_to_SACD.Value.ToString("yyyy/MM/dd") : "N/A",
                                       RequestedBy = !s.created_by.IsNullOrEmpty() ? s.created_by : "N/A",
                                       Date = s.created_at != null ? s.created_at.Value.ToString("yyyy/MM/dd") : "N/A",
                                       Status = s.shipment_status,
                                       CancelReason = !s.cancel_reason.IsNullOrEmpty() ? s.cancel_reason : "N/A",
                                       EditedBy = !s.updated_by.IsNullOrEmpty() ? s.updated_by : s.created_by,
                                       LastEdit = s.updated_at != null ? s.updated_at.Value.ToString("yyyy/MM/dd HH:mm") : s.created_at.Value.ToString("yyyy/MM/dd HH:mm")
                                   };

                // populate shipment list model
                foreach (var shipment in shipmentList)
                {
                    model.ShipmentListModel.Add(new ShipmentListItemModel
                    {
                        Id = shipment.Id,
                        IndentNumber = shipment.IndentNumber,
                        SupplierName = shipment.SupplierName,
                        RequestedBy = shipment.RequestedBy,
                        Date = shipment.Date,
                        Status = shipment.Status,
                        CancelReason = shipment.CancelReason,
                        EditedBy = shipment.EditedBy,
                        LastEdit = shipment.LastEdit
                    });
                }
            }
            else
            {
                // get the list of shipments
                var shipmentList = from s in _context.sdsShipment
                                   join supp in _context.sdsSupplier on s.supplier_id equals supp.id
                                   select new
                                   {
                                       Id = s.id,
                                       IndentNumber = s.indent_number,
                                       SupplierName = supp.name,
                                       ShipshapeNumber = !s.shipshape_number.IsNullOrEmpty() ? s.shipshape_number : "N/A",
                                       MotherVessel = !s.mother_vessel.IsNullOrEmpty() ? s.mother_vessel : "N/A",
                                       ETA = s.eta != null ? s.eta.Value.ToString("yyyy/MM/dd") : "N/A",
                                       BillOfEntryNumber = !s.bill_of_entry_number.IsNullOrEmpty() ? s.bill_of_entry_number : "N/A",
                                       ContainerNumber = !s.container_number.IsNullOrEmpty() ? s.container_number : "N/A",
                                       PortId = s.port_id,
                                       ContainerReceived = s.container_received != null ? s.container_received.Value.ToString("yyyy/MM/dd") : "N/A",
                                       NoOfFlatcases = !s.no_of_flatcases.IsNullOrEmpty() ? s.no_of_flatcases : "N/A",
                                       Voyage = !s.voyage.IsNullOrEmpty() ? s.voyage : "N/A",
                                       MBLNumber = !s.mbl_number.IsNullOrEmpty() ? s.mbl_number : "N/A",
                                       RedirectToSACD = s.redirect_to_SACD != null ? s.redirect_to_SACD.Value.ToString("yyyy/MM/dd") : "N/A",
                                       RequestedBy = !s.created_by.IsNullOrEmpty() ? s.created_by : "N/A",
                                       Date = s.created_at != null ? s.created_at.Value.ToString("yyyy/MM/dd") : "N/A",
                                       Status = s.shipment_status,
                                       CancelReason = !s.cancel_reason.IsNullOrEmpty() ? s.cancel_reason : "N/A",
                                       EditedBy = !s.updated_by.IsNullOrEmpty() ? s.updated_by : s.created_by,
                                       LastEdit = s.updated_at != null ? s.updated_at.Value.ToString("yyyy/MM/dd HH:mm") : s.created_at.Value.ToString("yyyy/MM/dd HH:mm")
                                   };

                // populate shipment list model
                foreach (var shipment in shipmentList)
                {
                    model.ShipmentListModel.Add(new ShipmentListItemModel
                    {
                        Id = shipment.Id,
                        IndentNumber = shipment.IndentNumber,
                        SupplierName = shipment.SupplierName,
                        ShipshapeNumber = shipment.ShipshapeNumber,
                        MotherVessel = shipment.MotherVessel,
                        ETA = shipment.ETA,
                        BillOfEntryNumber = shipment.BillOfEntryNumber,
                        ContainerNumber = shipment.ContainerNumber,
                        PortId = shipment.PortId,
                        ContainerReceived = shipment.ContainerReceived,
                        NoOfFlatcases = shipment.NoOfFlatcases,
                        Voyage = shipment.Voyage,
                        MBLNumber = shipment.MBLNumber,
                        RedirectToSACD = shipment.RedirectToSACD,
                        RequestedBy = shipment.RequestedBy,
                        Date = shipment.Date,
                        Status = shipment.Status,
                        CancelReason = shipment.CancelReason,
                        EditedBy = shipment.EditedBy,
                        LastEdit = shipment.LastEdit
                    });
                }
            }

            // return the model
            return model;
        }

        public sdsShipment AddShipment(sdsShipment model)
        {
            try
            {
                _context.sdsShipment.Add(model);
                _context.SaveChanges();

                return model;
            }
            catch (Exception ex)
            {
                return model;
            }
        }

        public async Task<string> UpdateShipmentAsync(sdsShipment shipment)
        {
            try
            {
                _context.sdsShipment.Update(shipment);
                await  _context.SaveChangesAsync();

                return "success";
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        public void SubmitShipment(ShipmentModel model)
        {
            // get the shipment to update
            var existingShipment = _context.sdsShipment.Where(x => x.id == model.Id).FirstOrDefault();

            if (existingShipment != null)
            {
                existingShipment.shipment_status = "COMPLETED";
                existingShipment.updated_by = model.UpdatedBy;
                existingShipment.updated_at = DateTime.Now;

                _context.sdsShipment.Update(existingShipment);
                _context.SaveChanges();
            }
        }

        public void CancelShipment(ShipmentModel model)
        {
            // get the shipment to update
            var existingShipment = _context.sdsShipment.Where(x => x.id == model.Id).FirstOrDefault();

            if (existingShipment != null)
            {
                existingShipment.shipment_status = "CANCELLED";
                existingShipment.cancel_reason = model.CancelReason;

                existingShipment.updated_by = model.UpdatedBy;
                existingShipment.updated_at = DateTime.Now;

                _context.sdsShipment.Update(existingShipment);
                _context.SaveChanges();
            }
        }

        public bool CheckShipmentSubmitStatus(sdsShipment shipment)
        {
            // check file name fields to ensure all are captured
            if (
                !shipment.clamping_request_file.IsNullOrEmpty() &&
                !shipment.supplier_invoice_file.IsNullOrEmpty() &&
                !shipment.bill_of_lading_file.IsNullOrEmpty() &&
                (!shipment.arrival_invoice_file.IsNullOrEmpty() || !shipment.arrival_invoice_file2.IsNullOrEmpty()) &&
                !shipment.certificate_of_origin_file.IsNullOrEmpty() &&
                !shipment.packing_list_file.IsNullOrEmpty() &&
                !shipment.dro_to_transport_file.IsNullOrEmpty() &&
                !shipment.certificate_of_removal_file.IsNullOrEmpty() &&
                !shipment.extended_detention_file.IsNullOrEmpty() &&
                !shipment.voc_file.IsNullOrEmpty() &&
                !shipment.dept_of_health_inspection_file.IsNullOrEmpty() &&
                !shipment.sars_documents_file.IsNullOrEmpty() &&
                !shipment.grv_complete_RGBC_file.IsNullOrEmpty() &&
                !shipment.grv_edward_snell_file.IsNullOrEmpty() &&
                !shipment.accpac_receipts_file.IsNullOrEmpty()
                )
            {
                return true;
            }
            else
            {
                return false;
            }
        }
    }
}