Ë
    |w¼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    ú*/var/www/html/bestweb/app/core/database.pyÚget_dbr      sB   è ø€ Ü Ó"÷ ð  gØ‹÷÷ ñ øð ø÷ ÷ ñ üs8   ‚A‘,’A•0›A¦.§A®A°A¶9·A¾Ac               ƒ   ó	  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<   ’Œ	 }}dM|vr"|j                  t        dN«      «      ƒ d {  –—†  dddd	dddOœdddPœddQddRœdddSddQddTœdUdiddddd	ddVœdWœ}|j                  «       D ]‘  \  }}|j                  t        dX|› dY�«      «      ƒ d {  –—† j                  «       D �ch c]  }|d<   ’Œ	 }}|j                  «       D ]5  \  }}||vsŒ|j                  t        dZ|› d[|› d>|› �«      «      ƒ d {  –—†  Œ7 Œ“ d d d «      ƒd {  –—†  y 7 �Œ7 �ŒÝ7 �ŒGc c}w 7 �Œî7 �ŒÍ7 �Œ‘7 �Œp7 �ŒQ7 �Œ27 �Œ7 �Œô7 �ŒÐc c}w 7 �Œw7 �ŒVc 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)Úcommission_ratezGALTER TABLE voucher_agents ADD COLUMN commission_rate INTEGER DEFAULT 0)Ú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_portrO   rP   rK   )ÚvendorÚadapter_keyrJ   rK   Ú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Útable_columnsÚtableÚ	additionsÚexisting_table_columnss                   r   Úinit_dbrx      sƒ  è ø€ Ý&Ü�|‰|‹~÷ vdð v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Ø OÑ3Ø—,‘,œtÐ$mÓnÓo×oÐoð
 "+À)Ø#,ÀØ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÷evd÷ vdñ vdùØ5ùðd (`ûÒlð bùàvùð YùØ|ùð 	Fùð 	@ùð 	`ùð	ùð" 0gûÒsð aùØ.kûÒxàoùð4 :qúÒ%}ð døðmvdø÷ vd÷ vdñ vdüs¨  ‚R¡P(¢R¥)Q/ÁP+ÁBQ/Ã'P.Ã(Q/Ã<P1ÄQ/Ä%#Q/ÅP6Å	#Q/Å,P9Å->Q/Æ+P<Æ,#Q/ÇP?Ç!Q/Ç1QÇ2!Q/ÈQÈ!Q/È5QÈ6!Q/ÉQÉ&Q/É>QÉ?Q/ÊQÊQ/Ê<#Q/ËQË #Q/ÌQÌQ/ÌQÌ$#Q/ÍQ!ÍA"Q/Î*Q$Î+Q/Î?Q&ÏQ/Ï(&Q/ÐQ+
ÐQ/ÐRÐ"Q-Ð#RÐ+Q/Ð.Q/Ð1Q/Ð9Q/Ð<Q/Ð?Q/ÑQ/ÑQ/ÑQ/ÑQ/ÑQ/ÑQ/ÑQ/ÑQ/Ñ$Q/Ñ&Q/Ñ-RÑ/RÑ5Q8Ñ6RÑ=R)Úsqlalchemy.ext.asyncior   r   r   Úsqlalchemy.ormr   Ú
sqlalchemyr   ÚosÚpathÚdirnameÚabspathÚ__file__ÚBASE_DIRÚjoinÚDATABASE_URLra   r   r   r   rx   © ó    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Ô[Ð ÙÓ€òóxdr…   