package org.eclipse.osbp.authentication.account.datamarts;

import com.vaadin.ui.Notification;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.ResourceBundle;
import org.eclipse.osbp.bpm.api.BPMTaskSummary;
import org.eclipse.osbp.core.api.persistence.IPersistenceService;
import org.eclipse.osbp.dsl.common.datatypes.IDto;
import org.eclipse.osbp.preferences.ProductConfiguration;
import org.eclipse.osbp.runtime.common.event.IDualData;
import org.eclipse.osbp.runtime.common.historized.UUIDHist;
import org.eclipse.osbp.runtime.common.i18n.ITranslator;
import org.eclipse.osbp.ui.api.datamart.DatamartData;
import org.eclipse.osbp.ui.api.datamart.DatamartFilter;
import org.eclipse.osbp.ui.api.datamart.DatamartPrimary;
import org.eclipse.osbp.ui.api.datamart.IDataMart;
import org.eclipse.osbp.ui.api.date.SimpleDateFormatter;
import org.eclipse.osbp.ui.api.user.IUser;
import org.eclipse.osbp.user.User;
import org.eclipse.osbp.xtext.datamart.common.AEntityDatamart;
import org.eclipse.osbp.xtext.datamart.common.DatamartDtoMapper;
import org.eclipse.osbp.xtext.datamart.common.olap.DerivedAxis;
import org.eclipse.osbp.xtext.datamart.common.olap.DerivedCellSet;
import org.eclipse.osbp.xtext.datamart.common.olap.DerivedMember;
import org.eclipse.osbp.xtext.datamart.common.olap.DerivedPosition;
import org.eclipse.osbp.xtext.datamart.common.sql.SqlCellSet;
import org.junit.Test;
import org.olap4j.Axis;
import org.osgi.service.component.annotations.ReferenceCardinality;
import org.osgi.service.component.annotations.ReferencePolicy;
import org.slf4j.Logger;

@SuppressWarnings("serial")
public class UserAccountDatamart extends AEntityDatamart {
  private static Logger log = org.slf4j.LoggerFactory.getLogger("datamarts");
  
  private IUser user;
  
  private Map<String, Map<String, String>> dateFilterAttributeProperties = new LinkedHashMap<String, Map<String, String>>();
  
  private String statement = "select UserAccount.USER_NAME as \"userName\",UserAccount.EMAIL as \"email\",UserGroup_userGroup.USER_GROUP_NAME as \"userGroupName\",UserAccount.POSITION as \"position\",UserAccount.PROFILEIMAGE as \"profileimage\",UserAccount.NOT_REGISTERED as \"notRegistered\",UserGroup_userGroup.id as \"__UserGroup__ID__\",UserAccount.id as \"__UserAccount__ID__\" from USER_ACCOUNT UserAccount left join USER_GROUP UserGroup_userGroup on(UserGroup_userGroup.id=UserAccount.USER_GROUP_ID) order by UserGroup_userGroup.USER_GROUP_NAME ASC,UserAccount.USER_NAME ASC";
  
  private DatamartDtoMapper datamartDtoMapper = new DatamartDtoMapper()
    .add("org.eclipse.osbp.authentication.account.dtos.UserGroupDto", "id", EType.NONE, "__UserGroup__ID__")
    .add("org.eclipse.osbp.authentication.account.dtos.UserAccountDto", "id", EType.NONE, "__UserAccount__ID__")
    ;
  
  private Connection connection = null;
  
  private Map<Integer, ArrayList<String>> axisMap = new LinkedHashMap<Integer,ArrayList<String>>() {{
    		put(0,new ArrayList<String>() {{
    		add("userName");
    		add("email");
    		add("userGroupName");
    		add("position");
    		add("profileimage");
    		add("notRegistered");
    		add("__UserGroup__ID__");
    		add("__UserAccount__ID__");
    		}});
    }};
  
  private Map<String, String> aliasMap = new LinkedHashMap<String,String>() {{
    	put("userName","org.eclipse.osbp.authentication.account.entities.UserAccount.userName");
    	put("email","org.eclipse.osbp.authentication.account.entities.UserAccount.email");
    	put("position","org.eclipse.osbp.authentication.account.entities.UserAccount.position");
    	put("profileimage","org.eclipse.osbp.authentication.account.entities.UserAccount.profileimage");
    	put("notRegistered","org.eclipse.osbp.authentication.account.entities.UserAccount.notRegistered");
    	put("userGroupName","org.eclipse.osbp.authentication.account.entities.UserGroup.userGroupName");
    }};
  
  private Map<String, IDataMart.AttributeVisibility> hiddenMap = new LinkedHashMap<String,AttributeVisibility>() {{
    }};
  
  private Map<String, IDataMart.EType> idMap = new LinkedHashMap<String,EType>() {{
    			put("__UserGroup__ID__", EType.STRING);
    			put("__UserAccount__ID__", EType.STRING);
    }};
  
  private Map<String, DatamartPrimary> primaryList = new LinkedHashMap<String, DatamartPrimary>() {{
    			put("__UserGroup__ID__", new DatamartPrimary("__UserGroup__ID__", "id", "org.eclipse.osbp.authentication.account.entities.UserGroup", false));
    			put("__UserAccount__ID__", new DatamartPrimary("__UserAccount__ID__", "id", "org.eclipse.osbp.authentication.account.entities.UserAccount", false));
    }};
  
  private Map<String, IDataMart.EType> typesMap = new LinkedHashMap<String,EType>() {{
    	put("UserGroup.userGroupName", EType.STRING);
    	put("UserAccount.userName", EType.STRING);
    	put("UserAccount.email", EType.STRING);
    	put("UserAccount.position", EType.STRING);
    	put("UserAccount.profileimage", EType.BLOPMAPPING);
    	put("UserAccount.notRegistered", EType.BOOLEAN);
    }};
  
  private Map<String, String> typesProp = new LinkedHashMap<String, String>() {{
    	put("position", "");
    	put("profileimage", "2");
    }};
  
  private Map<String, String> resultAttributes = new LinkedHashMap<String,String>();
  
  private boolean moreToLoad = false;
  
  public Map<String, IDataMart.EType> getIdMap() {
    return this.idMap;
  }
  
  public Map<String, DatamartPrimary> getPrimaryList() {
    return this.primaryList;
  }
  
  public Map<Integer, ArrayList<String>> getAxisMap() {
    return this.axisMap;
  }
  
  public Map<String, String> getAliasMap() {
    return this.aliasMap;
  }
  
  public Map<String, IDataMart.AttributeVisibility> getHiddenMap() {
    return this.hiddenMap;
  }
  
  public Map<String, String> getTypesProp() {
    return this.typesProp;
  }
  
  public IUser getUser() {
    return this.user;
  }
  
  public void setUser(final IUser user) {
    this.user = user;
  }
  
  public String getName() {
    return "UserAccount";
    
  }
  
  public List<Object> getPrimaryListKeys(final String columnId) {
    return primaryList.get(columnId).getKeys();
    
  }
  
  public boolean contains(final Object key) {
    for(String primaryKeyName:idMap.keySet()) {
    	if(primaryList.get(primaryKeyName).getKeys().contains(key)) {
    		return true;
    	}
    }
    return false;
    
  }
  
  protected ArrayList<DatamartFilter> initializeFilterMap() {
    return new ArrayList<DatamartFilter>() {{
    add(new DatamartFilter(DatamartFilter.MultipleType.SINGLE, DatamartFilter.FilterType.BY_ID, "UserGroup_userGroup.id", "UserGroup.id","", false));
    add(new DatamartFilter(DatamartFilter.MultipleType.SINGLE, DatamartFilter.FilterType.BY_ID, "UserAccount.id", "UserAccount.id","", false));
    }};
    
  }
  
  public String getPrimaryFilterId() {
    return "UserAccount.id";
    
  }
  
  @Override
  public void clearCache() {
    
  }
  
  @Override
  public String getResultAttribute(final String attributeName) {
    return resultAttributes.get(attributeName);
  }
  
  public Map<String, IDataMart.EType> getTypesMap() {
    return typesMap;
    
  }
  
  public String getPersistenceUnit() {
    return "authentication";
    
  }
  
  public Connection connect() {
    if (connection == null) {   			
    	try {
    		connection = org.eclipse.osbp.authentication.account.datamarts.DatamartsServiceBinder.getPersistenceService().getPersistenceUnitConnection(getPersistenceUnit());
    	} catch (SQLException e) {
    		log.error("{}", e);
    		return (connection);
    	}
    }
    return (connection);
  }
  
  public void disconnect() {
    if (connection != null) {   			
    	try {
    		connection.close();
    		connection = null;
    	} catch (SQLException e) {
    		log.error("{}", e);
    	}
    }
  }
  
  public boolean isMoreToLoad() {
    return moreToLoad;
  }
  
  public String insertCondition(final String filteredStatement, final String condition) {
    int index = filteredStatement.indexOf("order by");
    if(index > -1) {
    	return filteredStatement.substring(0, index-1) + condition + filteredStatement.substring(index-1); 
    } else {
    	return filteredStatement + condition;
    }
  }
  
  public String addUserFilter(final String sql, final String entity) {
    return addUserFilter( sql, entity, entity );
    
  }
  
  public String addUserFilter(final String sql, final String entity, final String entityAlias) {
    String condition = "";
    String retSql=sql;
    if ( user.getUserFilter() == null )
    	return retSql;
    if ( user.getUserFilter().getInFilterMap() != null ) {
    	String idList="";
    	for ( java.util.Map.Entry<String, List<String>> uf :user.getUserFilter().getInFilterMap().entrySet() ) {
    		if (uf.getKey().contains(entity)) {
    			for ( String entry:uf.getValue() ) {
    				if ( !idList.equals("") ) {
    					idList = idList + ",";
    				}
    				idList = idList + "\'" + entry + "\'" ;
    			}
    			idList="(" + idList + ")";
    		}
    	}
    	if ( !idList.equals("") ) {
    		condition = " ("+entityAlias+".id in "+idList+") ";
    	}
    } else if ( user.getUserFilter().getNotFilterMap() != null ) {
    	String idList="";
    	for ( java.util.Map.Entry<String, List<String>> uf :user.getUserFilter().getNotFilterMap().entrySet() ) {
    		if (uf.getKey().contains(entity)) {
    			for ( String entry:uf.getValue() ) {
    				if ( !idList.equals("") ) {
    					idList = idList + ",";
    				}
    				idList = idList + "\'" + entry + "\'" ;
    			}
    			idList="(" + idList + ")";
    		}
    	}
    	if ( !idList.equals("") ) {
    		condition = " ("+entityAlias+".id not in "+idList+") ";
    	}
    }
    if( condition != "" ) {
    	if ( retSql.toLowerCase().contains( " where " ) ) {
    		retSql = insertCondition(retSql,  " and " + condition);
    	} else {
    		retSql = insertCondition(retSql, " where " + condition);
    	}
    }
    return retSql;
    
  }
  
  public void renderFilters() {
    connect();
    if (connection != null) {
    	for (DatamartFilter filter : getFilters()) {
    		if((DatamartFilter.FilterType.BY_ID != filter.getType()) && (DatamartFilter.FilterType.BY_HISTUUID != filter.getType()) && (DatamartFilter.FilterType.BETWEEN != filter.getType()) && (DatamartFilter.FilterType.BETWEEN_DATE != filter.getType())) {
    			filter.setSql(addUserFilter(filter.getSql(), filter.getEntityName() ));
    			try (ResultSet rs = connection.createStatement().executeQuery(getVendorSql(filter))){
    				if (rs != null) {
    					while (rs.next()) {
    					 		int type = rs.getMetaData().getColumnType(1);
    					 		switch (type) {
    					 			case java.sql.Types.DATE:
    					 			    Date date = rs.getDate(1);
    					 			    if (date != null) {
    					 					if (user == null) {
    					 						filter.addItem(date.toString(), vendorConvertToDateSQL( date ), false);
    					 					} else {
    					 						filter.addItem(SimpleDateFormatter.getFormat("LONGDATE", user.getLocale()).format(date), vendorConvertToDateSQL( date ), false);
    					 					}
    					 				}
    					 				break;
    					 			case java.sql.Types.TIMESTAMP:
    					 				Timestamp timestamp = rs.getTimestamp(1);
    					 				if (timestamp != null) {
    					 					if (user == null) {
    					 						filter.addItem(timestamp.toString(), vendorConvertToTimestampSQL( timestamp ), false);
    					 					} else {
    					 						filter.addItem(SimpleDateFormatter.getFormat("LONGDATE", user.getLocale()).format(timestamp), vendorConvertToTimestampSQL( timestamp ), false);
    					 					}
    					 				}
    					 				break;
    					 			case java.sql.Types.DECIMAL:
    					 				BigDecimal bigDecimal = rs.getBigDecimal(1);
    					 				if (bigDecimal != null) {
    					 					filter.addItem(bigDecimal.toPlainString(), bigDecimal.toPlainString(), false);
    					 				}
    					 				break;
    					 			case java.sql.Types.NUMERIC:
    					 			case java.sql.Types.DOUBLE:
    					 				filter.addItem(Double.toString(rs.getDouble(1)), Double.toString(rs.getDouble(1)), false);
    					 				break;
    					 			case java.sql.Types.INTEGER:
    					 			case java.sql.Types.BIGINT:
    					 				filter.addItem(Integer.toString(rs.getInt(1)), Integer.toString(rs.getInt(1)), false);
    					 				break;
    					 			case java.sql.Types.FLOAT:
    					 				filter.addItem(Float.toString(rs.getFloat(1)), Float.toString(rs.getFloat(1)), false);
    					 				break;
    					 			case java.sql.Types.CHAR:
    					 			case java.sql.Types.NCHAR:
    					 			case java.sql.Types.NVARCHAR:
    					 			case java.sql.Types.VARCHAR:
    							case java.sql.Types.LONGVARCHAR: 
    										filter.addItem(rs.getString(1), rs.getString(1), true);
    										break;
    								}
    					}
    				}
    			} catch (SQLException e) {
    				log.error("{}", e);
    			}
    		}
    	}
    }
    disconnect();
  }
  
  public String applyFilters() {
    String filteredStatement = statement;
    computeConditions();
    for (DatamartFilter filter : getFilters()) {
    	if (DatamartFilter.FilterType.BY_ID == filter.getType() || DatamartFilter.FilterType.BY_HISTUUID == filter.getType()) {
    		if(filter.getCondition() != null) {
    			if	(!filteredStatement.toLowerCase().contains(" where ")) {
    				if	((" "+filter.getCondition()).toLowerCase().contains("where ")) {
    					filteredStatement = insertCondition(filteredStatement, " "+filter.getCondition());
    				}
    				else {
    					filteredStatement = insertCondition(filteredStatement, " where "+filter.getCondition());
    				}
    			}
    			else {
    				if	(filter.getCondition().trim().toLowerCase().startsWith("and ") ||
    					 filter.getCondition().trim().toLowerCase().startsWith("or ") ) {
    					filteredStatement = insertCondition(filteredStatement, filter.getCondition());
    				}
    				else {
    					filteredStatement = insertCondition(filteredStatement, " and "+filter.getCondition());
    				}
    			}
    		}
    	}
    	else {
    		if(filter.getCondition() != null) {
    		   	filteredStatement = filteredStatement.replace(filter.getDecoratedName(), filter.getCondition());
    			filteredStatement =addUserFilter( filteredStatement, filter.getEntityName(), filter.getAliasName() );
    		} else {
    			log.debug("condition was null in UserAccount:{}", filteredStatement);
    			return "";
    		}
    	}
    }
    log.debug("UserAccount:"+filteredStatement);
    String entity=getPrimaryFilterId().split("\\.")[0];
    if( entity.contains("_")){
    	entity=entity.split("\\_")[0];
    }
    filteredStatement = addUserFilter( filteredStatement, entity);
    return filteredStatement;
  }
  
  public DerivedCellSet getResults() {
    return getResults(false, null, null);
  }
  
  public DerivedCellSet getResults(final boolean limited, final Class operativeDtoClass, final List<IDto> operativeDtos) {
    SqlCellSet cellSet = null;
    java.sql.ResultSetMetaData metaData = null;
    connect();
    if (connection != null) {
    	String query = applyFilters();
    	if (query.length()>0) {
    		try {
    			log.debug("statement:{} limited:{}", query, limited);
    			Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
    			if(limited) {
    				stmt.setMaxRows(500);
    				stmt.setFetchSize(500);
    			}
    			ResultSet rs = stmt.executeQuery(query);
    			if(!rs.isClosed()) {
    				metaData = rs.getMetaData();
    				cellSet = new SqlCellSet(rs, metaData, user, resultAttributes, getAxisMap(), getIdMap(), getAliasMap(), getHiddenMap(), datamartDtoMapper, operativeDtoClass, operativeDtos, getPrimaryList(), org.eclipse.osbp.authentication.account.datamarts.DatamartsServiceBinder.getUserAccessService(), false);
    				moreToLoad = false;
    				if(limited && cellSet.getFetchedRows() == 500) {
    					moreToLoad = true;
    				}
    				disconnect();
    				return (new DerivedCellSet(cellSet,
    					metaData,
    					org.eclipse.osbp.authentication.account.datamarts.DatamartsServiceBinder.getDSLMetadataService(), user));
    			}
    		}
    		catch (java.sql.SQLException e) {
    			log.error("Statement: {}  error {}", query, e);
    		}
    	}
    }
    disconnect();
    return null;
  }
  
  @Test
  public void TestUserAccount() {
    renderFilters();
    org.junit.Assert.assertNotNull(getResults(true, null, null));
  }
}