PluginProbe
MxChat – AI Chatbot & Content Generation for WordPress / 3.2.2
MxChat – AI Chatbot & Content Generation for WordPress v3.2.2
3.2.21 3.2.20 3.2.19 3.2.18 3.2.17 3.2.16 3.2.15 3.2.14 3.2.12 3.2.13 3.2.11 3.2.10 3.2.9 3.2.8 3.2.7 3.2.6 3.2.5 3.2.4 3.2.3 3.2.2 3.2.1 2.0.3 2.0.4 2.0.5 2.0.6 All 152 releases
← All changes | includes/class-mxchat-admin.php +840 -30 3.2.1 → 3.2.2 View file →
@@ -54,8 +54,14 @@
54 54 add_action('admin_post_mxchat_add_intent', array($this, 'mxchat_handle_add_intent'));
55 55 add_action('admin_post_mxchat_delete_intent', array($this, 'mxchat_handle_delete_intent'));
56 56 add_action('admin_post_mxchat_edit_intent', array($this, 'mxchat_handle_edit_intent'));
57 57 add_action('wp_ajax_mxchat_export_transcripts', array($this, 'export_chat_transcripts'));
58 +
59 + // Leads tab (inside Transcripts)
60 + add_action('wp_ajax_mxchat_fetch_leads', array($this, 'mxchat_fetch_leads'));
61 + add_action('wp_ajax_mxchat_delete_leads', array($this, 'mxchat_delete_leads'));
62 + add_action('wp_ajax_mxchat_export_leads', array($this, 'mxchat_export_leads'));
63 +
58 64 add_action('admin_init', array($this, 'mxchat_transcripts_page_init'));
59 65 add_action('wp_ajax_dismiss_live_agent_notice', array($this, 'dismiss_live_agent_notice'));
60 66 add_action('wp_ajax_dismiss_theme_migration_notice', array($this, 'dismiss_theme_migration_notice'));
61 67 add_action('mxchat_cleanup_old_transcripts', array($this, 'cleanup_old_transcripts'));
@@ -1504,9 +1510,763 @@
1504 1510 fclose($output);
1505 1511 wp_die();
1506 1512 }
1507 1513
1514 +// ============================================================================
1515 +// Leads tab (inside Transcripts)
1516 +//
1517 +// Leads are derived from existing data — no dedicated table. Primary source:
1518 +// wp_mxchat_chat_transcripts rows where user_email is populated. Secondary
1519 +// source: wp_options entries `mxchat_email_{session_id}` / `mxchat_name_{sid}`
1520 +// for "orphan" leads who submitted the pre-chat form but never chatted.
1521 +// ============================================================================
1522 +
1508 1523 /**
1524 + * Fetch leads: dedup-by-email rows, stats strip, and top pages in one call.
1525 + */
1526 +public function mxchat_fetch_leads() {
1527 + if (!current_user_can('manage_options')) {
1528 + wp_send_json_error(['message' => 'Insufficient permissions']);
1529 + wp_die();
1530 + }
1531 +
1532 + global $wpdb;
1533 + $table = $wpdb->prefix . 'mxchat_chat_transcripts';
1534 +
1535 + $page = isset($_POST['page']) ? max(1, absint($_POST['page'])) : 1;
1536 + $per_page = isset($_POST['per_page']) ? min(100, max(10, absint($_POST['per_page']))) : 25;
1537 + $offset = ($page - 1) * $per_page;
1538 + $search = isset($_POST['search']) ? sanitize_text_field(wp_unslash($_POST['search'])) : '';
1539 + $date_range = isset($_POST['date_range']) ? sanitize_key($_POST['date_range']) : 'all';
1540 + $status = isset($_POST['status']) ? sanitize_key($_POST['status']) : 'all';
1541 + $page_filter = isset($_POST['page_url']) ? esc_url_raw(wp_unslash($_POST['page_url'])) : '';
1542 + $sort = isset($_POST['sort']) ? sanitize_key($_POST['sort']) : 'last_seen';
1543 + $sort_dir = (isset($_POST['sort_dir']) && $_POST['sort_dir'] === 'asc') ? 'ASC' : 'DESC';
1544 +
1545 + $date_cutoff = self::mxchat_leads_date_cutoff($date_range);
1546 + $has_page_url_column = !empty($wpdb->get_results("SHOW COLUMNS FROM $table LIKE 'originating_page_url'"));
1547 +
1548 + // Base WHERE for transcripts leads.
1549 + $where_clauses = ["user_email IS NOT NULL", "user_email != ''"];
1550 + $where_params = [];
1551 +
1552 + if ($date_cutoff) {
1553 + $where_clauses[] = 'timestamp >= %s';
1554 + $where_params[] = $date_cutoff;
1555 + }
1556 + if ($page_filter && $has_page_url_column) {
1557 + $where_clauses[] = 'originating_page_url = %s';
1558 + $where_params[] = $page_filter;
1559 + }
1560 + if ($search !== '') {
1561 + $like = '%' . $wpdb->esc_like($search) . '%';
1562 + $where_clauses[] = '(user_email LIKE %s OR user_name LIKE %s)';
1563 + $where_params[] = $like;
1564 + $where_params[] = $like;
1565 + }
1566 + $where_sql = 'WHERE ' . implode(' AND ', $where_clauses);
1567 +
1568 + // Aggregate query grouped by email.
1569 + $select_sql = $has_page_url_column
1570 + ? "SELECT user_email, MAX(timestamp) AS last_seen, MIN(timestamp) AS first_seen,
1571 + COUNT(DISTINCT session_id) AS conversation_count"
1572 + : "SELECT user_email, MAX(timestamp) AS last_seen, MIN(timestamp) AS first_seen,
1573 + COUNT(DISTINCT session_id) AS conversation_count";
1574 +
1575 + $order_column = in_array($sort, ['last_seen', 'conversation_count', 'first_seen'], true) ? $sort : 'last_seen';
1576 + $group_order_limit = " GROUP BY user_email ORDER BY {$order_column} {$sort_dir} LIMIT %d OFFSET %d";
1577 +
1578 + $transcripts_sql = $wpdb->prepare(
1579 + "{$select_sql} FROM {$table} {$where_sql}{$group_order_limit}",
1580 + array_merge($where_params, [$per_page, $offset])
1581 + );
1582 + $transcript_rows = $wpdb->get_results($transcripts_sql);
1583 +
1584 + // Count of unique transcript-based leads under the same filters.
1585 + $count_sql = $wpdb->prepare(
1586 + "SELECT COUNT(DISTINCT user_email) FROM {$table} {$where_sql}",
1587 + $where_params
1588 + );
1589 + $transcripts_lead_count = (int) $wpdb->get_var($count_sql);
1590 +
1591 + // Hydrate each row: name, latest_session_id, top page.
1592 + $leads = [];
1593 + foreach ($transcript_rows as $row) {
1594 + $detail = $has_page_url_column
1595 + ? $wpdb->get_row($wpdb->prepare(
1596 + "SELECT session_id, user_name, originating_page_url, originating_page_title
1597 + FROM {$table} WHERE user_email = %s ORDER BY timestamp DESC LIMIT 1",
1598 + $row->user_email
1599 + ))
1600 + : $wpdb->get_row($wpdb->prepare(
1601 + "SELECT session_id, user_name FROM {$table}
1602 + WHERE user_email = %s ORDER BY timestamp DESC LIMIT 1",
1603 + $row->user_email
1604 + ));
1605 +
1606 + $leads[] = [
1607 + 'email' => $row->user_email,
1608 + 'name' => isset($detail->user_name) ? (string) $detail->user_name : '',
1609 + 'conversation_count' => (int) $row->conversation_count,
1610 + 'last_seen' => $row->last_seen,
1611 + 'last_seen_display' => self::mxchat_leads_format_relative($row->last_seen),
1612 + 'first_seen' => $row->first_seen,
1613 + 'latest_session_id' => isset($detail->session_id) ? $detail->session_id : '',
1614 + 'top_page_url' => isset($detail->originating_page_url) ? $detail->originating_page_url : '',
1615 + 'top_page_title' => isset($detail->originating_page_title) ? $detail->originating_page_title : '',
1616 + 'is_orphan' => false,
1617 + 'status' => 'active',
1618 + ];
1619 + }
1620 +
1621 + // Non-transcript lead sources. Built once here, then filtered/merged based on the
1622 + // status filter below. Deduplication priority when the same email appears in multiple
1623 + // sources: transcripts > chat_deleted > orphan.
1624 + $transcripts_emails_seen = array_flip(array_map(
1625 + function ($r) { return strtolower($r['email']); },
1626 + $leads
1627 + ));
1628 +
1629 + // Chat-deleted leads: had a conversation that an admin removed. Preserved via
1630 + // mxchat_lead_del_* options, with timestamps so they respect date filters.
1631 + $chat_deleted_leads_all = [];
1632 + if ($status === 'all' || $status === 'chat_deleted') {
1633 + $chat_deleted_leads_all = self::mxchat_collect_chat_deleted_leads($search, $date_cutoff);
1634 + // Dedup: drop any chat_deleted row whose email is already in the transcripts set.
1635 + $chat_deleted_leads_all = array_values(array_filter(
1636 + $chat_deleted_leads_all,
1637 + function ($row) use ($transcripts_emails_seen) {
1638 + return !isset($transcripts_emails_seen[strtolower($row['email'])]);
1639 + }
1640 + ));
1641 + foreach ($chat_deleted_leads_all as $row) {
1642 + $transcripts_emails_seen[strtolower($row['email'])] = true;
1643 + }
1644 + }
1645 +
1646 + // Orphans: pre-chat form captures with no conversation. No timestamp, so skipped
1647 + // when a date filter is active.
1648 + $orphan_leads_all = [];
1649 + if (($status === 'all' || $status === 'orphan') && !$date_cutoff && !$page_filter) {
1650 + $orphan_leads_all = self::mxchat_collect_orphan_leads($search);
1651 + $orphan_leads_all = array_values(array_filter(
1652 + $orphan_leads_all,
1653 + function ($row) use ($transcripts_emails_seen) {
1654 + return !isset($transcripts_emails_seen[strtolower($row['email'])]);
1655 + }
1656 + ));
1657 + }
1658 +
1659 + // Apply status filter to the transcripts-derived list.
1660 + if ($status === 'orphan' || $status === 'chat_deleted') {
1661 + $leads = [];
1662 + $transcripts_lead_count = 0;
1663 + }
1664 +
1665 + // Stitch the current page from the three buckets in priority order.
1666 + $total_count = $transcripts_lead_count + count($chat_deleted_leads_all) + count($orphan_leads_all);
1667 + $remaining_slots = $per_page - count($leads);
1668 +
1669 + if ($remaining_slots > 0 && !empty($chat_deleted_leads_all)) {
1670 + $start = max(0, ($page - 1) * $per_page - $transcripts_lead_count);
1671 + if ($start < count($chat_deleted_leads_all)) {
1672 + $leads = array_merge($leads, array_slice($chat_deleted_leads_all, $start, $remaining_slots));
1673 + $remaining_slots = $per_page - count($leads);
1674 + }
1675 + }
1676 +
1677 + if ($remaining_slots > 0 && !empty($orphan_leads_all)) {
1678 + $before = $transcripts_lead_count + count($chat_deleted_leads_all);
1679 + $start = max(0, ($page - 1) * $per_page - $before);
1680 + if ($start < count($orphan_leads_all)) {
1681 + $leads = array_merge($leads, array_slice($orphan_leads_all, $start, $remaining_slots));
1682 + }
1683 + }
1684 +
1685 + $total_pages = $per_page > 0 ? (int) ceil($total_count / $per_page) : 1;
1686 +
1687 + // Stats strip: always computed over full dataset, unaffected by filters.
1688 + $stats = self::mxchat_leads_stats($table, $has_page_url_column);
1689 +
1690 + // Top pages: top 5 by distinct emails captured.
1691 + $top_pages = [];
1692 + if ($has_page_url_column) {
1693 + $top_pages_rows = $wpdb->get_results(
1694 + "SELECT originating_page_url AS url,
1695 + MAX(originating_page_title) AS title,
1696 + COUNT(DISTINCT user_email) AS lead_count
1697 + FROM {$table}
1698 + WHERE user_email IS NOT NULL AND user_email != ''
1699 + AND originating_page_url IS NOT NULL AND originating_page_url != ''
1700 + GROUP BY originating_page_url
1701 + ORDER BY lead_count DESC, url ASC
1702 + LIMIT 5"
1703 + );
1704 + foreach ($top_pages_rows as $p) {
1705 + $top_pages[] = [
1706 + 'url' => $p->url,
1707 + 'title' => $p->title ?: $p->url,
1708 + 'lead_count' => (int) $p->lead_count,
1709 + ];
1710 + }
1711 + }
1712 +
1713 + wp_send_json([
1714 + 'success' => true,
1715 + 'leads' => $leads,
1716 + 'page' => $page,
1717 + 'per_page' => $per_page,
1718 + 'total_count' => $total_count,
1719 + 'total_pages' => $total_pages,
1720 + 'showing_start' => $total_count === 0 ? 0 : ($offset + 1),
1721 + 'showing_end' => min($offset + $per_page, $total_count),
1722 + 'stats' => $stats,
1723 + 'top_pages' => $top_pages,
1724 + ]);
1725 + wp_die();
1726 +}
1727 +
1728 +/**
1729 + * Delete one or more leads by email. Removes every transcripts row for that
1730 + * email and cleans up related wp_options (mxchat_email_{sid}, mxchat_name_{sid},
1731 + * mxchat_history_{sid}) and any orphan option entries matching the email.
1732 + */
1733 +public function mxchat_delete_leads() {
1734 + if (!current_user_can('manage_options')) {
1735 + wp_send_json_error(['message' => 'Insufficient permissions']);
1736 + wp_die();
1737 + }
1738 + check_ajax_referer('mxchat_delete_leads', 'security');
1739 +
1740 + $emails_raw = isset($_POST['emails']) ? (array) wp_unslash($_POST['emails']) : [];
1741 + $emails = [];
1742 + foreach ($emails_raw as $e) {
1743 + $clean = sanitize_email((string) $e);
1744 + if ($clean) {
1745 + $emails[] = $clean;
1746 + }
1747 + }
1748 + if (empty($emails)) {
1749 + wp_send_json_error(['message' => 'No emails provided']);
1750 + wp_die();
1751 + }
1752 +
1753 + $summary = self::mxchat_wipe_leads_by_email($emails);
1754 +
1755 + wp_send_json([
1756 + 'success' => true,
1757 + 'deleted_leads' => count($emails),
1758 + 'deleted_sessions' => $summary['deleted_sessions'],
1759 + 'deleted_rows' => $summary['deleted_rows'],
1760 + ]);
1761 + wp_die();
1762 +}
1763 +
1764 +/**
1765 + * Fully wipe one or more leads by email: every transcripts row, every related wp_options
1766 + * entry (history, pre-chat capture, chat_deleted preservation, agent name, translations).
1767 + *
1768 + * Shared between the Leads-tab Delete button and the transcript-delete opt-in checkbox.
1769 + * Input emails must already be sanitized with sanitize_email().
1770 + */
1771 +private static function mxchat_wipe_leads_by_email(array $emails) {
1772 + global $wpdb;
1773 + $table = $wpdb->prefix . 'mxchat_chat_transcripts';
1774 + $translations_table = $wpdb->prefix . 'mxchat_transcript_translations';
1775 + $has_translations = $wpdb->get_var("SHOW TABLES LIKE '$translations_table'") === $translations_table;
1776 +
1777 + $deleted_sessions = 0;
1778 + $deleted_rows = 0;
1779 + $emails_lc = array_map('strtolower', $emails);
1780 +
1781 + foreach ($emails as $email) {
1782 + $session_ids = $wpdb->get_col($wpdb->prepare(
1783 + "SELECT DISTINCT session_id FROM {$table} WHERE user_email = %s",
1784 + $email
1785 + ));
1786 +
1787 + $rows_removed = $wpdb->delete($table, ['user_email' => $email], ['%s']);
1788 + if ($rows_removed !== false) {
1789 + $deleted_rows += (int) $rows_removed;
1790 + }
1791 +
1792 + foreach ($session_ids as $sid) {
1793 + $deleted_sessions++;
1794 + wp_cache_delete('chat_session_' . $sid, 'mxchat_chat_sessions');
1795 + delete_option('mxchat_history_' . $sid);
1796 + delete_option('mxchat_email_' . $sid);
1797 + delete_option('mxchat_name_' . $sid);
1798 + delete_option('mxchat_agent_name_' . $sid);
1799 + delete_option('mxchat_lead_del_email_' . $sid);
1800 + delete_option('mxchat_lead_del_name_' . $sid);
1801 + delete_option('mxchat_lead_del_ts_' . $sid);
1802 + if ($has_translations) {
1803 + $wpdb->delete($translations_table, ['session_id' => $sid], ['%s']);
1804 + }
1805 + }
1806 + }
1807 +
1808 + // Clean up any lingering option entries (orphan pre-chat captures + chat_deleted
1809 + // preservations) whose stored value matches one of the emails being wiped.
1810 + $lingering = $wpdb->get_results(
1811 + "SELECT option_name, option_value FROM {$wpdb->options}
1812 + WHERE option_name LIKE 'mxchat_email_%' OR option_name LIKE 'mxchat_lead_del_email_%'"
1813 + );
1814 + foreach ($lingering as $opt) {
1815 + if (!in_array(strtolower(trim($opt->option_value)), $emails_lc, true)) {
1816 + continue;
1817 + }
1818 + if (strpos($opt->option_name, 'mxchat_lead_del_email_') === 0) {
1819 + $sid = substr($opt->option_name, strlen('mxchat_lead_del_email_'));
1820 + delete_option('mxchat_lead_del_email_' . $sid);
1821 + delete_option('mxchat_lead_del_name_' . $sid);
1822 + delete_option('mxchat_lead_del_ts_' . $sid);
1823 + } else {
1824 + $sid = substr($opt->option_name, strlen('mxchat_email_'));
1825 + delete_option('mxchat_email_' . $sid);
1826 + delete_option('mxchat_name_' . $sid);
1827 + }
1828 + }
1829 +
1830 + wp_cache_delete('all_chat_sessions', 'mxchat_chat_sessions');
1831 +
1832 + return [
1833 + 'deleted_sessions' => $deleted_sessions,
1834 + 'deleted_rows' => $deleted_rows,
1835 + ];
1836 +}
1837 +
1838 +/**
1839 + * Stream a leads CSV. scope=all exports every lead under current filters is not
1840 + * supported to keep semantics simple; caller either exports all leads or a
1841 + * specific set of selected emails.
1842 + */
1843 +public function mxchat_export_leads() {
1844 + if (!current_user_can('manage_options')) {
1845 + wp_die(esc_html__('You do not have sufficient permissions to access this page.', 'mxchat'));
1846 + }
1847 + check_ajax_referer('mxchat_export_leads', 'security');
1848 +
1849 + $scope = isset($_POST['scope']) ? sanitize_key($_POST['scope']) : 'all';
1850 + $fields_mode = isset($_POST['fields']) ? sanitize_key($_POST['fields']) : 'email_and_name';
1851 + $emails_in = isset($_POST['emails']) ? (array) wp_unslash($_POST['emails']) : [];
1852 +
1853 + $emails_in_clean = [];
1854 + foreach ($emails_in as $e) {
1855 + $clean = sanitize_email((string) $e);
1856 + if ($clean) {
1857 + $emails_in_clean[] = $clean;
1858 + }
1859 + }
1860 +
1861 + global $wpdb;
1862 + $table = $wpdb->prefix . 'mxchat_chat_transcripts';
1863 + $has_page_url_column = !empty($wpdb->get_results("SHOW COLUMNS FROM $table LIKE 'originating_page_url'"));
1864 +
1865 + // Collect leads from transcripts.
1866 + $transcripts_sql = "SELECT user_email AS email,
1867 + MAX(timestamp) AS last_seen,
1868 + COUNT(DISTINCT session_id) AS conversation_count
1869 + FROM {$table}
1870 + WHERE user_email IS NOT NULL AND user_email != ''";
1871 + $params = [];
1872 + if ($scope === 'selected' && !empty($emails_in_clean)) {
1873 + $placeholders = implode(',', array_fill(0, count($emails_in_clean), '%s'));
1874 + $transcripts_sql .= " AND user_email IN ({$placeholders})";
1875 + $params = $emails_in_clean;
1876 + }
1877 + $transcripts_sql .= " GROUP BY user_email ORDER BY last_seen DESC";
1878 +
1879 + $rows = !empty($params)
1880 + ? $wpdb->get_results($wpdb->prepare($transcripts_sql, $params))
1881 + : $wpdb->get_results($transcripts_sql);
1882 +
1883 + // Hydrate each row with name + top page.
1884 + $export_rows = [];
1885 + foreach ($rows as $row) {
1886 + $detail = $has_page_url_column
1887 + ? $wpdb->get_row($wpdb->prepare(
1888 + "SELECT user_name, originating_page_url FROM {$table}
1889 + WHERE user_email = %s ORDER BY timestamp DESC LIMIT 1",
1890 + $row->email
1891 + ))
1892 + : $wpdb->get_row($wpdb->prepare(
1893 + "SELECT user_name FROM {$table}
1894 + WHERE user_email = %s ORDER BY timestamp DESC LIMIT 1",
1895 + $row->email
1896 + ));
1897 + $export_rows[] = [
1898 + 'email' => $row->email,
1899 + 'name' => isset($detail->user_name) ? (string) $detail->user_name : '',
1900 + 'conversation_count' => (int) $row->conversation_count,
1901 + 'last_seen' => $row->last_seen,
1902 + 'top_page_url' => isset($detail->originating_page_url) ? $detail->originating_page_url : '',
1903 + ];
1904 + }
1905 +
1906 + // Include orphan + chat_deleted leads when exporting all.
1907 + if ($scope === 'all') {
1908 + $transcripts_emails_lc = array_flip(array_map(
1909 + function ($r) { return strtolower($r['email']); },
1910 + $export_rows
1911 + ));
1912 + foreach (self::mxchat_collect_chat_deleted_leads('') as $cd) {
1913 + if (isset($transcripts_emails_lc[strtolower($cd['email'])])) continue;
1914 + $transcripts_emails_lc[strtolower($cd['email'])] = true;
1915 + $export_rows[] = [
1916 + 'email' => $cd['email'],
1917 + 'name' => $cd['name'],
1918 + 'conversation_count' => 0,
1919 + 'last_seen' => $cd['last_seen'],
1920 + 'top_page_url' => '',
1921 + ];
1922 + }
1923 + foreach (self::mxchat_collect_orphan_leads('') as $orphan) {
1924 + if (isset($transcripts_emails_lc[strtolower($orphan['email'])])) continue;
1925 + $transcripts_emails_lc[strtolower($orphan['email'])] = true;
1926 + $export_rows[] = [
1927 + 'email' => $orphan['email'],
1928 + 'name' => $orphan['name'],
1929 + 'conversation_count' => 0,
1930 + 'last_seen' => '',
1931 + 'top_page_url' => '',
1932 + ];
1933 + }
1934 + }
1935 +
1936 + if (empty($export_rows)) {
1937 + wp_send_json_error(['message' => 'No leads to export.']);
1938 + wp_die();
1939 + }
1940 +
1941 + $filename = 'mxchat-leads-' . date('Y-m-d') . '.csv';
1942 + header('Content-Type: text/csv');
1943 + header('Content-Disposition: attachment; filename="' . $filename . '"');
1944 + header('Pragma: no-cache');
1945 + header('Expires: 0');
1946 +
1947 + $output = fopen('php://output', 'w');
1948 + fputs($output, "\xEF\xBB\xBF"); // UTF-8 BOM for Excel
1949 +
1950 + if ($fields_mode === 'email_only') {
1951 + fputcsv($output, ['Email']);
1952 + foreach ($export_rows as $r) {
1953 + fputcsv($output, [$r['email']]);
1954 + }
1955 + } else {
1956 + fputcsv($output, ['Email', 'Name', 'Conversations', 'Last seen', 'Top page']);
1957 + foreach ($export_rows as $r) {
1958 + fputcsv($output, [
1959 + $r['email'],
1960 + $r['name'],
1961 + $r['conversation_count'],
1962 + $r['last_seen'],
1963 + $r['top_page_url'],
1964 + ]);
1965 + }
1966 + }
1967 +
1968 + fclose($output);
1969 + wp_die();
1970 +}
1971 +
1972 +/**
1973 + * Stats strip payload (independent of filters).
1974 + */
1975 +private static function mxchat_leads_stats($table, $has_page_url_column) {
1976 + global $wpdb;
1977 +
1978 + $total_transcripts_emails = (int) $wpdb->get_var(
1979 + "SELECT COUNT(DISTINCT user_email) FROM {$table}
1980 + WHERE user_email IS NOT NULL AND user_email != ''"
1981 + );
1982 +
1983 + $new_this_week = (int) $wpdb->get_var($wpdb->prepare(
1984 + "SELECT COUNT(*) FROM (
1985 + SELECT user_email FROM {$table}
1986 + WHERE user_email IS NOT NULL AND user_email != ''
1987 + GROUP BY user_email
1988 + HAVING MIN(timestamp) >= %s
1989 + ) AS new_leads",
1990 + gmdate('Y-m-d H:i:s', strtotime('-7 days'))
1991 + ));
1992 +
1993 + $total_convos = (int) $wpdb->get_var(
1994 + "SELECT COUNT(DISTINCT session_id) FROM {$table}
1995 + WHERE user_email IS NOT NULL AND user_email != ''"
1996 + );
1997 +
1998 + $orphan_count = count(self::mxchat_collect_orphan_leads(''));
1999 + $chat_deleted_count = self::mxchat_count_chat_deleted_leads();
2000 +
2001 + // Total leads = unique emails across all three sources (dedup priority: transcripts > chat_deleted > orphan
2002 + // is already enforced at collection time in mxchat_fetch_leads; stats re-apply it here).
2003 + $total_leads = $total_transcripts_emails + $chat_deleted_count + $orphan_count;
2004 +
2005 + $avg = $total_transcripts_emails > 0
2006 + ? round($total_convos / $total_transcripts_emails, 1)
2007 + : 0;
2008 +
2009 + // Orphan % reflects *true* orphans only (pre-chat dropoffs). Chat-deleted leads are
2010 + // excluded so the metric stays meaningful — admins shouldn't see their cleanups
2011 + // inflate this number.
2012 + $orphan_pct = $total_leads > 0
2013 + ? (int) round(($orphan_count / $total_leads) * 100)
2014 + : 0;
2015 +
2016 + return [
2017 + 'total_leads' => $total_leads,
2018 + 'new_this_week' => $new_this_week,
2019 + 'avg_convos' => $avg,
2020 + 'orphan_pct' => $orphan_pct,
2021 + 'orphan_count' => $orphan_count,
2022 + 'chat_deleted_count' => $chat_deleted_count,
2023 + ];
2024 +}
2025 +
2026 +/**
2027 + * Collect leads who had a conversation that an admin later deleted (preserved via
2028 + * mxchat_lead_del_* options). Returns rows tagged status='chat_deleted' with the
2029 + * original last-seen timestamp so they still sort and filter sensibly.
2030 + *
2031 + * @param string $search Optional email/name substring filter.
2032 + * @param string $date_cutoff Optional 'Y-m-d H:i:s' cutoff — only rows with last_ts >= cutoff.
2033 + * @return array
2034 + */
2035 +private static function mxchat_collect_chat_deleted_leads($search = '', $date_cutoff = '') {
2036 + global $wpdb;
2037 + $table = $wpdb->prefix . 'mxchat_chat_transcripts';
2038 +
2039 + $rows = $wpdb->get_results(
2040 + "SELECT option_name, option_value FROM {$wpdb->options}
2041 + WHERE option_name LIKE 'mxchat_lead_del_email_%'"
2042 + );
2043 + if (empty($rows)) {
2044 + return [];
2045 + }
2046 +
2047 + // Emails that currently have transcripts rows should not appear as chat_deleted —
2048 + // they've come back and chatted, so they're active leads again.
2049 + $emails_in_transcripts = array_map(
2050 + 'strtolower',
2051 + (array) $wpdb->get_col(
2052 + "SELECT DISTINCT user_email FROM {$table}
2053 + WHERE user_email IS NOT NULL AND user_email != ''"
2054 + )
2055 + );
2056 + $emails_in_transcripts = array_flip($emails_in_transcripts);
2057 +
2058 + $needle = strtolower(trim((string) $search));
2059 + $by_email = [];
2060 +
2061 + foreach ($rows as $opt) {
2062 + $email = sanitize_email(trim((string) $opt->option_value));
2063 + if (!$email) {
2064 + continue;
2065 + }
2066 + if (isset($emails_in_transcripts[strtolower($email)])) {
2067 + continue;
2068 + }
2069 + $sid = substr($opt->option_name, strlen('mxchat_lead_del_email_'));
2070 + if (!$sid) {
2071 + continue;
2072 + }
2073 + $name = (string) get_option('mxchat_lead_del_name_' . $sid, '');
2074 + $ts = (string) get_option('mxchat_lead_del_ts_' . $sid, '');
2075 +
2076 + if ($date_cutoff !== '' && ($ts === '' || $ts < $date_cutoff)) {
2077 + continue;
2078 + }
2079 + if ($needle !== '') {
2080 + $hay = strtolower($email . ' ' . $name);
2081 + if (strpos($hay, $needle) === false) {
2082 + continue;
2083 + }
2084 + }
2085 +
2086 + $key = strtolower($email);
2087 + if (!isset($by_email[$key]) || (isset($by_email[$key]['last_seen']) && $ts > $by_email[$key]['last_seen'])) {
2088 + $by_email[$key] = [
2089 + 'email' => $email,
2090 + 'name' => $name,
2091 + 'conversation_count' => 0,
2092 + 'last_seen' => $ts,
2093 + 'last_seen_display' => $ts ? self::mxchat_leads_format_relative($ts) : __('Chat deleted', 'mxchat'),
2094 + 'first_seen' => $ts,
2095 + 'latest_session_id' => '',
2096 + 'top_page_url' => '',
2097 + 'top_page_title' => '',
2098 + 'is_orphan' => false,
2099 + 'status' => 'chat_deleted',
2100 + ];
2101 + }
2102 + }
2103 +
2104 + // Newest chat_deleted first.
2105 + usort($by_email, function ($a, $b) {
2106 + return strcmp((string) $b['last_seen'], (string) $a['last_seen']);
2107 + });
2108 + return array_values($by_email);
2109 +}
2110 +
2111 +/**
2112 + * Count unique emails preserved as "chat deleted" (for the stats strip).
2113 + */
2114 +private static function mxchat_count_chat_deleted_leads() {
2115 + global $wpdb;
2116 + $table = $wpdb->prefix . 'mxchat_chat_transcripts';
2117 +
2118 + $emails = $wpdb->get_col(
2119 + "SELECT DISTINCT option_value FROM {$wpdb->options}
2120 + WHERE option_name LIKE 'mxchat_lead_del_email_%'"
2121 + );
2122 + if (empty($emails)) {
2123 + return 0;
2124 + }
2125 +
2126 + $transcripts_emails = array_map(
2127 + 'strtolower',
2128 + (array) $wpdb->get_col(
2129 + "SELECT DISTINCT user_email FROM {$table}
2130 + WHERE user_email IS NOT NULL AND user_email != ''"
2131 + )
2132 + );
2133 + $transcripts_emails = array_flip($transcripts_emails);
2134 +
2135 + $count = 0;
2136 + $seen = [];
2137 + foreach ($emails as $raw) {
2138 + $email = strtolower(trim((string) $raw));
2139 + if (!$email || isset($seen[$email]) || isset($transcripts_emails[$email])) {
2140 + continue;
2141 + }
2142 + $seen[$email] = true;
2143 + $count++;
2144 + }
2145 + return $count;
2146 +}
2147 +
2148 +/**
2149 + * Find leads who submitted the pre-chat form but never produced a transcripts row.
2150 + * Returned rows have no conversation_count, no timestamp.
2151 + *
2152 + * @param string $search Optional email/name substring filter.
2153 + * @return array
2154 + */
2155 +private static function mxchat_collect_orphan_leads($search = '') {
2156 + global $wpdb;
2157 + $table = $wpdb->prefix . 'mxchat_chat_transcripts';
2158 +
2159 + $option_rows = $wpdb->get_results(
2160 + "SELECT option_name, option_value FROM {$wpdb->options}
2161 + WHERE option_name LIKE 'mxchat_email_%'"
2162 + );
2163 + if (empty($option_rows)) {
2164 + return [];
2165 + }
2166 +
2167 + // Collect all session_ids that have real transcripts rows so we can exclude them.
2168 + $session_ids_with_rows = $wpdb->get_col(
2169 + "SELECT DISTINCT session_id FROM {$table}
2170 + WHERE user_email IS NOT NULL AND user_email != ''"
2171 + );
2172 + $session_ids_with_rows = array_flip($session_ids_with_rows);
2173 +
2174 + // Seen emails in transcripts (so orphans only include truly never-chatted leads).
2175 + $emails_in_transcripts = array_map(
2176 + 'strtolower',
2177 + (array) $wpdb->get_col(
2178 + "SELECT DISTINCT user_email FROM {$table}
2179 + WHERE user_email IS NOT NULL AND user_email != ''"
2180 + )
2181 + );
2182 + $emails_in_transcripts = array_flip($emails_in_transcripts);
2183 +
2184 + $orphans_by_email = [];
2185 + $needle = strtolower(trim((string) $search));
2186 +
2187 + foreach ($option_rows as $opt) {
2188 + $email = sanitize_email(trim((string) $opt->option_value));
2189 + if (!$email) {
2190 + continue;
2191 + }
2192 + $sid = substr($opt->option_name, strlen('mxchat_email_'));
2193 + if (!$sid) {
2194 + continue;
2195 + }
2196 + // Exclude leads who have any transcripts rows (they appear in the main list).
2197 + if (isset($emails_in_transcripts[strtolower($email)])) {
2198 + continue;
2199 + }
2200 + if (isset($session_ids_with_rows[$sid])) {
2201 + continue;
2202 + }
2203 +
2204 + $name_option = get_option('mxchat_name_' . $sid, '');
2205 + $name = is_string($name_option) ? trim($name_option) : '';
2206 +
2207 + if ($needle !== '') {
2208 + $hay = strtolower($email . ' ' . $name);
2209 + if (strpos($hay, $needle) === false) {
2210 + continue;
2211 + }
2212 + }
2213 +
2214 + $key = strtolower($email);
2215 + if (!isset($orphans_by_email[$key])) {
2216 + $orphans_by_email[$key] = [
2217 + 'email' => $email,
2218 + 'name' => $name,
2219 + 'conversation_count' => 0,
2220 + 'last_seen' => '',
2221 + 'last_seen_display' => __('No conversation yet', 'mxchat'),
2222 + 'first_seen' => '',
2223 + 'latest_session_id' => '',
2224 + 'top_page_url' => '',
2225 + 'top_page_title' => '',
2226 + 'is_orphan' => true,
2227 + 'status' => 'orphan',
2228 + ];
2229 + }
2230 + }
2231 +
2232 + return array_values($orphans_by_email);
2233 +}
2234 +
2235 +/**
2236 + * Map a date_range key to a SQL-comparable cutoff string, or '' for all-time.
2237 + */
2238 +private static function mxchat_leads_date_cutoff($date_range) {
2239 + switch ($date_range) {
2240 + case 'today': return gmdate('Y-m-d H:i:s', strtotime('-24 hours'));
2241 + case '7d': return gmdate('Y-m-d H:i:s', strtotime('-7 days'));
2242 + case '30d': return gmdate('Y-m-d H:i:s', strtotime('-30 days'));
2243 + case '90d': return gmdate('Y-m-d H:i:s', strtotime('-90 days'));
2244 + case 'all':
2245 + default: return '';
2246 + }
2247 +}
2248 +
2249 +/**
2250 + * Turn a UTC timestamp into a short relative display like "2h ago" or "Apr 12".
2251 + */
2252 +private static function mxchat_leads_format_relative($timestamp) {
2253 + if (!$timestamp) {
2254 + return '';
2255 + }
2256 + $ts = strtotime($timestamp . ' UTC');
2257 + if (!$ts) {
2258 + return '';
2259 + }
2260 + $diff = time() - $ts;
2261 + if ($diff < 60) return __('just now', 'mxchat');
2262 + if ($diff < 3600) return floor($diff / 60) . __('m ago', 'mxchat');
2263 + if ($diff < 86400) return floor($diff / 3600) . __('h ago', 'mxchat');
2264 + if ($diff < 604800) return floor($diff / 86400) . __('d ago', 'mxchat');
2265 + return wp_date('M j', $ts);
2266 +}
2267 +
2268 +/**
1509 2269 * Handle translation of chat messages via AJAX
1510 2270 */
1511 2271 public function mxchat_translate_messages() {
1512 2272 if (!current_user_can('manage_options')) {
@@ -2725,53 +3485,103 @@
2725 3485 echo wp_json_encode(['error' => esc_html__('You do not have sufficient permissions.', 'mxchat')]);
2726 3486 wp_die();
2727 3487 }
2728 3488 check_ajax_referer('mxchat_delete_chat_history', 'security');
3489 +
3490 + if (!isset($_POST['delete_session_ids']) || !is_array($_POST['delete_session_ids'])) {
3491 + echo wp_json_encode(['error' => esc_html__('No chat sessions selected for deletion.', 'mxchat')]);
3492 + wp_die();
3493 + }
3494 +
2729 3495 global $wpdb;
2730 3496 $table_name = $wpdb->prefix . 'mxchat_chat_transcripts';
3497 + $translations_table = $wpdb->prefix . 'mxchat_transcript_translations';
3498 + $has_translations = $wpdb->get_var("SHOW TABLES LIKE '$translations_table'") === $translations_table;
2731 3499
2732 - if (isset($_POST['delete_session_ids']) && is_array($_POST['delete_session_ids'])) {
2733 - $deleted_count = 0;
2734 - $translations_table = $wpdb->prefix . 'mxchat_transcript_translations';
3500 + // When true, any lead attached to these sessions is fully wiped (all their sessions,
3501 + // across the whole table). Default false: the chat rows go away but the lead is
3502 + // preserved as a separate "chat deleted" lead in the Leads tab.
3503 + $also_delete_lead = !empty($_POST['also_delete_lead']) && $_POST['also_delete_lead'] !== 'false';
2735 3504
2736 - foreach ($_POST['delete_session_ids'] as $session_id) {
2737 - $session_id_sanitized = sanitize_text_field($session_id);
3505 + $deleted_count = 0;
3506 + $preserved_as_deleted_leads = 0;
3507 + $emails_to_fully_wipe = [];
2738 3508
2739 - // Clear relevant cache before deletion
2740 - $cache_key = 'chat_session_' . $session_id_sanitized;
2741 - wp_cache_delete($cache_key, 'mxchat_chat_sessions');
3509 + foreach ((array) $_POST['delete_session_ids'] as $session_id) {
3510 + $session_id_sanitized = sanitize_text_field($session_id);
3511 + if ($session_id_sanitized === '') {
3512 + continue;
3513 + }
2742 3514
2743 - // Perform the deletion from the database table
2744 - $wpdb->delete($table_name, ['session_id' => $session_id_sanitized]);
3515 + // Capture the lead info attached to this session *before* we delete the rows.
3516 + $lead_row = $wpdb->get_row($wpdb->prepare(
3517 + "SELECT user_email, user_name, MAX(timestamp) AS last_ts
3518 + FROM {$table_name}
3519 + WHERE session_id = %s AND user_email IS NOT NULL AND user_email != ''
3520 + GROUP BY user_email, user_name
3521 + ORDER BY last_ts DESC LIMIT 1",
3522 + $session_id_sanitized
3523 + ));
2745 3524
2746 - // Delete any saved translations for this session
2747 - if ($wpdb->get_var("SHOW TABLES LIKE '$translations_table'") === $translations_table) {
2748 - $wpdb->delete($translations_table, ['session_id' => $session_id_sanitized]);
2749 - }
3525 + wp_cache_delete('chat_session_' . $session_id_sanitized, 'mxchat_chat_sessions');
3526 + $wpdb->delete($table_name, ['session_id' => $session_id_sanitized]);
2750 3527
2751 - // Delete the corresponding option entry from wp_options table
2752 - delete_option("mxchat_history_" . $session_id_sanitized);
3528 + if ($has_translations) {
3529 + $wpdb->delete($translations_table, ['session_id' => $session_id_sanitized]);
3530 + }
2753 3531
2754 - // Delete any associated metadata options
2755 - delete_option("mxchat_email_" . $session_id_sanitized);
2756 - delete_option("mxchat_agent_name_" . $session_id_sanitized);
3532 + delete_option('mxchat_history_' . $session_id_sanitized);
3533 + delete_option('mxchat_agent_name_' . $session_id_sanitized);
2757 3534
2758 - $deleted_count++;
3535 + if ($lead_row && !empty($lead_row->user_email)) {
3536 + if ($also_delete_lead) {
3537 + // Full-wipe requested — queue the email so that all their sessions and
3538 + // related options get swept below. Also clear this session's pre-chat
3539 + // capture options (they're no longer meaningful).
3540 + $emails_to_fully_wipe[strtolower($lead_row->user_email)] = $lead_row->user_email;
3541 + delete_option('mxchat_email_' . $session_id_sanitized);
3542 + delete_option('mxchat_name_' . $session_id_sanitized);
3543 + } else {
3544 + // Preserve the lead in a "Chat deleted" state via distinct option keys so
3545 + // they stay out of the orphan bucket (orphan = pre-chat form dropoff).
3546 + update_option('mxchat_lead_del_email_' . $session_id_sanitized, $lead_row->user_email, false);
3547 + if (!empty($lead_row->user_name)) {
3548 + update_option('mxchat_lead_del_name_' . $session_id_sanitized, $lead_row->user_name, false);
3549 + }
3550 + if (!empty($lead_row->last_ts)) {
3551 + update_option('mxchat_lead_del_ts_' . $session_id_sanitized, $lead_row->last_ts, false);
3552 + }
3553 + // Clean up pre-chat capture options for this session — chat_deleted supersedes.
3554 + delete_option('mxchat_email_' . $session_id_sanitized);
3555 + delete_option('mxchat_name_' . $session_id_sanitized);
3556 + $preserved_as_deleted_leads++;
3557 + }
3558 + } else {
3559 + // No lead attached — nothing to preserve. Clean up any orphan options anyway.
3560 + delete_option('mxchat_email_' . $session_id_sanitized);
3561 + delete_option('mxchat_name_' . $session_id_sanitized);
2759 3562 }
2760 3563
2761 - // Optionally, clear a general cache if you have one
2762 - wp_cache_delete('all_chat_sessions', 'mxchat_chat_sessions');
3564 + $deleted_count++;
3565 + }
2763 3566
2764 - echo wp_json_encode([
2765 - 'success' => sprintf(
2766 - esc_html__('%d chat session(s) have been deleted from all storage locations.', 'mxchat'),
2767 - $deleted_count
2768 - )
2769 - ]);
2770 - } else {
2771 - echo wp_json_encode(['error' => esc_html__('No chat sessions selected for deletion.', 'mxchat')]);
3567 + // Opt-in full-lead wipe: sweep every remaining row + every option key (including
3568 + // chat_deleted preservation) for each affected email. Reuses the same internal
3569 + // helper as the Leads-tab Delete button for consistency.
3570 + if (!empty($emails_to_fully_wipe)) {
3571 + self::mxchat_wipe_leads_by_email(array_values($emails_to_fully_wipe));
2772 3572 }
2773 3573
3574 + wp_cache_delete('all_chat_sessions', 'mxchat_chat_sessions');
3575 +
3576 + echo wp_json_encode([
3577 + 'success' => sprintf(
3578 + esc_html__('%d chat session(s) have been deleted.', 'mxchat'),
3579 + $deleted_count
3580 + ),
3581 + 'preserved_as_deleted_leads' => $preserved_as_deleted_leads,
3582 + 'leads_fully_wiped' => count($emails_to_fully_wipe),
3583 + ]);
2774 3584 wp_die();
2775 3585 }
2776 3586
2777 3587 /**