+
    hd(                        R t ^ RIt^ RIt^ RIHt ^ RIHt ^ RIHtHtH	t	H
t
 ^ RIHtHt  ! R R4      t]R8X  Ed   ]! 4       t]! R'4       ]! R	4       ]! R'4       ]P%                  4       t]! R
4       ]P)                  4        F  w  tt]! R] R] 24       K  	  ]P/                  4       t]! R4       ]! R]R,           R24       ]! R]R,           R]R,           24       ]! R]R,           24       ]P3                  4       t]'       d   ]! R4       ]^ ,          t]! R]P9                  RR4      R,           24       ]! R]P9                  RR4       24       ]! R]P9                  R4       R ]P9                  R!4       R"24       ]! R#]P9                  R$4       24       ]! R%]P9                  R&4       24       R# R# R# )(z@
Database Manager - Unified access layer for FarmMatch database
N)datetime)Path)ListDictOptionalTuple)DATABASE_PATHget_connectionc                     a  ] tR t^t o Rt]3R ltR tRV 3R lR lltV 3R lR lt	V 3R lR	 lt
V 3R
 lR ltV 3R lR ltV 3R lR ltV 3R lR ltRV 3R lR lltV 3R lR ltR V 3R lR lltR V 3R lR lltV 3R lR ltRtV tR# )!DatabaseManagerzUnified database access layerc                    Wn         R # )Ndb_path)selfr   s   &&S/Users/jonathan/SynologyDrive/Since Today/PROJECTEN/farmmatch/scraper/db_manager.py__init__DatabaseManager.__init__   s        c                ,    \        V P                  4      # )zGet a database connection)r	   r   )r   s   &r   r	   DatabaseManager.get_connection   s    dll++r   c                0   < V ^8  d   QhRS[ S[,          /#    returnr   r   )format__classdict__s   "r   __annotate__DatabaseManager.__annotate__   s      T$Z r   c                   V P                  4       pVP                  4       pV'       d   VP                  RV34       MVP                  R4       VP                  4        Uu. uF  p\	        V4      NK  	  ppVP                  4        V# u upi )z.Get all properties with optional status filterz)SELECT * FROM properties WHERE status = ?zSELECT * FROM propertiesr	   cursorexecutefetchalldictclose)r   statusconnr!   row
propertiess   &&    r   get_all_properties"DatabaseManager.get_all_properties   sq    ""$NNF	RNN56+1??+<=+<Cd3i+<
=

 >s    Bc                6   < V ^8  d   QhRS[ RS[S[,          /# r   urlr   )strr   r   )r   r   s   "r   r   r   '   s     * *s *x~ *r   c                    V P                  4       pVP                  4       pVP                  RV34       VP                  4       pVP	                  4        V'       d   \        V4      # R# )zGet a single property by URLz&SELECT * FROM properties WHERE url = ?Nr	   r!   r"   fetchoner%   r$   )r   r.   r'   r!   r(   s   &&   r   get_property_by_url#DatabaseManager.get_property_by_url'   sR    ""$?#Hoo

tCy)T)r   c                6   < V ^8  d   QhRS[ RS[S[,          /# )r   property_idr   )intr   r   )r   r   s   "r   r   r   0   s     * *c *htn *r   c                    V P                  4       pVP                  4       pVP                  RV34       VP                  4       pVP	                  4        V'       d   \        V4      # R# )zGet a single property by IDz%SELECT * FROM properties WHERE id = ?Nr1   )r   r6   r'   r!   r(   s   &&   r   get_property_by_id"DatabaseManager.get_property_by_id0   sR    ""$>Ooo

tCy)T)r   c                    < V ^8  d   QhRS[ /# r   )r7   )r   r   s   "r   r   r   9   s       r   c                   V P                  4       pVP                  4       p\        VP                  4       4      pRP	                  R.\        V4      ,          4      pRP	                  V4      pVP                  RV RV R2\        VP                  4       4      4       VP                  pVP                  4        VP                  4        V# )z'Add a new property, returns property_id,?z%
            INSERT INTO properties (z)
            VALUES (z
)
        )r	   r!   listkeysjoinlenr"   tuplevalues	lastrowidcommitr%   )r   kwargsr'   r!   fieldsplaceholdersfield_namesr6   s   &,      r   add_propertyDatabaseManager.add_property9   s    ""$fkkm$xxF 34hhv& %%0M 2!N #	 6==?#	%
 &&

r   c                &   < V ^8  d   QhRS[ RS[/# r-   r/   bool)r   r   s   "r   r   r   L   s      3 T r   c           	        V P                  4       pVP                  4       p\        P                  ! 4       P	                  4       VR&   RP                  VP                  4        Uu. uF  qU R2NK	  	  up4      pVP                  RV R2\        \        VP                  4       4      V.,           4      4       VP                  ^ 8  pVP                  4        VP                  4        V# u upi )zUpdate a property by URLlast_updated,  = ?z#
            UPDATE properties SET z#
            WHERE url = ?
        )r	   r!   r   now	isoformatrA   r@   r"   rC   r?   rD   rowcountrF   r%   )r   r.   rG   r'   r!   k
set_clausesuccesss   &&,     r   update_propertyDatabaseManager.update_propertyL   s    ""$ "*!9!9!;~YYFKKMBMq#T
MBC
 ##-, /	 4(C501	3
 //A%

  Cs   $C'c                &   < V ^8  d   QhRS[ RS[/# r-   rN   )r   r   s   "r   r   r   _   s      3 4 r   c                    V P                  4       pVP                  4       pVP                  RV34       VP                  ^ 8  pVP	                  4        VP                  4        V# )z0Delete a property (and related data via CASCADE)z$DELETE FROM properties WHERE url = ?)r	   r!   r"   rV   rF   r%   )r   r.   r'   r!   rY   s   &&   r   delete_propertyDatabaseManager.delete_property_   sQ    ""$=vF//A%

r   c                0   < V ^8  d   QhRS[ S[,          /# r   r   )r   r   s   "r   r   r   k   s      d r   c                    V P                  4       pVP                  4       pVP                  R4       VP                  4        Uu. uF  p\	        V4      NK  	  ppVP                  4        V# u upi )z)Get active properties without coordinatesz
            SELECT * FROM properties
            WHERE status = 'Active'
            AND (lat IS NULL OR lon IS NULL)
        r    )r   r'   r!   r(   r)   s   &    r   get_missing_geocoded_properties/DatabaseManager.get_missing_geocoded_propertiesk   sc    ""$  	
 ,2??+<=+<Cd3i+<
=

 >s   A,c                >   < V ^8  d   QhRS[ RS[RS[RS[ RS[ RS[/# )r   r.   latlon
confidencesourcer   )r/   floatrO   )r   r   s   "r   r   r   x   s:        C  e  %  $' <? OS r   c           
        V P                  4       pVP                  4       pVP                  RV34       VP                  4       pV'       g   VP	                  4        R# V^ ,          p	VP                  RW#WE\
        P                  ! 4       P                  4       \
        P                  ! 4       P                  4       V	34       VP                  RWW4V34       VP                  4        VP	                  4        R# )zUpdate geocoding for a property'SELECT id FROM properties WHERE url = ?Fz
            UPDATE properties
            SET lat = ?, lon = ?, geocoding_confidence = ?,
                geocoded_from = ?, geocoded_at = ?, last_updated = ?
            WHERE id = ?
        z
            INSERT INTO geocoding_history (property_id, lat, lon, confidence, source)
            VALUES (?, ?, ?, ?, ?)
        T)	r	   r!   r"   r2   r%   r   rT   rU   rF   )
r   r.   re   rf   rg   rh   r'   r!   r(   r6   s
   &&&&&&    r   update_geocoding DatabaseManager.update_geocodingx   s     ""$ 	@3&IooJJL!f 	 
 
lln&&((,,.*B*B*DkS	T 	  8	:
 	

r   c                    < V ^8  d   QhRS[ /# r   r   )r   r   s   "r   r   r      s     
 
T 
r   c                f   V P                  4       pVP                  4       pVP                  R4       VP                  4       ^ ,          pVP                  R4       VP                  4       ^ ,          pVP	                  4        V^ 8  d   WC,          ^d,          M^ pRVRVRW4,
          R\        V^4      /# )z!Get geocoding coverage statistics7SELECT COUNT(*) FROM properties WHERE status = 'Active'
            SELECT COUNT(*) FROM properties
            WHERE status = 'Active' AND lat IS NOT NULL AND lon IS NOT NULL
        totalgeocodedmissingcoverage_percent)r	   r!   r"   r2   r%   round)r   r'   r!   rs   rt   coverages   &     r   get_geocoding_stats#DatabaseManager.get_geocoding_stats   s    ""$PQ!!$  	 ??$Q'

/4qyH$s*aUu'h 2	
 	
r   c                &   < V ^8  d   QhRS[ RS[/# )r   output_pathr   rN   )r   r   s   "r   r   r      s      #  r   c                    V P                  RR7      pV F  pRV9   g   K  VR K  	  \        VR4      ;_uu_ 4       p\        P                  ! W$^R7       RRR4       R#   + '       g   i     R# ; i)z=Export all properties to JSON format (backward compatibility)N)r&   idw)indentT)r*   openjsondump)r   r|   r)   propfs   &&   r   export_to_jsonDatabaseManager.export_to_json   sf    ,,D,9
 Dt|J 
 +s##qIIjA. $  $# s   AA0	c                <   < V ^8  d   QhRS[ RS[S[S[3,          /# )r   
input_pathr   )r/   r   r7   )r   r   s   "r   r   r      s&     - -3 -%PSUXPX/ -r   c           
        \        VR4      ;_uu_ 4       p\        P                  ! V4      pRRR4       V P                  4       pVP	                  4       p^ p^ pX EF  pVP                  R4      p	V	'       g   K  VP                  RV	34       VP                  4       p
V
'       d   V
^ ,          pVP                  4        UUu/ uF  w  rVR8w  g   K  WbK  	  pppV'       d   RP                  VP                  4        Uu. uF  q R2NK	  	  up4      pVP                  RV R2\        \        VP                  4       4      \        P                  ! 4       P!                  4       V.,           4      4       V^,          pEK$  EK'  \        VP                  4       4      pR	P                  R
.\#        V4      ,          4      pR	P                  V4      pVP                  RV RV R2\        VP                  4       4      4       V^,          pEK  	  VP%                  4        VP'                  4        Wg3#   + '       g   i     EL; iu uppi u upi )z8Import properties from JSON (for backward compatibility)rNr.   rk   rR   rS   z/
                        UPDATE properties SET zL, last_updated = ?
                        WHERE id = ?
                    r=   r>   z-
                    INSERT INTO properties (z)
                    VALUES (z)
                )r   r   loadr	   r!   getr"   r2   itemsrA   r@   rC   r?   rD   r   rT   rU   rB   rF   r%   )r   r   r   r)   r'   r!   addedupdatedr   r.   existingr6   rW   v
set_fieldsrX   rH   rI   rJ   s   &&                 r   import_from_json DatabaseManager.import_from_json   s   *c""a1J # ""$D((5/C NNDsfM(H&qk/3zz|J|tqqEzdad|
J!%
@Q+R@Q1cJ@Q+R!SJNN (//9l ;$ tJ$5$5$78HLLN<T<T<VXc;ddeg qLG  diik*"xxF(;<!hhv. $--8M :)N +  4;;=)+ 
; > 	

~W #""* K+Rs   H. II>I
.H?	c                    < V ^8  d   QhRS[ /# r   ro   )r   r   s   "r   r   r      s     ( ( (r   c                   V P                  4       pVP                  4       p/ pVP                  R4       VP                  4       ^ ,          VR&   VP                  R4       VP                  4       ^ ,          VR&   VP                  R4       VP                  4       ^ ,          VR&   VP                  R4       VP                  4       ^ ,          VR&   VP                  R	4       VP                  4       ^ ,          VR
&   VP                  R4       VP                  4       ^ ,          VR&   \	        \        V P                  4      P                  4       P                  R,          ^4      VR&   VP                  4        V# )z#Get comprehensive system statisticszSELECT COUNT(*) FROM propertiestotal_propertiesrq   active_propertiesrr   geocoded_propertieszs
            SELECT COUNT(*) FROM properties
            WHERE status = 'Active' AND gpt_score IS NOT NULL
        analyzed_propertiesz&SELECT COUNT(*) FROM geocoding_historygeocoding_operationsz%SELECT COUNT(*) FROM analysis_historyanalysis_operationsi   database_size_kb)
r	   r!   r"   r2   rw   r   r   statst_sizer%   )r   r'   r!   statss   &   r   get_statisticsDatabaseManager.get_statistics   sL   ""$ 	89$*OO$5a$8 !PQ%+__%6q%9!" 	  	 (.'8';#$ 	  	 (.'8';#$ 	?@(.(9!(<$%>?'-'8';#$ %*$t||*<*A*A*C*K*Kd*RTU$V !

r   r   N)Active)mediummanual)zenriched_data.json)__name__
__module____qualname____firstlineno____doc__r   r   r	   r*   r3   r9   rK   rZ   r^   rb   rl   ry   r   r   r   __static_attributes____classdictcell__)r   s   @r   r   r      s     ', , * ** * & &     D
 
4 - -b( (r   r   __main__zDATABASE MANAGER - DEMOu   
📊 Statistics:z  z: u   
📍 Geocoding:z  Coverage: rv   %z  Geocoded: rt   /rs   z  Missing: ru   u   
🏠 Sample property:z	  Title: titlezN/A:N<   Nz  URL: r.   z  Location: (re   rR   rf   )z  GPT Score: 	gpt_scorez  Custom Score: custom_scorezF======================================================================)r   sqlite3r   r   pathlibr   typingr   r   r   r   database_schemar   r	   r   r   dbprintr   r   r   keyvaluery   	geo_statsr*   r)   r   r    r   r   <module>r      s       . . 9O Od z		B	&M	
#$	&M E	
kkm
U3%r%!" $ &&(I	
	L#567q
9:	L:./q71C0D
EF	K	),-
./ &&(J')!}	$((7E23789:./01dhhuo.b%0ACDdhh{3456 .!9 :;< / r   