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: 4:05:53 PM
 * To change this template use Options | File Templates.
 * <p/>
 * # Columns 1-2: United States Postal Service State Abbreviation
 * # Columns 3-66: Name (e.g. 35004 5-Digit ZCTA - there are no post office names)
 * # Columns 67-75: Total Population (2000)
 * # Columns 76-84: Total Housing Units (2000)
 * # Columns 85-98: Land Area (square meters) - Created for statistical purposes only.
 * # Columns 99-112: Water Area (square meters) - Created for statistical purposes only.
 * # Columns 113-124: Land Area (square miles) - Created for statistical purposes only.
 * # Columns 125-136: Water Area (square miles) - Created for statistical purposes only.
 * # Columns 137-146: Latitude (decimal degrees) First character is blank or "-" denoting North or South latitude respectively
 * # Columns 147-157: Longitude (decimal degrees) First character is blank or "-" denoting East or West longitude respectively
 */
public class CensusZip implements DataFile {
    public String getTable() {
        return "census_zip";
    }

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

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

    public String getCreateQuery() {
        return
                "create table census_zip (	"
                        + "state_code char(2), "
                        + "zip char(5), "
                        + "garbage varchar(100), "
                        + "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, "
                        + "latitude int, "
                        + "longitude int)";
    }

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

    public String getInsertQuery() {
        return "insert into census_zip (state_code, zip, garbage, total_population_2000, "
                + "total_housing_units_2000, land_area_sq_meters, water_area_sq_meters,"
                + "land_area_sq_miles, water_area_sq_miles, latitude, longitude) "
                + "values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
    }

    public int deliminate(InputStream inputStream, PreparedStatement pst) throws SQLException {
        FixedToDelim fixedToDelim = new FixedToDelim();
        return fixedToDelim.deliminate(inputStream, pst, "2,7,65,75,84,98,112,124,136,146");
    }
}