using MonumentDefine;
using Newtonsoft.Json.Linq;
using OT.COM.ArsenalDB;
using OT.COM.ArsenalDB.SQL;
using OT.COM.SignalerMessage;
using SoldierData.EnterprizeV4;
using System;
using System.Collections.Generic;
using System.Linq;

namespace CounsellorBL.ORD.ConstDefinition
{
    internal class SQLLib : DirectSQLLibBase
    {
        public SQLLib(ADirectSQLHelper i_sqlLibBase) : base(i_sqlLibBase) { }

        public virtual string GetMessageLogRecordSuccessCount(string message_log_uid_, string record_status_flag_, out Command command_)
        {
            ADirectCmdParameter cp = GetParamter();

            string sql = $@"SELECT COUNT({tb_ord_message_log_record.CN_STATUS_FLAG}) {tb_ord_message_log_record.CN_STATUS_FLAG} FROM {nameof(tb_ord_message_log_record)} 
                            WHERE {tb_ord_message_log_record.CN_LOG_UID} = {cp.Add(message_log_uid_)} 
                            AND {tb_ord_message_log_record.CN_STATUS_FLAG} = {cp.Add(record_status_flag_)} 
                            AND {tb_ord_message_log_record.CN_ERROR_CODE} IS NULL 
                        ";

            return GenerateCommand<tb_ord_message_log_record>(sql, cp, out command_);
        }

        /// <summary>
        /// readCommandPostDataHandler, 替代原本查詢方法用來排序 receivable & received
        /// </summary>
        public string readCommandPostDataHandlerQueryDetailSortGuid(CRequestMessage i_crmInput, Dictionary<string, string> dicCondition, List<string> lsBranch, List<string> userBranch, bool IsTotalQuery, int nPageIdx, int nPageNum, out Command command_)
        {
            ADirectCmdParameter cp = GetParamter();
            bool isGroup = false;
            const string received = "received";
            const string receivable = "receivable";

            string selectColumn = IsTotalQuery ? 
                $"COUNT(a.{ tb_ord_order_master.CN_UID})" : 
                $@"a.{ tb_ord_order_master.CN_UID},
                a.{received},
                a.{receivable},
                art.{tb_grp_article.CN_REMARK}, 
                aa.{ tb_ord_order_master.CN_TYPE},
                aa.{ tb_ord_order_master.CN_ORDER_CODE},
                aa.{ tb_ord_order_master.CN_STATUS},
                aa.{ tb_ord_order_master.CN_CREATE_DATE},
                aa.{ tb_ord_order_master.CN_GROUP_UID},
                aa.{ tb_ord_order_master.CN_BRANCH_UID},
                aa.{ tb_ord_order_master.CN_ARRIVED_DATE},
                aa.{ tb_ord_order_master.CN_ARTICLE_UID},
                aa.{ tb_ord_order_master.CN_NAME} 
                ";

            string sql = $@"
            SELECT {selectColumn} FROM  
            (
                select 
                a.{tb_ord_order_master.CN_UID},
                --sum (case when b.status = 2 then b.take_qty * c.price else 0 end) received,
				--sum (case when b.status = 2 then b.take_qty * (d.amount / d.qty)else 0 end) received,-- take_qty為實際拿取數量, d.amount / d.qty計算實際購買金額
               ---- sum (case when b.status = 2 then d.amount else 0 end) received,-- d.amount 已取貨總 Total 
                --sum ((b.price /b.order_qty) * take_qty) received,
				--sum(b.price) receivable 
                --sum (case when e.wprice_payment = 1 and c.wholesale_price != 0 then b.order_qty * c.wholesale_price else b.price end) receivable 
              ----  sum (case when b.status = 2 then d.amount else b.price end) receivable 
(case  max(a.type)  when '1' then   sum (case when b.status = 2 then d.amount else 0 end) 
			     when '2' then sum(ee.amount) else 0 end ) received,-- d.amount 已取貨總 Total 
 (case  max(a.type)  when '1' then    sum (case when b.status = 2 then d.amount else b.price end)
			     when '2' then sum(ee.amount) else 0 end ) receivable

                from {nameof(tb_ord_order_master)} a 
                LEFT JOIN {nameof(tb_ord_order_detail)} b on b.{tb_ord_order_detail.CN_ORDER_UID} = a.{tb_ord_order_master.CN_UID} 
                --LEFT JOIN {nameof(tb_prd_article2product)} c on c.{tb_prd_article2product.CN_UID} = b.{tb_ord_order_detail.CN_ARTICLE2PRODUCT_UID} 
                --LEFT JOIN {nameof(tb_ord_purchase_detail)} d on d.{tb_ord_purchase_detail.CN_ORDER_DETAIL_UID} = b.{tb_ord_order_detail.CN_UID} 
                LEFT JOIN (SELECT {tb_ord_purchase_detail.CN_ORDER_DETAIL_UID}, SUM({tb_ord_purchase_detail.CN_AMOUNT}) amount FROM {nameof(tb_ord_purchase_detail)} GROUP BY {tb_ord_purchase_detail.CN_ORDER_DETAIL_UID}) d on d.{tb_ord_purchase_detail.CN_ORDER_DETAIL_UID} = b.uid -- Make sure is only one 
                --LEFT JOIN {nameof(tb_meb_member)} e on e.{tb_meb_member.CN_UID} = b.{tb_ord_order_detail.CN_MEMBER_UID} 

                 LEFT JOIN  (SELECT sum(x.{tb_prd_article2product.CN_PRICE}* z.{tb_ord_purchase_detail.CN_QTY}) amount ,x.{tb_prd_article2product.CN_ORDER_UID} 
							FROM {nameof(tb_prd_article2product)} x 
							LEFT JOIN {nameof(tb_ord_order_master)} y  on y.{tb_ord_order_master.CN_UID}=x.{tb_prd_article2product.CN_ORDER_UID} 
							LEFT JOIN {nameof(tb_ord_purchase_detail)} z on z.{tb_ord_purchase_detail.CN_ARTICLE2PRODUCT_UID}=x.{tb_prd_article2product.CN_UID} 
							GROUP BY x.{tb_prd_article2product.CN_ORDER_UID} 
                ) as ee on ee.{tb_prd_article2product.CN_ORDER_UID} =  a.{tb_ord_order_master.CN_UID} 
                GROUP BY a.{tb_ord_order_master.CN_UID} 
            ) a
            LEFT JOIN {nameof(tb_ord_order_master)} aa on aa.{tb_ord_order_master.CN_UID} = a.{tb_ord_order_master.CN_UID} -- Get full column 
			LEFT JOIN {nameof(tb_grp_article)} art on art.uid = aa.article_uid -- Get remark 
            WHERE 1=1 
            ";

            sql += $"AND aa.{tb_ord_order_master.CN_TYPE} != {cp.Add((int)Enums.ArticleType.Purchase)} ";
            sql += $"AND aa.{tb_ord_order_master.CN_STATUS_FLAG} = {cp.Add(BLWording.STATUS_FLAG_ON)} ";

            if (dicCondition.ContainsKey(tb_ord_order_master.CN_CREATE_DATE + "_start"))
            {
                sql += $"AND aa.{tb_ord_order_master.CN_CREATE_DATE} >= {cp.Add(dicCondition[tb_ord_order_master.CN_CREATE_DATE + "_start"])} ";
            }
            if (dicCondition.ContainsKey(tb_ord_order_master.CN_CREATE_DATE + "_end"))
            {
                sql += $"AND aa.{tb_ord_order_master.CN_CREATE_DATE} < { cp.Add(Convert.ToDateTime(dicCondition[tb_ord_order_master.CN_CREATE_DATE + "_end"]).AddDays(1))} ";
            }
            if (dicCondition.ContainsKey(tb_ord_order_master.CN_STATUS))
            {
                sql += $"AND aa.{tb_ord_order_master.CN_STATUS} = {cp.Add(dicCondition[tb_ord_order_master.CN_STATUS])} ";
            }
            else
            {
                sql += $"AND aa.{tb_ord_order_master.CN_STATUS} != {cp.Add((int)Enums.OrderMainStatus.Archive)} ";
            }
            if (dicCondition.ContainsKey(tb_ord_order_master.CN_ORDER_CODE))
            {
                sql += $"AND {tb_ord_order_master.CN_ORDER_CODE} = {cp.Add(dicCondition[tb_ord_order_master.CN_ORDER_CODE])} ";
            }
            if (dicCondition.ContainsKey(tb_ord_order_master.CN_ARRIVED_DATE + "_start"))
            {
                sql += $"AND {tb_ord_order_master.CN_ARRIVED_DATE} >= {cp.Add(dicCondition[tb_ord_order_master.CN_ARRIVED_DATE + "_start"])} ";
            }
            if (dicCondition.ContainsKey(tb_ord_order_master.CN_ARRIVED_DATE + "_end"))
            {
                sql += $"AND {tb_ord_order_master.CN_ARRIVED_DATE} < {cp.Add(Convert.ToDateTime(dicCondition[tb_ord_order_master.CN_ARRIVED_DATE + "_end"]).AddDays(1))} ";
            }
            if (dicCondition.ContainsKey(tb_ord_order_master.CN_UID))
            {
                sql += $"AND {tb_ord_order_master.CN_UID} = {dicCondition[tb_ord_order_master.CN_UID]}";
            }
            if (dicCondition.ContainsKey(tb_ord_order_master.CN_GROUP_UID))
            {
                sql += $"AND aa.{tb_ord_order_master.CN_GROUP_UID} = {cp.Add(dicCondition[tb_ord_order_master.CN_GROUP_UID])} ";
            }
            else if (dicCondition.ContainsKey("GroupOrBranch_uid")) // 處理shopping list 搜尋選項 uid 有可能是branch or group
            {
                tb_grp_group cGroup = new tb_grp_group();
                cGroup.SetFullDirty();
                WhereNode wnGroup = new WhereNode(tb_grp_group.CN_UID, WhereNode.EColumnOperation.EOT_EQ, typeof(tb_grp_group), dicCondition["GroupOrBranch_uid"].ToString());
                Command gSelect = Command.SetupSelectCmd(cGroup, wnGroup);
                ArsenalInterface aiGroup = ArsenalDBMgr.GetInst(gSelect);
                var qdsGroup = aiGroup.RunQueryList<tb_grp_group>(gSelect);
                if (qdsGroup.Any())
                {
                    sql += $"AND aa.{tb_ord_order_master.CN_GROUP_UID} = {cp.Add(dicCondition["GroupOrBranch_uid"].ToString())} ";
                    isGroup = true;
                }
            }
            sql += $"AND aa.{tb_ord_order_master.CN_GROUP_UID} IN ({string.Join(',', lsBranch.Select(x => $"'{x}'").ToArray())}) ";

            if (dicCondition.ContainsKey(tb_ord_order_master.CN_BRANCH_UID))
            {
                sql += $"AND {tb_ord_order_master.CN_BRANCH_UID} = {cp.Add(dicCondition[tb_ord_order_master.CN_BRANCH_UID].ToString())} ";
            }
            else if (dicCondition.ContainsKey("GroupOrBranch_uid") && !isGroup)
            {
                sql += $"AND {tb_ord_order_master.CN_BRANCH_UID} = {cp.Add(dicCondition["GroupOrBranch_uid"].ToString())} ";
            }
            else
            {
                sql += $"AND {tb_ord_order_master.CN_BRANCH_UID} IN ({string.Join(',', userBranch.Select(x=> $"'{x}'").ToArray())}) "; ;
            }

            Dictionary<string, string> mainOrderData = null;
            string mainFullTextPattern = null;
            if (i_crmInput != null && i_crmInput.param != null && i_crmInput.param.ContainsKey(BLWording.QRY_MASTER))
            {
                JArray joData = i_crmInput.param[BLWording.QRY_MASTER] as JArray;

                if (joData.Any() && joData[0][BLWording.ORDERDATA] != null)
                {
                    JArray jaData = joData[0][BLWording.ORDERDATA] as JArray;
                    mainOrderData = jaData[0].ToObject<Dictionary<string, string>>();
                }
                if (joData.Any() && joData[0][BLWording.FULLTEXT_PATTERN] != null)
                {
                    mainFullTextPattern = joData[0][BLWording.FULLTEXT_PATTERN].ToString();
                 
                    sql +=
                        $@"
                        AND 
                        (
                           aa.{tb_ord_order_master.CN_ORDER_CODE} LIKE {cp.Add($"%{mainFullTextPattern}%")}
                        OR aa.{tb_ord_order_master.CN_NAME} LIKE {cp.Add($"%{mainFullTextPattern}%")}
                        ) 
                        ";
                }
            }
        
            // 如果不是查詢總Total, Join Sort And Offset
            if (!IsTotalQuery)
            {
                if (mainOrderData != null && mainOrderData.First().Key != tb_grp_branch.CN_BRANCH_NAME)
                {
                    sql += $"ORDER BY {mainOrderData.First().Key} {mainOrderData.First().Value} ";
                }
                else
                {
                    sql += $"ORDER BY {tb_grp_branch.CN_CREATE_DATE} DESC ";
                }

                sql += $"OFFSET {nPageIdx * nPageNum} ROW FETCH FIRST {nPageNum} ROW ONLY ";//配合上面語法
            }
           
            return GenerateCommand<tb_ord_message_log_record>(sql, cp, out command_);
        }

        public Command IsMemberOrderCheckedOut(string member_uid, string branch_uid)
        {
            Command command = null;
            ADirectCmdParameter cp = GetParamter();

            var sql = $@"
                        SELECT dtl.{tb_ord_order_detail.CN_UID} FROM {nameof(tb_ord_order_detail)} dtl 
                        LEFT JOIN {nameof(tb_meb_member)} meb 
                        ON meb.{tb_meb_member.CN_UID} = dtl.{tb_ord_order_detail.CN_MEMBER_UID} 
                        LEFT JOIN {nameof(tb_grp_branch)} branch 
                        ON branch.{tb_grp_branch.CN_UID} = meb.{tb_meb_member.CN_DEFAULT_BRANCH} 
                        WHERE 1=1 
                        AND dtl.{tb_ord_order_detail.CN_STATUS} = {cp.Add((int)Enums.OrderStatus.NotTake)} 
                        AND meb.{tb_meb_member.CN_UID} = {cp.Add(member_uid)} 
                        AND branch.{tb_grp_branch.CN_UID} = {cp.Add(branch_uid)} 
                        ";

            GenerateCommand<tb_ord_message_log_record>(sql, cp, out command);

            return command;
        }
    }
}