prefix . 'mybot_contacts'; } /** * Whether the table is there. * * A plugin updated by copying files runs `maybe_upgrade()` on the next request, but a request that * arrives BEFORE that has to answer "not yet" rather than throw a database error at a visitor. * * @return bool */ public static function table_exists() { global $wpdb; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching return (bool) $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', self::table() ) ); } /** * Stores one visitor card, updating the card of a person already known. * * @param array $data Card data, already unslashed. * @return array|WP_Error The stored card, or an error the widget translates. */ public static function create( $data ) { global $wpdb; if ( ! self::table_exists() ) { return new WP_Error( 'mybot_contacts_table_missing', __( 'The contacts table is missing. Deactivate and activate the plugin once so the database upgrade can run.', 'aihouse' ) ); } $name = self::clean_name( isset( $data['name'] ) ? (string) $data['name'] : '' ); if ( '' === $name ) { return new WP_Error( 'mybot_contact_name', __( 'Please tell us your name.', 'aihouse' ) ); } $email = self::clean_email( isset( $data['email'] ) ? (string) $data['email'] : '' ); $phone = self::clean_phone( isset( $data['phone'] ) ? (string) $data['phone'] : '' ); /* * One of the two, not both — the owner's decision of 16/10/2026, and it is a decision about people * rather than about data: on a Vietnamese site many visitors will give a phone number and no email * at all, and a form that insists on an email address loses them. Which one they gave is stored in * `channel`, so a screen can say "call this one" without guessing. */ if ( '' === $email && '' === $phone ) { /* * ⚠️ **Two refusals, not one, and the difference is what the visitor is told.** `sanitize_email()` * turns `khong-phai-email` into an empty string, so a typed-but-unusable address would otherwise * be reported as "please leave a phone number or an email address" — to somebody who just typed * an email address into the email field. That message sends them looking for the wrong mistake. */ $typed_email = trim( isset( $data['email'] ) ? (string) $data['email'] : '' ); if ( '' !== $typed_email ) { return new WP_Error( 'mybot_contact_email', __( 'That email address does not look right.', 'aihouse' ) ); } return new WP_Error( 'mybot_contact_channel', __( 'Please leave a phone number or an email address so we can reach you.', 'aihouse' ) ); } if ( '' !== $email && ! is_email( $email ) ) { return new WP_Error( 'mybot_contact_email', __( 'That email address does not look right.', 'aihouse' ) ); } if ( '' !== $phone && ! self::phone_is_plausible( $phone ) ) { return new WP_Error( 'mybot_contact_phone', __( 'That phone number does not look right.', 'aihouse' ) ); } $session_id = isset( $data['session_id'] ) ? (string) $data['session_id'] : ''; $session_id = mybot()->security->clean_session_id( $session_id ); $note = isset( $data['note'] ) ? (string) $data['note'] : ''; $note = mb_substr( sanitize_textarea_field( $note ), 0, self::MAX_NOTE ); $lang = isset( $data['lang'] ) ? substr( sanitize_key( (string) $data['lang'] ), 0, 8 ) : ''; /* * The page the card was left on, WITHOUT its query string. * * A query string is where a site puts things like `?email=…` or a search term, and copying it here * would smuggle a second copy of personal data into a table that is already personal — for a field * whose only job is "which page were they reading". The path is kept, the query is dropped. */ $source = isset( $data['source'] ) ? (string) $data['source'] : ''; $source = (string) wp_parse_url( esc_url_raw( $source ), PHP_URL_PATH ); $source = mb_substr( $source, 0, 255 ); $consent = ! empty( $data['consent'] ); $now = current_time( 'mysql' ); $existing = self::find_existing( $session_id, $email, $phone ); $row = array( 'name' => $name, 'email' => $email, 'phone' => $phone, 'channel' => self::channel_of( $email, $phone ), 'note' => $note, 'lang' => $lang, 'source_url' => $source, 'updated_at' => $now, ); if ( $consent ) { /* * The sentence is taken from the LANGUAGE TABLE, by this class, and never from the caller. * * ⚠️ This is the whole point of storing it: the record has to show the words the visitor was * actually shown, and the only copy of those words the plugin can trust is the table the widget * reads. Accepting a sentence from the browser would let a client store agreement to anything — * and accepting it from a PHP caller would let the two copies drift, which is the same defect one * layer in. One source, read here, in the visitor's language. */ $strings = MYBOT_Languages::ui_strings( $lang ); $row['consent'] = 1; $row['consent_text'] = isset( $strings['contactConsent'] ) ? (string) $strings['contactConsent'] : ''; $row['consent_at'] = $now; } if ( $existing ) { $row['session_id'] = '' !== $session_id ? $session_id : (string) $existing['session_id']; $row['submissions'] = (int) $existing['submissions'] + 1; /* * An empty field never OVERWRITES what the visitor told us before: somebody who left a phone * number last week and only a name today has not withdrawn their number. A non-empty one does, * because that is what correcting a detail looks like. */ foreach ( array( 'email', 'phone', 'note' ) as $keep ) { if ( '' === $row[ $keep ] ) { $row[ $keep ] = (string) $existing[ $keep ]; } } $row['channel'] = self::channel_of( $row['email'], $row['phone'] ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $updated = $wpdb->update( self::table(), $row, array( 'id' => (int) $existing['id'] ) ); if ( false === $updated ) { return new WP_Error( 'mybot_contact_update', __( 'Your details could not be saved.', 'aihouse' ) ); } return self::find( (int) $existing['id'] ); } $row['session_id'] = $session_id; $row['submissions'] = 1; $row['created_at'] = $now; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $inserted = $wpdb->insert( self::table(), $row ); if ( false === $inserted ) { return new WP_Error( 'mybot_contact_insert', __( 'Your details could not be saved.', 'aihouse' ) ); } return self::find( (int) $wpdb->insert_id ); } /** * The card of a person already known, or null. * * The order is the order of certainty: the session that is chatting right now is the strongest * evidence, then the email, then the phone. A blank value never matches — otherwise every card with an * empty phone number would be "the same person". * * @param string $session_id Session id. * @param string $email Email address. * @param string $phone Phone number. * @return array|null */ public static function find_existing( $session_id, $email = '', $phone = '' ) { foreach ( array( 'session_id' => $session_id, 'email' => $email, 'phone' => $phone, ) as $column => $value ) { if ( '' === (string) $value ) { continue; } $row = self::first_where( $column, (string) $value ); if ( $row ) { return $row; } } return null; } /** * One card by id. * * @param int $id Card id. * @return array|null */ public static function find( $id ) { $row = self::first_where( 'id', (int) $id ); return $row ? $row : null; } /** * The most recent card of a session. * * @param string $session_id Session id. * @return array|null */ public static function for_session( $session_id ) { $session_id = mybot()->security->clean_session_id( (string) $session_id ); if ( '' === $session_id ) { return null; } return self::first_where( 'session_id', $session_id, 'id DESC' ); } /** * Recent cards, newest first. * * @param int $limit How many. * @param int $offset How many to skip. * @return array */ public static function recent( $limit = 50, $offset = 0 ) { global $wpdb; if ( ! self::table_exists() ) { return array(); } $table = self::table(); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $rows = $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i ORDER BY created_at DESC, id DESC LIMIT %d OFFSET %d', $table, max( 1, min( 200, absint( $limit ) ) ), max( 0, absint( $offset ) ) ), ARRAY_A ); return is_array( $rows ) ? $rows : array(); } /** * How many cards there are. * * @return int */ public static function count() { global $wpdb; if ( ! self::table_exists() ) { return 0; } $table = self::table(); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i', $table ) ); } /** * Deletes one card. * * @param int $id Card id. * @return bool */ public static function delete( $id ) { global $wpdb; if ( ! self::table_exists() ) { return false; } // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $deleted = $wpdb->delete( self::table(), array( 'id' => absint( $id ) ), array( '%d' ) ); return false !== $deleted && $deleted > 0; } /** * Every card matching an email address or a phone number. * * This is the door the WordPress privacy tools come in by: a visitor asks "what do you hold about me", * and the answer has to be looked up by the one thing they know — the address or the number they typed. * * @param string $email Email address. * @param string $phone Phone number. * @return array */ public static function matching( $email = '', $phone = '' ) { $rows = array(); foreach ( array( 'email' => self::clean_email( $email ), 'phone' => self::clean_phone( $phone ), ) as $column => $value ) { if ( '' === $value ) { continue; } foreach ( self::all_where( $column, $value ) as $row ) { $rows[ (int) $row['id'] ] = $row; } } return array_values( $rows ); } /** * Deletes every card matching an email address or a phone number. * * @param string $email Email address. * @param string $phone Phone number. * @return int Number of cards deleted. */ public static function erase( $email = '', $phone = '' ) { $deleted = 0; foreach ( self::matching( $email, $phone ) as $row ) { if ( self::delete( (int) $row['id'] ) ) { ++$deleted; } } return $deleted; } /** * Which channel a card was given through. * * @param string $email Email address. * @param string $phone Phone number. * @return string 'phone', 'email', 'both' or ''. */ public static function channel_of( $email, $phone ) { $email = '' !== (string) $email; $phone = '' !== (string) $phone; if ( $email && $phone ) { return 'both'; } if ( $phone ) { return self::CHANNEL_PHONE; } if ( $email ) { return self::CHANNEL_EMAIL; } return ''; } /** * A name a human typed. * * @param string $value Raw value. * @return string */ public static function clean_name( $value ) { return mb_substr( sanitize_text_field( (string) $value ), 0, 120 ); } /** * An email address, lower-cased so the same address matches itself. * * @param string $value Raw value. * @return string */ public static function clean_email( $value ) { return mb_strtolower( mb_substr( sanitize_email( (string) $value ), 0, 190 ) ); } /** * A phone number, cleaned to what a person would dial. * * Digits, and a leading `+` are kept; every space, dot, dash and bracket is removed, because a * Vietnamese number is written six different ways and the same person must not become six cards. * * @param string $value Raw value. * @return string */ public static function clean_phone( $value ) { $value = (string) $value; $plus = ( 0 === strpos( trim( $value ), '+' ) ) ? '+' : ''; $value = preg_replace( '/\D+/', '', $value ); return $plus . mb_substr( (string) $value, 0, 20 ); } /** * Whether a cleaned phone number could be dialled by a human. * * Deliberately loose. A number that is too short is a typo; a number that is "wrong" is a matter of * country, and this plugin cannot know every country's rules. 8 digits is the shortest national * numbering plan worth accepting, 15 is the ITU ceiling. * * @param string $phone Cleaned phone number. * @return bool */ public static function phone_is_plausible( $phone ) { $digits = preg_replace( '/\D+/', '', (string) $phone ); return strlen( (string) $digits ) >= 8 && strlen( (string) $digits ) <= 15; } /** * One row, by one column. * * @param string $column Column name, from a fixed list. * @param mixed $value Value. * @param string $order Optional ORDER BY fragment. * @return array|null */ protected static function first_where( $column, $value, $order = '' ) { global $wpdb; if ( ! self::table_exists() ) { return null; } $column = self::safe_column( $column ); $table = self::table(); $order_by = ( '' !== $order ) ? ' ORDER BY id DESC' : ''; $sql = ( 'id' === $column ) ? 'SELECT * FROM %i WHERE %i = %d' . $order_by : 'SELECT * FROM %i WHERE %i = %s' . $order_by; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared $row = $wpdb->get_row( $wpdb->prepare( $sql, $table, $column, $value ), ARRAY_A ); return is_array( $row ) ? $row : null; } /** * Every row, by one column. * * @param string $column Column name, from a fixed list. * @param mixed $value Value. * @return array */ protected static function all_where( $column, $value ) { global $wpdb; if ( ! self::table_exists() ) { return array(); } $column = self::safe_column( $column ); $table = self::table(); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $rows = $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE %i = %s', $table, $column, $value ), ARRAY_A ); return is_array( $rows ) ? $rows : array(); } /** * Refuses a column name that is not one of ours. * * The column arrives as a string, and a string that reaches SQL is an injection waiting for the one * caller who forgets to prepare it. Every column this class can search by is listed here, and anything * else becomes `id`, which matches at most one row and never leaks a different table's column. * * @param string $column Requested column. * @return string */ protected static function safe_column( $column ) { $allowed = array( 'id', 'session_id', 'email', 'phone' ); return in_array( $column, $allowed, true ) ? $column : 'id'; } }