import psycopg2
from psycopg2.extras import DictCursor

# 데이터베이스 연결
def get_connection():
    return psycopg2.connect(
        dbname="mydatabase",
        user="username",
        password="password",
        host="localhost",
        port="5432"
    )

# 기본 연결 사용
conn = get_connection()
cur = conn.cursor(cursor_factory=DictCursor)

# 연결 종료 처리
def close_connection(conn, cur):
    if cur is not None:
        cur.close()
    if conn is not None:
        conn.close()
    print("Database connection closed.")

# 에러 처리 예시
try:
    conn = get_connection()
    cur = conn.cursor()
    # 데이터베이스 작업 수행
except psycopg2.DatabaseError as e:
    print(f"Database error: {e}")
finally:
    close_connection(conn, cur)

# 테이블 생성
def create_tables():
    commands = [
        """
        CREATE TABLE IF NOT EXISTS users (
            id SERIAL PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            email VARCHAR(100) UNIQUE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
        """,
        """
        CREATE TABLE IF NOT EXISTS posts (
            id SERIAL PRIMARY KEY,
            user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
            title VARCHAR(200) NOT NULL,
            content TEXT,
            published BOOLEAN DEFAULT FALSE,
            views INTEGER DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
        """
    ]
    
    try:
        for command in commands:
            cur.execute(command)
        conn.commit()
        print("Tables created successfully")
    except (Exception, psycopg2.DatabaseError) as error:
        print(f"Error: {error}")
        conn.rollback()

# 데이터 삽입 (Create)
def insert_user(name, email):
    sql = """
        INSERT INTO users (name, email)
        VALUES (%s, %s)
        RETURNING id, name, email, created_at
    """
    try:
        cur.execute(sql, (name, email))
        user = cur.fetchone()
        conn.commit()
        return dict(user) if isinstance(cur, DictCursor) else user
    except psycopg2.IntegrityError:
        conn.rollback()
        raise ValueError("중복된 이메일이다.")

# 데이터 조회 (Read)
def get_user(user_id):
    cur.execute("""
        SELECT id, name, email, created_at
        FROM users
        WHERE id = %s
    """, (user_id,))
    return cur.fetchone()

def get_all_users(limit=100, offset=0):
    cur.execute("""
        SELECT id, name, email, created_at
        FROM users
        ORDER BY created_at DESC
        LIMIT %s OFFSET %s
    """, (limit, offset))
    return cur.fetchall()

# 데이터 수정 (Update)
def update_user(user_id, name=None, email=None):
    updates = []
    params = []
    
    if name:
        updates.append("name = %s")
        params.append(name)
    
    if email:
        updates.append("email = %s")
        params.append(email)
    
    if not updates:
        return None
    
    params.append(user_id)
    
    sql = f"""
        UPDATE users
        SET {", ".join(updates)}
        WHERE id = %s
        RETURNING id, name, email, created_at
    """
    
    cur.execute(sql, params)
    conn.commit()
    return cur.fetchone()

# 데이터 삭제 (Delete)
def delete_user(user_id):
    cur.execute("DELETE FROM users WHERE id = %s RETURNING id", (user_id,))
    deleted_id = cur.fetchone()
    conn.commit()
    return deleted_id is not None

import json
from psycopg2.extras import Json, RealDictCursor

# JSON 데이터 처리
def create_profile(user_id, profile_data):
    """사용자 프로필을 JSON 형태로 저장한다."""
    sql = """
        ALTER TABLE users 
        ADD COLUMN IF NOT EXISTS profile JSONB;
        
        UPDATE users
        SET profile = %s::jsonb
        WHERE id = %s
        RETURNING id, profile
    """
    try:
        # Json 클래스를 사용하여 Python 객체를 PostgreSQL JSONB로 변환
        cur.execute(sql, (Json(profile_data), user_id))
        result = cur.fetchone()
        conn.commit()
        return result
    except Exception as e:
        conn.rollback()
        raise e

# JSON 필드 쿼리
def find_users_by_skill(skill):
    """특정 기술을 가진 사용자를 찾는다."""
    sql = """
        SELECT id, name, email, profile
        FROM users
        WHERE profile @> %s
    """
    cur.execute(sql, (json.dumps({"skills": [skill]}),))
    return cur.fetchall()

# JSON 필드 업데이트
def add_user_skill(user_id, new_skill):
    """사용자 프로필에 새로운 기술을 추가한다."""
    sql = """
        UPDATE users
        SET profile = jsonb_set(
            COALESCE(profile, '{}'::jsonb),
            '{skills}',
            COALESCE(profile->'skills', '[]'::jsonb) || %s::jsonb,
            true
        )
        WHERE id = %s
        RETURNING id, profile
    """
    cur.execute(sql, (Json([new_skill]), user_id))
    conn.commit()
    return cur.fetchone()

# 전체 텍스트 검색 인덱스 생성
def setup_text_search():
    """게시물 검색을 위한 인덱스를 설정한다."""
    commands = [
        """
        ALTER TABLE posts 
        ADD COLUMN IF NOT EXISTS search_vector tsvector;
        """,
        """
        CREATE INDEX IF NOT EXISTS posts_search_idx 
        ON posts USING GIN(search_vector);
        """,
        """
        CREATE OR REPLACE FUNCTION posts_search_update() RETURNS trigger AS $$
        BEGIN
            NEW.search_vector := 
                setweight(to_tsvector('korean', COALESCE(NEW.title, '')), 'A') ||
                setweight(to_tsvector('korean', COALESCE(NEW.content, '')), 'B');
            RETURN NEW;
        END
        $$ LANGUAGE plpgsql;
        """,
        """
        DROP TRIGGER IF EXISTS posts_search_update ON posts;
        """,
        """
        CREATE TRIGGER posts_search_update
        BEFORE INSERT OR UPDATE ON posts
        FOR EACH ROW EXECUTE PROCEDURE posts_search_update();
        """
    ]
    
    try:
        for command in commands:
            cur.execute(command)
        conn.commit()
        print("Text search setup complete")
    except Exception as e:
        conn.rollback()
        print(f"Error setting up text search: {e}")

# 전체 텍스트 검색 실행
def search_posts(query, limit=10):
    """게시물 내용을 검색한다."""
    sql = """
        SELECT id, title, content, ts_rank(search_vector, websearch_to_tsquery('korean', %s)) as rank
        FROM posts
        WHERE search_vector @@ websearch_to_tsquery('korean', %s)
        ORDER BY rank DESC
        LIMIT %s
    """
    cur.execute(sql, (query, query, limit))
    return cur.fetchall()

# 배열 데이터 처리
def add_tags(post_id, tags):
    """게시물에 태그 배열을 추가한다."""
    # 먼저 배열 컬럼 추가 확인
    cur.execute("""
        ALTER TABLE posts 
        ADD COLUMN IF NOT EXISTS tags TEXT[] DEFAULT '{}'::TEXT[];
    """)
    
    # 태그 추가 작업
    sql = """
        UPDATE posts
        SET tags = %s
        WHERE id = %s
        RETURNING id, title, tags
    """
    cur.execute(sql, (tags, post_id))
    conn.commit()
    return cur.fetchone()

# 배열 데이터 검색
def find_posts_by_tag(tag):
    """특정 태그가 포함된 게시물을 찾는다."""
    sql = """
        SELECT id, title, content, tags
        FROM posts
        WHERE %s = ANY(tags)
    """
    cur.execute(sql, (tag,))
    return cur.fetchall()

# 기본 트랜잭션 관리
def basic_transaction_example():
    """기본적인 트랜잭션 관리 예시"""
    conn = get_connection()
    try:
        # 자동 커밋 비활성화
        conn.autocommit = False
        cur = conn.cursor()
        
        # 트랜잭션 내 작업
        cur.execute("INSERT INTO users (name, email) VALUES (%s, %s)", 
                    ("사용자1", "[email protected]"))
        cur.execute("INSERT INTO users (name, email) VALUES (%s, %s)", 
                    ("사용자2", "[email protected]"))
        
        # 트랜잭션 완료
        conn.commit()
        print("Transaction committed successfully")
    except Exception as e:
        # 오류 발생 시 롤백
        conn.rollback()
        print(f"Transaction rolled back: {e}")
    finally:
        cur.close()
        conn.close()

# 동시성 제어를 위한 행 잠금
def transfer_money(from_account, to_account, amount):
    """계좌 간 송금 작업을 트랜잭션으로 처리한다."""
    conn = get_connection()
    try:
        conn.autocommit = False
        cur = conn.cursor()
        
        # 트랜잭션 격리 수준 설정
        cur.execute("SET TRANSACTION ISOLATION LEVEL REPEATABLE READ")
        
        # FOR UPDATE를 사용한 행 잠금으로 동시 접근 제어
        cur.execute("""
            SELECT id, balance FROM accounts
            WHERE id = %s FOR UPDATE
        """, (from_account,))
        
        from_acc = cur.fetchone()
        if not from_acc:
            raise ValueError(f"계좌 {from_account}가 존재하지 않는다.")
        
        from_balance = from_acc[1]
        
        if from_balance < amount:
            raise ValueError(f"잔액 부족: 필요 {amount}, 보유 {from_balance}")
        
        # 수취 계좌 확인 및 잠금
        cur.execute("""
            SELECT id, balance FROM accounts
            WHERE id = %s FOR UPDATE
        """, (to_account,))
        
        to_acc = cur.fetchone()
        if not to_acc:
            raise ValueError(f"계좌 {to_account}가 존재하지 않는다.")
        
        # 출금 처리
        cur.execute("""
            UPDATE accounts
            SET balance = balance - %s,
                updated_at = CURRENT_TIMESTAMP
            WHERE id = %s
        """, (amount, from_account))
        
        # 입금 처리
        cur.execute("""
            UPDATE accounts
            SET balance = balance + %s,
                updated_at = CURRENT_TIMESTAMP
            WHERE id = %s
        """, (amount, to_account))
        
        # 거래 기록 저장
        cur.execute("""
            INSERT INTO transactions 
            (from_account, to_account, amount, transaction_date, status)
            VALUES (%s, %s, %s, CURRENT_TIMESTAMP, 'completed')
            RETURNING id
        """, (from_account, to_account, amount))
        
        transaction_id = cur.fetchone()[0]
        
        # 모든 작업이 성공하면 커밋
        conn.commit()
        return {
            "transaction_id": transaction_id,
            "from_account": from_account,
            "to_account": to_account,
            "amount": amount,
            "status": "completed"
        }
        
    except Exception as e:
        # 오류 발생 시 롤백
        conn.rollback()
        
        # 실패한 거래 기록
        try:
            cur.execute("""
                INSERT INTO transactions 
                (from_account, to_account, amount, transaction_date, status, error_message)
                VALUES (%s, %s, %s, CURRENT_TIMESTAMP, 'failed', %s)
            """, (from_account, to_account, amount, str(e)))
            conn.commit()
        except:
            pass
            
        raise e
    finally:
        cur.close()
        conn.close()

# 데드락 방지 전략
def deadlock_prevention_example():
    """데드락을 방지하는 트랜잭션 패턴"""
    conn = get_connection()
    try:
        conn.autocommit = False
        cur = conn.cursor()
        
        # 트랜잭션 타임아웃 설정
        cur.execute("SET statement_timeout = '5s'")
        
        # 항상 ID 오름차순으로 잠금을 획득하여 데드락 방지
        account_ids = sorted([account1_id, account2_id])
        
        # 첫 번째 계좌 잠금
        cur.execute("""
            SELECT id, balance FROM accounts
            WHERE id = %s FOR UPDATE
        """, (account_ids[0],))
        
        # 두 번째 계좌 잠금
        cur.execute("""
            SELECT id, balance FROM accounts
            WHERE id = %s FOR UPDATE
        """, (account_ids[1],))
        
        # 이후 작업 수행...
        
        conn.commit()
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        cur.close()
        conn.close()

# 낙관적 동시성 제어
def optimistic_concurrency_control(user_id, new_data):
    """낙관적 동시성 제어 패턴 구현"""
    conn = get_connection()
    try:
        conn.autocommit = False
        cur = conn.cursor()
        
        # 버전 확인
        cur.execute("""
            SELECT id, data, version FROM user_data
            WHERE id = %s
        """, (user_id,))
        
        result = cur.fetchone()
        if not result:
            raise ValueError("사용자 데이터가 존재하지 않는다.")
        
        current_version = result[2]
        
        # 버전을 확인하며 업데이트
        updated_rows = cur.execute("""
            UPDATE user_data
            SET data = %s, version = version + 1, updated_at = CURRENT_TIMESTAMP
            WHERE id = %s AND version = %s
        """, (Json(new_data), user_id, current_version))
        
        if cur.rowcount == 0:
            # 다른 트랜잭션이 이미 데이터를 변경한 경우
            conn.rollback()
            return {"status": "conflict", "message": "Data was modified by another process"}
        
        conn.commit()
        return {"status": "success", "new_version": current_version + 1}
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        cur.close()
        conn.close()

from psycopg2.extras import execute_values
import time
import statistics

# 대량 삽입 최적화
def bulk_insert_users(users_data):
    """대량의 사용자 데이터를 효율적으로 삽입한다."""
    conn = get_connection()
    try:
        cur = conn.cursor()
        
        # execute_values 사용하여 대량 삽입
        insert_query = """
            INSERT INTO users (name, email)
            VALUES %s
            RETURNING id
        """
        
        # 데이터 변환
        values = [(user['name'], user['email']) for user in users_data]
        
        # 대량 삽입 실행
        result = execute_values(cur, insert_query, values, fetch=True)
        
        conn.commit()
        return [row[0] for row in result]  # 삽입된 ID 목록 반환
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        cur.close()
        conn.close()

# 쿼리 성능 측정
def measure_query_performance(query_func, *args, iterations=5):
    """쿼리 성능을 측정한다."""
    execution_times = []
    
    for i in range(iterations):
        start_time = time.time()
        query_func(*args)
        end_time = time.time()
        execution_times.append(end_time - start_time)
    
    avg_time = statistics.mean(execution_times)
    min_time = min(execution_times)
    max_time = max(execution_times)
    
    return {
        "average": avg_time,
        "min": min_time,
        "max": max_time,
        "total_iterations": iterations
    }

# 커넥션 풀링 구현
from contextlib import contextmanager
from psycopg2.pool import ThreadedConnectionPool

# 싱글톤 커넥션 풀
class ConnectionPool:
    _instance = None
    _pool = None
    
    @classmethod
    def get_instance(cls, min_conn=1, max_conn=10, **kwargs):
        if cls._instance is None:
            cls._instance = cls(min_conn, max_conn, **kwargs)
        return cls._instance
    
    def __init__(self, min_conn, max_conn, **kwargs):
        self._pool = ThreadedConnectionPool(
            min_conn,
            max_conn,
            dbname="mydatabase",
            user="username",
            password="password",
            host="localhost",
            port="5432",
            **kwargs
        )
    
    def get_connection(self):
        return self._pool.getconn()
    
    def release_connection(self, conn):
        self._pool.putconn(conn)
    
    def close_all(self):
        self._pool.closeall()

@contextmanager
def get_db_connection():
    """데이터베이스 연결을 컨텍스트 매니저로 제공한다."""
    pool = ConnectionPool.get_instance()
    conn = pool.get_connection()
    try:
        yield conn
    finally:
        pool.release_connection(conn)

@contextmanager
def get_db_cursor(commit=True):
    """데이터베이스 커서를 컨텍스트 매니저로 제공한다."""
    with get_db_connection() as conn:
        cur = conn.cursor()
        try:
            yield cur
            if commit:
                conn.commit()
        except:
            conn.rollback()
            raise
        finally:
            cur.close()

# 인덱스 생성 및 관리
def optimize_database_indexes():
    """데이터베이스 인덱스를 최적화한다."""
    with get_db_cursor() as cur:
        # 자주 검색되는 필드에 인덱스 생성
        cur.execute("""
            CREATE INDEX IF NOT EXISTS users_email_idx ON users(email);
            CREATE INDEX IF NOT EXISTS posts_user_id_idx ON posts(user_id);
            CREATE INDEX IF NOT EXISTS posts_created_at_idx ON posts(created_at);
            CREATE INDEX IF NOT EXISTS posts_title_idx ON posts USING gin(to_tsvector('korean', title));
        """)
        
        # 복합 인덱스 생성
        cur.execute("""
            CREATE INDEX IF NOT EXISTS posts_user_published_idx 
            ON posts(user_id, published);
        """)