-- HR Position System - 清空資料庫腳本
-- 只清空業務資料，保留表結構和參照代碼
-- 執行前請確認備份重要資料

USE hr_position_system;

-- ============================================================
-- 1. 停用外鍵檢查（避免刪除順序問題）
-- ============================================================
SET FOREIGN_KEY_CHECKS = 0;

-- ============================================================
-- 2. 清空業務資料表（保留表結構）
-- ============================================================

-- 清空組織崗位關聯表
TRUNCATE TABLE HR_position_organization_positions;

-- 清空部級單位表
TRUNCATE TABLE HR_position_departments;

-- 清空處級單位表
TRUNCATE TABLE HR_position_divisions;

-- 清空事業體表
TRUNCATE TABLE HR_position_business_units;

-- 清空崗位描述表
TRUNCATE TABLE HR_position_job_descriptions;

-- 清空職務基礎資料表
TRUNCATE TABLE HR_position_jobs;

-- 清空崗位基礎資料表
TRUNCATE TABLE HR_position_positions;

-- 清空審計日誌表
TRUNCATE TABLE HR_position_audit_logs;

-- ============================================================
-- 3. 重新啟用外鍵檢查
-- ============================================================
SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================
-- 4. 確認參照代碼表資料保留（不清空）
-- ============================================================
-- reference_codes 表保留所有參照代碼資料
-- 包含：POSITION_CATEGORY, POSITION_NATURE, POSITION_LEVEL,
--       JOB_CATEGORY, EDUCATION, SALARY_RANGE, WORK_LOCATION,
--       EMP_ATTRIBUTE, RECRUIT_POSITION, TITLE_REQ

-- 確認參照代碼數量
SELECT code_type, COUNT(*) as count
FROM HR_position_reference_codes
GROUP BY code_type
ORDER BY code_type;

-- ============================================================
-- 5. 顯示清空結果
-- ============================================================
SELECT 'HR_position_organization_positions' as table_name, COUNT(*) as row_count FROM HR_position_organization_positions
UNION ALL
SELECT 'HR_position_departments', COUNT(*) FROM HR_position_departments
UNION ALL
SELECT 'HR_position_divisions', COUNT(*) FROM HR_position_divisions
UNION ALL
SELECT 'HR_position_business_units', COUNT(*) FROM HR_position_business_units
UNION ALL
SELECT 'HR_position_job_descriptions', COUNT(*) FROM HR_position_job_descriptions
UNION ALL
SELECT 'HR_position_jobs', COUNT(*) FROM HR_position_jobs
UNION ALL
SELECT 'HR_position_positions', COUNT(*) FROM HR_position_positions
UNION ALL
SELECT 'HR_position_audit_logs', COUNT(*) FROM HR_position_audit_logs
UNION ALL
SELECT 'HR_position_reference_codes (保留)', COUNT(*) FROM HR_position_reference_codes;

-- ============================================================
-- 完成訊息
-- ============================================================
SELECT '資料庫清空完成！參照代碼已保留。' as message;