PlotDirector/PlotLine/Sql/050_Phase5I_StripePaymentIntegration.sql

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.SubscriptionLevel') AND name = N'StripeMonthlyPriceId')
    ALTER TABLE dbo.SubscriptionLevel ADD StripeMonthlyPriceId nvarchar(100) NULL;
GO

IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.SubscriptionLevel') AND name = N'StripeAnnualPriceId')
    ALTER TABLE dbo.SubscriptionLevel ADD StripeAnnualPriceId nvarchar(100) NULL;
GO

IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.SubscriptionLevel') AND name = N'RequiresStripeSubscription')
    ALTER TABLE dbo.SubscriptionLevel ADD RequiresStripeSubscription bit NOT NULL CONSTRAINT DF_SubscriptionLevel_RequiresStripeSubscription DEFAULT 0;
GO

IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.UserSubscription') AND name = N'StripeCustomerId')
    ALTER TABLE dbo.UserSubscription ADD StripeCustomerId nvarchar(100) NULL;
GO

IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.UserSubscription') AND name = N'StripeSubscriptionId')
    ALTER TABLE dbo.UserSubscription ADD StripeSubscriptionId nvarchar(100) NULL;
GO

IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.UserSubscription') AND name = N'StripePriceId')
    ALTER TABLE dbo.UserSubscription ADD StripePriceId nvarchar(100) NULL;
GO

IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.UserSubscription') AND name = N'BillingInterval')
    ALTER TABLE dbo.UserSubscription ADD BillingInterval nvarchar(20) NULL;
GO

IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.UserSubscription') AND name = N'StripeStatus')
    ALTER TABLE dbo.UserSubscription ADD StripeStatus nvarchar(50) NULL;
GO

IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.UserSubscription') AND name = N'CurrentPeriodStartUTC')
    ALTER TABLE dbo.UserSubscription ADD CurrentPeriodStartUTC datetime2 NULL;
GO

IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.UserSubscription') AND name = N'CurrentPeriodEndUTC')
    ALTER TABLE dbo.UserSubscription ADD CurrentPeriodEndUTC datetime2 NULL;
GO

IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.UserSubscription') AND name = N'CancelAtPeriodEnd')
    ALTER TABLE dbo.UserSubscription ADD CancelAtPeriodEnd bit NOT NULL CONSTRAINT DF_UserSubscription_CancelAtPeriodEnd DEFAULT 0;
GO

IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.UserSubscription') AND name = N'CancelledDateUTC')
    ALTER TABLE dbo.UserSubscription ADD CancelledDateUTC datetime2 NULL;
GO

IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.UserSubscription') AND name = N'LastPaymentFailureDateUTC')
    ALTER TABLE dbo.UserSubscription ADD LastPaymentFailureDateUTC datetime2 NULL;
GO

IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.UserSubscription') AND name = N'LastStripeEventId')
    ALTER TABLE dbo.UserSubscription ADD LastStripeEventId nvarchar(100) NULL;
GO

IF OBJECT_ID(N'dbo.StripeWebhookEvent', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.StripeWebhookEvent
    (
        StripeWebhookEventID bigint IDENTITY(1,1) NOT NULL CONSTRAINT PK_StripeWebhookEvent PRIMARY KEY,
        StripeEventId nvarchar(100) NOT NULL,
        EventType nvarchar(100) NOT NULL,
        ReceivedDateUTC datetime2 NOT NULL CONSTRAINT DF_StripeWebhookEvent_ReceivedDateUTC DEFAULT SYSUTCDATETIME(),
        ProcessedDateUTC datetime2 NULL,
        ProcessingStatus nvarchar(50) NOT NULL CONSTRAINT DF_StripeWebhookEvent_ProcessingStatus DEFAULT N'Received',
        ErrorMessage nvarchar(1000) NULL,
        CONSTRAINT UX_StripeWebhookEvent_EventId UNIQUE (StripeEventId)
    );
END;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_UserSubscription_StripeSubscriptionId' AND object_id = OBJECT_ID(N'dbo.UserSubscription'))
    CREATE INDEX IX_UserSubscription_StripeSubscriptionId ON dbo.UserSubscription(StripeSubscriptionId) WHERE StripeSubscriptionId IS NOT NULL;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_UserSubscription_StripeCustomerId' AND object_id = OBJECT_ID(N'dbo.UserSubscription'))
    CREATE INDEX IX_UserSubscription_StripeCustomerId ON dbo.UserSubscription(StripeCustomerId) WHERE StripeCustomerId IS NOT NULL;
GO

UPDATE dbo.SubscriptionLevel
SET RequiresStripeSubscription = CASE WHEN Name = N'DraftDesk' THEN 0 ELSE 1 END,
    StripeMonthlyPriceId = CASE Name
        WHEN N'WritingRoom' THEN N'price_1TffeZIH8aCkpHgj4AlC7Atv'
        WHEN N'StoryForge' THEN N'price_1TffdJIH8aCkpHgjvQfV7heX'
        WHEN N'AuthorStudio' THEN N'price_1TffbaIH8aCkpHgjKFLoaHMJ'
        ELSE NULL
    END,
    StripeAnnualPriceId = CASE Name
        WHEN N'WritingRoom' THEN N'price_1Tffe8IH8aCkpHgj25QRMu4t'
        WHEN N'StoryForge' THEN N'price_1TffccIH8aCkpHgjXlSwB4A1'
        WHEN N'AuthorStudio' THEN N'price_1TffaRIH8aCkpHgjIoQqgwBH'
        ELSE NULL
    END,
    ModifiedDateUTC = SYSUTCDATETIME();
GO

CREATE OR ALTER PROCEDURE dbo.SubscriptionLevel_ListActive
AS
BEGIN
    SET NOCOUNT ON;

    SELECT SubscriptionLevelID, Name, DisplayName, Description, MaxBooks, MaxChaptersPerBook,
        MaxScenesPerBook, MaxCharactersPerProject, MaxStorageMB, MaxCollaborators, IsActive,
        SortOrder, CreatedDateUTC, ModifiedDateUTC, StripeMonthlyPriceId, StripeAnnualPriceId,
        RequiresStripeSubscription
    FROM dbo.SubscriptionLevel
    WHERE IsActive = 1
    ORDER BY SortOrder, DisplayName;
END;
GO

CREATE OR ALTER PROCEDURE dbo.SubscriptionLevel_Get
    @SubscriptionLevelID int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT SubscriptionLevelID, Name, DisplayName, Description, MaxBooks, MaxChaptersPerBook,
        MaxScenesPerBook, MaxCharactersPerProject, MaxStorageMB, MaxCollaborators, IsActive,
        SortOrder, CreatedDateUTC, ModifiedDateUTC, StripeMonthlyPriceId, StripeAnnualPriceId,
        RequiresStripeSubscription
    FROM dbo.SubscriptionLevel
    WHERE SubscriptionLevelID = @SubscriptionLevelID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.SubscriptionLevel_GetByStripePrice
    @StripePriceId nvarchar(100)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT SubscriptionLevelID, Name, DisplayName, Description, MaxBooks, MaxChaptersPerBook,
        MaxScenesPerBook, MaxCharactersPerProject, MaxStorageMB, MaxCollaborators, IsActive,
        SortOrder, CreatedDateUTC, ModifiedDateUTC, StripeMonthlyPriceId, StripeAnnualPriceId,
        RequiresStripeSubscription,
        CASE
            WHEN StripeMonthlyPriceId = @StripePriceId THEN N'Monthly'
            WHEN StripeAnnualPriceId = @StripePriceId THEN N'Annual'
            ELSE NULL
        END AS BillingInterval
    FROM dbo.SubscriptionLevel
    WHERE IsActive = 1
        AND RequiresStripeSubscription = 1
        AND (StripeMonthlyPriceId = @StripePriceId OR StripeAnnualPriceId = @StripePriceId);
END;
GO

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

    SELECT us.UserSubscriptionID, us.UserID, us.SubscriptionLevelID, us.StartDateUTC, us.EndDateUTC,
        us.IsActive, us.IsTrial, us.TrialExpiryDateUTC, us.CreatedDateUTC, us.ModifiedDateUTC,
        us.StripeCustomerId, us.StripeSubscriptionId, us.StripePriceId, us.BillingInterval, us.StripeStatus,
        us.CurrentPeriodStartUTC, us.CurrentPeriodEndUTC, us.CancelAtPeriodEnd, us.CancelledDateUTC,
        us.LastPaymentFailureDateUTC, us.LastStripeEventId,
        sl.Name, sl.DisplayName, sl.Description, sl.MaxBooks, sl.MaxChaptersPerBook,
        sl.MaxScenesPerBook, sl.MaxCharactersPerProject, sl.MaxStorageMB, sl.MaxCollaborators,
        sl.IsActive AS SubscriptionLevelIsActive, sl.SortOrder, sl.StripeMonthlyPriceId,
        sl.StripeAnnualPriceId, sl.RequiresStripeSubscription
    FROM dbo.UserSubscription us
    INNER JOIN dbo.SubscriptionLevel sl ON sl.SubscriptionLevelID = us.SubscriptionLevelID
    WHERE us.UserID = @UserID
        AND us.IsActive = 1
        AND (us.EndDateUTC IS NULL OR us.EndDateUTC > SYSUTCDATETIME());
END;
GO

CREATE OR ALTER PROCEDURE dbo.UserSubscription_SetStripeCustomer
    @UserID int,
    @StripeCustomerId nvarchar(100)
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE dbo.UserSubscription
    SET StripeCustomerId = @StripeCustomerId,
        ModifiedDateUTC = SYSUTCDATETIME()
    WHERE UserID = @UserID
        AND IsActive = 1;
END;
GO

CREATE OR ALTER PROCEDURE dbo.StripeWebhookEvent_TryBegin
    @StripeEventId nvarchar(100),
    @EventType nvarchar(100)
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (SELECT 1 FROM dbo.StripeWebhookEvent WHERE StripeEventId = @StripeEventId)
    BEGIN
        SELECT CAST(0 AS bit) AS ShouldProcess;
        RETURN;
    END;

    INSERT dbo.StripeWebhookEvent (StripeEventId, EventType)
    VALUES (@StripeEventId, @EventType);

    SELECT CAST(1 AS bit) AS ShouldProcess;
END;
GO

CREATE OR ALTER PROCEDURE dbo.StripeWebhookEvent_Complete
    @StripeEventId nvarchar(100),
    @ProcessingStatus nvarchar(50),
    @ErrorMessage nvarchar(1000) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE dbo.StripeWebhookEvent
    SET ProcessingStatus = @ProcessingStatus,
        ProcessedDateUTC = SYSUTCDATETIME(),
        ErrorMessage = @ErrorMessage
    WHERE StripeEventId = @StripeEventId;
END;
GO

CREATE OR ALTER PROCEDURE dbo.UserSubscription_ApplyStripeState
    @UserID int = NULL,
    @SubscriptionLevelID int = NULL,
    @StripeCustomerId nvarchar(100),
    @StripeSubscriptionId nvarchar(100),
    @StripePriceId nvarchar(100),
    @BillingInterval nvarchar(20),
    @StripeStatus nvarchar(50),
    @CurrentPeriodStartUTC datetime2 = NULL,
    @CurrentPeriodEndUTC datetime2 = NULL,
    @CancelAtPeriodEnd bit = 0,
    @LastStripeEventId nvarchar(100) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    IF @SubscriptionLevelID IS NULL
        SELECT @SubscriptionLevelID = SubscriptionLevelID
        FROM dbo.SubscriptionLevel
        WHERE IsActive = 1
            AND (StripeMonthlyPriceId = @StripePriceId OR StripeAnnualPriceId = @StripePriceId);

    IF @BillingInterval IS NULL
        SELECT @BillingInterval = CASE
            WHEN StripeMonthlyPriceId = @StripePriceId THEN N'Monthly'
            WHEN StripeAnnualPriceId = @StripePriceId THEN N'Annual'
            ELSE NULL
        END
        FROM dbo.SubscriptionLevel
        WHERE SubscriptionLevelID = @SubscriptionLevelID;

    IF @UserID IS NULL
        SELECT TOP (1) @UserID = UserID
        FROM dbo.UserSubscription
        WHERE StripeSubscriptionId = @StripeSubscriptionId
            OR StripeCustomerId = @StripeCustomerId
        ORDER BY IsActive DESC, ModifiedDateUTC DESC;

    IF @UserID IS NULL OR @SubscriptionLevelID IS NULL
        RETURN;

    DECLARE @ExistingSubscriptionID int;
    SELECT TOP (1) @ExistingSubscriptionID = UserSubscriptionID
    FROM dbo.UserSubscription
    WHERE UserID = @UserID
        AND IsActive = 1
    ORDER BY UserSubscriptionID DESC;

    IF @ExistingSubscriptionID IS NULL
    BEGIN
        INSERT dbo.UserSubscription
        (
            UserID, SubscriptionLevelID, StartDateUTC, EndDateUTC, IsActive, IsTrial, TrialExpiryDateUTC,
            StripeCustomerId, StripeSubscriptionId, StripePriceId, BillingInterval, StripeStatus,
            CurrentPeriodStartUTC, CurrentPeriodEndUTC, CancelAtPeriodEnd, LastStripeEventId
        )
        VALUES
        (
            @UserID, @SubscriptionLevelID, COALESCE(@CurrentPeriodStartUTC, SYSUTCDATETIME()), NULL, 1, 0, NULL,
            @StripeCustomerId, @StripeSubscriptionId, @StripePriceId, @BillingInterval, @StripeStatus,
            @CurrentPeriodStartUTC, @CurrentPeriodEndUTC, @CancelAtPeriodEnd, @LastStripeEventId
        );
    END
    ELSE
    BEGIN
        UPDATE dbo.UserSubscription
        SET SubscriptionLevelID = @SubscriptionLevelID,
            EndDateUTC = NULL,
            IsActive = 1,
            IsTrial = 0,
            TrialExpiryDateUTC = NULL,
            StripeCustomerId = @StripeCustomerId,
            StripeSubscriptionId = @StripeSubscriptionId,
            StripePriceId = @StripePriceId,
            BillingInterval = @BillingInterval,
            StripeStatus = @StripeStatus,
            CurrentPeriodStartUTC = @CurrentPeriodStartUTC,
            CurrentPeriodEndUTC = @CurrentPeriodEndUTC,
            CancelAtPeriodEnd = @CancelAtPeriodEnd,
            CancelledDateUTC = CASE WHEN @StripeStatus = N'canceled' THEN COALESCE(CancelledDateUTC, SYSUTCDATETIME()) ELSE NULL END,
            LastPaymentFailureDateUTC = CASE WHEN @StripeStatus IN (N'active', N'trialing') THEN NULL ELSE LastPaymentFailureDateUTC END,
            LastStripeEventId = @LastStripeEventId,
            ModifiedDateUTC = SYSUTCDATETIME()
        WHERE UserSubscriptionID = @ExistingSubscriptionID;
    END;
END;
GO

CREATE OR ALTER PROCEDURE dbo.UserSubscription_DowngradeToDraftDesk
    @StripeCustomerId nvarchar(100) = NULL,
    @StripeSubscriptionId nvarchar(100) = NULL,
    @LastStripeEventId nvarchar(100) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @DraftDeskID int;
    SELECT @DraftDeskID = SubscriptionLevelID FROM dbo.SubscriptionLevel WHERE Name = N'DraftDesk';

    IF @DraftDeskID IS NULL
        RETURN;

    UPDATE dbo.UserSubscription
    SET SubscriptionLevelID = @DraftDeskID,
        EndDateUTC = NULL,
        IsActive = 1,
        IsTrial = 0,
        TrialExpiryDateUTC = NULL,
        StripeStatus = N'canceled',
        CancelAtPeriodEnd = 0,
        CancelledDateUTC = COALESCE(CancelledDateUTC, SYSUTCDATETIME()),
        LastStripeEventId = @LastStripeEventId,
        ModifiedDateUTC = SYSUTCDATETIME()
    WHERE (@StripeSubscriptionId IS NOT NULL AND StripeSubscriptionId = @StripeSubscriptionId)
        OR (@StripeCustomerId IS NOT NULL AND StripeCustomerId = @StripeCustomerId);
END;
GO

CREATE OR ALTER PROCEDURE dbo.UserSubscription_RecordPaymentFailure
    @StripeCustomerId nvarchar(100) = NULL,
    @StripeSubscriptionId nvarchar(100) = NULL,
    @LastStripeEventId nvarchar(100) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE dbo.UserSubscription
    SET LastPaymentFailureDateUTC = SYSUTCDATETIME(),
        LastStripeEventId = @LastStripeEventId,
        ModifiedDateUTC = SYSUTCDATETIME()
    WHERE (@StripeSubscriptionId IS NOT NULL AND StripeSubscriptionId = @StripeSubscriptionId)
        OR (@StripeCustomerId IS NOT NULL AND StripeCustomerId = @StripeCustomerId);
END;
GO

CREATE OR ALTER PROCEDURE dbo.UserSubscription_ClearPaymentFailure
    @StripeCustomerId nvarchar(100) = NULL,
    @StripeSubscriptionId nvarchar(100) = NULL,
    @LastStripeEventId nvarchar(100) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE dbo.UserSubscription
    SET LastPaymentFailureDateUTC = NULL,
        LastStripeEventId = @LastStripeEventId,
        ModifiedDateUTC = SYSUTCDATETIME()
    WHERE (@StripeSubscriptionId IS NOT NULL AND StripeSubscriptionId = @StripeSubscriptionId)
        OR (@StripeCustomerId IS NOT NULL AND StripeCustomerId = @StripeCustomerId);
END;
GO