o dBjÀ5ã@s<UdZddlmZddlZddlZddlZddlZddlmZddl m Z m Z m Z m Z e e¡ZdZdZdd d d d d d d d d d d d dddddœdœ Zded<dIdd„ZdJdd„ZdKdd„ZdLdd „Zdd!œdMd#d$„ZdNdOd(d)„ZdPd/d0„ZdQd2d3„ZdRd5d6„ZdSd8d9„Zddd:œdTd=d>„ZdUdBdC„Z dVdEdF„Z!dWdGdH„Z"dS)XzLConfigurable email notifications for support tickets (SMTP, off by default).é)Ú annotationsN)Ú EmailMessage)ÚAnyÚDictÚListÚOptionalÚ ticket_emailz********FÚsmtpÚéKTú PCA Support)Úticket_createdÚticket_assignedÚticket_resolvedÚcustomer_replyÚ staff_replyÚ staff_login) ÚenabledÚproviderÚ smtp_hostÚ smtp_portÚ smtp_userÚ smtp_passwordÚ smtp_use_tlsÚ from_emailÚ from_nameÚsupport_inbox_emailÚreply_to_emailÚeventsúDict[str, Any]ÚDEFAULT_CONFIGÚ table_nameÚstrÚreturnÚboolcCs| d|f¡| ¡duS)Nz} SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = %s LIMIT 1 )ÚexecuteÚfetchone)Úcursorr!©r(úF/home/aiteam/pcaa-dev/dashboard-backend/ticket_notification_service.pyÚ _table_exists(s û r*ÚNonecCs| d¡dS)Nav CREATE TABLE IF NOT EXISTS pcaa_notification_settings ( setting_key VARCHAR(64) NOT NULL PRIMARY KEY, config_json JSON NOT NULL, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, updated_by VARCHAR(100) NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci )r%)r'r(r(r)Ú#ensure_notification_settings_schema3sÿr,ÚrawúOptional[Dict[str, Any]]cCsXt t t¡¡}|s |S| ¡D]\}}|dkr%t|tƒr%|d |¡q|||<q|S)Nr)ÚjsonÚloadsÚdumpsr ÚitemsÚ isinstanceÚdictÚupdate)r-ÚbaseÚkeyÚvaluer(r(r)Ú_merge_defaults@s r9cCsêtt dd¡pdƒ ¡}|siStt dd¡ƒ ¡dv|tt dd¡p$dƒtt dd¡p-dƒ ¡tt d d¡p8dƒ ¡tt d d ¡ƒ ¡d vtt d d¡pNdƒ ¡tt dd¡pYdƒ ¡tt dd¡pddƒ ¡tt dd¡podƒ ¡dœ S)NÚTICKET_SMTP_HOSTr ÚTICKET_EMAIL_ENABLED)Ú1ÚtrueÚyesÚTICKET_SMTP_PORTÚ587r ÚTICKET_SMTP_USERÚTICKET_SMTP_PASSWORDÚTICKET_SMTP_USE_TLSr=)Ú0ÚfalseÚnoÚTICKET_FROM_EMAILÚTICKET_FROM_NAMEr ÚTICKET_SUPPORT_INBOXÚTICKET_REPLY_TO) rrrrrrrrrr)r"ÚosÚgetenvÚstripÚlowerÚint)Úhostr(r(r)Ú _env_fallbackLsörQ©Úinclude_secretsrSc CsÎt|ƒi}t|dƒr-| dtf¡| ¡}|r-| d¡r-|d}t|tƒr(|nt  |¡}t |ƒ}t ƒ}|  ¡D]\}}|dvrI| |¡sI|||<q8|rN|St|ƒ} |  d¡rat | d<d| d<| Sd| d<| S) NÚpcaa_notification_settingszQSELECT config_json FROM pcaa_notification_settings WHERE setting_key = %s LIMIT 1Ú config_json)Nr FrTÚhas_smtp_passwordF)r,r*r%Ú SETTING_KEYr&Úgetr3r4r/r0r9rQr2Ú PASSWORD_MASK) r'rSÚstoredÚrowr-ÚconfigÚenvr7r8Úmaskedr(r(r)Úget_ticket_email_config^s4 þ€ ÿr_ÚpayloadÚ updated_byú Optional[str]c sÐt|ƒt|dd}t|piƒ}t| d¡pdƒ ¡}|r!|tkr*| d¡p'd|d<t|ƒ}| d¡rVt| d¡p:iƒ‰d}t‡fdd „|DƒƒsV|D]}dˆ|<qKˆ|d<|  d t t   |¡|f¡t|d dS) NTrRrr rr)r rrrc3s|]}ˆ |¡VqdS©N)rX)Ú.0r7©rr(r)Ú Šs€z+save_ticket_email_config..zÞ INSERT INTO pcaa_notification_settings (setting_key, config_json, updated_by) VALUES (%s, %s, %s) ON DUPLICATE KEY UPDATE config_json = VALUES(config_json), updated_by = VALUES(updated_by) F) r,r_r9r"rXrMrYr4Úanyr%rWr/r1) r'r`raÚcurrentÚincomingÚpasswordr\Ú recommendedr7r(rer)Úsave_ticket_email_config}s&     ú rlr\Úto_addrsú List[str]ÚsubjectÚbodycCsŠdd„|Dƒ}|s tdƒ‚t| d¡pdƒ ¡}|stdƒ‚t| d¡p%dƒ}t| d ¡p.dƒ ¡}t| d ¡p9dƒ ¡}t| d d ¡ƒ} t| d ¡pN|pNdƒ ¡} | sXtdƒ‚t| d¡p_dƒ ¡} t| d¡pjdƒ ¡} tƒ} || d<| r| ›d| ›dn| | d<d |¡| d<| r| | d<|  |¡t j ||dd}| r¤|  ¡|r®|r®|  ||¡|  | ¡WdƒdS1s¾wYdS)NcSs(g|]}t|pdƒ ¡rt|ƒ ¡‘qS)r )r"rM)rdÚar(r(r)Ú šs(z_smtp_send..z No recipientsrr zSMTP host is not configuredrr rrrTrzFrom email is not configuredrr rÚSubjectz <Ú>ÚFromz, ÚTozReply-Toé)Útimeout)Ú ValueErrorr"rXrMrOr$rÚjoinÚ set_contentÚsmtplibÚSMTPÚstarttlsÚloginÚ send_message)r\rmrorpÚ recipientsrPÚportÚuserrjÚuse_tlsrrÚreply_toÚmsgr r(r(r)Ú _smtp_send™s:   "ûr‡Úto_emailcCs8t|dd}| d¡stdƒ‚t||gddƒd|dœS)NTrRrz;Ticket email notifications are disabled. Enable them first.uPCA Support — test emailzEThis is a test email from the PCA support ticket notification system.)ÚsuccessÚsent_to)r_rXryr‡)r'rˆr\r(r(r)Úsend_test_email½s  ü r‹ÚeventcCs*| d¡sdS| d¡p i}t| |¡ƒS)NrFr)rXr$)r\rŒrr(r(r)Ú_event_enabledÊs rÚticketcCst| d¡p d| d¡›ƒS)NÚ ticket_numberú#Úid)r"rX)rŽr(r(r)Ú _ticket_labelÑsr’)Úassignee_emailÚmessage_previewr“r”c Csúz\t|dd}t||ƒsWdSt|ƒ}| d¡}| d¡p$| d¡p$d}d|›d|›d | d ¡p3d ›d |›d | d¡›g} | d¡rR|  d| d¡›¡| d¡rb|  d| d¡›¡|rp|  d dt|ƒ ¡g¡d | ¡} d|  dd¡  ¡›d |›} |dkr“d|›} d| ›d} n|dkr¢d|›} d| ›d} g} |dkrÄt| d ¡p¯d ƒ ¡} | r»|  | ¡nxt   d!|¡WdS|dkrát|pÌd ƒ ¡}|rØ|  |¡n[t   d"|¡WdS|d#krøt| d$¡pìd ƒ ¡}|r÷|  |¡n;|d%krt| d ¡pd ƒ ¡} | r|  | ¡|r|  |¡n|d&kr3t| d$¡p'd ƒ ¡}|r3|  |¡t t d'd(„| Dƒ¡ƒ} | sLt   d)||¡WdSt|| | | ƒt   d*||| ¡WdSty|}zt  d+|| d,¡|¡WYd}~dSd}~ww)-NTrRÚ business_idroÚ issue_textzSupport ticketzTicket: z Business: u — Ú business_namer z Subject: zStatus: ÚstatusÚ assigned_toz Assigned to: Údue_atzDue: zMessage:Ú z[PCA] Ú_Ú r u[PCA] New support ticket — z)A customer raised a new support ticket. u6 Open PCA Master → Support to review and assign it.ru[PCA] New assignment — z*You have been assigned a support ticket. uA Open PCA Master → Support → My assigned tickets to view it.rzQticket email skipped event=ticket_created ticket=%s (support_inbox_email not set)zLticket email skipped event=ticket_assigned ticket=%s (assignee has no email)rÚ user_emailrrcSsg|]}|r|‘qSr(r()rdÚrr(r(r)rr'sz)try_send_ticket_email..z7ticket email skipped event=%s ticket=%s (no recipients)z*ticket email sent event=%s ticket=%s to=%sz*ticket email failed event=%s ticket=%s: %sr‘)r_rr’rXÚappendÚextendr"rMrzÚreplaceÚtitleÚloggerÚinfoÚlistr4Úfromkeysr‡Ú ExceptionÚwarning)r'rŒrŽr“r”r\ÚlabelÚbidroÚ body_linesrpÚ mail_subjectrÚinboxÚemailÚcustomerÚexcr(r(r)Útry_send_ticket_emailÕsš   ü    ÿÿ ÿÿ þ þ €   €  $€ÿr²Úuser_idrOúList[Dict[str, Any]]cCs"| dt|ƒf¡t| ¡pgƒS)Na  SELECT ticket_number, business_id, subject, issue_text, status, due_at FROM reported_issues WHERE assigned_to_user_id = %s AND status IN ('open', 'in_progress') ORDER BY due_at ASC, created_at DESC LIMIT 25 )r%rOr¦Úfetchall)r'r³r(r(r)Ú_assigned_active_tickets2s ÷ r¶rƒcCsN|sdS| d¡dvr dS| d¡dvrdSt| d¡pdƒ ¡ ¡}|dvS) NFÚ is_master)Tér<TÚhas_master_panelÚ staff_teamr )Ú developerÚsupport)rXr"rMrN)rƒÚteamr(r(r)Ú_is_staff_login_email_targetAsr¾c CsôzÜt|ƒsWdSt| d¡pdƒ ¡}| d¡}|r|sWdSt|dd}t|dƒs-WdSt| d¡p4dƒ ¡pDt| d ¡p?dƒ ¡pDd }t|t|ƒƒ}d |›d dd dg}|r°| dt |ƒ›d¡| d¡|D]D}| d¡prd} | d¡p~| d¡p~d} t| ƒ ¡  dd¡dd…} | d¡} | r™d| ›nd} | d| ›d| d¡›d| ›| ›¡qjn| d¡|  ddg¡d  |¡} dt |ƒ›d }t ||g|| ƒt d!|t |ƒ¡WdStyù}zt d"| d¡|¡WYd}~dSd}~ww)#zDEmail developer/support staff their assigned ticket digest on login.Nr¯r r‘TrRrÚ full_nameÚusernameÚtherezHello ú,z&You logged in to the PCA master panel.z You have z assigned ticket(s) open:rÚTicketror–r›rédršu · due z- u · BID r•u · z,You have no open assigned tickets right now.u— PCA Supportu[PCA] Login digest — z assigned ticket(s)z)staff login email sent to %s (%s tickets)z(staff login email failed for user %s: %s)r¾r"rXrMr_rr¶rOr Úlenr¢r¡rzr‡r¤r¥r¨r©)r'rƒr¯r³r\rÀÚticketsÚlinesr[rªÚsubjÚdueÚdue_txtrpror±r(r(r)Útry_send_staff_login_emailLsT   ÿý ü  (ú  "€ÿrË)r!r"r#r$)r#r+)r-r.r#r)r#r)rSr$r#rrc)r`rrarbr#r) r\rrmrnror"rpr"r#r+)rˆr"r#r)r\rrŒr"r#r$)rŽrr#r") rŒr"rŽrr“rbr”rbr#r+)r³rOr#r´)rƒrr#r$)rƒrr#r+)#Ú__doc__Ú __future__rr/ÚloggingrKr|Ú email.messagerÚtypingrrrrÚ getLoggerÚ__name__r¤rWrYr Ú__annotations__r*r,r9rQr_rlr‡r‹rr’r²r¶r¾rËr(r(r(r)Ús\   úô     $  ú ]