using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace GB5Shared.Query.FrameWork.DbConnection { public static class ConnectionQueryBuilder { public const string LOAD_DIFFERENT_DATABASE_NAME_FOR_PRODUCTION_CONNECTIONNNAME = @"SELECT A.serverconfigid AS ServerConfigId, A.connectionname AS ConnectionName, A.databasetype AS DbType, A.databasename AS DatabaseName, A.databaseusername AS DatabaseUserName, A.databasepassword AS DatabasePassword, A.databaseport AS DatabasePort, A.nature AS DbNaturte, A.dbsourcetype, A.systemserverconfigid AS SystemServerConfigId, A.refernceserverconfigid AS RefernceServerConfigId, A.archiveserverconfigid AS ArchivedDatabaseConnectionId, A.remarks, A.sortorder, A.status, A.version, A.sourcetype, A.createdbyid, A.createdon, A.modifiedbyid, A.modifiedon, A.clientid AS ClientId, A.""offset"" AS Offset, A.maxvalue AS MaxValue, A.clientsiteid AS ClientsiteId, A.applicationusercode AS ApplicationUserCode, A.applicationuserpassword AS ApplicationUserPassword, A.dbinstancetype AS DbInstanceType, A.parentserverconfigid AS ParentServerConfigId, A.fcmconfig AS ServerConfigFCMConfig, A.serviceserverid AS ServiceServerId, A.authmode AS AuthMode, A.keycloakhost AS KeycloakHost, A.keycloakrealm AS KeycloakRealm, A.keycloakadminvaultpath AS KeycloakAdminVaultPath, A.vaultaddress AS VaultAddress, S.servername AS ServerName, S.serverip AS ServerIP, S.servermachinename AS ServerMachineName, S.uniquedetails AS UniqueDetails, B.connectionname AS SystemDataBaseConnectionName, C.connectionname AS ReferalDataBaseConnectionName, D.connectionname AS ArchivedDataBaseConnectionName, E.connectionname AS ParentServerConfigConnectionName, E.databasename AS ParentServerConfigDatabaseName FROM mserverconfig A JOIN mserver S ON A.serverid = S.serverid LEFT JOIN mserverconfig B ON A.systemserverconfigid = B.serverconfigid LEFT JOIN mserverconfig C ON A.refernceserverconfigid = C.serverconfigid LEFT JOIN mserverconfig D ON A.archiveserverconfigid = D.serverconfigid LEFT JOIN mserverconfig E ON A.parentserverconfigid = E.serverconfigid WHERE A.connectionname = @ConnectionName; "; public const string LOAD_DIFFERENT_DATABASE_NAME_FOR_PRODUCTION = @"SELECT A.serverconfigid AS ServerConfigId, A.connectionname AS ConnectionName, A.databasetype AS DbType, A.databasename AS DatabaseName, A.databaseusername AS DatabaseUserName, A.databasepassword AS DatabasePassword, A.databaseport AS DatabasePort, A.nature AS DbNaturte, A.dbsourcetype, A.systemserverconfigid AS SystemServerConfigId, A.refernceserverconfigid AS RefernceServerConfigId, A.archiveserverconfigid AS ArchivedDatabaseConnectionId, A.remarks, A.sortorder, A.status, A.version, A.sourcetype, A.createdbyid, A.createdon, A.modifiedbyid, A.modifiedon, A.clientid AS ClientId, A.""offset"" AS Offset, A.maxvalue AS MaxValue, A.clientsiteid AS ClientsiteId, A.applicationusercode AS ApplicationUserCode, A.applicationuserpassword AS ApplicationUserPassword, A.dbinstancetype AS DbInstanceType, A.parentserverconfigid AS ParentServerConfigId, A.fcmconfig AS ServerConfigFCMConfig, A.serviceserverid AS ServiceServerId, A.authmode AS AuthMode, A.keycloakhost AS KeycloakHost, A.keycloakrealm AS KeycloakRealm, A.keycloakadminvaultpath AS KeycloakAdminVaultPath, A.vaultaddress AS VaultAddress, S.servername AS ServerName, S.serverip AS ServerIP, S.servermachinename AS ServerMachineName, S.uniquedetails AS UniqueDetails, B.connectionname AS SystemDataBaseConnectionName, C.connectionname AS ReferalDataBaseConnectionName, D.connectionname AS ArchivedDataBaseConnectionName, E.connectionname AS ParentServerConfigConnectionName, E.databasename AS ParentServerConfigDatabaseName FROM mserverconfig A JOIN mserver S ON A.serverid = S.serverid LEFT JOIN mserverconfig B ON A.systemserverconfigid = B.serverconfigid LEFT JOIN mserverconfig C ON A.refernceserverconfigid = C.serverconfigid LEFT JOIN mserverconfig D ON A.archiveserverconfigid = D.serverconfigid LEFT JOIN mserverconfig E ON A.parentserverconfigid = E.serverconfigid WHERE A.DATABASENAME = @ConnectionName;"; // Reverse of the two queries above: given a Keycloak realm name (extracted from a // validated token's issuer, e.g. "unisoftgb5" from // "https://sso.goodbookserp.in/realms/unisoftgb5"), find which tenant it belongs to. // AUTHMODE=1 filter is deliberate — a realm value only means something for a tenant that's // actually opted into Keycloak; guards against a stale/orphaned KEYCLOAKREALM value on a // row that's since reverted to AuthMode=Native. public const string RESOLVE_TENANT_BY_KEYCLOAK_REALM = @"SELECT A.serverconfigid AS ServerConfigId, A.connectionname AS ConnectionName, A.databasetype AS DbType, A.databasename AS DatabaseName, A.databaseusername AS DatabaseUserName, A.databasepassword AS DatabasePassword, A.databaseport AS DatabasePort, A.nature AS DbNaturte, A.dbsourcetype, A.systemserverconfigid AS SystemServerConfigId, A.refernceserverconfigid AS RefernceServerConfigId, A.archiveserverconfigid AS ArchivedDatabaseConnectionId, A.remarks, A.sortorder, A.status, A.version, A.sourcetype, A.createdbyid, A.createdon, A.modifiedbyid, A.modifiedon, A.clientid AS ClientId, A.""offset"" AS Offset, A.maxvalue AS MaxValue, A.clientsiteid AS ClientsiteId, A.applicationusercode AS ApplicationUserCode, A.applicationuserpassword AS ApplicationUserPassword, A.dbinstancetype AS DbInstanceType, A.parentserverconfigid AS ParentServerConfigId, A.fcmconfig AS ServerConfigFCMConfig, A.serviceserverid AS ServiceServerId, A.authmode AS AuthMode, A.keycloakhost AS KeycloakHost, A.keycloakrealm AS KeycloakRealm, A.keycloakadminvaultpath AS KeycloakAdminVaultPath, A.vaultaddress AS VaultAddress, S.servername AS ServerName, S.serverip AS ServerIP, S.servermachinename AS ServerMachineName, S.uniquedetails AS UniqueDetails FROM mserverconfig A JOIN mserver S ON A.serverid = S.serverid WHERE A.keycloakrealm = @Realm AND A.authmode = 1; "; // Used by Entitlement's onboarding orchestrator to find a freshly-provisioned tenant's // own ConnectionName — ProvisioningJobDTO doesn't carry it, only ClientId. public const string SELECT_CONNECTIONNAME_BY_CLIENTID = @"SELECT connectionname FROM mserverconfig WHERE clientid = @ClientId;"; // Flips a tenant's row to Keycloak mode once its realm+client have been provisioned — // called by Entitlement's onboarding orchestrator (ClientOnboardingOrchestratorBLL, // ResumeOneJobAsync) right after a new SaaS tenant's Keycloak realm is created. CLIENTID // is used rather than CONNECTIONNAME because that's what the onboarding job already has // in hand at this point — the newly-inserted row for a fresh tenant is uniquely // identified by its own new ClientId. public const string UPDATE_MSERVERCONFIG_KEYCLOAK_AUTH = @"UPDATE mserverconfig SET authmode = 1, keycloakhost = @KeycloakHost, keycloakrealm = @KeycloakRealm WHERE clientid = @ClientId;"; // Query used by IServerConfigCache to resolve a MSERVERCONFIG row by PK. // Joins MSERVER for IP / machine name (needed to build connection strings). // Includes REPORTSERVERCONFIGID and ARCHIVESERVERCONFIGID for datasource routing. public const string GET_MSERVERCONFIG_BY_ID = @"SELECT A.serverconfigid AS ServerConfigId, A.databasetype AS ProviderType, A.databasename AS DatabaseName, A.databaseusername AS DatabaseUserName, A.databasepassword AS DatabasePassword, A.databaseport AS DatabasePort, A.nature AS Nature, A.reportserverconfigid AS ReportServerConfigId, A.archiveserverconfigid AS ArchiveServerConfigId, S.serverip AS ServerIp, S.servermachinename AS ServerMachineName FROM mserverconfig A JOIN mserver S ON A.serverid = S.serverid WHERE A.serverconfigid = @ServerConfigId;"; } }