-- ============================================================================
-- HR Portal Database Schema Test Script
-- 用途: 驗證資料庫 schema 是否正確創建
-- 使用方法: psql -U postgres -d hr_portal -f test_schema.sql
-- ============================================================================

\echo ''
\echo '========================================='
\echo 'HR Portal Database Schema 測試'
\echo '========================================='
\echo ''

-- ============================================================================
-- 1. 測試表格是否存在
-- ============================================================================
\echo '1. 檢查表格是否存在...'
\echo ''

SELECT
    table_name,
    CASE
        WHEN table_name IN ('employees', 'business_units', 'departments',
                           'employee_identities', 'network_drives', 'audit_logs')
        THEN '✓ 存在'
        ELSE '✗ 不存在'
    END AS status
FROM information_schema.tables
WHERE table_schema = 'public'
  AND table_type = 'BASE TABLE'
ORDER BY table_name;

\echo ''

-- ============================================================================
-- 2. 測試視圖是否存在
-- ============================================================================
\echo '2. 檢查視圖是否存在...'
\echo ''

SELECT
    table_name AS view_name,
    '✓ 存在' AS status
FROM information_schema.views
WHERE table_schema = 'public'
ORDER BY table_name;

\echo ''

-- ============================================================================
-- 3. 測試初始資料是否存在
-- ============================================================================
\echo '3. 檢查初始資料...'
\echo ''

\echo '事業部資料:'
SELECT id, name, code, email_domain FROM business_units ORDER BY id;

\echo ''
\echo '部門資料:'
SELECT id, business_unit_id, name, code FROM departments ORDER BY id;

\echo ''

-- ============================================================================
-- 4. 測試外鍵約束
-- ============================================================================
\echo '4. 檢查外鍵約束...'
\echo ''

SELECT
    tc.table_name,
    kcu.column_name,
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
    ON tc.constraint_name = kcu.constraint_name
    AND tc.table_schema = kcu.table_schema
JOIN information_schema.constraint_column_usage AS ccu
    ON ccu.constraint_name = tc.constraint_name
    AND ccu.table_schema = tc.table_schema
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND tc.table_schema = 'public'
ORDER BY tc.table_name, kcu.column_name;

\echo ''

-- ============================================================================
-- 5. 測試唯一約束
-- ============================================================================
\echo '5. 檢查唯一約束...'
\echo ''

SELECT
    tc.table_name,
    tc.constraint_name,
    kcu.column_name
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
    ON tc.constraint_name = kcu.constraint_name
    AND tc.table_schema = kcu.table_schema
WHERE tc.constraint_type = 'UNIQUE'
  AND tc.table_schema = 'public'
ORDER BY tc.table_name, kcu.column_name;

\echo ''

-- ============================================================================
-- 6. 測試索引
-- ============================================================================
\echo '6. 檢查索引...'
\echo ''

SELECT
    schemaname,
    tablename,
    indexname
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY tablename, indexname;

\echo ''

-- ============================================================================
-- 7. 測試插入員工資料 (模擬真實場景)
-- ============================================================================
\echo '7. 測試插入員工資料...'
\echo ''

BEGIN;

-- 插入員工
INSERT INTO employees (employee_id, username_base, legal_name, english_name, phone, hire_date, status)
VALUES ('EMP001', 'porsche.chen', '陳保時', 'Porsche Chen', '02-1234-5678', '2020-01-01', 'active')
RETURNING id, employee_id, username_base, legal_name;

-- 插入第一個身份 (智能發展部 - 資訊部)
INSERT INTO employee_identities (
    employee_id,
    username,
    keycloak_id,
    business_unit_id,
    department_id,
    job_title,
    job_level,
    is_primary,
    email_quota_mb,
    started_at,
    is_active
)
VALUES (
    (SELECT id FROM employees WHERE username_base = 'porsche.chen'),
    'porsche.chen@lab.taipei',
    'test-keycloak-uuid-001',
    (SELECT id FROM business_units WHERE code = 'smart'),
    (SELECT id FROM departments WHERE code = 'it' AND business_unit_id = (SELECT id FROM business_units WHERE code = 'smart')),
    '技術總監',
    'Senior',
    true,
    5000,
    '2020-01-01',
    true
)
RETURNING id, username, job_title, is_primary;

-- 插入第二個身份 (業務發展部 - 顧問部)
INSERT INTO employee_identities (
    employee_id,
    username,
    keycloak_id,
    business_unit_id,
    department_id,
    job_title,
    job_level,
    is_primary,
    email_quota_mb,
    started_at,
    is_active
)
VALUES (
    (SELECT id FROM employees WHERE username_base = 'porsche.chen'),
    'porsche.chen@ease.taipei',
    'test-keycloak-uuid-002',
    (SELECT id FROM business_units WHERE code = 'biz'),
    (SELECT id FROM departments WHERE code = 'consulting' AND business_unit_id = (SELECT id FROM business_units WHERE code = 'biz')),
    '資深顧問',
    'Senior',
    false,
    5000,
    '2021-06-01',
    true
)
RETURNING id, username, job_title, is_primary;

-- 插入 NAS 帳號
INSERT INTO network_drives (
    employee_id,
    drive_name,
    quota_gb,
    webdav_url,
    smb_url,
    is_active
)
VALUES (
    (SELECT id FROM employees WHERE username_base = 'porsche.chen'),
    'porsche.chen',
    200,
    'https://nas.lab.taipei/webdav/porsche.chen',
    '\\10.1.0.30\porsche.chen',
    true
)
RETURNING id, drive_name, quota_gb;

\echo ''
\echo '測試查詢 v_employee_full_info 視圖:'
SELECT
    emp_no,
    username_base,
    legal_name,
    sso_username,
    job_title,
    job_level,
    business_unit_name,
    department_name,
    drive_name,
    nas_quota_gb
FROM v_employee_full_info
WHERE username_base = 'porsche.chen'
ORDER BY is_primary DESC;

ROLLBACK;

\echo ''
\echo '✓ 測試資料已回滾 (不影響資料庫)'
\echo ''

-- ============================================================================
-- 8. 測試唯一約束 (一個員工在同一事業部只能有一個身份)
-- ============================================================================
\echo '8. 測試唯一約束 (一個員工在同一事業部只能有一個身份)...'
\echo ''

DO $$
BEGIN
    BEGIN
        -- 嘗試插入重複身份 (應該失敗)
        INSERT INTO employees (employee_id, username_base, legal_name, hire_date)
        VALUES ('TEST001', 'test.user', '測試用戶', CURRENT_DATE);

        INSERT INTO employee_identities (
            employee_id, username, keycloak_id, business_unit_id,
            job_title, job_level, email_quota_mb, started_at
        )
        VALUES (
            (SELECT id FROM employees WHERE employee_id = 'TEST001'),
            'test.user@lab.taipei',
            'test-uuid-1',
            (SELECT id FROM business_units WHERE code = 'smart'),
            '測試職位1',
            'Junior',
            1000,
            CURRENT_DATE
        );

        INSERT INTO employee_identities (
            employee_id, username, keycloak_id, business_unit_id,
            job_title, job_level, email_quota_mb, started_at
        )
        VALUES (
            (SELECT id FROM employees WHERE employee_id = 'TEST001'),
            'test.user@lab.taipei',
            'test-uuid-2',
            (SELECT id FROM business_units WHERE code = 'smart'),
            '測試職位2',
            'Junior',
            1000,
            CURRENT_DATE
        );

        RAISE NOTICE '✗ 唯一約束測試失敗: 允許插入重複身份';
        ROLLBACK;
    EXCEPTION
        WHEN unique_violation THEN
            RAISE NOTICE '✓ 唯一約束測試成功: 正確阻止重複身份';
            ROLLBACK;
    END;
END $$;

\echo ''

-- ============================================================================
-- 9. 統計資訊
-- ============================================================================
\echo '9. 資料庫統計資訊...'
\echo ''

SELECT
    'employees' AS table_name,
    COUNT(*) AS row_count
FROM employees
UNION ALL
SELECT 'business_units', COUNT(*) FROM business_units
UNION ALL
SELECT 'departments', COUNT(*) FROM departments
UNION ALL
SELECT 'employee_identities', COUNT(*) FROM employee_identities
UNION ALL
SELECT 'network_drives', COUNT(*) FROM network_drives
UNION ALL
SELECT 'audit_logs', COUNT(*) FROM audit_logs
ORDER BY table_name;

\echo ''
\echo '========================================='
\echo '測試完成!'
\echo '========================================='
\echo ''