<?php

if ( ! defined( 'ABSPATH' ) ) {
	exit; // Exit if accessed directly....
}
require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );

global $nele_db_version, $nele_countries_table, $nele_places_table, $nele_offices_table, $nele_streets_table, $nele_log_entries_table;
$nele_countries_table = 'nele_countries';
$nele_places_table = 'nele_places';
$nele_offices_table = 'nele_offices';
$nele_streets_table = 'nele_streets';
$nele_log_entries_table = 'nele_log_entries';
$nele_db_version = '1.0.2';

function nele_clear_db_cache() {
	try {
		delete_transient("nele_countries_cache");
		delete_transient("nele_db_amount_cache");
		delete_transient("nele_couriers_cache");
	} catch (Exception $e) {
		write_log("Deactivate: Error deleting DB cache transients");
	}
}

function nele_db_create_tables() {
	global $wpdb, $nele_db_version, $nele_countries_table, $nele_places_table, $nele_offices_table, $nele_streets_table, $nele_log_entries_table;
	$charset_collate = $wpdb->get_charset_collate();

	$sql = "CREATE TABLE IF NOT EXISTS ".nele_db_add_table_prefix($nele_countries_table)." (
      id int(15) NOT NULL,
      name text(255) NOT NULL,
      code text(3) NOT NULL,
      currency text(3) NOT NULL,
      is_prod BOOLEAN default FALSE,
      INDEX idx_id (id),
      INDEX idx_name (name(255)),
      INDEX idx_code (code(3)),
      INDEX idx_currency (currency(3))
    ) $charset_collate;";
	dbDelta( $sql );

	$sql = "CREATE TABLE IF NOT EXISTS ".nele_db_add_table_prefix($nele_places_table)." (
      id int(15) NOT NULL,
      country_id int(15) NOT NULL,
      name text(255) NOT NULL,
      name_en text(255) NOT NULL,
      region text(255) NOT NULL,
      municipality text(255) NOT NULL,
      post_code text(10) NOT NULL,
      couriers text(500) NOT NULL,
      is_prod BOOLEAN default FALSE,
      INDEX idx_id (id),
      INDEX idx_country_id (country_id),
      INDEX idx_name (name(255)),
      INDEX idx_name_en (name_en(255)),
      INDEX idx_region (region(255)),
      INDEX idx_municipality (municipality(255)),
      INDEX idx_post_code (post_code(10)),
      INDEX idx_couriers (couriers(500))
    ) $charset_collate;";
	dbDelta( $sql );

	$sql = "CREATE TABLE IF NOT EXISTS ".nele_db_add_table_prefix($nele_offices_table)." (
      id int(15) NOT NULL,
      country_id int(15) NOT NULL,
      place_id int(15) NOT NULL,
      office_id int(15) NOT NULL,
      name text(255) NOT NULL,
      address text(500) NOT NULL,
      post_code text(10) NOT NULL,
      subcontractor text(255) NOT NULL,
      is_machine BOOLEAN default FALSE,
      is_prod BOOLEAN default FALSE,
      INDEX idx_id (id),
      INDEX idx_country_id (country_id),
      INDEX idx_place_id (place_id),
      INDEX idx_office_id (office_id),
      INDEX idx_name (name(255)),
      INDEX idx_address (address(500)),
      INDEX idx_post_code (post_code(10)),
      INDEX idx_subcontractor (subcontractor(255)),
      INDEX idx_is_machine (is_machine),
      INDEX idx_is_prod (is_prod)
    ) $charset_collate;";
	dbDelta( $sql );

	$sql = "CREATE TABLE IF NOT EXISTS ".nele_db_add_table_prefix($nele_streets_table)." (
      id int(15) NOT NULL,
      country_id int(15) NOT NULL,
      place_id int(15) NOT NULL,
      name text(255) NOT NULL,
      post_code text(10) NOT NULL,
      is_prod BOOLEAN default FALSE,
      INDEX idx_id (id),
      INDEX idx_country_id (country_id),
      INDEX idx_place_id (place_id),
      INDEX idx_name (name(255)),
      INDEX idx_post_code (post_code(10)),
      INDEX idx_is_prod (is_prod)
    ) $charset_collate;";
	dbDelta( $sql );

//	$sql = "CREATE TABLE IF NOT EXISTS ".nele_db_add_table_prefix($nele_log_entries_table)." (
//      id int(15) NOT NULL AUTO_INCREMENT,
//      text varchar(2000) NOT NULL,
//      severity varchar(10) NOT NULL,
//      added_on datetime NOT NULL,
//      is_read BOOLEAN DEFAULT FALSE,
//      PRIMARY KEY (id),
//      INDEX idx_severity (severity(10)),
//      INDEX idx_added_on (added_on),
//      INDEX idx_is_read (is_read)
//    ) $charset_collate;";
//	dbDelta( $sql );

	add_option( 'nele_db_version', $nele_db_version );
}

function nele_db_clear_queries() {
	global $queries, $countries_inserted, $places_inserted, $offices_inserted;
	$queries = array();
	$countries_inserted = array();
	$places_inserted = array();
	$offices_inserted = array();
}

function nele_db_append_query($short_table_name, $query) {
	global $queries;
	$queries[] = str_replace('{table_name}', nele_db_add_table_prefix($short_table_name), $query);
}

function nele_db_execute_queries() {
	global $queries, $wpdb;
    write_log("nele_db_execute_queries started");
	if (count($queries) === 0) {
		write_log("no queries left to be run");
		return;
	}
	$connect = $wpdb->__get('dbh');
	try {
		$chunkSize = 5000; // some ephemeral value - number of queries per chunk
		$queryChunks = array_chunk($queries, $chunkSize);
		mysqli_query($connect, "BEGIN");

		foreach ($queryChunks as $queryChunk) {
			$queriesStr = implode('; ', $queryChunk);
			mysqli_multi_query($connect, $queriesStr);
			while (mysqli_more_results($connect)) {
				mysqli_next_result($connect);
			}
		}
		mysqli_query($connect, "COMMIT");
	} finally {
		if ($wpdb->last_error !== '') {
			$error = $wpdb->last_error;
			write_log("Hit DB error: " . $error);
			nele_add_db_error($error);
		}
		nele_db_clear_queries();
		nele_clear_db_cache();
	}
	write_log("nele_db_execute_queries finished");
}

function nele_db_add_table_prefix($table_name): string {
    assert(!empty($table_name), "Table name couldn't be empty!");
	global $wpdb;
	return $wpdb->prefix . $table_name;
}

function nele_db_escape_vars($value) : string {
    if (empty($value)) {
        return $value;
    }
	return str_replace("'", '', trim($value));
}

function nele_db_all_tables(): array {
	global $nele_countries_table, $nele_places_table, $nele_offices_table, $nele_streets_table;
	return array(nele_db_add_table_prefix($nele_countries_table), nele_db_add_table_prefix($nele_places_table),
		nele_db_add_table_prefix($nele_offices_table), nele_db_add_table_prefix($nele_streets_table));
}

function nele_db_read_table_data($table_name, $order_by = null, $where = array())
{
	global $wpdb;
	$table_name = nele_db_add_table_prefix($table_name);
	$where["IS_PROD"] = 1;
	$where_clause = neleGenerateWhereClause($where);
	$order_clause = '';
	if ($order_by) {
		$order_clause = 'ORDER BY '.$order_by;
	}
	$query = "SELECT * FROM $table_name $where_clause $order_clause";
	return $wpdb->get_results( $query );
}

function nele_db_drop_tables() {
	global $wpdb;
	foreach (nele_db_all_tables() as $table_name) {
		try {
			$wpdb->query("DROP TABLE IF EXISTS " . $table_name);
		} catch (Exception $e) {
			write_log("Deactivate: Error deleting $table_name");
		}
	}
	try {
		delete_option( "nele_db_version" );
		delete_option("nele_setup_completed");
	} catch (Exception $e) {
		write_log("Deactivate: Error deleting DB options");
	}
	try {
		delete_transient("nele_upgrade_running");
	} catch (Exception $e) {
		write_log("Deactivate: Error deleting DB transient");
	}
	nele_clear_db_cache();
}

function nele_db_insert_country($id, $name, $code, $currency) {
	global $nele_countries_table;
	nele_db_append_query($nele_countries_table, "INSERT INTO {table_name} (id, name, code, currency) VALUES ($id,'".nele_db_escape_vars($name)."','".nele_db_escape_vars($code)."','".nele_db_escape_vars($currency)."')");
}

function nele_db_insert_place($id, $country_id, $name, $name_en, $region, $municipality, $post_code, $couriers=array()) {
	global $nele_places_table;
	nele_db_append_query($nele_places_table, "INSERT INTO {table_name} (id, country_id, name, name_en, region, municipality, couriers, post_code) VALUES ($id,'".nele_db_escape_vars($country_id)."','".nele_db_escape_vars($name)."','".nele_db_escape_vars($name_en)."','".nele_db_escape_vars($region)."','".nele_db_escape_vars($municipality)."','".nele_db_escape_vars(implode("|", $couriers))."','".nele_db_escape_vars($post_code)."')");
}

function nele_db_insert_office($id, $country_id, $place_id, $office_id, $name, $address, $post_code, $subcontractor, $is_machine) {
	global $nele_offices_table;
	nele_db_append_query($nele_offices_table, "INSERT INTO {table_name} (id, country_id, place_id, office_id, name, address, post_code, subcontractor, is_machine) VALUES ($id,'".nele_db_escape_vars($country_id)."','".nele_db_escape_vars($place_id)."','".nele_db_escape_vars($office_id)."','".nele_db_escape_vars($name)."','".nele_db_escape_vars($address)."','".nele_db_escape_vars($post_code)."','".nele_db_escape_vars($subcontractor)."','".nele_db_escape_vars($is_machine)."')");
}

function nele_db_insert_street($id, $country_id, $place_id, $name, $post_code) {
	global $nele_streets_table;
	nele_db_append_query($nele_streets_table, "INSERT INTO {table_name} (id, country_id, place_id, name, post_code) VALUES ($id,'".nele_db_escape_vars($country_id)."','".nele_db_escape_vars($place_id)."','".nele_db_escape_vars($name)."','".nele_db_escape_vars($post_code)."')");
}

// $is_prod is the way to avoid cleaning up the tables before we actually get the new values from the API
// this way we do the following transaction-alike logic:
// 1. first insert new data which is not 'visible' (since it has is_prod=0)
// 2. delete existing data with is_prod=1
// 3. mark newly inserted data with is_prod=1
function nele_db_truncate_tables($is_prod=false) {
	global $queries;
	$is_prod_int = (int) $is_prod;
	write_log("neleTruncateTables: is_prod=$is_prod_int");
	foreach (nele_db_all_tables() as $table_name) {
		$queries[] = "DELETE FROM $table_name WHERE IS_PROD = ".$is_prod_int;
	}
}

function nele_db_mark_as_prod() {
	global $queries;
	write_log("neleMarkDataAsProd: start");
	foreach (nele_db_all_tables() as $table_name) {
		$queries[] = "UPDATE $table_name SET IS_PROD = 1";
	}
}

function nele_db_amount($table_name): int {
	global $wpdb;
	$db_lookup = get_transient('nele_db_amount_cache'); // Get cached data
	if ($db_lookup && array_key_exists($table_name, $db_lookup) && ! is_array($db_lookup[ $table_name ])) { // If cache not found or expired
		$amt_int = $db_lookup[ $table_name ];
//		write_log("nele_db_amount (cache): table_name=$table_name, amt_int=$amt_int");
		return $amt_int;
	}
	$amount = $wpdb->get_results( "select count(*) as cnt from " . nele_db_add_table_prefix($table_name) );
	if (! $db_lookup) {
		$db_lookup = array();
	}
	$amt_int = $amount[0]->cnt;
	$db_lookup[ $table_name ] = $amt_int;
	set_transient('nele_db_amount_cache', $db_lookup, DAY_IN_SECONDS); // Cache for a day
	write_log("nele_db_amount: table_name=$table_name, amt_int=$amt_int");
	return $amt_int;
}

function nele_db_offices($country_id, $place_id, $courier_name = null, $office_name = null): array {
    write_log("neleDbGetOffices: country_id=$country_id, place_id=$place_id, courier_name=$courier_name, office_name=$office_name");
    global $wpdb, $nele_offices_table;
    $table_name = nele_db_add_table_prefix($nele_offices_table);
    $where_clause = " and country_id = $country_id and place_id = $place_id";
    $where_clause .= $courier_name ? " and lower(subcontractor) LIKE '%" . mb_strtolower($courier_name). "%'" : "";
    $where_clause .= $office_name ? " and lower(CONCAT(name, ' (', address, ')')) LIKE '%" . mb_strtolower($office_name) . "%'" : "";
    $query = "SELECT id, CONCAT(name, ' (', address, ')') as name FROM $table_name where IS_PROD = 1 $where_clause ORDER BY name LIMIT 100";
    return $wpdb->get_results( $query );
}

function nele_db_has_streets($country, $city_name): array {
    write_log("neleDbHasStreets: country=$country, city_name=$city_name");
    global $nele_streets_table, $nele_places_table, $nele_countries_table, $wpdb;
    $streets_table = nele_db_add_table_prefix($nele_streets_table);
    $places_table = nele_db_add_table_prefix($nele_places_table);
    $countries_table = nele_db_add_table_prefix($nele_countries_table);
    $city_name = preg_replace('/\s*\([^)]*\)/', '', $city_name);
    $where_clause = " and lower(p.name) like '%" . mb_strtolower($city_name) . "%'";
    $where_clause .= " and c.code = '$country'";
    $query = "SELECT count(s.id) as amt FROM $places_table p, $streets_table s, $countries_table c where s.IS_PROD = 1 and p.IS_PROD = 1 and c.IS_PROD = 1 and p.id = s.place_id and c.id = p.country_id $where_clause";
    return $wpdb->get_results( $query );
}

function nele_db_streets($country_id, $city_name, $street_name): array {
    write_log("neleDbGetStreets: country_id=$country_id, city_name=$city_name, street_name=$street_name");
    global $nele_streets_table, $nele_places_table, $wpdb;
    $streets_table = nele_db_add_table_prefix($nele_streets_table);
    $places_table = nele_db_add_table_prefix($nele_places_table);
    $city_name = preg_replace('/\s*\([^)]*\)/', '', $city_name);
    $where_clause = " and p.country_id = $country_id";
    $where_clause .= " and lower(p.name) like '%" . mb_strtolower($city_name) . "%'";
    $where_clause .= $street_name ? " and lower(s.name) LIKE '%" . mb_strtolower($street_name) . "%'" : "";
    $query = "SELECT s.id, s.name FROM $places_table p, $streets_table s where s.IS_PROD = 1 and p.IS_PROD = 1 and p.id = s.place_id $where_clause ORDER BY s.name LIMIT 100";
    return $wpdb->get_results( $query );
}

function nele_db_states($country_id, $courier_name = null, $state_name = null): array {
	write_log("nele_db_states: country_id=$country_id, courier_name=$courier_name, state_name=$state_name");
	global $nele_places_table, $wpdb;
	$table_name = nele_db_add_table_prefix($nele_places_table);
	$where_clause = " and country_id = $country_id";
	$where_clause .= $courier_name ? " and lower(couriers) LIKE '%" . mb_strtolower($courier_name) . "%'" : "";
	if (! empty($state_name)) {
		$lower_name = mb_strtolower($state_name);
		$where_clause .= " and lower(region) LIKE '%" . $lower_name . "%'";
	}
	$query = "SELECT @row_number := @row_number + 1 AS id, region as name FROM (SELECT DISTINCT region FROM $table_name WHERE IS_PROD = 1 $where_clause) AS regions, (SELECT @row_number := 0) AS counter ORDER BY region";
	return $wpdb->get_results( $query );
}

function nele_db_cities($country_id, $courier_name = null, $city_name = null, $state_name = null): array {
    write_log("nele_db_cities: country_id=$country_id, courier_name=$courier_name, city_name=$city_name, state_name=$state_name");
    global $nele_places_table, $wpdb;
    $table_name = nele_db_add_table_prefix($nele_places_table);
    $where_clause = " and country_id = $country_id";
    $where_clause .= $courier_name ? " and lower(couriers) LIKE '%" . mb_strtolower($courier_name) . "%'" : "";
	if (! empty($city_name)) {
		$lower_name = mb_strtolower($city_name);
		$where_clause .= " and (lower(CONCAT(name, ' (', post_code, ')')) LIKE '%" . $lower_name . "%'";
        $where_clause .= " or lower(CONCAT(name_en, ' (', post_code, ')')) LIKE '%" . $lower_name . "%')";
	}
	# filter cities by states only if such state exists in the DB. Otherwise, all cities are returned
	if (! empty($state_name) and count(nele_db_states($country_id, $courier_name, $state_name)) > 0) {
		$lower_name = mb_strtolower($state_name);
		$where_clause .= " and lower(region)='$lower_name'";
	}
    $query = "SELECT id, CONCAT(name, ' (', post_code, ')') as name FROM $table_name where IS_PROD = 1 $where_clause ORDER BY name LIMIT 100";
    return $wpdb->get_results( $query );
}

function nele_db_countries($country_code = null): array {
	global $nele_countries_table;
	$db_lookup = get_transient('nele_countries_cache'); // Get cached data
	if ($db_lookup && array_key_exists($country_code, $db_lookup)) { // If cache not found or expired
		return $db_lookup[ $country_code ];
	}
	$clause = $country_code ? array("code" => sanitize_text_field($country_code)) : array();
	$countries = nele_db_read_table_data($nele_countries_table, null, $clause);
	if (!$db_lookup) {
		$db_lookup = array();
	}
	$db_lookup[ $country_code ] = $countries;
	set_transient('nele_countries_cache', $db_lookup, DAY_IN_SECONDS); // Cache for a day
	return $countries;
}

function nele_db_couriers($country_id = null): array {
	global $wpdb, $nele_offices_table;
	$db_lookup = get_transient('nele_couriers_cache'); // Get cached data
	if ($db_lookup && array_key_exists($country_id, $db_lookup)) { // If cache not found or expired
		return $db_lookup[ $country_id ];
	}
	$offices_table = nele_db_add_table_prefix($nele_offices_table);
	$country_code_clause = $country_id ? "AND country_id = '$country_id'" : "";
	$couriers = $wpdb->get_results( "SELECT distinct subcontractor FROM $offices_table WHERE IS_PROD = 1 $country_code_clause order by subcontractor" );
	if (!$db_lookup) {
		$db_lookup = array();
	}
	$db_lookup[ $country_id ] = $couriers;
	set_transient('nele_couriers_cache', $db_lookup, DAY_IN_SECONDS); // Cache for a day
	return $couriers;
}

function nele_db_cities_by_id($city_id)
{
    global $nele_places_table;
    $city_id = sanitize_key($city_id);
    if (empty($city_id)) {
        return array();
    }
    return nele_db_read_table_data($nele_places_table, null, array("id" => $city_id));
}

# warning - accepts raw user input
function nele_db_offices_by_id($office_id)
{
	global $nele_offices_table;
    $office_id = sanitize_key($office_id);
    if (empty($office_id)) {
        return array();
    }
	return nele_db_read_table_data($nele_offices_table, null, array("id" => $office_id));
}

add_action( 'wp_ajax_get_log_entries', 'nele_get_log_entries' );
function nele_get_log_entries() {
	global $wpdb, $nele_log_entries_table;
	$log_entries = $wpdb->get_col( "SELECT text FROM " . nele_db_add_table_prefix($nele_log_entries_table) );
	$log_entries_html = '';
	foreach ( $log_entries as $log_entry ) {
		$log_entries_html .= esc_html( $log_entry ) . "\n";
	}
	echo $log_entries_html;
}

add_action( 'wp_ajax_clear_log_entries', 'nele_clear_log_entries' );
function nele_clear_log_entries() {
	global $wpdb, $nele_log_entries_table;
	$wpdb->query( "TRUNCATE TABLE " . nele_db_add_table_prefix($nele_log_entries_table) );
	echo 'Log entries cleared successfully.';
}