
    -i&                        d dl mZ 	 d dlZd dlZd dlZd dlmZmZ d dlmZ d dl	m
Z
  ej                  e      ZdZdZdZd	Zd
ZdZdZ G d d      Zy)    )annotationsN)datetimetimezone)Path)Anyz(/home/jay/projects/insuwiki/data/wiki.dbaz  
CREATE TABLE IF NOT EXISTS wiki_entries (
    id TEXT PRIMARY KEY,
    title TEXT NOT NULL,
    category TEXT NOT NULL,
    subcategory TEXT,
    question TEXT,
    answer TEXT,
    expert TEXT,
    source_date TEXT,
    source_chat TEXT,
    keywords TEXT,
    confidence TEXT,
    raw_thread TEXT,
    status TEXT DEFAULT 'draft',
    created_at TEXT,
    updated_at TEXT
);
z
CREATE VIRTUAL TABLE IF NOT EXISTS wiki_fts USING fts5(
    title, question, answer, keywords,
    content=wiki_entries, content_rowid=rowid
);
z
CREATE TRIGGER IF NOT EXISTS wiki_fts_ai AFTER INSERT ON wiki_entries BEGIN
    INSERT INTO wiki_fts(rowid, title, question, answer, keywords)
    VALUES (new.rowid, new.title, new.question, new.answer, new.keywords);
END;
z
CREATE TRIGGER IF NOT EXISTS wiki_fts_ad AFTER DELETE ON wiki_entries BEGIN
    INSERT INTO wiki_fts(wiki_fts, rowid, title, question, answer, keywords)
    VALUES ('delete', old.rowid, old.title, old.question, old.answer, old.keywords);
END;
a  
CREATE TRIGGER IF NOT EXISTS wiki_fts_au AFTER UPDATE ON wiki_entries BEGIN
    INSERT INTO wiki_fts(wiki_fts, rowid, title, question, answer, keywords)
    VALUES ('delete', old.rowid, old.title, old.question, old.answer, old.keywords);
    INSERT INTO wiki_fts(rowid, title, question, answer, keywords)
    VALUES (new.rowid, new.title, new.question, new.answer, new.keywords);
END;
)idtitlecategorysubcategoryquestionanswerexpertsource_datesource_chatkeywords
confidence
raw_threadstatus
created_at
updated_atc                      e Zd ZdZdddZddZddZddZddZddZ	dd	Z
dd
ZddZ	 	 	 	 d	 	 	 	 	 	 	 	 	 ddZdd dZddZddZd!dZd"dZddZy)#	WikiStoreu+   SQLite 기반 위키 항목 CRUD 저장소.Nc                   ||nt         }|dk7  r&t        |      j                  j                  dd       t	        j
                  |d      | _        t        j                  | j                  _        | j                          y )Nz:memory:T)parentsexist_okF)check_same_thread)
_DEFAULT_DB_PATHr   parentmkdirsqlite3connect_connRowrow_factoryinit_db)selfdb_pathresolveds      A/home/jay/projects/insuwiki/scripts/kakao_knowledge/wiki_store.py__init__zWikiStore.__init__h   sd    %177Gz!N!!''t'D)0*

 ")

    c                    | j                   j                         }|j                  t        t        z   t
        z   t        z   t        z          | j                   j                          y)u;   테이블, FTS 가상 테이블, 트리거를 생성한다.N)	r"   cursorexecutescript_CREATE_TABLE_CREATE_FTS_CREATE_TRIGGER_AI_CREATE_TRIGGER_AD_CREATE_TRIGGER_AUcommit)r&   curs     r)   r%   zWikiStore.init_dbv   s[    jj! ! !! !	!	
 	

r+   c                d    t        j                  t        j                        j	                         S N)r   nowr   utc	isoformatr&   s    r)   _now_isozWikiStore._now_iso   s    ||HLL)3355r+   c                    t        |      }dD ]E  }|j                  |      }t        |t              r	 t	        j
                  |      ||<   >|Ag ||<   G |S # t        j                  t        f$ r g ||<   Y jw xY w)u9   sqlite3.Row → dict, keywords/raw_thread는 JSON 파싱.r   r   )dictget
isinstancestrjsonloadsJSONDecodeError
ValueError)r&   rowdfieldvals        r)   _row_to_dictzWikiStore._row_to_dict   s    I/ 	E%%,C#s#"#zz#AeH %	 	 ,,j9 "!AeH"s   AA98A9c                    t        |      }dD ]>  }|j                  |      }t        |t              s%t	        j
                  |d      ||<   @ |S )uH   keywords/raw_thread가 list면 json.dumps로 변환한 복사본 반환.r>   F)ensure_ascii)r?   r@   rA   listrC   dumps)r&   entrypreparedrI   rJ   s        r)   _prepare_entryzWikiStore._prepare_entry   sO    ;/ 	FE,,u%C#t$"&**Su"E	F r+   c                    | j                  |      }| j                         }|j                  d|       |j                  d|       |j                  dd       t        D cg c]	  }||v s| }}dj	                  d |D              }|D cg c]  }||   	 }}ddj	                  |       d| d	}| j
                  j                         }	|	j                  ||       | j
                  j                          t        |d
         S c c}w c c}w )u*   항목을 삽입하고 id를 반환한다.r   r   r   draft, c              3      K   | ]  }d   yw)?N ).0_s     r)   	<genexpr>z)WikiStore.insert_entry.<locals>.<genexpr>   s      3 3s   zINSERT INTO wiki_entries (z
) VALUES ()r   )
rR   r<   
setdefault_COLUMNSjoinr"   r-   executer4   rB   )
r&   rP   rQ   r8   ccolsplaceholdersvaluessqlr5   s
             r)   insert_entryzWikiStore.insert_entry   s    &&u-mmoL#.L#.Hg.#5aqH}55yy 3d 33'+,!(1+,,*499T?*;:l^STUjj!C 

8D>"" 6,s    	D*DDc                    | j                   j                         }|j                  d|f       |j                         }|r| j	                  |      S dS )u   단건 조회. 없으면 None.z'SELECT * FROM wiki_entries WHERE id = ?N)r"   r-   r`   fetchonerK   )r&   r   r5   rG   s       r)   	get_entryzWikiStore.get_entry   sH    jj!=uElln),t  %6$6r+   c                   |sy| j                  |      }| j                         |d<   dD ]  }|j                  |d        dj                  d |D              }t	        |j                               |gz   }d| d}| j                  j                         }|j                  ||       | j                  j                          |j                  d	kD  S )
u*   부분 업데이트. 성공 여부 반환.Fr   )r   r   NrU   c              3  &   K   | ]	  }| d   yw)z = ?NrX   )rY   cols     r)   r[   z)WikiStore.update_entry.<locals>.<genexpr>   s     @#d|@s   zUPDATE wiki_entries SET z WHERE id = ?r   )rR   r<   popr_   rN   rd   r"   r-   r`   r4   rowcount)	r&   r   updatesrQ   	protected
set_clauserd   re   r5   s	            r)   update_entryzWikiStore.update_entry   s    &&w/!%- 	*ILLD)	* YY@x@@
hoo'(B4/(MBjj!C 

||ar+   c                    | j                   j                         }|j                  d|f       | j                   j                          |j                  dkD  S )u$   항목 삭제. 성공 여부 반환.z%DELETE FROM wiki_entries WHERE id = ?r   )r"   r-   r`   r4   rn   )r&   r   r5   s      r)   delete_entryzWikiStore.delete_entry   sD    jj!;bUC

||ar+   c                   g }g }|"|j                  d       |j                  |       |"|j                  d       |j                  |       |rddj                  |       nd}d| d}|j                  ||g       | j                  j	                         }	|	j                  ||       |	j                         D 
cg c]  }
| j                  |
       c}
S c c}
w )u   조건 필터링 목록 조회.zcategory = ?z
status = ?zWHERE z AND  zSELECT * FROM wiki_entries z* ORDER BY created_at DESC LIMIT ? OFFSET ?)appendr_   extendr"   r-   r`   fetchallrK   )r&   r
   r   limitoffset
conditionsparamswherere   r5   rG   s              r)   list_entrieszWikiStore.list_entries   s     !#
n-MM(#l+MM&!7A&j123r)% 18 9 	 	ufo&jj!C 25,,.A3!!#&AAAs   <Cc                    d}| j                   j                         }|j                  |||f       |j                         D cg c]  }| j	                  |       c}S c c}w )u   FTS5 전문 검색.z
            SELECT e.*
            FROM wiki_entries e
            JOIN wiki_fts f ON e.rowid = f.rowid
            WHERE wiki_fts MATCH ?
            ORDER BY rank
            LIMIT ?
        )r"   r-   r`   ry   rK   )r&   queryrz   re   r5   rG   s         r)   search_entrieszWikiStore.search_entries   sT     jj!C%(25,,.A3!!#&AAAs   Ac                *    | j                  |ddi      S )u   status를 'approved'로 변경.r   approvedrr   r&   r   s     r)   approve_entryzWikiStore.approve_entry         h
%;<<r+   c                *    | j                  |ddi      S )u   status를 'rejected'로 변경.r   rejectedr   r   s     r)   reject_entryzWikiStore.reject_entry  r   r+   c                    d}|D ]  }	 | j                  |       |dz  } |S # t        $ r }t        j                  d|       Y d}~Bd}~ww xY w)u$   일괄 import. 성공 건수 반환.r      u(   bulk_import: 항목 삽입 실패 — %sN)rf   	Exceptionloggerwarning)r&   entriessuccessrP   excs        r)   bulk_importzWikiStore.bulk_import  sb     	PEP!!%(1	P   PI3OOPs   #	AAAc                ~   | j                   j                         }|j                  d       |j                         }|r|d   nd}|j                  d       |j	                         D ci c]  }|d   |d    }}|j                  d       |j	                         D ci c]  }|d   |d    }}|||dS c c}w c c}w )	u#   카테고리별 / 상태별 통계.z!SELECT COUNT(*) FROM wiki_entriesr   zDSELECT category, COUNT(*) as cnt FROM wiki_entries GROUP BY categoryr
   cntz@SELECT status, COUNT(*) as cnt FROM wiki_entries GROUP BY statusr   )totalby_category	by_status)r"   r-   r`   rh   ry   )r&   r5   	total_rowr   rG   r   r   s          r)   	get_statszWikiStore.get_stats  s    jj!78LLN	%.Yq\AR	
 47<<>'
,/C
OSZ''
 '
 	VW14%
*-CM3u:%%
	 %

 &"
 	
'

%
s   (B5B:c                8    | j                   j                          y)u   DB 연결 종료.N)r"   closer;   s    r)   r   zWikiStore.close+  s    

r+   r7   )r'   
str | NonereturnNone)r   r   )r   rB   )rG   zsqlite3.Rowr   dict[str, Any])rP   r   r   r   )rP   r   r   rB   )r   rB   r   zdict[str, Any] | None)r   rB   ro   r   r   bool)r   rB   r   r   )NN2   r   )
r
   r   r   r   rz   intr{   r   r   list[dict[str, Any]])   )r   rB   rz   r   r   r   )r   r   r   r   )r   r   )__name__
__module____qualname____doc__r*   r%   r<   rK   rR   rf   ri   rr   rt   r   r   r   r   r   r   r   rX   r+   r)   r   r   e   s    5
 6#"7 $   $!BB B 	B
 B 
B6B==	
4r+   r   )
__future__r   rC   loggingr    r   r   pathlibr   typingr   	getLoggerr   r   r   r/   r0   r1   r2   r3   r^   r   rX   r+   r)   <module>r      sr    "    '  			8	$= (   0H Hr+   