package db.oracle.trueminer;

import org.flywaydb.core.api.migration.spring.SpringJdbcMigration;
import org.springframework.beans.BeanUtils;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;

import java.sql.*;
import java.util.List;
import java.util.Objects;

import static java.util.Collections.singletonList;
import static java.util.stream.Collectors.toList;
import static org.apache.commons.collections.CollectionUtils.isNotEmpty;

/**
 * @author Pacific GeoTech System
 *         <p>
 *         Migration to move Right fields from Independent Right Type groups to Dependent Right Type groups;
 *         to delete Independent Right Type groups;
 *         to delete unnecessary Setting Custom Field Configuration
 */
public class V1_2_0_666__MoveRightFieldsFromIndependentRightTypeGroupToDependentLol implements SpringJdbcMigration {

	private static final String INDEPENDENT_RIGHT_TYPE_GROUPS = "select g.ID_PK, c.JURISDICTION_CODE, g.CUSTOM_GROUP_TYPE from TM_SETTING_CUSTOM_FIELD_CONFIG c " +
			"inner join TM_SETTING_CSTM_FLD_GRP g on g.CUSTOM_FIELD_SETTING_CONFIG_ID = c.ID_PK where c.SCREEN_TYPE = 'RIGHT'";

	private static final String FIELD_TEMPLATES = "select ID_PK, ALIAS, BINDING, DATA_TYPE, DEFAULT_VALUE, DISPLAY, EFFECTIVE_DATE, EXPIRY_DATE, FIELD_LABEL, HAS_CHILD, " +
			"ITEM_ORDER, IS_REQUIRED, IS_MULTISELECT, IS_VISIBLE, SCHEMA_CF_TEMP_PARENT_ID, CUSTOM_FIELD_GROUP_ID, NOT_MANAGEABLE from TM_SETTING_CSTM_FLD_TMPLTE " +
			"where CUSTOM_FIELD_GROUP_ID = ?";

	private static final String DEPENDENT_RIGHT_TYPE_GROUPS = "select g.ID_PK from TM_SETTING_CUSTOM_FIELD_CONFIG c " +
			"inner join TM_SETTING_CSTM_FLD_GRP g on g.CUSTOM_FIELD_SETTING_CONFIG_ID = c.ID_PK inner join TM_RIGHT_TYPE_CODE t on c.ID_PK = t.CUSTOM_FIELD_SETTING_CONFIG_ID " +
			"where c.SCREEN_TYPE = 'RIGHT_TYPE' and g.CUSTOM_GROUP_TYPE = ? and c.JURISDICTION_CODE = ?";

	private static final String DEPENDENT_DEFAULT_RIGHT_TYPE_GROUPS = "select g.ID_PK from TM_SETTING_CSTM_FLD_GRP g inner join TM_SETTING_CUSTOM_FIELD_CONFIG c " +
			"on c.ID_PK = g.CUSTOM_FIELD_SETTING_CONFIG_ID where c.IS_DEFAULT = '1' and c.SCREEN_TYPE = 'RIGHT_TYPE' and g.CUSTOM_GROUP_TYPE = ? and c.JURISDICTION_CODE = ?";

	private static final String INSERT_TEMPLATE = "Insert into TM_SETTING_CSTM_FLD_TMPLTE (ID_PK, ALIAS, BINDING, DATA_TYPE, DEFAULT_VALUE, DISPLAY, EFFECTIVE_DATE, EXPIRY_DATE, " +
			"FIELD_LABEL, HAS_CHILD, ITEM_ORDER, IS_REQUIRED, IS_MULTISELECT, IS_VISIBLE, SCHEMA_CF_TEMP_PARENT_ID, CUSTOM_FIELD_GROUP_ID, NOT_MANAGEABLE)" +
			" values (TM_SET_CUS_FIELD_TEMP_SEQ.nextVal,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

	private static final String DELETE_TEMPLATES = "delete from TM_SETTING_CSTM_FLD_TMPLTE where CUSTOM_FIELD_GROUP_ID in (?)";
	private static final String DELETE_GROUPS = "delete from TM_SETTING_CSTM_FLD_GRP where ID_PK in (?)";
	private static final String DELETE_CONFIG = "delete from TM_SETTING_CUSTOM_FIELD_CONFIG where SCREEN_TYPE = 'RIGHT'";

	@Override
	public void migrate(JdbcTemplate jdbcTemplate) throws Exception {
		List<IndependentRightTypeGroup> independentRightTypeGroups = findIndependentRightTypeGroups(jdbcTemplate);

		for (IndependentRightTypeGroup independentRightTypeGroup : independentRightTypeGroups) {
			List<FieldTemplate> relatedTemplates = findRelatedTemplates(jdbcTemplate, independentRightTypeGroup);
			List<Long> dependentRightTypeGroupIds = findGroupIdsToAddTemplates(jdbcTemplate, independentRightTypeGroup);

			addTemplates(jdbcTemplate, dependentRightTypeGroupIds, relatedTemplates);

			removeTemplates(jdbcTemplate, relatedTemplates);
		}

		removeGroups(jdbcTemplate, independentRightTypeGroups);

		removeConfig(jdbcTemplate);
	}

	private void removeConfig(JdbcTemplate jdbcTemplate) {
		jdbcTemplate.update(DELETE_CONFIG);
	}

	private void removeGroups(JdbcTemplate jdbcTemplate, List<IndependentRightTypeGroup> independentRightTypeGroups) {
		List<Long> groupIds = independentRightTypeGroups.stream()
				.map(IndependentRightTypeGroup::getId)
				.collect(toList());

		if (isNotEmpty(groupIds)) {
			jdbcTemplate.update(DELETE_GROUPS, groupIds);
		}
	}

	private void removeTemplates(JdbcTemplate jdbcTemplate, List<FieldTemplate> relatedTemplates) {
		List<Long> templateIds = relatedTemplates.stream()
				.map(FieldTemplate::getId)
				.collect(toList());

		if (isNotEmpty(templateIds)) {
			jdbcTemplate.update(DELETE_TEMPLATES, templateIds);
		}
	}

	private void addTemplates(JdbcTemplate jdbcTemplate, List<Long> dependentRightTypeGroupIds, List<FieldTemplate> fieldTemplates) {
		for (FieldTemplate template : fieldTemplates) {
			addTemplateToGroups(jdbcTemplate, dependentRightTypeGroupIds, template, fieldTemplates);
		}
	}

	private void addTemplateToGroups(JdbcTemplate jdbcTemplate, List<Long> dependentRightTypeGroupIds, FieldTemplate template, List<FieldTemplate> fieldTemplates) {
		for (Long dependentRightTypeGroupId : dependentRightTypeGroupIds) {
			if (hasChildren(template)) {
				processTemplateWithChildren(jdbcTemplate, template, dependentRightTypeGroupId, fieldTemplates);
			} else {
				addTemplate(jdbcTemplate, template, dependentRightTypeGroupId);
			}
		}
	}

	/**
	 * Returns inserted template id.
	 */
	private Long addTemplate(JdbcTemplate jdbcTemplate, FieldTemplate template, Long groupId) {
		GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();

		jdbcTemplate.update(con -> {
			PreparedStatement statement = con.prepareStatement(INSERT_TEMPLATE, new String[]{"ID_PK"});

			statement.setString(1, template.getAlias());
			statement.setString(2, template.getBinding());
			statement.setString(3, template.getDataType());
			statement.setString(4, template.getDefaultValue());
			setNumber(statement, 5, template.getDisplay(), 6);
			statement.setDate(6, template.getEffectiveDate());
			statement.setDate(7, template.getExpiryDate());
			statement.setString(8, template.getFieldLabel());
			setNumber(statement, 9, template.getHasChild(), 10);
			setNumber(statement, 10, template.getItemOrder(), 11);
			setNumber(statement, 11, template.getIsRequired(), 12);
			setNumber(statement, 12, template.getIsMultiselect(), 13);
			setNumber(statement, 13, template.getIsVisible(), 14);
			setNumber(statement, 14, template.getSchemaCfTempParentId(), 15);
			statement.setLong(15, groupId);
			setNumber(statement, 16, template.getNotManageable(), 17);

			return statement;
		}, keyHolder);

		return keyHolder.getKey().longValue();
	}

	private <T extends Number> void setNumber(PreparedStatement statement, int targetColumnIndex, T number, int sourceColumnIndex) throws SQLException {
		if (number == null) {
			statement.setNull(targetColumnIndex, Types.NUMERIC);
		} else {
			statement.setLong(targetColumnIndex, number.longValue());
		}
	}


	private void processTemplateWithChildren(JdbcTemplate jdbcTemplate, FieldTemplate template, Long groupId, List<FieldTemplate> fieldTemplates) {
		List<FieldTemplate> children = fieldTemplates.stream()
				.filter(fieldTemplate -> Objects.equals(fieldTemplate.getSchemaCfTempParentId(), template.getId()))
				.collect(toList());

		Long newTemplateId = addTemplate(jdbcTemplate, template, groupId);

		for (FieldTemplate child : children) {
			FieldTemplate clone = new FieldTemplate();
			BeanUtils.copyProperties(child, clone);
			clone.setSchemaCfTempParentId(newTemplateId);

			addTemplateToGroups(jdbcTemplate, singletonList(groupId), clone, fieldTemplates);
		}
	}

	private boolean hasChildren(FieldTemplate template) {
		return template.getHasChild() == 1;
	}

	private List<Long> findGroupIdsToAddTemplates(JdbcTemplate jdbcTemplate, IndependentRightTypeGroup independentRightTypeGroup) {
		List<Long> dependentRightTypeGroupIds = jdbcTemplate.queryForList(DEPENDENT_RIGHT_TYPE_GROUPS, Long.class,
				independentRightTypeGroup.getCustomGroupType(), independentRightTypeGroup.getJurisdictionCode());

		List<Long> dependentDefaultRightTypeGroupIds = jdbcTemplate.queryForList(DEPENDENT_DEFAULT_RIGHT_TYPE_GROUPS, Long.class,
				independentRightTypeGroup.getCustomGroupType(), independentRightTypeGroup.getJurisdictionCode());

		dependentRightTypeGroupIds.addAll(dependentDefaultRightTypeGroupIds);

		return dependentRightTypeGroupIds;
	}

	private List<FieldTemplate> findRelatedTemplates(JdbcTemplate jdbcTemplate, IndependentRightTypeGroup independentRightTypeGroup) {
		return jdbcTemplate.query(FIELD_TEMPLATES, new Long[]{independentRightTypeGroup.getId()}, this::createTemplate);
	}

	private FieldTemplate createTemplate(ResultSet rs, int rowNumber) throws SQLException {
		FieldTemplate template = new FieldTemplate();
		template.setId(rs.getLong("ID_PK"));
		template.setAlias(rs.getString("ALIAS"));
		template.setBinding(rs.getString("BINDING"));
		template.setDataType(rs.getString("DATA_TYPE"));
		template.setDefaultValue(rs.getString("DEFAULT_VALUE"));
		template.setDisplay(getNullableInt(rs, "DISPLAY"));
		template.setEffectiveDate(rs.getDate("EFFECTIVE_DATE"));
		template.setExpiryDate(rs.getDate("EXPIRY_DATE"));
		template.setFieldLabel(rs.getString("FIELD_LABEL"));
		template.setHasChild(getNullableInt(rs, "HAS_CHILD"));
		template.setItemOrder(getNullableInt(rs, "ITEM_ORDER"));
		template.setIsRequired(getNullableInt(rs, "IS_REQUIRED"));
		template.setIsMultiselect(getNullableInt(rs, "IS_MULTISELECT"));
		template.setIsVisible(getNullableInt(rs, "IS_VISIBLE"));
		template.setSchemaCfTempParentId(getNullableLong(rs, "SCHEMA_CF_TEMP_PARENT_ID"));
		template.setCustomFieldGroupId(getNullableLong(rs, "CUSTOM_FIELD_GROUP_ID"));
		template.setNotManageable(getNullableInt(rs, "NOT_MANAGEABLE"));
		return template;
	}

	private Integer getNullableInt(ResultSet rs, String columnLabel) throws SQLException {
		int intValue = rs.getInt(columnLabel);
		return rs.wasNull() ? null : intValue;
	}

	private Long getNullableLong(ResultSet rs, String columnLabel) throws SQLException {
		long longValue = rs.getLong(columnLabel);
		return rs.wasNull() ? null : longValue;
	}

	private List<IndependentRightTypeGroup> findIndependentRightTypeGroups(JdbcTemplate jdbcTemplate) {
		return jdbcTemplate.query(INDEPENDENT_RIGHT_TYPE_GROUPS,
				(rs, rowNum) -> new IndependentRightTypeGroup(rs.getLong("ID_PK"), rs.getString("JURISDICTION_CODE"), rs.getString("CUSTOM_GROUP_TYPE")));
	}


	private static class IndependentRightTypeGroup {
		private Long id;
		private String jurisdictionCode;
		private String customGroupType;

		public IndependentRightTypeGroup(Long id, String jurisdictionCode, String customGroupType) {
			this.id = id;
			this.jurisdictionCode = jurisdictionCode;
			this.customGroupType = customGroupType;
		}

		public Long getId() {
			return id;
		}

		public void setId(Long id) {
			this.id = id;
		}

		public String getJurisdictionCode() {
			return jurisdictionCode;
		}

		public void setJurisdictionCode(String jurisdictionCode) {
			this.jurisdictionCode = jurisdictionCode;
		}

		public String getCustomGroupType() {
			return customGroupType;
		}

		public void setCustomGroupType(String customGroupType) {
			this.customGroupType = customGroupType;
		}
	}

	private static class FieldTemplate {
		private Long id;
		private String alias;
		private String binding;
		private String dataType;
		private String defaultValue;
		private Integer display;
		private Date effectiveDate;
		private Date expiryDate;
		private String fieldLabel;
		private Integer hasChild;
		private Integer itemOrder;
		private Integer isRequired;
		private Integer isMultiselect;
		private Integer isVisible;
		private Long schemaCfTempParentId;
		private Long customFieldGroupId;
		private Integer notManageable;

		public Long getId() {
			return id;
		}

		public void setId(Long id) {
			this.id = id;
		}

		public String getAlias() {
			return alias;
		}

		public void setAlias(String alias) {
			this.alias = alias;
		}

		public String getBinding() {
			return binding;
		}

		public void setBinding(String binding) {
			this.binding = binding;
		}

		public String getDataType() {
			return dataType;
		}

		public void setDataType(String dataType) {
			this.dataType = dataType;
		}

		public String getDefaultValue() {
			return defaultValue;
		}

		public void setDefaultValue(String defaultValue) {
			this.defaultValue = defaultValue;
		}

		public Integer getDisplay() {
			return display;
		}

		public void setDisplay(Integer display) {
			this.display = display;
		}

		public Date getEffectiveDate() {
			return effectiveDate;
		}

		public void setEffectiveDate(Date effectiveDate) {
			this.effectiveDate = effectiveDate;
		}

		public Date getExpiryDate() {
			return expiryDate;
		}

		public void setExpiryDate(Date expiryDate) {
			this.expiryDate = expiryDate;
		}

		public String getFieldLabel() {
			return fieldLabel;
		}

		public void setFieldLabel(String fieldLabel) {
			this.fieldLabel = fieldLabel;
		}

		public Integer getHasChild() {
			return hasChild;
		}

		public void setHasChild(Integer hasChild) {
			this.hasChild = hasChild;
		}

		public Integer getItemOrder() {
			return itemOrder;
		}

		public void setItemOrder(Integer itemOrder) {
			this.itemOrder = itemOrder;
		}

		public Integer getIsRequired() {
			return isRequired;
		}

		public void setIsRequired(Integer isRequired) {
			this.isRequired = isRequired;
		}

		public Integer getIsMultiselect() {
			return isMultiselect;
		}

		public void setIsMultiselect(Integer isMultiselect) {
			this.isMultiselect = isMultiselect;
		}

		public Integer getIsVisible() {
			return isVisible;
		}

		public void setIsVisible(Integer isVisible) {
			this.isVisible = isVisible;
		}

		public Long getSchemaCfTempParentId() {
			return schemaCfTempParentId;
		}

		public void setSchemaCfTempParentId(Long schemaCfTempParentId) {
			this.schemaCfTempParentId = schemaCfTempParentId;
		}

		public Long getCustomFieldGroupId() {
			return customFieldGroupId;
		}

		public void setCustomFieldGroupId(Long customFieldGroupId) {
			this.customFieldGroupId = customFieldGroupId;
		}

		public Integer getNotManageable() {
			return notManageable;
		}

		public void setNotManageable(Integer notManageable) {
			this.notManageable = notManageable;
		}
	}
}