/*
  One-time migration options:

  A) Legacy append-only table (id IDENTITY, created_at, multiple rows per user)
     -> single published row per user_id + same shape for new staging table.

  B) If dbo.user_profiling already has PK(user_id), only create staging:

     CREATE TABLE dbo.user_profiling_staging (
         user_id BIGINT NOT NULL PRIMARY KEY,
         updated_at DATETIME2(7) NOT NULL DEFAULT (SYSUTCDATETIME()),
         profile_json NVARCHAR(MAX) NOT NULL,
         deep_analysis_markdown NVARCHAR(MAX) NULL
     );

  Backup before running any script below.
*/

/* --- A: collapse legacy append-only into PK(user_id), then add staging --- */

IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'user_profiling' AND schema_id = SCHEMA_ID('dbo'))
BEGIN
    RAISERROR ('dbo.user_profiling does not exist.', 16, 1);
    RETURN;
END

IF NOT EXISTS (
    SELECT 1 FROM sys.columns c
    INNER JOIN sys.tables t ON c.object_id = t.object_id
    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
    WHERE s.name = 'dbo' AND t.name = 'user_profiling' AND c.name = 'id'
)
BEGIN
    PRINT 'No legacy id column on user_profiling — skip collapse; ensure staging table exists separately.';
    RETURN;
END

SET XACT_ABORT ON;

BEGIN TRY
    BEGIN TRANSACTION;

    CREATE TABLE dbo.user_profiling_new (
        user_id BIGINT NOT NULL,
        created_at DATETIME2(7) NOT NULL CONSTRAINT DF_user_profiling_new_created DEFAULT (SYSUTCDATETIME()),
        profile_json NVARCHAR(MAX) NOT NULL,
        deep_analysis_markdown NVARCHAR(MAX) NULL,
        CONSTRAINT PK_user_profiling_new PRIMARY KEY (user_id)
    );

    ;WITH ranked AS (
        SELECT
            user_id,
            created_at,
            profile_json,
            deep_analysis_markdown,
            ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
        FROM dbo.user_profiling
    )
    INSERT INTO dbo.user_profiling_new (user_id, created_at, profile_json, deep_analysis_markdown)
    SELECT user_id, created_at, profile_json, deep_analysis_markdown
    FROM ranked
    WHERE rn = 1;

    DROP TABLE dbo.user_profiling;

    EXEC sp_rename N'dbo.user_profiling_new', N'user_profiling', N'OBJECT';

    IF OBJECT_ID(N'dbo.user_profiling_staging', N'U') IS NULL
    BEGIN
        CREATE TABLE dbo.user_profiling_staging (
            user_id BIGINT NOT NULL,
            updated_at DATETIME2(7) NOT NULL CONSTRAINT DF_user_profiling_staging_updated DEFAULT (SYSUTCDATETIME()),
            profile_json NVARCHAR(MAX) NOT NULL,
            deep_analysis_markdown NVARCHAR(MAX) NULL,
            CONSTRAINT PK_user_profiling_staging PRIMARY KEY (user_id)
        );
    END

    COMMIT TRANSACTION;

    PRINT 'Migration complete: user_profiling = one row per user_id; user_profiling_staging created if missing.';
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
    THROW;
END CATCH;
