/* 
 * Diplomová práce ÚSI VUT 
 * Forenzní analýza webového prohlížeče
 *
 * Autor: Michal Bača
 * Datum: 20.5.2015
 */
package yafft.FFModel;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.net.MalformedURLException;
import java.net.URI;
import java.net.URISyntaxException;
import java.net.URL;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.util.Date;
import java.util.Iterator;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;
import javafx.beans.property.IntegerProperty;
import javafx.beans.property.ObjectProperty;
import javafx.beans.property.SimpleIntegerProperty;
import javafx.beans.property.SimpleLongProperty;
import javafx.beans.property.SimpleObjectProperty;
import javafx.beans.property.SimpleStringProperty;
import javafx.beans.property.StringProperty;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import org.json.simple.JSONArray;
import org.json.simple.JSONObject;
import org.json.simple.parser.JSONParser;
import org.json.simple.parser.ParseException;
import yafft.FFModel.ModelItems.Bookmark;
import yafft.FFModel.ModelItems.Cookie;
import yafft.FFModel.ModelItems.CookieValue;
import yafft.FFModel.ModelItems.DownloadsItem;
import yafft.FFModel.ModelItems.FormItem;
import yafft.FFModel.ModelItems.HistoryItem;
import yafft.FFModel.ModelItems.PasswordItem;
import yafft.Utils.HostInfo;

//http://www.tutorialspoint.com/sqlite/sqlite_java.htm
/**
 *
 * @author michal
 */
public class DataLoader {

    private String sqlXMLFile = "SQLstatements.xml";

    private HostInfo info;

    private Connection c = null;
    private Statement st = null;

    private final Properties SqlQueries;

    /**
     *
     * @param info
     */
    public DataLoader(HostInfo info) {
        this.SqlQueries = this.readSqlFromXml();
        this.info = info;
    }

    /**
     *
     * @param sqlXMLFile
     */
    public void setSqlXMLFile(String sqlXMLFile) {
        this.sqlXMLFile = sqlXMLFile;
    }

    /**
     *
     * @param index of profile
     * @return
     */
    public File getHistoryDB(Integer index) {
        return this.info.getFFProfiles().get(index).getHistory();
    }

    /**
     *
     * @return
     */
    public String getSqlXMLFile() {
        return sqlXMLFile;
    }

    /**
     *
     * @return
     */
    public Properties getSqlQueries() {
        return SqlQueries;
    }

    /**
     *
     * @return
     */
    public ObservableList<HistoryItem> getHistory(int i) {
        ObservableList<HistoryItem> history = FXCollections.observableArrayList();
        if (!info.getFFProfiles().get(i).getHistory().isFile()) {
            return history;
        }
        //load data from database
        ResultSet result = this.loadData(Features.HISTORY, info.getFFProfiles().get(i).getHistory(), SqlQueries.getProperty("history"));

        //parse results
        try {
            while (result.next()) {

                Timestamp ts;
                ts = new Timestamp(result.getLong(5));
                ObjectProperty<LocalDateTime> ld;
                ld = new SimpleObjectProperty(ts.toLocalDateTime());

                StringProperty date2 = new SimpleStringProperty(result.getString(1));
                // StringProperty date2 = new SimpleStringProperty(result.getDate(1).toString());

                URL url;
                ObjectProperty<URL> url2;
                url2 = new SimpleObjectProperty<>(this, result.getString(3));
                try {
                    url = new URL(result.getString(3));
                    url2.setValue(url);
                } catch (MalformedURLException ex) {
                    Logger.getLogger(DataLoader.class.getName()).log(Level.SEVERE, null, ex);
                }

                StringProperty title = new SimpleStringProperty(result.getString(2));
                if (title.getValue() == null) {
                    title.setValue("");
                }
                IntegerProperty visitCount = new SimpleIntegerProperty(result.getInt(4));

                HistoryItem hi = new HistoryItem(date2, url2, title, visitCount);

                history.add(hi);

            }
        } catch (SQLException ex) {
            Logger.getLogger(DataLoader.class.getName()).log(Level.SEVERE, null, ex);
        }

        return history;
    }

    public ObservableList<DownloadsItem> getDownloads(int i) {
        /*  if (info.getFFProfiles().get(i).getVersionInt() < 35) {
         return this.getDownloads26(i);
         } else {
         return this.getDownloads35(i);
         }*/

        ObservableList<DownloadsItem> finalList = FXCollections.observableArrayList();
        if (info.getFFProfiles().get(i).getDownloads().isFile()) {
            System.out.println("places.sql exists" + info.getFFProfiles().get(i).getDownloads());
            ObservableList<DownloadsItem> l1 = this.getDownloads26(i);
            finalList.addAll(l1);
        }

        if (info.getFFProfiles().get(i).getDownloads35().exists()) {

            ObservableList<DownloadsItem> l2 = this.getDownloads35(i);
            finalList.addAll(l2);
        }

        return finalList;

    }

    /**
     *
     * @return
     */
    private ObservableList<DownloadsItem> getDownloads26(int i) {
        ObservableList<DownloadsItem> downloads = FXCollections.observableArrayList();

        //load data from database
        ResultSet result = this.loadData(Features.DOWNLOADS, info.getFFProfiles().get(i).getDownloads(), SqlQueries.getProperty("downloads"));

        DownloadsItem di;
        StringProperty fileName;
        SimpleObjectProperty<URL> source;
        SimpleObjectProperty<URI> destination;
        SimpleLongProperty size = null;
        SimpleObjectProperty<Timestamp> tsp = null;

        JSONParser parser = new JSONParser();
        Object obj;
        JSONObject jObj;

        //parse results
        try {
            while (result.next()) {
                /*  
                 mp.url,         source URL
                 uri.content,    destination URI
                 file.content,   filename
                 info.content    JSON object - state, endTime, fileSize
                 */
                source = new SimpleObjectProperty<>(URL.class, result.getString(1));
                try {
                    //hodnota v Name
                    source.setValue(new URL(result.getString(1)));
                } catch (MalformedURLException ex) {
                    Logger.getLogger(DataLoader.class.getName()).log(Level.SEVERE, null, ex);
                }

                destination = new SimpleObjectProperty<>(URI.class, result.getString(2));
                //hodnota v Name
                try {
                    destination.setValue(new URI(result.getString(2)));
                } catch (URISyntaxException ex) {
                    Logger.getLogger(DataLoader.class.getName()).log(Level.SEVERE, null, ex);
                }

                //System.out.println("XXXXXXX: " + destination);
                fileName = new SimpleStringProperty(result.getString(3));

                try {
                    obj = parser.parse(result.getString(4));
                    jObj = (JSONObject) obj;

                    //size = new SimpleLongProperty((Long) jObj.get("fileSize"));
                    Long s = (Long) jObj.get("fileSize");
                    //System.out.println("YYYYYYYYYYYYYYYY "+ fileName + "; "+ s);

                    if (s != null) {
                        size = new SimpleLongProperty(s);
                        //System.out.println("AAAAAAAAAAA: " +size);
                    } else {
                        size = new SimpleLongProperty(-1L);
                    }

                    Long t = (Long) jObj.get("endTime");
                    if (t != null) {
                        tsp = new SimpleObjectProperty<>(new Timestamp(t));
                       //Hodnoty ve Value

                        //System.out.println("QQQQQQQQQ: " + tsp);
                    } else {
                        tsp = new SimpleObjectProperty<>(new Timestamp(0));
                    }

                } catch (ParseException ex) {
                    Logger.getLogger(DataLoader.class.getName()).log(Level.SEVERE, null, ex);
                }

                di = new DownloadsItem(fileName, source, destination, tsp, size);
                downloads.add(di);

            }
        } catch (SQLException ex) {
            Logger.getLogger(DataLoader.class.getName()).log(Level.SEVERE, null, ex);
        }

        return downloads;
    }

    /**
     * Downloads for FF version 36+
     *
     * @param i
     * @return
     */
    private ObservableList<DownloadsItem> getDownloads35(int i) {

        ObservableList<DownloadsItem> dwnlds = FXCollections.observableArrayList();

        DownloadsItem d;
        StringProperty fileName;
        SimpleObjectProperty<URL> source;
        SimpleObjectProperty<URI> destination = new SimpleObjectProperty<>();
        SimpleLongProperty size = null;
        SimpleObjectProperty<Timestamp> tsp = null;

        long l;

        String file;
        try {
            System.out.println("Importing downloads from " + info.getFFProfiles().get(i).getDownloads35().toPath());
            file = new String(Files.readAllBytes(info.getFFProfiles().get(i).getDownloads35().toPath()), StandardCharsets.UTF_8);

            JSONParser parser = new JSONParser();
            Object obj;
            JSONObject jObj;

            obj = parser.parse(file);
            jObj = (JSONObject) obj;

            JSONArray pwds = (JSONArray) jObj.get("list");

            Iterator it = pwds.iterator();

            while (it.hasNext()) {
                JSONObject dlItem = (JSONObject) it.next();
                JSONObject job;
                //System.out.println(pwdItem);

                job = (JSONObject) dlItem.get("target");
                File f = new File((String) job.get("path"));
                destination = new SimpleObjectProperty<>(f.toURI());

                fileName = new SimpleStringProperty((String) job.get("path"));

                job = (JSONObject) dlItem.get("source");
                source = new SimpleObjectProperty<>(new URL((String) job.get("url")));

                l = (Long) dlItem.get("totalBytes");
                size = new SimpleLongProperty(l);

                DateFormat df = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'");
                String s = (String) dlItem.get("startTime");

                Date res = df.parse(s);
                System.out.println(res);

                tsp = new SimpleObjectProperty<>(new Timestamp(res.getTime()));

                d = new DownloadsItem(fileName, source, destination, tsp, size);
                dwnlds.add(d);
            }

        } catch (IOException | ParseException | java.text.ParseException ex) {
            Logger.getLogger(DataLoader.class.getName()).log(Level.SEVERE, null, ex);
        }

        return dwnlds;
    }

    @Deprecated
    public ObservableList<Cookie> getCookies(int i) {
        ObservableList<Cookie> cookies = FXCollections.observableArrayList();
        if (!info.getFFProfiles().get(i).getCookies().isFile()) {
            return cookies;
        }

        //load data from database
        ResultSet result = this.loadData(Features.COOKIES, info.getFFProfiles().get(i).getCookies(), SqlQueries.getProperty("cookies"));
        String baseDomain = null;
        String baseDomainNext = null;

        Cookie c;
        CookieValue cv = null;

        //parse results
        try {
            while (result.next()) {
                /*
                 baseDomain (String), path (String), expiry (Timestamp) , creationTime (Timestamp), lastAccessed (Timestamp) ,
                 isSecure (Bool), isHttpOnly (Bool), name (String), value (String) 
                 */
                baseDomain = result.getString(1);
                c = new Cookie(baseDomain);
                //System.out.println("Cookie: " + baseDomain);

                do {
                    cv = new CookieValue(baseDomain, result.getString(2), result.getString(8), result.getString(9), new Timestamp(result.getLong("expiry") * 1000L), new Timestamp(result.getLong("creationTime") / 1000L));
                    c.addValue(cv);

                    result.next(); //move cursor to next result
                    baseDomainNext = result.getString(1); //baseDomain of the following row

                } while (baseDomainNext.equals(baseDomain));
                //different domain
                cookies.add(c);

            }
        } catch (SQLException ex) {
            Logger.getLogger(DataLoader.class.getName()).log(Level.SEVERE, null, ex);
        }

        return cookies;
    }

    public ObservableList<CookieValue> getCookies2(int i) {
        ObservableList<CookieValue> cookies = FXCollections.observableArrayList();
        if (!info.getFFProfiles().get(i).getCookies().isFile()) {
            return cookies;
        }

        //load data from database
        ResultSet result = this.loadData(Features.COOKIES, info.getFFProfiles().get(i).getCookies(), SqlQueries.getProperty("cookies"));
        String baseDomain = null;
        String baseDomainPrev = "#$$%^";

        CookieValue cv = null;

        //parse results
        try {
            while (result.next()) {
                /*
                 baseDomain (String), path (String), expiry (Timestamp) , creationTime (Timestamp), lastAccessed (Timestamp) ,
                 isSecure (Bool), isHttpOnly (Bool), name (String), value (String) 
                 */
                baseDomain = result.getString(1);
                cv = new CookieValue(baseDomain, result.getString(2), result.getString(8), result.getString(9), new Timestamp(result.getLong("expiry") * 1000L), new Timestamp(result.getLong("creationTime") / 1000L));

                if (!baseDomainPrev.equals(baseDomain)) {
                    cv.setRoot(true);
                    baseDomainPrev = baseDomain;
                };

                cookies.add(cv);

            }
        } catch (SQLException ex) {
            Logger.getLogger(DataLoader.class.getName()).log(Level.SEVERE, null, ex);
        }

        return cookies;
    }

    public ObservableList<FormItem> getForms(int i) {
        ObservableList<FormItem> forms = FXCollections.observableArrayList();
        if (!info.getFFProfiles().get(i).getForms().isFile()) {
            return forms;
        }

        //load data from database
        ResultSet result = this.loadData(Features.FORMS, info.getFFProfiles().get(i).getForms(), SqlQueries.getProperty("forms"));
        FormItem f;

        //parse results
        try {
            while (result.next()) {
                /*
                 fieldname (string), value (string), timesUsed (int), firstUsed (timestamp), lastUsed (timestamp)
                 */
                f = new FormItem(result.getString(1), result.getString(2), result.getInt(3), new Timestamp(result.getLong(4)), new Timestamp(result.getLong(5)));
                //System.out.println(f.toString(true));
                forms.add(f);

            }
        } catch (SQLException ex) {
            Logger.getLogger(DataLoader.class.getName()).log(Level.SEVERE, null, ex);
        }

        return forms;
    }

    public ObservableList<Bookmark> getBookmarks(int i) {
        ObservableList<Bookmark> bookmarks = FXCollections.observableArrayList();
        if (!info.getFFProfiles().get(i).getPlaces().isFile()) {
            return bookmarks;
        }


        //load data from database
        ResultSet result = this.loadData(Features.FORMS, info.getFFProfiles().get(i).getPlaces(), SqlQueries.getProperty("bookmarks"));
        Bookmark b;

        SimpleObjectProperty<URL> url;
        SimpleObjectProperty<Timestamp> added;
        SimpleObjectProperty<Timestamp> modified;
        StringProperty name;

        //parse results
        try {
            while (result.next()) {
                // moz_places.url (URL) ,moz_bookmarks.title (String) ,moz_bookmarks.dateAdded (timestamp) , moz_bookmarks.lastModified (timestamp)

                name = new SimpleStringProperty(result.getString(2));

                if (result.getString(1).startsWith("place:")) {
                    //System.out.println("-------------Skipping line in bookmarks " + name);
                    continue;
                }

                url = new SimpleObjectProperty<>(URL.class, result.getString(1));
                url.setValue(new URL(result.getString(1)));

                added = new SimpleObjectProperty<>(new Timestamp(result.getLong(3) / 1000L));
                modified = new SimpleObjectProperty<>(new Timestamp(result.getLong(4) / 1000L));

                bookmarks.add(new Bookmark(url, name, added, modified));

            }
        } catch (SQLException | MalformedURLException ex) {
            Logger.getLogger(DataLoader.class.getName()).log(Level.SEVERE, null, ex);
        }

        return bookmarks;
    }

    /**
     *
     * @return
     */
    public ObservableList<PasswordItem> getPasswords(int i) {

        ObservableList<PasswordItem> passwords = FXCollections.observableArrayList();
        if (!info.getFFProfiles().get(i).getPasswords().isFile()) {
            return passwords;
        }

        PasswordItem p;
        StringProperty fieldName;
        StringProperty username;
        StringProperty password;
        SimpleObjectProperty<URL> hostname;

        SimpleIntegerProperty timesUsed = null;
        SimpleObjectProperty<Timestamp> created = null;
        SimpleObjectProperty<Timestamp> lastUsed = null;
        SimpleObjectProperty<Timestamp> lastChanged = null;

        String file;
        try {
            file = new String(Files.readAllBytes(info.getFFProfiles().get(i).getPasswords().toPath()), StandardCharsets.UTF_8);

            JSONParser parser = new JSONParser();
            Object obj;
            JSONObject jObj;

            obj = parser.parse(file);
            jObj = (JSONObject) obj;

            JSONArray logins = (JSONArray) jObj.get("logins");

            Iterator it = logins.iterator();

            while (it.hasNext()) {
                JSONObject pwdItem = (JSONObject) it.next();
                //System.out.println(pwdItem);

                fieldName = new SimpleStringProperty((String) pwdItem.get("usernameField"));
                username = new SimpleStringProperty((String) pwdItem.get("encryptedUsername"));
                password = new SimpleStringProperty((String) pwdItem.get("encryptedPassword"));

                hostname = new SimpleObjectProperty<>(new URL((String) pwdItem.get("hostname")));

                Long l = (Long) pwdItem.get("timesUsed");
                timesUsed = new SimpleIntegerProperty(l.intValue());

                Long t = (Long) pwdItem.get("timeCreated");
                created = new SimpleObjectProperty<>(new Timestamp(t));

                t = (Long) pwdItem.get("timeLastUsed");
                lastUsed = new SimpleObjectProperty<>(new Timestamp(t));

                t = (Long) pwdItem.get("timePasswordChanged");
                lastChanged = new SimpleObjectProperty<>(new Timestamp(t));

                /*   
                 System.out.println("Fieldname: " + fieldName);
                 System.out.println("Userneme: " + username);
                 System.out.println("password: " + password);
                 System.out.println("hostname: " + hostname);
                 System.out.println("timesUsed: " + timesUsed);
                 System.out.println("created: " + created);
                 System.out.println("lastUsed: " + lastUsed);
                 System.out.println("lastChanged: " + lastChanged + "\n==========================");
                
                 */
                p = new PasswordItem(hostname, username, fieldName, password, created, lastUsed, lastChanged, timesUsed);
                passwords.add(p);
            }

        } catch (IOException | ParseException ex) {
            Logger.getLogger(DataLoader.class.getName()).log(Level.SEVERE, null, ex);
        }

        return passwords;
    }

    /**
     * Load data from DB specified by pathToDb and return generic ResultSet for
     * further processing by type
     *
     * @param type type of data (class Features)
     * @param pathToDb path to db file
     * @param query SQL query to perform
     * @return ResultSet
     */
    private ResultSet loadData(Features type, File pathToDb, String query) {
        ResultSet result = null;

        try {
            Class.forName("org.sqlite.JDBC");
            c = DriverManager.getConnection("jdbc:sqlite:" + pathToDb);

        } catch (ClassNotFoundException | SQLException e) {
            System.err.println(e.getClass().getName() + ": " + e.getMessage());
            System.out.println("Driver not available");
        }

        try {
            st = c.createStatement();
            result = st.executeQuery(query);

            /*while (result.next()) {
             //case history
             //System.out.println(result.getString("title"));
                
             //case etc
             }
             */
        } catch (Exception e) {
            System.err.println(e.getClass().getName() + ": " + e.getMessage());

        }

        return result;
    }

    /**
     * Read SQL queries from XML Properties file
     *
     * @return
     */
    private Properties readSqlFromXml() {
        Properties p = new Properties();
        try {

            InputStream is = this.getClass().getResourceAsStream(this.sqlXMLFile);
            p.loadFromXML(is);
            p.list(System.out);

        } catch (IOException ex) {
            Logger.getLogger(DataLoader.class.getName()).log(Level.SEVERE, null, ex);
        }
        return p;
    }
}
