o LeiB @sdddlZddlZddlZddlZddlZddlmZmZeeZ e dej ej ej eddZdZej ej edZej ej ej eddZd d Zd d Zd dZddZd3defddZdedefddZddZddZdefddZdedBfdd Zd!d"Zd#d$Z d%d&Z!d'ed(efd)d*Z"d+d,Z#d-d.Z$d/d0Z%d1d2Z&dS)4N)datetimetimezone DATABASE_URLz..zdeals.dbdealsz headers.jsonzwatermark.jsoncCsF|dddddd}tdd|}tdd|}|d}|S) z4Sanitizes a string to be a valid SQLite column name.%Percent&and._z [^a-zA-Z0-9_]z__+)replaceresubstrip)namer//var/www/agentarbitrage/keepa_deals/db_utils.pysanitize_col_names  rcCs$|d|ddd|DS)z+Fetches the column names for a given table.zPRAGMA table_info()cSsg|]}|dqSr.0rowrrr z%get_table_columns..)executefetchall)cursor table_namerrrget_table_columnssr c CszF|d|ddd|D}|D].}|d}|d|d|}t|dkrC|ddd krCtd |d Wd SqWd Stjyb}ztd|WYd}~d Sd}~ww)z3Checks if a unique index exists on the ASIN column.zPRAGMA index_list('z')cSsg|] }|ddkr|qS)rrrrrrr"sz,has_unique_index_on_asin..rzPRAGMA index_info('rr!ASINzFound existing unique index 'z ' on ASIN.TFz!Error checking for unique index: N)rrlenloggerinfosqlite3Errorerror)rrindexesindex index_namecolumnserrrhas_unique_index_on_asins"r.c Cstdtdz'tt}|}|d|WdWdS1s)wYWdStjyH}z tj d|ddd}~ww)z{ Ensures the 'system_state' table exists. Schema: key (TEXT PRIMARY KEY), value (TEXT), updated_at (TIMESTAMP) z2Database check: Ensuring table 'system_state' at ' ' exists.z CREATE TABLE IF NOT EXISTS system_state ( key TEXT PRIMARY KEY, value TEXT, updated_at TIMESTAMP ) Nz#Error creating system_state table: Texc_info) r$r%DB_PATHr&connectrrcommitr'r(connrr-rrr'create_system_state_table_if_not_exists/s   & r7keyc Csz8tt(}|}|d|f|}|r$|dWdWS|WdWS1s1wYWdStjyZ}ztjd|d|dd|WYd}~Sd}~ww)z.Retrieves a value from the system_state table.z,SELECT value FROM system_state WHERE key = ?rNz'Error retrieving system state for key '': Tr0) r&r3r2rrfetchoner'r$r()r8defaultr6rrr-rrrget_system_stateDs (r<valuec Csz@tt0}|}ttj}| d|t ||f| t d|d|WdWdS1s9wYWdStjya}zt jd|d|ddWYd}~dSd}~ww) z'Sets a value in the system_state table.z{ INSERT OR REPLACE INTO system_state (key, value, updated_at) VALUES (?, ?, ?) zSystem state updated: =Nz$Error setting system state for key 'r9Tr0)r&r3r2rrnowrutc isoformatrstrr4r$r%r'r()r8r=r6r updated_atr-rrrset_system_stateRs  & (rDc Csttdtdtdztt}|}|dtd| s=t dtdt  WdWdStdtd t |t}d |vr]td |d td d|vrotd|d tdd|vrtd|d tdd|vrtd|d tdd|vrtd|d tdt |tst d|tdWdWdS1swYWdStjtfy}z tjd|ddd}~ww)zt Ensures the 'deals' table exists and has the correct schema. Also ensures 'system_state' table exists. Database check: Ensuring table '' at '' is correctly configured.