
    iM                       U d Z ddlmZ ddlmZmZ ddlmZ ddlm	Z	 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mZmZmZ ddlmZmZmZ ddlmZ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) ddddddddddddgdddddddddddddddddgddd d!di d"dd#d$dd%dd&did"dd'd(ddddddddddgddd)d*dd+dd,did"dd-d.di d"dd/d0dd1dd2did"dd3d4ddddddddddgddd5d6ddddddddddgddd7d8dd9dd:did"dd;d<di d"dgZ*d=e+d><   	 	 	 	 	 	 	 	 	 	 dOd?Z,dPd@Z-dQdAZ.dRdBZ/dRdCZ0dRdDZ1dRdEZ2dRdFZ3dRdGZ4dRdHZ5dRdIZ6dRdJZ7dRdKZ8dRdLZ9dRdMZ:yN)Su   
AI Assistant tools — functions the LLM can call to query SPBU data.

Each tool returns a dict that gets serialized to the LLM as context.
    )annotations)date	timedelta)Decimal)Any)funcselect)AsyncSession)AnomaliRecord)Expense)LaporanShiftPenjualanNozzleStatusLaporanStockAdjustmentStockAdjustmentItem)	PenebusanPenebusanItemStatusPenebusan)
PenerimaanPenerimaanItem)
Penyetoran)ProdukProdukHarga)NozzleShiftSpbuTangkiget_ringkasan_harianzDapatkan rangkuman operasional SPBU untuk satu hari atau rentang tanggal. Termasuk total penjualan, expenses, penyetoran, dan penerimaan BBM.objectstringzTanggal mulai (YYYY-MM-DD))typedescriptionzTanggal akhir (YYYY-MM-DD))tanggal_mulaitanggal_akhirr#   r$   )r!   
propertiesrequired)namer"   
parametersget_penjualanzhDapatkan data penjualan BBM per produk dalam rentang tanggal tertentu, termasuk volume dan nilai rupiah.z9Filter nama produk (opsional, misal: Pertalite, Pertamax))r#   r$   produk_namaget_stokzaDapatkan status stok terkini per tangki, termasuk volume terakhir, kapasitas, dan persentase isi.)r!   r%   get_anomaliz>Dapatkan daftar anomali aktif (status new atau investigating).tipezbFilter tipe anomali (opsional: quota_exceeded, meter_discrepancy, losses_exceeded, negative_stock)get_expensesz<Dapatkan data pengeluaran operasional dalam rentang tanggal.get_laporan_statusu\   Dapatkan status laporan shift hari ini — mana yang sudah submit, draft, atau belum dibuat.tanggalz&Tanggal (YYYY-MM-DD), default hari iniget_tangki_statuszDapatkan kondisi detail setiap tangki: volume, kapasitas, produk, dan estimasi hari stok tersisa berdasarkan rata-rata penjualan.get_penebusanu`   Dapatkan status penebusan (DO/SO) BBM ke Pertamina — termasuk yang pending dan sudah diterima.statuszZFilter status (opsional: draft, waiting_so, submitted, partially_received, fully_received)get_penerimaanz3Dapatkan data penerimaan BBM dalam rentang tanggal.get_penyetoranz3Dapatkan data penyetoran kas dalam rentang tanggal.get_harga_produkzHDapatkan harga produk yang berlaku saat ini dan riwayat perubahan harga.r*   z3Nama produk (opsional, jika kosong tampilkan semua)get_info_spbuzaDapatkan informasi konfigurasi SPBU: nama, nomor Pertamina, jumlah tangki, shift, island, nozzle.zlist[dict[str, Any]]TOOL_DEFINITIONSc                   K   t         t        t        t        t        t
        t        t        t        t        t        t        d}|j                  |      }|sdd| diS  || ||       d{   S 7 w)z(Route a tool call to its implementation.)r   r)   r+   r,   r.   r/   r1   r2   r4   r5   r6   r7   errorzTool 'z' not foundN)_get_ringkasan_harian_get_penjualan	_get_stok_get_anomali_get_expenses_get_laporan_status_get_tangki_status_get_penebusan_get_penerimaan_get_penyetoran_get_harga_produk_get_info_spbuget)dbspbu_id	tool_name	argumentshandlershandlers         5/var/www/html/spbu.com/backend/app/assistant/tools.pyexecute_toolrO      so      !6'#%1/'))-'H ll9%G6)K899Wi0000s   A(A1*A/+A1c                ,    t        j                  |       S N)r   fromisoformat)ss    rN   _parse_daterT      s    a      c                     | t        |       S dS )N0)str)vs    rN   _decrZ      s    ]3q6++rU   c                  K   t        |d         }t        |d         }t        t        j                  t        j                  t
        j                        d      t        j                  t        j                  t
        j                        d            j                  t              j                  t        j                  |k(  t        j                  j                  ||            }| j                  |       d {   j                         \  }}t        t        j                  t        j                  t         j"                        d            j                  t         j                  |k(  t         j                  j                  ||            }| j                  |       d {   j%                         }	t        t        j                  t        j                  t&        j(                        d            j                  t&        j                  |k(  t&        j                  j                  ||            }
| j                  |
       d {   j%                         }t        t        j                  t        j                  t*        j,                        d            j                  t.              j                  t.        j                  |k(  t.        j                  j                  ||            }| j                  |       d {   j%                         }| d| t1        |      t1        |      t1        |	      t1        |      t1        |      t1        |xs d|	xs dz
        dS 7 k7 7 "7 kw)Nr#   r$   r    s/d )periodepenjualan_volume_literpenjualan_nilai_rupiahexpenses_rupiahpenyetoran_rupiahpenerimaan_bbm_liternet_kas)rT   r	   r   coalescesumr   volumenilaijoinr   whererI   r0   betweenexecuteoner   jumlah
scalar_oner   jumlah_setorr   volume_diterimar   rZ   )rH   rI   argsd1d2sqvolvaleqexpensespq
penyetoranrq
penerimaans                 rN   r;   r;      s~    	T/*	+B	T/*	+B 	MM$((?#9#9:A>MM$((?#8#891=	
 
l		|##w.0D0D0L0LRQS0T	U  jjn$))+HC 
dhhw~~6:	;	A	A7"GOO$;$;B$C
B jjn$002H 
dhhz'>'>?C	D	J	Jg%z'9'9'A'A"b'I
B 

2&224J 	t}}TXXn&D&DEqIJ	j		z!!W,j.@.@.H.HR.P	Q 
 

2&224J Trd#"&s)"&s)>!*- $Z 0hm!45 - % % ' 'sL   C9M1;M&<B(M1$M)%B%M1
M,B8M1M/A#M1)M1,M1/M1c                  K   t        |d         }t        |d         }|j                  d      }t        t        j                  t        j
                  t        j                  t        j                  t        j                        d      t        j                  t        j                  t        j                        d            j                  t        t        j                  t        j                  k(        j                  t        t        j                   t        j                  k(        j                  t"        t        j$                  t"        j                  k(        j'                  t"        j(                  |k(  t"        j*                  j-                  ||            j/                  t        j                  t        j
                        j1                  t        j                  t        j                        j3                               }|r2|j'                  t        j                  j5                  d| d            }| j7                  |       d {   j9                         }| d| |D cg c]'  }|d   |d   t;        |d         t;        |d	         d
) c}t;        t        d |D                    t;        t        d |D                    dS 7 c c}w w)Nr#   r$   r*   r   %r\            )namakodevolume_liternilai_rupiahc              3  &   K   | ]	  }|d      yw)r   N .0rs     rN   	<genexpr>z!_get_penjualan.<locals>.<genexpr>        4t!1t   c              3  &   K   | ]	  }|d      ywr   Nr   r   s     rN   r   z!_get_penjualan.<locals>.<genexpr>  s     3d!dr   )r]   produktotal_volumetotal_nilai)rT   rG   r	   r   r   r   r   rd   re   r   rf   rg   rh   r   	nozzle_idid	produk_idr   laporan_shift_idri   rI   r0   rj   group_byorder_bydescilikerk   allrZ   )	rH   rI   rq   rr   rs   produk_filterqrowsr   s	            rN   r<   r<      s    	T/*	+B	T/*	+BHH]+M 	KKMM$((?#9#9:A>MM$((?#8#891=	

 
fo//699<	=	ff&&&))3	4	lO<<O	P	|##w.0D0D0L0LRQS0T	U	&++v{{	+	$((?001668	9  GGFKK%%-&:;<**Q-$$&DTrd# 
 qT1Q4adUYZ[\]Z^U_`
 S 4t 445C3d334   
s$   IKKK+,K:KKc           
     @  K   | j                  t        t              j                  t        j                  |k(  t        j
                  j                  d      t        j                  j                  d                    d {   j                         j                         }g }|D ]  }t        t        j                        j                  t              j                  t        j                  |k(  t        j                  |j                  k(        j!                  t        j"                  j%                         t        j                  j%                               j'                  d      }| j                  |       d {   j)                         }|r1|j*                  r%t-        t/        ||j*                  z  dz        d      nd}t        t0        j2                        j                  t0        j                  |j4                  k(        }	|j4                  r'| j                  |	       d {   j)                         nd }
|j7                  |j2                  |
xs dt9        |      t9        |j*                        | d|dk  rdn|d	k  rd
ndd        d|iS 7 7 7 tw)NTr   d   r      —r~      Kritis   RendahNormal)tangkir   r   kapasitas_liter
persen_isir3   r   )rk   r	   r   ri   rI   	is_activeis_
deleted_atscalarsr   r   volume_final_literrh   r   	tangki_idr   r   r0   r   limitscalar_one_or_noner   roundfloatr   r   r   appendrZ   )rH   rI   rq   tangkisresulttvol_qru   pctproduk_qr*   s              rN   r=   r=     s    ZZvV^^w68H8H8L8LT8RTZTeTeTiTijnTop wy  F&99:T/"U?**g57J7T7TXYX\X\7\]Xo--224o6H6H6M6M6OPU1X 	 ZZ&&::<@CHYHYeE# 1 11C78!<_`&++&,,VYY!++-EFKL;;RZZ11EEG\`ff!*U I#A$5$56 E)"%(hC"H(
 	 . f9 ' 2s9   A>J JC;J<J=B*J'J(A/JJJc           
       K   t        t              j                  t        j                  |k(  t        j                  j                  ddg            j                  t        j                  j                               j                  d      }|j                  d      }|r"|j                  t        j                  |k(        }| j                  |       d {   j                         j                         }t        |      |D cg c]X  }|j                   |j                  |j                  |j"                  |j                  r|j                  j%                         nd dZ c}dS 7 c c}w w)Nnewinvestigating   r-   )r   r-   r3   detail
created_at)total_aktifanomali)r	   r   ri   rI   r3   in_r   r   r   r   rG   r-   rk   r   r   lenr   r   	isoformat)rH   rI   rq   r   r-   r   r   s          rN   r>   r>   9  s(    }##(  %!9:	 h}'',,./b	 
 88FDGGM&&$./**Q-((*..0D4y 	
  dd((((:;,,all446D 	
   	
s%   CE-E&.E-AE(!E-(E-c                  K   t        |d         }t        |d         }t        t              j                  t        j                  |k(  t        j
                  j                  ||            j                  t        j
                  j                               }| j                  |       d {   j                         j                         }t        d |D              }| d| t        |      t        |      |d d D cg c]8  }t        |j
                        |j                   t        |j"                        d: c}dS 7 c c}w w)Nr#   r$   c              3  4   K   | ]  }|j                     y wrQ   )rm   r   s     rN   r   z _get_expenses.<locals>.<genexpr>\  s     '$Q$   r\   r   )r0   
keteranganrm   )r]   total_rupiahjumlah_itemitems)rT   r	   r   ri   rI   r0   rj   r   r   rk   r   r   re   rZ   r   rX   r   rm   	rH   rI   rq   rr   rs   r   r   totalr   s	            rN   r?   r?   S  s    	T/*	+B	T/*	+Bw7"GOO$;$;B$C	hw##%&  **Q-((*..0D'$''E Trd#U4y #2Y
 AIIalldSTS[S[n]
	   
s%   B*E	,E-AE	 =E
=E	E	c           	       K   t        |j                  dt        j                         j	                                     }| j                  t        t              j                  t        j                  |k(  t        j                  j                  d      t        j                  j                  d             j                  t        j                               d {   j                         j!                         }g }|D ]B  }| j                  t        t"        j$                        j                  t"        j                  |k(  t"        j&                  |j(                  k(  t"        j*                  |k(               d {   j-                         }| j                  t        t.        j$                        j                  t.        j                  |k(  t.        j&                  |j(                  k(  t.        j*                  |k(               d {   j-                         }|j1                  |j2                  |xs d|xs dd       E t5        |      |dS 7 y7 7 Ow)Nr0   Tbelum_dibuat)shiftlaporan_penjualanstock_adjustment)r0   shifts)rT   rG   r   todayr   rk   r	   r   ri   rI   r   r   r   r   	jam_mulair   r   r   r3   shift_idr   r0   r   r   r   r   rX   )	rH   rI   rq   tglr   r   rS   lapsas	            rN   r@   r@   i  s    
dhhy$**,*@*@*BC
DCJJuEMMW4eoo6I6I$6OQVQaQaQeQefjQkl	%//	"  wy 
 FZZ<&&'--$$/1F1F!$$1NP\PdPdhkPk
 
 	 	 JJ?))*00''72O4L4LPQPTPT4TVeVmVmqtVt
 
 	 	 	VV!$!6 " 4n
 	 & 3x6223

s9   CIIB!I:I;BIIAIIIc                  K   t        | ||       d {   }t        j                         }|t        d      z
  }|d   D ]  }|d   }|dk(  rd|d<   t	        t        j                  t        j                  t        j                        d            j                  t        t        j                  t        j                  k(        j                  t        t        j                  t        j                  k(        j                  t         t        j"                  t         j                  k(        j%                  t         j&                  |k(  t         j(                  j+                  ||      t        j,                  |k(        }| j/                  |       d {   j1                         xs d}	|	rt3        |	      dz  nd}
|d   d	k7  rt3        |d         nd}|
dkD  rt5        ||
z  d
       dnd|d<    |S 7 7 dw)N   )daysr   r   r   estimasi_harir   r   rW   r   z hari)r=   r   r   r   r	   r   rd   re   r   rf   rh   r   r   r   r   r   r   r   ri   rI   r0   rj   r   rk   rn   r   r   )rH   rI   rq   stokd_endd_startr   r*   avg_qtotal_7d	daily_avgru   s               rN   rA   rA     s    2w--D JJLEiQ''G(^k%!&Ao 4==/*@*@!A1EFT&/33vyy@AT&&**fii78T, @ @LOO STU$$/$$,,We<{* 	 **U++779>Q+3E(Oa'	*+N*;s*BeAn%&DMPQMcIoq 9:%@W\/) , K9 .. ,s#   G9G4E>G9G7A"G97G9c           
     d  K   t        t              j                  t        j                  |k(        j	                  t        j
                  j                               j                  d      }|j                  d      }|r"|j                  t        j                  |k(        }| j                  |       d {   j                         j                         }d|D cg c]E  }|j                  |j                  |j                  t!        |j
                        |j                  dG c}iS 7 sc c}w w)N
   r3   	penebusan)r   no_dono_sor0   r3   )r	   r   ri   rI   r   r0   r   r   rG   r3   rk   r   r   r   r   r   rX   )rH   rI   rq   r   status_filterr   r   s          rN   rB   rB     s     y	 1 1W <=FFyGXGXG]G]G_`ffgijAHHX&MGGI$$56**Q-((*..0D 	
  ddqyy>(( 	
   	
s%   B3D05D)6%D0A
D+%D0+D0c                  K   t        |d         }t        |d         }t        t        j                  t        j                  t        j
                  t        j                  t        j                  t        j                        d            j                  t        t        j                  t        j                  k(        j                  t        j                  |k(  t        j                  j                  ||            j!                  t        j                  t        j                  t        j
                        j#                  t        j                  j%                               }| j'                  |       d {   j)                         }| d| |D cg c]'  }|d   t+        |d         |d   t-        |d         d) c}t-        t        d	 |D                    d
S 7 gc c}w w)Nr#   r$   r   r\   r   r   r   )r   r0   no_segelr   c              3  &   K   | ]	  }|d      ywr   r   r   s     rN   r   z"_get_penerimaan.<locals>.<genexpr>  r   r   )r]   r|   r   )rT   r	   r   r   r0   r   r   rd   re   r   rp   rh   penerimaan_idri   rI   rj   r   r   r   rk   r   rX   rZ   )rH   rI   rq   rr   rs   r   r   r   s           rN   rC   rC     sz    	T/*	+B	T/*	+B 	MM:--z/B/BMM$((>#A#ABAF	
 
nn::jmmK	L	z!!W,j.@.@.H.HR.P	Q	*--!3!3Z5H5H	I	*$$))+	,  **Q-$$&DTrd# 
 Q4C!I1Q4QUVWXYVZQ[\
 S 4t 445   
s$   E6G'8G 9G',G" G'"G'c           	     |  K   t        |d         }t        |d         }t        t              j                  t        j                  |k(  t        j
                  j                  ||            j                  t        j
                  j                               }| j                  |       d {   j                         j                         }t        d |D              }| d| t        |      |D cg c]8  }t        |j
                        t        |j                        |j                   d: c}dS 7 c c}w w)Nr#   r$   c              3  4   K   | ]  }|j                     y wrQ   )ro   r   s     rN   r   z"_get_penyetoran.<locals>.<genexpr>  s     -1r   r\   )r0   rm   r3   )r]   r   rz   )rT   r	   r   ri   rI   r0   rj   r   r   rk   r   r   re   rZ   rX   ro   r3   r   s	            rN   rD   rD     s    	T/*	+B	T/*	+Bz  g%z'9'9'A'A"b'I	hz!!&&()  **Q-((*..0D---E Trd#U 
 AII$q~~2FRSRZRZ[
   
s%   B*D<,D5-AD<3=D70D<7D<c                8  K   t        t              j                  t        j                  j	                  d             }|j                  d      }|r2|j                  t        j                  j                  d| d            }| j                  |       d {   j                         j                         }g }|D ]L  }t        t              j                  t        j                  |j                  k(        j                  t        j                  j!                               j#                  d      }| j                  |       d {   j                         j                         }	|j%                  |j                  |j&                  |j(                  |	rt+        |	d   j,                        nd|	rt/        |	d   j                        nd|	D 
cg c]-  }
t+        |
j,                        t/        |
j                        d/ c}
d       O d|iS 7 y7 c c}
w w)	Nr*   r~      r   r   )hargaberlaku_mulai)r   r   
is_subsidiharga_terkinir   riwayatr   )r	   r   ri   r   r   rG   r   r   rk   r   r   r   r   r   r   r   r   r   r   r   r   rZ   r   rX   )rH   rI   rq   r   r   produk_listr   pharga_q
harga_rowshs              rN   rE   rE     s    vV..22489AHH]+MGGFKK%%-&:;<A&//1557KF;U;((ADD01Xk//4467U1X	 	 JJw//88:>>@
ffFF,,:DT*Q-"5"56%AKSA!<!<=QV $#A qww-#aoo:NO#

 
	 ( f- ' 0s8   BHHB/HHBH2H HHHc                  K   | j                  t        t              j                  t        j                  |k(               d {   j                         }|sddiS | j                  t        t              j                  t        j                  |k(  t        j                  j                  d      t        j                  j                  d                    d {   j                         j                         }| j                  t        t              j                  t        j                  |k(  t        j                  j                  d      t        j                  j                  d                    d {   j                         j                         }| j                  t        t              j                  t        j                  j!                  |      t        j                  j                  d      t        j                  j                  d                    d {   j                         j                         }|j"                  |j$                  |j&                  t)        |      |D cg c]*  }|j*                  |j,                   d|j.                   d, c}t)        |      |D cg c]$  }|j*                  t1        |j2                        d& c}t)        |      dS 7 7 7 x7 c c}w c c}w w)	Nr:   zSPBU tidak ditemukanT)rI   -)r   jam)r   	kapasitas)r   nomor_pertaminaalamatjumlah_shiftr   jumlah_tangkir   jumlah_nozzle)rk   r	   r   ri   r   r   r   rI   r   r   r   r   r   r   r   islandhasr'   r  r  r   r   r   jam_selesairZ   r   )	rH   rI   rq   spbur   r   nozzlesrS   r   s	            rN   rF   rF     s7    **VD\//70BCDDXXZD/00JJuEMMW4eoo6I6I$6OQVQaQaQeQefjQkl wy  ZZvV^^w68H8H8L8LT8RTZTeTeTiTijnTop wy  ZZvV]]..w.?AQAQAUAUVZA[]c]n]n]r]rsw]xy wy 
 		//++FV\]V\QRAFFakk]!AMM?+KLV\]WSZ[SZaAFFa6G6G1HISZ[W	 	! E ^[sj   AK?K*BK?K-BK?8K09B*K?#K3$AK?3/K5"K?2)K:K?-K?0K?3K?5
K?N)
rH   r
   rI   intrJ   rX   rK   dict[str, Any]returnr  )rS   rX   r  r   )rY   r   r  rX   )rH   r
   rI   r  rq   dictr  r  );__doc__
__future__r   datetimer   r   decimalr   typingr   
sqlalchemyr   r	   sqlalchemy.ext.asyncior
   app.models.anomalir   app.models.expensesr   app.models.operationalr   r   r   r   r   app.models.penebusanr   r   r   app.models.penerimaanr   r   app.models.penyetoranr   app.models.productr   r   app.models.spbur   r   r   r   r8   __annotations__rO   rT   rZ   r;   r<   r=   r>   r?   r@   rA   rB   rC   rD   rE   rF   r   rU   rN   <module>r     s   # $   # / , '  K J < , 2 7 7 ' g*2C_!`*2C_!` )/:
   B*2C_!`*2C_!`(0A|}
 )/:
 z
 W  ;_  `
	 U*2C_!`*2C_!` )/:
 %uH=ef
	 $ [
  y8  =Y  Z
	 !L*2C_!`*2C_!` )/:
 !L*2C_!`*2C_!` )/:
 #aAvw
	  z
m~* & ~F111 1 	1
 1:!,+\B@4,3>B*2*>rU   