package db;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.jdom.Document;
import org.jdom.Element;
import org.jdom.JDOMException;
import org.jdom.input.SAXBuilder;

public class DBUpdater {

    private SAXBuilder parser;
    private Connection connection;

    private final String driver = "com.mysql.jdbc.Driver";
    private final String url = "jdbc:mysql://localhost:3306/dealers";
    private final String login = "root";
    private final String password = "";

    public DBUpdater() {
        parser = new SAXBuilder();

        // Connect to DB
        try {
            Class.forName(driver).newInstance();
            connection = DriverManager.getConnection(url, login, password);
        } catch (Exception e) {
            System.err.println("Exception: " + e.getMessage());
        }

    }

    public static void main(String args[]) {

        if (args.length == 0) {
            System.err.println("No XML file specified.");
            System.exit(0);
        }

        DBUpdater db = new DBUpdater();

        try {
            if (!db.connection.isClosed()) {
                for (String arg : args)
                    db.readFile(arg);

                db.disconnectFromDB();
            }
        } catch (SQLException e) {
            System.err
            .println("Error connecting to the Database. Exiting application.");
            System.exit(0);
        }
    }

    private void disconnectFromDB() throws SQLException {
        if (!this.connection.isClosed()) {
            this.connection.close();
            System.out.println("Disconnected from the Database.");
            System.exit(0);
        }
    }

    private void readFile(String file) {
        try {
            Document doc = parser.build(file);
            Element root = doc.getRootElement();
            getData(root);
        } catch (JDOMException e) {
            System.err.println("XML file " + file + " is not well formed.");
            System.err.println(e.getMessage());
        } catch (IOException e) {
            System.err.println("Could not access " + file + ".");
            System.err.println(e.getMessage());
        }
    }

    private void getData(Element root) {

        String brand = root.getAttributeValue("brand");
        String shop = root.getNamespaceURI();

        updateON(shop);

        @SuppressWarnings("unchecked")
        List<Element> laptops = root.getChildren();

        for (Element temp : laptops) {
            // for (int i = 0; i < laptops.size(); i++) {
            // Element temp = laptops.get(i);

            String model = temp.getAttributeValue("model");
            String price = temp.getChildText("price");
            String processor = temp.getChildText("processor");
            String clock = temp.getChildText("clock");
            String cache = temp.getChildText("cache");
            String ram = temp.getChildText("ram");
            String ramtype = temp.getChildText("ramtype");
            String disk = temp.getChildText("disk");
            String screensize = temp.getChildText("screensize");
            String screenres = temp.getChildText("screenres");
            String graphadap = temp.getChildText("graphadap");
            String graphmem = temp.getChildText("graphmem");
            String os = temp.getChildText("os");
            String software = temp.getChildText("software");
            String link = temp.getAttributeValue("img");

            Laptop l = new Laptop(brand, shop, model, price, processor, clock,
                    cache, ram, ramtype, disk, screensize, screenres,
                    graphadap, graphmem, os, software, link);

            this.insertIntoDB(l);
        }

        updateOff();
    }

    private void updateOff() {
        // TODO Auto-generated method stub

        String delete = "DELETE FROM laptops WHERE up = 1;";

        try {
            this.connection.createStatement().execute(delete);
        } catch (SQLException e) {
            System.err.println("Error in Query: "+delete);
            //e.printStackTrace();
        }

    }

    private void updateON(String shop) {
        // TODO Auto-generated method stub

        String query = "UPDATE laptops set up = 1 where SHOP LIKE '"+shop+"';";
        try {
            this.connection.createStatement().execute(query);
        } catch (SQLException e) {
            System.err.println("Error in Query: "+query);
            //e.printStackTrace();
        }

    }

    private void insertIntoDB(Laptop l) {
        String query = "INSERT INTO laptops (brand, shop, model, price, processor, clock, cache, ram, ramtype, disk, screensize, screenres, graphadap,graphmem, os, software, link) VALUES ('"
            + l.getBrand() + "', '" + l.getShop() + "', '" + l.getModel() + "', '"
            + l.getPrice() + "', '" + l.getProcessor() + "', '" + l.getClock()  + "', '"
            + l.getCache() + "', '" + l.getRam() + "', '" + l.getRamtype() + "', '"
            + l.getDisk() + "', '" + l.getScreensize() + "', '" + l.getScreenres() + "', '"
            + l.getGraphadap() + "', '" + l.getGraphmem() + "', '" + l.getOs() + "', '"
            + l.getSoftware() + "', '" + l.getPicurl() + "');";


        try {
            if(this.checkDB(l))
                this.connection.createStatement().execute(query);
        } catch (SQLException e) {
            System.err.println("Error inserting laptop " + l.getBrand() + " - " + l.getModel() + " in the database.");
            e.printStackTrace();
        }
    }

    private boolean checkDB(Laptop l) throws SQLException {

        int price = Integer.valueOf(l.getPrice());
        int id = 0, temp = 0, count = 0;

        String query = "SELECT id, model, price, shop FROM laptops WHERE model LIKE '" + l.getModel() + "';";
        ResultSet result = this.connection.createStatement().executeQuery(query);

        while(result.next()) {
            id = result.getInt("id");
            if(result.getString("shop").compareToIgnoreCase(l.getShop()) == 0){
                temp = Integer.valueOf(result.getString("price"));
                count++;
                String update ="UPDATE laptops SET up = 0 WHERE id = "+ id+";" ;
                this.connection.createStatement().execute(update);
                System.out.println("cenas "+id);
                break;
            }
        }

        if(count == 0)
            return true;
        else if(temp > price) {
            String delquery = "UPDATE laptops set price = "+temp+" WHERE id = '" + id + "';";
            this.connection.createStatement().execute(delquery);
            return true;
        }

        return false;
    }
}