177 lines
5.4 KiB
SQL
177 lines
5.4 KiB
SQL
-- ======================================================================
|
|
-- SECURE VIDEO & FILE SHARING MANAGEMENT SYSTEM - USER IMPORT SQL
|
|
-- ======================================================================
|
|
-- Database: fileshare_db (MySQL 8.0 / MariaDB)
|
|
-- Generated: 2026-10-14
|
|
--
|
|
-- Accounts Included:
|
|
-- 1. Superadmin:
|
|
-- Username: Ecohub
|
|
-- Email: ecohub@fileshare.local
|
|
-- Password: Ecohub@520
|
|
-- Role: SUPER_ADMIN (100 GB Quota)
|
|
--
|
|
-- 2. Staff User:
|
|
-- Username: Ecohubstaff
|
|
-- Email: ecohubstaff@fileshare.local
|
|
-- Password: 123
|
|
-- Role: ADMIN (50 GB Quota)
|
|
--
|
|
-- 3. Normal User:
|
|
-- Username: ecouser
|
|
-- Email: ecouser@fileshare.local
|
|
-- Password: 123
|
|
-- Role: USER (10 GB Quota)
|
|
-- ======================================================================
|
|
|
|
-- Ensure target database is selected
|
|
CREATE DATABASE IF NOT EXISTS `fileshare_db` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
USE `fileshare_db`;
|
|
|
|
-- Temporarily disable foreign key checks during import
|
|
SET FOREIGN_KEY_CHECKS = 0;
|
|
|
|
-- ----------------------------------------------------------------------
|
|
-- 1. INSERT OR UPDATE USERS (Table: `User`)
|
|
-- ----------------------------------------------------------------------
|
|
INSERT INTO `User` (
|
|
`id`,
|
|
`email`,
|
|
`username`,
|
|
`fullName`,
|
|
`companyName`,
|
|
`passwordHash`,
|
|
`viewablePassword`,
|
|
`role`,
|
|
`status`,
|
|
`storageQuotaBytes`,
|
|
`storageUsedBytes`,
|
|
`canCreateShareLinks`,
|
|
`canDeleteFiles`,
|
|
`canViewOtherUsers`,
|
|
`remoteFileServerUrl`,
|
|
`canUseRemoteServer`,
|
|
`createdAt`,
|
|
`updatedAt`
|
|
) VALUES
|
|
-- 1. Superadmin: Ecohub / Ecohub@520
|
|
(
|
|
'ecohub-superadmin-uuid-2026-0001',
|
|
'ecohub@fileshare.local',
|
|
'ecohub',
|
|
'Ecohub Super Administrator',
|
|
'Ecohub Technologies',
|
|
'$argon2id$v=19$m=65536,t=3,p=4$fmAQcJ4zAtKrzBNnfdWneQ$d3WbQGHsQ+CSwTiOL363uKudXfsFGgS4wFd/jPbenVw',
|
|
'Ecohub@520',
|
|
'SUPER_ADMIN',
|
|
'ACTIVE',
|
|
107374182400,
|
|
0,
|
|
1,
|
|
1,
|
|
1,
|
|
NULL,
|
|
1,
|
|
NOW(3),
|
|
NOW(3)
|
|
),
|
|
|
|
-- 2. Staff Officer: Ecohubstaff / 123
|
|
(
|
|
'ecohubstaff-admin-uuid-2026-0002',
|
|
'ecohubstaff@fileshare.local',
|
|
'ecohubstaff',
|
|
'Ecohub Staff Officer',
|
|
'Ecohub Media & Ops',
|
|
'$argon2id$v=19$m=65536,t=3,p=4$Mws8SMfU7v9D1Cs8zsN1BA$clCgpd4T/Os+Mp1AGpg61jqIL/UFwnzFivEGd8MWs4k',
|
|
'123',
|
|
'ADMIN',
|
|
'ACTIVE',
|
|
53687091200,
|
|
0,
|
|
1,
|
|
1,
|
|
0,
|
|
NULL,
|
|
1,
|
|
NOW(3),
|
|
NOW(3)
|
|
),
|
|
|
|
-- 3. Normal User: ecouser / 123
|
|
(
|
|
'ecouser-normal-uuid-2026-0003',
|
|
'ecouser@fileshare.local',
|
|
'ecouser',
|
|
'Ecohub Standard User',
|
|
'Ecohub Client Org',
|
|
'$argon2id$v=19$m=65536,t=3,p=4$Mws8SMfU7v9D1Cs8zsN1BA$clCgpd4T/Os+Mp1AGpg61jqIL/UFwnzFivEGd8MWs4k',
|
|
'123',
|
|
'USER',
|
|
'ACTIVE',
|
|
10737418240,
|
|
0,
|
|
0,
|
|
1,
|
|
0,
|
|
NULL,
|
|
1,
|
|
NOW(3),
|
|
NOW(3)
|
|
)
|
|
|
|
ON DUPLICATE KEY UPDATE
|
|
`passwordHash` = VALUES(`passwordHash`),
|
|
`viewablePassword` = VALUES(`viewablePassword`),
|
|
`role` = VALUES(`role`),
|
|
`status` = VALUES(`status`),
|
|
`fullName` = VALUES(`fullName`),
|
|
`companyName` = VALUES(`companyName`),
|
|
`storageQuotaBytes` = VALUES(`storageQuotaBytes`),
|
|
`canCreateShareLinks` = VALUES(`canCreateShareLinks`),
|
|
`canDeleteFiles` = VALUES(`canDeleteFiles`),
|
|
`canViewOtherUsers` = VALUES(`canViewOtherUsers`),
|
|
`remoteFileServerUrl` = VALUES(`remoteFileServerUrl`),
|
|
`canUseRemoteServer` = VALUES(`canUseRemoteServer`),
|
|
`updatedAt` = NOW(3);
|
|
|
|
-- ----------------------------------------------------------------------
|
|
-- 2. ENSURE ROOT STORAGE FOLDERS EXIST (Table: `Folder`)
|
|
-- ----------------------------------------------------------------------
|
|
INSERT INTO `Folder` (
|
|
`id`,
|
|
`ownerId`,
|
|
`parentId`,
|
|
`name`,
|
|
`logicalPath`,
|
|
`createdAt`,
|
|
`updatedAt`
|
|
) VALUES
|
|
('root-folder-ecohub-0001', 'ecohub-superadmin-uuid-2026-0001', NULL, 'Root', '/', NOW(3), NOW(3)),
|
|
('root-folder-ecohubstaff-0002', 'ecohubstaff-admin-uuid-2026-0002', NULL, 'Root', '/', NOW(3), NOW(3)),
|
|
('root-folder-ecouser-0003', 'ecouser-normal-uuid-2026-0003', NULL, 'Root', '/', NOW(3), NOW(3))
|
|
|
|
ON DUPLICATE KEY UPDATE
|
|
`logicalPath` = VALUES(`logicalPath`);
|
|
|
|
-- ----------------------------------------------------------------------
|
|
-- 3. INITIALIZE DEFAULT SYSTEM SETTINGS (Table: `SystemSetting`)
|
|
-- ----------------------------------------------------------------------
|
|
INSERT INTO `SystemSetting` (`id`, `key`, `value`, `description`, `updatedAt`) VALUES
|
|
(UUID(), 'MAX_FILE_SIZE_BYTES', '5368709120', 'Maximum permitted single file upload size in bytes (5 GB)', NOW(3)),
|
|
(UUID(), 'DEFAULT_USER_QUOTA_BYTES', '10737418240', 'Default storage quota allocated to newly registered users (10 GB)', NOW(3)),
|
|
(UUID(), 'ALLOWED_EXTENSIONS', 'mp4,webm,mov,avi,mkv,png,jpg,jpeg,webp,gif,pdf,doc,docx,xls,xlsx,ppt,pptx,zip,rar,7z,tar,gz,mp3,wav,ogg,txt,csv,json', 'Allowed file extensions whitelist', NOW(3)),
|
|
(UUID(), 'ENABLE_PUBLIC_REGISTRATION', 'true', 'Flag controlling whether new users can self-register', NOW(3)),
|
|
(UUID(), 'STORAGE_RETENTION_DAYS', '30', 'Soft-deleted file retention duration in recycle bin', NOW(3)),
|
|
(UUID(), 'STORAGE_BASE_PATH', './storage', 'Dynamic local storage directory location (can be changed by Superadmin anytime)', NOW(3)),
|
|
(UUID(), 'REMOTE_FILE_SERVER_URL', '', 'Optional Remote File Server Link / URL for external video uploads and mirroring', NOW(3))
|
|
|
|
ON DUPLICATE KEY UPDATE
|
|
`value` = VALUES(`value`);
|
|
|
|
-- Re-enable foreign key checks
|
|
SET FOREIGN_KEY_CHECKS = 1;
|
|
|
|
-- Output confirmation notice
|
|
SELECT 'Ecohub user accounts imported and activated successfully!' AS `Status`;
|