/**
 * CONTRIBUTION NOTE:
 * If you edit this file, create a ticket for the Doc Team to update the public-facing script
 *  - Project: Documentation (DOC)
 *  - Component: Ingestion
 *  - Label: lakeflow-connect-docs
 *
 * SQL Server Utility Objects Script for Databricks Lakeflow Connect
 * Version 1.1
 *
 * This script creates versioned utility stored procedures that can be used
 * to automatically remediate common SQL Server setup issues for ingestion.
 *
 * FEATURES:
 * - Automatic table discovery with @Tables = 'ALL', 'SCHEMAS:Sales,HR', wildcards
 * - Smart CT/CDC selection based on primary key presence
 * - Table-level CT/CDC enablement and DDL support objects
 * - Multi-platform detection and optimization
 * - Idempotent
 *
 * Platform Support:
 * - On-premises SQL Server
 * - Azure SQL Database
 * - Azure SQL Managed Instance
 * - Amazon RDS for SQL Server
 *
 */

SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;

PRINT N'Starting Lakeflow Connect Utility Objects installation...';
PRINT N'Version: 1.1';
PRINT N'Catalog: ' + DB_NAME();
PRINT N'Executed by: ' + SUSER_NAME();
PRINT N'Date/Time: ' + CONVERT(VARCHAR, GETDATE(), 120);
PRINT N'';

-- Detect platform
DECLARE @engineEdition INT = CAST(SERVERPROPERTY('EngineEdition') AS INT);
DECLARE @serverName NVARCHAR(255) = @@SERVERNAME;
DECLARE @currentPlatform NVARCHAR(50);

IF @engineEdition = 5
    SET @currentPlatform = 'AZURE_SQL_DATABASE';
ELSE
    IF @engineEdition = 8
        SET @currentPlatform = 'AZURE_SQL_MANAGED_INSTANCE';
    ELSE
        IF @serverName LIKE '%.rds.amazonaws.com'
            SET @currentPlatform = 'AMAZON_RDS';
        ELSE
            IF @engineEdition IN (1, 2, 3, 4)
                SET @currentPlatform = 'ON_PREMISES';
            ELSE
                SET @currentPlatform = 'UNKNOWN';

PRINT N'Detected platform: ' + @currentPlatform;

-- Validate that current user has sufficient privileges
IF (IS_ROLEMEMBER('db_owner') = 0)
    BEGIN
        RAISERROR ('User executing this script is not a ''db_owner'' role member. To execute this script, please use a user that is a member of the db_owner role.', 16, 1);
        RETURN;
    END

-- Cleanup existing objects
PRINT N'Cleaning up existing utility objects (all versions)...';

DECLARE @dropSql NVARCHAR(MAX) = '';

-- Drop lakeflowFixPermissions procedures
SELECT @dropSql = @dropSql + 'DROP PROCEDURE dbo.[' + name + '];' + CHAR(13)
FROM sys.procedures
WHERE name = 'lakeflowFixPermissions';

IF LEN(@dropSql) > 0
    BEGIN
        EXEC sp_executesql @dropSql;
        PRINT N'Dropped existing lakeflowFixPermissions procedures';
    END

-- Drop lakeflowSetupChangeTracking procedures
SET @dropSql = '';
SELECT @dropSql = @dropSql + 'DROP PROCEDURE dbo.[' + name + '];' + CHAR(13)
FROM sys.procedures
WHERE name = 'lakeflowSetupChangeTracking';

IF LEN(@dropSql) > 0
    BEGIN
        EXEC sp_executesql @dropSql;
        PRINT N'Dropped existing lakeflowSetupChangeTracking procedures';
    END

-- Drop lakeflowSetupChangeDataCapture procedures
SET @dropSql = '';
SELECT @dropSql = @dropSql + 'DROP PROCEDURE dbo.[' + name + '];' + CHAR(13)
FROM sys.procedures
WHERE name = 'lakeflowSetupChangeDataCapture';

IF LEN(@dropSql) > 0
    BEGIN
        EXEC sp_executesql @dropSql;
        PRINT N'Dropped existing lakeflowSetupChangeDataCapture procedures';
    END

-- Drop lakeflowDetectPlatform functions
SET @dropSql = '';
SELECT @dropSql = @dropSql + 'DROP FUNCTION dbo.[' + name + '];' + CHAR(13)
FROM sys.objects
WHERE name = 'lakeflowDetectPlatform'
  AND type = 'FN';

IF LEN(@dropSql) > 0
    BEGIN
        EXEC sp_executesql @dropSql;
        PRINT N'Dropped existing lakeflowDetectPlatform functions';
    END

-- Drop lakeflowUtilityVersion functions
SET @dropSql = '';
SELECT @dropSql = @dropSql + 'DROP FUNCTION dbo.[' + name + '];' + CHAR(13)
FROM sys.objects
WHERE name LIKE 'lakeflowUtilityVersion_%_%'
  AND type = 'FN';

IF LEN(@dropSql) > 0
    BEGIN
        EXEC sp_executesql @dropSql;
        PRINT N'Dropped existing lakeflowUtilityVersion functions';
    END

-- Drop older versions of change tracking DDL objects
SET @dropSql = '';
SELECT @dropSql = @dropSql + 'DROP TABLE [dbo].[' + name + '];' + CHAR(13)
FROM sys.tables
WHERE name LIKE 'lakeflowDdlAudit_%_%';

IF LEN(@dropSql) > 0
    BEGIN
        EXEC sp_executesql @dropSql;
        PRINT N'Dropped existing DDL audit tables from all versions';
    END

SET @dropSql = '';
SELECT @dropSql = @dropSql + 'DROP TRIGGER [' + name + '] ON DATABASE;' + CHAR(13)
FROM sys.triggers
WHERE name LIKE 'lakeflowDdlAuditTrigger_%_%' AND parent_class = 0;

IF LEN(@dropSql) > 0
    BEGIN
        EXEC sp_executesql @dropSql;
        PRINT N'Dropped existing DDL audit triggers from all versions';
    END

-- Drop older versions of CDC objects
SET @dropSql = '';
SELECT @dropSql = @dropSql + 'DROP PROCEDURE dbo.[' + name + '];' + CHAR(13)
FROM sys.procedures
WHERE name LIKE 'lakeflowDisableOldCaptureInstance_%_%'
   OR name LIKE 'lakeflowMergeCaptureInstances_%_%'
   OR name LIKE 'lakeflowRefreshCaptureInstance_%_%';

IF LEN(@dropSql) > 0
    BEGIN
        EXEC sp_executesql @dropSql;
        PRINT N'Dropped existing CDC procedures from all versions';
    END

SET @dropSql = '';
SELECT @dropSql = @dropSql + 'DROP TABLE [dbo].[' + name + '];' + CHAR(13)
FROM sys.tables
WHERE name LIKE 'lakeflowCaptureInstanceInfo_%_%';

IF LEN(@dropSql) > 0
    BEGIN
        EXEC sp_executesql @dropSql;
        PRINT N'Dropped existing capture instance tables from all versions';
    END

SET @dropSql = '';
SELECT @dropSql = @dropSql + 'DROP TRIGGER [' + name + '] ON DATABASE;' + CHAR(13)
FROM sys.triggers
WHERE name LIKE 'lakeflowAlterTableTrigger_%_%' AND parent_class = 0;

IF LEN(@dropSql) > 0
    BEGIN
        EXEC sp_executesql @dropSql;
        PRINT N'Dropped existing ALTER TABLE triggers from all versions';
    END

PRINT N'Cleanup completed.';
PRINT N'';

-- Create versioned functions first (dependencies)
PRINT N'Creating lakeflowDetectPlatform function...';
EXEC sp_executesql N'
CREATE FUNCTION dbo.lakeflowDetectPlatform()
RETURNS NVARCHAR(50)
AS
BEGIN
    DECLARE @engineEdition INT = CAST(SERVERPROPERTY(''EngineEdition'') AS INT);
    DECLARE @serverName NVARCHAR(255) = @@SERVERNAME;
    DECLARE @platform NVARCHAR(50);

    IF @engineEdition = 5
        SET @platform = ''AZURE_SQL_DATABASE'';
    ELSE IF @engineEdition = 8
        SET @platform = ''AZURE_SQL_MANAGED_INSTANCE'';
    ELSE IF @serverName LIKE ''%.rds.amazonaws.com''
        SET @platform = ''AMAZON_RDS'';
    ELSE IF @engineEdition IN (1, 2, 3, 4)
        SET @platform = ''ON_PREMISES'';
    ELSE
        SET @platform = ''UNKNOWN'';

    RETURN @platform;
END';
PRINT N'Created lakeflowDetectPlatform function';

PRINT N'Creating lakeflowUtilityVersion_1_1 function...';
EXEC sp_executesql N'
CREATE FUNCTION dbo.lakeflowUtilityVersion_1_1()
RETURNS NVARCHAR(10)
AS
BEGIN
    RETURN ''1.1'';
END';
PRINT N'Created lakeflowUtilityVersion_1_1 function';

-- Create lakeflowFixPermissions
PRINT N'Creating lakeflowFixPermissions procedure...';

PRINT N'Created lakeflowFixPermissions procedure';

-- Create lakeflowSetupChangeTracking
PRINT N'Creating lakeflowSetupChangeTracking procedure...';

PRINT N'Created lakeflowSetupChangeTracking procedure';

-- Create lakeflowSetupChangeDataCapture
PRINT N'Creating lakeflowSetupChangeDataCapture procedure...';

PRINT N'Created lakeflowSetupChangeDataCapture procedure';

-- Final validation and summary
PRINT N'';
PRINT N'=== Installation Summary ===';
DECLARE @Platform NVARCHAR(50) = dbo.lakeflowDetectPlatform();
PRINT N'Platform: ' + @Platform;
PRINT N'Version: ' + dbo.lakeflowUtilityVersion_1_1();

-- Verify created objects
IF OBJECT_ID('dbo.lakeflowDetectPlatform', 'FN') IS NOT NULL
    PRINT N'✓ lakeflowDetectPlatform function created successfully'
ELSE
    PRINT N'✗ lakeflowDetectPlatform function creation failed';

IF OBJECT_ID('dbo.lakeflowUtilityVersion_1_1', 'FN') IS NOT NULL
    PRINT N'✓ lakeflowUtilityVersion_1_1 function created successfully'
ELSE
    PRINT N'✗ lakeflowUtilityVersion_1_1 function creation failed';

IF OBJECT_ID('dbo.lakeflowFixPermissions', 'P') IS NOT NULL
    PRINT N'✓ lakeflowFixPermissions procedure created successfully'
ELSE
    PRINT N'✗ lakeflowFixPermissions procedure creation failed';

IF OBJECT_ID('dbo.lakeflowSetupChangeTracking', 'P') IS NOT NULL
    PRINT N'✓ lakeflowSetupChangeTracking procedure created successfully'
ELSE
    PRINT N'✗ lakeflowSetupChangeTracking procedure creation failed';

IF OBJECT_ID('dbo.lakeflowSetupChangeDataCapture', 'P') IS NOT NULL
    PRINT N'✓ lakeflowSetupChangeDataCapture procedure created successfully'
ELSE
    PRINT N'✗ lakeflowSetupChangeDataCapture procedure creation failed';

PRINT N'';
PRINT N'=== Usage Examples ===';
PRINT N'-- Table-specific setup:';
PRINT N'EXEC dbo.lakeflowSetupChangeTracking @Tables = ''dbo.Table1,Sales.Orders'', @User = ''YourUsername'';';
PRINT N'';
PRINT N'-- Enable change tracking on all user tables (auto-discovers, skips tables without PKs):';
PRINT N'EXEC dbo.lakeflowSetupChangeTracking @Tables = ''ALL'', @User = ''YourUsername'';';
PRINT N'';
PRINT N'-- Enable change tracking on all tables in specific schemas:';
PRINT N'EXEC dbo.lakeflowSetupChangeTracking @Tables = ''SCHEMAS:Sales,HR,Production'', @User = ''YourUsername'';';
PRINT N'';
PRINT N'-- Enable change tracking with wildcard support:';
PRINT N'EXEC dbo.lakeflowSetupChangeTracking @Tables = ''Sales.*,HR.Employees,dbo.SpecialTable'', @User = ''YourUsername'';';
PRINT N'';
PRINT N'-- Enable CDC on all user tables (processes tables with and without PKs):';
PRINT N'EXEC dbo.lakeflowSetupChangeDataCapture @Tables = ''ALL'', @User = ''YourUsername'';';
PRINT N'';
PRINT N'-- Smart two-step approach for complete coverage:';
PRINT N'-- Step 1: Enable CT on tables with primary keys';
PRINT N'EXEC dbo.lakeflowSetupChangeTracking @Tables = ''ALL'', @User = ''YourUsername'';';
PRINT N'-- Step 2: Enable CDC on tables without primary keys';
PRINT N'EXEC dbo.lakeflowSetupChangeDataCapture @Tables = ''ALL'', @User = ''YourUsername'';';
PRINT N'';
PRINT N'-- Fix permissions for a user:';
PRINT N'EXEC dbo.lakeflowFixPermissions @User = ''YourUsername'';';
PRINT N'';
PRINT N'-- Grant table permissions for specific tables:';
PRINT N'EXEC dbo.lakeflowFixPermissions @User = ''YourUsername'', @Tables = ''ALL'';';
PRINT N'EXEC dbo.lakeflowFixPermissions @User = ''YourUsername'', @Tables = ''Sales.*,HR.Employees'';';
PRINT N'';
PRINT N'-- Setup change tracking at database level only (no table processing):';
PRINT N'EXEC dbo.lakeflowSetupChangeTracking @Tables = NULL, @User = ''YourUsername'';';
PRINT N'';
PRINT N'-- NOTE: The @User parameter is optional. If provided, the procedures will grant';
PRINT N'-- the specified user permissions to access DDL support objects (audit tables, etc.)';
PRINT N'-- This is useful for granting read access to change tracking metadata.';
PRINT N'';
PRINT N'-- Cleanup DDL support objects:';
PRINT N'EXEC dbo.lakeflowSetupChangeTracking @Mode = ''CLEANUP'';';
PRINT N'EXEC dbo.lakeflowSetupChangeDataCapture @Mode = ''CLEANUP'';';
PRINT N'';
PRINT N'=== Installation Complete ===';
PRINT N'All utility objects have been installed successfully.';
PRINT N'';
PRINT N'=== Available Procedures ===';
PRINT N'1. lakeflowFixPermissions - Fix user permissions for ingestion';
PRINT N'2. lakeflowSetupChangeTracking - Setup change tracking and DDL audit objects';
PRINT N'3. lakeflowSetupChangeDataCapture - Setup CDC and capture instance objects';
PRINT N'';
PRINT N'For more information, visit: https://docs.databricks.com/aws/en/ingestion/lakeflow-connect/sql-server-source-setup';

