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 db.Connect;

/**
 *
 * @author sqlitetutorial.net
 */
public class DbStart {
     /**
     * 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.");
            /*
            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();
        	
        	
        	//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);
            	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;
    
        
    }
    /**
     * @param args the command line arguments
     */
    //public static void main(String[] args) {
    //    connect();
    //}
}