from typing import List, Dict
import mysql.connector
from app.core.config import B_DB_CONFIG
from app.core import logger
from datetime import datetime, timezone, timedelta

# ---------------------------------------------------------
# [Report/Chat 전용 DB 모듈]
# 대상 DB: B_DB (DAILY_DB_CONFIG 사용)
# ---------------------------------------------------------

# 1. 대화 내역(로그) 저장 (변경 없음)
def save_daily_log(session_id, sender, message):
    try:
        conn = mysql.connector.connect(**B_DB_CONFIG)
        cursor = conn.cursor()
        sql = "INSERT INTO daily_log (session_id, sender, message) VALUES (%s, %s, %s)"
        cursor.execute(sql, (session_id, sender, message))
        conn.commit()
        cursor.close()
        conn.close()
    except Exception as e:
        logger.error(f"Daily Log Save Error: {e}")

# 2. 유저 일기(노트) 저장 -> daily_note 테이블 (통일)
def save_structured_diary(session_id, date, learning, mindset, ai_comment):
    try:
        conn = mysql.connector.connect(**B_DB_CONFIG)
        cursor = conn.cursor()
        
        # boss_comment 컬럼이 있다면 NULL로 들어가거나 DB 기본값 사용
        sql = """
            INSERT INTO daily_note 
            (session_id, date, learning, today_mindset, ai_comment) 
            VALUES (%s, %s, %s, %s, %s)
        """
        
        cursor.execute(sql, (session_id, date, learning, mindset, ai_comment))
        conn.commit()
        cursor.close()
        conn.close()
        logger.info(f"Daily Note Saved: {date} - {session_id}")
        
    except Exception as e:
        logger.error(f"Daily Note Save Error: {e}")
        

def get_daily_log_from_db(session_id):
    try:
        conn = mysql.connector.connect(**B_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        sql = "SELECT sender, message, created_at FROM daily_log WHERE session_id = %s ORDER BY id ASC"
        cursor.execute(sql, (session_id,))
        rows = cursor.fetchall()
        cursor.close()
        conn.close()
        return rows
    except Exception as e:
        logger.error(f"Daily Log Load Error: {e}")
        return []
        
# 3. 모든 일기 조회 -> daily_note 테이블
# ★ 수정사항: ORDER BY date DESC, id DESC (최신순 정렬)
def get_all_daily_notes(session_id):
    try:
        conn = mysql.connector.connect(**B_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        
        sql = """
            SELECT * FROM daily_note 
            WHERE session_id = %s 
            ORDER BY date DESC, id DESC
        """
        cursor.execute(sql, (session_id,))
        results = cursor.fetchall()
        
        cursor.close()
        conn.close()
        return results
    except Exception as e:
        logger.error(f"All Daily Notes Load Error: {e}")
        return []
    
# 4. 가장 최근 데이터 1개 조회 -> daily_note 테이블
# ★ 수정사항: daily_note에서 가장 늦게 만들어진(최신) 데이터 가져오기
def get_yesterday_data(session_id):
    try:
        conn = mysql.connector.connect(**B_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        
        sql = """
            SELECT * FROM daily_note 
            WHERE session_id = %s 
            ORDER BY date DESC, id DESC 
            LIMIT 1
        """
        
        cursor.execute(sql, (session_id,))
        result = cursor.fetchone()
        
        cursor.close()
        conn.close()
        return result
    except Exception as e:
        logger.error(f"Latest Data Load Error: {e}")
        return None

# [추가] 오늘 날짜의 데이터만 조회하는 함수
def get_today_data(session_id):
    try:
        JST = timezone(timedelta(hours=9))
        today_str = datetime.now(JST).strftime("%Y-%m-%d")
        
        conn = mysql.connector.connect(**B_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        
        sql = "SELECT * FROM daily_note WHERE session_id = %s AND date = %s ORDER BY id DESC LIMIT 1"
        cursor.execute(sql, (session_id, today_str))
        result = cursor.fetchone()
        
        cursor.close()
        conn.close()
        return result
    except Exception as e:
        logger.error(f"Today Data Load Error: {e}")
        return None
    
# 5. AI 피드백 업데이트 -> daily_note 테이블
def update_yesterday_ai_feedback(session_id, ai_comment, advice):
    try:
        conn = mysql.connector.connect(**B_DB_CONFIG)
        cursor = conn.cursor()
        
        # id 기준 업데이트
        sql = "UPDATE daily_note SET ai_comment = %s, advice = %s WHERE id = %s"
        
        cursor.execute(sql, (ai_comment, advice, session_id))
        conn.commit()
        
        cursor.close()
        conn.close()
        logger.info(f"Updated AI Feedback for ID: {session_id}")
        
    except Exception as e:
        logger.error(f"AI Feedback Update Error: {e}")

# 6. 상사(Boss) 코멘트 업데이트 -> daily_note 테이블
def update_boss_comment(session_id, date, boss_comment):
    try:
        conn = mysql.connector.connect(**B_DB_CONFIG)
        cursor = conn.cursor()
        
        # session_id와 date가 일치하는 행 업데이트
        sql = "UPDATE daily_note SET boss_comment = %s WHERE session_id = %s AND date = %s"
        
        cursor.execute(sql, (boss_comment, session_id, date))
        conn.commit()
        
        row_count = cursor.rowcount
        cursor.close()
        conn.close()
        
        logger.info(f"Updated Boss Comment for {session_id} on {date}. Affected: {row_count}")
        return row_count > 0
        
    except Exception as e:
        logger.error(f"Boss Comment Update Error: {e}")
        return False

# 7. 오늘 작성 여부 확인 (변경 없음, 테이블명만 daily_note 확인)
def check_today_daily_log_exists(session_id: str) -> bool:
    conn = None
    try:
        JST = timezone(timedelta(hours=9))
        now = datetime.now(JST)
        today_str = now.strftime("%Y-%m-%d")
        
        conn = mysql.connector.connect(**B_DB_CONFIG)
        cursor = conn.cursor()
        
        sql = "SELECT id FROM daily_note WHERE session_id = %s AND date = %s LIMIT 1"
        
        cursor.execute(sql, (session_id, today_str))
        row = cursor.fetchone()
        
        cursor.close()
        conn.close()
        
        return True if row else False

    except Exception as e:
        logger.error(f"Check Today Log Error: {e}")
        if conn and conn.is_connected():
            conn.close()
        return False