<?php
error_reporting(E_ALL & ~E_WARNING & ~E_NOTICE & ~E_DEPRECATED);
session_start();
ob_start();
if(empty($_SERVER['HTTP_X_REQUESTED_WITH']) || strtolower($_SERVER['HTTP_X_REQUESTED_WITH']) !== 'xmlhttprequest') {
    die();
}
ini_set("memory_limit",-1);
ini_set('max_execution_time', 9999);
require_once("../../backend/functions.php");
require_once("../../db/connection.php");
require('../vendor/autoload.php');
try {
    if (file_exists("../vendor/MaxMind-DB-Reader-php/autoload.php")) {
        require_once("../vendor/MaxMind-DB-Reader-php/autoload.php");
    }
} catch (\Throwable $e) {}
use MaxMind\Db\Reader;
use proj4php\Proj4php;
use proj4php\Point;
use proj4php\Proj;
$settings = get_settings();
if(!empty($settings['timezone'])) {
    date_default_timezone_set($settings['timezone']);
}
$id_map = (int)$_POST['id_map'];
$id_marker_sel = (int)$_POST['id_marker_sel'];
$auth_settings = sml_get_viewer_auth_settings($id_map);
$viewer_user_id = ((int)$auth_settings['viewer_auth_mode']>0) ? sml_get_viewer_user_id() : null;
session_write_close();
$language = preg_replace('/[^a-zA-Z_]/','',strip_tags($_POST['language']));
$order_by = $_POST['order_by'];
$preview = (int)$_POST['preview'];
$viewport_mode = isset($_POST['viewport_mode']) && (int)$_POST['viewport_mode'] === 1;
$include_metadata = !isset($_POST['include_metadata']) || (int)$_POST['include_metadata'] === 1;
$track_access = !isset($_POST['track_access']) || (int)$_POST['track_access'] === 1;
$owner_pending_sql = '';
$owner_pending_visibility_sql = '';
if(!empty($viewer_user_id)) {
    $viewer_user_id = (int)$viewer_user_id;
    $owner_pending_sql = " OR (m.to_validate=1 AND m.id_user=$viewer_user_id)";
    $owner_pending_visibility_sql = " OR (m.to_validate=1 AND m.id_user=$viewer_user_id)";
}
$viewport_filter_m = '';
$viewport_filter_m1 = '';
$viewport_filter_m2 = '';
$viewport_center_lat = null;
$viewport_center_lon = null;
if($viewport_mode && isset($_POST['min_lat'],$_POST['max_lat'],$_POST['min_lon'],$_POST['max_lon'])) {
    $min_lat = max(-90.0,min(90.0,(float)$_POST['min_lat']));
    $max_lat = max(-90.0,min(90.0,(float)$_POST['max_lat']));
    $min_lon = max(-180.0,min(180.0,(float)$_POST['min_lon']));
    $max_lon = max(-180.0,min(180.0,(float)$_POST['max_lon']));
    if($min_lat>$max_lat) {
        $tmp = $min_lat;
        $min_lat = $max_lat;
        $max_lat = $tmp;
    }
    if($min_lon>$max_lon) {
        $tmp = $min_lon;
        $min_lon = $max_lon;
        $max_lon = $tmp;
    }
    $viewport_center_lat = isset($_POST['center_lat'])
        ? max(-90.0,min(90.0,(float)$_POST['center_lat']))
        : ($min_lat+$max_lat)/2;
    $viewport_center_lon = isset($_POST['center_lon'])
        ? max(-180.0,min(180.0,(float)$_POST['center_lon']))
        : ($min_lon+$max_lon)/2;
    $viewport_filter_m = " AND (m.id=$id_marker_sel OR (m.lat BETWEEN $min_lat AND $max_lat AND m.lon BETWEEN $min_lon AND $max_lon))";
    $viewport_filter_m1 = " AND (m1.id=$id_marker_sel OR (m1.lat BETWEEN $min_lat AND $max_lat AND m1.lon BETWEEN $min_lon AND $max_lon))";
    $viewport_filter_m2 = " AND (m2.id=$id_marker_sel OR (m2.lat BETWEEN $min_lat AND $max_lat AND m2.lon BETWEEN $min_lon AND $max_lon))";
} elseif($viewport_mode) {
    $viewport_filter_m = " AND 1=0";
    $viewport_filter_m1 = " AND 1=0";
    $viewport_filter_m2 = " AND 1=0";
}
$array_markers = array();
$array_base64 = array();
switch($order_by) {
    case 'priority':
        $order_by = "ORDER BY m.order ASC, m.id DESC";
        break;
    case 'name':
        $order_by = "ORDER BY m.name ASC, m.id DESC";
        break;
    case 'city':
        $order_by = "ORDER BY m.city ASC, m.id DESC";
        break;
    default:
        $order_by = "ORDER BY m.id DESC";
        break;
}
$viewport_scope_order = $order_by;
if($viewport_center_lat!==null && $viewport_center_lon!==null) {
    $longitude_scale = max(cos(deg2rad($viewport_center_lat)),0.01);
    $viewport_scope_order = "ORDER BY (POW(m.lat-($viewport_center_lat),2) + POW((m.lon-($viewport_center_lon))*$longitude_scale,2)) ASC, m.id DESC";
}
$viewport_truncated = false;
$viewport_limit = 3000;
if(!empty($settings['tripadvisor_api_key'])) {
    $tripadvisor_enable = true;
} else {
    $tripadvisor_enable = false;
}
$markers_size = 0;
$enable_last_update = false;
$enable_add_photo = false;
$extent = '';
$address_format = '';
$array_markers_lang = array();
$array_markers_conn_lang = array();
$array_categories_lang = array();
$query = "SELECT markers_size,extent,languages_enabled,language as language_map,address_format,enable_last_update,enable_add_photo,lazy_loading_max_markers FROM sml_maps WHERE id=$id_map LIMIT 1;";
$result = $mysqli->query($query);
if($result) {
    if ($result->num_rows == 1) {
        $row = $result->fetch_array(MYSQLI_ASSOC);
        $configured_limit = (int)$row['lazy_loading_max_markers'];
        if($configured_limit>0) {
            $viewport_limit = min($configured_limit,50000);
        }
        if($viewport_mode && $viewport_filter_m!=='') {
            $query_scope = "SELECT m.id FROM sml_markers AS m
                            WHERE m.id_map=$id_map AND (m.to_validate=0 $owner_pending_sql)
                            AND (m.visible_map=1 OR m.visible_search=1 OR m.centered=1 OR m.id=$id_marker_sel)
                            $viewport_filter_m
                            $viewport_scope_order
                            LIMIT ".($viewport_limit+1).";";
            $result_scope = $mysqli->query($query_scope);
            $viewport_marker_ids = [];
            if($result_scope) {
                while($row_scope = $result_scope->fetch_array(MYSQLI_ASSOC)) {
                    $viewport_marker_ids[] = (int)$row_scope['id'];
                }
            }
            if(count($viewport_marker_ids)>$viewport_limit) {
                $viewport_truncated = true;
                $viewport_marker_ids = array_slice($viewport_marker_ids,0,$viewport_limit);
            }
            if($id_marker_sel>0 && !in_array($id_marker_sel,$viewport_marker_ids,true)) {
                if(count($viewport_marker_ids)>=$viewport_limit) {
                    array_pop($viewport_marker_ids);
                }
                $viewport_marker_ids[] = $id_marker_sel;
            }
            if(count($viewport_marker_ids)>0) {
                $viewport_ids_sql = implode(',',$viewport_marker_ids);
                $viewport_filter_m = " AND m.id IN ($viewport_ids_sql)";
                $viewport_filter_m1 = " AND m1.id IN ($viewport_ids_sql)";
                $viewport_filter_m2 = " AND m2.id IN ($viewport_ids_sql)";
            } else {
                $viewport_filter_m = " AND 1=0";
                $viewport_filter_m1 = " AND 1=0";
                $viewport_filter_m2 = " AND 1=0";
            }
        }
        $enable_last_update = $row['enable_last_update'];
        $enable_add_photo = $row['enable_add_photo'];
        $markers_size = $row['markers_size'];
        $extent = $row['extent'];
        $extent = json_decode($extent, true);
        $address_format = json_decode($row['address_format'],true);
        $count_languages_enabled = 0;
        if(empty($row['languages_enabled'])) {
            $row['languages_enabled']=array();
        } else {
            $row['languages_enabled']=json_decode($row['languages_enabled'],true);
        }
        foreach ($row['languages_enabled'] as $lang_enabled) {
            if($lang_enabled==1) {
                $count_languages_enabled++;
            }
        }
        $map_language = $row['language_map'];
        $default_language_settings = $settings['language'];
        if(empty($map_language)) $map_language=$default_language_settings;
        if($count_languages_enabled>=1) {
            if(!empty($language) && $language!=$map_language) {
                $query_lang = "SELECT l.* FROM sml_markers_lang l INNER JOIN sml_markers m ON l.id_marker = m.id WHERE l.language='$language' AND m.id_map=$id_map $viewport_filter_m;";
                $result_lang = $mysqli->query($query_lang);
                if($result_lang) {
                    if ($result_lang->num_rows > 0) {
                        while($row_lang = $result_lang->fetch_array(MYSQLI_ASSOC)) {
                            $id_marker = $row_lang['id_marker'];
                            unset($row_lang['id_marker']);
                            unset($row_lang['language']);
                            if ($row_lang['hours'] == "<p><br></p>") {
                                $row_lang['hours'] = "";
                            }
                            if ($row_lang['description'] == "<p><br></p>") {
                                $row_lang['description'] = "";
                            }
                            $array_markers_lang[$id_marker]=$row_lang;
                        }
                    }
                }
                $query_lang = "SELECT DISTINCT l.* FROM sml_markers_connects_lang l INNER JOIN sml_markers_connects c ON l.id_marker_connect = c.id INNER JOIN sml_markers m ON (c.id_marker_source = m.id OR c.id_marker_dest = m.id) WHERE l.language='$language' AND m.id_map=$id_map $viewport_filter_m;";
                $result_lang = $mysqli->query($query_lang);
                if($result_lang) {
                    if ($result_lang->num_rows > 0) {
                        while($row_lang = $result_lang->fetch_array(MYSQLI_ASSOC)) {
                            $id_marker_conn = $row_lang['id_marker_connect'];
                            unset($row_lang['id_marker_connect']);
                            unset($row_lang['language']);
                            $array_markers_conn_lang[$id_marker_conn]=$row_lang;
                        }
                    }
                }
                if($include_metadata) {
                    $query_lang = "SELECT l.* FROM sml_categories_lang l INNER JOIN sml_categories c ON l.id_category = c.id WHERE l.language='$language' AND c.id_map=$id_map;";
                    $result_lang = $mysqli->query($query_lang);
                    if($result_lang) {
                        if ($result_lang->num_rows > 0) {
                            while($row_lang = $result_lang->fetch_array(MYSQLI_ASSOC)) {
                                $id_category = $row_lang['id_category'];
                                unset($row_lang['id_category']);
                                unset($row_lang['language']);
                                $array_categories_lang[$id_category]=$row_lang;
                            }
                        }
                    }
                }
            }
        }
    }
}
$markers_images = array();
$query = "SELECT i.* FROM sml_images i INNER JOIN sml_markers m ON i.id_marker = m.id WHERE i.to_validate=0 AND m.id_map=$id_map AND (m.visible_map=1 OR m.visible_search=1 OR m.id=$id_marker_sel) $viewport_filter_m ORDER BY i.priority;";
$result = $mysqli->query($query);
if($result) {
    if ($result->num_rows > 0) {
        while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
            $id_m = $row['id_marker'];
            if(!isset($markers_images[$id_m])) {
                $markers_images[$id_m] = array();
                $markers_images[$id_m]['main_image'] = "";
                $markers_images[$id_m]['images'] = array();
            }
            if($row['main']==1) {
                $markers_images[$id_m]['main_image'] = $row['image'];
            }
            if($row['exclude']==0) {
                if (strpos($row['image'], 'http') !== 0) {
                    if (strpos($row['image'], 'media_') === 0) {
                        $row['image'] = 'media/'.$row['image'];
                    } else {
                        $row['image'] = 'marker_images/'.$row['image'];
                    }
                }
                $markers_images[$id_m]['images'][] = $row['image'];
            }
        }
    }
}
$markers_images_count = array();
$query_count = "SELECT i.id_marker, COUNT(i.id) as total_images FROM sml_images i INNER JOIN sml_markers m ON i.id_marker = m.id WHERE m.id_map=$id_map AND (m.visible_map=1 OR m.visible_search=1 OR m.id=$id_marker_sel) $viewport_filter_m GROUP BY i.id_marker;";
$result_count = $mysqli->query($query_count);
if($result_count) {
    if ($result_count->num_rows > 0) {
        while ($row_count = $result_count->fetch_array(MYSQLI_ASSOC)) {
            $markers_images_count[$row_count['id_marker']] = (int)$row_count['total_images'];
        }
    }
}
$icons = array();
$query = "SELECT id,image FROM sml_icons;";
$result = $mysqli->query($query);
if($result) {
    if ($result->num_rows > 0) {
        while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
            $icons[$row['id']]=$row['image'];
        }
    }
}
$categories_assoc = array();
$query = "SELECT ca.id_marker, GROUP_CONCAT(ca.id_category SEPARATOR ',') as cats FROM sml_markers_categories_assoc ca INNER JOIN sml_markers m ON ca.id_marker = m.id WHERE m.id_map=$id_map AND (m.visible_map=1 OR m.visible_search=1 OR m.id=$id_marker_sel) $viewport_filter_m GROUP BY ca.id_marker;";
$result = $mysqli->query($query);
if($result) {
    if ($result->num_rows > 0) {
        while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
            $categories_assoc[$row['id_marker']] = $row['cats'] . ",";
        }
    }
}
$ratings = array();
$query = "SELECT r.id_marker, ROUND(AVG(r.rating),1) as rating, COUNT(r.rating) as num_reviews FROM sml_reviews r INNER JOIN sml_markers m ON r.id_marker = m.id WHERE m.id_map=$id_map AND (m.visible_map=1 OR m.visible_search=1 OR m.id=$id_marker_sel) $viewport_filter_m GROUP BY r.id_marker;";
$result = $mysqli->query($query);
if($result) {
    if ($result->num_rows > 0) {
        while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
            $ratings[$row['id_marker']]=[$row['rating'],$row['num_reviews']];
        }
    }
}
$date_now = date('Ymd');
$hour_now = date('Hi');
$day_now = date('N');
$query = "SELECT m.* FROM sml_markers as m 
            WHERE m.id_map=? AND (m.to_validate=0 $owner_pending_sql) AND (m.visible_map=1 OR m.visible_search=1 OR m.centered=1 OR m.id=? $owner_pending_visibility_sql) 
            $viewport_filter_m
            $order_by";

if($smt = $mysqli->prepare($query)) {
    $smt->bind_param('ii',$id_map,$id_marker_sel);
    $result = $smt->execute();
    if($result) {
        $result = get_result($smt);
        if(count($result)>0) {
            foreach($result as $row) {
                $id_marker = $row['id'];
                $floats = ['lat', 'lon', 'marker_size', 'song_bg_volume', 'song_volume'];
                foreach($floats as $k) {
                    if(isset($row[$k])) $row[$k] = (float)str_replace(",",".", $row[$k]);
                }
                if(!isValidCoordinate($row['lat'] ?? 0, $row['lon'] ?? 0)) continue;
                if(!empty($extent) && !isCoordinateInsideExtent($row['lat'], $row['lon'], $extent)) continue;
                $ints = ['active', 'centered', 'visible_map', 'visible_search', 'featured', 'open_sheet', 'view_popup', 'view_directions', 'view_street_view', 'view_review', 'view_add_photo', 'view_share', 'popup_image_height', 'open_extra_content', 'geofence_radius', 'selected_zoom', 'duration_zoom', 'song_play_bg'];
                foreach($ints as $k) {
                    if(isset($row[$k])) $row[$k] = (int)$row[$k];
                }
                if(isset($row['hours']) && $row['hours'] == "<p><br></p>") $row['hours'] = "";
                if(isset($row['description']) && $row['description'] == "<p><br></p>") $row['description'] = "";
                if(isset($array_markers_lang[$id_marker])) {
                    $lang_data = $array_markers_lang[$id_marker];
                    if (!empty($lang_data['name'])) $row['name'] = $lang_data['name'];
                    if (!empty($lang_data['hours'])) $row['hours'] = $lang_data['hours'];
                    if (!empty($lang_data['description'])) $row['description'] = $lang_data['description'];
                    if (!empty($lang_data['box_warning'])) $row['box_warning'] = $lang_data['box_warning'];
                    if (!empty($lang_data['icon_label'])) $row['icon_label'] = $lang_data['icon_label'];
                    if (!empty($lang_data['extra_button_title_1'])) $row['extra_button_title_1'] = $lang_data['extra_button_title_1'];
                    if (!empty($lang_data['extra_button_value_1'])) $row['extra_button_value_1'] = $lang_data['extra_button_value_1'];
                    for($i=1;$i<=20;$i++) {
                        if (!empty($lang_data['extra_field_name_'.$i])) {
                            $row['extra_field_name_'.$i] = $lang_data['extra_field_name_'.$i];
                        }
                        if (!empty($lang_data['extra_field_value_'.$i])) {
                            $row['extra_field_value_'.$i] = $lang_data['extra_field_value_'.$i];
                        }
                    }
                }
                $schedule = $row['schedule'] ?? '';
                if(!empty($schedule)) {
                    $schedule = json_decode($schedule, true);
                    $ignore_year = isset($schedule['ignore_year']) ? (int)$schedule['ignore_year'] : 0;
                    if(!empty($schedule['from_date']) && !empty($schedule['to_date'])) {
                        if ($ignore_year) {
                            $md_from = date('md', strtotime($schedule['from_date']));
                            $md_to = date('md', strtotime($schedule['to_date']));
                            $md_now = substr($date_now, -4);
                            if ($md_from <= $md_to) {
                                if (($md_now < $md_from) || ($md_now > $md_to)) continue;
                            } else {
                                if (($md_now < $md_from) && ($md_now > $md_to)) continue;
                            }
                        } else {
                            $date_from = date('Ymd', strtotime($schedule['from_date']));
                            $date_to = date('Ymd', strtotime($schedule['to_date']));
                            if(($date_now < $date_from) || ($date_now > $date_to)) continue;
                        }
                    }
                    if(!empty($schedule['days'])) {
                        $days = explode(",", $schedule['days']);
                        $alldays_zero = 1;
                        for($i=0; $i<count($days); $i++) {
                            if($days[$i] == 1) $alldays_zero = 0;
                        }
                        if($days[$day_now-1] == 0 && $alldays_zero == 0) continue;
                    }
                    if(!empty($schedule['from_hour']) && !empty($schedule['to_hour'])) {
                        $hour_from = date('Hi', strtotime($schedule['from_hour']));
                        $hour_to = date('Hi', strtotime($schedule['to_hour']));
                        if(($hour_now < $hour_from) || ($hour_now >= $hour_to)) continue;
                    }
                }
                if(!empty($row['schedule_box_warning'])) {
                    $schedule_box = json_decode($row['schedule_box_warning'], true);
                    $ignore_year = isset($schedule_box['ignore_year']) ? (int)$schedule_box['ignore_year'] : 0;
                    if(!empty($schedule_box['from_date']) && !empty($schedule_box['to_date'])) {
                        if ($ignore_year) {
                            $md_from = date('md', strtotime($schedule_box['from_date']));
                            $md_to = date('md', strtotime($schedule_box['to_date']));
                            $md_now = substr($date_now, -4);
                            if ($md_from <= $md_to) {
                                if (($md_now < $md_from) || ($md_now > $md_to)) $row['box_warning'] = "";
                            } else {
                                if (($md_now < $md_from) && ($md_now > $md_to)) $row['box_warning'] = "";
                            }
                        } else {
                            $date_from = date('Ymd', strtotime($schedule_box['from_date']));
                            $date_to = date('Ymd', strtotime($schedule_box['to_date']));
                            if(($date_now < $date_from) || ($date_now > $date_to)) $row['box_warning'] = "";
                        }
                    }
                    if(!empty($schedule_box['days'])) {
                        $days = explode(",", $schedule_box['days']);
                        $alldays_zero = 1;
                        for($i=0; $i<count($days); $i++) {
                            if($days[$i] == 1) $alldays_zero = 0;
                        }
                        if($days[$day_now-1] == 0 && $alldays_zero == 0) $row['box_warning'] = "";
                    }
                    if(!empty($schedule_box['from_hour']) && !empty($schedule_box['to_hour'])) {
                        $hour_from = date('Hi', strtotime($schedule_box['from_hour']));
                        $hour_to = date('Hi', strtotime($schedule_box['to_hour']));
                        if(($hour_now < $hour_from) || ($hour_now >= $hour_to)) $row['box_warning'] = "";
                    }
                }
                if($id_marker == $id_marker_sel) $row['visible_map'] = 1;
                $row['address'] = format_address_string($row, $address_format);
                if(!empty($row['song_link'])) $row['song'] = $row['song_link'];
                if(isset($markers_images[$row['id']])) {
                    if(!empty($markers_images[$row['id']]['main_image'])) $row['main_image'] = $markers_images[$row['id']]['main_image'];
                    if(!empty($markers_images[$row['id']]['images'])) $row['images'] = $markers_images[$row['id']]['images'];
                }
                $icon_parts = explode("|", $row['icon'] ?? '');
                $row['icon'] = $icon_parts[1] ?? '';
                if(!empty($row['icon_image'])) {
                    $ext = substr(strrchr($row['icon_image'], '.'), 1);
                    if($ext != 'gif' && $ext != 'svg') {
                        $row['icon_base64'] = resize_image($row['icon_image'], $row['marker_size'] == 0 ? $markers_size : $row['marker_size']);
                    }
                }
                $id_icon_library = $row['id_icon_library'] ?? 0;
                if(isset($icons[$id_icon_library])) {
                    $row['icon_image'] = $icons[$id_icon_library];
                }
                if(isset($ratings[$row['id']])) {
                    $row['rating'] = (float)$ratings[$row['id']][0];
                    $row['num_reviews'] = (int)$ratings[$row['id']][1];
                }
                if(isset($categories_assoc[$row['id']])) {
                    $row['id_categories'] = rtrim($categories_assoc[$row['id']], ",");
                }
                if(isset($markers_images_count[$row['id']])) {
                    $row['total_images'] = $markers_images_count[$row['id']];
                }
                if(!$tripadvisor_enable) $row['tripadvisor_location_id'] = '';
                if(!empty($row['color_hex'])) $row['darker_color_hex'] = adjustBrightness($row['color_hex'], -0.3);
                if(!empty($row['color_hex_hover'])) $row['darker_color_hex_hover'] = adjustBrightness($row['color_hex_hover'], -0.3);
                if($enable_last_update && $row['view_last_update']==1 && !empty($row['update_date'])) {
                    $row['update_date'] = formatTime("dd MMM y", $language, strtotime($row['update_date']));
                } else {
                    $row['update_date'] = '';
                }
                if(!$enable_add_photo) {
                    $row['view_add_photo']=0;
                }
                $row['is_owner'] = (!empty($viewer_user_id) && !empty($row['id_user']) && (int)$row['id_user']===(int)$viewer_user_id) ? 1 : 0;
                $row['can_edit_pending'] = ($row['is_owner'] && (int)$row['to_validate']===1) ? 1 : 0;
                if($row['can_edit_pending']) {
                    $row['visible_map'] = 1;
                    $row['visible_search'] = 1;
                    $row['view_directions'] = 0;
                    $row['view_street_view'] = 0;
                    $row['view_review'] = 0;
                    $row['view_add_photo'] = 0;
                    $row['view_share'] = 0;
                    $row['open_extra_content'] = 0;
                }
                for($i=1;$i<=20;$i++) {
                    if(empty($row['extra_field_visible_'.$i]) || empty($row['extra_field_value_'.$i])) {
                        unset($row['extra_field_value_'.$i], $row['extra_field_icon_'.$i], $row['extra_field_name_'.$i]);
                    } else {
                        $row['extra_field_value_'.$i] = str_replace(["\r\n","\r","\n"], "<br>", htmlspecialchars_decode($row['extra_field_value_'.$i]));
                        if($row['extra_field_icon_'.$i] == 'fa-circle') unset($row['extra_field_icon_'.$i]);
                    }
                    unset($row['extra_field_visible_'.$i]);
                }
                if(empty($row['extra_button_value_1'])) {
                    unset($row['extra_button_value_1'], $row['extra_button_icon_1']);
                } else {
                    $row['extra_button_value_1'] = str_replace(["\r\n","\r","\n"], "<br>", htmlspecialchars_decode($row['extra_button_value_1']));
                    if($row['extra_button_icon_1'] == 'fa-circle') unset($row['extra_button_icon_1']);
                }
                $empty_to_unset = ['website_caption', 'whatsapp', 'hours', 'description', 'website', 'email', 'phone', 'link', 'color_hex', 'name', 'icon_label', 'tripadvisor_location_id', 'icon', 'darker_color_hex', 'color_hex_hover', 'darker_color_hex_hover', 'icon_color_hex', 'icon_color_hex_hover', 'id_categories', 'song_link', 'song', 'address', 'box_warning', 'update_date', 'icon_base64', 'icon_image', 'main_image', 'images'];
                foreach($empty_to_unset as $k) {
                    if(empty($row[$k])) unset($row[$k]);
                }
                $zero_to_unset = ['rating', 'num_reviews', 'total_images'];
                foreach($zero_to_unset as $k) {
                    if(isset($row[$k]) && $row[$k] == 0) unset($row[$k]);
                }
                unset($row['id_map'], $row['id_user'], $row['to_validate'], $row['schedule'], $row['schedule_box_warning'], $row['city'], $row['street'], $row['postal_code'], $row['country'], $row['county'], $row['state'], $row['priority'], $row['order'], $row['id_icon_library'], $row['view_last_update']);
                $array_markers[] = $row;
            }
        }
    }
}

$array_categories = [];
if($include_metadata) {
$categories_map = [];
$query_cats = "SELECT c.*, i.image AS icon_image,
    (SELECT COUNT(sc.id) FROM sml_categories sc WHERE sc.id_category_parent = c.id) AS child_count
    FROM sml_categories AS c
    LEFT JOIN sml_icons AS i ON i.id = c.id_icon_library
    WHERE c.id_map = ?;";
if ($smt_cats = $mysqli->prepare($query_cats)) {
    $smt_cats->bind_param('i', $id_map);
    $smt_cats->execute();
    $result_cats = get_result($smt_cats);
    foreach ($result_cats as $row) {
        $id_category = (int)$row['id'];
        if(isset($array_categories_lang[$id_category])) {
            if (!empty($array_categories_lang[$id_category]['name'])) {
                $row['name'] = $array_categories_lang[$id_category]['name'];
            }
        }
        $row['id_category_parent'] = empty($row['id_category_parent']) ? 0 : (int)$row['id_category_parent'];
        $row['direct_count'] = 0;
        $categories_map[$id_category] = $row;
    }
    $smt_cats->close();
}

$query_markers = "SELECT ca.id_category,COUNT(DISTINCT m.id) AS total_markers
    FROM sml_markers_categories_assoc AS ca
    JOIN sml_markers AS m ON ca.id_marker = m.id
    WHERE m.id_map = ? AND m.to_validate = 0 AND m.active = 1 AND (m.visible_map = 1 OR m.visible_search = 1)
    GROUP BY ca.id_category;";
if ($smt_markers = $mysqli->prepare($query_markers)) {
    $smt_markers->bind_param('i', $id_map);
    $smt_markers->execute();
    $result_markers = get_result($smt_markers);
    foreach ($result_markers as $row) {
        $cat_id = (int)$row['id_category'];
        if (isset($categories_map[$cat_id])) {
            $categories_map[$cat_id]['direct_count'] += (int)$row['total_markers'];
        }
    }
    $smt_markers->close();
}

$query_geodata = "SELECT ca.id_category,COUNT(DISTINCT m.id) AS total_geodata
    FROM sml_geodata_categories_assoc AS ca
    JOIN sml_geodata AS m ON ca.id_geodata = m.id
    WHERE m.id_map = ?
    GROUP BY ca.id_category;";
if ($smt_geodata = $mysqli->prepare($query_geodata)) {
    $smt_geodata->bind_param('i', $id_map);
    $smt_geodata->execute();
    $result_geodata = get_result($smt_geodata);
    foreach ($result_geodata as $row) {
        $cat_id = (int)$row['id_category'];
        if (isset($categories_map[$cat_id])) {
            $categories_map[$cat_id]['direct_count'] += (int)$row['total_geodata'];
        }
    }
    $smt_geodata->close();
}

$children_map = [];
foreach ($categories_map as $id => $category) {
    $parent_id = $category['id_category_parent'];
    if (!isset($children_map[$parent_id])) {
        $children_map[$parent_id] = [];
    }
    $children_map[$parent_id][] = $id;
}

function get_category_total_count($category_id, &$categories_map, &$children_map, &$visited_global = []) {
    if (isset($visited_global[$category_id])) {
        return $categories_map[$category_id]['count_m'] ?? 0;
    }
    $visited_global[$category_id] = true;
    if (isset($categories_map[$category_id]['count_m'])) {
        return $categories_map[$category_id]['count_m'];
    }
    $total_count = $categories_map[$category_id]['direct_count'] ?? 0;
    if (isset($children_map[$category_id])) {
        foreach ($children_map[$category_id] as $child_id) {
            $total_count += get_category_total_count($child_id, $categories_map, $children_map, $visited_global);
        }
    }
    $categories_map[$category_id]['count_m'] = $total_count;
    return $total_count;
}

foreach (array_keys($categories_map) as $id) {
    get_category_total_count($id, $categories_map, $children_map);
}

$array_categories = [];
foreach ($categories_map as $category) {
    unset($category['direct_count']);
    $array_categories[] = $category;
}

usort($array_categories, function($a, $b) {
    if ($a['order'] == $b['order']) {
        return strnatcmp($a['name'], $b['name']);
    }
    return ($a['order'] < $b['order']) ? -1 : 1;
});
}

$array_markers_conn = array();
$query = "SELECT mc.id, mc.color, mc.width, m1.lat as lat_source, m1.lon as lon_source, 
                 m2.lat as lat_dest, m2.lon as lon_dest, m1.id as id_source, m2.id as id_dest, 
                 mc.title, mc.description 
          FROM sml_markers_connects as mc
          INNER JOIN sml_markers as m1 ON m1.id = mc.id_marker_source AND (m1.visible_map=1 OR m1.visible_search=1 OR m1.id=$id_marker_sel) $viewport_filter_m1 AND m1.id_map=?
          INNER JOIN sml_markers as m2 ON m2.id = mc.id_marker_dest AND (m2.visible_map=1 OR m2.visible_search=1 OR m2.id=$id_marker_sel) $viewport_filter_m2 AND m2.id_map=$id_map";
if($smt = $mysqli->prepare($query)) {
    $smt->bind_param('i', $id_map);
    $result = $smt->execute();
    if ($result) {
        $result = get_result($smt);
        if (count($result) > 0) {
            foreach ($result as $row) {
                $id_marker_conn = $row['id'];
                if(isset($array_markers_conn_lang[$id_marker_conn])) {
                    $lang_data = $array_markers_conn_lang[$id_marker_conn];
                    if (!empty($lang_data['title'])) $row['title'] = $lang_data['title'];
                    if (!empty($lang_data['description'])) $row['description'] = $lang_data['description'];
                }
                if(empty($row['title'])) $row['title']='';
                if(empty($row['description'])) $row['description']='';
                $row['description'] = htmlspecialchars_decode($row['description']);
                $row['description'] = str_replace("\'","'",$row['description']);
                $row['description'] = str_replace(["\r\n","\r","\n"], "<br>", $row['description']);
                $array_markers_conn[] = $row;
            }
        }
    }
}

$row_pin = array();
$row_pin['pin_marker_enable']=false;
if($include_metadata) {
$query = "SELECT m.pin_marker_enable,m.pin_marker_size,m.pin_marker_icon,m.pin_marker_color_hex,m.pin_marker_icon_color_hex,m.pin_marker_align,i.image as pin_marker_icon_image FROM sml_maps as m
            LEFT JOIN sml_icons as i ON i.id=m.pin_marker_id_icon_library
            WHERE m.id=?";
if($smt = $mysqli->prepare($query)) {
    $smt->bind_param('i', $id_map);
    $result = $smt->execute();
    if ($result) {
        $result = get_result($smt);
        if (count($result) == 1) {
            $row_pin = array_shift($result);
            try {
                $row_pin['pin_marker_icon'] = explode("|",$row_pin['pin_marker_icon'])[1];
            } catch (Exception $e) {
                $row_pin['pin_marker_icon'] = '';
            }
            if(empty($row_pin['pin_marker_icon_image'])) $row_pin['pin_marker_icon_image']='';
            $row_pin['pin_marker_icon_base64']='';
            if(!empty($row_pin['pin_marker_icon_image'])) {
                $ext = substr(strrchr($row_pin['pin_marker_icon_image'], '.'), 1);
                if(($ext=='gif') || ($ext=='svg')) {
                    $row_pin['pin_marker_icon_base64']='';
                } else {
                    $row_pin['pin_marker_icon_base64']=resize_image($row_pin['pin_marker_icon_image'],$row_pin['pin_marker_size']);
                }
            }
        }
    }
}
}
if($preview == 0 && $track_access) {
    $database_path = __DIR__ . '/../../services/ip-to-country.mmdb';
    $download_url = "https://github.com/iplocate/ip-address-databases/raw/refs/heads/main/ip-to-country/ip-to-country.mmdb";
    $ip = getIPAddress();
    $ip_visitor = hash('sha256',$ip);
    updateIpDatabaseIfNeeded($database_path, $download_url, 10);
    $location_data = getCountryFromIp($ip, $database_path);
    $country_code = "";
    if ($location_data) {
        $country_code = $location_data['code'];
    }
    $now = date('Y-m-d H:i:s');
    $stmt_check = $mysqli->prepare("SELECT id FROM sml_access_log WHERE id_map = ? AND ip = ? AND date_time >= DATE_SUB(?, INTERVAL 30 SECOND) LIMIT 1;");
    if ($stmt_check) {
        $stmt_check->bind_param('iss', $id_map, $ip_visitor,$now);
        $stmt_check->execute();
        $result_check = custom_get_result($stmt_check);
        if ($result_check->num_rows == 0) {
            $stmt_log = $mysqli->prepare("INSERT INTO sml_access_log(id_map, date_time, ip, country_code) VALUES(?, ?, ?, ?);");
            if ($stmt_log) {
                $stmt_log->bind_param('isss', $id_map, $now, $ip_visitor, $country_code);
                $stmt_log->execute();
            }
        }
    }
}
ob_end_clean();
echo json_encode(array("markers"=>$array_markers,"markers_connections"=>$array_markers_conn,"categories"=>$array_categories,"pin_marker"=>$row_pin,"truncated"=>$viewport_truncated,"marker_limit"=>$viewport_limit));

function adjustBrightness($hexCode, $adjustPercent) {
    $hexCode = ltrim($hexCode, '#');
    if (strlen($hexCode) == 3) {
        $hexCode = $hexCode[0] . $hexCode[0] . $hexCode[1] . $hexCode[1] . $hexCode[2] . $hexCode[2];
    }
    $hexCode = array_map('hexdec', str_split($hexCode, 2));
    foreach ($hexCode as & $color) {
        $adjustableLimit = $adjustPercent < 0 ? $color : 255 - $color;
        $adjustAmount = ceil($adjustableLimit * $adjustPercent);

        $color = str_pad(dechex($color + $adjustAmount), 2, '0', STR_PAD_LEFT);
    }
    return '#'.implode($hexCode);
}

function resize_image($img_name, $scale) {
    global $array_base64;
    $cache_key = $img_name . '_' . $scale;
    if (array_key_exists($cache_key, $array_base64)) {
        return $array_base64[$cache_key];
    }
    $icon_path = "../icons/$img_name";
    if (!file_exists($icon_path)) {
        return '';
    }
    list($width, $height, $type, $attr) = getimagesize($icon_path);
    if (!$width || !$height) return '';
    $new_w = ($width / 10) * $scale;
    $new_h = ($height / 10) * $scale;
    $ext = strtolower(pathinfo($img_name, PATHINFO_EXTENSION));
    if ($ext == 'jpg' || $ext == 'jpeg') {
        $src_img = imagecreatefromjpeg($icon_path);
    } elseif ($ext == 'png') {
        $src_img = imagecreatefrompng($icon_path);
    } else {
        return '';
    }
    if ($new_h <= 0) {
        $wRatio = $new_w / $width;
        $thumb_h = ceil($wRatio * $height);
        $thumb_w = $new_w;
    } else {
        $thumb_w = $new_w;
    }
    if ($new_w <= 0) {
        $hRatio = $new_h / $height;
        $thumb_w = ceil($hRatio * $width);
        $thumb_h = $new_h;
    } else {
        $thumb_h = $new_h;
    }
    $dst_img = imagecreatetruecolor($thumb_w, $thumb_h);
    if ($ext == 'png') {
        imagealphablending($dst_img, false);
        imagesavealpha($dst_img, true);
    }
    imagecopyresampled($dst_img, $src_img, 0, 0, 0, 0, $thumb_w, $thumb_h, $width, $height);
    ob_start();
    if ($ext == 'png') {
        imagepng($dst_img);
        $mime_type = 'png';
    } else {
        imagejpeg($dst_img);
        $mime_type = 'jpeg';
    }
    $image_data = ob_get_clean();
    $base64 = 'data:image/' . $mime_type . ';base64,' . base64_encode($image_data);
    imagedestroy($dst_img);
    imagedestroy($src_img);
    $array_base64[$cache_key] = $base64;
    return $base64;
}

function isValidCoordinate($latitude, $longitude) {
    if (!is_numeric($latitude) || $latitude < -90 || $latitude > 90) {
        return false;
    }
    if (!is_numeric($longitude) || $longitude < -180 || $longitude > 180) {
        return false;
    }
    return true;
}

function isCoordinateInsideExtent($latitude, $longitude, $extent) {
    $proj4 = new Proj4php();
    $projWGS84 = new Proj('EPSG:4326', $proj4);
    $proj3857 = new Proj('EPSG:3857', $proj4);
    $point = new Point($longitude, $latitude, $projWGS84);
    $point3857 = $proj4->transform($proj3857, $point);
    return (
        $point3857->x >= $extent[0] &&
        $point3857->x <= $extent[2] &&
        $point3857->y >= $extent[1] &&
        $point3857->y <= $extent[3]
    );
}

function getIPAddress() {
    if(!empty($_SERVER['HTTP_CLIENT_IP'])) {
        $ip = $_SERVER['HTTP_CLIENT_IP'];
    } elseif (!empty($_SERVER['HTTP_X_FORWARDED_FOR'])) {
        $ip = $_SERVER['HTTP_X_FORWARDED_FOR'];
    } else{
        $ip = $_SERVER['REMOTE_ADDR'];
    }
    return $ip;
}

function updateIpDatabaseIfNeeded($db_path, $download_url, $interval_days = 30) {
    $lock_file = $db_path . '.lock';
    $temp_file = $db_path . '.tmp';
    $interval_seconds = $interval_days * 24 * 60 * 60;
    $needs_update = false;
    if (!file_exists($db_path)) {
        $needs_update = true;
    } elseif ((time() - filemtime($db_path)) > $interval_seconds) {
        $needs_update = true;
    }
    if ($needs_update && !file_exists($lock_file)) {
        touch($lock_file);
        $ch = curl_init($download_url);
        $fp = fopen($temp_file, 'w+');
        if ($fp) {
            curl_setopt($ch, CURLOPT_FILE, $fp);
            curl_setopt($ch, CURLOPT_FOLLOWLOCATION, true);
            curl_setopt($ch, CURLOPT_TIMEOUT, 60);
            curl_setopt($ch, CURLOPT_FAILONERROR, true);
            curl_exec($ch);
            $http_code = curl_getinfo($ch, CURLINFO_HTTP_CODE);
            curl_close($ch);
            fclose($fp);
            if ($http_code == 200) {
                rename($temp_file, $db_path);
            } elseif (file_exists($temp_file)) {
                unlink($temp_file);
            }
        }
        if (file_exists($lock_file)) {
            unlink($lock_file);
        }
    }
}

function getCountryFromIp($ip, $db_path) {
    if (!file_exists($db_path)) {
        return null;
    }
    if (!class_exists('MaxMind\Db\Reader')) {
        return null;
    }
    try {
        $reader = new Reader($db_path);
        $record = $reader->get($ip);
        $reader->close();
        if ($record) {
            return [
                'code' => $record['country_code'] ?? '',
                'name' => $record['country_name'] ?? ''
            ];
        }
    } catch (\Throwable $e) {}
    return null;
}
