namespace AccountsDAL.Query.CostCenterPattern
{
    public static class CostCenterPatternQB
    {
        public const string GET_COSTCENTERPATTERN = @"
            SELECT
                CP.COSTCENTERPATTERNID          AS CostCenterPatternId,
                CP.COSTCENTERPATTERNCODE        AS CostCenterPatternCode,
                CP.COSTCENTERPATTERNNAME        AS CostCenterPatternName,
                CP.REMARKS                      AS CostCenterPatternRemarks,
                CP.COSTCENTERTYPEID             AS CostCenterTypeId,
                CT.COSTCENTERTYPECODE           AS CostCenterTypeCode,
                CT.COSTCENTERTYPENAME           AS CostCenterTypeName,
                CP.SORTORDER                    AS CostCenterPatternSortOrder,
                CP.STATUS                       AS CostCenterPatternStatus,
                CP.VERSION                      AS CostCenterPatternVersion,
                CP.CREATEDBYID                  AS CostCenterPatternCreatedById,
                CP.CREATEDON                    AS CostCenterPatternCreatedOn,
                CP.MODIFIEDBYID                 AS CostCenterPatternModifiedById,
                CP.MODIFIEDON                   AS CostCenterPatternModifiedOn
            FROM   MCOSTCENTERPATTERN CP
            LEFT JOIN MCOSTCENTERTYPE CT ON CP.COSTCENTERTYPEID = CT.COSTCENTERTYPEID
            WHERE  CP.COSTCENTERPATTERNID = @CostCenterPatternId
            AND    CP.TENANTID            = @TenantId;";

        // Full-DTO cost center pattern list (CostCenterPatternDTO, not CostCenterPatternPicklistDTO) —
        // same column/join set as GET_COSTCENTERPATTERN (header only, no detail rows — mirrors that
        // query's own flat shape; CostCenterPatternDetailDTO rows are fetched separately in
        // GetCostCenterPattern via GET_COSTCENTERPATTERN_DETAILS, not joined here, so this List query
        // does not introduce N:1 row duplication) but for every matching row instead of one,
        // criteria-filtered (search text + status) like AccountQB.GET_ACCOUNT_LIST, unpaged (no
        // FirstNumber/MaxResult, fixed ORDER BY, no dynamic OrderBy).
        // Deviation from the Account/AccountGroup/AccountSchedule/Insurance/Instrument List queries:
        // MCOSTCENTERPATTERN rows are tenant-scoped and GET_COSTCENTERPATTERN above filters by
        // TENANTID — dropping that filter here would leak rows across tenants, so @TenantId is kept
        // as an additional AND condition alongside the SearchText/StatusEquals/StatusNotEquals filters.
        public const string GET_COSTCENTERPATTERN_LIST = @"
            SELECT
                CP.COSTCENTERPATTERNID          AS CostCenterPatternId,
                CP.COSTCENTERPATTERNCODE        AS CostCenterPatternCode,
                CP.COSTCENTERPATTERNNAME        AS CostCenterPatternName,
                CP.REMARKS                      AS CostCenterPatternRemarks,
                CP.COSTCENTERTYPEID             AS CostCenterTypeId,
                CT.COSTCENTERTYPECODE           AS CostCenterTypeCode,
                CT.COSTCENTERTYPENAME           AS CostCenterTypeName,
                CP.SORTORDER                    AS CostCenterPatternSortOrder,
                CP.STATUS                       AS CostCenterPatternStatus,
                CP.VERSION                      AS CostCenterPatternVersion,
                CP.CREATEDBYID                  AS CostCenterPatternCreatedById,
                CP.CREATEDON                    AS CostCenterPatternCreatedOn,
                CP.MODIFIEDBYID                 AS CostCenterPatternModifiedById,
                CP.MODIFIEDON                   AS CostCenterPatternModifiedOn
            FROM   MCOSTCENTERPATTERN CP
            LEFT JOIN MCOSTCENTERTYPE CT ON CP.COSTCENTERTYPEID = CT.COSTCENTERTYPEID
            WHERE  CP.TENANTID = @TenantId
              AND  (@SearchText IS NULL OR @SearchText = ''
                    OR CP.COSTCENTERPATTERNCODE LIKE '%' + @SearchText + '%'
                    OR CP.COSTCENTERPATTERNNAME LIKE '%' + @SearchText + '%')
              AND  (@StatusEquals IS NULL OR CP.STATUS = @StatusEquals)
              AND  (@StatusNotEquals IS NULL OR CP.STATUS <> @StatusNotEquals)
            ORDER BY CP.COSTCENTERPATTERNCODE;";

        public const string GET_COSTCENTERPATTERN_DETAILS = @"
            SELECT
                CPD.COSTCENTERPATTERNDETAILID           AS CostCenterPatternDetailId,
                CPD.COSTCENTERPATTERNID                 AS CostCenterPatternId,
                CPD.SLNO                                AS CostCenterPatternDetailSlNo,
                CPD.COSTCENTERID                        AS CostCenterId,
                CC.COSTCENTERCODE                       AS CostCenterCode,
                CC.COSTCENTERNAME                       AS CostCenterName,
                CPD.ALLOCATIONPERCENTAGE                AS CostCenterPatternDetailAllocationPercentage
            FROM MCOSTCENTERPATTERNDETAIL CPD
            JOIN MCOSTCENTER CC ON CPD.COSTCENTERID = CC.COSTCENTERID
            WHERE CPD.COSTCENTERPATTERNID = @CostCenterPatternId
            ORDER BY CPD.SLNO;";

        public const string SAVE_COSTCENTERPATTERN = @"
            INSERT INTO MCOSTCENTERPATTERN
            (
                COSTCENTERPATTERNID, COSTCENTERPATTERNCODE, COSTCENTERPATTERNNAME,
                REMARKS, COSTCENTERTYPEID,
                SORTORDER, STATUS, VERSION,
                CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, TENANTID
            )
            VALUES
            (
                @CostCenterPatternId, @CostCenterPatternCode, @CostCenterPatternName,
                @CostCenterPatternRemarks, @CostCenterTypeId,
                @CostCenterPatternSortOrder, @CostCenterPatternStatus, @CostCenterPatternVersion,
                @CostCenterPatternCreatedById, @CostCenterPatternCreatedOn,
                @CostCenterPatternModifiedById, @CostCenterPatternModifiedOn, @TenantId
            );";

        public const string UPDATE_COSTCENTERPATTERN = @"
            UPDATE MCOSTCENTERPATTERN SET
                COSTCENTERPATTERNCODE       = @CostCenterPatternCode,
                COSTCENTERPATTERNNAME       = @CostCenterPatternName,
                REMARKS                     = @CostCenterPatternRemarks,
                COSTCENTERTYPEID            = @CostCenterTypeId,
                SORTORDER                   = @CostCenterPatternSortOrder,
                STATUS                      = @CostCenterPatternStatus,
                VERSION                     = @CostCenterPatternVersion,
                MODIFIEDBYID                = @CostCenterPatternModifiedById,
                MODIFIEDON                  = @CostCenterPatternModifiedOn
            WHERE COSTCENTERPATTERNID = @CostCenterPatternId
            AND   TENANTID            = @TenantId;";

        public const string DELETE_COSTCENTERPATTERN = @"
                DELETE FROM MCOSTCENTERPATTERNDETAIL 
                WHERE COSTCENTERPATTERNID = @CostCenterPatternId;

                DELETE FROM MCOSTCENTERPATTERN 
                WHERE COSTCENTERPATTERNID = @CostCenterPatternId;";

        public const string DELETE_COSTCENTERPATTERN_DETAILS = @"
            DELETE FROM MCOSTCENTERPATTERNDETAIL
            WHERE COSTCENTERPATTERNID = @CostCenterPatternId;";

        public const string SAVE_COSTCENTERPATTERN_DETAIL = @"
            INSERT INTO MCOSTCENTERPATTERNDETAIL
                (
                    COSTCENTERPATTERNDETAILID,
                    COSTCENTERPATTERNID,
                    SLNO,
                    COSTCENTERID,
                    ALLOCATIONPERCENTAGE
                )
                VALUES
                (
                    @CostCenterPatternDetailId,
                    @CostCenterPatternId,
                    @CostCenterPatternDetailSlNo,
                    @CostCenterId,
                    @CostCenterPatternDetailAllocationPercentage
                );";

        public const string GET_SELECTLIST_COSTCENTERPATTERN = @"
            SELECT
                COSTCENTERPATTERNID   AS Id,
                COSTCENTERPATTERNCODE AS Code,
                COSTCENTERPATTERNNAME AS Name
            FROM MCOSTCENTERPATTERN
            WHERE TENANTID = @TenantId
            ORDER BY COSTCENTERPATTERNNAME;";
    }
}