package CBBDD.CBBDD;

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Calendar;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * @author mortizf
 */

public class HolaMundoBaseDeDatos {
	public static Connection conn = null;
	public static Statement stmt = null;
	public static Calendar cal;
	public static Timestamp ts;
	public static String fich = "C:\\Users\\morti\\OneDrive\\Escritorio\\Ficheros\\Moviments.txt";

	public static void main(String[] args) throws SQLException, IOException, ParseException {

		String cadena;
		FileReader f = new FileReader(fich);
		BufferedReader b = new BufferedReader(f);
		String[] datoslinea;
		SimpleDateFormat fechaSimple = new SimpleDateFormat("yyyy-MM-dd");
		Date dmov;
		java.sql.Date sqldmov;
		int idvehicle = 0;
		int idcontribuent = 0;
		int idve = 0;
		int contador = 0;

		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			System.err.println("El driver no se encuentra");
			System.exit(-1);
		}
		try {
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc", "root", "");
			stmt = conn.createStatement();

			while ((cadena = b.readLine()) != null) {
				datoslinea = cadena.split("\\|");
				for (int i = 0; i < datoslinea.length; i++) {
					System.out.println(datoslinea[i]);
				}
				if (contador != 0) {
					if (datoslinea != null) {

						dmov = fechaSimple.parse(datoslinea[2]);
						sqldmov = new java.sql.Date(dmov.getTime());
						// Falta afegir al exercici ¡¡¡¡¡¡¡¡¡¡ I A LA BASE DE DADES !!!!!!!! el nom de
						// fitxer i la data d'importació (data del sistema)
						String movi = "INSERT INTO Moviments (ABM,TIPUS,DATA,MATRICULA,N_BASTIDOR,N_MOTOR,DNI,COGNOM_NOM,ADREÇA) VALUES (?,?,?,?,?,?,?,?,?)";
						PreparedStatement psmovi = conn.prepareStatement(movi);
						cal = Calendar.getInstance();
						ts = new java.sql.Timestamp(cal.getTime().getTime());

						psmovi.setString(1, datoslinea[0]);
						psmovi.setString(2, datoslinea[1]);
						psmovi.setDate(3, sqldmov);
						psmovi.setString(4, datoslinea[3]);
						psmovi.setInt(5, Integer.valueOf(datoslinea[4]));
						psmovi.setInt(6, Integer.valueOf(datoslinea[5]));
						psmovi.setString(7, datoslinea[6]);
						psmovi.setString(8, datoslinea[7]);
						psmovi.setString(9, datoslinea[8]);

						psmovi.executeUpdate();
						psmovi.close();

						if (datoslinea[0].equals("A")) {// CREO QUE HE ENTENDIDO MAL LO DEL ALTA

							NouVehicle(datoslinea);
							NouContribuent(datoslinea);
							NouHistoric(datoslinea);

						} else if (datoslinea[0].equals("M")) {// FALTA HACER EL HISTÓRICO

							if (datoslinea[1].equals("CD")) { // Actualizar domicilio
								String upcontri = "UPDATE Contribuents SET DOMICILI = ?  WHERE NIF = ?";
								PreparedStatement psupcontri = conn.prepareStatement(upcontri);

								psupcontri.setString(1, datoslinea[8]);
								psupcontri.setString(2, datoslinea[6]);

								psupcontri.executeUpdate();
								psupcontri.close();
							} else if (datoslinea[1].equals("CP")) { // FALTA Posar data de baixa en el historic
																		// (UPDATE), crear
																		// un nou registre a la taula Historic amb el
																		// nou
																		// propietari i el vehicle (INSERT)
								NouContribuent(datoslinea);

								ResultSet rscoh = stmt.executeQuery(
										"SELECT idVe FROM Vehicles WHERE Matricula = '" + datoslinea[3] + "'");
								if (rscoh.next()) {
									idve = rscoh.getInt(1);
								}
								cal = Calendar.getInstance();
								ts = new java.sql.Timestamp(cal.getTime().getTime());
								String uphis = "UPDATE Historic SET Data_Baixa = ?  WHERE idVe = ?";
								PreparedStatement psuphis = conn.prepareStatement(uphis);

								psuphis.setTimestamp(1, ts);
								psuphis.setInt(2, idve);
								psuphis.executeUpdate();
								psuphis.close();
								NouHistoric(datoslinea);
							}
						} else if (datoslinea[0].equals("B")) {// FALTA HACER EL HISTÓRICO
							if (datoslinea[1].equals("BD")) {// Falta data de baixa historic (UPDATE)
								UpdateVehicle(datoslinea);
								ResultSet rs2 = stmt.executeQuery(
										"SELECT idVe FROM Vehicles WHERE Matricula = '" + datoslinea[3] + "'");
								if (rs2.next()) {
									idvehicle = rs2.getInt(1);
								}
								cal = Calendar.getInstance();
								ts = new java.sql.Timestamp(cal.getTime().getTime());
								String uphis = "UPDATE Historic SET Data_Baixa = ?  WHERE idVe = ?";
								PreparedStatement psuphis = conn.prepareStatement(uphis);

								psuphis.setTimestamp(1, ts);
								psuphis.setInt(2, idvehicle);

								psuphis.executeUpdate();
								psuphis.close();
							} else if (datoslinea[1].equals("BT")) {
								UpdateVehicle(datoslinea);
							}
						}

					} else {
						System.out.println("la ultima linea esta mal");
					}

				}
			contador++;
			}
			b.close();
			stmt.close();
		} catch (SQLException e) {
			System.err.println("Error en la base de datos: " + e.getMessage());
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					System.err.println("Error al cerrar la conexión: " + e.getMessage());
				}
			}
		}
	}

	public static void UpdateVehicle(String[] datoslinea) throws SQLException {
		cal = Calendar.getInstance();
		ts = new java.sql.Timestamp(cal.getTime().getTime());
		String upvehitemp = "UPDATE Vehicles SET Tipus_Baixa = ?, Data_Baixa = ?  WHERE Matricula = ?";
		PreparedStatement psupvehitemp = conn.prepareStatement(upvehitemp);

		psupvehitemp.setString(1, datoslinea[1]);
		psupvehitemp.setTimestamp(2, ts);
		psupvehitemp.setString(3, datoslinea[3]);

		psupvehitemp.executeUpdate();
		psupvehitemp.close();
	}

	public static void NouContribuent(String[] datoslinea) throws SQLException {
		ResultSet rscontri = stmt.executeQuery("SELECT NIF FROM Contribuents WHERE NIF = '" + datoslinea[6] + "'");
		if (rscontri.isBeforeFirst()) {
			System.out.println("L'element ja existeix");
		} else {
			String contri = "INSERT INTO Contribuents (NIF,NOM,DOMICILI) VALUES (?,?,?)";
			PreparedStatement pscontri = conn.prepareStatement(contri);

			pscontri.setString(1, datoslinea[6]);
			pscontri.setString(2, datoslinea[7]);
			pscontri.setString(3, datoslinea[8]);

			pscontri.executeUpdate();
			pscontri.close();
			rscontri.close();
		}
	}

	public static void NouHistoric(String[] datoslinea) throws SQLException {

		int idcontribuent = 0;
		int idvehicle = 0;
		ResultSet rsv = stmt.executeQuery("SELECT idVe FROM Vehicles WHERE Matricula = '" + datoslinea[3] + "'");
		if (rsv.next()) {
			idvehicle = rsv.getInt(1);
		}
		rsv.close();
		ResultSet rsc = stmt.executeQuery("SELECT idCon FROM Contribuents WHERE NIF = '" + datoslinea[6] + "'");

		if (rsc.next()) {
			idcontribuent = rsc.getInt(1);
		}

		rsc.close();
		ResultSet rshisto = stmt
				.executeQuery("SELECT * FROM historic WHERE idCon = " + idcontribuent + " AND idVe = " + idvehicle);
		if (rshisto.isBeforeFirst()) {
			System.out.println("L'element ja existeix");
		} else {
			cal = Calendar.getInstance();
			ts = new java.sql.Timestamp(cal.getTime().getTime());

			String his = "INSERT INTO Historic (idCon,idVe,Data_Alta) VALUES (?,?,?)";
			PreparedStatement pshis = conn.prepareStatement(his);

			pshis.setInt(1, idcontribuent);
			pshis.setInt(2, idvehicle);
			pshis.setTimestamp(3, ts);

			pshis.executeUpdate();
			pshis.close();
			rshisto.close();
		}
	}

	public static void NouVehicle(String[] datoslinea) throws SQLException {

		ResultSet rsve = stmt.executeQuery("SELECT Matricula FROM Vehicles WHERE Matricula = '" + datoslinea[3] + "'");
		if (rsve.isBeforeFirst()) {
			System.out.println("L'element ja existeix");
		} else {
			cal = Calendar.getInstance();
			ts = new java.sql.Timestamp(cal.getTime().getTime());
			String vehi = "INSERT INTO Vehicles (Matricula,N_Motor,N_Bastidor,Data_Alta) VALUES (?,?,?,?)";
			PreparedStatement psvehi = conn.prepareStatement(vehi);

			psvehi.setString(1, datoslinea[3]);
			psvehi.setInt(2, Integer.valueOf(datoslinea[4]));
			psvehi.setInt(3, Integer.valueOf(datoslinea[5]));
			psvehi.setTimestamp(4, ts);

			psvehi.executeUpdate();
			psvehi.close();
			rsve.close();

		}
	}
}