using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MMDAL.Query.Costing
{
    // Indexes recommended:
    //   TCOSTING: IX_TCOSTING_COSTINGID (COSTINGID)
    //   TCOSTINGDETAIL: IX_TCOSTINGDETAIL_COSTINGID_ITEMID (COSTINGID, ITEMID)
    public static class CostingQB
    {
        // ── ProductCosting/CostingSelectList (paged) ─────────────────────────
        // GB4 parity: CostingBLL.CostingSelectlist → CostingQueryBuilder.COSTING_SELECTLIST

        public const string GET_COSTING_SELECTLIST_SQL = @"
            WITH Filtered AS (
                SELECT DISTINCT
                       A.COSTINGID AS Id,
                       A.COSTINGNUMBER AS Number,
                       A.BIZTRANSACTIONTYPEID AS BiztransactionTypeId,
                       A.PERIODID AS PeriodId,
                       ISNULL(D.DOCUMENTNUMBER, '') AS DocNumber,
                       CASE ISNULL(D.STATUS, 6)
                           WHEN 0 THEN 'Pending' WHEN 1 THEN 'Active' WHEN 2 THEN 'Deleted'
                           WHEN 3 THEN 'Amended' WHEN 4 THEN 'Inactive' WHEN 5 THEN 'Archived'
                           ELSE '' END AS DocNumberStatus,
                       ISNULL(CAST(D.AMENDMENTSLNO AS VARCHAR(20)), '') AS DocNumberAmendmentSlNo,
                       ISNULL(SQ.DOCUMENTNUMBER, '') AS NextDocNumber,
                       CASE ISNULL(E.STATUS, 6)
                           WHEN 0 THEN 'Pending' WHEN 1 THEN 'Active' WHEN 2 THEN 'Deleted'
                           WHEN 3 THEN 'Amended' WHEN 4 THEN 'Inactive' WHEN 5 THEN 'Archived'
                           ELSE '' END AS NextDocNumberStatus,
                       ISNULL(CAST(E.AMENDMENTSLNO AS VARCHAR(20)), '') AS NextDocNumberAmendmentSlNo,
                       ISNULL(SO.DOCUMENTNUMBER, '') AS NextNextDocNumber,
                       CASE ISNULL(F.STATUS, 6)
                           WHEN 0 THEN 'Pending' WHEN 1 THEN 'Active' WHEN 2 THEN 'Deleted'
                           WHEN 3 THEN 'Amended' WHEN 4 THEN 'Inactive' WHEN 5 THEN 'Archived'
                           ELSE '' END AS NextNextDocNumberStatus,
                       ISNULL(CAST(F.AMENDMENTSLNO AS VARCHAR(20)), '') AS NextNextDocNumberAmendmentSlNo,
                       ISNULL(COSTVER.VERSIONCOUNT, 0) AS CostingVersionCount,
                       ISNULL(PAR.PARTYCODE, '') AS CustomerCode,
                       ISNULL(PAR.PARTYNAME, '') AS CustomerName,
                       ROW_NUMBER() OVER (ORDER BY A.COSTINGNUMBER DESC) AS RowNum
                FROM   TCOSTING A
                OUTER APPLY (SELECT COUNT(COSTVER.COSTINGNUMBER) VERSIONCOUNT FROM TCOSTING COSTVER WHERE A.COSTINGNUMBER = COSTVER.COSTINGNUMBER) COSTVER
                INNER JOIN TCOSTINGDETAIL B ON A.COSTINGID = B.COSTINGID
                LEFT OUTER JOIN TMMDETAIL C ON B.OBJECTID = C.DOCUMENTDETAILID AND B.OBJECTTYPEID = -1899997854
                LEFT OUTER JOIN TMMHEAD D ON C.DOCUMENTID = D.DOCUMENTID
                LEFT OUTER JOIN TMMHEAD E ON B.COSTINGID = E.OBJECTID AND E.OBJECTTYPEID = -1390135001
                LEFT OUTER JOIN TPENDINGALLOCATION SQ ON E.DOCUMENTID = SQ.OBJECTHEADERID AND SQ.OBJECTHEADERTYPEID = -1899997952
                LEFT OUTER JOIN TPENDINGALLOCATION SO ON SQ.ALLOTEDALLOCATIONID = SO.PREALLOTEDALLOCATIONID AND SO.OBJECTHEADERTYPEID = -1899997952
                LEFT OUTER JOIN TMMHEAD F ON SO.OBJECTHEADERID = F.DOCUMENTID
                LEFT OUTER JOIN MPARTY PAR ON D.PARTYID = PAR.PARTYID
                LEFT OUTER JOIN MITEM ITEM ON B.ITEMID = ITEM.ITEMID
                WHERE  A.COSTINGID <> -1
                AND    (@searchtext IS NULL OR A.COSTINGNUMBER LIKE @searchtext)
                AND    (@type = -1 OR A.COSTINGTYPE = @type)
                AND    (@itemid = -1 OR B.ITEMID = @itemid)
                AND    (@costingid = -1 OR A.COSTINGID = @costingid)
                AND    (@biztransactiontypeid = -1 OR A.BIZTRANSACTIONTYPEID = @biztransactiontypeid)
                AND    (@documentid = -1 OR D.DOCUMENTID = @documentid)
                AND    (@objecttypeid = -1 OR B.OBJECTTYPEID = @objecttypeid)
                AND    (@status = -1 OR A.STATUS = @status)
                AND    (@costanalysistype = -1 OR A.COSTANALYSISTYPE = @costanalysistype)
            )
            SELECT Id, Number, BiztransactionTypeId, PeriodId, DocNumber, DocNumberStatus, DocNumberAmendmentSlNo,
                   NextDocNumber, NextDocNumberStatus, NextDocNumberAmendmentSlNo,
                   NextNextDocNumber, NextNextDocNumberStatus, NextNextDocNumberAmendmentSlNo,
                   CostingVersionCount, CustomerCode, CustomerName
            FROM   Filtered
            WHERE  (@firstnumber = -1 AND @maxresult = -1)
               OR  (RowNum BETWEEN @firstnumber AND @maxresult)
            ORDER BY RowNum;";
    }
}