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

-- ========== CONFIGURATION VARIABLES ==========
DECLARE @CompanyId INT = ;                          -- Analytics CompanyId
DECLARE @SurveyId INT = ;                        -- 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 INT;
DECLARE @CompanyDemographicValueMappingId_NotStarted INT;
DECLARE @CompanyDemographicValueMappingId_Draft INT;
DECLARE @CompanyDemographicValueMappingId_Shared 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);
        
        -- Create CompanyDemographicValue
        INSERT INTO Analytics.survey.CompanyDemographicValue (CompanyDemographicId)
        VALUES (@CompanyDemographicId);
        SET @CompanyDemographicValueId = SCOPE_IDENTITY();
        PRINT '  Created CompanyDemographicValueId: ' + CAST(@CompanyDemographicValueId AS VARCHAR);
        
        -- Create all three CompanyDemographicValueMappings
        INSERT INTO Analytics.survey.CompanyDemographicValueMapping (Text, CompanyDemographicValueId)
        VALUES ('Not Started', @CompanyDemographicValueId);
        SET @CompanyDemographicValueMappingId_NotStarted = SCOPE_IDENTITY();
        PRINT '  Created CompanyDemographicValueMapping "Not Started": ' + CAST(@CompanyDemographicValueMappingId_NotStarted AS VARCHAR);
        
        INSERT INTO Analytics.survey.CompanyDemographicValueMapping (Text, CompanyDemographicValueId)
        VALUES ('Draft', @CompanyDemographicValueId);
        SET @CompanyDemographicValueMappingId_Draft = SCOPE_IDENTITY();
        PRINT '  Created CompanyDemographicValueMapping "Draft": ' + CAST(@CompanyDemographicValueMappingId_Draft AS VARCHAR);
        
        INSERT INTO Analytics.survey.CompanyDemographicValueMapping (Text, CompanyDemographicValueId)
        VALUES ('Shared', @CompanyDemographicValueId);
        SET @CompanyDemographicValueMappingId_Shared = SCOPE_IDENTITY();
        PRINT '  Created CompanyDemographicValueMapping "Shared": ' + CAST(@CompanyDemographicValueMappingId_Shared 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 CompanyDemographicValueId
        SELECT @CompanyDemographicValueId = Id
        FROM Analytics.survey.CompanyDemographicValue
        WHERE CompanyDemographicId = @CompanyDemographicId;
        PRINT '  CompanyDemographicValueId: ' + CAST(@CompanyDemographicValueId AS VARCHAR);
        
        -- Get all three CompanyDemographicValueMapping IDs
        SELECT @CompanyDemographicValueMappingId_NotStarted = Id
        FROM Analytics.survey.CompanyDemographicValueMapping
        WHERE CompanyDemographicValueId = @CompanyDemographicValueId
          AND Text = 'Not Started';
        PRINT '  ValueMapping "Not Started": ' + CAST(@CompanyDemographicValueMappingId_NotStarted AS VARCHAR);
        
        SELECT @CompanyDemographicValueMappingId_Draft = Id
        FROM Analytics.survey.CompanyDemographicValueMapping
        WHERE CompanyDemographicValueId = @CompanyDemographicValueId
          AND Text = 'Draft';
        PRINT '  ValueMapping "Draft": ' + CAST(@CompanyDemographicValueMappingId_Draft AS VARCHAR);
        
        SELECT @CompanyDemographicValueMappingId_Shared = Id
        FROM Analytics.survey.CompanyDemographicValueMapping
        WHERE CompanyDemographicValueId = @CompanyDemographicValueId
          AND Text = 'Shared';
        PRINT '  ValueMapping "Shared": ' + CAST(@CompanyDemographicValueMappingId_Shared AS VARCHAR);
    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 three 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"';
        
        -- 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);
    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
                ELSE 0 
            END AS GrowthPlanState
            FROM Growth.develop.GrowthPlan gp 
            JOIN Growth.ec.[User] u ON u.Id = gp.OwnerId
            JOIN Analytics.survey.Users au ON au.MembershipId = u.Id
            LEFT JOIN Growth.develop.CareerVision cv on cv.UserId = u.Id
            WHERE u.OrganizationId = @OrganizationId
        ) 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: ' + CAST(@CompanyDemographicValueId 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 '  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;