<?php
/**
 * Unified submission importer utility.
 * Detects and migrates leads from other form databases (Flamingo, CFDB7, Fluent Forms, Elementor).
 *
 * @package Leadite
 */

namespace Leadite\Support;

use Leadite\Storage\Lead;
use Leadite\Storage\LeadsRepository;

defined( 'ABSPATH' ) || exit;

class SubmissionImporter {

	/**
	 * Detect available migration sources and count pending entries.
	 *
	 * NOTE: All subquery aliases use 'li' for the Leadite table (not 'lh' from the
	 * old LeadHub name — that alias mismatch caused COUNT to return NULL / 0).
	 *
	 * @return array Array of source statistics.
	 */
	public static function detect_sources() {
		$cache_key = 'leadite_detected_sources';
		$cached    = wp_cache_get( $cache_key, 'leadite_importer' );
		if ( false !== $cached ) {
			return $cached;
		}

		global $wpdb;
		$sources = array();

		// 1. Elementor Pro Submissions
		$e_sub_table = $wpdb->prefix . 'e_submissions';
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$e_exists    = $wpdb->get_var( $wpdb->prepare( "SHOW TABLES LIKE %s", $e_sub_table ) );
		if ( $e_exists ) {
			$lh_table = LeadsRepository::table();
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$count    = (int) $wpdb->get_var(
				$wpdb->prepare(
					"SELECT COUNT(*) FROM %i s
					 WHERE NOT EXISTS (
					 	SELECT 1 FROM %i li
					 	WHERE li.source = 'elementor'
					 	  AND li.form_id = CONCAT(s.element_id, '_migrated_', s.id)
					 )",
					$e_sub_table,
					$lh_table
				)
			);
			$sources['elementor'] = array(
				'label' => 'Elementor Pro Submissions',
				'count' => $count,
			);
		}

		// 2. Flamingo (Contact Form 7 Storage)
		$post_table = $wpdb->posts;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$fl_count   = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM %i WHERE post_type = %s",
				$post_table,
				'flamingo_contact'
			)
		);
		if ( $fl_count > 0 ) {
			$lh_table = LeadsRepository::table();
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$count    = (int) $wpdb->get_var(
				$wpdb->prepare(
					"SELECT COUNT(*) FROM %i p
					 WHERE p.post_type = %s
					   AND p.post_status = %s
					   AND NOT EXISTS (
					   	SELECT 1 FROM %i li
					   	WHERE li.source = 'cf7'
					   	  AND li.form_id = CONCAT('flamingo_migrated_', p.ID)
					   )",
					$post_table,
					'flamingo_contact',
					'publish',
					$lh_table
				)
			);
			$sources['flamingo'] = array(
				'label' => 'Flamingo (Contact Form 7)',
				'count' => $count,
			);
		}

		// 3. Contact Form 7 Database Addon (CFDB7)
		$cfdb7_table     = '';
		$tables_to_check = array( $wpdb->prefix . 'db7_submits', $wpdb->prefix . 'cfdb7_submits', $wpdb->prefix . 'db7_forms' );
		foreach ( $tables_to_check as $tbl ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			if ( $wpdb->get_var( $wpdb->prepare( "SHOW TABLES LIKE %s", $tbl ) ) ) {
				$cfdb7_table = $tbl;
				break;
			}
		}
		if ( $cfdb7_table ) {
			$lh_table = LeadsRepository::table();
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$count    = (int) $wpdb->get_var(
				$wpdb->prepare(
					"SELECT COUNT(*) FROM %i s
					 WHERE NOT EXISTS (
					 	SELECT 1 FROM %i li
					 	WHERE li.source = 'cf7'
					 	  AND li.form_id = CONCAT(s.form_post_id, '_migrated_', s.form_id)
					 )",
					$cfdb7_table,
					$lh_table
				)
			);
			$sources['cfdb7'] = array(
				'label' => 'CFDB7 (Contact Form 7 Database)',
				'count' => $count,
			);
		}

		// 4. Fluent Forms Submissions
		$ff_table = $wpdb->prefix . 'fluentform_submissions';
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$ff_exists = $wpdb->get_var( $wpdb->prepare( "SHOW TABLES LIKE %s", $ff_table ) );
		if ( $ff_exists ) {
			$lh_table = LeadsRepository::table();
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$count    = (int) $wpdb->get_var(
				$wpdb->prepare(
					"SELECT COUNT(*) FROM %i s
					 WHERE NOT EXISTS (
					 	SELECT 1 FROM %i li
					 	WHERE li.source = 'fluentform'
					 	  AND li.form_id = CONCAT(s.form_id, '_migrated_', s.id)
					 )",
					$ff_table,
					$lh_table
				)
			);
			$sources['fluentform'] = array(
				'label' => 'Fluent Forms',
				'count' => $count,
			);
		}

		// 5. Contact Form DB - Elementor (Web Ace Tech Services)
		$post_table  = $wpdb->posts;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$el_db_count = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM %i WHERE post_type = %s",
				$post_table,
				'elementor_cf_db'
			)
		);
		if ( $el_db_count > 0 ) {
			$lh_table = LeadsRepository::table();
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$count    = (int) $wpdb->get_var(
				$wpdb->prepare(
					"SELECT COUNT(*) FROM %i p
					 WHERE p.post_type = %s
					   AND NOT EXISTS (
					   	SELECT 1 FROM %i li
					   	WHERE li.source = 'elementor'
					   	  AND li.form_id = CONCAT('webace_migrated_', p.ID)
					   )",
					$post_table,
					'elementor_cf_db',
					$lh_table
				)
			);
			$sources['elementor_cf_db'] = array(
				'label' => 'Contact Form DB - Elementor (Web Ace)',
				'count' => $count,
			);
		}

		wp_cache_set( $cache_key, $sources, 'leadite_importer', 600 );

		return $sources;
	}

	/**
	 * Migrate up to $limit entries for the requested source.
	 *
	 * @param string $source Source key.
	 * @param int    $limit  Batch limit.
	 * @return int Number of successfully migrated items.
	 */
	public static function migrate( $source, $limit = 500 ) {
		switch ( $source ) {
			case 'elementor':
				return self::migrate_elementor( $limit );
			case 'flamingo':
				return self::migrate_flamingo( $limit );
			case 'cfdb7':
				return self::migrate_cfdb7( $limit );
			case 'fluentform':
				return self::migrate_fluentforms( $limit );
			case 'elementor_cf_db':
				return self::migrate_elementor_cf_db( $limit );
		}
		return 0;
	}

	/**
	 * Elementor Pro Submissions migration.
	 */
	protected static function migrate_elementor( $limit ) {
		global $wpdb;
		$e_sub_table = $wpdb->prefix . 'e_submissions';
		$e_val_table = $wpdb->prefix . 'e_submissions_values';
		$lh_table    = LeadsRepository::table();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$submissions = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT s.* FROM %i s
				 WHERE NOT EXISTS (
				 	SELECT 1 FROM %i li
				 	WHERE li.source = 'elementor'
				 	  AND li.form_id = CONCAT(s.element_id, '_migrated_', s.id)
				 )
				 ORDER BY s.id ASC LIMIT %d",
				$e_sub_table,
				$lh_table,
				$limit
			)
		);

		if ( empty( $submissions ) ) {
			return 0;
		}

		$count = 0;
		foreach ( $submissions as $sub ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$values = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM %i WHERE submission_id = %d", $e_val_table, $sub->id ) );
			if ( empty( $values ) ) {
				continue;
			}

			$lead            = new Lead();
			$lead->source    = 'elementor';
			$lead->form_id   = $sub->element_id . '_migrated_' . $sub->id;
			$lead->form_name = (string) $sub->form_name;

			$lead->meta['ip']         = (string) $sub->user_ip;
			$lead->meta['user_agent'] = (string) $sub->user_agent;
			$lead->meta['referer']    = (string) $sub->referer;
			$lead->meta['page_url']   = (string) $sub->referer;
			$lead->meta['lang']       = FieldMapper::detect_lang();
			$lead->meta['created_at'] = (string) $sub->created_at;

			foreach ( $values as $val ) {
				$key   = $val->key;
				$value = $val->value;
				$type  = ( false !== strpos( strtolower( $key ), 'email' ) ) ? 'email' : 'text';
				$lead->add_field( $key, $value, $key, $type );

				if ( false !== strpos( $value, 'http://' ) || false !== strpos( $value, 'https://' ) ) {
					$file_type = wp_check_filetype( $value );
					if ( ! empty( $file_type['ext'] ) ) {
						$lead->add_attachment( $key, basename( $value ), $value, false );
					}
				}
			}

			FieldMapper::apply_canonical( $lead );
			if ( self::insert_migrated_lead( $lead ) ) {
				$count++;
			}
		}
		return $count;
	}

	/**
	 * Flamingo Submissions migration.
	 */
	protected static function migrate_flamingo( $limit ) {
		global $wpdb;
		$post_table = $wpdb->posts;
		$lh_table   = LeadsRepository::table();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$posts = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT p.* FROM %i p
				 WHERE p.post_type = %s
				   AND p.post_status = %s
				   AND NOT EXISTS (
				   	SELECT 1 FROM %i li
				   	WHERE li.source = 'cf7'
				   	  AND li.form_id = CONCAT('flamingo_migrated_', p.ID)
				   )
				 ORDER BY p.ID ASC LIMIT %d",
				$post_table,
				'flamingo_contact',
				'publish',
				$lh_table,
				$limit
			)
		);

		if ( empty( $posts ) ) {
			return 0;
		}

		$count = 0;
		foreach ( $posts as $post ) {
			$fields = get_post_meta( $post->ID, '_fields', true );
			$meta   = get_post_meta( $post->ID, '_meta', true );

			if ( empty( $fields ) || ! is_array( $fields ) ) {
				continue;
			}

			$lead            = new Lead();
			$lead->source    = 'cf7';
			$lead->form_id   = 'flamingo_migrated_' . $post->ID;
			$lead->form_name = 'Contact Form 7 (Flamingo)';

			if ( is_array( $meta ) ) {
				$lead->meta['ip']         = isset( $meta['remote_ip'] ) ? $meta['remote_ip'] : '';
				$lead->meta['user_agent'] = isset( $meta['user_agent'] ) ? $meta['user_agent'] : '';
				$lead->meta['referer']    = isset( $meta['url'] ) ? $meta['url'] : '';
				$lead->meta['page_url']   = isset( $meta['url'] ) ? $meta['url'] : '';
			}
			$lead->meta['lang']       = FieldMapper::detect_lang();
			$lead->meta['created_at'] = (string) $post->post_date;

			foreach ( $fields as $key => $value ) {
				if ( is_array( $value ) ) {
					$value = implode( ', ', $value );
				}
				$label = ucwords( str_replace( array( '-', '_' ), ' ', $key ) );
				$type  = ( false !== strpos( strtolower( $key ), 'email' ) ) ? 'email' : 'text';
				$lead->add_field( $key, $value, $label, $type );
			}

			FieldMapper::apply_canonical( $lead );
			if ( self::insert_migrated_lead( $lead ) ) {
				$count++;
			}
		}
		return $count;
	}

	/**
	 * CFDB7 Submissions migration.
	 */
	protected static function migrate_cfdb7( $limit ) {
		global $wpdb;
		$cfdb7_table     = '';
		$tables_to_check = array( $wpdb->prefix . 'db7_submits', $wpdb->prefix . 'cfdb7_submits', $wpdb->prefix . 'db7_forms' );
		foreach ( $tables_to_check as $tbl ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			if ( $wpdb->get_var( $wpdb->prepare( "SHOW TABLES LIKE %s", $tbl ) ) ) {
				$cfdb7_table = $tbl;
				break;
			}
		}
		if ( ! $cfdb7_table ) {
			return 0;
		}
		$post_table = $wpdb->posts;
		$lh_table   = LeadsRepository::table();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$submissions = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT s.*, p.post_title as form_name FROM %i s
				 LEFT JOIN %i p ON s.form_post_id = p.ID
				 WHERE NOT EXISTS (
				 	SELECT 1 FROM %i li
				 	WHERE li.source = 'cf7'
				 	  AND li.form_id = CONCAT(s.form_post_id, '_migrated_', s.form_id)
				 )
				 ORDER BY s.form_id ASC LIMIT %d",
				$cfdb7_table,
				$post_table,
				$lh_table,
				$limit
			)
		);

		if ( empty( $submissions ) ) {
			return 0;
		}

		$count = 0;
		foreach ( $submissions as $sub ) {
			$fields = maybe_unserialize( $sub->form_value );
			if ( empty( $fields ) || ! is_array( $fields ) ) {
				continue;
			}

			$lead            = new Lead();
			$lead->source    = 'cf7';
			$lead->form_id   = $sub->form_post_id . '_migrated_' . $sub->form_id;
			$lead->form_name = $sub->form_name ? (string) $sub->form_name : 'Contact Form 7';

			$lead->meta['ip']         = isset( $fields['ip_address'] ) ? $fields['ip_address'] : '';
			$lead->meta['user_agent'] = '';
			$lead->meta['referer']    = '';
			$lead->meta['page_url']   = '';
			$lead->meta['lang']       = FieldMapper::detect_lang();
			$lead->meta['created_at'] = (string) $sub->form_date;

			foreach ( $fields as $key => $value ) {
				if ( in_array( $key, array( 'ip_address', 'g-recaptcha-response' ), true ) ) {
					continue;
				}
				if ( is_array( $value ) ) {
					$value = implode( ', ', $value );
				}
				$label = ucwords( str_replace( array( '-', '_' ), ' ', $key ) );
				$type  = ( false !== strpos( strtolower( $key ), 'email' ) ) ? 'email' : 'text';

				if ( false !== strpos( $key, 'file' ) && ( false !== strpos( $value, 'wp-content/uploads' ) ) ) {
					$lead->add_attachment( $key, basename( $value ), $value, false );
				} else {
					$lead->add_field( $key, $value, $label, $type );
				}
			}

			FieldMapper::apply_canonical( $lead );
			if ( self::insert_migrated_lead( $lead ) ) {
				$count++;
			}
		}
		return $count;
	}

	/**
	 * Fluent Forms Submissions migration.
	 */
	protected static function migrate_fluentforms( $limit ) {
		global $wpdb;
		$ff_table    = $wpdb->prefix . 'fluentform_submissions';
		$ff_form_tbl = $wpdb->prefix . 'fluentform_forms';
		$lh_table    = LeadsRepository::table();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$submissions = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT s.*, f.title as form_title FROM %i s
				 LEFT JOIN %i f ON s.form_id = f.id
				 WHERE NOT EXISTS (
				 	SELECT 1 FROM %i li
				 	WHERE li.source = 'fluentform'
				 	  AND li.form_id = CONCAT(s.form_id, '_migrated_', s.id)
				 )
				 ORDER BY s.id ASC LIMIT %d",
				$ff_table,
				$ff_form_tbl,
				$lh_table,
				$limit
			)
		);

		if ( empty( $submissions ) ) {
			return 0;
		}

		$count = 0;
		foreach ( $submissions as $sub ) {
			$fields = json_decode( $sub->response, true );
			if ( empty( $fields ) || ! is_array( $fields ) ) {
				continue;
			}

			$lead            = new Lead();
			$lead->source    = 'fluentform';
			$lead->form_id   = $sub->form_id . '_migrated_' . $sub->id;
			$lead->form_name = $sub->form_title ? (string) $sub->form_title : 'Fluent Form';

			$lead->meta['ip']         = (string) $sub->ip;
			$lead->meta['user_agent'] = (string) $sub->user_agent;
			$lead->meta['referer']    = (string) $sub->source_url;
			$lead->meta['page_url']   = (string) $sub->source_url;
			$lead->meta['lang']       = FieldMapper::detect_lang();
			$lead->meta['created_at'] = (string) $sub->created_at;

			foreach ( $fields as $key => $value ) {
				if ( strpos( $key, '__' ) === 0 || in_array( $key, array( 'ip', 'user_agent' ), true ) ) {
					continue;
				}
				if ( is_array( $value ) ) {
					$value = implode( ', ', $value );
				}
				$label = ucwords( str_replace( array( '-', '_' ), ' ', $key ) );
				$type  = ( false !== strpos( strtolower( $key ), 'email' ) ) ? 'email' : 'text';
				$lead->add_field( $key, $value, $label, $type );
			}

			FieldMapper::apply_canonical( $lead );
			if ( self::insert_migrated_lead( $lead ) ) {
				$count++;
			}
		}
		return $count;
	}

	/**
	 * Insert a migrated lead with historical dates, bypass duplicate hash matches.
	 *
	 * @param Lead $lead Normalized lead.
	 * @return int Inserted ID.
	 */
	protected static function insert_migrated_lead( Lead $lead ) {
		global $wpdb;

		$lead->add_field( '_imported', '1', __( 'Imported', 'leadite' ), 'system' );

		$created = ! empty( $lead->meta['created_at'] ) ? $lead->meta['created_at'] : current_time( 'mysql' );
		$table   = LeadsRepository::table();
		$mtable  = LeadsRepository::meta_table();

		// Check duplicate by exact date + source + form_id to prevent re-inserts.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$exists = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT id FROM %i WHERE source = %s AND form_id = %s AND created_at = %s LIMIT 1",
				$table,
				$lead->source,
				$lead->form_id,
				$created
			)
		);
		if ( $exists ) {
			return 0;
		}

		// Compute hash
		$parts = array( $lead->source, $lead->form_id );
		foreach ( $lead->fields as $f ) {
			if ( in_array( $f['canonical'], array( 'email', 'phone', 'full_name' ), true ) ) {
				$parts[] = $f['canonical'] . ':' . mb_strtolower( trim( $f['value'] ) );
			}
		}
		$hash = md5( implode( '|', $parts ) );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		$ok = $wpdb->insert(
			$table,
			array(
				'source'      => $lead->source,
				'form_id'     => $lead->form_id,
				'form_name'   => $lead->form_name,
				'status'      => 'read', // Migrated leads are read by default
				'starred'     => 0,
				'spam_score'  => 0,
				'hash'        => $hash,
				'ip'          => (string) $lead->meta( 'ip' ),
				'user_agent'  => mb_substr( (string) $lead->meta( 'user_agent' ), 0, 255 ),
				'referer'     => mb_substr( (string) $lead->meta( 'referer' ), 0, 255 ),
				'page_url'    => mb_substr( (string) $lead->meta( 'page_url' ), 0, 255 ),
				'lang'        => (string) $lead->meta( 'lang' ),
				'assigned_to' => 0,
				'created_at'  => $created,
			),
			array( '%s', '%s', '%s', '%s', '%d', '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%s' )
		);

		if ( ! $ok ) {
			return 0;
		}

		$submission_id = (int) $wpdb->insert_id;

		$order = 0;
		foreach ( $lead->fields as $field ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
			$wpdb->insert(
				$mtable,
				array(
					'submission_id' => $submission_id,
					'field_key'     => mb_substr( $field['key'], 0, 190 ),
					'field_label'   => mb_substr( $field['label'], 0, 190 ),
					'field_value'   => $field['value'],
					'field_type'    => mb_substr( $field['type'], 0, 40 ),
					'canonical'     => mb_substr( isset( $field['canonical'] ) ? $field['canonical'] : '', 0, 40 ),
					'sort_order'    => $order++,
				),
				array( '%d', '%s', '%s', '%s', '%s', '%s', '%d' )
			);
		}

		// Insert files
		if ( ! empty( $lead->attachments ) && is_array( $lead->attachments ) ) {
			$upload_dir = wp_upload_dir();
			foreach ( $lead->attachments as $attach ) {
				$file_path  = $attach['tmp_path'];
				$clean_name = sanitize_file_name( $attach['name'] );
				$local_path = str_replace( $upload_dir['baseurl'], $upload_dir['basedir'], $file_path );
				$size_bytes = 0;
				if ( file_exists( $local_path ) ) {
					$size_bytes = (int) filesize( $local_path );
				}
				$file_type = wp_check_filetype( $file_path );
				$mime      = $file_type['type'] ? $file_type['type'] : 'application/octet-stream';

				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
				$wpdb->insert(
					$wpdb->prefix . 'leadite_files',
					array(
						'submission_id' => $submission_id,
						'field_key'     => mb_substr( $attach['field_key'], 0, 190 ),
						'file_name'     => mb_substr( $clean_name, 0, 255 ),
						'file_path'     => mb_substr( $file_path, 0, 255 ),
						'mime'          => mb_substr( $mime, 0, 100 ),
						'size_bytes'    => $size_bytes,
					),
					array( '%d', '%s', '%s', '%s', '%s', '%d' )
				);
			}
		}

		return $submission_id;
	}

	/**
	 * Contact Form DB - Elementor (Web Ace) Submissions migration.
	 *
	 * @param int $limit Batch limit.
	 * @return int Number of successfully migrated items.
	 */
	protected static function migrate_elementor_cf_db( $limit ) {
		global $wpdb;
		$post_table = $wpdb->posts;
		$lh_table   = LeadsRepository::table();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$posts = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT p.* FROM %i p
				 WHERE p.post_type = %s
				   AND NOT EXISTS (
				   	SELECT 1 FROM %i li
				   	WHERE li.source = 'elementor'
				   	  AND li.form_id = CONCAT('webace_migrated_', p.ID)
				   )
				 ORDER BY p.ID ASC LIMIT %d",
				$post_table,
				'elementor_cf_db',
				$lh_table,
				$limit
			)
		);

		if ( empty( $posts ) ) {
			return 0;
		}

		$count = 0;
		foreach ( $posts as $post ) {
			$fields = get_post_meta( $post->ID, 'sb_elem_cfd', true );

			if ( is_object( $fields ) ) {
				if ( method_exists( $fields, 'get_formatted_data' ) ) {
					$fields = $fields->get_formatted_data();
				} elseif ( method_exists( $fields, 'get' ) ) {
					$fields = $fields->get( 'fields' );
				} else {
					$fields = (array) $fields;
				}
			}

			if ( is_array( $fields ) && isset( $fields['data'] ) && is_array( $fields['data'] ) ) {
				$fields = $fields['data'];
			}

			$title_parts = explode( ' - ', $post->post_title );
			$form_name   = ! empty( $title_parts[0] ) ? trim( $title_parts[0] ) : 'Elementor Form';

			$lead            = new Lead();
			$lead->source    = 'elementor';
			$lead->form_id   = 'webace_migrated_' . $post->ID;
			$lead->form_name = $form_name;

			$lead->meta['ip']         = get_post_meta( $post->ID, 'ip_address', true );
			$lead->meta['user_agent'] = '';
			$lead->meta['referer']    = '';
			$lead->meta['page_url']   = '';
			$lead->meta['lang']       = FieldMapper::detect_lang();
			$lead->meta['created_at'] = (string) $post->post_date;

			if ( is_array( $fields ) ) {
				foreach ( $fields as $key => $value ) {
					if ( is_object( $value ) ) {
						if ( method_exists( $value, 'get_formatted_data' ) ) {
							$value = $value->get_formatted_data();
						} elseif ( method_exists( $value, 'get' ) ) {
							$value = $value->get( 'fields' );
						} else {
							continue;
						}
					}

					if ( is_array( $value ) ) {
						$f_key   = isset( $value['key'] ) ? $value['key'] : ( isset( $value['name'] ) ? $value['name'] : ( isset( $value['id'] ) ? $value['id'] : $key ) );
						$f_label = isset( $value['label'] ) ? $value['label'] : ( isset( $value['title'] ) ? $value['title'] : ucwords( str_replace( array( '-', '_' ), ' ', $f_key ) ) );
						$f_val   = isset( $value['value'] ) ? $value['value'] : '';
						if ( is_array( $f_val ) ) {
							$f_val = implode( ', ', $f_val );
						}
						$type = ( false !== strpos( strtolower( $f_key ), 'email' ) ) ? 'email' : 'text';
						$lead->add_field( $f_key, $f_val, $f_label, $type );
					} else {
						$label = ucwords( str_replace( array( '-', '_' ), ' ', $key ) );
						$type  = ( false !== strpos( strtolower( $key ), 'email' ) ) ? 'email' : 'text';
						$lead->add_field( $key, $value, $label, $type );
					}
				}
			}

			FieldMapper::apply_canonical( $lead );
			if ( self::insert_migrated_lead( $lead ) ) {
				$count++;
			}
		}
		return $count;
	}
}