o
    cBjRm                     @  sx  d Z ddlmZ ddlZddlZddlZddlZddlmZmZ ddl	m
Z
 ddlmZmZmZmZmZ ddlmZmZ eh dZd	d
dddZeh dZeh dZdd eD ZdZdZeh dZeh dZe
e j d d Z!dddZ"dd d!Z#dd#d$Z$dd&d'Z%dd*d+Z&dd,d-Z'dd0d1Z(dd7d8Z)dd=d>Z*dd@dAZ+ddBdCZ,ddDdEdddddddddddFddVdWZ-ddddddddXddY	ddbdcZ.ddfdgZ/dhdiddkdlZ0dmdddhdnddtduZ1dvdwddydzZ2dddddd{dddZ3dddddZ4dddddZ5ddddZ6dddZ7dddZ8dddZ9dddZ:dddZ;dddddZ<dddZ=dS )uc   Support ticket system — extends reported_issues with categories, threading, and feature approval.    )annotationsN)datetime	timedelta)Path)AnyDictListOptionalTuple)ENTITLEMENT_KEYSENTITLEMENT_SPECS>   openresolved	dismissedin_progressOpenzIn ProgressResolved	Dismissed)r   r   r   r   >   dataissuegeneral
allocationfeature_request>   lowhighnormalurgentc                 C  s   i | ]\}}||qS  r   .0keylabelr   r   A/home/aiteam/pcaa-dev/dashboard-backend/support_ticket_service.py
<dictcomp>   s    r#      i  P >   	image/gif	image/jpg	image/png
image/jpeg
image/webp>   .gif.jpg.jpeg.webp.pnguploadssupportreturnr   c                  C  s:   t tddpd } | rt| nt}|jddd |S )NSUPPORT_UPLOAD_DIR T)parentsexist_ok)strosgetenvstripr   _DEFAULT_UPLOAD_ROOTmkdir)
configuredrootr   r   r"   support_upload_root(   s   r>   
table_namer6   column_nameboolc                 C  s   |  d||f |  d uS )Nz
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = DATABASE()
          AND table_name = %s
          AND column_name = %s
        LIMIT 1
        )executefetchone)cursorr?   r@   r   r   r"   _column_exists.   s
   rE   Nonec                 C  s   |  d t| dds|  d g d}|D ]\}}t| d|s+|  d| d|  qt| dds3	 |  d	 t|  p>i d
pCd}|rh|  d |  pQg D ]}t| t|d }|  d||d f qR|  d |  d d S )Na
  
        CREATE TABLE IF NOT EXISTS reported_issues (
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            business_id VARCHAR(50) NOT NULL,
            business_name VARCHAR(255) DEFAULT NULL,
            business_user_name VARCHAR(255) DEFAULT NULL,
            user_id BIGINT DEFAULT NULL,
            username VARCHAR(100) DEFAULT NULL,
            user_email VARCHAR(255) DEFAULT NULL,
            page_path VARCHAR(500) DEFAULT NULL,
            call_id VARCHAR(100) DEFAULT NULL,
            scope VARCHAR(100) DEFAULT NULL,
            issue_text TEXT NOT NULL,
            status ENUM('open','in_progress','resolved','dismissed') DEFAULT 'open',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX idx_business_status_created (business_id, status, created_at),
            INDEX idx_status_created (status, created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        reported_issuesbusiness_user_namezgALTER TABLE reported_issues ADD COLUMN business_user_name VARCHAR(255) DEFAULT NULL AFTER business_name))ticket_numberzVARCHAR(32) NULL)categoryz$VARCHAR(32) NOT NULL DEFAULT 'issue')subjectzVARCHAR(255) NULL)priorityz%VARCHAR(16) NOT NULL DEFAULT 'normal')entitlement_keyzVARCHAR(64) NULL)requested_minutesINT NULL)assigned_toVARCHAR(100) NULL)assigned_to_user_idzBIGINT NULL)assigned_atDATETIME NULL)assigned_byrQ   )	tat_hoursrO   )due_atrT   )resolved_byrQ   )resolved_atrT   )resolution_textz	TEXT NULLz'ALTER TABLE reported_issues ADD COLUMN  rI   z{
        SELECT COUNT(*) AS c FROM reported_issues
        WHERE ticket_number IS NULL OR TRIM(ticket_number) = ''
        cr   z
            SELECT id, business_id FROM reported_issues
            WHERE ticket_number IS NULL OR TRIM(ticket_number) = ''
            ORDER BY id ASC
            business_idz;UPDATE reported_issues SET ticket_number = %s WHERE id = %sidac  
        CREATE TABLE IF NOT EXISTS support_ticket_messages (
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            ticket_id BIGINT NOT NULL,
            author_type ENUM('user','master','system') NOT NULL DEFAULT 'user',
            author_id BIGINT NULL,
            author_name VARCHAR(255) NULL,
            message TEXT NOT NULL,
            is_internal TINYINT(1) NOT NULL DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_ticket_messages_ticket (ticket_id, created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        aW  
        CREATE TABLE IF NOT EXISTS support_ticket_attachments (
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            ticket_id BIGINT NOT NULL,
            stored_name VARCHAR(255) NOT NULL,
            original_name VARCHAR(255) NOT NULL,
            mime_type VARCHAR(100) NOT NULL,
            file_size INT NOT NULL DEFAULT 0,
            uploaded_by VARCHAR(255) NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_ticket_attachments_ticket (ticket_id, created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        )rB   rE   intrC   getfetchall_generate_ticket_numberr6   )rD   
extensionscol
definitionmissingrownumr   r   r"   ensure_support_ticket_schema=   sD   
ri   bidc                 C  sN   t | pd}| d|f t|  pi dpdd }d| d|dS )	N0zR
        SELECT COUNT(*) AS c FROM reported_issues WHERE business_id = %s
        r\   r      zPCA--04d)r6   r9   rB   r_   rC   r`   )rD   rj   bid_partseqr   r   r"   rb      s   rb   valueOptional[str]c                 C  $   t | pd  }|tv r|S dS )Nr   )r6   r9   lowerVALID_CATEGORIES)rq   catr   r   r"   _normalize_category      rw   c                 C  rs   )Nr   )r6   r9   rt   VALID_PRIORITIES)rq   prir   r   r"   _normalize_priority   rx   r{   rg   Dict[str, Any]c                 C  s2   | si S t | }|d}|rt|||d< |S )NrM   entitlement_label)dictr`   ENTITLEMENT_LABELS)rg   outr    r   r   r"   _serialize_ticket   s   
r   rS   r   rV   Optional[int]Optional[datetime]c                 C  s,   | r|d u rd S t dt|}| t|d S )Nrl   )hours)maxr_   r   )rS   rV   r   r   r   r"   _compute_due_at   s   r   eventticketextrar   c                 K  s$   ddl m} || ||fi | d S )Nr   )try_send_ticket_email)ticket_notification_servicer   )rD   r   r   r   r   r   r   r"   _notify   s   r   List[Dict[str, Any]]c                 C  sf   |  d g }|  pg D ]#}t|}t|dpd p(t|dp%d |d< || q|S )z(Staff users who can be assigned tickets.a  
        SELECT id, username, email, full_name, staff_team
        FROM business_users
        WHERE is_active = 1
          AND (
            is_master = 1
            OR (has_master_panel = 1 AND staff_team IN ('developer', 'support'))
          )
        ORDER BY
          CASE staff_team WHEN 'developer' THEN 0 WHEN 'support' THEN 1 ELSE 2 END,
          COALESCE(NULLIF(TRIM(full_name), ''), username)
        	full_namer3   usernamedisplay_name)rB   ra   r~   r6   r`   r9   append)rD   rowsrg   itemr   r   r"   list_ticket_assignees   s   r   c                 C  sL   t | } | dd p| d| d< | dd }|r"| ds"|| d< t| S )Nbusiness_user_name_resolvedrH   assignee_namerP   )r~   popr`   r   )rg   assigneer   r   r"   _apply_assignee_row   s   r   r   r   )rK   rJ   rL   business_namerH   user_idr   
user_email	page_pathcall_idscoperM   rN   r]   
issue_textrK   rJ   rL   r   rH   r   r   r   r   r   r   rM   rN   c                C  sr  t |  t| }t|pd }|std|stdt|}|dkr<|r<t| }|tvr9td| |}n|dkrB|nd }d }|dkrU|d urUtdt|}t|pYd p^d }|sq|d d t|dkrnd	nd }t	| |}| 
d
|||||	|
|||||||t|||f t| jpd}t| ||d||p|	dd | 
d|f t|  pi }t| d| |S )Nr3   zbusiness_id is requiredzissue_text is requiredr   zInvalid entitlement_key: r   r   x   u   …a  
        INSERT INTO reported_issues (
            business_id, business_name, business_user_name, user_id, username, user_email,
            page_path, call_id, scope, issue_text, status,
            ticket_number, category, subject, priority, entitlement_key, requested_minutes
        ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, 'open',
                  %s, %s, %s, %s, %s, %s)
        userF)messageauthor_type	author_idauthor_nameis_internalz+SELECT * FROM reported_issues WHERE id = %sticket_created)ri   r6   r9   
ValueErrorrw   r   r   r_   lenrb   rB   r{   	lastrowidadd_messager   rC   r   )rD   r]   r   rK   rJ   rL   r   rH   r   r   r   r   r   r   rM   rN   rj   textrv   entminssubjrI   	ticket_idr   r   r   r"   create_ticket   sn    
		r   d   )	r]   statusrJ   r   rR   created_afterassigned_afterlimitoffsetr   rR   r   r   r   r_   r    Tuple[List[Dict[str, Any]], int]c       	         C  s  t |  g }
g }|r|
d |t|  |r2t| }|tvr(td|
d || |rBt|}|
d || |d urR|
d |t| |d urb|
d |t| |rr|
d |t|  |r|
d |t|  |
rd	d
|
 nd}| 	d| | t| 
 pi dpd}tdtt|pdd}tdt|	pd}| 	d| d|||g  dd |  pg D }||fS )Nzri.business_id = %sInvalid statuszri.status = %szri.category = %szri.user_id = %szri.assigned_to_user_id = %szri.created_at > %szri.assigned_at > %szWHERE z AND r3   z1SELECT COUNT(*) AS total FROM reported_issues ri totalr   rl   r   i  a%  
        SELECT
            ri.*,
            COALESCE(NULLIF(ri.business_user_name, ''), NULLIF(bu.full_name, ''), ri.username) AS business_user_name_resolved,
            COALESCE(NULLIF(au.full_name, ''), au.username, ri.assigned_to) AS assignee_name,
            (SELECT COUNT(*) FROM support_ticket_messages m WHERE m.ticket_id = ri.id AND m.is_internal = 0) AS message_count
        FROM reported_issues ri
        LEFT JOIN business_users bu ON bu.id = ri.user_id
        LEFT JOIN business_users au ON au.id = ri.assigned_to_user_id
        z
        ORDER BY
            CASE ri.status WHEN 'open' THEN 0 WHEN 'in_progress' THEN 1 ELSE 2 END,
            ri.created_at DESC,
            ri.id DESC
        LIMIT %s OFFSET %s
        c                 S  s   g | ]}t |qS r   )r   )r   rg   r   r   r"   
<listcomp>  s    z list_tickets.<locals>.<listcomp>)ri   r   r6   r9   VALID_STATUSESr   rw   r_   joinrB   rC   r`   r   minra   )rD   r]   r   rJ   r   rR   r   r   r   r   whereparamsstrv   	where_sqlr   
safe_limitsafe_offsetr   r   r   r"   list_ticketsQ  sR   








	
r   r   Optional[Dict[str, Any]]c                 C  s2   t |  | dt|f |  }|sd S t|S )Na  
        SELECT
            ri.*,
            COALESCE(NULLIF(ri.business_user_name, ''), NULLIF(bu.full_name, ''), ri.username) AS business_user_name_resolved,
            COALESCE(NULLIF(au.full_name, ''), au.username, ri.assigned_to) AS assignee_name
        FROM reported_issues ri
        LEFT JOIN business_users bu ON bu.id = ri.user_id
        LEFT JOIN business_users au ON au.id = ri.assigned_to_user_id
        WHERE ri.id = %s
        LIMIT 1
        )ri   rB   r_   rC   r   )rD   r   rg   r   r   r"   
get_ticket  s   r   F)include_internalr   c                C  sB   t |  |r| dt|f n	| dt|f t|  pg S )Nz
            SELECT * FROM support_ticket_messages
            WHERE ticket_id = %s
            ORDER BY created_at ASC, id ASC
            z
            SELECT * FROM support_ticket_messages
            WHERE ticket_id = %s AND is_internal = 0
            ORDER BY created_at ASC, id ASC
            ri   rB   r_   listra   )rD   r   r   r   r   r"   get_ticket_messages  s   	r   r   )r   r   r   r   r   r   r   r   r   c          
   	   C  s   t |  t|pd }|stdt|pd  }|dvr$td| dt||||||r2dndf t| jp;d}	| d	|	f t| 	 pJi S )
Nr3   zmessage is requiredr   >   r   mastersystemzInvalid author_typez
        INSERT INTO support_ticket_messages
        (ticket_id, author_type, author_id, author_name, message, is_internal)
        VALUES (%s, %s, %s, %s, %s, %s)
        rl   r   z3SELECT * FROM support_ticket_messages WHERE id = %s)
ri   r6   r9   r   rt   rB   r_   r   r~   rC   )
rD   r   r   r   r   r   r   r   atypemsg_idr   r   r"   r     s(   

r      )rV   rU   c                C  s*  t |  t| |}|std| dt|f |  }|s"tdt|dp)d	 p7t|dp4d	 }t
dt|p>d}t }	t|	|}
| d	t|||	t|pVd	 p[d ||
t|f t| |d
| d| d|
d dddd t| |p|}t| d|t|dpd	 pd d |S )NTicket not foundz
        SELECT id, username, email, full_name
        FROM business_users
        WHERE id = %s AND is_active = 1
        LIMIT 1
        zAssignee not foundr   r3   r   rl   r   aE  
        UPDATE reported_issues
        SET assigned_to_user_id = %s,
            assigned_to = %s,
            assigned_at = %s,
            assigned_by = %s,
            tat_hours = %s,
            due_at = %s,
            status = CASE WHEN status = 'open' THEN 'in_progress' ELSE status END
        WHERE id = %s
        zAssigned to u    · TAT u	   h · due z%Y-%m-%d %H:%Mr   SystemFr   r   r   r   ticket_assignedemail)assignee_email)ri   r   LookupErrorrB   r_   rC   r   r6   r`   r9   r   r   nowr   r   strftimer   )rD   r   rR   rU   rV   r   r   r   r   r   rW   updatedr   r   r"   assign_ticket  sX   
	
	r   )r   rL   rP   rZ   rX   rP   rZ   rX   c                C  sD  t |  t| |}|stdg }g }	|d ur_t| }
|
tvr%td|d |	|
 |
dkr_|d |	t	  |rK|d |	| |d ur_|d |	t| p]d  |d uro|d |	t
| |d ur|d	 |	t| pd  |s|S |	t| | d
d| d|	 t| |p|S )Nr   r   zstatus = %sr   zresolved_at = %szresolved_by = %szresolution_text = %szpriority = %szassigned_to = %szUPDATE reported_issues SET z, z WHERE id = %s)ri   r   r   r6   r9   r   r   r   r   r   r{   r_   rB   r   )rD   r   r   rL   rP   rZ   rX   r   setsr   r   r   r   r"   update_ticket=  sF   









r   )author_display_name
changed_byr   c             	   C  s   t |  t| |}|stdt|pd }|tvrtdt|dp&d }||kr0|S |dkr8tdt| ||d}t|pE|pEd	 pJd	}	t	||
d
d }
t	||
d
d }t| |d|
 d| d|	 dddd t| |p||S )z@Update ticket status and post a customer-visible system message.r   r3   r   r   r   r   zAUse resolve_ticket with resolution_text to mark a ticket resolvedr   Staff_r[   Status updated from  to  by r   r   Fr   )ri   r   r   r6   r9   r   r   r`   r   STATUS_LABELSreplacetitler   )rD   r   r   r   r   r   
new_status
old_statusr   actor	old_label	new_labelr   r   r"   change_ticket_statusr  s2   	
r   r   r   c             	   C  s
  t |pd }|stdt| |}|stdt |dp d }t| ||||d}t |p1d }	||	krnt||dd		 }
t|	|	dd		 }t |pUd
 pZd
}t
| |d|
 d| d| dddd t
| ||d|dd t| d| t| |p|S )Nr3   zresolution_text is requiredr   r   r   )r   rZ   rX   r   r   r[   r   r   r   r   r   r   Fr   r   ticket_resolved)r6   r9   r   r   r   r`   r   r   r   r   r   r   )rD   r   rZ   rX   r   r   existingr   r   r   r   r   r   r   r   r"   resolve_ticket  sJ   
r   c                 C  s   t |  d}g }|rd}|t|  | d| d| |  p$i }t|dp,dt|dp4dt|dp<dt|d	pDdt|d
pLddS )Nr3   zWHERE business_id = %sa  
        SELECT
            COUNT(*) AS total,
            SUM(CASE WHEN status = 'open' THEN 1 ELSE 0 END) AS open_count,
            SUM(CASE WHEN status = 'in_progress' THEN 1 ELSE 0 END) AS in_progress_count,
            SUM(CASE WHEN status = 'resolved' THEN 1 ELSE 0 END) AS resolved_count,
            SUM(CASE WHEN category = 'feature_request' AND status IN ('open','in_progress') THEN 1 ELSE 0 END) AS feature_open
        FROM reported_issues
        z	
        r   r   
open_countin_progress_countresolved_countfeature_open)r   r   r   r   r   )ri   r   r6   r9   rB   rC   r_   r`   )rD   r]   r   r   rg   r   r   r"   get_ticket_stats  s&   
r   List[Dict[str, str]]c                   C  s   dd t D S )Nc                 S  s   g | ]	\}}||d qS ))r    r!   r   r   r   r   r"   r     s    z'entitlement_options.<locals>.<listcomp>)r   r   r   r   r"   entitlement_options  s   r   namec                 C  s8   t jt| pd}tdd|dpd}|d d S )Nzscreenshot.pngz	[^\w.\-]+r   z._   )r7   pathbasenamer6   resubr9   )r   baser   r   r"   _safe_filename  s   r  filename	mime_typesizec                 C  s   |dkrt d|tkrt dtd  dtt| j }|tvr't dt|p+ddd 	  }|t
vrIt| \}}t|pEd }|t
vrQt d	|S )
Nr   z
Empty filezFile too large (max i   z MB)z2Only image files are allowed (PNG, JPG, WEBP, GIF)r3   ;z"Only image screenshots are allowed)r   MAX_ATTACHMENT_BYTESr   r  suffixrt   ALLOWED_ATTACHMENT_EXTr6   splitr9   ALLOWED_ATTACHMENT_MIME	mimetypes
guess_type)r  r  r  extmimeguessedr   r   r   r"   _validate_attachment  s   r  c                 C  s*   t |  | dt|f t|  pg S )Nz
        SELECT id, ticket_id, stored_name, original_name, mime_type, file_size, uploaded_by, created_at
        FROM support_ticket_attachments
        WHERE ticket_id = %s
        ORDER BY created_at ASC, id ASC
        r   )rD   r   r   r   r"   list_ticket_attachments  s   	r  attachment_idc                 C  s"   t |  | dt|f |  S )Nz
        SELECT a.*, ri.business_id
        FROM support_ticket_attachments a
        INNER JOIN reported_issues ri ON ri.id = a.ticket_id
        WHERE a.id = %s
        LIMIT 1
        )ri   rB   r_   rC   )rD   r  r   r   r"   get_ticket_attachment  s   
r  )uploaded_bycontentbytesr  c             	   C  s   t |  t| |}|stdt| |}t|tkr"tdt dt||t|}t|}	t	|	j
 p6d}
| dt j |
 }t | }|| | dt|||	|t||f t| jpbd}| d|f t|  pqi S )	Nr   zMaximum z attachments per ticketr.   r   z
        INSERT INTO support_ticket_attachments
        (ticket_id, stored_name, original_name, mime_type, file_size, uploaded_by)
        VALUES (%s, %s, %s, %s, %s, %s)
        r   zSELECT id, ticket_id, stored_name, original_name, mime_type, file_size, uploaded_by, created_at FROM support_ticket_attachments WHERE id = %s)ri   r   r   r  r   MAX_ATTACHMENTS_PER_TICKETr   r  r  r   r  rt   uuiduuid4hexr>   write_bytesrB   r_   r   r~   rC   )rD   r   r  r  r  r  r   r   	safe_mimesafe_originalr  stored_namedestr  r   r   r"   save_ticket_attachment   s:   	



r   r  c                 C  sD   t   }|tjt| pd  }t|t|s td|S )Nr3   zInvalid attachment path)r>   resolver7   r   r   r6   
startswithr   )r  r=   r   r   r   r"   attachment_file_pathP  s
   
r#  )r1   r   )r?   r6   r@   r6   r1   rA   )r1   rF   )rj   r6   r1   r6   )rq   rr   r1   r6   )rg   r|   r1   r|   )rS   r   rV   r   r1   r   )r   r6   r   r|   r   r   r1   rF   )r1   r   ) r]   r6   r   r6   rK   rr   rJ   r6   rL   r6   r   rr   rH   rr   r   r   r   rr   r   rr   r   rr   r   rr   r   rr   rM   rr   rN   r   r1   r|   )r]   rr   r   rr   rJ   rr   r   r   rR   r   r   rr   r   rr   r   r_   r   r_   r1   r   )r   r_   r1   r   )r   r_   r   rA   r1   r   )r   r_   r   r6   r   r6   r   r   r   rr   r   rA   r1   r|   )
r   r_   rR   r_   rU   r6   rV   r   r1   r|   )r   r_   r   rr   rL   rr   rP   rr   rZ   rr   rX   rr   r1   r|   )
r   r_   r   r6   r   r6   r   rr   r1   r|   )
r   r_   rZ   r6   rX   r6   r   r6   r1   r|   )N)r]   rr   r1   r|   )r1   r   )r   r6   r1   r6   )r  r6   r  r6   r  r_   r1   r6   )r   r_   r1   r   )r  r_   r1   r   )r   r_   r  r6   r  r6   r  r  r  rr   r1   r|   )r  r6   r1   r   )>__doc__
__future__r   r  r7   r   r  r   r   pathlibr   typingr   r   r   r	   r
   feature_entitlementsr   r   	frozensetr   r   ru   ry   r   r  r  r
  r  __file__r!  parentr:   r>   rE   ri   rb   rw   r{   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r  r  r  r  r   r#  r   r   r   r"   <module>   s    



j







\
H!,P;./




0