prefix . 'chatdozo_conversations'; } public static function t_msg() { global $wpdb; return $wpdb->prefix . 'chatdozo_messages'; } /* ---------- conversations ---------- */ public static function get( $id ) { global $wpdb; $id = (int) $id; if ( $id <= 0 ) { return null; } return $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}chatdozo_conversations WHERE id = %d", $id ), ARRAY_A ); } /** The visitor's current conversation for a token: open, or closed recently enough to reopen. */ public static function for_token( $token ) { global $wpdb; $token = preg_replace( '/[^a-f0-9]/', '', strtolower( (string) $token ) ); if ( strlen( $token ) !== 40 ) { return null; } $row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}chatdozo_conversations WHERE token = %s ORDER BY id DESC LIMIT 1", $token ), ARRAY_A ); if ( ! $row ) { return null; } if ( $row['status'] === 'closed' ) { $age = time() - strtotime( $row['closed_at'] . ' UTC' ); if ( $age > self::REOPEN_DAYS * 86400 ) { return null; } } return $row; } /** The visitor's last conversation by token, closed or not, for name/email and ban checks. */ public static function last_for_token( $token ) { global $wpdb; $token = preg_replace( '/[^a-f0-9]/', '', strtolower( (string) $token ) ); if ( strlen( $token ) !== 40 ) { return null; } return $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}chatdozo_conversations WHERE token = %s ORDER BY id DESC LIMIT 1", $token ), ARRAY_A ); } public static function create( $token, array $ctx ) { global $wpdb; $now = Plugin::now(); $geo = Util::geo(); $wpdb->insert( self::t_conv(), array( 'token' => $token, 'status' => 'open', 'page_url' => $ctx['page_url'] ?? '', 'page_title' => $ctx['page_title'] ?? '', 'referrer' => $ctx['referrer'] ?? '', 'source' => Util::source( $ctx['referrer'] ?? '', $ctx['page_url'] ?? '' ), 'device' => Util::device( $ctx['ua'] ?? '' ), 'ua' => mb_substr( (string) ( $ctx['ua'] ?? '' ), 0, 255 ), 'browser' => Util::browser( $ctx['ua'] ?? '' ), 'os' => Util::os( $ctx['ua'] ?? '' ), 'ip_hash' => Util::ip_hash(), 'ip' => Util::ip(), 'country' => $geo['country'], 'region' => $geo['region'], 'city' => $geo['city'], 'lang' => Util::lang(), 'tz' => mb_substr( (string) ( $ctx['tz'] ?? '' ), 0, 64 ), 'visitor_seen_at'=> $now, 'created_at' => $now, 'updated_at' => $now, ) ); return self::get( $wpdb->insert_id ); } public static function update( $id, array $fields ) { global $wpdb; $fields['updated_at'] = Plugin::now(); return $wpdb->update( self::t_conv(), $fields, array( 'id' => (int) $id ) ); } public static function reopen( $id ) { self::update( $id, array( 'status' => 'open', 'closed_at' => null ) ); } public static function close( $id ) { self::update( $id, array( 'status' => 'closed', 'closed_at' => Plugin::now() ) ); } /** Delete a conversation, its messages and any files they carried. */ public static function delete( $id ) { global $wpdb; $id = (int) $id; foreach ( self::messages( $id, 0, 5000 ) as $m ) { if ( ! empty( $m['attachment'] ) ) { Uploads::remove( $m['attachment'] ); } } $wpdb->delete( self::t_msg(), array( 'conversation_id' => $id ) ); $wpdb->delete( self::t_conv(), array( 'id' => $id ) ); } /** Retention: remove closed conversations older than N days. Runs opportunistically, no cron. */ public static function prune( $days ) { global $wpdb; $days = (int) $days; if ( $days <= 0 ) { return 0; } if ( get_transient( 'chatdozo_pruned' ) ) { return 0; } set_transient( 'chatdozo_pruned', 1, 6 * HOUR_IN_SECONDS ); $cut = gmdate( 'Y-m-d H:i:s', time() - $days * DAY_IN_SECONDS ); $ids = $wpdb->get_col( $wpdb->prepare( "SELECT id FROM {$wpdb->prefix}chatdozo_conversations WHERE status = 'closed' AND closed_at < %s LIMIT 200", $cut ) ); foreach ( $ids as $id ) { self::delete( $id ); } return count( $ids ); } /* ---------- messages ---------- */ /** * $sender: visitor | operator | note (private, operators only) | system; the Pro edition adds ai and auto * $extra: attachment (url), attachment_type (image|file), attachment_name */ public static function add_message( $conv_id, $sender, $body, $user_id = 0, array $extra = array() ) { global $wpdb; $now = Plugin::now(); $body = trim( wp_strip_all_tags( (string) $body ) ); $body = mb_substr( $body, 0, 4000 ); $wpdb->insert( self::t_msg(), array( 'conversation_id' => (int) $conv_id, 'sender' => $sender, 'user_id' => (int) $user_id, 'body' => $body, 'attachment' => (string) ( $extra['attachment'] ?? '' ), 'attachment_type' => (string) ( $extra['attachment_type'] ?? '' ), 'attachment_name' => mb_substr( (string) ( $extra['attachment_name'] ?? '' ), 0, 120 ), 'created_at' => $now, ) ); $mid = (int) $wpdb->insert_id; if ( $sender !== 'note' ) { $up = array( 'last_message_id' => $mid, 'last_message_at' => $now, 'last_from' => $sender ); if ( $sender === 'operator' ) { $up['operator_read_id'] = $mid; $up['operator_typing_at'] = null; } elseif ( $sender === 'visitor' ) { $up['visitor_read_id'] = $mid; $up['visitor_typing_at'] = null; $up['visitor_seen_at'] = $now; $up['visitor_draft'] = ''; } elseif ( $sender !== 'system' ) { $up['operator_read_id'] = $mid; } // anything else written from the site's side (the Pro edition's automatic lines and assistant): nothing for a person to do self::update( $conv_id, $up ); } else { self::update( $conv_id, array() ); } return self::message( $mid ); } public static function message( $id ) { global $wpdb; return $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}chatdozo_messages WHERE id = %d", (int) $id ), ARRAY_A ); } /** $public = true leaves private notes out (what the visitor may see). */ public static function messages( $conv_id, $after = 0, $limit = 200, $public = false ) { global $wpdb; if ( $public ) { // visitors never see notes return $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}chatdozo_messages WHERE conversation_id = %d AND id > %d AND sender <> 'note' ORDER BY id ASC LIMIT %d", (int) $conv_id, (int) $after, (int) $limit ), ARRAY_A ); } return $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}chatdozo_messages WHERE conversation_id = %d AND id > %d ORDER BY id ASC LIMIT %d", (int) $conv_id, (int) $after, (int) $limit ), ARRAY_A ); } /** Who wrote the first line of a conversation ('' when empty). A chat a person on the team opened starts with 'operator'. */ public static function first_sender( $conv_id ) { global $wpdb; return (string) $wpdb->get_var( $wpdb->prepare( "SELECT sender FROM {$wpdb->prefix}chatdozo_messages WHERE conversation_id = %d AND sender <> 'note' ORDER BY id ASC LIMIT 1", (int) $conv_id ) ); } public static function mark_emailed( $id ) { global $wpdb; $wpdb->update( self::t_msg(), array( 'emailed' => 1 ), array( 'id' => (int) $id ) ); } /* ---------- inbox ---------- */ /** * Lists for the inbox tabs. * needs = open and the visitor's last message is newer than what operators have read * mine = open and assigned to this operator * open = everything open * closed = closed */ public static function listing( $tab, $q = '', $limit = 100, $user_id = 0 ) { global $wpdb; $where = 'WHERE 1=1'; $params = array(); if ( $tab === 'closed' ) { $where .= " AND status = 'closed'"; } else { $where .= " AND status = 'open'"; if ( $tab === 'needs' ) { $where .= ' AND last_message_id > operator_read_id'; } if ( $tab === 'mine' ) { $where .= ' AND assigned_to = %d'; $params[] = (int) $user_id; } } if ( $q !== '' ) { $like = '%' . $wpdb->esc_like( $q ) . '%'; $where .= ' AND (visitor_name LIKE %s OR visitor_email LIKE %s OR page_title LIKE %s OR tags LIKE %s OR city LIKE %s)'; array_push( $params, $like, $like, $like, $like, $like ); } $sql = "SELECT * FROM {$wpdb->prefix}chatdozo_conversations $where ORDER BY last_message_at DESC, id DESC LIMIT %d"; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $where is built above from fixed SQL and %s/%d placeholders only $params[] = (int) $limit; return $wpdb->get_results( $wpdb->prepare( $sql, $params ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql holds only fixed SQL and placeholders; every value arrives through $params } public static function counts( $user_id = 0 ) { global $wpdb; $row = $wpdb->get_row( $wpdb->prepare( "SELECT SUM(status='open' AND last_message_id > operator_read_id) AS needs, SUM(status='open' AND assigned_to = %d) AS mine, SUM(status='open') AS open_count, SUM(status='closed') AS closed_count FROM {$wpdb->prefix}chatdozo_conversations", (int) $user_id ), ARRAY_A ); return array( 'needs' => (int) ( $row['needs'] ?? 0 ), 'mine' => (int) ( $row['mine'] ?? 0 ), 'open' => (int) ( $row['open_count'] ?? 0 ), 'closed' => (int) ( $row['closed_count'] ?? 0 ), ); } /** Last public message for a set of conversations, keyed by conversation id. */ public static function previews( array $ids ) { global $wpdb; $ids = array_values( array_filter( array_map( 'intval', $ids ) ) ); if ( ! $ids ) { return array(); } $ph = implode( ',', array_fill( 0, count( $ids ), '%d' ) ); $rows = $wpdb->get_results( $wpdb->prepare( "SELECT m.conversation_id, m.sender, m.body, m.attachment_type FROM {$wpdb->prefix}chatdozo_messages m INNER JOIN (SELECT MAX(id) AS id FROM {$wpdb->prefix}chatdozo_messages WHERE conversation_id IN ($ph) AND sender <> 'note' GROUP BY conversation_id) x ON x.id = m.id", $ids ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- $ph is a list of %d placeholders, one per id $out = array(); foreach ( $rows as $r ) { $out[ (int) $r['conversation_id'] ] = $r; } return $out; } public static function visits_for_token( $token ) { global $wpdb; return (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$wpdb->prefix}chatdozo_conversations WHERE token = %s", $token ) ); } /* ---------- bans ---------- */ public static function banned_list() { $b = get_option( 'chatdozo_banned', array() ); return is_array( $b ) ? $b : array(); } public static function ban( array $conv ) { $b = self::banned_list(); $b[ $conv['ip_hash'] ] = array( 'ip' => $conv['ip'] ?? '', 'name' => $conv['visitor_name'], 'token' => $conv['token'], 'at' => Plugin::now(), 'conv' => (int) $conv['id'] ); update_option( 'chatdozo_banned', $b, false ); self::update( $conv['id'], array( 'banned' => 1 ) ); } public static function unban( $hash ) { $b = self::banned_list(); if ( isset( $b[ $hash ] ) ) { $conv = (int) ( $b[ $hash ]['conv'] ?? 0 ); unset( $b[ $hash ] ); update_option( 'chatdozo_banned', $b, false ); if ( $conv ) { self::update( $conv, array( 'banned' => 0 ) ); } } } public static function is_banned( $token = '' ) { $b = self::banned_list(); if ( ! $b ) { return false; } if ( isset( $b[ Util::ip_hash() ] ) ) { return true; } if ( $token ) { foreach ( $b as $row ) { if ( ( $row['token'] ?? '' ) === $token ) { return true; } } } return false; } /* ---------- presence ---------- */ /** * Last seen: the admin bar (any WordPress page), the inbox and the phone app all beat. The row holds the time only; * on or off duty is user meta, written in one place (set_duty), so a heartbeat landing mid-change can never undo a switch. */ public static function heartbeat( $user_id, $away = null ) { $p = get_option( 'chatdozo_presence', array() ); if ( ! is_array( $p ) ) { $p = array(); } $p[ (int) $user_id ] = array( 'ts' => time() ); foreach ( $p as $uid => $row ) { $ts = is_array( $row ) ? (int) ( $row['ts'] ?? 0 ) : (int) $row; if ( time() - $ts > 600 ) { unset( $p[ $uid ] ); } } update_option( 'chatdozo_presence', $p, false ); if ( $away !== null ) { self::set_duty( $user_id, ! $away ); } } /** A phone that gets pings counts as being there: the chat wakes it. The Pro edition answers; the free edition has no phones. */ public static function has_phone( $uid ) { return (bool) apply_filters( 'chatdozo_has_phone', false, (int) $uid ); } /** Who can take a chat this minute: on duty, and either the inbox is open or (Pro) a phone is set to ping. */ private static function present() { $p = get_option( 'chatdozo_presence', array() ); $out = array(); if ( ! is_array( $p ) ) { $p = array(); } foreach ( $p as $uid => $row ) { $ts = is_array( $row ) ? (int) ( $row['ts'] ?? 0 ) : (int) $row; if ( time() - $ts < 120 && ! self::is_away( $uid ) ) { $out[] = (int) $uid; } } foreach ( (array) apply_filters( 'chatdozo_present_extra', array(), $out ) as $uid ) { if ( ! in_array( (int) $uid, $out, true ) && ! self::is_away( $uid ) ) { $out[] = (int) $uid; } } return $out; } /** True when any operator has had the inbox open in the last two minutes and is not set to Away. */ public static function online() { return count( self::present() ) > 0; } public static function online_names() { $out = array(); foreach ( self::present() as $uid ) { $u = get_user_by( 'id', $uid ); if ( $u ) { $out[] = Util::op_name( $u->ID ); } } return $out; } /** On or off duty: one flag, one place. The last-seen time is not touched, so nobody looks present who is not. */ public static function set_duty( $user_id, $on_duty ) { update_user_meta( (int) $user_id, 'chatdozo_away', $on_duty ? 0 : 1 ); } /** Everyone who can answer: on duty or not, and whether their inbox is open right now. */ public static function duty() { $p = get_option( 'chatdozo_presence', array() ); if ( ! is_array( $p ) ) { $p = array(); } $out = array(); foreach ( Roles::operators() as $u ) { $row = isset( $p[ $u->ID ] ) ? $p[ $u->ID ] : null; $ts = is_array( $row ) ? (int) ( $row['ts'] ?? 0 ) : (int) $row; $name = Util::op_name( $u->ID ); $acct = $u->display_name && strcasecmp( trim( $u->display_name ), trim( $name ) ) !== 0 ? $u->display_name : ''; // a persona ("Tomo-chan") still says whose login it is $out[] = array( 'id' => (int) $u->ID, 'name' => $name, 'account' => $acct, 'face' => Util::avatar( $u->ID, 64 ), 'present' => $ts && time() - $ts < 120, 'phone' => self::has_phone( $u->ID ), 'duty' => ! self::is_away( $u->ID ) ); } return $out; } /** * Who answers a visitor who writes this minute, in one plain sentence, with a kind for colour: * live (a person) or away (nobody; the away line and email). The Pro edition adds its own kinds through chatdozo_situation. */ public static function situation() { $names = self::online_names(); $hours = Options::hours_state(); $list = function ( $n ) { $n = array_values( $n ); return count( $n ) > 1 ? implode( ', ', array_slice( $n, 0, -1 ) ) . ' ' . __( 'and', 'chatdozo' ) . ' ' . end( $n ) : (string) reset( $n ); }; $gone = array(); // on duty, but not in WordPress and no phone set to ping: the switch is on, the person is not there foreach ( self::duty() as $d ) { if ( $d['duty'] && ! $d['present'] && ! $d['phone'] ) { $gone[] = $d['name']; } } if ( $names ) { /* translators: %s: names of the people on duty */ $sit = array( 'kind' => 'live', 'short' => sprintf( __( 'Live: %s', 'chatdozo' ), $list( $names ) ), 'line' => sprintf( _n( '%s answers.', '%s answer.', count( $names ), 'chatdozo' ), $list( $names ) ) . ' ' . __( 'If they go off duty, visitors leave a message.', 'chatdozo' ) ); } elseif ( $hours['on'] && ! $hours['open'] ) { $sit = array( 'kind' => 'away', 'short' => __( 'Nobody on duty', 'chatdozo' ), 'line' => __( 'Closed hours and nobody on duty: visitors see the closed line and leave a message.', 'chatdozo' ) ); } elseif ( $gone ) { /* translators: %s: names of the people on duty */ $sit = array( 'kind' => 'away', 'short' => __( 'Nobody on duty', 'chatdozo' ), 'line' => sprintf( _n( '%s is on duty but not in WordPress, so visitors see the away line and leave a message.', '%s are on duty but not in WordPress, so visitors see the away line and leave a message.', count( $gone ), 'chatdozo' ), $list( $gone ) ) ); } else { $sit = array( 'kind' => 'away', 'short' => __( 'Nobody on duty', 'chatdozo' ), 'line' => __( 'Nobody is on duty: visitors see the away line and leave a message.', 'chatdozo' ) ); } return (array) apply_filters( 'chatdozo_situation', $sit, $names, $hours, $gone, $list ); } public static function is_away( $user_id ) { return (bool) get_user_meta( (int) $user_id, 'chatdozo_away', true ); } }