<?php
defined('ABSPATH') or die("No direct script access!");


// 5. Handle the AJAX request (PHP side)
function qcld_sbd_handle_csv_import() {
    check_ajax_referer( 'quantum_ajax_validation_18', 'security' );

    if ( ! current_user_can( 'manage_options' ) ) {
        wp_send_json_error( 'Unauthorized user.' );
    }

    if ( ! file_exists( SBD_CSV_FILE_PATH ) ) {
        wp_send_json_error( 'CSV file not found in plugin folder.' );
    }

	//First check if the uploaded file is valid
	$valid = true;
	
	$file_info = wp_check_filetype( SBD_CSV_FILE_PATH );

	if ( 'csv' !== $file_info['ext'] ) {
	    wp_send_json_error( 'The file at the specified path is not a CSV.' );
	}

    
    // 6. Create a new page automatically using PHP
    $page_title 	= esc_html('SBD Demo Data Imported');
    $page_content 	= '[qcpnd-directory mode="all" showmaponly="no" item_orderby="title" style="style-1" show_phone_icon="1" show_link_icon="1" enable_embedding="true" main_click_action="1" phone_number="1" map="show" column="3" orderby="date" order="ASC" min_width="620px"]';
    $page_slug 		= 'sbd-demo-data';

    $existing_page = get_page_by_path( $page_slug );

    if ( ! $existing_page ) {
        $new_page = array(
            'post_title'    => $page_title,
            'post_content'  => $page_content,
            'post_status'   => 'publish',
            'post_type'     => 'page',
            'post_name'     => $page_slug,
        );
        wp_insert_post( $new_page );
    }else{

	    wp_send_json_success( array(
	        'message' 		=> esc_html('Already CSV data imported and page created.'),
	        'redirect_url' 	=> get_permalink( get_page_by_path( $page_slug ) ), // URL for redirection
	    ) );
    }

	// Check if the file exists and is not empty before opening
	if ( file_exists( SBD_CSV_FILE_PATH ) && filesize( SBD_CSV_FILE_PATH ) > 0 ) {
	    
	    // Open the file directly using the path constant
	    $file = fopen( SBD_CSV_FILE_PATH, "r" );


		
		if( $file !== false )
		{
		
            $flag = true;
			
			//Reading file and building our array
			
			$baseData = array();

			$count = 0;

			$laps = 1;

			$mapapi = 'AIzaSyBACyZ4vA8pQybj9ZdZP-J5zQHqfQkqOXY';
			if(get_option('sbd_map_api_key')!=''){
				$mapapi = get_option('sbd_map_api_key');
			}
			
			//Read fields from CSV file and dump in $baseData
			while(($data = fgetcsv($file)) !== FALSE) 
			{
				$temp_laps = $laps;
				if ($flag) {
					$flag = false;
					continue;
				}

				$glat = trim($data[7]);
				$glon = trim($data[8]);
				if(isset($_POST['genlatlon']) && $_POST['genlatlon']!=''){


					if(isset($_POST['sbd_map_name']) && $_POST['sbd_map_name'] == 'openstreet_map'){

						// url encode the address
					    $address = urlencode($data[6]);

					    $url = "http://nominatim.openstreetmap.org/?format=json&addressdetails=1&q={$address}&format=json&limit=1";

					    // get the json response
					    $resp_json = sld_get_web_page($url);

					    // decode the json
					    $resp = json_decode($resp_json, true);

					    if(!empty($resp)){
					    	$glat = $resp[0]['lat'];
							$glon = $resp[0]['lon'];
					    }


					}else{

						$mapapi = 'AIzaSyBACyZ4vA8pQybj9ZdZP-J5zQHqfQkqOXY';
						if(get_option('sbd_map_api_key')!=''){
							$mapapi = get_option('sbd_map_api_key');
						}

						$url = 'https://maps.googleapis.com/maps/api/geocode/json?address='.urlencode($data[6]).'&sensor=false&key='.$mapapi;
						$coordinates = sld_get_web_page($url);
						
						$coordinates = json_decode($coordinates);
						
						if(isset($coordinates->status) and $coordinates->status=='OK'){
							$glat = $coordinates->results[0]->geometry->location->lat;
							$glon = $coordinates->results[0]->geometry->location->lng;
						}

					}

					
				}

				if( isset($data[49]) && $data[49] != '' ){
					$laps = $data[49];
				}
				
				$baseData[$data[0]][] = array(
					'list_id' 			=> trim($data[0]),
					'list_title' 		=> sanitize_text_field((trim($data[1]))),
					'qcpnd_item_title' 	=> sanitize_text_field((trim($data[2]))),
					'qcpnd_item_link' 	=> trim($data[3]),
					'qcpnd_item_phone' 	=> trim($data[4]),
					
					'qcpnd_item_subtitle' 	=> sanitize_text_field((trim($data[5]))),
					'qcpd_item_full_address' => sanitize_text_field((trim($data[6]))),
					
					
					'qcpd_item_latitude' => trim($glat),
					'qcpd_item_longitude' => trim($glon),
					
					
					'qcpd_item_email' => trim($data[9]),
					'qcpd_item_twitter' => trim($data[10]),
					'qcpnd_item_linkedin' => trim($data[11]),
					'qcpd_item_facebook' => trim($data[12]),
					'qcpd_item_yelp' => trim($data[13]),
					'qcpd_item_business_hour' => trim($data[14]),
					
					'qcpd_item_nofollow' => trim($data[15]),
					'qcpd_item_newtab' => trim($data[16]),
					'qcpd_item_subtitle' => sanitize_text_field((trim($data[17]))),
					'qcpd_fa_icon' => sanitize_text_field((trim($data[18]))),
					'qcpd_use_favicon' => trim($data[19]),
					'qcpd_item_img' => trim($data[20]),
					'qcpd_item_img_title' => trim($data[21]),
					'qcpd_item_img_link' => trim($data[22]),
					'qcpd_image_from_link' => trim($data[50]),
					'qcpd_generate_title' => trim($data[51]),
					'qcpd_upvote_count' => trim($data[23]),
					'attached_terms' => trim($data[24]),
					'qcpd_entry_time' => date("Y-m-d H:i:s"),
					'qcpd_timelaps' => $laps,
					'qcpnd_item_description'=> trim($data[40]),
					'qcpd_new'=> trim($data[41]),
					'qcpd_featured'=> trim($data[42]),
					'qcpd_tags'=> trim($data[43]),
					'qcpd_field_1'=> trim($data[44]),
					'qcpd_field_2'=> trim($data[45]),
					'qcpd_field_3'=> trim($data[46]),
					'qcpd_field_4'=> trim($data[47]),
					'qcpd_field_5'=> trim($data[48]),
					'list_border_color' => trim($data[25]),
					'list_bg_color' => trim($data[26]),
					'list_bg_color_hov' => trim($data[27]),
					'list_txt_color' => trim($data[28]),
					'list_txt_color_hov' => trim($data[29]),
					'list_subtxt_color' => trim($data[30]),
					'list_subtxt_color_hov' => trim($data[31]),
					'item_bdr_color' => trim($data[32]),
					'item_bdr_color_hov' => trim($data[33]),
					'list_title_color' => trim($data[34]),
					'filter_background_color' => trim($data[35]),
					'filter_text_color' => trim($data[36]),
					'add_block_text' => sanitize_text_field((trim($data[37]))),
					'menu_order' => trim($data[38]),
					'post_status' => trim($data[39])
				);

				$laps = $temp_laps;

				$count++;
				$laps++;

			}
			
			fclose($file);
			
			//Inserting Data from our built array
			
			$keyCounter = 0;
			$metaCounter = 0;
			
			global $wpdb;

			//Sort $baseData numerically
			ksort($baseData, SORT_NUMERIC);
			
			
			
			//Parse $baseData and insert in the database
			foreach( $baseData as $key => $data ){
			
				
				//Check menu order for current SLD post, set 0 if empty
				$menu_order_val = isset($data[0]['menu_order']) ? $data[0]['menu_order'] : 0;

				//Grab current LIST title
				$post_title = (isset($data[0]['list_title']) && $data[0]['list_title'] != "" ) ? $data[0]['list_title'] : '';

				$post_id = (isset($data[0]['list_id']) && $data[0]['list_id'] != "" ) ? $data[0]['list_id'] : '';
				
				//Grab current LIST status, set 'publish' if empty
				$post_status = (isset($data[0]['post_status']) && $data[0]['post_status'] != "" ) ? $data[0]['post_status'] : 'publish';

				if( !empty($post_id) ){
					$post_id = $wpdb->get_var("SELECT ID FROM $wpdb->posts WHERE post_type = 'pnd' AND post_status = 'publish' AND ID = $post_id ORDER BY ID DESC LIMIT 1");

					if( empty($post_id) && !empty($post_title) ){
						$post_id = $wpdb->get_var("SELECT ID FROM $wpdb->posts WHERE post_type = 'pnd' AND post_status = 'publish' AND post_title LIKE '%$post_title%' ORDER BY ID DESC LIMIT 1");
					}
				}

				//If $post_title is empty, then go for next iteration
				if( $post_title == '' ){
					continue;
				}
				
				$existing_list = false;

				//Existing post array
				//if(!empty(get_post($post_id))){
				if(!empty(get_post($post_id))){
					$newest_post_id = $post_id;
					$existing_list = true;
				}else{
					//Build post array and insert as new POST
					$post_arr = array(
						'post_title' 	=> trim($post_title),
						'post_status' 	=> $post_status,
						'post_author' 	=> get_current_user_id(),
						'post_type' 	=> 'pnd',
						'menu_order' 	=> $menu_order_val
					);

					wp_insert_post($post_arr);

					//Get the newest post ID, that we just inserted
					$newest_post_id = $wpdb->get_var("SELECT ID FROM $wpdb->posts WHERE post_type = 'pnd' ORDER BY ID DESC LIMIT 1");
				}
				
				
				
				$attachedTerms = '';

				$innerListCounter = 0;

				$configArray = array();
				$addBlockArray = array();

				//Add list meta fields. i.e. list items and configs
				foreach( $data as $k => $item ){

/*					if( $innerListCounter == 0 && $existing_list===false)
					{
						$attachedTerms = $item['attached_terms'];

						$configArray['list_border_color'] 		= $item['list_border_color'];
						$configArray['list_bg_color'] 			= $item['list_bg_color'];
						$configArray['list_bg_color_hov'] 		= $item['list_bg_color_hov'];
						$configArray['list_txt_color'] 			= $item['list_txt_color'];
						$configArray['list_txt_color_hov'] 		= $item['list_txt_color_hov'];
						$configArray['list_subtxt_color'] 		= $item['list_subtxt_color'];
						$configArray['list_subtxt_color_hov'] 	= $item['list_subtxt_color_hov'];
						$configArray['item_bdr_color'] 			= $item['item_bdr_color'];
						$configArray['item_bdr_color_hov'] 		= $item['item_bdr_color_hov'];
						$configArray['list_title_color'] 		= $item['list_title_color'];
						$configArray['filter_background_color'] = $item['filter_background_color'];
						$configArray['filter_text_color'] 		= $item['filter_text_color'];

						$addBlockArray['add_block_text'] 		= $item['add_block_text'];

						add_post_meta(
							$newest_post_id, 
							'qcpd_list_conf', array(
								'list_border_color' 	=> $item['list_border_color'],
								'list_bg_color' 		=> $item['list_bg_color'],
								'list_bg_color_hov' 	=> $item['list_bg_color_hov'],
								'list_txt_color' 		=> $item['list_txt_color'],
								'list_txt_color_hov' 	=> $item['list_txt_color_hov'],
								'list_subtxt_color' 	=> $item['list_subtxt_color'],
								'list_subtxt_color_hov' => $item['list_subtxt_color_hov'],
								'item_bdr_color' 		=> $item['item_bdr_color'],
								'item_bdr_color_hov' 	=>  $item['item_bdr_color_hov'],
								'list_title_color' 		=>  $item['list_title_color'],
								'filter_background_color' =>  $item['filter_background_color'],
								'filter_text_color' 	=>  $item['filter_text_color']
							)
						);

						add_post_meta(
							$newest_post_id, 
							'pd_add_block', array(
								'add_block_text' =>  $item['add_block_text'],
							)
						);

						$innerListCounter++;
					}*/




					$qcpnd_item_title = ( isset($item['qcpnd_item_title']) && !empty($item['qcpnd_item_title']) ) ? iconv(mb_detect_encoding($item['qcpnd_item_title']), "UTF-8", $item['qcpnd_item_title']) : '';
					$qcpd_item_subtitle = ( isset($item['qcpd_item_subtitle']) && !empty($item['qcpd_item_subtitle']) ) ? iconv(mb_detect_encoding($item['qcpd_item_subtitle']), "UTF-8", $item['qcpd_item_subtitle']) : '';

					$qcpd_generate_title 	= isset($item['qcpd_generate_title']) ? $item['qcpd_generate_title'] : '';
					$qcpnd_item_link 		= isset($item['qcpnd_item_link']) ? $item['qcpnd_item_link'] : '';



					$attachment_id 			= "";

					$attachmentId 			= isset($item['qcpd_item_img']) ? $item['qcpd_item_img'] : '';
					$qcpd_image_from_link 	= isset($item['qcpd_image_from_link']) ? $item['qcpd_image_from_link'] : '';
					$qcpnd_item_link 		= isset($item['qcpnd_item_link']) ? esc_url($item['qcpnd_item_link']) : '';

					if(  !empty( $attachmentId ) && is_numeric($attachmentId) && wp_get_attachment_url( $attachmentId )  ){
					  $attachment_id = $attachmentId;
					}else if( !empty( $attachmentId ) ){

						$image_attachment_id = attachment_url_to_postid( $attachmentId );

						if( isset($image_attachment_id) && !empty($image_attachment_id) ){
							$attachment_id = $image_attachment_id;
						}else{

							require_once ABSPATH . 'wp-admin/includes/media.php';
							require_once ABSPATH . 'wp-admin/includes/file.php';
							require_once ABSPATH . 'wp-admin/includes/image.php';
							if (filter_var($attachmentId, FILTER_VALIDATE_URL)) {
						        // Get the file type
						        $file_type = wp_check_filetype(basename($attachmentId), null);

						        // Prepare an array of data for the attachment
						        $attachment = array(
						            'post_title'     => sanitize_file_name(basename($attachmentId)),
						            'post_mime_type' => $file_type['type'],
						        );

						        // Try to upload the image
						        $attachment_id = media_sideload_image($attachmentId, 0, null, 'id');
						    }

						}


					}

					if( isset($_POST['genlatlon']) ){


						if(isset($_POST['sbd_map_name']) && $_POST['sbd_map_name'] == 'openstreet_map'){

							// url encode the address
						    $address = urlencode($item['qcpd_item_full_address']);

						    $url = "http://nominatim.openstreetmap.org/?format=json&addressdetails=1&q={$address}&format=json&limit=1";

						    // get the json response
						    $resp_json = sld_get_web_page($url);

						    // decode the json
						    $resp = json_decode($resp_json, true);

							$item['qcpd_item_latitude'] 	= isset( $resp[0]['lat'] ) ? $resp[0]['lat'] : '';
							$item['qcpd_item_longitude'] 	= isset( $resp[0]['lon'] ) ? $resp[0]['lon'] : '';

						}else{

							if( !isset($item['qcpd_item_latitude']) || !isset($item['qcpd_item_longitude']) || empty($item['qcpd_item_latitude']) || empty($item['qcpd_item_longitude']) ){
								if( !empty($item['qcpd_item_full_address']) ){

									$mapapi = 'AIzaSyBACyZ4vA8pQybj9ZdZP-J5zQHqfQkqOXY';
									if(get_option('sbd_map_api_key')!=''){
										$mapapi = get_option('sbd_map_api_key');
									}

									$address_to_lat_lang = sbd_get_lat_long_from_address($item['qcpd_item_full_address'], $mapapi);
									$item['qcpd_item_latitude'] 	= isset( $address_to_lat_lang['lat'] ) ? $address_to_lat_lang['lat'] : '';
									$item['qcpd_item_longitude'] 	= isset( $address_to_lat_lang['lng'] ) ? $address_to_lat_lang['lng'] : '';
		
								}
							}

						}

						
					}
					
					add_post_meta(
						$newest_post_id, 
						'qcpnd_list_item01', array(
							'qcpnd_item_title' 			=> $qcpnd_item_title,
							'qcpnd_item_link' 			=> $item['qcpnd_item_link'],

							'qcpd_item_subtitle' 		=> $qcpd_item_subtitle,
							'qcpnd_item_phone' 			=> $item['qcpnd_item_phone'],
							'qcpnd_item_subtitle' 		=> $item['qcpnd_item_subtitle'],
							'qcpd_item_full_address' 	=> $item['qcpd_item_full_address'],
							'qcpd_item_latitude' 		=> $item['qcpd_item_latitude'],
							'qcpd_item_longitude' 		=> $item['qcpd_item_longitude'],
							'qcpd_item_email' 			=> $item['qcpd_item_email'],
							'qcpd_item_twitter' 		=> $item['qcpd_item_twitter'],
							'qcpnd_item_linkedin' 		=> $item['qcpnd_item_linkedin'],
							'qcpd_item_facebook' 		=> $item['qcpd_item_facebook'],
							'qcpd_item_yelp' 			=> $item['qcpd_item_yelp'],
							'qcpd_item_business_hour' 	=> $item['qcpd_item_business_hour'],
							'qcpd_image_from_link' 		=> $item['qcpd_image_from_link'],
							'qcpd_generate_title' 		=> $item['qcpd_generate_title'],

							'qcpd_item_nofollow' 		=> $item['qcpd_item_nofollow'],
							'qcpd_item_newtab' 			=> $item['qcpd_item_newtab'],
							'qcpd_fa_icon' 				=> $item['qcpd_fa_icon'],
							'qcpd_use_favicon' 			=> $item['qcpd_use_favicon'],
							'qcpd_item_img' 			=> $attachment_id,
							'qcpd_upvote_count' 		=> $item['qcpd_upvote_count'],
							'qcpd_entry_time' 			=>  $item['qcpd_entry_time'],
							'qcpd_timelaps' 			=>  $item['qcpd_timelaps'],
							'qcpd_item_img_link' 		=>  $item['qcpd_item_img_link'],
							'qcpnd_item_description' 			=>  $item['qcpnd_item_description'],
							'qcpd_new' 					=>  $item['qcpd_new'],
							'qcpd_featured' 			=>  $item['qcpd_featured'],
							'qcpd_tags' 				=>  $item['qcpd_tags'],
							'qcpd_field_1' 				=>  $item['qcpd_field_1'],
							'qcpd_field_2' 				=>  $item['qcpd_field_2'],
							'qcpd_field_3' 				=>  $item['qcpd_field_3'],
							'qcpd_field_4' 				=>  $item['qcpd_field_4'],
							'qcpd_field_5' 				=>  $item['qcpd_field_5']
						)
					);
					
					$metaCounter++;
					
				} //end of inner-foreach
				
				$keyCounter++;

				//Relate terms, if exists
				if( !empty($attachedTerms)  )
				{
					
					$termIds = array();

					$postTerms = explode(',', $attachedTerms);

					foreach ($postTerms as $term ) {

						$termId = intval(trim($term));

						$term_name = trim($term);

						if( term_exists($termId, 'pnd_cat') ){
							array_push($termIds, $termId);
						}else if(!empty($term_name)) {

							if( term_exists( $term_name, 'pnd_cat' ) ){

                        		$term_id 	= term_exists( $term_name, 'pnd_cat' );
                        		$term_id 	= isset($term_id['term_id']) ? intval($term_id['term_id']) : intval($term_id);
                        		array_push($termIds, $term_id);

                        	}else{
					       	
					       		$term_id 	= wp_insert_term(
					           					$term_name,
					           					'pnd_cat',
									           	array(
									             	'description' => ''
									           	)
					       					);
					       		
					       		$term_id 	= isset($term_id['term_id']) ? intval($term_id['term_id']) : intval($term_id);
                        		array_push($termIds, $term_id);

                        	}


						}


					}

					wp_set_post_terms( $newest_post_id, $termIds, 'pnd_cat' );

				}
			
			} //end of outer-foreach


		
	    }
	    
	  

	} else {
	    wp_send_json_error( 'CSV file is missing or empty.' );
	}

    wp_send_json_success( array(
        'message' 		=> 'CSV data imported and page created successfully.',
        'redirect_url' 	=> get_permalink( get_page_by_path( $page_slug ) ), // URL for redirection
    ) );
}
add_action( 'wp_ajax_qcld_sbd_import_csv_from_folder', 'qcld_sbd_handle_csv_import' );
add_action('wp_ajax_nopriv_qcld_sbd_import_csv_from_folder', 'qcld_sbd_handle_csv_import'); // ajax for not logged in users


