package ftp;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.sql.DataSource;
import org.apache.commons.net.ftp.FTPClient;
import org.apache.commons.net.ftp.FTPReply;
import org.springframework.jdbc.datasource.SimpleDriverDataSource;

import com.mysql.jdbc.Driver;
import com.mysql.jdbc.Statement;

public class ftpToDB {
	private static void showServerReply(FTPClient ftpClient) {
		String[] replies = ftpClient.getReplyStrings();
		if (replies != null && replies.length > 0) {
			for (String aReply : replies) {
				System.out.println("SERVER: " + aReply);
			}
		}
	}

	public static void main(String[] args) {
		String server = "ftp.inerps.eu";
		int port = 21;
		String user = "burgas@inerps.eu";
		String pass = "i8w4sv9da0j2d7w0";
		FTPClient ftpClient = new FTPClient();
		try {
			ftpClient.connect(server, port);
			showServerReply(ftpClient);
			int replyCode = ftpClient.getReplyCode();
			if (!FTPReply.isPositiveCompletion(replyCode)) {
				System.out.println("Operation failed. Server reply code: " + replyCode);
				return;
			}
			ftpClient.login(user, pass);
			// showServerReply(ftpClient);
		} catch (IOException ex) {
			System.out.println("Oops! Something wrong happened");
			ex.printStackTrace();
		}
		String[] names = null;
		List<String> names1 = new ArrayList();
		try {
			names = ftpClient.listNames();
		} catch (IOException e2) {
			e2.printStackTrace();
		}
		for (int i = 0; i < names.length; i++) {
			names1.add(names[i]);
		}
		for (int i = 0; i < names1.size(); i++) {
			if (names1.get(i).startsWith(".")) {
				names1.remove(i);
				i--;
			}
		}
		Connection conn = null;
		DataSource ds = null;
		ResultSet resultSet = null;
		Statement statement = null;
		String preLast = null;

		try {

			Driver driver = new com.mysql.jdbc.Driver();
			String url = "jdbc:mysql://localhost:5555/camera";
			String username = "root";
			String password = "603400";
			ds = new SimpleDriverDataSource(driver, url, username, password);
			conn = ds.getConnection();
			statement = (Statement) conn.createStatement();
			resultSet = statement.executeQuery("SELECT * FROM camera ORDER BY id DESC LIMIT 1");
			while (resultSet.next()) {
			preLast = resultSet.getString("cameraTime");
		}
		}catch (SQLException e1) {
			e1.printStackTrace();
		}
		String last = preLast.substring(0, 8);
		for (String string : names1) {
			 DateFormat df = new SimpleDateFormat("yyyyMMdd");
			 Date lastDate;
			try {
				lastDate = df.parse(last);
				if(lastDate.after(df.parse(string))) {
					names1.remove(string);
				}
			} catch (ParseException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}


		}
		String[] times = null;
		List<String> times1 = new ArrayList();
		List<String> pattern1 = new ArrayList();
		String pattern = "/";
		for (String string : names1) {
			try {
				times = ftpClient.listNames("/" + string);
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			pattern = pattern.concat(string + "/");
			for (int i = 0; i < times.length; i++) {
				try {
					if (Character.isDigit(times[i].charAt(0))) {
						String numb = string + times[i].substring(0, 6);
						times1.add(numb);
					}
				} catch (Exception e) {
					// TODO: handle exception
				}
			}
			for (String string1 : times) {
				if (Character.isDigit(string1.charAt(0))) {
					String path1 = pattern;
					path1 = path1.concat(string1);
					pattern1.add(path1);
				}

			}
			try {
				for (int i = 0; i < times1.size(); i++) {
					statement = (Statement) conn.createStatement();
					String INSERT_SQL = "INSERT INTO camera (cameraTime,pattern)" + "SELECT * FROM (SELECT ?,?) as tmp"
							+ " WHERE NOT EXISTS (\r\n" + "    SELECT pattern FROM camera WHERE pattern = ?"
							+ ") LIMIT 1;";

					PreparedStatement ps = conn.prepareStatement(INSERT_SQL, Statement.RETURN_GENERATED_KEYS);
					ps.setString(1, times1.get(i));
					ps.setString(2, pattern1.get(i));
					ps.setString(3, pattern1.get(i));
					ps.executeUpdate();
					ps.close();
				}
			} catch (

			SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

	}
}