/*
enable_cdc_azuresql.sql — Enable Change Data Capture (CDC) on Azure SQL Database
=================================================================================
Azure SQL Database (single database / elastic pool) version of
enable_cdc_sqlserver.sql. The two are NOT interchangeable:

  - No SQL Server Agent exists in Azure SQL Database, so the Agent-status
    gate from the on-prem script is removed entirely (calling
    xp_servicecontrol here would just error). BUT unlike on-prem, Azure SQL
    Database does NOT auto-start the capture job when sp_cdc_enable_db runs
    — only the cleanup job is automatic there. This script explicitly runs
    EXEC sys.sp_cdc_add_job @job_type = N'capture' after enabling DB-level
    CDC. Skipping that call is a real, silent failure mode: is_cdc_enabled
    shows 1 and sp_cdc_enable_table succeeds, but no changes are ever
    captured because the capture job never started.
  - This script targets Azure SQL Database (single db / elastic pool) only
    — SERVERPROPERTY('EngineEdition') = 5. If you're on Azure SQL MANAGED
    INSTANCE (EngineEdition = 8), that product has a real SQL Server Agent
    and behaves like on-prem SQL Server — use enable_cdc_sqlserver.sql
    instead.
  - You must be connected as a login with sufficient privileges to enable
    CDC — typically the server admin, the Azure AD/Entra admin, or a login
    granted CONTROL on the database. A plain db_owner may not be enough
    depending on how the database's admin roles are configured; if
    sp_cdc_enable_db fails with a permission error, reconnect as the
    server/AAD admin and re-run.
  - CDC availability can depend on service tier/compute size. If
    sp_cdc_enable_db fails with an error mentioning the feature not being
    supported, check the current Azure SQL Database documentation for the
    tiers supported at the time you're reading this — it has changed over
    time and this script does not hard-code a tier allow-list to avoid
    going stale.

Usage:
    1. Connect to the target Azure SQL Database (not the master database).
    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>.database.windows.net -d <database> -U eb_admin -P '<password>' -C -i enable_cdc_azuresql.sql
*/

SET NOCOUNT ON;

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

-- ── Step 1: Confirm this is Azure SQL Database (single db / elastic pool) ──
DECLARE @engine_edition INT = CAST(SERVERPROPERTY('EngineEdition') AS INT);
IF @engine_edition <> 5
BEGIN
    IF @engine_edition = 8
        RAISERROR('This looks like Azure SQL Managed Instance (EngineEdition=8), which has a real SQL Server Agent. Use enable_cdc_sqlserver.sql instead of this script.', 16, 1);
    ELSE
        RAISERROR('This script targets Azure SQL Database (EngineEdition=5). Detected EngineEdition=%d — use enable_cdc_sqlserver.sql for on-prem/IaaS SQL Server.', 16, 1, @engine_edition);
    RETURN;
END
PRINT '[Check] Confirmed Azure SQL Database (EngineEdition=5).';

-- ── Step 2: Enable CDC at the database level ────────────────────────────────
-- No Agent check needed here — there is no SQL Server Agent on Azure SQL
-- Database. But sp_cdc_enable_db alone is NOT enough on Azure SQL Database:
-- it does not auto-start the capture job the way on-prem Agent does, so
-- Step 2b below is mandatory, every run.
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 2b: Explicitly start the capture job (Azure SQL Database only) ─────
-- Required every run, even if CDC was already enabled above — this call is
-- idempotent (safe to re-run if the job already exists) and there is no
-- reliable is_cdc_enabled-style flag to gate it on, so we just always call it.
-- The cleanup job does NOT need this — Azure SQL Database manages cleanup
-- internally.
BEGIN TRY
    EXEC sys.sp_cdc_add_job @job_type = N'capture';
    PRINT '[DB] Capture job started (sp_cdc_add_job @job_type=''capture'').';
END TRY
BEGIN CATCH
    -- Match on message text rather than a hard-coded error number — not
    -- confident enough in a specific "already exists" error code across
    -- Azure SQL Database versions to gate on that alone. Anything that
    -- doesn't look like an "already exists" condition is re-thrown so it
    -- isn't silently swallowed.
    IF ERROR_MESSAGE() LIKE '%already%'
        PRINT '[DB] Capture job already running — skipped (' + ERROR_MESSAGE() + ').';
    ELSE
        THROW;
END CATCH

-- ── Step 3: 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.';
PRINT '[Note] Cleanup runs as an Azure-managed background job automatically.';
PRINT '  Capture was started explicitly above (sp_cdc_add_job) — there is no';
PRINT '  SQL Server Agent on Azure SQL Database to auto-start it.';

-- ── 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;
