USE [master]
GO
/****** Object:  Database [MYCS]    Script Date: 5/9/2025 10:15:11 AM ******/
CREATE DATABASE [MYCS]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'MYCS_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\MYCS.mdf' , SIZE = 54272KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'MYCS_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\MYCS.ldf' , SIZE = 284288KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
 WITH CATALOG_COLLATION = DATABASE_DEFAULT, LEDGER = OFF
GO
ALTER DATABASE [MYCS] SET COMPATIBILITY_LEVEL = 150
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [MYCS].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [MYCS] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [MYCS] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [MYCS] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [MYCS] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [MYCS] SET ARITHABORT OFF 
GO
ALTER DATABASE [MYCS] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [MYCS] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [MYCS] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [MYCS] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [MYCS] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [MYCS] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [MYCS] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [MYCS] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [MYCS] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [MYCS] SET  ENABLE_BROKER 
GO
ALTER DATABASE [MYCS] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [MYCS] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [MYCS] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [MYCS] SET ALLOW_SNAPSHOT_ISOLATION ON 
GO
ALTER DATABASE [MYCS] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [MYCS] SET READ_COMMITTED_SNAPSHOT ON 
GO
ALTER DATABASE [MYCS] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [MYCS] SET RECOVERY FULL 
GO
ALTER DATABASE [MYCS] SET  MULTI_USER 
GO
ALTER DATABASE [MYCS] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [MYCS] SET DB_CHAINING OFF 
GO
ALTER DATABASE [MYCS] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO
ALTER DATABASE [MYCS] SET TARGET_RECOVERY_TIME = 60 SECONDS 
GO
ALTER DATABASE [MYCS] SET DELAYED_DURABILITY = DISABLED 
GO
ALTER DATABASE [MYCS] SET ACCELERATED_DATABASE_RECOVERY = OFF  
GO
EXEC sys.sp_db_vardecimal_storage_format N'MYCS', N'ON'
GO
ALTER DATABASE [MYCS] SET QUERY_STORE = ON
GO
ALTER DATABASE [MYCS] SET QUERY_STORE (OPERATION_MODE = READ_WRITE, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30), DATA_FLUSH_INTERVAL_SECONDS = 900, INTERVAL_LENGTH_MINUTES = 60, MAX_STORAGE_SIZE_MB = 100, QUERY_CAPTURE_MODE = AUTO, SIZE_BASED_CLEANUP_MODE = AUTO, MAX_PLANS_PER_QUERY = 200, WAIT_STATS_CAPTURE_MODE = ON)
GO
USE [MYCS]
GO
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 8;
GO
USE [MYCS]
GO
/****** Object:  Table [dbo].[AsyncRequests]    Script Date: 5/9/2025 10:15:12 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AsyncRequests](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ServiceProcessor] [nvarchar](max) NOT NULL,
	[Module] [nvarchar](50) NOT NULL,
	[State] [int] NOT NULL,
	[ReturnCode] [int] NULL,
	[UniqueID] [nvarchar](max) NOT NULL,
	[StartTime] [datetime] NOT NULL,
	[EndTime] [datetime] NULL,
	[OriginalRequest] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[ClientRequestsFromMYCS]    Script Date: 5/9/2025 10:15:12 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ClientRequestsFromMYCS](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Requestor] [nvarchar](50) NOT NULL,
	[HighRecord] [int] NOT NULL,
	[LowRecord] [int] NOT NULL,
	[AskedForStressed] [bit] NULL,
	[ClientsReturned] [int] NOT NULL,
	[timestamp] [datetime2](7) NOT NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Machines]    Script Date: 5/9/2025 10:15:12 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Machines](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[LastUpdated] [datetime2](7) NULL,
	[UniqueIndx] [nvarchar](200) NOT NULL,
	[MachineName] [nvarchar](50) NULL,
	[IpHostname] [nvarchar](50) NULL,
	[IpAddresses] [nvarchar](max) NULL,
	[OSVer] [nvarchar](50) NULL,
	[ProcessorArch] [nvarchar](50) NULL,
	[PhysicalRam] [bigint] NULL,
	[ProcessorCount] [int] NULL,
	[ClientVersion] [nvarchar](max) NULL,
	[ProcessorID] [nvarchar](max) NULL,
	[Timezone] [nvarchar](50) NULL,
	[CurrentMachineTime] [datetime2](7) NULL,
	[RegClientTimestamp] [datetime2](7) NULL,
	[NetAdapters] [nvarchar](max) NULL,
	[PrimaryNetWired] [bit] NULL,
	[VideoAdapters] [nvarchar](max) NULL,
	[ListenSocketAddr] [nvarchar](max) NULL,
	[WSConnected] [bit] NULL,
	[OnACPower] [bit] NULL,
	[BatteryPerc] [int] NULL,
	[IdleSeconds] [int] NULL,
	[RoundTripLatency] [time](7) NULL,
	[OSRuntimeSeconds] [bigint] NULL,
	[RuntimeReceived] [bit] NULL,
	[SystemCalcCapability] [int] NULL,
	[GPUCalcCapability] [float] NULL,
	[OutstandingCalls] [int] NULL,
	[ServiceCallsPerHour] [float] NULL,
	[WebSockEndPointServer] [nvarchar](max) NULL,
	[RAvCPU] [float] NULL,
	[RPercCPUOver80] [float] NULL,
	[RAvMemAvail] [float] NULL,
	[RAvPgSec] [float] NULL,
	[RPercPgSecOver500] [float] NULL,
	[RAvDiskQ] [float] NULL,
	[RPercDiskQOver20] [float] NULL,
	[RTimestamp] [datetime2](7) NULL,
	[Zombie] [bit] NOT NULL,
	[ConnTime]  AS (CONVERT([time],CONVERT([datetime],[LastUpdated],(127))-CONVERT([datetime],[RegClientTimestamp]),(114))),
	[WebSockEPServerInstance] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[ModelsInVRam]    Script Date: 5/9/2025 10:15:12 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ModelsInVRam](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[LastUpdated] [datetime2](7) NULL,
	[MachineName] [nvarchar](50) NULL,
	[ModelDataJson] [nvarchar](max) NULL,
	[MYCSServer] [nvarchar](50) NULL,
	[LatestModelExpiry] [datetime2](7) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[ModulesOnMachines]    Script Date: 5/9/2025 10:15:12 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ModulesOnMachines](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[MachineID] [int] NOT NULL,
	[ModuleID] [int] NULL,
	[AvModuleExecutionTime] [float] NULL,
	[StDevModuleExecutionTime] [float] NULL,
	[AvModuleProcessorTime] [float] NULL,
	[AvModuleRamUseWS] [bigint] NULL,
	[AvModuleRamUsePB] [bigint] NULL,
	[JsonBlobContext] [nvarchar](max) NULL,
	[JsonValidationParm] [int] NULL,
 CONSTRAINT [PK__ModulesO__3214EC07AB06A386] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[RegisteredModules]    Script Date: 5/9/2025 10:15:12 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RegisteredModules](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[ModuleName] [nvarchar](50) NOT NULL,
	[Version] [float] NOT NULL,
	[ModuleBinaryFileName] [nvarchar](max) NULL,
	[ZippedBinaries] [varbinary](max) NULL,
 CONSTRAINT [PK__Register__3214EC0791D10D12] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Index [singlecolumnindexCliMachR]    Script Date: 5/9/2025 10:15:12 AM ******/
CREATE NONCLUSTERED INDEX [singlecolumnindexCliMachR] ON [dbo].[ClientRequestsFromMYCS]
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [singlecolumnindexCliMachR2]    Script Date: 5/9/2025 10:15:12 AM ******/
CREATE NONCLUSTERED INDEX [singlecolumnindexCliMachR2] ON [dbo].[ClientRequestsFromMYCS]
(
	[Requestor] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
/****** Object:  Index [singlecolumnindexCliMachR3]    Script Date: 5/9/2025 10:15:12 AM ******/
CREATE NONCLUSTERED INDEX [singlecolumnindexCliMachR3] ON [dbo].[ClientRequestsFromMYCS]
(
	[HighRecord] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
/****** Object:  Index [IX_ModulesOnMachines_Covering]    Script Date: 5/9/2025 10:15:12 AM ******/
CREATE NONCLUSTERED INDEX [IX_ModulesOnMachines_Covering] ON [dbo].[ModulesOnMachines]
(
	[MachineID] ASC,
	[ModuleID] ASC,
	[Id] ASC,
	[AvModuleExecutionTime] ASC,
	[StDevModuleExecutionTime] ASC,
	[AvModuleProcessorTime] ASC,
	[AvModuleRamUseWS] ASC,
	[AvModuleRamUsePB] ASC,
	[JsonValidationParm] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ClientRequestsFromMYCS] ADD  CONSTRAINT [DF__ClientReq__times__73BA3083]  DEFAULT (getdate()) FOR [timestamp]
GO
ALTER TABLE [dbo].[ModelsInVRam] ADD  CONSTRAINT [DF_ModelsInVRam_LastUpdated]  DEFAULT (getdate()) FOR [LastUpdated]
GO
/****** Object:  StoredProcedure [dbo].[sp_Conns]    Script Date: 5/9/2025 10:15:12 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_Conns]
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

SELECT TOP (1000) [Id]
      ,[LastUpdated]
      ,[UniqueIndx]
      ,[MachineName]
      ,[VideoAdapters]
      ,[ListenSocketAddr]
      ,[WSConnected]
      ,[OnACPower]
      ,[BatteryPerc]
      ,[RoundTripLatency]
      ,[RuntimeReceived]
      ,[SystemCalcCapability]
      ,[OutstandingCalls]
      ,[ServiceCallsPerHour]
      ,[WebSockEndPointServer]
      ,[RAvCPU]
      ,[RPercCPUOver80]
      ,[RAvMemAvail]
      ,[Zombie]
      ,[ConnTime]
      ,[WebSockEPServerInstance]
  FROM [MYCS].[dbo].[Machines]
  where WSConnected=1
  order by ID
END
GO
/****** Object:  StoredProcedure [dbo].[sp_DeleteVRamRecsAndCreateNew]    Script Date: 5/9/2025 10:15:12 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_DeleteVRamRecsAndCreateNew]
    @MachineName nvarchar(50),
    @ModelsInVRamJson nvarchar(max),
    @MYCSServer nvarchar(50),
    @LatestModelExpiry datetime2(7)
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRANSACTION;

    BEGIN TRY
        DELETE FROM [dbo].[ModelsInVRam]
        WHERE [MachineName] = @MachineName;

        INSERT INTO [dbo].[ModelsInVRam] ([MachineName], [ModelDataJson], [MYCSServer], [LatestModelExpiry])
        VALUES (@MachineName, @ModelsInVRamJson, @MYCSServer, @LatestModelExpiry);

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        DECLARE @ErrorMessage nvarchar(4000), @ErrorSeverity int, @ErrorState int;
        SELECT 
            @ErrorMessage = ERROR_MESSAGE(),
            @ErrorSeverity = ERROR_SEVERITY(),
            @ErrorState = ERROR_STATE();
        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
    END CATCH
END
GO
/****** Object:  StoredProcedure [dbo].[sp_FindInVramModelsOnOtherMYCS]    Script Date: 5/9/2025 10:15:12 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_FindInVramModelsOnOtherMYCS]
    @MYCSServer nvarchar(50),
    @Since datetime2(7)
AS
BEGIN
    -- Step 1: Filter ModelsInVRam to only those updated before @Since
    WITH OlderModels AS (
        SELECT 
            miv.[Id],
            miv.[LastUpdated],
            miv.[MachineName],
            miv.[ModelDataJson],
            miv.[MYCSServer],
            miv.[LatestModelExpiry]
        FROM [dbo].[ModelsInVRam] miv
        WHERE miv.[LastUpdated] >= @Since
    ),

    -- Step 2: Exclude models from the specified @MYCSServer
    NonMatchingServers AS (
        SELECT 
            om.*
        FROM OlderModels om
        WHERE LOWER(om.[MYCSServer]) != LOWER(@MYCSServer)
    ),

    -- Step 3: Join with Machines table to validate WSConnected status
    FinalFilter AS (
        SELECT 
            nms.*,
            m.[WSConnected]
        FROM NonMatchingServers nms
        INNER JOIN [dbo].[Machines] m 
            ON nms.[MachineName] = m.[MachineName]
    )

    -- Final selection filtering only connected machines
    SELECT 
        Id,
        LastUpdated,
        MachineName,
        ModelDataJson,
        MYCSServer,
        LatestModelExpiry
    FROM FinalFilter
    WHERE [WSConnected] = 1;
END
GO
/****** Object:  StoredProcedure [dbo].[spDeleteMachine]    Script Date: 5/9/2025 10:15:12 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spDeleteMachine]
	@LastUpdated datetime2,
	@UniqueIndx nvarchar(200)
AS
	DECLARE @DeletedMachineID int

	-- Get the ID
	SELECT @DeletedMachineID = Id
	FROM	Machines 
	WHERE	UniqueIndx = @UniqueIndx

	-- Also need to delete the items in the mom table that relat to this machine
	DELETE FROM ModulesOnMachines
	WHERE MachineID = @DeletedMachineID

	UPDATE  Machines 
	SET		Zombie = 'true', WSConnected = 'false'
	OUTPUT	inserted.Id
	WHERE	Id = @DeletedMachineID

	--SET @DeletedMachineID = deleted.Id;

RETURN @@IDENTITY
GO
/****** Object:  StoredProcedure [dbo].[spFindMachine]    Script Date: 5/9/2025 10:15:12 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spFindMachine]
	@MachineName nvarchar(50)
AS

	SELECT Id, LastUpdated, UniqueIndx, MachineName, IpHostname, IpAddresses, OSVer,
                    ProcessorArch, PhysicalRam, ProcessorCount, ClientVersion, ProcessorID,
                    Timezone, CurrentMachineTime, RegClientTimestamp, NetAdapters, PrimaryNetWired,
                    VideoAdapters, ListenSocketAddr, WSConnected, OnACPower, BatteryPerc, IdleSeconds,
                    RoundTripLatency, OSRuntimeSeconds, RuntimeReceived, SystemCalcCapability, GPUCalcCapability, OutstandingCalls,
                    ServiceCallsPerHour, WebSockEndPointServer, RAvCPU, RPercCPUOver80, RAvMemAvail, RAvPgSec, 
                    RPercPgSecOver500, RAvDiskQ, RPercDiskQOver20, RTimestamp
	FROM MACHINES 
	WHERE LOWER(MachineName) = LOWER(@MachineName) and Zombie='false' and WSConnected = 'true'
	ORDER BY LastUpdated DESC;


RETURN @@IDENTITY
GO
/****** Object:  StoredProcedure [dbo].[spFindMachineByID]    Script Date: 5/9/2025 10:15:12 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spFindMachineByID]
	@ID int
AS

	SELECT Id, LastUpdated, UniqueIndx, MachineName, IpHostname, IpAddresses, OSVer,
                    ProcessorArch, PhysicalRam, ProcessorCount, ClientVersion, ProcessorID,
                    Timezone, CurrentMachineTime, RegClientTimestamp, NetAdapters, PrimaryNetWired,
                    VideoAdapters, ListenSocketAddr, WSConnected, OnACPower, BatteryPerc, IdleSeconds,
                    RoundTripLatency, OSRuntimeSeconds, RuntimeReceived, SystemCalcCapability, GPUCalcCapability, OutstandingCalls,
                    ServiceCallsPerHour, WebSockEndPointServer, RAvCPU, RPercCPUOver80, RAvMemAvail, RAvPgSec, 
                    RPercPgSecOver500, RAvDiskQ, RPercDiskQOver20, RTimestamp
	FROM MACHINES 
	WHERE Id = @ID


RETURN @@IDENTITY
GO
/****** Object:  StoredProcedure [dbo].[spFindMachineByIndx]    Script Date: 5/9/2025 10:15:12 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spFindMachineByIndx]
	@UniqueIndx nvarchar(200),
	@WSConnected bit
AS

	SELECT Id, LastUpdated, UniqueIndx, MachineName, IpHostname, IpAddresses, OSVer,
                    ProcessorArch, PhysicalRam, ProcessorCount, ClientVersion, ProcessorID,
                    Timezone, CurrentMachineTime, RegClientTimestamp, NetAdapters, PrimaryNetWired,
                    VideoAdapters, ListenSocketAddr, WSConnected, OnACPower, BatteryPerc, IdleSeconds,
                    RoundTripLatency, OSRuntimeSeconds, RuntimeReceived, SystemCalcCapability, GPUCalcCapability, OutstandingCalls,
                    ServiceCallsPerHour, WebSockEndPointServer, RAvCPU, RPercCPUOver80, RAvMemAvail, RAvPgSec, 
                    RPercPgSecOver500, RAvDiskQ, RPercDiskQOver20, RTimestamp
	FROM MACHINES 
	WHERE UniqueIndx = @UniqueIndx and Zombie='false' and WSConnected = @WSConnected




RETURN @@IDENTITY
GO
/****** Object:  StoredProcedure [dbo].[spFindMachineModulesData]    Script Date: 5/9/2025 10:15:12 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spFindMachineModulesData]
	@MachineName nvarchar(50)
AS

	declare @MachineID int

	select @MachineId = Id
	FROM MACHINES 
	WHERE MachineName = UPPER(@MachineName) and Zombie='false' and WSConnected = 'true'

	-- Go to Modules on Machines
	
	select Id, (select ModuleBinaryFileName from RegisteredModules where RegisteredModules.Id = ModuleID) AS ModuleGroupName, 
	AvModuleExecutionTime, StDevModuleExecutionTime, AvModuleProcessorTime, AvModuleRamUseWS, AvModuleRamUsePB, JsonBlobContext,
	JsonValidationParm
	INTO #MachineModules
	From ModulesOnMachines
	where MachineID = @MachineID

	--select ModuleBinaryFileName
	--from RegisteredModules
	--where RegisteredModules.Id in (select ModuleID from ModulesOnMachines where MachineID = @MachineID)

	select * from #MachineModules

RETURN 0
GO
/****** Object:  StoredProcedure [dbo].[spGetAllMachines]    Script Date: 5/9/2025 10:15:12 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spGetAllMachines]
	@Zombie bit
AS

		SELECT * FROM MACHINES 
		WHERE Zombie = @Zombie


RETURN 0
GO
/****** Object:  StoredProcedure [dbo].[spGetMachinesByModelName]    Script Date: 5/9/2025 10:15:12 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spGetMachinesByModelName]
	@MYCSServer NVARCHAR(255),
    @NumMachines INT,
	@SearchModelName NVARCHAR(255)
AS
BEGIN
    SELECT DISTINCT 
        m.* -- Select all columns from the Machines table.  TODO: Fix in future
    FROM [MYCS].[dbo].[ModulesOnMachines] mom
    INNER JOIN [MYCS].[dbo].[Machines] m
        ON mom.MachineID = m.ID
    CROSS APPLY OPENJSON(mom.JsonBlobContext)
        WITH (models NVARCHAR(MAX) AS JSON) AS jmodels
    CROSS APPLY OPENJSON(jmodels.models)
        WITH (name NVARCHAR(255) '$.name') AS jmdata
    WHERE mom.JsonBlobContext IS NOT NULL
      AND TRY_CONVERT(NVARCHAR(MAX), mom.JsonBlobContext) = mom.JsonBlobContext
      AND m.WSConnected = 1
      AND (
            jmdata.name COLLATE SQL_Latin1_General_CP1_CI_AS = @SearchModelName COLLATE SQL_Latin1_General_CP1_CI_AS
         OR jmdata.name COLLATE SQL_Latin1_General_CP1_CI_AS = (@SearchModelName + ':latest') COLLATE SQL_Latin1_General_CP1_CI_AS
      )
    ORDER BY m.ID -- Added an ORDER BY clause for consistent results
    OFFSET 0 ROWS
    FETCH NEXT @NumMachines ROWS ONLY;
END


GO
/****** Object:  StoredProcedure [dbo].[spImmediateChange]    Script Date: 5/9/2025 10:15:12 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spImmediateChange]
	@LastUpdated datetime2,
	@UniqueIndx nvarchar(200), 
	@CurrentMachineTime datetime2, 
	@WSConnected bit,
	@WSEndpointAddr nvarchar(max),
	@IdleSeconds int,
	@OnACPower bit, 
	@BatteryPerc int,
	@RoundTripLatency time,
	@SystemCalcCapability int,
	@GPUCalcCapability int
AS
BEGIN
	UPDATE Machines 
	SET
		LastUpdated = @LastUpdated,
		CurrentMachineTime = @CurrentMachineTime,
		WSConnected = @WSConnected,
		WebSockEndPointServer = @WSEndpointAddr,
		IdleSeconds = @IdleSeconds,
		OnACPower = @OnACPower,
		BatteryPerc = @BatteryPerc,
		RoundTripLatency = @RoundTripLatency,
		SystemCalcCapability = CASE WHEN @SystemCalcCapability > 0 
                                 THEN @SystemCalcCapability 
                                 ELSE SystemCalcCapability END,
		GPUCalcCapability = CASE WHEN @GPUCalcCapability > 0 
                              THEN @GPUCalcCapability 
                              ELSE GPUCalcCapability END
	OUTPUT inserted.Id
	WHERE UniqueIndx = @UniqueIndx AND Zombie = 'false';
END;

RETURN @@IDENTITY

GO
/****** Object:  StoredProcedure [dbo].[spInit]    Script Date: 5/9/2025 10:15:12 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spInit]
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

INSERT INTO [dbo].[RegisteredModules] 
    ([ModuleName], [Version], [ModuleBinaryFileName], [ZippedBinaries])
VALUES
    ('mycai.py', 1.5, 'mycai.py.e.1.5', NULL),
    ('wpxAI.py', 1.5, 'wpxAI.py.e.1.5', NULL),
    ('pingstub', 1.7, 'pingstub.e.1.7', NULL),
    ('sleeper', 1.3, 'sleeper.e.1.3', NULL),
	('asyncxgen', 1.2, 'asyncxgen.e.1.2', NULL);


END
GO
/****** Object:  StoredProcedure [dbo].[spInsertAsyncRequest]    Script Date: 5/9/2025 10:15:12 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spInsertAsyncRequest]
    @ServiceProcessor NVARCHAR(MAX),
    @Module NVARCHAR(50),
    @UniqueID NVARCHAR(MAX),
    @StartTime DATETIME
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @NewID INT;

    INSERT INTO [dbo].[AsyncRequests]
    (
        [ServiceProcessor],
        [Module],
        [State],
        [UniqueID],
        [StartTime]
    )
    VALUES
    (
        @ServiceProcessor,
        @Module,
        0, -- Setting State to 0 as specified
        @UniqueID,
        @StartTime
    );

    -- Get the ID of the newly inserted record
	SELECT @@IDENTITY
END
GO
/****** Object:  StoredProcedure [dbo].[spInsertMachine]    Script Date: 5/9/2025 10:15:12 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spInsertMachine]
	@LastUpdated datetime2,
	@UniqueIndx nvarchar(200), 
	@MachineName nvarchar(50), 
	@IpHostname nvarchar(50), 
	@IpAddresses nvarchar (MAX), 
	@OSVer nvarchar(50), 
	@ProcessorArch nvarchar(50), 
	@PhysicalRam bigint, 
	@ProcessorCount int, 
	@ClientVersion nvarchar(MAX), 
	@ProcessorID nvarchar(MAX), 
	@Timezone nvarchar(50), 
	@CurrentMachineTime datetime2, 
	@RegClientTimestamp datetime2, 
	@NetAdapters nvarchar(MAX), 
	@PrimaryNetWired bit, 
	@VideoAdapters nvarchar(max), 
	@ListenSocketAddr nvarchar(max), 
	@WSConnected bit, 
	@OnACPower bit, 
	@BatteryPerc int, 
	@IdleSeconds int, 
	@RoundTripLatency time, 
	@OSRuntimeSeconds bigint, 
	@RuntimeReceived bit, 
	@SystemCalcCapability int,
	@GPUCalcCapability float,
	@OutstandingCalls int, 
	@ServiceCallsPerHour float, 
	@WebSockEndPointServer nvarchar(max), 
	@RAvCPU float, 
	@RPercCPUOver80 float, 
	@RAvMemAvail float, 
	@RAvPgSec float, 
	@RPercPgSecOver500 float, 
	@RAvDiskQ float, 
	@RPercDiskQOver20 float, 
	@RTimestamp datetime2
AS

	--DECLARE @Id int
	--SELECT @Id = Id FROM Machines WHERE UniqueIndx = @UniqueIndx
	--IF @Id > 0
	--	DELETE FROM ModulesOnMachines WHERE MachineID = @Id
		--DELETE FROM Machines WHERE Id = @Id

	-- Make all existing records zombies
	UPDATE Machines
	SET Zombie = 'true'
	WHERE UniqueIndx = @UniqueIndx
		

	INSERT INTO Machines (LastUpdated, UniqueIndx, MachineName, IpHostname, IpAddresses, OSVer,
                    ProcessorArch, PhysicalRam, ProcessorCount, ClientVersion, ProcessorID,
                    Timezone, CurrentMachineTime, RegClientTimestamp, NetAdapters, PrimaryNetWired,
                    VideoAdapters, ListenSocketAddr, WSConnected, OnACPower, BatteryPerc, IdleSeconds,
                    RoundTripLatency, OSRuntimeSeconds, RuntimeReceived, SystemCalcCapability, GPUCalcCapability, OutstandingCalls,
                    ServiceCallsPerHour, WebSockEndPointServer, RAvCPU, RPercCPUOver80, RAvMemAvail, RAvPgSec, 
                    RPercPgSecOver500, RAvDiskQ, RPercDiskQOver20, RTimestamp, Zombie )
					OUTPUT INSERTED.Id
                    VALUES(@LastUpdated, @UniqueIndx, @MachineName, @IpHostname, @IpAddresses, @OSVer,
                    @ProcessorArch, @PhysicalRam, @ProcessorCount, @ClientVersion, @ProcessorID,
                    @Timezone, @CurrentMachineTime, @RegClientTimestamp, @NetAdapters, @PrimaryNetWired,
                    @VideoAdapters, @ListenSocketAddr, @WSConnected, @OnACPower, @BatteryPerc, @IdleSeconds,
                    @RoundTripLatency, @OSRuntimeSeconds, @RuntimeReceived, @SystemCalcCapability, @GPUCalcCapability, @OutstandingCalls,
                    @ServiceCallsPerHour, @WebSockEndPointServer, @RAvCPU, @RPercCPUOver80, @RAvMemAvail, @RAvPgSec, 
                    @RPercPgSecOver500, @RAvDiskQ, @RPercDiskQOver20, @RTimestamp, 'false' )


RETURN @@IDENTITY
GO
/****** Object:  StoredProcedure [dbo].[spMachineSessionCleanup]    Script Date: 5/9/2025 10:15:12 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spMachineSessionCleanup]
AS

declare @ThirtyMins datetime
declare @TwoHours datetime
declare @MyTableVar table ( ID int NOT NULL)
select @ThirtyMins = '00:30'
select @TwoHours = '02:00'

declare @RowsAffected int

DELETE FROM Machines
OUTPUT DELETED.Id
    INTO @MyTableVar  
WHERE Zombie = 'True' and (GETUTCDATE() -  convert(datetime, LastUpdated)) > @TwoHours

select @RowsAffected = @@ROWCOUNT

-- Delete the rows in MOM related to the deleted items in Machine table
DELETE ModulesOnMachines
where MachineID = (select ID from @MyTableVar)

UPDATE Machines
SET Zombie = 'True'
WHERE Zombie = 'False' and (GETUTCDATE() -  convert(datetime, LastUpdated)) > @ThirtyMins

select @RowsAffected = @@ROWCOUNT + @RowsAffected

select @RowsAffected

RETURN 0
GO
/****** Object:  StoredProcedure [dbo].[spMarkDroppedConns]    Script Date: 5/9/2025 10:15:12 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spMarkDroppedConns]
	@LastUpdated datetime2,
	@UniqueIndx nvarchar(200)

AS

	-- Mark the connection as offline (websocket is not connected)

	-- TODO: There is an optimization possible here where we can input the list of indexes separated by a 
	-- space char and then use the STRING_SPLIT TSQL function to break them out and loop in teh SP

	UPDATE	Machines 
	SET		LastUpdated = @LastUpdated,
			WSConnected = 'false'
	OUTPUT  inserted.Id
	WHERE	UniqueIndx = @UniqueIndx and Zombie = 'false'

RETURN @@IDENTITY
GO
/****** Object:  StoredProcedure [dbo].[spRequestClientBatch]    Script Date: 5/9/2025 10:15:12 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spRequestClientBatch]
	@MYCSServer nvarchar(50),
	@NumMachines int,
	@Stressed bit,
	@Type nvarchar(50)
AS
	declare @HighestIndexCR int  -- Highest ID record in the ClientRequests table
	declare @LowestClientID int  -- Lowest Client ID in the Machines Table
	declare @HighestClientID int -- Highest CLient ID in the Machines Table
	declare @IDToStartAt int -- The minimum ID to start the query from

	select TOP(1) @HighestIndexCR = HighRecord
	from ClientRequestsFromMYCS
	order by timestamp DESC

	IF @HighestIndexCR IS NULL
	BEGIN
		-- There are no records in the Client Requests table, get teh range of clients available
		select @LowestClientID = min(ID), @HighestClientID = max(ID) 
		from Machines 
		where WSConnected = 'true' and Zombie = 'false'  -- start from the earliest record in Machines table
		
		IF @LowestClientID IS NULL
		BEGIN
			-- Return an error as no clients
			RETURN -1
		END
		select @IDToStartAt = @LowestClientID
	END
	ELSE
	BEGIN
		select @IDToStartAt = @HighestIndexCR
	END

	-- Will return the next @NumMachines valid machines 
	select TOP(@NumMachines) *  -- TODO: Spell out the row names instead of '*'
	INTO #TempCli
	FROM Machines
	where ID > @IDToStartAt and WSConnected = 'true' and Zombie = 'false'
	ORDER BY ID ASC

	declare @ReturnedRows int 
	SELECT @ReturnedRows = @@ROWCOUNT

	declare @maxidc int
	declare @minidc int
	SELECT @maxidc = max(ID), @minidc = min(ID) from #TempCli

	IF @ReturnedRows = @NumMachines
	BEGIN
		-- We got what we needed, add a row and return the results
		INSERT INTO ClientRequestsFromMYCS (Requestor, HighRecord, LowRecord, AskedForStressed, ClientsReturned)
		VALUES (@MYCSServer, @maxidc, @minidc, 'false', @ReturnedRows)

		SELECT * FROM #TempCli -- TODO: Add in teh row names 
		RETURN 0
	END

	-- We didnt get what we wanted. Loop to the bottom of the list and get as many as we can there
	select @LowestClientID = min(ID) 
	from Machines 
	where WSConnected = 'true' and Zombie = 'false'

	select TOP( @NumMachines) *  -- TODO: Spell out the row names instead of '*'
	into #TempCli2
	FROM Machines
	where ID >= @LowestClientID and WSConnected = 'true' and Zombie = 'false'
	ORDER BY ID ASC

	-- Get the number of affected rows from the previous operation
	SELECT @ReturnedRows = @@ROWCOUNT;

	-- Check if there are no rows to process
	IF (@ReturnedRows = 0)
	BEGIN
		PRINT 'No rows were returned, exiting procedure';
		-- Return immediately since there is nothing to process
		RETURN 0;
	END

	-- Get max and min IDs from the temporary table
	SELECT @maxidc = MAX(ID), @minidc = MIN(ID) FROM #TempCli2;

	-- Insert record into ClientRequestsFromMYCS with the current results
	INSERT INTO ClientRequestsFromMYCS 
	(
		Requestor, 
		HighRecord, 
		LowRecord, 
		AskedForStressed, 
		ClientsReturned
	)
	VALUES 
	(
		@MYCSServer, 
		@maxidc, 
		@minidc, 
		'false', 
		@ReturnedRows
	);

	-- Return the results from the temporary table
	SELECT * FROM #TempCli2;

	-- Exit the procedure successfully
	RETURN 0;

GO
/****** Object:  StoredProcedure [dbo].[spResetDB]    Script Date: 5/9/2025 10:15:12 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spResetDB]
AS

DELETE ClientRequestsFromMYCS
DelETE Machines
Delete ModulesOnMachines
Delete AsyncRequests

RETURN 0
GO
/****** Object:  StoredProcedure [dbo].[spSendJsonRequest]    Script Date: 5/9/2025 10:15:12 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spSendJsonRequest]
AS
BEGIN
    DECLARE @json NVARCHAR(MAX) = 
    '{
        "MachineName": "YourMachineName",
        "ModelsInVRamNow": [
            {
                "name": "Model1",
                "model": "Model1Name",
                "size": 1024,
                "digest": "digest1",
                "details": {
                    "parent_model": "ParentModel1",
                    "format": "Format1",
                    "family": "Family1",
                    "families": ["Family1", "Family2"],
                    "parameter_size": "ParameterSize1",
                    "quantization_level": "QuantizationLevel1"
                },
                "expires_at": "2024-01-01T00:00:00",
                "size_vram": 2048
            }
        ],
        "ClientTimeStamp": "2023-01-01T00:00:00"
    }';

    DECLARE @obj INT
    DECLARE @responseText VARCHAR(MAX)
    DECLARE @statusCode INT
    DECLARE @url VARCHAR(255) = 'https://localhost:7072/api/ModelInVRamUpdate'

    -- Create the WinHttpRequest object
    EXEC sp_OACreate 'WinHttp.WinHttpRequest', @obj OUT

    -- Set option to ignore SSL errors
    EXEC sp_OASetProperty @obj, 'Option', 4, 13056 -- Ignore all SSL errors

    -- Open the request
    EXEC sp_OAMethod @obj, 'open', NULL, 'POST', @url, FALSE

    -- Set headers
    EXEC sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Type', 'application/json'

    -- Send JSON data
    EXEC sp_OAMethod @obj, 'send', NULL, @json

    -- Get status code
    EXEC sp_OAGetProperty @obj, 'status', @statusCode OUT

    -- Get response text
    EXEC sp_OAGetProperty @obj, 'responseText', @responseText OUT

    -- Display results
    PRINT 'Status Code: ' + CONVERT(VARCHAR, @statusCode)
    PRINT 'Response Text: ' + ISNULL(@responseText, 'No response text')

    -- Cleanup
    EXEC sp_OADestroy @obj
END
GO
/****** Object:  StoredProcedure [dbo].[spUpdateAsyncRequest]    Script Date: 5/9/2025 10:15:12 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spUpdateAsyncRequest]
    @LastUpdated DATETIME,
    @ServiceProcessor NVARCHAR(MAX),
    @State INT,
    @CausalityID NVARCHAR(MAX),
    @ReturnCode INT
AS
BEGIN
    SET NOCOUNT ON;

    -- Check if a record with the given CausalityID exists
    IF EXISTS (SELECT 1 FROM [dbo].[AsyncRequests] WHERE [UniqueID] = @CausalityID)
    BEGIN
        -- Update existing record
        UPDATE [dbo].[AsyncRequests]
        SET 
            [EndTime] = @LastUpdated,
            [State] = @State,
            [ReturnCode] = @ReturnCode
        WHERE 
            [UniqueID] = @CausalityID
    END
    ELSE
    BEGIN
        -- Insert new record
        INSERT INTO [dbo].[AsyncRequests]
        (
            [ServiceProcessor],
            [Module],
            [State],
            [ReturnCode],
            [UniqueID],
            [StartTime],
            [EndTime]
        )
        VALUES
        (
            @ServiceProcessor,
            'Unknown',
            @State,
            @ReturnCode,
            @CausalityID,
            @LastUpdated,
            @LastUpdated
        )
    END
END
GO
/****** Object:  StoredProcedure [dbo].[spUpdateMachine]    Script Date: 5/9/2025 10:15:12 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spUpdateMachine]
	@LastUpdated datetime2,
	@UniqueIndx nvarchar(200), 
	@CurrentMachineTime datetime2, 
	@WSConnected bit, 
	@OnACPower bit, 
	@BatteryPerc int, 
	@IdleSeconds int, 
	@HardNetWired bit,
	@IPAddresses nvarchar(max),
	@NetAdapters nvarchar(max),
	@VideoAdapters nvarchar(max),
	@OSRuntimeSeconds bigint, 
	@RoundTripLatency time(7),
	@RuntimeReceived bit, 
	@SystemCalcCapability int, 
	@GPUCalcCapability float,
	@ServiceCallsPerHour float, 
	@RAvCPU float, 
	@RPercCPUOver80 float, 
	@RAvMemAvail float, 
	@RAvPgSec float, 
	@RPercPgSecOver500 float, 
	@RAvDiskQ float, 
	@RPercDiskQOver20 float, 
	@RTimestamp datetime2
AS

	-- Update the main Machine Record with the periodic Data
	UPDATE	Machines 
	SET		LastUpdated = @LastUpdated,
			CurrentMachineTime = @CurrentMachineTime,
			WSConnected = @WSConnected,
			OnACPower= @OnACPower,
			BatteryPerc = @BatteryPerc,
			IdleSeconds = @IdleSeconds,
			PrimaryNetWired = @HardNetWired,
			IpAddresses = @IPAddresses,
			NetAdapters = @NetAdapters,
			VideoAdapters = @VideoAdapters,
			OSRuntimeSeconds = @OSRuntimeSeconds,
			RoundTripLatency = @RoundTripLatency,
			RuntimeReceived = @RuntimeReceived,
			SystemCalcCapability = @SystemCalcCapability,
			GPUCalcCapability = @GPUCalcCapability,
			ServiceCallsPerHour = @ServiceCallsPerHour,
			RAvCPU = @RAvCPU,
			RPercCPUOver80 = @RPercCPUOver80,
			RAvMemAvail = @RAvMemAvail,
			RAvPgSec = @RAvPgSec,
			RPercPgSecOver500 = @RPercPgSecOver500,
			RAvDiskQ = @RAvDiskQ,
			RPercDiskQOver20 = @RPercDiskQOver20,
			RTimestamp = @RTimestamp
	OUTPUT inserted.Id
	WHERE UniqueIndx = @UniqueIndx and zombie = 'false' and WSConnected='true'

RETURN @@IDENTITY
GO
/****** Object:  StoredProcedure [dbo].[spUpdateMachineTimeStamp]    Script Date: 5/9/2025 10:15:12 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spUpdateMachineTimeStamp]
	@LastUpdated datetime2,
	@UniqueIndx nvarchar(200)
AS

	-- Update the main Machine Record with the periodic Data
	UPDATE	Machines 
	SET		LastUpdated = @LastUpdated
	WHERE UniqueIndx = @UniqueIndx and zombie = 'false' and WSConnected='true'

RETURN @@IDENTITY
GO
/****** Object:  StoredProcedure [dbo].[spUpdateMachineTimeStampID]    Script Date: 5/9/2025 10:15:12 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spUpdateMachineTimeStampID]
	@LastUpdated datetime2,
	@ID int
AS
	-- Update the main Machine Record with the periodic Data
	UPDATE	Machines 
	SET		LastUpdated = @LastUpdated, Zombie = 'false'
	WHERE ID = @ID

RETURN @@IDENTITY
GO
/****** Object:  StoredProcedure [dbo].[spUpdateMachineWSConnection]    Script Date: 5/9/2025 10:15:12 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spUpdateMachineWSConnection]
	@LastUpdated datetime2,
	@UniqueIndx nvarchar(200),
	@ListenSocketAddr nvarchar(max)
AS

	-- Update the main Machine Record with the periodic Data
	UPDATE	Machines 
	SET		LastUpdated = @LastUpdated,
			ListenSocketAddr = @ListenSocketAddr,
			WSConnected = 'true'
	WHERE UniqueIndx = @UniqueIndx and zombie = 'false'

RETURN @@IDENTITY
GO
/****** Object:  StoredProcedure [dbo].[spUpdateMachineWSConnectionUsingID]    Script Date: 5/9/2025 10:15:12 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spUpdateMachineWSConnectionUsingID]
	@LastUpdated datetime2,
	@ID int,
	@ListenSocketAddr nvarchar(max),
	@PID int
AS

	-- Update the main Machine Record with the periodic Data
	UPDATE	Machines 
	SET		LastUpdated = @LastUpdated,
			ListenSocketAddr = @ListenSocketAddr,
			WebSockEPServerInstance = @PID,
			WSConnected = 'true',
			Zombie = 'false'
	WHERE Id = @ID

RETURN @@IDENTITY
GO
/****** Object:  StoredProcedure [dbo].[spUpdateWSConnectedStatusNewConnsInstance]    Script Date: 5/9/2025 10:15:12 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spUpdateWSConnectedStatusNewConnsInstance]
    @WebSockServerEP NVARCHAR(MAX),
    @PID INT
AS
BEGIN
    SET NOCOUNT ON;

    -- Update only records matching the endpoint but different instance
    UPDATE [dbo].[Machines]
    SET [WSConnected] = 0
    WHERE LOWER([WebSockEndPointServer]) = LOWER(@WebSockServerEP) 
      AND [WebSockEPServerInstance] <> @PID;

    -- Return the number of rows affected
    SELECT @@ROWCOUNT AS RowsUpdated;
END

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Serialized objects into text here' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Machines', @level2type=N'COLUMN',@level2name=N'IpAddresses'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Serialized objects into text here' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Machines', @level2type=N'COLUMN',@level2name=N'NetAdapters'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Serialized objects into text here' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Machines', @level2type=N'COLUMN',@level2name=N'VideoAdapters'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Convert TimeSpan into seconds' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Machines', @level2type=N'COLUMN',@level2name=N'OSRuntimeSeconds'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Everything that starts with an R represents recent runtime data to assess performance and usage on PC' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Machines', @level2type=N'COLUMN',@level2name=N'RAvCPU'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Used to mark a record that has been deleted but we want to investigate why' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Machines', @level2type=N'COLUMN',@level2name=N'Zombie'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Need to store the runtime stats of the module on the specific machine' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ModulesOnMachines', @level2type=N'COLUMN',@level2name=N'AvModuleExecutionTime'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Should be stored with .ToLower' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RegisteredModules', @level2type=N'COLUMN',@level2name=N'ModuleName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The binary zip file' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RegisteredModules', @level2type=N'COLUMN',@level2name=N'ZippedBinaries'
GO
USE [master]
GO
ALTER DATABASE [MYCS] SET  READ_WRITE 
GO