<?php
    require_once('../../_inc/poolsql.php');
    require_once('../../_inc/pgsql.php');
    
function array_multi_unique($array, $sub_key) {
    $target = array();
    $existing = array();
    foreach($array as $key => $sub_array) {
	if (!in_array($sub_array[$sub_key], $existing)) {
	    $existing[] = $sub_array[$sub_key];
	    $target[$key] = $sub_array;
	}
    }
    return $target;
}

function ucanythinghelper($a) {
    return strtoupper($a[1]).((count($a)>2) ? strtolower($a[2]) : '');
}

function ucanything($str) {
    return preg_replace_callback('/\b(\w)(\w+)?/', 'ucanythinghelper', $str);
}
    
    $locs = file('./ip-to-country.csv') or die();
    $conts = array();
    $ips = array();
    $cnt = count($locs);
    for($i=0;$i<$cnt;$i++) {
	$loc = $locs[$i];
	preg_match('/^"([0-9]+)","([0-9]+)","([A-Z]{2})","([A-Z]{3})","(.*?)".*?$/', $loc, $loc);
	if (!empty($loc)) {
    	    $loc[5] = SQL_EscapeString(ucanything(strtolower($loc[5])));
	    $conts[] = array($loc[5], $loc[3], $loc[4]);
    	    $ips[] = array(long2ip($loc[1]+0), long2ip($loc[2]+0), $loc[3]);
	    //echo long2ip($loc[1]+0)." ({$loc[1]}) => ".long2ip($loc[2]+0)." ({$loc[2]})\n";
	    //flush();
	} else
	    echo "$i\n";
    }
    unset($locs);

    $conts = array_multi_unique($conts, 1);
    sort($conts);
    //$ips = array_multi_unique($ips, 0);
    sort($ips);
    $cnt = count($conts);
    $xnt = count($ips);
    for($j=0;$j<$xnt;$j++) {
	for($i=0;$i<$cnt;$i++) {
	    if ($conts[$i][1] == $ips[$j][2]) {
		$ips[$j][2] = $i+1;
		break;
	    }
	}
	$ips[$j][2] = (integer)($ips[$j][2]+0);
    }
    //print_r($conts);
    flush();
    //die();

    pg_query($GLOBALS['sql_hand'], 'BEGIN') or die();
    pg_query($GLOBALS['sql_hand'], 'DELETE FROM chatv3.pjj_country_names') or die();
    pg_query($GLOBALS['sql_hand'], 'DELETE FROM chatv3.pjj_country_ips') or die();
    $cnt = count($conts);
    for($i=0;$i<$cnt;$i++)
        pg_query($GLOBALS['sql_hand'], "INSERT INTO chatv3.pjj_country_names (id, name, name2, name3) VALUES ({$i}+1, '{$conts[$i][0]}','{$conts[$i][1]}','{$conts[$i][2]}')") or die();
    $cnt = count($ips);
    for($i=0;$i<$cnt;$i++)
        pg_query($GLOBALS['sql_hand'], "INSERT INTO chatv3.pjj_country_ips (range_start, range_end, country) VALUES ('{$ips[$i][0]}'::inet,'{$ips[$i][1]}'::inet,{$ips[$i][2]})") or die();
    pg_query($GLOBALS['sql_hand'], 'COMMIT') or die();

?>