package testUnitaire.database.migration.etape3;

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.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Set;

import junit.framework.Assert;

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

import database.dataset.Article;
import database.dataset.Categorie_article;
import database.dataset.Facture;
import database.dataset.Location_chambre;
import database.dataset.Reservation;
import database.migration.SqlFactory;
import database.migration.etape0.categorie_article.ReadCategorieStock;
import database.migration.etape1.article.ReadStock;
import database.migration.etape1.chambre_reserv_reparation.NormalisedChambre;
import database.migration.etape1.chambre_reserv_reparation.ReadChambre;
import database.migration.etape2.facture.NormalisedFacture;
import database.migration.etape2.facture.ReadFacture;
import database.migration.etape3.reservationVente.ReadVente;

/**
 * @author martin
 * test l'importation de
 * vente => vente, location_chambre, vente_article
 * stock => Article
 * categorie => categorie_article
 */
public class testVente{
	private static Connection connection;
	private static Set<Categorie_article> _categorie_article_lst;
	private static Set<Article> _article_lst;
	private static NormalisedFacture _facture_lst;
	private static List<ReadVente> _vente_lst;
	private static NormalisedChambre _chambre_lst;
	
	@BeforeClass
	public static void connecte() throws FileNotFoundException, SQLException, IOException{
		connection = SqlFactory.connect(SqlFactory.loadProperties("hotel_orford_import.xml"));
		
		System.out.println("dependance importation des categories d'articles");
		_categorie_article_lst = ReadCategorieStock.from(connection);
		System.out.println("dependance importation des articles");
		_article_lst = ReadStock.from(connection);
		System.out.println("dependance importation des facture");
		_facture_lst =  ReadFacture.normaliseTaxe(ReadFacture.from(connection));
		System.out.println("dependance reservation chambre");
		_chambre_lst = NormalisedChambre.make(ReadChambre.from(connection));
		
		System.out.println("importation des ventes");
		_vente_lst = ReadVente.from(connection);
	}
	
	@AfterClass
	public static void deconnecte() throws SQLException{
		connection.close();
	}
	
	@Test
	public void testDansLaBaseDeDonneeOriginaleQueToutesLesDatesSontAuxFormatSQL() throws SQLException {
		Statement stm = connection.createStatement();
		ResultSet rs = stm.executeQuery("SELECT date FROM ventes");
		
		while(rs.next()){
			String dateStr = rs.getString("date");
			try{
				//System.out.println(dateStr);
				Date.valueOf(dateStr);
			}catch(IllegalArgumentException e){
				fail("date non valide : " + dateStr);
			}
		}
	}
	
	private boolean _categorie_article_contient(Set<Categorie_article> lst, int id){
		for (Categorie_article ca : lst)
			if (ca.getcategorie_article_p_id() == id)
				return true;
		return false;
	}
	@Test
	public void verifierIntegriteReferentielEntreArticleEtCategorieArticle(){
		for (Article a : _article_lst){
			if (!_categorie_article_contient(_categorie_article_lst, a.getCategorie_fk_id()))
				fail("integriter référentiel non présente " + a.getCategorie_fk_id());
		}
	}
	
	@Test
	public void testNotZero_categorie_fk_id(){
		for (Article a : _article_lst){
			assertFalse("echec not zero categorie n " + a.getCategorie_fk_id(),a.getCategorie_fk_id()==0);
		}
	}
	
	@Test
	public void verifier_que_rien_na_ete_oublie() throws SQLException{
		Statement stm = connection.createStatement();
		ResultSet rs = stm.executeQuery("SELECT ID FROM ventes");
		while(rs.next()){
			boolean ok = false;
			for (ReadVente r : _vente_lst){
				if (r.get_vente().getVente_p_id()==rs.getInt("ID")){
					ok=true;
				}
			}
			//System.out.println(ok + "vente "+ rs.getInt("ID"));
			Assert.assertTrue("missing vente n " + rs.getInt("ID"), ok);
		}
	}
	
	@Test
	public void verifier_quil_ny_a_pas_de_zero_dans_facture_fk_id(){
		for (ReadVente r : _vente_lst){
			assertFalse("facture fk = 0 a la vente "+r.get_vente().getVente_p_id(),r.get_vente().getFacture_fk_id()==0);
		}
	}
	
	@Test
	public void verifier_lintegrite_referentielle_dans_facture_fk_id(){
		for (ReadVente r : _vente_lst){
			boolean ok = false;
			for (Facture f : _facture_lst.getFactures())
				if (f.getFacture_p_id()==r.get_vente().getFacture_fk_id())
					ok=true;
			assertTrue("echec facture " + r.get_vente().getFacture_fk_id(),ok);
		}
	}
		
	@Test
	public void verifier_quil_ny_a_pas_de_zero_dans_article_fk_id(){
		for (ReadVente r : _vente_lst){
			assertFalse("article fk = 0 a la vente "+r.get_vente().getVente_p_id(),r.get_vente().getArticle_fk_id()==0);
		}
	}
	
	@Test
	public void verifier_lintegrite_referentielle_dans_article_fk_id(){
		for (ReadVente r : _vente_lst){
			boolean ok = false;
			for (Article a : _article_lst)
				if (a.getarticle_p_id()==r.get_vente().getArticle_fk_id())
					ok=true;
			assertTrue("echec article " + r.get_vente().getArticle_fk_id(),ok);
		}
	}
	
	@Test
	public void pour_les_location_chambre_verifier_quil_ny_a_pas_de_zero_dans_reservation_fk_id(){
		for (ReadVente r : _vente_lst){
			if(r.get_vente() instanceof Location_chambre)
				assertFalse("reservation fk = 0 a la vente "+r.get_vente().getVente_p_id(),
						    ((Location_chambre)(r.get_vente())).getReservation_fk_id()==0);
		}
	}
	
	@Test
	public void pour_les_location_chambre_verifier_lintegrite_referentielle_dans_reservation_fk_id(){
		for (ReadVente r : _vente_lst){
			if (r.get_vente() instanceof Location_chambre)
				assertTrue("echec article " + r.get_vente().getArticle_fk_id(), 
						   ((Location_chambre)(r.get_vente())).getReservation_fk_id()==r.get_reservation().getReservation_p_id());
		}
	}
	
	@Test
	public void verifier_les_collisions_entre_les_reservations_qui_proviennent_des_chambres_et_les_reservation_qui_provienent_des_vente(){
		for (ReadVente r : _vente_lst){
			if (r.get_vente() instanceof Location_chambre){
				for (Reservation rr : _chambre_lst.get_reservations()){
					assertFalse("conflit entre reservation vente "+r.get_reservation().getReservation_p_id()+"et chambre"+rr.getReservation_p_id(),
							rr.getReservation_p_id()==r.get_reservation().getReservation_p_id() && rr.equals(r.get_reservation()));
				}
			}
		}
	}
	
	@Test
	public void verifier_integrite_referenciel_entre_location_chambre_et_reservation(){
		for (ReadVente r : _vente_lst){
			if (r.get_vente() instanceof Location_chambre){
				Location_chambre l = ((Location_chambre)r.get_vente());
				boolean ok = false;
				for (ReadVente rr : _vente_lst){
					if (rr.get_reservation()!=null)
						if (rr.get_reservation().getReservation_p_id()==l.getReservation_fk_id())
							ok=true;
				}
				if (!ok){
					for (Reservation rr : _chambre_lst.get_reservations()){
						if (l.getReservation_fk_id() == rr.getReservation_p_id())
							ok=true;
					}
				}
				assertTrue("echec reservation vente => " + (r.get_vente()).getVente_p_id(),ok);
			}
		}
	}
}