o jq|i @sxddlZddlZddlZddlmZddlmZddlmZm Z m Z m Z m Z m Z mZddlmZddlmZddlmZddlmZddlmZdd lmZdd lmZmZddlZdd lmZm Z m Z!m"Z"m#Z#m$Z$m%Z%m&Z&m'Z'dd l(m)Z)ddl*Z*ddl+Z+ed dZ,ej-.ej-/ej-0e1dZ2ej34dZ5dZ6ddZ7e7Z8ddl9Z9ddl:Z:dddZ;dede efdefddZ?e,j@de*j>de efd e*jAdefd!d"ZBe,Cd#e efd$eZKe,4d?e efdefd@dAZLe,4dBdCe efd;ez t|ddiWSty}ztdt|dd}~ww)z8 Manually triggers ChromaDB rebuild from MySQL. rezAI Synchronization CompleterbN)dictionary_service rebuild_indexr0rr4rOr<r"r"r#sync_dictionary_to_ais  rmcCsNddl}|t}dd|D}||}tjtd}|j |dddS)NrcSsg|] }|j|j|jdqS))Term DefinitionVersesrU.0ir"r"r# s  z$save_db_to_excel..zdictionary.xlsxFindex) pandasr@rrQ DataFrameospathjoinUPLOAD_DIRECTORYto_excelrOpditemsdatadf excel_pathr"r"r#r^s r^z/dictionary/upload.filec sp|jds tdddddl}tjtd}t|d}t |j |Wdn1s/wYzl| |}dd |j D|_ d |j vsMd |j vrStdd d|t|D].\}}tt|d t|d ||d rt|d dndd}||q^|t|ddt|diWSty} z tddt| dd} ~ ww)N.xlsxOnly .xlsx files are allowedrbrzdictionary_temp.xlsxwbcSg|]}|qSr"lowerrrcr"r"r#rtz%upload_dictionary..rVrWz/Excel must have 'Term' and 'Definition' columnsrXrUre Imported z items successfullyrizError processing file: )filenameendswithrrwryrzr{r|openshutil copyfileobjr read_excelcolumnsr@rrgiterrowsr4rFnotnagetr[r\rjrklenr0) rrOr temp_pathbufferr_rowrTr<r"r"r#upload_dictionarys8     &  rcontentreturnc CsdtsdSztdd|d}|jddWSty1}ztd|WYd}~dSd}~ww) z*Uses Gemini to extract keywords from text.rrAuExtract 5 important keywords (strictly Nouns/名詞 ONLY) from this text in Japanese (日本語), separated by commas. Output ONLY the keywords. Do not include verbs or adjectives. Text: rBrC,zKeyword Extraction Error: N)r/r?rEreplacerFr0r )rresponser<r"r"r#extract_keywordssrz/create_folderrequest current_userc s|j}|rd|vsd|vrtddd|ttj|kr)tdddz!tj tj t |dd| t|d |d d |d iWSty^}ztd t|dd}~ww)Nz..r-rzInvalid folder namerbzFolder already existsTexist_oknamerezFolder 'z ' createdri) folder_namerFrr@rrYrrZrymakedirsrzr{r|r[r\r0r4)rrOrrr<r"r"r# create_folders   rz/uploadfolderfilesc s|ttj|k}|s |dkrtntjt|}tj |ddg}|D]8}tj||j }t |d} t |j| Wdn1sJwY||j t|j |d} || q)|d|dS)N UncategorizedTrr)rrzFiles uploaded)re filenames)r@rrYrrZr|ryrzr{rrrrrrappend FileModelr[r\) rrrOr db_folder target_diruploaded_filesr file_locationrnew_filer"r"r# upload_filess"    rz/folderscs<|t}dd|D}d|vr|ddt|iS)NcSsg|]}|jqSr"r)rrfr"r"r#rt#sz list_folders..rfolders)r@rrQrsorted)rOrresr"r"r# list_folders s  rz/videosrcsr|ttj|k}g}|D]}|jdr+||j|j d|jdq|j ddddd|iS) N) z.mp4z.movz.aviz.mkvz.webmz.jpgz.jpegz.pngz.gifz.webpz%Y-%m-%d)rdatercSs|dS)Nrr")xr"r"r#2szlist_videos..T)keyreversevideos) r@rrYrrQrrrr uploaded_atstrftimesort)rrOr video_listrr"r"r# list_videos's rz /delete_file/{folder}/{filename}rcs|ttj|ktj|k}|dkrtntj t|}tj ||}tj |r1t ||r<| || ddiS)Nrrerf)r@rrYrrrZr|ryrzr{existsremovergr\)rrrOrdb_filer file_pathr"r"r# delete_file5s"   rz/delete_folder/{folder_name}rcs|dkr tddd|ttj|k}tjt |}tj |r*t ||t t j|k|r>|||ddiS)NrrzCannot delete default categoryrbrerf)rr@rrYrrZryrzr{r|rrrmtreerrrgr\)rrOrr folder_pathr"r"r# delete_folderAs    rz/download/{folder}/{filename}csLt}|dkrtjt|}tj||}tj|r"t|d|dSt|S)Nrzapplication/octet-stream media_typer)r|ryrzr{rr )rrrrr"r"r# download_fileOs rcCsPddl}|tj}dd|D}||}tjt d}|j |dddS)NrcSs"g|] }|j|j|j|jdqS))QuestionAnswerKeywordsDate)questionanswerkeywords created_atrqr"r"r#rt`s z*save_bible_qa_to_excel.. bible_qa.xlsxFru) rwr@r2rrQrxryrzr{r|r}r~r"r"r#save_bible_qa_to_excel]s rz/bible_qa/syncc Cs>z t|ddiWSty}ztdt|dd}~ww)NrezBibleQA Sync Completerirb)rjrebuild_qa_indexr0rr4rlr"r"r# sync_bible_qaks  rz/bible_qa/uploadc s|jds tdddddl}tjtd}t|d}t |j |Wdn1s/wYz| |}dd |j D|_ d |j vsMd |j vrStdd d|tj|D]B\}}t|d }||d r}t|d dnd} | strzt|} WnYtj|t|d | d} || q_|t|ddt|diWSty} ztdt| dd} ~ ww)Nrrrrbrzbible_qa_temp.xlsxrcSrr"rrr"r"r#rtrz#upload_bible_qa..rrz/Excel must have 'Question' and 'Answer' columnsrrrrrrerz itemsri)rrrrwryrzr{r|rrrrrrr@r2rrgrr4rFrrr/rr[r\rjrrr0) rrOrrrrrrrrrTr<r"r"r#upload_bible_qassD     (   rz/bible_qa/exportcCs<t|tjtd}tj|rt|dddStddd)NrzAapplication/vnd.openxmlformats-officedocument.spreadsheetml.sheetrraz Export failedrb)rryrzr{r|rr r)rOrr"r"r#export_bible_qas   rz/bible_qa/check_similarityc Csze|j}tj|dd}g}|r2|D] }||d|d|d|ddd|dd dd q|rad d |D}|tjtjj | }d d|D}|D] }||dd|d<qTd|iWSt y} zt d| tdt| dd} ~ ww)z6 Check vector similarity for a given message. kidscorecoveragemetadatarUnknownr)rrrrrcSsg|]}t|dqS)r)int)rrmr"r"r#rtz$check_similarity..cSsi|] }t|j|jqSr")r4rrrrrTr"r"r# sz$check_similarity..zQuestion not found in DBmatcheszSimilarity Check Error: rirbN)rerjsearch_bible_qa_hybridrrr@r2rrYrin_rQr0r rr4) rrO search_queryvector_matchesrridsdb_itemsid_mapr<r"r"r#check_similaritys2   rz /bible_qacCs|tjtjjSrP)r@r2rorder_byrdescrQrRr"r"r# get_bible_qasrz/bible_qa/{id}rcCsP|tjtjj|k}|r$|||t |t |ddiS)Nrerf) r@r2rrYrrZrgr\rjdelete_qa_item init_bm25)rrOrTr"r"r#delete_bible_qas   rz /daily_breadc Cst|d}ztrdt|j}|st|j}Wnd}Ytj|j |j |j |j |j |j||jd}||||||S)Nrr)bookchapterverse end_chapter end_verserr user_username)ensure_daily_bread_user_columnr/r{rjtokenize_japaneserrr2rrrrrrusernamer[r\r])rTrOrrr_r"r"r#create_daily_breads0   r cCs6t||tjtjj|jktjj  SrP) rr@r2rrYrr rrrrQ)rOrr"r"r#get_daily_bread s  r z/daily_bread/{id}cCs|tjtjj|ktjj|jk}|stddd|j |_ |j |_ |j |_ |j |_ |j |_ |j|_dt|j|_||||S)Nra#Item not found or permission deniedrbr)r@r2rrYrrr rZrrrrrrrr{rjr rr\r])rrTrOrr_r"r"r#update_daily_breads$     rcCsT|tjtjj|ktjj|jk}|stddd| || ddiS)Nrar rbrerf) r@r2rrYrrr rZrrgr\)rrOrr_r"r"r#delete_daily_bread+s     rz/chat/sessionscCs"|ttj}d|iS)Nsessions)r@rrrrrQ)rOrrr"r"r# get_sessions=srz /chat/searchqc CsR|tttjtjkttj d|dtj d|d }|S)N%) r@r outerjoinrr session_idrYr titleilikerdistinctrQ)rrOrrr"r"r#search_sessionsCsrcCs0t|j|jd}||||||S)N)rr)rr rr[r\r])rrOr new_sessionr"r"r#create_sessionNs   rz/chat/sessions/{session_id}rcCs\|ttj|k}|stddd|ttj|ktj  }|j|j |dS)NraSession not foundrb)rrmessages) r@rrYrrZrrrr timestamprQr)rrOrsessionrr"r"r#get_session_messagesVs  "r z#/chat/sessions/{session_id}/messagec/ s|ttj|k}|stdddt|djd}||| |ttj |ktj|jk tj  }g}|D]}|jdkrIdnd} |tj| tj|jdgd q@ztsdtd tj} td | | rw| d nd} | r| d nd} | r| dgng} ztdjj}td|dtj|dd}d}|r|d}|d}td|dd|dd|dkr|}ntd|r |dd}|dd }|d!kr|d"7}|ddd#|}t|d|d}||| ||d||jd$WWSWn3ty'}z td%|WYd}~nd}~wty@}z td%|WYd}~nd}~wwd&}z|tj }g}|D]'}|jjvra||qQ| D]}|ru||jvru||nqcqQ|rd'd(|D}|d)d* |d+7}|tj! }g}|D][}d,} |j"#d-d&#d.d&#d/d&}!| r| r| |!kr|j$| krd0} n | r| s| |!krd0} | s|j%rt&fd1d2d3d(|j%'d4DDrd0} | r||qtd5t(|d6|rPg}"|D]=}#|#j$d7|#j)}$|#j*r'|#j+r'|$d8|#j*d7|#j+7}$n |#j+r3|$d8|#j+7}$|"d9|#j"d:|$d;|#jq|dd2|'Dr~d0} | s| r| D]sqt&fd?d2|'Drd0} nq| r|&|q[td5t(|&d@|&rg}(|&D]5})dA}*t(|)j|*kr|)jd|*#d*d:dBn|)j#d*d:}+|(d9|)j-d;|+tdC|)j-q|dDd* |(d+7}Wnty}z tdE|WYd}~nd}~wwj|},|tjdtj|,dgd g}-t.dF|-tj/t0dGdH}.|.j1}Wn!ty[}zdIt2|}tdJ|WYd}~nd}~wwt|d|d}||| ||d||jd$S)KNrarrbuser)rsenderrbotr&rErolepartszGemini API not configuredzDEBUG: Analyzed Intent: rrrz,DEBUG: Vector Search Logic Start for query: zDEBUG: Hybrid Search Query: ''r+rrrzDEBUG: [Hybrid] Closest ID: rz , Score: z.5fz (Threshold: > 0.01)g{Gz?z#DEBUG: [Hybrid] No matches returnedrrrg?g?uz (※ 질문하신 내용 중 일부 키워드(예: 바울)에 대한 정보는 포함되지 않았을 수 있습니다.)ul ⚠️ **주의: 질문하신 키워드 중 일부가 검색 결과에 없습니다.** (일치율: {:.0%}))r"r message_idzBibleQA Check Error: rcSs&g|]}|jd|jd|jqS)z | rUrr"r"r#rts&z send_message..uJ [System Note] 以下の[bible塾辞書データ]を積極的に活用して回答してください。 **回答は必ず以下のフォーマットに従って作成してください：** 【説明】 (単語の意味や質問への回答を記述) 【背景】 (聖書的・歴史的背景や文脈を記述) 【関連聖句】 (関連する聖書の言葉を引用。章と節を明記すること) 【参考データ】 (回答に使用した[bible塾辞書データ]の内容をそのまま表示) -------------------------------------------------- [bible塾辞書データ] rCz4 -------------------------------------------------- Fuによる福音書u の福音書u 福音書Tc3|]}|jvVqdSrPrerrrrr"r# zsend_message..cSg|]}|r|qSr"rFr,r"r"r#rtrrz DEBUG: Found z relevant Breads:~[ z] u [System Note - Additional Context] 以下の[日ごとの糧]（ユーザーの過去の黙想記録）も参考にして、共感的に回答してください。 回答の最後には、以下のフォーマットで参照した内容を記載してください。 【参考データ（日ごとの糧）】 (回答に使用した[日ごとの糧]の内容を要約して表示。使用していない場合は「なし」と記載) -------------------------------------------------- [日ごとの糧] cSr0r"r1r,r"r"r#rt!rc3r*rPr+r,r-r"r#r.%r/c3s |] }|vp |vVqdSrPr"r,)skr"r#r..sz relevant Sunday Messagesi@z...z%DEBUG: Using Sunday Message context: uh [System Note - Sunday Message Context] 以下の[主日メッセージ]（過去の説教等の記録）も参考にして、回答を補強してください。 メッセージの内容と関連する質問であれば、積極的に引用してください。 回答の最後には、以下のフォーマットで参照した内容を記載してください。 【参考データ（主日メッセージ）】 (回答に使用した[主日メッセージ]のタイトルを表示。使用していない場合は「なし」と記載) -------------------------------------------------- [主日メッセージ] z RAG Error: rA)system_instruction)r9r'r(zError: zGemini Error: )3r@rrYrrZrrrer[r\rrrrQr"rrContentPartrr/r0rMr rrjrformatr]r2rrVr{rrrrranyrHrrrrrrr?GenerateContentConfigSYSTEM_INSTRUCTIONrEr4)/rrrOrruser_msg history_msgsgemini_historymsgr&intent search_booksearch_chaptersearch_keywordsrhybrid_matches match_foundmatchr bot_contentrbot_msgr< context_textall_dict_itemsrelevant_dict_itemsrTr dict_lines daily_breadsrelevant_breadsr_ is_relevant db_book_norm bread_linesb range_strsunday_messagesrelevant_messages msg_keywords msg_linesrlimitsummary final_messager'rr")rr6r# send_message_sZ     "            * $   8     r]z /chat/messages/{message_id}/liker)c Cs2|ttj|k}|r|jdkrtddd|ttj|jktj|ktjdktj }|s>tdddzF|t j t j j |j k}|rVddiWSt|j }t j |j |j |d }||d |_|||t|t|dd iWSty}ztd t|dd}~ww) Nr#rzInvalid messagerbr!zUser question not foundrez Already savedrr+zSaved to BibleQAri)r@rrYrrZr"rrrrr2rrrrr[is_likedr\r]rj sync_qa_itemrr0r4)r)rOrJr>rrnew_qar<r"r"r# like_messageisB            racCsh|ttj|ktj|jk}|stddd|ttj |k | || ddiS)Nrarrbrerf) r@rrYrrr rZrrrrgr\)rrOrrr"r"r#delete_sessions$  rbrc CstsdSz>t|d }|}Wdn1swYd}tdtjdtjj|ddtjj|d gd gd }|j r@|j WSdWSt y\}zt d |WYd}~dSd}~ww) z? Uses Gemini AI to extract text from a PDF file (OCR). rrbNuこのPDFファイルに含まれるテキストを全て抽出して出力してください。要約ではなく、全文をそのままテキスト化してください。gemini-2.5-flash-liter!application/pdf)r mime_typer$r%rBzGemini OCR Error: ) r/rreadr?rr8r9 from_bytes from_textrEr0r )rr file_contentrKrr<r"r"r#extract_text_with_geminis.    rkrEc Csztr|sdSzd|ddd}td|d}|jr |jWSdWSty<}ztd|WYd}~dSd}~ww) zD Extracts 10 important keywords from the text using Gemini. rz Extract the 10 most important keywords or topics from the following text. Return ONLY a comma-separated string of keywords. Example: Keyword1, Keyword2, Keyword3 Text: NiPz rdrBzKeyword Extraction Failed: )r/r?rErFr0r )rErKrr<r"r"r#extract_keywords_with_geminis   rlc Cs~z$|td}|s"td|td|tdWdSWdSty>}ztd|WYd}~dSd}~ww)Nz1SHOW COLUMNS FROM sunday_messages LIKE 'keywords'z4Adding 'keywords' column to sunday_messages table...z4ALTER TABLE sunday_messages ADD COLUMN keywords TEXTz%Column 'keywords' added successfully.zMigration Check Failed: executerEfetchoner r\r0rOresultr<r"r"r#ensure_keywords_column_existssrrc Csz+|td}|s)td|td|td|tdWdSWdStyE}ztd|WYd}~dSd}~ww)Nz2SHOW COLUMNS FROM daily_bread LIKE 'user_username'z5Adding 'user_username' column to daily_bread table...z=ALTER TABLE daily_bread ADD COLUMN user_username VARCHAR(100)zHCREATE INDEX ix_daily_bread_user_username ON daily_bread (user_username)z*Column 'user_username' added successfully.z#DailyBread Migration Check Failed: rmrpr"r"r#rsrz/sunday_messages/uploadc s|jdstdddt|ttj}|d|j}t j t d}t j |ddt j ||}t|d }t|j|Wdn1sPwYd }z(t|}td t|t|d kr~td t|}tdt|Wn=ty}z1td|z tdt|}Wnty} z td| WYd} ~ nd} ~ wwWYd}~nd}~wwd } |rtdt|} td| t|jdd ||| d} || ||| | S)Nz.pdfrzOnly PDF files are allowedrbrrVTrrrzDEBUG: Extracted Text Length: 2z+DEBUG: Text too short, trying Gemini OCR...z%DEBUG: Gemini Extracted Text Length: zPDF Extraction Failed: z,DEBUG: PDFMiner failed, trying Gemini OCR...zGemini Extraction Failed: zDEBUG: Extracting keywords...zDEBUG: Keywords: )rrrr)rrrrrrrdatetimeutcnowrryrzr{r|rrrrrrr rrFrkr0rlrrr[r\r]) rrOrunique_filenamerrrrr<gemini_er new_messager"r"r#upload_sunday_messages^       ryz/sunday_messagescCs|ttjSrP)r@rrrrrQrRr"r"r#get_sunday_messages7srzz/sunday_messages/download/{id}cCsp|ttj|k}|stdddtjt d}tj||j }tj |r2t |d|j dStddd)NraMessage not foundrbrVrerzFile not found on server) r@rrYrrZrryrzr{r|rrr rrOrArrr"r"r#download_sunday_message;s   r}z/sunday_messages/{id}cCsx|ttj|k}|stdddtjt d}tj||j }tj |r/t || ||ddiS)Nrar{rbrVrerf)r@rrYrrZrryrzr{r|rrrrgr\r|r"r"r#delete_sunday_messageHs    r~)Nr%)vryrrtpdfminer.high_levelrtypingrfastapirrrrrr r fastapi.responsesr sqlalchemy.ormr sqlalchemyr google.generativeai generativeairgoogle google.genairdatabaserrr2rrrrrrrrrauthrschemasrjrouterrzr{dirnameabspath__file__r|environrrr=r$r/r7r5r?r4rMDictionaryItemResponserSpostDictionaryItemCreater`rgrhrmr^rr FolderRequestrrrrrrrrrrrSimilarityCheckResponseSimilarityCheckRequestrBibleQAResponserrrDailyBreadResponseDailyBreadCreater r putrrrChatSessionModelrCreateSessionRequestrr MessageRequestr]rarbrkrlrrrSundayMessageResponseryrzr}r~r"r"r"r#s  $      ,     $   "-(: * &  ". *(" ,&"& (&, 0&    ";