package testUnitaire.database.migration.etape2;

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.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


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

import tool.iterator.Inside;
import tool.regEx.Adresse;

import database.dataset.Client;
import database.dataset.Depot;
import database.dataset.Employe;
import database.dataset.Facture;
import database.dataset.Fermeture;
import database.dataset.Groupe;
import database.dataset.Taxe;
import database.dataset.Telephone;
import database.dataset.Utilisateur;
import database.migration.SqlFactory;
import database.migration.etape1.client.NormalisedClient;
import database.migration.etape1.client.ReadClient;
import database.migration.etape1.depotEtFermeture.NormalisedDepotAndFermeture;
import database.migration.etape1.depotEtFermeture.ReadDepot;
import database.migration.etape1.depotEtFermeture.ReadFermeture;
import database.migration.etape2.facture.NormalisedFacture;
import database.migration.etape2.facture.ReadFacture;

public class testNormalisedFacture {
	private static Connection connection;
	private static NormalisedFacture factures;
	private static NormalisedClient clients;
	private static NormalisedDepotAndFermeture depotEtFermeture;

	@BeforeClass
	public static void init() throws FileNotFoundException, SQLException, IOException{
		connection = SqlFactory.connect(SqlFactory.loadProperties("hotel_orford_import.xml"));
		factures = ReadFacture.normaliseTaxe(ReadFacture.from(connection));
		/*dependance client Fermeture*/
		clients = NormalisedClient.from(ReadClient.loadFrom(connection));
		depotEtFermeture = NormalisedDepotAndFermeture.from(ReadFermeture.from(connection),ReadDepot.from(connection));
	}
	
	@AfterClass
	public static void purge() throws SQLException{
		connection.close();
	}
	
	@Test
	public void testIntegriterReferentielDeFactureTaxe() {
		Inside<Taxe, Facture> inside = new Inside<Taxe, Facture>() {

			@Override
			public boolean have(Iterable<Taxe> lst, Facture val) {
				for (Taxe tax : lst)
					if (tax.getTaxe_p_id()==val.getTaxe_fk_id())
						return true;
				return false;
			}
		};
		
		for (Facture facture: factures.getFactures())
			if (!inside.have(factures.getTaxes(), facture))
				fail("echec integrité facture no "+facture.getFacture_p_id()+" taxe "+facture.getTaxe_fk_id());
	}
	
	@Test
	public void testIntegriterReferentielDeFactureClient() {
		Inside<Client, Facture> inside = new Inside<Client, Facture>() {

			@Override
			public boolean have(Iterable<Client> lst, Facture val) {
				for (Client unit : lst){
					if (unit.getClient_p_id()==val.getClient_fk_id())
						return true;
				}
				return false;
			}
		};
		
		for (Facture facture: factures.getFactures())
			if (!inside.have(clients.getClients(), facture))
				fail("echec integrité facture no "+facture.getFacture_p_id()+" client "+facture.getClient_fk_id());
	}
	
	@Test
	public void testIntegriterReferentielDeFactureFermeture() {
		Inside<Fermeture, Facture> inside = new Inside<Fermeture, Facture>() {

			@Override
			public boolean have(Iterable<Fermeture> lst, Facture val) {
				for (Fermeture unit : lst)
					if (unit.getfermeture_p_id()==val.getFermeture_fk_id())
						return true;
				return false;
			}
		};
		
		for (Facture facture: factures.getFactures())
			if (!inside.have(depotEtFermeture.getFermetures(), facture))
				fail("echec integrité facture no "+facture.getFacture_p_id()+" Fermeture "+facture.getFermeture_fk_id());
	}
	
	@Ignore
	@Test
	public void testDorphelinFermeture(){
		Inside<Facture, Fermeture> inside = new Inside<Facture, Fermeture>() {

			@Override
			public boolean have(Iterable<Facture> lst, Fermeture val) {
				for (Facture facture : lst)
					if (facture.getFermeture_fk_id()==val.getfermeture_p_id())
						return true;
				return false;
			}
		};
		
		for (Fermeture fermeture : depotEtFermeture.getFermetures()){
			assertTrue("fermeture orpheline id "+fermeture.getfermeture_p_id(),
					   inside.have(factures.getFactures(), fermeture));
		}
	}
	
	@Test
	public void testDeFactureManquante() throws SQLException{
		
		Statement stm = connection.createStatement();
		ResultSet rs = stm.executeQuery("SELECT * FROM facture");
		while(rs.next()){
			int id = rs.getInt("ID");
			
			boolean ok=false;
			for (Facture facture : factures.getFactures())
				if (facture.getFacture_p_id()==id)
					ok=true;
			
			assertTrue("facture oublier no "+id,ok);
		}
	}
	
	@Test
	public void testNotZero_facture_client_fk_id(){
		for (Facture a : factures.getFactures()){
			assertFalse("echec not zero client de facture n " + a.getClient_fk_id(),a.getClient_fk_id()==0);
		}
	}
	
	@Test
	public void testNotZero_facture_fermeture_fk_id(){
		for (Facture a : factures.getFactures()){
			assertFalse("echec not zero fermeture de facture n " + a.getFermeture_fk_id(),a.getFermeture_fk_id()==0);
		}
	}
	
	@Test
	public void testNotZero_facture_taxe_fk_id(){
		for (Facture a : factures.getFactures()){
			assertFalse("echec not zero taxe de facture n " + a.getTaxe_fk_id(),a.getTaxe_fk_id()==0);
		}
	}
	
	@Test
	public void testNotZero_adresse_ville_fk_id(){
		for (Adresse a : clients.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 : clients.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 : clients.getGroupes()){
			assertFalse("echec not zero client de groupe n " + a.getClient_fk_id(),a.getClient_fk_id()==0);
		}
	}
	
	@Test
	public void testNotZero_telephone_client_fk_id(){
		for (Telephone a : clients.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 : clients.getTelephones()){
			assertFalse("echec not zero telephone type de telephone n " + a.getTelephone_type_fk_id(),a.getTelephone_type_fk_id()==0);
		}
	}
	
	@Test
	public void testNotZero_depot_employe_fk_id(){
		for (Depot a : depotEtFermeture.getDepots()){
			assertFalse("echec not zero employe de depot n " + a.getEmploye_fk_id(),a.getEmploye_fk_id()==0);
		}
	}
	
	@Test
	public void testNotZero_employe_utilisateur_fk_id(){
		for (Employe a : depotEtFermeture.getEmployes()){
			assertFalse("echec not zero utilisateur de employe n " + a.getUtilisateur_fk_id(),a.getUtilisateur_fk_id()==0);
		}
	}
	
	@Test
	public void testNotZero_fermeture_depot_fk_id(){
		for (Fermeture a : depotEtFermeture.getFermetures()){
			assertFalse("echec not zero depot de fermeture n " + a.getdepot_fk_id(),a.getdepot_fk_id()==0);
		}
	}
	
	@Test
	public void testNotZero_fermeture_employe_fk_id(){
		for (Fermeture a : depotEtFermeture.getFermetures()){
			assertFalse("echec not zero employe de fermeture n " + a.getemploye_fk_id(),a.getemploye_fk_id()==0);
		}
	}
	
	@Test
	public void testNotZero_utilisateur_groupeUtilisateur_fk_id(){
		for (Utilisateur a : depotEtFermeture.getUtilisateurs()){
			assertFalse("echec not zero groupe utilisateur de utilisateur n " + a.getGroupe_utilisateur_fk_id(),a.getGroupe_utilisateur_fk_id()==0);
		}
	}
}