-- --------------------------------------------------------
-- 호스트:                          127.0.0.1
-- 서버 버전:                        8.4.3 - MySQL Community Server - GPL
-- 서버 OS:                        Win64
-- HeidiSQL 버전:                  12.8.0.6908
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


-- gswms 데이터베이스 구조 내보내기
CREATE DATABASE IF NOT EXISTS `gswms` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `gswms`;

-- 테이블 gswms.activity_logs 구조 내보내기
CREATE TABLE IF NOT EXISTS `activity_logs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int DEFAULT NULL,
  `username` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `action` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `details` text COLLATE utf8mb4_general_ci,
  `ip_address` varchar(45) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `timestamp` datetime DEFAULT CURRENT_TIMESTAMP,
  `type` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'general',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=71002 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.announcements 구조 내보내기
CREATE TABLE IF NOT EXISTS `announcements` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `attachment_path` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `attachment_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_by` int NOT NULL,
  `importance` enum('normal','urgent') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'normal',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `created_by` (`created_by`),
  CONSTRAINT `announcements_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.announcement_comments 구조 내보내기
CREATE TABLE IF NOT EXISTS `announcement_comments` (
  `id` int NOT NULL AUTO_INCREMENT,
  `announcement_id` int NOT NULL,
  `user_id` int NOT NULL,
  `comment` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `announcement_id` (`announcement_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `announcement_comments_ibfk_1` FOREIGN KEY (`announcement_id`) REFERENCES `announcements` (`id`) ON DELETE CASCADE,
  CONSTRAINT `announcement_comments_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.announcement_reads 구조 내보내기
CREATE TABLE IF NOT EXISTS `announcement_reads` (
  `id` int NOT NULL AUTO_INCREMENT,
  `announcement_id` int NOT NULL,
  `user_id` int NOT NULL,
  `read_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `announcement_user_unique` (`announcement_id`,`user_id`),
  KEY `announcement_id` (`announcement_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `announcement_reads_ibfk_1` FOREIGN KEY (`announcement_id`) REFERENCES `announcements` (`id`) ON DELETE CASCADE,
  CONSTRAINT `announcement_reads_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=186 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.attendance_logs 구조 내보내기
CREATE TABLE IF NOT EXISTS `attendance_logs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `clock_in` datetime NOT NULL,
  `clock_out` datetime DEFAULT NULL,
  `status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'on_time' COMMENT 'e.g., on_time, late, leave',
  `work_schedule_id` int DEFAULT NULL COMMENT 'FK to work_schedule_templates',
  `notes` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `shift` enum('DAY','NIGHT') COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '출근 시 결정된 근무조',
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `attendance_logs_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=193 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.attendance_requests 구조 내보내기
CREATE TABLE IF NOT EXISTS `attendance_requests` (
  `id` int NOT NULL AUTO_INCREMENT,
  `log_id` int NOT NULL,
  `user_id` int NOT NULL,
  `requested_clock_in` datetime DEFAULT NULL,
  `requested_clock_out` datetime DEFAULT NULL,
  `reason` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` enum('pending','approved','rejected') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending',
  `requested_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `reviewed_by` int DEFAULT NULL,
  `reviewed_at` datetime DEFAULT NULL,
  `reviewer_notes` text COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`id`),
  KEY `log_id` (`log_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `attendance_requests_ibfk_1` FOREIGN KEY (`log_id`) REFERENCES `attendance_logs` (`id`) ON DELETE CASCADE,
  CONSTRAINT `attendance_requests_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.auth_tokens 구조 내보내기
CREATE TABLE IF NOT EXISTS `auth_tokens` (
  `id` int NOT NULL AUTO_INCREMENT,
  `selector` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `hashed_validator` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `user_id` int NOT NULL,
  `expires_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `auth_tokens_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=84 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.away_notice_templates 구조 내보내기
CREATE TABLE IF NOT EXISTS `away_notice_templates` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `content` text COLLATE utf8mb4_general_ci NOT NULL,
  `sort_order` int NOT NULL DEFAULT '0',
  `created_by` int DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `created_by` (`created_by`),
  CONSTRAINT `away_notice_templates_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.cash_denominations 구조 내보내기
CREATE TABLE IF NOT EXISTS `cash_denominations` (
  `id` int NOT NULL AUTO_INCREMENT,
  `log_id` int NOT NULL COMMENT '관련 로그 ID',
  `count_50000` int DEFAULT '0',
  `count_10000` int DEFAULT '0',
  `count_5000` int DEFAULT '0',
  `count_1000` int DEFAULT '0',
  `count_500` int DEFAULT '0',
  `count_100` int DEFAULT '0',
  `count_50` int DEFAULT '0',
  `count_10` int DEFAULT '0',
  `total_amount` decimal(15,2) NOT NULL DEFAULT '0.00' COMMENT '실물 총액',
  `expected_amount` decimal(15,2) DEFAULT '0.00' COMMENT '전산 예상 잔액 (점검 시 사용)',
  `difference` decimal(15,2) DEFAULT '0.00' COMMENT '차액 (실물 - 전산)',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `log_id` (`log_id`),
  CONSTRAINT `cash_denominations_ibfk_1` FOREIGN KEY (`log_id`) REFERENCES `cash_safe_logs` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.cash_expenses 구조 내보내기
CREATE TABLE IF NOT EXISTS `cash_expenses` (
  `id` int NOT NULL AUTO_INCREMENT,
  `expense_date` date NOT NULL,
  `item_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `product_id` int DEFAULT NULL COMMENT '매입 상품 ID',
  `quantity` int DEFAULT NULL COMMENT '매입 수량',
  `location_id` int DEFAULT NULL COMMENT '입고 위치 ID',
  `amount` decimal(15,2) NOT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `receipt_image_path` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '영수증 이미지 경로',
  `user_id` int NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `fk_cash_expense_product` (`product_id`),
  KEY `fk_cash_expense_location` (`location_id`),
  CONSTRAINT `cash_expenses_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_cash_expense_location` FOREIGN KEY (`location_id`) REFERENCES `locations` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_cash_expense_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.cash_safe_logs 구조 내보내기
CREATE TABLE IF NOT EXISTS `cash_safe_logs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `type` enum('deposit','withdrawal','exchange','count_check','adjustment') COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '거래 유형',
  `category` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '세부 카테고리 (pos_drop, bank_deposit, expense, pos_change, bank_exchange 등)',
  `amount` decimal(15,2) NOT NULL DEFAULT '0.00' COMMENT '거래 금액 (입금+, 출금-, 교환0)',
  `balance_after` decimal(15,2) NOT NULL DEFAULT '0.00' COMMENT '거래 후 잔액',
  `user_id` int NOT NULL COMMENT '담당자 ID',
  `notes` text COLLATE utf8mb4_unicode_ci COMMENT '비고/메모',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `cash_safe_logs_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.categories 구조 내보내기
CREATE TABLE IF NOT EXISTS `categories` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `parent_id` int DEFAULT NULL,
  `description` text COLLATE utf8mb4_general_ci,
  `sort_order` int NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `disposal_check_time` time DEFAULT NULL COMMENT 'Time of day to check for disposal for products in this category.',
  PRIMARY KEY (`id`),
  KEY `parent_id` (`parent_id`),
  CONSTRAINT `categories_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=266 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.checklist_templates 구조 내보내기
CREATE TABLE IF NOT EXISTS `checklist_templates` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `shift` enum('DAY','NIGHT','ALL') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'ALL' COMMENT '적용될 근무 시간 (주간/야간/모두)',
  `is_default` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.checklist_template_items 구조 내보내기
CREATE TABLE IF NOT EXISTS `checklist_template_items` (
  `id` int NOT NULL AUTO_INCREMENT,
  `template_id` int NOT NULL,
  `item_text` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `sort_order` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `template_id` (`template_id`),
  CONSTRAINT `checklist_template_items_ibfk_1` FOREIGN KEY (`template_id`) REFERENCES `checklist_templates` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.comments 구조 내보내기
CREATE TABLE IF NOT EXISTS `comments` (
  `comment_id` int NOT NULL AUTO_INCREMENT,
  `post_id` int NOT NULL,
  `user_id` int NOT NULL,
  `content` text NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`comment_id`),
  KEY `post_id` (`post_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `comments_ibfk_1` FOREIGN KEY (`post_id`) REFERENCES `posts` (`post_id`) ON DELETE CASCADE,
  CONSTRAINT `comments_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.contacts 구조 내보내기
CREATE TABLE IF NOT EXISTS `contacts` (
  `id` int NOT NULL AUTO_INCREMENT,
  `type` varchar(50) COLLATE utf8mb4_general_ci NOT NULL COMMENT '구분',
  `contact_person_name` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '담당자명',
  `phone_number` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `notes` text COLLATE utf8mb4_general_ci,
  `sort_order` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.contract_expiration_alerts 구조 내보내기
CREATE TABLE IF NOT EXISTS `contract_expiration_alerts` (
  `id` int NOT NULL AUTO_INCREMENT,
  `contract_id` int NOT NULL COMMENT '계약서 ID',
  `employee_id` int NOT NULL COMMENT '근로자 ID',
  `days_until_expiration` int NOT NULL COMMENT '만료까지 남은 일수 (30, 14, 7, 1)',
  `alert_sent_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '알림 발송 일시',
  `is_read` tinyint(1) DEFAULT '0' COMMENT '읽음 여부',
  `action_taken` enum('renewed','terminated','pending') COLLATE utf8mb4_unicode_ci DEFAULT 'pending' COMMENT '조치 결과',
  PRIMARY KEY (`id`),
  KEY `idx_contract` (`contract_id`),
  KEY `idx_employee` (`employee_id`),
  KEY `idx_action` (`action_taken`),
  KEY `idx_days` (`days_until_expiration`),
  CONSTRAINT `contract_expiration_alerts_ibfk_1` FOREIGN KEY (`contract_id`) REFERENCES `employment_contracts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `contract_expiration_alerts_ibfk_2` FOREIGN KEY (`employee_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='계약 만료 알림';

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.contract_history 구조 내보내기
CREATE TABLE IF NOT EXISTS `contract_history` (
  `id` int NOT NULL AUTO_INCREMENT,
  `contract_id` int NOT NULL COMMENT '계약서 ID',
  `action` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '작업 종류 (created/employee_signed/employer_signed/modified/terminated/renewed/downloaded)',
  `action_by` int NOT NULL COMMENT '작업자 ID',
  `action_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '작업 일시',
  `details` text COLLATE utf8mb4_unicode_ci COMMENT '변경 내용 상세 (JSON 형식)',
  `ip_address` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'IP 주소',
  PRIMARY KEY (`id`),
  KEY `action_by` (`action_by`),
  KEY `idx_contract` (`contract_id`),
  KEY `idx_action_at` (`action_at`),
  KEY `idx_action` (`action`),
  CONSTRAINT `contract_history_ibfk_1` FOREIGN KEY (`contract_id`) REFERENCES `employment_contracts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `contract_history_ibfk_2` FOREIGN KEY (`action_by`) REFERENCES `users` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='계약 변경 이력';

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.contract_templates 구조 내보내기
CREATE TABLE IF NOT EXISTS `contract_templates` (
  `id` int NOT NULL AUTO_INCREMENT,
  `template_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '템플릿 이름',
  `contract_type` enum('regular','fixed_term','part_time') COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '계약 유형',
  `template_content` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '템플릿 HTML (변수 포함)',
  `is_active` tinyint(1) DEFAULT '1' COMMENT '활성화 여부',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '생성일시',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '수정일시',
  PRIMARY KEY (`id`),
  KEY `idx_type_active` (`contract_type`,`is_active`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='근로계약서 템플릿';

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.currency_exchange_requests 구조 내보내기
CREATE TABLE IF NOT EXISTS `currency_exchange_requests` (
  `id` int NOT NULL AUTO_INCREMENT,
  `request_number` varchar(20) NOT NULL COMMENT 'EX-2025-001 형식',
  `request_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `requested_by` int NOT NULL,
  `submit_bill_50000` int DEFAULT '0',
  `submit_bill_10000` int DEFAULT '0',
  `submit_bill_5000` int DEFAULT '0',
  `submit_bill_1000` int DEFAULT '0',
  `submit_coin_500` int DEFAULT '0',
  `submit_coin_100` int DEFAULT '0',
  `submit_coin_50` int DEFAULT '0',
  `submit_coin_10` int DEFAULT '0',
  `submit_total` int NOT NULL DEFAULT '0',
  `receive_bill_50000` int DEFAULT '0',
  `receive_bill_10000` int DEFAULT '0',
  `receive_bill_5000` int DEFAULT '0',
  `receive_bill_1000` int DEFAULT '0',
  `receive_coin_500` int DEFAULT '0',
  `receive_coin_100` int DEFAULT '0',
  `receive_coin_50` int DEFAULT '0',
  `receive_coin_10` int DEFAULT '0',
  `receive_total` int NOT NULL DEFAULT '0',
  `requester_signature` mediumtext COMMENT '요청자 서명 (Base64 WebP)',
  `bank_staff_signature` mediumtext COMMENT '은행직원 서명 (Base64 WebP)',
  `bank_staff_name` varchar(100) DEFAULT NULL COMMENT '은행직원 성명',
  `bank_name` varchar(100) DEFAULT NULL COMMENT '은행명',
  `status` enum('pending','completed') DEFAULT 'pending',
  `notes` text,
  `completed_at` datetime DEFAULT NULL,
  `completed_by` int DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `request_number` (`request_number`),
  KEY `requested_by` (`requested_by`),
  KEY `completed_by` (`completed_by`),
  KEY `idx_request_date` (`request_date`),
  KEY `idx_status` (`status`),
  CONSTRAINT `currency_exchange_requests_ibfk_1` FOREIGN KEY (`requested_by`) REFERENCES `users` (`id`),
  CONSTRAINT `currency_exchange_requests_ibfk_2` FOREIGN KEY (`completed_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='화폐교환 요청서';

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.customer_requests 구조 내보내기
CREATE TABLE IF NOT EXISTS `customer_requests` (
  `id` int NOT NULL AUTO_INCREMENT,
  `product_name` varchar(255) COLLATE utf8mb4_general_ci NOT NULL COMMENT '고객이 찾은 상품명',
  `product_sku` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '기존 상품 SKU (있을 경우)',
  `category` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '상품 카테고리',
  `request_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '요청 등록일시',
  `requested_by` int NOT NULL COMMENT '등록한 직원 ID',
  `customer_count` int DEFAULT '1' COMMENT '요청한 고객 수',
  `urgency_level` enum('low','normal','high','urgent') COLLATE utf8mb4_general_ci DEFAULT 'normal' COMMENT '긴급도: low=낮음, normal=보통, high=높음, urgent=긴급',
  `min_price` decimal(10,2) DEFAULT NULL COMMENT '희망 최소 가격',
  `max_price` decimal(10,2) DEFAULT NULL COMMENT '희망 최대 가격',
  `preferred_brand` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '선호 브랜드/제조사',
  `specification` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '규격/용량 (예: 500ml, 1kg)',
  `origin_preference` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '원산지 선호도 (예: 국산, 칠레산)',
  `image_path` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '첨부 이미지 경로',
  `product_id` int DEFAULT NULL COMMENT '연결된 상품 ID (products 테이블)',
  `can_order` tinyint(1) DEFAULT '1' COMMENT '발주 가능 여부 (기존 상품=1, 신규=0)',
  `requires_approval` tinyint(1) DEFAULT '0' COMMENT '승인 필요 여부 (신규 상품인 경우 TRUE)',
  `status` enum('pending','ordered','stocked','rejected') COLLATE utf8mb4_general_ci DEFAULT 'pending' COMMENT '처리 상태',
  `notes` text COLLATE utf8mb4_general_ci COMMENT '추가 메모',
  `resolved_at` datetime DEFAULT NULL COMMENT '처리 완료일시',
  `resolved_by` int DEFAULT NULL COMMENT '처리한 직원 ID',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_product_name` (`product_name`),
  KEY `idx_request_date` (`request_date`),
  KEY `idx_status` (`status`),
  KEY `fk_requested_by` (`requested_by`),
  KEY `fk_resolved_by` (`resolved_by`),
  KEY `idx_urgency_status` (`urgency_level`,`status`),
  KEY `idx_product_id` (`product_id`),
  KEY `idx_can_order` (`can_order`),
  CONSTRAINT `fk_customer_requests_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_customer_requests_requested_by` FOREIGN KEY (`requested_by`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_customer_requests_resolved_by` FOREIGN KEY (`resolved_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='고객 요청 상품 기록';

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.disciplinary_actions 구조 내보내기
CREATE TABLE IF NOT EXISTS `disciplinary_actions` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `action_type` varchar(50) COLLATE utf8mb4_general_ci NOT NULL COMMENT '예: warning, reprimand, suspension',
  `reason` text COLLATE utf8mb4_general_ci NOT NULL,
  `related_handover_id` int DEFAULT NULL,
  `action_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `issued_by_id` int NOT NULL,
  `status` varchar(50) COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'active' COMMENT '예: active, resolved',
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `issued_by_id` (`issued_by_id`),
  KEY `related_handover_id` (`related_handover_id`),
  CONSTRAINT `fk_disciplinary_handover` FOREIGN KEY (`related_handover_id`) REFERENCES `handoff_logs` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_disciplinary_issuer` FOREIGN KEY (`issued_by_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_disciplinary_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.disposal_approval_thresholds 구조 내보내기
CREATE TABLE IF NOT EXISTS `disposal_approval_thresholds` (
  `id` int NOT NULL AUTO_INCREMENT,
  `approval_level` enum('manager','branch','owner') NOT NULL,
  `min_amount` decimal(10,2) DEFAULT '0.00',
  `max_amount` decimal(10,2) DEFAULT '999999.99',
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_approval_level` (`approval_level`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.disposal_removals 구조 내보내기
CREATE TABLE IF NOT EXISTS `disposal_removals` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '반출 요청 ID',
  `request_number` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '요청 번호 (예: DR-20241228-001)',
  `requester_id` int NOT NULL COMMENT '요청자 ID (users 테이블)',
  `request_date` datetime NOT NULL COMMENT '요청 일시',
  `status` enum('pending','approved','rejected','completed') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending',
  `reviewer_id` int DEFAULT NULL COMMENT '승인/반려자 ID (관리자)',
  `reviewed_at` datetime DEFAULT NULL COMMENT '승인/반려 처리 일시',
  `rejection_reason` text COLLATE utf8mb4_unicode_ci COMMENT '반려 사유',
  `removal_reason` enum('general_waste','recycling','donation','employee_consumption','other') COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '반출 사유',
  `notes` text COLLATE utf8mb4_unicode_ci COMMENT '메모 및 상세 사유',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '생성일시',
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '수정일시',
  `completed_at` timestamp NULL DEFAULT NULL,
  `completed_by_id` int DEFAULT NULL,
  `completion_notes` text COLLATE utf8mb4_unicode_ci,
  `total_cost_loss` decimal(10,2) DEFAULT '0.00' COMMENT '원가 손실',
  `total_revenue_loss` decimal(10,2) DEFAULT '0.00' COMMENT '매출 손실',
  `total_margin_loss` decimal(10,2) DEFAULT '0.00' COMMENT '마진 손실',
  `requires_approval` tinyint(1) DEFAULT '1' COMMENT '승인 필요',
  `approval_level` enum('none','manager','branch','owner') COLLATE utf8mb4_unicode_ci DEFAULT 'manager',
  `approval_status` enum('pending','approved','rejected') COLLATE utf8mb4_unicode_ci DEFAULT 'pending',
  `approved_by` int DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `photos` json DEFAULT NULL COMMENT '첨부 사진',
  `preferred_date` date DEFAULT NULL,
  `preferred_time` enum('morning','afternoon','evening') COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `vehicle_info` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `actual_removal_date` datetime DEFAULT NULL,
  `removal_cost` decimal(10,2) DEFAULT '0.00',
  `notification_sent` tinyint(1) DEFAULT '0',
  `notified_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `request_number` (`request_number`),
  KEY `idx_status` (`status`),
  KEY `idx_request_date` (`request_date` DESC),
  KEY `idx_requester` (`requester_id`),
  KEY `idx_reviewer` (`reviewer_id`),
  KEY `idx_disposal_approval_status` (`approval_status`),
  KEY `idx_disposal_preferred_date` (`preferred_date`),
  KEY `idx_disposal_created_at` (`created_at`),
  KEY `idx_disposal_approved_by` (`approved_by`),
  CONSTRAINT `disposal_removals_ibfk_1` FOREIGN KEY (`requester_id`) REFERENCES `users` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `disposal_removals_ibfk_2` FOREIGN KEY (`reviewer_id`) REFERENCES `users` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='폐기 반출 요청';

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.disposal_removal_items 구조 내보내기
CREATE TABLE IF NOT EXISTS `disposal_removal_items` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '품목 ID',
  `disposal_removal_id` int NOT NULL COMMENT '반출 요청 ID',
  `stock_movement_id` int NOT NULL COMMENT '원래 폐기 기록 ID (stock_movements)',
  `product_id` int NOT NULL COMMENT '상품 ID',
  `quantity` int NOT NULL COMMENT '반출 수량',
  `item_notes` text COLLATE utf8mb4_unicode_ci COMMENT '품목별 메모',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '생성일시',
  PRIMARY KEY (`id`),
  KEY `idx_disposal_removal` (`disposal_removal_id`),
  KEY `idx_stock_movement` (`stock_movement_id`),
  KEY `idx_product` (`product_id`),
  CONSTRAINT `disposal_removal_items_ibfk_1` FOREIGN KEY (`disposal_removal_id`) REFERENCES `disposal_removals` (`id`) ON DELETE CASCADE,
  CONSTRAINT `disposal_removal_items_ibfk_2` FOREIGN KEY (`stock_movement_id`) REFERENCES `stock_movements` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `disposal_removal_items_ibfk_3` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=1626 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='폐기 반출 품목 상세';

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.disposal_removal_photos 구조 내보내기
CREATE TABLE IF NOT EXISTS `disposal_removal_photos` (
  `id` int NOT NULL AUTO_INCREMENT,
  `disposal_removal_id` int NOT NULL,
  `photo_path` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `uploaded_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `disposal_removal_id` (`disposal_removal_id`),
  CONSTRAINT `disposal_removal_photos_ibfk_1` FOREIGN KEY (`disposal_removal_id`) REFERENCES `disposal_removals` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.employee_details 구조 내보내기
CREATE TABLE IF NOT EXISTS `employee_details` (
  `user_id` int NOT NULL,
  `full_name` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `resident_registration_number` varchar(14) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '주민등록번호 (e.g., 900101-1234567)',
  `birth_date` date DEFAULT NULL,
  `contact_number` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `address` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `join_date` date DEFAULT NULL,
  `contract_type` enum('regular','contract','part_time') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'regular',
  `weekly_holiday` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `work_hours_per_week` decimal(4,2) DEFAULT NULL,
  `salary_type` enum('annual','monthly','hourly') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'monthly',
  `salary_amount` int unsigned DEFAULT NULL,
  `bank_name` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `account_number` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `insurance_national_pension` tinyint(1) NOT NULL DEFAULT '0',
  `insurance_health` tinyint(1) NOT NULL DEFAULT '0',
  `insurance_employment` tinyint(1) NOT NULL DEFAULT '0',
  `insurance_industrial_accident` tinyint(1) NOT NULL DEFAULT '0',
  `health_cert_issue_date` date DEFAULT NULL,
  `health_cert_renewal_date` date DEFAULT NULL,
  `default_shift` enum('DAY','NIGHT') COLLATE utf8mb4_general_ci DEFAULT 'DAY' COMMENT '기본 근무조 (DAY=주간, NIGHT=야간)',
  PRIMARY KEY (`user_id`),
  CONSTRAINT `employee_details_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.employment_contracts 구조 내보내기
CREATE TABLE IF NOT EXISTS `employment_contracts` (
  `id` int NOT NULL AUTO_INCREMENT,
  `contract_number` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '계약서 번호 (예: EC-2025-0001)',
  `template_id` int DEFAULT NULL,
  `employee_id` int NOT NULL COMMENT '근로자 ID (users 테이블 참조)',
  `employee_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '근로자 성명',
  `employee_phone` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '연락처',
  `employee_email` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '이메일',
  `employee_address` text COLLATE utf8mb4_unicode_ci COMMENT '주소',
  `contract_type` enum('regular','fixed_term','part_time') COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '정규직/기간제/단시간',
  `start_date` date NOT NULL COMMENT '계약 시작일',
  `end_date` date DEFAULT NULL COMMENT '계약 종료일 (정규직은 NULL)',
  `probation_period` int DEFAULT NULL COMMENT '수습기간(개월)',
  `work_location` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '근무지',
  `job_description` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '업무 내용',
  `work_hours` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '소정근로시간 (예: 주 40시간)',
  `work_days` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '근무일 (단시간 근로자용)',
  `base_salary` int NOT NULL COMMENT '기본급',
  `salary_components` text COLLATE utf8mb4_unicode_ci COMMENT '임금 구성항목 (JSON 형식)',
  `payment_method` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '지급방법 (예: 매월 25일 계좌이체)',
  `payment_day` int NOT NULL COMMENT '급여 지급일 (1-31)',
  `break_time` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '휴게시간',
  `holidays` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '휴일',
  `annual_leave` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '연차유급휴가',
  `notes` text COLLATE utf8mb4_unicode_ci COMMENT '특약사항',
  `contract_html` longtext COLLATE utf8mb4_unicode_ci COMMENT '계약서 HTML 스냅샷',
  `employee_signature` longtext COLLATE utf8mb4_unicode_ci COMMENT '근로자 서명 (base64 이미지)',
  `employee_signed_at` datetime DEFAULT NULL COMMENT '근로자 서명일시',
  `employee_ip` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '근로자 서명 IP',
  `employer_id` int NOT NULL COMMENT '사업주 ID (users 테이블)',
  `employer_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '사업주 성명',
  `employer_signature` longtext COLLATE utf8mb4_unicode_ci COMMENT '사업주 서명 (base64 이미지)',
  `employer_signed_at` datetime DEFAULT NULL COMMENT '사업주 서명일시',
  `employer_ip` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '사업주 서명 IP',
  `status` enum('draft','employee_signed','completed','terminated') COLLATE utf8mb4_unicode_ci DEFAULT 'draft' COMMENT '작성중/근로자서명완료/계약완료/종료',
  `content_hash` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'SHA-256 해시값',
  `delivered_at` datetime DEFAULT NULL COMMENT '교부일시',
  `delivery_method` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '교부방법 (email/download/qr_code/portal)',
  `employee_consent_at` datetime DEFAULT NULL COMMENT '전자 교부 동의 일시',
  `termination_date` date DEFAULT NULL COMMENT '근로관계 종료일',
  `retention_until` date DEFAULT NULL COMMENT '보존 만료일 (종료일+3년)',
  `previous_contract_id` int DEFAULT NULL COMMENT '이전 계약서 ID (갱신인 경우)',
  `is_renewal` tinyint(1) DEFAULT '0' COMMENT '갱신 계약 여부',
  `renewal_reason` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '갱신 사유',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '생성일시',
  `created_by` int NOT NULL COMMENT '생성자 ID',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '수정일시',
  PRIMARY KEY (`id`),
  UNIQUE KEY `contract_number` (`contract_number`),
  KEY `created_by` (`created_by`),
  KEY `idx_employee` (`employee_id`),
  KEY `idx_employer` (`employer_id`),
  KEY `idx_status` (`status`),
  KEY `idx_contract_type` (`contract_type`),
  KEY `idx_dates` (`start_date`,`end_date`),
  KEY `idx_retention` (`retention_until`),
  KEY `idx_previous` (`previous_contract_id`),
  KEY `idx_renewal` (`is_renewal`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `employment_contracts_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `users` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `employment_contracts_ibfk_2` FOREIGN KEY (`employer_id`) REFERENCES `users` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `employment_contracts_ibfk_3` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `employment_contracts_ibfk_4` FOREIGN KEY (`previous_contract_id`) REFERENCES `employment_contracts` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='전자근로계약서';

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.equipment 구조 내보내기
CREATE TABLE IF NOT EXISTS `equipment` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `location` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `requires_temp_check` tinyint(1) NOT NULL DEFAULT '0' COMMENT '온도 점검 필요 여부',
  `sort_order` int NOT NULL DEFAULT '0',
  `min_temp` decimal(5,2) DEFAULT NULL,
  `max_temp` decimal(5,2) DEFAULT NULL,
  `check_type` varchar(50) COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'temperature' COMMENT '점검 유형 (temperature, status 등)',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.equipment_checklist_items 구조 내보내기
CREATE TABLE IF NOT EXISTS `equipment_checklist_items` (
  `id` int NOT NULL AUTO_INCREMENT,
  `equipment_id` int NOT NULL,
  `item_text` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `item_order` int NOT NULL DEFAULT '0',
  `is_critical` tinyint(1) NOT NULL DEFAULT '0',
  `item_type` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'check',
  PRIMARY KEY (`id`),
  KEY `equipment_id` (`equipment_id`),
  CONSTRAINT `equipment_checklist_items_ibfk_1` FOREIGN KEY (`equipment_id`) REFERENCES `equipment` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.handoff_logs 구조 내보내기
CREATE TABLE IF NOT EXISTS `handoff_logs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `handoff_date` date NOT NULL,
  `shift` enum('DAY','NIGHT') COLLATE utf8mb4_general_ci NOT NULL COMMENT '주간/야간',
  `leaver_id` int NOT NULL COMMENT '인계자 ID',
  `receiver_id` int NOT NULL COMMENT '인수자 ID',
  `pos_difference` int NOT NULL DEFAULT '0' COMMENT 'POS 과부족',
  `safe_deposit` int NOT NULL DEFAULT '0' COMMENT '금고 입금액',
  `cash_notes` text COLLATE utf8mb4_general_ci COMMENT '시재 특이사항',
  `cash_withdrawal_amount` int DEFAULT NULL,
  `stock_request` text COLLATE utf8mb4_general_ci COMMENT '결품/부족 상품 (발주 요청)',
  `returns` text COLLATE utf8mb4_general_ci COMMENT '반품/환불 내역',
  `incoming_damage_notes` text COLLATE utf8mb4_general_ci COMMENT '입고 상품 파손/변질 내용',
  `unfinished_tasks` text COLLATE utf8mb4_general_ci COMMENT '미진행 업무',
  `general_notes` text COLLATE utf8mb4_general_ci COMMENT '기타 전달사항',
  `general_notes_private` tinyint(1) NOT NULL DEFAULT '0',
  `complaints` text COLLATE utf8mb4_general_ci COMMENT '고객 클레임',
  `equipment_issues` text COLLATE utf8mb4_general_ci COMMENT '기기 고장',
  `security_notes` text COLLATE utf8mb4_general_ci COMMENT '보안/안전',
  `status` enum('DRAFT','PENDING','ACCEPTED','REJECTED') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'PENDING' COMMENT '상태 (DRAFT: 임시저장, PENDING: 인수대기, ACCEPTED: 인수완료, REJECTED: 반려)',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '작성일',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '수정일',
  `accepted_by_id` int DEFAULT NULL COMMENT '인수 확인자 ID',
  `manager_verified_at` timestamp NULL DEFAULT NULL,
  `manager_verified_by` int DEFAULT NULL,
  `manager_notes` text COLLATE utf8mb4_general_ci,
  `accepted_at` timestamp NULL DEFAULT NULL COMMENT '인수 확인 시각',
  `checklist` json DEFAULT NULL,
  `acceptance_checklist` json DEFAULT NULL COMMENT 'Checklist state at the time of acceptance',
  `rejection_reason` text COLLATE utf8mb4_general_ci,
  `rejected_by_id` int DEFAULT NULL,
  `rejected_at` timestamp NULL DEFAULT NULL,
  `followup_notes` text COLLATE utf8mb4_general_ci COMMENT 'Follow-up instructions from owner after rejection',
  `followup_by_id` int DEFAULT NULL COMMENT 'ID of the owner who gave the follow-up',
  `followup_at` timestamp NULL DEFAULT NULL COMMENT 'Timestamp of when the follow-up was given',
  PRIMARY KEY (`id`),
  KEY `leaver_id` (`leaver_id`),
  KEY `receiver_id` (`receiver_id`),
  KEY `accepted_by_id` (`accepted_by_id`),
  KEY `fk_manager_verified_by` (`manager_verified_by`),
  CONSTRAINT `fk_manager_verified_by` FOREIGN KEY (`manager_verified_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=215 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.handovers 구조 내보내기
CREATE TABLE IF NOT EXISTS `handovers` (
  `id` int NOT NULL AUTO_INCREMENT,
  `handover_date` date NOT NULL,
  `shift` enum('morning','afternoon','night') COLLATE utf8mb4_general_ci NOT NULL,
  `user_id` int NOT NULL,
  `summary` text COLLATE utf8mb4_general_ci,
  `status` varchar(20) COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'written' COMMENT '작성 상태 (written, accepted)',
  `accepted_by_user_id` int DEFAULT NULL COMMENT '인수자 ID',
  `accepted_at` datetime DEFAULT NULL COMMENT '인수 시각',
  `accepted_cash_amount` decimal(15,2) DEFAULT NULL COMMENT '인수자가 확인한 시재',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `handover_date_shift` (`handover_date`,`shift`),
  KEY `user_id` (`user_id`),
  KEY `fk_accepted_by_user` (`accepted_by_user_id`),
  CONSTRAINT `fk_accepted_by_user` FOREIGN KEY (`accepted_by_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `handovers_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.handover_attachments 구조 내보내기
CREATE TABLE IF NOT EXISTS `handover_attachments` (
  `id` int NOT NULL AUTO_INCREMENT,
  `handover_id` int NOT NULL,
  `file_path` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `original_filename` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `uploaded_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `handover_id` (`handover_id`),
  CONSTRAINT `handover_attachments_ibfk_1` FOREIGN KEY (`handover_id`) REFERENCES `handovers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.handover_comments 구조 내보내기
CREATE TABLE IF NOT EXISTS `handover_comments` (
  `id` int NOT NULL AUTO_INCREMENT,
  `handover_id` int NOT NULL,
  `user_id` int NOT NULL,
  `comment` text COLLATE utf8mb4_general_ci NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `handover_id` (`handover_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `handover_comments_ibfk_1` FOREIGN KEY (`handover_id`) REFERENCES `handovers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `handover_comments_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.handover_confirmations 구조 내보내기
CREATE TABLE IF NOT EXISTS `handover_confirmations` (
  `id` int NOT NULL AUTO_INCREMENT,
  `handover_id` int NOT NULL,
  `user_id` int NOT NULL,
  `confirmed_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `handover_id_user_id` (`handover_id`,`user_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `handover_confirmations_ibfk_1` FOREIGN KEY (`handover_id`) REFERENCES `handovers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `handover_confirmations_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.handover_damaged_items 구조 내보내기
CREATE TABLE IF NOT EXISTS `handover_damaged_items` (
  `id` int NOT NULL AUTO_INCREMENT,
  `handover_id` int NOT NULL,
  `product_id` int NOT NULL,
  `quantity` int NOT NULL DEFAULT '1',
  `description` text COMMENT '파손/변질 상세 내용',
  `status` varchar(50) DEFAULT 'reported',
  `image_paths` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin COMMENT 'JSON Array of image paths',
  PRIMARY KEY (`id`),
  KEY `handover_id` (`handover_id`),
  KEY `product_id` (`product_id`),
  CONSTRAINT `handover_damaged_items_ibfk_1` FOREIGN KEY (`handover_id`) REFERENCES `handoff_logs` (`id`) ON DELETE CASCADE,
  CONSTRAINT `handover_damaged_items_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.handover_images 구조 내보내기
CREATE TABLE IF NOT EXISTS `handover_images` (
  `id` int NOT NULL AUTO_INCREMENT,
  `handover_id` int NOT NULL,
  `file_path` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `uploaded_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `handover_id` (`handover_id`),
  CONSTRAINT `handover_images_ibfk_1` FOREIGN KEY (`handover_id`) REFERENCES `handoff_logs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.handover_item_tags 구조 내보내기
CREATE TABLE IF NOT EXISTS `handover_item_tags` (
  `handover_id` int NOT NULL,
  `tag_id` int NOT NULL,
  PRIMARY KEY (`handover_id`,`tag_id`),
  KEY `tag_id` (`tag_id`),
  CONSTRAINT `handover_item_tags_ibfk_1` FOREIGN KEY (`handover_id`) REFERENCES `handovers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `handover_item_tags_ibfk_2` FOREIGN KEY (`tag_id`) REFERENCES `handover_tags` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.handover_tags 구조 내보내기
CREATE TABLE IF NOT EXISTS `handover_tags` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.handover_templates 구조 내보내기
CREATE TABLE IF NOT EXISTS `handover_templates` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `content` text COLLATE utf8mb4_general_ci NOT NULL,
  `created_by` int DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `created_by` (`created_by`),
  CONSTRAINT `handover_templates_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.inventory 구조 내보내기
CREATE TABLE IF NOT EXISTS `inventory` (
  `id` int NOT NULL AUTO_INCREMENT,
  `product_id` int NOT NULL,
  `location_id` int NOT NULL,
  `quantity` int NOT NULL DEFAULT '0',
  `expiration_date` date DEFAULT NULL,
  `last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_inventory_item` (`product_id`,`location_id`,`expiration_date`),
  KEY `location_id` (`location_id`),
  CONSTRAINT `inventory_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE,
  CONSTRAINT `inventory_ibfk_2` FOREIGN KEY (`location_id`) REFERENCES `locations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=7446 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.leave_requests 구조 내보내기
CREATE TABLE IF NOT EXISTS `leave_requests` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `leave_type` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `reason` text COLLATE utf8mb4_unicode_ci,
  `attachment_path` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` enum('pending','approved','rejected') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending',
  `requested_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `reviewed_by` int DEFAULT NULL,
  `reviewed_at` datetime DEFAULT NULL,
  `reviewer_notes` text COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `leave_requests_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.locations 구조 내보내기
CREATE TABLE IF NOT EXISTS `locations` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '위치 이름 (예: A-01-01)',
  `description` text COLLATE utf8mb4_unicode_ci COMMENT '위치에 대한 설명',
  `type` enum('warehouse','sales_floor') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'warehouse',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=226 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='창고 보관 위치';

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.notifications 구조 내보내기
CREATE TABLE IF NOT EXISTS `notifications` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL COMMENT 'Recipient User ID',
  `type` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `message` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `link` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_read` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `notifications_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=348 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.password_reset_tokens 구조 내보내기
CREATE TABLE IF NOT EXISTS `password_reset_tokens` (
  `id` int NOT NULL AUTO_INCREMENT,
  `email` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `token` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `expires_at` datetime NOT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `token` (`token`),
  KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.payrolls 구조 내보내기
CREATE TABLE IF NOT EXISTS `payrolls` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `payment_month` varchar(7) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'YYYY-MM',
  `total_work_hours` decimal(10,2) DEFAULT '0.00',
  `base_salary` decimal(15,2) NOT NULL DEFAULT '0.00',
  `total_allowance` decimal(15,2) NOT NULL DEFAULT '0.00',
  `total_deductions` decimal(15,2) NOT NULL DEFAULT '0.00',
  `net_pay` decimal(15,2) NOT NULL DEFAULT '0.00',
  `status` enum('calculated','confirmed','paid') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'calculated',
  `confirmed_at` datetime DEFAULT NULL,
  `paid_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_payment_month` (`user_id`,`payment_month`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `payrolls_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.payroll_items 구조 내보내기
CREATE TABLE IF NOT EXISTS `payroll_items` (
  `id` int NOT NULL AUTO_INCREMENT,
  `payroll_id` int NOT NULL,
  `item_type` enum('earning','deduction') COLLATE utf8mb4_unicode_ci NOT NULL,
  `item_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `payroll_id` (`payroll_id`),
  CONSTRAINT `payroll_items_ibfk_1` FOREIGN KEY (`payroll_id`) REFERENCES `payrolls` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=217854 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.performance_checklist_items 구조 내보내기
CREATE TABLE IF NOT EXISTS `performance_checklist_items` (
  `id` int NOT NULL AUTO_INCREMENT,
  `template_id` int NOT NULL,
  `item_text` varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '업무 내용',
  `incentive_amount` decimal(10,2) DEFAULT '0.00' COMMENT '인센티브 금액 (원)',
  `payout_type` enum('standard','debt_deduction') COLLATE utf8mb4_unicode_ci DEFAULT 'standard' COMMENT '수익 유형: standard=인센티브 지급, debt_deduction=채무 차감 전용',
  `time_limit_minutes` int DEFAULT NULL COMMENT '제한 시간(분), NULL이면 제한 없음',
  `is_required` tinyint(1) DEFAULT '0' COMMENT '필수 여부',
  `verification_type` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT 'none' COMMENT '인증 유형 (none, photo, qr, value)',
  `verification_target` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'QR content or numeric range',
  `display_order` int DEFAULT '0' COMMENT '표시 순서',
  `is_active` tinyint(1) DEFAULT '1' COMMENT '활성 여부',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `min_duration_seconds` int DEFAULT '30' COMMENT '최소 수행 시간(초) - 기본 30초',
  PRIMARY KEY (`id`),
  KEY `idx_template` (`template_id`),
  KEY `idx_active` (`is_active`),
  KEY `idx_order` (`display_order`),
  CONSTRAINT `performance_checklist_items_ibfk_1` FOREIGN KEY (`template_id`) REFERENCES `performance_checklist_templates` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=58 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='능력제 체크리스트 업무 항목';

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.performance_checklist_templates 구조 내보내기
CREATE TABLE IF NOT EXISTS `performance_checklist_templates` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '템플릿 이름',
  `category` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'daily',
  `target_role` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '대상 역할',
  `is_active` tinyint(1) DEFAULT '1' COMMENT '활성 여부',
  `created_by` int DEFAULT NULL COMMENT '생성자 (점주)',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `created_by` (`created_by`),
  KEY `idx_active` (`is_active`),
  KEY `idx_category` (`category`),
  CONSTRAINT `performance_checklist_templates_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='능력제 인센티브 체크리스트 템플릿';

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.performance_monthly_summary 구조 내보내기
CREATE TABLE IF NOT EXISTS `performance_monthly_summary` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `year_month` varchar(7) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'YYYY-MM',
  `total_records` int DEFAULT '0' COMMENT '총 기록 수',
  `total_days_worked` int DEFAULT '0' COMMENT '근무일 수',
  `total_incentive` decimal(10,2) DEFAULT '0.00' COMMENT '총 인센티브',
  `average_completion_rate` decimal(5,2) DEFAULT '0.00' COMMENT '평균 완료율',
  `status` enum('pending','calculated','approved','paid') COLLATE utf8mb4_unicode_ci DEFAULT 'pending' COMMENT '상태',
  `calculated_at` timestamp NULL DEFAULT NULL COMMENT '계산 시간',
  `approved_by` int DEFAULT NULL COMMENT '승인자',
  `approved_at` timestamp NULL DEFAULT NULL COMMENT '승인 시간',
  `paid_at` timestamp NULL DEFAULT NULL COMMENT '지급 시간',
  `notes` text COLLATE utf8mb4_unicode_ci COMMENT '비고',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_user_month` (`user_id`,`year_month`),
  KEY `approved_by` (`approved_by`),
  KEY `idx_year_month` (`year_month`),
  KEY `idx_status` (`status`),
  CONSTRAINT `performance_monthly_summary_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  CONSTRAINT `performance_monthly_summary_ibfk_2` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='월별 인센티브 집계';

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.performance_records 구조 내보내기
CREATE TABLE IF NOT EXISTS `performance_records` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL COMMENT '수행 직원',
  `template_id` int NOT NULL,
  `record_date` date NOT NULL COMMENT '수행일',
  `shift` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT 'DAY',
  `total_items` int DEFAULT '0' COMMENT '총 항목 수',
  `completed_items` int DEFAULT '0' COMMENT '완료 항목 수',
  `total_incentive` decimal(10,2) DEFAULT '0.00' COMMENT '총 인센티브 금액',
  `earned_incentive` decimal(10,2) DEFAULT '0.00' COMMENT '획득 인센티브',
  `completion_rate` decimal(5,2) DEFAULT '0.00' COMMENT '완료율 %',
  `status` enum('pending','submitted','peer_approved','peer_rejected','owner_approved','owner_rejected','paid') COLLATE utf8mb4_unicode_ci DEFAULT 'pending' COMMENT '상태',
  `submitted_at` timestamp NULL DEFAULT NULL COMMENT '제출 시간',
  `peer_verified_by` int DEFAULT NULL,
  `peer_verified_at` datetime DEFAULT NULL,
  `peer_comments` text COLLATE utf8mb4_unicode_ci,
  `peer_approved_by` int DEFAULT NULL COMMENT '동료 승인자 (다음 근무자)',
  `peer_approved_at` timestamp NULL DEFAULT NULL COMMENT '동료 승인 시간',
  `peer_comment` text COLLATE utf8mb4_unicode_ci COMMENT '동료 의견',
  `owner_approved_by` int DEFAULT NULL COMMENT '점주 승인자',
  `owner_approved_at` timestamp NULL DEFAULT NULL COMMENT '점주 승인 시간',
  `owner_comment` text COLLATE utf8mb4_unicode_ci COMMENT '점주 의견',
  `notes` text COLLATE utf8mb4_unicode_ci COMMENT '비고',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `requires_owner_review` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_user_date_template` (`user_id`,`record_date`,`template_id`),
  KEY `template_id` (`template_id`),
  KEY `peer_approved_by` (`peer_approved_by`),
  KEY `owner_approved_by` (`owner_approved_by`),
  KEY `idx_user_date` (`user_id`,`record_date`),
  KEY `idx_status` (`status`),
  KEY `idx_submitted` (`submitted_at`),
  KEY `peer_verified_by` (`peer_verified_by`),
  CONSTRAINT `performance_records_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  CONSTRAINT `performance_records_ibfk_2` FOREIGN KEY (`template_id`) REFERENCES `performance_checklist_templates` (`id`),
  CONSTRAINT `performance_records_ibfk_3` FOREIGN KEY (`peer_approved_by`) REFERENCES `users` (`id`),
  CONSTRAINT `performance_records_ibfk_4` FOREIGN KEY (`owner_approved_by`) REFERENCES `users` (`id`),
  CONSTRAINT `performance_records_ibfk_5` FOREIGN KEY (`peer_verified_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='일별 능력제 수행 기록';

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.performance_record_items 구조 내보내기
CREATE TABLE IF NOT EXISTS `performance_record_items` (
  `id` int NOT NULL AUTO_INCREMENT,
  `record_id` int NOT NULL,
  `item_id` int NOT NULL,
  `started_at` datetime DEFAULT NULL COMMENT '업무 시작 시작',
  `retry_count` int DEFAULT '0' COMMENT '재도전 횟수',
  `is_completed` tinyint(1) DEFAULT '0' COMMENT '완료 여부',
  `completed_at` timestamp NULL DEFAULT NULL COMMENT '완료 시간',
  `incentive_earned` decimal(10,2) DEFAULT '0.00' COMMENT '획득 인센티브',
  `debt_deduction_amount` decimal(10,2) DEFAULT '0.00' COMMENT '이 업무로 차감된 채무 금액 (상환형 업무에서만 사용)',
  `manager_status` enum('pending','approved','rejected') COLLATE utf8mb4_unicode_ci DEFAULT 'pending' COMMENT '관리자 승인 상태',
  `manager_comment` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '관리자 코멘트(반려사유)',
  `verification_data` text COLLATE utf8mb4_unicode_ci COMMENT 'Image path or input value',
  `verified_at` datetime DEFAULT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci COMMENT '메모',
  `proof_image` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '증빙 사진 경로',
  `peer_verified` tinyint(1) DEFAULT '0' COMMENT '동료 검증 완료',
  `peer_comment` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '동료 의견',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `item_id` (`item_id`),
  KEY `idx_record` (`record_id`),
  KEY `idx_completed` (`is_completed`),
  KEY `idx_peer_verified` (`peer_verified`),
  CONSTRAINT `performance_record_items_ibfk_1` FOREIGN KEY (`record_id`) REFERENCES `performance_records` (`id`) ON DELETE CASCADE,
  CONSTRAINT `performance_record_items_ibfk_2` FOREIGN KEY (`item_id`) REFERENCES `performance_checklist_items` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=511 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='항목별 완료 세부 기록';

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.permissions 구조 내보내기
CREATE TABLE IF NOT EXISTS `permissions` (
  `id` int NOT NULL AUTO_INCREMENT,
  `permission_key` varchar(100) COLLATE utf8mb4_general_ci NOT NULL COMMENT '권한 고유 키 (e.g., users.manage)',
  `description` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '권한에 대한 설명',
  PRIMARY KEY (`id`),
  UNIQUE KEY `permission_key` (`permission_key`)
) ENGINE=InnoDB AUTO_INCREMENT=45 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='시스템의 모든 권한 목록';

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.posts 구조 내보내기
CREATE TABLE IF NOT EXISTS `posts` (
  `post_id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `category` varchar(20) NOT NULL COMMENT 'improvement, bug, new_feature',
  `status` varchar(20) NOT NULL DEFAULT 'pending' COMMENT 'pending, in_progress, completed, rejected',
  `title` varchar(255) NOT NULL,
  `content` text NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`post_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `posts_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.post_reads 구조 내보내기
CREATE TABLE IF NOT EXISTS `post_reads` (
  `read_id` int NOT NULL AUTO_INCREMENT,
  `post_id` int NOT NULL,
  `user_id` int NOT NULL,
  `read_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`read_id`),
  UNIQUE KEY `unique_read` (`post_id`,`user_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `post_reads_ibfk_1` FOREIGN KEY (`post_id`) REFERENCES `posts` (`post_id`) ON DELETE CASCADE,
  CONSTRAINT `post_reads_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.products 구조 내보내기
CREATE TABLE IF NOT EXISTS `products` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `sku` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `barcode` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `image_path` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `supplier_id` int DEFAULT NULL,
  `category_id` int DEFAULT NULL,
  `brand_id` int DEFAULT NULL,
  `unit_price` decimal(10,2) DEFAULT '0.00',
  `cost_price` decimal(10,2) NOT NULL DEFAULT '0.00',
  `safety_stock` int NOT NULL DEFAULT '5',
  `last_ordered_at` datetime DEFAULT NULL,
  `replenishment_unit` int NOT NULL DEFAULT '1' COMMENT '묶음 보충 단위',
  `has_expiration_date` tinyint(1) NOT NULL DEFAULT '0',
  `shelf_life_years` int DEFAULT '0',
  `shelf_life_months` int DEFAULT '0',
  `shelf_life_days` int DEFAULT '0',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `quantity` int NOT NULL DEFAULT '0',
  `handling_status` varchar(20) COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'not-handling',
  `units_per_case` int NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `sku` (`sku`),
  KEY `fk_product_created_by` (`created_by`),
  KEY `fk_product_updated_by` (`updated_by`),
  KEY `idx_barcode` (`barcode`),
  KEY `idx_brand_id` (`brand_id`),
  KEY `idx_products_name` (`name`),
  KEY `idx_products_sku` (`sku`),
  KEY `idx_products_supplier_id` (`supplier_id`),
  KEY `idx_products_category_id` (`category_id`),
  CONSTRAINT `fk_product_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_product_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=2863 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.product_barcodes 구조 내보내기
CREATE TABLE IF NOT EXISTS `product_barcodes` (
  `id` int NOT NULL AUTO_INCREMENT,
  `product_id` int NOT NULL,
  `barcode` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `quantity_multiplier` int DEFAULT '1' COMMENT '1 for single, >1 for bundles',
  `description` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'e.g. Carton, Box',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `barcode` (`barcode`),
  KEY `product_id` (`product_id`),
  KEY `idx_barcode` (`barcode`),
  CONSTRAINT `product_barcodes_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.product_images 구조 내보내기
CREATE TABLE IF NOT EXISTS `product_images` (
  `id` int NOT NULL AUTO_INCREMENT,
  `product_id` int NOT NULL,
  `file_path` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `thumbnail_path` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `is_primary` tinyint(1) DEFAULT '0',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `product_id` (`product_id`),
  CONSTRAINT `product_images_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3068 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.profit_distribution_records 구조 내보내기
CREATE TABLE IF NOT EXISTS `profit_distribution_records` (
  `id` int NOT NULL AUTO_INCREMENT,
  `settlement_month` varchar(7) COLLATE utf8mb4_general_ci NOT NULL,
  `total_revenue` decimal(15,2) NOT NULL,
  `total_payroll_expense` decimal(15,2) NOT NULL,
  `other_expenses_total` decimal(15,2) NOT NULL,
  `other_expenses_details` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
  `owner_id` int NOT NULL,
  `partner_id` int DEFAULT NULL,
  `owner_final_payout` decimal(15,2) NOT NULL,
  `partner_final_payout` decimal(15,2) DEFAULT NULL,
  `owner_hourly_rate` decimal(10,2) DEFAULT NULL,
  `owner_total_debt` decimal(15,2) DEFAULT '0.00',
  `partner_hourly_rate` decimal(10,2) DEFAULT NULL,
  `partner_total_debt` decimal(15,2) DEFAULT '0.00',
  `owner_debt` decimal(15,2) NOT NULL DEFAULT '0.00',
  `partner_debt` decimal(15,2) NOT NULL DEFAULT '0.00',
  `owner_debt_details` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
  `partner_debt_details` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `settlement_month` (`settlement_month`),
  CONSTRAINT `profit_distribution_records_chk_1` CHECK (json_valid(`other_expenses_details`)),
  CONSTRAINT `profit_distribution_records_chk_2` CHECK (json_valid(`owner_debt_details`)),
  CONSTRAINT `profit_distribution_records_chk_3` CHECK (json_valid(`partner_debt_details`))
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.profit_sharing 구조 내보내기
CREATE TABLE IF NOT EXISTS `profit_sharing` (
  `id` int NOT NULL AUTO_INCREMENT,
  `settlement_month` varchar(7) COLLATE utf8mb4_unicode_ci NOT NULL,
  `total_revenue` decimal(15,2) DEFAULT '0.00',
  `total_payroll_expense` decimal(15,2) DEFAULT '0.00',
  `other_expenses` decimal(15,2) DEFAULT '0.00',
  `other_expenses_details` json DEFAULT NULL,
  `owner_id` int NOT NULL,
  `partner_id` int NOT NULL,
  `owner_payout` decimal(15,2) DEFAULT '0.00',
  `partner_payout` decimal(15,2) DEFAULT '0.00',
  `owner_debt_deduction` decimal(15,2) DEFAULT '0.00',
  `partner_debt_deduction` decimal(15,2) DEFAULT '0.00',
  `owner_remaining_debt` decimal(15,2) DEFAULT '0.00',
  `partner_remaining_debt` decimal(15,2) DEFAULT '0.00',
  `owner_debt_details` json DEFAULT NULL,
  `partner_debt_details` json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_settlement` (`settlement_month`,`owner_id`,`partner_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.role_permissions 구조 내보내기
CREATE TABLE IF NOT EXISTS `role_permissions` (
  `role` varchar(50) COLLATE utf8mb4_general_ci NOT NULL COMMENT 'users 테이블의 role과 동일',
  `permission_id` int NOT NULL,
  PRIMARY KEY (`role`,`permission_id`),
  KEY `permission_id` (`permission_id`),
  CONSTRAINT `role_permissions_ibfk_1` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='역할별 권한 매핑';

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.schedules 구조 내보내기
CREATE TABLE IF NOT EXISTS `schedules` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL COMMENT '직원 ID (users.id)',
  `start_time` datetime NOT NULL COMMENT '근무 시작 시간',
  `end_time` datetime NOT NULL COMMENT '근무 종료 시간',
  `shift_type` enum('morning','afternoon','night','other') COLLATE utf8mb4_unicode_ci DEFAULT 'other' COMMENT '교대 유형 (handover.php 참조)',
  `notes` text COLLATE utf8mb4_unicode_ci COMMENT '스케줄 관련 메모',
  `is_off` tinyint(1) NOT NULL DEFAULT '0' COMMENT '휴무 여부 (1: 휴무)',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_start_time` (`start_time`),
  CONSTRAINT `fk_schedules_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='직원 근무 스케줄';

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.stock_counts 구조 내보내기
CREATE TABLE IF NOT EXISTS `stock_counts` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `type` enum('FULL','BY_LOCATION','BY_PRODUCT') COLLATE utf8mb4_general_ci NOT NULL,
  `location_ids` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
  `product_ids` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
  `created_by` int NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `completed_at` timestamp NULL DEFAULT NULL,
  `status` enum('PENDING','IN_PROGRESS','COMPLETED','CANCELLED') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'PENDING',
  PRIMARY KEY (`id`),
  KEY `created_by` (`created_by`),
  CONSTRAINT `stock_counts_chk_1` CHECK (json_valid(`location_ids`)),
  CONSTRAINT `stock_counts_chk_2` CHECK (json_valid(`product_ids`))
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.stock_count_items 구조 내보내기
CREATE TABLE IF NOT EXISTS `stock_count_items` (
  `id` int NOT NULL AUTO_INCREMENT,
  `stock_count_id` int NOT NULL,
  `product_id` int NOT NULL,
  `location_id` int NOT NULL,
  `expiration_date` date DEFAULT NULL,
  `system_quantity` int NOT NULL,
  `counted_quantity` int DEFAULT NULL,
  `counted_by` int DEFAULT NULL,
  `counted_at` timestamp NULL DEFAULT NULL,
  `discrepancy` int GENERATED ALWAYS AS ((ifnull(`counted_quantity`,0) - `system_quantity`)) STORED,
  `status` enum('PENDING','CONFIRMED') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'PENDING',
  PRIMARY KEY (`id`),
  UNIQUE KEY `stock_count_batch` (`stock_count_id`,`product_id`,`location_id`,`expiration_date`),
  KEY `product_id` (`product_id`),
  KEY `location_id` (`location_id`),
  KEY `counted_by` (`counted_by`)
) ENGINE=InnoDB AUTO_INCREMENT=1949 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.stock_movements 구조 내보내기
CREATE TABLE IF NOT EXISTS `stock_movements` (
  `id` int NOT NULL AUTO_INCREMENT,
  `product_id` int NOT NULL COMMENT '상품 ID',
  `type` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `quantity` int NOT NULL COMMENT '이동 수량 (+/-)',
  `location_id` int DEFAULT NULL COMMENT '보관 위치 ID',
  `expiration_date` date DEFAULT NULL COMMENT '소비기한',
  `transaction_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '처리 일시',
  `user_id` int DEFAULT NULL COMMENT '처리한 사용자 ID',
  `notes` text COLLATE utf8mb4_unicode_ci COMMENT '관련 메모',
  `related_movement_id` int DEFAULT NULL,
  `status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'active',
  `receipt_number` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `product_id` (`product_id`),
  KEY `location_id` (`location_id`),
  KEY `stock_movements_ibfk_3` (`user_id`),
  KEY `idx_related_movement` (`related_movement_id`),
  KEY `idx_type_date` (`type`,`transaction_date`),
  KEY `idx_stock_mv_date` (`transaction_date`),
  KEY `idx_stock_mv_type` (`type`),
  KEY `idx_stock_mv_product_id` (`product_id`),
  CONSTRAINT `stock_movements_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE,
  CONSTRAINT `stock_movements_ibfk_2` FOREIGN KEY (`location_id`) REFERENCES `locations` (`id`) ON DELETE SET NULL,
  CONSTRAINT `stock_movements_ibfk_3` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=16932 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='재고 이동 기록';

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.suppliers 구조 내보내기
CREATE TABLE IF NOT EXISTS `suppliers` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `contact_person` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `email` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `phone` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `address` text COLLATE utf8mb4_general_ci,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.system_settings 구조 내보내기
CREATE TABLE IF NOT EXISTS `system_settings` (
  `id` int NOT NULL AUTO_INCREMENT,
  `setting_key` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `setting_value` text COLLATE utf8mb4_general_ci,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `setting_key` (`setting_key`)
) ENGINE=InnoDB AUTO_INCREMENT=213 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.temperature_logs 구조 내보내기
CREATE TABLE IF NOT EXISTS `temperature_logs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `equipment_id` int NOT NULL,
  `user_id` int NOT NULL,
  `temperature` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `logged_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `notes` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `log_status` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '상태 점검 결과 (정상, 오류 등)',
  `checklist_results` json DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `equipment_id` (`equipment_id`),
  KEY `logged_at` (`logged_at`),
  KEY `temperature_logs_ibfk_2` (`user_id`),
  CONSTRAINT `temperature_logs_ibfk_1` FOREIGN KEY (`equipment_id`) REFERENCES `equipment` (`id`) ON DELETE CASCADE,
  CONSTRAINT `temperature_logs_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=719 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.update_notes 구조 내보내기
CREATE TABLE IF NOT EXISTS `update_notes` (
  `id` int NOT NULL AUTO_INCREMENT,
  `version` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '업데이트 버전 (예: v1.2)',
  `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '업데이트 제목',
  `content` text COLLATE utf8mb4_unicode_ci COMMENT '업데이트 상세 내용',
  `release_date` date NOT NULL COMMENT '게시일',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '생성일시',
  PRIMARY KEY (`id`),
  KEY `idx_release_date` (`release_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='시스템 업데이트 기록을 저장하는 테이블';

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.users 구조 내보내기
CREATE TABLE IF NOT EXISTS `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `full_name` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `email` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `role` varchar(50) COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'store_manager',
  `pin` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Hashed PIN for Kiosk Mode',
  `profile_image_path` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `status` enum('active','resigned') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'active',
  `resignation_date` date DEFAULT NULL,
  `password` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `reset_token` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `reset_token_expires_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.user_checklist_items 구조 내보내기
CREATE TABLE IF NOT EXISTS `user_checklist_items` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `template_item_id` int NOT NULL,
  `is_completed` tinyint(1) NOT NULL DEFAULT '0',
  `completed_at` datetime DEFAULT NULL,
  `completed_by` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_template_item_unique` (`user_id`,`template_item_id`),
  KEY `user_id` (`user_id`),
  KEY `template_item_id` (`template_item_id`),
  CONSTRAINT `user_checklist_items_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `user_checklist_items_ibfk_2` FOREIGN KEY (`template_item_id`) REFERENCES `checklist_template_items` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.user_deductions 구조 내보내기
CREATE TABLE IF NOT EXISTS `user_deductions` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `deduction_name` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `total_amount` decimal(10,2) NOT NULL,
  `monthly_deduction` decimal(10,2) NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date DEFAULT NULL,
  `status` enum('active','paid_off') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `user_deductions_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.vehicles 구조 내보내기
CREATE TABLE IF NOT EXISTS `vehicles` (
  `id` int NOT NULL AUTO_INCREMENT,
  `vehicle_name` varchar(100) NOT NULL,
  `vehicle_number` varchar(50) NOT NULL,
  `vehicle_type` enum('truck','van','external') DEFAULT 'truck',
  `capacity_kg` int DEFAULT '1000',
  `driver_name` varchar(100) DEFAULT NULL,
  `driver_phone` varchar(20) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT '1',
  `notes` text,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_vehicle_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.votes 구조 내보내기
CREATE TABLE IF NOT EXISTS `votes` (
  `vote_id` int NOT NULL AUTO_INCREMENT,
  `post_id` int NOT NULL,
  `user_id` int NOT NULL,
  PRIMARY KEY (`vote_id`),
  UNIQUE KEY `unique_vote` (`post_id`,`user_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `votes_ibfk_1` FOREIGN KEY (`post_id`) REFERENCES `posts` (`post_id`) ON DELETE CASCADE,
  CONSTRAINT `votes_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 내보낼 데이터가 선택되어 있지 않습니다.

-- 테이블 gswms.work_schedule_templates 구조 내보내기
CREATE TABLE IF NOT EXISTS `work_schedule_templates` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL COMMENT '직원 ID (users.id)',
  `day_of_week` tinyint NOT NULL COMMENT '요일 (1:월요일, 7:일요일)',
  `start_time` time DEFAULT NULL COMMENT '근무 시작 시간',
  `end_time` time DEFAULT NULL COMMENT '근무 종료 시간',
  `is_off` tinyint(1) NOT NULL DEFAULT '0' COMMENT '휴무일 여부 (1: 휴무)',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_day` (`user_id`,`day_of_week`),
  CONSTRAINT `fk_schedule_templates_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=295 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='직원별 요일 근무 스케줄 템플릿';

-- 내보낼 데이터가 선택되어 있지 않습니다.

/*!40103 SET TIME_ZONE=IFNULL(@OLD_TIME_ZONE, 'system') */;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */;
