import pandas as pd
from sqlalchemy import create_engine

# SQLite (파일 기반, 설치 불필요)
engine = create_engine('sqlite:///sample.db')

# PostgreSQL
# engine = create_engine('postgresql://user:password@host:5432/dbname')

# MySQL
# engine = create_engine('mysql+pymysql://user:password@host:3306/dbname')

import numpy as np

np.random.seed(42)

# 예시 테이블 생성
employees = pd.DataFrame({
    'id': range(1, 201),
    'name': [f'직원_{i}' for i in range(1, 201)],
    'department': np.random.choice(['개발', '영업', '마케팅', '인사'], 200),
    'salary': np.random.normal(4500, 800, 200).round(0),
    'experience': np.random.uniform(1, 20, 200).round(1),
    'hire_date': pd.date_range('2015-01-01', periods=200, freq='15D')
})

orders = pd.DataFrame({
    'order_id': range(1, 501),
    'employee_id': np.random.randint(1, 201, 500),
    'amount': np.random.uniform(100, 10000, 500).round(0),
    'order_date': pd.date_range('2024-01-01', periods=500, freq='6H')
})

# 테이블 저장
employees.to_sql('employees', engine, if_exists='replace', index=False)
orders.to_sql('orders', engine, if_exists='replace', index=False)
print("테이블 생성 완료")

# 전체 테이블 읽기
df = pd.read_sql('SELECT * FROM employees', engine)
print(f"직원 수: {len(df)}")
print(df.head())

# 조건 필터링
dev_team = pd.read_sql(
    "SELECT * FROM employees WHERE department = '개발' AND salary > 5000",
    engine
)
print(f"\n개발팀 고연봉: {len(dev_team)}명")

# 집계 쿼리
dept_stats = pd.read_sql("""
    SELECT department,
           COUNT(*) as count,
           ROUND(AVG(salary), 0) as avg_salary,
           ROUND(AVG(experience), 1) as avg_experience
    FROM employees
    GROUP BY department
    ORDER BY avg_salary DESC
""", engine)
print("\n부서별 통계:")
print(dept_stats)

# 직원별 주문 현황 조인
employee_orders = pd.read_sql("""
    SELECT e.name, e.department, e.salary,
           COUNT(o.order_id) as order_count,
           COALESCE(SUM(o.amount), 0) as total_amount
    FROM employees e
    LEFT JOIN orders o ON e.id = o.employee_id
    GROUP BY e.id, e.name, e.department, e.salary
    ORDER BY total_amount DESC
    LIMIT 10
""", engine)
print("상위 10명 주문 실적:")
print(employee_orders)

# SQL 인젝션 방지: 파라미터 바인딩
from sqlalchemy import text

department = '개발'
min_salary = 4000

# 올바른 방법: 파라미터 바인딩
query = text("""
    SELECT * FROM employees
    WHERE department = :dept AND salary > :min_sal
""")
df_safe = pd.read_sql(query, engine, params={'dept': department, 'min_sal': min_salary})
print(f"파라미터 쿼리 결과: {len(df_safe)}행")

# 분석 결과를 새 테이블로 저장
analysis_result = pd.read_sql("""
    SELECT department,
           AVG(salary) as avg_salary,
           COUNT(*) as employee_count
    FROM employees
    GROUP BY department
""", engine)

# 데이터베이스에 저장
analysis_result.to_sql(
    'department_analysis',
    engine,
    if_exists='replace',  # 기존 테이블 덮어쓰기
    index=False
)

# 저장 확인
verify = pd.read_sql('SELECT * FROM department_analysis', engine)
print("저장된 분석 결과:")
print(verify)

# chunksize로 분할 읽기
chunks = pd.read_sql('SELECT * FROM orders', engine, chunksize=100)

total_amount = 0
for i, chunk in enumerate(chunks):
    total_amount += chunk['amount'].sum()
    print(f"  청크 {i+1}: {len(chunk)}행, 누적 금액: {total_amount:,.0f}")

print(f"\n총 주문 금액: {total_amount:,.0f}")