112 lines
4.1 KiB
SQL
112 lines
4.1 KiB
SQL
-- Smart Project Database Schema
|
|
|
|
CREATE DATABASE IF NOT EXISTS smart_project CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
USE smart_project;
|
|
|
|
-- Projects table (created first to avoid circular dependency)
|
|
CREATE TABLE IF NOT EXISTS projects (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
created_by INT DEFAULT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
INDEX idx_created_by (created_by)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- Users table
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
username VARCHAR(255) NOT NULL UNIQUE,
|
|
password_hash VARCHAR(512) NOT NULL,
|
|
user_type ENUM('platform', 'project') NOT NULL DEFAULT 'project',
|
|
role VARCHAR(32) NOT NULL,
|
|
real_name VARCHAR(128) NULL,
|
|
phone VARCHAR(32) NULL,
|
|
project_id INT 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,
|
|
INDEX idx_username (username),
|
|
INDEX idx_project_id (project_id),
|
|
INDEX idx_user_type (user_type)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- Add foreign key constraint to projects after users table exists
|
|
ALTER TABLE projects
|
|
ADD CONSTRAINT fk_projects_created_by
|
|
FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL;
|
|
|
|
-- OSS Files table
|
|
CREATE TABLE IF NOT EXISTS oss_files (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
project_id INT NOT NULL,
|
|
file_key VARCHAR(500) NOT NULL,
|
|
file_name VARCHAR(255) NOT NULL,
|
|
file_size BIGINT,
|
|
mime_type VARCHAR(100),
|
|
uploaded_by INT NOT NULL,
|
|
uploaded_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 (uploaded_by) REFERENCES users(id) ON DELETE CASCADE,
|
|
INDEX idx_project_id (project_id),
|
|
INDEX idx_uploaded_by (uploaded_by)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- Hazards table
|
|
CREATE TABLE IF NOT EXISTS hazards (
|
|
id VARCHAR(36) PRIMARY KEY,
|
|
project_id INT NOT NULL,
|
|
category VARCHAR(100) NOT NULL,
|
|
severity ENUM('general', 'serious', 'major') NOT NULL,
|
|
description TEXT NOT NULL,
|
|
photos JSON,
|
|
gps_lat DECIMAL(10, 8),
|
|
gps_lng DECIMAL(11, 8),
|
|
status ENUM('pending', 'assigned', 'resolved') DEFAULT 'pending',
|
|
reporter_id INT NOT NULL,
|
|
reporter_role VARCHAR(32) NOT NULL,
|
|
reported_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
assignee_id INT,
|
|
assignee_role VARCHAR(32),
|
|
assigned_at TIMESTAMP NULL DEFAULT NULL,
|
|
resolver_id INT,
|
|
resolver_role VARCHAR(32),
|
|
resolve_note TEXT,
|
|
resolved_at TIMESTAMP NULL DEFAULT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (reporter_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (assignee_id) REFERENCES users(id) ON DELETE SET NULL,
|
|
FOREIGN KEY (resolver_id) REFERENCES users(id) ON DELETE SET NULL,
|
|
INDEX idx_project_id (project_id),
|
|
INDEX idx_status (status),
|
|
INDEX idx_severity (severity),
|
|
INDEX idx_reported_at (reported_at)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- Construction logs table
|
|
CREATE TABLE IF NOT EXISTS construction_logs (
|
|
id VARCHAR(64) PRIMARY KEY,
|
|
project_id INT NOT NULL,
|
|
date DATE NOT NULL,
|
|
part VARCHAR(128) NOT NULL,
|
|
content TEXT NOT NULL,
|
|
workers INT NOT NULL,
|
|
weather VARCHAR(32),
|
|
equipment JSON,
|
|
photos JSON,
|
|
safety_note TEXT,
|
|
note TEXT,
|
|
author_id INT NOT NULL,
|
|
author_role VARCHAR(32) NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
INDEX idx_logs_project_id (project_id),
|
|
INDEX idx_logs_date (date DESC),
|
|
INDEX idx_logs_author_id (author_id),
|
|
INDEX idx_logs_created_at (created_at DESC)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|