IF COL_LENGTH(N'dbo.UserOnboardingState', N'ProjectID') IS NULL
    ALTER TABLE dbo.UserOnboardingState ADD ProjectID int NULL;
GO

IF COL_LENGTH(N'dbo.UserOnboardingState', N'BookID') IS NULL
    ALTER TABLE dbo.UserOnboardingState ADD BookID int NULL;
GO

IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = N'FK_UserOnboardingState_Projects')
    ALTER TABLE dbo.UserOnboardingState ADD CONSTRAINT FK_UserOnboardingState_Projects FOREIGN KEY (ProjectID) REFERENCES dbo.Projects(ProjectID);
GO

IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = N'FK_UserOnboardingState_Books')
    ALTER TABLE dbo.UserOnboardingState ADD CONSTRAINT FK_UserOnboardingState_Books FOREIGN KEY (BookID) REFERENCES dbo.Books(BookID);
GO

IF EXISTS (SELECT 1 FROM sys.check_constraints WHERE name = N'CK_UserOnboardingState_CurrentStep' AND parent_object_id = OBJECT_ID(N'dbo.UserOnboardingState'))
    ALTER TABLE dbo.UserOnboardingState DROP CONSTRAINT CK_UserOnboardingState_CurrentStep;
GO

ALTER TABLE dbo.UserOnboardingState ADD CONSTRAINT CK_UserOnboardingState_CurrentStep
CHECK (CurrentStep IN (N'Welcome', N'WritingJourney', N'WritingSoftware', N'Project', N'Book', N'NextPathPreview', N'Complete'));
GO

UPDATE dbo.UserOnboardingState
SET CurrentStep = N'Project',
    OnboardingCompletedUtc = NULL,
    UpdatedUtc = SYSUTCDATETIME()
WHERE OnboardingCompletedUtc IS NOT NULL
    AND (ProjectID IS NULL OR BookID IS NULL);
GO

CREATE OR ALTER PROCEDURE dbo.UserOnboarding_Get
    @UserID int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT UserOnboardingStateID, UserID, OnboardingStartedUtc, OnboardingCompletedUtc,
        CurrentStep, WritingJourney, WritingSoftware, UsesWordCompanion, ProjectID, BookID, CreatedUtc, UpdatedUtc
    FROM dbo.UserOnboardingState
    WHERE UserID = @UserID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.UserOnboarding_GetOrCreate
    @UserID int
AS
BEGIN
    SET NOCOUNT ON;

    IF NOT EXISTS (SELECT 1 FROM dbo.UserOnboardingState WHERE UserID = @UserID)
    BEGIN
        INSERT INTO dbo.UserOnboardingState (UserID)
        VALUES (@UserID);
    END;

    EXEC dbo.UserOnboarding_Get @UserID = @UserID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.UserOnboarding_SetCurrentStep
    @UserID int,
    @CurrentStep nvarchar(50)
AS
BEGIN
    SET NOCOUNT ON;

    IF NOT EXISTS (SELECT 1 FROM dbo.UserOnboardingState WHERE UserID = @UserID)
    BEGIN
        INSERT INTO dbo.UserOnboardingState (UserID)
        VALUES (@UserID);
    END;

    UPDATE dbo.UserOnboardingState
    SET CurrentStep = @CurrentStep,
        OnboardingCompletedUtc = CASE WHEN @CurrentStep = N'Complete' THEN COALESCE(OnboardingCompletedUtc, SYSUTCDATETIME()) ELSE NULL END,
        UpdatedUtc = SYSUTCDATETIME()
    WHERE UserID = @UserID;

    EXEC dbo.UserOnboarding_Get @UserID = @UserID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.UserOnboarding_SaveWritingJourney
    @UserID int,
    @WritingJourney nvarchar(50)
AS
BEGIN
    SET NOCOUNT ON;

    IF NOT EXISTS (SELECT 1 FROM dbo.UserOnboardingState WHERE UserID = @UserID)
    BEGIN
        INSERT INTO dbo.UserOnboardingState (UserID)
        VALUES (@UserID);
    END;

    UPDATE dbo.UserOnboardingState
    SET WritingJourney = @WritingJourney,
        CurrentStep = N'WritingSoftware',
        OnboardingCompletedUtc = NULL,
        UpdatedUtc = SYSUTCDATETIME()
    WHERE UserID = @UserID;

    EXEC dbo.UserOnboarding_Get @UserID = @UserID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.UserOnboarding_SaveWritingSoftware
    @UserID int,
    @WritingSoftware nvarchar(50)
AS
BEGIN
    SET NOCOUNT ON;

    IF NOT EXISTS (SELECT 1 FROM dbo.UserOnboardingState WHERE UserID = @UserID)
    BEGIN
        INSERT INTO dbo.UserOnboardingState (UserID)
        VALUES (@UserID);
    END;

    UPDATE dbo.UserOnboardingState
    SET WritingSoftware = @WritingSoftware,
        UsesWordCompanion = CASE WHEN @WritingSoftware = N'MicrosoftWord' THEN 1 ELSE 0 END,
        CurrentStep = N'Project',
        OnboardingCompletedUtc = NULL,
        UpdatedUtc = SYSUTCDATETIME()
    WHERE UserID = @UserID;

    EXEC dbo.UserOnboarding_Get @UserID = @UserID;
END;
GO

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

    IF NOT EXISTS (SELECT 1 FROM dbo.UserOnboardingState WHERE UserID = @UserID)
    BEGIN
        INSERT INTO dbo.UserOnboardingState (UserID)
        VALUES (@UserID);
    END;

    UPDATE dbo.UserOnboardingState
    SET ProjectID = @ProjectID,
        BookID = CASE WHEN ProjectID = @ProjectID THEN BookID ELSE NULL END,
        CurrentStep = N'Book',
        OnboardingCompletedUtc = NULL,
        UpdatedUtc = SYSUTCDATETIME()
    WHERE UserID = @UserID;

    EXEC dbo.UserOnboarding_Get @UserID = @UserID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.UserOnboarding_SaveBook
    @UserID int,
    @BookID int
AS
BEGIN
    SET NOCOUNT ON;

    IF NOT EXISTS (SELECT 1 FROM dbo.UserOnboardingState WHERE UserID = @UserID)
    BEGIN
        INSERT INTO dbo.UserOnboardingState (UserID)
        VALUES (@UserID);
    END;

    UPDATE dbo.UserOnboardingState
    SET BookID = @BookID,
        CurrentStep = N'NextPathPreview',
        OnboardingCompletedUtc = NULL,
        UpdatedUtc = SYSUTCDATETIME()
    WHERE UserID = @UserID;

    EXEC dbo.UserOnboarding_Get @UserID = @UserID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.UserOnboarding_MarkCompleted
    @UserID int
AS
BEGIN
    SET NOCOUNT ON;

    IF NOT EXISTS (SELECT 1 FROM dbo.UserOnboardingState WHERE UserID = @UserID)
    BEGIN
        INSERT INTO dbo.UserOnboardingState (UserID)
        VALUES (@UserID);
    END;

    UPDATE dbo.UserOnboardingState
    SET CurrentStep = N'Complete',
        OnboardingCompletedUtc = COALESCE(OnboardingCompletedUtc, SYSUTCDATETIME()),
        UpdatedUtc = SYSUTCDATETIME()
    WHERE UserID = @UserID;

    EXEC dbo.UserOnboarding_Get @UserID = @UserID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.UserOnboarding_ShouldShowWordCompanionHeader
    @UserID int
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @HasWordPreference bit = 0;
    DECLARE @HasCompanionUsage bit = 0;

    IF EXISTS
    (
        SELECT 1
        FROM dbo.UserOnboardingState
        WHERE UserID = @UserID
            AND WritingSoftware = N'MicrosoftWord'
            AND UsesWordCompanion = 1
    )
        SET @HasWordPreference = 1;

    IF OBJECT_ID(N'dbo.ManuscriptDocuments', N'U') IS NOT NULL
        AND EXISTS
        (
            SELECT 1
            FROM dbo.ManuscriptDocuments md
            LEFT JOIN dbo.ProjectUserAccess pua ON pua.ProjectID = md.ProjectID
                AND pua.UserID = @UserID
                AND pua.IsActive = 1
            WHERE md.IsActive = 1
                AND (md.CreatedByUserID = @UserID OR pua.ProjectUserAccessID IS NOT NULL)
        )
        SET @HasCompanionUsage = 1;

    SELECT CAST(CASE WHEN @HasWordPreference = 1 OR @HasCompanionUsage = 1 THEN 1 ELSE 0 END AS bit);
END;
GO