<?php

    if ( ! defined( 'ABSPATH' ) ) exit;

    // function to check for field values
    include 'acfcs-field-settings.php';

    // Create an array with available countries from db.
    function acfcs_get_countries( $show_first = true, $field = false, $force = false ) {
        global $wpdb;
        $cache_key            = 'all_countries';
        $cache_group          = 'cities_data';
        $countries            = [];
        $results              = wp_cache_get( $cache_key, $cache_group );
        $select_country_label = apply_filters( 'acfcs_select_country_label', esc_html__( 'Select a country', 'acf-city-selector' ) );
        $show_labels          = ( isset( $field[ 'show_labels' ] ) ) ? $field[ 'show_labels' ] : true;

        if ( $show_first ) {
            $countries[ '' ] = '-';
            if ( ! $show_labels ) {
                $countries[ '' ] = $select_country_label;
            }
        }

        if ( false === $results ) {
            $table   = $wpdb->prefix . 'cities';
            $query   = $wpdb->prepare( "SELECT * FROM %i GROUP BY country ORDER BY country ASC", $table );
            $method  = 'get_results';
            $results = $wpdb->$method( $query );

            wp_cache_set( $cache_key, $results, $cache_group, 2 * HOUR_IN_SECONDS );
        }

        if ( ! empty( $results ) ) {
            $country_results = [];
            foreach ( $results as $data ) {
                if ( isset( $data->country_code ) && isset( $data->country ) ) {
                    $country_results[ esc_attr( $data->country_code ) ] = esc_attr( acfcs_get_country_name( strtolower( $data->country_code ) ) );
                }
            }
            $countries = array_merge( $countries, $country_results );
        }

        return $countries;
    }

    // Create an array with states based on a country code
    function acfcs_get_states( $country_code = false, $show_first = true, $field = false ) {
        $select_province_state_label = apply_filters( 'acfcs_select_province_state_label', esc_attr__( 'Select a province/state', 'acf-city-selector' ) );
        $show_labels                 = ( isset( $field[ 'show_labels' ] ) ) ? $field[ 'show_labels' ] : true;
        $states                      = [];

        if ( $show_first ) {
            if ( $show_labels ) {
                $states[ '' ] = '-';
            } else {
                $states[ '' ] = $select_province_state_label;
            }
        }

        if ( false != $country_code ) {
            global $wpdb;
            $table         = $wpdb->prefix . 'cities';
            $cache_group   = 'cities_data';
            $cache_key     = 'states_' . md5( strtoupper( $country_code ) );
            $method        = 'get_results';
            $results       = wp_cache_get( $cache_key, $cache_group );
            $state_results = [];

            if ( false === $results ) {
                if ( 'FR' == $country_code ) {
                    $query = $wpdb->prepare( "SELECT * FROM %i WHERE country_code = %s GROUP BY state_code ORDER BY LENGTH(state_name), state_name", $table, strtoupper( $country_code ) );
                } else {
                    $query = $wpdb->prepare( "SELECT * FROM %i WHERE country_code = %s GROUP BY state_code ORDER BY state_name ASC", $table, strtoupper( $country_code ) );
                }
                $results = $wpdb->$method( $query );
                wp_cache_set( $cache_key, $results, $cache_group, 2 * HOUR_IN_SECONDS );
            }

            if ( is_array( $results ) ) {
                foreach ( $results as $data ) {
                    $state_results[ esc_attr( strtoupper( $country_code ) . '-' . $data->state_code ) ] = esc_attr( $data->state_name );
                }

                if ( ! empty( $state_results ) ) {
                    $states = array_merge( $states, $state_results );
                }
            }
        }

        return $states;
    }

    // Create an array with cities for a certain country/state
    function acfcs_get_cities( $country_code = false, $state_code = false, $field = false ) {
        $cache_group       = 'cities_data';
        $cities            = [];
        $city_results      = [];
        $method            = 'get_results';
        $select_city_label = apply_filters( 'acfcs_select_city_label', esc_attr__( 'Select a city', 'acf-city-selector' ) );
        $show_labels       = ( isset( $field[ 'show_labels' ] ) ) ? $field[ 'show_labels' ] : true;

        if ( $show_labels ) {
            $cities[ '' ] = '-';
        } else {
            $cities[ '' ] = $select_city_label;
        }

        if ( $country_code ) {
            global $wpdb;
            $table = $wpdb->prefix . 'cities';

            if ( $state_code ) {
                if ( 3 < strlen( $state_code ) ) {
                    $state_code = substr( $state_code, 3 );
                }
                $cache_key = 'cities_' . md5( strtoupper( $country_code ) . '_' . strtoupper( $state_code ) );
                $results   = wp_cache_get( $cache_key, $cache_group );

                if ( false === $results ) {
                    $query   = $wpdb->prepare( "SELECT * FROM %i WHERE country_code = %s AND state_code = %s ORDER BY state_name, city_name ASC", $table, $country_code, $state_code );
                    $results = $wpdb->$method( $query );
                    wp_cache_set( $cache_key, $results, $cache_group, 2 * HOUR_IN_SECONDS );
                }

            } elseif ( $country_code ) {
                $cache_key = 'cities_' . md5( strtoupper( $country_code ) );
                $results   = wp_cache_get( $cache_key, $cache_group );

                if ( false === $results ) {
                    $query   = $wpdb->prepare( "SELECT * FROM %i WHERE country_code = %s", $table, $country_code );
                    $results = $wpdb->$method( $query );
                    wp_cache_set( $cache_key, $results, $cache_group, 2 * HOUR_IN_SECONDS );
                }
            }

            if ( ! empty( $results ) ) {
                foreach ( $results as $data ) {
                    $city_results[] = [
                        'city_name' => $data->city_name,
                    ];
                }
            }

            if ( ! empty( $city_results ) ) {
                uasort( $city_results, 'acfcs_sort_array_with_quotes' );
            }
            foreach ( $city_results as $data ) {
                $city_array[ esc_attr( $data[ 'city_name' ] ) ] = esc_attr( $data[ 'city_name' ] );
            }
            if ( isset( $city_array ) ) {
                $cities = array_merge( $cities, $city_array );
            }
        }

        return $cities;
    }

    // Get country name by country code
    function acfcs_get_country_name( $country_code = false ) {
        if ( false != $country_code ) {
            $country_name = acfcs_country_i18n( strtolower( $country_code ) );

            if ( is_string( $country_name ) && 2 < strlen( $country_name ) ) {
                return $country_name;
            }

            $cache_group = 'cities_data';
            $cache_key   = 'cities_' . md5( strtoupper( $country_code ) );
            $method      = 'get_row';
            $results     = wp_cache_get( $cache_key, $cache_group );

            if ( false === $results ) {
                global $wpdb;
                $table   = $wpdb->prefix . 'cities';
                $query   = $wpdb->prepare( "SELECT * FROM %i WHERE country_code = %s", $table, $country_code );
                $country = $wpdb->$method( $query );
                wp_cache_set( $cache_key, $results, $cache_group, 2 * HOUR_IN_SECONDS );
            }

            if ( isset( $country->country ) ) {
                return $country->country;
            }
        }

        return $country_code;
    }

    // Checks if there are any cities in the database (for page availability)
    function acfcs_has_cities( $country_code = false ) {
        global $wpdb;
        $cache_group = 'cities_data';
        $method      = 'get_results';
        $results     = [];
        $table       = $wpdb->prefix . 'cities';

        if ( $country_code ) {
            $cache_key = 'has_cities_' . md5( strtoupper( $country_code ) );
            $results   = wp_cache_get( $cache_key, $cache_group );

            if ( false === $results ) {
                $query     = $wpdb->prepare( "SELECT * FROM %i WHERE country_code = %s LIMIT 1", $table, $country_code );
                $results   = $wpdb->$method( $query );
                wp_cache_set( $cache_key, $results, $cache_group, 2 * HOUR_IN_SECONDS );
            }

        } else {
            $cache_key = 'has_cities';
            $results   = wp_cache_get( $cache_key, $cache_group );

            if ( false === $results ) {
                $query = $wpdb->prepare( "SELECT * FROM %i LIMIT 1", $table );
                $results = $wpdb->$method( $query );
                wp_cache_set( $cache_key, $results, $cache_group, 2 * HOUR_IN_SECONDS );
            }
        }

        if ( count( $results ) > 0 ) {
            return true;
        }

        return false;
    }

    // Checks if files are uploaded
    function acfcs_check_if_files() {
        $actual_files = [];
        $target_dir   = acfcs_upload_folder();

        if ( is_dir( $target_dir ) ) {
            $file_index = scandir( $target_dir );
            $default_excluded_files = [
                '.',
                '..',
                '.DS_Store',
                'debug.json',
            ];
            $excluded_files = apply_filters( 'acfcs_exclude_files', $default_excluded_files );

            if ( is_array( $file_index ) ) {
                foreach ( $file_index as $file ) {
                    if ( ! in_array( $file, $excluded_files ) ) {
                        $actual_files[] = $file;
                    }
                }
                if ( ! empty( $actual_files ) ) {
                    return $actual_files;
                }
            }
        }

        return $actual_files;
    }

    // Convert data from an uploaded CSV file to an array
    function acfcs_csv_to_array( $file_name, $upload_folder = '', $delimiter = ';', $verify = false, $max_lines = false ) {
        global $wp_filesystem;

        if ( empty( $wp_filesystem ) ) {
            require_once ABSPATH . 'wp-admin/includes/file.php';
            WP_Filesystem();
        }

        $upload_folder = ( ! empty( $upload_folder ) ) ? $upload_folder : acfcs_upload_folder( '/' );
        $file_path     = $upload_folder . $file_name;

        if ( $wp_filesystem->exists( $file_path ) && false !== ( $file_content = $wp_filesystem->get_contents( $file_path ) ) ) {
            $column_benchmark = 5;
            $empty_array      = false;
            $errors           = ACF_City_Selector::acfcs_errors();
            $line_number      = 0;
            $max_line_length  = 500;
            $new_array        = [];
            $csv_lines        = explode( "\n", $file_content );

            foreach ( $csv_lines as $line ) {
                // Skip completely empty lines to prevent false errors
                if ( '' === trim( $line ) ) {
                    continue;
                }

                $line_number++;
                $csv_array[ 'delimiter' ] = $delimiter;

                // Enforce the line length filter if necessary
                if ( strlen( $line ) > $max_line_length ) {
                    $line = substr( $line, 0, $max_line_length );
                }

                // Parse the line string as CSV
                $csv_line = str_getcsv( $line, "{$delimiter}" );

                if ( false === $csv_line || null === $csv_line[0] ) {
                    continue;
                }

                // If column count doesn't match benchmark
                if ( count( $csv_line ) != $column_benchmark ) {
                    // If column count < benchmark
                    if ( count( $csv_line ) < $column_benchmark ) {
                        $error_message = esc_html__( 'Since your file is not accurate anymore, the file is deleted.', 'acf-city-selector' );
                        /* translators: 1 line number 2 error message */
                        $errors->add( 'error_no_correct_columns_' . $line_number, sprintf( esc_html__( 'There are too few columns on line %1$d. %2$s', 'acf-city-selector' ), $line_number, $error_message ) );

                    } elseif ( count( $csv_line ) > $column_benchmark ) {
                        // If column count > benchmark
                        $error_message = esc_html__( 'Since your file is not accurate anymore, the file is deleted.', 'acf-city-selector' );
                        if ( false === $verify ) {
                            $error_message = 'Lines 0-' . ( $line_number - 1 ) . ' are correctly imported but since your file is not accurate anymore, the file is deleted';
                        }
                        /* translators: 1 line number 2 error message */
                        $errors->add( 'error_no_correct_columns_' . $line_number, sprintf( esc_html__( 'There are too many columns on line %1$d. %2$s', 'acf-city-selector' ), $line_number, $error_message ) );
                    }
                }

                if ( $errors->has_errors() ) {
                    $empty_array = true;
                    $new_array   = [];
                } else {
                    // Create a new array for each row
                    $new_line = [];
                    foreach ( $csv_line as $item ) {
                        $new_line[] = $item;
                    }
                    if ( ! empty( $new_line ) ) {
                        $new_array[] = $new_line;
                    }

                    if ( false != $max_lines ) {
                        if ( $line_number == $max_lines ) {
                            break;
                        }
                    }
                }
            }

            if ( $errors->has_errors() ) {
                $delete_check_path = acfcs_upload_folder( '/' ) . $file_name;
                if ( $wp_filesystem->exists( $delete_check_path ) ) {
                    do_action( 'acfcs_delete_file', $file_name );
                    $csv_array[ 'error' ] = 'file_deleted';
                }
            }

            /*
             * Don't add data if there are any errors. This to prevent rows which had no error from outputting
             * on the preview page.
             */
            if ( ! empty( $new_array ) && false === $empty_array ) {
                $csv_array[ 'data' ] = array_values( $new_array );
            }
        }

        return $csv_array;
    }

    // Verify raw csv import
    function acfcs_verify_csv_data( $csv_data = false, $delimiter = ";" ) {
        if ( false != $csv_data ) {
            $column_benchmark = 5;
            $line_number      = 0;
            $lines            = explode( "\r\n", $csv_data );

            foreach ( $lines as $line ) {
                $line_number++;

                if ( ! is_array( $csv_data ) ) {
                    $line_array = explode( $delimiter, $line );
                }

                if ( count( $line_array ) != $column_benchmark ) {
                    // length of a line is not correct
                    if ( count( $line_array ) < $column_benchmark ) {
                        /* translators: %d line number */
                        ACF_City_Selector::acfcs_errors()->add( 'error_no_correct_columns', sprintf( esc_html__( 'There are too few columns on line %d.', 'acf-city-selector' ), $line_number ) );

                        return false;

                    } elseif ( count( $line_array ) > $column_benchmark ) {
                        /* translators: %d line number */
                        ACF_City_Selector::acfcs_errors()->add( 'error_no_correct_columns', sprintf( esc_html__( 'There are too many columns on line %d.', 'acf-city-selector' ), $line_number ) );

                        return false;
                    }
                }

                $column_counter = 0;
                foreach( $line_array as $element ) {
                    $column_counter++;
                    if ( $column_counter == 4 ) {
                        if ( 2 != strlen( $element ) ) {
                            /* translators: %d line number */
                            ACF_City_Selector::acfcs_errors()->add( 'error_wrong_country_length', sprintf( esc_html__( 'The length of the country abbreviation on line %d is incorrect.', 'acf-city-selector' ), $line_number ) );

                            return false;
                        }
                    }
                }
                $validated_data[] = $line_array;
            }

            return $validated_data;
        }

        return false;
    }

    // Get packages through WP_Http
    function acfcs_get_packages( $endpoint = 'single' ) {
        $url     = sprintf( '%s/wp-json/countries/v1/%s', ACFCS_WEBSITE_URL, $endpoint );
        $request = new WP_Http;
        $result  = $request->request( $url, [ 'method' => 'GET' ] );

        if ( 200 == $result[ 'response' ][ 'code' ] ) {
            $response = json_decode( $result[ 'body' ] );

            return $response;
        }

        return [];
    }

    // Get country info for debug
    function acfcs_get_countries_info() {
        global $wpdb;
        $acfcs_info  = [];
        $cache_group = 'cities_data';
        $cache_key   = 'country_codes';
        $method      = 'get_results';
        $results     = wp_cache_get( $cache_key, $cache_group );
        $table       = $wpdb->prefix . 'cities';

        if ( false === $results ) {
            $query   = $wpdb->prepare( "SELECT country_code FROM %i GROUP BY country_code ORDER BY country_code ASC", $table );
            $results = $wpdb->$method( $query );
            wp_cache_set( $cache_key, $results, $cache_group, 2 * HOUR_IN_SECONDS );
        }

        if ( is_array( $results ) ) {
            foreach ( $results as $data ) {
                if ( isset( $data->country_code ) ) {
                    $country_code = $data->country_code;
                    $cache_key    = 'country_info_' . md5( strtoupper( $country_code ) );
                    $sub_results  = wp_cache_get( $cache_key, $cache_group );

                    if ( false === $sub_results ) {
                        $query       = $wpdb->prepare( "SELECT * FROM %i WHERE country_code = %s ORDER BY country_code ASC", $table, $country_code );
                        $sub_results = $wpdb->$method( $query );
                        wp_cache_set( $cache_key, $sub_results, $cache_group, 2 * HOUR_IN_SECONDS );

                        $acfcs_info[ $country_code ] = [
                            'country_code' => $country_code,
                            'count'        => count( $sub_results ),
                            'name'         => acfcs_get_country_name( $country_code ),
                        ];
                    }
                }
            }
        }

        return $acfcs_info;
    }

    // Search an array which contains quotes like "'t Veld"
    function acfcs_sort_array_with_quotes( $a, $b ) {
        return strnatcasecmp( acfcs_custom_sort_with_quotes( $a[ 'city_name' ] ), acfcs_custom_sort_with_quotes( $b[ 'city_name' ] ) );
    }

    // Sort with quotes
    function acfcs_custom_sort_with_quotes( $city ) {
        // strip quote marks
        if ( strpos( $city, "'s" ) !== false ) {
            $city = trim( $city, '\'s ' );
            $city = preg_replace( '/^\s*\'s \s+/i', '', $city );
        } elseif ( strpos( $city, "'t" ) !== false ) {
            $city = trim( $city, '\'t ' );
            $city = preg_replace( '/^\s*\'t \s+/i', '', $city );
        }

        return $city;
    }

    // Render select in ACF field
    function acfcs_render_dropdown( $type, $field, $stored_value, $prefill_values ) {
        $acfcs_dropdown       = 'acfcs__dropdown';
        $city_label           = apply_filters( 'acfcs_select_city_label', esc_attr__( 'Select a city', 'acf-city-selector' ) );
        $countries            = acfcs_get_countries( true, $field );
        $country_label        = apply_filters( 'acfcs_select_country_label', esc_attr__( 'Select a country', 'acf-city-selector' ) );
        $default_country      = ( isset( $field[ 'default_country' ] ) && ! empty( $field[ 'default_country' ] ) ) ? $field[ 'default_country' ] : false;
        $default_value        = false;
        $field_id             = $field[ 'id' ];
        $field_name           = $field[ 'name' ];
        $prefill_cities       = $prefill_values[ 'prefill_cities' ];
        $prefill_states       = $prefill_values[ 'prefill_states' ];
        $province_state_label = apply_filters( 'acfcs_select_province_state_label', esc_attr__( 'Select a province/state', 'acf-city-selector' ) );
        $selected_selected    = ' selected="selected"';
        $show_labels          = ( isset( $field[ 'show_labels' ] ) ) ? $field[ 'show_labels' ] : true;
        $use_select2          = ( isset( $field[ 'use_select2' ] ) ) ? $field[ 'use_select2' ] : false;
        $dropdown_class       = ( true == $use_select2 ) ? 'select2 ' . $acfcs_dropdown : $acfcs_dropdown;
        $data_label_value     = ( true == $show_labels ) ? '1' : '0';
        $which_fields         = ( isset( $field[ 'which_fields' ] ) ) ? $field[ 'which_fields' ] : 'all';

        switch( $type ) {
            case 'country':
                $default_value  = $default_country;
                $field_label    = $country_label;
                $field_suffix   = 'countryCode';
                $modifier       = 'countries';
                $selected_value = esc_attr( $stored_value );
                $values         = $countries;
                break;
            case 'state':
                $field_label    = $province_state_label;
                $field_suffix   = 'stateCode';
                $modifier       = 'states';
                $selected_value = esc_attr( $stored_value );
                $values         = $prefill_states;
                break;
            case 'city':
                $field_label    = $city_label;
                $field_suffix   = 'cityName';
                $modifier       = 'cities';
                $selected_value = esc_attr( $stored_value );
                $values         = $prefill_cities;
                break;
        }
        $dropdown_class = $dropdown_class . ' ' . $acfcs_dropdown . '--' . $modifier;

        ?>
        <div class="acfcs__dropdown-box acfcs__dropdown-box--<?php echo esc_attr( $modifier ); ?>">
            <?php if ( $show_labels ) { ?>
                <div class="acf-input-header">
                    <?php echo esc_html( $field_label ); ?>
                </div>
            <?php } ?>
            <label for="<?php echo esc_attr( $field_id ) . esc_attr( $field_suffix ); ?>" class="screen-reader-text">
                <?php echo esc_html( $field_label ); ?>
            </label>
            <select name="<?php echo esc_attr( $field_name ); ?>[<?php echo esc_attr( $field_suffix ); ?>]" id="<?php echo esc_attr( $field_id ) . esc_attr( $field_suffix ); ?>" class="<?php echo esc_attr( $dropdown_class ); ?>" data-show-labels="<?php echo esc_attr( $data_label_value ); ?>" data-which-fields="<?php echo esc_attr( $which_fields ); ?>">
                <?php
                    if ( ! empty( $values ) ) {
                        foreach ( $values as $key => $label ) {
                            $selected = false;
                            if ( ! empty( $selected_value ) ) {
                                $selected = ( $selected_value == $key ) ? $selected_selected : false;
                            } elseif ( ! empty( $default_value ) ) {
                                // only when a default country is set
                                $selected = ( $default_value == $key ) ? $selected_selected : false;
                            }
                            echo '<option value="' . esc_attr( $key ) . '"' . esc_attr( $selected ) . '>' . esc_html( $label ) . '</option>';
                        }
                    }
                ?>
            </select>
        </div>
        <?php
    }

    function acfcs_render_hidden_field( $name, $value ) {
        if ( $name && $value ) {
            echo sprintf( '<input type="hidden" name="%s" value="%s" />', esc_attr( $name ), esc_attr( $value ) );
        }
    }

    // Verify CSV data
    function acfcs_verify_data( $file_name, $delimiter = ';', $verify = true ) {
        $csv_array = acfcs_csv_to_array( $file_name, '', $delimiter, $verify );
        if ( isset( $csv_array[ 'data' ] ) ) {
            /* translators: file name */
            ACF_City_Selector::acfcs_errors()->add( 'success_no_errors_in_csv', sprintf( esc_html__( 'Congratulations, there appear to be no errors in CSV file: "%s".', 'acf-city-selector' ), $file_name ) );
            do_action( 'acfcs_after_success_verify' );
        }
    }

    // Import CSV data
    function acfcs_import_data( $file_name, $upload_folder = '', $delimiter = ';', $verify = false, $max_lines = false ) {
        if ( $file_name ) {
            $method = 'insert';

            if ( ! is_array( $file_name ) ) {
                // csv data
                if ( strpos( $file_name, '.csv', -4 ) !== false ) {
                    $csv_array = acfcs_csv_to_array( $file_name, $upload_folder, $delimiter, $verify, $max_lines );

                    if ( ! is_wp_error( $csv_array ) ) {
                        if ( isset( $csv_array[ 'data' ] ) && ! empty( $csv_array[ 'data' ] ) ) {
                            $line_number = 0;
                            foreach ( $csv_array[ 'data' ] as $line ) {
                                $line_number++;

                                $city_row = [
                                    'city_name'    => $line[ 0 ],
                                    'state_code'   => $line[ 1 ],
                                    'state_name'   => $line[ 2 ],
                                    'country_code' => $line[ 3 ],
                                    'country'      => $line[ 4 ],
                                ];

                                global $wpdb;
                                $wpdb->$method( $wpdb->prefix . 'cities', $city_row );
                            }
                            if ( in_array( $file_name, [ 'be.csv', 'nl.csv' ] ) ) {
                                $country_code = substr( $file_name, 0, 2 );
                                /* translators: 1 amount cities, 2 file name */
                                ACF_City_Selector::acfcs_errors()->add( 'success_lines_imported_' . $country_code, sprintf( esc_html__( 'You have successfully imported %1$d cities from "%2$s".', 'acf-city-selector' ), $line_number, $file_name ) );
                            } else {
                                /* translators: 1 amount cities, 2 file name */
                                ACF_City_Selector::acfcs_errors()->add( 'success_lines_imported', sprintf( esc_html__( 'You have successfully imported %1$d cities from "%2$s".', 'acf-city-selector' ), $line_number, $file_name ) );
                            }

                            do_action( 'acfcs_after_success_import' );

                        } else {
                            /* translators: %s file name */
                            ACF_City_Selector::acfcs_errors()->add( 'error_file_name', sprintf( esc_html__( 'There\'s an error in "%s".', 'acf-city-selector' ), $file_name ) );
                        }
                    }
                }
            } else {
                // raw data
                global $wpdb;
                $line_number   = 0;
                $verified_data = $file_name;

                foreach ( $verified_data as $line ) {
                    $line_number++;

                    $city_row = [
                        'city_name'    => $line[ 0 ],
                        'state_code'   => $line[ 1 ],
                        'state_name'   => $line[ 2 ],
                        'country_code' => $line[ 3 ],
                        'country'      => $line[ 4 ],
                    ];

                    $wpdb->$method( $wpdb->prefix . 'cities', $city_row );
                }
                /* translators: amount cities */
                ACF_City_Selector::acfcs_errors()->add( 'success_cities_imported', sprintf( esc_html( _n( 'You imported %d city.', 'You imported %d cities.', $line_number, 'acf-city-selector'  ) ), $line_number ) );

                do_action( 'acfcs_after_success_import_raw' );
            }

        } else {
            ACF_City_Selector::acfcs_errors()->add( 'error_no_file_selected', esc_html__( "You didn't select a file.", 'acf-city-selector' ) );
        }
    }

    // Delete one or more countries
    function acfcs_delete_country( $countries ) {
        $country_names_and       = false;
        $sanitized_country_codes = [];
        $method                  = 'query';

        foreach( $countries as $country_code ) {
            $sanitized_country_code    = strtoupper( $country_code );
            $sanitized_country_codes[] = $sanitized_country_code;
            $country_names[]           = acfcs_get_country_name( $sanitized_country_code );
        }

        if ( ! empty( $country_names ) ) {
            $country_names_quotes = "'" . implode( "', '", $country_names ) . "'";
            if ( 1 < count( $country_names ) ) {
                $country_names_and = substr_replace( $country_names_quotes, ' and', strrpos( $country_names_quotes, ',' ), 1 );
            } else {
                $country_names_and = $country_names_quotes;
            }
        }
        if ( ! empty( $sanitized_country_codes ) ) {
            if ( 1 === count( $sanitized_country_codes ) ) {
                $country_string = strtoupper( $sanitized_country_codes[ 0 ] );
            } else {
                $country_string = strtoupper( "'" . implode( "', '", $sanitized_country_codes ) . "'" );
            }

            global $wpdb;
            $table  = $wpdb->prefix . 'cities';
            $query  = $wpdb->prepare( "DELETE FROM %i WHERE country_code IN (%s)", $table, $country_string );
            $result = $wpdb->$method( $query );

            if ( isset( $result ) && 0 < $result ) {
                /* translators: %s country name */
                ACF_City_Selector::acfcs_errors()->add( 'success_country_remove', sprintf( esc_html__( 'You have successfully removed all entries for %s.', 'acf-city-selector' ), $country_names_and ) );
            }
        }
    }

    // Get upload folder for plugin, can be overriden with filter
    function acfcs_upload_folder( $suffix = false ) {
        $upload_folder = apply_filters( 'acfcs_upload_folder', wp_upload_dir()[ 'basedir' ] . '/acfcs' . $suffix );

        return $upload_folder;
    }

    // Render preview results
    function acfcs_render_preview_results( $csv_data = [] ) {
        if ( ! empty( $csv_data ) ) {
            echo '<table class="acfcs__table acfcs__table--preview-result scrollable">';
            $table_columns = [
                esc_html__( 'City', 'acf-city-selector' ),
                esc_html__( 'State code', 'acf-city-selector' ),
                esc_html__( 'State', 'acf-city-selector' ),
                esc_html__( 'Country code', 'acf-city-selector' ),
                esc_html__( 'Country', 'acf-city-selector' ),
            ];

            echo '<thead>';
            foreach( $table_columns as $column ) {
                echo sprintf( '<th>%s</th>', esc_attr( $column ) );
            }
            echo '</thead>';

            echo '<tbody>';
            foreach ( $csv_data as $line ) {
                echo '<tr>';
                foreach ( $line as $column ) {
                    echo sprintf( '<td>%s</td>', esc_attr( stripslashes( htmlspecialchars( $column ) ) ) );
                }
                echo '</tr>';
            }
            echo '</tbody>';
            echo '</table>';
        }
    }

    // Get optgroups for states
    function acfcs_get_states_optgroup() {
        $results = acfcs_get_countries( false );
        $states  = [];
        $cache_group = 'cities_data';
        $method      = 'get_results';

        // if there is at least 1 country
        if ( ! empty( $results ) ) {
            foreach ( $results as $country_code => $label ) {
                $countries[] = [
                    'code' => $country_code,
                    'name' => $label,
                ];
            }

            // get states for these countries
            if ( ! empty( $countries ) ) {
                global $wpdb;
                $table = $wpdb->prefix . 'cities';

                foreach( $countries as $country ) {
                    $states[] = [
                        'state' => 'open_optgroup',
                        'name'  => esc_attr( acfcs_get_country_name( $country[ 'code' ] ) ),
                    ];

                    $cache_key = 'optgroup_states_' . md5( $country[ 'code' ] );
                    $results   = wp_cache_get( $cache_key, $cache_group );

                    if ( false === $results ) {
                        if ( 'FR' == $country[ 'code' ] ) {
                            $query = $wpdb->prepare( "SELECT * FROM %i WHERE country_code = %s GROUP BY state_code ORDER BY LENGTH(state_name), state_name", $table, $country[ 'code' ] );
                        } else {
                            $query = $wpdb->prepare( "SELECT * FROM %i WHERE country_code = %s GROUP BY state_code ORDER BY state_name ASC", $table, $country[ 'code' ] );
                        }
                        $results = $wpdb->$method( $query );
                        wp_cache_set( $cache_key, $results, $cache_group, 2 * HOUR_IN_SECONDS );
                    }

                    if ( count( $results ) > 0 ) {
                        foreach ( $results as $data ) {
                            $states[] = [
                                'state' => strtolower( $data->country_code ) . '-' . strtolower( $data->state_code ),
                                'name'  => esc_attr( $data->state_name ),
                            ];
                        }
                    }

                    $states[] = [
                        'state' => 'close_optgroup',
                        'name'  => '',
                    ];
                }
            }
        }

        return $states;
    }

    // Get search results (admin)
    function acfcs_get_searched_cities() {
        $cities = [];

        if ( ! isset( $_POST[ 'acfcs_search_form_nonce' ] ) ) {
            return $cities;
        }

        $nonce = sanitize_text_field( wp_unslash( $_POST[ 'acfcs_search_form_nonce' ] ) );
        if ( ! wp_verify_nonce( $nonce, 'acfcs-search-form-nonce' ) ) {
            if ( method_exists( 'ACF_City_Selector', 'acfcs_errors' ) ) {
                ACF_City_Selector::acfcs_errors()->add( 'error_no_nonce_match', esc_html__( 'Something went wrong, please try again.', 'acf-city-selector' ) );
            }

            return $cities;
        }

        global $wpdb;
        $table          = $wpdb->prefix . 'cities';
        $method         = 'get_results';
        $state_input    = ! empty( $_POST[ 'acfcs_state' ] ) ? sanitize_text_field( wp_unslash( $_POST[ 'acfcs_state' ] ) ) : false;
        $country_input  = ! empty( $_POST[ 'acfcs_country' ] ) ? sanitize_text_field( wp_unslash( $_POST[ 'acfcs_country' ] ) ) : false;
        $searched_by    = ! empty( $_POST[ 'acfcs_orderby' ] ) ? sanitize_text_field( wp_unslash( $_POST[ 'acfcs_orderby' ] ) ) : false;
        $searched_term  = ! empty( $_POST[ 'acfcs_search' ] ) ? sanitize_text_field( wp_unslash( $_POST[ 'acfcs_search' ] ) ) : false;
        $selected_limit = ! empty( $_POST[ 'acfcs_limit' ] ) ? absint( $_POST[ 'acfcs_limit' ] ) : 100;
        $query_args     = [];
        $where_clauses  = [];

        if ( $state_input ) {
            $state_code      = strtoupper( substr( $state_input, 3, 3 ) );
            $country_code    = strtoupper( substr( $state_input, 0, 2 ) );
            $where_clauses[] = "state_code = %s AND country_code = %s";
            $query_args[]    = $state_code;
            $query_args[]    = $country_code;
        } elseif ( $country_input ) {
            $where_clauses[] = "country_code = %s";
            $query_args[]    = $country_input;
        }

        if ( $searched_term ) {
            $where_clauses[] = "city_name LIKE %s";
            $query_args[]    = '%' . $wpdb->esc_like( $searched_term ) . '%';
        }

        $where_sql = ! empty( $where_clauses ) ? 'WHERE ' . implode( ' AND ', $where_clauses ) : '';

        if ( 'state' === $searched_by ) {
            $order_sql = 'ORDER BY state_name ASC, city_name ASC';
        } else {
            $order_sql = 'ORDER BY city_name ASC, state_name ASC';
        }

        $limit_sql    = ' LIMIT %d';
        $query_args[] = $selected_limit;

        // Use %i for table name identifier, rest are injected via $query_args
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $query  = $wpdb->prepare( "SELECT * FROM %i $where_sql $order_sql $limit_sql", array_merge( [ $table ], $query_args ) );
        $cities = $wpdb->$method( $query );

        return $cities;
    }

    // Get all translation strings for js/input fields
    function acfcs_get_js_translations() {
        $translations = [
            'no_countries'         => esc_attr__( 'No countries', 'acf-city-selector' ),
            'select_city'          => esc_attr( apply_filters( 'acfcs_select_city_label', __( 'Select a city', 'acf-city-selector' ) ) ),
            'select_country'       => esc_attr( apply_filters( 'acfcs_select_country_label', __( 'Select a country', 'acf-city-selector' ) ) ),
            'select_country_first' => esc_attr( apply_filters( 'acfcs_select_country_first', __( 'No results (yet), first select a country', 'acf-city-selector' ) ) ),
            'select_state'         => esc_attr( apply_filters( 'acfcs_select_province_state_label', __( 'Select a province/state', 'acf-city-selector' ) ) ),
            'select_state_first'   => esc_attr( apply_filters( 'acfcs_select_state_first', __( 'No results (yet), first select a state', 'acf-city-selector' ) ) ),
        ];

        return $translations;
    }