package db;

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.Arrays;

import org.json.JSONException;
import org.json.JSONObject;

import db.Connect;

/**
 *
 * @author sqlitetutorial.net
 */
public class DBC {
	/**
	 * Connect to a sample database
	 */
	// static Connection conn = null;
	/*
	 * public static void connect() {
	 * 
	 * try { // db parameters String url = "jdbc:sqlite:DBPissirDomain.db"; create a
	 * connection to the database conn = DriverManager.getConnection(url);
	 * 
	 * System.out.println("Connection to SQLite has been established.");
	 * 
	 * System.out.println("OIOI"); } catch (SQLException e) {
	 * System.out.println(e.getMessage()); } finally { try { if (conn != null) {
	 * conn.close(); } } catch (SQLException ex) {
	 * System.out.println(ex.getMessage()); } } }
	 */
	public static ArrayList<Dominio> domainsGet() throws SQLException {
		// connect();

		/**
		 * Connect to a sample database
		 */
		// public static Connection conn = null;

		Connection conn = null;
		try {
			// db parameters
			String url = "jdbc:sqlite:DBPissirDomain.db";
			// create a connection to the database
			conn = DriverManager.getConnection(url);

			System.out.println("Connection to SQLite has been established.");
			/*
			 * String sql= "SELECT * FROM Utenti"; Statement statement
			 * =conn.createStatement(); ResultSet result =statement.executeQuery(sql); while
			 * (result.next()) { String name=result.getString("User");
			 * 
			 * String desc=result.getString("Desc"); System.out.println(name+desc); }
			 */

			String sql = "SELECT * FROM Domini";
			Statement statement = conn.createStatement();
			ResultSet result = statement.executeQuery(sql);
			System.out.println("57");
			ArrayList<Dominio> res = new ArrayList<Dominio>();
			while (result.next()) {
				System.out.println("60");
				String dom = result.getString("Domain");
				String desc = result.getString("Desc");

				// String users=result.getString("Users");
				ArrayList<String> users = new ArrayList<String>(Arrays.asList(result.getString("Users").split(",")));
				ArrayList<String> reso = new ArrayList<String>(Arrays.asList(result.getString("Resources").split(",")));
				// String reso=result.getString("Resources");

				String state = result.getString("State");
				ArrayList<String> serv = new ArrayList<String>(Arrays.asList(result.getString("Services").split(",")));
				// String serv=result.getString("Services");
				Dominio d = new Dominio(dom, desc, users, reso, state, serv);
				res.add(d);
				System.out.println("74");

			}
			return res;

		} catch (SQLException e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException ex) {
				System.out.println(ex.getMessage());
			}
		}
		return null;

	}

	public static Dominio getDom(String dm) throws SQLException {
		// connect();
		// Statement statement =conn.createStatement();

		Connection conn = null;
		try {
			// db parameters
			String url = "jdbc:sqlite:DBPissirDomain.db";
			// create a connection to the database
			conn = DriverManager.getConnection(url);

			System.out.println("Connection to SQLite has been established: getDom");
			/*
			 * String sql= "SELECT * FROM Utenti"; Statement statement
			 * =conn.createStatement(); ResultSet result =statement.executeQuery(sql); while
			 * (result.next()) { String name=result.getString("User");
			 * 
			 * String desc=result.getString("Desc"); System.out.println(name+desc); }
			 */
			
			
			PreparedStatement prepared = conn.prepareStatement("SELECT * FROM Domini WHERE Domain = ?");
			prepared.setString(1, dm);
			ResultSet result = prepared.executeQuery();
			System.out.println("DBCISONO1");
			
			// String sql= "SELECT * FROM Domini WHERE Domain = ?";

			// ResultSet result =statement.executeQuery(sql);
			// Dominio res=new Dominio();
			
			while (result.next()) {
				System.out.println("DBCISONO2");
				String dom = result.getString("Domain");
				String desc = result.getString("Desc");

				// String users=result.getString("Users");
				ArrayList<String> users = new ArrayList<String>(Arrays.asList(result.getString("Users").split(",")));
				ArrayList<String> reso = new ArrayList<String>(Arrays.asList(result.getString("Resources").split(",")));
				// String reso=result.getString("Resources");

				String state = result.getString("State");
				ArrayList<String> serv = new ArrayList<String>(Arrays.asList(result.getString("Services").split(",")));
				// String serv=result.getString("Services");
				Dominio d = new Dominio(dom, desc, users, reso, state, serv);
				// res.add(d);
				System.out.println(d.toString());
				return d;
			}
			//return null;

		} catch (SQLException e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException ex) {
				System.out.println(ex.getMessage());
			}
		}
		return null;

	}

	public static void delDom(String dm) throws SQLException {

		Connection conn = null;
		try {
			// db parameters
			String url = "jdbc:sqlite:DBPissirDomain.db";
			// create a connection to the database
			conn = DriverManager.getConnection(url);

			System.out.println("Connection to SQLite has been established: delDom");

			PreparedStatement prepared = conn.prepareStatement("DELETE FROM Domini WHERE Domain = ?");
			prepared.setString(1, dm);
			int result = prepared.executeUpdate();

		} catch (SQLException e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException ex) {
				System.out.println(ex.getMessage());
			}
		}
		// return false;

	}
	
	
	public static void insertDom(JSONObject j) throws SQLException, JSONException {
		// connect();
		// Statement statement =conn.createStatement();

		Connection conn = null;
		try {
			// db parameters
			String url = "jdbc:sqlite:DBPissirDomain.db";
			// create a connection to the database
			conn = DriverManager.getConnection(url);

			System.out.println("Connection to SQLite has been established.");
			/*
			 * String sql= "SELECT * FROM Utenti"; Statement statement
			 * =conn.createStatement(); ResultSet result =statement.executeQuery(sql); while
			 * (result.next()) { String name=result.getString("User");
			 * 
			 * String desc=result.getString("Desc"); System.out.println(name+desc); }
			 */
/*
			String dom=j.getString("domain");
			String desc=j.getString("desc");
			String usr=j.getString("users");
			String rsc=j.getString("resources");
			String srv=j.getString("services");
			
			String sql = "INSERT INTO Domini (Domain, Desc, Users, Resources, State, Services) VALUES ("+dom+","+desc+","+usr+","+rsc+","+"N/A"+","+srv+")";
			Statement statement = conn.createStatement();
			ResultSet result = statement.executeQuery(sql);
			
	*/		
			
			PreparedStatement prepared = conn.prepareStatement("INSERT INTO Domini (Domain, Desc, Users, Resources, State, Services) VALUES (?1,?2,?3,?4,?5,?6)");
			
			System.out.println(j.getString("domain"));
			System.out.println(j.getString("desc"));
			System.out.println(j.getString("users"));
			System.out.println(j.getString("services"));
			System.out.println(j.getString("resources"));
			
			
			prepared.setString(1, j.getString("domain").toString().trim());
			prepared.setString(2, j.getString("desc").toString().trim());
			prepared.setString(3, j.getString("users").toString().trim());
			prepared.setString(4, j.getString("resources").toString().trim());
			prepared.setString(5, "N/A".toString());
			prepared.setString(6, j.getString("services").toString().trim());
			int result = prepared.executeUpdate();
			
			//System.out.println(result.toString());

			// String sql= "SELECT * FROM Domini WHERE Domain = ?";

			// ResultSet result =statement.executeQuery(sql);
			// Dominio res=new Dominio();
			
			
			/*
			while (result.next()) {

				String dom = result.getString("Domain");
				String desc = result.getString("Desc");

				// String users=result.getString("Users");
				ArrayList<String> users = new ArrayList<String>(Arrays.asList(result.getString("Users").split(",")));
				ArrayList<String> reso = new ArrayList<String>(Arrays.asList(result.getString("Resources").split(",")));
				// String reso=result.getString("Resources");

				String state = result.getString("State");
				ArrayList<String> serv = new ArrayList<String>(Arrays.asList(result.getString("Services").split(",")));
				// String serv=result.getString("Services");
				Dominio d = new Dominio(dom, desc, users, reso, state, serv);
				// res.add(d);
				
			}
			
	*/
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException ex) {
				System.out.println(ex.getMessage());
			}
		}
		

	}


	/**
	 * @param args the command line arguments
	 */
	// public static void main(String[] args) {
	// connect();
	// }
}