package db.usgs;

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

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

class DBNullValueException extends Exception {
}

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

  // Converts dddmmssc format to +/- decimal degreees
  static double addSDPtoUSGSLatLong(String latLong) 
    throws DBNullValueException {
    StringBuffer d=new StringBuffer(latLong);

    // remove leading space
    while (Character.isWhitespace(d.charAt(0))) {      
      d.deleteCharAt(0);
      if (d.length()==0) throw new DBNullValueException();
    }

    // Anything south or west is negative
    switch (d.charAt(d.length()-1)) {
    case 'S' :
    case 'W' :
      d.insert(0,'-');
    }

    //remove N,S,E,W
    d.deleteCharAt(d.length()-1);

    int l=d.length();
    try {
      return Double.parseDouble(d.substring(0,l-4))+Double.parseDouble(d.substring(l-4,l-2))/60+Double.parseDouble(d.substring(l-2,l))/3600;
    }
    catch (Exception e) {
      throw new DBNullValueException();
    }
  }

  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");
    }
  }   

  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,feature,populatedPlace");
      System.out.println("Dropped old usgs 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)"); 

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

      while ((str=fd.readLine())!=null) {
	ps.setInt(1,Integer.parseInt(str.substring(0,2)));
	ps.setString(2,str.substring(11,13));
	ps.setString(3,str.substring(21));
	con.executeInsert();
      }
      
      con.finishInsert();

      // Import principal cultural and physical features
      loadFile("features.txt");
      table="feature";
      rowCount=0;

      con.createTable(table,"name VARCHAR(50),"+ 
		      "type VARCHAR(9),"+
		      "county VARCHAR(30) NULL,"+
		      "stateName VARCHAR(30) NULL,"+
		      "latitude DECIMAL(7,4) NULL,"+
		      "longitude DECIMAL(7,4) NULL,"+
		      "elevation INT NULL,"+
		      "featureID INT");
      con.addConstraint(table,"feature_PK PRIMARY KEY (featureID)");
      // The following might be thought to be correct, but infact there are
      // "States" called Canada-US!
      //con.addConstraint(table,"feature_stateName_FK FOREIGN KEY (stateName) "+
      //		"REFERENCES state(name)");

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

      while ((str=fd.readLine())!=null) {
	rowCount++;
	try {
	  String name=str.substring(0,51).trim();
	  con.setInsertString(1,name);
	  con.setInsertString(2,str.substring(51,61));
	  con.setInsertString(3,str.substring(61,93));
	  // state names are left empty for the states themselves, so 
	  // obtain state name from name field
	  String state=str.substring(93,109).trim();
	  if (state.length()==0) {
	    int pos=name.indexOf(',');
	    if (pos>0)
	      state=name.substring(0,pos);
	    else
	      state=name;
	  }
	  con.setInsertString(4,state);
	  try {
	    ps.setDouble(5,addSDPtoUSGSLatLong(str.substring(109,117)));
	    ps.setDouble(6,addSDPtoUSGSLatLong(str.substring(117,125)));
	  }
	  catch (DBNullValueException ne) {
	    ps.setNull(5,Types.DOUBLE);
	    ps.setNull(6,Types.DOUBLE);
	  }
	  con.setInsertInt(7,str.substring(126));
	  ps.setInt(8,rowCount);

	  con.executeInsert();
	}
	catch (Exception e) {
	  System.out.println("Error reading row "+rowCount+":\n"+str);
	  if (con.getDebugLevel()==0)
	    System.out.println(e);
	  else
	    e.printStackTrace();
	}

      }
      
      con.finishInsert();

      // Import populated places
      loadFile("populated_places.txt");
      table="populatedPlace";
      rowCount=0;

      con.createTable(table,"name VARCHAR(50),"+
		      "type VARCHAR(9),"+
		      "county VARCHAR(40),"+
		      "stateCode INT,"+
		      "countyCode INT,"+
		      "latitude DECIMAL(7,4) NULL,"+
		      "longitude DECIMAL(7,4) NULL,"+
		      "elevation INT NULL,"+
		      "population INT NULL,"+
		      "federalStatus VARCHAR(8) NULL,"+
		      "cellName VARCHAR(40),"+
                      "placeID INT");
      con.addConstraint(table,"populatedPlace_PK PRIMARY KEY "+
			"(placeID)");
      con.addConstraint(table,"feature_stateCode_FK FOREIGN KEY "+
			"(stateCode) REFERENCES state(code)");

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

      while ((str=fd.readLine())!=null) {
	rowCount++;
	try {
	  db.lib.CSVTokeniser csvt=new db.lib.CSVTokeniser(str);

	  csvt.getNextToken(); // ignore stateAbbr since can be obtained from
	  // state table
          con.setInsertString(1,csvt.getNextToken());
	  con.setInsertString(2,csvt.getNextToken());
	  con.setInsertString(3,csvt.getNextToken());
	  con.setInsertInt(4,csvt.getNextToken());
	  con.setInsertInt(5,csvt.getNextToken());
	  csvt.getNextToken(); // Ignore degree/minute represenation of Lat
	  csvt.getNextToken(); // and of long
	  con.setInsertDouble(6,csvt.getNextToken());
	  con.setInsertDouble(7,csvt.getNextToken());
	  con.setInsertInt(8,csvt.getNextToken());
	  con.setInsertInt(9,csvt.getNextToken());
	  con.setInsertString(10,csvt.getNextToken());
	  con.setInsertString(11,csvt.getNextToken());
          ps.setInt(12,rowCount);

	  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();

   }
    catch(Exception e) {
      e.printStackTrace();
    }
  }                          
}