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

namespace FrameworkDAL.Query.LoadTemplate
{
    public class LoadTemplateQB
    {
        public const string GET_LOADTEMPLATE = @"SELECT
                                                LT.LOADTEMPLATEID   AS LoadTemplateId,
                                                LT.OBJECTTYPEID     AS LoadTemplateObjectTypeId,
                                                LT.OUID             AS LoadTemplateOUId,
                                                LT.TEMPLATENAME     AS LoadTemplateTemplateName,
                                                LT.OBJECTID         AS LoadTemplateObjectId,
                                                LT.SORTORDER        AS LoadTemplateSortOrder,
                                                LT.SOURCETYPE       AS LoadTemplateSourceType,
                                                LT.STATUS           AS LoadTemplateStatus,
                                                LT.VERSION          AS LoadTemplateVersion,
                                                LT.CREATEDBYID      AS LoadTemplateCreatedById,
                                                LT.CREATEDON        AS LoadTemplateCreatedOn,
                                                LT.MODIFIEDBYID     AS LoadTemplateModifiedById,
                                                LT.MODIFIEDON       AS LoadTemplateModifiedOn
                                            FROM dbo.MLOADTEMPLATE LT
                                            WHERE LT.LOADTEMPLATEID = @loadtemplateid;";

        public const string SAVE_LOADTEMPLATE = @"INSERT INTO dbo.MLOADTEMPLATE
                                                    (
                                                        LOADTEMPLATEID,
                                                        OBJECTTYPEID,
                                                        OUID,
                                                        TEMPLATENAME,
                                                        OBJECTID,
                                                        SORTORDER,
                                                        SOURCETYPE,
                                                        STATUS,
                                                        VERSION,
                                                        CREATEDBYID,
                                                        CREATEDON,
                                                        MODIFIEDBYID,
                                                        MODIFIEDON
                                                    )
                                                    VALUES
                                                    (
                                                        @LoadTemplateId,
                                                        @LoadTemplateObjectTypeId,
                                                        @LoadTemplateOUId,
                                                        @LoadTemplateTemplateName,
                                                        @LoadTemplateObjectId,
                                                        @LoadTemplateSortOrder,
                                                        @LoadTemplateSourceType,
                                                        @LoadTemplateStatus,
                                                        @LoadTemplateVersion,
                                                        @LoadTemplateCreatedById,
                                                        @LoadTemplateCreatedOn,
                                                        @LoadTemplateModifiedById,
                                                        @LoadTemplateModifiedOn
                                                    );";

        public const string UPDATE_LOADTEMPLATE = @"UPDATE dbo.MLOADTEMPLATE
                                                    SET
                                                        OBJECTTYPEID = @LoadTemplateObjectTypeId,
                                                        OUID = @LoadTemplateOUId,
                                                        TEMPLATENAME = @LoadTemplateTemplateName,
                                                        OBJECTID = @LoadTemplateObjectId,
                                                        SORTORDER = @LoadTemplateSortOrder,
                                                        SOURCETYPE = @LoadTemplateSourceType,
                                                        STATUS = @LoadTemplateStatus,
                                                        VERSION = @LoadTemplateVersion,
                                                        MODIFIEDBYID = @LoadTemplateModifiedById,
                                                        MODIFIEDON = @LoadTemplateModifiedOn
                                                    WHERE
                                                        LOADTEMPLATEID = @LoadTemplateId;";

        public const string DELETE_LOADTEMPLATE = @"DELETE FROM dbo.MLOADTEMPLATE
                                                    WHERE LOADTEMPLATEID = @loadtemplateid;";

        // Ported from GB4 fws/LoadTemplate.svc's SelectList operation. GB4's service calls the BLL
        // twice — once for the page of data, once with FirstNumber=-1/MaxResult=-1 and IsCount=true
        // purely to get a Total for the pagination envelope — same shape as City's SelectList
        // (GET_CITYPICKLIST_FOR_SELECT2), which this mirrors exactly: /**WHERE_CLAUSE**/ injected by
        // the DAL from dynamic criteria, -1/-1 sentinel means "return all rows, no paging".
        public const string GET_LOADTEMPLATEPICKLIST_FOR_SELECT2 = @"
WITH PagedLoadTemplate AS (
    SELECT
        LT.LOADTEMPLATEID as Id,
        LT.TEMPLATENAME as TemplateName,
        LT.OBJECTID as ObjectId,
        ROW_NUMBER() OVER (ORDER BY LT.LOADTEMPLATEID) AS RowNum
    FROM
        dbo.MLOADTEMPLATE LT
    WHERE 1=1
    /**WHERE_CLAUSE**/
)
SELECT Id, TemplateName, ObjectId
FROM PagedLoadTemplate
WHERE
(
    (@firstnumber = -1 AND @maxresult = -1)
    OR (RowNum BETWEEN @firstnumber AND @maxresult)
)";

        // Real Total count for the pagination envelope (City's equivalent path is stubbed to "0" —
        // implemented properly here instead of copying that shortcut into new code).
        public const string GET_LOADTEMPLATE_COUNT = @"
SELECT COUNT(1)
FROM dbo.MLOADTEMPLATE LT
WHERE 1=1
/**WHERE_CLAUSE**/";
    }
}