package appServerUtil;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.concurrent.atomic.AtomicBoolean;
import java.util.logging.Level;
import java.util.logging.Logger;

/*
 * 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.
 */
/**
 *
 * @author stefano
 */
public class DBConnector {

    private final static String CONNECTION_URL = "jdbc:mysql://93.42.227.195:3306/locale?serverTimezone=UTC&characterEncoding=UTF-8";
    private final static String USER = "";
    private final static String PASSWORD = "";
    private Connection connection;
    private Statement stmt;
    private String exception = "";

    public DBConnector() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection = DriverManager.getConnection(CONNECTION_URL, USER, PASSWORD);
            stmt = connection.createStatement();

        } catch (ClassNotFoundException | SQLException ex) {
            exception = ex.getMessage();
            System.err.println(ex.getMessage());
            //Logger.getLogger(DBConnector.class.getName()).log(Level.SEVERE, null, ex);
        }// end try-catch
    }

    public String checkDatabase() {
        return "CONNECTION=" + (connection == null ? "null" : connection) + "\nSTATEMENT=" + (stmt == null ? "null" : stmt) + "\n" + exception;
    }

    public ResultSet executeQuery(final String query) {
        if (stmt == null) {
            return null;
        }
        try {
            return stmt.executeQuery(query);
        } catch (SQLException ex) {
            System.err.println(ex.getMessage());
            Logger.getLogger(DBConnector.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }//end try-catch
    }

    public boolean execute(final String query) {
        try {
            return stmt.execute(query);
        } catch (SQLException ex) {
            System.err.println(ex.getMessage());
            Logger.getLogger(DBConnector.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    public void writeBlob(String table, int rowId, InputStream input) {
        // update sql
        String updateSQL = "UPDATE " + table + " "
                + "SET file = ? "
                + "WHERE id=?";

        try (PreparedStatement pstmt = connection.prepareStatement(updateSQL)) {
            pstmt.setBinaryStream(1, input);
            pstmt.setInt(2, rowId);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }

    public InputStream getBlobInputStream(ResultSet resultSet, int index){
        try {
            InputStream binaryStream = resultSet.getBinaryStream(index);
            return binaryStream;
        } catch (SQLException ex) {
             System.out.println(ex.getMessage());
        }
        return null;
    }

    public long getBlobSize(ResultSet resultSet, String name){
        try {
            Blob blob = resultSet.getBlob(name);
            return blob.length();
        } catch (SQLException ex) {
             System.out.println(ex.getMessage());
        }
        return 0;
    }

    public void close() {
        try {
            stmt.close();
            connection.close();
            //stmt = null;
            //connection = null;
        } catch (SQLException ex) {
            Logger.getLogger(DBConnector.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    @Override
    public void finalize() {
        try {
            super.finalize();
            close();
        } catch (Throwable ex) {
        }//end try-catch
    }

    public PreparedStatement getPreparedStatement(final String sqlQuery){
        try {
            PreparedStatement pstmt = connection.prepareStatement(sqlQuery);
            return pstmt;
        } catch (SQLException ex) {
            Logger.getLogger(DBConnector.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }



}