
    +jc6                        d dl mZm Z  d dlmZ d dlmZ d dlmZ ddlm	Z	m
Z
 ddlmZmZmZmZmZmZmZmZ ddlmZmZmZmZmZmZmZmZ  ed	          Zd
 Zd3dedededefdZ e!                                de"fd            Z#e!                                de"fd            Z$e!                                d4dede%de"fd            Z&e!                                d5dede%de"fd            Z'e!                                d6dede"fd            Z(e!                                de"fd            Z)e!                                d7de%d e%dede"fd!            Z*e!                                d8dede"fd"            Z+e!                                d9d$e%d%e%d&e%d'e%de"f
d(            Z,e!                                d:d)e"d&e%d'e%de"fd*            Z-e!                                d:d)e"d&e%d'e%de"fd+            Z.e!                                d:d)e"d&e%d'e%de"fd,            Z/e!                                d:d)e"d&e%d'e%de"fd-            Z0e!                                d:d)e"d&e%d'e%de"fd.            Z1e!                                d:d)e"d&e%d'e%de"fd/            Z2e!                                d:d)e"d&e%d'e%de"fd0            Z3e4d1k    re5                                 d2S d2S );    )datedatetime)Decimal)UUID)FastMCP   )	fetch_all	fetch_one)PartyIn	ProductInSourceFileInPurchaseOrderInStockMovementInShipmentSignoffInValidationIssueInReconciliationRunIn)create_partycreate_productregister_source_filecreate_purchase_orderadd_stock_movementcreate_shipment_signoffcreate_validation_issuerun_reconciliationzgoods-ledger-write-layerc                    t          | t          t          f          r|                                 S t          | t                    rt          |           S t          | t                    rt          |           S t          | t                    rd |                                 D             S t          | t                    rd | D             S | S )Nc                 4    i | ]\  }}|t          |          S  	_jsonable).0kvs      app/mcp_server.py
<dictcomp>z_jsonable.<locals>.<dictcomp>   s$    :::DAq9Q<<:::    c                 ,    g | ]}t          |          S r   r   )r    r"   s     r#   
<listcomp>z_jsonable.<locals>.<listcomp>   s    ,,,	!,,,r%   )

isinstancer   r   	isoformatr   strr   dictitemslist)values    r#   r   r      s    %(D)** !   %!! 5zz% 5zz% ;::EKKMM::::% -,,e,,,,Lr%      d   r.   defaultmaximumreturnc                     	 t          |           }n# t          $ r |}Y nw xY wt          dt          ||                    S )Nr   )int	Exceptionmaxmin)r.   r1   r2   ns       r#   _limitr:      sP    JJ   q#a//"""s    !!c                  J    t          d          } ddit          | pi           S )z@Read-only health check for the goods_ledger database connection.z;select current_database() as database, now() as server_timeokT)r
   r   )rows    r#   ledger_healthr>   '   s.     Q
R
RC$/)CI2..//r%   c                  8    t          d          } d | D             S )z5Read-only summary counts for the goods_ledger system.a  
        select 'products' as key, count(*)::int as value from products
        union all select 'source_files', count(*)::int from source_files
        union all select 'purchase_orders', count(*)::int from purchase_orders
        union all select 'order_lines', count(*)::int from order_lines
        union all select 'channel_allocations', count(*)::int from channel_allocations
        union all select 'stock_movements', count(*)::int from stock_movements
        union all select 'shipment_signoffs', count(*)::int from shipment_signoffs
        union all select 'validation_issues_open', count(*)::int from validation_issues where status = 'open'
        c                 ,    i | ]}|d          |d         S )keyr.   r   r    rs     r#   r$   z"ledger_summary.<locals>.<dictcomp>=   s"    ///QAeHaj///r%   )r	   )rowss    r#   ledger_summaryrE   .   s.     		 D 0/$////r%   2    limitkeywordc                     t          | dd          }|rt          dd| d|d          }nt          dd|i          }t          |          t          |          d	S )
zTRead-only current stock rows. Optional keyword matches stock item display name/code.rF   r0   r1   r2   z
            select * from v_current_stock
            where coalesce(display_name,'') ilike %(kw)s
               or coalesce(stock_item_code,'') ilike %(kw)s
            order by display_name nulls last
            limit %(limit)s
            %kwrH   zNselect * from v_current_stock order by display_name nulls last limit %(limit)srH   rD   countr:   r	   r   len)rH   rI   r9   rD   s       r#   ledger_current_stockrS   @   s     	ub#...A y "w>>>A..	
 	
 ilsuvkwxxdOOc$ii888r%      product_keywordc                     t          | dd          }d}d|i}|rd}d| d|d<   t          d	| d
|          }t          |          t          |          dS )z%Read-only recent inventory movements.rT   r0   rK   rG   rH   zwhere coalesce(si.display_name,'') ilike %(kw)s or coalesce(si.stock_item_code,'') ilike %(kw)s or coalesce(p.standard_name,'') ilike %(kw)s or coalesce(p.barcode_69,'') ilike %(kw)srL   rN   a  
        select sm.id, sm.movement_code, sm.movement_date, sm.direction, sm.movement_type,
               sm.quantity, sm.unit, sm.purpose, sm.returnable, sm.signoff_status,
               sm.logistics_company, sm.tracking_no, sm.receiver_name,
               si.stock_item_code, si.display_name, p.standard_name, p.spec, p.barcode_69,
               handler.display_name as handler_name, counterparty.display_name as counterparty_name
        from stock_movements sm
        left join stock_items si on si.id = sm.stock_item_id
        left join products p on p.id = si.product_id
        left join parties handler on handler.id = sm.handler_party_id
        left join parties counterparty on counterparty.id = sm.counterparty_id
        z\
        order by sm.movement_date desc, sm.created_at desc
        limit %(limit)s
        rO   rQ   )rH   rU   r9   whereparamsrD   s         r#   ledger_recent_stock_movementsrY   T   s     	ub#...AEq\F . I-?---t	 
	 	 	 	! D$ dOOc$ii888r%   c                     t          | dd          }t          dd|i          }t          |          t          |          dS )z0Read-only open validation issues needing review.rF   r0   rK   z
        select id, issue_type, severity, related_table, related_id, field_name,
               message, status, created_at
        from validation_issues
        where status = 'open'
        order by created_at desc
        limit %(limit)s
        rH   rO   rQ   )rH   r9   rD   s      r#   ledger_open_validation_issuesr[   r   sQ     	ub#...A	 
!
 
D dOOc$ii888r%   c                  z    d t          d          D             } t          d          pi }t          | |d          S )zMRead-only overview counts, recent source files, and recent validation issues.c                 ,    i | ]}|d          |d         S )metricr.   r   rB   s     r#   r$   z-ledger_dashboard_overview.<locals>.<dictcomp>   s"    ttt!ak1W:tttr%   z7select metric, value from v_api_summary order by metrica  
        select
            (select count(*) from validation_issues where status='open')::int as open_issue_count,
            (select count(*) from stock_movements where direction='in')::int as stock_in_count,
            (select count(*) from stock_movements where direction='out')::int as stock_out_count,
            (select count(*) from reconciliation_runs)::int as reconciliation_run_count,
            (select count(*) from api_write_requests)::int as api_write_request_count
    countsextras)r	   r
   r   r_   s     r#   ledger_dashboard_overviewrb      sZ     uty9r/s/stttF  	 	    &99:::r%   all
   scopec                 V   t          |dd          }d|  d}d |                    d          D             pdhdt          d	t          ffd
}i } |d          rt	          d||d          |d<    |d          rt	          d||d          |d<    |d          rt	          d||d          |d<    |d          rt	          d||d          |d<    |d          rt	          d||d          |d<    |d          rt	          d||d          |d<   | |t          |          dS )zVUnified search across products, orders, files, stock movements, shipments, and issues.rd   rF   rK   rL   c                 ^    h | ]*}|                                 |                                 +S r   )strip)r    ss     r#   	<setcomp>z ledger_search.<locals>.<setcomp>   s-    ???AQWWYY?aggii???r%   ,rc   namer3   c                     dv p| v S )Nrc   r   )rl   scopess    r#   enabledzledger_search.<locals>.enabled   s    0$&.0r%   productsa  
            select id, sku_code, standard_name, raw_name_default, category, spec, barcode_69, guobo_code, status
            from products
            where coalesce(standard_name,'') ilike %(kw)s or coalesce(raw_name_default,'') ilike %(kw)s
               or coalesce(sku_code,'') ilike %(kw)s or coalesce(barcode_69,'') ilike %(kw)s or coalesce(guobo_code,'') ilike %(kw)s
            order by standard_name limit %(limit)s
        rM   ordersa  
            select po.id, po.order_code, po.order_side, po.order_type, po.order_no, po.external_contract_no,
                   bp.display_name as buyer_name, sp.display_name as seller_name
            from purchase_orders po
            left join parties bp on bp.id = po.buyer_party_id
            left join parties sp on sp.id = po.seller_party_id
            where coalesce(po.order_code,'') ilike %(kw)s or coalesce(po.order_no,'') ilike %(kw)s
               or coalesce(po.external_contract_no,'') ilike %(kw)s or coalesce(po.tracking_no,'') ilike %(kw)s
               or coalesce(bp.display_name,'') ilike %(kw)s or coalesce(sp.display_name,'') ilike %(kw)s
            order by po.created_at desc limit %(limit)s
        filesa  
            select id, original_filename, stored_path, material_type, source_platform, uploader, extraction_status, received_at
            from source_files
            where coalesce(original_filename,'') ilike %(kw)s or coalesce(stored_path,'') ilike %(kw)s
               or coalesce(material_type,'') ilike %(kw)s or coalesce(source_platform,'') ilike %(kw)s or coalesce(uploader,'') ilike %(kw)s
            order by created_at desc limit %(limit)s
        stocka  
            select sm.id, sm.movement_code, sm.movement_date, sm.direction, sm.movement_type, sm.quantity, sm.unit,
                   sm.tracking_no, sm.receiver_name, si.display_name as stock_item_name
            from stock_movements sm left join stock_items si on si.id = sm.stock_item_id
            where coalesce(sm.movement_code,'') ilike %(kw)s or coalesce(sm.tracking_no,'') ilike %(kw)s
               or coalesce(sm.receiver_name,'') ilike %(kw)s or coalesce(si.display_name,'') ilike %(kw)s
               or coalesce(si.stock_item_code,'') ilike %(kw)s
            order by sm.movement_date desc, sm.created_at desc limit %(limit)s
        stock_movements	shipmentsa  
            select id, signoff_code, ship_date, status, logistics_company, tracking_no, receiver_name, signer_name, signed_at
            from shipment_signoffs
            where coalesce(signoff_code,'') ilike %(kw)s or coalesce(logistics_company,'') ilike %(kw)s
               or coalesce(tracking_no,'') ilike %(kw)s or coalesce(receiver_name,'') ilike %(kw)s or coalesce(signer_name,'') ilike %(kw)s
            order by created_at desc limit %(limit)s
        issuesa  
            select id, issue_type, severity, related_table, field_name, message, status, created_at
            from validation_issues
            where coalesce(issue_type,'') ilike %(kw)s or coalesce(related_table,'') ilike %(kw)s
               or coalesce(field_name,'') ilike %(kw)s or coalesce(message,'') ilike %(kw)s
            order by created_at desc limit %(limit)s
        )queryre   groups)r:   splitr*   boolr	   r   )rI   re   rH   r9   rN   ro   rx   rn   s          @r#   ledger_searchr{      s    	ub"---A	WB??S!1!1???JE7F1c 1d 1 1 1 1 1 1Fwz %& ( ##% %z wx %$ 
& ##
% 
%x ww %# % ##% %w ww 	%$- / ##%% %% ! w{ %' ) ##% %{ wx %$ & ##% %x u	&8I8IJJJr%   c                     t          | dd          }t          d          pi }t          dd|i          }t          |          t          |          t	          |          dS )z=Read-only live supplier-vs-upstream reconciliation dashboard.rT   r0   rK   a\  
        select count(*)::int as product_count,
               count(*) filter (where shortage_qty > 0)::int as shortage_count,
               coalesce(sum(shortage_qty),0) as shortage_quantity_total,
               coalesce(sum(self_stock_candidate_qty),0) as self_stock_candidate_total
        from v_supplier_vs_upstream_reconciliation_live
    z
        select * from v_supplier_vs_upstream_reconciliation_live
        order by shortage_qty desc nulls last, product_name limit %(limit)s
    rH   )summaryrD   rP   )r:   r
   r	   r   rR   )rH   r9   r}   rD   s       r#   ledger_reconciliation_dashboardr~      s     	ub#...A  	 	      1 D !))9T??SQUYYWWWr%   mcp
project_idnotesactor_profileidempotency_keyc                 X    d|pdi}| r| |d<   t          t          di |||pd          S )zNCreate an auditable reconciliation snapshot from the live reconciliation view.r   Nr   r   r   r   )r   r   )r   r   r   r   payloads        r#   ledger_run_reconciliationr      sc     &G + *1<<G<<Mkz  lC  C  D  D  D  Dr%   r   c                 >    t          t          di | ||pd          S )zYCreate a party/customer/supplier/channel/warehouse/person. Business-level write, audited.Nr   r   )r   r   r   r   r   s      r#   ledger_create_partyr      s.     **'**-YhYplpqqqqr%   c                 >    t          t          di | ||pd          S )u`   Create a product/SKU with optional barcode, 国博 code, spec, price, and alias fields. Audited.Nr   r   )r   r   r   s      r#   ledger_create_productr      s.     )..g..m]l]tptuuuur%   c                 >    t          t          di | ||pd          S )zBRegister a source/evidence file and its receipt metadata. Audited.Nr   r   )r   r   r   s      r#   ledger_register_source_filer      s.       7 7w 7 7}fuf}y}~~~~r%   c                 >    t          t          di | ||pd          S )zcCreate upstream/downstream/supplemental purchase order with lines and channel allocations. Audited.Nr   r   )r   r   r   s      r#   ledger_create_purchase_orderr     sB     !!;!;7!;!;=jy  kB  ~B  C  C  C  Cr%   c                 >    t          t          di | ||pd          S )zhAppend an inventory movement. Outbound movements validate available stock and write issues on rejection.Nr   r   )r   r   r   s      r#   ledger_add_stock_movementr     s.     o8888gvg~z~r%   c                 >    t          t          di | ||pd          S )zPCreate shipment/signoff header and lines with logistics/signing fields. Audited.Nr   r   )r   r   r   s      r#   ledger_create_shipment_signoffr     C     ##4#?#?w#?#?}n}  oF  BF  G  G  G  Gr%   c                 >    t          t          di | ||pd          S )z:Record a validation issue for later human review. Audited.Nr   r   )r   r   r   s      r#   ledger_create_validation_issuer     r   r%   __main__N)r/   r0   )rF   rG   )rT   rG   )rF   )rc   rd   )rT   )rG   rG   r   rG   )r   rG   )6r   r   decimalr   uuidr   mcp.server.fastmcpr   dbr	   r
   modelsr   r   r   r   r   r   r   r   write_servicer   r   r   r   r   r   r   r   r   r   r5   r:   toolr+   r>   rE   r*   rS   rY   r[   rb   r{   r~   r   r   r   r   r   r   r   r   __name__runr   r%   r#   <module>r      sR   # # # # # # # #             & & & & & & $ $ $ $ $ $ $ $ R  R  R  R  R  R  R  R  R  R  R  R  R  R  R  R  R  R  R  R                   
 g())  # ## # #3 # # # # # 0t 0 0 0 0 0 0 0 0 0" 9 9 93 9 9 9 9 9& 9 9 9C 9QU 9 9 9 9: 9 9 9d 9 9 9 9" ;4 ; ; ; ; >K >K3 >Ks >K3 >K >K >K >K >KB X X3 X X X X X" D D# D3 DTW Dru D  @D D D D D r r rc rTW rae r r r r
 v v4 v vVY vcg v v v v
   c \_ im    
 C C$ Cs C]` Cjn C C C C
 @ @t @C @Z] @gk @ @ @ @
 G GD G G_b Glp G G G G
 G GD G G_b Glp G G G G
 zGGIIIII r%   