package hr.fer.zemris.java.hw14.listeners;

import java.beans.PropertyVetoException;
import java.io.BufferedReader;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;
import javax.servlet.annotation.WebListener;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.mchange.v2.c3p0.DataSources;

import hr.fer.zemris.java.hw14.model.Poll;
import hr.fer.zemris.java.hw14.model.PollOption;

@WebListener
public class Initialization implements ServletContextListener {

	private static final String POLLS_TABLE_CREATE = "CREATE TABLE Polls"
			+ "(id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY," + "title VARCHAR(150) NOT NULL,"
			+ "message CLOB(2048) NOT NULL)";

	private static final String POLL_OPTIONS_TABLE_CREATE = "CREATE TABLE PollOptions"
			+ "(id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY," + "optionTitle VARCHAR(100) NOT NULL,"
			+ "optionLink VARCHAR(150) NOT NULL," + "pollID BIGINT,votesCount BIGINT,"
			+ "FOREIGN KEY (pollID) REFERENCES Polls(id))";

	/**
	 * {@inheritDoc}
	 */
	@Override
	public void contextDestroyed(ServletContextEvent sce) {
		ComboPooledDataSource cpds = (ComboPooledDataSource) sce.getServletContext()
				.getAttribute("hr.fer.zemris.dbpool");
		if (cpds != null) {
			try {
				DataSources.destroy(cpds);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public void contextInitialized(ServletContextEvent sce) {
		ComboPooledDataSource cpds = new ComboPooledDataSource();
		try {
			cpds.setDriverClass("org.apache.derby.jdbc.ClientDriver");
		} catch (PropertyVetoException e1) {
			throw new RuntimeException("Error while initializing connection-pool", e1);
		}

		String fileName = sce.getServletContext().getRealPath("/WEB-INF/dbsettings.properties");
		Path propertiesFile = Paths.get(fileName);
		if (Files.notExists(propertiesFile)) {
			throw new RuntimeException("File with database properties does not exist.");
		}

		String connectionURL = getConnectionURL(propertiesFile);
		cpds.setJdbcUrl(connectionURL);

		try {
			readPollOptions(Paths.get(sce.getServletContext().getRealPath("/WEB-INF/superhero-options.txt")));
		} catch (IOException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		try {
			Connection con = cpds.getConnection();
			if (!tableExist(con.getMetaData(), "Polls")) {
				createPollsTable(con);
			}

			if (!tableExist(con.getMetaData(), "PollOptions")) {
				createPollOptionsTable(con);
			}

			if (tableIsEmpty(con, "Polls")) {
				String path = sce.getServletContext().getRealPath("/WEB-INF/polls-definition.txt");
				List<Poll> polls = fillPollsTable(con, path);

				for(Poll p: polls) {
					fillPollOptionsTable(con, Paths.get(sce.getServletContext().getRealPath("/WEB-INF/" + p.getLink())), p.getId());
				}
			}
		} catch (Exception e) {
			throw new IllegalArgumentException(e.getMessage());
		}

		sce.getServletContext().setAttribute("hr.fer.zemris.dbpool", cpds);
	}

	private String getConnectionURL(Path file) {
		Properties properties = new Properties();
		StringBuilder sb = new StringBuilder();
		try (BufferedReader reader = Files.newBufferedReader(file, StandardCharsets.UTF_8)) {
			properties.load(reader);

			sb.append("jdbc:derby://");
			sb.append(properties.getProperty("host")).append(":");
			sb.append(properties.getProperty("port")).append("/");
			sb.append(properties.getProperty("name")).append(";");
			sb.append("user=").append(properties.getProperty("user")).append(";");
			sb.append("password=").append(properties.getProperty("password"));

		} catch (IOException e) {
			throw new RuntimeException("Could not load database properties.", e);
		}
		return sb.toString();
	}


	private boolean tableExist(DatabaseMetaData metaData, String tableName) throws SQLException {
		int numberOfRows = 0;

		ResultSet rs = metaData.getTables(null, null, tableName.toUpperCase(), null);
		while (rs.next()) {
			++numberOfRows;
		}

		return numberOfRows > 0;
	}

	private void createPollOptionsTable(Connection con) throws SQLException {
		Statement ps = con.createStatement();
		ps.executeUpdate(POLL_OPTIONS_TABLE_CREATE);
	}

	private void createPollsTable(Connection con) throws SQLException {
		Statement ps = con.createStatement();
		ps.executeUpdate(POLLS_TABLE_CREATE);
	}

	private boolean tableIsEmpty(Connection con, String table) throws SQLException {
		int numberOfRows = 0;

		PreparedStatement ps = con.prepareStatement("SELECT * FROM " + table);
		ResultSet rs = ps.executeQuery();
		while (rs != null && rs.next()) {
			++numberOfRows;
		}

		return numberOfRows <= 0;
	}

	private List<Poll> fillPollsTable(Connection con, String path)
			throws IOException, SQLException {
		List<Poll> pollsDef = readPolls(
				Paths.get(path));

		for (Poll pd : pollsDef) {
			PreparedStatement ps = con.prepareStatement("INSERT INTO Polls (title, message) values (?,?)",
					Statement.RETURN_GENERATED_KEYS);

			ps.setString(1, pd.getTitle());
			ps.setString(2, pd.getMessage());
			ps.executeUpdate();

			ResultSet rset = ps.getGeneratedKeys();
			Long pollID = null;
			if (rset != null && rset.next()) {
				pollID = rset.getLong(1);
			}
			if (pollID == null) {
				System.exit(1);
			}
			ps.close();
			pd.setId(pollID);
		}

		return pollsDef;
	}

	private void fillPollOptionsTable(Connection con, Path path, Long pollID)
			throws IOException, SQLException {
		List<PollOption> pollOpt = readPollOptions(path);

		for (PollOption po : pollOpt) {
			PreparedStatement ps = con.prepareStatement(
					"INSERT INTO PollOptions (optionTitle, optionLink, pollID, votesCount) values (?,?,?,?)");

			ps.setString(1, po.getOptionTitle());
			ps.setString(2, po.getOptionLink());
			ps.setLong(3, pollID);
			ps.setLong(4, 0);

			ps.executeUpdate();
			ps.close();
		}

	}

	private List<PollOption> readPollOptions(Path file) throws IOException {
		List<String> lines = Files.readAllLines(file, StandardCharsets.UTF_8);

		List<PollOption> pollOpt = new ArrayList<>();
		for (String l : lines) {
			String[] data = l.split("\t");
			if (data.length != 2) {
				throw new RuntimeException("Illigal format of " + file.getFileName() + " file.");
			}
			PollOption opt = new PollOption(null, data[0], data[1], null, null);
			pollOpt.add(opt);
		}

		return pollOpt;
	}

	private List<Poll> readPolls(Path file) throws IOException {
		List<String> lines = Files.readAllLines(file, StandardCharsets.UTF_8);

		List<Poll> polls = new ArrayList<>();
		for (String l : lines) {
			String[] data = l.split("\t");
			if (data.length != 3) {
				throw new RuntimeException("Illigal format of " + file.getFileName() + " file.");
			}
			Poll poll = new Poll(null, data[0], data[1], data[2]);
			polls.add(poll);
		}

		return polls;
	}

}