-- Hyungi Technical Korea - Database Schema v2
-- 개선 사항:
-- 1. 모든 테이블 및 컬럼 이름을 snake_case로 통일
-- 2. 데이터 무결성 강화를 위한 외래 키(Foreign Key) 제약조건 추가
-- 3. 유사/중복 테이블 통합 및 정리 제안 (예: tasks, work_types)
-- 4. 컬럼의 기본값(Default), 코멘트 등 명확화

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+09:00";

-- =================================================================
-- 1. 인증 및 사용자 관련 (Auth Domain)
-- =================================================================

-- 사용자 계정 정보
CREATE TABLE `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(100) NOT NULL,
  `password` varchar(255) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `role` varchar(30) DEFAULT 'user' COMMENT '역할 (system, admin, leader, user)',
  `access_level` varchar(30) DEFAULT NULL COMMENT '접근 레벨 (레거시 필드, role로 통합 고려)',
  `worker_id` int(11) DEFAULT NULL COMMENT '연결된 작업자 ID',
  `is_active` tinyint(1) DEFAULT 1 COMMENT '계정 활성화 여부',
  `last_login_at` datetime DEFAULT NULL COMMENT '마지막 로그인 시간',
  `password_changed_at` datetime DEFAULT NULL,
  `failed_login_attempts` int(11) DEFAULT 0,
  `locked_until` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `username` (`username`),
  KEY `fk_users_worker_id` (`worker_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 로그인 이력
CREATE TABLE `login_logs` (
  `log_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `login_time` datetime DEFAULT current_timestamp(),
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `login_status` enum('success','failed','locked') DEFAULT 'success',
  `failure_reason` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`log_id`),
  KEY `fk_login_logs_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 비밀번호 변경 이력
CREATE TABLE `password_change_logs` (
  `log_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `changed_by_user_id` int(11) DEFAULT NULL COMMENT '누가 변경했는지 (관리자)',
  `changed_at` datetime DEFAULT current_timestamp(),
  `change_type` enum('self','admin','reset','initial') DEFAULT 'self',
  `ip_address` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`log_id`),
  KEY `fk_pw_change_user_id` (`user_id`),
  KEY `fk_pw_change_changed_by` (`changed_by_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =================================================================
-- 2. 기초 정보 (Master Data)
-- =================================================================

-- 프로젝트 정보
CREATE TABLE `projects` (
  `project_id` int(11) NOT NULL AUTO_INCREMENT,
  `job_no` varchar(50) NOT NULL,
  `project_name` varchar(255) NOT NULL,
  `contract_date` date DEFAULT NULL,
  `due_date` date DEFAULT NULL,
  `delivery_method` varchar(100) DEFAULT NULL,
  `site` varchar(100) DEFAULT NULL,
  `pm` varchar(100) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`project_id`),
  UNIQUE KEY `job_no` (`job_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 작업자(인력) 정보
CREATE TABLE `workers` (
  `worker_id` int(11) NOT NULL AUTO_INCREMENT,
  `worker_name` varchar(100) NOT NULL,
  `job_type` varchar(100) DEFAULT NULL COMMENT '직종',
  `join_date` date DEFAULT NULL COMMENT '입사일',
  `status` varchar(20) DEFAULT 'active' COMMENT '상태 (active, inactive)',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`worker_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 작업자 그룹 (팀)
CREATE TABLE `worker_groups` (
  `group_id` int(11) NOT NULL AUTO_INCREMENT,
  `group_name` varchar(100) DEFAULT NULL,
  `group_leader_id` int(11) NOT NULL COMMENT '그룹장 user_id',
  `worker_id` int(11) NOT NULL COMMENT '소속 작업자 worker_id',
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`group_id`),
  KEY `fk_w_groups_leader_id` (`group_leader_id`),
  KEY `fk_w_groups_worker_id` (`worker_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 표준 작업(공수) 종류 (기존 Tasks 와 work_types 통합 제안)
CREATE TABLE `tasks` (
  `task_id` int(11) NOT NULL AUTO_INCREMENT,
  `task_category` varchar(255) NOT NULL COMMENT '작업 대분류 (예: PKG, Vessel)',
  `task_subcategory` varchar(255) DEFAULT NULL COMMENT '작업 중분류 (예: Pipe Pre-Fabrication)',
  `task_name` varchar(255) NOT NULL COMMENT '실제 작업명 (예: 취부&용접)',
  `description` text DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`task_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 파이프 사양(Spec) 정보
CREATE TABLE `pipe_specs` (
  `spec_id` int(11) NOT NULL AUTO_INCREMENT,
  `material` varchar(50) NOT NULL COMMENT '재질 (예: SS400, STS304)',
  `diameter_in` varchar(10) NOT NULL COMMENT '직경 (inch, 예: 2")',
  `schedule` varchar(50) NOT NULL COMMENT '스케줄 (예: STD, SCH10, SCH40)',
  PRIMARY KEY (`spec_id`),
  UNIQUE KEY `uk_pipe_specs` (`material`,`diameter_in`,`schedule`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 공장 설비/장비 목록
CREATE TABLE `equipment_list` (
  `equipment_id` int(11) NOT NULL AUTO_INCREMENT,
  `factory_id` int(11) DEFAULT NULL COMMENT '소속 공장 ID',
  `equipment_name` varchar(255) NOT NULL,
  `model` varchar(100) DEFAULT NULL,
  `status` varchar(50) DEFAULT 'operational',
  `purchase_date` date DEFAULT NULL,
  `description` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`equipment_id`),
  KEY `fk_equip_factory_id` (`factory_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 공장 구역 정보
CREATE TABLE `factory_info` (
  `factory_id` int(11) NOT NULL AUTO_INCREMENT,
  `factory_name` varchar(255) NOT NULL,
  `address` varchar(255) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `map_image_url` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`factory_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 코드 정의 (기존 IssueTypes, error_types 등을 통합 관리)
CREATE TABLE `code_types` (
    `code_type_id` VARCHAR(50) NOT NULL COMMENT '코드 타입 ID (예: ISSUE_TYPE, ERROR_TYPE)',
    `code_type_name` VARCHAR(100) NOT NULL COMMENT '코드 타입명 (예: 이슈 유형, 에러 유형)',
    `description` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`code_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `codes` (
    `code_id` INT NOT NULL AUTO_INCREMENT,
    `code_type_id` VARCHAR(50) NOT NULL COMMENT '참조하는 코드 타입 ID',
    `code_value` VARCHAR(100) NOT NULL COMMENT '코드 값 (예: design_miss)',
    `code_name` VARCHAR(100) NOT NULL COMMENT '코드 표시명 (예: 설계 미스)',
    `code_order` INT DEFAULT 0 COMMENT '정렬 순서',
    `is_active` TINYINT(1) DEFAULT 1,
    `description` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`code_id`),
    UNIQUE KEY `uk_code` (`code_type_id`, `code_value`),
    KEY `fk_codes_code_type_id` (`code_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =================================================================
-- 3. 업무 데이터 (Transactional Data)
-- =================================================================

-- 일일 작업 보고
CREATE TABLE `daily_work_reports` (
  `report_id` int(11) NOT NULL AUTO_INCREMENT,
  `report_date` date NOT NULL COMMENT '작업 날짜',
  `worker_id` int(11) NOT NULL COMMENT '작업자 ID',
  `project_id` int(11) NOT NULL COMMENT '프로젝트 ID',
  `task_id` int(11) NOT NULL COMMENT '작업 ID',
  `work_hours` decimal(4,2) NOT NULL COMMENT '작업 시간',
  `is_error` tinyint(1) NOT NULL DEFAULT 0 COMMENT '에러 여부 (0: 정상, 1: 에러)',
  `error_type_code_id` int(11) DEFAULT NULL COMMENT '에러 유형 코드 ID (codes 테이블 참조)',
  `created_by_user_id` int(11) NOT NULL COMMENT '작성자 user_id',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`report_id`),
  KEY `fk_dwr_worker_id` (`worker_id`),
  KEY `fk_dwr_project_id` (`project_id`),
  KEY `fk_dwr_task_id` (`task_id`),
  KEY `fk_dwr_error_type` (`error_type_code_id`),
  KEY `fk_dwr_created_by` (`created_by_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 일일 이슈 보고
CREATE TABLE `daily_issue_reports` (
  `issue_report_id` int(11) NOT NULL AUTO_INCREMENT,
  `report_date` date NOT NULL,
  `worker_id` int(11) NOT NULL,
  `project_id` int(11) NOT NULL,
  `issue_type_code_id` int(11) DEFAULT NULL COMMENT '이슈 유형 코드 ID (codes 테이블 참조)',
  `description` text DEFAULT NULL,
  `start_time` time NOT NULL,
  `end_time` time NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`issue_report_id`),
  KEY `fk_dir_worker_id` (`worker_id`),
  KEY `fk_dir_project_id` (`project_id`),
  KEY `fk_dir_issue_type` (`issue_type_code_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 절단 계획
CREATE TABLE `cutting_plans` (
  `cutting_plan_id` int(11) NOT NULL AUTO_INCREMENT,
  `project_id` int(11) NOT NULL,
  `spec_id` int(11) NOT NULL COMMENT '파이프 사양 ID',
  `drawing_name` varchar(255) NOT NULL,
  `area_number` varchar(100) DEFAULT NULL,
  `spool_number` varchar(255) DEFAULT NULL,
  `length` decimal(10,2) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`cutting_plan_id`),
  KEY `fk_cp_project_id` (`project_id`),
  KEY `fk_cp_spec_id` (`spec_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =================================================================
-- 외래 키(Foreign Key) 제약조건 설정
-- =================================================================

-- Auth Domain
ALTER TABLE `users`
  ADD CONSTRAINT `fk_users_worker_id` FOREIGN KEY (`worker_id`) REFERENCES `workers` (`worker_id`) ON DELETE SET NULL;
ALTER TABLE `login_logs`
  ADD CONSTRAINT `fk_login_logs_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE SET NULL;
ALTER TABLE `password_change_logs`
  ADD CONSTRAINT `fk_pw_change_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_pw_change_changed_by` FOREIGN KEY (`changed_by_user_id`) REFERENCES `users` (`user_id`) ON DELETE SET NULL;

-- Master Data
ALTER TABLE `worker_groups`
  ADD CONSTRAINT `fk_w_groups_leader_id` FOREIGN KEY (`group_leader_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_w_groups_worker_id` FOREIGN KEY (`worker_id`) REFERENCES `workers` (`worker_id`) ON DELETE CASCADE;
ALTER TABLE `equipment_list`
  ADD CONSTRAINT `fk_equip_factory_id` FOREIGN KEY (`factory_id`) REFERENCES `factory_info` (`factory_id`) ON DELETE SET NULL;
ALTER TABLE `codes`
  ADD CONSTRAINT `fk_codes_code_type_id` FOREIGN KEY (`code_type_id`) REFERENCES `code_types` (`code_type_id`) ON DELETE CASCADE;
  
-- Transactional Data
ALTER TABLE `daily_work_reports`
  ADD CONSTRAINT `fk_dwr_worker_id` FOREIGN KEY (`worker_id`) REFERENCES `workers` (`worker_id`) ON DELETE RESTRICT,
  ADD CONSTRAINT `fk_dwr_project_id` FOREIGN KEY (`project_id`) REFERENCES `projects` (`project_id`) ON DELETE RESTRICT,
  ADD CONSTRAINT `fk_dwr_task_id` FOREIGN KEY (`task_id`) REFERENCES `tasks` (`task_id`) ON DELETE RESTRICT,
  ADD CONSTRAINT `fk_dwr_error_type` FOREIGN KEY (`error_type_code_id`) REFERENCES `codes` (`code_id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_dwr_created_by` FOREIGN KEY (`created_by_user_id`) REFERENCES `users` (`user_id`) ON DELETE RESTRICT;
ALTER TABLE `daily_issue_reports`
  ADD CONSTRAINT `fk_dir_worker_id` FOREIGN KEY (`worker_id`) REFERENCES `workers` (`worker_id`) ON DELETE RESTRICT,
  ADD CONSTRAINT `fk_dir_project_id` FOREIGN KEY (`project_id`) REFERENCES `projects` (`project_id`) ON DELETE RESTRICT,
  ADD CONSTRAINT `fk_dir_issue_type` FOREIGN KEY (`issue_type_code_id`) REFERENCES `codes` (`code_id`) ON DELETE SET NULL;
ALTER TABLE `cutting_plans`
  ADD CONSTRAINT `fk_cp_project_id` FOREIGN KEY (`project_id`) REFERENCES `projects` (`project_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_cp_spec_id` FOREIGN KEY (`spec_id`) REFERENCES `pipe_specs` (`spec_id`) ON DELETE RESTRICT;


COMMIT;

-- =================================================================
-- 기존 테이블 (Legacy) - 검토 후 마이그레이션 및 삭제 필요
-- =================================================================
/*
-- 기존 Tasks, IssueTypes, error_types 등은 `codes` 와 `code_types`로 통합 제안
-- 아래 테이블들은 `codes` 테이블로 데이터 마이그레이션 후 삭제 고려
CREATE TABLE `IssueTypes` ( ... );
CREATE TABLE `error_types` ( ... );
CREATE TABLE `work_status_types` ( ... );
CREATE TABLE `work_types` ( ... );

-- daily_work_reports 로 통합된 것으로 추정되는 레거시 테이블
CREATE TABLE `WorkReports` ( ... );
CREATE TABLE `daily_worker_summary` ( ... );
*/