namespace FrameworkDAL.Query.Server
{
    public static class ServerQB
    {
        // Criteria (ApplyCriteria) is appended directly after "WHERE 1 = 1" below,
        // so this constant must NOT close the FilteredServer CTE.
        // MSERVER has no dedicated code column, so ServerPicklistAutoCode mirrors
        // ServerPicklistId (same convention used by GET_SELECTLIST_DBINSTANCE below).
        public const string GET_SELECTLIST_SERVER_BASE = @"
    WITH FilteredServer AS (
        SELECT
            SERVERID          AS ServerPicklistId,
            SERVERID          AS ServerPicklistAutoCode,
            SERVERNAME        AS ServerPicklistName,
            SERVERIP          AS ServerPicklistIp,
            SERVERMACHINENAME AS ServerPicklistMachineName
        FROM MSERVER
        WHERE 1 = 1";

        public const string GET_SELECTLIST_SERVER_PAGING_SUFFIX = @"
    ),
    PagedServer AS (
        SELECT *, ROW_NUMBER() OVER (ORDER BY ServerPicklistId) AS RowNum
        FROM FilteredServer
    )
    SELECT
        ServerPicklistId,
        ServerPicklistAutoCode,
        ServerPicklistName,
        ServerPicklistIp,
        ServerPicklistMachineName
    FROM PagedServer
    WHERE (@firstnumber = -1 AND @maxresult = -1) OR (RowNum BETWEEN @firstnumber AND @maxresult);";

        public const string GET_SELECTLIST_SERVER_COUNT_BASE = @"
    SELECT COUNT(*)
    FROM MSERVER
    WHERE 1 = 1";

        // Required index: IX_DBINSTANCE_PK already covered by primary key
        public const string GET_DBINSTANCE_BY_ID = @"
            SELECT
                DI.DBINSTANCEID          AS DBInstanceId,
                DI.DBINSTANCENAME        AS DBInstanceName,
                DI.DBTYPEID              AS DBTypeId,
                DT.DBTYPENAME            AS DBTypeName,
                DT.DBTYPEDBDRIVERSTRING  AS DBTypeDBDriverString,
                DI.DBINSTANCEUSERNAME    AS DBInstanceUserName,
                DI.DBINSTANCEPASSWORD    AS DBInstancePassword,
                DI.DBINSTANCEDATABASE    AS DBInstanceDataBase,
                DI.DBINSTANCECONNECTIONSTRING AS DBInstanceConnectionString,
                DI.SERVERID              AS ServerId,
                S.SERVERNAME             AS ServerName,
                S.SERVERIP               AS ServerIp,
                S.SERVERMACHINENAME      AS ServerMachineName,
                DI.STATUS                AS DBInstanceStatus,
                DI.SOURCETYPE            AS DBInstanceSourceType
            FROM  DBINSTANCE DI
            LEFT JOIN MDBTYPE DT ON DI.DBTYPEID = DT.DBTYPEID
            LEFT JOIN MSERVER S  ON DI.SERVERID  = S.SERVERID
            WHERE DI.DBINSTANCEID = @DBInstanceId";

        public const string GET_SELECTLIST_DBINSTANCE = @"
            WITH Paged AS (
                SELECT
                    DI.DBINSTANCEID   AS Id,
                    DI.DBINSTANCEID   AS Code,
                    DI.DBINSTANCENAME AS Name,
                    ROW_NUMBER() OVER (ORDER BY DI.DBINSTANCEID) AS RowNum
                FROM DBINSTANCE DI
                WHERE DI.STATUS = 1
            )
            SELECT Id, Code, Name FROM Paged
            WHERE (@FirstNumber = -1 AND @MaxResult = -1)
               OR (RowNum BETWEEN @FirstNumber AND @MaxResult)";
    }
}