search

ngocthanhdoan / devhub-web-api

DevHub Việt Nam
0 stars 2 forks source link

[devhub][issues_05][systems_db_manager_database] #2

Open ngocthanhdoan opened 3 months ago

ngocthanhdoan commented 3 months ago

-- Tạo bảng users (Tài khoản) CREATE TABLE users ( id UUID PRIMARY KEY, username VARCHAR(255) UNIQUE NOT NULL, email VARCHAR(255) UNIQUE NOT NULL, password_hash VARCHAR(255) NOT NULL, full_name VARCHAR(255) NOT NULL, role ENUM('developer', 'admin') DEFAULT 'developer', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );

-- Tạo bảng password_resets (Khôi phục mật khẩu) CREATE TABLE password_resets ( id UUID PRIMARY KEY, user_id UUID, reset_token VARCHAR(255) UNIQUE NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, expires_at TIMESTAMP NOT NULL, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE );

-- Tạo bảng databases (Cơ sở dữ liệu) CREATE TABLE databases ( id UUID PRIMARY KEY, name VARCHAR(255) UNIQUE NOT NULL, host VARCHAR(255) NOT NULL, port INT NOT NULL, username VARCHAR(255) NOT NULL, password VARCHAR(255) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );

-- Tạo bảng access_rights (Quyền truy cập) CREATE TABLE access_rights ( id UUID PRIMARY KEY, user_id UUID, database_id UUID, role ENUM('read', 'write', 'admin') NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY (database_id) REFERENCES databases(id) ON DELETE CASCADE );

-- Tạo bảng projects (Dự án) CREATE TABLE projects ( id UUID PRIMARY KEY, name VARCHAR(255) UNIQUE NOT NULL, description TEXT, database_id UUID, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (database_id) REFERENCES databases(id) ON DELETE SET NULL );

-- Tạo bảng project_access (Quyền truy cập dự án) CREATE TABLE project_access ( id UUID PRIMARY KEY, project_id UUID, user_id UUID, role ENUM('read', 'write', 'admin') NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE );

-- Tạo bảng system_config (Cấu hình hệ thống) CREATE TABLE system_config ( id UUID PRIMARY KEY, config_name VARCHAR(255) UNIQUE NOT NULL, config_value TEXT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );

-- Tạo bảng logs (Lịch sử hoạt động) CREATE TABLE logs ( id UUID PRIMARY KEY, user_id UUID, action TEXT NOT NULL, timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE );

-- Tạo bảng reports (Báo cáo và thống kê) CREATE TABLE reports ( id UUID PRIMARY KEY, report_type ENUM('usage', 'access') NOT NULL, data JSON NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

-- Tạo bảng faqs (Câu hỏi thường gặp) CREATE TABLE faqs ( id UUID PRIMARY KEY, question TEXT NOT NULL, answer TEXT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );

-- Tạo bảng contacts (Liên hệ hỗ trợ) CREATE TABLE contacts ( id UUID PRIMARY KEY, user_id UUID, subject VARCHAR(255) NOT NULL, message TEXT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, status ENUM('open', 'closed') DEFAULT 'open', FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL );

-- Tạo bảng about (Về chúng tôi) CREATE TABLE about ( id UUID PRIMARY KEY, company_name VARCHAR(255) NOT NULL, history TEXT, vision TEXT, mission TEXT, team TEXT, recruitment TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );

-- Tạo bảng documentation (Tài liệu hướng dẫn) CREATE TABLE documentation ( id UUID PRIMARY KEY, title VARCHAR(255) NOT NULL, content TEXT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );