-- ========== CONFIGURATION VARIABLES ==========
DECLARE @CompanyId INT = 2271;                          -- Analytics CompanyId
DECLARE @DemographicName NVARCHAR(100) = 'Growth Plan Activity';

-- ========== INTERNAL VARIABLES (Auto-populated) ==========
DECLARE @CompanyDemographicId INT;
DECLARE @CompanyDemographicValueId_NotStarted INT;
DECLARE @CompanyDemographicValueId_Draft INT;
DECLARE @CompanyDemographicValueId_Shared INT;
DECLARE @CompanyDemographicValueMappingId_Draft INT;
DECLARE @CompanyDemographicValueMappingId_Shared INT;

BEGIN TRANSACTION;
BEGIN TRY

    -- ========== Locate the demographic ==========
    SELECT @CompanyDemographicId = cd.Id
    FROM Analytics.survey.CompanyDemographic cd
    INNER JOIN Analytics.survey.CompanyDemographicNameMapping cdnm ON cdnm.CompanyDemographicId = cd.Id
    WHERE cd.CompanyId = @CompanyId
      AND cdnm.Text = @DemographicName;

    IF @CompanyDemographicId IS NULL
    BEGIN
        RAISERROR('No CompanyDemographic found for CompanyId=%d, Name=''%s''.', 16, 1, @CompanyId, @DemographicName);
    END

    -- ========== Read current mapping/value IDs ==========
    SELECT @CompanyDemographicValueId_NotStarted = cdvm.CompanyDemographicValueId
    FROM Analytics.survey.CompanyDemographicValueMapping cdvm
    JOIN Analytics.survey.CompanyDemographicValue cdv ON cdv.Id = cdvm.CompanyDemographicValueId
    WHERE cdv.CompanyDemographicId = @CompanyDemographicId
      AND cdvm.Text = 'Not Started';

    SELECT @CompanyDemographicValueMappingId_Draft = cdvm.Id,
           @CompanyDemographicValueId_Draft = cdvm.CompanyDemographicValueId
    FROM Analytics.survey.CompanyDemographicValueMapping cdvm
    JOIN Analytics.survey.CompanyDemographicValue cdv ON cdv.Id = cdvm.CompanyDemographicValueId
    WHERE cdv.CompanyDemographicId = @CompanyDemographicId
      AND cdvm.Text = 'Draft';

    SELECT @CompanyDemographicValueMappingId_Shared = cdvm.Id,
           @CompanyDemographicValueId_Shared = cdvm.CompanyDemographicValueId
    FROM Analytics.survey.CompanyDemographicValueMapping cdvm
    JOIN Analytics.survey.CompanyDemographicValue cdv ON cdv.Id = cdvm.CompanyDemographicValueId
    WHERE cdv.CompanyDemographicId = @CompanyDemographicId
      AND cdvm.Text = 'Shared';

    PRINT 'Current state:';
    PRINT '  CompanyDemographicId: ' + CAST(@CompanyDemographicId AS VARCHAR);
    PRINT '  CompanyDemographicValueId "Not Started": ' + ISNULL(CAST(@CompanyDemographicValueId_NotStarted AS VARCHAR), 'NULL');
    PRINT '  CompanyDemographicValueId "Draft":       ' + ISNULL(CAST(@CompanyDemographicValueId_Draft AS VARCHAR), 'NULL');
    PRINT '  CompanyDemographicValueId "Shared":      ' + ISNULL(CAST(@CompanyDemographicValueId_Shared AS VARCHAR), 'NULL');

    -- ========== Verify the malformed state before touching anything ==========
    IF @CompanyDemographicValueId_NotStarted IS NULL
       OR @CompanyDemographicValueId_Draft IS NULL
       OR @CompanyDemographicValueId_Shared IS NULL
    BEGIN
        RAISERROR('One or more expected mappings (Not Started, Draft, Shared) were not found.', 16, 1);
    END

    IF @CompanyDemographicValueId_NotStarted <> @CompanyDemographicValueId_Draft
       AND @CompanyDemographicValueId_NotStarted <> @CompanyDemographicValueId_Shared
       AND @CompanyDemographicValueId_Draft <> @CompanyDemographicValueId_Shared
    BEGIN
        RAISERROR('Structure is already correct: all three mappings have distinct CompanyDemographicValueIds. No repair needed.', 16, 1);
    END

    -- ========== Repair ==========
    IF @CompanyDemographicValueId_Draft = @CompanyDemographicValueId_NotStarted
    BEGIN
        INSERT INTO Analytics.survey.CompanyDemographicValue (CompanyDemographicId)
        VALUES (@CompanyDemographicId);
        SET @CompanyDemographicValueId_Draft = SCOPE_IDENTITY();

        UPDATE Analytics.survey.CompanyDemographicValueMapping
        SET CompanyDemographicValueId = @CompanyDemographicValueId_Draft
        WHERE Id = @CompanyDemographicValueMappingId_Draft;

        PRINT 'Repaired "Draft": reassigned to new CompanyDemographicValueId ' + CAST(@CompanyDemographicValueId_Draft AS VARCHAR);
    END

    IF @CompanyDemographicValueId_Shared = @CompanyDemographicValueId_NotStarted
       OR @CompanyDemographicValueId_Shared = @CompanyDemographicValueId_Draft
    BEGIN
        INSERT INTO Analytics.survey.CompanyDemographicValue (CompanyDemographicId)
        VALUES (@CompanyDemographicId);
        SET @CompanyDemographicValueId_Shared = SCOPE_IDENTITY();

        UPDATE Analytics.survey.CompanyDemographicValueMapping
        SET CompanyDemographicValueId = @CompanyDemographicValueId_Shared
        WHERE Id = @CompanyDemographicValueMappingId_Shared;

        PRINT 'Repaired "Shared": reassigned to new CompanyDemographicValueId ' + CAST(@CompanyDemographicValueId_Shared AS VARCHAR);
    END

    COMMIT TRANSACTION;
    PRINT '';
    PRINT '========== SUCCESS: Repair completed successfully ==========';
    PRINT '  CompanyDemographicValueId "Not Started": ' + CAST(@CompanyDemographicValueId_NotStarted AS VARCHAR);
    PRINT '  CompanyDemographicValueId "Draft":       ' + CAST(@CompanyDemographicValueId_Draft AS VARCHAR);
    PRINT '  CompanyDemographicValueId "Shared":      ' + CAST(@CompanyDemographicValueId_Shared AS VARCHAR);

END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    PRINT '';
    PRINT '========== ERROR: Transaction rolled back ==========';
    PRINT 'Error Message: ' + ERROR_MESSAGE();
    PRINT 'Error Line: ' + CAST(ERROR_LINE() AS VARCHAR);
    PRINT 'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR);
    PRINT 'Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR);

    THROW;
END CATCH;