/*
 * @author: Bansi Kotecha (supervised by Chee Yap)
 */
import java.io.*;
import java.util.*;
import java.sql.*;

public class SReader extends RawDataReader {

    private final int fileExtLength = 3;
    private String frmtFile;	// e.g., RT1.fmt
    private String dataFile;	// e.g., TGR34017.RT1
    private String tableName;	// extracted from dataFile: e.g., TGR34017 
    private String[][] frmtdata;
    private int count = 0;
    private int noOfAttributes;
    private int FieldsPerAttributes;
    private int countRows = 0;
    private Statement stmt;
    private char option;

    public SReader(String[] args) {
	getArguments(args);
	tableName = getTableName(dataFile);
	readFrmtFile();
	
	try {
	    stmt = establishConnection().createStatement();
	} catch(SQLException se) {
	    se.printStackTrace();
	}
	
	if((option == 'c')  || (option == 'C'))
	    createTable();

	readRawDataFile();	// THE MAIN READING FUNCTION!
				// This reads each line of data file
				// and insert into database
    }

    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.dataFile = args[2];
	}
	else {
	    this.frmtFile = args[0];
	    this.dataFile = args[1];
	}

	
    }

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

    private void readFrmtFile() {
	//print("readFrmtFile() called");
	FileReader fr = null;
	BufferedReader br = null;
	try {
	    fr = new FileReader(frmtFile);
	    br = new BufferedReader(fr);
	    String line;
	    int i = 0;
	    while((line = br.readLine()) != null){
		if((line.startsWith("#")) || (line.length() == 0)) {
		    continue;		 
		}
		if(line.charAt(0) != ' '){
		    count++;
		    if(count == 1) {
			noOfAttributes = stringToInt(line.substring(0, 1).trim()); 
		    }
		    else if(count == 2) {
			FieldsPerAttributes = stringToInt(line.substring(0, 1).trim()); 
			frmtdata = new String[noOfAttributes][FieldsPerAttributes];
		    }
		    else {
			if(i != frmtdata.length) {
			    parseFrmtdata(line, i);
			    i++;
			}
		    }
		}
	    }
	}catch(Exception e){}
    }

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

    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 createTable() {
	try {
	    String query =  "create table " + tableName + "(";
	    for(int i = 0; i < frmtdata.length; i++) {
		if(i == (frmtdata.length - 1))
		    query += frmtdata[i][0] + " " + getDataTypeAsString(frmtdata[i][1]);
		else
		    query += frmtdata[i][0] + " " + getDataTypeAsString(frmtdata[i][1]) + ", ";
	    }
	    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";
	else
	    return "text";
    }

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

    /********************************************************************
	readRawDataFile() is the main reading function.
	This reads each line of data file
	and insert the information into the database
     ********************************************************************/

    private void readRawDataFile() {
	//print("readRawDataFile() called");
	try {
	    FileReader fr = new FileReader(dataFile);
	    BufferedReader br = new BufferedReader(fr);
	    String record;
	    String[] attributes;
	    String query;
	    while((record = br.readLine()) != null) {
		attributes = parseRecord(record);
		query = createQuery(attributes);
		insertIntoDatabase(query);
	    }
	    commit();
	    print("\n\n" + countRows + " rows inserted from file " + dataFile);
	}catch (Exception ie) {}
	
    }

    private String[] parseRecord(String record) {
	String[] arrayOfAttributes = new String[frmtdata.length];
	for(int i = 0; i < frmtdata.length; i++) {
	    arrayOfAttributes[i] = record.substring(Integer.parseInt(frmtdata[i][2]) - 1, Integer.parseInt(frmtdata[i][3])).trim();
	    //for(int j = 0; j < arrayOfAttri
	    ///if(arrayOfAttributes[i].charAt())
	}
	
	return arrayOfAttributes;
    }

    private String checkCharacters(String value) {
	//print("checkCharacters() called");
	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) {
	//print("createQuery() called");
	String query = "";
	    query += "insert into " + tableName + " values (";
	    for(int i = 0; i < frmtdata.length; i++) {
		if(i == (attributes.length - 1)) {
		    if(frmtdata[i][1].equals("N"))
			query += stringToInt(attributes[i]);
		    else {
			//attributes[i] = checkCharacters(attributes[i]);
			query += "'" + attributes[i] + "'";
		    }
		}
		else {
		    if(frmtdata[i][1].equals("N"))
			query += stringToInt(attributes[i]) + ",";
		    else {
			//attributes[i] = checkCharacters(attributes[i]);
			query += "'" + attributes[i] + "',";
		    }
		}
	    }
	    query += ")";
	return query;
    }

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

    private void print(String s) {
	System.out.println(s);
    }
    public static void main(String args[]){
	new SReader(args);

    }

}