
    ì)i,H                     l    d Z ddlZddlmZmZ ddlmZmZmZmZm	Z	 ddl
mZmZmZmZmZ  G d d      Zy)	ze
Data Controller for the MAB Recommendation System.
Handles all ORM queries and database operations.
    N)datetime	timedelta)ListDictAnyOptionalTuple   )
FeedbackDBProductCatalogUserDemographicsSearchHistoryDatabaseManagerc            
       n   e Zd ZdZd(defdZdedefdZd)de	de	de
eeef      fd	Zd)d
ede	de
eeef      fdZd*de	de
eeef      fdZdedefdZd
e	deeeef      fdZd+dede	de
eeef      fdZd+dede	de
eeef      fdZd,de	de
eeef      fdZdede	fdZde	deeeef      fdZdedeeeef      fdZde	de	defdZde	de
eeef      fdZdedefdZd+de	de	de
eeef      fd Zde	deeef   fd!Z d
edeeef   fd"Z!d)de	de
eeef      fd#Z"de	fd$Z#de	fd%Z$de	fd&Z%y')-DataControllerz0Handles all database operations and ORM queries.db_pathc                 X    t        |      | _        | j                  j                          y )N)r   
db_managerinit_database)selfr   s     _/Users/divyeshpatel/Desktop/sahana/Recommender/recommender_rl/recommender/db/data_controller.py__init__zDataController.__init__   s    )'2%%'    feedbackreturnc           	         	 | j                   5 }|j                         }|j                  d|j                  |j                  |j
                  |j                  |j                  f       |j                          |j                          	 ddd       y# 1 sw Y   yxY w# t        $ r}t        d|        Y d}~yd}~ww xY w)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)r   cursorexecuteuser_id
product_id
event_typesessionactive_usercommitclose	Exceptionprint)r   r   connr   es        r   add_feedbackzDataController.add_feedback   s    	D   $$h&9&98;N;N$$h&:&: 

 !  	/s34	s5   B A;B	B BB B 	B?'B::B?r   limitc                 B   	 | j                   5 }|j                         }|j                  d||f       g }|j                         D ]  }|j	                  t        |              |cddd       S # 1 sw Y   yxY w# t        $ r}t        d|        g cY d}~S d}~ww xY w)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: r   r   r   fetchallappenddictr&   r'   r   r   r+   r(   r   resultsrowr)   s           r   get_user_feedbackz DataController.get_user_feedback+   s    	D   u%' !??,CNN49- - !  	5aS9:I	;   A; AA/%	A; /A84A; 8A; ;	BBBBr    c                 B   	 | j                   5 }|j                         }|j                  d||f       g }|j                         D ]  }|j	                  t        |              |cddd       S # 1 sw Y   yxY w# t        $ r}t        d|        g cY d}~S d}~ww xY w)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-   )r   r    r+   r(   r   r2   r3   r)   s           r   get_product_feedbackz#DataController.get_product_feedbackA   s    	D   !%(* !??,CNN49- - !  	8<=I	r5   hoursc                    	 | j                   5 }|j                         }t        j                         t	        |      z
  }|j                  d|f       g }|j                         D ]  }|j                  t        |              |cddd       S # 1 sw Y   yxY w# t        $ r}t        d|        g cY d}~S d}~ww xY w)z+Get recent feedback within specified hours.)r8   aa  
                    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: )r   r   r   nowr   r   r.   r/   r0   r&   r'   )r   r8   r(   r   cutoff_timer2   r3   r)   s           r   get_recent_feedbackz"DataController.get_recent_feedbackW   s    	D&llnyu/EE   "^% !??,CNN49- - !   	7s;<I	s;   B A7B	B BB B 	B>$B93B>9B>productc                    	 | j                   5 }|j                         }|j                  d|j                  |j                  |j
                  |j                  |j                  |j                  |j                  f       |j                          	 ddd       y# 1 sw Y   yxY w# t        $ r}t        d|        Y d}~yd}~ww xY w)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)r   r   r   r    product_name
brand_namecategorypricedescription	image_urlr$   r&   r'   )r   r=   r(   r   r)   s        r   add_productzDataController.add_productp   s    	D  
 &&(<(<g>P>P$$gmmW5H5H'J[J[	  !  	.qc23	s5   B$ BBB$ B!B$ !B$ $	C-C  Cc                    	 | j                   5 }|j                         }|j                  d|f       |j                         }|rt	        |      ndcddd       S # 1 sw Y   yxY w# t
        $ r}t        d|        Y d}~yd}~ww xY w)zGet product details by ID.zX
                    SELECT * FROM product_catalog WHERE product_id = ?
                Nu   ❌ Error getting product: r   r   r   fetchoner0   r&   r'   )r   r    r(   r   r3   r)   s         r   get_productzDataController.get_product   sx    	D   ]$ oo'$'tCyT !  	/s34	5   A& AA	A& A#A& #A& &	B/BBrA   c                 B   	 | j                   5 }|j                         }|j                  d||f       g }|j                         D ]  }|j	                  t        |              |cddd       S # 1 sw Y   yxY w# t        $ r}t        d|        g cY d}~S d}~ww xY w)zGet products by category.z
                    SELECT * FROM product_catalog 
                    WHERE category = ? 
                    ORDER BY product_id 
                    LIMIT ?
                Nu(   ❌ Error getting products by category: r-   )r   rA   r+   r(   r   r2   r3   r)   s           r   get_products_by_categoryz'DataController.get_products_by_category   s    	D  
 &( !??,CNN49- - !  	<QC@AI	r5   brandc                 B   	 | j                   5 }|j                         }|j                  d||f       g }|j                         D ]  }|j	                  t        |              |cddd       S # 1 sw Y   yxY w# t        $ r}t        d|        g cY d}~S d}~ww xY w)zGet products by brand.z
                    SELECT * FROM product_catalog 
                    WHERE brand_name = ? 
                    ORDER BY product_id 
                    LIMIT ?
                Nu%   ❌ Error getting products by brand: r-   )r   rM   r+   r(   r   r2   r3   r)   s           r   get_products_by_brandz$DataController.get_products_by_brand   s    	D  
 U^% !??,CNN49- - !  	9!=>I	r5   c                 @   	 | j                   5 }|j                         }|j                  d|f       g }|j                         D ]  }|j	                  t        |              |cddd       S # 1 sw Y   yxY w# t        $ r}t        d|        g cY d}~S d}~ww xY w)z!Get all products with pagination.z
                    SELECT * FROM product_catalog 
                    ORDER BY product_id 
                    LIMIT ?
                Nu    ❌ Error getting all products: r-   r   r+   r(   r   r2   r3   r)   s          r   get_all_productszDataController.get_all_products   s    	D   X	 !??,CNN49- - !  	4QC89I	;   A: AA.$	A: .A73A: 7A: :	BBBBuserc                 h   	 | j                   5 }|j                         }|j                  d|j                  |j                  |j
                  |j                  f       |j                  }|j                          |cddd       S # 1 sw Y   yxY w# t        $ r}t        d|        Y d}~yd}~ww xY w)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: )r   r   r   namegenderageuser_cluster_id	lastrowidr$   r&   r'   )r   rT   r(   r   r   r)   s         r   add_userzDataController.add_user   s    	D   iidhh8L8LMO
 !** !  	+A3/0	s5   B A,B:	B B	B B 	B1B,,B1c                    	 | j                   5 }|j                         }|j                  d|f       |j                         }|rt	        |      ndcddd       S # 1 sw Y   yxY w# t
        $ r}t        d|        Y d}~yd}~ww xY w)zGet user details by ID.zW
                    SELECT * FROM user_demographics WHERE user_id = ?
                Nu   ❌ Error getting user: rG   )r   r   r(   r   r3   r)   s         r   get_userzDataController.get_user   sx    	D  Z! oo'$'tCyT !  	,QC01	rJ   rW   c                    	 | j                   5 }|j                         }|j                  d|f       |j                         }|rt	        |      ndcddd       S # 1 sw Y   yxY w# t
        $ r}t        d|        Y d}~yd}~ww xY w)z?Lookup a user by name (used when importing external customers).zX
                    SELECT * FROM user_demographics WHERE name = ?
                    Nu    ❌ Error getting user by name: rG   )r   rW   r(   r   r3   r)   s         r   get_user_by_namezDataController.get_user_by_name   sz    	D G	 oo'$'tCyT !  	4QC89	rJ   
cluster_idc                     	 | j                   5 }|j                         }|j                  d||f       |j                          	 ddd       y# 1 sw Y   yxY w# t        $ r}t        d|        Y d}~yd}~ww xY w)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)r   r   r   r$   r&   r'   )r   r   ra   r(   r   r)   s         r   update_user_clusterz"DataController.update_user_cluster   sr    	D   !'*	,
  !  	5aS9:	s4   A 6AA AA A 	A:"A55A:c                 @   	 | j                   5 }|j                         }|j                  d|f       g }|j                         D ]  }|j	                  t        |              |cddd       S # 1 sw Y   yxY w# t        $ r}t        d|        g cY d}~S d}~ww xY w)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-   )r   ra   r(   r   r2   r3   r)   s          r   get_users_by_clusterz#DataController.get_users_by_cluster  s    	D   !]	$ !??,CNN49- - !  	8<=I	rS   searchc                 N   	 | j                   5 }|j                         }|j                  d|j                  |j                  |j
                  |j                  f       |j                          	 ddd       y# 1 sw Y   yxY w# t        $ r}t        d|        Y d}~yd}~ww xY w)zAdd search history entry.z
                    INSERT INTO search_history (user_id, query, product_id, session_id)
                    VALUES (?, ?, ?, ?)
                NTu!   ❌ Error adding search history: F)
r   r   r   r   queryr    
session_idr$   r&   r'   )r   rf   r(   r   r)   s        r   add_search_historyz!DataController.add_search_history%  s    	D   nnfllF4E4EvGXGXY[  !  	5aS9:	s5   B A A7.B 7B <B  B 	B$BB$c                 B   	 | j                   5 }|j                         }|j                  d||f       g }|j                         D ]  }|j	                  t        |              |cddd       S # 1 sw Y   yxY w# t        $ r}t        d|        g cY d}~S d}~ww xY w)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-   r1   s           r   get_user_search_historyz&DataController.get_user_search_history4  s    	D  
 u%' !??,CNN49- - !  	;A3?@I	r5   c                 b   	 | j                   5 }|j                         }|j                  d|f       |j                         }|j                  d|f       |j	                         D ci c]  }|d   |d    }}|j                  d|f       |j                         d   }|j                  d|f       |j	                         D cg c]  }t        |       }}|rt        |      nd|||d	cddd       S c c}w c c}w # 1 sw Y   yxY w# t        $ r}	t        d
|	        i cY d}	~	S d}	~	ww xY w)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
                r!   countz
                    SELECT COUNT(*) as recent_count
                    FROM feedback_db 
                    WHERE user_id = ? AND session >= datetime('now', '-7 days')
                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
                N)	user_infoevent_countsrecent_activityfavorite_categoriesu)   ❌ Error getting user behavior summary: r   r   r   rH   r.   r0   r&   r'   )
r   r   r(   r   rp   r3   rq   rr   rs   r)   s
             r   get_user_behavior_summaryz(DataController.get_user_behavior_summaryJ  sP   -	D RU\T^_"OO-	   
 Z! LR??K\]K\CL 13w< ?K\]    Z	!
 #)//"3N"C    Z! =COO<M&N<MStCy<M#&N 5>i4$0'6+>	I !  ^( 'OE !T  	=aSABI	sZ   D AC?'C57AC?C:C?+	D 5
C??DD D 	D.D)#D.)D.c                    	 | j                   5 }|j                         }|j                  d|f       |j                         }|j                  d|f       |j	                         D ci c]  }|d   |d    }}|j                  d|f       |j                         d   }|rt        |      nd||dcddd       S c c}w # 1 sw Y   yxY w# t        $ r}t        d	|        i cY d}~S d}~ww xY w)
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
                r!   rn   z
                    SELECT COUNT(DISTINCT user_id) as unique_users
                    FROM feedback_db 
                    WHERE product_id = ?
                unique_usersN)product_inforq   rw   u'   ❌ Error getting product performance: rt   )	r   r    r(   r   rx   r3   rq   rw   r)   s	            r   get_product_performancez&DataController.get_product_performance{  s    	D SV`Ubc%0   
 !]$ LR??K\]K\CL 13w< ?K\]    !]	$
  &0@ ;GD$6D$0$01 !  ^ !:  	;A3?@I	sM   C AC 'B;7:C 1	C ;C  C	C 	C 	C/C*$C/*C/c                 @   	 | j                   5 }|j                         }|j                  d|f       g }|j                         D ]  }|j	                  t        |              |cddd       S # 1 sw Y   yxY w# t        $ r}t        d|        g cY d}~S d}~ww xY w)z Get all users from the database.z
                    SELECT * FROM user_demographics 
                    ORDER BY user_id ASC
                    LIMIT ?
                Nu   ❌ Error getting all users: r-   rQ   s          r   get_all_userszDataController.get_all_users  s    	D   X	 !??,CNN49- - !  	1!56I	rS   c                    	 | j                   5 }|j                         }|j                  d       |j                         }|r|d   ndcddd       S # 1 sw Y   yxY w# t        $ r}t        d|        Y d}~yd}~ww xY w)z%Get total number of feedback records.z)SELECT COUNT(*) as count FROM feedback_dbrn   r   Nu(   ❌ Error getting total feedback count: r   r   r   rH   r&   r'   r   r(   r   resultr)   s        r   get_total_feedbackz!DataController.get_total_feedback  sm    	DJK**0vga	 !
  	<QC@A	4   A :A	A AA A 	A?'A::A?c                    	 | j                   5 }|j                         }|j                  d       |j                         }|r|d   ndcddd       S # 1 sw Y   yxY w# t        $ r}t        d|        Y d}~yd}~ww xY w)zGet total number of users.z/SELECT COUNT(*) as count FROM user_demographicsrn   r   Nu   ❌ Error getting user count: r}   r~   s        r   get_user_countzDataController.get_user_count  sm    	DPQ**0vga	 !
  	21#67	r   c                    	 | j                   5 }|j                         }|j                  d       |j                         }|r|d   ndcddd       S # 1 sw Y   yxY w# t        $ r}t        d|        Y d}~yd}~ww xY w)zGet total number of products.z-SELECT COUNT(*) as count FROM product_catalogrn   r   Nu!   ❌ Error getting product count: r}   r~   s        r   get_product_countz DataController.get_product_count  sm    	DNO**0vga	 !
  	5aS9:	r   N)zrecommender_system.db)d   )   )2   )i  )&__name__
__module____qualname____doc__strr   r   boolr*   intr   r   r   r4   r7   r<   r   rE   r   rI   rL   rO   rR   r   r\   r^   r`   rc   re   r   rj   rl   ru   ry   r{   r   r   r    r   r   r   r      s   :( (Z D & S 4SRUXCW ,s 3 dSVX[S[nI] , d4S>6J 2> d &c htCH~.F  S $tTWY\T\~J^ (3 s DcSVhDX (c T$sCx.5I *- # " c3h(@ S Xd38n-E "3 C D  s tDcN7K * 4 s 3 TRUWZRZ^H\ ,/ /c3h /b"# "$sCx. "H3 d38n1E &
C 

 

3 
r   r   )r   sqlite3r   r   typingr   r   r   r   r	   modelsr   r   r   r   r   r   r   r   r   <module>r      s.   
  ( 3 3 E Er   