#!/usr/bin/php -q

<?php

    if ($_SERVER["argc"] > 1 && file_exists($_SERVER["argv"][1]) === TRUE) {
    } else {
        echo "USAGE: php cities-import.php input-file\n";
        $dbh = NULL;
        exit;
    }

    $dbName = readline("Database Name: ");

    if (strlen($dbName) < 1) {
        echo "\nERROR: Database name required.\n";
        $dbh = NULL;
        exit;
    }

    $dbUser = readline("Database User: ");

    if (strlen($dbUser) < 1) {
        echo "\nERROR: Database user required.\n";
        $dbh = NULL;
        exit;
    }

    $dbPass = readline("Database Password: ");
    $dbh = new PDO('mysql:host=localhost;dbname='.$dbName, $dbUser, $dbPass);
    $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $index = array();
    $sql = "SELECT city, state_or_province FROM isvr_cities";
    $sth = $dbh->query($sql);

    foreach ($sth as $row) {
        $index[] = $row['city'].$row['state_or_province'];
    }

    $dbh = NULL;

    echo "\nProcessing...\n";

    $handle = fopen($_SERVER["argv"][1], "r");
    if ($handle) {

        $cntLines = 0;
        $newAreas = array();

        while (($line = fgets($handle)) !== false) {
            $fields = array();
            $fields = explode(",", $line);
            $cntLines++;

            if (sizeof($fields) !== 2) {
                echo "\nERROR: Missing field at line $cntLines.\n";
                $dbh = NULL;
                exit;
            }

            $area = array();

            // Field size checks

            if (strlen($fields[1]) > 50 ) {
                echo "\nERROR: Max state_or_province field length (2) exceeded at line $cntLines.\n";
                $dbh = NULL;
                exit;
            } else { $area['state_or_province'] = $fields[0]; }

            if (strlen($fields[0]) > 50 ) {
                echo "\nERROR: Max city field length (50) exceeded at line $cntLines.\n";
                $dbh = NULL;
                exit;
            } else { $area['city'] = $fields[1]; }

            foreach ($index as $i) {
                if (trim($area['city'].$area['state_or_province']) === trim($i)) {
                    echo "\nERROR: City ".$area['city']." already exists at line $cntLines.\n";
                    $dbh = NULL;
                    exit;
                }
            }

            $newAreas[] = $area;
        }

        fclose($handle);
    } else {
        echo "\nERROR: Couldn't open file.\n";
        $dbh = NULL;
        exit;
    }

    $dbh = new PDO('mysql:host=localhost;dbname='.$dbName, $dbUser, $dbPass);
    $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $area = array();

    // Sanitize and insert into database

    foreach ($newAreas as $area) {
        $area['city'] = trim($area['city']);
        $area['state_or_province'] = trim($area['state_or_province']);
        $area['city'] = preg_replace( "/\r|\n/", "", $area['city'] );
        $area['state_or_province'] = preg_replace( "/\r|\n/", "", $area['state_or_province'] );
        $stmt = $dbh->prepare('INSERT INTO isvr_cities (city, state_or_province) VALUES (:city, :state_or_province)');
        $stmt->execute(array('city' => $area['city'], 'state_or_province' => $area['state_or_province']));
    }

    $dbh = NULL;
    echo "$cntLines processed\n";
?>