package database.crud.denormalised;

import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import tool.regEx.Adresse;

import database.Crud;
import database.dataset.Client;
import database.dataview.view_client;
import database.tools.DbConnection;

public class ViewClientCrud implements Crud<view_client> {
	private Connection connection;
	private Statement statement;

	/**
	 * @throws SQLException 
	 * 
	 */
	public ViewClientCrud(Connection connection) throws SQLException {
		this.connection = connection;
		this.statement = this.connection.createStatement();
	}

	@Override
	public int create(view_client dataset) throws SQLException {
		throw new UnsupportedOperationException();
	}

	@Override
	public view_client readById(int id) throws SQLException {
		String query = "SELECT * FROM view_client WHERE client_p_id='"+id+"'";
		ResultSet rs = statement.executeQuery(query);
		rs.next();
		
		//Construire le dataset � partir du resultset
		return new view_client(rs.getInt("client_p_id"), 
				               rs.getString("client_nom"), 
				               rs.getString("client_prenom"), 
				               rs.getString("client_commentaire"), 
				               rs.getInt("adresse_p_id"), 
				               rs.getString("adresse_no_rue_app"), 
				               rs.getString("adresse_codepostal"), 
				               rs.getInt("ville_p_id"), 
				               rs.getString("ville_nom"), 
				               rs.getString("province"), 
				               rs.getString("pays"));
	}

	@Override
	public List<view_client> readAll() throws SQLException {
		List<view_client> lst = new ArrayList<>();
		
		String query = "SELECT * FROM view_client LIMIT 0, 100";
		ResultSet rs = statement.executeQuery(query);
		
		while(rs.next()){
		    //Construire le dataset � partir du resultset
			view_client tmp = new view_client(rs.getInt("client_p_id"), 
								               rs.getString("client_nom"), 
								               rs.getString("client_prenom"), 
								               rs.getString("client_commentaire"), 
								               rs.getInt("adresse_p_id"), 
								               rs.getString("adresse_no_rue_app"), 
								               rs.getString("adresse_codepostal"), 
								               rs.getInt("ville_p_id"), 
								               rs.getString("ville_nom"), 
								               rs.getString("province"), 
								               rs.getString("pays"));
			lst.add(tmp);
		}
		
		return lst;
	}

	@Override
	public boolean update(view_client dataset) throws SQLException {
		throw new UnsupportedOperationException();
	}

	@Override
	public boolean destroy(view_client dataset) throws SQLException {
		throw new UnsupportedOperationException();
	}

	@Override
	public int getNewSequenceId() throws SQLException {
		throw new UnsupportedOperationException();
	}
	
	public List<view_client> readAllLike(String nom, 
			                             String prenom, 
			                             String adresse, 
			                             String codepostal, 
			                             String ville, 
			                             String province, 
			                             String pays, 
			                             String commentaire, 
			                             String telephone) throws SQLException {
		List<view_client> lstClient = new ArrayList<view_client>();
		
		String query = "SELECT * FROM view_client "+
		               "INNER JOIN telephone ON client_fk_id = client_p_id "+
					   "WHERE client_nom LIKE '%"+nom+"%' AND "+
					   "client_prenom LIKE '%"+prenom+"%' AND "+
					   "client_commentaire LIKE '%"+commentaire+"%' AND "+
					   "adresse_no_rue_app LIKE '%"+adresse+"%' AND " +
					   "adresse_codepostal LIKE '%"+codepostal+"%' AND " +
					   "ville_nom LIKE '%"+ville+"%' AND " +
					   "province LIKE '%"+province+"%' AND "+
					   "pays LIKE '%"+pays+"%' AND "+
					   "telephone_numero LIKE '%"+telephone+"%'" +
					   " GROUP BY client_p_id LIMIT 0,100";
		
		ResultSet rs = statement.executeQuery(query);
		
		while(rs.next()){
		//Construire le dataset � partir du resultset
			view_client tmp = new view_client(rs.getInt("client_p_id"), 
		               rs.getString("client_nom"), 
		               rs.getString("client_prenom"), 
		               rs.getString("client_commentaire"), 
		               rs.getInt("adresse_p_id"), 
		               rs.getString("adresse_no_rue_app"), 
		               rs.getString("adresse_codepostal"), 
		               rs.getInt("ville_p_id"), 
		               rs.getString("ville_nom"), 
		               rs.getString("province"), 
		               rs.getString("pays"));
			lstClient.add(tmp);
		}
		
		return lstClient;
	}

	public static void main(String ... arg) throws ClassNotFoundException, IOException, SQLException{
		Connection connection = DbConnection.createConnection();
		ViewClientCrud crud = new ViewClientCrud(connection);
		for (view_client v : crud.readAll())
			System.out.println(v.toString());
	}
}