UKj$PdZddlmZddlZddlZddlmZddlmZGddZ y)z2SQLite + FTS5 catalog index for fast offer lookup.) annotationsN)Path)OfferceZdZdZdddZddZddZddZddZddZ ddZ dd Z d dd Z dd Z dd ZddZy ) CatalogIndexz5Local index of seller offers backed by SQLite + FTS5.c|dk7r&t|jjddtj||_tj |j _|jy)N:memory:T)parentsexist_ok) rparentmkdirsqlite3connectconnRow row_factory _init_schema)selfdb_paths F/var/www/html/agent.samochodowy/src/agent_samochodowy/matcher/index.py__init__zCatalogIndex.__init__sS j M & &td & COOG, '   c|jj}|jd|jd|jj y)NaE CREATE TABLE IF NOT EXISTS offers ( offer_id TEXT PRIMARY KEY, platform TEXT NOT NULL, url TEXT NOT NULL, title TEXT NOT NULL, price REAL, currency TEXT DEFAULT 'PLN', qty INTEGER DEFAULT 1, brand TEXT, model TEXT, category TEXT, engine_code TEXT, oe_numbers_json TEXT DEFAULT '[]', is_part INTEGER DEFAULT 1 ); CREATE TABLE IF NOT EXISTS oe_index ( oe_number TEXT NOT NULL, offer_id TEXT NOT NULL, UNIQUE(oe_number, offer_id) ); CREATE INDEX IF NOT EXISTS idx_oe ON oe_index(oe_number); CREATE TABLE IF NOT EXISTS engine_index ( engine_code TEXT NOT NULL, offer_id TEXT NOT NULL, UNIQUE(engine_code, offer_id) ); CREATE INDEX IF NOT EXISTS idx_engine ON engine_index(engine_code); z CREATE VIRTUAL TABLE IF NOT EXISTS offers_fts USING fts5( offer_id UNINDEXED, title, category, brand, model, engine_code, tokenize='unicode61 remove_diacritics 2' ) )rcursor executescriptexecutecommitrcurs rrzCatalogIndex._init_schemasMii    @   rc@|jj}tj|j}|j d|j |j|j|j|j|j|j|j|j|j|j |t#|j$f |j d|j f|jD] }|j d||j f"|j d|j f|j r6|j d|j j'|j f|jj)y)z6Insert or update a single offer and its index entries.a INSERT INTO offers (offer_id, platform, url, title, price, currency, qty, brand, model, category, engine_code, oe_numbers_json, is_part) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON CONFLICT(offer_id) DO UPDATE SET platform=excluded.platform, url=excluded.url, title=excluded.title, price=excluded.price, currency=excluded.currency, qty=excluded.qty, brand=excluded.brand, model=excluded.model, category=excluded.category, engine_code=excluded.engine_code, oe_numbers_json=excluded.oe_numbers_json, is_part=excluded.is_part z'DELETE FROM oe_index WHERE offer_id = ?zBINSERT OR IGNORE INTO oe_index (oe_number, offer_id) VALUES (?, ?)z+DELETE FROM engine_index WHERE offer_id = ?zHINSERT OR IGNORE INTO engine_index (engine_code, offer_id) VALUES (?, ?)N)rrjsondumps oe_numbersroffer_idplatformurltitlepricecurrencyqtybrandmodelcategory engine_codeintis_partupperr)rofferroe_jsonoes r upsert_offerzCatalogIndex.upsert_offer@s:ii **U--.   NNENNEIIu{{ KK KKenn   wEMM(:   $ =?PQ"" B KKTU^^$   AENNCTU    KKZ""((*ENN;  rcn|D] }|js|j|"|jy)z Bulk-load offers into the index.N)r0r5 _rebuild_fts)roffersr2s rbuild_from_offerszCatalogIndex.build_from_offershs4 )E}}!!%( ) rc|jj}|jd|jd|jjy)z-Rebuild the FTS5 index from the offers table.zDELETE FROM offers_ftsz INSERT INTO offers_fts(offer_id, title, category, brand, model, engine_code) SELECT offer_id, title, category, brand, model, engine_code FROM offers WHERE is_part = 1 N)rrrrrs rr7zCatalogIndex._rebuild_ftsosDii  ,-   rcg}|D]l}|jt|d|d|d|d|d|d|d|d|d |d |d tj|d t |d  n|S)Nr$r%r&r'r(r)r*r+r,r-r.oe_numbers_jsonr0) r$r%r&r'r(r)r*r+r,r-r.r#r0)appendrr!loadsbool)rrowsresultrows r_rows_to_offerszCatalogIndex._rows_to_offerszs C MM%ZZJ'l'lZJ'l'lZ .::c*;&<=S^,    rc|jj}|jd|f|j|j S)zExact OE number lookup.z SELECT o.* FROM offers o JOIN oe_index i ON o.offer_id = i.offer_id WHERE i.oe_number = ? AND o.is_part = 1 )rrrrCfetchall)rr4rs r search_by_oezCatalogIndex.search_by_oesEii   U   ##CLLN33rc|jj}|jd|jf|j |j S)zExact engine code lookup.z SELECT o.* FROM offers o JOIN engine_index i ON o.offer_id = i.offer_id WHERE i.engine_code = ? AND o.is_part = 1 )rrrr1rCrE)rcoders rsearch_by_engine_codez"CatalogIndex.search_by_engine_codesKii   jjl_   ##CLLN33rNcddlm}dg}g}|r"|jd|j||r%|jd|j|||g|r||}g}|D](} |jd|jd| d*|D]E} t | j dks |jd |jd | d G|jd d j |dt |dk(rgS|jj} | jddj |d||j| jS)uAttribute-based search (brand, model, category). Category matching uses synonym expansion: a query for "sprężarka powietrza" also matches offers with category "compressor" or title containing "SPRĘŻARKA". r)expand_category_synonymsz is_part = 1zLOWER(brand) = LOWER(?)zc(LOWER(model) = LOWER(?) OR LOWER(model) LIKE LOWER(?) || '%' OR LOWER(?) LIKE LOWER(model) || '%')zLOWER(category) LIKE LOWER(?)%zLOWER(title) LIKE LOWER(?)z% z %( OR )zSELECT * FROM offers WHERE z AND z LIMIT 50) #agent_samochodowy.matcher.normalizerKr=extendlensplitjoinrrrrCrE) rr+r,r-rK conditionsparamssynonyms cat_clausessynrs rsearch_by_attributesz!CatalogIndex.search_by_attributessm Q#_     7 8 MM%      MM5%/ 0 /9HK *""#BC #aj) *   0syy{#q(&&'CDMMBse2,/ 0   &++k":!;1= > z?a Iii  )',,z*B)C9 Mv ##CLLN33rcDddl}|jd||j}|sgSdj|dd}|jj } |j d|f|j|jS#tj$rgcYSwxYw)z7FTS5 full-text search on title, category, brand, model.rNz\w+rO z SELECT o.* FROM offers_fts f JOIN offers o ON f.offer_id = o.offer_id WHERE offers_fts MATCH ? AND o.is_part = 1 LIMIT 20 ) refindallUNICODErVrrrrCrErOperationalError)rtextr_tokens fts_queryrs r search_fuzzyzCatalogIndex.search_fuzzys FD"**5IKKs , ii   KK    '' 7 7'' I s1BBBc~|jj}|jd|jdS)Nz-SELECT COUNT(*) FROM offers WHERE is_part = 1r)rrrfetchoners rcountzCatalogIndex.counts2ii  CD||~a  rc8|jjy)N)rclose)rs rrkzCatalogIndex.closes r)r )rstrreturnNone)rmrn)r2rrmrn)r8 list[Offer]rmrn)r@zlist[sqlite3.Row]rmro)r4rlrmro)rHrlrmro)NNN)r+ str | Noner,rpr-rprmro)rcrlrmro)rmr/)__name__ __module__ __qualname____doc__rrr5r9r7rCrFrIr\rfrirkrrrr su?(T&P (44! # 242424 24  24h*! rr) rt __future__rr!rpathlibragent_samochodowy.modelsrrrurrrys#8" *eer