import mysql.connector

# 데이터베이스 연결
config = {
    'user': 'username',
    'password': 'password',
    'host': 'localhost',
    'database': 'mydatabase',
    'raise_on_warnings': True,
    'charset': 'utf8mb4',
    'collation': 'utf8mb4_unicode_ci',
    'autocommit': False
}

conn = mysql.connector.connect(**config)
cursor = conn.cursor(dictionary=True)  # 결과를 딕셔너리 형태로 반환

# 환경 변수를 통한 연결 문자열 관리
import os
from dotenv import load_dotenv

load_dotenv()  # .env 파일에서 환경 변수 로드
db_config = {
    'user': os.getenv('MYSQL_USER'),
    'password': os.getenv('MYSQL_PASSWORD'),
    'host': os.getenv('MYSQL_HOST'),
    'database': os.getenv('MYSQL_DATABASE')
}

secure_conn = mysql.connector.connect(**db_config)

# 연결 확인 및 종료
def check_connection():
    try:
        cursor.execute("SELECT VERSION()")
        version = cursor.fetchone()
        print(f"Database version: {version['VERSION()']}")
        return True
    except mysql.connector.Error as err:
        print(f"에러: {err}")
        return False
    finally:
        if conn.is_connected():
            print("연결 성공!")

# 연결 종료
def close_connection():
    if conn.is_connected():
        cursor.close()
        conn.close()
        print("MySQL 연결이 닫혔습니다.")

# 테이블 생성
def create_tables():
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS users (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            email VARCHAR(100) UNIQUE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
            INDEX idx_email (email),
            INDEX idx_status (status)
        )
    ''')
    
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS posts (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT NOT NULL,
            title VARCHAR(200) NOT NULL,
            content TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
            FULLTEXT INDEX idx_content (title, content)
        )
    ''')
    conn.commit()

# 데이터 삽입 (Create)
def insert_user(name, email, status='active'):
    try:
        cursor.execute('''
            INSERT INTO users (name, email, status)
            VALUES (%s, %s, %s)
        ''', (name, email, status))
        conn.commit()
        return cursor.lastrowid
    except mysql.connector.Error as err:
        print(f"에러 발생: {err}")
        conn.rollback()
        return None

# 다수 레코드 삽입
def insert_many_users(users_data):
    try:
        cursor.executemany('''
            INSERT INTO users (name, email, status)
            VALUES (%s, %s, %s)
        ''', users_data)
        conn.commit()
        return cursor.rowcount
    except mysql.connector.Error as err:
        print(f"에러 발생: {err}")
        conn.rollback()
        return 0

# 데이터 조회 (Read)
def get_user(user_id):
    cursor.execute('''
        SELECT * FROM users WHERE id = %s
    ''', (user_id,))
    return cursor.fetchone()

# 다수 레코드 조회
def get_users(limit=10, offset=0, status=None):
    query = "SELECT * FROM users"
    params = []
    
    if status:
        query += " WHERE status = %s"
        params.append(status)
    
    query += " ORDER BY created_at DESC LIMIT %s OFFSET %s"
    params.extend([limit, offset])
    
    cursor.execute(query, params)
    return cursor.fetchall()

# 데이터 조회 - JOIN 사용
def get_user_with_posts(user_id):
    cursor.execute('''
        SELECT u.*, p.id as post_id, p.title, p.content, p.created_at as post_created_at
        FROM users u
        LEFT JOIN posts p ON u.id = p.user_id
        WHERE u.id = %s
    ''', (user_id,))
    
    results = cursor.fetchall()
    if not results:
        return None
    
    user = {
        'id': results[0]['id'],
        'name': results[0]['name'],
        'email': results[0]['email'],
        'created_at': results[0]['created_at'],
        'status': results[0]['status'],
        'posts': []
    }
    
    for row in results:
        if row['post_id']:
            user['posts'].append({
                'id': row['post_id'],
                'title': row['title'],
                'content': row['content'],
                'created_at': row['post_created_at']
            })
    
    return user

# 데이터 수정 (Update)
def update_user(user_id, name=None, email=None, status=None):
    update_fields = []
    params = []
    
    if name:
        update_fields.append("name = %s")
        params.append(name)
    if email:
        update_fields.append("email = %s")
        params.append(email)
    if status:
        update_fields.append("status = %s")
        params.append(status)
    
    if not update_fields:
        return False
    
    query = f"UPDATE users SET {', '.join(update_fields)} WHERE id = %s"
    params.append(user_id)
    
    try:
        cursor.execute(query, params)
        conn.commit()
        return cursor.rowcount > 0
    except mysql.connector.Error as err:
        print(f"에러 발생: {err}")
        conn.rollback()
        return False

# 데이터 삭제 (Delete)
def delete_user(user_id):
    try:
        cursor.execute("DELETE FROM users WHERE id = %s", (user_id,))
        conn.commit()
        return cursor.rowcount > 0
    except mysql.connector.Error as err:
        print(f"에러 발생: {err}")
        conn.rollback()
        return False

# 조건부 삭제
def delete_inactive_users(days=90):
    try:
        cursor.execute('''
            DELETE FROM users 
            WHERE status = 'inactive' 
            AND updated_at < DATE_SUB(NOW(), INTERVAL %s DAY)
        ''', (days,))
        conn.commit()
        return cursor.rowcount
    except mysql.connector.Error as err:
        print(f"에러 발생: {err}")
        conn.rollback()
        return 0

# 사용 예시
def crud_example():
    # 테이블 생성
    create_tables()
    
    # 사용자 추가
    user_id = insert_user('홍길동', '[email protected]')
    print(f"추가된 사용자 ID: {user_id}")
    
    # 여러 사용자 추가
    users = [
        ('김철수', '[email protected]', 'active'),
        ('이영희', '[email protected]', 'active'),
        ('박지성', '[email protected]', 'inactive')
    ]
    inserted = insert_many_users(users)
    print(f"{inserted}명의 사용자가 추가되었습니다.")
    
    # 사용자 조회
    user = get_user(user_id)
    print(f"조회된 사용자: {user['name']} ({user['email']})")
    
    # 사용자 정보 수정
    update_user(user_id, name='홍길순', status='active')
    
    # 사용자 삭제
    deleted = delete_inactive_users(30)
    print(f"{deleted}명의 비활성 사용자가 삭제되었습니다.")

# 트랜잭션 처리
def transfer_money(from_account, to_account, amount):
    try:
        cursor.execute('START TRANSACTION')
        
        # 출금 계좌 조회
        cursor.execute('''
            SELECT balance FROM accounts WHERE id = %s FOR UPDATE
        ''', (from_account,))
        from_balance = cursor.fetchone()
        
        if not from_balance or from_balance['balance'] < amount:
            raise Exception("잔액 부족 또는 계좌 없음")
        
        # 출금
        cursor.execute('''
            UPDATE accounts 
            SET balance = balance - %s, 
                updated_at = NOW(),
                transaction_count = transaction_count + 1
            WHERE id = %s
        ''', (amount, from_account))
        
        # 입금 계좌 확인
        cursor.execute('''
            SELECT id FROM accounts WHERE id = %s FOR UPDATE
        ''', (to_account,))
        if not cursor.fetchone():
            raise Exception("대상 계좌 없음")
        
        # 입금
        cursor.execute('''
            UPDATE accounts 
            SET balance = balance + %s,
                updated_at = NOW(),
                transaction_count = transaction_count + 1
            WHERE id = %s
        ''', (amount, to_account))
        
        # 거래 내역 기록
        cursor.execute('''
            INSERT INTO transactions
            (from_account_id, to_account_id, amount, transaction_type, description)
            VALUES (%s, %s, %s, 'transfer', '계좌 이체')
        ''', (from_account, to_account, amount))
        
        cursor.execute('COMMIT')
        return True
    except Exception as e:
        cursor.execute('ROLLBACK')
        print(f"거래 실패: {e}")
        return False

# 스토어드 프로시저 생성 및 호출
def create_stored_procedures():
    # 사용자 상태 업데이트 프로시저
    cursor.execute('''
        DROP PROCEDURE IF EXISTS update_user_status
    ''')
    
    cursor.execute('''
        CREATE PROCEDURE update_user_status(
            IN user_id INT,
            IN new_status VARCHAR(20)
        )
        BEGIN
            UPDATE users
            SET status = new_status,
                updated_at = NOW()
            WHERE id = user_id;
            
            SELECT ROW_COUNT() AS affected_rows;
        END
    ''')
    
    # 비활성 사용자 정리 프로시저
    cursor.execute('''
        DROP PROCEDURE IF EXISTS cleanup_inactive_users
    ''')
    
    cursor.execute('''
        CREATE PROCEDURE cleanup_inactive_users(
            IN days_threshold INT
        )
        BEGIN
            DECLARE affected INT;
            
            START TRANSACTION;
            
            DELETE FROM users
            WHERE status = 'inactive'
            AND updated_at < DATE_SUB(NOW(), INTERVAL days_threshold DAY);
            
            SET affected = ROW_COUNT();
            
            COMMIT;
            
            SELECT affected AS deleted_users;
        END
    ''')
    
    conn.commit()

# 스토어드 프로시저 호출
def call_stored_procedure(user_id, new_status):
    try:
        cursor.callproc('update_user_status', (user_id, new_status))
        
        # 결과 가져오기
        for result in cursor.stored_results():
            return result.fetchone()['affected_rows'] > 0
    except mysql.connector.Error as err:
        print(f"프로시저 호출 에러: {err}")
        return False

# 고급 쿼리 - 집계 및 그룹화
def user_statistics():
    cursor.execute('''
        SELECT 
            status,
            COUNT(*) as user_count,
            MIN(created_at) as oldest_user,
            MAX(created_at) as newest_user,
            AVG(TIMESTAMPDIFF(DAY, created_at, NOW())) as avg_account_age_days
        FROM users
        GROUP BY status
        ORDER BY user_count DESC
    ''')
    
    return cursor.fetchall()

# 고급 쿼리 - 윈도우 함수 사용 (MySQL 8.0+)
def post_analytics():
    cursor.execute('''
        SELECT 
            p.*,
            COUNT(*) OVER(PARTITION BY DATE(p.created_at)) as posts_same_day,
            RANK() OVER(ORDER BY p.view_count DESC) as popularity_rank
        FROM posts p
        JOIN users u ON p.user_id = u.id
        WHERE u.status = 'active'
        ORDER BY p.created_at DESC
        LIMIT 100
    ''')
    
    return cursor.fetchall()

# 전체텍스트 검색
def search_posts(query):
    cursor.execute('''
        SELECT *
        FROM posts
        WHERE MATCH(title, content) AGAINST(%s IN NATURAL LANGUAGE MODE)
        ORDER BY MATCH(title, content) AGAINST(%s IN NATURAL LANGUAGE MODE) DESC
        LIMIT 20
    ''', (query, query))
    
    return cursor.fetchall()

from mysql.connector import pooling

class DatabasePool:
    _pool = None
    
    @classmethod
    def init_pool(cls, pool_name, pool_size=5, **kwargs):
        dbconfig = {
            "pool_name": pool_name,
            "pool_size": pool_size,
            "pool_reset_session": True,  # 연결 반환 시 세션 초기화
            **kwargs
        }
        cls._pool = mysql.connector.pooling.MySQLConnectionPool(**dbconfig)
        print(f"{pool_name} 풀이 {pool_size}개의 연결로 초기화되었습니다.")
    
    @classmethod
    def get_connection(cls):
        if cls._pool is None:
            raise Exception("풀이 초기화되지 않았습니다.")
        return cls._pool.get_connection()
    
    @classmethod
    def execute_query(cls, query, params=None, commit=False, dictionary=True):
        """풀에서 연결을 가져와 쿼리를 실행하고 결과를 반환한다"""
        conn = None
        cursor = None
        try:
            conn = cls.get_connection()
            cursor = conn.cursor(dictionary=dictionary)
            cursor.execute(query, params or ())
            
            result = None
            if query.strip().upper().startswith("SELECT"):
                result = cursor.fetchall()
            else:
                if commit:
                    conn.commit()
                result = {"affected_rows": cursor.rowcount, "last_id": cursor.lastrowid}
            
            return result
        except Exception as e:
            if conn and commit:
                conn.rollback()
            raise e
        finally:
            if cursor:
                cursor.close()
            if conn:
                conn.close()  # 풀에 반환

# 커넥션 풀 사용 예제
def connection_pool_example():
    # 풀 초기화
    DatabasePool.init_pool(
        "mypool", 
        pool_size=10, 
        user=os.getenv('MYSQL_USER'), 
        password=os.getenv('MYSQL_PASSWORD'),
        host=os.getenv('MYSQL_HOST'),
        database=os.getenv('MYSQL_DATABASE')
    )
    
    # 동시 쿼리 시뮬레이션
    def worker(user_id):
        try:
            result = DatabasePool.execute_query(
                "SELECT * FROM users WHERE id = %s", (user_id,)
            )
            return result[0] if result else None
        except Exception as e:
            print(f"쿼리 실행 중 오류: {e}")
            return None
    
    # 병렬 처리 예시
    import concurrent.futures
    
    with concurrent.futures.ThreadPoolExecutor(max_workers=5) as executor:
        user_ids = [1, 2, 3, 4, 5]
        results = list(executor.map(worker, user_ids))
        
        for user_id, result in zip(user_ids, results):
            if result:
                print(f"사용자 {user_id}: {result['name']}")
            else:
                print(f"사용자 {user_id}를 찾을 수 없습니다.")

# 대규모 애플리케이션을 위한 향상된 풀링
import queue
import threading
import time

class AdvancedConnectionPool:
    def __init__(self, config, min_connections=5, max_connections=20, timeout=30):
        self.config = config
        self.min_conn = min_connections
        self.max_conn = max_connections
        self.timeout = timeout
        self.connections = queue.Queue(maxsize=max_connections)
        self.active_connections = 0
        self.lock = threading.RLock()
        self.initialize()
    
    def initialize(self):
        for _ in range(self.min_conn):
            self._add_connection()
    
    def _add_connection(self):
        with self.lock:
            if self.active_connections >= self.max_conn:
                return False
            
            try:
                connection = mysql.connector.connect(**self.config)
                self.connections.put(connection)
                self.active_connections += 1
                return True
            except mysql.connector.Error as err:
                print(f"연결 생성 오류: {err}")
                return False
    
    def get_connection(self, timeout=None):
        timeout = timeout or self.timeout
        try:
            # 가능한 연결 확인
            connection = self.connections.get(block=True, timeout=timeout)
            
            # 연결 유효성 검사
            if not connection.is_connected():
                connection.reconnect()
            
            return connection
        except queue.Empty:
            # 풀에 사용 가능한 연결이 없으면 새로 생성 시도
            with self.lock:
                if self._add_connection():
                    return self.connections.get(block=False)
            
            raise Exception("연결 타임아웃: 사용 가능한 연결 없음")
    
    def release_connection(self, connection):
        if connection.is_connected():
            # 열린 트랜잭션이 있으면 롤백
            connection.rollback()
            self.connections.put(connection)
        else:
            # 끊어진 연결은 새로운 연결로 대체
            with self.lock:
                self.active_connections -= 1
                self._add_connection()
    
    def close_all(self):
        with self.lock:
            while not self.connections.empty():
                try:
                    conn = self.connections.get(block=False)
                    if conn.is_connected():
                        conn.close()
                except queue.Empty:
                    break
            
            self.active_connections = 0

class DatabaseMigration:
    def __init__(self, connection):
        self.conn = connection
        self.cursor = connection.cursor(dictionary=True)
    
    def create_migrations_table(self):
        self.cursor.execute('''
            CREATE TABLE IF NOT EXISTS migrations (
                id INT AUTO_INCREMENT PRIMARY KEY,
                migration_name VARCHAR(255) NOT NULL UNIQUE,
                batch INT NOT NULL,
                executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')
        self.conn.commit()
    
    def get_executed_migrations(self):
        self.cursor.execute("SELECT migration_name FROM migrations")
        return [row['migration_name'] for row in self.cursor.fetchall()]
    
    def record_migration(self, migration_name, batch):
        self.cursor.execute(
            "INSERT INTO migrations (migration_name, batch) VALUES (%s, %s)",
            (migration_name, batch)
        )
        self.conn.commit()
    
    def migrate(self, migrations_directory):
        self.create_migrations_table()
        executed = self.get_executed_migrations()
        
        # 마이그레이션 파일 찾기
        import os
        import importlib.util
        import re
        
        migration_files = [f for f in os.listdir(migrations_directory) 
                          if f.endswith('.py') and re.match(r'\d+_\w+\.py', f)]
        migration_files.sort()  # 날짜순/이름순 정렬
        
        # 가장 최근 뱃치 번호 찾기
        self.cursor.execute("SELECT MAX(batch) as max_batch FROM migrations")
        result = self.cursor.fetchone()
        current_batch = (result['max_batch'] or 0) + 1
        
        executed_count = 0
        for filename in migration_files:
            migration_name = filename[:-3]  # .py 제거
            
            if migration_name in executed:
                continue  # 이미 실행된 마이그레이션 건너뛰기
            
            # 마이그레이션 모듈 동적 로드 및 실행
            file_path = os.path.join(migrations_directory, filename)
            spec = importlib.util.spec_from_file_location(migration_name, file_path)
            module = importlib.util.module_from_spec(spec)
            spec.loader.exec_module(module)
            
            print(f"마이그레이션 실행: {migration_name}")
            
            # 마이그레이션 실행
            try:
                if hasattr(module, 'up'):
                    module.up(self.cursor)
                    self.conn.commit()
                    self.record_migration(migration_name, current_batch)
                    executed_count += 1
                else:
                    print(f"경고: {migration_name}에 up() 함수가 없습니다.")
            except Exception as e:
                self.conn.rollback()
                print(f"마이그레이션 오류 {migration_name}: {e}")
                break
        
        return executed_count
    
    def rollback(self, steps=1):
        self.cursor.execute(
            "SELECT migration_name FROM migrations WHERE batch = "
            "(SELECT MAX(batch) FROM migrations) ORDER BY id DESC"
        )
        
        last_batch_migrations = self.cursor.fetchall()
        if not last_batch_migrations:
            print("롤백할 마이그레이션이 없습니다.")
            return 0
        
        rollback_count = 0
        for row in last_batch_migrations:
            migration_name = row['migration_name']
            
            # 마이그레이션 모듈 로드
            import os
            import importlib.util
            
            file_path = os.path.join('migrations', f"{migration_name}.py")
            if not os.path.exists(file_path):
                print(f"경고: {file_path}를 찾을 수 없습니다.")
                continue
            
            spec = importlib.util.spec_from_file_location(migration_name, file_path)
            module = importlib.util.module_from_spec(spec)
            spec.loader.exec_module(module)
            
            print(f"마이그레이션 롤백: {migration_name}")
            
            # 롤백 실행
            try:
                if hasattr(module, 'down'):
                    module.down(self.cursor)
                    self.conn.commit()
                    
                    # 마이그레이션 기록 삭제
                    self.cursor.execute(
                        "DELETE FROM migrations WHERE migration_name = %s",
                        (migration_name,)
                    )
                    self.conn.commit()
                    
                    rollback_count += 1
                else:
                    print(f"경고: {migration_name}에 down() 함수가 없습니다.")
            except Exception as e:
                self.conn.rollback()
                print(f"롤백 오류 {migration_name}: {e}")
                break
            
            # 지정된 단계만큼 롤백
            if rollback_count >= steps:
                break
        
        return rollback_count

# 마이그레이션 파일 예시 (migrations/20210101_create_users_table.py)
"""
def up(cursor):
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS users (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            email VARCHAR(100) UNIQUE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')

def down(cursor):
    cursor.execute('DROP TABLE IF EXISTS users')
"""

# 보안 - SQL 인젝션 방지
def safe_query_example():
    # 안전하지 않은 방식 (절대 사용하지 말 것)
    def unsafe_search(username):
        # 위험: 사용자 입력을 직접 쿼리에 삽입
        cursor.execute(f"SELECT * FROM users WHERE username = '{username}'")
        return cursor.fetchall()
    
    # 안전한 방식 - 파라미터화된 쿼리
    def safe_search(username):
        cursor.execute("SELECT * FROM users WHERE username = %s", (username,))
        return cursor.fetchall()
    
    # 동적 쿼리를 안전하게 생성
    def dynamic_safe_search(criteria):
        query = "SELECT * FROM users WHERE 1=1"
        params = []
        
        if 'username' in criteria:
            query += " AND username = %s"
            params.append(criteria['username'])
        
        if 'status' in criteria:
            query += " AND status = %s"
            params.append(criteria['status'])
        
        if 'created_after' in criteria:
            query += " AND created_at > %s"
            params.append(criteria['created_after'])
        
        cursor.execute(query, params)
        return cursor.fetchall()

# 최적화 - 쿼리 실행 계획 분석
def explain_query(query, params=None):
    cursor.execute(f"EXPLAIN {query}", params or ())
    return cursor.fetchall()

# 최적화 - 인덱스 관리
def optimize_indexes():
    # 인덱스 추가
    def add_index(table, columns, index_name=None, unique=False):
        index_type = "UNIQUE" if unique else ""
        name = index_name or f"idx_{'_'.join(columns)}"
        
        columns_str = ', '.join(columns)
        try:
            cursor.execute(f"CREATE {index_type} INDEX {name} ON {table}({columns_str})")
            conn.commit()
            return True
        except mysql.connector.Error as err:
            print(f"인덱스 생성 오류: {err}")
            return False
    
    # 불필요한 인덱스 찾기
    def find_unused_indexes():
        cursor.execute('''
            SELECT
                t.TABLE_SCHEMA,
                t.TABLE_NAME,
                s.INDEX_NAME,
                s.COLUMN_NAME
            FROM information_schema.STATISTICS s
            JOIN information_schema.TABLES t
                ON s.TABLE_SCHEMA = t.TABLE_SCHEMA
                AND s.TABLE_NAME = t.TABLE_NAME
            LEFT JOIN performance_schema.table_io_waits_summary_by_index_usage i
                ON i.OBJECT_SCHEMA = t.TABLE_SCHEMA
                AND i.OBJECT_NAME = t.TABLE_NAME
                AND i.INDEX_NAME = s.INDEX_NAME
            WHERE t.TABLE_SCHEMA = DATABASE()
                AND i.COUNT_STAR IS NULL
                OR i.COUNT_STAR = 0
            ORDER BY t.TABLE_NAME, s.INDEX_NAME
        ''')
        
        return cursor.fetchall()
    
    # 테이블 최적화
    def optimize_table(table):
        cursor.execute(f"OPTIMIZE TABLE {table}")
        return cursor.fetchall()

# 성능 - 느린 쿼리 분석
def analyze_slow_queries():
    # 느린 쿼리 로그 활성화
    cursor.execute("SET GLOBAL slow_query_log = 'ON'")
    cursor.execute("SET GLOBAL long_query_time = 1")  # 1초 이상 쿼리 기록
    conn.commit()
    
    # 느린 쿼리 조회
    cursor.execute('''
        SELECT
            start_time,
            query_time,
            sql_text
        FROM mysql.slow_log
        ORDER BY start_time DESC
        LIMIT 10
    ''')
    
    return cursor.fetchall()

# 성능 - 캐싱 레이어 구현
import functools
import hashlib
import pickle
import time

class QueryCache:
    def __init__(self, max_size=100, default_ttl=300):
        self.cache = {}  # {key: (value, timestamp, ttl)}
        self.max_size = max_size
        self.default_ttl = default_ttl
    
    def _generate_key(self, query, params):
        key_data = f"{query}:{pickle.dumps(params) if params else ''}".encode('utf-8')
        return hashlib.md5(key_data).hexdigest()
    
    def get(self, key):
        if key not in self.cache:
            return None
        
        value, timestamp, ttl = self.cache[key]
        if time.time() - timestamp > ttl:
            del self.cache[key]
            return None
        
        return value
    
    def set(self, key, value, ttl=None):
        ttl = ttl or self.default_ttl
        
        # 캐시 크기 관리
        if len(self.cache) >= self.max_size:
            # 가장 오래된 항목 제거
            oldest_key = min(self.cache, key=lambda k: self.cache[k][1])
            del self.cache[oldest_key]
        
        self.cache[key] = (value, time.time(), ttl)
    
    def clear(self):
        self.cache.clear()

# 캐시 사용 예시
def cached_database_access():
    cache = QueryCache()
    
    def get_user_cached(user_id, ttl=60):
        query = "SELECT * FROM users WHERE id = %s"
        params = (user_id,)
        
        cache_key = cache._generate_key(query, params)
        cached_result = cache.get(cache_key)
        
        if cached_result:
            print("캐시 히트!")
            return cached_result
        
        print("캐시 미스, DB 조회 중...")
        cursor.execute(query, params)
        result = cursor.fetchone()
        
        if result:
            cache.set(cache_key, result, ttl)
        
        return result
    
    # 여러 번 같은 사용자 조회
    user = get_user_cached(1)  # 캐시 미스
    user = get_user_cached(1)  # 캐시 히트

# SQLAlchemy ORM 예시
from sqlalchemy import create_engine, Column, Integer, String, DateTime, ForeignKey, Text, func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
import datetime

# 데이터베이스 연결
connection_string = f"mysql+mysqlconnector://{os.getenv('MYSQL_USER')}:{os.getenv('MYSQL_PASSWORD')}@{os.getenv('MYSQL_HOST')}/{os.getenv('MYSQL_DATABASE')}"
engine = create_engine(connection_string, echo=False)

# 모델 정의
Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(100), nullable=False)
    email = Column(String(100), unique=True)
    created_at = Column(DateTime, default=datetime.datetime.utcnow)
    
    # 관계 정의
    posts = relationship("Post", back_populates="author", cascade="all, delete-orphan")
    
    def __repr__(self):
        return f"<User(name='{self.name}', email='{self.email}')>"

class Post(Base):
    __tablename__ = 'posts'
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey('users.id', ondelete='CASCADE'), nullable=False)
    title = Column(String(200), nullable=False)
    content = Column(Text)
    created_at = Column(DateTime, default=datetime.datetime.utcnow)
    
    # 관계 정의
    author = relationship("User", back_populates="posts")
    
    def __repr__(self):
        return f"<Post(title='{self.title}')>"

# 테이블 생성
Base.metadata.create_all(engine)

# 세션 생성
Session = sessionmaker(bind=engine)

def orm_example():
    session = Session()
    
    try:
        # 사용자 추가
        new_user = User(name='김철수', email='[email protected]')
        session.add(new_user)
        session.commit()
        
        # 사용자와 연결된 게시물 추가
        new_post = Post(
            title='ORM 사용하기',
            content='SQLAlchemy는 강력한 Python ORM 프레임워크입니다.',
            author=new_user
        )
        session.add(new_post)
        session.commit()
        
        # 조회 - 단일 레코드
        user = session.query(User).filter_by(email='[email protected]').first()
        print(f"사용자: {user.name}, 이메일: {user.email}")
        
        # 조회 - JOIN
        results = session.query(User, Post).join(Post).filter(User.id == new_user.id).all()
        for user, post in results:
            print(f"사용자: {user.name}, 게시물: {post.title}")
        
        # 조회 - 집계
        post_count = session.query(func.count(Post.id)).scalar()
        print(f"총 게시물 수: {post_count}")
        
        # 필터링 및 정렬
        recent_posts = session.query(Post).order_by(Post.created_at.desc()).limit(5).all()
        for post in recent_posts:
            print(f"최근 게시물: {post.title}")
        
        # 수정
        user.name = '김철수 (수정됨)'
        session.commit()
        
        # 삭제
        session.delete(new_post)
        session.commit()
        
    except Exception as e:
        session.rollback()
        print(f"ORM 작업 오류: {e}")
    finally:
        session.close()