-- Backfill subject_id in audit_log from the userId field stored in the param column.
--
-- Usage:
--   Run this script in sqlcmd or SQL Server Management Studio (SSMS):
--     sqlcmd -S <server> -d <database> -U <user> -P <password> -i migration_2.0.0_2.1.0_backfill_audit_log_subject_id.sql
--
--   To adjust the batch size, change the value of @batch_size below (default: 1000).
--   Larger batches are faster but hold locks longer; smaller batches are safer on busy tables.
--
--   The script is idempotent — it is safe to re-run if interrupted.

DECLARE @batch_size  INT = 1000; -- adjust batch size here
DECLARE @rows_updated INT;
DECLARE @total        INT = 0;

WHILE 1 = 1
BEGIN
    WITH candidates AS (
        SELECT TOP (@batch_size) audit_log_id, JSON_VALUE(param, '$.userId') AS user_id
        FROM audit_log
        WHERE subject_id IS NULL
          AND param LIKE '%"userId"%'
    )
    UPDATE audit_log
    SET subject_id = candidates.user_id
    FROM candidates
    WHERE audit_log.audit_log_id = candidates.audit_log_id
      AND candidates.user_id IS NOT NULL;

    SET @rows_updated = @@ROWCOUNT;
    SET @total = @total + @rows_updated;
    PRINT 'Batch done: ' + CAST(@rows_updated AS VARCHAR) +
          ' rows updated (total so far: ' + CAST(@total AS VARCHAR) + ')';

    IF @rows_updated = 0 BREAK;
END;

PRINT 'Migration complete. Total rows updated: ' + CAST(@total AS VARCHAR);
GO
