-- ============================================================================
-- Setup Growth Plan Activity Demographic for Survey
-- ============================================================================
-- This script creates and populates a demographic to track Growth Plan activity
-- (Not Started, Draft, Shared, Not Logged In) for survey participants.
--
-- USAGE: Set the variables below for your target company/survey
-- ============================================================================

-- ========== CONFIGURATION VARIABLES ==========
DECLARE @CompanyId INT = 2077;                          -- Analytics CompanyId
DECLARE @SurveyId INT = 54446;                        -- Target Survey ID
DECLARE @DemographicName NVARCHAR(100) = 'Growth Plan Activity';

-- ========== INTERNAL VARIABLES (Auto-populated) ==========
DECLARE @OrganizationId INT;
DECLARE @DemographicSlot INT;
DECLARE @CompanyDemographicId INT;
DECLARE @CompanyDemographicNameMappingId INT;
DECLARE @CompanyDemographicValueId_NotStarted INT;
DECLARE @CompanyDemographicValueId_Draft INT;
DECLARE @CompanyDemographicValueId_Shared INT;
DECLARE @CompanyDemographicValueId_NotLoggedIn INT;
DECLARE @CompanyDemographicValueMappingId_NotStarted INT;
DECLARE @CompanyDemographicValueMappingId_Draft INT;
DECLARE @CompanyDemographicValueMappingId_Shared INT;
DECLARE @CompanyDemographicValueMappingId_NotLoggedIn INT;
DECLARE @SurveyDemographicId INT;
DECLARE @DynamicSQL NVARCHAR(MAX);
DECLARE @DemographicColumn NVARCHAR(50);

BEGIN TRANSACTION;
BEGIN TRY

    -- ========== STEP 1: Lookup OrganizationId from CompanyId ==========
    SELECT @OrganizationId = o.Id
    FROM Analytics.survey.Organization o
    WHERE o.CompanyId = @CompanyId;

    IF @OrganizationId IS NULL
    BEGIN
        RAISERROR('Unable to find OrganizationId for CompanyId: %d', 16, 1, @CompanyId);
    END
    ELSE
    BEGIN
        PRINT 'Found OrganizationId: ' + CAST(@OrganizationId AS VARCHAR) + ' for CompanyId: ' + CAST(@CompanyId AS VARCHAR);
    END;

    -- ========== STEP 3: Check if CompanyDemographic exists ==========
    SELECT
        @CompanyDemographicId = cd.Id,
        @CompanyDemographicNameMappingId = cdnm.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
        -- ========== Create all Company Demographic structures at once ==========
        PRINT 'CompanyDemographic not found. Creating new demographic structure for CompanyId: ' + CAST(@CompanyId AS VARCHAR);

        -- Create CompanyDemographic
        INSERT INTO Analytics.survey.CompanyDemographic (CompanyId)
        VALUES (@CompanyId);
        SET @CompanyDemographicId = SCOPE_IDENTITY();
        PRINT '  Created CompanyDemographicId: ' + CAST(@CompanyDemographicId AS VARCHAR);

        -- Create CompanyDemographicNameMapping
        INSERT INTO Analytics.survey.CompanyDemographicNameMapping (CompanyDemographicId, Text)
        VALUES (@CompanyDemographicId, @DemographicName);
        SET @CompanyDemographicNameMappingId = SCOPE_IDENTITY();
        PRINT '  Created CompanyDemographicNameMappingId: ' + CAST(@CompanyDemographicNameMappingId AS VARCHAR);

        INSERT INTO Analytics.survey.CompanyDemographicValue (CompanyDemographicId)
        VALUES (@CompanyDemographicId);
        SET @CompanyDemographicValueId_NotStarted = SCOPE_IDENTITY();
        INSERT INTO Analytics.survey.CompanyDemographicValueMapping (Text, CompanyDemographicValueId)
        VALUES ('Not Started', @CompanyDemographicValueId_NotStarted);
        SET @CompanyDemographicValueMappingId_NotStarted = SCOPE_IDENTITY();
        PRINT '  Created CompanyDemographicValue/Mapping "Not Started": value=' + CAST(@CompanyDemographicValueId_NotStarted AS VARCHAR) + ', mapping=' + CAST(@CompanyDemographicValueMappingId_NotStarted AS VARCHAR);

        INSERT INTO Analytics.survey.CompanyDemographicValue (CompanyDemographicId)
        VALUES (@CompanyDemographicId);
        SET @CompanyDemographicValueId_Draft = SCOPE_IDENTITY();
        INSERT INTO Analytics.survey.CompanyDemographicValueMapping (Text, CompanyDemographicValueId)
        VALUES ('Draft', @CompanyDemographicValueId_Draft);
        SET @CompanyDemographicValueMappingId_Draft = SCOPE_IDENTITY();
        PRINT '  Created CompanyDemographicValue/Mapping "Draft": value=' + CAST(@CompanyDemographicValueId_Draft AS VARCHAR) + ', mapping=' + CAST(@CompanyDemographicValueMappingId_Draft AS VARCHAR);

        INSERT INTO Analytics.survey.CompanyDemographicValue (CompanyDemographicId)
        VALUES (@CompanyDemographicId);
        SET @CompanyDemographicValueId_Shared = SCOPE_IDENTITY();
        INSERT INTO Analytics.survey.CompanyDemographicValueMapping (Text, CompanyDemographicValueId)
        VALUES ('Shared', @CompanyDemographicValueId_Shared);
        SET @CompanyDemographicValueMappingId_Shared = SCOPE_IDENTITY();
        PRINT '  Created CompanyDemographicValue/Mapping "Shared": value=' + CAST(@CompanyDemographicValueId_Shared AS VARCHAR) + ', mapping=' + CAST(@CompanyDemographicValueMappingId_Shared AS VARCHAR);

        INSERT INTO Analytics.survey.CompanyDemographicValue (CompanyDemographicId)
        VALUES (@CompanyDemographicId);
        SET @CompanyDemographicValueId_NotLoggedIn = SCOPE_IDENTITY();
        INSERT INTO Analytics.survey.CompanyDemographicValueMapping (Text, CompanyDemographicValueId)
        VALUES ('Not Logged In', @CompanyDemographicValueId_NotLoggedIn);
        SET @CompanyDemographicValueMappingId_NotLoggedIn = SCOPE_IDENTITY();
        PRINT '  Created CompanyDemographicValue/Mapping "Not Logged In": value=' + CAST(@CompanyDemographicValueId_NotLoggedIn AS VARCHAR) + ', mapping=' + CAST(@CompanyDemographicValueMappingId_NotLoggedIn AS VARCHAR);
    END
    ELSE
    BEGIN
        -- ========== CompanyDemographic exists, retrieve all related IDs ==========
        PRINT 'Found existing CompanyDemographic structure:';
        PRINT '  CompanyDemographicId: ' + CAST(@CompanyDemographicId AS VARCHAR);
        PRINT '  CompanyDemographicNameMappingId: ' + CAST(@CompanyDemographicNameMappingId AS VARCHAR);

        -- Get each CompanyDemographicValueMapping ID (and its parent ValueId) by text
        SELECT @CompanyDemographicValueMappingId_NotStarted = cdvm.Id,
               @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';
        PRINT '  ValueMapping "Not Started": value=' + CAST(@CompanyDemographicValueId_NotStarted AS VARCHAR) + ', mapping=' + CAST(@CompanyDemographicValueMappingId_NotStarted AS VARCHAR);

        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';
        PRINT '  ValueMapping "Draft": value=' + CAST(@CompanyDemographicValueId_Draft AS VARCHAR) + ', mapping=' + CAST(@CompanyDemographicValueMappingId_Draft AS VARCHAR);

        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 '  ValueMapping "Shared": value=' + CAST(@CompanyDemographicValueId_Shared AS VARCHAR) + ', mapping=' + CAST(@CompanyDemographicValueMappingId_Shared AS VARCHAR);

        SELECT @CompanyDemographicValueMappingId_NotLoggedIn = cdvm.Id,
               @CompanyDemographicValueId_NotLoggedIn = 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 Logged In';
        IF @CompanyDemographicValueId_NotLoggedIn IS NULL
        BEGIN
            INSERT INTO Analytics.survey.CompanyDemographicValue (CompanyDemographicId)
            VALUES (@CompanyDemographicId);
            SET @CompanyDemographicValueId_NotLoggedIn = SCOPE_IDENTITY();
            INSERT INTO Analytics.survey.CompanyDemographicValueMapping (Text, CompanyDemographicValueId)
            VALUES ('Not Logged In', @CompanyDemographicValueId_NotLoggedIn);
            SET @CompanyDemographicValueMappingId_NotLoggedIn = SCOPE_IDENTITY();
            PRINT '  Created (new) CompanyDemographicValue/Mapping "Not Logged In": value=' + CAST(@CompanyDemographicValueId_NotLoggedIn AS VARCHAR) + ', mapping=' + CAST(@CompanyDemographicValueMappingId_NotLoggedIn AS VARCHAR);
        END
        ELSE
        BEGIN
            PRINT '  ValueMapping "Not Logged In": value=' + CAST(@CompanyDemographicValueId_NotLoggedIn AS VARCHAR) + ', mapping=' + CAST(@CompanyDemographicValueMappingId_NotLoggedIn AS VARCHAR);
        END
    END

    -- ========== STEP 4: Check if SurveyDemographic exists ==========
    SELECT @SurveyDemographicId = Id
    FROM Analytics.survey.SurveyDemographic
    WHERE SurveyId = @SurveyId
      AND CompanyDemographicNameMappingId = @CompanyDemographicNameMappingId;

    IF @SurveyDemographicId IS NULL
    BEGIN
        -- ========== Create SurveyDemographic and all SurveyDemographicValues at once ==========
        PRINT 'SurveyDemographic not found. Creating demographic for SurveyId: ' + CAST(@SurveyId AS VARCHAR) + ', Slot: ' + CAST(@DemographicSlot AS VARCHAR);


        -- ========== Find next available demographic slot (<=200) ==========
        WITH UsedSlots AS (
            SELECT Slot
            FROM Analytics.survey.SurveyDemographic
            WHERE SurveyId = @SurveyId
        ),
        AvailableSlots AS (
            SELECT TOP 1 n AS Slot
            FROM (
                SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
                FROM sys.all_objects
            ) Numbers
            WHERE n <= 200
            AND n NOT IN (SELECT Slot FROM UsedSlots)
            ORDER BY n
        )
        SELECT @DemographicSlot = Slot
        FROM AvailableSlots;

        IF @DemographicSlot IS NULL
        BEGIN
            RAISERROR('No available demographic slots found (all slots 1-200 are in use for SurveyId: %d)', 16, 1, @SurveyId);
        END
        ELSE
        BEGIN
            PRINT 'Selected available demographic slot: ' + CAST(@DemographicSlot AS VARCHAR);
        END

        -- Create SurveyDemographic
        INSERT INTO Analytics.survey.SurveyDemographic (
            SurveyId,
            Slot,
            Name,
            DemographicType,
            SliceAccessLevel,
            FilterAccessLevel,
            IsSelfReported,
            SurveyQuestionId,
            VisibilityLevel,
            CompanyDemographicNameMappingId
        )
        VALUES (
            @SurveyId,
            @DemographicSlot,
            @DemographicName,
            1,      -- DemographicType
            2,      -- SliceAccessLevel
            2,      -- FilterAccessLevel
            0,      -- IsSelfReported
            NULL,   -- SurveyQuestionId
            1,      -- VisibilityLevel
            @CompanyDemographicNameMappingId
        );
        SET @SurveyDemographicId = SCOPE_IDENTITY();
        PRINT '  Created SurveyDemographicId: ' + CAST(@SurveyDemographicId AS VARCHAR);

        -- Create all four SurveyDemographicValues
        INSERT INTO Analytics.survey.SurveyDemographicValue (
            SurveyDemographicId,
            SurveyId,
            Value,
            Code,
            TextValue1,
            CompanyDemographicValueMappingId
        )
        VALUES (
            @SurveyDemographicId,
            @SurveyId,
            'Not Started',
            0,
            'Not Started',
            @CompanyDemographicValueMappingId_NotStarted
        );
        PRINT '  Created SurveyDemographicValue "Not Started"';

        INSERT INTO Analytics.survey.SurveyDemographicValue (
            SurveyDemographicId,
            SurveyId,
            Value,
            Code,
            TextValue1,
            CompanyDemographicValueMappingId
        )
        VALUES (
            @SurveyDemographicId,
            @SurveyId,
            'Draft',
            1,
            'Draft',
            @CompanyDemographicValueMappingId_Draft
        );
        PRINT '  Created SurveyDemographicValue "Draft"';

        INSERT INTO Analytics.survey.SurveyDemographicValue (
            SurveyDemographicId,
            SurveyId,
            Value,
            Code,
            TextValue1,
            CompanyDemographicValueMappingId
        )
        VALUES (
            @SurveyDemographicId,
            @SurveyId,
            'Shared',
            2,
            'Shared',
            @CompanyDemographicValueMappingId_Shared
        );
        PRINT '  Created SurveyDemographicValue "Shared"';

        INSERT INTO Analytics.survey.SurveyDemographicValue (
            SurveyDemographicId,
            SurveyId,
            Value,
            Code,
            TextValue1,
            CompanyDemographicValueMappingId
        )
        VALUES (
            @SurveyDemographicId,
            @SurveyId,
            'Not Logged In',
            3,
            'Not Logged In',
            @CompanyDemographicValueMappingId_NotLoggedIn
        );
        PRINT '  Created SurveyDemographicValue "Not Logged In"';

        -- Update the slot variable to the one we used
        SELECT @DemographicSlot = Slot
        FROM Analytics.survey.SurveyDemographic
        WHERE Id = @SurveyDemographicId;
    END
    ELSE
    BEGIN
        -- ========== SurveyDemographic exists, retrieve the slot ==========
        PRINT 'Found existing SurveyDemographic:';
        PRINT '  SurveyDemographicId: ' + CAST(@SurveyDemographicId AS VARCHAR);

        SELECT @DemographicSlot = Slot
        FROM Analytics.survey.SurveyDemographic
        WHERE Id = @SurveyDemographicId;
        PRINT '  Using existing Slot: ' + CAST(@DemographicSlot AS VARCHAR);

        IF NOT EXISTS (
            SELECT 1 FROM Analytics.survey.SurveyDemographicValue
            WHERE SurveyDemographicId = @SurveyDemographicId AND Code = 3
        )
        BEGIN
            INSERT INTO Analytics.survey.SurveyDemographicValue (
                SurveyDemographicId,
                SurveyId,
                Value,
                Code,
                TextValue1,
                CompanyDemographicValueMappingId
            )
            VALUES (
                @SurveyDemographicId,
                @SurveyId,
                'Not Logged In',
                3,
                'Not Logged In',
                @CompanyDemographicValueMappingId_NotLoggedIn
            );
            PRINT '  Created (new) SurveyDemographicValue "Not Logged In"';
        END
    END


    -- ========== STEP 5: Update SurveyParticipant with Growth Plan State ==========
    PRINT 'Updating SurveyParticipant Growth Plan state for demographic slot ' + CAST(@DemographicSlot AS VARCHAR) + '...';

    SET @DemographicColumn = 'DemographicValue' + CAST(@DemographicSlot AS VARCHAR);
    SET @DynamicSQL = N'
        UPDATE sp
        SET sp.' + @DemographicColumn + N' = growthUser.GrowthPlanState
        FROM Analytics.survey.SurveyParticipant sp
        JOIN (
            SELECT au.Id,
            CASE
                WHEN gp.PublishedAt IS NOT NULL THEN 2
                WHEN gp.LastActivityAt > gp.CreatedAt OR cv.UpdatedAt > cv.CreatedAt THEN 1
                WHEN gp.OwnerId IS NOT NULL THEN 0
                ELSE 3
            END AS GrowthPlanState
            FROM Growth.ec.[User] u
            JOIN Growth.ec.[UserRoles] ur ON ur.UserId = u.Id
            JOIN Analytics.survey.Users au ON au.MembershipId = u.Id
            LEFT JOIN Growth.develop.GrowthPlan gp ON gp.OwnerId = u.Id
            LEFT JOIN Growth.develop.CareerVision cv ON cv.UserId = u.Id
            WHERE u.OrganizationId = @OrganizationId
              AND (ur.GrowthUser = 1 or ur.GrowthAdministrator = 1)
        ) growthUser ON sp.UserId = growthUser.Id AND sp.SurveyId = @SurveyId';

    EXEC sp_executesql @DynamicSQL,
        N'@OrganizationId INT, @SurveyId INT',
        @OrganizationId = @OrganizationId,
        @SurveyId = @SurveyId;

    PRINT 'SurveyParticipant update complete.';

    COMMIT TRANSACTION;
    PRINT '';
    PRINT '========== SUCCESS: All operations completed successfully ==========';
    PRINT '';
    PRINT 'Summary:';
    PRINT '  CompanyId: ' + CAST(@CompanyId AS VARCHAR);
    PRINT '  OrganizationId: ' + CAST(@OrganizationId AS VARCHAR);
    PRINT '  SurveyId: ' + CAST(@SurveyId AS VARCHAR);
    PRINT '  Demographic Slot: ' + CAST(@DemographicSlot AS VARCHAR);
    PRINT '  Demographic Name: ' + @DemographicName;
    PRINT '';
    PRINT 'Generated IDs:';
    PRINT '  CompanyDemographicId: ' + CAST(@CompanyDemographicId AS VARCHAR);
    PRINT '  CompanyDemographicNameMappingId: ' + CAST(@CompanyDemographicNameMappingId AS VARCHAR);
    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);
    PRINT '  CompanyDemographicValueId - Not Logged In: ' + CAST(@CompanyDemographicValueId_NotLoggedIn AS VARCHAR);
    PRINT '  ValueMapping - Not Started: ' + CAST(@CompanyDemographicValueMappingId_NotStarted AS VARCHAR);
    PRINT '  ValueMapping - Draft: ' + CAST(@CompanyDemographicValueMappingId_Draft AS VARCHAR);
    PRINT '  ValueMapping - Shared: ' + CAST(@CompanyDemographicValueMappingId_Shared AS VARCHAR);
    PRINT '  ValueMapping - Not Logged In: ' + CAST(@CompanyDemographicValueMappingId_NotLoggedIn AS VARCHAR);
    PRINT '  SurveyDemographicId: ' + CAST(@SurveyDemographicId 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;