HealthSync_Intelligence/app/repositories/queries/similar_mission_queries.py

# app/repositories/queries/similar_mission_queries.py
"""
HealthSync AI 유사 사용자 미션 관련 쿼리 모음 (건강 데이터 포함)
"""


class SimilarMissionQueries:
    """유사 사용자 미션 관련 쿼리 (건강 데이터 강화)"""

    # 최근 24시간 내 미션 완료 이력 조회 (건강 데이터 포함)
    GET_RECENT_MISSION_COMPLETIONS = """
        SELECT 
            mch.member_serial_number,
            u.name,
            u.occupation,
            u.birth_date,
            EXTRACT(YEAR FROM AGE(u.birth_date)) as age,
            umg.mission_name,
            umg.mission_description,
            mch.daily_completed_count,
            mch.completion_date,
            mch.created_at,
            -- 건강 데이터 추가
            hc.height,
            hc.weight,
            hc.bmi,
            hc.waist_circumference,
            hc.systolic_bp,
            hc.diastolic_bp,
            hc.fasting_glucose,
            hc.total_cholesterol,
            hc.hdl_cholesterol,
            hc.ldl_cholesterol,
            hc.triglyceride,
            hc.ast,
            hc.alt,
            hc.gamma_gtp,
            hc.serum_creatinine,
            hc.hemoglobin,
            hc.smoking_status,
            hc.drinking_status
        FROM goal_service.mission_completion_history mch
        INNER JOIN user_service.user u ON mch.member_serial_number = u.member_serial_number
        INNER JOIN goal_service.user_mission_goal umg ON mch.mission_id = umg.mission_id
        LEFT JOIN health_service.health_checkup hc ON u.member_serial_number = hc.member_serial_number
        WHERE mch.member_serial_number = ANY(:user_ids)
        AND mch.completion_date >= CURRENT_DATE - INTERVAL '1 day'
        AND mch.daily_completed_count >= mch.daily_target_count
        AND (hc.reference_year IS NULL OR hc.reference_year = (
            SELECT MAX(reference_year) 
            FROM health_service.health_checkup 
            WHERE member_serial_number = u.member_serial_number
        ))
        ORDER BY mch.created_at DESC
        LIMIT 20
    """

    # 사용자 건강 정보 조회 (벡터 생성용)
    GET_USER_HEALTH_FOR_VECTOR = """
        SELECT 
            u.member_serial_number,
            u.name,
            u.occupation,
            EXTRACT(YEAR FROM AGE(u.birth_date)) as age,
            u.updated_at,
            hc.height,
            hc.weight,
            hc.bmi,
            hc.waist_circumference,
            hc.systolic_bp,
            hc.diastolic_bp,
            hc.fasting_glucose,
            hc.total_cholesterol,
            hc.hdl_cholesterol,
            hc.ldl_cholesterol,
            hc.triglyceride,
            hc.ast,
            hc.alt,
            hc.gamma_gtp,
            hc.serum_creatinine,
            hc.hemoglobin,
            hc.smoking_status,
            hc.drinking_status
        FROM user_service.user u
        LEFT JOIN health_service.health_checkup hc ON u.member_serial_number = hc.member_serial_number
        WHERE u.member_serial_number = :user_id
        AND (hc.reference_year IS NULL OR hc.reference_year = (
            SELECT MAX(reference_year) 
            FROM health_service.health_checkup 
            WHERE member_serial_number = u.member_serial_number
        ))
    """

    # 직업 코드별 이름 조회
    GET_OCCUPATION_NAME = """
        SELECT 
            occupation_code,
            occupation_name,
            category
        FROM user_service.occupation_type
        WHERE occupation_code = :occupation_code
    """

    # 사용자 기본 정보 조회 (여러 사용자)
    GET_USERS_BASIC_INFO = """
        SELECT 
            u.member_serial_number,
            u.name,
            u.occupation,
            ot.occupation_name,
            EXTRACT(YEAR FROM AGE(u.birth_date)) as age
        FROM user_service.user u
        LEFT JOIN user_service.occupation_type ot ON u.occupation = ot.occupation_code
        WHERE u.member_serial_number = ANY(:user_ids)
    """

    # 벡터 처리를 위한 모든 사용자 데이터 조회
    GET_ALL_USERS_FOR_VECTOR = """
        SELECT 
            u.member_serial_number,
            u.name,
            u.occupation,
            EXTRACT(YEAR FROM AGE(u.birth_date)) as age,
            u.updated_at,
            hc.height,
            hc.weight,
            hc.bmi,
            hc.waist_circumference,
            hc.systolic_bp,
            hc.diastolic_bp,
            hc.fasting_glucose,
            hc.total_cholesterol,
            hc.hdl_cholesterol,
            hc.ldl_cholesterol,
            hc.triglyceride,
            hc.ast,
            hc.alt,
            hc.gamma_gtp,
            hc.serum_creatinine,
            hc.hemoglobin,
            hc.visual_acuity_left,
            hc.visual_acuity_right,
            hc.hearing_left,
            hc.hearing_right,
            hc.urine_protein,
            hc.smoking_status,
            hc.drinking_status
        FROM user_service.user u
        LEFT JOIN health_service.health_checkup hc ON u.member_serial_number = hc.member_serial_number
        WHERE hc.reference_year IS NULL OR hc.reference_year = (
            SELECT MAX(reference_year) 
            FROM health_service.health_checkup 
            WHERE member_serial_number = u.member_serial_number
        )
        ORDER BY u.member_serial_number
    """