using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Google.Rpc;
using static GB5Shared.DTO.GOP.GBGatewayDTO;

namespace AccountsDAL.Query.CostCenter
{
    public class CostCenterQB
    {
        //public const string GET_SELECTLIST_COSTCENTER = @"WITH PagedCostCenter AS
        //            (
        //                SELECT
        //                    CC.COSTCENTERID     AS Id,
        //                    CC.COSTCENTERCODE   AS Code,
        //                    CC.COSTCENTERNAME   AS Name,
        //                    ROW_NUMBER() OVER (ORDER BY CC.COSTCENTERNAME) AS RowNum
        //                FROM MCOSTCENTER CC
        //                WHERE 
        //                    CC.STATUS = 1
        //                    AND CC.TENANTID = @TenantId
        //            )
        //            SELECT
        //                Id,
        //                Code,
        //                Name
        //            FROM PagedCostCenter
        //            WHERE RowNum BETWEEN (@firstnumber + 1) AND (@firstnumber + @maxresult)
        //            ORDER BY RowNum;";
        public const string GET_SELECTLIST_COSTCENTER = @"
                        SELECT
                    CC.COSTCENTERID   AS Id,
                    CC.COSTCENTERCODE AS Code,
                    CC.COSTCENTERNAME AS Name
                FROM MCOSTCENTER CC
                WHERE
                    CC.STATUS = 1
            
                ORDER BY
                    CC.COSTCENTERNAME;";

        public const string SAVE_COSTCENTER = @"INSERT INTO MCOSTCENTER
                                                (
                                                    COSTCENTERID,
                                                    COSTCENTERCODE,
                                                    COSTCENTERNAME,
                                                    COSTCENTERSHORTNAME,
                                                    COSTCENTERTYPEID,
                                                    SORTORDER,
                                                    STATUS,
                                                    VERSION,
                                                    CREATEDBYID,
                                                    CREATEDON,
                                                    MODIFIEDBYID,
                                                    MODIFIEDON,
													SOURCETYPE,
													PARENTID,
													PARENTIDS,            	
													LEVELS,			
													
													APPLICABLEOUIDS,		
													APPLICABLEDEPARTMENTIDS,	
													APPLICABLEBIZDIMENSIONVALUEV1IDS
                                                )
                                                VALUES
                                                (
                                                    @CostCenterId,
                                                    @CostCenterCode,
                                                    @CostCenterName,
                                                    @CostCenterShortName,
                                                    @CostCenterTypeId,
                                                    @CostCenterSortOrder,
                                                    @CostCenterStatus,
                                                    @CostCenterVersion,
                                                    @CostCenterCreatedById,
                                                    @CostCenterCreatedOn,
                                                    @CostCenterModifiedById,
                                                    @CostCenterModifiedOn,
													@CostCenterSourceType,
													@ParentId,
													@CostCenterParentIds,
													@CostCenterLevels,
													
													@CostCenterApplicableOUIds,
													@CostCenterApplicableDepartmentIds,
													@CostCenterApplicableBizDimensionValueV1Ids
                                                );";



        public const string UPDATE_COSTCENTER = @"UPDATE MCOSTCENTER
                                                SET
                                                    COSTCENTERCODE          = @CostCenterCode,
                                                    COSTCENTERNAME          = @CostCenterName,
                                                    COSTCENTERSHORTNAME 	= @CostCenterShortName,
													COSTCENTERTYPEID        = @CostCenterTypeId,		
													SORTORDER    	       	= @CostCenterSortOrder,
													STATUS        	        = @CostCenterStatus,
													VERSION     			= @CostCenterVersion,
													MODIFIEDBYID    	    = @CostCenterModifiedById,		
													MODIFIEDON    			= @CostCenterModifiedOn,      
													SOURCETYPE 		        = @CostCenterSourceType,
													PARENTID 		        = @ParentId, 
													PARENTIDS            	= @CostCenterParentIds,
													LEVELS			        = @CostCenterLevels,
													
													APPLICABLEOUIDS		    = @CostCenterApplicableOUIds,
													APPLICABLEDEPARTMENTIDS	= @CostCenterApplicableDepartmentIds,
													APPLICABLEBIZDIMENSIONVALUEV1IDS= @CostCenterApplicableBizDimensionValueV1Ids
                                                WHERE COSTCENTERID = @CostCenterId;";

        //public const string GET_COSTCENTER = @"
        //                        SELECT
        //                            CC.COSTCENTERID                             AS CostCenterId,
        //                            CC.COSTCENTERCODE                           AS CostCenterCode,
        //                            CC.COSTCENTERNAME                           AS CostCenterName,
        //                            CC.COSTCENTERSHORTNAME                      AS CostCenterShortName,

        //                            CC.COSTCENTERTYPEID                         AS CostCenterTypeId,
        //                            CCT.COSTCENTERTYPECODE                      AS CostCenterTypeCode,
        //                            CCT.COSTCENTERTYPENAME                      AS CostCenterTypeName,

        //                            CC.SORTORDER                                AS CostCenterSortOrder,
        //                            CC.STATUS                                   AS CostCenterStatus,
        //                            CC.VERSION                                  AS CostCenterVersion,

        //                            CC.CREATEDBYID                              AS CostCenterCreatedById,
        //                            CC.CREATEDON                                AS CostCenterCreatedOn,
        //                            UC.USERNAME                                 AS CostCenterCreatedByName,

        //                            CC.MODIFIEDBYID                             AS CostCenterModifiedById,
        //                            CC.MODIFIEDON                               AS CostCenterModifiedOn,
        //                            UM.USERNAME                                 AS CostCenterModifiedByName,

        //                            CC.SOURCETYPE                               AS CostCenterSourceType,

        //                            CC.PARENTID                                 AS ParentId,
        //                            PCC.COSTCENTERCODE                          AS ParentCode,
        //                            PCC.COSTCENTERNAME                          AS ParentName,

        //                            CC.PARENTIDS                                AS CostCenterParentIds,
        //                            CC.LEVELS                                   AS CostCenterLevels,



        //                            CC.APPLICABLEOUIDS                          AS CostCenterApplicableOUIds,
        //                            CC.APPLICABLEDEPARTMENTIDS                  AS CostCenterApplicableDepartmentIds,
        //                            CC.APPLICABLEBIZDIMENSIONVALUEV1IDS         AS CostCenterApplicableBizDimensionValueV1Ids

        //                        FROM MCOSTCENTER CC

        //                        LEFT JOIN MCOSTCENTER PCC
        //                            ON CC.PARENTID = PCC.COSTCENTERID

        //                        LEFT JOIN MCOSTCENTERTYPE CCT
        //                            ON CC.COSTCENTERTYPEID = CCT.COSTCENTERTYPEID

        //                        LEFT JOIN MUSER UC
        //                            ON CC.CREATEDBYID = UC.USERID

        //                        LEFT JOIN MUSER UM
        //                            ON CC.MODIFIEDBYID = UM.USERID

        //                        WHERE CC.COSTCENTERID = @CostCenterId;";
        public const string GET_COSTCENTER = @"
                        SELECT
                            CC.COSTCENTERID                             AS CostCenterId,
                            CC.COSTCENTERCODE                           AS CostCenterCode,
                            CC.COSTCENTERNAME                           AS CostCenterName,
                            CC.COSTCENTERSHORTNAME                      AS CostCenterShortName,

                            CC.COSTCENTERTYPEID                         AS CostCenterTypeId,
                            CCT.COSTCENTERTYPECODE                      AS CostCenterTypeCode,
                            CCT.COSTCENTERTYPENAME                      AS CostCenterTypeName,

                            CC.SORTORDER                                AS CostCenterSortOrder,
                            CC.STATUS                                   AS CostCenterStatus,
                            CC.VERSION                                  AS CostCenterVersion,

                            CC.CREATEDBYID                              AS CostCenterCreatedById,
                            CC.CREATEDON                                AS CostCenterCreatedOn,
                            UC.USERNAME                                 AS CostCenterCreatedByName,

                            CC.MODIFIEDBYID                             AS CostCenterModifiedById,
                            CC.MODIFIEDON                               AS CostCenterModifiedOn,
                            UM.USERNAME                                 AS CostCenterModifiedByName,

                            CC.SOURCETYPE                               AS CostCenterSourceType,

                            CC.PARENTID                                 AS ParentId,
                            PCC.COSTCENTERCODE                          AS ParentCode,
                            PCC.COSTCENTERNAME                          AS ParentName,

                            CC.PARENTIDS                                AS CostCenterParentIds,
                            CC.LEVELS                                   AS CostCenterLevels,

                            CC.APPLICABLEOUIDS                          AS CostCenterApplicableOUIds,

                            (
                                SELECT STRING_AGG(OU.ORGANIZATIONUNITNAME, ', ')
                                FROM MORGANIZATIONUNIT OU
                                WHERE ',' + ISNULL(CC.APPLICABLEOUIDS, '') + ','
                                      LIKE '%,' + CAST(OU.OUID AS VARCHAR(20)) + ',%'
                            )                                           AS CostCenterApplicableOUNames,

                            CC.APPLICABLEDEPARTMENTIDS                  AS CostCenterApplicableDepartmentIds,

                            (
                                SELECT STRING_AGG(D.DEPARTMENTNAME, ', ')
                                FROM MDEPARTMENT D
                                WHERE ',' + ISNULL(CC.APPLICABLEDEPARTMENTIDS, '') + ','
                                      LIKE '%,' + CAST(D.DEPARTMENTID AS VARCHAR(20)) + ',%'
                            )                                           AS CostCenterApplicableDepartmentNames,

                            CC.APPLICABLEBIZDIMENSIONVALUEV1IDS         AS CostCenterApplicableBizDimensionValueV1Ids

                        FROM MCOSTCENTER CC

                        LEFT JOIN MCOSTCENTER PCC
                            ON CC.PARENTID = PCC.COSTCENTERID

                        LEFT JOIN MCOSTCENTERTYPE CCT
                            ON CC.COSTCENTERTYPEID = CCT.COSTCENTERTYPEID

                        LEFT JOIN MUSER UC
                            ON CC.CREATEDBYID = UC.USERID

                        LEFT JOIN MUSER UM
                            ON CC.MODIFIEDBYID = UM.USERID

                        WHERE CC.COSTCENTERID = @CostCenterId;";

        // Full-DTO cost center list — same column set/joins as GET_COSTCENTER (returns CostCenterDTO
        // for every matching row instead of one), criteria-filtered like the other *_LIST queries
        // (Account/AccountGroup/AccountSchedule) rather than paged — no FirstNumber/MaxResult, fixed
        // ORDER BY only. @SearchText filters CostCenterCode/CostCenterName/CostCenterShortName
        // (LIKE, OR'd), matching AccountGroupQB/AccountScheduleQB's pattern for entities that carry
        // a Code/Name/ShortName triple.
        public const string GET_COSTCENTER_LIST = @"
                        SELECT
                            CC.COSTCENTERID                             AS CostCenterId,
                            CC.COSTCENTERCODE                           AS CostCenterCode,
                            CC.COSTCENTERNAME                           AS CostCenterName,
                            CC.COSTCENTERSHORTNAME                      AS CostCenterShortName,

                            CC.COSTCENTERTYPEID                         AS CostCenterTypeId,
                            CCT.COSTCENTERTYPECODE                      AS CostCenterTypeCode,
                            CCT.COSTCENTERTYPENAME                      AS CostCenterTypeName,

                            CC.SORTORDER                                AS CostCenterSortOrder,
                            CC.STATUS                                   AS CostCenterStatus,
                            CC.VERSION                                  AS CostCenterVersion,

                            CC.CREATEDBYID                              AS CostCenterCreatedById,
                            CC.CREATEDON                                AS CostCenterCreatedOn,
                            UC.USERNAME                                 AS CostCenterCreatedByName,

                            CC.MODIFIEDBYID                             AS CostCenterModifiedById,
                            CC.MODIFIEDON                               AS CostCenterModifiedOn,
                            UM.USERNAME                                 AS CostCenterModifiedByName,

                            CC.SOURCETYPE                               AS CostCenterSourceType,

                            CC.PARENTID                                 AS ParentId,
                            PCC.COSTCENTERCODE                          AS ParentCode,
                            PCC.COSTCENTERNAME                          AS ParentName,

                            CC.PARENTIDS                                AS CostCenterParentIds,
                            CC.LEVELS                                   AS CostCenterLevels,

                            CC.APPLICABLEOUIDS                          AS CostCenterApplicableOUIds,

                            (
                                SELECT STRING_AGG(OU.ORGANIZATIONUNITNAME, ', ')
                                FROM MORGANIZATIONUNIT OU
                                WHERE ',' + ISNULL(CC.APPLICABLEOUIDS, '') + ','
                                      LIKE '%,' + CAST(OU.OUID AS VARCHAR(20)) + ',%'
                            )                                           AS CostCenterApplicableOUNames,

                            CC.APPLICABLEDEPARTMENTIDS                  AS CostCenterApplicableDepartmentIds,

                            (
                                SELECT STRING_AGG(D.DEPARTMENTNAME, ', ')
                                FROM MDEPARTMENT D
                                WHERE ',' + ISNULL(CC.APPLICABLEDEPARTMENTIDS, '') + ','
                                      LIKE '%,' + CAST(D.DEPARTMENTID AS VARCHAR(20)) + ',%'
                            )                                           AS CostCenterApplicableDepartmentNames,

                            CC.APPLICABLEBIZDIMENSIONVALUEV1IDS         AS CostCenterApplicableBizDimensionValueV1Ids

                        FROM MCOSTCENTER CC

                        LEFT JOIN MCOSTCENTER PCC
                            ON CC.PARENTID = PCC.COSTCENTERID

                        LEFT JOIN MCOSTCENTERTYPE CCT
                            ON CC.COSTCENTERTYPEID = CCT.COSTCENTERTYPEID

                        LEFT JOIN MUSER UC
                            ON CC.CREATEDBYID = UC.USERID

                        LEFT JOIN MUSER UM
                            ON CC.MODIFIEDBYID = UM.USERID

                        WHERE
                            (@SearchText IS NULL OR @SearchText = ''
                             OR CC.COSTCENTERCODE LIKE '%' + @SearchText + '%'
                             OR CC.COSTCENTERNAME LIKE '%' + @SearchText + '%'
                             OR CC.COSTCENTERSHORTNAME LIKE '%' + @SearchText + '%')
                            AND (@StatusEquals IS NULL OR CC.STATUS = @StatusEquals)
                            AND (@StatusNotEquals IS NULL OR CC.STATUS <> @StatusNotEquals)
                        ORDER BY CC.COSTCENTERCODE;";

        public const string DELETE_COSTCENTER = @"
            DELETE FROM MCOSTCENTER
            WHERE COSTCENTERID = @CostCenterId;";

    }
}