/**
 * MailArchiver is an application that provides services for storing and managing e-mail messages through a Web Services SOAP interface.
 * Copyright (C) 2012  Marcio Andre Scholl Levien and Fernando Alberto Reuter Wendt and Jose Ronaldo Nogueira Fonseca Junior
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Affero General Public License as
 * published by the Free Software Foundation, either version 3 of the
 * License, or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU Affero General Public License for more details.
 *
 * You should have received a copy of the GNU Affero General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */

/******************************************************************************\
*
*  This product was developed by
*
*        SERVIÇO FEDERAL DE PROCESSAMENTO DE DADOS (SERPRO),
*
*  a government company established under Brazilian law (5.615/70),
*  at Department of Development of Porto Alegre.
*
\******************************************************************************/

package serpro.mailarchiver.util;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Set;

public class Sqlite {

    public static final String PERMISSIONS_DATABASE_NAME = "permissions.db";

    public static final String LOCALSERVER_DATABASE_NAME = "localserver.db";

    private String browserArchiverPath = null;

    private static Sqlite instance = new Sqlite();

    private static final Logger log = Logger.getLocalLogger();

    public static Sqlite getInstance(){
        try {
            Class.forName("org.sqlite.JDBC");
            if(instance!=null){
                return instance;
            }
        } catch (ClassNotFoundException ex) {
            log.error(ex);
        }
        return new Sqlite();
    }

    public void setBrowserArchiverPath(String browserArchiverPath) {
        this.browserArchiverPath = browserArchiverPath;
        log.debug("Configurando origem do arquivamento gears..: " + browserArchiverPath);
    }

    public Map<String, List<String>> listDatabasePaths(){

//        System.out.println("Sqlite.listDatabasePaths sendo executado... ");

        log.debug("Sqlite.listDatabasePaths sendo executado... ");

        Map<String, List<String>> result = new HashMap<String, List<String>>();

        try{
            if(browserArchiverPath!=null){
                String databasePermissions = GearsUtil.loadFilePaths(browserArchiverPath, PERMISSIONS_DATABASE_NAME);

                Connection connection = DriverManager.getConnection("jdbc:sqlite:" + databasePermissions);

//                System.out.println("Consultando databaseNames... ");

                log.debug("Consultando databaseNames... ");

                ResultSet rs = connection.createStatement().
                    executeQuery("select d.origin, d.basename from databasenames as d, "
                        + "access a where d.origin=a.name "
                        + "and d.isdeleted=0");

//                System.out.println("Databases encontrados no path de arquivamento..: " + browserArchiverPath);

                List<String> databasePaths = null; //armazenar os databasePaths por origin url

                while(rs.next()){
                    int index = rs.getString(1).indexOf("//");

                    String origin = null;

                    String protocol = null;

                    String databasePath = null;

                    if(index!=-1){

                        if(rs.getString(1).contains("https")){
                            protocol = File.separatorChar + "https_443";
                        }else{
                            protocol = File.separatorChar + "http_80";
                        }

                        index++;

                        origin = browserArchiverPath + rs.getString(1).substring(index++, rs.getString(1).length());

                        origin += protocol;

                        if(origin!=null && origin.length()>0 && !result.containsKey(origin)){

                            databasePaths = new ArrayList<String>();

                            databasePath = GearsUtil.loadFilePaths(origin, rs.getString(2));

                            if(databasePath!=null){

                                databasePaths.add(databasePath);

//                                System.out.println("Origem arquivamento..: " + origin + " DatabasePath do arquivamento..: " + databasePath);

                                log.debug("Origem arquivamento..: " + origin + " DatabasePath do arquivamento..: " + databasePath);
                            }
                            result.put(origin, databasePaths);
                        }else if(result.containsKey(origin)){
                            databasePath = GearsUtil.loadFilePaths(origin, rs.getString(2));

                            databasePaths.add(databasePath);//apenas adiciona databasePaths ao map

//                            System.out.println("Origem arquivamento..: " + origin + " DatabasePath do arquivamento..: " + databasePath);

                            log.debug("Origem arquivamento..: " + origin + " DatabasePath do arquivamento..: " + databasePath);
                        }
                    }
                }
                rs.close();
                connection.close();
            }
        }catch (SQLException ex) {
            log.error("Falha na obtenção dos databaseNames da origem de arquivamento gears..: " + browserArchiverPath, ex);
        }
//        System.out.println("Sqlite.listDatabasePaths sendo finalizado... ");

        log.debug("Sqlite.listDatabasePaths sendo finalizado... ");

        return result;
   }

    // valida se a estrutura de banco sqlite é utilizada pela suite expresso
    public boolean isExpressoDatabase(String databasePath){

//        System.out.println("Sqlite.isExpressoDatabase sendo executado... ");

        log.debug("Sqlite.isExpressoDatabase sendo executado... ");

        boolean result = false;

        try{
            Connection connection = DriverManager.getConnection("jdbc:sqlite:" + databasePath);

//            System.out.println("Sqlite Driver Version..: " + connection.getMetaData().getDriverVersion());

//            System.out.println("Validando estrutura da tabela.: " + databasePath + "... ");

            log.debug("Validando estrutura da tabela.: " + databasePath + "... ");

            //#define SQLITE_CORRUPT     11   /* The database disk image is malformed */
            ResultSet rs = connection.createStatement().
                executeQuery(
                    "select name from sqlite_master where type='table' order by name;");

            int index=0;

            while(rs.next()){

                if(rs.getString(1).equalsIgnoreCase("anexo")
                        || rs.getString(1).equalsIgnoreCase("folder")
                        || rs.getString(1).equalsIgnoreCase("mail")){
                    index++;
                    if(index==3) break;
                }
            }
            if(index == 3){

//                System.out.println("Estrutura da tabela foi validada com sucesso! ");

                log.debug("Estrutura da tabela foi validada com sucesso! ");

                createArchiveImporter(connection, databasePath); //cria a tabela de controle caso ainda não exista

                result = true;

            }else{
//                System.out.println("Estrutura da tabela inválida! ");

                log.debug("Estrutura da tabela inválida! ");
            }

            rs.close();

            connection.close();

        }catch (SQLException ex) {

            log.error("Falha na validação da estrutura sqlite referente ao databasePath..: " + databasePath, ex);
        }

//        System.out.println("Sqlite.isExpressoDatabase sendo finalizado... ");

        log.debug("Sqlite.isExpressoDatabase sendo finalizado... ");

        return result;
    }


    public boolean isUserDatabase(String databasePath, String user){

        System.out.println("Sqlite.isUserDatabase sendo executado... ");

        log.debug("Sqlite.isUserDatabase sendo executado... ");

        boolean result = false;

        try{
            Connection connection = DriverManager.getConnection("jdbc:sqlite:" + databasePath);

            ResultSet rs = connection.createStatement().executeQuery("select count(*) from folder where uid_usuario='" + user +  "'");

            System.out.println("Consultando folders para o usuário " + user + " ... ");

            log.debug("Consultando folders para o usuário " + user + " ... ");

            int folders = Integer.parseInt(rs.getString(1));

            if(folders > 0){
                result = true;
            }

            rs.close();

            connection.close();

        }catch (SQLException ex) {
            log.error("Falha ao consultar permissão de usuário em folders para o databasePath..: " + databasePath, ex);
        }

        System.out.println("Sqlite.isUserDatabase sendo finalizado... ");

        log.debug("Sqlite.isUserDatabase sendo finalizado... ");

        return result;
    }

    // seleciona todos os folders(names) dos folders encontrados
    public Set<String> listFolders(String databasePath, String user){

//        System.out.println("Sqlite.listFolders sendo executado... ");

        log.debug("Sqlite.listFolders sendo executado... ");

        Set<String> result = new HashSet<String>();

        try{
            Connection connection = DriverManager.getConnection("jdbc:sqlite:" + databasePath);

            ResultSet rs = connection.createStatement().executeQuery("select folder from folder where uid_usuario='" + user + "' order by rowid");

//            System.out.println("Consultando folders... ");

            log.debug("Consultando folders... ");

            while(rs.next())
                result.add(rs.getString(1));

            rs.close();

            connection.close();

        }catch (SQLException ex) {
            log.error("Falha na listagem de folder para o databasePath..: " + databasePath, ex);
        }

//        System.out.println("Sqlite.listFolders sendo finalizado... ");

        log.debug("Sqlite.listFolders sendo finalizado... ");

        return result;
    }

    // seleciona todos os folders(names) já criados pelo mailarchiver
    // por usuário e por mailArcID
    public Set<String> listArchivedFolders(String databasePath, String mailArcID, String user){

//        System.out.println("Sqlite.listArchivedFolders sendo executado... ");

        log.debug("Sqlite.listArchivedFolders sendo executado... ");

        Set<String> result = new HashSet<String>();

        try{
            Connection connection = DriverManager.getConnection("jdbc:sqlite:" + databasePath);

            ResultSet rs = connection.createStatement().executeQuery("select folder from user_archive_importer "
                    + "where imported='true' and uid_usuario='" + user +  "' order by rowid");

//            System.out.println("Consultando folders arquivados... ");

            log.debug("Consultando folders arquivados... ");

            while(rs.next())
                result.add(rs.getString(1));

            rs.close();

            connection.close();

        }catch (SQLException ex) {
            log.error("Falha na listagem de folder arquivado para o databasePath..: " + databasePath, ex);
        }

//        System.out.println("Sqlite.listArchivedFolders sendo finalizado... ");

        log.debug("Sqlite.listArchivedFolders sendo finalizado... ");

        return result;
    }

    // carrega o total de mensagens por databasePath
    public Integer getTotalMessages(String databasePath, String mailArcID, String user){

//        System.out.println("Sqlite.getTotalMessages sendo executado... ");

        log.debug("Sqlite.getTotalMessages sendo executado... ");

        Integer result = 0;

        try{
            Connection connection = DriverManager.getConnection("jdbc:sqlite:" + databasePath);

            ResultSet rs = connection.createStatement().
                    executeQuery("select count(*) rowid from mail where rowid not in("
                      + "select archive.mailid from user_archive_importer as archive, mail as mail "
                      + "where mail.rowid = archive.mailid and archive.uid_usuario='" + user + "' "
                      + "and archive.mailarcid='" + mailArcID + "') and uid_usuario='" + user + "'");

            while(rs.next()){
                result = Integer.parseInt(rs.getString(1));
            }

//            System.out.println(result + " emails contabilizados para o databasePath " + databasePath);

            log.debug(result + " emails contabilizados para o databasePath " + databasePath);

            rs.close();

            connection.close();

        }catch (SQLException ex) {
            log.error("Falha na contagem de emails para o databasePath..: " + databasePath, ex);
        }

//        System.out.println("Sqlite.getTotalMessages sendo finalizado... ");

        log.debug("Sqlite.getTotalMessages sendo finalizado... ");

        return result;
    }

    // carrega o total de mensagens validas por databasePath para importação pelo mailarchiver
    public Integer getTotalValidMessages(String databasePath, String mailArcID, String user){

//        System.out.println("Sqlite.getTotalValidMessages sendo executado... ");

        log.debug("Sqlite.getTotalValidMessages sendo executado... ");

        Integer result = 0;

        try{
            Connection connection = DriverManager.getConnection("jdbc:sqlite:" + databasePath);

            ResultSet rs = connection.createStatement().executeQuery("select count(*) rowid from user_archive_importer "
                    + "where imported='false' and uid_usuario='" + user + "' and mailarcid='" + mailArcID + "'");

            while(rs.next()){
                result = Integer.parseInt(rs.getString(1));
            }

//            System.out.println(result + " emails válidos contabilizados para o databasePath " + databasePath);

            log.debug(result + " emails válidos contabilizados para o databasePath " + databasePath);

            rs.close();

            connection.close();

        }catch (SQLException ex) {
            log.error("Falha na contagem de emails para o databasePath..: " + databasePath, ex);
        }

//        System.out.println("Sqlite.getTotalValidMessages sendo finalizado... ");

        log.debug("Sqlite.getTotalValidMessages sendo finalizado... ");

        return result;
    }

    // leitura dos dados da tabela mail a serem importados por folder
    public Map<String, Map<String, String>> listMessages(String databasePath, String folder, String mailArcID, String user){

//        System.out.println("Sqlite.listMessages sendo executado... ");

        log.debug("Sqlite.listMessages sendo executado... ");

        Map<String, Map<String, String>> result = new HashMap<String, Map<String, String>>();

        try{
            Connection connection = DriverManager.getConnection("jdbc:sqlite:" + databasePath);

            ResultSet rs = connection.createStatement().
                    executeQuery("select m.rowid, m.subject, m.mail from mail as m, folder as f "
                    + "where m.id_folder = f.rowid and f.folder ='" + folder + "' "
                    + "and m.rowid not in(select archive.mailid from "
                    + "user_archive_importer as archive, mail as mail "
                    + "where mail.rowid = archive.mailid "
                    + "and archive.uid_usuario='" + user + "' "
                    + "and archive.mailarcid='" + mailArcID + "') and m.uid_usuario='" + user + "'");

//            System.out.println("Consultando emails... ");

            log.debug("Consultando emails... ");

//            int messages = 0;

            while(rs.next())   {

                Map values = new HashMap<String, String>();

                values.put(rs.getString(2), rs.getString(3));

                result.put(rs.getString(1), values);

//                messages++;
            }

            rs.close();

            connection.close();

        }catch (SQLException ex) {
            log.error("Falha na listagem de mensagens do folder " + folder + " referentes ao databasePath..: " + databasePath, ex);
        }

//        System.out.println("Sqlite.listMessages sendo finalizado... ");

        log.debug("Sqlite.listMessages sendo finalizado... ");

        return result;
    }
    
    // carrega o filepath extraido da urlExportFile passada como parâmetro
    public String getResponseBodiesFilePath(String urlExportFile) throws SQLException{

//        System.out.println("Sqlite.getResponseBodiesFilePath sendo executado... ");

        log.debug("Sqlite.getResponseBodiesFilePath sendo executado... ");

        String result = null;

        String databasePath = GearsUtil.loadFilePaths(browserArchiverPath, LOCALSERVER_DATABASE_NAME);

        Connection connection = DriverManager.getConnection("jdbc:sqlite:" + databasePath);

        ResultSet rs = connection.createStatement().
                executeQuery("select resp.filepath from responsebodies as resp, entries as ent "
                    + "where ent.payloadid=resp.bodyid "
                    + "and ent.url like '%" + urlExportFile + "'");

        if(rs.getString(1)!=null && rs.getString(1).length()>0){
            result = rs.getString(1);
        }

        rs.close();

        connection.close();

//        System.out.println("Sqlite.getResponseBodiesFilePath sendo finalizado... ");

        log.debug("Sqlite.getResponseBodiesFilePath sendo finalizado... ");

        //Ajusta o caminho correto para a carga do arquivo fonte da mensagem, de acordo com o sistema operacional em uso
        //implementado para possibilitar a importação de mensagens caso o usuário copie o seu Gears do Windows para o Linux
       result = result.replace('\\', '/');
       log.debug("ImportGears: ajustado o URLFilePath de acordo com o sistema operacional ::'" + result + "'");
       return result;
    }

    // cria tabela de controle de importação de emails do google gears
    public void createArchiveImporter(Connection connection,String databasePath){

//        System.out.println("Sqlite.createArchiveImporter sendo executado... ");

        log.debug("Sqlite.createArchiveImporter sendo executado... ");

        try{
//            Connection connection = DriverManager.getConnection("jdbc:sqlite:" + databasePath);
//
//            System.out.println("Criando tabela de controle archive_importer em..: " + databasePath);

            log.debug("Criando tabela de controle user_archive_importer... ");

            connection.createStatement().
                executeUpdate("create table if not exists user_archive_importer(mailid text,mailarcid text,"
                    + "folder text,filepath text,imported boolean,import_date text,uid_usuario text,unique(mailid,mailarcid,uid_usuario))");

//            System.out.println("Tabela de controle archive_importer criada. ");

            log.debug("Tabela de controle user_archive_importer criada. ");

//            connection.close();

        }catch (SQLException ex) {
            log.error("Falha na criação da tabela de controle de arquivamento user_archive_importer", ex);
        }
//        System.out.println("Sqlite.createArchiveImporter sendo finalizado... ");

        log.debug("Sqlite.createArchiveImporter sendo finalizado... ");
    }

    // insere registro de controle de importação de emails do google gears
    // utiliza conexão já ativa
    public boolean insertMessageParameters(String databasePath, String folder, String mailId, String mailarcId, String filePath, String user) throws SQLException{

//        System.out.println("Sqlite.insertMessageParameters sendo executado... ");

        log.debug("Sqlite.insertMessageParameters sendo executado... ");

        boolean result = false;

//        System.out.println("Inserindo registro na tabela de controle archive_importer... ");

        log.debug("Inserindo registro na tabela de controle user_archive_importer... ");

        Connection connection = DriverManager.getConnection("jdbc:sqlite:" + databasePath);

        connection.createStatement().
                executeUpdate("BEGIN TRANSACTION; "
                                + "insert into user_archive_importer values('"
                                + mailId + "','" + mailarcId + "','" + folder + "','"
                                + filePath + "','" + false + "','"
                                + new SimpleDateFormat("dd-MMMM-yyyy HH:mm:ss").
                                format(new Date()) + "','" + user + "'); "
                            + "COMMIT;");

        result = true;

//        System.out.println("Tabela de controle archive_importer atualizada. ");

        log.debug("Tabela de controle user_archive_importer atualizada. ");

        connection.close();

//        System.out.println("Sqlite.insertMessageParameters sendo finalizado... ");

        log.debug("Sqlite.insertMessageParameters sendo finalizado... ");

        return result;
    }

     // atualiza tabela de controle de importação de emails do google gears após importação pelo mailarchiver
    public void updateMessageParameters(String databasePath, String mailId, String mailarcId, String user) throws SQLException{

//        System.out.println("Sqlite.updateMessageParameters sendo executado... ");

        log.debug("Sqlite.updateMessageParameters sendo executado... ");

//        System.out.println("Atualizando a tabela de controle archive_importer ... ");

        log.debug("Atualizando a tabela de controle user_archive_importer ... ");

        Connection connection = DriverManager.getConnection("jdbc:sqlite:" + databasePath);

        connection.createStatement().
                executeUpdate("BEGIN TRANSACTION; "
                                + "update user_archive_importer set imported='true', import_date='"
                                + new SimpleDateFormat("dd-MMMM-yyyy HH:mm:ss").format(new Date()) + "' "
                                + "where mailid='" + mailId + "' and uid_usuario='" + user + "' "
                                + "and mailarcid='" + mailarcId + "'; "
                            + "COMMIT;");

//        System.out.println("Tabela de controle archive_importer atualizada. ");

        log.debug("Tabela de controle user_archive_importer atualizada. ");

        connection.close();

//        System.out.println("Sqlite.updateMessageParameters sendo finalizado... ");

        log.debug("Sqlite.updateMessageParameters sendo finalizado... ");
    }

    // deleta os registros da tabela de controle de importação de emails do google gears para o mailarcid passado
    public void deleteMessageParameters(String databasePath, String mailarcId, String user) throws SQLException{

//        System.out.println("Sqlite.deleteMessageParameters sendo executado... ");

        log.debug("Sqlite.deleteMessageParameters sendo executado... ");

//        System.out.println("Excluindo registros da tabela de controle archive_importer ... ");

        log.debug("Excluindo registros da tabela de controle user_archive_importer ... ");

        Connection connection = DriverManager.getConnection("jdbc:sqlite:" + databasePath);

        connection.createStatement().
                executeUpdate("delete from user_archive_importer where mailarcid='" + mailarcId + "' and uid_usuario='" + user + "'");

//        System.out.println("Tabela de controle archive_importer atualizada. ");

        log.debug("Tabela de controle user_archive_importer atualizada. ");

        connection.close();

//        System.out.println("Sqlite.deleteMessageParameters sendo finalizado... ");

        log.debug("Sqlite.deleteMessageParameters sendo finalizado... ");
    }

    // lista os registros da tabela de controle de importação de emails do google gears para o mailarcid passado
    public LinkedList<String[]> listMessageParameters(String databasePath, String mailArcId, String user){

//        System.out.println("Sqlite.listMessageParameters sendo executado... ");

        log.debug("Sqlite.listMessageParameters sendo executado... ");

        LinkedList<String[]> result = new LinkedList<String[]>();//manter a ordem de folder

        try{
//            System.out.println("Listando registros da tabela de controle archive_importer ... ");

            log.debug("Listando registros da tabela de controle user_archive_importer ... ");

            Connection connection = DriverManager.getConnection("jdbc:sqlite:" + databasePath);

            ResultSet rs = connection.createStatement().executeQuery("select archive.folder, archive.mailid, archive.filepath, m.header, m.unseen from user_archive_importer as archive, mail as m "
                    + "where archive.mailid=m.rowid and archive.mailarcid='" + mailArcId + "' and archive.imported='false' and archive.uid_usuario='" + user + "' order by archive.folder");

            while(rs.next()){
                result.add(new String[]{rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5)});
            }

            connection.close();

        }catch (SQLException ex) {
            log.error("Falha na listagem de registros da tabela user_archive_importer para o mailArcId..: " + mailArcId, ex);
        }
//        System.out.println("Sqlite.listMessageParameters sendo finalizado... ");

        log.debug("Sqlite.listMessageParameters sendo finalizado... ");

        return result;
    }
}