namespace SwDAL.Query.ServerConfig;

/// <summary>SQL Server dialect only for this release — mirrors ServerConfigCache's own
/// SqlConnection/NpgsqlConnection dbType switch; a Postgres variant of these statements would be
/// added at the same call sites, not here, if/when this write path needs dual-dialect support.
/// Column list for MSERVERCONFIG matches TCMSTestEnvQB.INSERT_MSERVERCONFIG (the only other real
/// INSERT against this table in the repo), extended with REPORTSERVERCONFIGID/
/// ARCHIVESERVERCONFIGID.</summary>
public static class ServerConfigWriteQB
{
    // Required index note: no existing index confirmed on MSERVER.SERVERMACHINENAME — a lookup
    // by machine name should have one if this find-or-create path sees real traffic.
    public const string FIND_MSERVER_BY_MACHINE_NAME = @"
        SELECT SERVERID FROM MSERVER WHERE SERVERMACHINENAME = @ServerMachineName AND STATUS = 1";

    // Confirmed live: MSERVERID is NOT an IDENTITY column — it's populated via the standard
    // MAUTONUMBER convention (entity code "SERVER", confirmed present live) like every other
    // GB5 PK. Omitting @ServerId here failed outright ("Cannot insert the value NULL into column
    // 'SERVERID'") the first time this find-or-create path ever actually ran against a real,
    // brand-new machine name — fixed by allocating a real ServerId via AutoNumber before this
    // INSERT (see ServerConfigBLL.RegisterDatabaseAsync) and supplying it explicitly.
    public const string INSERT_MSERVER = @"
        INSERT INTO MSERVER
            (SERVERID, SERVERNAME, SERVERIP, SERVERMACHINENAME, UNIQUEDETAILS, REMARKS,
             SORTORDER, STATUS, VERSION, SOURCETYPE, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON,
             SERVERRUNSTATUS, PRIMARYBASEURL, SECONDORYBASEURL)
        OUTPUT INSERTED.SERVERID
        VALUES
            (@ServerId, @ServerName, @ServerIp, @ServerMachineName, @UniqueDetails, @Remarks,
             @SortOrder, @Status, @Version, @SourceType, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn,
             @ServerRunStatus, @PrimaryBaseUrl, @SecondoryBaseUrl)";

    // Confirmed live: SERVERCONFIGID is NOT an IDENTITY column either — same class of bug as
    // INSERT_MSERVER above, found in the same first-ever live run. Allocated via AutoNumber
    // (entity code "SERVERCONFIG", confirmed present live) in ServerConfigBLL.RegisterDatabaseAsync
    // and supplied explicitly here.
    public const string INSERT_MSERVERCONFIG = @"
        INSERT INTO MSERVERCONFIG
            (SERVERCONFIGID, CONNECTIONNAME, SERVERID, DATABASETYPE, DATABASENAME,
             DATABASEUSERNAME, DATABASEPASSWORD, DATABASEPORT,
             CONTROLSOURCEDBNAME,
             DBINSTANCETYPE, NATURE, DBSOURCETYPE, SYSTEMSERVERCONFIGID,
             REPORTSERVERCONFIGID, ARCHIVESERVERCONFIGID, PARENTSERVERCONFIGID,
             CLIENTID, CLIENTSITEID, REMARKS,
             STATUS, VERSION, SOURCETYPE,
             CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON,
             OFFSET, MAXVALUE, FCMCONFIG, SERVICESERVERID)
        OUTPUT INSERTED.SERVERCONFIGID
        VALUES
            (@ServerConfigId, @ConnectionName, @ServerId, @DatabaseType, @DatabaseName,
             @DatabaseUserName, @DatabasePassword, @DatabasePort,
             @ControlSourceDbName,
             @DbInstanceType, @Nature, @DbSourceType, @SystemServerConfigId,
             @ReportServerConfigId, @ArchiveServerConfigId, @ParentServerConfigId,
             @ClientId, @ClientSiteId, @Remarks,
             @Status, @Version, @SourceType,
             @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn,
             @Offset, @MaxValue, @FcmConfig, @ServiceServerId)";

    public const string UPDATE_REPORT_SERVER_CONFIG_ID = @"
        UPDATE MSERVERCONFIG SET REPORTSERVERCONFIGID = @LinkedServerConfigId
        WHERE SERVERCONFIGID = @MainServerConfigId";

    public const string UPDATE_ARCHIVE_SERVER_CONFIG_ID = @"
        UPDATE MSERVERCONFIG SET ARCHIVESERVERCONFIGID = @LinkedServerConfigId
        WHERE SERVERCONFIGID = @MainServerConfigId";
}