package it.unicam.pizzeria4.services;

import java.sql.*;
import java.util.Map;
import java.util.HashMap;
import java.util.ArrayList;

import it.unicam.pizzeria4.util.DatiPiatto;
import it.unicam.pizzeria4.util.DatiTavolo;

public class DBManager {
	private static DBManager instance;
	private String url;
	private String user;
	private String pwd;
	private Connection conn = null;

	private DBManager() {
	}

	public void setDBManager(String url, String user, String pwd) {
		this.url = url;
		this.user = user;
		this.pwd = pwd;
	}

	public static DBManager getInstance() {
		if (instance == null) {
			instance = new DBManager();
		}
		return instance;
	}

	private void connect() {
		try {
			Class.forName("org.postgresql.Driver");
		} catch (ClassNotFoundException e) {
			System.out.println("Where is your PostgreSQL JDBC Driver? " + "Include in your library path!");
			e.printStackTrace();
		}
		try {
			conn = DriverManager.getConnection(url, user, pwd);
			System.out.println("Database connected, ready to go!");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("Problems in opening a connection to the DB");
			e.printStackTrace();
		}
	}

	private void close() {
		try {
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("Problems in closing the connection to the DB");
			e.printStackTrace();
		}
	}

	public boolean DBtest() {
		Boolean result = true;
		try {
			if (conn == null || !conn.isClosed()) {
				connect();
				result = false;
			}
			DatabaseMetaData data = conn.getMetaData();
			System.out.println("Details on DBMS - " + data.getDatabaseProductName() + "\n" + "  version:  "
					+ data.getDriverMajorVersion() + "\n" + "  catalogs: " + data.getCatalogs().getCursorName() + "\n"
					+ "  schemas:  " + data.getSchemas().getRow() + "\n");
			close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	public int getTableCount(String table) {
		String SQL = "SELECT count(*) FROM " + table + ";";
		int count = 0;
		try (Connection conn = DriverManager.getConnection(url, user, pwd);
				Statement stmt = conn.createStatement();
				ResultSet rs = stmt.executeQuery(SQL);) {
			count = rs.getFetchSize();
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return count;
	}

	public ArrayList<DatiTavolo> getTavoli() {
		String SQL = "SELECT * FROM pizzeria4.tavolo;";
		int count = 0;
		ArrayList<DatiTavolo> tavoli = new ArrayList<DatiTavolo>();
		DatiTavolo tavolo;
		int i = 1;
		try (Connection conn = DriverManager.getConnection(url, user, pwd);
				Statement stmt = conn.createStatement();
				ResultSet rs = stmt.executeQuery(SQL);) {
//			rs.beforeFirst();
			while (rs.next()) {
				i = 1;
				tavolo = new DatiTavolo(rs.getString(i++), rs.getInt(i++), rs.getString(i));
				tavoli.add(tavolo);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return tavoli;
	}

	public ArrayList<DatiPiatto> setMenu(String menu) throws SQLException {
		ArrayList<DatiPiatto> datiPiatti = new ArrayList<DatiPiatto>();
		int i, j;
		i = j = 1;
		DatiPiatto dPiatto;
		String SQL = "SELECT p.disponibile, p.id, p.nome, p.course, mp.prezzo FROM pizzeria4.piatto p join pizzeria4.menu_piatto mp "
				+ "on p.id=mp.piattoid where mp.menucodice = '" + menu + "';";
		try (Connection conn = DriverManager.getConnection(url, user, pwd);
				Statement stmt = conn.createStatement();
				ResultSet rs = stmt.executeQuery(SQL);) {
			while (rs.next()) {
				i = 1;
				if (rs.getBoolean(i++)) {
					dPiatto = new DatiPiatto();
					dPiatto.setID(rs.getString(i++));
					dPiatto.setNome(rs.getString(i++));
					dPiatto.setTipo(rs.getInt(i++));
					dPiatto.setPrezzo(rs.getDouble(i));
					SQL = "SELECT i.codice, i.descrizione from pizzeria4.piatto_ingredienti pi "
							+ "join pizzeria4.ingredienti i on i.codice=pi.ingredienticodice where pi.piattoid = '"
							+ dPiatto.getID() + "';";
					Statement stmti = conn.createStatement();
					ResultSet rsi = stmti.executeQuery(SQL);
					ArrayList<String> als = new ArrayList<String>();
					String intolleranze = "";
					while (rsi.next()) {
						j = 1;
						SQL = "SELECT int.descrizione from pizzeria4.ingredienti_intolleranze ii "
								+ "join pizzeria4.intolleranze int on int.codice=ii.intolleranzecodice where ii.ingredienticodice = '"
								+ rsi.getString(j++) + "';";
						Statement stmtii = conn.createStatement();
						ResultSet rsii = stmtii.executeQuery(SQL);
						while (rsii.next()) {
							intolleranze = intolleranze + rsii.getString(1) + " ";
						}
						als.add(rsi.getString(j));
					}
					String[] ingredienti = new String[als.size()];
					for (int k = 0; k < ingredienti.length; k++) {
						ingredienti[k] = als.get(k);
					}
					dPiatto.setIngredienti(ingredienti);
					dPiatto.setIntolleranze(intolleranze);
					intolleranze = "";
					datiPiatti.add(dPiatto);
				}
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return datiPiatti;
	}

//	public Map<Integer, Company> getAll() {
//		HashMap<Integer, Company> result = new HashMap<Integer, Company>();
//		Company v;
//		Integer k;
//		int key;
//		String name;
//		int age;
//		String address;
//		double salary;
//		int i = 1;
//
//		String SQL = "SELECT * FROM company;";
//		try (Connection conn = DriverManager.getConnection(url, user, pwd);
//				Statement stmt = conn.createStatement();
//				ResultSet rs = stmt.executeQuery(SQL);) {
//			while (rs.next()) {
//				i = 1;
//				key = rs.getInt(i++);
//				name = rs.getString(i++);
//				age = rs.getInt(i++);
//				address = rs.getString(i++);
//				salary = rs.getDouble(i);
//				k = key;
//				v = new Company(key, name, age, address, salary);
//				result.put(k, v);
//			}
//			ResultSetMetaData rsmd = rs.getMetaData();
//			int col = rsmd.getColumnCount();
//			for (i = 1; i <= col; i++) {
//				System.out.println("Field " + i + " name: " + rsmd.getColumnLabel(i));
//			}
//		} catch (SQLException ex) {
//			ex.printStackTrace();
//		}
//		return result;
//	}

	/*
	 * public void createTableCompany(Connection c) { try { Statement stmt =
	 * c.createStatement(); String sql = "CREATE TABLE COMPANY " +
	 * "(ID INT PRIMARY KEY     NOT NULL," + " NAME           TEXT    NOT NULL, " +
	 * " AGE            INT     NOT NULL, " + " ADDRESS        CHAR(50), " +
	 * " SALARY         REAL)"; stmt.executeUpdate(sql); stmt.close(); } catch
	 * (Exception e) { System.err.println(e.getClass().getName() + ": " +
	 * e.getMessage()); System.exit(0); }
	 * 
	 * }
	 */
}
