========================================
HR Portal 一鍵完成腳本
在 Ubuntu Server (10.1.0.254) 執行
========================================

用 PuTTY 登入: porsche@10.1.0.254
複製以下所有命令並執行:

========================================
開始執行
========================================

echo "=========================================="
echo "  HR Portal 資料庫初始化"
echo "=========================================="
echo ""

# 插入測試資料
docker exec -i hr-postgres psql -U hr_user -d hr_portal <<'SQLEND'
INSERT INTO employees (employee_id, username, first_name, last_name, chinese_name, email, mobile, business_unit_id, position, job_level, hire_date, status) VALUES
('E0001', 'porsche.chen', 'Porsche', 'Chen', '陳博駿', 'porsche.chen@porscheworld.tw', '0912-345-678', 4, 'CTO / 技術長', 'C-Level', '2020-01-01', 'active'),
('E1001', 'alice.wang', 'Alice', 'Wang', '王小華', 'alice.wang@lab.taipei', '0922-111-222', 3, 'Technical Director', 'Director', '2020-06-01', 'active'),
('E1002', 'bob.chen', 'Bob', 'Chen', '陳大明', 'bob.chen@lab.taipei', '0933-222-333', 3, 'Senior Software Engineer', 'Senior', '2021-03-15', 'active'),
('E2001', 'charlie.lin', 'Charlie', 'Lin', '林小風', 'charlie.lin@ease.taipei', '0944-333-444', 1, 'Wind Energy Consultant', 'Senior', '2021-08-01', 'active'),
('E3001', 'diana.wu', 'Diana', 'Wu', '吳小綠', 'diana.wu@ease.taipei', '0955-444-555', 2, 'Carbon Credit Specialist', 'Staff', '2022-01-10', 'active');

INSERT INTO email_accounts (employee_id, email_address, mailbox_quota_mb, is_active)
SELECT e.id, e.email, CASE e.job_level WHEN 'C-Level' THEN 20480 WHEN 'Director' THEN 10240 ELSE 5120 END, true FROM employees e;

INSERT INTO network_drives (employee_id, drive_name, drive_path, quota_gb, webdav_url, smb_path, is_active)
SELECT e.id, e.username || '_personal', '/nas/users/' || e.username, CASE e.job_level WHEN 'C-Level' THEN 500 WHEN 'Director' THEN 200 WHEN 'Senior' THEN 80 ELSE 50 END, 'https://nas.porscheworld.tw/webdav/' || e.username, '//10.1.0.30/homes/' || e.username, true FROM employees e;

INSERT INTO system_permissions (employee_id, system_name, access_level, is_active, notes)
SELECT e.id, 'HR Portal', CASE WHEN e.job_level IN ('C-Level', 'Director') THEN 'admin' ELSE 'user' END, true, 'Initial access' FROM employees e;

INSERT INTO system_permissions (employee_id, system_name, access_level, is_active, notes)
SELECT e.id, 'Gitea', 'user', true, 'Git access' FROM employees e WHERE e.business_unit_id = 3;

INSERT INTO system_permissions (employee_id, system_name, access_level, is_active, notes)
VALUES ((SELECT id FROM employees WHERE username = 'porsche.chen'), 'Portainer', 'admin', true, 'Docker management');

INSERT INTO projects (project_code, project_name, description, project_manager_id, start_date, status) VALUES
('WIND-2024-001', '離岸風電技術評估', '台中港離岸風電場技術可行性評估', (SELECT id FROM employees WHERE username = 'charlie.lin'), '2024-01-15', 'in_progress'),
('CARBON-2024-001', '企業碳盤查輔導', '協助製造業進行 ISO 14064-1 碳盤查', (SELECT id FROM employees WHERE username = 'diana.wu'), '2024-02-01', 'in_progress'),
('AI-2024-001', 'HR Portal 系統開發', '開發整合 Keycloak 的人資管理系統', (SELECT id FROM employees WHERE username = 'alice.wang'), '2024-01-01', 'in_progress');

INSERT INTO project_members (project_id, employee_id, role) VALUES
((SELECT id FROM projects WHERE project_code = 'WIND-2024-001'), (SELECT id FROM employees WHERE username = 'charlie.lin'), 'Project Manager'),
((SELECT id FROM projects WHERE project_code = 'CARBON-2024-001'), (SELECT id FROM employees WHERE username = 'diana.wu'), 'Project Manager'),
((SELECT id FROM projects WHERE project_code = 'AI-2024-001'), (SELECT id FROM employees WHERE username = 'alice.wang'), 'Project Manager'),
((SELECT id FROM projects WHERE project_code = 'AI-2024-001'), (SELECT id FROM employees WHERE username = 'bob.chen'), 'Lead Developer'),
((SELECT id FROM projects WHERE project_code = 'AI-2024-001'), (SELECT id FROM employees WHERE username = 'porsche.chen'), 'Technical Advisor');
SQLEND

echo ""
echo "=========================================="
echo "  驗證結果"
echo "=========================================="

docker exec hr-postgres psql -U hr_user -d hr_portal -c "SELECT COUNT(*) as employees FROM employees;"
docker exec hr-postgres psql -U hr_user -d hr_portal -c "SELECT COUNT(*) as email_accounts FROM email_accounts;"
docker exec hr-postgres psql -U hr_user -d hr_portal -c "SELECT COUNT(*) as network_drives FROM network_drives;"
docker exec hr-postgres psql -U hr_user -d hr_portal -c "SELECT COUNT(*) as projects FROM projects;"

echo ""
echo "=========================================="
echo "  完成!"
echo "=========================================="
echo ""
echo "測試資料已插入:"
echo "  - 5 個員工"
echo "  - 5 個郵件帳號"
echo "  - 5 個網路硬碟"
echo "  - 3 個專案"
echo ""
echo "請回到 Windows 告訴 Claude '完成'"
echo ""

========================================
執行結束
========================================