/*
 * @author: Bansi Kotecha (simplified by Chee Yap for class)
 * @synopsis:
 *	This is a simplified version of the Reader.java program
 *	for illustration purposes.
 * @date: March 2003
 */

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

public class Reader 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., RT1
    private String[][] frmtdata;
    private int noOfAttributes;
    private int fieldsPerAttribute;
    private int countRows = 0;
    private Statement stmt;
    private char option;

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


    public void getArguments(String args[]){
	if(args[0].startsWith("-")) {
	    // In this simplified program, we assume only one option.
	    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() {
        int count = 0;
	BufferedReader br = null;
	try {
	    br = new BufferedReader(new FileReader(frmtFile));
	    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++;
			}
		}
	    }
	}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 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";
	if(given.equals("A"))
	    return "text";
	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.
     ******************************************************************/
    private void readRawDataFile() {
	//print("readRawDataFile() called");
	try {
	    BufferedReader br = new BufferedReader(new FileReader(dataFile));
	    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 " + 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();
	}
	return arrayOfAttributes;
    }

    /******************************************************************
      Quoting all roque characters in the raw input files: \'
     ******************************************************************/
    private String checkCharacters(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) {
	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 += Integer.parseInt(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) {
	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();
	}
    }
    
    /******************************************************************
      The rest of the code are for testing
     ******************************************************************/

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


    /******************************************************************
      The main program
     ******************************************************************/
    public static void main(String args[]){
	Reader r = new Reader(args);
    }
}