/*
enable_cdc_sqlserver.sql — Enable SQL Server Change Data Capture (CDC)
=======================================================================
Pure T-SQL equivalent of enable_cdc_sqlserver.py. Run this in SSMS, Azure
Data Studio, or via sqlcmd against the target database.

For on-prem SQL Server, IaaS SQL Server on a VM, and Azure SQL MANAGED
INSTANCE (all of which have a real SQL Server Agent). For Azure SQL
DATABASE (single db / elastic pool — no Agent), use
enable_cdc_azuresql.sql instead; this script's Agent check will fail
there since xp_servicecontrol doesn't exist on Azure SQL Database.

Requires: CDC is only supported on SQL Server Standard/Enterprise/Developer
edition — NOT Express. SQL Server Agent must be running (CDC's capture and
cleanup jobs depend on it) or changes will never be processed into the
change tables.

Usage:
    1. Connect to the target database (USE <database> below, or set it in
       your client before running).
    2. Optionally edit @schema_name / @role_name near the top.
    3. Run the whole script. It is idempotent — safe to re-run; already-
       enabled tables are skipped.

    sqlcmd -S <server> -d <database> -U eb_admin -P '<password>' -C -i enable_cdc_sqlserver.sql
*/

SET NOCOUNT ON;

DECLARE @schema_name  SYSNAME       = N'dbo';
DECLARE @role_name     SYSNAME       = NULL;   -- NULL = no gating role on change data access

-- ── Step 0: Reject Azure SQL Database (no Agent — use enable_cdc_azuresql.sql) ──
IF CAST(SERVERPROPERTY('EngineEdition') AS INT) = 5
BEGIN
    RAISERROR('This looks like Azure SQL Database (EngineEdition=5), which has no SQL Server Agent. Use enable_cdc_azuresql.sql instead of this script.', 16, 1);
    RETURN;
END

-- ── Step 1: Edition check ──────────────────────────────────────────────────
DECLARE @edition VARCHAR(100) = CAST(SERVERPROPERTY('Edition') AS VARCHAR(100));
PRINT '[Check] SQL Server edition: ' + @edition;
IF @edition LIKE '%Express%'
BEGIN
    RAISERROR('CDC is not supported on SQL Server Express edition. Use Standard, Enterprise, or Developer.', 16, 1);
    RETURN;
END

-- ── Step 2: SQL Server Agent status ─────────────────────────────────────────
DECLARE @agent_state TABLE (state_text NVARCHAR(200));
INSERT INTO @agent_state EXEC master.dbo.xp_servicecontrol N'QUERYSTATE', N'SQLServerAGENT';
DECLARE @agent_running BIT = CASE WHEN EXISTS (
    SELECT 1 FROM @agent_state WHERE state_text LIKE 'Running%'
) THEN 1 ELSE 0 END;
PRINT '[Check] SQL Server Agent running: ' + CAST(@agent_running AS VARCHAR(1));

IF @agent_running = 0
BEGIN
    PRINT '[WARNING] SQL Server Agent is not running. CDC capture/cleanup jobs';
    PRINT '  will not process changes until Agent is started. In a Docker container:';
    PRINT '    docker exec <container> /opt/mssql/bin/mssql-conf set sqlagent.enabled true';
    PRINT '    docker restart <container>';
    PRINT '  On a normal install: start the ''SQL Server Agent'' service.';
    RAISERROR('Refusing to enable CDC while SQL Server Agent is stopped — changes would never be captured. Start Agent first, then re-run.', 16, 1);
    RETURN;
END

-- ── Step 3: Enable CDC at the database level ────────────────────────────────
IF (SELECT is_cdc_enabled FROM sys.databases WHERE name = DB_NAME()) = 0
BEGIN
    EXEC sys.sp_cdc_enable_db;
    PRINT '[DB] CDC enabled on database ''' + DB_NAME() + '''.';
END
ELSE
    PRINT '[DB] CDC already enabled on database ''' + DB_NAME() + '''.';

-- ── Step 4: Enable CDC on every user table in @schema_name ──────────────────
DECLARE @tbl SYSNAME;
DECLARE @enabled_count INT = 0, @skipped_count INT = 0;

DECLARE tbl_cursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT t.name
    FROM sys.tables t
    WHERE t.is_ms_shipped = 0
      AND t.schema_id = SCHEMA_ID(@schema_name);

OPEN tbl_cursor;
FETCH NEXT FROM tbl_cursor INTO @tbl;

WHILE @@FETCH_STATUS = 0
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM cdc.change_tables ct
        JOIN sys.tables t ON ct.source_object_id = t.object_id
        WHERE t.name = @tbl AND t.schema_id = SCHEMA_ID(@schema_name)
    )
    BEGIN
        EXEC sys.sp_cdc_enable_table
            @source_schema = @schema_name,
            @source_name   = @tbl,
            @role_name     = @role_name,
            @supports_net_changes = 1;
        PRINT '  [' + @tbl + '] CDC enabled.';
        SET @enabled_count += 1;
    END
    ELSE
    BEGIN
        PRINT '  [' + @tbl + '] Already enabled — skipped.';
        SET @skipped_count += 1;
    END

    FETCH NEXT FROM tbl_cursor INTO @tbl;
END

CLOSE tbl_cursor;
DEALLOCATE tbl_cursor;

PRINT '';
PRINT '[Done] ' + CAST(@enabled_count AS VARCHAR(10)) + ' table(s) newly enabled, '
    + CAST(@skipped_count AS VARCHAR(10)) + ' already were.';

-- ── Summary ───────────────────────────────────────────────────────────────
SELECT ct.capture_instance, OBJECT_SCHEMA_NAME(ct.source_object_id) AS schema_name,
       t.name AS table_name, ct.start_lsn, ct.create_date
FROM cdc.change_tables ct
JOIN sys.tables t ON ct.source_object_id = t.object_id
ORDER BY t.name;
