/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package DataLayer;

import org.jetbrains.annotations.NotNull;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * @author install1
 */
public class DataMapper {

    public static ArrayList<String> getAllStrings() {
        Connection l_cCon = null;
        PreparedStatement l_pStatement = null;
        ResultSet l_rsSearch = null;
        ArrayList<String> arrayListStr = new ArrayList();
        Properties prop = new Properties();
        String fileName = "app.config";
        try (FileInputStream fis = new FileInputStream(fileName)) {
            prop.load(fis);
        } catch (FileNotFoundException ex) {
        } catch (IOException ex) {
        }
        try {
            l_cCon = DBCPDataSource.getConnection();
            //not better, just forces the responds to variate a lot more
            String l_sSQL = "SELECT * FROM `Sentences` where last_used < CURRENT_DATE() - INTERVAL "
                    + String.valueOf(prop.getProperty("app.interval_days"))
                    + " DAY order by LENGTH(Strings) desc";
            //System.out.println("l_sSQL: " + l_sSQL);
            l_pStatement = l_cCon.prepareStatement(l_sSQL);
            l_rsSearch = l_pStatement.executeQuery();
            while (l_rsSearch.next()) {
                arrayListStr.add(l_rsSearch.getString(1));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            CloseConnections(l_pStatement, l_rsSearch, l_cCon);
        }
        return arrayListStr;
    }

    public static void InsertMYSQLStrings(String str) {
        Connection l_cCon = null;
        PreparedStatement l_pStatement = null;
        ResultSet l_rsSearch = null;
        Properties prop = new Properties();
        String fileName = "app.config";
        try (FileInputStream fis = new FileInputStream(fileName)) {
            prop.load(fis);
        } catch (FileNotFoundException ex) {
        } catch (IOException ex) {
        }
        String l_sSQL = "INSERT IGNORE `Sentences` (`Strings`, `last_used`) VALUES (?, DATE(NOW()) - interval " +
                String.valueOf(prop.getProperty("app.interval_days_minus")) + " DAY)";
        try {
            l_cCon = DBCPDataSource.getConnection();
            l_pStatement = l_cCon.prepareStatement(l_sSQL);
            l_pStatement.setString(1, str);

            l_pStatement.execute();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            CloseConnections(l_pStatement, l_rsSearch, l_cCon);
        }
    }


    public static void CloseConnections(PreparedStatement ps, ResultSet rs, Connection con) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException ex) {
                Logger.getLogger(DataMapper.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException ex) {
                Logger.getLogger(DataMapper.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        if (con != null) {
            try {
                con.close();
            } catch (SQLException ex) {
                Logger.getLogger(DataMapper.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

    public static void checkStringsToDelete() {
        Properties prop = new Properties();
        String fileName = "app.config";
        try (FileInputStream fis = new FileInputStream(fileName)) {
            prop.load(fis);
        } catch (FileNotFoundException ex) {
        } catch (IOException ex) {
        }
        PreparedStatement l_pStatement = null;
        ResultSet l_rsSearch = null;
        String CountSQL = "select count(*) from Sentences";
        String l_sSQL = "delete from Sentences order by last_used asc limit 5;";
        try (Connection l_cCon = DBCPDataSource.getConnection()) {
            l_pStatement = l_cCon.prepareStatement(CountSQL);
            ResultSet resultSet = l_pStatement.executeQuery();
            if (resultSet.next()) {
                int count = resultSet.getInt(1);
                if (count > Integer.valueOf(prop.getProperty("app.string_count"))) {
                    //System.out.println("cleaning strings: " + l_sSQL);
                    l_pStatement = l_cCon.prepareStatement(l_sSQL);
                    l_pStatement.executeUpdate();
                }
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            CloseConnections(l_pStatement, l_rsSearch, null);
        }
    }

    public static void updateLastUsed(@NotNull ArrayList<String> mysqlUpdateLastUsed) {
        Connection l_cCon = null;
        PreparedStatement l_pStatement = null;
        ResultSet l_rsSearch = null;
        String l_sSQL = "update Sentences Set last_used = now() where Strings = (?)";
        try {
            l_cCon = DBCPDataSource.getConnection();
            l_pStatement = l_cCon.prepareStatement(l_sSQL);
            for (String str1 : mysqlUpdateLastUsed) {
                l_pStatement.setString(1, str1);
                l_pStatement.execute();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            CloseConnections(l_pStatement, l_rsSearch, l_cCon);
        }
    }
}
