using System;

namespace MMDAL.Query.SKUSetting
{
    // Required indexes:
    //   MSKUSETTING: (SKUSETTINGID)                           -- PK; covers paged ORDER BY
    //   MSKUSETTING: (TENANTID, STATUS) INCLUDE (SKUSETTINGID, SKUSETTINGCODE, SKUSETTINGNAME)
    //   MUSER: (USERID)                                       -- covers LEFT JOIN on CREATEDBYID / MODIFIEDBYID
    internal static class SKUSettingQB
    {
        public const string GET_SKUSETTING = @"
SELECT
    ss.SKUSETTINGID         AS SKUSettingId,
    ss.SKUSETTINGCODE       AS SKUSettingCode,
    ss.SKUSETTINGNAME       AS SKUSettingName,

    ss.SORTORDER            AS SKUSettingSortOrder,
    ss.STATUS               AS SKUSettingStatus,
    ss.VERSION              AS SKUSettingVersion,

    ss.CREATEDBYID          AS SKUSettingCreatedById,
    cb.USERNAME             AS SKUSettingCreatedByName,
    ss.CREATEDON            AS SKUSettingCreatedOn,

    ss.MODIFIEDBYID         AS SKUSettingModifiedById,
    mb.USERNAME             AS SKUSettingModifiedByName,
    ss.MODIFIEDON           AS SKUSettingModifiedOn,

    ss.SOURCETYPE           AS SKUSettingSourceType,
    ss.TENANTID             AS SKUSettingTenantId

FROM dbo.MSKUSETTING ss

LEFT JOIN dbo.MUSER cb
       ON ss.CREATEDBYID = cb.USERID

LEFT JOIN dbo.MUSER mb
       ON ss.MODIFIEDBYID = mb.USERID

WHERE ss.SKUSETTINGID = @SKUSettingId;
";

        public const string SAVE_SKUSETTING = @"
INSERT INTO MSKUSETTING
(
    SKUSETTINGCODE,
    SKUSETTINGNAME,
    SORTORDER,
    STATUS,
    VERSION,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON,
    SOURCETYPE,
    TENANTID
)
VALUES
(
    @SKUSettingCode,
    @SKUSettingName,
    @SKUSettingSortOrder,
    @SKUSettingStatus,
    @SKUSettingVersion,
    @SKUSettingCreatedById,
    @SKUSettingCreatedOn,
    @SKUSettingModifiedById,
    @SKUSettingModifiedOn,
    @SKUSettingSourceType,
    @SKUSettingTenantId
);";

        public const string UPDATE_SKUSETTING = @"
UPDATE MSKUSETTING
SET
    SKUSETTINGCODE = @SKUSettingCode,
    SKUSETTINGNAME = @SKUSettingName,
    SORTORDER = @SKUSettingSortOrder,
    STATUS = @SKUSettingStatus,
    VERSION = @SKUSettingVersion,
    MODIFIEDBYID = @SKUSettingModifiedById,
    MODIFIEDON = @SKUSettingModifiedOn,
    SOURCETYPE = @SKUSettingSourceType
WHERE
    SKUSETTINGID = @SKUSettingId;";

        public const string DELETE_SKUSETTING = @"
DELETE FROM MSKUSETTING
WHERE SKUSETTINGID = @SKUSettingId;";

        public const string GET_SELECTLIST_SKUSETTING = @"
WITH SKUSetting AS
(
    SELECT
        ss.SKUSETTINGID         AS SKUSettingId,
        ss.SKUSETTINGCODE       AS SKUSettingCode,
        ss.SKUSETTINGNAME       AS SKUSettingName,

        ss.SORTORDER            AS SKUSettingSortOrder,
        ss.STATUS               AS SKUSettingStatus,
        ss.VERSION              AS SKUSettingVersion,

        ss.CREATEDBYID          AS SKUSettingCreatedById,
        cb.USERNAME             AS SKUSettingCreatedByName,
        ss.CREATEDON            AS SKUSettingCreatedOn,

        ss.MODIFIEDBYID         AS SKUSettingModifiedById,
        mb.USERNAME             AS SKUSettingModifiedByName,
        ss.MODIFIEDON           AS SKUSettingModifiedOn,

        ss.SOURCETYPE           AS SKUSettingSourceType,
        ss.TENANTID             AS SKUSettingTenantId,

        ROW_NUMBER() OVER (ORDER BY ss.SKUSETTINGID) AS RowNum

    FROM dbo.MSKUSETTING ss

    LEFT JOIN dbo.MUSER cb
           ON ss.CREATEDBYID = cb.USERID

    LEFT JOIN dbo.MUSER mb
           ON ss.MODIFIEDBYID = mb.USERID
)

SELECT
    SKUSettingId,
    SKUSettingCode,
    SKUSettingName,
    SKUSettingSortOrder,
    SKUSettingStatus,
    SKUSettingVersion,
    SKUSettingCreatedById,
    SKUSettingCreatedByName,
    SKUSettingCreatedOn,
    SKUSettingModifiedById,
    SKUSettingModifiedByName,
    SKUSettingModifiedOn,
    SKUSettingSourceType,
    SKUSettingTenantId,
    RowNum
FROM SKUSetting
WHERE
    (@firstnumber = -1 AND @maxresult = -1)
    OR (RowNum BETWEEN @firstnumber AND @maxresult);";

        public const string GET_SKUSETTING_LIST = @"
SELECT
    ss.SKUSETTINGID         AS SKUSettingId,
    ss.SKUSETTINGCODE       AS SKUSettingCode,
    ss.SKUSETTINGNAME       AS SKUSettingName,

    ss.SORTORDER            AS SKUSettingSortOrder,
    ss.STATUS               AS SKUSettingStatus,
    ss.VERSION              AS SKUSettingVersion,

    ss.CREATEDBYID          AS SKUSettingCreatedById,
    ss.CREATEDON            AS SKUSettingCreatedOn,

    ss.MODIFIEDBYID         AS SKUSettingModifiedById,
    ss.MODIFIEDON           AS SKUSettingModifiedOn,

    ss.SOURCETYPE           AS SKUSettingSourceType,
    ss.TENANTID             AS SKUSettingTenantId

FROM dbo.MSKUSETTING ss;";
    }
}