! empty( $f['key'] ) ) ) : array(); $keys = array_map( static fn( $f ) => (string) $f['key'], $fields ); $header = array_merge( array( 'Email', 'First name', 'Last name', 'Status', 'Tags', 'Source', 'Customer type', 'Consent source', 'Confirmed at', 'Created at' ), array_map( static fn( $f ) => (string) ( $f['label'] ?? $f['key'] ), $fields ) ); $lines = array( self::csv_line( $header ) ); $contacts = Mailpuff_Schema::table( Mailpuff_Schema::SUBSCRIBERS ); $groups = Mailpuff_Schema::table( Mailpuff_Schema::LISTS ); $members = Mailpuff_Schema::table( Mailpuff_Schema::SUBSCRIBER_LISTS ); $meta = Mailpuff_Schema::table( Mailpuff_Schema::SUBSCRIBER_META ); foreach ( array_chunk( array_values( array_map( 'intval', $ids ) ), 500 ) as $chunk ) { $in = implode( ',', array_fill( 0, count( $chunk ), '%d' ) ); // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- ids bound through the %d lists. $rows = array(); foreach ( (array) $wpdb->get_results( $wpdb->prepare( "SELECT id, email, first_name, last_name, status, source, customer_type, consent_source, confirmed_at, created_at FROM %i WHERE id IN ($in)", array_merge( array( $contacts ), $chunk ) ) ) as $row ) { $rows[ (int) $row->id ] = $row; } $names = array(); foreach ( (array) $wpdb->get_results( $wpdb->prepare( "SELECT m.contact_id, g.name FROM %i m INNER JOIN %i g ON g.id = m.group_id WHERE m.contact_id IN ($in) ORDER BY g.name ASC", array_merge( array( $members, $groups ), $chunk ) ) ) as $row ) { $names[ (int) $row->contact_id ][] = (string) $row->name; } $values = array(); if ( $keys ) { $key_in = implode( ',', array_fill( 0, count( $keys ), '%s' ) ); foreach ( (array) $wpdb->get_results( $wpdb->prepare( "SELECT contact_id, meta_key, meta_value FROM %i WHERE contact_id IN ($in) AND meta_key IN ($key_in)", array_merge( array( $meta ), $chunk, $keys ) ) ) as $row ) { $values[ (int) $row->contact_id ][ (string) $row->meta_key ] = (string) $row->meta_value; } } // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare foreach ( $chunk as $id ) { if ( ! isset( $rows[ $id ] ) ) { continue; } $c = $rows[ $id ]; $line = array( $c->email, $c->first_name, $c->last_name, $c->status, implode( '; ', $names[ $id ] ?? array() ), $c->source, $c->customer_type, $c->consent_source, $c->confirmed_at, $c->created_at ); foreach ( $keys as $key ) { $line[] = $values[ $id ][ $key ] ?? ''; } $lines[] = self::csv_line( $line ); } } return implode( "\r\n", $lines ) . "\r\n"; } /** * One CSV line of quoted, formula-safe cells. * * @param array $cells Cell values. * @return string */ public static function csv_line( array $cells ) { return implode( ',', array_map( static function ( $value ) { $text = (string) $value; if ( preg_match( '/^[=+\-@\t\r]/', $text ) ) { $text = "'" . $text; } return '"' . str_replace( '"', '""', $text ) . '"'; }, $cells ) ); } /** * Export all plugin data as an array. * * @param array $sections Which data types to include. Default: all. * Accepts: campaigns, contacts, lists, settings. * @return array The backup data with metadata. */ public function export( $sections = array() ) { global $wpdb; $all = empty( $sections ); $data = array(); if ( $all || in_array( 'campaigns', $sections, true ) ) { $table = Mailpuff_Schema::table( Mailpuff_Schema::CAMPAIGNS ); $data['campaigns'] = $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i', $table ), ARRAY_A ); } if ( $all || in_array( 'contacts', $sections, true ) ) { $table = Mailpuff_Schema::table( Mailpuff_Schema::SUBSCRIBERS ); // Deliberately omit unsubscribe_token — it's a per-contact secret // (anyone holding it can unsubscribe that contact and it appears in // tracking URLs). Import regenerates a fresh token when it's absent. $data['contacts'] = $wpdb->get_results( $wpdb->prepare( 'SELECT id, email, first_name, last_name, status, customer_type, gross_revenue, source, consent_source, confirmed_at, created_at, updated_at FROM %i', $table ), ARRAY_A ); } if ( $all || in_array( 'lists', $sections, true ) ) { $table = Mailpuff_Schema::table( Mailpuff_Schema::LISTS ); $data['lists'] = $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i', $table ), ARRAY_A ); $junction_table = Mailpuff_Schema::table( Mailpuff_Schema::SUBSCRIBER_LISTS ); $data['contact_lists'] = $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i', $junction_table ), ARRAY_A ); // Segments name groups by id, so they travel with the groups. $segments_table = Mailpuff_Schema::table( Mailpuff_Schema::SEGMENTS ); $data['segments'] = $wpdb->get_results( $wpdb->prepare( 'SELECT name, filter FROM %i', $segments_table ), ARRAY_A ); } if ( $all || in_array( 'settings', $sections, true ) ) { $data['settings'] = array( 'brand' => get_option( 'mailpuff_brand_settings', array() ), 'email' => get_option( 'mailpuff_email_settings', array() ), 'compliance' => get_option( 'mailpuff_compliance_settings', array() ), ); } return array( 'version' => MAILPUFF_VERSION, 'site' => wp_parse_url( home_url(), PHP_URL_HOST ), 'exported_at' => current_time( 'mysql' ), 'data' => $data, ); } /** * Import data from a backup array. * * @param array $backup The parsed backup data. * @param string $mode 'merge' (skip duplicates) or 'replace' (delete existing first). * @return array|WP_Error { imported: { campaigns: N, contacts: N, ... }, skipped: N, errors: [] } */ public function import( $backup, $mode = 'merge' ) { global $wpdb; if ( ! is_array( $backup ) || empty( $backup['data'] ) || ! is_array( $backup['data'] ) ) { return new WP_Error( 'invalid_backup', __( 'Invalid backup format.', 'mailpuff' ), array( 'status' => 400 ) ); } $data = $backup['data']; $result = array( 'imported' => array(), 'skipped' => 0, 'errors' => array(), ); // In replace mode, truncate target tables first. if ( 'replace' === $mode ) { $this->truncate_tables( $data ); } // Import contacts (need ID mapping for contact_lists). $contact_id_map = array(); if ( ! empty( $data['contacts'] ) && is_array( $data['contacts'] ) ) { $table = Mailpuff_Schema::table( Mailpuff_Schema::SUBSCRIBERS ); $count = 0; foreach ( $data['contacts'] as $row ) { $email = isset( $row['email'] ) ? sanitize_email( $row['email'] ) : ''; if ( empty( $email ) ) { ++$result['skipped']; continue; } $old_id = isset( $row['id'] ) ? absint( $row['id'] ) : 0; unset( $row['id'] ); $row['email'] = $email; if ( 'merge' === $mode ) { $existing = $wpdb->get_var( $wpdb->prepare( 'SELECT id FROM %i WHERE email = %s', $table, $email ) ); if ( $existing ) { $contact_id_map[ $old_id ] = absint( $existing ); ++$result['skipped']; continue; } } $row = $this->whitelist_row( $row, 'contacts' ); // Clamp status to a known value (never trust the backup blindly). $row['status'] = Mailpuff_Schema::normalize_status( $row['status'] ?? '' ); // Always a fresh token: a backup never carries one (export leaves it // out), and one in a crafted file would let its author unsubscribe // or edit that contact. $row['unsubscribe_token'] = wp_generate_password( 32, false ); $row['email_hash'] = Mailpuff_Schema::email_hash( $email ); $inserted = $wpdb->insert( $table, $row ); if ( false === $inserted ) { $result['errors'][] = sprintf( 'Contact %s: %s', $email, $wpdb->last_error ); } else { $contact_id_map[ $old_id ] = absint( $wpdb->insert_id ); ++$count; } } $result['imported']['contacts'] = $count; } // Import lists (need ID mapping for contact_lists). $list_id_map = array(); if ( ! empty( $data['lists'] ) && is_array( $data['lists'] ) ) { $table = Mailpuff_Schema::table( Mailpuff_Schema::LISTS ); $count = 0; foreach ( $data['lists'] as $row ) { $name = isset( $row['name'] ) ? sanitize_text_field( $row['name'] ) : ''; if ( empty( $name ) ) { ++$result['skipped']; continue; } $old_id = isset( $row['id'] ) ? absint( $row['id'] ) : 0; unset( $row['id'] ); $row['name'] = $name; if ( 'merge' === $mode ) { $existing = $wpdb->get_var( $wpdb->prepare( 'SELECT id FROM %i WHERE name = %s', $table, $name ) ); if ( $existing ) { $list_id_map[ $old_id ] = absint( $existing ); ++$result['skipped']; continue; } } $inserted = $wpdb->insert( $table, $this->whitelist_row( $row, 'lists' ) ); if ( false === $inserted ) { $result['errors'][] = sprintf( 'List %s: %s', $name, $wpdb->last_error ); } else { $list_id_map[ $old_id ] = absint( $wpdb->insert_id ); ++$count; } } $result['imported']['lists'] = $count; } // Import contact_lists junction table with remapped IDs. if ( ! empty( $data['contact_lists'] ) && is_array( $data['contact_lists'] ) ) { $table = Mailpuff_Schema::table( Mailpuff_Schema::SUBSCRIBER_LISTS ); $count = 0; foreach ( $data['contact_lists'] as $row ) { $old_contact_id = isset( $row['contact_id'] ) ? absint( $row['contact_id'] ) : 0; $old_group_id = isset( $row['group_id'] ) ? absint( $row['group_id'] ) : 0; $new_contact_id = isset( $contact_id_map[ $old_contact_id ] ) ? $contact_id_map[ $old_contact_id ] : 0; $new_group_id = isset( $list_id_map[ $old_group_id ] ) ? $list_id_map[ $old_group_id ] : 0; if ( ! $new_contact_id || ! $new_group_id ) { ++$result['skipped']; continue; } $insert_data = array( 'contact_id' => $new_contact_id, 'group_id' => $new_group_id, ); if ( ! empty( $row['added_at'] ) ) { $insert_data['added_at'] = $row['added_at']; } $inserted = $wpdb->insert( $table, $insert_data ); if ( false === $inserted ) { // Likely a duplicate in merge mode — just skip. ++$result['skipped']; } else { ++$count; } } $result['imported']['contact_lists'] = $count; } if ( ! empty( $data['segments'] ) && is_array( $data['segments'] ) ) { $result['imported']['segments'] = $this->import_segments( $data['segments'], $list_id_map, $mode, $result ); } if ( ! empty( $data['campaigns'] ) && is_array( $data['campaigns'] ) ) { $table = Mailpuff_Schema::table( Mailpuff_Schema::CAMPAIGNS ); $count = 0; foreach ( $data['campaigns'] as $row ) { $title = isset( $row['title'] ) ? sanitize_text_field( $row['title'] ) : ''; $old_id = isset( $row['id'] ) ? absint( $row['id'] ) : 0; unset( $row['id'] ); if ( 'merge' === $mode && ! empty( $title ) ) { $existing = $wpdb->get_var( $wpdb->prepare( 'SELECT id FROM %i WHERE title = %s', $table, $title ) ); if ( $existing ) { ++$result['skipped']; continue; } } $inserted = $wpdb->insert( $table, $this->whitelist_row( $row, 'campaigns' ) ); if ( false === $inserted ) { $result['errors'][] = sprintf( 'Campaign "%s": %s', $title, $wpdb->last_error ); } else { ++$count; } } $result['imported']['campaigns'] = $count; } if ( ! empty( $data['settings'] ) && is_array( $data['settings'] ) ) { $settings_map = array( 'brand' => 'mailpuff_brand_settings', 'email' => 'mailpuff_email_settings', 'compliance' => 'mailpuff_compliance_settings', ); $count = 0; foreach ( $settings_map as $key => $option_name ) { if ( isset( $data['settings'][ $key ] ) && is_array( $data['settings'][ $key ] ) ) { $current = get_option( $option_name, array() ); update_option( $option_name, array_merge( is_array( $current ) ? $current : array(), $this->clean_settings( $key, $data['settings'][ $key ] ) ) ); ++$count; } } $result['imported']['settings'] = $count; } return $result; } /** * A backup's settings, cleaned the way the settings screen cleans them: * the footer through wp_kses_post, addresses through sanitize_email, text * through sanitize_text_field. The owner-only "My host delivers mail" * switch is never taken from a file; the site keeps its own. * * @param string $group brand | email | compliance. * @param array $in The backup's values. * @return array */ private function clean_settings( $group, array $in ) { $out = array(); foreach ( $in as $key => $value ) { $key = sanitize_key( (string) $key ); if ( '' === $key || ( 'email' === $group && 'host_delivers_mail' === $key ) ) { continue; } if ( is_array( $value ) ) { // The brand's social links: network => URL. if ( 'brand' === $group && 'social' === $key ) { $out[ $key ] = array_map( 'esc_url_raw', array_filter( $value, 'is_scalar' ) ); } continue; } if ( ! is_scalar( $value ) ) { continue; } if ( 'footer' === $key ) { $out[ $key ] = wp_kses_post( (string) $value ); } elseif ( in_array( $key, array( 'from_email', 'reply_to', 'admin_email' ), true ) ) { $out[ $key ] = sanitize_email( (string) $value ); } elseif ( is_int( $value ) || is_bool( $value ) ) { $out[ $key ] = (int) $value; } else { $out[ $key ] = sanitize_text_field( (string) $value ); } } return $out; } /** * Columns an import is allowed to write, per data type. Anything else in a * backup row (crafted or from a newer version) is dropped before insert, so * an import can never set unexpected/internal columns. * * @param string $type 'contacts' | 'lists' | 'campaigns'. * @return string[] */ private function allowed_columns( $type ) { $map = array( 'contacts' => array( 'email', 'first_name', 'last_name', 'status', 'customer_type', 'gross_revenue', 'source', 'consent_source', 'confirmed_at', 'created_at', 'updated_at' ), 'lists' => array( 'name', 'description', 'visibility', 'created_at' ), 'campaigns' => array( 'user_id', 'title', 'subject', 'preheader', 'status', 'audience_id', 'audience_type', 'content_html', 'content_blocks', 'email_type', 'type', 'sender_name', 'sender_email', 'reply_to', 'created_at', 'updated_at', 'sent_at' ), ); return isset( $map[ $type ] ) ? $map[ $type ] : array(); } /** * Keep only whitelisted columns from a backup row. * * @param array $row Backup row. * @param string $type Data type. * @return array */ private function whitelist_row( $row, $type ) { return array_intersect_key( $row, array_flip( $this->allowed_columns( $type ) ) ); } /** * Restore segments, pointing their groups at the restored groups' ids. A * segment whose group did not come back is skipped: dropping the group * from its filter would widen it to people it never meant. * * @param array $rows Backup rows { name, filter }. * @param array $list_id_map Old group id => new group id. * @param string $mode merge or replace. * @param array $result The running result, for skips and errors. * @return int Segments restored. */ private function import_segments( array $rows, array $list_id_map, $mode, array &$result ) { global $wpdb; $table = Mailpuff_Schema::table( Mailpuff_Schema::SEGMENTS ); $count = 0; foreach ( $rows as $row ) { $name = isset( $row['name'] ) ? sanitize_text_field( (string) $row['name'] ) : ''; $filter = isset( $row['filter'] ) ? json_decode( (string) $row['filter'], true ) : null; $filter = Mailpuff_Contact_Filter::normalize( is_array( $filter ) ? $filter : array() ); if ( '' === $name ) { ++$result['skipped']; continue; } if ( ! empty( $filter['group_ids'] ) ) { $mapped = array(); foreach ( $filter['group_ids'] as $old ) { if ( isset( $list_id_map[ $old ] ) ) { $mapped[] = (int) $list_id_map[ $old ]; } } if ( count( $mapped ) !== count( $filter['group_ids'] ) ) { ++$result['skipped']; continue; } $filter['group_ids'] = $mapped; } if ( 'merge' === $mode && $wpdb->get_var( $wpdb->prepare( 'SELECT id FROM %i WHERE name = %s', $table, $name ) ) ) { ++$result['skipped']; continue; } $now = current_time( 'mysql' ); $inserted = $wpdb->insert( $table, array( 'name' => $name, 'filter' => wp_json_encode( $filter ), 'created_at' => $now, 'updated_at' => $now, ), array( '%s', '%s', '%s', '%s' ) ); if ( false === $inserted ) { $result['errors'][] = sprintf( 'Segment %s: %s', $name, $wpdb->last_error ); } else { ++$count; } } return $count; } /** * Truncate tables for the data types present in the import payload. * * Order matters: the join table is cleared before its parent tables. * * @param array $data The backup data array. */ private function truncate_tables( $data ) { $has_relational = isset( $data['contact_lists'] ) || isset( $data['contacts'] ) || isset( $data['lists'] ); if ( $has_relational ) { $this->truncate( Mailpuff_Schema::SUBSCRIBER_LISTS ); } if ( isset( $data['contacts'] ) ) { $this->truncate( Mailpuff_Schema::SUBSCRIBERS ); } if ( isset( $data['lists'] ) ) { $this->truncate( Mailpuff_Schema::LISTS ); } if ( isset( $data['segments'] ) ) { $this->truncate( Mailpuff_Schema::SEGMENTS ); } if ( isset( $data['campaigns'] ) ) { $this->truncate( Mailpuff_Schema::CAMPAIGNS ); } } /** * Empty a Mailpuff-owned table. * * The table name is derived from a Schema constant, never user input. * * @param string $schema_constant One of the Mailpuff_Schema::* table constants. */ private function truncate( $schema_constant ) { global $wpdb; $table = Mailpuff_Schema::table( $schema_constant ); $wpdb->query( $wpdb->prepare( 'DELETE FROM %i', $table ) ); } }