/*
 * DBConnection.java
 *
 * Created on den 18 juli 2002, 18:32
 */

/**
 *
 * @author  h&m
 * @version 
 */

import java.sql.*;
import java.util.*;

public class DBConnection {
    private java.sql.Connection connection; 
    
    final private String driver = "org.gjt.mm.mysql.Driver";
    final private String url = "jdbc:mysql://atlas.dsv.su.se/db_01_henrik_j";
    final private String usr = "db_01_henrik_j";
    final private String pw = "274758";
    
    private String user = "";
    private String password = null;
    private boolean pwok = false;
    private java.util.LinkedList actions = new LinkedList();
    private java.util.HashMap expansions = new HashMap(10);
    private DBApplets parent = null;
    private String error = null;

    /** Creates new DBConnection */
    public DBConnection(DBApplets parent, String user) {
        this.parent = parent;
        this.user = user;
        
        try {
            Class.forName(driver).newInstance();
            connection = DriverManager.getConnection(url, usr, pw);
        } catch(ClassNotFoundException e) {
            error = e.getMessage();
        } catch(InstantiationException e) {
            error = e.getMessage();
        } catch(IllegalAccessException e) {
            error = e.getMessage();
        } catch(SQLException e) {
            error = e.getMessage();
        }
    }
    
    public boolean setPassword(String pw) {
        password = pw;
        try {
            Statement statement = connection.createStatement();
            ResultSet result = statement.executeQuery("SELECT * FROM bookmarksusers WHERE user='"+user+"' AND pw = '"+password+"'");
            if(!result.next()) {
                pwok = false;
                return false;
            } 
        } catch(SQLException e) {
            return false;
        }
        pwok = true;
        return true;
    }
    
    public String getError() {
        String e = error;
        error = null;
        return e;
    }
    
    public boolean hasError() {
        return error != null;
    }
    
    public boolean removeDbNode(String id, String lastInNodeId, String prevId, String nextId) {
        if(!pwok)
            return false;
        if(nextId == null)
            nextId = "0";
        String uSQL = "UPDATE bookmarks SET nextId = '"+nextId+"' WHERE id = '"+prevId+"' AND userId = '"+user+"'";
        actions.addLast(uSQL);
        try {
            Statement statement = connection.createStatement();
            ResultSet result;
            String query;
            while(!id.equals(lastInNodeId)) {
                actions.addLast("UPDATE bookmarks SET deleted = 'd' WHERE id = '"+id+"' AND userId = '"+user+"'");
                query = "SELECT nextId from bookmarks WHERE id = '"+id+"' AND userId = '"+user+"'";
                result = statement.executeQuery(query);
                result.next();
                id = result.getString(1);
            }
            actions.addLast("UPDATE bookmarks SET deleted = 'd' WHERE id = '"+id+"' AND userId = '"+user+"'");
        } catch(SQLException e) {
            error = e.getMessage();
            return false;
        }
        return true;
    }
    
    public void addBookmarkNode(String id, String prevId, String nextId, String parentId, String name, String url) {
        if(!pwok)
            return;
        if(nextId == null)
            nextId = "0";
        String uSQL = "UPDATE bookmarks SET nextId = '"+id+"' WHERE id = '"+prevId+"' AND userId = '"+user+"'";
        String iSQL = "INSERT INTO bookmarks VALUES (1,'"+name+"','"+url+"','"+id+"','"+nextId+"','"+parentId+"','"+user+"','')";
        actions.addLast(uSQL);
        actions.addLast(iSQL);
    }
    
    public void addFolderNode(String id, String prevId, String nextId, String parentId, String name) {
        if(!pwok)
            return;
        if(nextId == null)
            nextId = "0";
        String uSQL = "UPDATE bookmarks SET nextId = '"+id+"' WHERE id = '"+prevId+"' AND userId = '"+user+"'";
        String iSQL = "INSERT INTO bookmarks VALUES (0,'"+name+"','expanded','"+id+"','"+nextId+"','"+parentId+"','"+user+"','')";
        actions.addLast(uSQL);
        actions.addLast(iSQL);
    }
    
    public boolean pasteNode(String id, String lastInNodeId, String prevId, String nextId, String parentId) {
        if(!pwok)
            return false;
        if(nextId == null)
            nextId = "0";
        actions.addLast("UPDATE bookmarks SET nextId = '"+id+"' WHERE id = '"+prevId+"' AND userId = '"+user+"'");
        actions.addLast("UPDATE bookmarks SET parentId = '"+parentId+"' WHERE id = '"+id+"' AND userId = '"+user+"'");
        actions.addLast("UPDATE bookmarks SET nextId = '"+nextId+"' WHERE id = '"+lastInNodeId+"' AND userId = '"+user+"'");
        try {
            Statement statement = connection.createStatement();
            ResultSet result;
            String query;
            while(!id.equals(lastInNodeId)) {
                actions.addLast("UPDATE bookmarks SET deleted = '' WHERE id = '"+id+"' AND userId = '"+user+"'");
                query = "SELECT nextId from bookmarks WHERE id = '"+id+"' AND userId = '"+user+"'";
                result = statement.executeQuery(query);
                result.next();
                id = result.getString(1);
            }
            actions.addLast("UPDATE bookmarks SET deleted = '' WHERE id = '"+id+"' AND userId = '"+user+"'");
        } catch(SQLException e) {
            error = e.getMessage();
            return false;
        }
        return true;
    }
        
    public void cancelUpdate() {
        if(!pwok)
            return;
        actions.clear();
        expansions.clear();
    }
    
    public boolean setExpanded(String id, boolean expanded) {
        if(!pwok)
            return false;
        if(expansions.containsKey(id))
            expansions.remove(id);
        else
            expansions.put(id, expanded ? "expanded" : "");
        if(actions.isEmpty() && expansions.isEmpty())
            return false;
        else
            return true;
    }
    
    public boolean dbupdate() {
        if(!pwok)
            return false;
        int n = actions.size() + expansions.size();
        double i = 0;
        parent.progress(0);
        try {
            if(!actions.isEmpty()) {
                Statement statement;
                Iterator iter = actions.iterator();
                while(iter.hasNext()) {
                    statement = connection.createStatement();
                    statement.execute((String) iter.next());
                    parent.progress((int) (++i / n * 100));
                }
                actions.clear();
            }
            if(!expansions.isEmpty()) {
                PreparedStatement statement = connection.prepareStatement("UPDATE bookmarks SET url = ? WHERE id = ? AND userId = '"+user+"'");
                Set keys = expansions.keySet();
                Iterator iter = keys.iterator();
                String id;
                while(iter.hasNext()) {
                    id = (String) iter.next();
                    statement.setString(1, (String) expansions.get(id));
                    statement.setString(2, id);
                    statement.executeUpdate();
                    parent.progress((int) (++i / n * 100));
                }
                expansions.clear();
            }
        } catch(SQLException e) {
            error = e.getMessage();
            return false;
        }
        return true;
    }
    
    public boolean destroy() {
        String sql ="DELETE FROM bookmarks WHERE deleted='d' AND userId = '"+user+"'";
        try {
            Statement statement = connection.createStatement();
            statement.execute(sql);
            connection.close();
        } catch(SQLException e) {
            error = e.getMessage();
            return false;
        }
        return true;
    }

}