SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

CREATE OR ALTER PROCEDURE dbo.ProjectHardDelete_ForOwner
    @ProjectID int,
    @UserID int
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    IF NOT EXISTS
    (
        SELECT 1
        FROM dbo.Projects p
        INNER JOIN dbo.ProjectUserAccess pua ON pua.ProjectID = p.ProjectID
        WHERE p.ProjectID = @ProjectID
            AND pua.UserID = @UserID
            AND pua.AccessRole = N'Owner'
            AND pua.IsActive = 1
    )
        THROW 52001, 'Project was not found for this owner.', 1;

    IF EXISTS (SELECT 1 FROM dbo.Projects WHERE ProjectID = @ProjectID AND IsArchived = 0)
        THROW 52002, 'Only archived projects can be permanently deleted.', 1;

    BEGIN TRY
        BEGIN TRANSACTION;

        DECLARE @BookIDs TABLE (BookID int NOT NULL PRIMARY KEY);
        DECLARE @ChapterIDs TABLE (ChapterID int NOT NULL PRIMARY KEY);
        DECLARE @SceneIDs TABLE (SceneID int NOT NULL PRIMARY KEY);
        DECLARE @StoryIntelligenceRunIDs TABLE (StoryIntelligenceRunID int NOT NULL PRIMARY KEY);
        DECLARE @StoryIntelligenceChapterResultIDs TABLE (ChapterResultID int NOT NULL PRIMARY KEY);

        INSERT @BookIDs (BookID)
        SELECT BookID FROM dbo.Books WHERE ProjectID = @ProjectID;

        INSERT @ChapterIDs (ChapterID)
        SELECT c.ChapterID
        FROM dbo.Chapters c
        INNER JOIN @BookIDs b ON b.BookID = c.BookID;

        INSERT @SceneIDs (SceneID)
        SELECT s.SceneID
        FROM dbo.Scenes s
        INNER JOIN @ChapterIDs c ON c.ChapterID = s.ChapterID;

        INSERT @StoryIntelligenceRunIDs (StoryIntelligenceRunID)
        SELECT DISTINCT r.StoryIntelligenceRunID
        FROM dbo.StoryIntelligenceRuns r
        WHERE r.ProjectID = @ProjectID
            OR EXISTS (SELECT 1 FROM @BookIDs b WHERE b.BookID = r.BookID)
            OR EXISTS (SELECT 1 FROM @ChapterIDs c WHERE c.ChapterID = r.ChapterID)
            OR EXISTS (SELECT 1 FROM @SceneIDs s INNER JOIN dbo.Scenes scene ON scene.SceneID = s.SceneID WHERE scene.ImportRunID = r.StoryIntelligenceRunID);

        INSERT @StoryIntelligenceChapterResultIDs (ChapterResultID)
        SELECT DISTINCT cr.ChapterResultID
        FROM dbo.StoryIntelligenceChapterResults cr
        WHERE cr.ProjectID = @ProjectID
            OR EXISTS (SELECT 1 FROM @StoryIntelligenceRunIDs r WHERE r.StoryIntelligenceRunID = cr.StoryIntelligenceRunID)
            OR EXISTS (SELECT 1 FROM @BookIDs b WHERE b.BookID = cr.BookID)
            OR EXISTS (SELECT 1 FROM @ChapterIDs c WHERE c.ChapterID = cr.ChapterID);

        DELETE sr
        FROM dbo.StoryIntelligenceSceneResults sr
        WHERE sr.ProjectID = @ProjectID
            OR EXISTS (SELECT 1 FROM @StoryIntelligenceRunIDs r WHERE r.StoryIntelligenceRunID = sr.StoryIntelligenceRunID)
            OR EXISTS (SELECT 1 FROM @StoryIntelligenceChapterResultIDs cr WHERE cr.ChapterResultID = sr.ChapterResultID)
            OR EXISTS (SELECT 1 FROM @BookIDs b WHERE b.BookID = sr.BookID)
            OR EXISTS (SELECT 1 FROM @ChapterIDs c WHERE c.ChapterID = sr.ChapterID)
            OR EXISTS (SELECT 1 FROM @SceneIDs s WHERE s.SceneID = sr.SceneID);

        DELETE cr
        FROM dbo.StoryIntelligenceChapterResults cr
        WHERE cr.ProjectID = @ProjectID
            OR EXISTS (SELECT 1 FROM @StoryIntelligenceRunIDs r WHERE r.StoryIntelligenceRunID = cr.StoryIntelligenceRunID)
            OR EXISTS (SELECT 1 FROM @StoryIntelligenceChapterResultIDs result WHERE result.ChapterResultID = cr.ChapterResultID)
            OR EXISTS (SELECT 1 FROM @BookIDs b WHERE b.BookID = cr.BookID)
            OR EXISTS (SELECT 1 FROM @ChapterIDs c WHERE c.ChapterID = cr.ChapterID);

        DELETE commitRows
        FROM dbo.StoryIntelligenceImportCommits commitRows
        WHERE commitRows.ProjectID = @ProjectID
            OR EXISTS (SELECT 1 FROM @StoryIntelligenceRunIDs r WHERE r.StoryIntelligenceRunID = commitRows.StoryIntelligenceRunID)
            OR EXISTS (SELECT 1 FROM @BookIDs b WHERE b.BookID = commitRows.BookID)
            OR EXISTS (SELECT 1 FROM @ChapterIDs c WHERE c.ChapterID = commitRows.ChapterID);

        UPDATE scene
        SET ImportRunID = NULL
        FROM dbo.Scenes scene
        INNER JOIN @SceneIDs s ON s.SceneID = scene.SceneID
        WHERE scene.ImportRunID IS NOT NULL;

        DELETE r
        FROM dbo.StoryIntelligenceRuns r
        WHERE EXISTS (SELECT 1 FROM @StoryIntelligenceRunIDs runIDs WHERE runIDs.StoryIntelligenceRunID = r.StoryIntelligenceRunID);

        DELETE job
        FROM dbo.StoryIntelligenceJobs job
        LEFT JOIN dbo.StoryIntelligenceConsents consent ON consent.StoryIntelligenceConsentID = job.StoryIntelligenceConsentID
        WHERE job.ProjectID = @ProjectID
            OR EXISTS (SELECT 1 FROM @BookIDs b WHERE b.BookID = job.BookID)
            OR consent.ProjectID = @ProjectID
            OR EXISTS (SELECT 1 FROM @BookIDs b WHERE b.BookID = consent.BookID);

        DELETE consent
        FROM dbo.StoryIntelligenceConsents consent
        WHERE consent.ProjectID = @ProjectID
            OR EXISTS (SELECT 1 FROM @BookIDs b WHERE b.BookID = consent.BookID);

        DELETE FROM dbo.ProjectActivity WHERE ProjectID = @ProjectID;
        DELETE prh
        FROM dbo.ProjectRestoreHistory prh
        LEFT JOIN dbo.ProjectRestorePoints sourcePoint ON sourcePoint.ProjectRestorePointID = prh.SourceRestorePointID
        LEFT JOIN dbo.ProjectRestorePoints safetyPoint ON safetyPoint.ProjectRestorePointID = prh.SafetyRestorePointID
        WHERE prh.ProjectID = @ProjectID
            OR sourcePoint.ProjectID = @ProjectID
            OR safetyPoint.ProjectID = @ProjectID;

        DELETE FROM dbo.ProjectRestorePoints WHERE ProjectID = @ProjectID;
        DELETE FROM dbo.ProjectInvitation WHERE ProjectID = @ProjectID;

        DELETE uf
        FROM dbo.UserFile uf
        WHERE uf.ProjectID = @ProjectID
            OR EXISTS
            (
                SELECT 1
                FROM dbo.SceneAttachments sa
                INNER JOIN @SceneIDs s ON s.SceneID = sa.SceneID
                WHERE sa.FilePath = uf.StoragePath
            );

        DELETE wsi
        FROM dbo.WritingScheduleItems wsi
        INNER JOIN dbo.WritingPlans wp ON wp.WritingPlanID = wsi.WritingPlanID
        WHERE wp.ProjectID = @ProjectID;

        DELETE wsi
        FROM dbo.WritingScheduleItems wsi
        INNER JOIN @SceneIDs s ON s.SceneID = wsi.SceneID;

        DELETE FROM dbo.WritingPlans WHERE ProjectID = @ProjectID OR EXISTS (SELECT 1 FROM @BookIDs b WHERE b.BookID = dbo.WritingPlans.BookID);

        UPDATE dbo.UserOnboardingState
        SET ProjectID = NULL,
            BookID = NULL
        WHERE ProjectID = @ProjectID
            OR EXISTS (SELECT 1 FROM @BookIDs b WHERE b.BookID = dbo.UserOnboardingState.BookID);

        DELETE ombsm FROM dbo.OnboardingManuscriptBuildSceneMap ombsm INNER JOIN @SceneIDs scene ON scene.SceneID = ombsm.SceneID;
        DELETE ombcm FROM dbo.OnboardingManuscriptBuildChapterMap ombcm INNER JOIN @ChapterIDs chapter ON chapter.ChapterID = ombcm.ChapterID;
        DELETE ombcm
        FROM dbo.OnboardingManuscriptBuildChapterMap ombcm
        INNER JOIN dbo.OnboardingManuscriptBuilds build ON build.OnboardingManuscriptBuildID = ombcm.OnboardingManuscriptBuildID
        WHERE build.ProjectID = @ProjectID OR EXISTS (SELECT 1 FROM @BookIDs b WHERE b.BookID = build.BookID);
        DELETE ombsm
        FROM dbo.OnboardingManuscriptBuildSceneMap ombsm
        INNER JOIN dbo.OnboardingManuscriptBuilds build ON build.OnboardingManuscriptBuildID = ombsm.OnboardingManuscriptBuildID
        WHERE build.ProjectID = @ProjectID OR EXISTS (SELECT 1 FROM @BookIDs b WHERE b.BookID = build.BookID);
        DELETE ombcharm
        FROM dbo.OnboardingManuscriptBuildCharacterMap ombcharm
        INNER JOIN dbo.OnboardingManuscriptBuilds build ON build.OnboardingManuscriptBuildID = ombcharm.OnboardingManuscriptBuildID
        WHERE build.ProjectID = @ProjectID OR EXISTS (SELECT 1 FROM @BookIDs b WHERE b.BookID = build.BookID);
        DELETE FROM dbo.OnboardingManuscriptBuilds WHERE ProjectID = @ProjectID OR EXISTS (SELECT 1 FROM @BookIDs b WHERE b.BookID = dbo.OnboardingManuscriptBuilds.BookID);

        DELETE sw
        FROM dbo.ScenarioWarnings sw
        INNER JOIN dbo.Scenarios sc ON sc.ScenarioID = sw.ScenarioID
        WHERE sc.ProjectID = @ProjectID;

        DELETE svr
        FROM dbo.ScenarioValidationRuns svr
        INNER JOIN dbo.Scenarios sc ON sc.ScenarioID = svr.ScenarioID
        WHERE sc.ProjectID = @ProjectID;

        DELETE sso
        FROM dbo.ScenarioSceneOrder sso
        INNER JOIN dbo.Scenarios sc ON sc.ScenarioID = sso.ScenarioID
        WHERE sc.ProjectID = @ProjectID;

        DELETE sso
        FROM dbo.ScenarioSceneOrder sso
        WHERE EXISTS (SELECT 1 FROM @SceneIDs scene WHERE scene.SceneID = sso.SceneID)
            OR EXISTS (SELECT 1 FROM @ChapterIDs chapter WHERE chapter.ChapterID = sso.ProposedChapterID);

        DELETE FROM dbo.Scenarios WHERE ProjectID = @ProjectID;

        DELETE cwa
        FROM dbo.ContinuityWarningAcknowledgements cwa
        WHERE cwa.ProjectID = @ProjectID
            OR EXISTS (SELECT 1 FROM @SceneIDs scene WHERE scene.SceneID = cwa.SceneID);

        DELETE FROM dbo.ContinuityWarnings WHERE ProjectID = @ProjectID;
        DELETE FROM dbo.ValidationRuns WHERE ProjectID = @ProjectID;

        DELETE sfpo FROM dbo.SceneFloorPlanOccupancy sfpo INNER JOIN @SceneIDs s ON s.SceneID = sfpo.SceneID;
        DELETE sa FROM dbo.SceneAttachments sa INNER JOIN @SceneIDs s ON s.SceneID = sa.SceneID;
        DELETE sci FROM dbo.SceneChecklistItems sci INNER JOIN @SceneIDs s ON s.SceneID = sci.SceneID;
        DELETE sw FROM dbo.SceneWorkflow sw INNER JOIN @SceneIDs s ON s.SceneID = sw.SceneID;
        DELETE sn FROM dbo.SceneNotes sn INNER JOIN @SceneIDs s ON s.SceneID = sn.SceneID;
        DELETE cg FROM dbo.ChapterGoals cg INNER JOIN @ChapterIDs c ON c.ChapterID = cg.ChapterID;

        DELETE sd
        FROM dbo.SceneDependencies sd
        LEFT JOIN @SceneIDs ss ON ss.SceneID = sd.SourceSceneID
        LEFT JOIN @SceneIDs ts ON ts.SceneID = sd.TargetSceneID
        WHERE sd.ProjectID = @ProjectID
            OR ss.SceneID IS NOT NULL
            OR ts.SceneID IS NOT NULL;

        DELETE re
        FROM dbo.RelationshipEvents re
        INNER JOIN dbo.CharacterRelationships cr ON cr.CharacterRelationshipID = re.CharacterRelationshipID
        WHERE cr.ProjectID = @ProjectID;

        DELETE re FROM dbo.RelationshipEvents re INNER JOIN @SceneIDs scene ON scene.SceneID = re.SceneID;
        DELETE FROM dbo.CharacterRelationships WHERE ProjectID = @ProjectID;

        DELETE ck
        FROM dbo.CharacterKnowledge ck
        LEFT JOIN dbo.Characters ch ON ch.CharacterID = ck.CharacterID
        LEFT JOIN @SceneIDs scene ON scene.SceneID = ck.SceneID
        LEFT JOIN dbo.StoryAssets sa ON sa.StoryAssetID = ck.StoryAssetID
        LEFT JOIN dbo.PlotThreads pt ON pt.PlotThreadID = ck.PlotThreadID
        LEFT JOIN dbo.PlotLines pl ON pl.PlotLineID = pt.PlotLineID
        WHERE ch.ProjectID = @ProjectID
            OR scene.SceneID IS NOT NULL
            OR sa.ProjectID = @ProjectID
            OR pl.ProjectID = @ProjectID;

        DELETE cae
        FROM dbo.CharacterAttributeEvents cae
        LEFT JOIN dbo.Characters ch ON ch.CharacterID = cae.CharacterID
        LEFT JOIN @SceneIDs scene ON scene.SceneID = cae.SceneID
        WHERE ch.ProjectID = @ProjectID
            OR scene.SceneID IS NOT NULL;

        DELETE sc
        FROM dbo.SceneCharacters sc
        LEFT JOIN dbo.Characters ch ON ch.CharacterID = sc.CharacterID
        LEFT JOIN @SceneIDs scene ON scene.SceneID = sc.SceneID
        WHERE ch.ProjectID = @ProjectID
            OR scene.SceneID IS NOT NULL;

        DELETE acec
        FROM dbo.AssetCustodyEventCharacters acec
        INNER JOIN dbo.AssetCustodyEvents ace ON ace.AssetCustodyEventID = acec.AssetCustodyEventID
        INNER JOIN dbo.StoryAssets sa ON sa.StoryAssetID = ace.StoryAssetID
        WHERE sa.ProjectID = @ProjectID;

        DELETE acec
        FROM dbo.AssetCustodyEventCharacters acec
        INNER JOIN dbo.AssetCustodyEvents ace ON ace.AssetCustodyEventID = acec.AssetCustodyEventID
        INNER JOIN @SceneIDs scene ON scene.SceneID = ace.SceneID;

        DELETE ace
        FROM dbo.AssetCustodyEvents ace
        LEFT JOIN dbo.StoryAssets sa ON sa.StoryAssetID = ace.StoryAssetID
        LEFT JOIN @SceneIDs scene ON scene.SceneID = ace.SceneID
        WHERE sa.ProjectID = @ProjectID
            OR scene.SceneID IS NOT NULL;

        DELETE ad
        FROM dbo.AssetDependencies ad
        INNER JOIN dbo.StoryAssets sourceAsset ON sourceAsset.StoryAssetID = ad.SourceAssetID
        INNER JOIN dbo.StoryAssets targetAsset ON targetAsset.StoryAssetID = ad.TargetAssetID
        WHERE sourceAsset.ProjectID = @ProjectID
            OR targetAsset.ProjectID = @ProjectID;

        DELETE ae
        FROM dbo.AssetEvents ae
        LEFT JOIN dbo.StoryAssets sa ON sa.StoryAssetID = ae.StoryAssetID
        LEFT JOIN @SceneIDs scene ON scene.SceneID = ae.SceneID
        WHERE sa.ProjectID = @ProjectID
            OR scene.SceneID IS NOT NULL;

        DELETE sal
        FROM dbo.SceneAssetLocations sal
        LEFT JOIN dbo.StoryAssets sa ON sa.StoryAssetID = sal.StoryAssetID
        LEFT JOIN @SceneIDs scene ON scene.SceneID = sal.SceneID
        WHERE sa.ProjectID = @ProjectID
            OR scene.SceneID IS NOT NULL;

        DELETE petpl
        FROM dbo.PlotEventTargetPlotLines petpl
        LEFT JOIN dbo.ThreadEvents te ON te.ThreadEventID = petpl.PlotEventID
        LEFT JOIN dbo.PlotThreads pt ON pt.PlotThreadID = te.PlotThreadID
        LEFT JOIN dbo.PlotLines eventPlotLine ON eventPlotLine.PlotLineID = pt.PlotLineID
        LEFT JOIN dbo.PlotLines targetPlotLine ON targetPlotLine.PlotLineID = petpl.PlotLineID
        WHERE eventPlotLine.ProjectID = @ProjectID
            OR targetPlotLine.ProjectID = @ProjectID
            OR EXISTS (SELECT 1 FROM @SceneIDs scene WHERE scene.SceneID = te.SceneID);

        DELETE te
        FROM dbo.ThreadEvents te
        LEFT JOIN dbo.PlotThreads pt ON pt.PlotThreadID = te.PlotThreadID
        LEFT JOIN dbo.PlotLines pl ON pl.PlotLineID = pt.PlotLineID
        LEFT JOIN dbo.PlotLines target ON target.PlotLineID = te.TargetPlotLineID
        LEFT JOIN @SceneIDs scene ON scene.SceneID = te.SceneID
        WHERE pl.ProjectID = @ProjectID
            OR target.ProjectID = @ProjectID
            OR scene.SceneID IS NOT NULL;

        DELETE pt
        FROM dbo.PlotThreads pt
        INNER JOIN dbo.PlotLines pl ON pl.PlotLineID = pt.PlotLineID
        WHERE pl.ProjectID = @ProjectID;

        UPDATE dbo.PlotLines
        SET ParentPlotLineID = NULL,
            EmergesFromPlotLineID = NULL
        WHERE ProjectID = @ProjectID;

        DELETE FROM dbo.PlotLines WHERE ProjectID = @ProjectID;

        DELETE FROM dbo.TimelineViewPresets WHERE ProjectID = @ProjectID OR EXISTS (SELECT 1 FROM @BookIDs b WHERE b.BookID = dbo.TimelineViewPresets.BookID);
        DELETE FROM dbo.ProjectTimelineMetricSettings WHERE ProjectID = @ProjectID;
        DELETE FROM dbo.ProjectTimelineSettings WHERE ProjectID = @ProjectID;

        DELETE smv FROM dbo.SceneMetricValues smv INNER JOIN @SceneIDs s ON s.SceneID = smv.SceneID;
        DELETE sp FROM dbo.ScenePurposes sp INNER JOIN @SceneIDs s ON s.SceneID = sp.SceneID;
        DELETE so FROM dbo.SceneOutcomes so INNER JOIN @SceneIDs s ON s.SceneID = so.SceneID;

        UPDATE dbo.Characters
        SET AgeReferenceSceneID = NULL
        WHERE ProjectID = @ProjectID;

        UPDATE s
        SET PrimaryLocationID = NULL,
            FloorPlanID = NULL,
            InitialFloorPlanFloorID = NULL,
            ManuscriptDocumentID = NULL
        FROM dbo.Scenes s
        INNER JOIN @SceneIDs scene ON scene.SceneID = s.SceneID;

        UPDATE c
        SET ManuscriptDocumentID = NULL
        FROM dbo.Chapters c
        INNER JOIN @ChapterIDs chapter ON chapter.ChapterID = c.ChapterID;

        UPDATE dbo.StoryAssets
        SET CurrentLocationID = NULL
        WHERE ProjectID = @ProjectID;

        DELETE fpt
        FROM dbo.FloorPlanTransitions fpt
        INNER JOIN dbo.FloorPlanFloors fpf ON fpf.FloorPlanFloorID = fpt.FloorPlanFloorID
        INNER JOIN dbo.FloorPlans fp ON fp.FloorPlanID = fpf.FloorPlanID
        WHERE fp.ProjectID = @ProjectID;

        DELETE fpb
        FROM dbo.FloorPlanBlocks fpb
        INNER JOIN dbo.FloorPlanFloors fpf ON fpf.FloorPlanFloorID = fpb.FloorPlanFloorID
        INNER JOIN dbo.FloorPlans fp ON fp.FloorPlanID = fpf.FloorPlanID
        WHERE fp.ProjectID = @ProjectID;

        DELETE fpf
        FROM dbo.FloorPlanFloors fpf
        INNER JOIN dbo.FloorPlans fp ON fp.FloorPlanID = fpf.FloorPlanID
        WHERE fp.ProjectID = @ProjectID;

        DELETE FROM dbo.FloorPlans WHERE ProjectID = @ProjectID;

        DELETE FROM dbo.StoryAssets WHERE ProjectID = @ProjectID;

        UPDATE dbo.Locations
        SET ParentLocationID = NULL
        WHERE ProjectID = @ProjectID;

        DELETE lr
        FROM dbo.LocationRelationships lr
        INNER JOIN dbo.Locations fromLocation ON fromLocation.LocationID = lr.FromLocationID
        INNER JOIN dbo.Locations toLocation ON toLocation.LocationID = lr.ToLocationID
        WHERE fromLocation.ProjectID = @ProjectID
            OR toLocation.ProjectID = @ProjectID;

        DELETE FROM dbo.Locations WHERE ProjectID = @ProjectID;

        DELETE s FROM dbo.Scenes s INNER JOIN @SceneIDs scene ON scene.SceneID = s.SceneID;
        DELETE c FROM dbo.Chapters c INNER JOIN @ChapterIDs chapter ON chapter.ChapterID = c.ChapterID;

        DELETE FROM dbo.ManuscriptDocuments
        WHERE ProjectID = @ProjectID
            OR EXISTS (SELECT 1 FROM @BookIDs b WHERE b.BookID = dbo.ManuscriptDocuments.BookID);

        DELETE FROM dbo.Books WHERE ProjectID = @ProjectID;

        DELETE FROM dbo.CharacterImages WHERE ProjectID = @ProjectID;
        DELETE FROM dbo.Characters WHERE ProjectID = @ProjectID;

        DELETE gpmm
        FROM dbo.GenreMetricPresetMetrics gpmm
        INNER JOIN dbo.SceneMetricTypes mt ON mt.MetricTypeID = gpmm.MetricTypeID
        WHERE mt.ProjectID = @ProjectID;

        DELETE FROM dbo.SceneMetricTypes WHERE ProjectID = @ProjectID;
        DELETE FROM dbo.CharacterAttributeTypes WHERE ProjectID = @ProjectID;
        DELETE FROM dbo.AssetStates WHERE ProjectID = @ProjectID;

        DELETE FROM dbo.ProjectUserAccess WHERE ProjectID = @ProjectID;
        DELETE FROM dbo.Projects WHERE ProjectID = @ProjectID;

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

        THROW;
    END CATCH
END;
GO