package com.example.portfolio2;

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

/// standard Database configuration provided at lecture
class MyDB {

	/// foo
	Connection conn = null;

	/// foo
	MyDB() {
		if (conn == null) open();
	}

	/// foo
	public void open() {
		try {
			String url = "jdbc:sqlite:identifier.sqlite";
			conn = DriverManager.getConnection(url);
		} catch (SQLException e) {
			System.out.println("cannot open");
			if (conn != null) close();
			throw new RuntimeException(e);
		}
		;
	}

	/// foo
	public void close() {
		try {
			if (conn != null) conn.close();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		conn = null;
	}

	/// foo
	/// @param sql  foo
	public void cmd(String sql) {
		if (conn == null) open();
		if (conn == null) {
			System.out.println("No connection");
			return;
		}
		Statement stmt = null;
		try {
			stmt = conn.createStatement();
			stmt.executeUpdate(sql);
		} catch (SQLException e) {
			System.out.println("Error in statement " + sql);
			throw new RuntimeException(e);
		}
		try {
			if (stmt != null) {
				stmt.close();
			}
		} catch (SQLException e) {
			System.out.println("Error in statement " + sql);
			throw new RuntimeException(e);
		}
	}

	/// foo
	/// @param query  foo
	/// @param fld    foo
	/// @return       foo
	public ArrayList<String> query(String query, String fld) {
		ArrayList<String> res = new ArrayList<>();
		if (conn == null) open();
		if (conn == null) {
			System.out.println("No connection");
			throw new RuntimeException("No connection");
		}
		Statement stmt = null;
		try {
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			while (rs.next()) {
				String name = rs.getString(fld);
				res.add(name);
			}
		} catch (SQLException e) {
			System.out.println("Error in statement " + query + " " + fld);
			throw new RuntimeException(e);
		}
		try {
			if (stmt != null) {
				stmt.close();
			}
		} catch (SQLException e) {
			System.out.println("Error in statement " + query + " " + fld);
			throw new RuntimeException(e);
		}
		return res;
	}
}