+
    T@j                         R t ^ RIt^ RIt^ RIt^ RIHt R tR t]! ]	4      P                  t]R,          R,          t]R,          tR tR	 tR
 R ltR tR R ltR R ltR R ltR R ltRR R lltR tR R ltR# )z1SQLite helpers for the Lunch Money finance store.N)Pathc                     \        V \        P                  \        P                  34      '       d   V P	                  4       # \        R \        V 4       24      h)znot JSON serializable: )
isinstancedtdatedatetime	isoformat	TypeErrortypeos   &*/data/cameron/life/projects/finances/db.py_json_defaultr      s?    !bggr{{+,,{{}
-d1gY7
88    c                 :    \         P                  ! V \        R 7      # ))default)jsondumpsr   r   s   &r   _dumpsr      s    ::a//r   dataztransactions.dbz
schema.sqlc                      \         P                  P                  R R R7       \        P                  ! \         4      p \        P
                  V n        V # )T)parentsexist_ok)DB_PATHparentmkdirsqlite3connectRowrow_factoryconns    r   r   r      s8    NN5??7#D{{DKr   c                      \        4       p V P                  \        P                  4       4       V P	                  4        V P                  4        R # )N)r   executescriptSCHEMA_PATH	read_textcommitcloser    s    r   init_dbr(      s2    9D{,,./KKMJJLr   c                $    V ^8  d   QhR\         /# )   cdict)formats   "r   __annotate__r/   %   s      t r   c                 z   \        4       pTP                  R V R,          V R,          V P                  R4      V P                  R4      '       d   ^M^ V P                  R4      '       d   ^M^ V P                  R4      V P                  R4      '       d   ^M^ 34       VP                  4        VP	                  4        R# )	zINSERT OR REPLACE INTO categories
        (id, name, description, is_income, is_group, group_id, excluded_from_budget)
        VALUES (?, ?, ?, ?, ?, ?, ?)idnamedescription	is_incomeis_groupgroup_idexclude_from_budgetN)r   executegetr&   r'   )r+   r!   s   & r   upsert_categoryr:   %   s    9DLL	( dGQvYm 4{##Az""AEE*,--A1	
	 	KKMJJLr   c                 v    Vf   R # V P                  RV34      P                  4       pV'       d
   VR,          # R # )Nz(SELECT name FROM categories WHERE id = ?r2   )r8   fetchone)r!   category_idrows   && r   _category_namer?   7   s8    
,,AK>
R
[
[
]C3v;'4'r   c                $    V ^8  d   QhR\         /# )r*   tr,   )r.   s   "r   r/   r/   >   s     % %$ %r   c                    \        4       pV P                  R 4      pV P                  R4      pVe   \        V4      RrTMVe   \        V4      RrTMRRrT\        WP                  R4      4      pTP	                  R\        V R,          4      TTV R,          V P                  R	4      ;'       g    R
\        V R,          4      V P                  RR4      V P                  R4      TV P                  R4      V P                  R4      V P                  R4      '       d   ^M^ V P                  R4      \        V 4      34       VP                  4        VP                  4        R# )asset_idplaid_account_idNassetplaidr=   a%  INSERT OR REPLACE INTO transactions
        (id, account_id, account_source, date, payee, amount, currency,
         category_id, category_name, notes, status, is_pending, recurring_id,
         raw_json, updated_at)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)r1   r   payee	(unnamed)amountcurrencyusdnotesstatus
is_pendingrecurring_id)	r   r9   strr?   r8   floatr   r&   r'   )rA   r!   rC   plaid_id
account_idsourcecategory_names   &      r   upsert_transactionrV   >   s   9DuuZ Huu'(H ]GF		 ]GF!4F"4})=>MLL	P $LfIEE'N))k!H+EE*e$EE- EE'NEE(O|$$A!EE.!1I	
. 	KKMJJLr   c                $    V ^8  d   QhR\         /# )r*   ar,   )r.   s   "r   r/   r/   f   s      D r   c                    \        4       pTP                  R \        V R,          4      V P                  R4      ;'       g    V P                  R4      V P                  R4      V P                  R4      RV P                  R4      e   \	        V R,          4      MRV P                  RR	4      V P                  R
4      34       VP                  4        VP                  4        R# )zINSERT OR REPLACE INTO accounts
        (id, source, name, type, subtype, mask, balance, currency, institution_name, last_synced_at)
        VALUES (?, 'asset', ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)r1   display_namer2   	type_namesubtype_nameNbalancerJ   rK   institution_namer   r8   rP   r9   rQ   r&   r'   )rX   r!   s   & r   upsert_assetr`   f   s    9DLL	G $LEE.!22QUU6]EE+EE.!#$55#3#?E!I,TEE*e$EE$%		
	 	KKMJJLr   c                $    V ^8  d   QhR\         /# )r*   pr,   )r.   s   "r   r/   r/   {   s      D r   c                    \        4       pTP                  R \        V R,          4      V P                  R4      V P                  R4      V P                  R4      V P                  R4      V P                  R4      e   \	        V R,          4      MRV P                  RR	4      V P                  R
4      34       VP                  4        VP                  4        R# )zINSERT OR REPLACE INTO accounts
        (id, source, name, type, subtype, mask, balance, currency, institution_name, last_synced_at)
        VALUES (?, 'plaid', ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)r1   r2   r
   subtypemaskr]   NrJ   rK   r^   r_   )rb   r!   s   & r   upsert_plaid_accountrf   {   s    9DLL	G $LEE&MEE&MEE)EE&M#$55#3#?E!I,TEE*e$EE$%		
	 	KKMJJLr   c                $    V ^8  d   QhR\         /# )r*   rr,   )r.   s   "r   r/   r/      s       r   c                    \        4       pTP                  R V R,          V P                  R4      ;'       g    RV P                  R4      e   \        V R,          4      MRV P                  R4      V P                  R4      V P                  R4      V P                  R	4      V P                  R
4      \	        V 4      3	4       VP                  4        VP                  4        R# )zINSERT OR REPLACE INTO recurring_groups
        (id, payee, typical_amount, cadence, start_date, end_date, category_id, notes, raw_json)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)r1   rG   rH   rI   Ncadence
start_dateend_dater=   rL   )r   r8   r9   rQ   r   r&   r'   )rh   r!   s   & r   upsert_recurringrm      s    9DLL	. dGEE'N))k"#%%/"=E!H+4EE)EE,EE*EE- EE'N1I
	
	  	KKMJJLr   c                $    V ^8  d   QhR\         /# )r*   days)int)r.   s   "r   r/   r/      s     	# 	#3 	#r   c                     \        4       pVP                  R RV  R234      P                  4       pVP                  4        V Uu. uF  p\	        V4      NK  	  up# u upi )zfSELECT * FROM transactions
           WHERE date(date) >= date('now', ?)
           ORDER BY date DESC-z daysr   r8   fetchallr'   r-   )ro   r!   rowsrh   s   &   r   list_recent_transactionsrv      s_    9D<<	! TF%	
 hj 	 	JJL!"TDGT"""s   Ac                      \        4       p V P                  R 4      P                  4       pV P                  4        V Uu. uF  p\	        V4      NK  	  up# u upi )zFSELECT * FROM recurring_groups ORDER BY typical_amount DESC NULLS LASTrs   )r!   ru   rh   s      r   list_recurringrx      sF    9D<<`ajjlDJJL!"TDGT"""s   Ac          
      T    V ^8  d   QhR\         R\         R\         R\         R\        /# )r*   kindperiod_start
period_end
summary_mdstructured_json)rP   r-   )r.   s   "r   r/   r/      s0       3 C S cg r   c           
          \        4       pVP                  R WW#\        V4      34       VP                  4        VP	                  4        R# )zqINSERT INTO analyses (kind, period_start, period_end, summary_md, structured_json)
        VALUES (?, ?, ?, ?, ?)N)r   r8   r   r&   r'   )rz   r{   r|   r}   r~   r!   s   &&&&& r   save_analysisr      s=    9DLL	"	ZVO5LM
 	KKMJJLr   )   )__doc__r   r   r   r   pathlibr   r   r   __file__r   ROOTr   r$   r   r(   r:   r?   rV   r`   rf   rm   rv   rx   r    r   r   <module>r      s    7    90 H~
-+
+\!$(%P**,	##r   