package at.asitplus.eidas.specific.modules.authmodule_eIDASv2.service;

import java.io.File;
import java.io.IOException;
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.time.Instant;
import java.util.Properties;

import javax.annotation.PostConstruct;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.sqlite.SQLiteConfig;
import org.sqlite.SQLiteConfig.LockingMode;
import org.sqlite.SQLiteConfig.SynchronousMode;
import org.sqlite.SQLiteErrorCode;

import at.asitplus.eidas.specific.modules.authmodule_eIDASv2.Constants;
import at.asitplus.eidas.specific.modules.authmodule_eIDASv2.DAO.eIDASPersonalIdStoreDAO;
import at.asitplus.eidas.specific.modules.authmodule_eIDASv2.exception.SQLiteServiceException;
import at.gv.egiz.eaaf.core.api.idp.IConfiguration;
import at.gv.egiz.eaaf.core.impl.data.Pair;
import at.gv.egiz.eaaf.core.impl.data.Trible;

@Component
@Deprecated
public class eIDASDataStore {

	private static final String SQLITE_JDBC_DRIVER_CLASS = "org.sqlite.JDBC";
	private static final String SQLITE_CONNECTION_PARAM = "jdbc:sqlite:%s";
	private static final boolean sleep = true;
	private static final int howLongToSleepOnBusyLock_ = 100;
	
	private static final Logger log = LoggerFactory.getLogger(eIDASDataStore.class);
	
	@Autowired private IConfiguration basicConfig;
		
	private String connectionURL;
	private Connection conn = null;
	
	@PostConstruct
	private void initialize() throws SQLiteServiceException {
		try {
			String sqlLiteDBUrl = basicConfig.getBasicConfiguration(
					Constants.CONIG_PROPS_EIDAS_SZRCLIENT_WORKAROUND_SQLLITEDATASTORE_URL, 
					basicConfig.getConfigurationRootDirectory().toString() + "/sqlite/database.db"
					
					);
			
			log.info("Use SQLite database with URL: " + sqlLiteDBUrl);
			
			//check if SQLite lib is in Classpath
			Class.forName(SQLITE_JDBC_DRIVER_CLASS);
			
			//open DB connection
			boolean isNewFileCreated = false;
			
			//open file or create file if not already exists
			File dbFile = new File(sqlLiteDBUrl);
			if (!dbFile.exists()) {
				log.info("SQLite database does not exist. Creating new database file ... ");
				dbFile.createNewFile();
				isNewFileCreated = true;
				
			}
			
			//open database connection
			connectionURL = String.format(SQLITE_CONNECTION_PARAM, dbFile.getPath());			
					
			//create DB scheme if new DB file was created
			if (isNewFileCreated) {
				executeUpdate(startConnection().createStatement(), eIDASPersonalIdStoreDAO.CREATE);
				log.debug("SQLite db scheme created");
			
			}
						
		} catch (ClassNotFoundException e) {
			log.warn("Can NOT initialize SQLite database for temporarly identity mapping. ", e);
			throw new SQLiteServiceException("internal.05", new Object[] {e.getMessage()}, e);
			
		} catch (SQLException | IOException e) {
			log.warn("Can NOT initialize SQLite database for temporarly identity mapping. ", e);
			throw new SQLiteServiceException("internal.05", new Object[] {e.getMessage()}, e);
		
		}
		
	}
			

	
	
	/**
	 * Store a mapping entry with eIDAS personal identifier (source country / destination country / personal identifier)
	 * and the identifier that is used for ERnB communication
	 * 
	 * @param transactionId Id of this authentication transaction
	 * @param eIDASId eIDAS personal identifier without country prefixes
	 * @param ernbId personal identifier that is used to request the ERnB
	 * @throws SQLiteServiceException 
	 */
	public void storeNationalId(String transactionId, Trible<String, String, String> eIDASId, String ernbId) throws SQLiteServiceException {
		try {
			PreparedStatement preStatment = startConnection().prepareStatement(
					eIDASPersonalIdStoreDAO.INSERT,
					Statement.RETURN_GENERATED_KEYS);
			
			for (int i=1; i<=eIDASPersonalIdStoreDAO.TABLE_COLS.size(); i++)  {
				Pair<String, eIDASPersonalIdStoreDAO.T> col = eIDASPersonalIdStoreDAO.TABLE_COLS.get(i-1);
				if (col.getFirst().equals(eIDASPersonalIdStoreDAO.COLS.timestamp.name()))
					preStatment.setDate(i, new java.sql.Date(Instant.now().toEpochMilli()));
				
				else if (col.getFirst().equals(eIDASPersonalIdStoreDAO.COLS.transactionId.name()))
					preStatment.setString(i, transactionId);
				
				else if (col.getFirst().equals(eIDASPersonalIdStoreDAO.COLS.eidasId.name()))
					preStatment.setString(i, eIDASId.getThird());
				
				else if (col.getFirst().equals(eIDASPersonalIdStoreDAO.COLS.eidasSourceCountry.name()))
					preStatment.setString(i, eIDASId.getFirst());
				
				else if (col.getFirst().equals(eIDASPersonalIdStoreDAO.COLS.eidasDestinationCountry.name()))
					preStatment.setString(i, eIDASId.getSecond());
				
				else if (col.getFirst().equals(eIDASPersonalIdStoreDAO.COLS.ernbId.name()))
					preStatment.setString(i, ernbId);
				
				else
					log.warn("SQLite table:" + eIDASPersonalIdStoreDAO.NAME + " contains no col with name:" + col.getFirst());
				
			}
			
			//execute SQL query
			int sqlResult = preStatment.executeUpdate();
			
			if (sqlResult != 1) {
				log.warn("SQLite query execution FAILED!");
				throw new SQLiteServiceException("internal.06", new Object[] {"Queryresult is '-1'"});
				
			}
			
		} catch (SQLiteServiceException | SQLException e) {
			log.warn("SQLite query execution FAILED!", e);
			throw new SQLiteServiceException("internal.05", new Object[] {e.getMessage()}, e);
			
		}
				
	}

	public String getErnbNationalId(Trible<String, String, String> eIDASId) throws SQLiteServiceException {
		try {
			PreparedStatement preStatment = startConnection().prepareStatement(
					eIDASPersonalIdStoreDAO.SELECT_BY_EIDAS_RAW_ID,
					Statement.RETURN_GENERATED_KEYS);
			
			preStatment.setString(1, eIDASId.getThird());
			preStatment.setString(2, eIDASId.getFirst());
			
			ResultSet rs = preStatment.executeQuery();
			
			if(!rs.next())				
				return null;
				
			else
				return rs.getString(eIDASPersonalIdStoreDAO.COLS.ernbId.name());
							
		} catch (SQLiteServiceException | SQLException e) {
			log.warn("SQLite query execution FAILED!", e);
			throw new SQLiteServiceException("internal.05", new Object[] {e.getMessage()}, e);
			
		}
		
		
	}
	
	public String getEidasRawNationalId(String ernbId) throws SQLiteServiceException { 
		try {
			PreparedStatement preStatment = startConnection().prepareStatement(
					eIDASPersonalIdStoreDAO.SELECT_BY_ERNB_ID,
					Statement.RETURN_GENERATED_KEYS);
			
			preStatment.setString(1, ernbId);
			
			ResultSet rs = preStatment.executeQuery();
			
			if(!rs.next())				
				return null;
				
			else
				return rs.getString(eIDASPersonalIdStoreDAO.COLS.eidasId.name());
			
		} catch (SQLiteServiceException | SQLException e) {
			log.warn("SQLite query execution FAILED!", e);
			throw new SQLiteServiceException("internal.05", new Object[] {e.getMessage()}, e);
			
		}
		
	}
	
	private Connection startConnection() throws SQLiteServiceException {		
		int i = howLongToSleepOnBusyLock_;
			
		while (true) {
			try {
				if (conn == null) {
					log.info("Initializing SQLite database with URL: " + connectionURL + " ... ");				
					conn = DriverManager.getConnection(connectionURL, getConnectionProperties());
				
				} else {
					if (!conn.isValid(10)) {
						log.info("SQLite connection is not valid any more --> restarting connection ...");
						conn.close();
						conn = DriverManager.getConnection(connectionURL, getConnectionProperties());
					}					
				}
				
				log.info("SQLite database connected");
				return conn;
				
			} catch (SQLException e) {
				String msg = e.getLocalizedMessage();
				if (isBusyLocked( e)) {
					log.warn(msg, e);
					try {					
						if (sleep)
							Thread.sleep(i++);
					
					} catch (InterruptedException e1) {
						throw new SQLiteServiceException("internal.05", new Object[] {e1.getMessage()}, e1);
						
					}
					continue;
					
				}
				throw new SQLiteServiceException("internal.05", new Object[] {e.getMessage()}, e);
				
			}
		}
	}
	
	
	/*
	 *     SQLite query code
	 */
	
	protected Properties getConnectionProperties() {
		SQLiteConfig config = new SQLiteConfig();
		config.enforceForeignKeys(true);
		config.setCacheSize(8000);
		config.setLockingMode(LockingMode.NORMAL);
		config.setSharedCache(false);
		config.setReadUncommited(true);
		config.setSynchronous(SynchronousMode.NORMAL);
		return config.toProperties();
		
	}
	
	private int executeUpdate(Statement statement, String sql) throws SQLiteServiceException {
		int i = 10;

		int rc = -1;
		while (true) {
			try {
				rc = statement.executeUpdate(sql);
				break;
				
			} catch (SQLException e) {
				try {
					if (executeUpdateError(e, i))
						continue;
					else
						throw new SQLiteServiceException("internal.06",
								new Object[] {e.getMessage()}, e);
					
				} catch (SQLiteServiceException e1) {
					log.warn("\n" + sql + "\n" + e1.getMessage());
					throw e1;
					
				}									
			}
		}
		
		return rc;
		
	}
	
	private boolean isBusyLocked(SQLException e) {
		int eC = e.getErrorCode();
		
		if (eC == SQLiteErrorCode.SQLITE_LOCKED.code
		    || eC == SQLiteErrorCode.SQLITE_BUSY.code) {
			log.trace("SQLite db is busy looked");			
			return true;
			
		}
		
		String msg = e.getMessage();
		if ( msg.contains("[SQLITE_LOCKED]") || msg.contains("[SQLITE_BUSY]")) {
			log.trace("SQLite db is busy looked");
			return true;
		}
		
		return false;
	}

	private boolean executeUpdateError(SQLException e, int theadSleepCounter) throws SQLiteServiceException {
		if (isBusyLocked(e)) {			
			try {
				if (sleep) Thread.sleep(theadSleepCounter++);
				
			} catch (InterruptedException e1) {
				throw new SQLiteServiceException("internal.05", new Object[] {e1.getMessage()}, e1);
				
			}
			
			return true;
		}
				
		return false;
				
	}
}