-- =====================================================
-- 운영 DB → 테스트 서버 전체 데이터 Import 스크립트
--
-- 주의: 테스트 서버 데이터가 덮어씌워집니다!
-- 실행 전 백업 권장
-- =====================================================

SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO';

-- =====================================================
-- 1. work_attendance_types (근로 유형)
-- =====================================================
TRUNCATE TABLE work_attendance_types;
INSERT INTO work_attendance_types (id, type_code, type_name, description, is_active, created_at, updated_at) VALUES
(1, 'REGULAR', '정시근로', '8시간 정규 근무', 1, '2025-11-03 06:48:45', '2025-11-03 06:48:45'),
(2, 'OVERTIME', '연장근로', '8시간 초과 근무', 1, '2025-11-03 06:48:45', '2025-11-03 06:48:45'),
(3, 'PARTIAL', '부분근로', '8시간 미만 근무', 1, '2025-11-03 06:48:45', '2025-11-03 06:48:45'),
(4, 'VACATION', '휴가근로', '휴가와 함께하는 부분 근무', 1, '2025-11-03 06:48:45', '2025-11-03 06:48:45');

-- =====================================================
-- 2. vacation_types (휴가 유형)
-- 운영: hours_deduction → 테스트: deduct_days
-- 변환: 8시간=1일, 4시간=0.5일, 2시간=0.25일
-- =====================================================
TRUNCATE TABLE vacation_types;
INSERT INTO vacation_types (id, type_code, type_name, deduct_days, description, is_active, is_special, priority, is_system, created_at, updated_at) VALUES
(1, 'ANNUAL_FULL', '연차', 1.0, '하루 전체 연차', 1, 0, 1, 1, '2025-11-03 06:48:45', '2025-11-03 06:48:45'),
(2, 'ANNUAL_HALF', '반차', 0.5, '반일 연차', 1, 0, 2, 1, '2025-11-03 06:48:45', '2025-11-03 06:48:45'),
(3, 'ANNUAL_QUARTER', '반반차', 0.25, '1/4일 연차', 1, 0, 3, 1, '2025-11-03 06:48:45', '2025-11-03 06:48:45'),
(4, 'SICK_FULL', '병가', 1.0, '하루 전체 병가', 1, 0, 4, 1, '2025-11-03 06:48:45', '2025-11-03 06:48:45'),
(5, 'SICK_HALF', '반일병가', 0.5, '반일 병가', 1, 0, 5, 1, '2025-11-03 06:48:45', '2025-11-03 06:48:45');

-- =====================================================
-- 3. workers (작업자)
-- employment_status 추가 (기본값: employed)
-- =====================================================
TRUNCATE TABLE workers;
INSERT INTO workers (worker_id, worker_name, join_date, job_type, salary, annual_leave, status, created_at, updated_at, phone_number, email, hire_date, department, notes, employment_status) VALUES
(1, '김두수', '2025-04-01', '배관', 2200000.00, 15, 'active', '2025-04-15 13:23:17', '2025-04-15 13:23:17', NULL, NULL, NULL, NULL, NULL, 'employed'),
(2, '임영규', NULL, 'leader', NULL, NULL, 'active', '2025-04-15 13:23:17', '2025-04-15 13:23:17', NULL, NULL, NULL, NULL, NULL, 'employed'),
(3, '반치원', '2025-04-01', '배관', 2200000.00, 15, 'active', '2025-04-15 13:23:22', '2025-04-15 13:23:22', NULL, NULL, NULL, NULL, NULL, 'employed'),
(4, '황인용', '2025-04-01', '가공,조공', 2200000.00, 15, 'active', '2025-04-15 13:23:33', '2025-04-15 13:23:33', NULL, NULL, NULL, NULL, NULL, 'employed'),
(5, '표영진', '2025-04-01', '가공,조공', 2200000.00, 15, 'active', '2025-04-15 13:23:38', '2025-04-15 13:23:38', NULL, NULL, NULL, NULL, NULL, 'employed'),
(6, '김윤섭', NULL, 'worker', NULL, NULL, 'active', '2025-04-15 13:23:46', '2025-04-15 13:23:46', NULL, NULL, NULL, NULL, NULL, 'employed'),
(7, '이창호', NULL, 'worker', NULL, NULL, 'inactive', '2025-04-15 13:23:51', '2025-04-15 13:23:51', NULL, NULL, NULL, NULL, NULL, 'resigned'),
(8, '최광욱', '2025-04-01', '용접', 2200000.00, 15, 'active', '2025-04-15 13:23:57', '2025-04-15 13:23:57', NULL, NULL, NULL, NULL, NULL, 'employed'),
(9, '박현수', '2025-04-01', '용접', 2200000.00, 15, 'active', '2025-04-15 13:24:01', '2025-04-15 13:24:01', NULL, NULL, NULL, NULL, NULL, 'employed'),
(10, '조윤호', NULL, 'worker', NULL, NULL, 'active', '2025-04-15 13:24:07', '2025-04-15 13:24:07', NULL, NULL, NULL, NULL, NULL, 'employed'),
(11, '조승민', NULL, 'worker', NULL, NULL, 'active', '2026-01-06 06:18:27', '2026-01-06 06:18:27', NULL, NULL, '2026-01-05', '생산팀', '용접사', 'employed');

-- =====================================================
-- 4. users (사용자 계정)
-- 운영 role → 테스트 role_id 매핑: admin→2, leader→2, user→4
-- =====================================================
TRUNCATE TABLE users;
INSERT INTO users (user_id, username, password, role_id, created_at, worker_id, is_active, last_login_at, password_changed_at, failed_login_attempts, locked_until, name, email, updated_at) VALUES
(1, 'hyungi', '$2b$10$lwSfKipx0fQ.9nfMUBmzt.WXzerdxuT5MVX4b3YhFeHxyYlUjM7bi', 2, '2025-05-06 05:03:02', NULL, 1, '2025-12-02 11:26:49', '2025-05-06 05:03:02', 0, NULL, NULL, NULL, '2025-12-02 02:26:49'),
(3, '김두수', '$2a$10$z3i2EVOotRFBj.KHzx5LQOKlXD0QHLNFEvJcd6FlO6/1TCYGk6SSu', 2, '2025-06-07 23:48:35', 1, 1, '2026-02-03 12:52:32', '2025-06-15 07:40:39', 0, NULL, '김두수', NULL, '2026-02-03 03:52:32'),
(4, '김아무개', '$2a$10$QAJIoPyi.apz91exp8GsiO/prAD5Xwanht6XImP1jvKsy/7Ba/b8.', 4, '2025-06-11 08:03:59', NULL, 0, '2025-06-15 23:28:04', '2025-06-15 06:03:42', 0, NULL, '김아무개', NULL, '2025-11-05 02:48:03'),
(5, '임영규', '$2a$10$66ps/MEEi4BVABfJc5P0y.yCap09NhTMyd1A/7rFVxESytQGlB3wC', 2, '2025-06-15 07:41:02', 3, 1, '2025-12-10 15:55:48', '2025-06-15 07:41:02', 0, NULL, '임영규', NULL, '2025-12-10 06:55:48'),
(6, '반치원', '$2a$10$jcn6f7flRLZlr5yKQcXDIePodRK0rsM4deNnNGjuOlredeTVsRYZ6', 2, '2025-06-15 07:41:32', 3, 1, '2025-11-05 01:56:13', '2025-06-15 07:41:32', 5, '2025-11-25 16:22:46', '반치원', NULL, '2025-11-25 07:07:46'),
(7, 'khahn', '$2a$10$vQcJ3C37TPGweYF8rok2quzN2DK9lgxiqDAXV38vvPVjRdz3l6M8y', 2, '2025-11-05 02:18:15', NULL, 1, '2025-11-05 15:56:23', NULL, 0, NULL, 'khahn', NULL, '2025-11-05 06:56:23'),
(8, '그룹장', '$2a$10$/8yMaLoMSR0epA3zDH.H7eq2vB.5EmB9jZfZKPepR.S07LCXZzH0C', 2, '2025-11-05 02:51:29', NULL, 1, '2025-11-05 02:52:07', NULL, 0, NULL, '그룹장', NULL, '2025-11-05 02:52:07'),
(9, '작업자', '$2a$10$n8ZY0H0UtBhND7OuUcwzKOjLj26s5oNHmRma9toIV/R5bxB3NoL3q', 4, '2025-11-05 02:51:45', NULL, 1, '2025-11-05 02:51:57', NULL, 0, NULL, '작업자', NULL, '2025-11-05 02:51:57');

-- =====================================================
-- 5. projects (프로젝트)
-- =====================================================
TRUNCATE TABLE projects;
INSERT INTO projects (project_id, job_no, project_name, contract_date, due_date, delivery_method, site, pm, created_at, updated_at, is_active, completed_date, project_status) VALUES
(1, 'TKO-24008P', 'YHP Project', NULL, NULL, NULL, 'Quang Ninh(Vietnam)', '장형태', '2025-04-15 22:40:28', '2025-04-15 22:40:28', 0, NULL, 'active'),
(2, 'TKG-24009P', '한화에어로스페이스 순천', NULL, NULL, NULL, '순천', '장형태', '2025-04-15 22:42:41', '2025-04-15 22:42:41', 0, NULL, 'active'),
(3, 'TKG-24011P', '효성화학 에틸렌 탱크 건설공사', NULL, NULL, NULL, '울산', '김길종', '2025-04-15 22:43:53', '2025-04-15 22:43:53', 0, NULL, 'active'),
(4, 'TKG-24013P', '김천 솔라 파워 그린 수소 Project', NULL, NULL, NULL, '김천', '김길종', '2025-04-15 22:44:57', '2025-04-15 22:44:57', 0, NULL, 'active'),
(5, 'TKG-24016P', 'LG Chem P3RE Project', NULL, NULL, NULL, '.', '장형태', '2025-04-15 22:46:36', '2025-04-15 22:46:36', 0, NULL, 'active'),
(7, 'TKO-25003F', '25년 안전보건시설설비', NULL, NULL, NULL, '.', '.', '2025-04-15 22:47:32', '2025-04-15 22:47:32', 0, NULL, 'active'),
(8, 'TKG-25007P', 'P Project', NULL, NULL, NULL, '오창읍', '장형태', '2025-04-15 22:48:50', '2025-04-15 22:48:50', 0, NULL, 'active'),
(10, 'TKR-25008P', 'DIG Airgas LG CHEM', NULL, NULL, NULL, '여수', '서태원', '2025-04-15 22:50:05', '2025-04-15 22:50:05', 0, NULL, 'active'),
(11, 'TKR-25010P', 'FK FISCHER Project', NULL, NULL, NULL, '울산', '전상신', '2025-04-15 22:51:12', '2025-04-15 22:51:12', 0, NULL, 'active'),
(12, 'TKO-24007P', 'MP7 Project', NULL, NULL, NULL, '.', '윤지민', '2025-04-15 23:56:26', '2025-04-15 23:56:26', 0, NULL, 'active'),
(13, '연차/휴무', '연차/휴무', '2025-01-01', '2025-12-31', '.', '.', '.', '2025-04-16 01:58:23', '2025-04-16 01:58:23', 1, NULL, 'active'),
(14, 'TKO-25009R', 'M Project', NULL, NULL, NULL, NULL, '이민후', '2025-09-28 22:13:33', '2025-09-28 22:13:33', 0, NULL, 'active'),
(15, 'TKR-25013P', 'HAS MCS Plnat', NULL, '2026-03-31', NULL, NULL, NULL, '2026-01-06 06:16:37', '2026-01-06 06:16:37', 1, NULL, 'active'),
(16, 'TKO-26003F', '안전보건시설설비', '2026-01-01', '2026-12-31', NULL, NULL, NULL, '2026-01-06 06:17:33', '2026-01-06 06:17:33', 1, NULL, 'active');

-- =====================================================
-- 6. error_types (오류 유형)
-- =====================================================
TRUNCATE TABLE error_types;
INSERT INTO error_types (id, name, description, severity, solution_guide, created_at, updated_at) VALUES
(1, '설계미스', '설계미스', 'medium', NULL, '2025-06-16 02:21:32', '2025-06-16 03:02:03'),
(2, '외주작업 불량', '입고 자재 불량', 'medium', NULL, '2025-06-16 02:21:32', '2025-06-16 03:02:05'),
(3, '입고지연', '자재 미입고', 'medium', NULL, '2025-06-16 02:21:32', '2025-06-16 03:02:11'),
(4, '작업불량', '작업자 실수', 'medium', NULL, '2025-06-16 02:21:32', '2025-06-16 03:02:13'),
(5, '설비고장', '장비없음', 'medium', NULL, '2025-06-16 02:21:32', '2025-06-16 03:02:16'),
(6, '검사불량', '검사불량', 'medium', NULL, '2025-06-16 02:21:32', '2025-06-24 04:36:18');

-- =====================================================
-- 7. work_types (작업 유형)
-- =====================================================
TRUNCATE TABLE work_types;
INSERT INTO work_types (id, name, description, category, created_at, updated_at) VALUES
(1, 'Base(구조물)', NULL, NULL, '2025-06-16 02:21:32', '2025-06-16 03:03:42'),
(2, 'Vessel(용기)', NULL, NULL, '2025-06-16 02:21:32', '2025-06-16 03:03:47'),
(3, 'Piping Assembly(배관)', NULL, NULL, '2025-06-16 02:21:32', '2025-06-16 03:03:50'),
(4, '작업대기', NULL, NULL, '2025-06-16 02:21:32', '2025-06-16 03:03:56'),
(11, '휴무', '연차사용 등', NULL, '2025-11-04 05:53:46', '2025-11-04 05:53:46');

-- =====================================================
-- 8. work_status_types (작업 상태 유형)
-- =====================================================
TRUNCATE TABLE work_status_types;
INSERT INTO work_status_types (id, name, description, is_error, created_at) VALUES
(1, '정규', '정상적으로 완료된 작업', 0, '2025-06-16 02:21:16'),
(2, '에러', '오류가 발생한 작업', 1, '2025-06-16 02:21:16');

-- =====================================================
-- 9. daily_work_reports (일일 작업 보고서)
-- 운영 DB description 컬럼 제외 (테스트 DB에 없음)
-- =====================================================
TRUNCATE TABLE daily_work_reports;
INSERT INTO daily_work_reports (id, report_date, worker_id, project_id, work_type_id, work_status_id, error_type_id, work_hours, created_at, updated_at, created_by, updated_by) VALUES
-- 파일 용량 관계로 별도 import 필요
-- SOURCE /tmp/daily_work_reports_import.sql
(14, '2025-06-02', 1, 4, 3, 1, NULL, 8.00, '2025-06-16 05:10:23', '2025-06-16 05:10:23', 1, NULL);

-- 주의: 전체 daily_work_reports 데이터(1524건)는 별도로 import 해야 합니다
-- 아래 명령으로 import:
-- docker exec -i tkfb_db mysql -uroot -p'tycdoq-Kawcug-8wesfa' hyungi < /tmp/daily_work_reports_import.sql

-- =====================================================
-- 10. daily_attendance_records 초기화
-- =====================================================
TRUNCATE TABLE daily_attendance_records;

-- =====================================================
-- 외래키 복원
-- =====================================================
SET FOREIGN_KEY_CHECKS = 1;

-- =====================================================
-- 결과 확인
-- =====================================================
SELECT 'work_attendance_types' as 테이블, COUNT(*) as 건수 FROM work_attendance_types
UNION ALL SELECT 'vacation_types', COUNT(*) FROM vacation_types
UNION ALL SELECT 'workers', COUNT(*) FROM workers
UNION ALL SELECT 'users', COUNT(*) FROM users
UNION ALL SELECT 'projects', COUNT(*) FROM projects
UNION ALL SELECT 'error_types', COUNT(*) FROM error_types
UNION ALL SELECT 'work_types', COUNT(*) FROM work_types
UNION ALL SELECT 'work_status_types', COUNT(*) FROM work_status_types
UNION ALL SELECT 'daily_work_reports', COUNT(*) FROM daily_work_reports
UNION ALL SELECT 'daily_attendance_records', COUNT(*) FROM daily_attendance_records;