package hr.fer.zemris.java.p12;

import java.beans.PropertyVetoException;
import java.io.IOException;
import java.nio.file.Files;
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 java.util.Scanner;

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.p12.model.Band;

@WebListener
public class Initialization implements ServletContextListener {

    private static final String CONFIG_FILE = "dbsettings.properties";

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

    public static final String CREATE_POLL_OPTIONS = "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))";

    @Override
    public void contextInitialized(ServletContextEvent sce) {

        String connectionURL;
        try {
            connectionURL = readInit(CONFIG_FILE, sce);
        } catch (IOException e) {
            throw new RuntimeException("The properties file is missing, or some property is missing.");
        }

        ComboPooledDataSource cpds = new ComboPooledDataSource();
        try {
            cpds.setDriverClass("org.apache.derby.jdbc.ClientDriver");
        } catch (PropertyVetoException e1) {
            throw new RuntimeException("Pogreška prilikom inicijalizacije poola.", e1);
        }
        cpds.setJdbcUrl(connectionURL);

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

        try {
            initializeTables(cpds, sce);
        } catch (SQLException e) {
            throw new RuntimeException("Can't initialize tables! - "+e.getMessage());
        }
    }

    @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();
            }
        }
    }

    public String readInit(String fileName, ServletContextEvent sce) throws IOException {

        String pathString = sce.getServletContext().getRealPath("/WEB-INF/" + fileName);

        Properties p = new Properties();
        p.load(Files.newInputStream(Paths.get(pathString)));

        String host = p.getProperty("host");
        String port = p.getProperty("port");
        String name = p.getProperty("name");
        String user = p.getProperty("user");
        String password = p.getProperty("password");

        if (host == null || port == null || name == null || user == null || password == null) {
            throw new IOException("Error while getting dbsettings.properties!");
        }

        String connectionURL = "jdbc:derby://" + host + ":" + port + "/" + name + ";user=" + user + ";password="
                + password;

        return connectionURL;
    }

    private void initializeTables(ComboPooledDataSource cpds, ServletContextEvent sce) throws SQLException {
        Connection con = cpds.getConnection();
        DatabaseMetaData dbmd = con.getMetaData();
        ResultSet res1 = dbmd.getTables(null, null, "POLLS", null);

        if (!res1.next()) {
            createTables(con, sce);
        }

    }

    private void createTables(Connection con, ServletContextEvent sce) throws SQLException {
        PreparedStatement pst = null;
        pst = con.prepareStatement(CREATE_POLLS);
        pst.executeUpdate();

        pst = con.prepareStatement(CREATE_POLL_OPTIONS);
        pst.executeUpdate();

        pst = con.prepareStatement("insert into Polls (title, message) VALUES (?,?)", Statement.RETURN_GENERATED_KEYS);
        pst.setString(1, "Glasanje za omiljeni bend:");
        pst.setString(2, "Od sljedećih bendova, koji Vam je bend najdraži?");
        pst.executeUpdate();

        ResultSet idRes = pst.getGeneratedKeys();
        idRes.next();
        String generatedPollID = idRes.getString(1);

        for (Band band : readBands(sce)) {
            pst = con.prepareStatement("insert into PollOptions (optionTitle,optionLink,pollID,votesCount) VALUES (?,?,?,?)");
            pst.setString(1, band.getName());
            pst.setString(2, band.getLink());
            pst.setString(3, generatedPollID);
            pst.setInt(4, 0);
            pst.executeUpdate();
        }
    }

    private List<Band> readBands(ServletContextEvent sce) {
        String fileName = sce.getServletContext().getRealPath("/WEB-INF/glasanje-definicija.txt");

        try (Scanner sc = new Scanner(Files.newInputStream(Paths.get(fileName)))) {
            List<Band> bands = new ArrayList<>();

            while (sc.hasNextLine()) {
                String line = sc.nextLine();
                String[] elems = line.split("\t");
                if (elems.length != 3) {
                    continue;
                }

                String bandName = elems[1];
                String link = elems[2];

                bands.add(new Band(bandName, link));
            }

            return bands;
        } catch (IOException ex) {
            throw new RuntimeException("Error while reading definition! - Check if the file is located in /WEB-INF/ folder and if the format is good.");
        }
    }
}