SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

/*
Remove obsolete dependency from the development reset procedure.
The old visualisation assignment table was intentionally dropped by script 150.
*/

CREATE OR ALTER PROCEDURE dbo.StoryIntelligence_DevelopmentAccountReset
    @DevelopmentEmail nvarchar(256)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    DECLARE @UserID int;
    SELECT @UserID = UserID
    FROM dbo.AppUser
    WHERE Email = @DevelopmentEmail;

    IF @UserID IS NULL
        THROW 52101, 'Development account email was not found.', 1;

    DECLARE @ProjectIDs TABLE (ProjectID int NOT NULL PRIMARY KEY);
    INSERT @ProjectIDs (ProjectID)
    SELECT DISTINCT p.ProjectID
    FROM dbo.Projects p
    INNER JOIN dbo.ProjectUserAccess pua ON pua.ProjectID = p.ProjectID
    WHERE pua.UserID = @UserID
        AND pua.AccessRole = N'Owner'
        AND pua.IsActive = 1;

    DECLARE @DeletedProjects table (ProjectID int NOT NULL PRIMARY KEY, ProjectName nvarchar(200) NULL);
    INSERT @DeletedProjects(ProjectID, ProjectName)
    SELECT p.ProjectID, p.ProjectName
    FROM dbo.Projects p
    INNER JOIN @ProjectIDs ids ON ids.ProjectID = p.ProjectID;

    BEGIN TRY
        BEGIN TRANSACTION;

        UPDATE dbo.StoryIntelligenceRuns
        SET Status = CASE WHEN Status IN (N'Pending', N'Running') THEN N'Cancelled' ELSE Status END,
            CurrentStage = N'Cancelled by Phase 21U reset',
            CurrentMessage = N'Development reset invalidated this run.',
            CompletedUtc = COALESCE(CompletedUtc, SYSUTCDATETIME()),
            UpdatedUtc = SYSUTCDATETIME()
        WHERE ProjectID IN (SELECT ProjectID FROM @ProjectIDs)
            AND Status IN (N'Pending', N'Running');

        UPDATE dbo.IllustrationLibraryItems
        SET Status = CASE WHEN Status IN (N'Queued', N'Generating') THEN N'Failed' ELSE Status END,
            LastError = N'Invalidated by Phase 21U development reset',
            UpdatedUtc = SYSUTCDATETIME()
        WHERE Category IN (N'Character', N'Location', N'Asset')
            AND Status IN (N'Queued', N'Generating');

        DELETE FROM dbo.StoryMemoryIllustrationAssignments;
        DELETE FROM dbo.StoryMemoryIllustrationDemands;
        DELETE FROM dbo.StoryMemoryRelationships;
        DELETE FROM dbo.StoryMemoryCharacterAttributes;
        DELETE FROM dbo.StoryMemoryCharacterAliases;
        DELETE FROM dbo.StoryMemoryAssets;
        DELETE FROM dbo.StoryMemoryLocations;
        DELETE FROM dbo.StoryMemoryCharacters;
        DELETE FROM dbo.StoryMemoryProcessedSceneResults;
        DELETE FROM dbo.StoryMemoryImportPreferences;
        DELETE FROM dbo.StoryIntelligenceIllustrationDemands;

        IF OBJECT_ID(N'dbo.IllustrationGenerationAttempts', N'U') IS NOT NULL
            DELETE FROM dbo.IllustrationGenerationAttempts;

        IF OBJECT_ID(N'dbo.IllustrationLibraryApprovalHistory', N'U') IS NOT NULL
            DELETE FROM dbo.IllustrationLibraryApprovalHistory;

        IF OBJECT_ID(N'dbo.IllustrationLibraryLineage', N'U') IS NOT NULL
            DELETE FROM dbo.IllustrationLibraryLineage;

        UPDATE dbo.IllustrationLibraryItems
        SET ParentItemID = NULL
        WHERE Category IN (N'Character', N'Location', N'Asset');

        DELETE FROM dbo.IllustrationLibraryItems
        WHERE Category IN (N'Character', N'Location', N'Asset');

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
        THROW;
    END CATCH;

    DECLARE @ProjectID int;
    DECLARE project_cursor CURSOR LOCAL FAST_FORWARD FOR
        SELECT ProjectID FROM @ProjectIDs ORDER BY ProjectID;

    OPEN project_cursor;
    FETCH NEXT FROM project_cursor INTO @ProjectID;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        UPDATE dbo.Projects
        SET IsArchived = 1,
            ArchivedDate = COALESCE(ArchivedDate, SYSUTCDATETIME()),
            ArchivedReason = N'Phase 21U development reset'
        WHERE ProjectID = @ProjectID;

        EXEC dbo.ProjectHardDelete_ForOwner @ProjectID = @ProjectID, @UserID = @UserID;
        FETCH NEXT FROM project_cursor INTO @ProjectID;
    END
    CLOSE project_cursor;
    DEALLOCATE project_cursor;

    SELECT N'ProjectsDeleted' AS Metric, COUNT(*) AS Value FROM @DeletedProjects
    UNION ALL SELECT N'RemainingOwnedProjects', COUNT(*)
        FROM dbo.Projects p
        INNER JOIN dbo.ProjectUserAccess pua ON pua.ProjectID = p.ProjectID
        WHERE pua.UserID = @UserID AND pua.IsActive = 1
    UNION ALL SELECT N'RemainingStoryIntelligenceRuns', COUNT(*)
        FROM dbo.StoryIntelligenceRuns
        WHERE UserID = @UserID
    UNION ALL SELECT N'RemainingIllustrationLibraryItems', COUNT(*)
        FROM dbo.IllustrationLibraryItems
        WHERE Category IN (N'Character', N'Location', N'Asset');

    SELECT ProjectID, ProjectName
    FROM @DeletedProjects
    ORDER BY ProjectID;
END;
GO