o ì)i,H@sfdZddlZddlmZmZddlmZmZmZmZm Z ddl m Z m Z m Z mZmZGdddZdS) ze Data Controller for the MAB Recommendation System. Handles all ORM queries and database operations. N)datetime timedelta)ListDictAnyOptionalTuple) FeedbackDBProductCatalogUserDemographics SearchHistoryDatabaseManagerc @sheZdZdZdDdefddZdedefdd ZdEd e d e de e ee ffd dZ dEded e de e ee ffddZdFde de e ee ffddZdedefddZde dee ee ffddZdGded e de e ee ffddZdGded e de e ee ffd d!ZdHd e de e ee ffd#d$Zd%ede fd&d'Zd e dee ee ffd(d)Zd*edee ee ffd+d,Zd e d-e defd.d/Zd-e de e ee ffd0d1Zd2edefd3d4ZdGd e d e de e ee ffd5d6Zd e de ee ffd7d8Z dede ee ffd9d:Z!dEd e de e ee ffd;d<Z"de fd=d>Z#de fd?d@Z$de fdAdBZ%dCS)IDataControllerz0Handles all database operations and ORM queries.recommender_system.dbdb_pathcCst||_|jdS)N)r db_manager init_database)selfrr_/Users/divyeshpatel/Desktop/sahana/Recommender/recommender_rl/recommender/db/data_controller.py__init__s zDataController.__init__feedbackreturnc Csz5|j'}|}|d|j|j|j|j|jf||  WdWdS1s.wYWdSt yO}zt d|WYd}~dSd}~ww)z"Add user feedback to the database.z INSERT INTO feedback_db (user_id, product_id, event_type, session, active_user) VALUES (?, ?, ?, ?, ?) NTu❌ Error adding feedback: F) rcursorexecuteuser_id product_id event_typesession active_usercommitclose Exceptionprint)rrconnrerrr add_feedbacks" ( zDataController.add_feedbackdrlimitc Cz5|j'}|}|d||fg}|D] }|t|q|WdWS1s.wYWdStyP}ztd|gWYd}~Sd}~ww)z)Get feedback history for a specific user.aD SELECT f.*, p.product_name, p.brand_name, p.category, p.price FROM feedback_db f JOIN product_catalog p ON f.product_id = p.product_id WHERE f.user_id = ? ORDER BY f.session DESC LIMIT ? Nu!❌ Error getting user feedback: rrrfetchallappenddictr#r$rrr)r%rresultsrowr&rrrget_user_feedback+  (z DataController.get_user_feedbackrc Cr*)z,Get feedback history for a specific product.a% SELECT f.*, u.name as user_name FROM feedback_db f JOIN user_demographics u ON f.user_id = u.user_id WHERE f.product_id = ? ORDER BY f.session DESC LIMIT ? Nu$❌ Error getting product feedback: r+)rrr)r%rr0r1r&rrrget_product_feedbackAr3z#DataController.get_product_feedbackhoursc Csz=|j/}|}tt|d}|d|fg}|D] }|t|q|WdWS1s6wYWdSt yX}zt d|gWYd}~Sd}~ww)z+Get recent feedback within specified hours.)r6aa SELECT f.*, p.product_name, u.name as user_name FROM feedback_db f JOIN product_catalog p ON f.product_id = p.product_id JOIN user_demographics u ON f.user_id = u.user_id WHERE f.session >= ? ORDER BY f.session DESC Nu#❌ Error getting recent feedback: ) rrrnowrrr,r-r.r#r$)rr6r%rZ cutoff_timer0r1r&rrrget_recent_feedbackWs" (z"DataController.get_recent_feedbackproductc Csz5|j'}|}|d|j|j|j|j|j|j|j f|  WdWdS1s.wYWdSt yO}zt d|WYd}~dSd}~ww)z!Add a new product to the catalog.z INSERT OR REPLACE INTO product_catalog (product_id, product_name, brand_name, category, price, description, image_url) VALUES (?, ?, ?, ?, ?, ?, ?) NTu❌ Error adding product: F) rrrr product_name brand_namecategoryprice description image_urlr!r#r$)rr9r%rr&rrr add_productps  ( zDataController.add_productc Cz.|j }|}|d|f|}|rt|ndWdWS1s'wYWdStyH}ztd|WYd}~dSd}~ww)zGet product details by ID.zX SELECT * FROM product_catalog WHERE product_id = ? Nu❌ Error getting product: rrrfetchoner.r#r$)rrr%rr1r&rrr get_product(zDataController.get_product2r<c Cr*)zGet products by category.z SELECT * FROM product_catalog WHERE category = ? ORDER BY product_id LIMIT ? Nu(❌ Error getting products by category: r+)rr<r)r%rr0r1r&rrrget_products_by_category  ( z'DataController.get_products_by_categorybrandc Cr*)zGet products by brand.z SELECT * FROM product_catalog WHERE brand_name = ? ORDER BY product_id LIMIT ? Nu%❌ Error getting products by brand: r+)rrIr)r%rr0r1r&rrrget_products_by_brandrHz$DataController.get_products_by_brandc Cz4|j&}|}|d|fg}|D] }|t|q|WdWS1s-wYWdStyO}ztd|gWYd}~Sd}~ww)z!Get all products with pagination.z SELECT * FROM product_catalog ORDER BY product_id LIMIT ? Nu ❌ Error getting all products: r+rr)r%rr0r1r&rrrget_all_products  ( zDataController.get_all_productsuserc Csz2|j$}|}|d|j|j|j|jf|j}||WdWS1s+wYWdSt yL}zt d|WYd}~dSd}~ww)z&Add a new user and return the user ID.z INSERT INTO user_demographics (name, gender, age, user_cluster_id) VALUES (?, ?, ?, ?) Nu❌ Error adding user: ) rrrnamegenderageuser_cluster_id lastrowidr!r#r$)rrPr%rrr&rrradd_users( zDataController.add_userc CrA)zGet user details by ID.zW SELECT * FROM user_demographics WHERE user_id = ? Nu❌ Error getting user: rB)rrr%rr1r&rrrget_userrEzDataController.get_userrRc CrA)z?Lookup a user by name (used when importing external customers).zX SELECT * FROM user_demographics WHERE name = ? Nu ❌ Error getting user by name: rB)rrRr%rr1r&rrrget_user_by_names( zDataController.get_user_by_name cluster_idc Csz)|j}|}|d||f| WdWdS1s"wYWdStyC}ztd|WYd}~dSd}~ww)z!Update user's cluster assignment.z UPDATE user_demographics SET user_cluster_id = ?, last_active = CURRENT_TIMESTAMP WHERE user_id = ? NTu!❌ Error updating user cluster: F)rrrr!r#r$)rrrZr%rr&rrrupdate_user_clusters( z"DataController.update_user_clusterc CrL)z$Get all users in a specific cluster.z SELECT * FROM user_demographics WHERE user_cluster_id = ? ORDER BY user_id Nu$❌ Error getting users by cluster: r+)rrZr%rr0r1r&rrrget_users_by_clusterrOz#DataController.get_users_by_clustersearchc Csz/|j!}|}|d|j|j|j|jf| WdWdS1s(wYWdStyI}zt d|WYd}~dSd}~ww)zAdd search history entry.z INSERT INTO search_history (user_id, query, product_id, session_id) VALUES (?, ?, ?, ?) NTu!❌ Error adding search history: F) rrrrqueryr session_idr!r#r$)rr]r%rr&rrradd_search_history%s(z!DataController.add_search_historyc Cr*)z'Get search history for a specific user.z SELECT * FROM search_history WHERE user_id = ? ORDER BY timestamp DESC LIMIT ? Nu'❌ Error getting user search history: r+r/rrrget_user_search_history4rHz&DataController.get_user_search_historyc Csz`|jR}|}|d|f|}|d|fdd|D}|d|f|d}|d|fdd |D}|rGt|nd |||d Wd WS1sYwYWd Sty{}ztd |iWYd }~Sd }~ww) z(Get comprehensive user behavior summary.z1SELECT * FROM user_demographics WHERE user_id = ?z SELECT event_type, COUNT(*) as count FROM feedback_db WHERE user_id = ? GROUP BY event_type cSi|] }|d|dqSrcountr.0r1rrr [z.z SELECT COUNT(*) as recent_count FROM feedback_db WHERE user_id = ? AND session >= datetime('now', '-7 days') Z recent_countaO SELECT p.category, COUNT(*) as count FROM feedback_db f JOIN product_catalog p ON f.product_id = p.product_id WHERE f.user_id = ? GROUP BY p.category ORDER BY count DESC LIMIT 5 cSsg|]}t|qSr)r.rerrr osz.N) user_info event_countsrecent_activityfavorite_categoriesu)❌ Error getting user behavior summary: rrrrCr,r.r#r$) rrr%rrjrkrlrmr&rrrget_user_behavior_summaryJs8  (*z(DataController.get_user_behavior_summaryc CszO|jA}|}|d|f|}|d|fdd|D}|d|f|d}|r7t|nd||dWdWS1sHwYWdStyj}ztd |iWYd}~Sd}~ww) z Get product performance metrics.z2SELECT * FROM product_catalog WHERE product_id = ?z SELECT event_type, COUNT(*) as count FROM feedback_db WHERE product_id = ? GROUP BY event_type cSrbrcrrerrrrgrhz:DataController.get_product_performance..z SELECT COUNT(DISTINCT user_id) as unique_users FROM feedback_db WHERE product_id = ? unique_usersN) product_inforkrpu'❌ Error getting product performance: rn)rrr%rrqrkrpr&rrrget_product_performance{s. (z&DataController.get_product_performancec CrL)z Get all users from the database.z SELECT * FROM user_demographics ORDER BY user_id ASC LIMIT ? Nu❌ Error getting all users: r+rMrrr get_all_usersrOzDataController.get_all_usersc Cz,|j}|}|d|}|r|dndWdWS1s%wYWdStyF}ztd|WYd}~dSd}~ww)z%Get total number of feedback records.z)SELECT COUNT(*) as count FROM feedback_dbrdrNu(❌ Error getting total feedback count: rrrrCr#r$rr%rresultr&rrrget_total_feedback (z!DataController.get_total_feedbackc Crt)zGet total number of users.z/SELECT COUNT(*) as count FROM user_demographicsrdrNu❌ Error getting user count: rurvrrrget_user_countryzDataController.get_user_countc Crt)zGet total number of products.z-SELECT COUNT(*) as count FROM product_catalogrdrNu!❌ Error getting product count: rurvrrrget_product_countryz DataController.get_product_countN)r)r()r5)rF)rK)&__name__ __module__ __qualname____doc__strrr boolr'intrrrr2r4r8r r@rrDrGrJrNr rWrXrYr[r\r r`rarorrrsrxrzr{rrrrrs2$$ $$ $1 $  r)rsqlite3rrtypingrrrrrmodelsr r r r rrrrrrs