-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~  Add new Server  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--      <Hostname>     =  (Eg: 'VITALDC401')
--      <IP>           =  (Eg: '100.64.28.11')
--      <Description>  =  (Eg: 'P4 IIS Server' / 'NONP4 DataBase Server' / 'Beta and Stage Database Server')
--      <Server_Type>  =  'Armor IIS Server' / 'Armor Database Server' / 'Azure IIS Server' / 'Azure Database Server' / 'Integration Server' / 'Storage Server' / 'AD Server' / 'CACHE Server' / 'Load Balancer'
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
IF NOT EXISTS (SELECT * FROM SMDServers WHERE hostname = '<Hostname>')
BEGIN
    INSERT INTO SMDServers (hostname, ip, description, type) 
    VALUES 
    (
        '<Hostname>', 
        '<IP>', 
        '<Description>', 
        '<Server_Type>' 
    ); 
END
GO


-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~  Add new Deployment  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--      <Environment>    =  'Live' / 'Stage' / 'Beta'
--      <Product>        =  'VitalDx' / 'VitalCollect' / 'VitalHub'
--      <Created_Date>   =  '2022-09-05 13:46:50.947' / NULL
--      <Static_Application>   =  'VALive-Static v6.87' / 'VALive-Static v6.85' / 'VABeta-Static v6.87' / 'VABeta-Static v6.85'
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
IF NOT EXISTS (SELECT * FROM SMDDeployments WHERE deployment_name = '<Deployment_Name>' AND type = '<Environment>')
BEGIN
    INSERT INTO SMDDeployments (deployment_name, type, created_date, description, db_name, db_username, db_password, product, version, static_application) 
    VALUES 
    (
        '<Deployment_Name>', 
        '<Environment>', 
        '<Created_Date>', 
        '<Description>', 
        '<Database_Name>', 
        '<Database_Username>', 
        '<Database_Password>', 
        '<Product>', 
        '<Version>', 
        '<Static_Application>' 
    ); 
END
GO


-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~  Add new Applications  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--      <Application_Path>  =  Folder Path (Eg: E:/VitalAxis/Websites-IIS/VALive-MYLIS)
--      <Application_Type>  =  'Web-Site' / 'Windows-Services' / 'Integration-Services' / 'Elasticsearch-service'
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
IF NOT EXISTS (SELECT * FROM SMDApplications WHERE application_name = '<Application_Name>')
BEGIN
    INSERT INTO SMDApplications (application_name, deployment_id, source_path, target_path, type) 
    VALUES 
    (
        '<Application_Name>', 
        (SELECT deployment_id FROM SMDDeployments WHERE deployment_name = '<Deployment_Name>' AND type = '<Environment>'), 
        '<Artifactory_Path>', 
        '<Application_Path>', 
        '<Application_Type>' 
    ); 
END
GO


-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~  Add new Application-Server Mapping  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--      <Application_Name>            =  (Eg: 'VALive-LIS')
--      <Primary_Server_Hostname>     =  (Eg: 'VITALWEB403')
--      <Secondary_Server_Hostname>   =  (Eg: 'VITALWEB404')
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
INSERT INTO SMDApplicationServerMapping (application_id, primary_server_id, secondary_server_id) 
VALUES 
(
    (SELECT application_id FROM SMDApplications WHERE application_name = '<Application_Name>'), 
    (SELECT server_id FROM SMDServers WHERE hostname = '<Primary_Server_Hostname>'), 
    (SELECT server_id FROM SMDServers WHERE hostname = '<Secondary_Server_Hostname>') 
); 
GO


-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~  Add new Azure Application  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--      <Environment>  =  'Live' / 'Stage' / 'QC'
--      <Team>  =  'VitalHub' / 'VitalEdge' / 'VitalOrders'
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
IF NOT EXISTS (SELECT * FROM SMDAzureApplications WHERE application_name = '<Application_Name>')
BEGIN
    INSERT INTO SMDAzureApplications (application_name, application_type, subscription, resource_group, location, source_path, environment, team, email, is_active) 
    VALUES 
    (
        '<Application_Name>', 
        '<Application_Type>', 
        '<Subscription>', 
        '<Resource_Group>', 
        '<Location>', 
        '<Source_Path>', 
        '<Environment>', 
        '<Team>', 
        '<Email>', 
        1 
    ); 
END
GO