o Bpj$S @sUdZddlmZddlZddlZddlZddlmZmZm Z m Z ddl m Z m Z ddlmZeeZdZdZgd Zd d gZd gd dd d dd dd eeeed Zded<d\ddZd]d d!Zd^d$d%Zd_d'd(Zd d)d`d+d,Zdadbd0d1Zdcd2d3Z ddd7d8Z!daded:d;Z"dfd=d>Z#dgdAdBZ$d dCdhdEdFZ%dadidGdHZ&djdJdKZ'dkdLdMZ(dldOdPZ)dmdTdUZ*dndXdYZ+dodZd[Z,dS)puConfigurable email alerts for business usage-limit and validity events. This feature owns its own SMTP transport config, fully independent of the support-ticket email settings. It only borrows the generic ``_smtp_send`` transport helper (a pure sender that takes a config dict). Two levels of configuration: * Global (settings key ``usage_validity_alert_email``): SMTP transport, master enable, global recipient list, and default thresholds (usage %, validity warn-days). * Per-business (table ``pca_usage_alert_prefs``): optional own enable flag, extra recipients (added to global), and threshold overrides. Also stores the per-event "already sent" state so each threshold alerts at most once per fresh transition. Events are dynamic: * ``usage_`` — transcribed minutes reached t% of the limit (e.g. 90/95/100) * ``validity_d`` — d days (or fewer) remain before validity expiry (e.g. 7/1) * ``validity_expired``— validity period has passed ) annotationsN)AnyDictListOptional) _smtp_send#ensure_notification_settings_schema)evaluate_usage_allocationusage_validity_alert_emailz********)Z_dFiKTz PCA Alerts) enabled recipients smtp_host smtp_port smtp_user smtp_password smtp_use_tls from_email from_namereply_to_emailusage_thresholdsvalidity_warn_daysDict[str, Any]DEFAULT_CONFIG table_namestrreturnboolcCs|d|f|duS)Nz} SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = %s LIMIT 1 )executefetchone)cursorrr&G/home/aiteam/pcaa-dev/dashboard-backend/usage_validity_alert_service.py _table_exists>s  r(valuerlointhiOptional[List[int]]c Cs|durdSt|ttfsdSg}|D])}zt|}Wn ttfy&Yqw||kr1|krr?rI PASSWORD_MASK)r%rQstoredrowr:configmaskedr&r&r'get_usage_alert_configxs*  r\payload updated_by Optional[str]cCst|t|dd}t|pi}dd|dpgD|d<t|dp%d}|r/|tkr8|dp5d|d<t|dp?d}|rKt||d<|d t t ||ft|d dS) NTrPcS(g|]}t|pdrt|qSrr rN.0rr&r&r'  z+save_usage_alert_config..rrrrz 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) rr\rIrBr rNrWrOr#rUr>r@)r%r]r^currentrZpasswordrJr&r&r'save_usage_alert_configs"       rjcCsr|sdSt|tjr|}n%t|tjr|}nztjt|dd}Wn ty3t|YSw|dS)NN/A z%d %b %Y)r/datetimedate fromisoformatr r3strftime)r)dr&r&r'_format_date_humans      rrnamebidusagec Cs|dpd}|d}t|}|dpd}|dpd}|d}|dur-|ddnd } |dur6d } n|dkrCd t|d } n|d } d |d|d} d|d|d|dt|d| d|dd|dd| d} | | fS)Nvalidity_monthsrvalidity_expiry_date used_minutesmonthly_minute_limit usage_percentz.2f%rkzExpired z day(s) agoz Daysu-[PCA TEST] Subscription & Usage Reminder —  (BID )uZDear Team, This is a friendly reminder that your PCA (Post Call Analytics) subscription is approaching its expiry date. ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ SUBSCRIPTION DETAILS ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ Business Name : z Business ID : z Subscription Validity : z Months Expiry Date : z Days Remaining : u ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ CURRENT USAGE ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ Current Usage : ,z Minutes Allocated Limit : z Minutes Usage Percentage : u ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ ACTION REQUIRED ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ Your subscription is nearing expiration. To ensure uninterrupted access to PCA services, please renew or extend your subscription before the expiry date. If your renewal has already been processed, kindly ignore this email. For assistance or renewal support, please contact our support team. Thank you for choosing PCA. Regards, PCA Support Team [This is a TEST email sent via the PCA usage & validity alert test path. No thresholds were marked as already-sent by this test.])rB_days_until_expiryabsrr) rsrtrurvexpirydays_remainingusedlimitpct pct_display days_displaysubjectbodyr&r&r'"_build_subscription_reminder_emails@     rto_emailcCst|dd}|dstdt|d|d<|r3t|t|}t|t|}t|t||\}}nd}d}t||g||d||rIt|dSddS) NTrPrz>Usage & validity alert emails are disabled. Enable them first.ru*PCA Usage & Validity Alerts — test emailzMThis is a test email from the PCA usage & validity alert notification system.)successsent_tort) r\rBr3rO_business_namer r rr)r%rrtrZrsrurrr&r&r'send_test_usage_alert_emails  rNonecCs|ddS)Na CREATE TABLE IF NOT EXISTS pca_usage_alert_prefs ( bid VARCHAR(64) NOT NULL PRIMARY KEY, enabled TINYINT NULL, recipients_json JSON NULL, usage_thresholds_json JSON NULL, validity_warn_days_json JSON NULL, sent_state_json JSON NULL, updated_by VARCHAR(100) NULL, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci )r#)r%r&r&r'ensure_alert_prefs_tablesrrYrFc CsN||}|dur dSt|ttfr|Szt|WSttfy&YdSwr.)rBr/rVr0r>r?r2r3)rYrFr:r&r&r'_load_json_fields  r include_staterc Cst||dt|f|pi}|d}t||dur!dnt|t|dp*gtt|ddddtt|dd d dd }|rJt|d pGi|d <|S)Nz:SELECT * FROM pca_usage_alert_prefs WHERE bid = %s LIMIT 1rrecipients_jsonusage_thresholds_jsonrr r<validity_warn_days_jsonrr=)rtrrrrsent_state_json sent_state)rr#r r$rBr"rr9)r%rtrrYrprefsr&r&r'get_business_alert_prefss    rc Cst||d}|durdnt|rdnd}dd|dp gD}t|dddd }t|d dd d }|d t||t||durKt|nd|durUt|nd|ft||S) NrrrcSr`rarbrcr&r&r'rf7rgz-save_business_alert_prefs..rrr r<rr=a INSERT INTO pca_usage_alert_prefs (bid, enabled, recipients_json, usage_thresholds_json, validity_warn_days_json, updated_by) VALUES (%s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE enabled = VALUES(enabled), recipients_json = VALUES(recipients_json), usage_thresholds_json = VALUES(usage_thresholds_json), validity_warn_days_json = VALUES(validity_warn_days_json), updated_by = VALUES(updated_by) ) rrBr"r9r#r r>r@r) r%rtr]r^r enabled_valrrrr&r&r'save_business_alert_prefs3s&    rrcCs&t||dt|t|fdS)Nz INSERT INTO pca_usage_alert_prefs (bid, sent_state_json) VALUES (%s, %s) ON DUPLICATE KEY UPDATE sent_state_json = VALUES(sent_state_json) )rr#r r>r@)r%rtrr&r&r'_save_sent_stateUs rcCsRt|ds d|S|dt|f|pi}t|dp dp(d|S)N businesses Business z2SELECT name FROM businesses WHERE bid = %s LIMIT 1rsr)r(r#r r$rBrN)r%rtrYr&r&r'rds    r Optional[int]cCs||d}|s dSt|tjr|}n"t|tjr|}nztjt|dd}Wn ty5YdSw|tjjS)Nrwrl) rBr/rmrnror r3todaydays)rur expiry_dater&r&r'rls     rr List[int]rc Csg}|d}|ds'|dur'tt|D]}|d|||kd|fq|drot|d}|d}tt|D]'}|d krEq>| oW|duoWd |koU|kn} |d |d | d |fq>|d|dd f|S) z*Yield (event_key, condition, kind, value).rz unlimitedNusage_rurwvalidity_expiredvalidity_days_remainingr validity_rq validity_warn)rBr5setr4r") rurreventsuptexpiredrrqcondr&r&r' _events_for|s   &r event_keykindcCs|d}|d}|d}|d} |d} |d} |ds.t|p'd|p*ddnd } |dur9|d nd } | rAt| nd }| rN| dkrN| d nd }| sUd }n|dr]d}n | durhd| d}nd}d|d|dsu|nd d| d| d|d|d|}|dkr|dkrd|d|d}d|d|d |d!}||fSd"|d#|d|d}d|d|d$|d%|d& }||fS|d'krd(|d)|d|d}d|d|d*|d+}||fSd,|d|d}d|d|d-|d.}||fS)/Nrxryrzrwrrvrr Unlimitedr{zN/A (unlimited)zNot configuredz month(s) from onboardingrExpiredzActive (z day(s) remaining)ActivezUsed minutes: z Total allocated minutes: z Remaining minutes: z Usage: z Validity period: z Validity expiry: z Validity status: rur u [PCA] Usage limit exhausted — r|r}rz() has exhausted its allocated minutes. zh The pipeline for this business may be auto-paused. Open PCA Master Panel > Usage to review and top up.z[PCA] Usage at u% — z) has reached z% of its allocated minutes. z[ Consider topping up before the limit is reached. Open PCA Master Panel > Usage to review.rz[PCA] Validity expiring in ~u day(s) — z() will reach its validity expiry soon. zE Open PCA Master Panel > Usage to extend validity before it expires.uCA] Validity expired — z.) has passed its configured validity period. zq The pipeline for this business may be auto-paused. Open PCA Master Panel > Usage to review and extend validity.)rBmaxr )rrr)rsrtrurrrrrrv remaining usage_text expiry_textvalidity_periodvalidity_statusaccount_detailsrrr&r&r' _build_emails~      $   rc Cs2zt|ds WdSt|dd}|dsWdSt|dpds%WdSt||dd}|dd ur6WdSttd d |d pBgDd d |d pMgD}|sXWdS|dpc|dpct }|dpo|dpot }t|}t ||d<t |||}t|dpi} t ||} d } |D]]\} } }}| | }| r|sz)t| ||| ||\}}t||||tjjdd| | <d} td| ||Wqty}ztd| ||WYd}~qd}~ww| s|rd| | <d} q| rt||| WdSWdSty}ztd||WYd}~dSd}~ww)aSend at most one email per threshold per fresh transition into that state. Called once per supervisor sweep per bid. Each event's sent-flag is cleared when its condition resolves (admin tops up minutes / extends validity) so a future re-occurrence alerts again. pca_business_allocationsNTrPrrrrFcSg|]}|r|qSr&r&rcr&r&r'rfz3maybe_send_usage_validity_alert..rcSrr&r&rcr&r&r'rfrrrrrseconds)timespecz/usage/validity alert sent event=%s bid=%s to=%sz/usage/validity alert failed event=%s bid=%s: %sz5maybe_send_usage_validity_alert failed for bid=%s: %s)r(r\rBr rNrr0rVfromkeysrCrDrrrrrrmnow isoformatloggerinfo Exceptionwarningr)r%rtru global_cfgrrrrrrrschangedrF conditionrr)alreadyrrexcr&r&r'maybe_send_usage_validity_alertsh       r)rr r!r")r)rr*r+r,r+r!r-)r:r;r!r)rJr r!r )rQr"r!rr.)r]rr^r_r!r)r!r )rsr rtr rur)rr rtr_r!r)r!r)rYrrFr )rtr rr"r!r)rtr r]rr^r_r!r)rtr rrr!r)rtr r!r )rurr!r)rurrrrr) rr rr r)r+rsr rtr rur)rtr rurr!r)-__doc__ __future__rrmr>loggingtypingrrrrticket_notification_servicerrusage_allocation_utilr getLogger__name__rrUrWrCrDr0r__annotations__r(r9rIrOr\rjrrrrrrrrrrrrrrr&r&r&r'sZ          1    "    I