/*
 * Copyright (C) 2017 OpenDiabetes
 * <p>
 * This program 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 3 of the License, or
 * (at your option) any later version.
 * <p>
 * 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 General Public License for more details.
 * <p>
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */
package de.opendiabetes.vault.data;

import com.j256.ormlite.dao.CloseableIterator;
import com.j256.ormlite.dao.Dao;
import com.j256.ormlite.dao.DaoManager;
import com.j256.ormlite.db.HsqldbDatabaseType;
import com.j256.ormlite.jdbc.JdbcConnectionSource;
import com.j256.ormlite.logger.LocalLog;
import com.j256.ormlite.logger.Log;
import com.j256.ormlite.logger.LoggerFactory;
import com.j256.ormlite.stmt.PreparedQuery;
import com.j256.ormlite.support.ConnectionSource;
import com.j256.ormlite.table.TableUtils;
import de.opendiabetes.vault.container.RawEntry;
import de.opendiabetes.vault.container.VaultEntry;
import de.opendiabetes.vault.container.VaultEntryType;
import de.opendiabetes.vault.plugin.util.TimestampUtils;

import java.io.IOException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * The class implements an interface to the database used.
 * It does so by using the Database Access Objects defined in {@link Dao}.
 *
 * @author Jens Heuschkel
 * @author Lucas Buschlinger
 */
public final class VaultDao {

    /**
     * Error code indicating that an error occurred.
     */
    public static final long RESULT_ERROR = -1;
    /**
     * The link to the database used.
     */
    private static final String DATABASE_URL = "jdbc:hsqldb:mem:odvault";
    //private static final String DATABASE_URL = "jdbc:hsqldb:file:./test.db";
    /**
     * The {@link Logger} used by this Vault Database Access Object.
     */
    private static final Logger LOG = Logger.getLogger(VaultDao.class.getName());
    /**
     * The instance of the Database Access Object used herein.
     */
    private static VaultDao INSTANCE = null;

    /**
     * The {@link ConnectionSource} used herein.
     */
    private ConnectionSource connectionSource;
    /**
     * The Database Access Object ({@link Dao}) for data in the form of {@link VaultEntry} used herein.
     */
    private Dao<VaultEntry, Long> vaultDao;
    /**
     * The Database Access Object ({@link Dao}) for data in the form of {@link RawEntry} used herein.
     */
    private Dao<RawEntry, Long> rawDao;

    /**
     * Default constructor for the Vault Database Access Object.
     * It is declared private as {@link #initializeDb()} has to be used to set up the database access.
     */
    private VaultDao() {
    }

    /**
     * Getter for the {@link #INSTANCE} of the database.
     * If the database has not been initialized by calling {@link #initializeDb()} this will terminate the execution.
     *
     * @return The instance of the Vault Database Access Object, provided it has been initialized by {@link #initializeDb()}.
     */
    public static VaultDao getInstance() {
        if (INSTANCE == null) {
            LOG.severe("Database is not initialized. Call VaultDao.initializeDb first!");
            System.exit(-1);
        }
        return INSTANCE;
    }

    /**
     * Closes the connection to the database and thus finalizes the Vault Database Access Object {@link #INSTANCE}.
     *
     * @throws IOException Thrown if the connection to the database can not be closed.
     */
    public static void finalizeDb() throws IOException {
        INSTANCE.connectionSource.close();
    }

    /**
     * Initializes the connection to the database and sets up the Vault Database Access Object {@link #INSTANCE}.
     *
     * @throws SQLException Thrown if the database can not be successfully initialized.
     */
    public static void initializeDb() throws SQLException {
        //TODO combine logging
        System.setProperty(LoggerFactory.LOG_TYPE_SYSTEM_PROPERTY,
                LoggerFactory.LogType.LOCAL.toString());
        System.setProperty(LocalLog.LOCAL_LOG_LEVEL_PROPERTY,
                Log.Level.INFO.toString());

        INSTANCE = new VaultDao();
        INSTANCE.initDb();
    }

    /**
     * Initializes the actual database as a {@link JdbcConnectionSource}.
     *
     * @throws SQLException Thrown if the database can not be successfully initialized.
     */
    private void initDb() throws SQLException {
        // create a connection source to our database
        connectionSource = new JdbcConnectionSource(DATABASE_URL, "sa", "",
                new HsqldbDatabaseType());
        // instantiate the DAO
        vaultDao = DaoManager.createDao(connectionSource, VaultEntry.class);
        if (!vaultDao.isTableExists()) {
            TableUtils.createTableIfNotExists(connectionSource, VaultEntry.class);
        } else {
            LOG.warning("Found existing DB for VaultEntries. Reusing it!!");
        }

        rawDao = DaoManager.createDao(connectionSource, RawEntry.class);
        if (!rawDao.isTableExists()) {
            TableUtils.createTableIfNotExists(connectionSource, RawEntry.class);
        }
//        TableUtils.createTableIfNotExists(connectionSource, SliceEntry.class);
    }

    /**
     * Puts {@link VaultEntry}s into the database.
     *
     * @param entry The {@link VaultEntry} to be put into the database.
     * @return The ID of respective entry or {@link #RESULT_ERROR}.
     */
    public long putEntry(final VaultEntry entry) {
        try {
            return vaultDao.createIfNotExists(entry).getId();
        } catch (SQLException exception) {
            LOG.log(Level.SEVERE, "Error saving entry:\n" + entry.toString(), exception);
            return RESULT_ERROR;
        }
    }

    /**
     * Puts {@link RawEntry}s into the database.
     *
     * @param entry The {@link RawEntry} to be put into the database.
     * @return The ID of the respective entry or {@link #RESULT_ERROR}.
     */
    public long putRawEntry(final RawEntry entry) {
        // TODO rethink raw entry tracking
        return 0;
//        try {
//            return rawDao.createIfNotExists(entry).getId();
//        } catch (SQLException exception) {
//            LOG.log(Level.SEVERE, "Error saving entry:\n" + entry.toString(), exception);
//            return RESULT_ERROR;
//        }
    }

    /**
     * Searches the database for duplicate entries and removes them accordingly.
     *
     * @return True if no duplicate entries were found or all duplicate entries were successfully removed from the database.
     *          False if a duplicate entry could not be removed.
     */
    public boolean removeDuplicates() {
        // DELETE FROM MyTable WHERE RowId NOT IN (SELECT MIN(RowId) FROM MyTable GROUP BY Col1, Col2, Col3);
        // but we need a workaround for the or mapper
        try {
            PreparedQuery<VaultEntry> query
                    = vaultDao.queryBuilder().orderBy("timestamp", true)
                    .prepare();
            CloseableIterator<VaultEntry> iterator = vaultDao.iterator(query);

            Date startGenerationTimestamp = null;
            List<VaultEntry> tmpList = new ArrayList<>();
            List<Long> duplicateId = new ArrayList<>();
            while (iterator.hasNext()) {
                VaultEntry entry = iterator.next();
                if (startGenerationTimestamp == null) {
                    // start up
                    startGenerationTimestamp = entry.getTimestamp();
                    tmpList.add(entry);
                } else if (!startGenerationTimestamp
                        .equals(entry.getTimestamp())) {
                    // not same timestamp --> new line generation
                    startGenerationTimestamp = entry.getTimestamp();
                    tmpList.clear();
                    tmpList.add(entry);
                } else {
                    // same timestamp --> check if it is a duplicate
                    for (VaultEntry item : tmpList) {
                        if (item.equals(entry)) {
                            // duplicate --> delete and move on
                            duplicateId.add(entry.getId());
                            break;
                        }
                    }
                }
            }

            // delete duplicates
            int lines = vaultDao.deleteIds(duplicateId);
            LOG.log(Level.INFO, "Removed {0} duplicates", lines);
        } catch (SQLException exception) {
            LOG.log(Level.SEVERE, "Error while db query", exception);
            return false;
        }

        return true;
    }

    /**
     * This method is used to query {@link VaultEntry}s which are of a given type and lie in a specified period.
     * The types to be queried for are glucose types:
     * <ul>
     *     <li>{@link VaultEntryType#GLUCOSE_BG}</li>
     *     <li>{@link VaultEntryType#GLUCOSE_CGM}</li>
     *     <li>{@link VaultEntryType#GLUCOSE_CGM_ALERT}</li>
     * </ul>
     *
     * @param from The start of the period to query entries from.
     * @param to The end of the period to query entries from.
     * @return All {@link VaultEntry} which are of the required type and lie in the specified period.
     */
    //TODO OTHER TYPES? Let's ask Jens @next meeting
    public List<VaultEntry> queryGlucoseBetween(final Date from, final Date to) {
        List<VaultEntry> returnValues = null;
        try {
            PreparedQuery<VaultEntry> query
                    = vaultDao.queryBuilder().orderBy("timestamp", true)
                    .where()
                    .eq(VaultEntry.TYPE_FIELD_NAME, VaultEntryType.GLUCOSE_BG)
                    .or()
                    .eq(VaultEntry.TYPE_FIELD_NAME, VaultEntryType.GLUCOSE_CGM)
                    .or()
                    .eq(VaultEntry.TYPE_FIELD_NAME, VaultEntryType.GLUCOSE_CGM_ALERT)
                    .and()
                    .between(VaultEntry.TIMESTAMP_FIELD_NAME, from, to)
                    .prepare();
            returnValues = vaultDao.query(query);
        } catch (SQLException exception) {
            LOG.log(Level.SEVERE, "Error while db query", exception);
        }
        return returnValues;
    }

    /**
     * This method is used to query {@link VaultEntry}s which are of a given type and lie in a specified period.
     * The types to be queried for are exercise types:
     * <ul>
     *     <li>{@link VaultEntryType#EXERCISE_BICYCLE}</li>
     *     <li>{@link VaultEntryType#EXERCISE_RUN}</li>
     *     <li>{@link VaultEntryType#EXERCISE_WALK}</li>
     *     <li>{@link VaultEntryType#EXERCISE_MANUAL}</li>
     * </ul>
     *
     * @param from The start of the period to query entries from.
     * @param to The end of the period to query entries from.
     * @return All {@link VaultEntry} which are of the required type and lie in the specified period.
     */
    //TODO OTHER TYPES? Let's ask Jens @next meeting
    public List<VaultEntry> queryExerciseBetween(final Date from, final Date to) {
        List<VaultEntry> returnValues = null;
        try {
            PreparedQuery<VaultEntry> query
                    = vaultDao.queryBuilder().orderBy("timestamp", true)
                    .where()
                    .eq(VaultEntry.TYPE_FIELD_NAME, VaultEntryType.EXERCISE_BICYCLE)
                    .or()
                    .eq(VaultEntry.TYPE_FIELD_NAME, VaultEntryType.EXERCISE_RUN)
                    .or()
                    .eq(VaultEntry.TYPE_FIELD_NAME, VaultEntryType.EXERCISE_WALK)
                    .or()
                    .eq(VaultEntry.TYPE_FIELD_NAME, VaultEntryType.EXERCISE_MANUAL)
                    .and()
                    .between(VaultEntry.TIMESTAMP_FIELD_NAME, from, to)
                    .prepare();
            returnValues = vaultDao.query(query);
        } catch (SQLException exception) {
            LOG.log(Level.SEVERE, "Error while db query", exception);
        }
        return returnValues;
    }

    /**
     * This is used to query a single Event ({@link VaultEntry} before a given point in time.
     * This is useful to determine why certain events might have happened (correlation of events).
     *
     * @param timestamp The point in time to get the preceding event from.
     * @param type The Type of {@link VaultEntry} to query for.
     * @return The event of the {@link VaultEntryType} preceding the specified point in time.
     */
    public VaultEntry queryLatestEventBefore(final Date timestamp, final VaultEntryType type) {
        VaultEntry returnValue = null;
        try {

            PreparedQuery<VaultEntry> query
                    = vaultDao.queryBuilder().orderBy("timestamp", false)
                    .limit(1L)
                    .where()
                    .eq(VaultEntry.TYPE_FIELD_NAME, type)
                    .and()
                    .le(VaultEntry.TIMESTAMP_FIELD_NAME, timestamp)
                    .prepare();
            List<VaultEntry> tmpList = vaultDao.query(query);
            if (tmpList.size() > 0) {
                returnValue = tmpList.get(0);
            }
        } catch (SQLException exception) {
            LOG.log(Level.SEVERE, "Error while db query", exception);
        }
        return returnValue;
    }

    /**
     * This is used to query all {@link VaultEntry}s currently store in the database.
     *
     * @return The full list of all {@link VaultEntry}s in the database.
     */
    public List<VaultEntry> queryAllVaultEntries() {
        List<VaultEntry> returnValues = new ArrayList<>();
        try {

            PreparedQuery<VaultEntry> query
                    = vaultDao.queryBuilder().orderBy("timestamp", true)
                    .prepare();
            returnValues = vaultDao.query(query);
        } catch (SQLException exception) {
            LOG.log(Level.SEVERE, "Error while db query", exception);
        }
        return returnValues;
    }

    /**
     * This is used to retrieve an entry from the database by its identifier.
     *
     * @param id The ID of the {@link VaultEntry} to be retrieved.
     * @return The {@link VaultEntry} with respective ID or null.
     */
    public VaultEntry queryVaultEntryById(final long id) {
        List<VaultEntry> returnValues;
        try {
            PreparedQuery<VaultEntry> query
                    = vaultDao.queryBuilder().orderBy("timestamp", true)
                    .limit(1L)
                    .where()
                    .eq(VaultEntry.ID_FIELD_NAME, id)
                    .prepare();
            returnValues = vaultDao.query(query);
        } catch (SQLException exception) {
            LOG.log(Level.SEVERE, "Error while db query", exception);
            return null;
        }
        return returnValues.get(0);
    }

    /**
     * This is used to retrieve all {@link VaultEntry}s from the database which lie in the specified period of time, no matter their type.
     *
     * @param from The start of the time period to query entries from.
     * @param to The end of the time period to query entries from.
     * @return List of all {@link VaultEntry}s which lie in the specified time period.
     */
    public List<VaultEntry> queryVaultEntriesBetween(final Date from, final Date to) {
        List<VaultEntry> returnValues = new ArrayList<>();
        try {
            Date fromTimestamp = TimestampUtils.createCleanTimestamp(from);
            Date toTimestamp = TimestampUtils.createCleanTimestamp(to);

            PreparedQuery<VaultEntry> query
                    = vaultDao.queryBuilder().orderBy("timestamp", true)
                    .where()
                    .between(VaultEntry.TIMESTAMP_FIELD_NAME, fromTimestamp, toTimestamp)
                    .prepare();
            returnValues = vaultDao.query(query);
        } catch (SQLException exception) {
            LOG.log(Level.SEVERE, "Error while db query", exception);
        }
        return returnValues;
    }
    /**
     * This method is used to query {@link VaultEntry}s which are of a given type and lie in a specified period.
     * The types to be queried for are basal types:
     * <ul>
     *     <li>{@link VaultEntryType#BASAL_MANUAL}</li>
     *     <li>{@link VaultEntryType#BASAL_PROFILE}</li>
     *     <li>{@link VaultEntryType#BASAL_INTERPRETER}</li>
     * </ul>
     *
     * @param from The start of the period to query entries from.
     * @param to The end of the period to query entries from.
     * @return All {@link VaultEntry} which are of the required type and lie in the specified period.
     */
    public List<VaultEntry> queryBasalBetween(final Date from, final Date to) {
        List<VaultEntry> returnValues = null;
        try {
            PreparedQuery<VaultEntry> query
                    = vaultDao.queryBuilder().orderBy("timestamp", true)
                    .where()
                    .eq(VaultEntry.TYPE_FIELD_NAME, VaultEntryType.BASAL_MANUAL)
                    .or()
                    .eq(VaultEntry.TYPE_FIELD_NAME, VaultEntryType.BASAL_PROFILE)
                    .or()
                    .eq(VaultEntry.TYPE_FIELD_NAME, VaultEntryType.BASAL_INTERPRETER)
                    .and()
                    .between(VaultEntry.TIMESTAMP_FIELD_NAME, from, to)
                    .prepare();
            returnValues = vaultDao.query(query);
        } catch (SQLException exception) {
            LOG.log(Level.SEVERE, "Error while db query", exception);
        }
        return returnValues;
    }

    /**
     * This is used to remove a specific entry from the database.
     *
     * @param historyEntry The {@link VaultEntry} to be removed from the database.
     * @return True if the entry was removed, false if not.
     */
    public boolean removeEntry(final VaultEntry historyEntry) {
        try {
            vaultDao.deleteById(historyEntry.getId());
            LOG.log(Level.INFO, "Removed dntry: {0}", historyEntry.toString());
        } catch (SQLException exception) {
            LOG.log(Level.SEVERE, "Error while db query", exception);
            return false;
        }

        return true;
    }
}