db.py

from datetime import date, timedelta
import pymysql
import os
from dotenv import load_dotenv
from pymysql.cursors import DictCursor
load_dotenv()

DB_PASSWORD = os.environ['DB_PASSWORD']

def get_connection():
    return pymysql.connect(
        host='localhost',
        user='root',
        password = DB_PASSWORD,
        db='dementia_app',
        charset='utf8mb4',
        cursorclass=pymysql.cursors.DictCursor
    )
    
def register_user(username, password):
    conn = get_connection()
    try:
        with conn.cursor() as cursor:
            cursor.execute("SELECT * FROM users WHERE username = %s", (username,))
            if cursor.fetchone():
                return False
            cursor.execute("INSERT INTO users (username, password) VALUES (%s, %s)", (username, password))
            conn.commit()
            return True
    finally:
        conn.close()

def insert_user_image(user_id, image_path, scheduled_date, description):
    conn = get_connection()
    try:
        with conn.cursor() as cursor:
            sql = """
                INSERT INTO user_images (user_id, image_path, scheduled_date, description)
                VALUES (%s, %s, %s, %s)
            """
            cursor.execute(sql, (user_id, image_path, scheduled_date, description))
        conn.commit()
    finally:
        conn.close()
        
def get_today_image(user_id, today_date):
    conn = get_connection()
    try:
        with conn.cursor() as cursor:
            sql = """
                SELECT * FROM user_images
                WHERE user_id = %s AND scheduled_date = %s
                ORDER BY id LIMIT 1
            """
            cursor.execute(sql, (user_id, today_date))
            return cursor.fetchone()
    finally:
        conn.close()
        
def get_next_available_date(user_id):
    conn = get_connection()
    try:
        with conn.cursor() as cursor:
            sql = """
                SELECT MAX(scheduled_date) AS last_date
                FROM user_images
                WHERE user_id = %s
            """
            cursor.execute(sql, (user_id,))
            result = cursor.fetchone()
            last_date = result["last_date"]

            today = date.today()

            if last_date is None or last_date < today:
                return today 
            else:
                return last_date + timedelta(days=1)
    finally:
        conn.close()

def get_fallback_image():
    conn = get_connection()
    try:
        with conn.cursor() as cursor:
            sql = "SELECT * FROM fallback_images ORDER BY RAND() LIMIT 1"
            cursor.execute(sql)
            return cursor.fetchone()
    finally:
        conn.close()

def insert_chat_report(user_id, image_id, fallback_image_id, chat_summary, memo=""):
    conn = get_connection()
    try:
        with conn.cursor() as cursor:
            sql = """
                INSERT INTO chat_reports (user_id, image_id, fallback_image_id, chat_summary, memo)
                VALUES (%s, %s, %s, %s, %s)
            """
            cursor.execute(sql, (user_id, image_id, fallback_image_id, chat_summary, memo))
            conn.commit()
            print("✅ DB 저장 성공")
    except Exception as e:
        print("❌ DB 저장 실패:", e)
    finally:
        conn.close()
        
def get_video_by_category(category, index=None):
    conn = get_connection()
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    if index is not None:
        cursor.execute(
            "SELECT url FROM media_library WHERE category=%s ORDER BY id LIMIT 1 OFFSET %s",
            (category, index)
        )
    else:
        cursor.execute(
            "SELECT url FROM media_library WHERE category=%s ORDER BY RAND() LIMIT 1",
            (category,)
        )
    row = cursor.fetchone()
    conn.close()
    return row['url'] if row else None

def get_latest_chat_report(user_id):
    conn = get_connection()
    try:
        with conn.cursor(pymysql.cursors.DictCursor) as cursor:
            sql = """
                SELECT report_date, chat_summary, memo
                FROM chat_reports
                WHERE user_id = %s
                ORDER BY report_date DESC
                LIMIT 1
            """
            cursor.execute(sql, (user_id,))
            return cursor.fetchone()
    finally:
        conn.close()

def insert_user_info(user_id, name, age, trigger_elements, gender, dementia_level):
    conn = get_connection()
    try:
        with conn.cursor() as cursor:
            cursor.execute("SELECT * FROM user_info WHERE user_id = %s", (user_id,))
            if cursor.fetchone():
                return False

            sql = """
                INSERT INTO user_info (user_id, name, age, trigger_elements, gender, dementia_level)
                VALUES (%s, %s, %s, %s, %s, %s)
            """
            cursor.execute(sql, (user_id, name, age, trigger_elements, gender, dementia_level))
            conn.commit()
            return True
    finally:
        conn.close()


def get_user_info(user_id):
    conn = get_connection()
    try:
        with conn.cursor() as cursor:
            sql = "SELECT name, age, trigger_elements, gender, dementia_level FROM user_info WHERE user_id = %s"
            cursor.execute(sql, (user_id,))
            return cursor.fetchone()
    finally:
        conn.close()