
    'j5                     &   U d Z ddlZddlZddlmZmZ ddlZddlmZ ddlm	Z
 daeej                 ed<   d(dZdej        fd	Z	 	 d)dededee         defdZd*dededededef
dZdedee         fdZ	 d+dededee         defdZ	 d+dedee         defdZ	 	 	 	 	 d,dedededee         dee         dee         d ee         dee         defd!Zdedee         fd"Zd#ed$edee         fd%Zdedee         fd&Zd#edee         fd'ZdS )-zConversation history database module.

Manages conversation and conversation_message tables for chat history.
Uses the same database connection as authentication.
    N)OptionalList)datetime)logger_poolreturnc                  ^   K   t           #t                                            d{V  da dS dS )z9Close the shared pool. Call once at application shutdown.N)r   close     K/var/www/html/ai-enterprise-brain/backend/chanakya/conversation/database.py
close_poolr      s?       kkmm r   c            	        K   t           t          j        t          j                            dd          t          t          j                            dd                    t          j        d         t          j        d         t          j        d         d	d
           d{V a t           t          d          t           S )zEnsure the pool is initialized, initializing it lazily if needed.
    
    Returns:
        The connection pool
        
    Raises:
        RuntimeError: If initialization fails
    NDB_HOST	localhostDB_PORT5432DB_NAMEDB_USERDB_PASSWORD   
   )hostportdatabaseuserpasswordmin_sizemax_sizezMConversation database pool not initialized. Check your environment variables.)r   asyncpgcreate_poolosenvirongetintRuntimeErrorr   r   r   ensure_poolr'      s       })	;77RZ^^Iv6677Z	*I&Z.
 
 
 
 
 
 
 
 
 }[
 
 	
 Lr   New Chatuser_idtitle
created_byc                   K   t                       d{V }|| }|                                4 d{V }|                    d| ||           d{V }ddd          d{V  n# 1 d{V swxY w Y   t          |          S )a=  Create a new conversation.
    
    Args:
        user_id: ID of the user creating the conversation
        title: Initial title (defaults to "New Chat")
        created_by: User ID who created it (defaults to user_id)
    
    Returns:
        Created conversation dict with id, user_id, title, created_at, etc.
    Nz
            INSERT INTO conversation (user_id, title, status, created_by, updated_by)
            VALUES ($1, $2, 'active', $3, $3)
            RETURNING id, user_id, title, status, created_at, updated_at
            r'   acquirefetchrowdict)r)   r*   r+   poolconnrows         r   create_conversationr4   =   s5      D
||~~ 
 
 
 
 
 
 
MM
 UJ
 
 
 
 
 
 
 

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 99s   A&&
A03A0Fr   include_deletedlimitoffsetc                 j  K   t                       d{V }|rdnd}|                                4 d{V }|                    d| |            d{V }|                    d| d| ||           d{V }ddd          d{V  n# 1 d{V swxY w Y   d |D             }	|	|||z   |k     dS )	a  Get paginated conversations for a user with preview data.

    Each conversation includes ``last_message`` (last assistant reply preview,
    truncated to 200 chars) and ``response_count`` so the caller does NOT need
    to make individual per-conversation requests.

    Args:
        user_id: User ID to get conversations for
        include_deleted: Whether to include deleted conversations
        limit: Maximum number of conversations to return (default 10)
        offset: Number of conversations to skip for pagination (default 0)

    Returns:
        Dict with keys:
            - conversations: list of enriched conversation dicts
            - total: total count of conversations for this user
            - has_more: whether more pages exist after this one
    Nzstatus IN ('active', 'deleted')zstatus = 'active'z9SELECT COUNT(*) FROM conversation WHERE user_id = $1 AND a  
            SELECT 
                c.id, c.user_id, c.title, c.status, c.created_at, c.updated_at,
                (
                    SELECT COUNT(*) 
                    FROM conversation_message 
                    WHERE conversation_id = c.id
                      AND message_author = 'assistant'
                ) AS response_count,
                (
                    SELECT LEFT(message, 200)
                    FROM conversation_message
                    WHERE conversation_id = c.id
                      AND message_author = 'assistant'
                    ORDER BY created_at ASC
                    LIMIT 1
                ) AS last_message
            FROM conversation c
            WHERE c.user_id = $1 AND zS
            ORDER BY c.updated_at DESC
            LIMIT $2 OFFSET $3
            c                 ,    g | ]}t          |          S r   r0   .0r3   s     r   
<listcomp>z%get_conversations.<locals>.<listcomp>   s    ///3T#YY///r   )conversationstotalhas_more)r'   r.   fetchvalfetch)
r)   r5   r6   r7   r1   status_filterr2   r?   rowsr>   s
             r   get_conversationsrE   ^   s     & D9Ha55NaM||~~ !
 !
 !
 !
 !
 !
 !
==WWW
 
 
 
 
 
 
 

 ZZ$ '4%  , 3
 
 
 
 
 
 
 
!
 !
 !
 !
 !
 !
 !
 !
 !
 !
 !
 !
 !
 !
 !
 !
 !
 !
 !
 !
 !
 !
 !
 !
 !
 !
 !
F 0/$///M&e^u,  s   AB
BBconversation_idc                   K   t                       d{V }|                                4 d{V }|                    d|            d{V }ddd          d{V  n# 1 d{V swxY w Y   |rt          |          ndS )zGet a single conversation by ID.
    
    Args:
        conversation_id: Conversation ID
    
    Returns:
        Conversation dict or None if not found
    Nz
            SELECT id, user_id, title, status, created_at, updated_at
            FROM conversation
            WHERE id = $1
            r-   )rF   r1   r2   r3   s       r   get_conversationrH      s/      D||~~ 
 
 
 
 
 
 
MM
 
 
 
 
 
 
 
 

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 %4999%   A  
A*-A*
updated_byc                 "  K   t                       d{V }|                                4 d{V }|                    d|||            d{V }ddd          d{V  n# 1 d{V swxY w Y   |                                d         dk    S )zUpdate conversation title.
    
    Args:
        conversation_id: Conversation ID
        title: New title
        updated_by: User ID who updated it
    
    Returns:
        True if updated, False if not found
    Nz
            UPDATE conversation
            SET title = $1, updated_at = NOW(), updated_by = $2
            WHERE id = $3
            1r'   r.   executesplit)rF   r*   rJ   r1   r2   results         r   update_conversation_titlerR      s6      D||~~ 
 
 
 
 
 
 
||
 :
 
 
 
 
 
 
 

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 <<>>"$$s   A""
A,/A,
deleted_byc                    K   t                       d{V }|                                4 d{V }|                    d||            d{V }ddd          d{V  n# 1 d{V swxY w Y   |                                d         dk    S )zSoft delete a conversation.
    
    Args:
        conversation_id: Conversation ID to delete
        deleted_by: User ID who deleted it
    
    Returns:
        True if deleted, False if not found
    Nz
            UPDATE conversation
            SET status = 'deleted', 
                deleted_at = NOW(), 
                updated_at = NOW(), 
                updated_by = $1
            WHERE id = $2
            rL   rM   rN   )rF   rS   r1   r2   rQ   s        r   delete_conversationrU      s4      D||~~ 
 
 
 
 
 
 
|| 

 

 

 

 

 

 

 


 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 <<>>"$$   A!!
A+.A+message_authormessagemessage_type	sql_querymetadataparent_message_idc                   K   t                       d{V }|
|dk    rdnd}|t          j        |          nd}	|                                4 d{V }
|
                                4 d{V  |
                    d|            d{V }|s(t          j        d|            t          d|  d          |
	                    d	| ||||||	|	  	         d{V }|

                    d
|            d{V  ddd          d{V  n# 1 d{V swxY w Y   ddd          d{V  n# 1 d{V swxY w Y   t          |          S )a  Save a message to a conversation.
    
    Args:
        conversation_id: Conversation ID
        message_author: 'user' or 'assistant'
        message: Message text content
        message_type: 'request' or 'response'
        sql_query: Optional SQL query (for NL2SQL responses)
        metadata: Optional JSON metadata
        parent_message_id: Optional parent message ID for threading
        created_by: User ID who created the message
    
    Returns:
        Created message dict
    Nr   requestresponsez(SELECT 1 FROM conversation WHERE id = $1uY   [CONV] save_message: conversation_id={} not found in conversation table — skipping savezconversation z does not exista  
                INSERT INTO conversation_message (
                    conversation_id, message_type, message_author, message,
                    parent_message_id, sql_query, metadata, created_by, updated_by
                )
                VALUES ($1, $2, $3, $4, $5, $6, $7::jsonb, $8, $8)
                RETURNING id, conversation_id, message_type, message_author,
                          message, created_at, metadata
                z8UPDATE conversation SET updated_at = NOW() WHERE id = $1)r'   jsondumpsr.   transactionrA   _logwarning
ValueErrorr/   rO   r0   )rF   rW   rX   rY   rZ   r[   r\   r+   r1   metadata_jsonr2   existsr3   s                r   save_messagerh      s     2 D $2f$<$<yy* -5,@DJx(((dM||~~ " " " " " " "##%% !	 !	 !	 !	 !	 !	 !	 !	  ==:O       F  So#   !!Q!Q!Q!QRRR   ~w!9mZ       C ,,J        =!	 !	 !	 !	 !	 !	 !	 !	 !	 !	 !	 !	 !	 !	 !	 !	 !	 !	 !	 !	 !	 !	 !	 !	 !	 !	 !	" " " " " " " " " " " " " " " " " " " " " " " " " " "H 99s7   D+0BD6D+
D	D+D	D++
D58D5c                    K   t                       d{V }|                                4 d{V }|                    d|            d{V }ddd          d{V  n# 1 d{V swxY w Y   d |D             S )zGet all messages for a conversation.

    Args:
        conversation_id: Conversation ID

    Returns:
        List of message dicts, ordered by created_at ASC (chronological)
    NaR  
            SELECT
                id, conversation_id, message_type, message_author,
                message, parent_message_id, reference_message_id,
                sql_query, metadata, created_at, bookmarked
            FROM conversation_message
            WHERE conversation_id = $1
            ORDER BY created_at ASC
            c                 ,    g | ]}t          |          S r   r:   r;   s     r   r=   z get_messages.<locals>.<listcomp>[      &&&#DII&&&r   r'   r.   rB   )rF   r1   r2   rD   s       r   get_messagesrm   B  s.      D||~~ 
 
 
 
 
 
 
ZZ 
 
 
 
 
 
 
 

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 '&&&&&rI   
message_id
bookmarkedc                 
  K   t                       d{V }|                                4 d{V }|                    d||            d{V }ddd          d{V  n# 1 d{V swxY w Y   |rt          |          ndS )u   Set or clear the bookmarked flag on a message.

    Args:
        message_id: Message ID
        bookmarked: New bookmark state

    Returns:
        Dict with id, conversation_id, bookmarked — or None if not found
    Nz
            UPDATE conversation_message
            SET bookmarked = $1, updated_at = NOW()
            WHERE id = $2
            RETURNING id, conversation_id, bookmarked
            r-   )rn   ro   r1   r2   r3   s        r   toggle_bookmarkrq   ^  s2      D||~~ 

 

 

 

 

 

 

MM 	
 	
 	
 	
 	
 	
 	
 	


 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 %4999%rV   c                    K   t                       d{V }|                                4 d{V }|                    d|            d{V }ddd          d{V  n# 1 d{V swxY w Y   d |D             S )a&  Get all bookmarked assistant messages for a user.

    Only returns messages from active conversations owned by the user.
    Message preview is truncated to 150 characters.

    Args:
        user_id: User ID

    Returns:
        List of bookmark dicts ordered by message created_at DESC
    Na  
            SELECT
                cm.id            AS message_id,
                cm.conversation_id,
                COALESCE(
                    (SELECT message FROM conversation_message
                     WHERE conversation_id = cm.conversation_id
                       AND message_author = 'user'
                       AND id < cm.id
                     ORDER BY id DESC
                     LIMIT 1),
                    c.title
                )                AS conversation_title,
                LEFT(cm.message, 150) AS message_preview,
                cm.created_at
            FROM conversation_message cm
            JOIN conversation c ON c.id = cm.conversation_id
            WHERE cm.bookmarked = TRUE
              AND cm.message_author = 'assistant'
              AND c.status = 'active'
              AND c.user_id = $1
            ORDER BY cm.created_at DESC
            c                 ,    g | ]}t          |          S r   r:   r;   s     r   r=   z&get_user_bookmarks.<locals>.<listcomp>  rk   r   rl   )r)   r1   r2   rD   s       r   get_user_bookmarksrt   y  s.      D||~~ 
 
 
 
 
 
 
ZZ. 1
 
 
 
 
 
 
 

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
8 '&&&&&rI   c                   K   t                       d{V }|                                4 d{V }|                    d|            d{V }ddd          d{V  n# 1 d{V swxY w Y   |rt          |          ndS )zGet a single message by ID.
    
    Args:
        message_id: Message ID
    
    Returns:
        Message dict or None if not found
    Nz
            SELECT 
                id, conversation_id, message_type, message_author,
                message, parent_message_id, sql_query, metadata, created_at
            FROM conversation_message
            WHERE id = $1
            r-   )rn   r1   r2   r3   s       r   get_messagerv     s/      D||~~ 

 

 

 

 

 

 

MM 	
 	
 	
 	
 	
 	
 	
 	


 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 %4999%rI   )r   N)r(   N)Fr   r   )N)NNNNN)__doc__r"   r`   typingr   r   r    r   logurur   rc   r   Pool__annotations__r   r'   r%   strr0   r4   boolrE   rH   rR   rU   rh   rm   rq   rt   rv   r   r   r   <module>r~      s9     
			  ! ! ! ! ! ! ! !        ! ! ! ! ! ! !%x $ $ $   7<    H  $   
	   B? ?S ?4 ?PS ?be ?nr ? ? ? ?D&C &HTN & & & &6 !%% %%% % 
	% % % %@ !%% %%% 
% % % %N #'##'+ $F FFF F 3-	F
 }F tnF  }F F 
F F F FR' 'T
 ' ' ' '8&c &t & & & & &6*'c *'d4j *' *' *' *'Z&# &(4. & & & & & &r   