main_sleep.py

import streamlit as st
import pandas as pd
import plotly.graph_objects as go
from datetime import datetime, timedelta
import pymysql
import os
from dotenv import load_dotenv

# .env 파일 로드
load_dotenv()

# ==================== 페이지 설정 ====================
st.set_page_config(
    page_title="FISAGRAM",
    page_icon="🍪",
    layout="wide"
)

# ==================== 데이터베이스 연결 함수 (MySQL) ====================
def get_db_connection():
    """MySQL 데이터베이스 연결"""
    try:
        connection = pymysql.connect(
            host='118.67.131.22',
            port=3306,
            user='fisaai6',
            passwd=os.getenv('FISADB_PASSWORD'),
            db='fisagram',
            charset='utf8mb4'
        )
        return connection
    except Exception as e:
        st.error(f"❌ 데이터베이스 연결 실패: {str(e)}")
        return None

def get_db_connection_dict():
    """딕셔너리 커서용 연결 (일반 쿼리용)"""
    try:
        connection = pymysql.connect(
            host='118.67.131.22',
            port=3306,
            user='fisaai6',
            passwd=os.getenv('FISADB_PASSWORD'),
            db='fisagram',
            charset='utf8mb4',
            cursorclass=pymysql.cursors.DictCursor
        )
        return connection
    except Exception as e:
        st.error(f"❌ 데이터베이스 연결 실패: {str(e)}")
        return None

# ==================== 수면 데이터 관련 함수 ====================
def load_sleep_data():
    """DB에서 모든 수면 기록 불러오기"""
    conn = get_db_connection()
    if conn is None:
        return pd.DataFrame()

    try:
        query = """
            SELECT 
                log_id,
                user_id,
                sleep_date,
                sleep_hours
            FROM sleep_logs
            ORDER BY sleep_date DESC
        """
        df = pd.read_sql(query, conn)
        
        if not df.empty:
            df['date'] = pd.to_datetime(df['sleep_date'])
            df = df.drop(columns=['sleep_date'])
            df['sleep_hours'] = pd.to_numeric(df['sleep_hours'], errors='coerce')
            df = df.dropna(subset=['date', 'sleep_hours'])

        return df
    except Exception as e:
        st.error(f"❌ 데이터 로드 실패: {str(e)}")
        return pd.DataFrame()
    finally:
        conn.close()

def get_user_list():
    """사용자 목록 가져오기"""
    conn = get_db_connection_dict()
    if conn is None:
        return []
    
    try:
        cursor = conn.cursor()
        cursor.execute("SELECT DISTINCT user_id FROM users_study_flat ORDER BY user_id")
        users = [row['user_id'] for row in cursor.fetchall()]
        return users
    except Exception as e:
        st.error(f"❌ 사용자 목록 로드 실패: {str(e)}")
        return []
    finally:
        conn.close()

def save_sleep_record(user_id, sleep_date, sleep_hours):
    """새 수면 기록 저장"""
    conn = get_db_connection_dict()
    if conn is None:
        return False
    
    try:
        cursor = conn.cursor()
        
        delete_query = """
            DELETE FROM sleep_logs 
            WHERE user_id = %s AND sleep_date = %s
        """
        cursor.execute(delete_query, (user_id, sleep_date))
        
        insert_query = """
            INSERT INTO sleep_logs (user_id, sleep_date, sleep_hours)
            VALUES (%s, %s, %s)
        """
        cursor.execute(insert_query, (user_id, sleep_date, sleep_hours))
        
        conn.commit()
        return True
    except Exception as e:
        st.error(f"❌ 데이터 저장 실패: {str(e)}")
        conn.rollback()
        return False
    finally:
        conn.close()

# ==================== 커뮤니티 팁 관련 함수 ====================
def create_tips_table_if_not_exists():
    """커뮤니티 팁 테이블 생성"""
    conn = get_db_connection_dict()
    if conn is None:
        return False
    
    try:
        cursor = conn.cursor()
        create_table_query = """
            CREATE TABLE IF NOT EXISTS community_tips (
                tip_id INT AUTO_INCREMENT PRIMARY KEY,
                username VARCHAR(50) NOT NULL,
                tip_content TEXT NOT NULL,
                likes INT DEFAULT 0,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
        """
        cursor.execute(create_table_query)
        conn.commit()
        return True
    except Exception as e:
        st.warning(f"⚠️ 테이블 생성 실패: {str(e)}")
        return False
    finally:
        conn.close()

def load_community_tips():
    """커뮤니티 팁 불러오기"""
    conn = get_db_connection_dict()
    if conn is None:
        return []
    
    try:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT tip_id, username, tip_content, likes, created_at
            FROM community_tips
            ORDER BY likes DESC, created_at DESC
        """)
        tips = cursor.fetchall()
        return tips
    except Exception as e:
        create_tips_table_if_not_exists()
        return []
    finally:
        conn.close()

def save_community_tip(username, tip_content):
    """새 커뮤니티 팁 저장"""
    conn = get_db_connection_dict()
    if conn is None:
        return False
    
    try:
        cursor = conn.cursor()
        insert_query = """
            INSERT INTO community_tips (username, tip_content, likes)
            VALUES (%s, %s, 0)
        """
        cursor.execute(insert_query, (username, tip_content))
        conn.commit()
        return True
    except Exception as e:
        st.error(f"❌ 팁 저장 실패: {str(e)}")
        return False
    finally:
        conn.close()

def update_tip_likes(tip_id, new_likes):
    """팁 좋아요 수 업데이트"""
    conn = get_db_connection_dict()
    if conn is None:
        return False
    
    try:
        cursor = conn.cursor()
        cursor.execute("""
            UPDATE community_tips
            SET likes = %s
            WHERE tip_id = %s
        """, (new_likes, tip_id))
        conn.commit()
        return True
    except Exception as e:
        st.error(f"❌ 좋아요 업데이트 실패: {str(e)}")
        return False
    finally:
        conn.close()

def delete_community_tip(tip_id):
    """커뮤니티 팁 삭제"""
    conn = get_db_connection_dict()
    if conn is None:
        return False
    
    try:
        cursor = conn.cursor()
        cursor.execute("DELETE FROM community_tips WHERE tip_id = %s", (tip_id,))
        conn.commit()
        return True
    except Exception as e:
        st.error(f"❌ 삭제 실패: {str(e)}")
        return False
    finally:
        conn.close()

# ==================== 좌석표 관련 함수 (MySQL 연동) ====================
def create_seating_table_if_not_exists():
    """좌석 배치 테이블 생성"""
    conn = get_db_connection_dict()
    if conn is None:
        return False
    
    try:
        cursor = conn.cursor()
        create_table_query = """
            CREATE TABLE IF NOT EXISTS seating_arrangement (
                id INT AUTO_INCREMENT PRIMARY KEY,
                section VARCHAR(20) NOT NULL,
                row_num INT NOT NULL,
                col_num INT NOT NULL,
                user_id INT NOT NULL,
                UNIQUE KEY unique_seat (section, row_num, col_num),
                FOREIGN KEY (user_id) REFERENCES users_study_flat(user_id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
        """
        cursor.execute(create_table_query)
        conn.commit()
        return True
    except Exception as e:
        st.warning(f"⚠️ 테이블 생성 실패: {str(e)}")
        return False
    finally:
        conn.close()

def get_all_users_from_mysql():
    """MySQL에서 모든 사용자 정보 가져오기"""
    conn = get_db_connection_dict()
    if conn is None:
        return []
    
    try:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT user_id, user_name, birth_year, major, mbti, 
                   region, interests, intro, study_name
            FROM users_study_flat
            ORDER BY user_name
        """)
        users = cursor.fetchall()
        return users
    except Exception as e:
        st.error(f"❌ 사용자 목록 로드 실패: {str(e)}")
        return []
    finally:
        conn.close()

def get_user_by_id_mysql(user_id):
    """MySQL에서 특정 사용자 정보 가져오기"""
    conn = get_db_connection_dict()
    if conn is None:
        return None
    
    try:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT user_id, user_name, birth_year, major, mbti, 
                   region, interests, intro, study_name
            FROM users_study_flat
            WHERE user_id = %s
        """, (user_id,))
        user = cursor.fetchone()
        return user
    except Exception as e:
        st.error(f"❌ 사용자 정보 로드 실패: {str(e)}")
        return None
    finally:
        conn.close()

def save_seating_arrangement(seating_data):
    """좌석 배치 저장 (MySQL)"""
    conn = get_db_connection_dict()
    if conn is None:
        return False
    
    try:
        cursor = conn.cursor()
        
        # 기존 좌석 배치 삭제
        cursor.execute("DELETE FROM seating_arrangement")
        
        # 새 좌석 배치 저장
        for seat in seating_data:
            cursor.execute("""
                INSERT INTO seating_arrangement (section, row_num, col_num, user_id) 
                VALUES (%s, %s, %s, %s)
            """, (seat['section'], seat['row'], seat['col'], seat['user_id']))
        
        conn.commit()
        return True
    except Exception as e:
        st.error(f"❌ 좌석 배치 저장 실패: {str(e)}")
        conn.rollback()
        return False
    finally:
        conn.close()

def load_seating_arrangement():
    """저장된 좌석 배치 불러오기 (MySQL)"""
    conn = get_db_connection_dict()
    if conn is None:
        return {}
    
    try:
        cursor = conn.cursor()
        cursor.execute("SELECT section, row_num, col_num, user_id FROM seating_arrangement")
        seats = cursor.fetchall()
        
        seating = {}
        for seat in seats:
            key = f"{seat['section']}_{seat['row_num']}_{seat['col_num']}"
            seating[key] = seat['user_id']
        
        return seating
    except Exception as e:
        # 테이블이 없으면 생성 시도
        create_seating_table_if_not_exists()
        return {}
    finally:
        conn.close()

# ==================== 세션 상태 초기화 ====================
if "current_page" not in st.session_state:
    st.session_state.current_page = "main"

if "selected_user" not in st.session_state:
    st.session_state.selected_user = 1

if 'seating' not in st.session_state:
    st.session_state.seating = load_seating_arrangement()

if 'selected_user_info' not in st.session_state:
    st.session_state.selected_user_info = None

if 'sleep_tips' not in st.session_state:
    st.session_state.sleep_tips = []

if 'tip_counter' not in st.session_state:
    st.session_state.tip_counter = 1

if 'user_liked_tips' not in st.session_state:
    st.session_state.user_liked_tips = set()

if 'sleep_data' not in st.session_state:
    # 초기 데이터 로드
    with st.spinner("📊 데이터 로딩 중..."):
        st.session_state.sleep_data = load_sleep_data()

# ==================== Instagram 스타일 CSS ====================
st.markdown("""
<style>
/* 전체 배경 */
.main {
    background: linear-gradient(135deg, #f5f7fa 0%, #c3cfe2 100%);
}

/* 메인 헤더 - Instagram 느낌 */
.main-header {
    font-size: 2.8rem;
    font-weight: 800;
    text-align: center;
    margin-bottom: 2rem;
    background: linear-gradient(135deg, #667EEA 0%, #764BA2 100%);
    -webkit-background-clip: text;
    -webkit-text-fill-color: transparent;
    letter-spacing: -1px;
}

/* 통계 카드 - 4가지 색상 그라데이션 */
.stat-card-purple {
    background: linear-gradient(135deg, #667EEA 0%, #764BA2 100%);
    padding: 2rem;
    border-radius: 20px;
    color: white;
    text-align: center;
    box-shadow: 0 10px 30px rgba(102, 126, 234, 0.3);
    transition: transform 0.3s ease;
}

.stat-card-pink {
    background: linear-gradient(135deg, #F093FB 0%, #F5576C 100%);
    padding: 2rem;
    border-radius: 20px;
    color: white;
    text-align: center;
    box-shadow: 0 10px 30px rgba(245, 87, 108, 0.3);
    transition: transform 0.3s ease;
}

.stat-card-blue {
    background: linear-gradient(135deg, #4FACFE 0%, #00F2FE 100%);
    padding: 2rem;
    border-radius: 20px;
    color: white;
    text-align: center;
    box-shadow: 0 10px 30px rgba(79, 172, 254, 0.3);
    transition: transform 0.3s ease;
}

.stat-card-teal {
    background: linear-gradient(135deg, #43E97B 0%, #38F9D7 100%);
    padding: 2rem;
    border-radius: 20px;
    color: white;
    text-align: center;
    box-shadow: 0 10px 30px rgba(67, 233, 123, 0.3);
    transition: transform 0.3s ease;
}

.stat-card-purple:hover, .stat-card-pink:hover, 
.stat-card-blue:hover, .stat-card-teal:hover {
    transform: translateY(-5px);
}

.stat-label {
    font-size: 0.95rem;
    font-weight: 600;
    opacity: 0.95;
    margin-bottom: 0.5rem;
    letter-spacing: 0.5px;
}

.stat-value {
    font-size: 2.5rem;
    font-weight: 800;
    text-shadow: 0 2px 4px rgba(0,0,0,0.1);
}

/* 학습 진행도 게이지 컨테이너 */
.progress-container {
    background: white;
    border-radius: 20px;
    padding: 2.5rem;
    box-shadow: 0 10px 40px rgba(0,0,0,0.08);
    margin: 2rem 0;
}

.progress-title {
    font-size: 1.8rem;
    font-weight: 700;
    color: #333;
    text-align: center;
    margin-bottom: 1.5rem;
}

/* 카드 스타일 */
.instagram-card {
    background: white;
    border-radius: 20px;
    padding: 2rem;
    box-shadow: 0 10px 40px rgba(0,0,0,0.08);
    margin-bottom: 1.5rem;
    transition: transform 0.3s ease;
}

.instagram-card:hover {
    transform: translateY(-3px);
    box-shadow: 0 15px 50px rgba(0,0,0,0.12);
}

/* 수면 팁 카드 */
.sleep-tip {
    background: linear-gradient(135deg, #E8F4F8 0%, #D4E7F0 100%);
    padding: 1.2rem;
    border-radius: 15px;
    border-left: 5px solid #4FACFE;
    margin-bottom: 0.8rem;
    font-weight: 500;
    transition: all 0.3s ease;
}

.sleep-tip:hover {
    transform: translateX(5px);
    box-shadow: 0 5px 15px rgba(79, 172, 254, 0.2);
}

/* 커뮤니티 팁 */
.community-tip {
    background: linear-gradient(135deg, #FFF9E6 0%, #FFF0CC 100%);
    padding: 1.5rem;
    border-radius: 15px;
    margin-bottom: 1.2rem;
    border: 2px solid #FFE4A3;
    box-shadow: 0 5px 20px rgba(0,0,0,0.05);
}

.tip-header {
    font-weight: 700;
    color: #333;
    margin-bottom: 0.7rem;
    font-size: 1.05rem;
}

.tip-content {
    color: #555;
    line-height: 1.7;
    margin: 0.8rem 0;
}

.tip-footer {
    display: flex;
    justify-content: space-between;
    align-items: center;
    margin-top: 0.8rem;
    font-size: 0.9rem;
    color: #999;
}

.tip-likes {
    color: #FF6B6B;
    font-size: 1rem;
    font-weight: 600;
}

/* DB 연결 상태 */
.db-status {
    background: linear-gradient(135deg, #E8F5E9 0%, #C8E6C9 100%);
    padding: 1rem;
    border-radius: 15px;
    border-left: 5px solid #4CAF50;
    margin-bottom: 1.5rem;
    font-weight: 600;
}

/* 정보 카드 */
.info-card {
    background: white;
    border-radius: 20px;
    padding: 2rem;
    box-shadow: 0 10px 40px rgba(0,0,0,0.08);
    margin-bottom: 1.5rem;
}

.info-header {
    font-size: 1.8rem;
    font-weight: 700;
    background: linear-gradient(135deg, #667EEA 0%, #764BA2 100%);
    -webkit-background-clip: text;
    -webkit-text-fill-color: transparent;
    margin-bottom: 1.5rem;
}

.info-item {
    margin: 12px 0;
    padding: 12px;
    background: linear-gradient(135deg, #F8F9FA 0%, #E9ECEF 100%);
    border-radius: 10px;
    font-size: 0.95rem;
    font-weight: 500;
}

/* 섹션 타이틀 */
.section-title {
    font-size: 1.5rem;
    font-weight: 700;
    color: #333;
    margin-bottom: 1.2rem;
    text-align: center;
}

/* 사이드바 메뉴 버튼 스타일 */
[data-testid="stSidebar"] .stButton>button {
    background: white;
    color: #555;
    border-radius: 12px;
    border: 2px solid #E9ECEF;
    font-weight: 600;
    padding: 0.75rem 1.5rem;
    transition: all 0.3s ease;
    box-shadow: none;
    width: 100%;
    text-align: left;
}

[data-testid="stSidebar"] .stButton>button:hover {
    background: #F8F9FA;
    border-color: #667EEA;
    color: #667EEA;
    transform: translateX(5px);
}

/* 메인 페이지 버튼은 그라데이션 유지 */
.main .stButton>button {
    background: linear-gradient(135deg, #667EEA 0%, #764BA2 100%);
    color: white;
    border-radius: 12px;
    border: none;
    font-weight: 700;
    padding: 0.75rem 1.5rem;
    transition: all 0.3s ease;
    box-shadow: 0 4px 15px rgba(102, 126, 234, 0.3);
}

.main .stButton>button:hover {
    transform: translateY(-2px);
    box-shadow: 0 8px 25px rgba(102, 126, 234, 0.4);
}

/* 좌석 버튼 - 하늘색 그라데이션 */
div[data-testid="column"] .stButton>button {
    background: linear-gradient(135deg, #89CFF0 0%, #4FC3F7 100%);
    color: white;
    border-radius: 12px;
    border: none;
    font-weight: 700;
    transition: all 0.3s ease;
    box-shadow: 0 4px 15px rgba(79, 195, 247, 0.3);
    font-size: 0.95rem;
    padding: 1rem;
}

div[data-testid="column"] .stButton>button:hover {
    transform: translateY(-3px);
    box-shadow: 0 8px 25px rgba(79, 195, 247, 0.4);
    background: linear-gradient(135deg, #4FC3F7 0%, #29B6F6 100%);
}

/* 빈 자리 버튼 */
div[data-testid="column"] .stButton>button:disabled {
    background: #F0F0F0 !important;
    color: #999 !important;
    border: 2px dashed #DDD !important;
    box-shadow: none !important;
}

/* 폼 입력 스타일 */
.stTextInput>div>div>input, 
.stTextArea>div>div>textarea,
.stSelectbox>div>div>select {
    border-radius: 10px;
    border: 2px solid #E9ECEF;
    transition: all 0.3s ease;
}

.stTextInput>div>div>input:focus, 
.stTextArea>div>div>textarea:focus {
    border-color: #667EEA;
    box-shadow: 0 0 0 3px rgba(102, 126, 234, 0.1);
}

/* 네비게이션 배너 */
.nav-banner {
    background: white;
    padding: 1rem;
    border-radius: 15px;
    box-shadow: 0 4px 15px rgba(0,0,0,0.08);
    margin-bottom: 2rem;
    display: flex;
    gap: 0.8rem;
    flex-wrap: wrap;
    justify-content: center;
}

.nav-button {
    display: inline-block;
    padding: 0.6rem 1.2rem;
    background: linear-gradient(135deg, #F8F9FA 0%, #E9ECEF 100%);
    color: #555;
    text-decoration: none;
    border-radius: 10px;
    font-weight: 600;
    font-size: 0.9rem;
    transition: all 0.3s ease;
    border: 2px solid transparent;
}

.nav-button:hover {
    background: linear-gradient(135deg, #667EEA 0%, #764BA2 100%);
    color: white;
    transform: translateY(-2px);
    box-shadow: 0 4px 15px rgba(102, 126, 234, 0.3);
}

/* 통계 카드 */
.stat-card {
    background: linear-gradient(135deg, #667EEA 0%, #764BA2 100%);
    padding: 1.5rem;
    border-radius: 15px;
    color: white;
    text-align: center;
    box-shadow: 0 8px 20px rgba(102, 126, 234, 0.3);
}

.stat-card .stat-label {
    font-size: 0.9rem;
    font-weight: 600;
    opacity: 0.95;
    margin-bottom: 0.5rem;
}

.stat-card .stat-value {
    font-size: 2rem;
    font-weight: 800;
    text-shadow: 0 2px 4px rgba(0,0,0,0.1);
}

</style>
""", unsafe_allow_html=True)

# ==================== 메인 페이지 ====================
def show_main_page():
    st.markdown('<div class="main-header"><span style="-webkit-text-fill-color: initial;">🍪</span> FISAGRAM</div>', unsafe_allow_html=True)
    


    # 학습 진행도 계산 (총 960시간, 주 5일 하루 9시간씩)
    total_hours = 960
    start_date = datetime(2025, 12, 30)  # 시작일
    end_date = datetime(2026, 6, 25)   # 수료일 (6월 25일)
    current_date = datetime.now()
    
    # 현재까지 경과한 영업일 수 계산 (주 5일 기준)
    days_elapsed = (current_date - start_date).days
    weeks_elapsed = days_elapsed // 7
    remaining_days_in_week = days_elapsed % 7
    
    # 주말 제외하고 실제 학습일 수 계산 (간단 계산: 주 5일 기준)
    study_days = weeks_elapsed * 5 + min(remaining_days_in_week, 5)
    elapsed_hours = study_days * 9  # 하루 9시간
    
    # 최소값 보정 (음수 방지)
    elapsed_hours = max(0, elapsed_hours)
    remaining_hours = max(0, total_hours - elapsed_hours)
    progress_percent = min(100, (elapsed_hours / total_hours) * 100)
    
    # 수료일까지 남은 일수
    days_until_completion = (end_date - current_date).days

    # 4개 통계 카드 (Instagram 색상)
    c1, c2, c3, c4 = st.columns(4)
    
    with c1:
        st.markdown(f"""
        <div class="stat-card-purple">
            <div class="stat-label">🌙 진행률</div>
            <div class="stat-value">{progress_percent:.1f}%</div>
        </div>
        """, unsafe_allow_html=True)
    
    with c2:
        st.markdown(f"""
        <div class="stat-card-pink">
            <div class="stat-label">⏱️ 경과 시간</div>
            <div class="stat-value">{elapsed_hours}h</div>
        </div>
        """, unsafe_allow_html=True)
    
    with c3:
        st.markdown(f"""
        <div class="stat-card-blue">
            <div class="stat-label">🎯 남은 시간</div>
            <div class="stat-value">{remaining_hours}h</div>
        </div>
        """, unsafe_allow_html=True)
    
    with c4:
        st.markdown(f"""
        <div class="stat-card-teal">
            <div class="stat-label">📚 전체 시간</div>
            <div class="stat-value">{total_hours}h</div>
        </div>
        """, unsafe_allow_html=True)

    # 학습 진행도 게이지 차트
    st.markdown("---")
    st.markdown('<div class="progress-title">📊 부트캠프 진행도</div>', unsafe_allow_html=True)
    
    fig = go.Figure(go.Indicator(
        mode="gauge+number+delta",
        value=progress_percent,
        delta={'reference': 0, 'increasing': {'color': "#43E97B"}},
        title={'text': f"<b>{elapsed_hours}/{total_hours} 시간</b>", 'font': {'size': 24}},
        number={'suffix': "%", 'font': {'size': 50, 'color': '#667EEA'}},
        gauge={
            'axis': {'range': [None, 100], 'tickwidth': 2, 'tickcolor': "#667EEA"},
            'bar': {'color': "#667EEA", 'thickness': 0.8},
            'bgcolor': "white",
            'borderwidth': 3,
            'bordercolor': "#E9ECEF",
            'steps': [
                {'range': [0, 33.3], 'color': '#FFE5E5'},
                {'range': [33.3, 66.6], 'color': '#FFF5E5'},
                {'range': [66.6, 100], 'color': '#E5F5E5'}
            ],
            'threshold': {
                'line': {'color': "#F5576C", 'width': 4},
                'thickness': 0.75,
                'value': progress_percent
            }
        }
    ))
    
    fig.update_layout(
        height=350,
        margin=dict(l=20, r=20, t=50, b=20),
        paper_bgcolor='rgba(0,0,0,0)',
        font={'family': "Arial, sans-serif"}
    )
    
    st.plotly_chart(fig, use_container_width=True)
    
    # 진행도 상세 정보
    col_info1, col_info2 = st.columns(2)
    with col_info1:
        avg_hours_per_day = elapsed_hours / max(1, study_days)
        st.markdown(f"""
        <div class="instagram-card">
            <h4>⚡ 현재 진행 속도</h4>
            <p style="font-size: 1.2rem; color: #667EEA; font-weight: 700;">
                평균 {avg_hours_per_day:.1f}시간/일 (주 5일 기준)
            </p>
        </div>
        """, unsafe_allow_html=True)
    
    with col_info2:
        st.markdown(f"""
        <div class="instagram-card">
            <h4>🎓 예상 수료일</h4>
            <p style="font-size: 1.2rem; color: #F5576C; font-weight: 700;">
                2025년 6월 25일 (D-{days_until_completion})
            </p>
        </div>
        """, unsafe_allow_html=True)
    
    st.markdown('</div>', unsafe_allow_html=True)

    st.markdown("---")
    
    # 메뉴 카드
    col1, col2 = st.columns(2)
    
    with col1:
        st.markdown("""
        <div class="instagram-card">
            <h3>😴 수면 기록</h3>
            <p style="color: #666; margin: 1rem 0;">수면 시간을 기록하고 분석하세요</p>
        </div>
        """, unsafe_allow_html=True)
        if st.button("수면 기록 페이지로 이동 →", use_container_width=True, key="goto_sleep"):
            st.session_state.current_page = "sleep"
            st.rerun()
    
    with col2:
        st.markdown("""
        <div class="instagram-card">
            <h3>🪑 좌석표</h3>
            <p style="color: #666; margin: 1rem 0;">우리 반 좌석 배치를 확인하세요</p>
        </div>
        """, unsafe_allow_html=True)
        if st.button("좌석표 페이지로 이동 →", use_container_width=True, key="goto_seating"):
            st.session_state.current_page = "seating"
            st.rerun()

# ==================== 수면 기록 페이지 ====================
def show_sleep_page():
    st.markdown('<div class="main-header">💤 수면 기록 관리</div>', unsafe_allow_html=True)

    if st.button("⬅️ 메인으로"):
        st.session_state.current_page = "main"
        st.rerun()

    df_all = st.session_state.sleep_data

    if df_all.empty:
        st.warning("⚠️ 수면 기록이 없습니다.")
        return

    # MySQL에서 사용자 이름 가져오기
    conn = get_db_connection_dict()
    user_map = {}  # {user_id: user_name}
    
    if conn:
        try:
            cursor = conn.cursor()
            cursor.execute("SELECT user_id, user_name FROM users_study_flat")
            for row in cursor.fetchall():
                user_map[row['user_id']] = row['user_name']
            conn.close()
        except:
            conn.close()
    
    users = sorted(df_all["user_id"].unique())
    if st.session_state.selected_user not in users:
        st.session_state.selected_user = users[0]
    
    # 사용자 이름으로 표시
    user_display_options = [f"{user_map.get(uid, f'사용자 {uid}')}" for uid in users]
    
    selected_display = st.selectbox(
        "👤 사용자 선택",
        user_display_options,
        index=users.index(st.session_state.selected_user)
    )
    
    # 선택된 표시 옵션에서 실제 user_id 추출
    selected_user = users[user_display_options.index(selected_display)]
    st.session_state.selected_user = selected_user

    # ==================== 배너와 오늘의 수면 기록을 양옆에 배치 ====================
    col_banner, col_record = st.columns([1, 1])
    
    with col_banner:
        st.markdown("""
        <style>
        .nav-banner-large {
            background: white;
            padding: 1.5rem;
            border-radius: 20px;
            box-shadow: 0 8px 25px rgba(0,0,0,0.12);
            margin-bottom: 2.5rem;
        }
        
        .nav-grid {
            display: grid;
            grid-template-columns: repeat(2, 1fr);
            gap: 1rem;
        }
        
        .nav-button-large {
            display: flex;
            align-items: center;
            justify-content: center;
            padding: 1.2rem 1.5rem;
            background: linear-gradient(135deg, #8B9FE8 0%, #9B8FCA 100%);
            color: white !important;
            text-decoration: none !important;
            border-radius: 15px;
            font-weight: 700;
            font-size: 1.05rem;
            transition: all 0.3s ease;
            border: none;
            box-shadow: 0 4px 15px rgba(139, 159, 232, 0.3);
            white-space: nowrap;
        }
        
        .nav-button-large:hover {
            transform: translateY(-3px);
            box-shadow: 0 8px 25px rgba(139, 159, 232, 0.4);
            color: white !important;
            background: linear-gradient(135deg, #667EEA 0%, #764BA2 100%);
        }
        
        .emoji-icon {
            font-size: 1.4rem;
            margin-right: 0.6rem;
            flex-shrink: 0;
            text-decoration: none !important;
        }
        
        .tip-delete-btn {
            background: #FF6B6B;
            color: white;
            border: none;
            padding: 0.3rem 0.8rem;
            border-radius: 8px;
            font-size: 0.85rem;
            cursor: pointer;
            transition: all 0.3s ease;
        }
        
        .tip-delete-btn:hover {
            background: #FF5252;
            transform: scale(1.05);
        }
        </style>
        
        <div class="nav-banner-large">
            <div class="nav-grid">
                <a href="#sleep-record" class="nav-button-large">
                    <span class="emoji-icon">✨</span>오늘의 수면 기록
                </a>
                <a href="#sleep-trend" class="nav-button-large">
                    <span class="emoji-icon">📈</span>나의 수면 트렌드
                </a>
                <a href="#sleep-tips" class="nav-button-large">
                    <span class="emoji-icon">💡</span>수면 개선 팁
                </a>
                <a href="#user-distribution" class="nav-button-large">
                    <span class="emoji-icon">📊</span>전체 이용자 분포
                </a>
                <a href="#community-tips" class="nav-button-large">
                    <span class="emoji-icon">🍯</span>꿀팁 공유
                </a>
                <a href="#sleep-content" class="nav-button-large">
                    <span class="emoji-icon">🎵</span>추천 컨텐츠
                </a>
            </div>
        </div>
        """, unsafe_allow_html=True)

    with col_record:
        # ==================== 오늘의 수면 기록 입력 ====================
        st.markdown('<div id="sleep-record"></div>', unsafe_allow_html=True)
        st.markdown("### ✨ 오늘의 수면 기록하기")
        st.markdown("*당신의 수면 패턴을 기록하고 개선해보세요!*")
        
        with st.form("sleep_form"):
            col1, col2, col3 = st.columns(3)
            
            with col1:
                input_date = st.date_input("📅 날짜", datetime.now())
            with col2:
                sleep_time = st.time_input("😴 취침", datetime.strptime("23:00", "%H:%M").time())
            with col3:
                wake_time = st.time_input("⏰ 기상", datetime.strptime("07:00", "%H:%M").time())

            submitted = st.form_submit_button("💾 기록 저장", use_container_width=True)

            if submitted:
                sleep_dt = datetime.combine(input_date, sleep_time)
                wake_dt = datetime.combine(input_date, wake_time)

                if wake_dt <= sleep_dt:
                    wake_dt += pd.Timedelta(days=1)

                sleep_hours = (wake_dt - sleep_dt).total_seconds() / 3600

                # MySQL에 저장
                if save_sleep_record(selected_user, input_date, sleep_hours):
                    # 세션 데이터도 업데이트
                    df = st.session_state.sleep_data
                    df = df[~(
                        (df["user_id"] == selected_user) &
                        (df["date"] == pd.to_datetime(input_date))
                    )]

                    new_id = df["log_id"].max() + 1 if len(df) > 0 else 1
                    new_row = pd.DataFrame({
                        "log_id": [new_id],
                        "user_id": [selected_user],
                        "date": [pd.to_datetime(input_date)],
                        "sleep_hours": [round(sleep_hours, 2)]
                    })

                    st.session_state.sleep_data = pd.concat([df, new_row], ignore_index=True)

                    st.success(
                        f"✅ 저장 완료! "
                        f"({sleep_time.strftime('%H:%M')} → {wake_time.strftime('%H:%M')} / "
                        f"{sleep_hours:.2f}시간)"
                    )
                    st.rerun()
                else:
                    st.error("❌ 저장 실패. 다시 시도해주세요.")

    st.markdown("---")

    # ==================== 나의 수면 트렌드 ====================
    st.markdown('<div id="sleep-trend"></div>', unsafe_allow_html=True)
    st.markdown("### 📈 나의 수면 트렌드")
    
    # 기간 선택
    period_options = {
        "1주일": 7,
        "2주일": 14,
        "3주일": 21,
        "1개월": 30,
        "전체": None
    }
    
    selected_period = st.selectbox(
        "📆 조회 기간 선택",
        list(period_options.keys()),
        index=0
    )
    
    df_user = df_all[df_all["user_id"] == selected_user].sort_values("date")
    
    days = period_options[selected_period]
    if days:
        df_period = df_user.tail(days)
    else:
        df_period = df_user
    
    RECOMMENDED = 7.5
    
    if len(df_period) > 0:
        total_sleep = df_period["sleep_hours"].sum()
        avg_sleep = df_period["sleep_hours"].mean()
        
        c1, c2, c3, c4 = st.columns(4)
        
        # 부트캠프 진행도와 같은 색상 적용
        with c1:
            st.markdown(f"""
            <div class="stat-card-purple">
                <div class="stat-label">💤 총 수면시간</div>
                <div class="stat-value">{total_sleep:.1f}h</div>
            </div>
            """, unsafe_allow_html=True)
        
        with c2:
            st.markdown(f"""
            <div class="stat-card-pink">
                <div class="stat-label">📊 평균 수면</div>
                <div class="stat-value">{avg_sleep:.1f}h</div>
            </div>
            """, unsafe_allow_html=True)
        
        with c3:
            st.markdown(f"""
            <div class="stat-card-blue">
                <div class="stat-label">⭐ 권장 평균</div>
                <div class="stat-value">{RECOMMENDED:.1f}h</div>
            </div>
            """, unsafe_allow_html=True)
        
        with c4:
            st.markdown(f"""
            <div class="stat-card-teal">
                <div class="stat-label">🎯 목표 대비</div>
                <div class="stat-value">{(avg_sleep/RECOMMENDED*100):.0f}%</div>
            </div>
            """, unsafe_allow_html=True)
        
        # 히스토그램 (파스텔톤 색상 적용)
        fig = go.Figure()
        
        fig.add_bar(
            x=df_period["date"].dt.strftime("%m/%d"),
            y=df_period["sleep_hours"],
            name="수면시간",
            marker=dict(
                color=df_period["sleep_hours"],
                colorscale=[
                    [0, "#FFB3BA"],      # 파스텔 핑크
                    [0.25, "#FFDFBA"],   # 파스텔 오렌지
                    [0.5, "#FFFFBA"],    # 파스텔 옐로우
                    [0.75, "#BAFFC9"],   # 파스텔 그린
                    [1, "#BAE1FF"]       # 파스텔 블루
                ],
                cmin=5,
                cmax=9,
                line=dict(color="white", width=2)
            )
        )
        
        # 권장 수면시간 선
        fig.add_scatter(
            x=df_period["date"].dt.strftime("%m/%d"),
            y=[RECOMMENDED]*len(df_period),
            mode="lines",
            name="권장 수면시간",
            line=dict(dash="dash", color="#A8DADC", width=3)
        )
        
        # 평균 수면시간 선
        fig.add_scatter(
            x=df_period["date"].dt.strftime("%m/%d"),
            y=[avg_sleep]*len(df_period),
            mode="lines",
            name="내 평균",
            line=dict(dash="dot", color="#F1C0E8", width=3)
        )
        
        fig.update_layout(
            height=450,
            yaxis_title="수면시간 (시간)",
            xaxis_title="날짜",
            hovermode='x unified',
            plot_bgcolor='rgba(0,0,0,0)',
            paper_bgcolor='rgba(0,0,0,0)',
            yaxis=dict(gridcolor='rgba(128,128,128,0.2)'),
            legend=dict(
                orientation="h",
                yanchor="bottom",
                y=1.02,
                xanchor="right",
                x=1
            ),
            bargap=0.2
        )
        st.plotly_chart(fig, use_container_width=True)
    else:
        st.info("선택한 기간에 수면 기록이 없습니다.")

    st.markdown("---")

    col_tip, col_stats = st.columns(2)

    # ==================== 수면 팁 ====================
    with col_tip:
        st.markdown('<div id="sleep-tips"></div>', unsafe_allow_html=True)
        st.markdown("### 💡 기본 수면 개선 팁")
        for tip in [
            "🌙 매일 같은 시간에 취침",
            "📱 취침 전 스마트폰 줄이기",
            "☕ 오후 카페인 피하기",
            "🏃 가벼운 운동",
            "🌡️ 18~20도 유지"
        ]:
            st.markdown(f"<div class='sleep-tip'>{tip}</div>", unsafe_allow_html=True)

    # ==================== 전체 이용자 수면 분포 (이름 표시) ====================
    with col_stats:
        st.markdown('<div id="user-distribution"></div>', unsafe_allow_html=True)
        st.markdown("### 📊 전체 이용자 수면 분포")
        
        # 각 사용자별 평균 수면시간 계산
        user_avg = df_all.groupby("user_id")["sleep_hours"].mean().sort_index()
        
        # 사용자 이름 매핑
        user_names = [user_map.get(uid, f"사용자 {uid}") for uid in user_avg.index]
        
        fig_dist = go.Figure()
        
        # 선 그래프로 변경 (부트캠프 색상 적용)
        fig_dist.add_scatter(
            x=user_names,
            y=user_avg.values,
            mode='lines+markers',
            name='평균 수면시간',
            line=dict(color='#667EEA', width=3),
            marker=dict(
                size=12,
                color=user_avg.values,
                colorscale=[[0, "#F5576C"], [0.5, "#FFB946"], [1, "#43E97B"]],
                cmin=5,
                cmax=9,
                line=dict(color="white", width=2)
            ),
            fill='tozeroy',
            fillcolor='rgba(102, 126, 234, 0.1)'
        )
        
        # 전체 평균선
        overall_avg = user_avg.mean()
        fig_dist.add_hline(
            y=overall_avg,
            line_dash="dash",
            line_color="#764BA2",
            annotation_text=f"전체 평균: {overall_avg:.1f}h",
            annotation_position="top right"
        )
        
        # 권장 수면시간선
        fig_dist.add_hline(
            y=RECOMMENDED,
            line_dash="dot",
            line_color="#43E97B",
            annotation_text=f"권장: {RECOMMENDED:.1f}h",
            annotation_position="bottom right"
        )
        
        fig_dist.update_layout(
            height=300,
            xaxis_title="이용자",
            yaxis_title="평균 수면시간 (시간)",
            showlegend=False,
            margin=dict(t=20, b=20, l=20, r=20),
            plot_bgcolor='rgba(0,0,0,0)',
            paper_bgcolor='rgba(0,0,0,0)',
            yaxis=dict(gridcolor='rgba(128,128,128,0.2)'),
            xaxis=dict(tickangle=-45)
        )
        st.plotly_chart(fig_dist, use_container_width=True)
        
        # 통계 요약
        col1, col2, col3 = st.columns(3)
        with col1:
            st.metric("최소", f"{user_avg.min():.1f}h")
        with col2:
            st.metric("평균", f"{overall_avg:.1f}h")
        with col3:
            st.metric("최대", f"{user_avg.max():.1f}h")

    st.markdown("---")
    
    # ==================== 커뮤니티 꿀팁 공유 (좋아요 토글 & 삭제 기능 추가) ====================
    st.markdown('<div id="community-tips"></div>', unsafe_allow_html=True)
    st.markdown("### 🍯 불면증 극복 & 수면부족 꿀팁")
    st.markdown("*다른 사용자들이 공유한 실제로 효과있는 숙면 팁들이에요!*")
    
    # 좋아요 상태 초기화
    if 'user_liked_tips' not in st.session_state:
        st.session_state.user_liked_tips = set()
    
    # MySQL에서 팁 로드
    community_tips = load_community_tips()
    
    # 기존 팁 표시 (좋아요 순)
    for tip in community_tips:
        col1, col2 = st.columns([5, 1])
        
        with col1:
            # 좋아요 여부 확인
            is_liked = tip['tip_id'] in st.session_state.user_liked_tips
            like_color = "#FF6B6B" if is_liked else "#DDD"
            
            st.markdown(f"""
            <div class="community-tip">
                <div class="tip-header">👤 {tip['username']}</div>
                <div class="tip-content">{tip['tip_content']}</div>
                <div class="tip-footer">
                    <span style="color: {like_color}; font-weight: 700;">
                        ❤️ 좋아요 {tip['likes']}개
                    </span>
                    <span>{tip['created_at'].strftime('%Y-%m-%d')}</span>
                </div>
            </div>
            """, unsafe_allow_html=True)
        
        with col2:
            # 좋아요 버튼 (토글 기능)
            like_label = "❤️ 좋아요" if not is_liked else "💔 취소"
            if st.button(like_label, key=f"like_{tip['tip_id']}", use_container_width=True):
                if is_liked:
                    # 좋아요 취소
                    st.session_state.user_liked_tips.remove(tip['tip_id'])
                    update_tip_likes(tip['tip_id'], max(0, tip['likes'] - 1))
                else:
                    # 좋아요 추가
                    st.session_state.user_liked_tips.add(tip['tip_id'])
                    update_tip_likes(tip['tip_id'], tip['likes'] + 1)
                st.rerun()
            
            # 삭제 버튼
            if st.button("🗑️ 삭제", key=f"delete_{tip['tip_id']}", use_container_width=True):
                delete_community_tip(tip['tip_id'])
                st.success("✅ 팁이 삭제되었습니다.")
                st.rerun()
    
    # 새 팁 작성
    with st.expander("✏️ 나만의 꿀팁 공유하기"):
        with st.form("tip_form"):
            tip_user = st.text_input("닉네임", placeholder="익명")
            tip_content = st.text_area(
                "꿀팁 내용", 
                placeholder="당신만의 숙면 비법을 공유해주세요! 다른 사람들에게 큰 도움이 됩니다 😊",
                height=100
            )
            tip_submit = st.form_submit_button("🍯 꿀팁 등록하기")
            
            if tip_submit:
                if tip_content.strip():
                    username = tip_user if tip_user.strip() else "익명"
                    if save_community_tip(username, tip_content.strip()):
                        st.success("✅ 꿀팁이 DB에 등록되었습니다! 감사합니다 🙏")
                        st.rerun()
                    else:
                        st.error("❌ 등록 실패. 다시 시도해주세요.")
                else:
                    st.warning("꿀팁 내용을 입력해주세요!")
    
    st.markdown("---")
    
    # ==================== 수면 도움 컨텐츠 추천 ====================
    st.markdown('<div id="sleep-content"></div>', unsafe_allow_html=True)
    st.markdown("### 🎵 숙면을 위한 추천 컨텐츠")
    st.markdown("*편안한 잠자리를 위한 도움되는 자료들을 모았어요*")
    
    tab1, tab2, tab3 = st.tabs(["🎬 영상", "🎧 음악", "📰 아티클"])
    
    with tab1:
        st.markdown("#### 💤 수면 유도 영상")
        
        col1, col2 = st.columns(2)
        
        with col1:
            st.markdown("""
            <div style="background: linear-gradient(135deg, #667EEA 0%, #764BA2 100%); 
                        padding: 1rem; border-radius: 10px; color: white; margin-bottom: 1rem;">
                <h4 style="margin:0;">🌙 8시간 백색소음</h4>
                <p style="margin:0.5rem 0; font-size:0.9rem;">빗소리, 파도소리 등 자연의 소리</p>
                <a href="https://www.youtube.com/results?search_query=8+hour+white+noise+sleep" 
                   target="_blank" style="color: #FFE66D; text-decoration: none;">
                   ▶️ YouTube에서 보기
                </a>
            </div>
            """, unsafe_allow_html=True)
            
            st.markdown("""
            <div style="background: linear-gradient(135deg, #4FACFE 0%, #00F2FE 100%); 
                        padding: 1rem; border-radius: 10px; color: white; margin-bottom: 1rem;">
                <h4 style="margin:0;">🧘 수면 명상 가이드</h4>
                <p style="margin:0.5rem 0; font-size:0.9rem;">몸과 마음을 이완시키는 명상</p>
                <a href="https://www.youtube.com/results?search_query=sleep+meditation+korean" 
                   target="_blank" style="color: #FFE66D; text-decoration: none;">
                   ▶️ YouTube에서 보기
                </a>
            </div>
            """, unsafe_allow_html=True)
        
        with col2:
            st.markdown("""
            <div style="background: linear-gradient(135deg, #FA709A 0%, #FEE140 100%); 
                        padding: 1rem; border-radius: 10px; color: white; margin-bottom: 1rem;">
                <h4 style="margin:0;">✨ 수면 유도 ASMR</h4>
                <p style="margin:0.5rem 0; font-size:0.9rem;">부드러운 소리로 편안함 제공</p>
                <a href="https://www.youtube.com/results?search_query=sleep+asmr" 
                   target="_blank" style="color: white; text-decoration: none;">
                   ▶️ YouTube에서 보기
                </a>
            </div>
            """, unsafe_allow_html=True)
            
            st.markdown("""
            <div style="background: linear-gradient(135deg, #30CDC7 0%, #2E78D2 100%); 
                        padding: 1rem; border-radius: 10px; color: white; margin-bottom: 1rem;">
                <h4 style="margin:0;">🌌 우주 다큐멘터리</h4>
                <p style="margin:0.5rem 0; font-size:0.9rem;">차분한 내레이션의 우주 영상</p>
                <a href="https://www.youtube.com/results?search_query=space+documentary+sleep" 
                   target="_blank" style="color: #FFE66D; text-decoration: none;">
                   ▶️ YouTube에서 보기
                </a>
            </div>
            """, unsafe_allow_html=True)
    
    with tab2:
        st.markdown("#### 🎼 수면 음악 플레이리스트")
        
        music_list = [
            ("🎹 클래식 수면 음악", "모차르트, 쇼팽 등 편안한 클래식", "https://open.spotify.com/search/sleep%20classical"),
            ("🌊 자연의 소리", "빗소리, 파도, 새소리 등", "https://open.spotify.com/search/nature%20sounds%20sleep"),
            ("🎵 로파이 힙합", "부드러운 비트의 Lo-fi 음악", "https://open.spotify.com/search/lofi%20sleep"),
            ("🎻 힐링 피아노", "감성적인 피아노 연주", "https://open.spotify.com/search/healing%20piano%20sleep"),
        ]
        
        for title, desc, link in music_list:
            st.markdown(f"""
            <div style="background-color: #F8F9FA; padding: 0.8rem; border-radius: 8px; 
                        margin-bottom: 0.5rem; border-left: 4px solid #667EEA;">
                <div style="font-weight: 600; color: #333; margin-bottom: 0.3rem;">{title}</div>
                <div style="color: #666; font-size: 0.85rem; margin-bottom: 0.5rem;">{desc}</div>
                <a href="{link}" target="_blank" 
                   style="color: #667EEA; text-decoration: none; font-size: 0.85rem;">
                   🎧 Spotify에서 듣기 →
                </a>
            </div>
            """, unsafe_allow_html=True)
    
    with tab3:
        st.markdown("#### 📚 수면 건강 아티클")
        
        articles = [
            {
                "title": "🧠 수면의 과학: 왜 잠을 자야 할까?",
                "desc": "수면이 뇌와 신체에 미치는 영향에 대한 최신 연구",
                "source": "Nature",
                "link": "https://www.nature.com/subjects/sleep"
            },
            {
                "title": "😴 불면증 극복을 위한 인지행동치료",
                "desc": "약물 없이 불면증을 치료하는 효과적인 방법",
                "source": "Mayo Clinic",
                "link": "https://www.mayoclinic.org/diseases-conditions/insomnia/diagnosis-treatment/drc-20355173"
            },
            {
                "title": "⏰ 수면 리듬 회복하기",
                "desc": "일주기 리듬을 정상화하는 실용적인 팁",
                "source": "Sleep Foundation",
                "link": "https://www.sleepfoundation.org/"
            },
            {
                "title": "💤 최적의 수면 환경 만들기",
                "desc": "온도, 조명, 소음 등 숙면을 위한 환경 설정",
                "source": "Harvard Health",
                "link": "https://www.health.harvard.edu/topics/sleep"
            }
        ]
        
        for article in articles:
            st.markdown(f"""
            <div style="background-color: #FFFEF7; padding: 1rem; border-radius: 10px; 
                        margin-bottom: 1rem; border: 1px solid #FFE4A3;">
                <div style="font-weight: 600; color: #333; margin-bottom: 0.5rem; font-size: 1.05rem;">
                    {article['title']}
                </div>
                <div style="color: #666; margin-bottom: 0.7rem; line-height: 1.5;">
                    {article['desc']}
                </div>
                <div style="display: flex; justify-content: space-between; align-items: center;">
                    <span style="color: #999; font-size: 0.85rem;">📖 출처: {article['source']}</span>
                    <a href="{article['link']}" target="_blank" 
                       style="color: #667EEA; text-decoration: none; font-weight: 500;">
                       읽어보기 →
                    </a>
                </div>
            </div>
            """, unsafe_allow_html=True)

# ==================== 좌석표 페이지 ====================
def show_seating_page():
    st.markdown('<div class="main-header">🪑 FISA 좌석표</div>', unsafe_allow_html=True)
    
    if st.button("⬅️ 메인으로"):
        st.session_state.current_page = "main"
        st.rerun()
    
    tab1, tab2 = st.tabs(["🪑 좌석표", "⚙️ 좌석 배치"])
    
    # ==================
    # 탭 1: 좌석표 보기
    # ==================
    with tab1:
        col_left, col_right = st.columns([2, 1])
        
        with col_left:
            st.markdown('<div class="instagram-card">', unsafe_allow_html=True)
            
            # 왼쪽 구역 (4행 4열)
            st.markdown('<div class="section-title">📍 왼쪽 구역 (1분단)</div>', unsafe_allow_html=True)
            
            for row in range(4):
                cols = st.columns(4)
                for col in range(4):
                    with cols[col]:
                        seat_key = f"left_{row}_{col}"
                        user_id = st.session_state.seating.get(seat_key)
                        
                        if user_id:
                            user = get_user_by_id_mysql(user_id)
                            name = user['user_name'] if user else "알 수 없음"
                            
                            if st.button(name, key=f"btn_{seat_key}", use_container_width=True):
                                st.session_state.selected_user_info = user
                                st.rerun()
                        else:
                            st.button("빈 자리", key=f"btn_{seat_key}", 
                                    disabled=True, use_container_width=True)
            
            st.markdown("---")
            
            # 오른쪽 구역 (5행 4열)
            st.markdown('<div class="section-title">📍 오른쪽 구역 (2분단)</div>', unsafe_allow_html=True)
            
            for row in range(5):
                cols = st.columns(4)
                for col in range(4):
                    with cols[col]:
                        seat_key = f"right_{row}_{col}"
                        user_id = st.session_state.seating.get(seat_key)
                        
                        if user_id:
                            user = get_user_by_id_mysql(user_id)
                            name = user['user_name'] if user else "알 수 없음"
                            
                            if st.button(name, key=f"btn_{seat_key}", use_container_width=True):
                                st.session_state.selected_user_info = user
                                st.rerun()
                        else:
                            st.button("빈 자리", key=f"btn_{seat_key}", 
                                    disabled=True, use_container_width=True)
            
            st.markdown('</div>', unsafe_allow_html=True)
        
        with col_right:
            st.markdown('<div class="info-card">', unsafe_allow_html=True)
            
            if st.session_state.selected_user_info:
                user = st.session_state.selected_user_info
                st.markdown('<div class="info-header">👤 학생 정보</div>', unsafe_allow_html=True)
                
                st.markdown(f"### {user['user_name']}")
                
                if user.get('birth_year'):
                    st.markdown(f'<div class="info-item">🎂 출생연도: {user["birth_year"]}</div>', unsafe_allow_html=True)
                if user.get('major'):
                    st.markdown(f'<div class="info-item">📚 전공: {user["major"]}</div>', unsafe_allow_html=True)
                if user.get('mbti'):
                    st.markdown(f'<div class="info-item">🧠 MBTI: {user["mbti"]}</div>', unsafe_allow_html=True)
                if user.get('region'):
                    st.markdown(f'<div class="info-item">📍 지역: {user["region"]}</div>', unsafe_allow_html=True)
                if user.get('interests'):
                    st.markdown(f'<div class="info-item">💡 관심사: {user["interests"]}</div>', unsafe_allow_html=True)
                if user.get('study_name'):
                    st.markdown(f'<div class="info-item">📖 스터디: {user["study_name"]}</div>', unsafe_allow_html=True)
                if user.get('intro'):
                    st.markdown("---")
                    st.markdown("**자기소개**")
                    st.info(user['intro'])
                
                if st.button("❌ 닫기"):
                    st.session_state.selected_user_info = None
                    st.rerun()
            else:
                st.info("💡 좌석을 클릭하면 학생 정보를 볼 수 있습니다.")
            
            st.markdown('</div>', unsafe_allow_html=True)
    
    # ==================
    # 탭 2: 좌석 배치
    # ==================
    with tab2:
        st.markdown("### ⚙️ 좌석 배치 설정")
        st.info("💡 각 좌석에 배치할 학생을 선택하세요 (users_study_flat 테이블 연동)")
        
        users_list = get_all_users_from_mysql()
        user_options = ["빈 자리"] + [user['user_name'] for user in users_list]
        user_id_map = {user['user_name']: user['user_id'] for user in users_list}
        
        col_config1, col_config2 = st.columns(2)
        
        with col_config1:
            st.markdown("#### 📍 왼쪽 구역 (1분단) - 4x4")
            
            left_seating = {}
            for row in range(4):
                st.markdown(f"**{row+1}열**")
                cols = st.columns(4)
                for col in range(4):
                    with cols[col]:
                        seat_key = f"left_{row}_{col}"
                        current_user_id = st.session_state.seating.get(seat_key)
                        
                        default_idx = 0
                        if current_user_id:
                            user = get_user_by_id_mysql(current_user_id)
                            if user:
                                default_name = user['user_name']
                                if default_name in user_options:
                                    default_idx = user_options.index(default_name)
                        
                        selected = st.selectbox(
                            f"자리 {row+1}-{col+1}",
                            user_options,
                            index=default_idx,
                            key=f"config_{seat_key}",
                            label_visibility="collapsed"
                        )
                        
                        if selected != "빈 자리":
                            left_seating[seat_key] = user_id_map[selected]
        
        with col_config2:
            st.markdown("#### 📍 오른쪽 구역 (2분단) - 5x4")
            
            right_seating = {}
            for row in range(5):
                st.markdown(f"**{row+1}열**")
                cols = st.columns(4)
                for col in range(4):
                    with cols[col]:
                        seat_key = f"right_{row}_{col}"
                        current_user_id = st.session_state.seating.get(seat_key)
                        
                        default_idx = 0
                        if current_user_id:
                            user = get_user_by_id_mysql(current_user_id)
                            if user:
                                default_name = user['user_name']
                                if default_name in user_options:
                                    default_idx = user_options.index(default_name)
                        
                        selected = st.selectbox(
                            f"자리 {row+1}-{col+1}",
                            user_options,
                            index=default_idx,
                            key=f"config_{seat_key}",
                            label_visibility="collapsed"
                        )
                        
                        if selected != "빈 자리":
                            right_seating[seat_key] = user_id_map[selected]
        
        st.markdown("---")
        
        col_save1, col_save2, col_save3 = st.columns([1, 1, 2])
        with col_save1:
            if st.button("💾 좌석 배치 저장", use_container_width=True):
                all_seating = {**left_seating, **right_seating}
                
                seating_data = []
                for key, user_id in all_seating.items():
                    parts = key.split('_')
                    section = parts[0]
                    row = int(parts[1])
                    col = int(parts[2])
                    seating_data.append({
                        'section': section,
                        'row': row,
                        'col': col,
                        'user_id': user_id
                    })
                
                if save_seating_arrangement(seating_data):
                    st.session_state.seating = load_seating_arrangement()
                    st.success("✅ 좌석 배치가 MySQL DB에 저장되었습니다!")
                    st.balloons()
        
        with col_save2:
            if st.button("🔄 초기화", use_container_width=True):
                # 먼저 DB에서 모든 좌석 삭제
                conn = get_db_connection_dict()
                if conn:
                    try:
                        cursor = conn.cursor()
                        cursor.execute("DELETE FROM seating_arrangement")
                        conn.commit()
                        conn.close()
                        
                        # 세션 상태 초기화
                        st.session_state.seating = {}
                        st.success("✅ 좌석 배치가 초기화되었습니다!")
                        st.rerun()
                    except Exception as e:
                        st.error(f"❌ 초기화 실패: {str(e)}")
                        conn.close()
                else:
                    st.error("❌ 데이터베이스 연결 실패")

# ==================== 라우팅 ====================
if st.session_state.current_page == "main":
    show_main_page()

elif st.session_state.current_page == "sleep":
    show_sleep_page()

elif st.session_state.current_page == "seating":
    show_seating_page()

# ==================== 사이드바 ====================
with st.sidebar:
    st.markdown("### 🍪 메뉴")
    
    if st.button("🏠 메인 페이지", use_container_width=True):
        st.session_state.current_page = "main"
        st.rerun()
    
    if st.button("🪑 좌석표", use_container_width=True):
        st.session_state.current_page = "seating"
        st.rerun()
    
    if st.button("😴 수면 기록", use_container_width=True):
        st.session_state.current_page = "sleep"
        st.rerun()
    
    st.markdown("---")
    
    # 통계 정보
    if st.session_state.current_page == "main":
        st.markdown("### 📊 학습 통계")
        
        # 현재 진행도 계산
        total_hours = 960
        start_date = datetime(2025, 12, 30)
        current_date = datetime.now()
        days_elapsed = (current_date - start_date).days
        weeks_elapsed = days_elapsed // 7
        remaining_days_in_week = days_elapsed % 7
        study_days = weeks_elapsed * 5 + min(remaining_days_in_week, 5)
        elapsed_hours = max(0, study_days * 9)
        progress_percent = min(100, (elapsed_hours / total_hours) * 100)
        
        st.metric("진행 시간", f"{elapsed_hours}h / {total_hours}h")
        st.metric("진행률", f"{progress_percent:.1f}%")
    
    elif st.session_state.current_page == "seating":
        st.markdown("### 📊 좌석 통계")
        total_seats = 36
        occupied_seats = len(st.session_state.seating)
        st.metric("배치된 좌석", f"{occupied_seats}/{total_seats}")
        st.metric("빈 자리", f"{total_seats - occupied_seats}개")
        
        users_list = get_all_users_from_mysql()
        st.markdown("### 👥 전체 학생 수")
        st.metric("등록된 학생", f"{len(users_list)}명")