/*
 * @file: DetailReader.java
 * @author:  Zubin Girglani, Evelina Khukhashvili, Marie Shvartsapel
 * @course:  V22.0480.03, Visualization, Spring 2003
 * @date: March 2003
 * @synopsis:
 *	This is derived from Reader.java by Bansi Kotecha and Chee Yap.
 *		To understand the format used in this program, please
 *		read the comments in Reader.java.
 *	This reader is highly specialized to combine the
 *		information in Tiger RT1 and RT2 files.
 *	The goal is to produce a single path called "details"
 *		for each tiger line.  
 *	-- It assumes that the RT1.fmt and RT2.fmt files 
 *	   are stored in the same directory location.
 *	-- It assumes that the database bas been created(?)
 *
 */

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

public class DetailReader extends RawDataReader {

    private final int fileExtLength = 3;
    private String frmtFile;	// e.g., RT1.fmt
    private String dataFile1;	// e.g., TGR34017.RT1
    private String rt2FmtFileName;
    private String rt2FileName;
    private String tableName;	// Extracted from dataFile, e.g., RT1
    private String[][] frmtdata;
    private int noOfAttributes;
    private int fieldsPerAttribute;	// This is a slight misnomer, as
    private int countRows = 0;
    private Statement stmt;
    private PreparedStatement pstmt;
    private char option;

    public DetailReader(String[] args) {
        getArguments(args);
        //tableName = getTableName(dataFile1);
        tableName = "rt12";
        rt2FmtFileName = getRT2FmtFile(frmtFile);
        rt2FileName = getRT2File(dataFile1);

        try {
            stmt = establishConnection().createStatement();
        } catch (SQLException se) {
            se.printStackTrace();
        }

        if ((option == 'c') || (option == 'C'))
            createTable();
        readFrmtFile(frmtFile);
        readRawDataFile1();
        readFrmtFile(rt2FmtFileName);
        readRawDataFile2();
    }


    public void getArguments(String args[]) {
        if (args[0].startsWith("-")) {
            // do something to get the option
            // this is temporary. There can me more than one options.
            option = args[0].charAt(1);
            this.frmtFile = args[1];
            this.dataFile1 = args[2];
        } else {
            this.frmtFile = args[0];
            this.dataFile1 = args[1];
        }
    }

    private String getTableName(String filename) {
        String ext = filename.substring(filename.length() - fileExtLength);
        return ext;
    }

    private String getRT2FmtFile(String filename) {
        String fmtFile = filename.substring(0, filename.length() - (fileExtLength + 2));
        fmtFile = fmtFile + "2.fmt";
        return fmtFile;
    }

    private String getRT2File(String filename) {
        String tigerFile = filename.substring(0, filename.length() - fileExtLength);
        tigerFile = tigerFile + "RT2";
        return tigerFile;
    }

    private void readFrmtFile(String formatFile) {
        int count = 0;
        BufferedReader br = null;
        try {
            br = new BufferedReader(new FileReader(formatFile));
            String line;
            int i = 0;
            while ((line = br.readLine()) != null) {
                line = line.trim();
                int x = line.indexOf("#");
                if (x >= 0) line = line.substring(0, x).trim();
                if (line.length() == 0) continue;
                count++;
                if (count == 1) {
                    noOfAttributes = Integer.parseInt(line);
                } else if (count == 2) {
                    fieldsPerAttribute = Integer.parseInt(line);
                    frmtdata =
                            new String[noOfAttributes][fieldsPerAttribute];
                } else {
                    if (i < frmtdata.length) {
                        parseFrmtdata(line, i);
                        i++;
                    }	// excess lines are discarded
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private void parseFrmtdata(String line, int i) {
        StringTokenizer st = new StringTokenizer(line, " \t");
        int j = 0;
        while (st.hasMoreTokens()) {
            frmtdata[i][j] = st.nextToken();
            j++;
        }
    }

    private void createTable() {
        try {
            String query = "create table " + tableName + "(";
            for (int i = 0; i < frmtdata.length; i++) {
                query += frmtdata[i][0] + " "
                        + getDataTypeAsString(frmtdata[i][1]);
                if (i == (frmtdata.length - 1))
                    query += ")";
                else
                    query += ", ";
            }

            //print("create query is : " + query);
            int i = stmt.executeUpdate(query);
            if (i == 1)
                print("Table " + tableName + " created successfully");
            else
                print("Error: Could not create table " + tableName);
        } catch (SQLException se) {
            se.printStackTrace();
        }
    }

    private String getDataTypeAsString(String given) {
        if (given.equals("N"))
            return "integer";
        if (given.equals("A"))	// Also need "C" for single character.
            return "text";
        if (given.equals("L"))
            return "point";	// Kludge: cannot have rtree index
        // on point data!  Only polygondata.
        if (given.equals("P"))
            return "path";
        return "";		// should be error
    }

    private int stringToInt(String value) {
        if (value.equals("")) {
            return -1;
        } else {
            return Integer.parseInt(value);
        }
    }

    /******************************************************************
     readRawDataFile is the main reading function
     -- it reads each line of dataFile and inserts into database table.
     ******************************************************************/
     
    //process file RT1 
    private void readRawDataFile1() {
    	countRows = 0;
        //print("readRawDataFile() called");
        try {
            BufferedReader br = new BufferedReader(
                    new FileReader(dataFile1));
            String record, query;
            String[] attributes;

            while ((record = br.readLine()) != null) {
                attributes = parseRecord(record);
                query = createQuery(attributes);
                insertIntoDatabase(query);
            }

            commit();
            print("\n" + countRows + " rows inserted from file " + dataFile1);


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


    }
    

    private void readRawDataFile2() {
        //do the same for RT2
        String record, query;
        String[] attributes;

	countRows = 0;
        try {
            BufferedReader br = new BufferedReader(new FileReader(rt2FileName));

            String temp, currentTLID = "", path;
            pstmt = con.prepareStatement("SELECT startpt, endpt FROM " + tableName + " where tlid = ?");

            record = br.readLine();
            attributes = parseRecord(record);
            currentTLID = attributes[0];
            path = attributes[2];
            
            while ((record = br.readLine()) != null) {
                attributes = parseRecord(record);
                if (attributes[0].equals(currentTLID)) {
                    path = addSequence(path, attributes[2]);
                } else {
                    //the record last read belongs to the nextTLID
                    //store attr for later use
                    temp = attributes[2];
                    //create and insert query for complete path
                    query = createQueryRT2(currentTLID, path);
                    insertIntoDatabase(query);
                    //move to the next TLID
                    currentTLID = attributes[0];
                    path = temp;
                }

            }
            //inserting the last one
            query = createQueryRT2(currentTLID, path);
            insertIntoDatabase(query);

            commit();
            print("\n" + countRows + " rows inserted from file " + rt2FileName);
        } catch (Exception ie) {
            ie.printStackTrace();
        }


        //insert start and end points into details field for those tlid, that were not mentioned in the RT2 file
        /*try {
            String startpt;
            String endpt;
            String tlid;

            ResultSet results = stmt.executeQuery("SELECT tlid, startpt, endpt FROM " + tableName + " where details is null");

            int resSize =results.getFetchSize();
            String[] queries = new String[resSize];

            int count = 0;
            while (results.next()) {
                startpt = results.getString("startpt");
                endpt = results.getString("endpt");
                tlid = results.getString("tlid");
                //startpt = startpt.substring(1, (startpt.length() - 1));
                //endpt = endpt.substring(1, (endpt.length() - 1));

                query = "update " + tableName + " set details = '" + startpt + "," + endpt + "' where tlid=" + tlid;
                //System.out.println("last query is " + query);
                //insertIntoDatabase(query);
                queries[count] = query;
                count++;
                //print("resultSet size (after insert)"+results.getFetchSize());
            }
            for (int i = 0; i < queries.length; i++) {
                //System.out.println("updating query number " + i + "out of " + queries.length);
                insertIntoDatabase(queries[i]);
            }
            commit();
        } catch (java.sql.SQLException e) {
            System.out.println("Sql exception occurred: " + e);
            System.exit(0);
        }*/


    }

    private String addSequence(String path, String sequence) {
	//when reading detail points for the path with sequense number greater than 1, we need to ass them to existing path
        return path + ", " + sequence;
    }

    private String[] parseRecord(String record) {
        String[] attributes = new String[frmtdata.length];
        String type;

        for (int i = 0; i < frmtdata.length; i++) {
            type = frmtdata[i][1];
            if (type.equals("N")) {
                attributes[i] = stringToN(record, i);
            } else if (type.equals("A")) {
                attributes[i] = stringToA(record, i);
            } else if (type.equals("L")) { // assume equals("L"), lat/lon
                attributes[i] = stringToL(record, i);
            } else if (type.equals("P")) {
                attributes[i] = stringToP(record, i);
            }
        }
        return attributes; //attributes are string data representation
        //ready to go into DB
    }

    private String stringToN(String record, int i) {
        //to convert to type N just
        String result;

        result = record.substring(Integer.parseInt(frmtdata[i][2]) - 1,
                Integer.parseInt(frmtdata[i][3])).trim();
        return result;
    }

    private String stringToA(String record, int i) {
        //to convert to type A single quotes need to be added
        String result;

        result = record.substring(Integer.parseInt(frmtdata[i][2]) - 1,
                Integer.parseInt(frmtdata[i][3])).trim();
        return result;
    }

    private String stringToL(String record, int i) {
        //to convert to type L we need to extract Long, Lat and form a point
        String lonlat, result;
        lonlat = record.substring(Integer.parseInt(frmtdata[i][2]) - 1, Integer.parseInt(frmtdata[i][3]));

        return formPoint(lonlat);
    }

    private String stringToP(String record, int i) {
    //parses the record into pairs of long, lat, forming valid Postgress representation for a path /polygon 
        int beginIndex = 0;
        String path, result = "", point;
        String[] points = new String[10];

        path = record.substring(Integer.parseInt(frmtdata[i][2]) - 1, Integer.parseInt(frmtdata[i][3]));

        int k = 0;
        do {
            points[k] = path.substring(beginIndex, beginIndex + 19);
            point = formPoint(points[k]);
            if (!point.equals("") && k != 0) {
                result += ",";
            }
            result += point;
            beginIndex += 19;
            k++;
        } while (!point.equals("") && k < 10);

        return result;
    }

    private String formPoint(String longlat) {
        String lon, lat;
        //print("form point- longlat"+longlat);
        //we know that first 10 chars in this piece represent Lon and second 9 - Lat
        lon = longlat.substring(0, 10);     //first 10 chars
        lat = longlat.substring(10);         //starting from 11th(first is 0) till the end
        lon = lon.trim();
        lat = lat.trim();
        lon = removeLeadingPlus(lon);
        lat = removeLeadingPlus(lat);
        if (Integer.parseInt(lon) == 0 && Integer.parseInt(lat) == 0)
            return "";
        else
            return "(" + lon + ", " + lat + ")";
    }

    /******************************************************************
     Quoting all roque characters in the raw input files: \'
     ******************************************************************/
    private String quoteCharacters(String value) {
        StringBuffer sb = new StringBuffer(value);
        for (int j = 0; j < sb.length(); j++) {
            if (sb.charAt(j) == '\'') {
                sb.insert(j - 1, '\'');
            }
        }
        return sb.toString();
    }

    private String createQuery(String[] attributes) {
        /*for (int i = 0; i < attributes.length; i++) {
            System.out.println(attributes[i]);
        } */
        String query = "";

        query += "insert into " + tableName + " values (";

        for (int i = 0; i < frmtdata.length; i++) {
            query += "'" + attributes[i] + "'";

            if (i != frmtdata.length - 1) {
                query += ", ";
            }

        }
        //this breaks the generality of the query, but allows for more efficiency by inserting start and end points into details column
        query += ", '" + attributes[1] + "," + attributes[2] + "'";
        query += ")";
        //System.out.println("The query is " + query);
        return query;
    }

    private String createQueryRT2(String tlid, String path) {
        //for(int i = 0; i < attributes.length; i++)
        //{
        //	System.out.println(attributes[i]);
        //}

        String query = "";
        String startpt = "";
        String endpt = "";

        try {
            pstmt.setInt(1, Integer.parseInt(tlid));
            //execute select
            ResultSet results = pstmt.executeQuery();
            if (results.next()) {
                startpt = results.getString("startpt");
                endpt = results.getString("endpt");
            }
            //print("startpt " + startpt);
            //startpt = startpt.substring(1, (startpt.length() - 1));
            //endpt = endpt.substring(1, (endpt.length() - 1));
            results.close();
        } catch (java.sql.SQLException e) {
            System.out.println("Sql exception occurred: " + e);
            System.exit(0);
        }
        query = "update " + tableName + " set details = '" + startpt + "," + path + "," + endpt + "'";
        query += " where tlid=" + tlid;
        //System.out.println("The query is " + query);
        return query;
    }


    private String removeLeadingPlus(String ll) {
        if (ll.charAt(0) == '+')
            return ll.substring(1, ll.length());
        return ll;
    }

    public void insertIntoDatabase(String query) {
        try {
            int i = stmt.executeUpdate(query);
            if (i == 1) {
                countRows++;
                if ((countRows % 300) == 0)
                    print("Row " + countRows + " successfully inserted");
            } else
                print("could not complete insertion");
        } catch (Exception se) {
            se.printStackTrace();
        }
    }

    /******************************************************************
     The rest of the code are for testing
     ******************************************************************/

    private void printMatrix() {
        for (int i = 0; i < frmtdata.length; i++) {
            for (int j = 0; j < frmtdata[0].length; j++) {
                print("frmtdata[" + i + "][" + j + "] : " + frmtdata[i][j]);
            }
        }
    }

    private void print(String s) {
        System.out.println(s);
        System.out.flush();
    }

    //debugging constructor
    public DetailReader(String[] args, boolean b) {
        getArguments(args);
        tableName = getTableName(dataFile1);
        readFrmtFile(frmtFile);
        // printMatrix();
        readRawDataFile(true);
    }

    //debugging readRawDataFile (do not actually insert)
    private void readRawDataFile(boolean b) {
        try {
            BufferedReader br = new BufferedReader(new FileReader(dataFile1));
            String record, query;
            String[] attributes;
            while ((record = br.readLine()) != null) {
                attributes = parseRecord(record);
                query = createQuery(attributes);
                print("Query: " + query);
                if (countRows++ > 20) break;
            }
            print("\n" + countRows + " rows inserted from file " + dataFile1);
        } catch (Exception ie) {
            ie.printStackTrace();
        }

    }

    /******************************************************************
     The main program
     ******************************************************************/
    public static void main(String args[]) {

        // Reader r = new Reader(args, true);	// debug version
        DetailReader r = new DetailReader(args);
    }
}