namespace PayRollDAL.Query.AdditionDeductionDetail
{
    // Plain fixed-schema CRUD for TADDITIONDEDUCTIONDETAIL — deliberately does NOT port GB4's
    // LMW/CSV bulk-import side effects (SavePeriodicOrPayRevsion) or the cross-entity FieldCode ->
    // MADDITIONDEDUCTION name/remarks enrichment previously done in
    // AdditionDeductionDetailBLL.Get (GB4). Column set taken from
    // GB4Solution/DAL/PayrollDAL/HibernateMapFile/AdditionDeductionDetail.hbm.xml. Column aliases
    // below match AdditionDeductionDetailDTO's property names exactly (Dapper maps by name,
    // case-insensitive). Only columns that physically exist on TADDITIONDEDUCTIONDETAIL are
    // selected — the DTO's denormalized Code/Name lookup fields (OUCode/OUName,
    // PayConfigurationCode/Name, PayGroupCode/Name, DAGroupCode/Name, EmployeeCode/Name,
    // PayPeriodCode/Name, FieldActualName/FieldDescription) are populated by the caller, not by
    // these queries.
    public static class AdditionDeductionDetailQB
    {
        public const string GET_ADDITIONDEDUCTIONDETAIL = @"SELECT
                                                    T.ADDITIONDEDUCTIONDETAILID  AS AdditionDeductionDetailId,
                                                    T.PERIODTYPE                 AS AdditionDeductionDetailPeriodType,
                                                    T.PAYREVISIONNATURE          AS AdditionDeductionDetailPayRevisionNature,
                                                    T.APPLICABLE                 AS AdditionDeductionDetailApplicable,
                                                    T.OUID                       AS OUId,
                                                    T.PAYCONFIGURATIONID         AS PayConfigurationId,
                                                    T.PAYGROUPID                 AS PayGroupId,
                                                    T.DAGROUPID                  AS DAGroupId,
                                                    T.EMPLOYEEID                 AS EmployeeId,
                                                    T.PAYPERIODID                AS PayPeriodId,
                                                    T.FROMDATE                   AS AdditionDeductionDetailFromDate,
                                                    T.TODATE                     AS AdditionDeductionDetailToDate,
                                                    T.SLNO                       AS AdditionDeductionDetailSlNo,
                                                    T.FIELDID                    AS AdditionDeductionDetailFieldId,
                                                    T.FIELDCODE                  AS AdditionDeductionDetailFieldCode,
                                                    T.ISPARTIALALLOWED           AS AdditionDeductionDetailIsPartialAllowed,
                                                    T.SPLITTYPE                  AS AdditionDeductionDetailSplitType,
                                                    T.AMOUNT                     AS AdditionDeductionDetailAmount,
                                                    T.PROCESSEDAMOUNT            AS AdditionDeductionDetailProcessedAmount,
                                                    T.REFERENCE                  AS AdditionDeductionDetailReference,
                                                    T.REMARKS                    AS AdditionDeductionDetailRemarks,
                                                    T.PROCESSREMARKS             AS AdditionDeductionDetailProcessRemarks,
                                                    T.PAYPROCESSID               AS PayProcessId,
                                                    T.SL_NO                      AS AdditionDeductionDetailSL_NO,
                                                    T.SORTORDER                  AS AdditionDeductionDetailSortOrder,
                                                    T.STATUS                     AS AdditionDeductionDetailStatus,
                                                    T.SOURCETYPE                 AS AdditionDeductionDetailSourceType,
                                                    T.VERSION                    AS AdditionDeductionDetailVersion,
                                                    T.CREATEDBYID                AS AdditionDeductionDetailCreatedById,
                                                    T.CREATEDON                  AS AdditionDeductionDetailCreatedOn,
                                                    T.MODIFIEDBYID               AS AdditionDeductionDetailModifiedById,
                                                    T.MODIFIEDON                 AS AdditionDeductionDetailModifiedOn,
                                                    T.ADVANCEID                  AS AdvanceId
                                                FROM TADDITIONDEDUCTIONDETAIL T
                                                WHERE T.ADDITIONDEDUCTIONDETAILID = @AdditionDeductionDetailId;";

        public const string SAVE_ADDITIONDEDUCTIONDETAIL = @"INSERT INTO TADDITIONDEDUCTIONDETAIL
                                                (
                                                    ADDITIONDEDUCTIONDETAILID,
                                                    PERIODTYPE,
                                                    PAYREVISIONNATURE,
                                                    APPLICABLE,
                                                    OUID,
                                                    PAYCONFIGURATIONID,
                                                    PAYGROUPID,
                                                    DAGROUPID,
                                                    EMPLOYEEID,
                                                    PAYPERIODID,
                                                    FROMDATE,
                                                    TODATE,
                                                    SLNO,
                                                    FIELDID,
                                                    FIELDCODE,
                                                    ISPARTIALALLOWED,
                                                    SPLITTYPE,
                                                    AMOUNT,
                                                    PROCESSEDAMOUNT,
                                                    REFERENCE,
                                                    REMARKS,
                                                    PROCESSREMARKS,
                                                    PAYPROCESSID,
                                                    SL_NO,
                                                    SORTORDER,
                                                    STATUS,
                                                    SOURCETYPE,
                                                    VERSION,
                                                    CREATEDBYID,
                                                    CREATEDON,
                                                    MODIFIEDBYID,
                                                    MODIFIEDON,
                                                    ADVANCEID
                                                )
                                                VALUES
                                                (
                                                    @AdditionDeductionDetailId,
                                                    @AdditionDeductionDetailPeriodType,
                                                    @AdditionDeductionDetailPayRevisionNature,
                                                    @AdditionDeductionDetailApplicable,
                                                    @OUId,
                                                    @PayConfigurationId,
                                                    @PayGroupId,
                                                    @DAGroupId,
                                                    @EmployeeId,
                                                    @PayPeriodId,
                                                    @AdditionDeductionDetailFromDate,
                                                    @AdditionDeductionDetailToDate,
                                                    @AdditionDeductionDetailSlNo,
                                                    @AdditionDeductionDetailFieldId,
                                                    @AdditionDeductionDetailFieldCode,
                                                    @AdditionDeductionDetailIsPartialAllowed,
                                                    @AdditionDeductionDetailSplitType,
                                                    @AdditionDeductionDetailAmount,
                                                    @AdditionDeductionDetailProcessedAmount,
                                                    @AdditionDeductionDetailReference,
                                                    @AdditionDeductionDetailRemarks,
                                                    @AdditionDeductionDetailProcessRemarks,
                                                    @PayProcessId,
                                                    @AdditionDeductionDetailSL_NO,
                                                    @AdditionDeductionDetailSortOrder,
                                                    @AdditionDeductionDetailStatus,
                                                    @AdditionDeductionDetailSourceType,
                                                    @AdditionDeductionDetailVersion,
                                                    @AdditionDeductionDetailCreatedById,
                                                    @AdditionDeductionDetailCreatedOn,
                                                    @AdditionDeductionDetailModifiedById,
                                                    @AdditionDeductionDetailModifiedOn,
                                                    @AdvanceId
                                                );";

        public const string UPDATE_ADDITIONDEDUCTIONDETAIL = @"UPDATE TADDITIONDEDUCTIONDETAIL
                                                    SET
                                                        PERIODTYPE = @AdditionDeductionDetailPeriodType,
                                                        PAYREVISIONNATURE = @AdditionDeductionDetailPayRevisionNature,
                                                        APPLICABLE = @AdditionDeductionDetailApplicable,
                                                        OUID = @OUId,
                                                        PAYCONFIGURATIONID = @PayConfigurationId,
                                                        PAYGROUPID = @PayGroupId,
                                                        DAGROUPID = @DAGroupId,
                                                        EMPLOYEEID = @EmployeeId,
                                                        PAYPERIODID = @PayPeriodId,
                                                        FROMDATE = @AdditionDeductionDetailFromDate,
                                                        TODATE = @AdditionDeductionDetailToDate,
                                                        SLNO = @AdditionDeductionDetailSlNo,
                                                        FIELDID = @AdditionDeductionDetailFieldId,
                                                        FIELDCODE = @AdditionDeductionDetailFieldCode,
                                                        ISPARTIALALLOWED = @AdditionDeductionDetailIsPartialAllowed,
                                                        SPLITTYPE = @AdditionDeductionDetailSplitType,
                                                        AMOUNT = @AdditionDeductionDetailAmount,
                                                        PROCESSEDAMOUNT = @AdditionDeductionDetailProcessedAmount,
                                                        REFERENCE = @AdditionDeductionDetailReference,
                                                        REMARKS = @AdditionDeductionDetailRemarks,
                                                        PROCESSREMARKS = @AdditionDeductionDetailProcessRemarks,
                                                        PAYPROCESSID = @PayProcessId,
                                                        SL_NO = @AdditionDeductionDetailSL_NO,
                                                        SORTORDER = @AdditionDeductionDetailSortOrder,
                                                        STATUS = @AdditionDeductionDetailStatus,
                                                        SOURCETYPE = @AdditionDeductionDetailSourceType,
                                                        MODIFIEDBYID = @AdditionDeductionDetailModifiedById,
                                                        MODIFIEDON = @AdditionDeductionDetailModifiedOn,
                                                        ADVANCEID = @AdvanceId
                                                    WHERE
                                                        ADDITIONDEDUCTIONDETAILID = @AdditionDeductionDetailId;";

        public const string DELETE_ADDITIONDEDUCTIONDETAIL = @"DELETE FROM TADDITIONDEDUCTIONDETAIL
                                                    WHERE ADDITIONDEDUCTIONDETAILID = @AdditionDeductionDetailId;";

        // Slim Id/Code/Name-style picklist (EmployeeId/PayPeriodId along for filter context on the
        // client), paginated the same way as AdditionDeductionQB.GET_SELECTLIST_ADDITIONDEDUCTION.
        public const string GET_SELECTLIST_ADDITIONDEDUCTIONDETAIL = @"
WITH AdditionDeductionDetailSelectList AS
(
    SELECT
        T.ADDITIONDEDUCTIONDETAILID                          AS Id,
        T.FIELDCODE                                          AS Code,
        T.FIELDCODE                                          AS Name,
        T.EMPLOYEEID                                          AS EmployeeId,
        T.PAYPERIODID                                         AS PayPeriodId,
        ROW_NUMBER() OVER (ORDER BY T.ADDITIONDEDUCTIONDETAILID) AS RowNum
    FROM TADDITIONDEDUCTIONDETAIL T
)
SELECT Id, Code, Name, EmployeeId, PayPeriodId
FROM AdditionDeductionDetailSelectList
WHERE (@FirstNumber = -1 AND @MaxResult = -1)
   OR (RowNum > @FirstNumber AND RowNum <= @FirstNumber + @MaxResult)
ORDER BY RowNum;";

        public const string GET_SELECTLIST_ADDITIONDEDUCTIONDETAIL_COUNT = @"
SELECT COUNT(*) AS TotalCount
FROM TADDITIONDEDUCTIONDETAIL T;";

        // Full-row paged list, filterable by EmployeeId/PayPeriodId (-1 = no filter on that field) —
        // mirrors the natural query shape of GB4's AdditionDeductionDetailBLL.Get(EmployeeId,
        // PayperiodId, ...).
        public const string GET_ADDITIONDEDUCTIONDETAIL_LIST = @"
WITH AdditionDeductionDetail AS
(
    SELECT
        T.ADDITIONDEDUCTIONDETAILID  AS AdditionDeductionDetailId,
        T.PERIODTYPE                 AS AdditionDeductionDetailPeriodType,
        T.PAYREVISIONNATURE          AS AdditionDeductionDetailPayRevisionNature,
        T.APPLICABLE                 AS AdditionDeductionDetailApplicable,
        T.OUID                       AS OUId,
        T.PAYCONFIGURATIONID         AS PayConfigurationId,
        T.PAYGROUPID                 AS PayGroupId,
        T.DAGROUPID                  AS DAGroupId,
        T.EMPLOYEEID                 AS EmployeeId,
        T.PAYPERIODID                AS PayPeriodId,
        T.FROMDATE                   AS AdditionDeductionDetailFromDate,
        T.TODATE                     AS AdditionDeductionDetailToDate,
        T.SLNO                       AS AdditionDeductionDetailSlNo,
        T.FIELDID                    AS AdditionDeductionDetailFieldId,
        T.FIELDCODE                  AS AdditionDeductionDetailFieldCode,
        T.ISPARTIALALLOWED           AS AdditionDeductionDetailIsPartialAllowed,
        T.SPLITTYPE                  AS AdditionDeductionDetailSplitType,
        T.AMOUNT                     AS AdditionDeductionDetailAmount,
        T.PROCESSEDAMOUNT            AS AdditionDeductionDetailProcessedAmount,
        T.REFERENCE                  AS AdditionDeductionDetailReference,
        T.REMARKS                    AS AdditionDeductionDetailRemarks,
        T.PROCESSREMARKS             AS AdditionDeductionDetailProcessRemarks,
        T.PAYPROCESSID               AS PayProcessId,
        T.SL_NO                      AS AdditionDeductionDetailSL_NO,
        T.SORTORDER                  AS AdditionDeductionDetailSortOrder,
        T.STATUS                     AS AdditionDeductionDetailStatus,
        T.SOURCETYPE                 AS AdditionDeductionDetailSourceType,
        T.VERSION                    AS AdditionDeductionDetailVersion,
        T.CREATEDBYID                AS AdditionDeductionDetailCreatedById,
        T.CREATEDON                  AS AdditionDeductionDetailCreatedOn,
        T.MODIFIEDBYID               AS AdditionDeductionDetailModifiedById,
        T.MODIFIEDON                 AS AdditionDeductionDetailModifiedOn,
        T.ADVANCEID                  AS AdvanceId,
        ROW_NUMBER() OVER (ORDER BY T.ADDITIONDEDUCTIONDETAILID) AS RowNum
    FROM TADDITIONDEDUCTIONDETAIL T
    WHERE (@EmployeeId = -1 OR T.EMPLOYEEID = @EmployeeId)
      AND (@PayPeriodId = -1 OR T.PAYPERIODID = @PayPeriodId)
)
SELECT *
FROM AdditionDeductionDetail
WHERE (@FirstNumber = -1 AND @MaxResult = -1)
   OR (RowNum > @FirstNumber AND RowNum <= @FirstNumber + @MaxResult)
ORDER BY RowNum;";

        public const string GET_ADDITIONDEDUCTIONDETAIL_LIST_COUNT = @"
SELECT COUNT(*) AS TotalCount
FROM TADDITIONDEDUCTIONDETAIL T
WHERE (@EmployeeId = -1 OR T.EMPLOYEEID = @EmployeeId)
  AND (@PayPeriodId = -1 OR T.PAYPERIODID = @PayPeriodId);";
    }
}