package com.philmcrew.data;

import com.philmcrew.utility.FixedToDelim;

import java.io.IOException;
import java.io.InputStream;
import java.net.MalformedURLException;
import java.net.URL;
import java.net.URLConnection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * Created by IntelliJ IDEA.
 * User: Mitchell
 * Date: Feb 27, 2005
 * Time: 3:57:53 PM
 * To change this template use Options | File Templates.
 * <p/>
 * <p/>
 * # Columns 1-2: United States Postal Service State Abbreviation
 * # Columns 3-4: State Federal Information Processing Standard (FIPS) code
 * # Columns 5-7: FIPS county code
 * # Columns 8-71: Name
 * # Columns 72-80: Total Population (2000)
 * # Columns 81-89: Total Housing Units (2000)
 * # Columns 90-103: Land Area (square meters) - Created for statistical purposes only.
 * # Columns 104-117: Water Area (square meters) - Created for statistical purposes only.
 * # Columns 118-129: Land Area (square miles) - Created for statistical purposes only.
 * # Columns 130-141 Water Area (square miles) - Created for statistical purposes only.
 * # Columns 142-151 Latitude (decimal degrees) First character is blank or "-" denoting North or South latitude respectively
 * # Columns 152-162 Longitude (decimal degrees) First character is blank or "-" denoting East or West longitude respectively
 */

public class CensusCounties implements DataFile {
    public String getTable() {
        return "census_counties";
    }

    public URL getDataSource() throws MalformedURLException {
        return new URL("http://www.census.gov/tiger/tms/gazetteer/county2k.txt");
    }

    public InputStream getInputStream() throws IOException {
        URL url = getDataSource();
        URLConnection urlConnection = url.openConnection();
        return urlConnection.getInputStream();
    }

    public String getCreateQuery() {
        return "create table census_counties (	"
                + "state_code char(2), "
                + "fips_code char(2), "
                + "fips_county_code char(3), "
                + "name varchar(64), "
                + "total_population_2000 int, "
                + "total_housing_units_2000 int, "
                + "land_area_sq_meters int, "
                + "water_area_sq_meters int, "
                + "land_area_sq_miles int, "
                + "water_area_sq_miles int, "
                + "latitute int, "
                + "longitude int)";
    }

    public String getDeleteQuery() {
        return "delete from census_counties";
    }

    public String getInsertQuery() {
        return "insert into census_counties values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
    }

    public int deliminate(InputStream inputStream, PreparedStatement pst) throws SQLException {
        FixedToDelim fixedToDelim = new FixedToDelim();
        return fixedToDelim.deliminate(inputStream, pst, "2,4,7,71,80,89,103,117,129,141,151");
    }
}