postgresql.py

import os
from dotenv import load_dotenv
from sqlalchemy import create_engine, text
import pandas as pd
import gradio as gr

load_dotenv()

DATABASE_URL = os.getenv('DATABASE_URL')
engine = create_engine(DATABASE_URL)

def test_connection():
    """Test database connection and show basic info"""
    try:
        with engine.connect() as connection:
            result = connection.execute(text("SELECT version()"))
            version = result.scalar()
            return f"✅ 연결 성공!\n데이터베이스 버전: {version}"
    except Exception as e:
        return f"❌ 연결 실패: {e}"

def get_all_puzzles():
    """Fetch all puzzles from database"""
    try:
        query = text("SELECT * FROM puzzles ORDER BY puzzle_number")
        df = pd.read_sql_query(query, engine)
        return df
    except Exception as e:
        return pd.DataFrame({"error": [str(e)]})

def get_puzzle_by_difficulty(difficulty):
    """Fetch puzzles filtered by difficulty"""
    try:
        if difficulty == "전체":
            query = text("SELECT * FROM puzzles ORDER BY puzzle_number")
            df = pd.read_sql_query(query, engine)
        else:
            query = text("SELECT * FROM puzzles WHERE difficulty = :difficulty ORDER BY puzzle_number")
            df = pd.read_sql_query(query, engine, params={"difficulty": difficulty})
        return df
    except Exception as e:
        return pd.DataFrame({"error": [str(e)]})

def get_table_count():
    """Get total count of puzzles"""
    try:
        query = text("SELECT COUNT(*) FROM puzzles")
        with engine.connect() as connection:
            result = connection.execute(query)
            count = result.scalar()
            return f"전체 퍼즐 개수: {count}개"
    except Exception as e:
        return f"오류: {e}"

with gr.Blocks(title="Puzzles DB 테스트") as demo:
    gr.Markdown("# 🧩 Puzzles 데이터베이스 연결 테스트")

    with gr.Row():
        with gr.Column():
            test_btn = gr.Button("연결 테스트", variant="primary")
            connection_status = gr.Textbox(label="연결 상태", lines=3)

        with gr.Column():
            count_btn = gr.Button("데이터 개수 확인")
            count_output = gr.Textbox(label="개수")

    gr.Markdown("## 전체 데이터 조회")
    load_all_btn = gr.Button("모든 퍼즐 불러오기")
    all_data = gr.Dataframe(label="전체 퍼즐 데이터")

    gr.Markdown("## 난이도별 필터링")
    difficulty_filter = gr.Dropdown(
        ["전체", "easy", "medium", "hard"],
        value="전체",
        label="난이도 선택"
    )
    filter_btn = gr.Button("필터 적용")
    filtered_data = gr.Dataframe(label="필터링된 데이터")

    # Event handlers
    test_btn.click(test_connection, outputs=connection_status)
    count_btn.click(get_table_count, outputs=count_output)
    load_all_btn.click(get_all_puzzles, outputs=all_data)
    filter_btn.click(get_puzzle_by_difficulty, inputs=difficulty_filter, outputs=filtered_data)
    difficulty_filter.change(get_puzzle_by_difficulty, inputs=difficulty_filter, outputs=filtered_data)

if __name__ == "__main__":
    demo.launch()