using System;
using System.Collections.Generic;
using System.Text;

namespace PayRollDAL.Query.AdditionDeduction
{
    public class AdditionDeductionQB
    {
        // GB4 parity: AdditionDeduction.svc GetAdditionDeductionFields — AdditionDeductionBLL.cs:1169,
        // AdditionDeductionQueryBuilder.GETADDITIONDEDUCTION_FIELDS. Distinct from
        // GetAdditionDeductionPicklist (PayProcess module) — returns addon-field metadata
        // (EntityCode/OperationType/FieldName/DataType/Remarks/LovTypeId) for Nature IN (2,3,4)
        // fields applicable to the given PayConfigurationId(s), driving dynamic addon-form rendering.
        // Columns confirmed against SAVE_ADDITIONDEDUCTION/UPDATE_ADDITIONDEDUCTION above (same table).
        // GB4 parity: AdditionDeductionQueryBuilder.GETADDITIONDEDUCTION_FIELDS. GB4 splits
        // MAD.PAYCONFIGURATIONIDS via comma2row/CROSS APPLY and filters the exploded row (b.data)
        // against the caller's PayIds criteria value using ApplyCriteria's Equal-as-IN-list
        // semantics (b.data IN (1,2,3)) — i.e. PayIds structurally supports a comma-separated
        // multi-value match, not just a single id. :payidsfilter is replaced with an IN-list
        // clause built from the parsed PayIds values (parameter name PayId0, PayId1, ...).
        public const string GET_ADDITIONDEDUCTION_FIELDS = @"
SELECT
    MAD.FIELDCODE AS EntityCode,
    MAD.OPERATIONTYPE AS OperationType,
    MAD.FIELDNAME AS AddOnFieldFieldName,
    CAST(CASE WHEN MAD.FIELDDATATYPE = 0 THEN 3
              WHEN MAD.FIELDDATATYPE = 1 THEN 1
              WHEN MAD.FIELDDATATYPE = 2 THEN 6
              ELSE 5 END AS TINYINT) AS AddOnFieldDataType,
    MAD.REMARKS AS AddOnFieldRemarks,
    MAD.LOVTYPEID AS LovTypeId
FROM MADDITIONDEDUCTION MAD
CROSS APPLY (SELECT value AS DATA FROM STRING_SPLIT(MAD.PAYCONFIGURATIONIDS, ',')) b
WHERE MAD.NATURE IN (2, 3, 4)
:payidsfilter;";

        public const string GET_ADDITIONDEDUCTION_LIST = @"
WITH AdditionDeduction AS
(
    SELECT
        A.FIELDID                                                    AS AdditionDeductionId,
        A.FIELDCODE                                                  AS AdditionDeductionCode,
        A.FIELDNAME                                                  AS AdditionDeductionName,
        A.DISPLAYNAME                                                AS AdditionDeductionDisplayName,
        CASE A.FIELDDATATYPE
            WHEN 0 THEN 'DateTime'
            WHEN 1 THEN 'String'
            WHEN 2 THEN 'Number'
            WHEN 3 THEN 'ListOfValues'
            ELSE 'Unknown'
        END                                                          AS AdditionDeductionFieldDataTypeName,
        A.FIELDDATATYPE                                              AS AdditionDeductionFieldDataType,
        A.FIELDDATASIZE                                              AS AdditionDeductionFieldDataSize,
        CASE A.APPLICABLETYPE
            WHEN 0 THEN 'Overall'
            WHEN 1 THEN 'OULevel'
            WHEN 2 THEN 'PayConfigurationLevel'
            WHEN 3 THEN 'EmployeeGroup'
            WHEN 4 THEN 'DAGroupLevel'
            WHEN 5 THEN 'EmployeeLevel'
            ELSE 'Unknown'
        END                                                          AS AdditionDeductionApplicableTypeName,
        A.APPLICABLETYPE                                             AS AdditionDeductionApplicableType,
        CASE A.NATURE
            WHEN 0 THEN 'Fixed'
            WHEN 1 THEN 'Variable'
            WHEN 2 THEN 'WageRule'
            WHEN 3 THEN 'WageRuleAndFixed'
            WHEN 4 THEN 'WageRuleAndVariable'
            WHEN 5 THEN 'FixedOthers'
            WHEN 6 THEN 'MonthlyAttendance'
            WHEN 7 THEN 'Dynamic'
            ELSE 'Unknown'
        END                                                          AS AdditionDeductionNatureName,
        A.NATURE                                                     AS AdditionDeductionNature,
        A.DEFAULTVALUE                                               AS AdditionDeductionDefaultValue,
        A.REMARKS                                                    AS AdditionDeductionRemarks,
        A.ARREARAPPLICABLE                                           AS AdditionDeductionArrearApplicable,
        A.LOVTYPEID                                                  AS LovTypeId,
        L.LOVTYPECODE                                                AS LovTypeCode,
        L.LOVTYPENAME                                                AS LovTypeName,
        A.OPERATIONTYPE                                              AS AdditionDeductionOperationType,
        A.SPLITTYPE                                                  AS AdditionDeductionSplitType,
        A.SORTORDER                                                  AS AdditionDeductionSortOrder,
        A.STATUS                                                     AS AdditionDeductionStatus,
        A.VERSION                                                    AS AdditionDeductionVersion,
        A.CREATEDBYID                                                AS AdditionDeductionCreatedById,
        A.CREATEDON                                                  AS AdditionDeductionCreatedOn,
        A.MODIFIEDBYID                                               AS AdditionDeductionModifiedById,
        A.MODIFIEDON                                                 AS AdditionDeductionModifiedOn,
        CU.USERNAME                                                  AS AdditionDeductionCreatedByName,
        MU.USERNAME                                                  AS AdditionDeductionModifiedByName,
        A.ISPARTIALALLOWED                                           AS AdditionDeductionIsPartialAllowed,
        A.PAYCONFIGURATIONIDS                                        AS AdditionDeductionPayConfigurationIds,
        'NONE'                                                       AS AdditionDeductionPayConfigurationNames,
        A.FORMULAID                                                  AS FormulaId,
        F.FORMULACODE                                                AS FormulaCode,
        F.FORMULANAME                                                AS FormulaName,
        A.DISPLAYCODE                                                AS AdditionDeductionDisplayCode,
        ROW_NUMBER() OVER (ORDER BY A.SORTORDER, A.FIELDID)          AS RowNum
    FROM MADDITIONDEDUCTION A
    LEFT JOIN MLOVTYPE L ON A.LOVTYPEID = L.LOVTYPEID
    LEFT JOIN MUSER CU ON A.CREATEDBYID = CU.USERID
    LEFT JOIN MUSER MU ON A.MODIFIEDBYID = MU.USERID
    LEFT JOIN MFORMULA F ON A.FORMULAID = F.FORMULAID
    WHERE A.NATURE = @Nature
      AND A.APPLICABLETYPE = @ApplicableType
      AND A.FIELDID <> -1
      AND A.FIELDCODE <> 'NONE'
)
SELECT *
FROM AdditionDeduction
WHERE (@FirstNumber = -1 AND @MaxResult = -1)
   OR (RowNum > @FirstNumber AND RowNum <= @FirstNumber + @MaxResult)
ORDER BY RowNum;";

        public const string GET_ADDITIONDEDUCTION_COUNT = @"
SELECT COUNT(*) AS TotalCount
FROM MADDITIONDEDUCTION A
WHERE A.NATURE = @Nature
  AND A.APPLICABLETYPE = @ApplicableType
  AND A.FIELDID <> -1
  AND A.FIELDCODE <> 'NONE'
;";

        // GB4 parity: ADDITIONDEDUCTION.svc/SelectList — master picklist over MADDITIONDEDUCTION
        // (distinct from GetAdditionDeductionList's Nature/ApplicableType-scoped report, and from
        // PayProcess.GetAdditionDeductionPicklist which is scoped to fields already used in TADDITIONDEDUCTIONDETAIL).
        public const string GET_SELECTLIST_ADDITIONDEDUCTION = @"
WITH AdditionDeductionSelectList AS
(
    SELECT
        A.FIELDID     AS Id,
        A.DISPLAYCODE AS Code,
        A.DISPLAYNAME AS Name,
        A.FIELDCODE   AS FieldCode,
        A.FIELDNAME   AS FieldName,
        ROW_NUMBER() OVER (ORDER BY A.SORTORDER, A.FIELDID) AS RowNum
    FROM MADDITIONDEDUCTION A
    WHERE A.FIELDID <> -1
      AND A.FIELDCODE <> 'NONE'
)
SELECT Id, Code, Name, FieldCode, FieldName
FROM AdditionDeductionSelectList
WHERE (@FirstNumber = -1 AND @MaxResult = -1)
   OR (RowNum > @FirstNumber AND RowNum <= @FirstNumber + @MaxResult)
ORDER BY RowNum;";

        public const string GET_SELECTLIST_ADDITIONDEDUCTION_COUNT = @"
SELECT COUNT(*) AS TotalCount
FROM MADDITIONDEDUCTION A
WHERE A.FIELDID <> -1
  AND A.FIELDCODE <> 'NONE';";

        // Plain fixed-schema CRUD for MADDITIONDEDUCTION — deliberately does NOT replicate GB4's
        // dynamic ALTER TABLE ADD/DROP COLUMN EAV behavior. Column aliases below match
        // AdditionDeductionDTO's property names exactly (Dapper maps by name, case-insensitive).
        // No TENANTID column on this table, and no STATUS filter — matching the existing
        // GET_ADDITIONDEDUCTION_LIST/GET_SELECTLIST_ADDITIONDEDUCTION queries above.
        public const string GET_ADDITIONDEDUCTION = @"SELECT
                                                    A.FIELDID              AS AdditionDeductionId,
                                                    A.FIELDCODE            AS AdditionDeductionCode,
                                                    A.FIELDNAME            AS AdditionDeductionName,
                                                    A.DISPLAYNAME          AS AdditionDeductionDisplayName,
                                                    A.FIELDDATATYPE        AS AdditionDeductionFieldDataType,
                                                    A.FIELDDATASIZE        AS AdditionDeductionFieldDataSize,
                                                    A.APPLICABLETYPE       AS AdditionDeductionApplicableType,
                                                    A.NATURE               AS AdditionDeductionNature,
                                                    A.DEFAULTVALUE         AS AdditionDeductionDefaultValue,
                                                    A.REMARKS              AS AdditionDeductionRemarks,
                                                    A.ARREARAPPLICABLE     AS AdditionDeductionArrearApplicable,
                                                    A.LOVTYPEID            AS LovTypeId,
                                                    A.OPERATIONTYPE        AS AdditionDeductionOperationType,
                                                    A.SPLITTYPE            AS AdditionDeductionSplitType,
                                                    A.SORTORDER            AS AdditionDeductionSortOrder,
                                                    A.STATUS               AS AdditionDeductionStatus,
                                                    A.VERSION              AS AdditionDeductionVersion,
                                                    A.CREATEDBYID          AS AdditionDeductionCreatedById,
                                                    A.CREATEDON            AS AdditionDeductionCreatedOn,
                                                    A.MODIFIEDBYID         AS AdditionDeductionModifiedById,
                                                    A.MODIFIEDON           AS AdditionDeductionModifiedOn,
                                                    A.ISPARTIALALLOWED     AS AdditionDeductionIsPartialAllowed,
                                                    A.PAYCONFIGURATIONIDS  AS AdditionDeductionPayConfigurationIds,
                                                    A.FORMULAID            AS FormulaId,
                                                    A.DISPLAYCODE          AS AdditionDeductionDisplayCode
                                                FROM MADDITIONDEDUCTION A
                                                WHERE A.FIELDID = @AdditionDeductionId;";

        public const string SAVE_ADDITIONDEDUCTION = @"INSERT INTO MADDITIONDEDUCTION
                                                (
                                                    FIELDID,
                                                    FIELDCODE,
                                                    FIELDNAME,
                                                    DISPLAYNAME,
                                                    FIELDDATATYPE,
                                                    FIELDDATASIZE,
                                                    APPLICABLETYPE,
                                                    NATURE,
                                                    DEFAULTVALUE,
                                                    REMARKS,
                                                    ARREARAPPLICABLE,
                                                    LOVTYPEID,
                                                    OPERATIONTYPE,
                                                    SPLITTYPE,
                                                    SORTORDER,
                                                    STATUS,
                                                    VERSION,
                                                    CREATEDBYID,
                                                    CREATEDON,
                                                    MODIFIEDBYID,
                                                    MODIFIEDON,
                                                    ISPARTIALALLOWED,
                                                    PAYCONFIGURATIONIDS,
                                                    FORMULAID,
                                                    DISPLAYCODE
                                                )
                                                VALUES
                                                (
                                                    @AdditionDeductionId,
                                                    @AdditionDeductionCode,
                                                    @AdditionDeductionName,
                                                    @AdditionDeductionDisplayName,
                                                    @AdditionDeductionFieldDataType,
                                                    @AdditionDeductionFieldDataSize,
                                                    @AdditionDeductionApplicableType,
                                                    @AdditionDeductionNature,
                                                    @AdditionDeductionDefaultValue,
                                                    @AdditionDeductionRemarks,
                                                    @AdditionDeductionArrearApplicable,
                                                    @LovTypeId,
                                                    @AdditionDeductionOperationType,
                                                    @AdditionDeductionSplitType,
                                                    @AdditionDeductionSortOrder,
                                                    @AdditionDeductionStatus,
                                                    @AdditionDeductionVersion,
                                                    @AdditionDeductionCreatedById,
                                                    @AdditionDeductionCreatedOn,
                                                    @AdditionDeductionModifiedById,
                                                    @AdditionDeductionModifiedOn,
                                                    @AdditionDeductionIsPartialAllowed,
                                                    @AdditionDeductionPayConfigurationIds,
                                                    @FormulaId,
                                                    @AdditionDeductionDisplayCode
                                                );";

        public const string UPDATE_ADDITIONDEDUCTION = @"UPDATE MADDITIONDEDUCTION
                                                    SET
                                                        FIELDCODE = @AdditionDeductionCode,
                                                        FIELDNAME = @AdditionDeductionName,
                                                        DISPLAYNAME = @AdditionDeductionDisplayName,
                                                        FIELDDATATYPE = @AdditionDeductionFieldDataType,
                                                        FIELDDATASIZE = @AdditionDeductionFieldDataSize,
                                                        APPLICABLETYPE = @AdditionDeductionApplicableType,
                                                        NATURE = @AdditionDeductionNature,
                                                        DEFAULTVALUE = @AdditionDeductionDefaultValue,
                                                        REMARKS = @AdditionDeductionRemarks,
                                                        ARREARAPPLICABLE = @AdditionDeductionArrearApplicable,
                                                        LOVTYPEID = @LovTypeId,
                                                        OPERATIONTYPE = @AdditionDeductionOperationType,
                                                        SPLITTYPE = @AdditionDeductionSplitType,
                                                        SORTORDER = @AdditionDeductionSortOrder,
                                                        STATUS = @AdditionDeductionStatus,
                                                        MODIFIEDBYID = @AdditionDeductionModifiedById,
                                                        MODIFIEDON = @AdditionDeductionModifiedOn,
                                                        ISPARTIALALLOWED = @AdditionDeductionIsPartialAllowed,
                                                        PAYCONFIGURATIONIDS = @AdditionDeductionPayConfigurationIds,
                                                        FORMULAID = @FormulaId,
                                                        DISPLAYCODE = @AdditionDeductionDisplayCode
                                                    WHERE
                                                        FIELDID = @AdditionDeductionId;";

        public const string DELETE_ADDITIONDEDUCTION = @"DELETE FROM MADDITIONDEDUCTION
                                                    WHERE FIELDID = @AdditionDeductionId;";
    }
}