Files
2026-10-29 13:21:30 +06:00

225 lines
9.4 KiB
SQL

-- CreateTable
CREATE TABLE `User` (
`id` VARCHAR(191) NOT NULL,
`email` VARCHAR(191) NOT NULL,
`username` VARCHAR(191) NOT NULL,
`fullName` VARCHAR(191) NOT NULL,
`passwordHash` VARCHAR(191) NOT NULL,
`role` ENUM('SUPER_ADMIN', 'ADMIN', 'USER') NOT NULL DEFAULT 'USER',
`status` ENUM('ACTIVE', 'SUSPENDED', 'PENDING') NOT NULL DEFAULT 'ACTIVE',
`storageQuotaBytes` BIGINT NOT NULL DEFAULT 5368709120,
`storageUsedBytes` BIGINT NOT NULL DEFAULT 0,
`createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
`updatedAt` DATETIME(3) NOT NULL,
`lastLoginAt` DATETIME(3) NULL,
UNIQUE INDEX `User_email_key`(`email`),
UNIQUE INDEX `User_username_key`(`username`),
INDEX `User_role_idx`(`role`),
INDEX `User_status_idx`(`status`),
PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- CreateTable
CREATE TABLE `Folder` (
`id` VARCHAR(191) NOT NULL,
`ownerId` VARCHAR(191) NOT NULL,
`parentId` VARCHAR(191) NULL,
`name` VARCHAR(191) NOT NULL,
`logicalPath` VARCHAR(191) NOT NULL,
`createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
`updatedAt` DATETIME(3) NOT NULL,
INDEX `Folder_ownerId_idx`(`ownerId`),
INDEX `Folder_parentId_idx`(`parentId`),
UNIQUE INDEX `Folder_ownerId_parentId_name_key`(`ownerId`, `parentId`, `name`),
PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- CreateTable
CREATE TABLE `FileAsset` (
`id` VARCHAR(191) NOT NULL,
`ownerId` VARCHAR(191) NOT NULL,
`folderId` VARCHAR(191) NULL,
`originalFilename` VARCHAR(191) NOT NULL,
`internalStorageKey` VARCHAR(191) NOT NULL,
`mimeType` VARCHAR(191) NOT NULL,
`fileExtension` VARCHAR(191) NOT NULL,
`fileSizeBytes` BIGINT NOT NULL,
`fileCategory` ENUM('VIDEO', 'IMAGE', 'DOCUMENT', 'ARCHIVE', 'AUDIO', 'OTHER') NOT NULL,
`checksum` VARCHAR(191) NULL,
`status` ENUM('PENDING', 'UPLOADING', 'PROCESSING', 'READY', 'FAILED') NOT NULL DEFAULT 'PENDING',
`durationSeconds` DOUBLE NULL,
`width` INTEGER NULL,
`height` INTEGER NULL,
`videoCodec` VARCHAR(191) NULL,
`audioCodec` VARCHAR(191) NULL,
`thumbnailStorageKey` VARCHAR(191) NULL,
`hlsMasterPlaylistKey` VARCHAR(191) NULL,
`isSoftDeleted` BOOLEAN NOT NULL DEFAULT false,
`deletedAt` DATETIME(3) NULL,
`createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
`updatedAt` DATETIME(3) NOT NULL,
UNIQUE INDEX `FileAsset_internalStorageKey_key`(`internalStorageKey`),
INDEX `FileAsset_ownerId_idx`(`ownerId`),
INDEX `FileAsset_folderId_idx`(`folderId`),
INDEX `FileAsset_fileCategory_idx`(`fileCategory`),
INDEX `FileAsset_status_idx`(`status`),
INDEX `FileAsset_isSoftDeleted_idx`(`isSoftDeleted`),
PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- CreateTable
CREATE TABLE `FilePermission` (
`id` VARCHAR(191) NOT NULL,
`resourceType` ENUM('FILE', 'FOLDER', 'USER_FILES') NOT NULL,
`resourceId` VARCHAR(191) NULL,
`ownerId` VARCHAR(191) NOT NULL,
`granteeUserId` VARCHAR(191) NOT NULL,
`grantedByUserId` VARCHAR(191) NOT NULL,
`canViewMetadata` BOOLEAN NOT NULL DEFAULT true,
`canPreview` BOOLEAN NOT NULL DEFAULT false,
`canPlay` BOOLEAN NOT NULL DEFAULT false,
`canDownload` BOOLEAN NOT NULL DEFAULT false,
`canListFolder` BOOLEAN NOT NULL DEFAULT false,
`expiresAt` DATETIME(3) NULL,
`revokedAt` DATETIME(3) NULL,
`createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
`updatedAt` DATETIME(3) NOT NULL,
INDEX `FilePermission_granteeUserId_idx`(`granteeUserId`),
INDEX `FilePermission_ownerId_idx`(`ownerId`),
INDEX `FilePermission_resourceType_resourceId_idx`(`resourceType`, `resourceId`),
INDEX `FilePermission_revokedAt_expiresAt_idx`(`revokedAt`, `expiresAt`),
PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- CreateTable
CREATE TABLE `UploadSession` (
`id` VARCHAR(191) NOT NULL,
`ownerId` VARCHAR(191) NOT NULL,
`folderId` VARCHAR(191) NULL,
`originalFilename` VARCHAR(191) NOT NULL,
`mimeType` VARCHAR(191) NOT NULL,
`fileCategory` ENUM('VIDEO', 'IMAGE', 'DOCUMENT', 'ARCHIVE', 'AUDIO', 'OTHER') NOT NULL,
`totalSizeBytes` BIGINT NOT NULL,
`uploadedSizeBytes` BIGINT NOT NULL DEFAULT 0,
`tempStorageKey` VARCHAR(191) NOT NULL,
`status` ENUM('INITIALIZED', 'UPLOADING', 'COMPLETED', 'ABORTED', 'EXPIRED') NOT NULL DEFAULT 'INITIALIZED',
`expiresAt` DATETIME(3) NOT NULL,
`createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
`updatedAt` DATETIME(3) NOT NULL,
UNIQUE INDEX `UploadSession_tempStorageKey_key`(`tempStorageKey`),
INDEX `UploadSession_ownerId_idx`(`ownerId`),
INDEX `UploadSession_status_idx`(`status`),
INDEX `UploadSession_expiresAt_idx`(`expiresAt`),
PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- CreateTable
CREATE TABLE `VideoProcessingJob` (
`id` VARCHAR(191) NOT NULL,
`fileAssetId` VARCHAR(191) NOT NULL,
`status` ENUM('QUEUED', 'PROCESSING', 'COMPLETED', 'FAILED') NOT NULL DEFAULT 'QUEUED',
`progress` INTEGER NOT NULL DEFAULT 0,
`attempts` INTEGER NOT NULL DEFAULT 0,
`errorReason` TEXT NULL,
`createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
`updatedAt` DATETIME(3) NOT NULL,
`completedAt` DATETIME(3) NULL,
INDEX `VideoProcessingJob_fileAssetId_idx`(`fileAssetId`),
INDEX `VideoProcessingJob_status_idx`(`status`),
PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- CreateTable
CREATE TABLE `RefreshToken` (
`id` VARCHAR(191) NOT NULL,
`tokenHash` VARCHAR(191) NOT NULL,
`userId` VARCHAR(191) NOT NULL,
`userAgent` VARCHAR(191) NULL,
`ipAddress` VARCHAR(191) NULL,
`expiresAt` DATETIME(3) NOT NULL,
`revokedAt` DATETIME(3) NULL,
`createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
UNIQUE INDEX `RefreshToken_tokenHash_key`(`tokenHash`),
INDEX `RefreshToken_userId_idx`(`userId`),
INDEX `RefreshToken_expiresAt_idx`(`expiresAt`),
PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- CreateTable
CREATE TABLE `AuditLog` (
`id` VARCHAR(191) NOT NULL,
`actorUserId` VARCHAR(191) NULL,
`action` VARCHAR(191) NOT NULL,
`resourceType` VARCHAR(191) NOT NULL,
`resourceId` VARCHAR(191) NULL,
`targetUserId` VARCHAR(191) NULL,
`ipAddress` VARCHAR(191) NULL,
`userAgent` VARCHAR(191) NULL,
`detailsJson` TEXT NULL,
`createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
INDEX `AuditLog_actorUserId_idx`(`actorUserId`),
INDEX `AuditLog_action_idx`(`action`),
INDEX `AuditLog_resourceType_resourceId_idx`(`resourceType`, `resourceId`),
INDEX `AuditLog_createdAt_idx`(`createdAt`),
PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- CreateTable
CREATE TABLE `SystemSetting` (
`id` VARCHAR(191) NOT NULL,
`key` VARCHAR(191) NOT NULL,
`value` TEXT NOT NULL,
`description` VARCHAR(191) NULL,
`updatedAt` DATETIME(3) NOT NULL,
UNIQUE INDEX `SystemSetting_key_key`(`key`),
PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- AddForeignKey
ALTER TABLE `Folder` ADD CONSTRAINT `Folder_ownerId_fkey` FOREIGN KEY (`ownerId`) REFERENCES `User`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
-- AddForeignKey
ALTER TABLE `Folder` ADD CONSTRAINT `Folder_parentId_fkey` FOREIGN KEY (`parentId`) REFERENCES `Folder`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
-- AddForeignKey
ALTER TABLE `FileAsset` ADD CONSTRAINT `FileAsset_ownerId_fkey` FOREIGN KEY (`ownerId`) REFERENCES `User`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
-- AddForeignKey
ALTER TABLE `FileAsset` ADD CONSTRAINT `FileAsset_folderId_fkey` FOREIGN KEY (`folderId`) REFERENCES `Folder`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;
-- AddForeignKey
ALTER TABLE `FilePermission` ADD CONSTRAINT `FilePermission_ownerId_fkey` FOREIGN KEY (`ownerId`) REFERENCES `User`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
-- AddForeignKey
ALTER TABLE `FilePermission` ADD CONSTRAINT `FilePermission_granteeUserId_fkey` FOREIGN KEY (`granteeUserId`) REFERENCES `User`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
-- AddForeignKey
ALTER TABLE `FilePermission` ADD CONSTRAINT `FilePermission_grantedByUserId_fkey` FOREIGN KEY (`grantedByUserId`) REFERENCES `User`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
-- AddForeignKey
ALTER TABLE `UploadSession` ADD CONSTRAINT `UploadSession_ownerId_fkey` FOREIGN KEY (`ownerId`) REFERENCES `User`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
-- AddForeignKey
ALTER TABLE `UploadSession` ADD CONSTRAINT `UploadSession_folderId_fkey` FOREIGN KEY (`folderId`) REFERENCES `Folder`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;
-- AddForeignKey
ALTER TABLE `VideoProcessingJob` ADD CONSTRAINT `VideoProcessingJob_fileAssetId_fkey` FOREIGN KEY (`fileAssetId`) REFERENCES `FileAsset`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
-- AddForeignKey
ALTER TABLE `RefreshToken` ADD CONSTRAINT `RefreshToken_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `User`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
-- AddForeignKey
ALTER TABLE `AuditLog` ADD CONSTRAINT `AuditLog_actorUserId_fkey` FOREIGN KEY (`actorUserId`) REFERENCES `User`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;
-- AddForeignKey
ALTER TABLE `AuditLog` ADD CONSTRAINT `AuditLog_targetUserId_fkey` FOREIGN KEY (`targetUserId`) REFERENCES `User`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;