package assign3;


import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.swing.table.AbstractTableModel;

import com.mysql.jdbc.Statement;

public class MyDB extends AbstractTableModel{

	private static String user = MyDBInfo.MYSQL_USERNAME;
	private static String password = MyDBInfo.MYSQL_PASSWORD;
	private static String server = MyDBInfo.MYSQL_DATABASE_SERVER;
	private static String database = MyDBInfo.MYSQL_DATABASE_NAME;

	private Statement state;
	private ResultSet set;

	public MyDB() {
		getConnectionStatement();
		//InputStreamReader reader = new InputStreamReader(metropolises.sql);
	}

	private void getConnectionStatement() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			try {
				Connection con = DriverManager.getConnection("jdbc:mysql://"+server, user, password);
				state = (Statement) con.createStatement();
				state.executeQuery("use "+database);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	public static final char GREATER = '>';
	public static final char LESS = '<';
	public static final char EQUAL = '=';
	public static final int EMPTY_POPULATION = -1;



	public void search(String metropolis, String continent, int population, char greater, boolean exact) {
		String selectString = getSelectString(metropolis, continent, population, greater, exact);
		try {
			set = state.executeQuery(selectString);
			fireTableDataChanged();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	private String getSelectString(String metropolis, String continent, int population, char greater, boolean exact) {
		String ex = "%";
		if(exact) ex = "";
		if(metropolis.length()==0) {
			metropolis = "metropolis";
		}else metropolis = "\""+ex+metropolis+ex+"\"";
		if(continent.length()==0) {
			continent = "continent";
		}else continent = "\""+ex+continent+ex+"\"";
		String result = "select * from metropolises where metropolis like "+metropolis+" and continent like "+continent;
		if(population!=EMPTY_POPULATION)
			result += " and population"+greater+population;
		return result;
	}

	public void add(String metropolis, String continent, int population) {
		String insertString = "insert into metropolises values("+"\""+metropolis+"\","+"\""+continent+"\","+population+")";
		try {
			state.executeUpdate(insertString);
			search(metropolis, continent, population, MyDB.EQUAL, true);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void main(String[] args) {
//		MyDB db = new MyDB();
//		search("mumbai", "Asia", 5, '0', false);
//		try {
//			result.first();
//		} catch (SQLException e) {
//			e.printStackTrace();
//		}
//		try {
//			System.out.println(result.getString(2));
		System.out.println("dradadam");
//		} catch (SQLException e) {
//			e.printStackTrace();
//		}
//		db.add("xashuri", "chixirtmuli", 88);
//
	}

	@Override
	public Object getValueAt(int rowIndex, int columnIndex) {
		Object result = null;
		try {
			set.absolute(rowIndex+1);
			result = set.getObject(columnIndex+1);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}

	@Override
	public int getRowCount() {
		int result = 0;
		try {
			if(set != null) {
				set.last();
				result = set.getRow();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	private static final int COLUMN_COUNT = 3;
	@Override
	public int getColumnCount() {
			int result = COLUMN_COUNT;
			if(set!=null) {
				try {
					result = set.getMetaData().getColumnCount();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			return result;

	}
}