#!/usr/bin/env python3
"""
Детальный анализ сотрудников из tblPREmployee
Анализ PII данных: SSN, имена, даты рождения, адреса
"""
import json
from pathlib import Path
from collections import defaultdict, Counter
import re

print("=== ДЕТАЛЬНЫЙ АНАЛИЗ СОТРУДНИКОВ ===\n")

# Загрузка данных из всех баз
DUMPS_DIR = Path('/root/dumps/50.21.183.111')
OUTPUT_FILE = Path('/root/ir-assessment/redteam/irelydata/employee_analysis.json')

employees = []
company_stats = defaultdict(lambda: {
    'total': 0,
    'with_ssn': 0,
    'with_email': 0,
    'with_phone': 0,
    'with_birthdate': 0
})

print(f"Поиск файлов сотрудников в {DUMPS_DIR}...")

employee_files = list(DUMPS_DIR.rglob('tblPREmployee.dat'))
print(f"Найдено {len(employee_files)} файлов tblPREmployee.dat")

for emp_file in employee_files:
    db_name = emp_file.parent.name
    print(f"[{emp_file.name}] Загрузка...")
    
    try:
        with open(emp_file, 'r', encoding='utf-8', errors='ignore') as f:
            # Пропускаем заголовок
            for i in range(3):
                f.readline()
            
            # Читаем данные
            for line in f:
                parts = line.strip().split('|')
                if len(parts) >= 26:
                    emp_id = parts[0] if len(parts) > 0 else ''
                    employee_id = parts[1] if len(parts) > 1 else ''
                    emp_type = parts[2] if len(parts) > 2 else ''
                    first_name = parts[3] if len(parts) > 3 else ''
                    middle_name = parts[4] if len(parts) > 4 else ''
                    last_name = parts[5] if len(parts) > 5 else ''
                    hire_date = parts[9] if len(parts) > 9 else ''
                    birth_date = parts[10] if len(parts) > 10 else ''
                    gender = parts[11] if len(parts) > 11 else ''
                    ssn = parts[18] if len(parts) > 18 else ''
                    phone = parts[24] if len(parts) > 24 else ''
                    phone2 = parts[25] if len(parts) > 25 else ''
                    
                    employee = {
                        'db_name': db_name,
                        'emp_id': emp_id,
                        'employee_id': employee_id,
                        'emp_type': emp_type,
                        'first_name': first_name,
                        'middle_name': middle_name,
                        'last_name': last_name,
                        'full_name': f"{first_name} {last_name}".strip(),
                        'ssn': ssn if ssn != 'NULL' else '',
                        'birth_date': birth_date if birth_date != 'NULL' else '',
                        'hire_date': hire_date if hire_date != 'NULL' else '',
                        'gender': gender if gender != 'NULL' else '',
                        'phone': phone if phone != 'NULL' else '',
                        'phone2': phone2 if phone2 != 'NULL' else ''
                    }
                    
                    employees.append(employee)
                    
                    # Статистика компании
                    company_stats[db_name]['total'] += 1
                    if employee['ssn']:
                        company_stats[db_name]['with_ssn'] += 1
                    if employee['phone']:
                        company_stats[db_name]['with_phone'] += 1
                    if employee['birth_date']:
                        company_stats[db_name]['with_birthdate'] += 1
    except Exception as e:
        print(f"  ✗ Ошибка: {e}")

print(f"\n{'='*60}")
print("АНАЛИЗ РЕЗУЛЬТАТОВ")
print("="*60)

print(f"\nВсего сотрудников: {len(employees)}")
print(f"Компаний: {len(company_stats)}")

# Анализ SSN
ssn_employees = [e for e in employees if e.get('ssn')]
print(f"\nСотрудников с SSN: {len(ssn_employees)}")

# Анализ телефонов
phone_employees = [e for e in employees if e.get('phone')]
print(f"Сотрудников с телефоном: {len(phone_employees)}")

# Анализ дат рождения
birthdate_employees = [e for e in employees if e.get('birth_date')]
print(f"Сотрудников с датой рождения: {len(birthdate_employees)}")

# Анализ пола
gender_counter = Counter(e['gender'] for e in employees if e.get('gender'))
if gender_counter:
    print(f"\n{'='*60}")
    print("РАСПРЕДЕЛЕНИЕ ПО ПОЛУ")
    print("="*60)
    for gender, count in gender_counter.most_common():
        print(f"{gender}: {count} сотрудников")

# Построение результата
result = {
    'total_employees': len(employees),
    'total_companies': len(company_stats),
    'with_ssn': len(ssn_employees),
    'with_phone': len(phone_employees),
    'with_birthdate': len(birthdate_employees),
    'gender_distribution': dict(gender_counter.most_common()),
    'company_stats': dict(company_stats),
    'sample_employees': employees[:50]  # Первые 50 для примера
}

# Сохранение
print(f"\n{'='*60}")
print("СОХРАНЕНИЕ РЕЗУЛЬТАТОВ")
print("="*60)

with open(OUTPUT_FILE, 'w', encoding='utf-8') as f:
    json.dump(result, f, indent=2, ensure_ascii=False)

print(f"✓ Результаты сохранены в {OUTPUT_FILE}")
print(f"✓ Всего записей: {len(employees)}")