
    ii                     @   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 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dTd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dUd#ed&efd'Z d#efd(Z!ded#efd)Z"d*e#fd+Z$dUd#ed&efd,Z d- Z%d&efd.Z&d/ed0efd1Z'd2efd3Z(d2ed&efd4Z)dVd2ed/ed0edefd5Z*deded6ed7ed8ed9efd: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@Z0dedAefdBZ1ded#efdCZ2ded#edDedEefdFZ3dGedHed?efdIZ4d#efdJZ5dedKedLefdMZ6dN Z7dO Z8ded#efdPZ9ded#edDefdQZ:dedefdRZ;defdSZ<dS )W    N)get_db_configloggerPROGRESS_PDF_PATH)	timedelta)datetimer   )DataBarRuleC_DBGoalskill_loginGoalskill_DB	Theory_DBA_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
dictionarya  
            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' OR mi.category LIKE '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_dayr;   +   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' OR mi.category LIKE 'CERT\_%')
              AND mi.item_type = 'THEORY'
            ORDER BY mi.sort_order ASC
           第(\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appendr1   r2   r   r3   )r#   r$   r5   r-   r6   _r8   rowschaptersseenrowmatchch_numr9   s                 r:   get_cert_chapters_for_monthrO   G   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@   r1   r2   r   r3   )rP   rQ   rR   r5   r-   
table_namer8   r9   s           r:   get_theory_datarW   q   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/   r0   r1   )r#   r5   r-   r8   rL   s        r:   get_user_progressr\      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-   r1   r/   r0   )r#   r$   r5   r-   prev_daysql_journals         r:   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=, sort=) u   件を配定しましたu"   [init_user_curriculum] エラー: r(   )r)   r*   r+   r,   r-   r.   r/   r0   r1   commitr   inforowcountr2   r3   )
r#   r$   r5   r-   r6   r7   	sql_checkresult
sql_insertr9   s
             r:   init_user_curriculumrk      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_indexr7   r6   s       r:   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     r:   
<listcomp>z'get_today_item_info.<locals>.<listcomp>  s    "B"B"BD4<"B"B"Brp   r   item_id)ry   r>   r(   )
r)   r*   r+   r,   r-   r.   r/   r@   r1   join)	r#   r$   r5   r-   r6   r7   r8   itemscombined_names	            r:   get_today_item_infor}     s   D&5555--5kBB* 	sZ:>???!! 	  		"B"BE"B"B"BCC Qx	*!
 
 4s   A4C /C C3ry   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@   r1   ry   r5   r-   r8   s       r:   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   z
            SELECT question_text, correct_answer, explanation
            FROM quiz_problems
            WHERE item_id = %s
            LIMIT 1 OFFSET %s
        r(   r)   r*   r+   r,   r-   r/   r0   r1   )ry   r   r5   r-   offsetr8   s         r:   get_quiz_problemr   4  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       r:   get_item_info_by_idr   H  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
        rr   r6   r7   c                     g | ]
}|d          S )ry   r(   ru   s     r:   rx   z1get_daily_items_and_resources.<locals>.<listcomp>w  s    <<<DO<<<rp   ,%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/   r0   r1   r@   rz   lentuple)r#   ry   r5   r-   sql_infoinfo_row	sql_itemsdaily_itemsitem_idsformat_stringssql_resources	resourcess               r:   get_daily_items_and_resourcesr   U  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-   rz   r   r/   r   r@   r1   )r   r5   r-   r   r8   s        r:   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 = %srb   r   r   z
            SELECT problem_id, question_text, correct_answer, explanation
            FROM quiz_problems
            WHERE item_id = %s
            LIMIT 1 OFFSET %s
        is_lastr(   r   )
ry   r   r5   r-   	sql_countcount_resulttotal_countr   r8   problems
             r:   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_1rb   r   r   zFE Count Error: r(   )r   r)   r*   r+   r-   r/   r0   minr1   r2   r   r3   )r5   configr-   r8   ri   totalr9   s          r:   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/   r0   r1   )
r   q_dataconn_qr-   r   sql_qq_nor   conn_exsql_exs
             r:   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/   r0   r1   striplowerget)r   r   correct_dataconn_ansr-   sql_ansreal_answerr   sol_dataconn_solsql_solr   r   s                r:   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 = %srb   r   zcert_question_count error: r(   r)   r*   r+   r,   r-   r/   r0   r1   r2   r   r3   )r?   r5   r-   r8   ri   r9   s         r:   get_cert_question_countr   D  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   r5   r-   r   r8   rL   r9   s           r:   get_cert_question_detailr   T  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}|r|	                    d          rd}	 t          j        j        di t          }|                                }d}|                    |||d         |	|f           |
                                 n.# t          $ r!}t          j        d|            Y d}~nd}~ww xY w|r|                                 n# |r|                                 w w xY w|	||
|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)uB   cert_questions에서 정답 확인 + 해설 반환 + 로그 저장NTr&   z
            SELECT id, 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   idz
                    INSERT INTO cert_results (session_id, question_id, is_correct, user_answer)
                    VALUES (%s, %s, %s, %s)
                zSave cert_results error: zcheck_cert_answer error: u	   エラーr(   )r)   r*   r+   r,   r-   r/   r0   r1   r   r   re   r2   r   r3   )r?   r   r   r#   r5   r-   r8   rL   r   r   r   r   conn_ccursor_crj   r9   s                   r:   check_cert_answerr     s   D9&5555-- 	sWo6777oo 	L"'9Un  EK  L  LV S *+!''))[->->-@-@@
3!77>"" 	&s>': 	&n-K$WW_%% 	&#o*> 	&o.K%  	##''$-- 	#F#0??;??!==??
   j#d)ZQ\-]^^^ > > ><<<========>  #LLNNN  #LLNNNN# %)& 0	
 
 	  z z z444555#yQ\rxyyyyyyy	z st   A'H BH "A%F G 
F3F.)G .F33G 6H G&&	H 
II.I/I II I,summaryscorefeedbackpraisec           	         d }	 t          j        j        di t          }|                    d          }|                    d| |f           |                                }|rGd}	|                    |	|||||d         f           t          j        d|d          d|  d|            n8d	}
|                    |
| |||||f           t          j        d
|  d|            |	                                 	 |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)NTr&   zXSELECT journal_id FROM debug_journals WHERE session_id = %s AND target_date = %s LIMIT 1z
                UPDATE debug_journals
                SET summary_content = %s,
                    understanding_score = %s,
                    ai_feedback = %s,
                    praise_content = %s
                WHERE journal_id = %s
            
journal_idz[Journal] UPDATED journal_id=z for session= day=z
                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())
            z+[Journal] INSERTED new journal for session=zSave Journal Error: Fr(   )r)   r*   r+   r,   r-   r/   r0   r   rf   re   r1   r2   r3   )r#   r$   r   r   r   r   r5   r-   existing
update_sql
insert_sqlr9   s               r:   save_debug_journalr     s   D)&5555--
 	f%	
 	
 	
 ??$$ 	fJ NN:&(S_J`'abbbK{8N{{]g{{ny{{||||J
 NN:
K%QY[a'bcccKdjddWbddeee
 	    /A//000uuu	 s*   C2D 
EE2E EE E1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$   r5   r-   r8   s        r:   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(   r   )r#   r5   r-   r8   s       r:   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   rL   column              Y@z;;;u   ✏️ Row : z + z -> r   r      r   )cellvaluefloat
ValueErrornumber_formatrf   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                r:   process_row_updatez9update_progress_excel_and_pdf.<locals>.process_row_updateE  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
subprocessrf   r   pathrz   existsload_workbookactivetypeconditional_formattingmerged_cellsrangesboundsr  r  	alignmentsaver1   run
returncoder3   stderrr2   	traceback
format_exc)r#   r)  r,  r   r   r   r0  r1  base_dirtemplate_file	user_filetarget_pathwbvertical_center_alignr$  merged_rangemin_colmin_rowmax_colmax_rowrcr  cmdri   r9   r@  r!  r   r"  r#  s                              @@@@r:   update_progress_excel_and_pdfrP    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(   )rv   rL   s     r:   rx   z,update_category_progress.<locals>.<listcomp>  s    LLL#m,LLLrp   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   )rv   catpctr#   s      r:   rx   z,update_category_progress.<locals>.<listcomp>  sF     
 
 
S eCUOOQ778
 
 
rp   z[CategoryProgress] Updated z categories for r  rs   c                 $    g | ]\  }}| d | dS )=%r(   )rv   rW  rX  s      r:   rx   z,update_category_progress.<locals>.<listcomp>  s(    NNNXS#C#NNNrp   z[CategoryProgress] Error: r(   )r)   r*   r+   r,   r-   r/   r@   r   rf   r1   r.   r  r   r[   r{   executemanyre   r   rz   r2   r3   )r#   r$   r   conn_gr   completed_dayscategory_pctdayr6   r7   r8   rI   rL   rW  rX  cursor_g
upsert_sqlrows_to_insertr9   s   `                  r:   update_category_progressrd    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                 6   d}d}	 t          j        j        d
i t          }|                    d          }|                    d           d |                                D             }t          j        j        d
i t          }|                    d          }|                    d| f           d |                                D             fd|D             |r|                                 |r|                                 S S # t          $ rP}t          j        d	|            g cY d}~|r|                                 |r|                                 S S d}~ww xY w# |r|                                 |r|                                 w w xY w)u  
    ユーザーのカテゴリ別進捗率を取得する。
    master_items に存在する全カテゴリを基準にし、user_category_progress の値で
    上書きする。学習を開始していないカテゴリは 0% を返すことで、進捗モーダルで
    全カテゴリを常に表示できるようにする。
    Returns: [{'category': 'Python', 'total_pct': 25.5}, ...]
    NTr&   u  
            SELECT DISTINCT category
            FROM master_items
            WHERE category IS NOT NULL AND category <> ''
            ORDER BY
                CASE WHEN category LIKE 'CERT\_%' THEN 0 ELSE 1 END,
                FIELD(category,
                    'Python','JavaScript','MySQL','FastAPI',
                    '外部API連携','AWS','MCP','PJT企画','PJT設計','PJT開発','テスト'),
                category
            c                     g | ]
}|d          S )r   r(   rv   rM  s     r:   rx   z)get_category_progress.<locals>.<listcomp>  s    EEEA!J-EEErp   zLSELECT category, total_pct FROM user_category_progress WHERE session_id = %sc                 ,    i | ]}|d          |d         S r   	total_pctr(   rh  s     r:   
<dictcomp>z)get_category_progress.<locals>.<dictcomp>  s"    SSS!*q~SSSrp   c                 B    g | ]}|                     |d           dS )r	  rj  r   )rv   rW  progress_maps     r:   rx   z)get_category_progress.<locals>.<listcomp>  sA     
 
 
 <+;+;C+E+EFF
 
 
rp   z[CategoryProgress] Get Error: r(   )r)   r*   r+   r,   r-   r/   r@   r[   r1   r2   r   r3   )r#   r   r]  r   all_categoriesra  r9   ro  s          @r:   get_category_progressrq    s    FF#"(77;77==D=11
	
 	
 	
 FE1B1B1D1DEEE(??+>??==D=11ZM	
 	
 	
 TSx?P?P?R?RSSS
 
 
 
%
 
 
 !6<<>>>!6<<>>>>!    9a99:::					!6<<>>>!6<<>>>>! !6<<>>>!6<<>>>>!s0   CD 
E&E!.E&/E) !E&&E) )/FrE   c                 6   d}	 t          j        j        di t          }|                    d          }|dk    rd}nd}d| d}|                    || f           |                                }i }|D ]}|                    d	          }	d
}
|	|
v}|dk    rG|                    d          pd}|}|                    d          r|	                    dd          n|}n|                    d          pd}|}||vr	||g d||<   ||         d         
                    |d         |d         |                    d          |                    d          |                    d          ||r|	ndd           |t          |                                          d|r|                                 S S # t          $ r=}t          j        d|            |g dcY d}~|r|                                 S S d}~ww xY w# |r|                                 w w xY w)u  
    카테고리(자격증/PJT)별 학습 항목 상세 조회.
    user_curriculum.status에 'NOT_STARTED' 외의 값(완료일자 등)이 있으면 활성으로 판정.

    Args:
        session_id: 학습자 세션 ID
        group: 'CERT' 또는 'PJT'

    Returns:
        {
          "group": "CERT"|"PJT",
          "tabs": [
            { "tab_key": str, "tab_label": str,
              "items": [ { item_id, name, category, block_id, sort_order,
                           is_active, status_value } ] }
          ]
        }
    NTr&   CERTz4(mi.category = 'CERT' OR mi.category LIKE 'CERT\_%')zTmi.category IS NOT NULL AND mi.category <> 'CERT' AND mi.category NOT LIKE 'CERT\_%'a  
            SELECT mi.item_id, mi.name, mi.category, mi.block_id, mi.sort_order,
                   mb.month_no, mb.block_title,
                   uc.status AS uc_status
            FROM master_items mi
            LEFT JOIN monthly_blocks mb ON mi.block_id = mb.block_id
            LEFT JOIN user_curriculum uc
                   ON uc.item_id = mi.item_id AND uc.session_id = %s
            WHERE zQ
            ORDER BY mi.block_id ASC, mi.sort_order ASC, mi.item_id ASC
        	uc_statusN NOT_STARTEDr   CERT_rv  u	   その他)tab_key	tab_labelr{   r{   ry   r>   r6   r7   )ry   r>   r   r6   r7   	is_activestatus_value)rE   tabsz[get_category_detail] Error: r(   )r)   r*   r+   r,   r-   r/   r@   r   
startswithreplacerG   listvaluesr1   r2   r   r3   )r#   rE   r5   r-   
cat_filterr8   rI   r}  rM  r|  inactive_marksr{  cat_rawry  rz  r9   s                   r:   get_category_detailr  *  s   & D?&5555--F??PJJ6 
 
 
 
 	sZM***   	 	A55--L6N$N:I%%
++5v!<C<N<Nw<W<WdGOOGR888]d		%%
++:{#	d""&!*! !W M'"))Y<&	EE*--EE*--eeL11&09 Ct+ +     T[[]](;(;<<
 	  , , ,8Q88999+++++++	, s0   FF5 5
G<?G7G<G? 7G<<G? ?Hc                 "   d}	 t          j        j        di t          }|                    d          }|                    d| |f           |                                }|r|d         nd}d}||v r	 |r|                                 dS dS |                    d|f           |                                pi }|                    d          }|                    d	          }	|                    d
|f           |	                                }
|
D ]i}|                    d          pd
                                dk    r:|                    d          pd                    d          d         }|rd| |d<   jt          | |||	          }|||	||
|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   
    특정 item에 대한 학습 리소스 조회. user_curriculum.status가 활성(완료)된 항목만 반환.
    비활성/미완료 항목이면 None을 돌려준다(프론트에서 차단).
    NTr&   zQSELECT status FROM user_curriculum WHERE session_id = %s AND item_id = %s LIMIT 1rY   ru  zBSELECT name, category FROM master_items WHERE item_id = %s LIMIT 1r>   r   z
            SELECT resource_id, title, url, resource_type, thumbnail_url, sort_order
            FROM learning_resources
            WHERE item_id = %s
            ORDER BY sort_order ASC, resource_id ASC
            resource_typerv  PPTurl/z components/ppt_viewer.html?file=)ry   r>   r   r|  r   quizzesz[get_item_resources] Error: r(   )r)   r*   r+   r,   r-   r/   r0   r1   r   r@   uppersplit_get_item_quiz_historyr2   r   r3   )r#   ry   r5   r-   rL   r|  r  	item_info	item_nameitem_categoryr   rM  	file_namer  r9   s                  r:   get_item_resourcesr    s~   
 D6&5555--_!	
 	
 	
 oo(+5s8}}2>))T Q 	PJ	
 	
 	
 OO%%+	MM&))	!j11 J	
 	
 	
 OO%%	  	N 	NAo&&,"3355>>UU5\\/R66s;;B?	 NM)MMAeH(WiWW %("
 
 	    7A77888ttt	 s1   A/F. DF. .
G28G-G5 -G22G5 5Hr  r  c                     t          |          o|dk    p|                    d          }|rt          | ||          S t          | |          S )uF   item 종류에 따라 CERT/IT 분기로 풀이 이력을 가져온다.rs  rx  )boolr~  _get_cert_quiz_history_get_python_quiz_history)r#   ry   r  r  is_certs        r:   r  r    s_    =!! D=#;#;G#D#D   F%j'9EEE#J888rp   r6   r7   c                 X    | |dS t          |           dz
  dz  t          |          z   S )uH   master_items.block_id + sort_order → 글로벌 일차(1~120) 역변환Nr   rn   rm   )rD   )r6   r7   s     r:   _block_and_sort_to_dayr    s3    :-qMMA#c*oo55rp   c                 &   d}	 t          j        j        di t          }|                    d          }|                    d| f           |                                }|s	 |r|                                 dS dS t          |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)	u9   item_id로 해당 아이템의 글로벌 일차를 조회NTr&   zHSELECT block_id, sort_order FROM master_items WHERE item_id = %s LIMIT 1r6   r7   z[_get_item_day] Error: r(   )r)   r*   r+   r,   r-   r/   r0   r1   r  r2   r   r3   )ry   r5   r-   rL   r9   s        r:   _get_item_dayr    s<   D&5555--VJ	
 	
 	
 oo 	  &c*os<7HII
 	    2q22333ttt	 s0   AB0 =B0 0
C4:C/C7 /C44C7 7Ditem_daytarget_categoryc                    |sg S ddl m} d}	 t          j        j        di |}|                    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\   chat_log_DB.chat_messages에서 해당 일차의 퀴즈 카테고리 로그를 가져온다.r   )CHAT_LOG_DB_CONFIGNTr&   z
            SELECT id, log, sender, daily, category, created_at
            FROM chat_messages
            WHERE session_id = %s AND category = %s AND daily = %s
            ORDER BY id ASC
            z"[_load_chat_quiz_messages] Error: r(   )app.core.configr  r)   r*   r+   r-   r/   r@   r1   r2   r   r3   )r#   r  r  r  r5   r-   r9   s          r:   _load_chat_quiz_messagesr    s!    	222222D&<<);<<-- (3	
 	
 	
   
 	    =!==>>>						 s0   AA> >
CB= C!C =CC Cc                    | sg S t          |pg           }g }d}t          |           }||k     r| |         }|d         dv r	 t          |          }n# t          $ r d}Y nw xY w|                    d          pd}d}	d}
|dz   |k     rV| |dz            d         dv rC| |dz                                d          pd}	| |dz                                d	          }
|dz  }|	od
|	v pd|	v od|	v}|r|                    d          ndpt          |          dz   }|                    d|r|d         n|d         d| |r|                    d          nddd|r|                    d          nd|r|                    d          ndp|	|t          |          t          |
p|                    d	          pd          dd           |dz  }||k     |S )uC   user/bot 메시지를 순서대로 quiz_problems(C_DB)와 페어링r   senderuserMNlogrv  r   botI
created_at   正解u	   さすが	   不正解r   python
problem_idr      問題r   r   r   T)	quiz_typeref_idlabelr   r   optionsr   r   r   r   	solved_atfrom_chat_log)iterr   nextStopIterationr   rG   r  str)messagesproblems	pool_iterresultsinmsgr   r   bot_logbot_atr   	label_nums                r:   _pair_chat_with_pool_pythonr    sZ    	X^$$IG	AHA
a%%qkx=M))y//    ''%...BKGF1uqyyXa!e_X6,FF"1q5/--e44:!!a%,,\::Q /(BK7,B/w. 
 29BW---dYGWXHXINN%3:I',//D	-)--AH!R_!=!=!=d $CJ"T'++.>"?"?"?PT>E OM : : :4[T[*":.. !F377<+@+@!FBGG!%     	
QE a%%F Ns   A A#"A#c                 `   | sg S d |pg D             }fdfd}g }d}t          |           }||k     r| |         }|d         dv r|                    d          pd}	t          j        d	|	          }
|
r=|
                    d
          }|
                    d                                          }n!t          t          |          d
z             }|	}d}d}|d
z   |k     rV| |d
z            d         dv rC| |d
z                                d          pd}| |d
z                                d          }|d
z  }d}d}|r`t          j        d|          }|rA|                    d
                                          }|                    d          dk    }nd|v od|v}|                    |          }|r|                    d          } |                    d                    } ||                    d                     ||                    d                     ||                    d                     ||                    d                    d}|p|                    d          }|                    d          p|} |                    d                    }|d         }nd}d}d}|}|}d}|d         }|                    d|d| |||||||t          |          t          |p|                    d          pd          d d!           |d
z  }||k     |S )"uI   user 메시지의 [資格問題 X-Y]를 cert_questions(C_DB)에서 매칭c                 :    i | ]}|                     d           |S )r   rn  )rv   qs     r:   rl  z-_pair_chat_with_pool_cert.<locals>.<dictcomp>1  s'    OOO/00!OOOrp   c                 V    | rd S |                      d          d         }d d| S )Nr  r  z'/goal-skill-t/api/file/cert-img/section)r  )r2  fnamer?   s     r:   _img_urlz+_pair_chat_with_pool_cert.<locals>._img_url3  s>     	w4

3#JJJ5JJJrp   c                    | dS t          |                                           }|s| S |                                }|                    d          }|                    d          pd|v pd|v pd|v }|s|r |          }|r|n| S | S )uP   option_1~4 가 이미지 경로면 URL 로 변환, 텍스트면 그대로 반환N)z.pngz.jpgz.jpegz.gifz.webpr  \z/img_q/z/img_explanation/)r  r   r   endswithr~  )r  vr   is_imglooks_like_pathr  r  s         r:   _option_valuez0_pair_chat_with_pool_cert.<locals>._option_value9  s    =4JJ 	L		 JKK,,s++ftqyfINfNaefNf 	)_ 	)(1++C(335(rp   r   r  r  r  rv  u@   ^\[資格問題\s+([0-9\-]+)\]\s*ユーザー回答[::]?\s*(.*)$r   r   Nr  r  Fu=   正解[::]\s*([^\s\(]+)\s*\(判定[::]\s*(正解|不正解)\)r  r  r   r   r   r   r   r   )u   アu   イu   ウu   エr   r   r   r   certr  T)r  r  r  r   r   r  r   r   explanation_imgr   r   r  r  )
r   r   rB   rM   rE   r   r  rC   rG   r  )r  	questionsr?   questions_by_numr  r  r  r  r  user_logm_user	q_num_strr   r  r  r   parsed_correctm_botr  r   r   r  r   r   r  r  r  s     `                       @r:   _pair_chat_with_pool_certr  -  s    	OOio2OOOK K K K K     G	AHA
a%%qkx=M))wwu~~+HXS F  '"LLOO	$ll1oo3355Gq 011	&GF1uqyyXa!e_X6,FF"1q5/--e44:!!a%,,\::QJ!N 	X	T   X%*[[^^%9%9%;%;N"'++a..H"<JJ"*g"5!WKw<VJ $$Y//A # !o 6 6'xn(=(=>>(=z):):;;(=z):):;;(=z):):;;(=z):):;;	  "0!J1559I3J3JeeO44?"*(155+@+@"A"A4 $#!/%"&TNN# -)--!. ,""0*#2*":.. !F377<+@+@!FBGG!%     	
QS a%%T Nrp   c                    d}	 t          j        j        di t          }|                    d          }|                    d|f           |                                }t          |          }|rt          | |d          ng }t          ||          |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   C_DB.quiz_problems(問題プール) × chat_log_DB.chat_messages(category='quiz') 머지NTr&   z
            SELECT problem_id, q_num, question_text, correct_answer, explanation
            FROM quiz_problems
            WHERE item_id = %s
            ORDER BY q_num ASC, problem_id ASC
            quizz"[_get_python_quiz_history] Error: r(   )r)   r*   r+   r,   r-   r/   r@   r  r  r  r1   r2   r   r3   )r#   ry   r   r   r  r`  r  r9   s           r:   r  r    s9   F"(77;77==D=11 J	
 	
 	
 $$&&G$$HKS+JVDDDQS*8X>>
 !6<<>>>>!	    =!==>>>					!6<<>>>>!	 !6<<>>>>!s0   BB+ +
C/5C*C/C2 *C//C2 2Dc                    d}|r9t          j        d|          }|r"t          |                    d                    }d}	 g }|\t	          j        j        d	i t          }|                    d          }|	                    d|f           |
                                }t          |          }|rt          | |d          ng }	t          |	||          |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.cert_questions(問題プール) × chat_log_DB.chat_messages(category='cert_quiz') 머지Nr=   r   Tr&   aC  
                SELECT id, 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
                	cert_quizz [_get_cert_quiz_history] Error: r(   )rB   rC   rD   rE   r)   r*   r+   r,   r-   r/   r@   r  r  r  r1   r2   r   r3   )r#   ry   r  r?   rM   r   r  r   r`  r  r9   s              r:   r  r    s   G *	.)44 	*%++a..))GF"	_,;;{;;F}}}55H 

 
 
 !))++IG$$MPX+J[IIIVX(9gFF
 !6<<>>>>!	    ;;;<<<					!6<<>>>>!	 !6<<>>>>!s1   BC- -
D17D,D1D4 ,D11D4 4Ec                 <   d}	 t          j        j        di t          }|                                }t          |          \  }}d}|                    || ||f           |                                 t          j	        d|  d| d| d| d|j
         d           |j
        |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  
    指定された日次(current_day)に該当する user_curriculum 行の status を
    本日の日付('YYYY-MM-DD')に更新する。デバッグ日誌書込み完了時に呼ぶ。
    既に日付が入っている場合は上書きしない(初回完了日を保持)。
    Nar  
            UPDATE user_curriculum uc
            JOIN master_items mi ON uc.item_id = mi.item_id
            SET uc.status = CAST(CURDATE() AS CHAR)
            WHERE uc.session_id = %s
              AND mi.block_id = %s
              AND mi.sort_order = %s
              AND (uc.status IS NULL OR uc.status = '' OR uc.status IN ('NOT_STARTED','IN_PROGRESS'))
        z[mark_curriculum_completed] r   z (block=rc   rd   u    行を完了マークz#[mark_curriculum_completed] Error: r   r(   )r)   r*   r+   r,   r-   r.   r/   re   r   rf   rg   r1   r2   r3   r4   s           r:   mark_curriculum_completedr    s}    D&55555kBB* 	sZ:>???]: ] ]K ] ]] ]'1] ]5;_] ] ]	
 	
 	
 
 	    >1>>???qqq	 s*   BB; ;
C?C:D :C??D Dc           
         d}	 t          j        j        di t          }|                    d          }d}|                    |           |                                }g }|D ]}|d         r|d         |d         |                    |d         |d         dk    p't          |d                   	                    d	          t          |d                   d
z
  t          |d                   d
z
  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   
    유저의 커리큘럼 계획표(간트 차트용) 데이터를 가져옵니다.
    최우선: 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_blockrs  rx  r   )r   r  start_month	end_monthz[get_curriculum_plan] Error: r(   )r)   r*   r+   r,   r-   r/   r@   rG   r  r~  rD   r1   r2   r   r3   )r#   r5   r-   
sql_masterrI   
categoriesrM  r9   s           r:   get_curriculum_planr    s    D&5555--
 	z"""  
 	 	AZ= AkN$:an>TjMZ=F2\c!J-6H6H6S6ST[6\6\"1[>22Q6 ;0014	      
 	    8Q88999						 s0   C;D 
E!E9E:E EE E7)NN)r   )N)=mysql.connectorr)   r  r   r   r   r   r   r   r1  r0  openpyxl.formatting.ruler   rB   r,   Goalskill_login_DB_CONFIGr[   rU   A_DB_CONFIGTHEORY_SUB_RANGESr  rD   r;   rO   r   rW   r\   r`   rk   r.   r}   r   r   r   r   r  r   r   r   r   r   r   r   r   r   r   rP  rd  rq  r  r  r  r  r  r  r  r  r  r  r  r  r(   rp   r:   <module>r     s4       D D D D D D D D D D       ( ( ( ( ( ( ( (      				 0 0 0 0 0 0 				 mF##)M*;<< #mN33  =-- mF## 	  $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 + + + +\# C    "s    "Qc Q Q Q QpR" R"3 R" R" R" R"j-"c -"d -" -" -" -"^SC S S S S Sl<3 < < < < <~9s 9S 9S 9Y\ 9 9 9 96S 6c 6c 6 6 6 63    *  c    4+ + +\h h hV" "s " " " "6""s ""S ""S "" "" "" ""J!# !C ! ! ! !H&C & & & & & &rp   