SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

IF OBJECT_ID(N'dbo.SceneNoteTypes', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.SceneNoteTypes
    (
        SceneNoteTypeID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_SceneNoteTypes PRIMARY KEY,
        TypeName nvarchar(100) NOT NULL,
        SortOrder int NOT NULL,
        IsActive bit NOT NULL CONSTRAINT DF_SceneNoteTypes_IsActive DEFAULT 1,
        CONSTRAINT UQ_SceneNoteTypes_TypeName UNIQUE (TypeName)
    );
END;
GO

IF OBJECT_ID(N'dbo.SceneNotes', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.SceneNotes
    (
        SceneNoteID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_SceneNotes PRIMARY KEY,
        SceneID int NOT NULL,
        SceneNoteTypeID int NOT NULL,
        NoteTitle nvarchar(200) NULL,
        NoteText nvarchar(max) NOT NULL,
        SortOrder int NULL,
        IsPinned bit NOT NULL CONSTRAINT DF_SceneNotes_IsPinned DEFAULT 0,
        IsResolved bit NOT NULL CONSTRAINT DF_SceneNotes_IsResolved DEFAULT 0,
        CreatedDate datetime2 NOT NULL CONSTRAINT DF_SceneNotes_CreatedDate DEFAULT SYSUTCDATETIME(),
        UpdatedDate datetime2 NOT NULL CONSTRAINT DF_SceneNotes_UpdatedDate DEFAULT SYSUTCDATETIME(),
        CONSTRAINT FK_SceneNotes_Scenes FOREIGN KEY (SceneID) REFERENCES dbo.Scenes(SceneID),
        CONSTRAINT FK_SceneNotes_Types FOREIGN KEY (SceneNoteTypeID) REFERENCES dbo.SceneNoteTypes(SceneNoteTypeID)
    );
END;
GO

IF OBJECT_ID(N'dbo.ChapterGoals', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.ChapterGoals
    (
        ChapterGoalID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_ChapterGoals PRIMARY KEY,
        ChapterID int NOT NULL,
        GoalSummary nvarchar(max) NULL,
        EmotionalGoal nvarchar(max) NULL,
        ReaderTakeaway nvarchar(max) NULL,
        MustInclude nvarchar(max) NULL,
        RisksOrConcerns nvarchar(max) NULL,
        CreatedDate datetime2 NOT NULL CONSTRAINT DF_ChapterGoals_CreatedDate DEFAULT SYSUTCDATETIME(),
        UpdatedDate datetime2 NOT NULL CONSTRAINT DF_ChapterGoals_UpdatedDate DEFAULT SYSUTCDATETIME(),
        CONSTRAINT FK_ChapterGoals_Chapters FOREIGN KEY (ChapterID) REFERENCES dbo.Chapters(ChapterID),
        CONSTRAINT UQ_ChapterGoals_ChapterID UNIQUE (ChapterID)
    );
END;
GO

IF OBJECT_ID(N'dbo.SceneWorkflow', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.SceneWorkflow
    (
        SceneWorkflowID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_SceneWorkflow PRIMARY KEY,
        SceneID int NOT NULL,
        DraftStatus nvarchar(50) NOT NULL CONSTRAINT DF_SceneWorkflow_DraftStatus DEFAULT N'Planned',
        EstimatedWordCount int NULL,
        ActualWordCount int NULL,
        DraftedDate date NULL,
        LastWorkedOn date NULL,
        ReadyForDraft bit NOT NULL CONSTRAINT DF_SceneWorkflow_ReadyForDraft DEFAULT 0,
        ReadyForRevision bit NOT NULL CONSTRAINT DF_SceneWorkflow_ReadyForRevision DEFAULT 0,
        ReadyForPolish bit NOT NULL CONSTRAINT DF_SceneWorkflow_ReadyForPolish DEFAULT 0,
        IsBlocked bit NOT NULL CONSTRAINT DF_SceneWorkflow_IsBlocked DEFAULT 0,
        BlockedReason nvarchar(max) NULL,
        Priority int NULL,
        CreatedDate datetime2 NOT NULL CONSTRAINT DF_SceneWorkflow_CreatedDate DEFAULT SYSUTCDATETIME(),
        UpdatedDate datetime2 NOT NULL CONSTRAINT DF_SceneWorkflow_UpdatedDate DEFAULT SYSUTCDATETIME(),
        CONSTRAINT FK_SceneWorkflow_Scenes FOREIGN KEY (SceneID) REFERENCES dbo.Scenes(SceneID),
        CONSTRAINT UQ_SceneWorkflow_SceneID UNIQUE (SceneID),
        CONSTRAINT CK_SceneWorkflow_DraftStatus CHECK (DraftStatus IN (N'Planned', N'Ready To Draft', N'Drafting', N'Drafted', N'Revising', N'Polishing', N'Complete', N'On Hold')),
        CONSTRAINT CK_SceneWorkflow_Priority CHECK (Priority IS NULL OR Priority BETWEEN 1 AND 10),
        CONSTRAINT CK_SceneWorkflow_WordCounts CHECK ((EstimatedWordCount IS NULL OR EstimatedWordCount >= 0) AND (ActualWordCount IS NULL OR ActualWordCount >= 0))
    );
END;
GO

IF OBJECT_ID(N'dbo.SceneChecklistItems', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.SceneChecklistItems
    (
        SceneChecklistItemID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_SceneChecklistItems PRIMARY KEY,
        SceneID int NOT NULL,
        ItemText nvarchar(300) NOT NULL,
        IsCompleted bit NOT NULL CONSTRAINT DF_SceneChecklistItems_IsCompleted DEFAULT 0,
        SortOrder int NULL,
        CreatedDate datetime2 NOT NULL CONSTRAINT DF_SceneChecklistItems_CreatedDate DEFAULT SYSUTCDATETIME(),
        UpdatedDate datetime2 NOT NULL CONSTRAINT DF_SceneChecklistItems_UpdatedDate DEFAULT SYSUTCDATETIME(),
        CONSTRAINT FK_SceneChecklistItems_Scenes FOREIGN KEY (SceneID) REFERENCES dbo.Scenes(SceneID)
    );
END;
GO

IF OBJECT_ID(N'dbo.SceneAttachmentTypes', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.SceneAttachmentTypes
    (
        SceneAttachmentTypeID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_SceneAttachmentTypes PRIMARY KEY,
        TypeName nvarchar(100) NOT NULL,
        SortOrder int NOT NULL,
        IsActive bit NOT NULL CONSTRAINT DF_SceneAttachmentTypes_IsActive DEFAULT 1,
        CONSTRAINT UQ_SceneAttachmentTypes_TypeName UNIQUE (TypeName)
    );
END;
GO

IF OBJECT_ID(N'dbo.SceneAttachments', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.SceneAttachments
    (
        SceneAttachmentID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_SceneAttachments PRIMARY KEY,
        SceneID int NOT NULL,
        SceneAttachmentTypeID int NOT NULL,
        Title nvarchar(200) NOT NULL,
        FilePath nvarchar(500) NULL,
        ExternalUrl nvarchar(1000) NULL,
        Notes nvarchar(max) NULL,
        CreatedDate datetime2 NOT NULL CONSTRAINT DF_SceneAttachments_CreatedDate DEFAULT SYSUTCDATETIME(),
        UpdatedDate datetime2 NOT NULL CONSTRAINT DF_SceneAttachments_UpdatedDate DEFAULT SYSUTCDATETIME(),
        CONSTRAINT FK_SceneAttachments_Scenes FOREIGN KEY (SceneID) REFERENCES dbo.Scenes(SceneID),
        CONSTRAINT FK_SceneAttachments_Types FOREIGN KEY (SceneAttachmentTypeID) REFERENCES dbo.SceneAttachmentTypes(SceneAttachmentTypeID)
    );
END;
GO

MERGE dbo.SceneNoteTypes AS target
USING (VALUES
    (N'Drafting Note', 10), (N'Revision Note', 20), (N'Continuity Note', 30), (N'Research Note', 40),
    (N'Emotional Intent', 50), (N'Dialogue Note', 60), (N'Sensory Note', 70), (N'Timeline Note', 80),
    (N'Plot Reminder', 90), (N'Character Reminder', 100), (N'TODO', 110)
) AS source (TypeName, SortOrder)
ON target.TypeName = source.TypeName
WHEN MATCHED THEN UPDATE SET SortOrder = source.SortOrder, IsActive = 1
WHEN NOT MATCHED THEN INSERT (TypeName, SortOrder, IsActive) VALUES (source.TypeName, source.SortOrder, 1);
GO

MERGE dbo.SceneAttachmentTypes AS target
USING (VALUES
    (N'Image', 10), (N'Link', 20), (N'Research Reference', 30), (N'Historical Reference', 40),
    (N'Character Reference', 50), (N'Location Reference', 60), (N'Continuity Reference', 70), (N'General File', 80)
) AS source (TypeName, SortOrder)
ON target.TypeName = source.TypeName
WHEN MATCHED THEN UPDATE SET SortOrder = source.SortOrder, IsActive = 1
WHEN NOT MATCHED THEN INSERT (TypeName, SortOrder, IsActive) VALUES (source.TypeName, source.SortOrder, 1);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_SceneNotes_SceneID' AND object_id = OBJECT_ID(N'dbo.SceneNotes'))
    CREATE INDEX IX_SceneNotes_SceneID ON dbo.SceneNotes(SceneID, IsResolved, IsPinned, SortOrder);
GO
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_SceneChecklistItems_SceneID' AND object_id = OBJECT_ID(N'dbo.SceneChecklistItems'))
    CREATE INDEX IX_SceneChecklistItems_SceneID ON dbo.SceneChecklistItems(SceneID, IsCompleted, SortOrder);
GO
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_SceneAttachments_SceneID' AND object_id = OBJECT_ID(N'dbo.SceneAttachments'))
    CREATE INDEX IX_SceneAttachments_SceneID ON dbo.SceneAttachments(SceneID, SceneAttachmentTypeID);
GO

CREATE OR ALTER PROCEDURE dbo.SceneNoteType_List
AS
BEGIN
    SET NOCOUNT ON;
    SELECT SceneNoteTypeID, TypeName, SortOrder, IsActive
    FROM dbo.SceneNoteTypes
    WHERE IsActive = 1
    ORDER BY SortOrder, TypeName;
END;
GO

CREATE OR ALTER PROCEDURE dbo.SceneAttachmentType_List
AS
BEGIN
    SET NOCOUNT ON;
    SELECT SceneAttachmentTypeID, TypeName, SortOrder, IsActive
    FROM dbo.SceneAttachmentTypes
    WHERE IsActive = 1
    ORDER BY SortOrder, TypeName;
END;
GO

CREATE OR ALTER PROCEDURE dbo.SceneWorkflow_Get
    @SceneID int
AS
BEGIN
    SET NOCOUNT ON;

    IF NOT EXISTS (SELECT 1 FROM dbo.SceneWorkflow WHERE SceneID = @SceneID)
    BEGIN
        INSERT dbo.SceneWorkflow (SceneID)
        VALUES (@SceneID);
    END;

    IF NOT EXISTS (SELECT 1 FROM dbo.SceneChecklistItems WHERE SceneID = @SceneID)
    BEGIN
        INSERT dbo.SceneChecklistItems (SceneID, ItemText, SortOrder)
        VALUES
            (@SceneID, N'Purpose clear', 10),
            (@SceneID, N'Emotional payoff clear', 20),
            (@SceneID, N'Continuity checked', 30),
            (@SceneID, N'POV consistent', 40),
            (@SceneID, N'Dialogue pass complete', 50),
            (@SceneID, N'Sensory detail pass complete', 60),
            (@SceneID, N'Character motivation clear', 70),
            (@SceneID, N'Ending hook strong', 80);
    END;

    SELECT SceneWorkflowID, SceneID, DraftStatus, EstimatedWordCount, ActualWordCount, DraftedDate, LastWorkedOn,
        ReadyForDraft, ReadyForRevision, ReadyForPolish, IsBlocked, BlockedReason, Priority, CreatedDate, UpdatedDate
    FROM dbo.SceneWorkflow
    WHERE SceneID = @SceneID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.SceneWorkflow_Save
    @SceneID int,
    @DraftStatus nvarchar(50),
    @EstimatedWordCount int = NULL,
    @ActualWordCount int = NULL,
    @DraftedDate date = NULL,
    @LastWorkedOn date = NULL,
    @ReadyForDraft bit = 0,
    @ReadyForRevision bit = 0,
    @ReadyForPolish bit = 0,
    @IsBlocked bit = 0,
    @BlockedReason nvarchar(max) = NULL,
    @Priority int = NULL
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (SELECT 1 FROM dbo.SceneWorkflow WHERE SceneID = @SceneID)
    BEGIN
        UPDATE dbo.SceneWorkflow
        SET DraftStatus = @DraftStatus,
            EstimatedWordCount = @EstimatedWordCount,
            ActualWordCount = @ActualWordCount,
            DraftedDate = @DraftedDate,
            LastWorkedOn = @LastWorkedOn,
            ReadyForDraft = @ReadyForDraft,
            ReadyForRevision = @ReadyForRevision,
            ReadyForPolish = @ReadyForPolish,
            IsBlocked = @IsBlocked,
            BlockedReason = @BlockedReason,
            Priority = @Priority,
            UpdatedDate = SYSUTCDATETIME()
        WHERE SceneID = @SceneID;
    END
    ELSE
    BEGIN
        INSERT dbo.SceneWorkflow (SceneID, DraftStatus, EstimatedWordCount, ActualWordCount, DraftedDate, LastWorkedOn, ReadyForDraft, ReadyForRevision, ReadyForPolish, IsBlocked, BlockedReason, Priority)
        VALUES (@SceneID, @DraftStatus, @EstimatedWordCount, @ActualWordCount, @DraftedDate, @LastWorkedOn, @ReadyForDraft, @ReadyForRevision, @ReadyForPolish, @IsBlocked, @BlockedReason, @Priority);
    END;
END;
GO

CREATE OR ALTER PROCEDURE dbo.SceneNote_ListByScene
    @SceneID int
AS
BEGIN
    SET NOCOUNT ON;
    SELECT n.SceneNoteID, n.SceneID, n.SceneNoteTypeID, nt.TypeName, n.NoteTitle, n.NoteText, n.SortOrder,
        n.IsPinned, n.IsResolved, n.CreatedDate, n.UpdatedDate
    FROM dbo.SceneNotes n
    INNER JOIN dbo.SceneNoteTypes nt ON nt.SceneNoteTypeID = n.SceneNoteTypeID
    WHERE n.SceneID = @SceneID
    ORDER BY n.IsPinned DESC, n.IsResolved, ISNULL(n.SortOrder, 9999), n.UpdatedDate DESC;
END;
GO

CREATE OR ALTER PROCEDURE dbo.SceneNote_Save
    @SceneNoteID int = 0,
    @SceneID int,
    @SceneNoteTypeID int,
    @NoteTitle nvarchar(200) = NULL,
    @NoteText nvarchar(max),
    @SortOrder int = NULL,
    @IsPinned bit = 0,
    @IsResolved bit = 0
AS
BEGIN
    SET NOCOUNT ON;

    IF @SceneNoteID IS NULL OR @SceneNoteID = 0
    BEGIN
        INSERT dbo.SceneNotes (SceneID, SceneNoteTypeID, NoteTitle, NoteText, SortOrder, IsPinned, IsResolved)
        VALUES (@SceneID, @SceneNoteTypeID, @NoteTitle, @NoteText, @SortOrder, @IsPinned, @IsResolved);
        SET @SceneNoteID = CAST(SCOPE_IDENTITY() AS int);
    END
    ELSE
    BEGIN
        UPDATE dbo.SceneNotes
        SET SceneNoteTypeID = @SceneNoteTypeID,
            NoteTitle = @NoteTitle,
            NoteText = @NoteText,
            SortOrder = @SortOrder,
            IsPinned = @IsPinned,
            IsResolved = @IsResolved,
            UpdatedDate = SYSUTCDATETIME()
        WHERE SceneNoteID = @SceneNoteID;
    END;

    SELECT @SceneNoteID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.SceneNote_Delete
    @SceneNoteID int
AS
BEGIN
    SET NOCOUNT ON;
    DELETE dbo.SceneNotes WHERE SceneNoteID = @SceneNoteID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.SceneChecklist_ListByScene
    @SceneID int
AS
BEGIN
    SET NOCOUNT ON;
    SELECT SceneChecklistItemID, SceneID, ItemText, IsCompleted, SortOrder, CreatedDate, UpdatedDate
    FROM dbo.SceneChecklistItems
    WHERE SceneID = @SceneID
    ORDER BY IsCompleted, ISNULL(SortOrder, 9999), SceneChecklistItemID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.SceneChecklist_Save
    @SceneChecklistItemID int = 0,
    @SceneID int,
    @ItemText nvarchar(300),
    @IsCompleted bit = 0,
    @SortOrder int = NULL
AS
BEGIN
    SET NOCOUNT ON;
    IF @SceneChecklistItemID IS NULL OR @SceneChecklistItemID = 0
    BEGIN
        INSERT dbo.SceneChecklistItems (SceneID, ItemText, IsCompleted, SortOrder)
        VALUES (@SceneID, @ItemText, @IsCompleted, @SortOrder);
        SET @SceneChecklistItemID = CAST(SCOPE_IDENTITY() AS int);
    END
    ELSE
    BEGIN
        UPDATE dbo.SceneChecklistItems
        SET ItemText = @ItemText, IsCompleted = @IsCompleted, SortOrder = @SortOrder, UpdatedDate = SYSUTCDATETIME()
        WHERE SceneChecklistItemID = @SceneChecklistItemID;
    END;

    SELECT @SceneChecklistItemID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.SceneChecklist_Delete
    @SceneChecklistItemID int
AS
BEGIN
    SET NOCOUNT ON;
    DELETE dbo.SceneChecklistItems WHERE SceneChecklistItemID = @SceneChecklistItemID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.SceneAttachment_ListByScene
    @SceneID int
AS
BEGIN
    SET NOCOUNT ON;
    SELECT a.SceneAttachmentID, a.SceneID, a.SceneAttachmentTypeID, at.TypeName, a.Title, a.FilePath, a.ExternalUrl, a.Notes, a.CreatedDate, a.UpdatedDate
    FROM dbo.SceneAttachments a
    INNER JOIN dbo.SceneAttachmentTypes at ON at.SceneAttachmentTypeID = a.SceneAttachmentTypeID
    WHERE a.SceneID = @SceneID
    ORDER BY a.UpdatedDate DESC, a.SceneAttachmentID DESC;
END;
GO

CREATE OR ALTER PROCEDURE dbo.SceneAttachment_Save
    @SceneAttachmentID int = 0,
    @SceneID int,
    @SceneAttachmentTypeID int,
    @Title nvarchar(200),
    @FilePath nvarchar(500) = NULL,
    @ExternalUrl nvarchar(1000) = NULL,
    @Notes nvarchar(max) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    IF @SceneAttachmentID IS NULL OR @SceneAttachmentID = 0
    BEGIN
        INSERT dbo.SceneAttachments (SceneID, SceneAttachmentTypeID, Title, FilePath, ExternalUrl, Notes)
        VALUES (@SceneID, @SceneAttachmentTypeID, @Title, @FilePath, @ExternalUrl, @Notes);
        SET @SceneAttachmentID = CAST(SCOPE_IDENTITY() AS int);
    END
    ELSE
    BEGIN
        UPDATE dbo.SceneAttachments
        SET SceneAttachmentTypeID = @SceneAttachmentTypeID,
            Title = @Title,
            FilePath = @FilePath,
            ExternalUrl = @ExternalUrl,
            Notes = @Notes,
            UpdatedDate = SYSUTCDATETIME()
        WHERE SceneAttachmentID = @SceneAttachmentID;
    END;

    SELECT @SceneAttachmentID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.SceneAttachment_Delete
    @SceneAttachmentID int
AS
BEGIN
    SET NOCOUNT ON;
    DELETE dbo.SceneAttachments WHERE SceneAttachmentID = @SceneAttachmentID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.ChapterGoal_Get
    @ChapterID int
AS
BEGIN
    SET NOCOUNT ON;
    IF NOT EXISTS (SELECT 1 FROM dbo.ChapterGoals WHERE ChapterID = @ChapterID)
    BEGIN
        INSERT dbo.ChapterGoals (ChapterID) VALUES (@ChapterID);
    END;

    SELECT ChapterGoalID, ChapterID, GoalSummary, EmotionalGoal, ReaderTakeaway, MustInclude, RisksOrConcerns, CreatedDate, UpdatedDate
    FROM dbo.ChapterGoals
    WHERE ChapterID = @ChapterID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.ChapterGoal_Save
    @ChapterID int,
    @GoalSummary nvarchar(max) = NULL,
    @EmotionalGoal nvarchar(max) = NULL,
    @ReaderTakeaway nvarchar(max) = NULL,
    @MustInclude nvarchar(max) = NULL,
    @RisksOrConcerns nvarchar(max) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    IF EXISTS (SELECT 1 FROM dbo.ChapterGoals WHERE ChapterID = @ChapterID)
    BEGIN
        UPDATE dbo.ChapterGoals
        SET GoalSummary = @GoalSummary,
            EmotionalGoal = @EmotionalGoal,
            ReaderTakeaway = @ReaderTakeaway,
            MustInclude = @MustInclude,
            RisksOrConcerns = @RisksOrConcerns,
            UpdatedDate = SYSUTCDATETIME()
        WHERE ChapterID = @ChapterID;
    END
    ELSE
    BEGIN
        INSERT dbo.ChapterGoals (ChapterID, GoalSummary, EmotionalGoal, ReaderTakeaway, MustInclude, RisksOrConcerns)
        VALUES (@ChapterID, @GoalSummary, @EmotionalGoal, @ReaderTakeaway, @MustInclude, @RisksOrConcerns);
    END;
END;
GO

CREATE OR ALTER PROCEDURE dbo.ChapterWorkflow_Summary
    @ChapterID int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT s.SceneID, s.SceneNumber, s.SceneTitle,
        COALESCE(sw.DraftStatus, N'Planned') AS DraftStatus,
        sw.Priority,
        COALESCE(sw.ReadyForDraft, 0) AS ReadyForDraft,
        COALESCE(sw.ReadyForRevision, 0) AS ReadyForRevision,
        COALESCE(sw.ReadyForPolish, 0) AS ReadyForPolish,
        COALESCE(sw.IsBlocked, 0) AS IsBlocked,
        sw.BlockedReason,
        ISNULL(warnings.WarningCount, 0) AS WarningCount,
        ISNULL(checklist.RemainingChecklistCount, 0) AS RemainingChecklistCount,
        ISNULL(notes.UnresolvedNoteCount, 0) AS UnresolvedNoteCount
    FROM dbo.Scenes s
    LEFT JOIN dbo.SceneWorkflow sw ON sw.SceneID = s.SceneID
    OUTER APPLY
    (
        SELECT COUNT(*) AS WarningCount
        FROM dbo.ContinuityWarnings cw
        WHERE cw.SceneID = s.SceneID AND cw.IsDismissed = 0
    ) warnings
    OUTER APPLY
    (
        SELECT COUNT(*) AS RemainingChecklistCount
        FROM dbo.SceneChecklistItems sci
        WHERE sci.SceneID = s.SceneID AND sci.IsCompleted = 0
    ) checklist
    OUTER APPLY
    (
        SELECT COUNT(*) AS UnresolvedNoteCount
        FROM dbo.SceneNotes sn
        WHERE sn.SceneID = s.SceneID AND sn.IsResolved = 0
    ) notes
    WHERE s.ChapterID = @ChapterID AND s.IsArchived = 0
    ORDER BY s.SortOrder, s.SceneID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.WriterDashboard_Scenes
    @ProjectID int = NULL,
    @Queue nvarchar(50)
AS
BEGIN
    SET NOCOUNT ON;

    WITH SceneBase AS
    (
        SELECT p.ProjectID, p.ProjectName, b.BookID, b.BookTitle, c.ChapterID, c.ChapterNumber, c.ChapterTitle,
            s.SceneID, s.SceneNumber, s.SceneTitle, s.Summary,
            COALESCE(sw.DraftStatus, N'Planned') AS DraftStatus,
            sw.Priority,
            COALESCE(sw.IsBlocked, 0) AS IsBlocked,
            sw.BlockedReason,
            COALESCE(sw.ReadyForDraft, 0) AS ReadyForDraft,
            COALESCE(sw.ReadyForRevision, 0) AS ReadyForRevision,
            COALESCE(sw.ReadyForPolish, 0) AS ReadyForPolish,
            b.SortOrder AS BookSortOrder,
            c.SortOrder AS ChapterSortOrder,
            s.SortOrder AS SceneSortOrder
        FROM dbo.Projects p
        INNER JOIN dbo.Books b ON b.ProjectID = p.ProjectID AND b.IsArchived = 0
        INNER JOIN dbo.Chapters c ON c.BookID = b.BookID AND c.IsArchived = 0
        INNER JOIN dbo.Scenes s ON s.ChapterID = c.ChapterID AND s.IsArchived = 0
        LEFT JOIN dbo.SceneWorkflow sw ON sw.SceneID = s.SceneID
        WHERE p.IsArchived = 0 AND (@ProjectID IS NULL OR p.ProjectID = @ProjectID)
    )
    SELECT TOP (40) sb.ProjectID, sb.ProjectName, sb.BookID, sb.BookTitle, sb.ChapterID, sb.ChapterNumber, sb.ChapterTitle,
        sb.SceneID, sb.SceneNumber, sb.SceneTitle, sb.Summary, sb.DraftStatus, sb.Priority, sb.IsBlocked, sb.BlockedReason,
        ISNULL(warnings.WarningCount, 0) AS WarningCount,
        ISNULL(checklist.RemainingChecklistCount, 0) AS RemainingChecklistCount,
        ISNULL(notes.UnresolvedNoteCount, 0) AS UnresolvedNoteCount,
        threads.ImportantThreads,
        pinned.PinnedNotes
    FROM SceneBase sb
    OUTER APPLY
    (
        SELECT COUNT(*) AS WarningCount
        FROM dbo.ContinuityWarnings cw
        WHERE cw.SceneID = sb.SceneID AND cw.IsDismissed = 0
    ) warnings
    OUTER APPLY
    (
        SELECT COUNT(*) AS RemainingChecklistCount
        FROM dbo.SceneChecklistItems sci
        WHERE sci.SceneID = sb.SceneID AND sci.IsCompleted = 0
    ) checklist
    OUTER APPLY
    (
        SELECT COUNT(*) AS UnresolvedNoteCount
        FROM dbo.SceneNotes sn
        INNER JOIN dbo.SceneNoteTypes snt ON snt.SceneNoteTypeID = sn.SceneNoteTypeID
        WHERE sn.SceneID = sb.SceneID AND sn.IsResolved = 0
            AND (@Queue <> N'NeedsAttention' OR snt.TypeName IN (N'TODO', N'Continuity Note'))
    ) notes
    OUTER APPLY
    (
        SELECT STRING_AGG(CONVERT(nvarchar(max), pt.ThreadTitle), N', ') AS ImportantThreads
        FROM
        (
            SELECT DISTINCT TOP (5) pt.ThreadTitle, pt.Importance
            FROM dbo.ThreadEvents te
            INNER JOIN dbo.PlotThreads pt ON pt.PlotThreadID = te.PlotThreadID
            WHERE te.SceneID = sb.SceneID
            ORDER BY pt.Importance DESC, pt.ThreadTitle
        ) pt
    ) threads
    OUTER APPLY
    (
        SELECT STRING_AGG(CONVERT(nvarchar(max), COALESCE(sn.NoteTitle, sn.NoteText)), N' | ') AS PinnedNotes
        FROM dbo.SceneNotes sn
        WHERE sn.SceneID = sb.SceneID AND sn.IsPinned = 1 AND sn.IsResolved = 0
    ) pinned
    WHERE
        (@Queue = N'ContinueWriting' AND (sb.DraftStatus IN (N'Ready To Draft', N'Drafting') OR sb.ReadyForDraft = 1 OR ISNULL(sb.Priority, 0) >= 8))
        OR (@Queue = N'NeedsAttention' AND (sb.IsBlocked = 1 OR ISNULL(warnings.WarningCount, 0) > 0 OR ISNULL(notes.UnresolvedNoteCount, 0) > 0))
        OR (@Queue = N'RevisionQueue' AND (sb.DraftStatus IN (N'Drafted', N'Revising') OR sb.ReadyForRevision = 1))
        OR (@Queue = N'PolishingQueue' AND (sb.DraftStatus = N'Polishing' OR sb.ReadyForPolish = 1))
    ORDER BY ISNULL(sb.Priority, 0) DESC, sb.BookSortOrder, sb.ChapterSortOrder, sb.SceneSortOrder, sb.SceneID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.WriterDashboard_Reminders
    @ProjectID int = NULL
AS
BEGIN
    SET NOCOUNT ON;

    SELECT TOP (60) N'Chapter Goal' AS Category, N'Info' AS SeverityName,
        CONCAT(N'Chapter ', c.ChapterNumber, N' needs intent') AS Title,
        N'Goal summary is empty.' AS Detail,
        CAST(NULL AS int) AS SceneID, c.ChapterID AS EntityID, N'Chapter' AS EntityType
    FROM dbo.Chapters c
    INNER JOIN dbo.Books b ON b.BookID = c.BookID AND b.IsArchived = 0
    LEFT JOIN dbo.ChapterGoals cg ON cg.ChapterID = c.ChapterID
    WHERE c.IsArchived = 0 AND (@ProjectID IS NULL OR b.ProjectID = @ProjectID)
        AND NULLIF(LTRIM(RTRIM(cg.GoalSummary)), N'') IS NULL
    UNION ALL
    SELECT N'Plot Thread', N'Info', pt.ThreadTitle,
        CONCAT(N'Unresolved thread in ', pl.PlotLineName, N'.'),
        CAST(NULL AS int), pt.PlotThreadID, N'PlotThread'
    FROM dbo.PlotThreads pt
    INNER JOIN dbo.ThreadStatuses ts ON ts.ThreadStatusID = pt.ThreadStatusID
    INNER JOIN dbo.PlotLines pl ON pl.PlotLineID = pt.PlotLineID AND pl.IsArchived = 0
    WHERE pt.IsArchived = 0 AND ts.IsOpenStatus = 1 AND (@ProjectID IS NULL OR pl.ProjectID = @ProjectID)
    UNION ALL
    SELECT N'Writer Note', N'Warning', COALESCE(sn.NoteTitle, s.SceneTitle),
        sn.NoteText, s.SceneID, sn.SceneNoteID, N'SceneNote'
    FROM dbo.SceneNotes sn
    INNER JOIN dbo.SceneNoteTypes snt ON snt.SceneNoteTypeID = sn.SceneNoteTypeID
    INNER JOIN dbo.Scenes s ON s.SceneID = sn.SceneID AND s.IsArchived = 0
    INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID AND c.IsArchived = 0
    INNER JOIN dbo.Books b ON b.BookID = c.BookID AND b.IsArchived = 0
    WHERE sn.IsResolved = 0 AND snt.TypeName IN (N'TODO', N'Continuity Note', N'Revision Note') AND (@ProjectID IS NULL OR b.ProjectID = @ProjectID);
END;
GO

CREATE OR ALTER PROCEDURE dbo.StoryBible_Search
    @ProjectID int,
    @Query nvarchar(200) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    IF NULLIF(LTRIM(RTRIM(@Query)), N'') IS NULL
        RETURN;

    DECLARE @Term nvarchar(204) = N'%' + LTRIM(RTRIM(@Query)) + N'%';

    SELECT N'Character' AS ResultType, ch.CharacterID AS EntityID, ch.CharacterName AS Title,
        ch.DefaultDescription AS Detail, CAST(NULL AS int) AS SceneID
    FROM dbo.Characters ch
    WHERE ch.ProjectID = @ProjectID AND ch.IsArchived = 0
        AND (ch.CharacterName LIKE @Term OR ch.ShortName LIKE @Term OR ch.DefaultDescription LIKE @Term)
    UNION ALL
    SELECT N'Scene', s.SceneID, CONCAT(N'Scene ', s.SceneNumber, N': ', s.SceneTitle),
        s.Summary, s.SceneID
    FROM dbo.Scenes s
    INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID AND c.IsArchived = 0
    INNER JOIN dbo.Books b ON b.BookID = c.BookID AND b.ProjectID = @ProjectID AND b.IsArchived = 0
    WHERE s.IsArchived = 0 AND (s.SceneTitle LIKE @Term OR s.Summary LIKE @Term OR s.ScenePurposeNotes LIKE @Term OR s.SceneOutcomeNotes LIKE @Term)
    UNION ALL
    SELECT N'Plot Thread', pt.PlotThreadID, pt.ThreadTitle, pt.Summary, CAST(NULL AS int)
    FROM dbo.PlotThreads pt
    INNER JOIN dbo.PlotLines pl ON pl.PlotLineID = pt.PlotLineID AND pl.ProjectID = @ProjectID AND pl.IsArchived = 0
    WHERE pt.IsArchived = 0 AND (pt.ThreadTitle LIKE @Term OR pt.Summary LIKE @Term)
    UNION ALL
    SELECT N'Story Asset', sa.StoryAssetID, sa.AssetName, sa.Description, CAST(NULL AS int)
    FROM dbo.StoryAssets sa
    WHERE sa.ProjectID = @ProjectID AND sa.IsArchived = 0 AND (sa.AssetName LIKE @Term OR sa.Description LIKE @Term)
    UNION ALL
    SELECT N'Location', lp.LocationID, lp.LocationPath, lp.Description, CAST(NULL AS int)
    FROM dbo.LocationPaths lp
    WHERE lp.ProjectID = @ProjectID AND (lp.LocationName LIKE @Term OR lp.LocationPath LIKE @Term OR lp.Description LIKE @Term)
    UNION ALL
    SELECT N'Relationship', cr.CharacterRelationshipID, CONCAT(ca.CharacterName, N' - ', cb.CharacterName),
        CONCAT(rt.TypeName, N'. ', ISNULL(cr.Notes, N'')), CAST(NULL AS int)
    FROM dbo.CharacterRelationships cr
    INNER JOIN dbo.Characters ca ON ca.CharacterID = cr.CharacterAID
    INNER JOIN dbo.Characters cb ON cb.CharacterID = cr.CharacterBID
    INNER JOIN dbo.RelationshipTypes rt ON rt.RelationshipTypeID = cr.RelationshipTypeID
    WHERE cr.ProjectID = @ProjectID AND cr.IsArchived = 0
        AND (ca.CharacterName LIKE @Term OR cb.CharacterName LIKE @Term OR rt.TypeName LIKE @Term OR cr.Notes LIKE @Term)
    UNION ALL
    SELECT N'Warning', cw.ContinuityWarningID, cw.Message, cw.Details, cw.SceneID
    FROM dbo.ContinuityWarnings cw
    WHERE cw.ProjectID = @ProjectID AND cw.IsDismissed = 0
        AND (cw.Message LIKE @Term OR cw.Details LIKE @Term OR cw.EntityType LIKE @Term)
    UNION ALL
    SELECT N'Scene Note', sn.SceneNoteID, COALESCE(sn.NoteTitle, CONCAT(snt.TypeName, N' for ', s.SceneTitle)),
        sn.NoteText, sn.SceneID
    FROM dbo.SceneNotes sn
    INNER JOIN dbo.SceneNoteTypes snt ON snt.SceneNoteTypeID = sn.SceneNoteTypeID
    INNER JOIN dbo.Scenes s ON s.SceneID = sn.SceneID AND s.IsArchived = 0
    INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID AND c.IsArchived = 0
    INNER JOIN dbo.Books b ON b.BookID = c.BookID AND b.ProjectID = @ProjectID AND b.IsArchived = 0
    WHERE sn.NoteTitle LIKE @Term OR sn.NoteText LIKE @Term OR snt.TypeName LIKE @Term
    UNION ALL
    SELECT N'Chapter Goal', cg.ChapterGoalID, CONCAT(N'Chapter ', c.ChapterNumber, N': ', c.ChapterTitle),
        CONCAT(ISNULL(cg.GoalSummary, N''), N' ', ISNULL(cg.EmotionalGoal, N''), N' ', ISNULL(cg.ReaderTakeaway, N''), N' ', ISNULL(cg.MustInclude, N''), N' ', ISNULL(cg.RisksOrConcerns, N'')),
        CAST(NULL AS int)
    FROM dbo.ChapterGoals cg
    INNER JOIN dbo.Chapters c ON c.ChapterID = cg.ChapterID AND c.IsArchived = 0
    INNER JOIN dbo.Books b ON b.BookID = c.BookID AND b.ProjectID = @ProjectID AND b.IsArchived = 0
    WHERE cg.GoalSummary LIKE @Term OR cg.EmotionalGoal LIKE @Term OR cg.ReaderTakeaway LIKE @Term OR cg.MustInclude LIKE @Term OR cg.RisksOrConcerns LIKE @Term
    UNION ALL
    SELECT N'Attachment', sa.SceneAttachmentID, sa.Title,
        CONCAT(ISNULL(sa.ExternalUrl, N''), N' ', ISNULL(sa.FilePath, N''), N' ', ISNULL(sa.Notes, N'')), sa.SceneID
    FROM dbo.SceneAttachments sa
    INNER JOIN dbo.Scenes s ON s.SceneID = sa.SceneID AND s.IsArchived = 0
    INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID AND c.IsArchived = 0
    INNER JOIN dbo.Books b ON b.BookID = c.BookID AND b.ProjectID = @ProjectID AND b.IsArchived = 0
    WHERE sa.Title LIKE @Term OR sa.ExternalUrl LIKE @Term OR sa.FilePath LIKE @Term OR sa.Notes LIKE @Term
    UNION ALL
    SELECT N'Workflow', sw.SceneWorkflowID, CONCAT(N'Workflow: ', s.SceneTitle),
        sw.BlockedReason, s.SceneID
    FROM dbo.SceneWorkflow sw
    INNER JOIN dbo.Scenes s ON s.SceneID = sw.SceneID AND s.IsArchived = 0
    INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID AND c.IsArchived = 0
    INNER JOIN dbo.Books b ON b.BookID = c.BookID AND b.ProjectID = @ProjectID AND b.IsArchived = 0
    WHERE sw.BlockedReason LIKE @Term OR sw.DraftStatus LIKE @Term
    ORDER BY ResultType, Title;
END;
GO