package localhost;
 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.sql.*; 
import javax.naming.*;

public class dataBase { 
	
	public static PreparedStatement ps;
	public static Connection conn;  
	public ResultSet rs;  

	public void povezi(){  

		//String host = "localhost";
		//String dbName = "vaja7";  
		//int port = 3306;
		//String mySqlUrl = "jdbc:mysql://" + host + ":" + port + "/" + dbName;
		
		try {
			// connection pooling
			Context ctx = new InitialContext();
			DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/mydb");
			conn = ds.getConnection();
		}
		catch (NamingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}  
		catch (SQLException e) { 
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	} //povezi 
	
	public void prekini(){
		if (conn == null) return;
		try {
			conn.close();
		}
		catch (SQLException e){
			e.printStackTrace();
		}
	} //prekini 
	
	public void dodajRezervacijo(Rezervacija rez) {
		String ime = rez.getIme();
		String priimek = rez.getPriimek();   
		povezi(); 
		try {
			ps = conn.prepareStatement("select count(*) from rezervacije where ime = ? AND priimek = ?"); 
			ps.setString(1, ime);
			ps.setString(2, priimek); 
			
			rs = ps.executeQuery();   
			rs.first();    
			
			if(rs.getInt(1) < 1) {    
				ps = conn.prepareStatement("insert into rezervacije values (?,?,?,?,?,?,?,?,?,?)"); 
				ps.setInt(1, 0);  
				ps.setString(2, rez.getAktivnost());   
				ps.setString(3, rez.getVrsta());
				ps.setString(4, rez.getTermin()); 
				ps.setString(5, rez.getLokacija()); 
				ps.setString(6, rez.getIme());
				ps.setString(7, rez.getPriimek());
				ps.setString(8, rez.getNaslov());
				ps.setInt(9, rez.getTelefon());
				ps.setString(10, rez.getEmail()); 
				ps.execute();         
				 
				System.out.printf("Rezervacija uspesno dodana v bazo.\n");     
			}
			else { 
				System.out.printf("Vnos rezervacije neuspesen.\n");  
				prekini();          
			}
		} 
		catch (SQLException e) {  
			// TODO: handle exception
			e.printStackTrace();  
		}
		finally {
			prekini(); 
		} 
	} //dodajRezervacijo 
	
	public void dodajMail(String email) {
		povezi();
		try {
			ps = conn.prepareStatement("select count(*) from mailing_lista where email = ?");  
			ps.setString(1, email);
			rs = ps.executeQuery();   
			rs.first();
			
			if(rs.getInt(1) < 1) {    
				ps = conn.prepareStatement("insert into mailing_lista values (?)");   
				ps.setString(1, email);    
				ps.execute();          
			}
			else {   
				prekini();           
			}
		}
		catch (SQLException e) {  
			// TODO: handle exception
			e.printStackTrace();  
		}
		finally {
			prekini();   
		}   
	} //dodajMail
	
	public void dodajKomentar(Komentar kom) {  
		povezi();
		try {
			ps = conn.prepareStatement("select count(*) from komentarji where komentar = ?");   
			ps.setString(1, kom.komentar);
			rs = ps.executeQuery();   
			rs.first();
			
			if(rs.getInt(1) < 1) {    
				ps = conn.prepareStatement("insert into komentarji values (?,?,?,?)");   
				ps.setInt(1, 0);
				ps.setString(2, kom.getIme());
				ps.setString(3, kom.getAktivnost());
				ps.setString(4, kom.getKomentar()); 
				ps.execute();            
			}
			else {   
				prekini();           
			}
		}
		catch (SQLException e) {  
			// TODO: handle exception
			e.printStackTrace();  
		}
		finally {
			prekini();   
		}   
	} //dodajKomentar 
	
	public String[] vrniAktivnosti() {
		ArrayList<String> list = new ArrayList<String>(); 
		povezi(); 
		try {
			ps = conn.prepareStatement("select aktivnost from rezervacije");   
			rs = ps.executeQuery();   
			if (rs.first()){ 
				ps = conn.prepareStatement("select aktivnost from rezervacije");
				rs = ps.executeQuery(); 
				
				while(rs.next()) {
					list.add(rs.getString(1));   
				}
			} 
		}
		catch (SQLException e) {   
			// TODO: handle exception
			e.printStackTrace();  
		} 
		finally {
			prekini();    
		}
		
		String[] sez = new String[list.size()]; 
		for(int i = 0; i < list.size(); i++)
			sez[i] = list.get(i);
		return sez;  
	} //vrniAktivnosti  
	
	public String[] vrniEmaile() {
		ArrayList<String> list = new ArrayList<String>(); 
		povezi(); 
		try {
			ps = conn.prepareStatement("select email from mailing_lista");   
			rs = ps.executeQuery();   
			if (rs.first()){ 
				ps = conn.prepareStatement("select email from mailing_lista");
				rs = ps.executeQuery();   
				
				while(rs.next()) {
					list.add(rs.getString(1));   
				}
			} 
		}
		catch (SQLException e) {   
			// TODO: handle exception
			e.printStackTrace();  
		}
		finally {
			prekini();    
		}
		
		String[] sez = new String[list.size()]; 
		for(int i = 0; i < list.size(); i++)
			sez[i] = list.get(i);
		return sez;       
	} //vrniAktivnosti   
	
	public String[] vrniKomentarje() {
		ArrayList<String> komentarji = new ArrayList<String>();
		povezi();
		try {
			ps = conn.prepareStatement("select * from komentarji");   
			rs = ps.executeQuery();   
			if (rs.first()){ 
				ps = conn.prepareStatement("select * from komentarji");
				rs = ps.executeQuery();  
				
				while(rs.next()) {
					komentarji.add(rs.getString(2));
					komentarji.add(rs.getString(3));
					komentarji.add(rs.getString(4)); 
				}
			} 
		}
		catch (SQLException e) {   
			// TODO: handle exception
			e.printStackTrace();  
		}
		finally {
			prekini();    
		}
		
		String[] sez = new String[komentarji.size()]; 
		for(int i = 0; i < komentarji.size(); i++)
			sez[i] = komentarji.get(i);
		return sez;    
	} //vrniKomentarje 
	
}