-- =============================================================================
-- Entitlement Platform — register the 'EntitlementDb' logical connection (SQL Server variant)
-- Migration: 20260724 — Entitlement/EntitlementDb Provisioning
-- Database:  Gb5system (or whatever central DB hosts MSERVER/MSERVERCONFIG/MAUTONUMBER in the
--            target install) — NOT the tenant business DB the Entitlement schema itself lives in.
--
-- Purpose: `EntitlementLoginFactory` (EntitlementBLL/Common/EntitlementLoginFactory.cs) builds
-- every Entitlement LoginDTO with `DatabaseName = IGB5Environment.Resolve(Entitlement:Db:DatabaseName)`
-- from appsettings.json — `ApplicationConnection`/`QueryExecutor` then resolve that value as a
-- `CONNECTIONNAME` lookup against `MSERVERCONFIG`. Live verification against a real server
-- (2026-07-24) found this row never existed anywhere, which is why every real query through
-- Entitlement's own code (e.g. the Quartz `KillSwitchMonitorJob`) failed at runtime with
-- "No server configuration found for connection name: EntitlementDb".
--
-- SUPERSEDED NAMING (2026-09-08): the base connection name is no longer the interim "EntitlementDb"
-- pointed at a repurposed tenant DB (USCIMPSYS) — per the user's own confirmed architecture
-- correction, Entitlement now lives in its own fixed, central, developer/release-owned database,
-- `GB_Central_Entitlement`, tier-suffixed the same way every other IGB5Environment-resolved
-- connection is: `GB_Central_Entitlement-Dev` (Dev), `GB_Central_Entitlement-QC` (QC),
-- `GB_Central_Entitlement` unsuffixed (Live) — see GB5Shared/Deployment/GB5Environment.cs's
-- `Resolve()`. `EntitlementSL/appsettings.json`'s `Entitlement:Db:DatabaseName` is already set to
-- the base, unsuffixed `"GB_Central_Entitlement"` and `GB5:Environment` to `"Dev"` — this script
-- only needs to register the MATCHING tier-suffixed MSERVERCONFIG row for whichever tier is being
-- provisioned; it no longer registers "EntitlementDb" at all.
--
-- CRITICAL DESIGN NOTE, confirmed directly from 20260713_Entitlement_Phase1_Schema_SqlServer.sql's
-- own header: Entitlement's MENTITLEMENT* tables are NOT meant to live in a brand-new, empty
-- database — they FK-reference MCLIENT/MUSER/MMODULE/MMENUSET "by name — this migration does not
-- create them", i.e. they must be created INSIDE whichever database already holds the real
-- MCLIENT/MUSER/MMENU/MROLE tables. `CONNECTIONNAME` and `DATABASENAME` are separate columns on
-- MSERVERCONFIG specifically so a logical name ("EntitlementDb") can point at whatever the real
-- physical database is called in a given install (e.g. "USCIMPSYS" in the one live environment
-- checked this session) — this script does NOT run `CREATE DATABASE`. If your target install
-- genuinely wants Entitlement isolated in its own dedicated physical database, run the Phase 1-3
-- Entitlement schema migrations (20260713/20260716/20260719, listed below) against that dedicated
-- database instead, and set @TargetDatabaseName accordingly — this script only wires up the
-- connection-name lookup, it is agnostic to which physical database you point it at.
--
-- Entitlement schema migrations to run AGAINST @TargetDatabaseName, in this order, AFTER this
-- script (not part of this script — each already exists as its own committed migration):
--   1. 20260713_Entitlement_Phase1_Schema_SqlServer.sql          (core MENTITLEMENT* tables)
--   2. 20260713_Entitlement_Phase1_MCLIENT_Extension_SqlServer.sql (MCLIENT additive columns)
--   3. 20260713_Entitlement_Phase1_4_ProvisioningJob_Schema_SqlServer.sql
--   4. 20260716_Entitlement_Phase1_5_FlagTargetRemarks_SqlServer.sql
--   5. 20260716_Entitlement_Phase2_ClientAuth_Schema_SqlServer.sql
--   6. 20260716_Entitlement_Phase2_5_PasswordResetMailTemplate_Seed_SqlServer.sql
--   7. 20260716_Entitlement_Phase2_6_ClientAuthEventType_Seed_SqlServer.sql
--   8. 20260717_Entitlement_SqlWorkbenchFeature_Seed_SqlServer.sql
--   9. 20260719_Entitlement_Phase3_ChangeRequest_Schema_SqlServer.sql
-- (The four *_MenuSeed_* migrations are separate — they target whatever tenant DB owns
-- MMENU/MROLE for GOODBOOKS_ADMIN staff, not necessarily @TargetDatabaseName.)
--
-- ID GENERATION: SERVERCONFIGID is reserved from MAUTONUMBER (ENTITYCODE='SERVERCONFIG'), with an
-- explicit collision check — live verification found this counter can be stale relative to actual
-- MSERVERCONFIG rows on at least one real install (a prior row was inserted without going through
-- the atomic autonumber path), so this script does not trust a single increment blindly.
-- =============================================================================

-- ── SET THESE THREE VALUES FOR THE TARGET INSTALL BEFORE RUNNING ────────────────────────────
-- @ConnectionName must equal IGB5Environment.Resolve('GB_Central_Entitlement') for whichever tier
-- you are registering, i.e. exactly Entitlement:Db:DatabaseName + '-' + the tier code (Dev/QC),
-- or unsuffixed for Live — do not reintroduce the old "EntitlementDb" logical name here.
DECLARE @ConnectionName     NVARCHAR(100) = 'GB_Central_Entitlement-Dev';  -- 'GB_Central_Entitlement-QC' for QC, 'GB_Central_Entitlement' (unsuffixed) for Live
DECLARE @TargetDatabaseName NVARCHAR(100) = 'GB_Central_Entitlement-Dev'; -- the real, already-provisioned, dedicated central Entitlement database for this tier — same name as @ConnectionName by convention, a direct 1:1 mapping, not a repurposed tenant DB
DECLARE @ServerId           INT           = -1900009997;     -- TODO: verify this MSERVER row exists in the target install (the server @TargetDatabaseName physically lives on)
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 password, encrypted via
                                                               -- GB5Shared.Connection.ApplicationConnection.Encrypt(plaintext, "GB5")
                                                               -- (AES-256-GCM, SHA-256("GB5") key, base64 IV+ciphertext+tag) — do NOT
                                                               -- reuse this literal value outside the one environment it was taken from.
-- ─────────────────────────────────────────────────────────────────────────────────────────────

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

-- Reserve a free SERVERCONFIGID: atomically advance the counter, then walk forward if the
-- resulting value collides with an existing row (see this migration's own header note on why
-- the counter can be stale on at least one real install).
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

-- SYSTEMSERVERCONFIGID/CLIENTID/CLIENTSITEID are NOT NULL with no table default (confirmed via
-- INFORMATION_SCHEMA.COLUMNS) — SYSTEMSERVERCONFIGID self-references the new row; CLIENTID/
-- CLIENTSITEID use this codebase's established -1 = NONE sentinel (no specific client/site owns
-- this platform-level connection).
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)) + ').';
GO

-- =============================================================================
-- END OF MIGRATION 20260724_Entitlement_EntitlementDb_Provisioning (SQL Server variant)
-- =============================================================================