-- =============================================================================
-- DXP Platform — register tier-suffixed 'GB_Central_DXP-{Dev,QC}' connections
-- Migration: 20260907 — Environment-tiering plan, Part D
-- Database:  Gb5system (or whatever central DB hosts MSERVER/MSERVERCONFIG/MAUTONUMBER in the
--            target install) — same convention Entitlement's own tier-connections migration
--            (20260907_Entitlement_EntitlementDb_TierConnections_SqlServer.sql) uses.
--
-- NOTE: the base database name was renamed from 'DXPDb' to 'GB_Central_DXP' to make explicit,
-- in the name itself, that this is a single central/product-wide database with no per-install
-- counterpart (unlike a tenant DB) — see the environment-tiering plan's naming discussion.
-- 'DXPSystemDatabase:DatabaseName' in appsettings.json now carries this base name; only the
-- physical/registered name changed, not the module's own schema or code.
--
-- Purpose: DXPSystemContext now resolves its database name through
-- IGB5Environment.Resolve() (GB5Shared/Deployment) — Dev/QC processes (GB5:Environment=Dev|QC)
-- look up 'GB_Central_DXP-Dev'/'GB_Central_DXP-QC' as the CONNECTIONNAME instead of the bare
-- 'GB_Central_DXP' Live uses. This script registers those two NEW rows; it does not touch the
-- existing Live row (unchanged, no suffix).
--
-- No prior migration exists that registered GB_Central_DXP's own MSERVERCONFIG row (unlike
-- Entitlement's documented 20260724 precedent) — if 'GB_Central_DXP' itself doesn't already
-- exist as a CONNECTIONNAME in the target install, register it the same way first (same INSERT
-- shape, @ConnectionName = 'GB_Central_DXP', no tier suffix) before running this script for
-- Dev/QC.
--
-- Run this block ONCE PER TIER (Dev, then QC) — set @Tier accordingly each time.
-- =============================================================================

-- ── SET THESE VALUES FOR THE TARGET TIER BEFORE RUNNING (run once for Dev, once for QC) ────
DECLARE @Tier               NVARCHAR(10)  = 'Dev';         -- TODO: 'Dev' or 'QC' — must match GB5Tier.ToString()
DECLARE @TargetDatabaseName NVARCHAR(100) = 'GB_Central_DXP-Dev'; -- TODO: the real scratch/QC physical database name
DECLARE @ServerId           INT           = -1900009997;   -- TODO: verify this MSERVER row exists in the target install
DECLARE @DbUserName         NVARCHAR(100) = 'Developer';   -- TODO: real DB login for the target install
DECLARE @DbPasswordEncrypted NVARCHAR(200) =
    'MMtnHE5Btr39ZKjs045SQiVqDxqKLyDr96LPTEPeBCDKo/BTxRQY'; -- TODO: replace with THIS install's own encrypted password
                                                             -- (GB5Shared.Connection.ApplicationConnection.Encrypt, AES-256-GCM) —
                                                             -- do NOT reuse this literal value outside the source environment.
-- ─────────────────────────────────────────────────────────────────────────────────────────────

DECLARE @ConnectionName NVARCHAR(100) = 'GB_Central_DXP-' + @Tier;

IF EXISTS (SELECT 1 FROM MSERVERCONFIG WHERE CONNECTIONNAME = @ConnectionName)
BEGIN
    PRINT 'MSERVERCONFIG row for CONNECTIONNAME = ''' + @ConnectionName + ''' already exists — nothing to do.';
    RETURN;
END

IF NOT EXISTS (SELECT 1 FROM MSERVER WHERE SERVERID = @ServerId)
BEGIN
    RAISERROR('MSERVER row %d does not exist in this install — verify @ServerId before running.', 16, 1, @ServerId);
    RETURN;
END

DECLARE @IdTable TABLE (AutoId INT);
UPDATE MAUTONUMBER
SET    AUTOID = AUTOID + 1
OUTPUT DELETED.AUTOID INTO @IdTable
WHERE  ENTITYCODE = 'SERVERCONFIG';
DECLARE @NewServerConfigId INT = (SELECT AutoId FROM @IdTable);

WHILE EXISTS (SELECT 1 FROM MSERVERCONFIG WHERE SERVERCONFIGID = @NewServerConfigId)
BEGIN
    UPDATE MAUTONUMBER SET AUTOID = AUTOID + 1 WHERE ENTITYCODE = 'SERVERCONFIG';
    SET @NewServerConfigId = @NewServerConfigId + 1;
END

INSERT INTO MSERVERCONFIG
    (SERVERCONFIGID, CONNECTIONNAME, SERVERID, DATABASETYPE, DATABASENAME, DATABASEUSERNAME,
     DATABASEPASSWORD, DATABASEPORT, NATURE, DBSOURCETYPE, SYSTEMSERVERCONFIGID, SORTORDER, STATUS,
     VERSION, SOURCETYPE, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, CLIENTID, CLIENTSITEID)
VALUES
    (@NewServerConfigId, @ConnectionName, @ServerId, 0, @TargetDatabaseName, @DbUserName,
     @DbPasswordEncrypted, NULL, 0, 5, @NewServerConfigId, 1, 1,
     0, 0, -1, GETUTCDATE(), -1, GETUTCDATE(), -1, -1);

PRINT 'Registered MSERVERCONFIG.CONNECTIONNAME = ''' + @ConnectionName + ''' -> DATABASENAME = ''' + @TargetDatabaseName + ''' (SERVERCONFIGID = ' + CAST(@NewServerConfigId AS NVARCHAR(20)) + ').';
PRINT 'Next: run the DXP Phase 1+ schema migrations (20260710/20260711/20260712/20260715/20260716_DXP_*_SqlServer.sql, in date order) against ' + @TargetDatabaseName + '.';
GO

-- =============================================================================
-- END OF MIGRATION 20260907_DXP_DXPDb_TierConnections (SQL Server variant)
-- =============================================================================