package workerthreads.getmetadata;

import gui.MainFrameController;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.swing.SwingWorker;

import logging.Log;
import logging.LogLevel;
import database.DatabaseAccess;
import database.PostgresDatabaseAccess;

/**
 * get metadata of database
 * 
 * this class is used to produce a list with all attributes from all tables in
 * given database
 * 
 * @author Martin Pabst, 2009
 * 
 */
public class GetMetadataWorker extends SwingWorker<Void, String> {

	/**
	 * store controller object
	 */
	private MainFrameController controller;

	/**
	 * path to database in filesystem
	 */
	private String path;

	/**
	 * constructor, only stores controller
	 * 
	 * @param controller
	 */
	public GetMetadataWorker(MainFrameController controller) {
		super();
		this.controller = controller;

	}

	/**
	 * worker method
	 */
	@Override
	protected Void doInBackground() throws Exception {
		controller.setButtonsEnabled(false);
		controller.setWaitCursor(true);
		controller.enableProgressbar(true);
		controller.setProgressBarMaximum(-1);

		try {
			DatabaseAccess.getActiveDatabase(false).openConnection();
			printMetaData();
			DatabaseAccess.getActiveDatabase(false).closeConnection();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		controller.setWaitCursor(false);
		controller.setButtonsEnabled(true);
		controller.enableProgressbar(false);

		return null;
	}

	/**
	 * print out metadata for given database
	 * 
	 * @throws SQLException
	 */
	private void printMetaData() throws SQLException {

		ArrayList<String> tables = new ArrayList<String>();
		ArrayList<Attribute> attributes = new ArrayList<Attribute>();

		Connection connection = DatabaseAccess.getActiveDatabase(false)
				.getConnection();

		DatabaseMetaData dmd = connection.getMetaData();		
		
		
//		ResultSet rs1 = dmd.getSchemas();
//		
//		while (rs1.next()){
//			System.out.print(rs1.getString(1) + ": ");
//			System.out.println(rs1.getString(2));
//		}

		String catalog = null;
		String schema = null;
		String tableName = "SVP%";

		if (DatabaseAccess.getActiveDatabase(false) instanceof PostgresDatabaseAccess) {
			tableName = tableName.toLowerCase();
		}

		String[] types = null;//{ "TABLE" };

		ResultSet rs = dmd.getTables(catalog, schema, tableName, types);

		/**
		 * Resultset holds 5 Columns; column 3 is the tablename
		 */

		while (rs.next()) {

			String id = rs.getString(3);
			// Log.outl(id, LogLevel.useful);
			
			tables.add(id);
		}

		rs.close();

		/**
		 * first: only output tables
		 */
		
		Log.outl("Tabellen: ", LogLevel.useful);
		Log.outl("", LogLevel.useful);
		
		for(String table: tables){
			Log.outl(table, LogLevel.useful);
			
		}
		
		Log.outl("\n\n", LogLevel.useful);
		
		
		for (String table : tables) {

			catalog = null;
			schema = null;
			tableName = table;
			String columnName = null;

			dmd = connection.getMetaData();

			rs = dmd.getColumns(catalog, schema, tableName, columnName);

			while (rs.next()) {
				columnName = rs.getString(4);
				int dataType = rs.getInt(5);
				if (dataType == java.sql.Types.DATE) {
					Attribute at = new Attribute(table, columnName);
					Log.outl(table + "." + columnName, LogLevel.useful);
					attributes.add(at);
				}
			}

		}

		rs.close();

		Log.outl("", LogLevel.useful);

		for (String table : tables) {

			catalog = null;
			schema = null;
			tableName = table;
			String columnName = null;

			dmd = connection.getMetaData();

			rs = dmd.getColumns(catalog, schema, tableName, columnName);

			while (rs.next()) {
				columnName = rs.getString(4);
				if (columnName.compareToIgnoreCase("schuljahr_id") == 0
						|| columnName.compareToIgnoreCase("pkz") == 0) {
					Attribute at = new Attribute(table, columnName);
					Log.outl(table + "." + columnName, LogLevel.useful);
					attributes.add(at);
				}
			}

		}

		rs.close();

	}

}
