-- ====================================
-- HR Portal - 測試資料 (修正版)
-- ====================================

-- 插入測試員工
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
        WHEN 'Manager' THEN 10240
        WHEN 'Senior' THEN 5120
        ELSE 5120
    END as quota,
    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 'Manager' THEN 100
        WHEN 'Senior' THEN 80
        ELSE 50
    END as quota,
    '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'
        WHEN e.job_level = 'Manager' THEN 'manager'
        ELSE 'user'
    END,
    true,
    'Initial access granted'
FROM employees e;

-- 額外權限: Gitea
INSERT INTO system_permissions (employee_id, system_name, access_level, is_active, notes)
SELECT e.id, 'Gitea', 'user', true, 'Git repository access'
FROM employees e
WHERE e.business_unit_id = 3;  -- 智能研發事業部

-- 額外權限: Portainer (給 IT 人員)
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
-- WIND-2024-001 團隊
((SELECT id FROM projects WHERE project_code = 'WIND-2024-001'),
 (SELECT id FROM employees WHERE username = 'charlie.lin'),
 'Project Manager'),

-- CARBON-2024-001 團隊
((SELECT id FROM projects WHERE project_code = 'CARBON-2024-001'),
 (SELECT id FROM employees WHERE username = 'diana.wu'),
 'Project Manager'),

-- AI-2024-001 團隊
((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');

-- 驗證結果
SELECT '=== 員工列表 ===' as info;
SELECT
    e.employee_id,
    e.username,
    e.chinese_name,
    bu.name as business_unit,
    e.position,
    e.job_level
FROM employees e
LEFT JOIN business_units bu ON e.business_unit_id = bu.id
ORDER BY e.employee_id;

SELECT '=== 郵件帳號 ===' as info;
SELECT
    e.username,
    ea.email_address,
    ea.mailbox_quota_mb || ' MB' as quota
FROM email_accounts ea
JOIN employees e ON ea.employee_id = e.id
ORDER BY e.employee_id;

SELECT '=== 網路硬碟 ===' as info;
SELECT
    e.username,
    nd.drive_name,
    nd.quota_gb || ' GB' as quota
FROM network_drives nd
JOIN employees e ON nd.employee_id = e.id
ORDER BY e.employee_id;

SELECT '=== 系統權限 ===' as info;
SELECT
    e.username,
    sp.system_name,
    sp.access_level
FROM system_permissions sp
JOIN employees e ON sp.employee_id = e.id
WHERE sp.is_active = true
ORDER BY e.employee_id, sp.system_name;

SELECT '=== 專案列表 ===' as info;
SELECT
    p.project_code,
    p.project_name,
    e.chinese_name as manager,
    COUNT(pm.id) as members,
    p.status
FROM projects p
LEFT JOIN employees e ON p.project_manager_id = e.id
LEFT JOIN project_members pm ON p.id = pm.project_id
GROUP BY p.id, p.project_code, p.project_name, e.chinese_name, p.status
ORDER BY p.project_code;

SELECT '=== 完成! ===' as info;
SELECT
    (SELECT COUNT(*) FROM employees) as employees,
    (SELECT COUNT(*) FROM email_accounts) as email_accounts,
    (SELECT COUNT(*) FROM network_drives) as network_drives,
    (SELECT COUNT(*) FROM system_permissions WHERE is_active = true) as permissions,
    (SELECT COUNT(*) FROM projects) as projects;