Ë
    ñçÀj   ã            	       ó†  — d dl mZmZmZ d dlmZ d dlmZ d dlZej                  j                  ej                  j                  ej                  j                  ej                  j                  e«      «      «      «      Zdej                  j                  ed«      › �Z eed¬«      Z eeed	¬
«      Z e«       Zd„ Zd„ Zy)é    )Úcreate_async_engineÚAsyncSessionÚasync_sessionmaker)Údeclarative_base)ÚtextNzsqlite+aiosqlite:///z
billing.dbT)ÚechoF)Úclass_Úexpire_on_commitc                 óŽ   K  — t        «       4 ƒd {  –—† } | ­–— d d d «      ƒd {  –—†  y 7 Œ7 Œ# 1 ƒd {  –—†7  sw Y   y xY w­w)N)ÚAsyncSessionLocal)Úsessions    úapp/core/database.pyÚget_dbr      sB   è ø€ Ü Ó"÷ ð  gØ‹÷÷ ñ øð ø÷ ÷ ñ üs8   ‚A‘,’A•0›A¦.§A®A°A¶9·A¾Ac               ƒ   óX	  K  — ddl m}  t        j                  «       4 ƒd {  –—† }|j	                  | j
                  j                  «      ƒ d {  –—†  i dd“dd“dd“dd	“d
d“dd“dd“dd“dd“dd“dd“dd“dd“dd“dd“dd“dd“i d d!“d"d#“d$d“d%d&“d'd(“d)d“d*d“d+d“d,d“d-d“d.d/“d0d“d1d“d2d3“d4d3“d5d3“d6d3“¥ddd7dddddd8d9d:œ
¥}|j                  t        d;«      «      ƒ d {  –—† j                  «       D �ch c]  }|d<   ’Œ	 }}|j                  «       D ]2  \  }}||vsŒ|j                  t        d=|› d>|› �«      «      ƒ d {  –—†  Œ4 |j                  t        d?«      «      ƒ d {  –—† j                  «       }|D ].  \  }|j                  t        d@«      dA|dB›�|dCœ«      ƒ d {  –—†  Œ0 |j                  t        dD«      «      ƒ d {  –—†  |j                  t        dE«      «      ƒ d {  –—†  |j                  t        dF«      «      ƒ d {  –—†  |j                  t        dG«      «      ƒ d {  –—†  |j                  t        dH«      «      ƒ d {  –—†  dddIœ}	|j                  t        dJ«      «      ƒ d {  –—† j                  «       D �ch c]  }|d<   ’Œ	 }
}|	j                  «       D ]2  \  }}||
vsŒ|j                  t        dK|› d>|› �«      «      ƒ d {  –—†  Œ4 |j                  t        dL«      «      ƒ d {  –—† j                  «       D �ch c]  }|d<   ’Œ	 }}dMddddNœ}|j                  «       D ]2  \  }}||vsŒ|j                  t        dO|› d>|› �«      «      ƒ d {  –—†  Œ4 dddd	dddPœdddQœddRddSœdddTddRddUœdVdiddddd	ddWœdXœ}|j                  «       D ]‘  \  }}|j                  t        dY|› dZ�«      «      ƒ d {  –—† j                  «       D �ch c]  }|d<   ’Œ	 }}|j                  «       D ]5  \  }}||vsŒ|j                  t        d[|› d\|› d>|› �«      «      ƒ d {  –—†  Œ7 Œ“ d d d «      ƒd {  –—†  y 7 �Œ-7 �Œ7 �Œmc c}w 7 �Œ7 �Œó7 �Œ·7 �Œ–7 �Œw7 �ŒX7 �Œ97 �Œ7 �Œöc c}w 7 �Œ�7 �Œ|c c}w 7 �Œ7 Œ¹c c}w 7 Œ\7 ŒJ# 1 ƒd {  –—†7  sw Y   y xY w­w)]Nr   )ÚBaseÚprovisioning_statuszVARCHAR DEFAULT 'not_started'Úmikrotik_provisioning_statuszVARCHAR DEFAULT 'pending'Úolt_provisioning_statusÚprovisioning_messageÚTEXTÚprovisioned_atÚDATETIMEÚis_archivedzBOOLEAN DEFAULT 0Ú	play_typezVARCHAR DEFAULT '1play'Újatuh_tempoÚservice_profileÚVARCHARÚprovisioning_modezVARCHAR DEFAULT 'private_pppoe'Úpppoe_usernameÚpppoe_passwordÚonu_idÚINTEGERÚwan_vlanzINTEGER DEFAULT 175Úservice_vlanÚ
pppoe_vlanzINTEGER DEFAULT 100Úhotspot_vlanzINTEGER DEFAULT 150Ú	iptv_vlanzINTEGER DEFAULT 200Úolt_vlan_profilezVARCHAR DEFAULT 'bnbv1009'Úodp_idÚ
olt_vendorzVARCHAR DEFAULT 'ZTE'Ú	olt_modelzVARCHAR DEFAULT 'C320'Úservice_package_idÚservice_profile_idÚservice_modeÚmanagement_pppoe_vlanÚodp_portÚont_management_modezVARCHAR DEFAULT 'none'Úmanagement_pppoe_usernameÚmanagement_pppoe_passwordÚont_sn_modezVARCHAR DEFAULT 'manual'Úonu_id_modeÚolt_port_modeÚont_brand_modezVARCHAR DEFAULT 'unavailable'zINTEGER DEFAULT 1zTEXT DEFAULT '["pppoe"]')
Úont_rx_powerÚont_tx_powerÚhardware_discovery_statusÚodp_latitudeÚodp_longitudeÚcustomer_codeÚnikÚpppoe_secret_revealed_atÚpppoe_secret_versionÚservice_modes_jsonzPRAGMA table_info(customers)é   z!ALTER TABLE customers ADD COLUMN ú zJSELECT id FROM customers WHERE customer_code IS NULL OR customer_code = ''z9UPDATE customers SET customer_code = :code WHERE id = :idzPLG-Ú07d)ÚcodeÚidzXCREATE UNIQUE INDEX IF NOT EXISTS ix_customers_customer_code ON customers(customer_code)zaCREATE UNIQUE INDEX IF NOT EXISTS ix_customers_nik_unique ON customers(nik) WHERE nik IS NOT NULLz›CREATE UNIQUE INDEX IF NOT EXISTS ix_customers_pppoe_username_unique ON customers(pppoe_username) WHERE pppoe_username IS NOT NULL AND pppoe_username <> ''z{CREATE UNIQUE INDEX IF NOT EXISTS ix_customers_ont_sn_unique ON customers(ont_sn) WHERE ont_sn IS NOT NULL AND ont_sn <> ''a  
            UPDATE customers
            SET status = 'active',
                provisioning_message = 'Internet aktif; status dipulihkan oleh rekonsiliasi billing.'
            WHERE status = 'suspended'
              AND provisioning_status = 'provisioned'
              AND NOT EXISTS (
                  SELECT 1 FROM invoices
                  WHERE invoices.customer_id = customers.id
                    AND invoices.status <> 'paid'
                    AND invoices.due_date <= CURRENT_TIMESTAMP
              )
        )Úused_atÚ
expires_atzPRAGMA table_info(vouchers)z ALTER TABLE vouchers ADD COLUMN z!PRAGMA table_info(voucher_agents)zINTEGER DEFAULT 0)Úcommission_rateÚnama_ispÚ
domain_ispÚprefixz&ALTER TABLE voucher_agents ADD COLUMN )Úolt_device_idÚmikrotik_device_idÚolt_adapter_keyÚolt_fields_jsonr%   r&   )r%   r&   zVARCHAR DEFAULT 'registered')ÚareaÚstatusÚcredential_idzVARCHAR DEFAULT 'ssh')ÚlocationÚpop_nameÚmanagement_protocolÚapi_portrR   rS   rN   )ÚvendorÚadapter_keyrM   rN   Úpayload_summaryÚexecuted_at)Ú	customersÚservice_packagesÚmikrotik_devicesÚolt_devicesÚodpsÚprovisioning_jobszPRAGMA table_info(ú)zALTER TABLE z ADD COLUMN )Úapp.models.modelsr   ÚengineÚbeginÚrun_syncÚmetadataÚ
create_allÚexecuter   ÚfetchallÚitems)r   ÚconnÚcolumnsÚrowÚexistingÚnameÚ
definitionÚrowsÚcustomer_idÚvoucher_columnsÚexisting_voucherÚexisting_agentsÚagent_columnsÚtable_columnsÚtableÚ	additionsÚexisting_table_columnss                    r   Úinit_dbr|      sÌ  è ø€ Ý&Ü�|‰|‹~÷ }dð }d Ø�m‰m˜DŸM™M×4Ñ4Ó5×5Ð5ð/
Ø!Ð#Bð/
à*Ð,Gð/
ð &Ð'Bð/
ð # Fð	/
ð
 ˜jð/
ð Ð.ð/
ð Ð2ð/
ð ˜:ð/
ð ˜yð/
ð  Ð!Bð/
ð ˜ið/
ð ˜ið/
ð �ið/
ð Ð-ð/
ð Ð1ð/
ð  Ð/ð!/
ð" Ð1ñ#/
ð$ Ð.ð%/
ð& Ð <ð'/
ð, �ið-/
ð. Ð1ð//
ð0 Ð1ð1/
ð2 ! )ð3/
ð4 ! )ð5/
ð6 Ð=ð7/
ð8 $ Yð9/
ð: ˜	ð;/
ð< "Ð#;ð=/
ð> (¨ð?/
ð@ (¨ðA/
ðB Ð5ðC/
ðD Ð5ðE/
ðF Ð7ðG/
ðH Ð8ñI/
ðJ &Ø%Ø)HØ%Ø&Ø&ØØ(2Ø$7Ø">ò]/
ˆð` .2¯\©\¼$Ð?]Ó:^Ó-_×'_×&iÑ&iÓ&kÖl˜s�C˜“FÐlˆÐlØ '§¡£ò 	bÑˆD�*Ø˜8Ò#Ø—l‘l¤4Ð*KÈDÈ6ÐQRÐS]ÐR^Ð(_Ó#`Óa×aÑað	bð —l‘l¤4Ð(tÓ#uÓv×v÷  Añ  Aó  CˆØ"ò 	Y‰Nˆ[Ø—,‘,œtÐ$_Ó`ÐmqÐr}ð  Bð  rCð  lDð  LWñ  cXó  Y÷  Yñ  Yð	Yà�l‰lœ4Ð zÓ{Ó|×|Ð|Ø�l‰lœ4ð  !Dó  Eó  F÷  	Fð  	FØ�l‰lœ4ð  !~ó  ó  @÷  	@ð  	@Ø�l‰lœ4ð  !^ó  _ó  `÷  	`ð  	`ð �l‰lœ4ð !ó ó ÷ 	ð 	ð "Ø$ñ
ˆð 6:·\±\Ä$ÐGdÓBeÓ5f×/f×.pÑ.pÓ.rÖs s˜C ›FÐsÐÐsØ /× 5Ñ 5Ó 7ò 	aÑˆD�*ØÐ+Ò+Ø—l‘l¤4Ð*JÈ4È&ÐPQÐR\ÐQ]Ð(^Ó#_Ó`×`Ñ`ð	að 59·L±LÄÐFiÓAjÓ4k×.k×-uÑ-uÓ-wÖx c˜3˜q›6ÐxˆÐxà2Ø!Ø#Øñ	
ˆð !.× 3Ñ 3Ó 5ò 	gÑˆD�*Ø˜?Ò*Ø—l‘l¤4Ð*PÐQUÐPVÐVWÐXbÐWcÐ(dÓ#eÓf×fÑfð	gð "+À)Ø#,ÀØ3ÐEZñð 4ÐEZñ!ð "Ð-KÐ^gñ!ð &°9ÐUlØ%Ð1OÐbkñð *¨9Ð5à#°IÐPYØ&/ÀFÐ[eñ"ñ#
ˆð, !.× 3Ñ 3Ó 5ò 	dÑˆE�9Ø?C¿|¹|ÌDÐSeÐfkÐelÐlmÐQnÓLoÓ?p×9p×8zÑ8zÓ8|Ö%}° c¨!£fÐ%}Ð"Ð%}Ø$-§O¡OÓ$5ò dÑ ��jØÐ5Ò5ØŸ,™,¤t¨l¸5¸'ÀÈdÈVÐSTÐU_ÐT`Ð,aÓ'bÓc×cÑcñdð	d÷s}d÷ }dñ }dùØ5ùðd (`ûÒlð bùàvùð YùØ|ùð 	Fùð 	@ùð 	`ùð	ùð" 0gûÒsð aùØ.kûÒxð gùð4 :qúÒ%}ð døð{}dø÷ }d÷ }dñ }düs®  ‚R*¡Q¢R*¥)RÁQÁBRÃ'QÃ(RÃ<QÄRÄ%#RÅQÅ	#RÅ,QÅ->RÆ+Q"Æ,#RÇQ%Ç!RÇ1Q(Ç2!RÈQ+È!RÈ5Q.È6!RÉQ1É&RÉ>Q4É?RÊQ7ÊRÊ<#RËQ<Ë #RÌQ?ÌRÌRÌ$#RÍ#RÍ+RÍ,A$RÏR
ÏRÏ%RÏ1RÐ&RÐ4R
Ð5RÐ=R*ÑRÑ	R*ÑRÑRÑRÑRÑ"RÑ%RÑ(RÑ+RÑ.RÑ1RÑ4RÑ7RÑ?RÒRÒ
RÒRÒR*ÒR'ÒRÒR'Ò#R*)Úsqlalchemy.ext.asyncior   r   r   Úsqlalchemy.ormr   Ú
sqlalchemyr   ÚosÚpathÚdirnameÚabspathÚ__file__ÚBASE_DIRÚjoinÚDATABASE_URLrd   r   r   r   r|   © ó    r   ú<module>rŠ      s™   ðß XÑ XÝ +Ý Û 	à�7‰7�?‰?˜2Ÿ7™7Ÿ?™?¨2¯7©7¯?©?¸2¿7¹7¿?¹?È8Ó;TÓ+UÓVÓW€Ø% b§g¡g§l¡l°8¸\Ó&JÐ%KÐL€á	˜\°Ô	5€Ù& v°lÐUZÔ[Ð ÙÓ€òódr‰   