-- ============================================================================
-- 04_ERP_Database.sql
-- SQLio v2 - nieuwe installatie, stap 4: een ERP-database klaarmaken voor SQLio
--
-- Draaien op: de ERP-DATABASE ZELF (SSMS), niet op SQLioDB. Per database die
-- SQLio gebruikt één keer, bijvoorbeeld voor Test en Productie, en per vestiging.
--
-- Vul hieronder eerst de login in waarmee SQLio verbinding maakt met deze
-- database: dezelfde login als in de connectiestring die je in SQLio bij de
-- omgeving invoert. De login moet op deze SQL Server al bestaan.
--
-- Wat het doet:
--   1. Rol SQLioRole, en de login als database-gebruiker in die rol.
--   2. Rechten: VIEW DEFINITION (tabellen, views en procedures kunnen bekijken bij
--      het inrichten) en, als @LeesrechtOpAlles = 1, SELECT op alle tabellen en
--      views in schema dbo.
--      EXECUTE op ERP-procedures die een API of dataset gebruikt, en INSERT/UPDATE/
--      DELETE voor schrijvende API's, geef je per object; SQLio meldt het als dat
--      ontbreekt ("Rechten ontbreken").
--   3. Webhook-queue SQLio_WebhookQueue met index (zonder filter).
--   4. Procedure SQLio_CreateDeleteTableTrigger, waarmee SQLio de webhooktriggers
--      aanmaakt en verwijdert.
--
-- Mag vaker gedraaid worden: wat al bestaat wordt overgeslagen.
-- Alles in één transactie: bij een fout wordt niets vastgelegd.
-- Na afloop: in SQLio bij Omgevingen de connectie testen; de status is "Gereed".
-- ============================================================================

SET NOCOUNT ON;
SET XACT_ABORT ON;
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;

DECLARE @ServiceAccount   sysname = N'<<login van SQLio>>';   -- bv. N'DOMEIN\svc_SQLio' of N'svc_SQLio'
DECLARE @LeesrechtOpAlles bit     = 1;                         -- 1 = SQLioRole mag alle tabellen en views lezen

-- ----------------------------------------------------------------------------
-- Controles vooraf
-- ----------------------------------------------------------------------------
IF @ServiceAccount LIKE N'<<%'
    THROW 50001, 'Vul bovenin eerst @ServiceAccount in: de login waarmee SQLio verbinding maakt.', 1;

IF DB_NAME() IN (N'master', N'model', N'msdb', N'tempdb') OR OBJECT_ID(N'dbo.SQLio_Environments', N'U') IS NOT NULL
    THROW 50002, 'Dit script hoort op een ERP-database, niet op een systeemdatabase of de SQLio-configuratiedatabase.', 1;

IF SUSER_SID(@ServiceAccount) IS NULL
    THROW 50003, 'Deze login bestaat niet op de server. Controleer de naam, of maak eerst de login aan.', 1;

PRINT 'SQLio installeren in database ' + DB_NAME() + '...';

BEGIN TRY
BEGIN TRAN;

-- ----------------------------------------------------------------------------
-- 1. Rol en gebruiker
-- ----------------------------------------------------------------------------
IF DATABASE_PRINCIPAL_ID(N'SQLioRole') IS NULL
BEGIN
    EXEC (N'CREATE ROLE [SQLioRole];');
    PRINT '   [OK] Rol SQLioRole aangemaakt';
END
ELSE
    PRINT '   [--] Rol SQLioRole bestaat al';

-- Bestaat de login al als gebruiker in deze database (eventueel onder een andere
-- naam), dan die gebruiken
DECLARE @UserName sysname = (SELECT name FROM sys.database_principals WHERE sid = SUSER_SID(@ServiceAccount));
DECLARE @Sql nvarchar(400);

IF @UserName IS NULL
BEGIN
    SET @UserName = @ServiceAccount;
    SET @Sql = N'CREATE USER ' + QUOTENAME(@UserName) + N' FOR LOGIN ' + QUOTENAME(@ServiceAccount) + N';';
    EXEC sys.sp_executesql @Sql;
    PRINT '   [OK] Gebruiker ' + @UserName + ' aangemaakt';
END
ELSE
    PRINT '   [--] Gebruiker ' + @UserName + ' bestaat al';

IF @UserName = N'dbo'
    PRINT '   [--] De login is eigenaar van de database (dbo); lid maken van SQLioRole is niet nodig';
ELSE IF ISNULL(IS_ROLEMEMBER(N'SQLioRole', @UserName), 0) = 0
BEGIN
    SET @Sql = N'ALTER ROLE [SQLioRole] ADD MEMBER ' + QUOTENAME(@UserName) + N';';
    EXEC sys.sp_executesql @Sql;
    PRINT '   [OK] ' + @UserName + ' is lid van SQLioRole';
END
ELSE
    PRINT '   [--] ' + @UserName + ' is al lid van SQLioRole';

-- ----------------------------------------------------------------------------
-- 2. Rechten
-- ----------------------------------------------------------------------------
GRANT VIEW DEFINITION TO [SQLioRole];
PRINT '   [OK] VIEW DEFINITION voor SQLioRole';

IF @LeesrechtOpAlles = 1
BEGIN
    GRANT SELECT ON SCHEMA::[dbo] TO [SQLioRole];
    PRINT '   [OK] SELECT op schema dbo voor SQLioRole';
END

-- ----------------------------------------------------------------------------
-- 3. Webhook-queue
-- ----------------------------------------------------------------------------
IF OBJECT_ID(N'dbo.SQLio_WebhookQueue', N'U') IS NULL
BEGIN
    CREATE TABLE [dbo].[SQLio_WebhookQueue](
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [ConfigurationId] [int] NOT NULL,
        [OldValue] [nvarchar](max) NULL,
        [NewValue] [nvarchar](max) NULL,
        [CreatedAt] [datetime2](7) NOT NULL CONSTRAINT [DF_SQLio_WebhookQueue_CreatedAt] DEFAULT (GETDATE()),
        [Processed] [bit] NOT NULL CONSTRAINT [DF_SQLio_WebhookQueue_Processed] DEFAULT (0),
        [ProcessedAt] [datetime2](7) NULL,
        [RetryCount] [int] NOT NULL CONSTRAINT [DF_SQLio_WebhookQueue_RetryCount] DEFAULT (0),
        [OperationType] [nvarchar](20) NULL,
        [FieldName] [nvarchar](200) NULL,
        [PrimaryKeyValue] [nvarchar](200) NULL,
        [LastResponseStatus] [int] NULL,
        CONSTRAINT [PK_SQLio_WebhookQueue] PRIMARY KEY CLUSTERED ([Id] ASC)
    );
    PRINT '   [OK] Tabel SQLio_WebhookQueue aangemaakt';
END
ELSE
    PRINT '   [--] Tabel SQLio_WebhookQueue bestaat al';

-- Bewust geen gefilterde index: de trigger schrijft in de sessie van wie de tabel
-- wijzigt, en met ANSI_WARNINGS OFF (zoals Isah) faalt een insert in een tabel met een
-- gefilterde index
IF NOT EXISTS (SELECT 1 FROM sys.indexes
               WHERE name = N'IX_SQLio_WebhookQueue_Pending' AND object_id = OBJECT_ID(N'dbo.SQLio_WebhookQueue'))
BEGIN
    CREATE NONCLUSTERED INDEX [IX_SQLio_WebhookQueue_Pending]
        ON [dbo].[SQLio_WebhookQueue] ([Processed] ASC, [CreatedAt] ASC)
        INCLUDE ([RetryCount], [ConfigurationId]);
    PRINT '   [OK] Index IX_SQLio_WebhookQueue_Pending aangemaakt';
END
ELSE
    PRINT '   [--] Index IX_SQLio_WebhookQueue_Pending bestaat al';

GRANT SELECT, INSERT, UPDATE, DELETE ON [dbo].[SQLio_WebhookQueue] TO [SQLioRole];
PRINT '   [OK] Rechten op SQLio_WebhookQueue voor SQLioRole';

-- ----------------------------------------------------------------------------
-- 4. Procedure voor het aanmaken en verwijderen van webhooktriggers
-- ----------------------------------------------------------------------------
IF OBJECT_ID(N'dbo.SQLio_CreateDeleteTableTrigger', N'P') IS NULL
BEGIN
    EXEC (N'
CREATE PROCEDURE [dbo].[SQLio_CreateDeleteTableTrigger]
    @TriggerSql NVARCHAR(MAX)
WITH EXECUTE AS OWNER
AS
BEGIN
    SET NOCOUNT ON;
    EXEC sp_executesql @TriggerSql;
END');
    PRINT '   [OK] Procedure SQLio_CreateDeleteTableTrigger aangemaakt';
END
ELSE
    PRINT '   [--] Procedure SQLio_CreateDeleteTableTrigger bestaat al';

GRANT EXECUTE ON [dbo].[SQLio_CreateDeleteTableTrigger] TO [SQLioRole];
PRINT '   [OK] EXECUTE op SQLio_CreateDeleteTableTrigger voor SQLioRole';

COMMIT TRAN;
PRINT '';
PRINT 'Klaar.';
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRAN;
    PRINT 'FOUT - er is niets vastgelegd.';
    THROW;
END CATCH;

cd "C:\Program Files (x86)\HybridConnectionManager\CLI"
hcm.exe add

hcm.exe list

net stop HybridConnectionManagerService
net start HybridConnectionManagerService

hcm.exe test 192.168.1.100:1434

Server=[IP],[POORT];Database=[DATABASE];User Id=svc_sqlio;Password=[WACHTWOORD];TrustServerCertificate=True;Encrypt=False;