-- ScadaBridge env2 database setup
-- Creates env2 logical databases on an existing scadabridge-mssql instance.
-- Idempotent: re-runs are no-ops. Assumes setup.sql has already run
-- (i.e. the scadabridge_app login already exists).

-- Create env2 databases
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = 'ScadaBridgeConfig2')
    CREATE DATABASE ScadaBridgeConfig2;
GO

IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = 'ScadaBridgeMachineData2')
    CREATE DATABASE ScadaBridgeMachineData2;
GO

-- Grant db_owner on ScadaBridgeConfig2
USE ScadaBridgeConfig2;
GO
IF NOT EXISTS (SELECT name FROM sys.database_principals WHERE name = 'scadabridge_app')
    CREATE USER scadabridge_app FOR LOGIN scadabridge_app;
GO
ALTER ROLE db_owner ADD MEMBER scadabridge_app;
GO

-- Grant db_owner on ScadaBridgeMachineData2
USE ScadaBridgeMachineData2;
GO
IF NOT EXISTS (SELECT name FROM sys.database_principals WHERE name = 'scadabridge_app')
    CREATE USER scadabridge_app FOR LOGIN scadabridge_app;
GO
ALTER ROLE db_owner ADD MEMBER scadabridge_app;
GO