HE^ja4ddlZddlZddlmZddlmZddZy)N)get_db_connection)_logcHt} |j}|jd|jd|jdu}d}|s|jd|jdu}|jd|rc|jd|j Dcgc]}|d }}|jd|jdu}|rd }n d |vrd}nd}nd}|jd |f|j t d |n$|jd |jd}t d||dkrt d|jd|jd|jd|j Dcgc]}|d }}gd}|D]3\} } | |vs t d| d| d|jd| d| 5|jd|j t dd}|d kr[t d|jd|jd|jd|j t dd }|dkrJt d |jd!|jd"|j t d#d}|d$krt d%|jd&|j Dcgc]}|d } }d'| vr|jd(|jd)|j t d*d$}|d+kr[t d,|jd-|jd.|jd/|j t d0d+}|d1krt d2|jd3|j } g} | D]H}| j|d4|d5|d6|d7|d8|d9|d:|d;|d<|d=|d>|d?|d@dA J|jdB|jdC|jdD|jd.|jdE|jdF| D]}|jdG|d4|d5|d6|d7|d8|d:|d;|d<|d=|d>|d?|d@f |d9}|sM tj|}t|tr"|D]}|jdH|d4||d?f|jdK|j t dLd1}|dMkrt dN|jdO|j Dcgc]}|d }}gdP}|D]0\} } | |vs t d| dQ|jdR| d| 2|jdS|j t dTdM}|dUkr[t dV|jdW|jdX|jdY|j t dZdU}|d[krt d\|jd|j Dcgc]}|d }}gd]}|D]0\} }| |vs t d| d^|jd| d|2|jd_|j t d`d[}|dakrt db|jd|j Dcgc]}|d }}dc|vrt dd|jde|jdf|jdg|jdh|j t dida}|djkr[t dk|jdl|jdm|jdn|j t dodj}|dpkr}t dq|jdr|jds|jdt|jdu|jdv|j t dwdp}|dxkrJt dy|jdz|jd{|j t d|dx}|d}krt d~|jd|jd|jd|jd|jd|jd|j t dd}}|dkrJt d|jd|jd|j t dd}|dkr[t d|jd|jd|jd|j t dd}|jycc}wcc}wcc}w#t$r}t dI|d4dJ|Yd}~d}~wwxYwcc}wcc}wcc}w#|jwxYw)NzPRAGMA journal_mode=WALzKSELECT name FROM sqlite_master WHERE type='table' AND name='schema_version'rzESELECT name FROM sqlite_master WHERE type='table' AND name='products'z9CREATE TABLE schema_version (version INTEGER PRIMARY KEY)zPRAGMA table_info(products)zRSELECT name FROM sqlite_master WHERE type='table' AND name='discord_subscriptions' full_specsz/INSERT INTO schema_version (version) VALUES (?)z1Database schema version initialized to baseline: z"SELECT version FROM schema_versionz!Current database schema version: zDApplying migration 1: Create base products and price_history tables.a CREATE TABLE IF NOT EXISTS products ( product_code TEXT PRIMARY KEY, product_name TEXT, condition TEXT, first_seen TEXT, last_seen TEXT, removed_at TEXT, original_price REAL, current_price REAL, save_percent REAL, active INTEGER, specs TEXT, rating_star REAL, comment_count INTEGER, thumbnail_url TEXT, full_specs TEXT ) av CREATE TABLE IF NOT EXISTS price_history ( id INTEGER PRIMARY KEY AUTOINCREMENT, product_code TEXT, timestamp TEXT, price REAL, save_percent REAL, FOREIGN KEY(product_code) REFERENCES products(product_code) ) )) removed_atTEXT)specsr ) rating_starREAL) comment_countINTEGER) thumbnail_urlr )rr zAdding column 'z' (z) to products table.z ALTER TABLE products ADD COLUMN  z%UPDATE schema_version SET version = 1z!Migration 1 applied successfully.z,Applying migration 2: Create Discord tables.a CREATE TABLE IF NOT EXISTS discord_subscriptions ( channel_id TEXT PRIMARY KEY, guild_id TEXT NOT NULL, min_savings REAL, max_price REAL, brands TEXT, keywords TEXT, conditions TEXT, event_types TEXT DEFAULT 'all', created_at TEXT, updated_by TEXT ) z CREATE TABLE IF NOT EXISTS discord_authorized_users ( user_id TEXT PRIMARY KEY, added_by TEXT, added_at TEXT ) z%UPDATE schema_version SET version = 2z!Migration 2 applied successfully.z-Applying migration 3: Create watchlist table.a$ CREATE TABLE IF NOT EXISTS watchlists ( user_id TEXT, product_code TEXT, target_price REAL, created_at TEXT, PRIMARY KEY (user_id, product_code) ) z%UPDATE schema_version SET version = 3z!Migration 3 applied successfully.z>Applying migration 4: Add price_delta column to price_history.z PRAGMA table_info(price_history) price_deltaz5ALTER TABLE price_history ADD COLUMN price_delta REALz%UPDATE schema_version SET version = 4z!Migration 4 applied successfully.z6Applying migration 5: Create push_subscriptions table.a CREATE TABLE IF NOT EXISTS push_subscriptions ( id INTEGER PRIMARY KEY AUTOINCREMENT, endpoint TEXT UNIQUE NOT NULL, p256dh TEXT NOT NULL, auth TEXT NOT NULL, user_agent TEXT, watchlist TEXT DEFAULT '[]', notify_price_drops INTEGER DEFAULT 1, notify_new_listings INTEGER DEFAULT 1, notify_back_in_stock INTEGER DEFAULT 1, notify_watchlist_only INTEGER DEFAULT 0, min_drop_percent REAL DEFAULT 0.0, created_at TEXT, last_used TEXT ) zLCREATE INDEX IF NOT EXISTS idx_push_endpoint ON push_subscriptions(endpoint)z%UPDATE schema_version SET version = 5z!Migration 5 applied successfully.zTApplying migration 6: Reconstruct push_subscriptions, create subscription_watchlist.a. SELECT id, endpoint, p256dh, auth, user_agent, watchlist, notify_price_drops, notify_new_listings, notify_back_in_stock, notify_watchlist_only, min_drop_percent, created_at, last_used FROM push_subscriptions idendpointp256dhauth user_agent watchlistnotify_price_dropsnotify_new_listingsnotify_back_in_stocknotify_watchlist_onlymin_drop_percent created_at last_used) rrrrrrrrrr r!r"r#z&DROP INDEX IF EXISTS idx_push_endpointz'DROP TABLE IF EXISTS push_subscriptionsa CREATE TABLE IF NOT EXISTS push_subscriptions ( id INTEGER PRIMARY KEY AUTOINCREMENT, endpoint TEXT UNIQUE NOT NULL, p256dh TEXT NOT NULL, auth TEXT NOT NULL, user_agent TEXT, notify_price_drops INTEGER DEFAULT 1, notify_new_listings INTEGER DEFAULT 1, notify_back_in_stock INTEGER DEFAULT 1, notify_watchlist_only INTEGER DEFAULT 0, min_drop_percent REAL DEFAULT 0.0, created_at TEXT, last_used TEXT ) a CREATE TABLE IF NOT EXISTS subscription_watchlist ( id INTEGER PRIMARY KEY AUTOINCREMENT, subscription_id INTEGER NOT NULL, product_code TEXT NOT NULL, created_at TEXT, FOREIGN KEY(subscription_id) REFERENCES push_subscriptions(id) ON DELETE CASCADE, UNIQUE(subscription_id, product_code) ) zYCREATE INDEX IF NOT EXISTS idx_sub_watchlist_code ON subscription_watchlist(product_code)av INSERT INTO push_subscriptions ( id, endpoint, p256dh, auth, user_agent, notify_price_drops, notify_new_listings, notify_back_in_stock, notify_watchlist_only, min_drop_percent, created_at, last_used ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) z INSERT OR IGNORE INTO subscription_watchlist (subscription_id, product_code, created_at) VALUES (?, ?, ?) z9Warning: Failed to parse watchlist JSON for subscription z: z%UPDATE schema_version SET version = 6z!Migration 6 applied successfully.zPApplying migration 7: Add individual ping role columns to discord_subscriptions.z(PRAGMA table_info(discord_subscriptions)))ping_role_added_idr )ping_role_removed_idr )ping_role_price_drop_idr )ping_role_price_hike_idr z!' to discord_subscriptions table.z-ALTER TABLE discord_subscriptions ADD COLUMN z%UPDATE schema_version SET version = 7z!Migration 7 applied successfully.z;Applying migration 8: Create stock_history table and index.a CREATE TABLE IF NOT EXISTS stock_history ( id INTEGER PRIMARY KEY AUTOINCREMENT, product_code TEXT, event_type TEXT NOT NULL, timestamp TEXT NOT NULL, price REAL, FOREIGN KEY(product_code) REFERENCES products(product_code) ) zPCREATE INDEX IF NOT EXISTS idx_stock_history_code ON stock_history(product_code)z%UPDATE schema_version SET version = 8z!Migration 8 applied successfully. zAApplying migration 9: Add oscillation-filter columns to products.))stability_countzINTEGER DEFAULT 0) pending_stateTEXT DEFAULT NULL)pending_removal_sincer-z' to products table.z%UPDATE schema_version SET version = 9z!Migration 9 applied successfully. zIApplying migration 10: Add debounce_duration and message tracking tables.debounce_durationz4Adding column 'debounce_duration' to products table.zFALTER TABLE products ADD COLUMN debounce_duration INTEGER DEFAULT NULLa CREATE TABLE IF NOT EXISTS discord_sent_messages ( product_code TEXT NOT NULL, channel_id TEXT NOT NULL, message_id TEXT NOT NULL, event_type TEXT NOT NULL, sent_at TEXT NOT NULL, PRIMARY KEY (product_code, channel_id) ) a CREATE TABLE IF NOT EXISTS telegram_sent_messages ( product_code TEXT NOT NULL, chat_id TEXT NOT NULL, message_id TEXT NOT NULL, event_type TEXT NOT NULL, message_type TEXT NOT NULL, sent_at TEXT NOT NULL, PRIMARY KEY (product_code, chat_id) ) z&UPDATE schema_version SET version = 10z"Migration 10 applied successfully. z5Applying migration 11: Create removal_attempts table.z CREATE TABLE IF NOT EXISTS removal_attempts ( id INTEGER PRIMARY KEY AUTOINCREMENT, product_code TEXT, timestamp TEXT ) zVCREATE INDEX IF NOT EXISTS idx_removal_attempts_code ON removal_attempts(product_code)z&UPDATE schema_version SET version = 11z"Migration 11 applied successfully. zEApplying migration 12: Create scrape_stats and ghost tracking tables.a^ CREATE TABLE IF NOT EXISTS scrape_stats ( id INTEGER PRIMARY KEY CHECK (id = 1), listed_count INTEGER NOT NULL, purchasable_count INTEGER NOT NULL, ghost_count INTEGER NOT NULL, updated_at TEXT NOT NULL ) a CREATE TABLE IF NOT EXISTS ghosts_current ( product_code TEXT PRIMARY KEY, product_name TEXT, price REAL, first_ghosted_at TEXT NOT NULL ) a CREATE TABLE IF NOT EXISTS ghost_events ( id INTEGER PRIMARY KEY AUTOINCREMENT, timestamp TEXT NOT NULL, product_code TEXT NOT NULL, product_name TEXT, price REAL, event_type TEXT NOT NULL CHECK (event_type IN ('appeared', 'resolved')) ) zNCREATE INDEX IF NOT EXISTS idx_ghost_events_code ON ghost_events(product_code)z&UPDATE schema_version SET version = 12z"Migration 12 applied successfully. z0Applying migration 13: Create psref_specs table.a CREATE TABLE IF NOT EXISTS psref_specs ( machine_type TEXT PRIMARY KEY, product_key TEXT, page_url TEXT, spec_data TEXT, updated_at TEXT ) z&UPDATE schema_version SET version = 13z"Migration 13 applied successfully.zcApplying migration 14: Consolidate discord_sent_messages/telegram_sent_messages into sent_messages.a CREATE TABLE IF NOT EXISTS sent_messages ( platform TEXT NOT NULL, product_code TEXT NOT NULL, target_id TEXT NOT NULL, message_id TEXT NOT NULL, event_type TEXT NOT NULL, message_type TEXT, sent_at TEXT NOT NULL, PRIMARY KEY (platform, product_code, target_id) ) a6 INSERT OR REPLACE INTO sent_messages (platform, product_code, target_id, message_id, event_type, message_type, sent_at) SELECT 'discord', product_code, channel_id, message_id, event_type, NULL, sent_at FROM discord_sent_messages a= INSERT OR REPLACE INTO sent_messages (platform, product_code, target_id, message_id, event_type, message_type, sent_at) SELECT 'telegram', product_code, chat_id, message_id, event_type, message_type, sent_at FROM telegram_sent_messages z DROP TABLE discord_sent_messagesz!DROP TABLE telegram_sent_messagesz&UPDATE schema_version SET version = 14z"Migration 14 applied successfully.zCApplying migration 15: Create index on price_history(product_code).zPCREATE INDEX IF NOT EXISTS idx_price_history_code ON price_history(product_code)z&UPDATE schema_version SET version = 15z"Migration 15 applied successfully.zOApplying migration 16: Create timestamp indexes on stock_history/price_history.zWCREATE INDEX IF NOT EXISTS idx_stock_history_timestamp ON stock_history(timestamp DESC)zWCREATE INDEX IF NOT EXISTS idx_price_history_timestamp ON price_history(timestamp DESC)z&UPDATE schema_version SET version = 16z"Migration 16 applied successfully.)rcursorexecutefetchonefetchallcommitrappendjsonloads isinstancelist Exceptionclose)connr7has_version_tablecurrent_versionhas_products_tablerowcolumns has_discord migrationscol_namecol_type ph_columnsrows existing_subsrsubwatchlist_jsoncodescodee sub_columnsnew_cols prod_columnscol_defs (/home/container/backend/db/migrations.pyrun_migrationsr[s  DP 01 de"OO-T9 NNb c!'!2$!>  NNV W!<=-3__->?->c3q6->?st$oo/t; &'O!W,&'O&'O"# NNLN` a KKM D_DUV W NN? @$oo/2O 00A BC Q  W X NN ( NN   NN8 9)/):;):#s1v):G;J'1"(7*?8*CzAUVWNN%EhZqQYPZ#[\'1 NNB C KKM 4 5O Q  ? @ NN   NN  NNB C KKM 4 5O Q  @ A NN  NNB C KKM 4 5O Q  Q R NN= >,2OO,=>,=S#a&,=J>J.VW NNB C KKM 4 5O Q  I J NN " NN^  NNB C KKM 4 5O Q  g h NN  ??$DM$$D' !* kfI"#L/!";*+,@*A+,-B+C,-.D,E-./F-G()*<(="#L/!";&$ NNC D NND E NN  NNi j NN   NNv w% Is:H s6{CP\L],-s3H/I3OeKf/0#6H2I3|K\^abm^n "%[!1! k $ > :%eT2(- &0$'*$is<7H%I!K).%%4 NNB C KKM 4 5O Q  c d NNE F-3__->?->c3q6->K?H'/"(;.?8*4UVWNN%RS[R\\]^f]g#hi'/ NNB C KKM 4 5O Q  N O NN   NNm n NNB C KKM 4 5O Q  T U NN8 9.4oo.?@.?sCF.?L@H &.!'</?8*4HIJNN%EhZqQXPY#Z[&. NNB C KKM 4 5O R  \ ] NN8 9.4oo.?@.?sCF.?L@",6KLgh NN   NN   NNC D KKM 5 6 O R  H I NN  NNs t NNC D KKM 5 6 O R  X Y NN  NN  NN   NNk l NNC D KKM 5 6 O R  C D NN  NNC D KKM 5 6 O R  v w NN   NN  NN  NN= > NN> ? NNC D KKM 5 6 O R  V W NNm n NNC D KKM 5 6 O R  b c NNt u NNt u NNC D KKM 5 6 O u@zrbs 3'Rra