-- ====================================
-- HR Portal - 測試資料
-- ====================================
-- 用途: 插入測試資料以驗證系統功能
-- ====================================

-- 檢查事業部資料
SELECT '=== 事業部資料 ===' as info;
SELECT id, code, name, name_en FROM business_units ORDER BY id;

-- 檢查部門資料
SELECT '=== 部門資料 ===' as info;
SELECT id, business_unit_id, code, name, name_en FROM divisions ORDER BY id;

-- 插入測試員工
INSERT INTO employees (
    employee_id,
    username,
    first_name,
    last_name,
    chinese_name,
    email,
    mobile,
    business_unit_id,
    division_id,
    position,
    job_title,
    job_level,
    employment_type,
    hire_date,
    status
) VALUES
-- 管理層
(
    'E0001',
    'porsche.chen',
    'Porsche',
    'Chen',
    '陳博駿',
    'porsche.chen@porscheworld.tw',
    '0912-345-678',
    4,  -- 管理部門
    10, -- 資訊部
    'CTO',
    '技術長',
    'C-Level',
    'full-time',
    '2020-01-01',
    'active'
),

-- 智能研發服務 - 軟體工程師
(
    'E1001',
    'alice.wang',
    'Alice',
    'Wang',
    '王小華',
    'alice.wang@lab.taipei',
    '0923-456-789',
    3,  -- 智能研發服務
    7,  -- 軟體研發部
    'Frontend Developer',
    '前端工程師',
    'Engineer',
    'full-time',
    '2024-03-01',
    'active'
),
(
    'E1002',
    'bob.chen',
    'Bob',
    'Chen',
    '陳大明',
    'bob.chen@lab.taipei',
    '0934-567-890',
    3,  -- 智能研發服務
    7,  -- 軟體研發部
    'Backend Developer',
    '後端工程師',
    'Senior',
    'full-time',
    '2023-06-15',
    'active'
),

-- 玄鐵風能授權服務
(
    'E2001',
    'charlie.lin',
    'Charlie',
    'Lin',
    '林志明',
    'charlie.lin@ease.taipei',
    '0945-678-901',
    1,  -- 玄鐵風能
    1,  -- 技術授權部
    'Sales Manager',
    '業務經理',
    'Manager',
    'full-time',
    '2022-08-20',
    'active'
),

-- 國際碳權申請服務
(
    'E3001',
    'diana.wu',
    'Diana',
    'Wu',
    '吳雅婷',
    'diana.wu@ease.taipei',
    '0956-789-012',
    2,  -- 國際碳權
    4,  -- 碳權申請部
    'Carbon Credit Consultant',
    '碳權顧問',
    'Senior',
    'full-time',
    '2023-02-10',
    'active'
)

ON CONFLICT (employee_id) DO NOTHING;

-- 插入測試郵件帳號
INSERT INTO email_accounts (
    employee_id,
    email_address,
    mailbox_quota_mb,
    is_active
)
SELECT
    id,
    email,
    CASE
        WHEN job_level = 'C-Level' THEN 5120  -- 5GB
        WHEN job_level = 'Manager' THEN 2048  -- 2GB
        ELSE 1024  -- 1GB
    END,
    TRUE
FROM employees
WHERE email IS NOT NULL
ON CONFLICT (email_address) DO NOTHING;

-- 插入測試網路硬碟
INSERT INTO network_drives (
    employee_id,
    drive_name,
    drive_path,
    quota_gb,
    webdav_url,
    smb_path,
    is_active
)
SELECT
    id,
    username || '_personal',
    '/volume1/homes/' || username,
    CASE
        WHEN job_level = 'C-Level' THEN 50
        WHEN job_level = 'VP' THEN 30
        WHEN job_level = 'Director' THEN 30
        WHEN job_level = 'Manager' THEN 20
        WHEN job_level = 'Senior' THEN 15
        ELSE 10
    END,
    'https://nas.porscheworld.tw/webdav/' || username,
    '\\10.1.0.30\homes\' || username,
    TRUE
FROM employees
ON CONFLICT DO NOTHING;

-- 插入測試系統權限
INSERT INTO system_permissions (
    employee_id,
    system_name,
    system_url,
    access_level,
    is_active,
    granted_by
)
SELECT
    id,
    system,
    url,
    access_level,
    TRUE,
    'system'
FROM employees
CROSS JOIN (
    VALUES
        ('HR Portal', 'https://hr.porscheworld.tw', 'user'),
        ('Webmail', 'https://webmail.porscheworld.tw', 'user')
) AS systems(system, url, access_level)
WHERE status = 'active'
ON CONFLICT (employee_id, system_name) DO NOTHING;

-- 為研發人員新增額外權限
INSERT INTO system_permissions (
    employee_id,
    system_name,
    system_url,
    access_level,
    is_active,
    granted_by
)
SELECT
    id,
    system,
    url,
    access_level,
    TRUE,
    'system'
FROM employees
CROSS JOIN (
    VALUES
        ('Gitea', 'https://git.lab.taipei', 'developer'),
        ('Portainer', 'https://portainer.porscheworld.tw', 'user')
) AS dev_systems(system, url, access_level)
WHERE division_id IN (7, 8, 9)  -- 研發部門
AND status = 'active'
ON CONFLICT (employee_id, system_name) DO NOTHING;

-- 插入測試專案
INSERT INTO projects (
    business_unit_id,
    project_code,
    project_name,
    description,
    client_name,
    status,
    project_manager_id,
    start_date,
    end_date,
    budget_amount,
    budget_currency
) VALUES
(
    3,  -- 智能研發
    'PRJ-2024-001',
    'HR Portal 開發專案',
    '企業人資管理系統開發',
    '內部專案',
    'active',
    (SELECT id FROM employees WHERE username = 'porsche.chen'),
    '2024-01-15',
    '2024-06-30',
    1000000.00,
    'TWD'
),
(
    1,  -- 玄鐵風能
    'PRJ-2024-002',
    '台中風場評估案',
    '台中離岸風場技術評估與授權',
    '台灣風電公司',
    'active',
    (SELECT id FROM employees WHERE username = 'charlie.lin'),
    '2024-02-01',
    '2024-08-31',
    5000000.00,
    'TWD'
),
(
    2,  -- 國際碳權
    'PRJ-2024-003',
    '某企業碳盤查專案',
    'ISO 14064-1 組織型溫室氣體盤查',
    '科技公司 A',
    'planning',
    (SELECT id FROM employees WHERE username = 'diana.wu'),
    '2024-03-01',
    '2024-12-31',
    800000.00,
    'TWD'
)
ON CONFLICT (project_code) DO NOTHING;

-- 插入專案成員
INSERT INTO project_members (
    project_id,
    employee_id,
    role,
    allocation_percentage
)
SELECT
    p.id,
    e.id,
    member.role,
    member.allocation
FROM projects p
CROSS JOIN LATERAL (
    VALUES
        ((SELECT id FROM employees WHERE username = 'porsche.chen'), 'project-manager', 50),
        ((SELECT id FROM employees WHERE username = 'alice.wang'), 'developer', 80),
        ((SELECT id FROM employees WHERE username = 'bob.chen'), 'developer', 100)
) AS member(employee_id, role, allocation)
JOIN employees e ON e.id = member.employee_id
WHERE p.project_code = 'PRJ-2024-001'
ON CONFLICT (project_id, employee_id) DO NOTHING;

-- ====================================
-- 查詢驗證資料
-- ====================================

SELECT '=== 員工資料 ===' as info;
SELECT
    employee_id,
    username,
    chinese_name,
    email,
    position,
    job_level,
    status
FROM employees
ORDER BY employee_id;

SELECT '=== 郵件帳號 ===' as info;
SELECT
    e.username,
    ea.email_address,
    ea.mailbox_quota_mb || 'MB' as quota,
    ea.is_active
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,
    nd.is_active
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,
    sp.is_active
FROM system_permissions sp
JOIN employees e ON sp.employee_id = e.id
ORDER BY e.username, sp.system_name;

SELECT '=== 專案資訊 ===' as info;
SELECT
    p.project_code,
    p.project_name,
    p.status,
    e.chinese_name as project_manager,
    COUNT(pm.id) as member_count
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, p.status, e.chinese_name
ORDER BY p.project_code;

-- 統計資訊
SELECT '=== 統計資訊 ===' as info;
SELECT
    '員工總數' as metric,
    COUNT(*) as count
FROM employees
UNION ALL
SELECT
    '活躍員工',
    COUNT(*)
FROM employees
WHERE status = 'active'
UNION ALL
SELECT
    '郵件帳號',
    COUNT(*)
FROM email_accounts
UNION ALL
SELECT
    '網路硬碟',
    COUNT(*)
FROM network_drives
UNION ALL
SELECT
    '活躍專案',
    COUNT(*)
FROM projects
WHERE status = 'active';

SELECT '=== 資料插入完成 ===' as info;