665 lines
30 KiB
SQL
665 lines
30 KiB
SQL
-- =============================================================================
|
||
-- MariaDB 데이터베이스 스키마 생성 SQL (오류 수정 완)
|
||
-- Aislo (아이슬로) — AI + Slotti 산림 도로 설계 자동화 시스템
|
||
--
|
||
-- DB 명: aislo_db
|
||
-- DBMS: MariaDB 10.6+
|
||
-- 실행 순서:
|
||
-- 1. DATABASE 생성 (aislo_db)
|
||
-- 2. CREATE TABLE (각 테이블)
|
||
-- 3. CREATE INDEX (인덱싱)
|
||
-- 4. ALTER TABLE ADD CONSTRAINT (외래키)
|
||
-- =============================================================================
|
||
|
||
-- 0. 데이터베이스 생성
|
||
-- =============================================================================
|
||
CREATE DATABASE IF NOT EXISTS aislo_db
|
||
CHARACTER SET utf8mb4
|
||
COLLATE utf8mb4_unicode_ci;
|
||
|
||
USE aislo_db;
|
||
|
||
|
||
-- 1. 테이블 생성 (순서 중요: FK 참조 테이블이 먼저 생성되어야 함)
|
||
-- =============================================================================
|
||
|
||
/* --------- 1-1. 사용자 & 인증 그룹 --------- */
|
||
|
||
-- users: 시스템 사용자
|
||
CREATE TABLE IF NOT EXISTS users (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
email VARCHAR(255) NOT NULL UNIQUE,
|
||
password_hash VARCHAR(255) NOT NULL,
|
||
name VARCHAR(255) NOT NULL,
|
||
position VARCHAR(100), -- 직급 (과장, 대리, 사원)
|
||
department VARCHAR(100), -- 부서명 (설계팀, 영업팀)
|
||
phone VARCHAR(20), -- 연락처 (010-1234-5678)
|
||
company_id INT, -- 소속 회사 (FK는 later)
|
||
role ENUM('SYSTEM_ADMIN', 'MASTER', 'MEMBER') NOT NULL DEFAULT 'MEMBER',
|
||
is_master BOOLEAN NOT NULL DEFAULT FALSE,
|
||
status ENUM('PENDING_EMAIL', 'PENDING', 'ACTIVE', 'INACTIVE', 'REJECTED') NOT NULL DEFAULT 'PENDING_EMAIL',
|
||
last_email_verified_at TIMESTAMP NULL,
|
||
login_failures INT NOT NULL DEFAULT 0,
|
||
last_failed_at TIMESTAMP NULL,
|
||
account_locked_until TIMESTAMP NULL,
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
deleted_at TIMESTAMP NULL DEFAULT NULL -- 소프트 삭제
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- companies: 회사 정보
|
||
CREATE TABLE IF NOT EXISTS companies (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
name VARCHAR(255) NOT NULL UNIQUE,
|
||
business_registration_number VARCHAR(20) UNIQUE, -- 사업자등록번호
|
||
business_address VARCHAR(255), -- 사업장 주소
|
||
business_owner VARCHAR(100), -- 사업주 이름
|
||
business_status VARCHAR(50), -- 기업 상태 (활동중, 폐업, 휴업)
|
||
created_by INT, -- 생성자 (FK는 later)
|
||
company_code VARCHAR(20) NOT NULL UNIQUE,
|
||
phone_number VARCHAR(20),
|
||
representative_name VARCHAR(100),
|
||
master_user_id INT NULL,
|
||
status ENUM('ACTIVE', 'INACTIVE', 'SUSPENDED') NOT NULL DEFAULT 'ACTIVE',
|
||
subscription_status ENUM('FREE', 'TRIAL', 'PAID', 'EXPIRED') NOT NULL DEFAULT 'FREE',
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
deleted_at TIMESTAMP NULL DEFAULT NULL
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- sessions: Secure HttpOnly 쿠키의 서버 측 세션 저장소
|
||
CREATE TABLE IF NOT EXISTS sessions (
|
||
id VARCHAR(255) PRIMARY KEY,
|
||
user_id INT NOT NULL,
|
||
user_agent_hash CHAR(64) NOT NULL,
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
expires_at TIMESTAMP NOT NULL,
|
||
last_activity_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
INDEX idx_sessions_user_id (user_id),
|
||
INDEX idx_sessions_expires_at (expires_at)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- email_otps: 목적별 일회용 이메일 인증 코드
|
||
CREATE TABLE IF NOT EXISTS email_otps (
|
||
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
||
user_id INT NOT NULL,
|
||
purpose ENUM('REGISTER', 'LOGIN', 'REVERIFY', 'PASSWORD_CHANGE') NOT NULL,
|
||
otp_hash VARCHAR(255) NOT NULL,
|
||
expires_at TIMESTAMP NOT NULL,
|
||
consumed_at TIMESTAMP NULL,
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
INDEX idx_email_otps_user_purpose (user_id, purpose, created_at)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
CREATE TABLE IF NOT EXISTS trusted_devices (
|
||
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
||
user_id INT NOT NULL,
|
||
user_agent_hash CHAR(64) NOT NULL,
|
||
verified_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
last_used_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE KEY uq_trusted_devices_user_agent (user_id, user_agent_hash)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
CREATE TABLE IF NOT EXISTS join_requests (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
user_id INT NOT NULL,
|
||
company_id INT NOT NULL,
|
||
status ENUM('PENDING', 'APPROVED', 'REJECTED') NOT NULL DEFAULT 'PENDING',
|
||
requested_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
reviewed_at TIMESTAMP NULL,
|
||
reviewed_by INT NULL,
|
||
review_comment TEXT,
|
||
INDEX idx_join_requests_company_status (company_id, status)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
CREATE TABLE IF NOT EXISTS login_logs (
|
||
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
||
user_id INT NULL,
|
||
email VARCHAR(255) NOT NULL,
|
||
status ENUM('SUCCESS', 'FAILURE') NOT NULL,
|
||
failure_reason VARCHAR(100),
|
||
user_agent TEXT,
|
||
login_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
INDEX idx_login_logs_user_id (user_id),
|
||
INDEX idx_login_logs_login_at (login_at)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
CREATE TABLE IF NOT EXISTS activity_logs (
|
||
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
||
user_id INT NOT NULL,
|
||
action_type VARCHAR(100) NOT NULL,
|
||
action_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
resource_id VARCHAR(255),
|
||
status ENUM('SUCCESS', 'FAILURE') NOT NULL DEFAULT 'SUCCESS',
|
||
details JSON,
|
||
INDEX idx_activity_logs_user_at (user_id, action_at)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
CREATE TABLE IF NOT EXISTS system_admin_logs (
|
||
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
||
admin_user_id INT NOT NULL,
|
||
action_type VARCHAR(100) NOT NULL,
|
||
target_id VARCHAR(255),
|
||
action_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
details JSON,
|
||
INDEX idx_system_admin_logs_admin_at (admin_user_id, action_at)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
CREATE TABLE IF NOT EXISTS support_requests (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
company_id INT NULL,
|
||
user_email VARCHAR(255) NOT NULL,
|
||
subject VARCHAR(255) NOT NULL,
|
||
message TEXT NOT NULL,
|
||
status ENUM('NEW', 'REVIEWED', 'RESOLVED', 'DELETED') NOT NULL DEFAULT 'NEW',
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
reviewed_at TIMESTAMP NULL,
|
||
reviewed_by INT NULL,
|
||
review_notes TEXT,
|
||
INDEX idx_support_requests_status_at (status, created_at)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
CREATE TABLE IF NOT EXISTS user_consents (
|
||
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
||
user_id INT NOT NULL,
|
||
terms_version VARCHAR(20) NOT NULL,
|
||
terms_agreed BOOLEAN NOT NULL,
|
||
privacy_agreed BOOLEAN NOT NULL,
|
||
marketing_agreed BOOLEAN NOT NULL DEFAULT FALSE,
|
||
agreed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE KEY uq_user_consents_version (user_id, terms_version)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
|
||
/* --------- 1-2. 프로젝트 그룹 --------- */
|
||
|
||
-- projects: 설계 프로젝트
|
||
CREATE TABLE IF NOT EXISTS projects (
|
||
id CHAR(36) PRIMARY KEY COMMENT 'UUID',
|
||
user_id INT NOT NULL, -- 프로젝트 소유자 (FK는 later)
|
||
company_id INT NOT NULL, -- 소속 회사 (FK는 later)
|
||
name VARCHAR(255) NOT NULL,
|
||
region VARCHAR(100), -- 지역명 (울진군 금강송면)
|
||
road_type VARCHAR(100), -- 임도 종류 (간선임도, 지선임도, 산불진화임도, 계류보전)
|
||
project_year INT, -- 사업 연도
|
||
estimated_length_m FLOAT, -- 추정 연장 (m)
|
||
memo TEXT,
|
||
status VARCHAR(50) DEFAULT 'NEW', -- NEW, FILE_UPLOADED, WF1_ANALYZING, ..., DONE
|
||
crs_epsg INT DEFAULT 5178, -- 좌표계 (한국 표준)
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
deleted_at TIMESTAMP NULL DEFAULT NULL,
|
||
storage_path VARCHAR(500) -- storage/회사/사용자/project_uuid
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- project_versions: 프로젝트 버전 이력
|
||
CREATE TABLE IF NOT EXISTS project_versions (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
project_id CHAR(36) NOT NULL, -- FK는 later
|
||
version_num INT NOT NULL,
|
||
snapshot_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
status VARCHAR(50),
|
||
data JSON, -- 설계 데이터 스냅샷
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
|
||
/* --------- 1-3. 파일 & 입력 데이터 그룹 --------- */
|
||
|
||
-- input_files: 사용자 업로드 원본 파일
|
||
CREATE TABLE IF NOT EXISTS input_files (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
project_id CHAR(36) NOT NULL, -- FK는 later
|
||
file_type VARCHAR(50), -- las, tif, tfw, prj, dxf, dwg, other
|
||
original_filename VARCHAR(255) NOT NULL,
|
||
raw_file_path VARCHAR(500) NOT NULL, -- storage/.../B03_FileInput/input/...
|
||
file_size_mb FLOAT,
|
||
upload_by INT, -- FK는 later
|
||
upload_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
crs_epsg INT,
|
||
metadata JSON, -- resolution_m, data_range, ...
|
||
status VARCHAR(50) DEFAULT 'UPLOADED' -- UPLOADED, PROCESSED, ARCHIVED
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- processed_point_cloud: 변환된 포인트클라우드 데이터
|
||
CREATE TABLE IF NOT EXISTS processed_point_cloud (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
input_file_id INT NOT NULL, -- FK는 later
|
||
project_id CHAR(36) NOT NULL, -- FK는 later
|
||
process_type VARCHAR(50), -- filtered, sampled, classified
|
||
processed_file_path VARCHAR(500), -- storage/.../B04_wf1_Surface/processed/...
|
||
converted_format VARCHAR(50), -- ply, laz, las 등
|
||
converted_file_path VARCHAR(500), -- storage/.../B04_wf1_Surface/processed/...
|
||
point_count INT,
|
||
min_z FLOAT,
|
||
max_z FLOAT,
|
||
mean_z FLOAT,
|
||
x_min FLOAT,
|
||
x_max FLOAT,
|
||
y_min FLOAT,
|
||
y_max FLOAT,
|
||
density_per_sqm FLOAT,
|
||
classification_summary JSON, -- ground, vegetation, building, ...
|
||
processing_params JSON,
|
||
processed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
status VARCHAR(50) DEFAULT 'PROCESSING' -- PROCESSING, COMPLETE, FAILED
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
|
||
/* --------- 1-4. 지표면 & 분석 결과 그룹 --------- */
|
||
|
||
-- surface_models: WF1 지표면 분석 결과
|
||
CREATE TABLE IF NOT EXISTS surface_models (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
project_id CHAR(36) NOT NULL, -- FK는 later
|
||
model_type VARCHAR(50), -- dem_grid, tin, mesh_triangulated, contour_lines
|
||
source_file_id INT, -- FK는 later
|
||
processed_cloud_id INT, -- FK는 later
|
||
status VARCHAR(50) DEFAULT 'PROCESSING', -- PROCESSING, COMPLETE, FAILED
|
||
crs_epsg INT,
|
||
resolution_m FLOAT,
|
||
model_file_path VARCHAR(500), -- storage/.../B04_wf1_Surface/models/...
|
||
generation_params JSON, -- filter_type, algorithm, params
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
completed_at TIMESTAMP NULL DEFAULT NULL
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- terrain_layers: WF1 지형 레이어
|
||
CREATE TABLE IF NOT EXISTS terrain_layers (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
surface_model_id INT NOT NULL, -- FK는 later
|
||
layer_name VARCHAR(100), -- 지표, 제1층, 제2층
|
||
geometry_type VARCHAR(50), -- POINTCLOUD, GRID, MESH, CONTOUR
|
||
layer_file_path VARCHAR(500), -- storage/.../B04_wf1_Surface/models/...
|
||
file_format VARCHAR(50), -- geojson, geotiff, ply, obj
|
||
file_size_mb FLOAT,
|
||
statistics JSON, -- min_z, max_z, mean_slope, point_count
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
|
||
/* --------- 1-5. 경로 & 설계 그룹 --------- */
|
||
|
||
-- routes: WF2 경로 설계 결과
|
||
CREATE TABLE IF NOT EXISTS routes (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
project_id CHAR(36) NOT NULL, -- FK는 later
|
||
surface_model_id INT, -- FK는 later
|
||
status VARCHAR(50) DEFAULT 'DRAFT', -- DRAFT, CONFIRMED, ARCHIVED
|
||
start_chainage_m FLOAT,
|
||
end_chainage_m FLOAT,
|
||
total_length_m FLOAT,
|
||
grade_percent JSON, -- [2.5, 1.8, 3.2, ...] 각 구간 경사도
|
||
constraints JSON, -- max_grade, min_radius, avoidance_zones
|
||
algorithm_params JSON, -- cost_weights, ...
|
||
route_data_path VARCHAR(500), -- storage/.../B05_wf2_Route/route/...
|
||
computed_at TIMESTAMP NULL DEFAULT NULL,
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- route_points: 웹 렌더링용 경로 포인트 샘플
|
||
CREATE TABLE IF NOT EXISTS route_points (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
route_id INT NOT NULL, -- FK는 later
|
||
chainage_m FLOAT,
|
||
elevation_m FLOAT,
|
||
slope_percent FLOAT,
|
||
sequence_num INT
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- route_statistics: 경로 통계
|
||
CREATE TABLE IF NOT EXISTS route_statistics (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
route_id INT NOT NULL, -- FK는 later
|
||
min_slope FLOAT,
|
||
max_slope FLOAT,
|
||
mean_slope FLOAT,
|
||
cut_volume_m3 FLOAT,
|
||
fill_volume_m3 FLOAT,
|
||
tree_cutting_volume FLOAT,
|
||
cost_score FLOAT
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
|
||
/* --------- 1-6. 종단면 & 횡단면 그룹 --------- */
|
||
|
||
-- longitudinal_sections: WF3 종단면
|
||
CREATE TABLE IF NOT EXISTS longitudinal_sections (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
project_id CHAR(36) NOT NULL, -- FK는 later
|
||
route_id INT NOT NULL, -- FK는 later
|
||
computed_at TIMESTAMP NULL DEFAULT NULL,
|
||
data JSON, -- chainages, elevations, grades, design_elevations
|
||
longitudinal_file_path VARCHAR(500), -- storage/.../B06_wf3_ProfileCross/longitudinal/...
|
||
status VARCHAR(50) DEFAULT 'DRAFT' -- DRAFT, CONFIRMED
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- cross_sections: WF3 횡단면
|
||
CREATE TABLE IF NOT EXISTS cross_sections (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
project_id CHAR(36) NOT NULL, -- FK는 later
|
||
route_id INT NOT NULL, -- FK는 later
|
||
chainage_m FLOAT,
|
||
sequence_num INT,
|
||
data JSON, -- left_slope, right_slope, width_m, cut_volume_m3, fill_volume_m3, structures, notes
|
||
cross_section_file_path VARCHAR(500), -- storage/.../B06_wf3_ProfileCross/cross_sections/...
|
||
status VARCHAR(50) DEFAULT 'DRAFT' -- DRAFT, CONFIRMED
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
|
||
/* --------- 1-7. 설계 & 구조물 그룹 --------- */
|
||
|
||
-- structures: WF4 상세 설계 - 구조물
|
||
CREATE TABLE IF NOT EXISTS structures (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
project_id CHAR(36) NOT NULL, -- FK는 later
|
||
cross_section_id INT, -- FK는 later
|
||
structure_type VARCHAR(100), -- 낙석방지책, 돌붙임, 계간수로, 낙차공
|
||
chainage_m FLOAT,
|
||
location VARCHAR(50), -- LEFT, CENTER, RIGHT
|
||
length_m FLOAT,
|
||
width_m FLOAT,
|
||
height_m FLOAT,
|
||
material VARCHAR(100), -- 강재, 콘크리트, 목재, 돌
|
||
quantity INT,
|
||
unit_price FLOAT,
|
||
design_notes JSON,
|
||
structure_data_path VARCHAR(500), -- storage/.../B07_wf4_DesignDetail/structures/...
|
||
last_modified_by INT, -- FK는 later
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
|
||
/* --------- 1-8. 수량 산출 그룹 --------- */
|
||
|
||
-- quantity_items: WF5 수량 산출
|
||
CREATE TABLE IF NOT EXISTS quantity_items (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
project_id CHAR(36) NOT NULL, -- FK는 later
|
||
category VARCHAR(100), -- 토공, 구조물, 포장, 배수, 녹화, 안전시설
|
||
item_name VARCHAR(255),
|
||
unit VARCHAR(50), -- m3, 개, m, m2
|
||
quantity_design FLOAT,
|
||
quantity_actual FLOAT,
|
||
unit_price FLOAT,
|
||
total_price FLOAT, -- quantity_actual × unit_price
|
||
standard_reference VARCHAR(255),
|
||
computed_at TIMESTAMP NULL DEFAULT NULL,
|
||
quantity_data_path VARCHAR(500), -- storage/.../B08_wf5_Quantity/quantities/...
|
||
data JSON -- formula, source, ...
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
|
||
/* --------- 1-9. 산출물 & 문서 그룹 --------- */
|
||
|
||
-- outputs: WF6 최종 산출물 세트
|
||
CREATE TABLE IF NOT EXISTS outputs (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
project_id CHAR(36) NOT NULL, -- FK는 later
|
||
output_type VARCHAR(100), -- estimation_excel, drawing_dxf, report_pdf, all_bundle
|
||
status VARCHAR(50) DEFAULT 'GENERATING', -- GENERATING, COMPLETE, FAILED
|
||
generated_by INT, -- FK는 later
|
||
generated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
version INT DEFAULT 1,
|
||
outputs_directory_path VARCHAR(500), -- storage/.../B09_wf6_Estimation/v1/
|
||
metadata JSON -- template_used, company_name, project_name, total_cost, generation_time_sec
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- output_files: 최종 산출 파일
|
||
CREATE TABLE IF NOT EXISTS output_files (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
output_id INT NOT NULL, -- FK는 later
|
||
file_type VARCHAR(50), -- xlsx, pdf, dxf, dwg, json, zip
|
||
original_filename VARCHAR(255),
|
||
output_file_path VARCHAR(500), -- storage/.../B09_wf6_Estimation/v1/...
|
||
file_size_mb FLOAT,
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
download_count INT DEFAULT 0
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
|
||
/* --------- 1-10. 변경 이력 & 감시 그룹 --------- */
|
||
|
||
-- audit_logs: 보안 감시 로그
|
||
CREATE TABLE IF NOT EXISTS audit_logs (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
user_id INT, -- FK는 later
|
||
action VARCHAR(100), -- CREATE, READ, UPDATE, DELETE, EXPORT, DOWNLOAD
|
||
entity_type VARCHAR(100), -- projects, routes, structures, outputs
|
||
entity_id VARCHAR(36),
|
||
timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
ip_address VARCHAR(45),
|
||
details JSON -- old_value, new_value, reason
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- change_logs: 설계 변경 이력
|
||
-- [★오류 수정]: 외래키 ON DELETE SET NULL 조건을 위해 changed_by 컬럼을 NULL 가능으로 변경
|
||
CREATE TABLE IF NOT EXISTS change_logs (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
project_id CHAR(36) NOT NULL, -- FK는 later
|
||
changed_by INT NULL, -- NOT NULL 에서 NULL로 변경 완료
|
||
changed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
entity_type VARCHAR(100), -- routes, cross_sections, structures, quantity_items
|
||
entity_id INT,
|
||
old_value JSON,
|
||
new_value JSON,
|
||
reason VARCHAR(255)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
|
||
-- =============================================================================
|
||
-- 2. 외래키(FK) 제약조건 추가
|
||
-- =============================================================================
|
||
|
||
ALTER TABLE users
|
||
ADD CONSTRAINT fk_users_company_id
|
||
FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL;
|
||
|
||
ALTER TABLE companies
|
||
ADD CONSTRAINT fk_companies_created_by
|
||
FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
|
||
ADD CONSTRAINT fk_companies_master_user_id
|
||
FOREIGN KEY (master_user_id) REFERENCES users(id) ON DELETE SET NULL;
|
||
|
||
ALTER TABLE sessions
|
||
ADD CONSTRAINT fk_sessions_user_id FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;
|
||
|
||
ALTER TABLE email_otps
|
||
ADD CONSTRAINT fk_email_otps_user_id FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;
|
||
|
||
ALTER TABLE trusted_devices
|
||
ADD CONSTRAINT fk_trusted_devices_user_id FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;
|
||
|
||
ALTER TABLE join_requests
|
||
ADD CONSTRAINT fk_join_requests_user_id FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
|
||
ADD CONSTRAINT fk_join_requests_company_id FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
|
||
ADD CONSTRAINT fk_join_requests_reviewed_by FOREIGN KEY (reviewed_by) REFERENCES users(id) ON DELETE SET NULL;
|
||
|
||
ALTER TABLE login_logs
|
||
ADD CONSTRAINT fk_login_logs_user_id FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL;
|
||
|
||
ALTER TABLE activity_logs
|
||
ADD CONSTRAINT fk_activity_logs_user_id FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;
|
||
|
||
ALTER TABLE system_admin_logs
|
||
ADD CONSTRAINT fk_system_admin_logs_admin_user_id FOREIGN KEY (admin_user_id) REFERENCES users(id) ON DELETE CASCADE;
|
||
|
||
ALTER TABLE support_requests
|
||
ADD CONSTRAINT fk_support_requests_company_id FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL,
|
||
ADD CONSTRAINT fk_support_requests_reviewed_by FOREIGN KEY (reviewed_by) REFERENCES users(id) ON DELETE SET NULL;
|
||
|
||
ALTER TABLE user_consents
|
||
ADD CONSTRAINT fk_user_consents_user_id FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;
|
||
|
||
ALTER TABLE projects
|
||
ADD CONSTRAINT fk_projects_user_id
|
||
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT,
|
||
ADD CONSTRAINT fk_projects_company_id
|
||
FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE RESTRICT;
|
||
|
||
ALTER TABLE project_versions
|
||
ADD CONSTRAINT fk_project_versions_project_id
|
||
FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE;
|
||
|
||
ALTER TABLE input_files
|
||
ADD CONSTRAINT fk_input_files_project_id
|
||
FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
|
||
ADD CONSTRAINT fk_input_files_upload_by
|
||
FOREIGN KEY (upload_by) REFERENCES users(id) ON DELETE SET NULL;
|
||
|
||
ALTER TABLE processed_point_cloud
|
||
ADD CONSTRAINT fk_processed_point_cloud_input_file_id
|
||
FOREIGN KEY (input_file_id) REFERENCES input_files(id) ON DELETE CASCADE,
|
||
ADD CONSTRAINT fk_processed_point_cloud_project_id
|
||
FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE;
|
||
|
||
ALTER TABLE surface_models
|
||
ADD CONSTRAINT fk_surface_models_project_id
|
||
FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
|
||
ADD CONSTRAINT fk_surface_models_source_file_id
|
||
FOREIGN KEY (source_file_id) REFERENCES input_files(id) ON DELETE SET NULL,
|
||
ADD CONSTRAINT fk_surface_models_processed_cloud_id
|
||
FOREIGN KEY (processed_cloud_id) REFERENCES processed_point_cloud(id) ON DELETE SET NULL;
|
||
|
||
ALTER TABLE terrain_layers
|
||
ADD CONSTRAINT fk_terrain_layers_surface_model_id
|
||
FOREIGN KEY (surface_model_id) REFERENCES surface_models(id) ON DELETE CASCADE;
|
||
|
||
ALTER TABLE routes
|
||
ADD CONSTRAINT fk_routes_project_id
|
||
FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
|
||
ADD CONSTRAINT fk_routes_surface_model_id
|
||
FOREIGN KEY (surface_model_id) REFERENCES surface_models(id) ON DELETE SET NULL;
|
||
|
||
ALTER TABLE route_points
|
||
ADD CONSTRAINT fk_route_points_route_id
|
||
FOREIGN KEY (route_id) REFERENCES routes(id) ON DELETE CASCADE;
|
||
|
||
ALTER TABLE route_statistics
|
||
ADD CONSTRAINT fk_route_statistics_route_id
|
||
FOREIGN KEY (route_id) REFERENCES routes(id) ON DELETE CASCADE;
|
||
|
||
ALTER TABLE longitudinal_sections
|
||
ADD CONSTRAINT fk_longitudinal_sections_project_id
|
||
FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
|
||
ADD CONSTRAINT fk_longitudinal_sections_route_id
|
||
FOREIGN KEY (route_id) REFERENCES routes(id) ON DELETE CASCADE;
|
||
|
||
ALTER TABLE cross_sections
|
||
ADD CONSTRAINT fk_cross_sections_project_id
|
||
FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
|
||
ADD CONSTRAINT fk_cross_sections_route_id
|
||
FOREIGN KEY (route_id) REFERENCES routes(id) ON DELETE CASCADE;
|
||
|
||
ALTER TABLE structures
|
||
ADD CONSTRAINT fk_structures_project_id
|
||
FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
|
||
ADD CONSTRAINT fk_structures_cross_section_id
|
||
FOREIGN KEY (cross_section_id) REFERENCES cross_sections(id) ON DELETE SET NULL,
|
||
ADD CONSTRAINT fk_structures_last_modified_by
|
||
FOREIGN KEY (last_modified_by) REFERENCES users(id) ON DELETE SET NULL;
|
||
|
||
ALTER TABLE quantity_items
|
||
ADD CONSTRAINT fk_quantity_items_project_id
|
||
FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE;
|
||
|
||
ALTER TABLE outputs
|
||
ADD CONSTRAINT fk_outputs_project_id
|
||
FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
|
||
ADD CONSTRAINT fk_outputs_generated_by
|
||
FOREIGN KEY (generated_by) REFERENCES users(id) ON DELETE SET NULL;
|
||
|
||
ALTER TABLE output_files
|
||
ADD CONSTRAINT fk_output_files_output_id
|
||
FOREIGN KEY (output_id) REFERENCES outputs(id) ON DELETE CASCADE;
|
||
|
||
ALTER TABLE audit_logs
|
||
ADD CONSTRAINT fk_audit_logs_user_id
|
||
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL;
|
||
|
||
ALTER TABLE change_logs
|
||
ADD CONSTRAINT fk_change_logs_project_id
|
||
FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
|
||
ADD CONSTRAINT fk_change_logs_changed_by
|
||
FOREIGN KEY (changed_by) REFERENCES users(id) ON DELETE SET NULL;
|
||
|
||
|
||
-- =============================================================================
|
||
-- 3. 인덱스 생성 (성능 최적화)
|
||
-- =============================================================================
|
||
|
||
-- 사용자/회사 인덱싱
|
||
CREATE INDEX idx_users_email ON users(email);
|
||
CREATE INDEX idx_users_company_id ON users(company_id);
|
||
CREATE INDEX idx_companies_name ON companies(name);
|
||
|
||
-- 프로젝트 인덱싱
|
||
CREATE INDEX idx_projects_user_id ON projects(user_id);
|
||
CREATE INDEX idx_projects_company_id ON projects(company_id);
|
||
CREATE INDEX idx_projects_status ON projects(status);
|
||
CREATE INDEX idx_projects_created_at ON projects(created_at DESC);
|
||
CREATE INDEX idx_projects_storage_path ON projects(storage_path);
|
||
|
||
-- 파일 인덱싱
|
||
CREATE INDEX idx_input_files_project_id ON input_files(project_id);
|
||
CREATE INDEX idx_input_files_file_type ON input_files(file_type);
|
||
CREATE INDEX idx_input_files_raw_file_path ON input_files(raw_file_path);
|
||
CREATE INDEX idx_processed_point_cloud_input_file_id ON processed_point_cloud(input_file_id);
|
||
CREATE INDEX idx_processed_point_cloud_project_id ON processed_point_cloud(project_id);
|
||
CREATE INDEX idx_processed_point_cloud_converted_file_path ON processed_point_cloud(converted_file_path);
|
||
|
||
-- 지표면 모델 인덱싱
|
||
CREATE INDEX idx_surface_models_project_id ON surface_models(project_id);
|
||
CREATE INDEX idx_surface_models_status ON surface_models(status);
|
||
CREATE INDEX idx_surface_models_model_file_path ON surface_models(model_file_path);
|
||
CREATE INDEX idx_terrain_layers_surface_model_id ON terrain_layers(surface_model_id);
|
||
CREATE INDEX idx_terrain_layers_layer_file_path ON terrain_layers(layer_file_path);
|
||
|
||
-- 경로 인덱싱
|
||
CREATE INDEX idx_routes_project_id ON routes(project_id);
|
||
CREATE INDEX idx_routes_status ON routes(status);
|
||
CREATE INDEX idx_routes_route_data_path ON routes(route_data_path);
|
||
CREATE INDEX idx_route_points_route_id ON route_points(route_id);
|
||
CREATE INDEX idx_route_statistics_route_id ON route_statistics(route_id);
|
||
|
||
-- 단면 인덱싱
|
||
CREATE INDEX idx_longitudinal_sections_project_id ON longitudinal_sections(project_id);
|
||
CREATE INDEX idx_longitudinal_sections_route_id ON longitudinal_sections(route_id);
|
||
CREATE INDEX idx_longitudinal_sections_file_path ON longitudinal_sections(longitudinal_file_path);
|
||
CREATE INDEX idx_cross_sections_project_id ON cross_sections(project_id);
|
||
CREATE INDEX idx_cross_sections_route_id ON cross_sections(route_id);
|
||
CREATE INDEX idx_cross_sections_chainage_m ON cross_sections(chainage_m);
|
||
CREATE INDEX idx_cross_sections_file_path ON cross_sections(cross_section_file_path);
|
||
|
||
-- 구조물/수량 인덱싱
|
||
CREATE INDEX idx_structures_project_id ON structures(project_id);
|
||
CREATE INDEX idx_structures_cross_section_id ON structures(cross_section_id);
|
||
CREATE INDEX idx_structures_file_path ON structures(structure_data_path);
|
||
CREATE INDEX idx_quantity_items_project_id ON quantity_items(project_id);
|
||
CREATE INDEX idx_quantity_items_category ON quantity_items(category);
|
||
CREATE INDEX idx_quantity_items_file_path ON quantity_items(quantity_data_path);
|
||
|
||
-- 산출물 인덱싱
|
||
CREATE INDEX idx_outputs_project_id ON outputs(project_id);
|
||
CREATE INDEX INDEX idx_outputs_version ON outputs(version);
|
||
CREATE INDEX idx_outputs_directory_path ON outputs(outputs_directory_path);
|
||
CREATE INDEX idx_output_files_output_id ON output_files(output_id);
|
||
CREATE INDEX idx_output_files_output_file_path ON output_files(output_file_path);
|
||
|
||
-- 감시/이력 인덱싱
|
||
CREATE INDEX idx_audit_logs_user_id ON audit_logs(user_id);
|
||
CREATE INDEX idx_audit_logs_timestamp ON audit_logs(timestamp DESC);
|
||
CREATE INDEX idx_audit_logs_entity_type ON audit_logs(entity_type);
|
||
CREATE INDEX idx_change_logs_project_id ON change_logs(project_id);
|
||
CREATE INDEX idx_change_logs_changed_by ON change_logs(changed_by);
|
||
CREATE INDEX idx_change_logs_changed_at ON change_logs(changed_at DESC);
|
||
|
||
-- =============================================================================
|
||
-- 5. 생성 완료 메시지
|
||
-- =============================================================================
|
||
|
||
-- 모든 테이블과 인덱스가 정상적으로 생성되었으면 아래의 SELECT를 실행하여 확인할 수 있습니다:
|
||
-- SELECT table_name FROM information_schema.tables WHERE table_schema='public';
|
||
-- SELECT indexname FROM pg_indexes WHERE schemaname='public';
|