o 2&h3@sFddlZddlmZddlmZmZmZmZmZm Z GdddZ dS)N)asynccontextmanager)ListDictAnyTupleOptionalUnionc @seZdZdefddZeddZddZded eeefd e fd d Z ded e ee fd e efddZ d&dedeeefdeeeefd e fddZ   d'dedeeeefdeedee d e ef ddZdedeeefdeeefd e fddZdedeeefd e fddZdedeeefd e fddZd&dedeeeefd e fdd Zd(d"ed#ed e efd$d%ZdS))DatabaseManagerdb_namecCs ||_dSN)r )selfr r (/var/www/html/server/database/handler.py__init__s zDatabaseManager.__init__c Cszt|j4IdH%}tj|_z |VW|IdHn|IdHwWdIdHdS1IdHs6wYdS)z/Async context manager for database connections.N) aiosqliteconnectr Row row_factorycloser connr r rget_connection s".zDatabaseManager.get_connectionc s|4IdH3}|dIdH|dIdH|dIdH|dIdH|IdHWdIdHdS1IdHsBwYdS)z7Create database and initial tables if they don't exist.Nan CREATE TABLE IF NOT EXISTS visitors ( id INTEGER PRIMARY KEY AUTOINCREMENT, ip TEXT DEFAULT NULL, phrase TEXT DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) a] CREATE TABLE IF NOT EXISTS api_keys ( key_id INTEGER PRIMARY KEY AUTOINCREMENT, ip TEXT NOT NULL, api_key VARCHAR(64) UNIQUE, active BOOLEAN DEFAULT TRUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) a CREATE TABLE IF NOT EXISTS config ( id INTEGER PRIMARY KEY CHECK (id = 1), enabled INTEGER DEFAULT 1, callback TEXT DEFAULT '45.11.59.247:8000', auth_key TEXT DEFAULT 'AD9U1JVCwYsl43EtQyvQLz9u6KuEQjLM3LOHmxfqeTtbk0QrxkFhBgpXkOJQ42sVF8J3xc5VZusvloi12pz6dcY3Mct3Ehwi' ) zJ INSERT OR IGNORE INTO config (id) VALUES (1) )rexecutecommitrr r rinitialize_databases  .z#DatabaseManager.initialize_database table_namecolumnsreturnc szH|4IdH2}ddd|D}d|d|d}||IdH|IdH WdIdHWdS1IdHsBwYWdStjyd}ztd |WYd}~d Sd}~ww) z*Create a new table with specified columns.N, cSsg|] \}}|d|qS) r ).0coldtyper r r 9sz0DatabaseManager.create_table..zCREATE TABLE IF NOT EXISTS  ()TzError creating table: F)rjoinitemsrrrErrorprint)r rrr columns_defqueryer r r create_table5s4zDatabaseManager.create_table search_valuec srz|4IdH}|d|dIdH}dd|2IdH}|IdH|s7gWdIdHWSttrKdd|D}gt|}ndd|D}fdd|D}d |}d |d |} || |IdH}d d|2IdH} |IdH| WdIdHWS1IdHswYWdStjy} zt d | gWYd} ~ Sd} ~ ww)a/ Search all columns in a table for a given value (string or int). Args: table_name: The table to search search_value: The value to look for in any column Returns: List of rows (as dicts) where the value appears in any column NzPRAGMA table_info(r%cs g|z 3dHW}|dq6S)Nr r rowr r rr#Pz0DatabaseManager.search_table..cSg|]}|dqSz = ?r r r!r r rr#WcSr3)z LIKE ?r r5r r rr#Zr6csg|]}ddqS)%r r _r.r rr#[sz OR SELECT * FROM  WHERE c g|z 3dHW}t|q6Sr dictr0r r rr#ar2zError searching table: ) rrr isinstanceintlenr&rr(r)) r rr.rcursorr conditionsparams where_clauser+rowsr,r r:r search_tableBs4   4zDatabaseManager.search_tableNdata conditionc sz|4IdH}|r{ddd|D}t|}d|d|}|||IdH}|IdH} | ddk} | r{d d d|D} t||} d |d | d|}||| IdH|IdH WdIdHWd Sd |} d dd|D}t|}d|d| d|d}|||IdH|IdH WdIdHWd S1IdHswYWdStj y}zt d|WYd}~dSd}~ww)a Universal insert/update function. If the record matching the condition exists, it will be updated; otherwise, a new record will be inserted. Args: table_name: The table to operate on data: The data to insert or update condition: Key-value pairs to identify existing records. If None, will always insert. Returns: bool: Success or failure N AND cSr3r4r r kr r rr#{r6z*DatabaseManager.upsert..SELECT COUNT(*) as count FROM r<countrrcSr3r4r r5r r rr#r6UPDATE  SET TcSsg|]}dqS)?r r8r r rr#sz INSERT INTO r$z ) VALUES (r%zError in upsert operation: F) rr&keystuplevaluesrfetchonerrr(r))r rrIrJrrF where_valuesr+rCresult record_exists set_clause update_valuesr placeholdersrUr,r r rupsertis>   4zDatabaseManager.upsertorder_bylimitc s zr|4IdH\}d|}d}|r-ddd|D}t|}|d|7}|r6|d|7}|r?|d |7}|||IdH} d d| 2IdH} | IdH| WdIdHWS1IdHslwYWdStjy} zt d | gWYd} ~ Sd} ~ ww) a Retrieve rows from the specified table based on conditions. Args: table_name: The table to query condition: Key-value pairs for WHERE clause order_by: Optional ORDER BY clause (e.g., "id DESC") limit: Optional limit for number of returned rows Returns: List of dictionaries representing rows Nr;r rKcSr3r4r rLr r rr#r6z,DatabaseManager.get_rows..r<z ORDER BY z LIMIT cr=r r>r0r r rr#r2zError retrieving rows: ) rr&rSrTrUrrrr(r)) r rrJr^r_rr+rErFrCrGr,r r rget_rowss.  4zDatabaseManager.get_rowsc szc|4IdHM}ddd|D}ddd|D}t|t|}d|d|d |}|||IdH|IdH WdIdHWd S1IdHs]wYWdStjy} zt d | WYd} ~ d Sd} ~ ww) a6 Update rows in the specified table based on condition. Args: table_name: The table to update data: Key-value pairs of fields to update condition: Key-value pairs for WHERE clause Returns: Success or failure NrcSr3r4r r5r r rr#r6z/DatabaseManager.update_rows..rKcSr3r4r rLr r rr#r6rPrQr<TzError updating rows: F rr&rSrTrUrrrr(r)) r rrIrJrrZrFrUr+r,r r r update_rowss  4 zDatabaseManager.update_rowsc szN|4IdH8}ddd|D}t|}d|d|}|||IdH|IdH WdIdHWdS1IdHsHwYWdStjyj}zt d|WYd}~d Sd}~ww) a Delete rows from the specified table based on condition. Args: table_name: The table to delete from condition: Key-value pairs for WHERE clause Returns: Success or failure NrKcSr3r4r rLr r rr#r6z/DatabaseManager.delete_rows..z DELETE FROM r<TzError deleting rows: Fra)r rrJrrFrEr+r,r r r delete_rowss  4 zDatabaseManager.delete_rowsc szY|4IdHC}ddd|D}t|}d|d|}|||IdH}|IdH}|IdH|ddkWdIdHWS1IdHsSwYWdStj yu} zt d | WYd} ~ d Sd} ~ ww) a  Check if a record exists based on condition. Args: table_name: The table to check condition: Key-value pairs for WHERE clause Returns: True if record exists, False otherwise NrKcSr3r4r rLr r rr#r6z*DatabaseManager.exists..rNr<rOrzError checking existence: F rr&rSrTrUrrVrrr(r)) r rrJrrFrEr+rCrXr,r r rexistss    4 zDatabaseManager.existsc sz_|4IdHI}d|}d}|r-ddd|D}t|}|d|7}|||IdH}|IdH}|IdH|dWdIdHWS1IdHsYwYWdStj y{} zt d | WYd} ~ d Sd} ~ ww) z Count records based on condition. Args: table_name: The table to count from condition: Key-value pairs for WHERE clause Returns: Number of matching records NrNr rKcSr3r4r rLr r rr#"r6z)DatabaseManager.count..r<rOzError counting records: rrd) r rrJrr+rErFrCrXr,r r rrOs&   4zDatabaseManager.countr r+rEc sz[|4IdHE}|||IdH}|dr;dd|2IdH}|IdH|WdIdHWS|IdHgWdIdHWS1IdHsUwYWdStjyx}zt d|gWYd}~Sd}~ww)z Execute a custom query with parameters. Args: query: SQL query to execute params: Parameters for the query Returns: Results of the query NSELECTcr=r r>r0r r rr#@r2z1DatabaseManager.execute_query..zError executing query: ) rrstripupper startswithrrrr(r))r r+rErrCrGr,r r r execute_query0s" 4 zDatabaseManager.execute_queryr )NNN)r )__name__ __module__ __qualname__strrrrrrboolr-rrArrHrrr]r`rbrcrerOrrjr r r rr sD  "" ( / )  $ r ) r contextlibrtypingrrrrrrr r r r rs