ek1U2g28

1DROP TABLE IF EXISTS dbo.Customers;
2
3CREATE TABLE dbo.Customers 
4(
5	CustomerSecretID VARCHAR(255) NOT NULL CONSTRAINT DF_Customers_CustomerSecretID DEFAULT NEWID(),
6	SteamID BIGINT NOT NULL,
7	SteamName NVARCHAR(255) NULL,
8	DiscordTag NVARCHAR(255) NULL,
9	Email VARCHAR(255) NULL,	
10	CreateDate DATETIME2(0) NOT NULL CONSTRAINT DF_Customers_CreateDate DEFAULT SYSDATETIME(),
11	CONSTRAINT PK_Customers PRIMARY KEY (CustomerSecretID)
12);
13
14INSERT INTO dbo.Customers (SteamID, SteamName, DiscordTag, Email) 
15VALUES (76561198285897058, 'MCrow', 'MCrow#2847', 'plugins@restoremonarchy.com');
16
17SELECT * FROM dbo.Customers;
18
19DROP TABLE IF EXISTS dbo.ProductsOwned;
20
21CREATE TABLE dbo.ProductsOwned 
22(
23	CustomerSecretID VARCHAR(255) NOT NULL,
24	PluginId INT NOT NULL,
25	CreateDate DATETIME2(0) NOT NULL CONSTRAINT DF_ProductsOwned_CreateDate DEFAULT SYSDATETIME(),
26	CONSTRAINT FK_ProductsOwned_CustomerSecretID FOREIGN KEY (CustomerSecretID) REFERENCES dbo.Customers (CustomerSecretID)
27);
28
29INSERT INTO dbo.ProductsOwned (CustomerSecretID, PluginId) VALUES ('9040C094-E4DC-473E-A43C-26DFF24480FD', 1);
30
31SELECT * FROM dbo.ProductsOwned;
32
33SELECT p.*, c.* FROM dbo.ProductsOwned as p INNER JOIN dbo.Customers as c ON p.CustomerSecretID = c.CustomerSecretID;
34
35DROP TABLE IF EXISTS dbo.CustomerServers;
36
37CREATE TABLE dbo.CustomerServers 
38(
39	CustomerSecretID VARCHAR(255) NOT NULL,
40	ServerIP VARCHAR(255) NOT NULL,
41	ServerPort INT NOT NULL,
42	ServerName NVARCHAR(255) NULL,
43	LastActivity DATETIME2(0) NOT NULL,
44	CreateDate DATETIME2(0) NOT NULL CONSTRAINT DF_CustomerServers_CreateDate DEFAULT SYSDATETIME(),
45	CONSTRAINT FK_CustomerServers_CustomerSecretID FOREIGN KEY (CustomerSecretID) REFERENCES dbo.Customers (CustomerSecretID)
46);
47
48IF (NOT EXISTS(SELECT * FROM dbo.CustomerServers WHERE ServerIP = '46.242.130.177' AND ServerPort = 27015))
49BEGIN 
50    INSERT INTO dbo.CustomerServers (CustomerSecretID, ServerIP, ServerPort, ServerName, LastActivity) 
51    VALUES('9040C094-E4DC-473E-A43C-26DFF24480FD', '46.242.130.177', 27015, '#1 RestoreMonarchy', GETDATE()); 
52END 
53ELSE 
54BEGIN 
55    UPDATE dbo.CustomerServers 
56    SET LastActivity = GetDate(), ServerName = '#1 RestoreMonarchy'
57    WHERE ServerIP = '46.242.130.177' AND ServerPort = 27015;
58END 
59-- hi
60--IF (NOT EXISTS(SELECT * FROM dbo.CustomerServers WHERE ServerIP = @ServerIP AND ServerPort = @ServerPort))
61--BEGIN 
62--    INSERT INTO dbo.CustomerServers (CustomerSecretID, ServerIP, ServerPort, ServerName, LastActivity) 
63--    VALUES(@CustomerSecretID, @ServerIP, @ServerPort, @ServerName, GETDATE()); 
64--END 
65--ELSE 
66--BEGIN 
67--    UPDATE dbo.CustomerServers 
68--    SET LastActivity = GetDate(), ServerName = @ServerName
69--    WHERE ServerIP = @ServerIP AND ServerPort = @ServerPort;
70--END 
71
72SELECT * FROM dbo.CustomerServers;