SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

IF OBJECT_ID(N'dbo.UserFile', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.UserFile
    (
        UserFileID bigint IDENTITY(1,1) NOT NULL CONSTRAINT PK_UserFile PRIMARY KEY,
        UserID int NOT NULL,
        ProjectID int NULL,
        EntityType nvarchar(100) NOT NULL,
        EntityID int NOT NULL,
        FileName nvarchar(300) NOT NULL,
        OriginalFileName nvarchar(300) NULL,
        ContentType nvarchar(200) NULL,
        FileSizeBytes bigint NOT NULL,
        StoragePath nvarchar(1000) NOT NULL,
        UploadedDateUTC datetime2 NOT NULL CONSTRAINT DF_UserFile_UploadedDateUTC DEFAULT SYSUTCDATETIME(),
        DeletedDateUTC datetime2 NULL,
        IsDeleted bit NOT NULL CONSTRAINT DF_UserFile_IsDeleted DEFAULT 0,
        CONSTRAINT FK_UserFile_AppUser FOREIGN KEY (UserID) REFERENCES dbo.AppUser(UserID),
        CONSTRAINT FK_UserFile_Project FOREIGN KEY (ProjectID) REFERENCES dbo.Projects(ProjectID),
        CONSTRAINT CK_UserFile_FileSizeBytes CHECK (FileSizeBytes >= 0)
    );
END;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_UserFile_User_Active' AND object_id = OBJECT_ID(N'dbo.UserFile'))
    CREATE INDEX IX_UserFile_User_Active ON dbo.UserFile(UserID, IsDeleted, UploadedDateUTC DESC) INCLUDE (FileSizeBytes, ProjectID, EntityType, EntityID);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_UserFile_Project_Entity' AND object_id = OBJECT_ID(N'dbo.UserFile'))
    CREATE INDEX IX_UserFile_Project_Entity ON dbo.UserFile(ProjectID, EntityType, EntityID, IsDeleted);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_UserFile_StoragePath' AND object_id = OBJECT_ID(N'dbo.UserFile'))
    CREATE INDEX IX_UserFile_StoragePath ON dbo.UserFile(StoragePath, IsDeleted);
GO

CREATE OR ALTER PROCEDURE dbo.Project_GetOwnerUserID
    @ProjectID int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT TOP (1) UserID
    FROM dbo.ProjectUserAccess
    WHERE ProjectID = @ProjectID
        AND AccessRole = N'Owner'
        AND IsActive = 1
    ORDER BY ProjectUserAccessID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.UserFile_Create
    @UserID int,
    @ProjectID int = NULL,
    @EntityType nvarchar(100),
    @EntityID int,
    @FileName nvarchar(300),
    @OriginalFileName nvarchar(300) = NULL,
    @ContentType nvarchar(200) = NULL,
    @FileSizeBytes bigint,
    @StoragePath nvarchar(1000)
AS
BEGIN
    SET NOCOUNT ON;

    INSERT dbo.UserFile (UserID, ProjectID, EntityType, EntityID, FileName, OriginalFileName, ContentType, FileSizeBytes, StoragePath)
    VALUES (@UserID, @ProjectID, @EntityType, @EntityID, @FileName, @OriginalFileName, @ContentType, @FileSizeBytes, @StoragePath);

    SELECT CAST(SCOPE_IDENTITY() AS bigint);
END;
GO

CREATE OR ALTER PROCEDURE dbo.UserFile_MarkDeletedByStoragePath
    @StoragePath nvarchar(1000)
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE dbo.UserFile
    SET IsDeleted = 1,
        DeletedDateUTC = COALESCE(DeletedDateUTC, SYSUTCDATETIME())
    WHERE StoragePath = @StoragePath
        AND IsDeleted = 0;
END;
GO

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

    SELECT CAST(COALESCE(SUM(FileSizeBytes), 0) AS bigint)
    FROM dbo.UserFile
    WHERE UserID = @UserID
        AND IsDeleted = 0;
END;
GO

INSERT INTO dbo.UserFile (UserID, ProjectID, EntityType, EntityID, FileName, OriginalFileName, ContentType, FileSizeBytes, StoragePath, UploadedDateUTC)
SELECT ownerAccess.UserID,
    b.ProjectID,
    N'SceneAttachment',
    a.SceneAttachmentID,
    RIGHT(a.FilePath, CHARINDEX(N'/', REVERSE(a.FilePath + N'/')) - 1),
    RIGHT(a.FilePath, CHARINDEX(N'/', REVERSE(a.FilePath + N'/')) - 1),
    NULL,
    0,
    a.FilePath,
    COALESCE(a.CreatedDateUTC, a.CreatedDate, SYSUTCDATETIME())
FROM dbo.SceneAttachments a
INNER JOIN dbo.Scenes s ON s.SceneID = a.SceneID
INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
INNER JOIN dbo.Books b ON b.BookID = c.BookID
OUTER APPLY
(
    SELECT TOP (1) UserID
    FROM dbo.ProjectUserAccess
    WHERE ProjectID = b.ProjectID
        AND AccessRole = N'Owner'
        AND IsActive = 1
    ORDER BY ProjectUserAccessID
) ownerAccess
WHERE a.FilePath IS NOT NULL
    AND a.FilePath LIKE N'/uploads/scenes/%'
    AND ownerAccess.UserID IS NOT NULL
    AND NOT EXISTS
    (
        SELECT 1
        FROM dbo.UserFile existing
        WHERE existing.StoragePath = a.FilePath
            AND existing.EntityType = N'SceneAttachment'
            AND existing.EntityID = a.SceneAttachmentID
    );
GO