import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

class DBController
{

	private Connection connection;

	private String DB_PATH_Linux;

	public String dbname = "tlcIndex";

	public void main()
	{
		try {
			connection = DriverManager
					.getConnection("jdbc:sqlite:" + DB_PATH_Linux + dbname + ".db");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public DBController(String projectPath)
	{
		this.DB_PATH_Linux = projectPath;
	}

	public void connectDatabase()
	{ // connect to database
		System.out.println("Verbinde ... DB name: " + dbname);
		try {
			if (connection != null)
				return;
			connection = DriverManager
					.getConnection("jdbc:sqlite:" + DB_PATH_Linux + dbname + ".db");
			if (!connection.isClosed())
				System.out.println("DB Datei-Verbindung erstellt");
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		Runtime.getRuntime().addShutdownHook(new Thread() {
			public void run()
			{
				try {
					if (!connection.isClosed() && connection != null) {
						connection.close();
						if (connection.isClosed())
							System.out.println();
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		});
		
		System.out.println("Connect geklappt");
		
	}

	public void createTableImages()
	{ // create table position
		System.out.println("Erstelle Tabelle images");
		try {
			Statement stmt = connection.createStatement();
			stmt.executeUpdate("DROP TABLE IF EXISTS images;");
			stmt.executeUpdate(
					"CREATE TABLE images (imageCounter, path, day, month, year, hour, minute, size, brightness);");
		} catch (SQLException e) {
			System.err.println("Couldn't handle DB-Query");
			e.printStackTrace();
			System.exit(-1);
		}
	}

	public void fillImages(int imageCounter, String path, int day, int month,
			int year, int hour, int minute, int size, int brightness)
	{ // create new data in table
		// System.out.println("Erstelle neuen images eintrag");
		try {
			PreparedStatement ps = connection.prepareStatement(
					"INSERT INTO images VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);");
			ps.setInt(1, imageCounter); // primary
			ps.setString(2, path);
			ps.setInt(3, day);
			ps.setInt(4, month);
			ps.setInt(5, year);
			ps.setInt(6, hour);
			ps.setInt(7, minute);
			ps.setInt(8, size);
			ps.setInt(9, brightness);
			ps.addBatch();
			connection.setAutoCommit(false);
			ps.executeBatch(); // SQL execution
			connection.setAutoCommit(true);
		} catch (SQLException e) {
			System.err.println("Couldn't handle DB-Query");
			e.printStackTrace();
		}
	}

	public List<Image> query(String pQuery)
	{
		List<Image> list = new ArrayList<Image>();

		try {
			Statement stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(pQuery);
			while (rs.next()) {
				list.add(new Image(rs.getInt("imageCounter"), rs.getString("path"),
						rs.getInt("day"), rs.getInt("month"), rs.getInt("year"),
						rs.getInt("hour"), rs.getInt("minute"), rs.getInt("size"),
						rs.getInt("brightness")));
			} // end while
				return list;
		} catch (SQLException e) {
			System.err.println("Couldn't handle DB-Query");
			e.printStackTrace();
			return null;
		}
	}

	public String getPathFromIndex(int pCounter)
	{
		try {
			Statement stmt = connection.createStatement();
			ResultSet rs = stmt
					.executeQuery("SELECT path FROM images WHERE imageCounter = "
							+ pCounter + " ;");
			return rs.getString("path");
		} catch (SQLException e) {
			System.err.println("Couldn't handle DB-Query");
			e.printStackTrace();
			return "Error 404";
		}
	}

	public int getHourFromIndex(int pCounter)
	{
		try {
			Statement stmt = connection.createStatement();
			ResultSet rs = stmt
					.executeQuery("SELECT hour FROM images WHERE imageCounter = "
							+ pCounter + " ;");
			return rs.getInt("hour");
		} catch (SQLException e) {
			System.err.println("Couldn't handle DB-Query");
			e.printStackTrace();
			return 404;
		}
	}

	public int getMinuteFromIndex(int pCounter)
	{
		try {
			Statement stmt = connection.createStatement();
			ResultSet rs = stmt
					.executeQuery("SELECT minute FROM images WHERE imageCounter = "
							+ pCounter + " ;");
			return rs.getInt("minute");
		} catch (SQLException e) {
			System.err.println("Couldn't handle DB-Query");
			e.printStackTrace();
			return 404;
		}
	}

	public int getDayFromIndex(int pCounter)
	{
		try {
			Statement stmt = connection.createStatement();
			ResultSet rs = stmt
					.executeQuery("SELECT day FROM images WHERE imageCounter = "
							+ pCounter + " ;");
			return rs.getInt("day");
		} catch (SQLException e) {
			System.err.println("Couldn't handle DB-Query");
			e.printStackTrace();
			return 404;
		}
	}

	public int getSizeFromIndex(int pCounter)
	{
		try {
			Statement stmt = connection.createStatement();
			ResultSet rs = stmt
					.executeQuery("SELECT size FROM images WHERE imageCounter = "
							+ pCounter + " ;");
			return rs.getInt("size");
		} catch (SQLException e) {
			System.err.println("Couldn't handle DB-Query");
			e.printStackTrace();
			return 404;
		}
	}

}