package rpa;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.zip.ZipEntry;
import java.util.zip.ZipInputStream;

import com.mysql.jdbc.Statement;

public class DataBase {
	public static void main(String[] args) {
		unzip();
		BaseDados();

	}

	public static void BaseDados() {
		// Directory path here
		String path = "C:\\Users\\WNDWS\\Downloads\\NosVodafone";

		String files;
		File folder = new File(path);
		File[] listOfFiles = folder.listFiles();

		for (int i = 0; i < listOfFiles.length; i++) {

			if (listOfFiles[i].isFile()) {
				files = listOfFiles[i].getName();

				if (files.contains("DownloadCD")) {// || files.contains("DownloadEncargosCorrentes")) { // vodafone

					LerCSV(files, "vodafone");

				} else { // nos
					LerCSV(files, "nos");
				}

			}
		}

	}

	public static void LerCSV(String csvFile, String rede) {

		String path = "C:\\Users\\WNDWS\\Downloads\\NosVodafone\\";
		BufferedReader br = null;
		String line = "";
		String cvsSplitBy = ";";

		try {

			String charset = "ISO-8859-1"; // or what corresponds
			br = new BufferedReader(new InputStreamReader(new FileInputStream(path + csvFile), charset));

			// br = new BufferedReader(new FileReader(path + csvFile));
			boolean flag = false;
			while ((line = br.readLine()) != null) {

				// use comma as separator
				String[] parametros = line.split(cvsSplitBy);

				if (parametros[0].charAt(1) == 'C' && flag == true && rede.equals("nos")) {
					for (int i = 0; i < parametros.length; i++) {
						parametros[i] = parametros[i].replace("\"", "");
					}

					CreateDataNos(parametros);

				} else if (parametros[0].charAt(1) == 'C' && rede.equals("nos"))
					flag = true;
				else if (parametros[0].charAt(0) == 'C' && rede.equals("vodafone"))
					flag = true;
				else if (rede.equals("vodafone") && flag == true) {
					CreateDataVodafone(parametros);

				}

			}

		} catch (IOException e) {
			e.printStackTrace();
		}

	}

	public static void CreateDataVodafone(String[] parametros) {
		final String USERDATA = "root";
		final String PASSDATA = "wndws";
		final String CONNECTION = "jdbc:mysql://localhost:3306/vodafone?useSSL=false";

		try {
			Connection myConnection = DriverManager.getConnection(CONNECTION, USERDATA, PASSDATA);

			Statement stmt = (Statement) myConnection.createStatement();

			String sql = "INSERT IGNORE INTO comunicacoes (numero, conta,tipo, destino, descricao, durCham, durCum, custo) VALUES "
					+ "('" + parametros[1] + "','" + parametros[0] + "','" + parametros[2] + "','" + parametros[6]
					+ parametros[7] + parametros[8] + "','" + parametros[9] + "','" + parametros[10] + "','"
					+ parametros[11] + "','" + parametros[12] + "')";

			stmt.executeUpdate(sql);
			myConnection.close();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}

	}

	public static void CreateDataNos(String[] parametros) {
		final String USERDATA = "root";
		final String PASSDATA = "wndws";
		final String CONNECTION = "jdbc:mysql://localhost:3306/nos?useSSL=false";

		try {
			Connection myConnection = DriverManager.getConnection(CONNECTION, USERDATA, PASSDATA);

			Statement stmt = (Statement) myConnection.createStatement();

			String sql = "INSERT IGNORE INTO registos (numero, conta, tarifario) VALUES " + "('" + parametros[3] + "','"
					+ parametros[2] + "','" + parametros[4] + "')";

			stmt.executeUpdate(sql);

			sql = "INSERT IGNORE INTO contactos (origem, destino, descricao, data, hora, quantidade, unidade, valor) VALUES "
					+ "('" + parametros[9] + "','" + parametros[10] + "','" + parametros[5] + "','" + parametros[7]
					+ "','" + parametros[8] + "','" + parametros[11] + "','" + parametros[12] + "','" + parametros[13]
					+ "')";
			;
			stmt.executeUpdate(sql);
			myConnection.close();

			// ResultSet myResultSet = stmt.executeQuery("select * from registos");
			// while (myResultSet.next())
			// System.out.println(myResultSet.getString("numero"));

			/*
			 *
			 * String sql = "CREATE TABLE REGISTOS " + " conta VARCHAR(255), " +
			 * " numero INTEGER, " + " tarifario VARCHAR(255), " +
			 * " PRIMARY KEY ( numero ))"; stmt.executeUpdate(sql);
			 */
			/*
			 * Statement myStatement = (Statement) myConnection.createStatement(); ResultSet
			 * myResultSet = myStatement.executeQuery("select * from city"); while
			 * (myResultSet.next()) System.out.println(myResultSet.getString("name"));
			 */
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}

	private static void unzip() {
		// Directory path here
		String path = "C:\\Users\\WNDWS\\Downloads\\";

		String files;
		File folder = new File(path);
		File[] listOfFiles = folder.listFiles();

		String destDir = path + "NosVodafone";
		for (int i = 0; i < listOfFiles.length; i++) {

			if (listOfFiles[i].isFile()) {
				files = listOfFiles[i].getName();
				if (files.endsWith(".zip")) {
					// System.out.println(files);
					if (files.contains("_ed_") && files.contains("_ED")) { // nos
						String zipFilePath = path + files;

						unzipFile(zipFilePath, destDir);
						deleteFile(zipFilePath);
						String csvName = files.replaceAll("zip", "csv");
						LerCSV(csvName, "nos");
					} else if (files.contains("DownloadCD") || files.contains("DownloadEncargosCorrentes")) { // vodafone
						String zipFilePath = path + files;

						unzipFile(zipFilePath, destDir);
						deleteFile(zipFilePath);
					}

				}
			}
		}
	}

	private static void unzipFile(String zipFilePath, String destDir) {
		File dir = new File(destDir);
		// Se dir nao existir criar
		if (!dir.exists())
			dir.mkdirs();
		FileInputStream fis;
		// buffer ler e escrever
		byte[] buffer = new byte[1024];
		try {
			fis = new FileInputStream(zipFilePath);
			ZipInputStream zis = new ZipInputStream(fis);
			ZipEntry ze = zis.getNextEntry();
			while (ze != null) {
				String fileName = ze.getName();
				File newFile = new File(destDir + File.separator + fileName);
				// System.out.println("Unzipping to " + newFile.getAbsolutePath());
				// create directories for sub directories in zip
				new File(newFile.getParent()).mkdirs();
				FileOutputStream fos = new FileOutputStream(newFile);
				int len;
				while ((len = zis.read(buffer)) > 0) {
					fos.write(buffer, 0, len);
				}
				fos.close();
				// fechar ZipEntry
				zis.closeEntry();
				ze = zis.getNextEntry();
			}
			// fechar ultimo ZipEntry
			zis.closeEntry();
			zis.close();
			fis.close();
		} catch (IOException e) {
			e.printStackTrace();
		}

	}

	public static void deleteFile(String path) {
		try {

			File file = new File(path);

			if (file.delete()) {
				System.out.println(file.getName() + " is deleted!");
			} else {
				System.out.println("Delete operation is failed.");
			}

		} catch (Exception e) {

			e.printStackTrace();

		}

	}

}