o
    ZziMM                     @   sl  d dl Zd dlmZmZ d dlmZ d dlmZmZ d dlZd dlZd dl	Z	d dl
mZ edZedZdefd	d
ZdedefddZdedefddZdefddZd0dedefddZdefddZdedefddZd0dedefddZdd ZdefddZd ed!efd"d#Zdeded$ed%ed&ed'efd(d)Zdedefd*d+Zdefd,d-Zdefd.d/ZdS )1    N)get_db_configlogger)	timedelta)datetimer   )DataBarRuleC_DBGoalskill_login
session_idc                 C   ^   d}z$t jjdi t}|jdd}d}||| f | W |r&|  S S |r.|  w w )u:   유저의 현재 진도와 상태 확인 (Goalskill_login)NT
dictionaryz
            SELECT current_day, status, created_at
            FROM user_study_progress
            WHERE session_id = %s
            ORDER BY current_day DESC
            LIMIT 1
         )mysql	connectorconnectGoalskill_login_DB_CONFIGcursorexecutefetchonecloser	   connr   sqlr   r   >/home/air/goalskill_sales/back/app/models/curriculum_module.pyget_user_progress   s    r   current_dayc                 C   s   d}z8t jjdi t}|jdd}|d }|dk r%W |r#|  dS dS d}||| |f | W |r:|  S S |rB|  w w )u+   N-1일차의 디버그 일지 조회 (C_DB)NTr      z
            SELECT summary_content, ai_feedback, praise_content
            FROM debug_journals
            WHERE session_id = %s AND target_date = %s
        r   )r   r   r   C_DB_CONFIGr   r   r   r   )r	   r   r   r   prev_daysql_journalr   r   r   get_previous_feedback    s    r    c                 C   s   d}zEt jjdi t}|jdd}d}||| |f | }|s-W |r+|  dS dS ddd |D }|d d	 |d
W |rG|  S S |rO|  w w )u>   오늘(N일차)의 커리큘럼 아이템 정보 조회 (C_DB)NTr   
            SELECT C.item_id, C.name
            FROM daily_schedules D
            JOIN curriculum_items C ON D.item_id = C.item_id
            WHERE D.session_id = %s AND D.target_date = %s
            ORDER BY C.sort_order ASC
        z, c                 S      g | ]}|d  qS )namer   .0itemr   r   r   
<listcomp>L       z'get_today_item_info.<locals>.<listcomp>r   item_id)r)   r#   r   )	r   r   r   r   r   r   fetchallr   join)r	   r   r   r   r   itemscombined_namer   r   r   get_today_item_info9   s   	
 r.   r)   c                 C   r
   )u!   유튜브 리소스 조회 (C_DB)NTr   z
            SELECT title, url, thumbnail_url
            FROM learning_resources
            WHERE item_id = %s AND resource_type = 'YOUTUBE'
        r   r   r   r   r   r   r   r*   r   r)   r   r   r   r   r   r   get_learning_resourcesV   s    r1   r   q_numc                 C   sh   d}z)t jjdi t}|jdd}|d }d}||| |f | W |r+|  S S |r3|  w w )u   퀴즈 문제 조회 (C_DB)NTr   r   
            SELECT question_text, correct_answer, explanation
            FROM quiz_problems
            WHERE item_id = %s
            LIMIT 1 OFFSET %s
        r   r   r   r   r   r   r   r   r   )r)   r2   r   r   offsetr   r   r   r   get_quiz_problemg   s    r6   c                 C   r
   )u@   item_id로 특정 아이템의 이름과 정보를 조회 (C_DB)NTr   z=SELECT name, item_id FROM curriculum_items WHERE item_id = %sr   r4   r0   r   r   r   get_item_info_by_id{   s    r7   c                 C   s
  d}zzt jjdi t}|jdd}d}||| |f | }|s.dg fW |r-|  S S |d }d}||| |f | }dd |D }	|	sU|g fW |rT|  S S d		d
gt
|	 }
d|
 d}||t|	 | }||fW |r||  S S |r|  w w )u   
    특정 item_id가 포함된 '오늘(Day)'의 모든 아이템과 리소스를 조회
    논리: item_id -> target_date 찾기 -> 해당 date의 모든 item_id 찾기 -> 리소스 조회
    NTr   z~
            SELECT target_date 
            FROM daily_schedules 
            WHERE session_id = %s AND item_id = %s
        target_dater!   c                 S   r"   )r)   r   r$   r   r   r   r'      r(   z1get_daily_items_and_resources.<locals>.<listcomp>,z%sz
            SELECT resource_id, item_id, title, url, thumbnail_url
            FROM learning_resources
            WHERE item_id IN (zL) AND resource_type = 'YOUTUBE'
            ORDER BY sort_order ASC
        r   )r   r   r   r   r   r   r   r   r*   r+   lentuple)r	   r)   r   r   sql_datedate_rowr8   	sql_itemsdaily_itemsitem_idsformat_stringssql_resources	resourcesr   r   r   get_daily_items_and_resources   s4    rD   c           
      C   s   d}zHt jjd
i t}|jdd}d}||| f | }|r%|d nd}|d }d}||| |f | }	|	rA||k|	d	< |	W |rJ|  S S |rR|  w w )u<   퀴즈 문제 조회 (C_DB) + 마지막 문제 여부 확인NTr   z<SELECT COUNT(*) as cnt FROM quiz_problems WHERE item_id = %scntr   r   r3   is_lastr   r4   )
r)   r2   r   r   	sql_countcount_resulttotal_countr5   r   problemr   r   r   r6      s     c               
   C   s   d} zYz4t d}tjjd
i |} | jdd}d}|| | }|r(|d nd}t|dW W | r7|   S S  t	y[ } zt
d	|  W Y d}~W | rU|   dS dS d}~ww | rc|   w w )u<   FE 문제(Q_DB)의 총 개수 조회 (최대 2개로 제한)NQ_DBTr   z%SELECT COUNT(*) as cnt FROM section_1rE   r      zFE Count Error: r   )r   r   r   r   r   r   r   minr   	Exceptionr   error)r   configr   r   resulttotaler   r   r   get_fe_problem_count   s$   
rT   c           
      C   s   d}d}z*t jjdi td}|jdd}| d }d}|||f | }W |r.|  n|r6|  w w |s;dS |d }d}d}z&t jjdi td}|jdd}d	}	||	|f | }W |ri|  n|rq|  w w ||d
 |ddS )u   
    문제 번호(q_num)에 해당하는 문제(Q), 보기(Example)를 조회
    논리: Q_DB에서 문제 가져오기 -> 해당 question_number로 Example_DB에서 보기 가져오기
    NrK   Tr   r   z:SELECT question_number, Q FROM section_1 LIMIT 1 OFFSET %squestion_number
Example_DBzFSELECT one, two, three, four FROM section_1 WHERE question_number = %sQF)rU   question_textchoicesrF   r   )r   r   r   r   r   r   r   r   )
r2   q_dataconn_qr   r5   sql_qq_norY   conn_exsql_exr   r   r   get_fe_problem_detail   s>   
 
 r`   rU   user_answerc                 C   sF  d}d}z&t jjdi td}|jdd}d}||| f | }W |r*|  n|r2|  w w |s<dddd	d
S |d }| 	 | 	 k}d}d}	z&t jjdi td}	|	jdd}d}
||
| f | }W |	rv|	  n|	r~|	  w w d}d	}|r|
d	r|d	 }d	}n|
dr|d }d}||||d
S )u   
    정답 확인 및 해설 조회
    1. Correct_Answer_DB에서 정답(A) 조회 및 비교
    2. Solution_DB에서 해설(text/img) 조회
    NCorrect_Answer_DBTr   z2SELECT A FROM section_1 WHERE question_number = %sFUnknownu   해설 데이터 없음text)
is_correctcorrect_answerexplanationexplanation_typeASolution_DBz:SELECT text, img FROM section_1 WHERE question_number = %su   해설이 없습니다.imgr   )r   r   r   r   r   r   r   r   striplowerget)rU   ra   correct_dataconn_ansr   sql_ansreal_answerre   sol_dataconn_solsql_solrg   rh   r   r   r   check_fe_answer_logic!  sP   
 
 

rv   summaryscorefeedbackpraisec           
   
   C   s   d }zPz+t jjdi t}| }d}||| |||||f |  W W |r-|  dS dS  tyR }	 zt	
d|	  W Y d }	~	W |rL|  dS dS d }	~	ww |rZ|  w w )Na  
            INSERT INTO debug_journals 
            (session_id, target_date, summary_content, understanding_score, ai_feedback, praise_content, created_at)
            VALUES (%s, %s, %s, %s, %s, %s, NOW())
            ON DUPLICATE KEY UPDATE
                summary_content = VALUES(summary_content),
                understanding_score = VALUES(understanding_score),
                ai_feedback = VALUES(ai_feedback),
                praise_content = VALUES(praise_content)
        TzSave Journal Error: Fr   )r   r   r   r   r   r   commitr   rN   r   rO   )
r	   r   rw   rx   ry   rz   r   r   r   rS   r   r   r   save_debug_journalZ  s    
r|   c                 C   s`   d }z%t jjdi t}|jdd}d}||| |f | W |r'|  S S |r/|  w w )NTr   z
            SELECT summary_content, understanding_score, ai_feedback, praise_content, created_at
            FROM debug_journals
            WHERE session_id = %s AND target_date = %s
        r   r4   )r	   r   r   r   r   r   r   r   get_debug_journalt  s    r}   c                 C   s^   d }z$t jjdi t}|jdd}d}||| f | W |r&|  S S |r.|  w w )NTr   z
            SELECT created_at, summary_content, ai_feedback, praise_content
            FROM debug_journals
            WHERE session_id = %s
            ORDER BY created_at DESC
        r   r/   r   r   r   r   get_all_debug_journals  s    r~   c              
      sL  dd l }|tdd l}|jdtdd ddlm}m} dd l	}dd l
}dd l}zՈd|   d}|j|d}	|j|d	|  d
}
|j|
rO|
}n|	}||}|jtj _d |dddd |d d|ddddd} fdd}|dd |dd jjD ]3}|j\}}}}|dkrt||d D ]}t||d D ]}j||d}||_qqd|  q||
 |  d|
  dd d!d"d#||
g}|j|d$d$d%}|jdkrd& W d$S d'|j  W dS  t y% } zd(|  dd l!}|"  W Y d }~dS d }~ww ))Nr   ignoreopenpyxl)categorymodule)	AlignmentPatternFillu5   🚀 [Start] Excel & PDF Update Process for Session: z)/home/air/goalskill_t/front/progress_PDF/zGOALSKill_progress.xlsx	progress_z.xlsxu7   🧹 Cleared all existing conditional formatting rules.63C384solid)start_color	end_color	fill_type)r   center   F)
horizontalverticaltext_rotation	wrap_textc              
      s  d}j | |d}z|jd urt|jnd}W n ty"   d}Y nw || }|dkr-d}||_d|_d|  d| d| d	|  d
}d}d}t||d D ]5}	j ||	d}
z|
jd uret|
jnd}W n tys   d}Y nw j | |	d}||kr |_qQ|_qQd S )N   rowcolumng        g      Y@z;;;u   ✏️ Row z: z + z ->          r   )cellvaluefloat
ValueErrornumber_formatinforangefill)target_row_idxincrement_value	x_col_idxcell_xcurrent_valnew_valheader_row_idx	start_colend_colcol_idxheader_cell	thresholdtarget_cell
green_fillr   no_fillwsr   r   process_row_update  s6   "z9update_progress_excel_and_pdf.<locals>.process_row_updater   g333330@   gQ?rL   r   r   u,   📐 Fixed Alignment (Center/Vertical) for: u   💾 Excel Saved: libreofficez
--headlessz--convert-topdfz--outdirT)capture_outputrd   u#   ✅ LibreOffice Conversion Success!u   ❌ LibreOffice Failed: u   🔥 Critical Error: )#logging	getLogger__name__warningsfilterwarningsUserWarningopenpyxl.stylesr   r   r   os
subprocessr   pathr+   existsload_workbookactivetypeconditional_formattingmerged_cellsrangesboundsr   r   	alignmentsaver   run
returncoderO   stderrrN   	traceback
format_exc)r	   r   r   r   r   r   r   r   base_dirtemplate_file	user_filetarget_pathwbvertical_center_alignr   merged_rangemin_colmin_rowmax_colmax_rowrcr   cmdrQ   rS   r   r   r   r   update_progress_excel_and_pdf  s~   





*



r   )r   )mysql.connectorr   app.core.configr   r   r   r   r   r   r   openpyxl.formatting.ruler   r   r   strr   intr    r.   r1   r6   r7   rD   rT   r`   rv   r|   r}   r~   r   r   r   r   r   <module>   s2    2!-"9