package testUnitaire.database.migration.etape1;

import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertTrue;
import static org.junit.Assert.fail;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashSet;
import java.util.Set;

import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;

import tool.regEx.Adresse;
import tool.regEx.DataNormalizer;
import database.dataset.Client;
import database.dataset.Groupe;
import database.dataset.Telephone;
import database.dataset.Ville;
import database.migration.SqlFactory;
import database.migration.etape1.client.NormalisedClient;
import database.migration.etape1.client.ReadClient;

public class testNormalisedClient{
	private static java.sql.Connection connection;
	private static NormalisedClient normalisedClient;
	
	@BeforeClass
	public static void init() throws FileNotFoundException, SQLException, IOException{
		connection = SqlFactory.connect(SqlFactory.loadProperties("hotel_orford_import.xml"));
		normalisedClient = NormalisedClient.from(ReadClient.loadFrom(connection));
	}
	
	@AfterClass
	public static void purge() throws SQLException{
		connection.close();
	}
	
	@Test
	public void testUnClientManquant() throws SQLException {
		System.out.println("verification de tout les clients de la BD orginale");
		Statement stm = connection.createStatement();
		ResultSet rs = stm.executeQuery("SELECT ID, nom,prenom FROM client");
		
		while (rs.next()){
			String nom = DataNormalizer.normalize(rs.getString("nom")),
				   prenom = DataNormalizer.normalize(rs.getString("prenom"));
			int id = rs.getInt("ID");
			boolean found = false;
			for (Client cli : normalisedClient.getClients()){
				if (cli.getClient_p_id()==id && cli.getClient_nom().equals(nom) && cli.getClient_prenom().equals(prenom)){
					found=true;
					break;
				}
			}
			assertTrue("ne trouve pas "+id+" "+prenom +" "+nom,found);
			//System.out.println("trouvé : " +prenom+" "+nom);
		}
		System.out.println("success");
	}
	
	@Test
	public void testUneAdresseManquante() throws SQLException {
		System.out.println("verification de toutes les adresses de la BD orginale");
		Statement stm = connection.createStatement();
		ResultSet rs = stm.executeQuery("SELECT Adresse FROM client");
		
		while (rs.next()){
			String rue = DataNormalizer.normalize(rs.getString("Adresse"));
			boolean found = false;
			for (Adresse adr : normalisedClient.getAdresses()){
				if (adr.getadresse_no_rue_app().equals(rue)){
					found=true;
					break;
				}
			}
			assertTrue("ne trouve pas "+rue,found);
			//System.out.println("trouvé : " +prenom+" "+nom);
		}
		System.out.println("success");
	}

	@Test
	public void testUnCodePostalManquant() throws SQLException {
		System.out.println("verification de touts les codepostals de la BD orginale");
		Statement stm = connection.createStatement();
		ResultSet rs = stm.executeQuery("SELECT codePostal FROM client");
		
		while (rs.next()){
			String rue = DataNormalizer.normalize(rs.getString("codePostal"));
			boolean found = false;
			for (Adresse adr : normalisedClient.getAdresses()){
				if (adr.getAdresse_codepostal().equals(rue)){
					found=true;
					break;
				}
			}
			assertTrue("ne trouve pas "+rue,found);
		}
		System.out.println("success");
	}
	
	@Test
	public void testUnTelephoneMaisonManquant() throws SQLException {
		System.out.println("verification de toutes les telephones domestique de la BD orginale");
		Statement stm = connection.createStatement();
		ResultSet rs = stm.executeQuery("SELECT telephoneMaison FROM client");
		
		while (rs.next()){
			String no = DataNormalizer.normalize(rs.getString("telephoneMaison"));
			if (no!=null && !no.equals("")){
				boolean found = false;
				for (Telephone tel : normalisedClient.getTelephones()){
					if (tel.getTelephone_numero().equals(no)){
						found=true;
						break;
					}
				}
				assertTrue("ne trouve pas "+no,found);
			}
		}
		System.out.println("success");
	}
	
	@Test
	public void testUnTelephoneTravailManquant() throws SQLException {
		System.out.println("verification de toutes les telephones au travail de la BD orginale");
		Statement stm = connection.createStatement();
		ResultSet rs = stm.executeQuery("SELECT telephoneTravail FROM client");
		
		while (rs.next()){
			String no = DataNormalizer.normalize(rs.getString("telephoneTravail"));
			if (no!=null && !no.equals("")){
				boolean found = false;
				for (Telephone tel : normalisedClient.getTelephones()){
					if (tel.getTelephone_numero().equals(no)){
						found=true;
						break;
					}
				}
				assertTrue("ne trouve pas "+no,found);
			}
		}
		System.out.println("success");
	}
	
	@Test
	public void testUneVilleManquante() throws SQLException {
		System.out.println("verification de toutes les villes de la BD orginale");
		Statement stm = connection.createStatement();
		ResultSet rs = stm.executeQuery("SELECT ville FROM client");
		
		while (rs.next()){
			String nomville = DataNormalizer.normalize(rs.getString("ville"));
			boolean found = false;
			for (Ville nom : normalisedClient.getVilles()){
				if (nom.getVille_nom().equals(nomville)){
					found=true;
					break;
				}
			}
			assertTrue("ne trouve pas "+nomville,found);
		}
		System.out.println("success");
	}
	
	@Test
	public void testDeDuplicationDeCleVille(){
		Set<Integer> cleTrouve = new HashSet<Integer>();
		for (Ville v : normalisedClient.getVilles())
			if (cleTrouve.contains(v.getville_p_id()))
				fail("cle de ville no " + v.getville_p_id());
			else
				cleTrouve.add(v.getville_p_id());
	}
	
	@Test
	public void testDeDuplicationDeCleAdresse(){
		Set<Integer> cleTrouve = new HashSet<Integer>();
		for (Adresse v : normalisedClient.getAdresses())
			if (cleTrouve.contains(v.getadresse_p_id()))
				fail("cle d Adresse no " + v.getadresse_p_id());
			else
				cleTrouve.add(v.getadresse_p_id());
	}
	
	@Test
	public void testDeDuplicationDeCleTelephone(){
		Set<Integer> cleTrouve = new HashSet<Integer>();
		for (Telephone v : normalisedClient.getTelephones())
			if (cleTrouve.contains(v.getTelephone_p_id()))
				fail("cle de telephone no " + v.getTelephone_p_id());
			else
				cleTrouve.add(v.getTelephone_p_id());
	}
	
	@Test
	public void testDeDuplicationDeCleGroupe(){
		Set<Integer> cleTrouve = new HashSet<Integer>();
		for (Groupe v : normalisedClient.getGroupes())
			if (cleTrouve.contains(v.getgroupe_p_id()))
				fail("cle de groupe no " + v.getgroupe_p_id());
			else
				cleTrouve.add(v.getgroupe_p_id());
	}
	
	@Test
	public void testDeDuplicationDeCleClient(){
		Set<Integer> cleTrouve = new HashSet<Integer>();
		for (Client v : normalisedClient.getClients())
			if (cleTrouve.contains(v.getClient_p_id()))
				fail("cle de client no " + v.getClient_p_id());
			else
				cleTrouve.add(v.getClient_p_id());
	}
	
	@Test
	public void testDintegriteReferentielleAdresseVille() throws SQLException{
		for (Adresse a : normalisedClient.getAdresses()){
			boolean ok = false;
			for (Ville v : normalisedClient.getVilles()){
				if (a.getVille_fk_id()==v.getville_p_id()){
					ok=true;
					break;
				}
			}
			if(!ok)
				fail("Adresse pointe vers une ville inexistante , adresse_no:"+a.getadresse_p_id() +" ville_no"+a.getVille_fk_id());
		}
	}
	
	@Test
	public void testDintegriteReferentielleClientAdresse() throws SQLException{
		for (Client c : normalisedClient.getClients()){
			boolean ok = false;
			for (Adresse a : normalisedClient.getAdresses()){
				if (c.getAdresse_fk_id()==a.getadresse_p_id()){
					ok=true;
					break;
				}
			}
			if(!ok)
				fail("client pointe vers une Adresse inexistante , adresse_no:"+c.getAdresse_fk_id() +" client_no"+c.getClient_p_id());
		}
	}
	
	@Test
	public void testDintegriteReferentielleTelephoneClient() throws SQLException{
		for (Telephone t : normalisedClient.getTelephones()){
			boolean ok = false;
			for (Client c : normalisedClient.getClients()){
				if (c.getClient_p_id()==t.getClient_fk_id()){
					ok=true;
					break;
				}
			}
			if(!ok)
				fail("telephone pointe vers un client inexistant , client no:"+t.getClient_fk_id() +" telephone no"+t.getTelephone_p_id());
		}
	}
	
	@Test
	public void testDintegriteReferentielleGroupeClient()
	{
		for (Groupe g : normalisedClient.getGroupes()){
			boolean ok = false;
			for (Client c : normalisedClient.getClients()){
				if (c.getClient_p_id()==g.getClient_fk_id()){
					ok=true;
					break;
				}
			}
			if (!ok)
				fail("groupe pointe vers un client inexistant, groupe:" + g.getgroupe_p_id() + " client:"+g.getClient_fk_id());
		}
	}
	
	@Test
	public void testDeVilleOrpheline(){
		for (Ville v : normalisedClient.getVilles()){
			boolean ok = false;
			for (Adresse a : normalisedClient.getAdresses()){
				if (a.getVille_fk_id() == v.getville_p_id()){
					ok = true;
					break;
				}
			}
			if (!ok)
				fail("ville orpheline : "+v.getVille_nom());
		}
	}
	
	@Test
	public void testDadresseOrpheline(){
		for (Adresse a : normalisedClient.getAdresses()){
			boolean ok = false;
			for (Client c : normalisedClient.getClients()){
				if (c.getAdresse_fk_id() == a.getadresse_p_id()){
					ok = true;
					break;
				}
			}
			if (!ok)
				fail("address orpheline : " + a.getadresse_no_rue_app());
		}
	}
	
	@Test
	public void testDeTelephoneOrphelin(){
		for (Telephone t : normalisedClient.getTelephones()){
			boolean ok = false;
			for (Client c : normalisedClient.getClients()){
				if (c.getClient_p_id() == t.getClient_fk_id()){
					ok = true;
					break;
				}
			}
			if (!ok)
				fail("telephone orphelin : " + t.getTelephone_numero());
		}
	}
	
	@Test
	public void testDeGroupeOrpheline(){
		for (Groupe t : normalisedClient.getGroupes()){
			boolean ok = false;
			for (Client c : normalisedClient.getClients()){
				if (c.getClient_p_id() == t.getClient_fk_id()){
					ok = true;
					break;
				}
			}
			if (!ok)
				fail("groupe orphelin : " + t.getGroupe_nom());
		}
	}
	
	@Test
	public void testNotZero_adresse_ville_fk_id(){
		for (Adresse a : normalisedClient.getAdresses()){
			assertFalse("echec not zero ville de adresse n " + a.getVille_fk_id(),a.getVille_fk_id()==0);
		}
	}
	
	@Test
	public void testNotZero_client_adresse_fk_id(){
		for (Client a : normalisedClient.getClients()){
			assertFalse("echec not zero adresse de client n " + a.getAdresse_fk_id(),a.getAdresse_fk_id()==0);
		}
	}
	
	@Test
	public void testNotZero_groupe_client_fk_id(){
		for (Groupe a : normalisedClient.getGroupes()){
			assertFalse("echec not zero client de goupe n " + a.getClient_fk_id(),a.getClient_fk_id()==0);
		}
	}
	
	@Test
	public void testNotZero_telephone_client_fk_id(){
		for (Telephone a : normalisedClient.getTelephones()){
			assertFalse("echec not zero client de telephone n " + a.getClient_fk_id(),a.getClient_fk_id()==0);
		}
	}
	
	@Test
	public void testNotZero_telephone_telephoneType_fk_id(){
		for (Telephone a : normalisedClient.getTelephones()){
			assertFalse("echec not zero telephone type de telephone n " + a.getTelephone_type_fk_id(),a.getTelephone_type_fk_id()==0);
		}
	}
}