using System;

namespace MMDAL.Query.DutyTable
{
    // Required indexes:
    //   MDUTYTABLE: (DUTYTABLEID)                             -- PK; covers paged ORDER BY
    //   MDUTYTABLE: (TENANTID, STATUS) INCLUDE (DUTYTABLEID, DUTYTABLECODE, DUTYTABLENAME)
    //   MUSER: (USERID)                                       -- covers LEFT JOIN on CREATEDBYID / MODIFIEDBYID
    public static class DutyTableQB
    {
        public const string GET_DUTYTABLE = @"
SELECT
    dt.DUTYTABLEID          AS DutyTableId,
    dt.DUTYTABLECODE        AS DutyTableCode,
    dt.DUTYTABLENAME        AS DutyTableName,

    dt.SORTORDER            AS DutyTableSortOrder,
    dt.STATUS               AS DutyTableStatus,
    dt.VERSION              AS DutyTableVersion,

    dt.CREATEDBYID          AS DutyTableCreatedById,
    cb.USERNAME             AS DutyTableCreatedByName,
    dt.CREATEDON            AS DutyTableCreatedOn,

    dt.MODIFIEDBYID         AS DutyTableModifiedById,
    mb.USERNAME             AS DutyTableModifiedByName,
    dt.MODIFIEDON           AS DutyTableModifiedOn,

    dt.SOURCETYPE           AS DutyTableSourceType,
    dt.TENANTID             AS DutyTableTenantId

FROM dbo.MDUTYTABLE dt

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

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

WHERE dt.DUTYTABLEID = @DutyTableId;
";

        public const string SAVE_DUTYTABLE = @"
INSERT INTO MDUTYTABLE
(
    DUTYTABLECODE,
    DUTYTABLENAME,
    SORTORDER,
    STATUS,
    VERSION,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON,
    SOURCETYPE,
    TENANTID
)
VALUES
(
    @DutyTableCode,
    @DutyTableName,
    @DutyTableSortOrder,
    @DutyTableStatus,
    @DutyTableVersion,
    @DutyTableCreatedById,
    @DutyTableCreatedOn,
    @DutyTableModifiedById,
    @DutyTableModifiedOn,
    @DutyTableSourceType,
    @DutyTableTenantId
);";

        public const string UPDATE_DUTYTABLE = @"
UPDATE MDUTYTABLE
SET
    DUTYTABLECODE = @DutyTableCode,
    DUTYTABLENAME = @DutyTableName,
    SORTORDER = @DutyTableSortOrder,
    STATUS = @DutyTableStatus,
    VERSION = @DutyTableVersion,
    MODIFIEDBYID = @DutyTableModifiedById,
    MODIFIEDON = @DutyTableModifiedOn,
    SOURCETYPE = @DutyTableSourceType
WHERE
    DUTYTABLEID = @DutyTableId;";

        public const string DELETE_DUTYTABLE = @"
DELETE FROM MDUTYTABLE
WHERE DUTYTABLEID = @DutyTableId;";

        public const string GET_SELECTLIST_DUTYTABLE = @"
WITH DutyTable AS
(
    SELECT
        dt.DUTYTABLEID          AS DutyTableId,
        dt.DUTYTABLECODE        AS DutyTableCode,
        dt.DUTYTABLENAME        AS DutyTableName,

        dt.SORTORDER            AS DutyTableSortOrder,
        dt.STATUS               AS DutyTableStatus,
        dt.VERSION              AS DutyTableVersion,

        dt.CREATEDBYID          AS DutyTableCreatedById,
        cb.USERNAME             AS DutyTableCreatedByName,
        dt.CREATEDON            AS DutyTableCreatedOn,

        dt.MODIFIEDBYID         AS DutyTableModifiedById,
        mb.USERNAME             AS DutyTableModifiedByName,
        dt.MODIFIEDON           AS DutyTableModifiedOn,

        dt.SOURCETYPE           AS DutyTableSourceType,
        dt.TENANTID             AS DutyTableTenantId,

        ROW_NUMBER() OVER (ORDER BY dt.DUTYTABLEID) AS RowNum

    FROM dbo.MDUTYTABLE dt

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

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

SELECT
    DutyTableId,
    DutyTableCode,
    DutyTableName,
    DutyTableSortOrder,
    DutyTableStatus,
    DutyTableVersion,
    DutyTableCreatedById,
    DutyTableCreatedByName,
    DutyTableCreatedOn,
    DutyTableModifiedById,
    DutyTableModifiedByName,
    DutyTableModifiedOn,
    DutyTableSourceType,
    DutyTableTenantId,
    RowNum
FROM DutyTable
WHERE
    (@firstnumber = -1 AND @maxresult = -1)
    OR (RowNum BETWEEN @firstnumber AND @maxresult);";

        public const string GET_DUTYTABLE_LIST = @"
SELECT
    dt.DUTYTABLEID          AS DutyTableId,
    dt.DUTYTABLECODE        AS DutyTableCode,
    dt.DUTYTABLENAME        AS DutyTableName,

    dt.SORTORDER            AS DutyTableSortOrder,
    dt.STATUS               AS DutyTableStatus,
    dt.VERSION              AS DutyTableVersion,

    dt.CREATEDBYID          AS DutyTableCreatedById,
    dt.CREATEDON            AS DutyTableCreatedOn,

    dt.MODIFIEDBYID         AS DutyTableModifiedById,
    dt.MODIFIEDON           AS DutyTableModifiedOn,

    dt.SOURCETYPE           AS DutyTableSourceType,
    dt.TENANTID             AS DutyTableTenantId

FROM dbo.MDUTYTABLE dt;";
    }
}