namespace MMDAL.Query.NCR
{
    public class NCRQB
    {
        // GB4 parity: mms/NCR.svc/SelectList (default mmloadncr!=0 path) —
        // NCRBLL.GetSelectListNCR -> NCRDAL.NcrPicklist -> NCRQueryBuilder.GET_NCR_SELECTLIST.
        // Filters (all optional, -1/NULL = not applied): BIZTransactionTypeId, OUId, Status,
        // NCRStatus, and a free-text Number match against NCR number / item code / item name /
        // document number. Paged via FirstNumber/MaxResult; ordered by document number.
        // Required index: TNCR(BIZTRANSACTIONTYPEID, OUID, STATUS, NCRSTATUS), TNCRMATERIAL(NCRID)
        public const string GET_SELECTLIST_NCR = @"
            SELECT
                nc.NCRID                                                              AS Id,
                nc.NCRNUMBER                                                          AS Number,
                nc.DOCUMENTNUMBER                                                     AS DocumentNumber,
                CASE WHEN nc.NCRSTATUS = 0 THEN 'Pending'
                     WHEN nc.NCRSTATUS = 1 THEN 'Open'
                     ELSE 'Closed' END                                                AS NCRStatusName,
                ISNULL(itm.ITEMCODE, '')                                              AS ItemCode,
                ISNULL(itm.ITEMNAME, '')                                              AS ItemName
            FROM TNCR nc
            LEFT OUTER JOIN TNCRMATERIAL ncm ON ncm.NCRID = nc.NCRID
            LEFT OUTER JOIN MITEM itm         ON itm.ITEMID = ncm.ITEMID
            WHERE 1 = 1
            AND (@biztransactiontypeid = -1   OR nc.BIZTRANSACTIONTYPEID = @biztransactiontypeid)
            AND (@ouid                 = -1   OR nc.OUID                 = @ouid)
            AND (@status                = -1   OR nc.STATUS               = @status)
            AND (@ncrstatus             = -1   OR nc.NCRSTATUS            = @ncrstatus)
            AND (@number IS NULL OR nc.NCRNUMBER      LIKE '%' + @number + '%'
                                 OR itm.ITEMCODE       LIKE '%' + @number + '%'
                                 OR itm.ITEMNAME        LIKE '%' + @number + '%'
                                 OR nc.DOCUMENTNUMBER   LIKE '%' + @number + '%')
            ORDER BY nc.DOCUMENTNUMBER ASC
            OFFSET @firstnumber ROWS FETCH NEXT @maxresult ROWS ONLY;";

        // GB4 parity: mms/NCR.svc/SelectList (mmloadncr=0 path) —
        // NCRDAL.NcrPicklist -> NCRQueryBuilder.GET_NCR_FOR_DOCUMENT.
        // Narrower shape used only to resolve NCRs already linked to a document's corrective
        // action (TNCRACTION.ACTIONTYPE = 3), scoped by BizTransactionType/OU/Period.
        // Required index: TNCRACTION(NCRID, NCRMATERIALID, ACTIONTYPE), TNCR(BIZTRANSACTIONTYPEID, OUID)
        public const string GET_SELECTLIST_NCR_FOR_DOCUMENT = @"
            SELECT
                ncr.NCRID     AS Id,
                ncr.NCRNUMBER AS Number,
                ncr.NCRDATE   AS NCDate
            FROM TNCR ncr
            JOIN TNCRMATERIAL       ncrmat  ON ncr.NCRID = ncrmat.NCRID
            JOIN TNCRACTION         ncaction ON ncaction.NCRID = ncr.NCRID AND ncaction.NCRMATERIALID = ncrmat.NCRMATERIALID
            JOIN MBIZTRANSACTIONTYPE bizt    ON bizt.BIZTRANSACTIONTYPEID = ncr.BIZTRANSACTIONTYPEID
            WHERE 1 = 1
            AND ncaction.ACTIONTYPE = 3
            AND (@biztransactiontypeid      = -1 OR ncr.BIZTRANSACTIONTYPEID       = @biztransactiontypeid)
            AND (@biztransactionclassid     = -1 OR bizt.BIZTRANSACTIONCLASSID     = @biztransactionclassid)
            AND (@ouid                      = -1 OR ncr.OUID                       = @ouid)
            AND (@periodid                  = -1 OR bizt.PERIODID                  = @periodid)
            AND (@number IS NULL OR ncr.NCRNUMBER LIKE '%' + @number + '%')
            OFFSET @firstnumber ROWS FETCH NEXT @maxresult ROWS ONLY;";
    }
}