-- v27 — Connection resolver: catalog row -> live collection target

IF COL_LENGTH('ctl.Servers', 'AuthMode') IS NULL
  ALTER TABLE ctl.Servers ADD AuthMode NVARCHAR(32) NOT NULL CONSTRAINT DF_Servers_AuthMode DEFAULT N'Integrated';
IF COL_LENGTH('ctl.Servers', 'CredentialRef') IS NULL
  ALTER TABLE ctl.Servers ADD CredentialRef NVARCHAR(128) NULL;
IF COL_LENGTH('ctl.Servers', 'ConnectOverride') IS NULL
  ALTER TABLE ctl.Servers ADD ConnectOverride NVARCHAR(512) NULL;
IF COL_LENGTH('ctl.Servers', 'CollectionEligible') IS NULL
  ALTER TABLE ctl.Servers ADD CollectionEligible BIT NOT NULL CONSTRAINT DF_Servers_CollectionEligible DEFAULT 1;
GO

-- Catalog-only cloud placeholders: not SQL-collectable until ConnectOverride is set
UPDATE ctl.Servers
SET CollectionEligible = 0
WHERE Engine NOT IN (N'SQL Server', N'Azure SQL')
  AND (ConnectOverride IS NULL OR LTRIM(RTRIM(ConnectOverride)) = N'');
GO

CREATE OR ALTER VIEW ctl.vw_ResolvedConnections AS
SELECT
  s.ServerId,
  s.ServerName,
  s.Engine,
  s.Environment,
  s.Port,
  s.AuthMode,
  s.CredentialRef,
  s.IsActive,
  s.CollectionEligible,
  ConnectName = CASE
    WHEN s.ConnectOverride IS NOT NULL AND LTRIM(RTRIM(s.ConnectOverride)) <> N'' THEN LTRIM(RTRIM(s.ConnectOverride))
    WHEN s.Port IS NOT NULL AND s.Port > 0 AND CHARINDEX(N'\', s.ServerName) = 0
      THEN s.ServerName + N',' + CAST(s.Port AS NVARCHAR(10))
    ELSE s.ServerName
  END,
  IsCollectable = CAST(CASE
    WHEN s.IsActive = 0 THEN 0
    WHEN s.CollectionEligible = 0 THEN 0
    WHEN s.Engine IN (N'SQL Server', N'Azure SQL') THEN 1
    WHEN s.ConnectOverride IS NOT NULL AND LTRIM(RTRIM(s.ConnectOverride)) <> N'' THEN 1
    ELSE 0
  END AS BIT)
FROM ctl.Servers s;
GO

CREATE OR ALTER PROCEDURE ctl.usp_ResolveConnection
  @ServerId UNIQUEIDENTIFIER
AS
BEGIN
  SET NOCOUNT ON;
  SELECT *
  FROM ctl.vw_ResolvedConnections
  WHERE ServerId = @ServerId;
END;
GO

CREATE OR ALTER PROCEDURE ctl.usp_ResolveCollectionTargets
  @ServerId UNIQUEIDENTIFIER = NULL,
  @IncludeIneligible BIT = 0
AS
BEGIN
  SET NOCOUNT ON;
  SELECT *
  FROM ctl.vw_ResolvedConnections
  WHERE (@ServerId IS NULL OR ServerId = @ServerId)
    AND (@IncludeIneligible = 1 OR IsCollectable = 1)
  ORDER BY ServerName;
END;
GO
