using MailQuery.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using MailQuery.Filter;
using System.Xml.Linq;
using System.Web.Helpers;

namespace MailQuery.Controllers
{


    [AllowedIP]
    public class queryController : ApiController
    {

        private static string datePattern = "=([0-9]{4})[-/]([1-9]|[0][1-9]|[1][0-2])[-/]([1-9]|0[1-9]|1[0-9]|2[0-9]|3[0-1])=";
        private System.Text.RegularExpressions.Regex regexdate = new System.Text.RegularExpressions.Regex(datePattern);//,System.Text.RegularExpressions.RegexOptions.Compiled);
        private static string inputPattern = "={0}=";  //19999/12/32  testing 
        private static string replaceEsc = "=";
        private static string[] nothingString = new string[] { };
        private static CsmsResult[] nothingCSMSResult = new CsmsResult[] { };

        private static CsmsResult[] toofewCSMSResult = new CsmsResult[] { new CsmsResult { codedesc = "條件過少" } };
        private static string msgnoQueryPattern = "{0}%"; //len must>6

        private static string powernoQueryPattern = "{0}%"; //len must>5
        public class HelpPageApiModel
        {
            public string ResponseDocumentation { get; set; }

            // existing code
        }
       
        private string[] getmatchDateString(string sDate)
        {
            if (regexdate.IsMatch(string.Format(inputPattern,sDate)) ) {

                return regexdate.Match(string.Format(inputPattern, sDate)).Value
                    .Replace(replaceEsc , "")
                    .Split("/-".ToCharArray()) ;
            }
            return nothingString;
        }
        private bool checkAcceptAPI(queryCond cond)
        {
            if(cond is null) return false;
            string szClient = cond.client;
            if(szClient == null) return false;

            bool bPass = false;
            
            using (developEntities entities = new developEntities())
            {
                var allowSource =( from lst601 in entities.SysEnvVar
                                  where lst601.CodeID == 601 
                                  select lst601.CodeValue).ToList();
                if (allowSource.Count==0) { bPass = false; }
                else
                {
                    List<string> lstCode = new List<string>();
                    foreach (string code in allowSource)
                    {
                        lstCode.AddRange(code.Split(new char[] { ',' }));
                    }
                    lstCode.RemoveAll(x => x == "");
                    if(!String.IsNullOrEmpty( szClient))
                    {
                      
                        if( lstCode.Contains(szClient))
                            bPass = true; //accept
                    }
                }
            }

            return bPass;
        }
     
        
        [AllowedIP]
        public IEnumerable<CsmsResult> query(queryCond cond)
        {
            if (!checkAcceptAPI(cond)) return nothingCSMSResult;

            int check_condsize = 0;
            
            List<CsmsResult> results = new List<CsmsResult>();
            
            using (developEntities entities = new developEntities())
            {
                //string entitySQL = "select MsgNo, Email,MailorName ,MailorTel,UnitName,CateName FROM FeaturesResult  ";
                string entitySQL = "select * FROM FeaturesResult   ";
                List < System.Data.SqlClient.SqlParameter> queryParam = new List<System.Data.SqlClient.SqlParameter>();

                List<string> condarray = new List<string>();
                if (cond.email != null) { check_condsize++; condarray.Add(" Email = @email"); queryParam.Add(new System.Data.SqlClient.SqlParameter("@email", cond.email )); }
                if (cond.mailorname != null) { check_condsize++; condarray.Add(" MailorName = @mailorname "); queryParam.Add(new System.Data.SqlClient.SqlParameter("@mailorname", cond.mailorname)); }
                if (cond.mailortel != null) {  string tel = cond.mailortel.Replace(" ", "").Replace("%", ""); if (tel.Length >= 6) { tel = "%" + tel + "%"; check_condsize++; condarray.Add(" MailorTel like @mailortel"); queryParam.Add(new System.Data.SqlClient.SqlParameter("@mailortel","%"+ tel+ "%" )); } else { } }
                if (cond.msgno != null && cond.msgno.Length>=6) { check_condsize++; condarray.Add(" MsgNo like @msgno"); queryParam.Add(new System.Data.SqlClient.SqlParameter("@msgno", string.Format(msgnoQueryPattern, cond.msgno.Replace("%","").Trim() +"%" ))  ) ; }
                if (cond.powerno != null && cond.powerno.Length >= 6) { check_condsize++; condarray.Add(" PowerNo = @powerno"); queryParam.Add(new System.Data.SqlClient.SqlParameter("@powerno",string.Format( powernoQueryPattern, cond.powerno.Replace("%", "").Trim() + "%" ))); }
                if (cond.createdate_from != null) {
                    try { 
                    string[] from_dt = getmatchDateString(cond.createdate_from);
                    if (from_dt.Length==3)
                    {
//
                        DateTime dtFrom = new DateTime(
                            Convert.ToInt16(from_dt[0]), Convert.ToInt16(from_dt[1]), Convert.ToInt16(from_dt[2])
                            );
                            //[SelfReceivedDate]
                            condarray.Add(" CreatedDate >= @dtFrom"); queryParam.Add(new System.Data.SqlClient.SqlParameter("@dtFrom", dtFrom));
                    }
                    }catch(Exception ex)
                    {
                        //faildt
                    }
                }
                if (cond.createdate_to != null)
                {
                    try
                    {
                        string[] to_dt = getmatchDateString(cond.createdate_to);
                        if (to_dt.Length == 3)
                        {
                            DateTime dtFrom = new DateTime(
                                Convert.ToInt16(to_dt[0]), Convert.ToInt16(to_dt[1]), Convert.ToInt16(to_dt[2])
                                );

                            condarray.Add(" CreatedDate <= @dateto"); queryParam.Add(new System.Data.SqlClient.SqlParameter("@dateto", dtFrom));
                        }
                    }
                    catch (Exception ex)
                    {
                        //faildt
                    }
                }
                //must have one  or more cond
                if (check_condsize ==0) {
                    return toofewCSMSResult;
                }                   

                for (int i=0; i< condarray.Count; i++)       
                {
                    if (i == 0)
                    {
                        entitySQL += " WHERE ";
                    }else
                    {
                        entitySQL += " AND ";
                    }
                    entitySQL+= condarray[i];
                }

                var res = entities.FeaturesResult.SqlQuery(entitySQL, queryParam.ToArray()).
                    ToList().OrderByDescending(x => x.currentProsID);
                   
                var allunit = entities.SYS_UNIT_M.ToList();
                CsmsResult csmsResult = null;
                string tempMsgno = "";
                foreach (var oneRec in res)
                {
                    
                    var thisunit=allunit.Find(f => f.UnitID == oneRec.ToUnit);
                    var qunit = ((thisunit.MasterUnit != null && thisunit.MasterUnit != "-1") ? thisunit.MasterUnit : thisunit.UnitID);
                    var masterunit = allunit.Find(f => f.UnitID == qunit);
                    string dtAllow_d = (oneRec.AllowedDate != null ? oneRec.AllowedDate.Value.ToString("yyyy/MM/dd HH:mm:ss") : "NULL");
                    //string dtAllow_t = (oneRec.AllowedDate != null ? oneRec.AllowedDate.Value.ToLongTimeString() : "");
                    string dtSelf = (oneRec.SelfDonedDate != null ? oneRec.SelfDonedDate.Value.ToString("yyyy/MM/dd") : "NULL");

                    csmsResult = new CsmsResult { msgno = oneRec.MsgNo, mailorname = oneRec.MailorName, mailortel = oneRec.MailorTel,
                        email = oneRec.Email, catename = oneRec.CateName ,powerno= oneRec.PowerNo,
                        allowedddate= dtAllow_d  ,selfdoneddate= dtSelf,codedesc= (oneRec.MsgState==9 ? "已回覆" :"辦理中"),
                        unitname =thisunit.UnitName ,masterunit=masterunit.UnitName,ismaster= (oneRec.IsMaster==1 ? "主案件" :"") };
                      
                    /*
                    results.Add(new CsmsResult { msgno = oneRec.MsgNo, mailorname = oneRec.MailorName, mailortel = oneRec.MailorTel,
                        email = oneRec.Email, catename = oneRec.CateName ,powerno= oneRec.PowerNo,
                        allowedddate= dtAllow_d  ,selfdoneddate= dtSelf,codedesc= (oneRec.MsgState==9 ? "已回覆" :"辦理中"),
                        unitname =thisunit.UnitName ,masterunit=masterunit.UnitName,ismaster= (oneRec.IsMaster==1 ? "主案件" :"") });
                    */

                    if (oneRec == null)
                    {
                        continue;
                    }



                    //如果相等，已有寫入過此編號
                    // 111 -- toxxx
                    // 111 -- toooo
                    // 111 -- toroot

                    // 112  <-

                    if (oneRec.MsgNo.Trim().Equals(tempMsgno))
                    {
                   
                        // 111 -- toooo [1+]
                        // 111 -- toroot [1+] <---
                        // 112  <--[1+]
                        //not 1, 只要有就不加入前面的
                        if (!oneRec.ToUnit.Trim().Equals("root"))
                        {
                            //toroot 不要 其他都加
                            results.Add(csmsResult);

                        }
                    }
                    else
                    {


                        // 111 -- toxxx [1]
                        // 111 -- toooo [1+]
                        // 111 -- toroot [1+]
                        // 112  <--[1+]

                     //   if (!tempMsgno.Equals(""))
                     //   {
                            // 111 -- toxxx [1]
                            // 112  <--[1+]
                            //save 111
//                            results.Add(csmsResult);
                    //    }
                        // 111 -- toroot [1]
                        // 111 -- toxxx [1+]

                        tempMsgno = oneRec.MsgNo.Trim();

                        string toMsgNo = oneRec.MsgNo.Trim().Equals(oneRec.MsgID.Trim()) ? "未受理" : oneRec.MsgNo.Trim();
                    

                        csmsResult.msgno = toMsgNo;

                        if (!csmsResult.msgno.Equals("未受理"))
                            results.Add(csmsResult); //latest

                        
                        

                        //檢查是否為併案信件
                        //if (item.IsCombin == 0 && item.FileCombinNo.Length > 0)
                        
                    }
                    
                }
                        
                    

            }
            return results.ToArray();
        }
    }
}