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

namespace PayRollDAL.Query.PayGroup
{
    public class PayGroupQB
    {
        public const string GET_SELECTLIST_PAYGROUP = @"
    WITH PagedPayGroup AS (
        SELECT
            PAYGROUPID AS Id,
            PAYGROUPCODE AS Code,
            PAYGROUPNAME AS Name,
            ROW_NUMBER() OVER (ORDER BY PAYGROUPID) AS RowNum
        FROM MPAYGROUP
    )
    SELECT
        Id,
        Code,
        Name
    FROM PagedPayGroup
    WHERE
        (@firstnumber = -1 AND @maxresult = -1)
        OR
        (RowNum BETWEEN @firstnumber AND @maxresult);
";

        // ── Get single record ────────────────────────────────────────────

        public const string GET_PAYGROUP = @"
    SELECT
        PG.PAYGROUPID       AS PayGroupId,
        PG.PAYGROUPCODE     AS PayGroupCode,
        PG.PAYGROUPNAME     AS PayGroupName,
        PG.SORTORDER        AS PayGroupSortOrder,
        PG.STATUS           AS PayGroupStatus,
        PG.VERSION          AS PayGroupVersion,
        PG.CREATEDBYID      AS PayGroupCreatedById,
        PG.CREATEDON        AS PayGroupCreatedOn,
        PG.MODIFIEDBYID     AS PayGroupModifiedById,
        PG.MODIFIEDON       AS PayGroupModifiedOn
    FROM MPAYGROUP PG
    WHERE PG.PAYGROUPID = @paygroupid;";

        // ── Insert ────────────────────────────────────────────────────────

        public const string SAVE_PAYGROUP = @"INSERT INTO MPAYGROUP
(
    PAYGROUPID, PAYGROUPCODE, PAYGROUPNAME, SORTORDER, STATUS, VERSION,
    CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
)
VALUES
(
    @PayGroupId, @PayGroupCode, @PayGroupName, @PayGroupSortOrder, @PayGroupStatus, 1,
    @PayGroupCreatedById, @PayGroupCreatedOn, @PayGroupModifiedById, @PayGroupModifiedOn
);";

        // ── Update ───────────────────────────────────────────────────────

        public const string UPDATE_PAYGROUP = @"UPDATE MPAYGROUP
SET
    PAYGROUPCODE     = @PayGroupCode,
    PAYGROUPNAME     = @PayGroupName,
    SORTORDER        = @PayGroupSortOrder,
    STATUS           = @PayGroupStatus,
    VERSION          = VERSION + 1,
    MODIFIEDBYID     = @PayGroupModifiedById,
    MODIFIEDON       = @PayGroupModifiedOn
WHERE PAYGROUPID = @PayGroupId;";

        // ── Delete ───────────────────────────────────────────────────────

        public const string DELETE_PAYGROUP = @"DELETE FROM MPAYGROUP WHERE PAYGROUPID = @paygroupid;";

        // ── List (unpaged, criteria-filtered) ───────────────────────────────

        public const string GET_PAYGROUP_LIST = @"
    SELECT
        PG.PAYGROUPID       AS PayGroupId,
        PG.PAYGROUPCODE     AS PayGroupCode,
        PG.PAYGROUPNAME     AS PayGroupName,
        PG.SORTORDER        AS PayGroupSortOrder,
        PG.STATUS           AS PayGroupStatus,
        PG.VERSION          AS PayGroupVersion,
        PG.CREATEDBYID      AS PayGroupCreatedById,
        PG.CREATEDON        AS PayGroupCreatedOn,
        PG.MODIFIEDBYID     AS PayGroupModifiedById,
        PG.MODIFIEDON       AS PayGroupModifiedOn
    FROM MPAYGROUP PG
    WHERE
        (@SearchText IS NULL OR @SearchText = ''
         OR PG.PAYGROUPCODE LIKE '%' + @SearchText + '%'
         OR PG.PAYGROUPNAME LIKE '%' + @SearchText + '%')
        AND (@StatusEquals IS NULL OR PG.STATUS = @StatusEquals)
        AND (@StatusNotEquals IS NULL OR PG.STATUS <> @StatusNotEquals)
    ORDER BY PG.PAYGROUPCODE;";
    }
}