Diff -- /var/www/vhosts/3dshawn.com/admin.3dshawn.com/MODS/MetaAdmin/AutoAck.pm

O Operator
Diff

/var/www/vhosts/3dshawn.com/admin.3dshawn.com/MODS/MetaAdmin/AutoAck.pm

added on local at 2026-07-10 14:13:06

Added
+537
lines
Removed
-0
lines
Context
0
unchanged
Blobs
from
to 0cb9fe0eed3d
Restore this content →
Unified diff
Naive LCS-based line diff. Additions in emerald, deletions in rose. Both sides decompressed live from BLOB_STORE.
1package MODS::MetaAdmin::AutoAck;
2#======================================================================
3# Meta-Admin -- auto-acknowledge scheduler for Support Inbox.
4#
5# Design:
6# * discover() -- scan all supported sites for eligible threads,
7# match against active rules, insert scheduled fires
8# into auto_ack_queue (with random delay per rule).
9# * fire_ready() -- process queued rows whose fire_at <= NOW(). Re-runs
10# eligibility checks at fire time (a human may have
11# replied in the meantime; the customer may have
12# closed the thread; global kill switch may be off).
13#
14# Both intended to be called from auto_ack_tick.pl, running every minute
15# from /etc/cron.d.
16#
17# Ethical guardrail: the templates themselves are enforced-ack-only by
18# operator discipline (see the /canned.cgi KPI tile). This module just
19# fires whatever's in the pool -- so keep the pool clean.
20#======================================================================
21use strict;
22use warnings;
23use POSIX ();
24use MODS::DBConnect;
25use MODS::MetaAdmin::SiteConn;
26use MODS::MetaAdmin::SupportInbox;
27use MODS::MetaAdmin::CannedReplies;
28
29sub new {
30 my ($class) = @_;
31 return bless({
32 db => MODS::DBConnect->new,
33 sc => MODS::MetaAdmin::SiteConn->new,
34 inbox => MODS::MetaAdmin::SupportInbox->new,
35 canned => MODS::MetaAdmin::CannedReplies->new,
36 log => [],
37 }, $class);
38}
39
40sub log_lines { return $_[0]->{log} }
41
42sub _log { push @{$_[0]->{log}}, $_[1] }
43
44#---------------------------------------------------------------------
45# is_globally_enabled -> 0/1. The single kill switch.
46#---------------------------------------------------------------------
47sub is_globally_enabled {
48 my $self = shift;
49 my $dbh = $self->{db}->db_connect or return 0;
50 my $r = $self->{db}->db_readwrite($dbh,
51 "SELECT setting_value AS v FROM auto_ack_settings WHERE setting_key='global_enabled' LIMIT 1",
52 __FILE__, __LINE__);
53 $self->{db}->db_disconnect($dbh);
54 return ($r && $r->{v} && $r->{v} eq '1') ? 1 : 0;
55}
56
57sub set_global_enabled {
58 my ($self, $v) = @_;
59 $v = $v ? '1' : '0';
60 my $dbh = $self->{db}->db_connect or return 0;
61 $self->{db}->db_readwrite($dbh, qq~
62 UPDATE auto_ack_settings SET setting_value='$v'
63 WHERE setting_key='global_enabled'
64 ~, __FILE__, __LINE__);
65 $self->{db}->db_disconnect($dbh);
66 return 1;
67}
68
69#---------------------------------------------------------------------
70# is_business_hours($rule) -> 0/1
71# Compares current time (Mountain Time) against rule's start/end hours.
72#---------------------------------------------------------------------
73sub is_business_hours {
74 my ($self, $rule) = @_;
75 return 1 unless $rule->{business_hours_only};
76 local $ENV{TZ} = 'America/Denver';
77 POSIX::tzset();
78 my @lt = localtime(time);
79 my $hour = $lt[2];
80 my $s = int($rule->{business_hours_start} || 0);
81 my $e = int($rule->{business_hours_end} || 23);
82 if ($s <= $e) { return ($hour >= $s && $hour <= $e) ? 1 : 0; }
83 # Wrap-around window (e.g. 22..6)
84 return ($hour >= $s || $hour <= $e) ? 1 : 0;
85}
86
87#---------------------------------------------------------------------
88# list_rules({ active_only }) -> arrayref of rule hashrefs
89#---------------------------------------------------------------------
90sub list_rules {
91 my ($self, %a) = @_;
92 my $active = exists $a{active_only} ? $a{active_only} : 0;
93 my $where = $active ? 'WHERE is_active=1' : '';
94 my $dbh = $self->{db}->db_connect or return [];
95 my @rows = $self->{db}->db_readwrite_multiple($dbh, qq~
96 SELECT * FROM auto_ack_rules $where ORDER BY priority, id
97 ~, __FILE__, __LINE__);
98 $self->{db}->db_disconnect($dbh);
99 return \@rows;
100}
101
102sub get_rule {
103 my ($self, $id) = @_;
104 $id = int($id || 0); return undef unless $id > 0;
105 my $dbh = $self->{db}->db_connect or return undef;
106 my $r = $self->{db}->db_readwrite($dbh,
107 "SELECT * FROM auto_ack_rules WHERE id=$id LIMIT 1", __FILE__, __LINE__);
108 $self->{db}->db_disconnect($dbh);
109 return $r;
110}
111
112sub save_rule {
113 my ($self, %a) = @_;
114 my $id = int($a{id} || 0);
115 my $dbh = $self->{db}->db_connect or return undef;
116 my $name_q = $dbh->quote($a{name} // '');
117 my $desc_q = $dbh->quote($a{description} // '');
118 my $ss_q = $dbh->quote($a{site_scope} // 'all');
119 my $tpl = int($a{template_id} || 0);
120 my $mn = int($a{min_delay_seconds} || 120);
121 my $mx = int($a{max_delay_seconds} || 480);
122 my $mno = ($a{match_new_only}) ? 1 : 0;
123 my $mnar = ($a{match_no_admin_reply}) ? 1 : 0;
124 my $motmin = int($a{match_customer_other_threads_min} || 0);
125 my $motmax = int($a{match_customer_other_threads_max} || 999);
126 my $bho = ($a{business_hours_only}) ? 1 : 0;
127 my $bhs = int($a{business_hours_start} // 8);
128 my $bhe = int($a{business_hours_end} // 23);
129 my $pri = int($a{priority} || 100);
130 my $act = ($a{is_active} // 1) ? 1 : 0;
131
132 if ($id > 0) {
133 $self->{db}->db_readwrite($dbh, qq~
134 UPDATE auto_ack_rules SET
135 name=$name_q, description=$desc_q, template_id=$tpl,
136 site_scope=$ss_q, min_delay_seconds=$mn, max_delay_seconds=$mx,
137 match_new_only=$mno, match_no_admin_reply=$mnar,
138 match_customer_other_threads_min=$motmin,
139 match_customer_other_threads_max=$motmax,
140 business_hours_only=$bho, business_hours_start=$bhs,
141 business_hours_end=$bhe, priority=$pri, is_active=$act
142 WHERE id=$id LIMIT 1
143 ~, __FILE__, __LINE__);
144 } else {
145 $self->{db}->db_readwrite($dbh, qq~
146 INSERT INTO auto_ack_rules
147 (name, description, template_id, site_scope,
148 min_delay_seconds, max_delay_seconds,
149 match_new_only, match_no_admin_reply,
150 match_customer_other_threads_min, match_customer_other_threads_max,
151 business_hours_only, business_hours_start, business_hours_end,
152 priority, is_active, created_at)
153 VALUES
154 ($name_q, $desc_q, $tpl, $ss_q,
155 $mn, $mx, $mno, $mnar, $motmin, $motmax,
156 $bho, $bhs, $bhe, $pri, $act, NOW())
157 ~, __FILE__, __LINE__);
158 my $r = $self->{db}->db_readwrite($dbh, "SELECT LAST_INSERT_ID() AS id",
159 __FILE__, __LINE__);
160 $id = $r && $r->{id} ? $r->{id} : 0;
161 }
162 $self->{db}->db_disconnect($dbh);
163 return $id;
164}
165
166sub toggle_rule {
167 my ($self, $id) = @_;
168 $id = int($id || 0); return 0 unless $id > 0;
169 my $dbh = $self->{db}->db_connect or return 0;
170 $self->{db}->db_readwrite($dbh,
171 "UPDATE auto_ack_rules SET is_active=1-is_active WHERE id=$id LIMIT 1",
172 __FILE__, __LINE__);
173 $self->{db}->db_disconnect($dbh);
174 return 1;
175}
176
177#---------------------------------------------------------------------
178# discover() -- for each active rule, scan supported sites for eligible
179# threads, insert into auto_ack_queue. Returns count inserted.
180#---------------------------------------------------------------------
181sub discover {
182 my $self = shift;
183 return 0 unless $self->is_globally_enabled;
184
185 my $rules = $self->list_rules(active_only => 1);
186 return 0 unless @$rules;
187
188 my $supported = $self->{inbox}->supported_slugs;
189 my %slug_ok = map { $_ => 1 } @$supported;
190
191 my $inserted = 0;
192 foreach my $rule (@$rules) {
193 # Business hours short-circuit avoids per-site query cost outside window.
194 next unless $self->is_business_hours($rule);
195
196 my @scope_slugs;
197 if (($rule->{site_scope} || 'all') eq 'all') {
198 @scope_slugs = @$supported;
199 } else {
200 @scope_slugs = grep { $slug_ok{$_} }
201 split(/\s*,\s*/, $rule->{site_scope});
202 }
203 next unless @scope_slugs;
204
205 foreach my $slug (@scope_slugs) {
206 my $candidates = $self->_find_candidates($slug, $rule);
207 foreach my $cand (@$candidates) {
208 my $queued = $self->_enqueue($slug, $cand, $rule);
209 $inserted++ if $queued;
210 }
211 }
212 }
213 return $inserted;
214}
215
216#---------------------------------------------------------------------
217# _find_candidates($slug, $rule) -> arrayref of {thread_id, customer_user_id}
218#
219# Runs per-site via SiteConn. Handles PT column names via SupportInbox's
220# adapter (fetch adapter, use its column names in the SQL).
221#---------------------------------------------------------------------
222sub _find_candidates {
223 my ($self, $slug, $rule) = @_;
224 # Adapter columns for THIS site
225 my $adapter = MODS::MetaAdmin::SupportInbox->adapter_for($slug);
226 return [] unless $adapter;
227 my $cust_col = $adapter->{thread_customer_col};
228
229 # Only look at recent (72h) threads to keep the scan bounded.
230 # "new_only" = last message from customer/user (not admin) AND thread status
231 # is one of the "waiting for us" family (native to each site).
232 my @native_open_statuses;
233 push @native_open_statuses, $adapter->{status_reverse}{open};
234 push @native_open_statuses, $adapter->{status_reverse}{waiting_admin};
235 my %uniq; @native_open_statuses = grep { !$uniq{$_}++ } @native_open_statuses;
236 my $status_in = join(',', map { "'$_'" } @native_open_statuses);
237
238 my $admin_reply_clause = '';
239 if ($rule->{match_no_admin_reply}) {
240 $admin_reply_clause = qq~AND NOT EXISTS (
241 SELECT 1 FROM support_messages m
242 WHERE m.thread_id = t.id
243 AND m.$adapter->{message_sender_col} = '$adapter->{admin_sender_value}'
244 )~;
245 }
246
247 my $sql = qq~
248 SELECT t.id AS thread_id, t.$cust_col AS customer_user_id
249 FROM support_threads t
250 WHERE t.created_at >= DATE_SUB(NOW(), INTERVAL 72 HOUR)
251 AND t.$adapter->{thread_status_col} IN ($status_in)
252 $admin_reply_clause
253 ORDER BY t.id ASC
254 LIMIT 50
255 ~;
256 my $rows = $self->{sc}->query_many($slug, $sql) || [];
257
258 # Filter by "other open threads by same customer" bracket + by not-already-queued.
259 my $dbh = $self->{db}->db_connect;
260 my @keep;
261 foreach my $r (@$rows) {
262 my $tid = int($r->{thread_id} || 0);
263 my $cust = int($r->{customer_user_id} || 0);
264
265 # Dedup: already queued/sent for this (site, thread, template)?
266 my $already = $self->{db}->db_readwrite($dbh, qq~
267 SELECT id FROM auto_ack_queue
268 WHERE site='$slug' AND thread_id=$tid AND template_id=$rule->{template_id}
269 LIMIT 1
270 ~, __FILE__, __LINE__);
271 next if $already && $already->{id};
272
273 # Dedup: same template already sent to same customer on this site?
274 if ($cust) {
275 my $prev = $self->{db}->db_readwrite($dbh, qq~
276 SELECT id FROM auto_ack_queue
277 WHERE site='$slug' AND customer_user_id=$cust
278 AND template_id=$rule->{template_id}
279 AND status IN ('sent','queued')
280 LIMIT 1
281 ~, __FILE__, __LINE__);
282 next if $prev && $prev->{id};
283 }
284
285 # Other-open-threads-by-same-customer bracket
286 if ($cust) {
287 my $other_count = $self->_count_other_open_threads($slug, $cust, $tid, $adapter);
288 next if $other_count < ($rule->{match_customer_other_threads_min} || 0);
289 next if $other_count > ($rule->{match_customer_other_threads_max} || 999);
290 $r->{other_open} = $other_count;
291 }
292
293 push @keep, $r;
294 }
295 $self->{db}->db_disconnect($dbh);
296 return \@keep;
297}
298
299sub _count_other_open_threads {
300 my ($self, $slug, $cust, $tid, $adapter) = @_;
301 my $cust_col = $adapter->{thread_customer_col};
302 my @open_statuses;
303 push @open_statuses, $adapter->{status_reverse}{open};
304 push @open_statuses, $adapter->{status_reverse}{waiting_admin};
305 push @open_statuses, $adapter->{status_reverse}{waiting_customer};
306 my %uniq; @open_statuses = grep { !$uniq{$_}++ } @open_statuses;
307 my $status_in = join(',', map { "'$_'" } @open_statuses);
308 my $r = $self->{sc}->query_one($slug, qq~
309 SELECT COUNT(*) AS n FROM support_threads
310 WHERE $cust_col=$cust AND id != $tid
311 AND $adapter->{thread_status_col} IN ($status_in)
312 ~);
313 return $r ? int($r->{n} || 0) : 0;
314}
315
316sub _enqueue {
317 my ($self, $slug, $cand, $rule) = @_;
318 my $tid = int($cand->{thread_id});
319 my $cust = int($cand->{customer_user_id} || 0);
320 my $tpl = int($rule->{template_id});
321 my $rid = int($rule->{id});
322
323 # Random delay within rule window
324 my $mn = int($rule->{min_delay_seconds} || 60);
325 my $mx = int($rule->{max_delay_seconds} || 300);
326 $mx = $mn if $mx < $mn;
327 my $delay = $mn + int(rand($mx - $mn + 1));
328
329 my $dbh = $self->{db}->db_connect or return 0;
330 my $ok = eval {
331 $self->{db}->db_readwrite($dbh, qq~
332 INSERT INTO auto_ack_queue
333 (site, thread_id, customer_user_id, template_id, rule_id,
334 discovered_at, fire_at, status)
335 VALUES
336 ('$slug', $tid, $cust, $tpl, $rid,
337 NOW(), DATE_ADD(NOW(), INTERVAL $delay SECOND), 'queued')
338 ~, __FILE__, __LINE__);
339 1;
340 };
341 $self->{db}->db_disconnect($dbh);
342 if (!$ok) {
343 $self->_log("enqueue $slug/$tid tpl=$tpl failed: $@");
344 return 0;
345 }
346 $self->_log("queued $slug/$tid tpl=$tpl rule=$rid delay=${delay}s");
347 return 1;
348}
349
350#---------------------------------------------------------------------
351# fire_ready() -- process queued rows whose fire_at <= NOW(). Returns
352# arrayref of results.
353#---------------------------------------------------------------------
354sub fire_ready {
355 my $self = shift;
356 unless ($self->is_globally_enabled) {
357 $self->_log("global kill switch is OFF, skipping fire_ready");
358 return [];
359 }
360 my $dbh = $self->{db}->db_connect or return [];
361 my @due = $self->{db}->db_readwrite_multiple($dbh, qq~
362 SELECT * FROM auto_ack_queue
363 WHERE status='queued' AND fire_at <= NOW()
364 ORDER BY fire_at ASC
365 LIMIT 20
366 ~, __FILE__, __LINE__);
367 $self->{db}->db_disconnect($dbh);
368
369 my @results;
370 foreach my $q (@due) {
371 push @results, $self->_fire_one($q);
372 }
373 return \@results;
374}
375
376sub _fire_one {
377 my ($self, $q) = @_;
378 my $qid = int($q->{id});
379 my $slug = $q->{site};
380 my $tid = int($q->{thread_id});
381 my $tpl = int($q->{template_id});
382 my $rule = $self->get_rule($q->{rule_id});
383 my $tpl_row = $self->{canned}->get($tpl);
384
385 # Re-verify eligibility -- state may have shifted since queueing.
386 my $verdict = $self->_reverify($q, $rule);
387 if ($verdict->{skip}) {
388 $self->_mark_skipped($qid, $verdict->{reason});
389 $self->_log("skip q=$qid $slug/$tid: $verdict->{reason}");
390 return { id => $qid, action => 'skipped', reason => $verdict->{reason} };
391 }
392 unless ($tpl_row) {
393 $self->_mark_skipped($qid, 'template_deleted');
394 return { id => $qid, action => 'skipped', reason => 'template_deleted' };
395 }
396 unless ($tpl_row->{is_active}) {
397 $self->_mark_skipped($qid, 'template_inactive');
398 return { id => $qid, action => 'skipped', reason => 'template_inactive' };
399 }
400
401 # Expand template body against live thread context
402 my $thread_res = $self->{inbox}->get_thread($slug, $tid);
403 unless ($thread_res && $thread_res->{thread}) {
404 $self->_mark_skipped($qid, 'thread_missing');
405 return { id => $qid, action => 'skipped', reason => 'thread_missing' };
406 }
407 my $ctx = $self->{canned}->thread_context($thread_res->{thread});
408 my $body = $self->{canned}->expand($tpl_row->{body}, $ctx);
409
410 my ($ok, $ret) = $self->{inbox}->post_reply($slug, $tid, $body);
411 if ($ok) {
412 $self->_mark_sent($qid, $ret);
413 $self->_log("SENT q=$qid $slug/$tid tpl=$tpl mid=$ret");
414 return { id => $qid, action => 'sent', message_id => $ret };
415 } else {
416 $self->_mark_failed($qid, $ret);
417 $self->_log("FAIL q=$qid $slug/$tid: $ret");
418 return { id => $qid, action => 'failed', error => $ret };
419 }
420}
421
422#---------------------------------------------------------------------
423# _reverify -- last-mile guards. Runs immediately before firing.
424# Returns { skip => 0|1, reason => '...' }.
425#---------------------------------------------------------------------
426sub _reverify {
427 my ($self, $q, $rule) = @_;
428 return { skip => 1, reason => 'rule_deleted' } unless $rule;
429 return { skip => 1, reason => 'rule_disabled' } unless $rule->{is_active};
430 return { skip => 1, reason => 'outside_hours' } unless $self->is_business_hours($rule);
431
432 my $slug = $q->{site};
433 my $tid = int($q->{thread_id});
434 my $adapter = MODS::MetaAdmin::SupportInbox->adapter_for($slug);
435 return { skip => 1, reason => 'unsupported_site' } unless $adapter;
436
437 # Fetch current thread state
438 my $t = $self->{sc}->query_one($slug, qq~
439 SELECT id, $adapter->{thread_status_col} AS status,
440 $adapter->{thread_customer_col} AS customer_user_id
441 FROM support_threads WHERE id = $tid LIMIT 1
442 ~);
443 return { skip => 1, reason => 'thread_gone' } unless $t;
444
445 # If the thread has since been closed/resolved, skip.
446 my $native_status = $t->{status} || '';
447 my $normalized = $adapter->{status_map}{$native_status} || $native_status;
448 if ($normalized eq 'closed' || $normalized eq 'resolved') {
449 return { skip => 1, reason => 'thread_closed' };
450 }
451
452 # If any admin message has been posted since queueing, skip -- a human
453 # got there first, don't step on their reply.
454 if ($rule->{match_no_admin_reply}) {
455 my $adm = $self->{sc}->query_one($slug, qq~
456 SELECT COUNT(*) AS n FROM support_messages
457 WHERE thread_id = $tid
458 AND $adapter->{message_sender_col} = '$adapter->{admin_sender_value}'
459 ~);
460 return { skip => 1, reason => 'admin_replied_first' }
461 if $adm && $adm->{n};
462 }
463
464 return { skip => 0 };
465}
466
467sub _mark_sent {
468 my ($self, $qid, $mid) = @_;
469 $mid = int($mid || 0);
470 my $dbh = $self->{db}->db_connect or return;
471 $self->{db}->db_readwrite($dbh, qq~
472 UPDATE auto_ack_queue
473 SET status='sent', sent_at=NOW(), message_id=$mid
474 WHERE id=$qid LIMIT 1
475 ~, __FILE__, __LINE__);
476 $self->{db}->db_disconnect($dbh);
477}
478
479sub _mark_skipped {
480 my ($self, $qid, $reason) = @_;
481 my $dbh = $self->{db}->db_connect or return;
482 my $r_q = $dbh->quote($reason || '');
483 $self->{db}->db_readwrite($dbh, qq~
484 UPDATE auto_ack_queue
485 SET status='skipped', sent_at=NOW(), skip_reason=$r_q
486 WHERE id=$qid LIMIT 1
487 ~, __FILE__, __LINE__);
488 $self->{db}->db_disconnect($dbh);
489}
490
491sub _mark_failed {
492 my ($self, $qid, $err) = @_;
493 my $dbh = $self->{db}->db_connect or return;
494 my $e_q = $dbh->quote($err || '');
495 $self->{db}->db_readwrite($dbh, qq~
496 UPDATE auto_ack_queue
497 SET status='failed', sent_at=NOW(), error_message=$e_q
498 WHERE id=$qid LIMIT 1
499 ~, __FILE__, __LINE__);
500 $self->{db}->db_disconnect($dbh);
501}
502
503sub list_queued {
504 my ($self, %a) = @_;
505 my $limit = int($a{limit} || 30);
506 my $dbh = $self->{db}->db_connect or return [];
507 my @rows = $self->{db}->db_readwrite_multiple($dbh, qq~
508 SELECT q.*, t.name AS template_name, r.name AS rule_name
509 FROM auto_ack_queue q
510 LEFT JOIN canned_templates t ON t.id = q.template_id
511 LEFT JOIN auto_ack_rules r ON r.id = q.rule_id
512 WHERE q.status='queued'
513 ORDER BY q.fire_at ASC
514 LIMIT $limit
515 ~, __FILE__, __LINE__);
516 $self->{db}->db_disconnect($dbh);
517 return \@rows;
518}
519
520sub list_history {
521 my ($self, %a) = @_;
522 my $limit = int($a{limit} || 50);
523 my $dbh = $self->{db}->db_connect or return [];
524 my @rows = $self->{db}->db_readwrite_multiple($dbh, qq~
525 SELECT q.*, t.name AS template_name, r.name AS rule_name
526 FROM auto_ack_queue q
527 LEFT JOIN canned_templates t ON t.id = q.template_id
528 LEFT JOIN auto_ack_rules r ON r.id = q.rule_id
529 WHERE q.status IN ('sent','skipped','failed')
530 ORDER BY COALESCE(q.sent_at, q.discovered_at) DESC
531 LIMIT $limit
532 ~, __FILE__, __LINE__);
533 $self->{db}->db_disconnect($dbh);
534 return \@rows;
535}
536
5371;