
    Ui                     x   d dl Zd dlmZ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 d dlZ ed          Z ed          Z ed          Z ed	          Zd
dddddddddd
ZdedefdZdedefdZdAdededefdZdefdZdedefdZdedefdZdefd Zdedefd!Zd"efd#ZdBd"ed%efd&Zd"efd'Z ded"efd(Z!d)e"fd*Z#dBd"ed%efd+Zd, Z$d%efd-Z%d.ed/efd0Z&d1efd2Z'd1ed%efd3Z(d1ed.ed/efd4Z)deded5ed6ed7ed8efd9Z*dedefd:Z+defd;Z,defd<Z-dedefd=Z.ded>e"fd?Z/defd@Z0dS )C    N)get_db_configloggerPROGRESS_PDF_PATH)	timedelta)datetimer   )DataBarRuleC_DBGoalskill_loginGoalskill_DB	Theory_DB)      )      )r      )   r   )r   r   )r      )r      )      )r      )   +   )
r   r         D   H   L   P   ^   b   
session_idcurrent_dayc                    d}	 t          j        j        di t          }|                    d          }t          |          \  }}d}|                    || ||f           |                                |r|                                 S S # t          $ r:}t          j        d|            Y d}~|r|                                 dS dS d}~ww xY w# |r|                                 w w xY w)u?   현재 일차의 CERT 아이템 조회 (THEORY/PRACTICE 모두)NT
dictionaryaw  
            SELECT uc.item_id, mi.name, mi.category, mi.item_type
            FROM user_curriculum uc
            JOIN master_items mi ON uc.item_id = mi.item_id
            WHERE uc.session_id = %s
              AND mi.block_id = %s
              AND mi.sort_order = %s
              AND mi.category = 'CERT'
            ORDER BY mi.item_id ASC
            LIMIT 1
        zget_cert_item_for_day error:  )mysql	connectorconnectC_DB_CONFIGcursor_day_to_block_and_sortexecutefetchoneclose	Exceptionr   error)r"   r#   connr,   block_id
sort_ordersqles           $/app/app/models/curriculum_module.pyget_cert_item_for_dayr9   *   s   D&5555--5kBB*
 	sZ:>???  
 	    8Q88999ttt	 s*   A1B 
CC.C CC C-c                    d}	 t          j        j        d
i t          }|                    d          }t          |          \  }}d}|                    || |f           |                                }g }t                      }	|D ]x}
t          j
        d|
d                   }|rYt          |                    d                    }||	vr3|	                    |           |                    ||
d         d           y||r|                                 S S # t           $ r:}t#          j        d	|            g cY d}~|r|                                 S S d}~ww xY w# |r|                                 w w xY w)uX   해당 월(block_id)에 속한 모든 CERT 장 번호 목록 반환 (13~20일 복습용)NTr%   aS  
            SELECT mi.name, mi.sort_order
            FROM user_curriculum uc
            JOIN master_items mi ON uc.item_id = mi.item_id
            WHERE uc.session_id = %s
              AND mi.block_id = %s
              AND mi.category = 'CERT'
              AND mi.item_type = 'THEORY'
            ORDER BY mi.sort_order ASC
        u   第(\d+)章namer   )chapterr;   z#get_cert_chapters_for_month error: r'   )r(   r)   r*   r+   r,   r-   r.   fetchallsetresearchintgroupaddappendr0   r1   r   r2   )r"   r#   r3   r,   r4   _r6   rowschaptersseenrowmatchch_numr7   s                 r8   get_cert_chapters_for_monthrL   F   s   D%&5555--,[99!	 	sZ2333   uu 		 		CInc&k::E U[[^^,,%%HHV$$$OO#) #F% %    
 	    >1>>???						 s0   C=D 
E#E;E<E  EE   E9section_numchapter_num	sub_rangec                 `   d}	 t          j        j        di t          }|                    d          }d|  }|rd| d}|                    ||f           nH|r+d| d}|                    ||d         |d	         f           nd| d
}|                    |           |                                |r|                                 S S # t          $ r:}t          j
        d|            g cY d}~|r|                                 S S d}~ww xY w# |r|                                 w w xY w)u  Theory_DB.section_N 테이블에서 이론 데이터를 조회
    
    Args:
        section_num: 섹션(章) 번호
        chapter_num: 특정 챕터만 조회 (예: '2-1')
        sub_range: (start, end) 서브챕터 번호 범위 필터 (예: (1, 4) → X-1 ~ X-4)
    NTr%   section_z
                SELECT id, chapter, main_title, sub_title, 
                       theory_description, tip, img, img_description
                FROM `zQ`
                WHERE chapter = %s
                ORDER BY id ASC
            z`
                WHERE CAST(SUBSTRING_INDEX(chapter, '-', -1) AS UNSIGNED) BETWEEN %s AND %s
                ORDER BY id ASC
            r   r   z.`
                ORDER BY id ASC
            zget_theory_data error: r'   )r(   r)   r*   THEORY_DB_CONFIGr,   r.   r=   r0   r1   r   r2   )rM   rN   rO   r3   r,   
table_namer6   r7   s           r8   get_theory_datarT   p   s    D'&::)9::-----
 	  "  C NN3//// 	  "  C NN31y| <==== "  C NN3  
 	    2q22333						 s0   B1C 
DD/D0D DD D-c                 |   d}	 t          j        j        d	i t          }|                    d          }d}|                    || f           |                                }|sddd|r|                                 S S |d         dd|r|                                 S S # |r|                                 w w xY w)
u:   유저의 현재 일차 확인 (Goalskill_DB.result.daily)NTr%   z
            SELECT daily
            FROM result
            WHERE session_id = %s
            ORDER BY id DESC
            LIMIT 1
        r   r   )r#   statusdailyr'   )r(   r)   r*   Goalskill_DB_CONFIGr,   r.   r/   r0   )r"   r3   r,   r6   rI   s        r8   get_user_progressrY      s    D&==)<==-- 	sZM***oo 	3#$22   #7|q994s   A$B"  
B" "B;c                 x   d}	 t          j        j        di t          }|                    d          }|dz
  }|dk     r	 |r|                                 dS dS d}|                    || |f           |                                |r|                                 S S # |r|                                 w w xY w)u+   N-1일차의 디버그 일지 조회 (C_DB)NTr%   r   z
            SELECT summary_content, ai_feedback, praise_content
            FROM debug_journals
            WHERE session_id = %s AND target_date = %s
        r'   )r(   r)   r*   r+   r,   r0   r.   r/   )r"   r#   r3   r,   prev_daysql_journals         r8   get_previous_feedbackr]      s    D&5555-- ?a<< 
 	{Z$:;;;   4s   <B  -B   B9c                    d}	 t          j        j        di t          }|                    d          }t          |          \  }}d}|                    || ||f           |                                }|r'|d         dk    r	 |r|                                 dS dS d}|                    || ||f           |	                                 t          j        d|  d	| d
| d| d|j         d           n.# t          $ r!}	t          j        d|	            Y d}	~	nd}	~	ww xY w|r|                                 dS dS # |r|                                 w w xY w)u^   오늘(current_day)에 해당하는 커리큘럼이 user_curriculum에 없으면 자동 배정NTr%   z
            SELECT COUNT(*) as cnt 
            FROM user_curriculum uc
            JOIN master_items mi ON uc.item_id = mi.item_id
            WHERE uc.session_id = %s AND mi.block_id = %s AND mi.sort_order = %s
        cntr   a  
            INSERT INTO user_curriculum (session_id, item_id, category, weight_pct, status, sort_order)
            SELECT %s, item_id, category, weight_pct, 'NOT_STARTED', item_id
            FROM master_items
            WHERE block_id = %s AND sort_order = %s
        z[init_user_curriculum] z: Dayz(block=z, sort=z) u   件を配定しましたu"   [init_user_curriculum] エラー: r'   )r(   r)   r*   r+   r,   r-   r.   r/   r0   commitr   inforowcountr1   r2   )
r"   r#   r3   r,   r4   r5   	sql_checkresult
sql_insertr7   s
             r8   init_user_curriculumrf      s   D &5555--  6kBB*	 	y:x"DEEE"" 	fUma'' 
 	zJ*#EFFF  \j  \  \{  \  \S[  \  \dn  \  \rx  sB  \  \  \  	]  	]  	]  	] ? ? ?=!==>>>>>>>>? 4s7   B C8 AC8 7E  8
D#DE  D##E   Ec                 :    | dz
  dz  }| dz
  dz  dz   }d|z   }||fS )uG   글로벌 일차(1~120)를 block_id(7~12)와 sort_order(1~20)로 변환r         r'   )r#   block_indexr5   r4   s       r8   r-   r-      s7    ?r)K?b(A-J;HZ    c                    d}	 t          j        j        d
i t          }|                    d          }t          |          \  }}d}|                    || ||f           |                                }|s	 |r|                                 dS dS d	                    d |D                       }|d         d         |d	|r|                                 S S # |r|                                 w w xY w)uX   오늘(N일차)의 커리큘럼 아이템 정보 조회 (user_curriculum + master_items)NTr%   !  
            SELECT uc.item_id, mi.name
            FROM user_curriculum uc
            JOIN master_items mi ON uc.item_id = mi.item_id
            WHERE uc.session_id = %s
              AND mi.block_id = %s
              AND mi.sort_order = %s
            ORDER BY mi.item_id ASC
        , c                     g | ]
}|d          S )r;   r'   .0items     r8   
<listcomp>z'get_today_item_info.<locals>.<listcomp>  s    "B"B"BD4<"B"B"Brk   r   item_id)rt   r;   r'   )
r(   r)   r*   r+   r,   r-   r.   r=   r0   join)	r"   r#   r3   r,   r4   r5   r6   itemscombined_names	            r8   get_today_item_inforx     s   D&5555--5kBB* 	sZ:>???!! 	  		"B"BE"B"B"BCC Qx	*!
 
 4s   A4C /C C3rt   c                 *   d}	 t          j        j        di t          }|                    d          }d}|                    || f           |                                |r|                                 S S # |r|                                 w w xY w)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=   r0   rt   r3   r,   r6   s       r8   get_learning_resourcesr|   "  s    D&5555--
 	sWJ'''  4   AA9 9Br   q_numc                 6   d}	 t          j        j        di t          }|                    d          }|dz
  }d}|                    || |f           |                                |r|                                 S S # |r|                                 w w xY 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/   r0   )rt   r~   r3   r,   offsetr6   s         r8   get_quiz_problemr   3  s    D&5555--  	sWf-...  4s   A#A? ?Bc                 *   d}	 t          j        j        di t          }|                    d          }d}|                    || f           |                                |r|                                 S S # |r|                                 w w xY w)uH   item_id로 특정 아이템의 이름과 정보를 조회 (master_items)NTr%   z9SELECT name, item_id FROM master_items WHERE item_id = %sr'   r   r{   s       r8   get_item_info_by_idr   G  s    D&5555--IsWJ'''  4r}   c                    d}	 t          j        j        di t          }|                    d          }d}|                    ||f           |                                }|sdg f|r|                                 S S d}|                    || |d         |d         f           |                                }d |D             }|s|g f|r|                                 S S d		                    d
gt          |          z            }	d|	 d}
|                    |
t          |                     |                                }||f|r|                                 S S # |r|                                 w w xY w)u   
    특정 item_id가 포함된 '오늘(Day)'의 모든 아이템과 리소스를 조회
    논리: item_id -> master_items에서 block_id, sort_order 찾기 -> 같은 날의 모든 아이템 -> 리소스 조회
    NTr%   zp
            SELECT block_id, sort_order 
            FROM master_items 
            WHERE item_id = %s
        rm   r4   r5   c                     g | ]
}|d          S )rt   r'   rp   s     r8   rs   z1get_daily_items_and_resources.<locals>.<listcomp>v  s    <<<DO<<<rk   ,%s
            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/   r0   r=   ru   lentuple)r"   rt   r3   r,   sql_infoinfo_row	sql_itemsdaily_itemsitem_idsformat_stringssql_resources	resourcess               r8   get_daily_items_and_resourcesr   T  s   
 D/&5555--
 	x',,,??$$ 	8B =	 	y:x
/CXlE["\]]]oo''<<<<< 	#?   4&3x=="899  .   	}eHoo666OO%%	I% 4s   A#E! ?AE! #A&E! !E:r   c                    | sg S d}	 t          j        j        di t          }|                    d          }d                    dgt          |           z            }d| d}|                    |t          |                      |	                                |r|
                                 S S # |r|
                                 w w xY w)	uP   해당 item_id 목록에 대한 PPT 리소스를 learning_resources에서 조회NTr%   r   r   r   zU) AND resource_type = 'PPT'
            ORDER BY item_id ASC, sort_order ASC
        r'   )r(   r)   r*   r+   r,   ru   r   r.   r   r=   r0   )r   r3   r,   r   r6   s        r8   get_ppt_resourcesr     s     	D&5555--4&3x=="899  .   	sE(OO,,,  4s   BB3 3Cc                    d}	 t          j        j        d
i t          }|                    d          }d}|                    || f           |                                }|r|d         nd}|dz
  }d}|                    || |f           |                                }	|	r	||k    |	d	<   |	|r|                                 S S # |r|                                 w w xY w)u<   퀴즈 문제 조회 (C_DB) + 마지막 문제 여부 확인NTr%   z<SELECT COUNT(*) as cnt FROM quiz_problems WHERE item_id = %sr_   r   r   r   is_lastr'   r   )
rt   r~   r3   r,   	sql_countcount_resulttotal_countr   r6   problems
             r8   r   r     s   D&5555-- S	y7*---((-9@l5))q  	sWf-...//## 	8 #(;"6GI4s   B)C Cc                      d} 	 t          d          }t          j        j        d
i |} |                     d          }d}|                    |           |                                }|r|d         nd}t          |d          | r|                                  S S # t          $ r:}t          j        d	|            Y d}~| r|                                  dS dS d}~ww xY w# | r|                                  w w xY w)u<   FE 문제(Q_DB)의 총 개수 조회 (최대 2개로 제한)NQ_DBTr%   z%SELECT COUNT(*) as cnt FROM section_1r_   r   r   zFE Count Error: r'   )r   r(   r)   r*   r,   r.   r/   minr0   r1   r   r2   )r3   configr,   r6   rd   totalr7   s          r8   get_fe_problem_countr     s    Dv&&&0000-- 6s"" "(.uQ5!}} 	    +++,,,qqq	 s*   BB 
C!'C>C$ C!!C$ $C=c                    d}d}	 t          j        j        di t          d          }|                    d          }| dz
  }d}|                    ||f           |                                }|r|                                 n# |r|                                 w w xY w|sdS |d         }d}d}	 t          j        j        di t          d          }|                    d          }d	}	|                    |	|f           |                                }|r|                                 n# |r|                                 w w xY w||d
         |ddS )u   
    문제 번호(q_num)에 해당하는 문제(Q), 보기(Example)를 조회
    논리: Q_DB에서 문제 가져오기 -> 해당 question_number로 Example_DB에서 보기 가져오기
    Nr   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)r   question_textchoicesr   r'   )r(   r)   r*   r   r,   r.   r/   r0   )
r~   q_dataconn_qr,   r   sql_qq_nor   conn_exsql_exs
             r8   get_fe_problem_detailr     s|    FF
"(AA=+@+@AA$// Luvi(((""!6<<>>>6!6<<>>>>! t#$D GG$/)HHM,,G,GHH400Yvw'''//###GMMOOO7#GMMOOOO#  	  s   A+B B!5A&D2 2Er   user_answerc                    d}d}	 t          j        j        di t          d          }|                    d          }d}|                    || f           |                                }|r|                                 n# |r|                                 w w xY w|sdddd	d
S |d         }|                                	                                |                                	                                k    }d}d}		 t          j        j        di t          d          }	|	                    d          }d}
|                    |
| f           |                                }|	r|	                                 n# |	r|	                                 w w xY 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Unknown   해설 데이터 없음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/   r0   striplowerget)r   r   correct_dataconn_ansr,   sql_ansreal_answerr   sol_dataconn_solsql_solr   r   s                r8   check_fe_answer_logicr     s1    LH&?*PP];N-O-OPPD11Fw 2333((%X^^%%%8%X^^%%%%% H#yQj  AG  H  H  	Hs#K##%%++--1B1B1D1D1J1J1L1LLJ HH&?*JJ]=-I-IJJD11Nw 2333??$$%X^^%%%8%X^^%%%%%+K %<< 	%"6*K%\\%   	%"5/K$ !%",	  s   A&B BA&F   Fr<   c                    d}	 t          j        j        di t          }|                    d          }d}|                    || f           |                                }|r|d         nd	 |r|                                 S S # t          $ r:}t          j
        d|            Y d}~|r|                                 dS dS d}~ww xY w# |r|                                 w w xY w)	u1   해당 챕터의 cert_questions 문제 수 조회NTr%   z=SELECT COUNT(*) as cnt FROM cert_questions WHERE chapter = %sr_   r   zcert_question_count error: r'   r(   r)   r*   r+   r,   r.   r/   r0   r1   r   r2   )r<   r3   r,   r6   rd   r7   s         r8   get_cert_question_countr   C  s	   D&5555--MsWJ'''"" &-ve}}A-
 	    6166777qqq	 s*   A)B 
C
C'C C

C C&c           	         d}	 t          j        j        di t          }|                    d          }|dz
  }d}|                    || |f           |                                }|s	 |r|                                 dS dS |d         |d         |d         |d	         |d
         |d         |d         d|d         |d         |d         dd|r|                                 S S # t          $ r:}t          j
        d|            Y d}~|r|                                 dS dS d}~ww xY w# |r|                                 w w xY w)u5   챕터 내 q_num번째 문제 조회 (cert_questions)NTr%   r   aJ  
            SELECT id, chapter, question_number, question_text, question_img,
                   option_1, option_2, option_3, option_4,
                   correct_answer, solution_text, solution_img
            FROM cert_questions
            WHERE chapter = %s
            ORDER BY id ASC
            LIMIT 1 OFFSET %s
        r   r   question_imgoption_1option_2option_3option_4)onetwothreefourr   solution_textsolution_imgF)r   r   r   r   r   r   r   r   zcert_question_detail error: r'   r   )r<   r~   r3   r,   r   r6   rI   r7   s           r8   get_cert_question_detailr   S  s   D'&5555-- 	sWf-...oo 	* %  ##45 1/::ZJ	  ""23 1/
 
& 	    7A77888ttt	 s1   A&C( AC( (
D,2D'	D/ 'D,,D/ /Ec                    d}	 t          j        j        di t          }|                    d          }d}|                    || |f           |                                }|sddddd	|r|                                 S S |d
         }|                                |                                k    }d}	d}
|	                    d          r|d         r|d         }	d}
n'|	                    d          r|d         r
|d         }	d}
|||	|
d	|r|                                 S S # t          $ r?}t          j        d|            ddddd	cY d}~|r|                                 S S d}~ww xY w# |r|                                 w w xY w)u2   cert_questions에서 정답 확인 + 해설 반환NTr%   z
            SELECT correct_answer, solution_text, solution_img
            FROM cert_questions
            WHERE chapter = %s AND question_number = %s
            LIMIT 1
        Fr   r   r   r   r   u   解説がありません。r   r   r   zcheck_cert_answer error: u	   エラーr'   )r(   r)   r*   r+   r,   r.   r/   r0   r   r   r1   r   r2   )r<   r   r   r3   r,   r6   rI   r   r   r   r   r7   s               r8   check_cert_answerr     s   D'&5555-- 	sWo6777oo 	L"'9Un  EK  L  L2 / *+!''))[->->-@-@@
3!77>"" 	&s>': 	&n-K$WW_%% 	&#o*> 	&o.K% %)& 0	
 
 	  z z z444555#yQ\rxyyyyyyy	z s7   A'D& BD& &
E/0E*E/E2 *E//E2 2Fsummaryscorefeedbackpraisec           	         d }	 t          j        j        di t          }|                                }d}|                    || |||||f           |                                 	 |r|                                 dS dS # t          $ r:}	t          j
        d|	            Y d }	~	|r|                                 dS dS d }	~	ww xY w# |r|                                 w w xY 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.   r`   r0   r1   r   r2   )
r"   r#   r   r   r   r   r3   r,   r6   r7   s
             r8   save_debug_journalr     s   D&5555	 	sZguhPVWXXX
 	    /A//000uuu	 s*   A!B   
C
B?!C ?CC C c                 ,   d }	 t          j        j        di t          }|                    d          }d}|                    || |f           |                                |r|                                 S S # |r|                                 w w xY 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'   r   )r"   r#   r3   r,   r6   s        r8   get_debug_journalr     s    D&5555--
 	sZ5666  4s   AA: :Bc                 *   d }	 t          j        j        di t          }|                    d          }d}|                    || f           |                                |r|                                 S S # |r|                                 w w xY 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'   rz   )r"   r3   r,   r6   s       r8   get_all_debug_journalsr     s    D&5555-- 	sZM***  4r}   c                 v   dd l }|                    t                    dd l}|                    dt
          d           ddlm}m} dd l	}dd l
}dd l}	  j        d|             t          }|j                            |d          }	|j                            |d|  d	          }
|j                            |
          r|
}n|	} |j        |          }|j         t'          j                              _         j        d
            |ddd           |d            |dddd          }fd} |dd            |dd           j        j        D ]q}|j        \  }}}}|dk    r]t1          ||dz             D ]6}t1          ||dz             D ] }                    ||          }||_        !7 j        d|            r|                    |
           |                                  j        d|
            dddd d!||
g} |j        |d"d"#          }|j        dk    r j        d$           d"S  j        d%|j                     dS # tB          $ rD} j        d&|            dd l"} j        |#                                           Y d }~dS d }~ww xY w)'Nr   ignoreopenpyxl)categorymodule)	AlignmentPatternFillu5   🚀 [Start] Excel & PDF Update Process for Session: 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           
      *   d}                     | |          }	 |j        t          |j                  nd}n# t          $ r d}Y nw xY w||z   }|dk    rd}||_        d|_         j        d|  d| d| d	|            d
}d}d}t          ||dz             D ]v}	                     ||	          }
	 |
j        t          |
j                  nd}n# t          $ r d}Y nw xY w                     | |	          }||k    r|_        o|_        wd S )N   rI   column              Y@z;;;u   ✏️ Row : z + z -> r   r      r   )cellvaluefloat
ValueErrornumber_formatra   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wss                r8   process_row_updatez9update_progress_excel_and_pdf.<locals>.process_row_update  s   IWW	WBBF"5;\5MeFL111SV " " "!" "O3G%"FL#(F FKfnffffff]dffggg NIG GaK88 / / gg.gII$<G<M<Yk&7 8 8 8_bII! $ $ $ #III$ !gg.gII i'''1K$$'.K$$/ /s!   : A	A	6CC#"C#r   g333330@   gQ?r   r   r   u,   📐 Fixed Alignment (Center/Vertical) for: u   💾 Excel Saved: libreofficez
--headlessz--convert-topdfz--outdirT)capture_outputr   u#   ✅ LibreOffice Conversion Success!u   ❌ LibreOffice Failed: u   🔥 Critical Error: )$logging	getLogger__name__warningsfilterwarningsUserWarningopenpyxl.stylesr   r   r   os
subprocessra   r   pathru   existsload_workbookactivetypeconditional_formattingmerged_cellsrangesboundsr  r  	alignmentsaver0   run
returncoder2   stderrr1   	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  cmdrd   r7   r4  r  r   r  r  s                              @@@@r8   update_progress_excel_and_pdfrD    s   NNNx((F OOOH{:NNN 76666666OOOIIICXJXXYYY %X/HIIGLL+Hz+H+H+HII	 7>>)$$ 	(#KK'K#X#K00Y
 %DD)B$C$C$E$E!MNNN ![XU\]]]
+--- !*		!
 !
 !
%	/ %	/ %	/ %	/ %	/ %	/ %	/ %	/T 	1d###2t$$$
 O2 	[ 	[L1=1D.GWgw !||w!44 ? ?A"7GaK88 ? ?!ww1Qw77)>? Y<YYZZZ
 		



444555 E
  DtDDD!!FK=>>>4FLCFMCCDDD5   0Q00111Y))++,,,uuuuu	s   G5I* I* *
J849J33J8c                     d}d}	 t          j        j        di t          }|                    d          }|                    d f           d |                                D             }|sHt          j        d             	 |r|	                                 |r|	                                 dS dS i }|D ]}t          |          \  }}	d}
|                    |
 ||	f           |                                }|D ];}|d         }t          |d	                   }|                    |d
          |z   ||<   <|	                                 |sHt          j        d             	 |r|	                                 |r|	                                 dS dS t          j        j        di t          }|                                }d} fd|                                D             }|                    ||           |                                 t          j        dt#          |           d  dd                    d |                                D                       z              n.# t&          $ r!}t          j        d|            Y d}~nd}~ww xY w|r|	                                 |r|	                                 dS dS # |r|	                                 |r|	                                 w w xY w)u  
    デバッグ日誌完了時にカテゴリ別の進捗率を再計算して保存する。

    ロジック（毎回ゼロから再計算 → 重複防止）:
    1. debug_journals から完了済みの全日次(target_date)を取得
    2. 各日次 → block_id + sort_order → user_curriculum + master_items
    3. カテゴリ別に weight_pct を合算
    4. Goalskill_DB.user_category_progress に UPSERT
    NTr%   zESELECT DISTINCT target_date FROM debug_journals WHERE session_id = %sc                     g | ]
}|d          S )target_dater'   )rq   rI   s     r8   rs   z,update_category_progress.<locals>.<listcomp>  s    LLL#m,LLLrk   z)[CategoryProgress] No completed days for aH  
                SELECT mi.category, mi.weight_pct
                FROM user_curriculum uc
                JOIN master_items mi ON uc.item_id = mi.item_id
                WHERE uc.session_id = %s
                  AND mi.block_id = %s
                  AND mi.sort_order = %s
                  AND mi.weight_pct > 0
            r   
weight_pctr   z+[CategoryProgress] No weight_pct items for z
            INSERT INTO user_category_progress (session_id, category, total_pct)
            VALUES (%s, %s, %s)
            ON DUPLICATE KEY UPDATE
                total_pct = VALUES(total_pct)
        c           
      X    g | ]&\  }}|t          t          |d           d          f'S )r   r   )roundr   )rq   catpctr"   s      r8   rs   z,update_category_progress.<locals>.<listcomp>  sF     
 
 
S eCUOOQ778
 
 
rk   z[CategoryProgress] Updated z categories for r   rn   c                 $    g | ]\  }}| d | dS )=%r'   )rq   rK  rL  s      r8   rs   z,update_category_progress.<locals>.<listcomp>  s(    NNNXS#C#NNNrk   z[CategoryProgress] Error: r'   )r(   r)   r*   r+   r,   r.   r=   r   ra   r0   r-   r  r   rX   rv   executemanyr`   r   ru   r1   r2   )r"   r#   conn_cconn_gcursor_ccompleted_dayscategory_pctdayr4   r5   r6   rF   rI   rK  rL  cursor_g
upsert_sqlrows_to_insertr7   s   `                  r8   update_category_progressrZ    s    FFF"(77;77==D=11SM	
 	
 	
 ML8I8I8K8KLLL 	KPJPPQQQp !6<<>>>!6<<>>>>>!!m ! 	E 	EC#9##>#> HjC S:x"DEEE$$&&D E E*oC-..$0$4$4S#$>$>$DS!!E
 	 	KRjRRSSS6 !6<<>>>!6<<>>>>>!!3 (??+>??==??

 
 
 
(..00
 
 
 	Z888]#n*=*=]]z]]]iiNN9K9K9M9MNNNOOP	
 	
 	
 	

  7 7 75!55666666667 !6<<>>>!6<<>>>>>!! !6<<>>>!6<<>>>>!s>   A?I1 7B3I1 CI1 0K 1
J;JK JK /K>returnc                    d}	 t          j        j        di t          }|                    d          }d}|                    || f           |                                |r|                                 S S # t          $ r:}t          j
        d|            g cY d}~|r|                                 S S d}~ww xY w# |r|                                 w w xY w)u   
    ユーザーのカテゴリ別進捗率を取得する。
    Returns: [{'category': 'Python', 'total_pct': 25.5}, ...]
    NTr%   u>  
            SELECT category, total_pct
            FROM user_category_progress
            WHERE session_id = %s
            ORDER BY FIELD(category,
                'CERT','Python','JavaScript','MySQL','FastAPI',
                '外部API連携','AWS','MCP','PJT企画','PJT設計','PJT開発','テスト')
        z[CategoryProgress] Get Error: r'   )r(   r)   r*   rX   r,   r.   r=   r0   r1   r   r2   )r"   r3   r,   r6   r7   s        r8   get_category_progressr]    s    
 D&==)<==-- 	sZM***  
 	    9a99:::						 s0   AA9 9
B=B8B=C  8B==C   Cc           
         d}	 t          j        j        di t          }|                    d          }d}|                    |           |                                }g }|D ]q}|d         r|d         |d         |                    |d         |d         dk    t          |d                   d	z
  t          |d                   d	z
  d
           r||r|	                                 S S # t          $ r:}t          j        d|            g cY d}~|r|	                                 S S d}~ww xY w# |r|	                                 w w xY w)u   
    유저의 커리큘럼 계획표(간트 차트용) 데이터를 가져옵니다.
    최우선: C_DB.user_curriculum
    차선: C_DB.master_items (user_curriculum에 데이터가 없을 경우)
    NTr%   z
            SELECT category, MIN(block_id) as min_block, MAX(block_id) as max_block
            FROM master_items
            WHERE category IS NOT NULL
            GROUP BY category
        r   	min_block	max_blockCERTr   )r   is_certstart_month	end_monthz[get_curriculum_plan] Error: r'   )r(   r)   r*   r+   r,   r.   r=   rD   rA   r0   r1   r   r2   )r"   r3   r,   
sql_masterrF   
categoriesrA  r7   s           r8   get_curriculum_planrg    s    D&5555--
 	z"""  
 	 	AZ= AkN$:an>TjMZ=F2"1[>22Q6 ;0014	      
 	    8Q88999						 s0   CC/ /
D39D.D3D6 .D33D6 6E)NN)r   )1mysql.connectorr(   app.core.configr   r   r   r   r   r   r%  r$  openpyxl.formatting.ruler   r?   r+   Goalskill_login_DB_CONFIGrX   rR   THEORY_SUB_RANGESstrrA   r9   rL   r   rT   rY   r]   rf   r-   rx   r|   r   r   r   listr   r   r   r   r   r   r   r   r   r   rD  rZ  r]  rg  r'   rk   r8   <module>ro     s       D D D D D D D D D D       ( ( ( ( ( ( ( (      				 0 0 0 0 0 0 				 mF##)M*;<< #mN33  =--  	  $c     8(C (c ( ( ( (T0 0 03 0% 0 0 0 0d#    4c     2#S #s # # # #J         C c    BC    " c #    (    5c 5C 5 5 5 5n    * c #    B  2+ + + + +Z73 7S 7 7 7 7xS     *c *# * * * *X*s *S *s * * * *X3 S 3 s ^a kn    4# C    "s    "Qc Q Q Q QpR" R"3 R" R" R" R"jc d    4&C & & & & & &rk   