Create/Import WordPress Posts from table rows from another non-WordPress database table into a Custom Post Type

// Pull Data From Puppies Table


add_action( 'admin_init', 'my_admin' );

function my_admin() {
    add_meta_box( 
        'puppy_details_meta_box',
        'Puppy Information',
        'display_puppy_details_meta_box',
        'puppies', // custom post type name, register_post_type( 'puppies', $args );
        'normal', 
        'high'
    );
}

function display_puppy_details_meta_box() 
{
  

 ?>
     <table>
         <tr>
             <td style="width: 50%">ID</td>
             <td><input type="text" size="40" name="puppyid" value="<?php echo get_post_meta( get_the_ID() , 'id', true ); ?>" /></td>
         </tr>
         <tr>
             <td style="width: 50%">Breed</td>
             <td><input type="text" size="40" name="puppybreed" value="<?php echo get_post_meta( get_the_ID() , 'breed', true ); ?>" /></td>
         </tr>
         <tr>
             <td style="width: 50%">Breed Sold As</td>
             <td><input type="text" size="40" name="breedsoldas" value="<?php echo get_post_meta( get_the_ID() , 'breed_sold_as', true ); ?>" /></td>       
         </tr>
           <tr>
             <td style="width: 50%">Microchip</td>
             <td><input type="text" size="40" name="microchip" value="<?php echo get_post_meta( get_the_ID() , 'microchip', true ); ?>" /></td>       
         </tr>
         <tr>
             <td style="width: 50%">Color</td>
             <td><input type="text" size="40" name="color" value="<?php echo get_post_meta( get_the_ID() , 'color', true ); ?>" /></td>       
         </tr>
         <tr>
             <td style="width: 50%">Gender</td>
             <td><input type="text" size="40" name="gender" value="<?php echo get_post_meta( get_the_ID() , 'gender', true ); ?>" /></td>       
         </tr>
           <tr>
             <td style="width: 50%">Date of Birth</td>
             <td><input type="text" size="40" name="dob" value="<?php echo get_post_meta( get_the_ID() , 'dob', true ); ?>" /></td>       
         </tr>
           <tr>
             <td style="width: 50%">Date of Arrival</td>
             <td><input type="text" size="40" name="doa" value="<?php echo get_post_meta( get_the_ID() , 'doa', true ); ?>" /></td>       
         </tr>
     </table>
    <?php
    
}


add_action( 'wp_loaded', 'puppies_insert_into_cpt');

function puppystore_verify_existing_puppies_in_cpt() {
    $id_arrays_in_cpt = [];
    // Query all puppies
    $args = array(
        'post_type'      => 'puppies',
        'posts_per_page' => -1,
    );

    $loop = new WP_Query($args);
    while ( $loop->have_posts() ) {
        $loop->the_post();
        $id_arrays_in_cpt[] = get_post_meta( get_the_ID() , 'id', true );
    }
    return $id_arrays_in_cpt;
}

function puppystore_query_puppies_table( $puppies_available_in_cpt_array ) {
     // Query Database
     global $wpdb;
    $table_name = 'puppies'; // Adjusted for potential WordPress table prefix

    // Check if the array of available puppies is empty or null
    if (empty($puppies_available_in_cpt_array)) {
        // If the array is empty, select all records from the table
        $results = $wpdb->get_results("SELECT * FROM {$table_name}");
    } else {
        // If the array is not empty, select records where the id is not in the provided array
        // Prepare the placeholder string - one %d (integer placeholder) for each ID in the array
        $placeholders = implode(',', array_fill(0, count($puppies_available_in_cpt_array), '%d'));

        // Prepare the SQL statement with the correct number of placeholders and array values
        $sql = $wpdb->prepare("SELECT * FROM {$table_name} WHERE id NOT IN ($placeholders)", ...$puppies_available_in_cpt_array);

        // Execute the query and get results
        $results = $wpdb->get_results($sql);
    }

    return $results;
}



function puppies_insert_into_cpt() {
    // Get the specific admin user ID
    $admin_user_id = get_user_by('login', 'nicholas')->ID; // Replace 'nicholas' with the username of the specific admin

        // Define an array to store existing microchip values
    $existing_microchips = [];

    // Query all existing puppies for their microchip values
    $existing_puppies_query = new WP_Query([
        'post_type'      => 'puppies',
        'posts_per_page' => -1,
        'fields'         => 'ids', // Only get post IDs to improve performance
    ]);

    // Collect all existing microchip values
    foreach ($existing_puppies_query->posts as $post_id) {
        $existing_microchips[] = get_post_meta($post_id, 'microchip', true);
    }

    // if the queried results don't match the existing puppies
    $puppies_available_in_cpt_array = puppystore_verify_existing_puppies_in_cpt();
    $database_results = puppystore_query_puppies_table($puppies_available_in_cpt_array);

    if ( NULL === $database_results || 0 === $database_results || '0' === $database_results || empty( $database_results ) ) {
        return;
    }
    
    //Insert into CPT
    foreach ($database_results as $result) {

         // Ensure unique microchip values to avoid duplicates
        if (in_array($result->microchip, $existing_microchips)) {
            continue; // Skip this iteration if the microchip already exists
        }

        // Check if the breed category exists, if not, create it
        $breed = isset($result->breed) ? $result->breed : ''; // Ensure $breed is a string
        if (!empty($breed)) {
            // Try to get the breed term by name
            $breed_term = get_term_by('name', $breed, 'breeds');
            if (!$breed_term) {
                // If the term doesn't exist, create it
                $breed_term_data = wp_insert_term($breed, 'breeds');
                if (!is_wp_error($breed_term_data) && isset($breed_term_data['term_id'])) {
                    $breed_term = $breed_term_data['term_id'];
                }
            } else {
                // Extract term ID from the existing term data
                $breed_term = $breed_term->term_id;
            }
        }

        // Set the featured image
        $featured_image_id = null;
        $puppy_images = isset($result->puppy_images) ? unserialize($result->puppy_images) : [];
        if (!empty($puppy_images[0])) {
            $image_path = '/var/www/Domain/uploads/front_puppies/' . $puppy_images[0];
            if (file_exists($image_path)) {
                $featured_image_id = puppystore_upload_image($image_path, $result->breed);
            }
        }

        // Create post object
        $post_title = isset($result->breed) && isset($result->microchip) ? wp_strip_all_tags($result->breed . ' ' . $result->microchip) : '';

// Insert the post into the database if it doesn't already exist
 $post_id = wp_insert_post([
            'post_title' => $post_title,
            'post_type' => 'puppies',
            'post_status' => 'publish',
        ]);


            // Check if the post was inserted successfully
            if (!is_wp_error($post_id)) {
                // Set featured image
                if ($featured_image_id) {
                    set_post_thumbnail($post_id, $featured_image_id);
                }

                // Assign the post to the breed category
                if ($breed_term && !is_wp_error($post_id)) {
                    wp_set_object_terms($post_id, $breed_term, 'breeds');
                }

                // Prepare post meta data
                $meta_data = [
                    'id' => isset($result->id) ? $result->id : '',
                    'breed' => isset($result->breed) ? $result->breed : '',
                    'breed_sold_as' => isset($result->breed_sold_as) ? $result->breed_sold_as : '',
                    'microchip' => isset($result->microchip) ? $result->microchip : '',
                    'color' => isset($result->color) ? $result->color : '',
                    'gender' => isset($result->gender) ? $result->gender : '',
                    'dob' => isset($result->dob) ? $result->dob : '',
                    'doa' => isset($result->doa) ? $result->doa : '',
                ];

                // Update post meta with formatted dob and doa
                foreach ($meta_data as $key => $value) {
                    update_post_meta($post_id, $key, $value);
                }
            } else {
                // Handle insertion error
                error_log('Failed to insert post: ' . $post_id->get_error_message());
            }
        }
    }
//}

// Function to upload image and return attachment ID
function puppystore_upload_image($image_path, $post_title) {
    $upload = wp_upload_bits(basename($image_path), null, file_get_contents($image_path));

    if (!$upload['error']) {
        $file_path = $upload['file'];
        $file_name = basename($file_path);
        $file_type = wp_check_filetype($file_name, null);
        $attachment_title = sanitize_file_name(pathinfo($file_name, PATHINFO_FILENAME));
        $wp_upload_dir = wp_upload_dir();

        $attachment = [
            'guid' => $wp_upload_dir['url'] . '/' . $file_name,
            'post_mime_type' => $file_type['type'],
            'post_title' => $attachment_title,
            'post_content' => '',
            'post_status' => 'inherit'
        ];

        $attach_id = wp_insert_attachment($attachment, $file_path);

        if (!is_wp_error($attach_id)) {
            require_once(ABSPATH . 'wp-admin/includes/image.php');
            $attach_data = wp_generate_attachment_metadata($attach_id, $file_path);
            wp_update_attachment_metadata($attach_id, $attach_data);
            return $attach_id;
        }
    }

    return null;
}

// End import from Puppies DB


function save_puppy_meta_box_data($post_id) {
    // Check if it's a valid submission:
    // Verify nonce, if you have one, to ensure the data came from the correct place
    // Check the user's permissions.
    // Check if it's not an autosave or a revision being saved.

    if (defined('DOING_AUTOSAVE') && DOING_AUTOSAVE) 
        return;
    if (!current_user_can('edit_post', $post_id)) 
        return;

            // Assuming 'id' is a number field
    if (isset($_POST['id'])) {
        $puppy_id = intval($_POST['id']); // Use intval for integer input
        update_post_meta($post_id, 'id', $puppy_id);
    }

    if (isset($_POST['breed'])) {
        $breed = sanitize_text_field($_POST['breed']);
        update_post_meta($post_id, 'breed', $breed);
    }

    if (isset($_POST['breed_sold_as'])) {
        $breed_sold_as = sanitize_text_field($_POST['breed_sold_as']);
        update_post_meta($post_id, 'breed_sold_as', $breed_sold_as);
    }

        // Assuming 'microchip' is a number field
    if (isset($_POST['microchip'])) {
        $microchip = intval($_POST['microchip']); // Use intval for integer input
        update_post_meta($post_id, 'microchip', $microchip);
    }

    if (isset($_POST['color'])) {
        $color = sanitize_text_field($_POST['color']);
        update_post_meta($post_id, 'color', $color);
    }

    if (isset($_POST['gender'])) {
        $gender = sanitize_text_field($_POST['gender']);
        update_post_meta($post_id, 'gender', $gender);
    }
        // Assuming 'dob' is a number field
    if (isset($_POST['dob'])) {
        $dob = intval($_POST['dob']); // Use intval for integer input
        update_post_meta($post_id, 'dob', $dob);
    }

        // Assuming 'doa' is a number field
    if (isset($_POST['doa'])) {
        $doa = intval($_POST['doa']); // Use intval for integer input
        update_post_meta($post_id, 'doa', $doa);
    }
    // Add checks and sanitizations for additional fields as necessary
}

// Hook this function to save post action
add_action('save_post_puppies', 'save_puppy_meta_box_data');