package db.uscensus1990;

import java.io.*;
import java.util.*;
import java.sql.*;

/**
 * Title:        BuildUSCensus1990
 * Description:
 * Copyright:    Copyright (c) 2001
 * Company:
 * @author Peter McBrien
 * @version 1.0
 */

class DBNullValueException extends Exception {
}

public class BuildUSCensus1990 {
  private static RandomAccessFile fd=null;
  private static db.lib.DBConnection con=null;
  private static boolean removeSingleQuote=true; 

  static double addDPtoFIPSLatLong(String FIPSLatLong) {
    int l=FIPSLatLong.length();
    return Double.parseDouble(FIPSLatLong.substring(0,l-6)+
			      "."+FIPSLatLong.substring(l-6,l));
  }

  static void loadFile(String file) {
    try {
      if (fd!=null) fd.close();
    }
    catch (Exception ce) {}

    System.out.println("Reading data file " + file);
    try {
      fd=new RandomAccessFile(file, "r");
    }
    catch(FileNotFoundException e) {
      System.out.println("File " + file + " not found");
    }
  }   

  private static void readPlaceFormat(String table) 
  throws Exception {
    con.createTable(table,"stateCode INT,"+
		    "countyCode INT,"+
		    "name CHAR(70),"+
		    "population INT," +
		    "housingUnits INT,"+
		    "landArea INT,"+
		    "waterArea INT,"+
		    "latitude DECIMAL(12,6),"+
		    "longitude DECIMAL(12,6)");
    con.addConstraint(table,table+"_PK PRIMARY KEY (stateCode, countyCode)");
    con.addConstraint(table,table+"_stateCode_FK FOREIGN KEY (stateCode) "+
		      "REFERENCES state(code)");

    PreparedStatement ps=con.prepareInsert(table,"(?,?,?,?,?,?,?,?,?)");
    String str;

    while ((str=fd.readLine())!=null) {
      con.setInsertInt(1,str.substring(0,2));
      con.setInsertInt(2,str.substring(5,8));
      con.setInsertString(3,str.substring(9,75));
      con.setInsertInt(4,str.substring(79,88));
      con.setInsertInt(5,str.substring(89,98));
      con.setInsertInt(6,str.substring(99,109));
      con.setInsertInt(7,str.substring(110,120));
      con.setInsertDouble(8,addDPtoFIPSLatLong(str.substring(121,130)));
      con.setInsertDouble(9,addDPtoFIPSLatLong(str.substring(131,141)));
      con.executeInsert();
    }  
  }

  public static void main(String[] args) {

    try {
      String table;
      String str="";
      int rowCount=0;

      con=new db.lib.DBConnection(args);
      
      // Drop the tables if there
      con.executeUpdate("DROP TABLE state,county,place,zip,mcd");
      System.out.println("Dropped old uscensus1990 database");

      // Import state codes
      loadFile("states.txt");
      table="state";
      con.createTable(table,"code INT,"+ 
		  "abbr CHAR(2),"+
		  "name CHAR(30)");
      con.addConstraint(table,"state_PK PRIMARY KEY (code)");
      con.executeUpdate("CREATE UNIQUE INDEX state_name ON state(name)"); 
      con.executeUpdate("CREATE UNIQUE INDEX state_abbr ON state(abbr)"); 

      con.prepareInsert(table,"(?,?,?)");

      while ((str=fd.readLine())!=null) {
	con.setInsertInt(1,str.substring(0,2));
	con.setInsertString(2,str.substring(11,13));
	con.setInsertString(3,str.substring(21));
	con.executeInsert();
      }
      
      con.finishInsert();

      // Import mcds
      loadFile("mcds.txt");
      table="mcd";
      con.createTable(table,"stateCode INT,"+
                  "countyCode INT,"+
		  "countySubdivisionCode INT,"+
		  "name CHAR(60),"+
                  "type CHAR(20) NULL,"+
		      //"stateAbbr CHAR(2),"+
		  "population INT,"+
		  "housingUnits INT,"+
		  "landArea INT,"+
		  "waterArea INT,"+
		  "latitude DECIMAL(12,6),"+
		  "longitude DECIMAL(12,6)");
      con.addConstraint(table,"mcd_PK PRIMARY KEY (stateCode, countySubdivisionCode, countyCode)");
      con.addConstraint(table,"mcd_stateCode_FK FOREIGN KEY (stateCode) "+
		    "REFERENCES state(code)");
      con.addExtraConstraint(table,"mcd_stateCode_countyCode_FK "+
			     "FOREIGN KEY (stateCode,CountyCode)"+
			     "REFERENCES county(stateCode,countyCode)");

      con.prepareInsert(table,"(?,?,?,?,?,?,?,?,?,?,?)");

      while ((str=fd.readLine())!=null) {
	try {
	  String stateAbbr=str.substring(76,78);
	  String name=str.substring(9,75).trim();
	  String type="NULL";
	  int div=name.lastIndexOf(' ');

	  // Maryland and Louisian seem to have just Districts
	  if (name.length()>8 && name.substring(0,9).equals("District "))
	    type="'district'";
	  else if (name.length()>8 && name.substring(0,9).equals("Precinct "))
	    type="'precinct'";
	  else if (name.length()>8 && name.substring(0,9).equals("Township "))
	    type="'township'";
	  else if (name.length()>14 && 
		   name.substring(name.length()-14).equals("census subarea"))
	    type="'census subarea'";
	  else if (div>0 && Character.isLowerCase(name.charAt(div+1))) {
	    type="'"+name.substring(div+1)+"'";	    
	    name=name.substring(0,div);
	  }
      
	  name="'"+name+"'";

	  con.setInsertInt(1,str.substring(0,2));
	  con.setInsertInt(2,str.substring(142,145));
	  con.setInsertInt(3,str.substring(3,8));
	  con.setInsertString(4,name);
	  con.setInsertString(5,type);
	  con.setInsertInt(6,str.substring(79,88));
	  con.setInsertInt(7,str.substring(89,98));
	  con.setInsertInt(8,str.substring(99,109));
	  con.setInsertInt(9,str.substring(110,120));
	  con.setInsertDouble(10,addDPtoFIPSLatLong(str.substring(121,130)));
	  con.setInsertDouble(11,addDPtoFIPSLatLong(str.substring(131,141)));
	  con.executeInsert();
	}
	catch (DBNullValueException ne) {
	  System.out.println("Ignoring null value in:\n"+str);
	}
	catch (Exception e) {
	  System.out.println("Error reading row "+rowCount+":\n"+str);
	  e.printStackTrace();
	}
      }
      
      con.finishInsert();
						  
      // Import zip codes
      loadFile("zips.txt");
      table="zip";
      con.createTable(table,"stateCode INT,"+
		  "zipCode INT,"+
		  "zipName CHAR(20)," +
		  "longitude DECIMAL(18,8),"+
		  "latitude DECIMAL(18,8),"+
		  "population INT," +
		  "allocationFactor DECIMAL(10,8)");
      con.addConstraint(table,"zip_PK PRIMARY KEY (zipCode)");
      con.addConstraint(table,"zip_stateCode_FK FOREIGN KEY (stateCode) "+
			"REFERENCES state(code)");

      con.prepareInsert(table,"(?,?,?,?,?,?,?)");

      while ((str=fd.readLine())!=null) {
	try {
	  db.lib.CSVTokeniser csvt=new db.lib.CSVTokeniser(str);
	  con.setInsertInt(1,csvt.getNextToken());
	  con.setInsertInt(2,csvt.getNextToken());
	  csvt.getNextToken(); // State abbreviation code
	  con.setInsertString(3,csvt.getNextToken());
	  con.setInsertDouble(4,csvt.getNextToken());
	  con.setInsertDouble(5,csvt.getNextToken());
	  con.setInsertInt(6,csvt.getNextToken());
	  con.setInsertDouble(7,csvt.getNextToken());
	  con.executeInsert();
	}
	catch (DBNullValueException ne) {
	  System.out.println("Ignoring null value in:\n"+str);
	}
	catch (Exception e) {
	  System.out.println("Error reading row "+rowCount+":\n"+str);
	  e.printStackTrace();
	}
      }
      
      con.finishInsert();

      // Import counties
      loadFile("counties.txt");
      readPlaceFormat("county");      
      con.finishInsert();

      // Import places
      loadFile("places.txt");
      readPlaceFormat("place");
      con.finishInsert();
   }
    catch(Exception e) {
      e.printStackTrace();
    }
  }                          
}