
    jjd5                        d dl mZ d dlmZ d dlmZ d dlmZ d dlm	Z	 d dl
mZ dZd%dZd d d dd&dZdddddd'dZd(dZdddddd)d"Zdddddd*d$ZdS )+    )annotations)date)Decimal)
Connection)Any)HTTPException household_financial_semantics_v1valuer   returnstrc                    t          t          t          | pd                                        t          d                    d          S )N0z0.01z.2f)formatr   r   quantize)r
   s    3src/jarvis_finance/services/household_financials.py_fmtr      s:    '#els++,,55gfooFFNNN    unavailable_chf_countunlinked_refund_count"transfer_membership_conflict_countr   intr   r   dict[str, Any]c                    g }| r|                     d| dd           |r|                     d|dd           |r|                     d|dd           |rdnd	| |||d
S )Nmissing_chf_conversionuL   CHF-Summen sind wegen fehlender belastbarer Umrechnung teilweise verfügbar.)codecountmessageunlinked_refundu^   Ausgabensummen sind wegen nicht belastbar verknüpfter Rückerstattungen teilweise verfügbar.transfer_membership_conflictuV   Transfer-Volumen ist wegen widersprüchlicher Transferverknüpfungen nicht verfügbar.partialcurrent)data_statusr   r   r   warnings)append)r   r   r   r$   s       r   financial_quality_metadatar&      s     &(H ,*e
 
 	 	 	
  %*w
 
 	 	 	
 * 27o
 
 	 	 	 %-;yy)!6!6.P  r   Nperiod	date_fromdate_toaccountr(   
str | Noner)   r*   r+   tuple[list[str], list[Any]]c                &   dg}g }| r	 |                      dd          \  }}t          |          dk    s/t          |          dk    sdt          |          cxk    rdk    s	n t          n # t          $ r t	          dd	          d w xY w|                    d
           |                    |            d|dfd|dffD ]o\  }}	}
|	rg	 t          j        |	           n## t          $ r t	          d| d	          d w xY w|                    d|
 d           |                    |	           p|r|r||k    rt	          dd	          |rB|                    d           |                    d|                                z   dz              ||fS )Nzt.status='confirmed'-            i  zperiod must use YYYY-MM)status_codedetailz substr(t.transaction_date,1,7)=?r)   z>=r*   z<=z must use YYYY-MM-DDzt.transaction_date z ?z#date_from must not be after date_toz!lower(coalesce(a.name,'')) LIKE ?%)	splitlenr   
ValueErrorr   r%   r   fromisoformatcasefold)r(   r)   r*   r+   whereparamsyearmonthlabelr
   operators              r   _filtersrB   3   s    $$EF 	] ,,sA..KD%4yyA~~UqSZZ8M8M8M8M28M8M8M8M   9N 	] 	] 	]C8QRRRX\\	]7888f$/D#AIwX\C]"^ ! !uh 	!f"5)))) f f f#u<Z<Z<Z[[[aeefLL;x;;;<<<MM%    [W [W!4!44YZZZZ 68999cG,,...4555&=s   A"A, ,B		C C>r<   	list[str]c                t    d}d}d}d}d| d| d}d| d	| d
| d| d| dd                     |            dS )NzCASE WHEN t.amount_chf IS NOT NULL THEN CAST(t.amount_chf AS REAL) WHEN upper(t.currency_original)='CHF' THEN CAST(t.amount_original AS REAL) ELSE NULL ENDzCASE WHEN r.amount_chf IS NOT NULL THEN CAST(r.amount_chf AS REAL) WHEN upper(r.currency_original)='CHF' THEN CAST(r.amount_original AS REAL) ELSE NULL ENDzCASE WHEN origin.amount_chf IS NOT NULL THEN CAST(origin.amount_chf AS REAL) WHEN upper(origin.currency_original)='CHF' THEN CAST(origin.amount_original AS REAL) ELSE NULL ENDzCOALESCE(r.reversal_of_transaction_id,CASE WHEN json_valid(r.notes) THEN json_extract(r.notes,'$.reversal_of_transaction_id') END)z~t.transaction_type='refund' AND origin.budget_transaction_id IS NOT NULL AND origin.transaction_type IN ('expense','fee') AND zQ IS NOT NULL AND rr.refund_amount_chf IS NOT NULL AND rr.running_refund_chf<=abs()a  
        WITH transfer_membership_rows AS (
            SELECT transfer_id,from_transaction_id AS budget_transaction_id
            FROM budget_transfers
            UNION ALL
            SELECT transfer_id,to_transaction_id AS budget_transaction_id
            FROM budget_transfers
        ), transfer_memberships AS (
            SELECT budget_transaction_id,MIN(transfer_id) AS transfer_id,
                   COUNT(DISTINCT transfer_id) AS membership_count
            FROM transfer_membership_rows
            GROUP BY budget_transaction_id
        ), refund_base AS (
            SELECT r.budget_transaction_id,r.transaction_date,r.created_at,
                   z AS origin_id,a   AS refund_amount_chf
            FROM budget_transactions r
            WHERE r.status='confirmed' AND r.transaction_type='refund'
        ), refund_ranked AS (
            SELECT refund_base.*,
                   SUM(CASE WHEN refund_amount_chf IS NOT NULL THEN abs(refund_amount_chf) ELSE 0 END)
                   OVER (PARTITION BY origin_id ORDER BY transaction_date,created_at,budget_transaction_id
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_refund_chf
            FROM refund_base
        ), base AS (
            SELECT t.budget_transaction_id,t.transaction_date,t.transaction_type,
                   t.account_id,t.amount_chf,t.amount_original,t.description,t.payee,
                   t.source_type,t.source_candidate_id,t.created_at,t.category_id,
                   CASE WHEN COALESCE(tm.membership_count,0)>1
                        THEN 'conflict:' || t.budget_transaction_id
                        ELSE COALESCE(tm.transfer_id,'transaction:' || t.budget_transaction_id)
                   END AS transfer_unit_id,
                   CASE WHEN COALESCE(tm.membership_count,0)>1 THEN 1 ELSE 0 END
                        AS transfer_membership_conflict,
                   z AS effective_amount_chf,
                   origin.budget_transaction_id AS origin_id,origin.category_id AS origin_category_id,
                   rr.refund_amount_chf,rr.running_refund_chf,z4 AS origin_amount_chf,
                   CASE WHEN aD   THEN 1 ELSE 0 END AS valid_refund
            FROM budget_transactions t
            LEFT JOIN refund_ranked rr ON rr.budget_transaction_id=t.budget_transaction_id
            LEFT JOIN budget_transactions origin
              ON origin.budget_transaction_id=rr.origin_id AND origin.status='confirmed'
             AND origin.budget_transaction_id<>t.budget_transaction_id
            LEFT JOIN transfer_memberships tm
              ON tm.budget_transaction_id=t.budget_transaction_id
            LEFT JOIN budget_accounts a ON a.budget_account_id=t.account_id
            WHERE z AND a  
        ), effects AS (
            SELECT base.*,
                   CASE WHEN valid_refund=1 THEN origin_category_id ELSE category_id END AS effective_category_id,
                   CASE WHEN transaction_type='income'
                        THEN COALESCE(abs(effective_amount_chf),0) ELSE 0 END AS income_effect,
                   CASE WHEN transaction_type IN ('expense','fee')
                        THEN COALESCE(abs(effective_amount_chf),0)
                        WHEN valid_refund=1 THEN -abs(effective_amount_chf) ELSE 0 END AS expense_effect,
                   CASE WHEN transaction_type IN ('income','expense','fee','refund','transfer')
                             AND effective_amount_chf IS NULL
                        THEN 1 ELSE 0 END AS unavailable_chf,
                   CASE WHEN transaction_type='refund' AND valid_refund=0 THEN 1 ELSE 0 END AS unlinked_refund,
                   CASE WHEN transaction_type='transfer' THEN 1 ELSE 0 END AS neutral_transfer,
                   CASE WHEN transaction_type='transfer'
                             AND (effective_amount_chf IS NULL OR transfer_membership_conflict=1)
                        THEN 1 ELSE 0 END
                        AS unavailable_transfer
            FROM base
        )
    )join)r<   
chf_amountrefund_amountorigin_amountrefund_targetvalid_refunds         r   _effects_cterL   U   s    	 	 	 	I 
	;	; 	; +8	; 	; 	; A "A A 2?A AB CA AF @MGA AH +IA AZ <<&&[A A A Ar   connr   c               F   t          ||||          \  }}t          |          }|                     |dz   |                                          }|                     |dz   |                                          }	t          t          |d         pd          t          |d         pd          t          |d         pd                    }
t          |d         pd          }t          t          |d	         pd          |          }t          t          |d
                   t          |d                   t          |d                   d|
t          |d         pd          ||rd nt          |d                   t          |d         pd          d |	D             dS )Nr'   a  
        , transfer_units AS (
            SELECT transfer_unit_id,
                   MAX(unavailable_transfer) AS unavailable_transfer,
                   MAX(CASE WHEN effective_amount_chf IS NOT NULL
                            THEN abs(effective_amount_chf) ELSE 0 END) AS volume_chf
            FROM effects
            WHERE neutral_transfer=1
            GROUP BY transfer_unit_id
        )
        SELECT COALESCE(SUM(income_effect),0) AS income,
               COALESCE(SUM(expense_effect),0) AS expense,
               COALESCE(SUM(income_effect-expense_effect),0) AS net,
               SUM(unlinked_refund) AS unlinked_refund_count,
               SUM(unavailable_chf) AS unavailable_chf_count,
               SUM(transfer_membership_conflict) AS transfer_membership_conflict_count,
               COALESCE((SELECT SUM(unavailable_transfer) FROM transfer_units),0)
                    AS unavailable_transfer_count,
               SUM(neutral_transfer) AS neutral_transfer_count,
               COALESCE((SELECT SUM(volume_chf) FROM transfer_units),0)
                    AS neutral_transfer_volume,
               COUNT(*) AS confirmed_transaction_count
        FROM effects
        aj  
        SELECT COALESCE(c.category_id,'uncategorized') AS category_id,
               COALESCE(c.name,'Unkategorisiert') AS category_name,
               COALESCE(c.category_type,
                   CASE WHEN SUM(income_effect)>0 AND SUM(expense_effect)=0 THEN 'income' ELSE 'expense' END
               ) AS category_type,
               COALESCE(SUM(income_effect),0) AS income,
               COALESCE(SUM(expense_effect),0) AS expense,
               SUM(CASE WHEN income_effect<>0 OR expense_effect<>0 THEN 1 ELSE 0 END) AS transaction_count
        FROM effects e
        LEFT JOIN budget_categories c ON c.category_id=e.effective_category_id
        WHERE income_effect<>0 OR expense_effect<>0
        GROUP BY COALESCE(c.category_id,'uncategorized'),COALESCE(c.name,'Unkategorisiert'),c.category_type
        ORDER BY expense DESC,income DESC,category_name
        r   r   r   r   r   unavailable_transfer_countincomeexpensenet)semantics_version
income_chfexpense_chfnet_chfneutral_transfer_countneutral_transfer_volumeconfirmed_transaction_countc                   g | ]}t          |d                    t          |d                   t          |d                   t          |d                   t          |d                   t          |d         pd          dS )category_idcategory_namecategory_typerP   rQ   transaction_countr   )r[   r\   r]   rT   rU   r^   )r   r   r   .0rows     r   
<listcomp>z3get_household_financial_summary.<locals>.<listcomp>  s     

 

 

   #3}#566!$S%9!:!:!$S%9!:!:"3x=11#C	N33%(-@)A)FQ%G%G 

 

 

r   )rW   rO   neutral_transfer_volume_chfrY   
categories)
rB   rL   executefetchonefetchallr&   r   maxFINANCIAL_SEMANTICS_VERSIONr   )rM   r(   r)   r*   r+   r<   r=   ctetotalsrd   qualitytransfer_conflict_countrO   s                r   get_household_financial_summaryrn      s    FiZabbbME6
u

C\\  	0 	3 4 hjj5 6   	 	! " hjj# $ )!&)@"A"FQGG!&)@"A"FQGG+.78=A,
 ,
  G "&)M"N"SRSTT!$F/05A66" "
 96(+,,F9-..u&&	 
  #&f-E&F&K!"L"L&@. 9DDf6788'*62O+P+UTU'V'V

 

 "

 

 

   r   list[dict[str, Any]]c                   t          ||||          \  }}|                     t          |          dz   |                                          }d |D             S )zHReturn transaction-level effects under the canonical monetary semantics.r'   a  
            SELECT e.*, c.name AS effective_category_name,
                   c.category_type AS effective_category_type,
                   c.color AS effective_category_color,
                   c.icon AS effective_category_icon,
                   COALESCE(e.payee,e.description,e.source_type,'Quelle') AS merchant_display_name
            FROM effects e
            LEFT JOIN budget_categories c ON c.category_id=e.effective_category_id
            ORDER BY e.transaction_date DESC, e.budget_transaction_id DESC
        c                ,    g | ]}t          |          S  )dictr_   s     r   rb   z4list_household_financial_effects.<locals>.<listcomp>-  s    &&&#DII&&&r   )rB   re   rL   rg   )rM   r(   r)   r*   r+   r<   r=   rowss           r    list_household_financial_effectsru     s{     	  ME6 <<U 	 		 	  hjj 	 '&&&&&r   )r
   r   r   r   )r   r   r   r   r   r   r   r   )
r(   r,   r)   r,   r*   r,   r+   r,   r   r-   )r<   rC   r   r   )rM   r   r(   r,   r)   r,   r*   r,   r+   r,   r   r   )rM   r   r(   r,   r)   r,   r*   r,   r+   r,   r   ro   )
__future__r   datetimer   decimalr   sqlite3r   typingr   fastapir   ri   r   r&   rB   rL   rn   ru   rr   r   r   <module>r|      sh   " " " " " "                         ! ! ! ! ! !@ O O O O "#!"./	     H       D[ [ [ [B  [ [ [ [ [ [B  ' ' ' ' ' ' ' 'r   