/*
 * Copyright
 *   2008 axYus - www.axyus.com
 *   2008 C.Bosquet - charles.bosquet@axyus.com
 *
 * This file is part of XEMELIOS.
 *
 * XEMELIOS is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 2 of the License, or
 * (at your option) any later version.
 *
 * XEMELIOS 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 General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with XEMELIOS; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
 */
package fr.gouv.finances.dgfip.xemelios.auth;

import fr.gouv.finances.cp.xemelios.auth.dgcp.NewAdmin;
import fr.gouv.finances.dgfip.utils.Pair;
import fr.gouv.finances.dgfip.xemelios.auth.impl.std.db.ui.DataHelper;
import fr.gouv.finances.dgfip.xemelios.common.Crypter;
import fr.gouv.finances.dgfip.xemelios.data.impl.pool.PoolManager;
import fr.gouv.finances.dgfip.xemelios.data.utils.jdbc.JdbcUtils;
import fr.gouv.finances.dgfip.xemelios.data.utils.jdbc.PStmtBinder.SimplePStmtBinderBuilder;
import fr.gouv.finances.dgfip.xemelios.data.utils.jdbc.RowMapper;
import fr.gouv.finances.dgfip.xemelios.data.utils.jdbc.RowMapper.ColToIntegerRowMapper;
import fr.gouv.finances.dgfip.xemelios.data.utils.jdbc.RowMapper.ColToLongRowMapper;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

/**
 * @author CBO
 */
public class DbAuthEngineHelper {

    static public void checkDBStruct() throws SQLException {
        Connection connection = null;
        long count = 0;
        try {
            connection = PoolManager.getInstance().getConnection();
            connection.setAutoCommit(false);
            // Création des tables :
            JdbcUtils.executeUpdate(connection, "CREATE TABLE IF NOT EXISTS AUTH_COLLECTIVITE (TID_CODE varchar(20) not null, COL_CLEF varchar(50) not null, COL_LIBELLE varchar(100) not null, primary key (TID_CODE, COL_CLEF)) type = MYISAM");
            JdbcUtils.executeUpdate(connection, "CREATE TABLE IF NOT EXISTS AUTH_FONCTIONNALITE (FCT_CODE varchar(20) not null, FCT_LIBELLE varchar(100), primary key (FCT_CODE)) type = MYISAM");
            JdbcUtils.executeUpdate(connection, "CREATE TABLE IF NOT EXISTS AUTH_HAB_COL (UTI_ID int not null, TID_CODE varchar(20) not null, COL_CLEF varchar(50) not null, primary key (TID_CODE, UTI_ID, COL_CLEF), index AUTH_HAB_COL_UTI_FK(UTI_ID), index AUTH_HAB_COL_COL_FK(TID_CODE, COL_CLEF)) type = MYISAM");
            JdbcUtils.executeUpdate(connection, "CREATE TABLE IF NOT EXISTS AUTH_HAB_DOC (UTI_ID int not null, DOC_CODE varchar(100) not null, primary key (UTI_ID, DOC_CODE), index AUTH_HAB_DOC_UTI_FK(UTI_ID)) type = MYISAM");
            JdbcUtils.executeUpdate(connection, "CREATE TABLE IF NOT EXISTS AUTH_PRF_FCT (PRF_CODE varchar(20) not null, FCT_CODE varchar(20) not null, primary key (PRF_CODE, FCT_CODE), index AUTH_PRF_FCT_PRF_FK(PRF_CODE), index AUTH_PRF_FCT_FCT_FK(FCT_CODE) ) type = MYISAM");
            JdbcUtils.executeUpdate(connection, "CREATE TABLE IF NOT EXISTS AUTH_PROFIL (PRF_CODE varchar(20) not null, PRF_LIBELLE varchar(100) not null, primary key (PRF_CODE)) type = MYISAM");
            count = ((Long)JdbcUtils.queryObject(connection, "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = schema() and upper(table_name) = 'AUTH_UTILISATEUR'", new ColToLongRowMapper()));
            boolean tableUtilisateurExist = count > 0;
            if (! tableUtilisateurExist) {
                JdbcUtils.executeUpdate(connection, "CREATE TABLE IF NOT EXISTS AUTH_UTILISATEUR (UTI_ID int not null AUTO_INCREMENT, UTI_LOGIN varchar(50) not null, UTI_PASSWORD varchar(50), UTI_PRENOM varchar(50), UTI_NOM varchar(50), UTI_ACTIF int not null, UTI_ALL_FONC int not null, UTI_ALL_DOC int not null, UTI_ALL_COL int not null, primary key (UTI_ID), index AUTH_UTI_LOGIN_IDX(UTI_LOGIN)) type = MYISAM");
                JdbcUtils.executeUpdate(connection, "ALTER TABLE AUTH_UTILISATEUR AUTO_INCREMENT = 10");
            }
            JdbcUtils.executeUpdate(connection, "CREATE TABLE IF NOT EXISTS AUTH_UTI_PRF( UTI_ID int not null, PRF_CODE varchar(20) not null, primary key (UTI_ID, PRF_CODE), index AUTH_UTI_PRF_UTI_FK(UTI_ID), index AUTH_UTI_PRF_PRF_FK(PRF_CODE)) type = MYISAM");
            // Création des fonctionnalités si besoin :
            long fonctionnaliteCount = (Long)JdbcUtils.queryObject(connection, "SELECT COUNT(*) FROM AUTH_FONCTIONNALITE", new ColToLongRowMapper());
            if (fonctionnaliteCount <= 0) {
                JdbcUtils.executeUpdate(connection, "INSERT INTO AUTH_FONCTIONNALITE (FCT_CODE, FCT_LIBELLE) VALUES (?, ?)",
                        new SimplePStmtBinderBuilder().add("CONNECT").add("Connexion").toPStmtBinder());
                JdbcUtils.executeUpdate(connection, "INSERT INTO AUTH_FONCTIONNALITE (FCT_CODE, FCT_LIBELLE) VALUES (?, ?)",
                        new SimplePStmtBinderBuilder().add("SEARCH").add("Recherche").toPStmtBinder());
                JdbcUtils.executeUpdate(connection, "INSERT INTO AUTH_FONCTIONNALITE (FCT_CODE, FCT_LIBELLE) VALUES (?, ?)",
                        new SimplePStmtBinderBuilder().add("BROWSE").add("Navigation").toPStmtBinder());
                JdbcUtils.executeUpdate(connection, "INSERT INTO AUTH_FONCTIONNALITE (FCT_CODE, FCT_LIBELLE) VALUES (?, ?)",
                        new SimplePStmtBinderBuilder().add("IMPORT").add("Import").toPStmtBinder());
                JdbcUtils.executeUpdate(connection, "INSERT INTO AUTH_FONCTIONNALITE (FCT_CODE, FCT_LIBELLE) VALUES (?, ?)",
                        new SimplePStmtBinderBuilder().add("EXPORT").add("Export").toPStmtBinder());
                JdbcUtils.executeUpdate(connection, "INSERT INTO AUTH_FONCTIONNALITE (FCT_CODE, FCT_LIBELLE) VALUES (?, ?)",
                        new SimplePStmtBinderBuilder().add("CLEAN").add("Suppression").toPStmtBinder());
                JdbcUtils.executeUpdate(connection, "INSERT INTO AUTH_FONCTIONNALITE (FCT_CODE, FCT_LIBELLE) VALUES (?, ?)",
                        new SimplePStmtBinderBuilder().add("USER_ADMIN").add("Administration utilisateurs").toPStmtBinder());
            }
            count = ((Long)JdbcUtils.queryObject(connection, "SELECT COUNT(*) FROM AUTH_UTILISATEUR", new ColToLongRowMapper()));
            connection.commit();
        } finally {
            PoolManager.getInstance().releaseConnection(connection);
        }
        if(count==0) {
            // il faut créer un admin
            NewAdmin na = new NewAdmin(null);
            Object splash = null;
            Method setVisible = null;
            try {
                Class clazz = Class.forName("fr.gouv.finances.cp.xemelios.Splash");
                Field fld = clazz.getField("instance");
                splash = fld.get(null);
                if(splash!=null) {
                    Method[] m = clazz.getMethods();
                    for(Method mm:m) {
                        if(mm.getName().equals("setVisible")) {
                            setVisible = mm;
                            break;
                        }
                    }
                    setVisible.invoke(splash, false);
                }
            } catch(Throwable t) {}
            if(na.run()) {
                String login = na.getNewLogin();
                String password = na.getNewPassword();
                DataHelper.Profil profil = new DataHelper.Profil("USER_ADMIN", "Administrateur utilisateurs");
                profil.addFonctionnalite("CONNECT");
                profil.addFonctionnalite("USER_ADMIN");
                DataHelper.createProfil(profil);
                DataHelper.saveProfil(profil);
                DataHelper.StdUser user = new DataHelper.StdUser();
                user.setLogin(login);
                user.setNom(login);
                ArrayList<Pair> profils = new ArrayList<Pair>();
                profils.add(new Pair(profil.getCode(),profil.getLibelle()));
                user.setProfils(profils);
                DataHelper.saveUser(user, Crypter.cryptPassword(password));
                DataHelper.saveUserProfils(user);
            }
            try {
                setVisible.invoke(splash, true);
            } catch(Throwable t) {}
        }
    }

    static public void createProfil(Connection connection, String codeProfil, String libelleProfil, String codesFonctionnalite[]) throws SQLException {
        JdbcUtils.executeUpdate(connection,
                "INSERT INTO AUTH_PROFIL(PRF_CODE, PRF_LIBELLE) VALUES(?, ?)",
                new SimplePStmtBinderBuilder().add(codeProfil).add(libelleProfil).toPStmtBinder());
        if (codesFonctionnalite != null) {
            for (String codeFonctionnalite : codesFonctionnalite) {
                JdbcUtils.executeUpdate(connection,
                        "INSERT INTO AUTH_PRF_FCT(PRF_CODE, FCT_CODE) VALUES(?, ?)",
                        new SimplePStmtBinderBuilder().add(codeProfil).add(codeFonctionnalite).toPStmtBinder());
            }
        }
    }

    static public void createUser(Connection connection, long id, String login, String password, String prenom, String nom, boolean allowedToAllFonctionnalites, boolean allowedToAllDocuments, boolean allowedToAllCollectivites, String codesProfil[]) throws SQLException {
        JdbcUtils.executeUpdate(connection,
                "INSERT INTO AUTH_UTILISATEUR (UTI_ID, UTI_LOGIN, UTI_PASSWORD, UTI_PRENOM, UTI_NOM, UTI_ALL_FONC, UTI_ALL_DOC, UTI_ALL_COL, UTI_ACTIF) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
                new SimplePStmtBinderBuilder().add(id).add(login).add(password).add(prenom).add(nom).add(allowedToAllFonctionnalites).add(allowedToAllDocuments).add(allowedToAllCollectivites).add(1).toPStmtBinder());
        if (codesProfil != null) {
            for (String codeProfil : codesProfil) {
                JdbcUtils.executeUpdate(connection,
                        "INSERT INTO AUTH_UTI_PRF (UTI_ID, PRF_CODE) VALUES (?, ?)",
                        new SimplePStmtBinderBuilder().add(id).add(codeProfil).toPStmtBinder());
            }
        }
    }

    static public boolean userHasAllFonctionnalites(Connection connection, String login) throws SQLException {
        return ((Integer)JdbcUtils.queryObject(connection, "SELECT UTI_ALL_FONC FROM AUTH_UTILISATEUR WHERE UTI_LOGIN = ?", new ColToIntegerRowMapper(), new SimplePStmtBinderBuilder().add(login).toPStmtBinder())).intValue() == 1;
    }

    static public Collection<String> getFonctionnalitesByLogin(Connection connection, String login) throws SQLException {
        return JdbcUtils.queryObjects(connection,
                "SELECT PF.FCT_CODE FROM AUTH_UTILISATEUR U INNER JOIN AUTH_UTI_PRF UP ON U.UTI_ID = UP.UTI_ID INNER JOIN AUTH_PRF_FCT PF ON UP.PRF_CODE = PF.PRF_CODE WHERE U.UTI_LOGIN = ?",
                new RowMapper.ColToStringRowMapper(),
                new SimplePStmtBinderBuilder().add(login).toPStmtBinder());
    }

    static public boolean userHasAllCollectivites(Connection connection, String login) throws SQLException {
        return ((Integer)JdbcUtils.queryObject(connection, "SELECT UTI_ALL_COL FROM AUTH_UTILISATEUR WHERE UTI_LOGIN = ?", new ColToIntegerRowMapper(), new SimplePStmtBinderBuilder().add(login).toPStmtBinder())).intValue() == 1;
    }

    static public Collection<Collectivite> getCollectivitesByLogin(Connection connection, String login) throws SQLException {
        return JdbcUtils.queryObjects(connection,
                "SELECT DISTINCT HB.TID_CODE, HB.COL_CLEF FROM AUTH_HAB_COL HB INNER JOIN AUTH_UTILISATEUR U ON HB.UTI_ID = U.UTI_ID WHERE U.UTI_LOGIN = ?",
                new RowMapper() {

            public Object map(ResultSet rs) throws SQLException {
                return new Collectivite(rs.getString(1),rs.getString(2));
            }
        },
                new SimplePStmtBinderBuilder().add(login).toPStmtBinder());
    }

    static public boolean userHasAllDocuments(Connection connection, String login) throws SQLException {
        return ((Integer)JdbcUtils.queryObject(connection, "SELECT UTI_ALL_DOC FROM AUTH_UTILISATEUR WHERE UTI_LOGIN = ?", new ColToIntegerRowMapper(), new SimplePStmtBinderBuilder().add(login).toPStmtBinder())).intValue() == 1;
    }

    static public Collection<String> getDocumentsByLogin(Connection connection, String login) throws SQLException {
        return JdbcUtils.queryObjects(connection,
                "SELECT DISTINCT HD.DOC_CODE FROM AUTH_HAB_DOC HD INNER JOIN AUTH_UTILISATEUR U ON HD.UTI_ID = U.UTI_ID WHERE U.UTI_LOGIN = ?",
                new RowMapper.ColToStringRowMapper(),
                new SimplePStmtBinderBuilder().add(login).toPStmtBinder());
    }

    static public String getDisplayName(Connection connection, String login) throws SQLException {
        return (String)JdbcUtils.queryObject(connection,
                "SELECT UTI_LOGIN, UTI_PRENOM, UTI_NOM AUTH_UTILISATEUR WHERE U.UTI_LOGIN = ?",
                new RowMapper() {

                    public Object map(ResultSet rs) throws SQLException {
                        if (rs.getString("UTI_PRENOM") != null && rs.getString("UTI_NOM") != null) {
                            return rs.getString("UTI_PRENOM") + " " + rs.getString("UTI_NOM");
                        } else if (rs.getString("UTI_PRENOM") != null) {
                            return rs.getString("UTI_PRENOM");
                        } else if (rs.getString("UTI_NOM") != null) {
                            return rs.getString("UTI_NOM");
                        } else {
                            return rs.getString("UTI_LOGIN");
                        }
                    }

                },
                new SimplePStmtBinderBuilder().add(login).toPStmtBinder());
    }

    static public boolean authenticate(Connection connection, String login, String password) throws SQLException {
        return JdbcUtils.queryObject(connection,
            "SELECT 'OK' FROM AUTH_UTILISATEUR WHERE UTI_LOGIN = ? AND UTI_PASSWORD = ? AND UTI_ACTIF = ?",
            new RowMapper.ColToStringRowMapper(),
            new SimplePStmtBinderBuilder().add(login).add(Crypter.cryptPassword(password)).add(1).toPStmtBinder()) != null;
    }
}
