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

namespace PayRollDAL.Query.GratuityGroup
{
    public static class GratuityGroupQB
    {
        // ── Picklist (paged, dual-dialect) ──────────────────────────────────
        // Indexes recommended:
        //   MGRATUITYGROUP: IX_MGRATUITYGROUP_CODE (GRATUITYGROUPCODE), IX_MGRATUITYGROUP_NAME (GRATUITYGROUPNAME)

        public const string GET_SELECTLIST_GRATUITYGROUP_SQL = @"
WITH Paged AS (
    SELECT A.GRATUITYGROUPID   AS Id,
           A.GRATUITYGROUPCODE AS Code,
           A.GRATUITYGROUPNAME AS Name,
           ROW_NUMBER() OVER (ORDER BY A.GRATUITYGROUPNAME) AS RowNum
    FROM   MGRATUITYGROUP A
    WHERE  1 = 1 /*FILTER*/
)
SELECT Id, Code, Name
FROM   Paged
WHERE  (@firstnumber = -1 AND @maxresult = -1)
   OR  (RowNum BETWEEN @firstnumber AND @maxresult);";

        public const string GET_SELECTLIST_GRATUITYGROUP_PG = @"
WITH Paged AS (
    SELECT A.GRATUITYGROUPID   AS ""Id"",
           A.GRATUITYGROUPCODE AS ""Code"",
           A.GRATUITYGROUPNAME AS ""Name"",
           ROW_NUMBER() OVER (ORDER BY A.GRATUITYGROUPNAME) AS ""RowNum""
    FROM   MGRATUITYGROUP A
    WHERE  1 = 1 /*FILTER*/
)
SELECT ""Id"", ""Code"", ""Name""
FROM   Paged
WHERE  (:firstnumber = -1 AND :maxresult = -1)
   OR  (""RowNum"" BETWEEN :firstnumber AND :maxresult);";

        public const string GET_SELECTLIST_GRATUITYGROUP_COUNT_SQL = @"
SELECT COUNT(*)
FROM   MGRATUITYGROUP A
WHERE  1 = 1 /*FILTER*/;";

        public const string GET_SELECTLIST_GRATUITYGROUP_COUNT_PG = @"
SELECT COUNT(*)
FROM   MGRATUITYGROUP A
WHERE  1 = 1 /*FILTER*/;";

        // ── Get single record (header) ──────────────────────────────────────

        public const string GET_GRATUITYGROUP_SQL = @"
SELECT
    A.GRATUITYGROUPID              AS GratuityGroupId,
    A.GRATUITYGROUPCODE            AS GratuityGroupCode,
    A.GRATUITYGROUPNAME            AS GratuityGroupName,
    A.ELIGIBILITYDAYS              AS GratuityGroupEligibilityDays,
    A.ELIGIBILITYYEARS             AS GratuityGroupEligibilityYears,
    A.SORTORDER                    AS GratuityGroupSortOrder,
    A.STATUS                       AS GratuityGroupStatus,
    A.VERSION                      AS GratuityGroupVersion,
    A.CREATEDBYID                  AS GratuityGroupCreatedById,
    A.CREATEDON                    AS GratuityGroupCreatedOn,
    A.MODIFIEDBYID                 AS GratuityGroupModifiedById,
    A.MODIFIEDON                   AS GratuityGroupModifiedOn
FROM MGRATUITYGROUP A
WHERE A.GRATUITYGROUPID = @gratuitygroupid;";

        public const string GET_GRATUITYGROUP_PG = @"
SELECT
    A.""GRATUITYGROUPID""              AS ""GratuityGroupId"",
    A.""GRATUITYGROUPCODE""            AS ""GratuityGroupCode"",
    A.""GRATUITYGROUPNAME""            AS ""GratuityGroupName"",
    A.""ELIGIBILITYDAYS""              AS ""GratuityGroupEligibilityDays"",
    A.""ELIGIBILITYYEARS""             AS ""GratuityGroupEligibilityYears"",
    A.""SORTORDER""                    AS ""GratuityGroupSortOrder"",
    A.""STATUS""                       AS ""GratuityGroupStatus"",
    A.""VERSION""                      AS ""GratuityGroupVersion"",
    A.""CREATEDBYID""                  AS ""GratuityGroupCreatedById"",
    A.""CREATEDON""                    AS ""GratuityGroupCreatedOn"",
    A.""MODIFIEDBYID""                 AS ""GratuityGroupModifiedById"",
    A.""MODIFIEDON""                   AS ""GratuityGroupModifiedOn""
FROM ""MGRATUITYGROUP"" A
WHERE A.""GRATUITYGROUPID"" = :gratuitygroupid;";

        // ── Get detail rows (joined to MFORMULA for BasedOn) ────────────────

        public const string GET_GRATUITYGROUPDETAIL_SQL = @"
SELECT
    D.GRATUITYGROUPDETAILID    AS GratuityGroupDetailId,
    D.GRATUITYGROUPID          AS GratuityGroupId,
    D.SLNO                     AS GratuityGroupDetailSlNo,
    D.YEARSFROM                AS GratuityGroupDetailYearsFrom,
    D.YEARSTO                  AS GratuityGroupDetailYearsTo,
    D.NOOFDAYS                 AS GratuityGroupDetailNoOfDays,
    D.BASEDONID                AS BasedOnId,
    F.FORMULACODE               AS BasedOnCode,
    F.FORMULANAME               AS BasedOnName
FROM MGRATUITYGROUPDETAIL D
LEFT JOIN MFORMULA F ON F.FORMULAID = D.BASEDONID
WHERE D.GRATUITYGROUPID = @gratuitygroupid
ORDER BY D.SLNO;";

        public const string GET_GRATUITYGROUPDETAIL_PG = @"
SELECT
    D.""GRATUITYGROUPDETAILID""    AS ""GratuityGroupDetailId"",
    D.""GRATUITYGROUPID""          AS ""GratuityGroupId"",
    D.""SLNO""                     AS ""GratuityGroupDetailSlNo"",
    D.""YEARSFROM""                AS ""GratuityGroupDetailYearsFrom"",
    D.""YEARSTO""                  AS ""GratuityGroupDetailYearsTo"",
    D.""NOOFDAYS""                 AS ""GratuityGroupDetailNoOfDays"",
    D.""BASEDONID""                AS ""BasedOnId"",
    F.""FORMULACODE""              AS ""BasedOnCode"",
    F.""FORMULANAME""              AS ""BasedOnName""
FROM ""MGRATUITYGROUPDETAIL"" D
LEFT JOIN ""MFORMULA"" F ON F.""FORMULAID"" = D.""BASEDONID""
WHERE D.""GRATUITYGROUPID"" = :gratuitygroupid
ORDER BY D.""SLNO"";";

        // ── Save (insert) header ─────────────────────────────────────────────

        public const string SAVE_GRATUITYGROUP_SQL = @"INSERT INTO MGRATUITYGROUP
(
    GRATUITYGROUPID, GRATUITYGROUPCODE, GRATUITYGROUPNAME, ELIGIBILITYDAYS, ELIGIBILITYYEARS,
    SORTORDER, STATUS, VERSION, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
)
VALUES
(
    @GratuityGroupId, @GratuityGroupCode, @GratuityGroupName, @GratuityGroupEligibilityDays, @GratuityGroupEligibilityYears,
    @GratuityGroupSortOrder, @GratuityGroupStatus, 1, @GratuityGroupCreatedById, @GratuityGroupCreatedOn, @GratuityGroupModifiedById, @GratuityGroupModifiedOn
);";

        public const string SAVE_GRATUITYGROUP_PG = @"INSERT INTO ""MGRATUITYGROUP""
(
    ""GRATUITYGROUPID"", ""GRATUITYGROUPCODE"", ""GRATUITYGROUPNAME"", ""ELIGIBILITYDAYS"", ""ELIGIBILITYYEARS"",
    ""SORTORDER"", ""STATUS"", ""VERSION"", ""CREATEDBYID"", ""CREATEDON"", ""MODIFIEDBYID"", ""MODIFIEDON""
)
VALUES
(
    :GratuityGroupId, :GratuityGroupCode, :GratuityGroupName, :GratuityGroupEligibilityDays, :GratuityGroupEligibilityYears,
    :GratuityGroupSortOrder, :GratuityGroupStatus, 1, :GratuityGroupCreatedById, :GratuityGroupCreatedOn, :GratuityGroupModifiedById, :GratuityGroupModifiedOn
);";

        // ── Update header ────────────────────────────────────────────────────

        public const string UPDATE_GRATUITYGROUP_SQL = @"UPDATE MGRATUITYGROUP
SET
    GRATUITYGROUPCODE   = @GratuityGroupCode,
    GRATUITYGROUPNAME   = @GratuityGroupName,
    ELIGIBILITYDAYS     = @GratuityGroupEligibilityDays,
    ELIGIBILITYYEARS    = @GratuityGroupEligibilityYears,
    SORTORDER           = @GratuityGroupSortOrder,
    STATUS              = @GratuityGroupStatus,
    VERSION             = VERSION + 1,
    MODIFIEDBYID        = @GratuityGroupModifiedById,
    MODIFIEDON          = @GratuityGroupModifiedOn
WHERE GRATUITYGROUPID = @GratuityGroupId;";

        public const string UPDATE_GRATUITYGROUP_PG = @"UPDATE ""MGRATUITYGROUP""
SET
    ""GRATUITYGROUPCODE""   = :GratuityGroupCode,
    ""GRATUITYGROUPNAME""   = :GratuityGroupName,
    ""ELIGIBILITYDAYS""     = :GratuityGroupEligibilityDays,
    ""ELIGIBILITYYEARS""    = :GratuityGroupEligibilityYears,
    ""SORTORDER""           = :GratuityGroupSortOrder,
    ""STATUS""              = :GratuityGroupStatus,
    ""VERSION""             = ""VERSION"" + 1,
    ""MODIFIEDBYID""        = :GratuityGroupModifiedById,
    ""MODIFIEDON""          = :GratuityGroupModifiedOn
WHERE ""GRATUITYGROUPID"" = :GratuityGroupId;";

        // ── Save (insert) detail row ─────────────────────────────────────────

        public const string SAVE_GRATUITYGROUPDETAIL_SQL = @"INSERT INTO MGRATUITYGROUPDETAIL
(
    GRATUITYGROUPDETAILID, GRATUITYGROUPID, SLNO, YEARSFROM, YEARSTO, NOOFDAYS, BASEDONID
)
VALUES
(
    @GratuityGroupDetailId, @GratuityGroupId, @GratuityGroupDetailSlNo, @GratuityGroupDetailYearsFrom, @GratuityGroupDetailYearsTo, @GratuityGroupDetailNoOfDays, @BasedOnId
);";

        public const string SAVE_GRATUITYGROUPDETAIL_PG = @"INSERT INTO ""MGRATUITYGROUPDETAIL""
(
    ""GRATUITYGROUPDETAILID"", ""GRATUITYGROUPID"", ""SLNO"", ""YEARSFROM"", ""YEARSTO"", ""NOOFDAYS"", ""BASEDONID""
)
VALUES
(
    :GratuityGroupDetailId, :GratuityGroupId, :GratuityGroupDetailSlNo, :GratuityGroupDetailYearsFrom, :GratuityGroupDetailYearsTo, :GratuityGroupDetailNoOfDays, :BasedOnId
);";

        // ── Delete detail rows (parameterized, not string-concatenated) ─────

        public const string DELETE_GRATUITYGROUPDETAIL_SQL = @"DELETE FROM MGRATUITYGROUPDETAIL WHERE GRATUITYGROUPID = @gratuitygroupid;";

        public const string DELETE_GRATUITYGROUPDETAIL_PG = @"DELETE FROM ""MGRATUITYGROUPDETAIL"" WHERE ""GRATUITYGROUPID"" = :gratuitygroupid;";

        // ── Delete header ────────────────────────────────────────────────────

        public const string DELETE_GRATUITYGROUP_SQL = @"DELETE FROM MGRATUITYGROUP WHERE GRATUITYGROUPID = @gratuitygroupid;";

        public const string DELETE_GRATUITYGROUP_PG = @"DELETE FROM ""MGRATUITYGROUP"" WHERE ""GRATUITYGROUPID"" = :gratuitygroupid;";

        // ── List (unpaged, header fields only, SearchText/Status filter) ────

        public const string GET_GRATUITYGROUP_LIST_SQL = @"
SELECT
    A.GRATUITYGROUPID              AS GratuityGroupId,
    A.GRATUITYGROUPCODE            AS GratuityGroupCode,
    A.GRATUITYGROUPNAME            AS GratuityGroupName,
    A.ELIGIBILITYDAYS              AS GratuityGroupEligibilityDays,
    A.ELIGIBILITYYEARS             AS GratuityGroupEligibilityYears,
    A.SORTORDER                    AS GratuityGroupSortOrder,
    A.STATUS                       AS GratuityGroupStatus,
    A.VERSION                      AS GratuityGroupVersion,
    A.CREATEDBYID                  AS GratuityGroupCreatedById,
    A.CREATEDON                    AS GratuityGroupCreatedOn,
    A.MODIFIEDBYID                 AS GratuityGroupModifiedById,
    A.MODIFIEDON                   AS GratuityGroupModifiedOn
FROM MGRATUITYGROUP A
WHERE
    (@SearchText IS NULL OR @SearchText = ''
     OR A.GRATUITYGROUPCODE LIKE '%' + @SearchText + '%'
     OR A.GRATUITYGROUPNAME LIKE '%' + @SearchText + '%')
    AND (@StatusEquals IS NULL OR A.STATUS = @StatusEquals)
    AND (@StatusNotEquals IS NULL OR A.STATUS <> @StatusNotEquals)
ORDER BY A.GRATUITYGROUPNAME;";

        public const string GET_GRATUITYGROUP_LIST_PG = @"
SELECT
    A.""GRATUITYGROUPID""              AS ""GratuityGroupId"",
    A.""GRATUITYGROUPCODE""            AS ""GratuityGroupCode"",
    A.""GRATUITYGROUPNAME""            AS ""GratuityGroupName"",
    A.""ELIGIBILITYDAYS""              AS ""GratuityGroupEligibilityDays"",
    A.""ELIGIBILITYYEARS""             AS ""GratuityGroupEligibilityYears"",
    A.""SORTORDER""                    AS ""GratuityGroupSortOrder"",
    A.""STATUS""                       AS ""GratuityGroupStatus"",
    A.""VERSION""                      AS ""GratuityGroupVersion"",
    A.""CREATEDBYID""                  AS ""GratuityGroupCreatedById"",
    A.""CREATEDON""                    AS ""GratuityGroupCreatedOn"",
    A.""MODIFIEDBYID""                 AS ""GratuityGroupModifiedById"",
    A.""MODIFIEDON""                   AS ""GratuityGroupModifiedOn""
FROM ""MGRATUITYGROUP"" A
WHERE
    (:SearchText IS NULL OR :SearchText = ''
     OR A.""GRATUITYGROUPCODE"" LIKE '%' || :SearchText || '%'
     OR A.""GRATUITYGROUPNAME"" LIKE '%' || :SearchText || '%')
    AND (:StatusEquals IS NULL OR A.""STATUS"" = :StatusEquals)
    AND (:StatusNotEquals IS NULL OR A.""STATUS"" <> :StatusNotEquals)
ORDER BY A.""GRATUITYGROUPNAME"";";
    }
}