/*
  Migrate dbo.user_profiling_tracker to surrogate id IDENTITY + unique user_id
  without dropping live data: build dbo.user_profiling_tracker_new, copy rows,
  then cut over in one transaction (DROP old + rename new).

  Legacy shape supported: (user_id PK, last_completed_at) with no id column.
  Target shape: id IDENTITY, user_id UNIQUE, last_completed_at (see storage/db_model.py).

  For a destructive empty reset (e.g. dev), use reset_user_profiling_tracker.sql instead.
*/

SET NOCOUNT ON;
SET XACT_ABORT ON;

IF OBJECT_ID(N'dbo.user_profiling_tracker', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.user_profiling_tracker (
        id BIGINT IDENTITY(1, 1) NOT NULL,
        user_id BIGINT NOT NULL,
        last_completed_at DATETIME2(7) NOT NULL,
        CONSTRAINT PK_user_profiling_tracker PRIMARY KEY (id),
        CONSTRAINT UQ_user_profiling_tracker_user_id UNIQUE (user_id)
    );
    PRINT 'Created dbo.user_profiling_tracker (fresh).';
    RETURN;
END;

DECLARE @oid INT = OBJECT_ID(N'dbo.user_profiling_tracker', N'U');
DECLARE @colCount INT = (
    SELECT COUNT(*) FROM sys.columns WHERE object_id = @oid
);
DECLARE @idIsIdentity BIT = CASE
    WHEN COLUMNPROPERTY(@oid, N'id', N'IsIdentity') = 1 THEN 1
    ELSE 0
END;
DECLARE @hasIdCol BIT = CASE
    WHEN COL_LENGTH(N'dbo.user_profiling_tracker', N'id') IS NOT NULL THEN 1
    ELSE 0
END;
DECLARE @hasUserId BIT = CASE
    WHEN COL_LENGTH(N'dbo.user_profiling_tracker', N'user_id') IS NOT NULL THEN 1
    ELSE 0
END;
DECLARE @hasLastCompleted BIT = CASE
    WHEN COL_LENGTH(N'dbo.user_profiling_tracker', N'last_completed_at') IS NOT NULL THEN 1
    ELSE 0
END;

IF @colCount = 3 AND @hasIdCol = 1 AND @idIsIdentity = 1
   AND @hasUserId = 1 AND @hasLastCompleted = 1
BEGIN
    PRINT 'dbo.user_profiling_tracker already matches target schema; no action.';
    RETURN;
END;

IF OBJECT_ID(N'dbo.user_profiling_tracker_new', N'U') IS NOT NULL
    DROP TABLE dbo.user_profiling_tracker_new;

CREATE TABLE dbo.user_profiling_tracker_new (
    id BIGINT IDENTITY(1, 1) NOT NULL,
    user_id BIGINT NOT NULL,
    last_completed_at DATETIME2(7) NOT NULL,
    CONSTRAINT PK_user_profiling_tracker_new PRIMARY KEY (id),
    CONSTRAINT UQ_user_profiling_tracker_user_id_new UNIQUE (user_id)
);

DECLARE @usedIdentityInsert BIT = 0;
DECLARE @readyForCutover BIT = 0;

IF @hasIdCol = 1 AND @idIsIdentity = 1
BEGIN
    SET @usedIdentityInsert = 1;
    SET IDENTITY_INSERT dbo.user_profiling_tracker_new ON;
    INSERT INTO dbo.user_profiling_tracker_new (id, user_id, last_completed_at)
    SELECT id, user_id, last_completed_at
    FROM dbo.user_profiling_tracker;
    SET IDENTITY_INSERT dbo.user_profiling_tracker_new OFF;
    SET @readyForCutover = 1;
END
ELSE IF @hasUserId = 1 AND @hasLastCompleted = 1
BEGIN
    INSERT INTO dbo.user_profiling_tracker_new (user_id, last_completed_at)
    SELECT user_id, last_completed_at
    FROM dbo.user_profiling_tracker;
    SET @readyForCutover = 1;
END
ELSE
BEGIN
    DROP TABLE dbo.user_profiling_tracker_new;
    RAISERROR (
        'dbo.user_profiling_tracker has unexpected columns; migrate manually after backup.',
        16,
        1
    );
END;

IF @readyForCutover = 1
BEGIN
    BEGIN TRANSACTION;

    DROP TABLE dbo.user_profiling_tracker;

    EXEC sp_rename N'dbo.user_profiling_tracker_new', N'user_profiling_tracker', N'OBJECT';

    EXEC sp_rename N'dbo.user_profiling_tracker.PK_user_profiling_tracker_new', N'PK_user_profiling_tracker', N'OBJECT';
    EXEC sp_rename N'dbo.user_profiling_tracker.UQ_user_profiling_tracker_user_id_new', N'UQ_user_profiling_tracker_user_id', N'OBJECT';

    COMMIT TRANSACTION;

    IF @usedIdentityInsert = 1
    BEGIN
        DECLARE @maxId BIGINT;
        SELECT @maxId = ISNULL(MAX(id), 0) FROM dbo.user_profiling_tracker;
        IF @maxId > 0
            DBCC CHECKIDENT (N'dbo.user_profiling_tracker', RESEED, @maxId);
    END;

    PRINT 'Migration complete: dbo.user_profiling_tracker rebuilt; rows preserved.';
END;
