using System;

namespace MMDAL.Query.SizeSet
{
    // Required indexes:
    //   MSIZESET: (SIZESETID)                                 -- PK; covers paged ORDER BY
    //   MSIZESET: (TENANTID, STATUS) INCLUDE (SIZESETID, SIZESETCODE, SIZESETNAME)
    //   MUSER: (USERID)                                       -- covers LEFT JOIN on CREATEDBYID / MODIFIEDBYID
    internal static class SizeSetQB
    {
        public const string GET_SIZESET = @"
SELECT
    ss.SIZESETID            AS SizeSetId,
    ss.SIZESETCODE          AS SizeSetCode,
    ss.SIZESETNAME          AS SizeSetName,

    ss.SORTORDER            AS SizeSetSortOrder,
    ss.VERSION              AS SizeSetVersion,
    ss.STATUS               AS SizeSetStatus,

    ss.CREATEDBYID          AS SizeSetCreatedById,
    cb.USERNAME             AS SizeSetCreatedByName,
    ss.CREATEDON            AS SizeSetCreatedOn,

    ss.MODIFIEDBYID         AS SizeSetModifiedById,
    mb.USERNAME             AS SizeSetModifiedByName,
    ss.MODIFIEDON           AS SizeSetModifiedOn,

    ss.SOURCETYPE           AS SizeSetSourceType,
    ss.TENANTID             AS SizeSetTenantId

FROM dbo.MSIZESET ss

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

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

WHERE ss.SIZESETID = @SizeSetId;
";

        public const string SAVE_SIZESET = @"
INSERT INTO MSIZESET
(
    SIZESETCODE,
    SIZESETNAME,
    SORTORDER,
    VERSION,
    STATUS,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON,
    SOURCETYPE,
    TENANTID
)
VALUES
(
    @SizeSetCode,
    @SizeSetName,
    @SizeSetSortOrder,
    @SizeSetVersion,
    @SizeSetStatus,
    @SizeSetCreatedById,
    @SizeSetCreatedOn,
    @SizeSetModifiedById,
    @SizeSetModifiedOn,
    @SizeSetSourceType,
    @SizeSetTenantId
);";

        public const string UPDATE_SIZESET = @"
UPDATE MSIZESET
SET
    SIZESETCODE = @SizeSetCode,
    SIZESETNAME = @SizeSetName,
    SORTORDER = @SizeSetSortOrder,
    VERSION = @SizeSetVersion,
    STATUS = @SizeSetStatus,
    MODIFIEDBYID = @SizeSetModifiedById,
    MODIFIEDON = @SizeSetModifiedOn,
    SOURCETYPE = @SizeSetSourceType
WHERE
    SIZESETID = @SizeSetId;";

        public const string DELETE_SIZESET = @"
DELETE FROM MSIZESET
WHERE SIZESETID = @SizeSetId;";

        public const string GET_SELECTLIST_SIZESET = @"
WITH SizeSet AS
(
    SELECT
        ss.SIZESETID            AS SizeSetId,
        ss.SIZESETCODE          AS SizeSetCode,
        ss.SIZESETNAME          AS SizeSetName,

        ss.SORTORDER            AS SizeSetSortOrder,
        ss.VERSION              AS SizeSetVersion,
        ss.STATUS               AS SizeSetStatus,

        ss.CREATEDBYID          AS SizeSetCreatedById,
        cb.USERNAME             AS SizeSetCreatedByName,
        ss.CREATEDON            AS SizeSetCreatedOn,

        ss.MODIFIEDBYID         AS SizeSetModifiedById,
        mb.USERNAME             AS SizeSetModifiedByName,
        ss.MODIFIEDON           AS SizeSetModifiedOn,

        ss.SOURCETYPE           AS SizeSetSourceType,
        ss.TENANTID             AS SizeSetTenantId,

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

    FROM dbo.MSIZESET ss

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

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

SELECT
    SizeSetId,
    SizeSetCode,
    SizeSetName,
    SizeSetSortOrder,
    SizeSetVersion,
    SizeSetStatus,
    SizeSetCreatedById,
    SizeSetCreatedByName,
    SizeSetCreatedOn,
    SizeSetModifiedById,
    SizeSetModifiedByName,
    SizeSetModifiedOn,
    SizeSetSourceType,
    SizeSetTenantId,
    RowNum
FROM SizeSet
WHERE
    (@firstnumber = -1 AND @maxresult = -1)
    OR (RowNum BETWEEN @firstnumber AND @maxresult);";

        public const string GET_SIZESET_LIST = @"
SELECT
    ss.SIZESETID            AS SizeSetId,
    ss.SIZESETCODE          AS SizeSetCode,
    ss.SIZESETNAME          AS SizeSetName,

    ss.SORTORDER            AS SizeSetSortOrder,
    ss.VERSION              AS SizeSetVersion,
    ss.STATUS               AS SizeSetStatus,

    ss.CREATEDBYID          AS SizeSetCreatedById,
    ss.CREATEDON            AS SizeSetCreatedOn,

    ss.MODIFIEDBYID         AS SizeSetModifiedById,
    ss.MODIFIEDON           AS SizeSetModifiedOn,

    ss.SOURCETYPE           AS SizeSetSourceType,
    ss.TENANTID             AS SizeSetTenantId

FROM dbo.MSIZESET ss;";
    }
}