package database.migration;

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.Calendar;

import database.dataset.Depot;

//detruire la chambre 0
//mettre le nombre de lit pour chaque chambre

public class PreMigration {
	public static void maintenant(Connection connection) throws SQLException{
		Statement stm = connection.createStatement();
		ResultSet rs;
		
		Date today = new Date(Calendar.getInstance().getTimeInMillis());


		//fermeture->depot
//		delete all bidon before insert new one
		stm.execute("DELETE FROM depots WHERE employe='BIDON'");
		//find next id
		rs = stm.executeQuery("SELECT MAX(ID) FROM depots");
		rs.next();
		int depot_bidon_id = rs.getInt(1)+1;
		//insert bidon
		stm.execute("INSERT INTO depots (ID, date, employe) VALUES("+depot_bidon_id+", '"+new Date(0)+"', 'BIDON')");
		//link to it
		stm.execute("UPDATE fermeture SET ID_depots="+depot_bidon_id+" WHERE ID_depots=0");
		
		//facture->fermeture
		stm.execute("DELETE FROM fermeture WHERE employe='BIDON'");
		//find next id
		rs = stm.executeQuery("SELECT MAX(ID) FROM fermeture");
		rs.next();
		int fermeture_bidon_id = rs.getInt(1)+1;
		//insert bidon
		stm.execute("INSERT INTO fermeture (ID, ID_depots, date, employe) VALUES("+fermeture_bidon_id+" , "+depot_bidon_id+", '"+new Date(0)+"', 'BIDON')");
		//link to it
		stm.execute("UPDATE facture SET ID_fermeture="+depot_bidon_id+" WHERE ID_fermeture=0");
		
		//client bidon
		stm.execute("DELETE FROM client WHERE prenom='BIDON'");
		//find next id
		rs = stm.executeQuery("SELECT MAX(ID) FROM client");
		rs.next();
		int client_bidon_id = rs.getInt(1)+1;
		//insert bidon
		stm.execute("INSERT INTO client (ID, prenom)"+
		            " VALUES("+client_bidon_id+",'BIDON')");
		
		//encaissement->facture
		stm.execute("DELETE FROM facture WHERE commentaire='BIDON'");
		//find next id
		rs = stm.executeQuery("SELECT MAX(ID) FROM facture");
		rs.next();
		int facture_bidon_id = rs.getInt(1)+1;
		//insert bidon
		stm.execute("INSERT INTO facture (ID, ID_statut, ID_client, ID_fermeture, commentaire, taux_TPS, taux_TVQ, verifier)"+
		            " VALUES("+facture_bidon_id+",2,"+client_bidon_id+", "+client_bidon_id+", 'BIDON', 0, 0, 0)");
		//link to it
		stm.execute("UPDATE encaissement SET ID_facture="+facture_bidon_id+" WHERE ID_facture=0");
		
		//stock bidon
		stm.execute("DELETE FROM stock WHERE code='BIDON'");
		//find next id
		rs = stm.executeQuery("SELECT MAX(ID) FROM stock");
		rs.next();
		int stock_bidon_id = rs.getInt(1)+1;
		//insert bidon
		stm.execute("INSERT INTO stock (ID, code, description, taxe, ID_categorie, actif)"+
		            " VALUES("+stock_bidon_id+",'BIDON', '', 1,1,1)");
		//vente->stock
		stm.execute("UPDATE ventes SET ID_stock="+stock_bidon_id+" WHERE ID_stock=0");
		//vente->facture
		stm.execute("UPDATE ventes SET ID_facture="+facture_bidon_id+" WHERE ID_facture=0");
		
		//il y a comme 1000 ville
		//ce code ne marche qu'avec mysql
		stm.execute("UPDATE `client` SET `ville` = REPLACE(`ville`,'-',' ') WHERE `ville` LIKE '%-%'");
		stm.execute("UPDATE `client` SET `ville` = REPLACE(`ville`,'#','') WHERE `ville` LIKE '%#%'");
		stm.execute("UPDATE `client` SET `ville` = REPLACE(`ville`,',',' ') WHERE `ville` LIKE '%,%'");
		stm.execute("UPDATE `client` SET `ville` = REPLACE(`ville`,'QC','') WHERE `ville` LIKE '%QC%'");
		stm.execute("UPDATE `client` SET `ville` = REPLACE(`ville`,'MTL','MONTREAL') WHERE `ville` LIKE '%MTL%'");
		stm.execute("UPDATE `client` SET `ville` = REPLACE(`ville`,'/','E') WHERE `ville` LIKE '%/%'");
		stm.execute("UPDATE `client` SET `ville` = REPLACE(`ville`,'N D ','NOTRE DAME ') WHERE `ville` LIKE '%N D %'");
		stm.execute("UPDATE `client` SET `ville` = REPLACE(`ville`,'CANTON DE','') WHERE `ville` LIKE '%CANTON DE%'");
		stm.execute("UPDATE `client` SET `ville` = REPLACE(`ville`,'CANTON','') WHERE `ville` LIKE '%CANTON%'");
		stm.execute("UPDATE `client` SET `ville` = REPLACE(`ville`,'VILLE DE','') WHERE `ville` LIKE '%VILLE DE%'");
		stm.execute("UPDATE `client` SET `ville` = REPLACE(`ville`,'VILLE','') WHERE `ville` LIKE '%VILLE%'");
		stm.execute("UPDATE `client` SET `ville` = 'MONTREAL' WHERE `ville` LIKE '%MONTREAL%'");		
		stm.execute("UPDATE `client` SET `ville` = 'LAVAL' WHERE `ville` LIKE '%LAVAL%'");
		stm.execute("UPDATE `client` SET `ville` = REPLACE(`ville`,'  ',' ') WHERE `ville` LIKE '%  %'");
		stm.execute("UPDATE `client` SET `ville` = LTRIM(`ville`) WHERE `ville` LIKE '% ' OR `ville` LIKE ' %'");

		//correction des données effacé par les utilisateur
//		stm.execute("DELETE FROM depots WHERE `ID`=502");
//		stm.execute("INSERT INTO depots (`ID`, `date`, `employe`) VALUES (502, '1960-01-01', 'BIDON')");
//		stm.execute("UPDATE encaissement SET `methode`=7 WHERE `methode`=0");
//		stm.execute("DELETE FROM facture WHERE `ID`=11531");
//		stm.execute("INSERT INTO facture (`ID`, `ID_statut`, `ID_client`, `ID_fermeture`, `commentaire`, `taux_TPS`, `taux_TVQ`, `verifier`) VALUES (11531, 8, (SELECT `ID` FROM `client` WHERE `prenom`='BIDON'), (SELECT `ID` FROM `fermeture` WHERE `employe`='BIDON'), 'BIDON', 0, 0, 0)");
//		stm.execute("UPDATE `client` SET `nom`='bidon', `prenom`='bidon' WHERE `nom`='' AND `prenom`=''");
//		stm.execute("UPDATE `facture` SET `ID_client`=(SELECT `ID` FROM `client` WHERE `prenom`='BIDON' LIMIT 1) WHERE (SELECT count(`ID`) FROM `client` WHERE `ID`=`ID_client`)=0;");
//		stm.execute("UPDATE `facture` SET `ID_fermeture`=(SELECT `ID` FROM `fermeture` WHERE `employe`='BIDON' LIMIT 1) WHERE (SELECT count(`ID`) FROM `fermeture` WHERE `ID`=`ID_fermeture`)=0");

		//static
//		stm.execute("DELETE FROM depots WHERE `ID`=502");
//
//		stm.execute("INSERT INTO depots (`ID`, `date`, `employe`) VALUES (502, '1960-01-01', 'BIDON')");
//
//		stm.execute("UPDATE encaissement SET `methode`=7 WHERE `methode`=0");

//		stm.execute("DELETE FROM facture WHERE `ID`=11531");
//		stm.execute("UPDATE `client` SET `nom`='bidon', `prenom`='bidon' WHERE `nom`='' AND `prenom`=''");
//		stm.execute("UPDATE `facture` " +
//		"SET `ID_fermeture`=(SELECT `ID` FROM `fermeture` WHERE `employe`='BIDON') " +
//		"WHERE (SELECT count(`ID`) FROM `fermeture` WHERE `ID`=`ID_fermeture`)=0");
//		
//		stm.execute("INSERT INTO facture (`ID`, `ID_statut`, `ID_client`, `ID_fermeture`, `commentaire`, `taux_TPS`, `taux_TVQ`, `verifier`)" +
//		            "VALUES (11531, 8, (SELECT `ID` FROM `client` WHERE `prenom`='BIDON' LIMIT 1), " + 
//				                      "(SELECT `ID` FROM `fermeture` WHERE `employe`='BIDON' LIMIT 1)," +
//		            " 'BIDON', 0, 0, 0)");


		stm.execute("UPDATE `facture` " +
		"SET `ID_client`=(SELECT `ID` FROM `client` WHERE `prenom`='BIDON') " +
		"WHERE (SELECT count(`ID`) FROM `client` WHERE `ID`=`ID_client`)=0");

		//chambre 0
		stm.execute("DELETE FROM chambre WHERE ID=0");
		
		//reservation occupe mais terminé depuis plus d'un ans
		stm.execute("UPDATE statut SET statut='CHECKIN' WHERE statut='OCCUPEE'");
		stm.execute("UPDATE facture INNER JOIN `ventes` ON `ID_facture` = facture.ID SET `ID_statut` =4 WHERE facture.ID_statut =3 AND date < '"+new Date(System.currentTimeMillis())+"'");
		
		//chambre avec date 00-00-00
		stm.execute("UPDATE chambre SET dateND=NULL WHERE dateND=0");
		
		//bigdecimal
		stm.execute("UPDATE facture SET taux_TPS=REPLACE(taux_TPS,',','.'), taux_TVQ=REPLACE(taux_TVQ,',','.')");
		stm.execute("UPDATE facture SET taux_TPS=0 WHERE taux_TPS NOT REGEXP '^[0-9]+\\.?[0-9]*$';");
		stm.execute("UPDATE facture SET taux_TVQ=0 WHERE taux_TVQ NOT REGEXP '^[0-9]+\\.?[0-9]*$';");
		
	}
	
	public static void main(String ... arg) throws FileNotFoundException, SQLException, IOException{
		Connection connection = SqlFactory.connect(SqlFactory.loadProperties("hotel_orford_import.xml"));
		maintenant(connection);
	}
}