package database.migration;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.Date;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashSet;
import java.util.InvalidPropertiesFormatException;
import java.util.Set;

import tool.regEx.Adresse;
import database.Crud;
import database.crud.AdresseCrud;
import database.crud.ArticleCrud;
import database.crud.CategorieArticleCrud;
import database.crud.ChambreCrud;
import database.crud.ClientCrud;
import database.crud.ConstanteCrud;
import database.crud.DepotCrud;
import database.crud.EmployeCrud;
import database.crud.EncaissementCrud;
import database.crud.FactureCrud;
import database.crud.FermetureCrud;
import database.crud.GroupeCrud;
import database.crud.GroupeUtilisateurCrud;
import database.crud.ModePaiementCrud;
import database.crud.ReservationCrudGeneral;
import database.crud.StatusChambreCrud;
import database.crud.TaxeCrud;
import database.crud.TelephoneCrud;
import database.crud.TelephoneTypeCrud;
import database.crud.UtilisateurCrud;
import database.crud.VenteCrudGeneral;
import database.crud.VilleCrud;
import database.dataset.Article;
import database.dataset.Categorie_article;
import database.dataset.Chambre;
import database.dataset.Client;
import database.dataset.Constante;
import database.dataset.Depot;
import database.dataset.Employe;
import database.dataset.Encaissement;
import database.dataset.Facture;
import database.dataset.Fermeture;
import database.dataset.Groupe;
import database.dataset.Groupe_utilisateur;
import database.dataset.Location_chambre;
import database.dataset.Modedepaiement;
import database.dataset.Reservation;
import database.dataset.Reservation_groupe;
import database.dataset.Reservation_individu;
import database.dataset.Status_chambre;
import database.dataset.Taxe;
import database.dataset.Telephone;
import database.dataset.TelephoneType;
import database.dataset.Utilisateur;
import database.dataset.Vente;
import database.dataset.Vente_article;
import database.dataset.Ville;
import database.migration.etape0.categorie_article.ReadCategorieStock;
import database.migration.etape0.groupeUtilisateur.GenererGroupeUtilisateur;
import database.migration.etape0.modeDePaiement.ReadModeDePaiement;
import database.migration.etape0.permanente.ReadConstant;
import database.migration.etape0.status_chambre.ReadStatusChambre;
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.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;
import database.migration.etape3.encaissement.ReadEncaissement;
import database.migration.etape3.reservationVente.ReadVente;
import database.tools.DbConnection;

public class Migration {
	//etape0
	private Set<Categorie_article> categorie_article;
	private Set<Groupe_utilisateur> groupe_utilisateur;
	private Set<Modedepaiement> modedepaiement;
	private Set<Constante> constante;
	private Set<Status_chambre> status_chambre;
		//telephonetype
	//etape1
	private Set<Article> article;
	private Set<Chambre> chambre;
		private Set<Reservation> reservation;
	private Set<Client> client;
	private Set<Ville> ville;
	private Set<Adresse> adresse;
	private Set<Groupe> groupe;
	private Set<TelephoneType> telephone_type;
	private Set<Telephone> telephone;
	private Set<Depot> depot;
	private Set<Employe> employe;
	private Set<Fermeture> fermeture;
	private Set<Utilisateur> utilisateur;
	//etape2
	private Set<Facture> facture;
	private Set<Taxe> taxe;
	//etape3
	private Set<Encaissement> encaissement;
	private Set<Vente> vente;
	
	public Migration(Connection connection) throws SQLException, InvalidPropertiesFormatException, FileNotFoundException, IOException{
		categorie_article = ReadCategorieStock.from(connection);
		groupe_utilisateur = GenererGroupeUtilisateur.generer();
		modedepaiement = new HashSet<Modedepaiement>(ReadModeDePaiement.from(connection));
		constante = new HashSet<>();
		constante.add(ReadConstant.from(connection));
		status_chambre = new HashSet<>(ReadStatusChambre.from(connection));
		article = ReadStock.from(connection);
		NormalisedChambre ch = NormalisedChambre.make(ReadChambre.from(connection));
		chambre = ch.get_chambres();
		reservation = ch.get_reservations();
		NormalisedClient cl = NormalisedClient.from(ReadClient.loadFrom(connection));
		client = cl.getClients();
		ville = cl.getVilles();
		adresse = cl.getAdresses();
		groupe = cl.getGroupes();
		telephone_type = cl.getTelephones_type();
		telephone = cl.getTelephones();
		NormalisedDepotAndFermeture df = NormalisedDepotAndFermeture.from(ReadFermeture.from(connection), ReadDepot.from(connection));
		depot = df.getDepots();
		employe = df.getEmployes();
		fermeture = df.getFermetures();
		utilisateur = df.getUtilisateurs();
		NormalisedFacture fa = ReadFacture.normaliseTaxe(ReadFacture.from(connection));
		facture = fa.getFactures();
		taxe = fa.getTaxes();
		encaissement = new HashSet<>(ReadEncaissement.from(connection));
		vente = new HashSet<>();
		for (ReadVente rv : ReadVente.from(connection)){
			vente.add(rv.get_vente());
			if (rv.get_reservation() != null)
				reservation.add(rv.get_reservation());
		}
	}
	
	private <DATASET> void fillDb(Set<DATASET> lst, Crud<DATASET> crud) throws SQLException{
		System.out.println(crud.getClass().getName());
		for (DATASET dataset : lst)
			crud.create(dataset);
	}
	
	public void exportation(Connection connection) throws SQLException{
		//do massive drop
		
		//do massive insert		
//		fillDb(categorie_article, new CategorieArticleCrud(connection));
//		fillDb(groupe_utilisateur, new GroupeUtilisateurCrud(connection));
//		fillDb(modedepaiement, new ModePaiementCrud(connection));
//		fillDb(constante, new ConstanteCrud(connection));
//		fillDb(status_chambre, new StatusChambreCrud(connection));
//		//etape1
//		fillDb(article, new ArticleCrud(connection));
//		fillDb(chambre, new ChambreCrud(connection));
//		fillDb(ville, new VilleCrud(connection));
//		fillDb(adresse, new AdresseCrud(connection));
//		fillDb(client, new ClientCrud(connection));
//		fillDb(groupe, new GroupeCrud(connection));
//		ReservationCrudGeneral rc = new ReservationCrudGeneral(connection);
//		for (Reservation r : reservation)
//			if (!(r instanceof Reservation_groupe) && !(r instanceof Reservation_individu)){
//				rc.create(r);
//			}
//		for (Reservation r : reservation)
//			if ((r instanceof Reservation_groupe) || (r instanceof Reservation_individu)){
//				//System.out.println("reservation "+r.toString());
//				rc.create(r);
//			}
//		fillDb(telephone_type, new TelephoneTypeCrud(connection));
//		fillDb(telephone, new TelephoneCrud(connection));
//		fillDb(utilisateur, new UtilisateurCrud(connection));
//		fillDb(employe, new EmployeCrud(connection));
//		fillDb(depot, new DepotCrud(connection));
//		fillDb(fermeture, new FermetureCrud(connection));
//		//etape2
//		fillDb(taxe, new TaxeCrud(connection));
//		fillDb(facture, new FactureCrud(connection));
		//etape3
		fillDb(encaissement, new EncaissementCrud(connection));
		VenteCrudGeneral vc = new VenteCrudGeneral(connection);
		for (Vente v : vente){
			if (!(v instanceof Location_chambre)&&!(v instanceof Vente_article)){
				//System.out.println(v.getVente_p_id());
				vc.create(v);
			}
		}
		for (Vente v : vente){
			if ((v instanceof Location_chambre)||(v instanceof Vente_article)){
				//System.out.println(v.getVente_p_id());
				vc.create(v);
			}
		}
		
		//post migration
		//le nombre de lits
		Statement stm = connection.createStatement();
		stm.execute("UPDATE chambre SET chambre_nombredelits=2 WHERE chambre_description LIKE '2%'");
		
		//les factures en 1969
		stm.execute("UPDATE facture INNER JOIN vente ON facture_fk_id=facture_p_id SET facture_jour=vente_jour WHERE vente_jour>facture_jour");
		
		//les fermeture effacé avec leur facture dont les ventes ne correspondent pas
		//la fermeture et le depot #1 n'existent plus
		stm.execute("INSERT INTO `depot`(`depot_p_id`, `depot_date`, `depot_montant`, `employe_fk_id`) VALUES (1,'1969-12-31',0,1)");
		stm.execute("INSERT INTO `fermeture`(`fermeture_p_id`, `fermeture_jour`, `employe_fk_id`, `depot_fk_id`, `fermeture_commentaire`) VALUES (1,'1969-12-31',1,1,'DONNE EFFACE')");
		stm.execute("UPDATE `facture` SET `fermeture_fk_id`=1 WHERE `facture_jour`='1969-12-31'");
		
		//fermeture avec une date de 0
		stm.execute("UPDATE fermeture SET fermeture_jour='"+new Date(System.currentTimeMillis())+"' WHERE fermeture_jour='0000-00-00'");
	}

	/**
	 * @param args
	 * @throws IOException 
	 * @throws SQLException 
	 * @throws FileNotFoundException 
	 * @throws SecurityException 
	 * @throws NoSuchFieldException 
	 * @throws IllegalAccessException 
	 * @throws IllegalArgumentException 
	 * @throws ClassNotFoundException 
	 */
	public static void main(String[] args) throws FileNotFoundException, SQLException, IOException, IllegalArgumentException, IllegalAccessException, NoSuchFieldException, SecurityException, ClassNotFoundException {
		Connection connection = SqlFactory.connect(SqlFactory.loadProperties("hotel_orford_import.xml"));
		Migration migration = new Migration(connection);
		connection.close();
		
		for (Field f : Migration.class.getDeclaredFields()){
			System.out.println("importé " + f.getName() + " | nombre d'entré " + ((Set)f.get(migration)).size());
		}
		
		
		Connection emmigration = DbConnection.createConnection();
		migration.exportation(emmigration);

	}

}