<?php

class Block
{
    private $startIpNum;
    private $endIpNum;
    private $locId;

    public function Block( $startIpNum, $endIpNum, $locId )
    {
        $this->startIpNum = (int)$startIpNum;
        $this->endIpNum = (int)$endIpNum;
        $this->locId = (int)$locId;
    }

    public function getStartIpNum() { return $this->startIpNum; }
    public function getEndIpNum() { return $this->endIpNum; }
    public function getLocId() { return $this->locId; }
}

class Location
{
    private $locId;
    private $country;
    private $region;
    private $city;
    private $postalCode;
    private $latitude;
    private $longitude;
    private $metroCode;
    private $areaCode;

    public function Location( $locId, $country, $region, $city, $postalCode, $latitude, $longitude, $metroCode, $areaCode )
    {
        $this->locId = (int)$locId;
        $this->country = (string)$country;
        $this->region = (string)$region;
        $this->city = (string)$city;
        $this->postalCode = (string)$postalCode;
        $this->latitude = (double)$latitude;
        $this->longitude = (double)$longitude;
        $this->metroCode = (int)$metroCode;
        $this->areaCode = (int)$areaCode;
    }

    public function getLocId() { return $this->locId; }
    public function getCountry() { return $this->country; }
    public function getRegion() { return $this->region; }
    public function getCity() { return $this->city; }
    public function getPostalCode() { return $this->postalCode; }
    public function getLatitude() { return $this->latitude; }
    public function getLongitude() { return $this->longitude; }
    public function getMetroCode() { return $this->metroCode; }
    public function getAreaCode() { return $this->areaCode; }
}

interface BlockReader
{
    /**
      * @returns Block
      */
    public function readEntry();
}

class CsvBlockReader implements BlockReader
{
    private $fd;

    public function CsvBlockReader( $filename )
    {
        $this->fd = fopen( $filename, "r" );
        if( !$this->fd )
            throw new Exception( "Could not open block csv file $filename" );

        // skip copyright line
        fgets( $this->fd );
        // skip header line
        fgets( $this->fd );
    }

    public function readEntry()
    {
        $entry = fgetcsv( $this->fd, 0, ",", "\"", "\\" );
        if( !$entry )
            return null;

        $startIpNum = (int)$entry[0];
        $endIpNum = (int)$entry[1];
        $locId = (int)$entry[2];

        $block = new Block( $startIpNum, $endIpNum, $locId );

        return $block;
    }
}

interface LocationReader
{
    /**
      * @returns Location
      */
    public function readEntry();
}

class CsvLocationReader implements LocationReader
{
    private $fd;

    public function CsvLocationReader( $filename )
    {
        $this->fd = fopen( $filename, "r" );
        if( !$this->fd )
            throw new Exception( "Could not open location csv file $filename" );

        // skip copyright line
        fgets( $this->fd );
        // skip header line
        fgets( $this->fd );
    }

    public function readEntry()
    {
        $entry = fgetcsv( $this->fd, 0, ",", "\"", "\\" );
        if( !$entry )
            return null;

        $locId = (int)$entry[0];
        $country = (string)$entry[1];
        $region = (string)$entry[2];
        $city = (string)$entry[3];
        $postalCode = (string)$entry[4];
        $latitude = (double)$entry[5];
        $longitude = (double)$entry[6];
        $metroCode = (int)$entry[7];
        $areaCode = (int)$entry[8];

        $location = new Location( $locId, $country, $region, $city, $postalCode, $latitude, $longitude, $metroCode, $areaCode );

        return $location;
    }
}

interface GeoLiteCityDao
{
    public function insertLocation( Location &$location );
    public function insertBlock( Block &$block );
}

class GeoLiteCityDaoException extends Exception
{
    private $debugInfo;

    public function __construct( $message, $debugInfo )
    {
        $this->debugInfo = $debugInfo;
        parent::__construct( $message );
    }

    public function getDebugInfo()
    {
        return $this->debugInfo;
    }
}

class SqlGeoLiteCityDao implements GeoLiteCityDao
{
    protected $conn;
    private $insert_location_stmt = null;
    private $insert_block_stmt = null;

    public function SqlGeoLiteCityDao( PDO &$conn )
    {
        $this->conn = $conn;
    }

    public function insertLocation( Location &$location )
    {
        if( $this->insert_location_stmt === null )
        {
            $insert_location_sql =
"INSERT INTO Location
(
    locId,
    country,
    region,
    city,
    postalCode,
    location,
    metroCode,
    areaCode
) VALUES (
    :locId,
    :country,
    :region,
    :city,
    :postalCode,
    Point(:latitude, :longitude),
    :metroCode,
    :areaCode
);";
            $this->insert_location_stmt = $this->conn->prepare( $insert_location_sql );
            if( !$this->insert_location_stmt )
            {
                $debugInfo = array(
                    "statement" => $insert_location_sql,
                    "errorInfo" => $this->conn->errorInfo()
                );
                throw new GeoLiteCityDaoException( "Insert location statement prepare failed", $debugInfo );
            }
        }

        $parameters = array(
            ":locId" => $location->getLocId(),
            ":country" => $location->getCountry(),
            ":region" => $location->getRegion(),
            ":city" => $location->getCity(),
            ":postalCode" => $location->getPostalCode(),
            ":longitude" => $location->getLongitude(),
            ":latitude" => $location->getLatitude(),
            ":metroCode" => $location->getMetroCode(),
            ":areaCode" => $location->getAreaCode()
        );
        $ret = $this->insert_location_stmt->execute( $parameters );
        if( $ret !== true )
        {
            $debugInfo = array(
                "statement" => $insert_location_sql,
                "parameters" => $parameters,
                "errorInfo" => $this->insert_location_stmt->errorInfo()
            );
            throw new GeoLiteCityDaoException( "Insert location statement execution failed", $debugInfo );
        }
    }

    public function insertBlock( Block &$block )
    {
        if( $this->insert_block_stmt === null )
        {
            $insert_block_sql =
"INSERT INTO Blocks
(
    startIpNum,
    endIpNum,
    locId
) VALUES (
    :startIpNum,
    :endIpNum,
    :locId
);";
            $this->insert_block_stmt = $this->conn->prepare( $insert_block_sql );
            if( !$this->insert_block_stmt )
            {
                $debugInfo = array(
                    "statement" => $insert_block_sql,
                    "errorInfo" => $this->conn->errorInfo()
                );
                throw new GeoLiteCityDaoException( "Insert block statement prepare failed", $debugInfo );
            }
        }

        $parameters = array(
            ":startIpNum" => $block->getStartIpNum(),
            ":endIpNum" => $block->getEndIpNum(),
            ":locId" => $block->getLocId()
        );
        $ret = $this->insert_block_stmt->execute( $parameters );
        if( $ret !== true )
        {
            $debugInfo = array(
                "statement" => $insert_block_sql,
                "parameters" => $parameters,
                "errorInfo" => $this->insert_block_stmt->errorInfo()
            );
            throw new GeoLiteCityDaoException( "Insert block statement execution failed", $debugInfo );
        }
    }
}

class ProgressIndicator
{
    private $count_str = "";

    public function begin()
    {
        echo "Progress: {$this->count_str}";
        flush();
    }
    protected function clearProgressDisplay()
    {
        for( $i = 0; $i < strlen($this->count_str); $i++ )
        {
            echo "\x08";
        }
    }

    public function show( $count )
    {
        $this->clearProgressDisplay();
        $this->count_str = (string)$count;
        echo $this->count_str;
        flush();
    }
    public function end()
    {
        echo "\n";
    }
}

class GeoLiteCityImporter
{
    protected $dao;
    protected $location_reader;
    protected $block_reader;

    public function GeoLiteCityImporter( GeoLiteCityDao &$dao, LocationReader &$location_reader, BlockReader &$block_reader )
    {
        $this->dao = $dao;
        $this->location_reader = $location_reader;
        $this->block_reader = $block_reader;
    }

    public function run()
    {
        echo "Inserting locations.\n";
        $count = 0;
        $progress_indicator = new ProgressIndicator();
        $progress_indicator->begin();
        while( $location = $this->location_reader->readEntry() )
        {
            $this->dao->insertLocation( $location );

            $count++;
            if( $count % 1000 == 0 )
            {
                $progress_indicator->show( $count );
            }
        }
        $progress_indicator->show( $count );
        $progress_indicator->end();

        echo "Inserting blocks.\n";
        $count = 0;
        $progress_indicator = new ProgressIndicator();
        $progress_indicator->begin();
        while( $block = $this->block_reader->readEntry() )
        {
            $this->dao->insertBlock( $block );

            $count++;
            if( $count % 1000 == 0 )
            {
                $progress_indicator->show( $count );
            }
        }
        $progress_indicator->show( $count );
        $progress_indicator->end();
    }
}

$dsn = "mysql:host=localhost;dbname=GeoLiteCity";
$user = "root";
$pass = "";
$conn = new PDO( $dsn, $user, $pass );

$dao = new SqlGeoLiteCityDao( $conn );

try
{
    $location_reader = new CsvLocationReader( "GeoLiteCity-Location.csv" );
    $block_reader = new CsvBlockReader( "GeoLiteCity-Blocks.csv" );

    $importer = new GeoLiteCityImporter( $dao, $location_reader, $block_reader );
    $importer->run();
} catch( GeoLiteCityDaoException $e )
{
    echo "Dao exception: " . $e->getMessage() . "\n";
    var_dump( $e->getDebugInfo() );
    exit(1);
} catch( Exception $e )
{
    echo "Exception: $e\n";
    exit(1);
}

exit(0);

?>