package org.codefilarete.stalactite.engine;

import javax.sql.DataSource;
import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashSet;
import java.util.LinkedHashSet;
import java.util.Set;
import java.util.function.Function;

import org.apache.commons.lang3.builder.MultilineRecursiveToStringStyle;
import org.apache.commons.lang3.builder.ToStringBuilder;
import org.assertj.core.api.InstanceOfAssertFactories;
import org.assertj.core.presentation.StandardRepresentation;
import org.codefilarete.stalactite.dsl.FluentMappings;
import org.codefilarete.stalactite.dsl.property.CascadeOptions.RelationMode;
import org.codefilarete.stalactite.dsl.entity.FluentEntityMappingBuilder;
import org.codefilarete.stalactite.dsl.naming.ForeignKeyNamingStrategy;
import org.codefilarete.stalactite.dsl.MappingConfigurationException;
import org.codefilarete.stalactite.dsl.RuntimeMappingException;
import org.codefilarete.stalactite.engine.PersistenceContext.ExecutableBeanPropertyQueryMapper;
import org.codefilarete.stalactite.engine.idprovider.LongProvider;
import org.codefilarete.stalactite.engine.model.device.Address;
import org.codefilarete.stalactite.engine.model.device.Company;
import org.codefilarete.stalactite.engine.model.device.Device;
import org.codefilarete.stalactite.id.Identifier;
import org.codefilarete.stalactite.id.PersistableIdentifier;
import org.codefilarete.stalactite.id.PersistedIdentifier;
import org.codefilarete.stalactite.id.StatefulIdentifierAlreadyAssignedIdentifierPolicy;
import org.codefilarete.stalactite.sql.Dialect;
import org.codefilarete.stalactite.sql.hsqldb.HSQLDBDialectBuilder;
import org.codefilarete.stalactite.sql.ddl.DDLDeployer;
import org.codefilarete.stalactite.sql.ddl.structure.Table;
import org.codefilarete.stalactite.sql.result.Accumulators;
import org.codefilarete.stalactite.sql.result.ResultSetIterator;
import org.codefilarete.stalactite.sql.statement.binder.DefaultParameterBinders;
import org.codefilarete.stalactite.sql.hsqldb.test.HSQLDBInMemoryDataSource;
import org.codefilarete.tool.collection.Arrays;
import org.codefilarete.tool.collection.Iterables;
import org.codefilarete.tool.exception.Exceptions;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Nested;
import org.junit.jupiter.api.Test;

import static org.assertj.core.api.Assertions.assertThat;
import static org.assertj.core.api.Assertions.assertThatCode;
import static org.assertj.core.api.Assertions.assertThatThrownBy;
import static org.codefilarete.stalactite.dsl.property.CascadeOptions.RelationMode.ALL;
import static org.codefilarete.stalactite.dsl.property.CascadeOptions.RelationMode.ALL_ORPHAN_REMOVAL;
import static org.codefilarete.stalactite.dsl.property.CascadeOptions.RelationMode.ASSOCIATION_ONLY;
import static org.codefilarete.stalactite.dsl.property.CascadeOptions.RelationMode.READ_ONLY;

/**
 * @author Guillaume Mary
 */
public class FluentEntityMappingConfigurationSupportManyToOneTest {
	
	private final Dialect dialect = HSQLDBDialectBuilder.defaultHSQLDBDialect();
	private final DataSource dataSource = new HSQLDBInMemoryDataSource();
	private FluentEntityMappingBuilder<Company, Identifier<Long>> companyConfiguration;
	private FluentEntityMappingBuilder<Address, Identifier<Long>> addressConfiguration;
	private PersistenceContext persistenceContext;
	
	@BeforeEach
	public void initTest() {
		dialect.getColumnBinderRegistry().register((Class) Identifier.class, Identifier.identifierBinder(DefaultParameterBinders.LONG_PRIMITIVE_BINDER));
		dialect.getSqlTypeRegistry().put(Identifier.class, "int");
		persistenceContext = new PersistenceContext(dataSource, dialect);
		
		FluentEntityMappingBuilder<Company, Identifier<Long>> companyMappingBuilder = FluentMappings.entityBuilder(Company.class, Identifier.LONG_TYPE)
				.mapKey(Company::getId, StatefulIdentifierAlreadyAssignedIdentifierPolicy.ALREADY_ASSIGNED)
				.map(Company::getName);
		companyConfiguration = companyMappingBuilder;
		
		FluentEntityMappingBuilder<Address, Identifier<Long>> addressMappingBuilder = FluentMappings.entityBuilder(Address.class, Identifier.LONG_TYPE)
				.mapKey(Address::getId, StatefulIdentifierAlreadyAssignedIdentifierPolicy.ALREADY_ASSIGNED)
				.map(Address::getStreet);
		addressConfiguration = addressMappingBuilder;
	}
	
	@Nested
	class CascadeDeclaration {
		
		@Test
		void associationOnly_throwsException() {
			FluentEntityMappingBuilder<Device, Identifier<Long>> mappingBuilder = FluentMappings.entityBuilder(Device.class, Identifier.LONG_TYPE)
					.mapKey(Device::getId, StatefulIdentifierAlreadyAssignedIdentifierPolicy.ALREADY_ASSIGNED)
					.map(Device::getName)
					.mapManyToOne(Device::getLocation, addressConfiguration).cascading(ASSOCIATION_ONLY);
			
			assertThatThrownBy(() -> mappingBuilder.build(persistenceContext))
					.extracting(t -> Exceptions.findExceptionInCauses(t, MappingConfigurationException.class), InstanceOfAssertFactories.THROWABLE)
					.hasMessage(RelationMode.ASSOCIATION_ONLY + " is only relevant for one-to-many association");
		}
		
		@Test
		void notDefined_defaultIsAll_getter() {
			EntityPersister<Device, Identifier<Long>> devicePersister = FluentMappings.entityBuilder(Device.class, Identifier.LONG_TYPE)
					.mapKey(Device::getId, StatefulIdentifierAlreadyAssignedIdentifierPolicy.ALREADY_ASSIGNED)
					.map(Device::getName)
					// no cascade definition
					.mapManyToOne(Device::getManufacturer, companyConfiguration)
					.build(persistenceContext);
			
			DDLDeployer ddlDeployer = new DDLDeployer(persistenceContext);
			ddlDeployer.deployDDL();
			
			Device device = new Device(new PersistableIdentifier<>(42L));
			device.setManufacturer(new Company(new PersistableIdentifier<>(666L)));
			devicePersister.insert(device);
			
			Device selectedDevice = devicePersister.select(new PersistedIdentifier<>(42L));
			assertThat(selectedDevice.getId().getDelegate()).isEqualTo(42L);
			assertThat(selectedDevice.getManufacturer().getId().getDelegate()).isEqualTo(666L);
			
			devicePersister.delete(selectedDevice);
			
			assertThat(devicePersister.select(new PersistedIdentifier<>(42L))).isEqualTo(null);
			// orphan was'nt removed because cascade is ALL, not ALL_ORPHAN_REMOVAL
			EntityPersister<Company, Identifier<Long>> personPersister = companyConfiguration.build(persistenceContext);
			assertThat(personPersister.select(new PersistedIdentifier<>(666L)).getId().getDelegate()).isEqualTo(666L);
		}
		
		@Test
		void readOnly_getter() throws SQLException {
			EntityPersister<Device, Identifier<Long>> devicePersister = FluentMappings.entityBuilder(Device.class, Identifier.LONG_TYPE)
					.mapKey(Device::getId, StatefulIdentifierAlreadyAssignedIdentifierPolicy.ALREADY_ASSIGNED)
					.map(Device::getName)
					// cascade read-only
					.mapManyToOne(Device::getManufacturer, companyConfiguration).cascading(READ_ONLY)
					.build(persistenceContext);
			
			assert_cascade_readOnly(devicePersister);
		}
		
		@Test
		void readOnly_setter() throws SQLException {
			EntityPersister<Device, Identifier<Long>> devicePersister = FluentMappings.entityBuilder(Device.class, Identifier.LONG_TYPE)
					// setting a foreign key naming strategy to be tested
					.withForeignKeyNaming(ForeignKeyNamingStrategy.DEFAULT)
					.mapKey(Device::getId, StatefulIdentifierAlreadyAssignedIdentifierPolicy.ALREADY_ASSIGNED)
					.map(Device::getName)
					// cascade read-only
					.mapManyToOne(Device::setManufacturer, companyConfiguration).cascading(READ_ONLY)
					.build(persistenceContext);
			
			assert_cascade_readOnly(devicePersister);
		}
		
		private void assert_cascade_readOnly(EntityPersister<Device, Identifier<Long>> devicePersister) throws SQLException {
			DDLDeployer ddlDeployer = new DDLDeployer(persistenceContext);
			ddlDeployer.deployDDL();
			
			Device dummyDevice = new Device(new PersistableIdentifier<>(42L));
			dummyDevice.setName("UPS");
			Company company = new Company(new PersistableIdentifier<>(1L));
			company.setName("World company");
			dummyDevice.setManufacturer(company);
			
			// insert throws integrity constraint because it doesn't save target entity
			assertThatThrownBy(() -> devicePersister.insert(dummyDevice))
					.extracting(t -> Exceptions.findExceptionInCauses(t, BatchUpdateException.class), InstanceOfAssertFactories.THROWABLE)
					.hasMessage("integrity constraint violation: foreign key no parent ; FK_DEVICE_MANUFACTURERID_COMPANY_ID table: DEVICE value: 1");
			
			persistenceContext.getConnectionProvider().giveConnection().prepareStatement("insert into Company(id, name) values (1, 'World company')").execute();
			persistenceContext.getConnectionProvider().giveConnection().prepareStatement("insert into Device(id, name, manufacturerId) values (42, 'UPS', 1)").execute();
			
			// select entity and relation
			Device loadedDevice = devicePersister.select(new PersistedIdentifier<>(42L));
			assertThat(loadedDevice.getName()).isEqualTo("UPS");
			assertThat(loadedDevice.getManufacturer().getName()).isEqualTo("World company");
			
			loadedDevice.setName("touched UPS");
			loadedDevice.getManufacturer().setName("touched World Company");
			devicePersister.update(loadedDevice, dummyDevice, false);
			
			// company is left untouched because association is read only
			assertThat(persistenceContext.newQuery("select name from Company where id = 1", String.class)
					.mapKey("name", String.class)
					.execute(Accumulators.getFirst()))
					.isEqualTo("World company");
			
			// deletion has no action on target
			devicePersister.delete(loadedDevice);
			ExecutableQuery<String> stringExecutableQuery = persistenceContext.newQuery("select name from Device", String.class)
					.mapKey("name", String.class);
			assertThat(stringExecutableQuery.execute(Accumulators.toSet())
					.isEmpty()).isTrue();
			assertThat(persistenceContext.newQuery("select name from Company where id = 1", String.class)
					.mapKey("name", String.class)
					.execute(Accumulators.getFirst()))
					.isEqualTo("World company");
		}
		
		@Test
		void cascade_deleteWithOrphanRemoval() throws SQLException {
			EntityPersister<Device, Identifier<Long>> devicePersister = FluentMappings.entityBuilder(Device.class, Identifier.LONG_TYPE)
					.mapKey(Device::getId, StatefulIdentifierAlreadyAssignedIdentifierPolicy.ALREADY_ASSIGNED)
					.map(Device::getName)
					.mapManyToOne(Device::setManufacturer, companyConfiguration).cascading(ALL_ORPHAN_REMOVAL)
					.build(persistenceContext);

			DDLDeployer ddlDeployer = new DDLDeployer(persistenceContext);
			ddlDeployer.deployDDL();

			persistenceContext.getConnectionProvider().giveConnection().createStatement().executeUpdate("insert into Company(id) values (42), (666)");
			persistenceContext.getConnectionProvider().giveConnection().createStatement().executeUpdate("insert into Device(id, manufacturerId) values (100, 42), (200, 666)");
			
			Device persistedDevice = devicePersister.select(new PersistedIdentifier<>(100L));
			devicePersister.delete(persistedDevice);
			ResultSet resultSet;
			// Checking that we deleted what we wanted
			resultSet = persistenceContext.getConnectionProvider().giveConnection().createStatement().executeQuery("select id from Device where id = 100");
			assertThat(resultSet.next()).isFalse();
			resultSet = persistenceContext.getConnectionProvider().giveConnection().createStatement().executeQuery("select id from Company where id = 42");
			assertThat(resultSet.next()).isFalse();
			// but we didn't delete everything !
			resultSet = persistenceContext.getConnectionProvider().giveConnection().createStatement().executeQuery("select id from Device where id = 200");
			assertThat(resultSet.next()).isTrue();
			resultSet = persistenceContext.getConnectionProvider().giveConnection().createStatement().executeQuery("select id from Company where id = 666");
			assertThat(resultSet.next()).isTrue();
		}
		
		@Test
		void all() {
			EntityPersister<Device, Identifier<Long>> devicePersister = FluentMappings.entityBuilder(Device.class, Identifier.LONG_TYPE)
					.mapKey(Device::getId, StatefulIdentifierAlreadyAssignedIdentifierPolicy.ALREADY_ASSIGNED)
					.map(Device::getName)
					.mapManyToOne(Device::setManufacturer, companyConfiguration).cascading(ALL)
					.build(persistenceContext);
			
			DDLDeployer ddlDeployer = new DDLDeployer(persistenceContext);
			ddlDeployer.deployDDL();
			
			LongProvider deviceIdProvider = new LongProvider(42);
			Device dummyDevice = new Device(deviceIdProvider.giveNewIdentifier());
			dummyDevice.setName("UPS");
			
			LongProvider companyIdProvider = new LongProvider();
			Company company = new Company(companyIdProvider.giveNewIdentifier());
			company.setName("World Company");
			dummyDevice.setManufacturer(company);
			
			// insert cascade test
			devicePersister.insert(dummyDevice);
			Device persistedDevice = devicePersister.select(dummyDevice.getId());
			assertThat(persistedDevice.getId()).isEqualTo(new PersistedIdentifier<>(42L));
			assertThat(persistedDevice.getManufacturer().getName()).isEqualTo("World Company");
			assertThat(persistedDevice.getManufacturer().getId().isPersisted()).isTrue();
			
			// choosing better names for next tests
			Device modifiedDevice = persistedDevice;
			Device referentDevice = dummyDevice;
			
			// nullifiying relation test
			modifiedDevice.setManufacturer(null);
			devicePersister.update(modifiedDevice, referentDevice, false);
			modifiedDevice = devicePersister.select(referentDevice.getId());
			assertThat(modifiedDevice.getManufacturer()).isNull();
			
			// ensuring that manufacturer was not deleted nor updated (we didn't ask for orphan removal)
			ExecutableBeanPropertyQueryMapper<LiteCompany> companySelector = persistenceContext.newQuery("select name from Company where id = :id", LiteCompany.class)
					.mapKey(LiteCompany::new, "name", String.class);
			LiteCompany liteCompany = companySelector
					.set("id", company.getId())
					.execute(Accumulators.getFirstUnique());
			// but relation is cut on both sides (because setCapital(..) calls setCountry(..))
			assertThat(liteCompany).usingRecursiveComparison().isEqualTo(new LiteCompany("World Company"));
			
			// from null to a (new) object
			referentDevice = devicePersister.select(referentDevice.getId());
			Company stalactiteCorp = new Company(companyIdProvider.giveNewIdentifier());
			stalactiteCorp.setName("Stalactite Corp");
			modifiedDevice.setManufacturer(stalactiteCorp);
			devicePersister.update(modifiedDevice, referentDevice, false);
			modifiedDevice = devicePersister.select(referentDevice.getId());
			assertThat(modifiedDevice.getManufacturer()).usingRecursiveComparison().isEqualTo(stalactiteCorp);
			// ensuring that capital was not deleted nor updated
			assertThat(companySelector
					.set("id", stalactiteCorp.getId())
					.execute(Accumulators.getFirstUnique())).usingRecursiveComparison().isEqualTo(new LiteCompany("Stalactite Corp"));
			
			// testing update cascade
			referentDevice = devicePersister.select(referentDevice.getId());
			modifiedDevice.getManufacturer().setName("Skynet");
			devicePersister.update(modifiedDevice, referentDevice, false);
			modifiedDevice = devicePersister.select(referentDevice.getId());
			// ensuring that capital was not deleted nor updated
			assertThat(companySelector
					.set("id", stalactiteCorp.getId())
					.execute(Accumulators.getFirstUnique())).usingRecursiveComparison().isEqualTo(new LiteCompany("Skynet"));
			
			// testing delete cascade
			devicePersister.delete(modifiedDevice);
			// ensuring that capital was not deleted nor updated
			assertThat(companySelector
					.set("id", stalactiteCorp.getId())
					.execute(Accumulators.getFirstUnique())).usingRecursiveComparison().isEqualTo(new LiteCompany("Skynet"));
		}
	}
	
	@Nested
	class ForeignKeyCreation {
		
		@Test
		void defaultBehavior() throws SQLException {
			FluentMappings.entityBuilder(Device.class, Identifier.LONG_TYPE)
					// setting a foreign key naming strategy to be tested
					.withForeignKeyNaming(ForeignKeyNamingStrategy.DEFAULT)
					.mapKey(Device::getId, StatefulIdentifierAlreadyAssignedIdentifierPolicy.ALREADY_ASSIGNED)
					.map(Device::getName)
					.mapManyToOne(Device::getLocation, addressConfiguration).mandatory()
					.build(persistenceContext);
			
			DDLDeployer ddlDeployer = new DDLDeployer(persistenceContext);
			ddlDeployer.deployDDL();
			
			Connection currentConnection = persistenceContext.getConnectionProvider().giveConnection();
			ResultSetIterator<JdbcForeignKey> fkPersonIterator = new ResultSetIterator<JdbcForeignKey>(currentConnection.getMetaData().getExportedKeys(null, null, "ADDRESS")) {
				@Override
				public JdbcForeignKey convert(ResultSet rs) throws SQLException {
					return new JdbcForeignKey(
							rs.getString("FK_NAME"),
							rs.getString("FKTABLE_NAME"), rs.getString("FKCOLUMN_NAME"),
							rs.getString("PKTABLE_NAME"), rs.getString("PKCOLUMN_NAME")
					);
				}
			};
			JdbcForeignKey foundForeignKey = Iterables.first(fkPersonIterator);
			JdbcForeignKey expectedForeignKey = new JdbcForeignKey("FK_DEVICE_LOCATIONID_ADDRESS_ID", "DEVICE", "LOCATIONID", "ADDRESS", "ID");
			assertThat(foundForeignKey.getSignature()).isEqualTo(expectedForeignKey.getSignature());

			class Column {
				private final String tableName;
				private final String name;
				private final boolean nullable;

				Column(String tableName, String name, boolean nullable) {
					this.tableName = tableName;
					this.name = name;
					this.nullable = nullable;
				}
			}
			
			ResultSetIterator<Column> columnIterator = new ResultSetIterator<Column>(currentConnection.getMetaData().getColumns(null, null, "DEVICE", "%")) {
				@Override
				public Column convert(ResultSet rs) throws SQLException {
					return new Column(
							rs.getString("TABLE_NAME"),
							rs.getString("COLUMN_NAME"),
							rs.getBoolean("NULLABLE")
					);
				}
			};
			assertThat(Iterables.collectToList(() -> columnIterator, Function.identity()))
					.usingRecursiveFieldByFieldElementComparator()
					.withRepresentation(new StandardRepresentation() {
						@Override
						protected String fallbackToStringOf(Object object) {
							return ToStringBuilder.reflectionToString(object, new MultilineRecursiveToStringStyle());
						}
					})
					.containsExactlyInAnyOrder(
					new Column("DEVICE", "ID", false),
					new Column("DEVICE", "NAME", true),
					new Column("DEVICE", "LOCATIONID", false));
		}
		
		@Test
		void columnName() throws SQLException {
			FluentMappings.entityBuilder(Device.class, Identifier.LONG_TYPE)
					// setting a foreign key naming strategy to be tested
					.withForeignKeyNaming(ForeignKeyNamingStrategy.DEFAULT)
					.mapKey(Device::getId, StatefulIdentifierAlreadyAssignedIdentifierPolicy.ALREADY_ASSIGNED)
					.map(Device::getName)
					.mapManyToOne(Device::getLocation, addressConfiguration)
					.columnName("positionId")
						.mandatory()
					.build(persistenceContext);
			
			DDLDeployer ddlDeployer = new DDLDeployer(persistenceContext);
			ddlDeployer.deployDDL();
			
			Connection currentConnection = persistenceContext.getConnectionProvider().giveConnection();
			ResultSetIterator<JdbcForeignKey> fkPersonIterator = new ResultSetIterator<JdbcForeignKey>(currentConnection.getMetaData().getExportedKeys(null, null, "ADDRESS")) {
				@Override
				public JdbcForeignKey convert(ResultSet rs) throws SQLException {
					return new JdbcForeignKey(
							rs.getString("FK_NAME"),
							rs.getString("FKTABLE_NAME"), rs.getString("FKCOLUMN_NAME"),
							rs.getString("PKTABLE_NAME"), rs.getString("PKCOLUMN_NAME")
					);
				}
			};
			JdbcForeignKey foundForeignKey = Iterables.first(fkPersonIterator);
			JdbcForeignKey expectedForeignKey = new JdbcForeignKey("FK_DEVICE_POSITIONID_ADDRESS_ID", "DEVICE", "POSITIONID", "ADDRESS", "ID");
			assertThat(foundForeignKey.getSignature()).isEqualTo(expectedForeignKey.getSignature());
			
			class Column {
				private final String tableName;
				private final String name;
				private final boolean nullable;
				
				Column(String tableName, String name, boolean nullable) {
					this.tableName = tableName;
					this.name = name;
					this.nullable = nullable;
				}
			}
			
			ResultSetIterator<Column> columnIterator = new ResultSetIterator<Column>(currentConnection.getMetaData().getColumns(null, null, "DEVICE", "%")) {
				@Override
				public Column convert(ResultSet rs) throws SQLException {
					return new Column(
							rs.getString("TABLE_NAME"),
							rs.getString("COLUMN_NAME"),
							rs.getBoolean("NULLABLE")
					);
				}
			};
			assertThat(Iterables.collectToList(() -> columnIterator, Function.identity()))
					.usingRecursiveFieldByFieldElementComparator()
					.withRepresentation(new StandardRepresentation() {
						@Override
						protected String fallbackToStringOf(Object object) {
							return ToStringBuilder.reflectionToString(object, new MultilineRecursiveToStringStyle());
						}
					})
					.containsExactlyInAnyOrder(
							new Column("DEVICE", "ID", false),
							new Column("DEVICE", "NAME", true),
							new Column("DEVICE", "POSITIONID", false));
		}
		
		@Test
		void defaultBehavior_2Relations() throws SQLException {
			FluentMappings.entityBuilder(Device.class, Identifier.LONG_TYPE)
					// setting a foreign key naming strategy to be tested
					.withForeignKeyNaming(ForeignKeyNamingStrategy.DEFAULT)
					.mapKey(Device::getId, StatefulIdentifierAlreadyAssignedIdentifierPolicy.ALREADY_ASSIGNED)
					.map(Device::getName)
					.mapManyToOne(Device::getLocation, FluentMappings.entityBuilder(Address.class, Identifier.LONG_TYPE)
							.mapKey(Address::getId, StatefulIdentifierAlreadyAssignedIdentifierPolicy.ALREADY_ASSIGNED)
							.map(Address::getStreet))
					.mapManyToOne(Device::getManufacturer, FluentMappings.entityBuilder(Company.class, Identifier.LONG_TYPE)
							.mapKey(Company::getId, StatefulIdentifierAlreadyAssignedIdentifierPolicy.ALREADY_ASSIGNED)
							.map(Company::getName))
					.build(persistenceContext);
			
			DDLDeployer ddlDeployer = new DDLDeployer(persistenceContext);
			ddlDeployer.deployDDL();
			
			Connection currentConnection = persistenceContext.getConnectionProvider().giveConnection();
			ResultSetIterator<JdbcForeignKey> fkPersonIterator = new ResultSetIterator<JdbcForeignKey>(currentConnection.getMetaData().getExportedKeys(null, null, "ADDRESS")) {
				@Override
				public JdbcForeignKey convert(ResultSet rs) throws SQLException {
					return new JdbcForeignKey(
							rs.getString("FK_NAME"),
							rs.getString("FKTABLE_NAME"), rs.getString("FKCOLUMN_NAME"),
							rs.getString("PKTABLE_NAME"), rs.getString("PKCOLUMN_NAME")
					);
				}
			};
			JdbcForeignKey foundForeignKey = Iterables.first(fkPersonIterator);
			JdbcForeignKey expectedForeignKey = new JdbcForeignKey("FK_DEVICE_LOCATIONID_ADDRESS_ID", "DEVICE", "LOCATIONID", "ADDRESS", "ID");
			assertThat(foundForeignKey.getSignature()).isEqualTo(expectedForeignKey.getSignature());
			
			ResultSetIterator<JdbcForeignKey> fkLocationIterator = new ResultSetIterator<JdbcForeignKey>(currentConnection.getMetaData().getExportedKeys(null, null, "COMPANY")) {
				@Override
				public JdbcForeignKey convert(ResultSet rs) throws SQLException {
					return new JdbcForeignKey(
							rs.getString("FK_NAME"),
							rs.getString("FKTABLE_NAME"), rs.getString("FKCOLUMN_NAME"),
							rs.getString("PKTABLE_NAME"), rs.getString("PKCOLUMN_NAME")
					);
				}
			};
			foundForeignKey = Iterables.first(fkLocationIterator);
			expectedForeignKey = new JdbcForeignKey("FK_DEVICE_MANUFACTURERID_COMPANY_ID", "DEVICE", "MANUFACTURERID", "COMPANY", "ID");
			assertThat(foundForeignKey.getSignature()).isEqualTo(expectedForeignKey.getSignature());
		}
		
		@Test
		void withTargetTable_targetTableIsUsed() throws SQLException {
			FluentMappings.entityBuilder(Device.class, Identifier.LONG_TYPE)
					// setting a foreign key naming strategy to be tested
					.withForeignKeyNaming(ForeignKeyNamingStrategy.DEFAULT)
					.mapKey(Device::getId, StatefulIdentifierAlreadyAssignedIdentifierPolicy.ALREADY_ASSIGNED)
					.map(Device::getName)
					.mapManyToOne(Device::getLocation, FluentMappings.entityBuilder(Address.class, Identifier.LONG_TYPE)
							.mapKey(Address::getId, StatefulIdentifierAlreadyAssignedIdentifierPolicy.ALREADY_ASSIGNED)
							.map(Address::getStreet)
							.onTable(new Table<>("Township")))
					.build(persistenceContext);
			
			DDLDeployer ddlDeployer = new DDLDeployer(persistenceContext);
			ddlDeployer.deployDDL();
			
			Connection currentConnection = persistenceContext.getConnectionProvider().giveConnection();
			
			ResultSetIterator<Table> tableIterator = new ResultSetIterator<Table>(currentConnection.getMetaData().getTables(null, currentConnection.getSchema(),
					null, null)) {
				@Override
				public Table convert(ResultSet rs) throws SQLException {
					return new Table(
							rs.getString("TABLE_NAME")
					);
				}
			};
			Set<String> foundTables = Iterables.collect(() -> tableIterator, Table::getName, HashSet::new);
			assertThat(foundTables).containsExactlyInAnyOrder("DEVICE", "TOWNSHIP");
			
			ResultSetIterator<JdbcForeignKey> fkPersonIterator = new ResultSetIterator<JdbcForeignKey>(currentConnection.getMetaData().getExportedKeys(null, null,
					"TOWNSHIP")) {
				@Override
				public JdbcForeignKey convert(ResultSet rs) throws SQLException {
					return new JdbcForeignKey(
							rs.getString("FK_NAME"),
							rs.getString("FKTABLE_NAME"), rs.getString("FKCOLUMN_NAME"),
							rs.getString("PKTABLE_NAME"), rs.getString("PKCOLUMN_NAME")
					);
				}
			};
			JdbcForeignKey foundForeignKey = Iterables.first(fkPersonIterator);
			JdbcForeignKey expectedForeignKey = new JdbcForeignKey("FK_DEVICE_LOCATIONID_TOWNSHIP_ID", "DEVICE", "LOCATIONID", "TOWNSHIP", "ID");
			assertThat(foundForeignKey.getSignature()).isEqualTo(expectedForeignKey.getSignature());
		}
		
		@Test
		void withTargetTableSetByTargetEntity_tableSetByTargetEntityIsUSed() throws SQLException {
			FluentMappings.entityBuilder(Device.class, Identifier.LONG_TYPE)
					// setting a foreign key naming strategy to be tested
					.withForeignKeyNaming(ForeignKeyNamingStrategy.DEFAULT)
					.mapKey(Device::getId, StatefulIdentifierAlreadyAssignedIdentifierPolicy.ALREADY_ASSIGNED)
					.map(Device::getName)
					.mapManyToOne(Device::getLocation, FluentMappings.entityBuilder(Address.class, Identifier.LONG_TYPE)
							.onTable(new Table<>("Town"))
							.mapKey(Address::getId, StatefulIdentifierAlreadyAssignedIdentifierPolicy.ALREADY_ASSIGNED)
							.map(Address::getStreet))
					.build(persistenceContext);
			
			DDLDeployer ddlDeployer = new DDLDeployer(persistenceContext);
			ddlDeployer.deployDDL();
			
			Connection currentConnection = persistenceContext.getConnectionProvider().giveConnection();
			
			ResultSetIterator<Table> tableIterator = new ResultSetIterator<Table>(currentConnection.getMetaData().getTables(null, currentConnection.getSchema(),
					null, null)) {
				@Override
				public Table convert(ResultSet rs) throws SQLException {
					return new Table(
							rs.getString("TABLE_NAME")
					);
				}
			};
			Set<String> foundTables = Iterables.collect(() -> tableIterator, Table::getName, HashSet::new);
			assertThat(foundTables).containsExactlyInAnyOrder("DEVICE", "TOWN");
			
			ResultSetIterator<JdbcForeignKey> fkPersonIterator = new ResultSetIterator<JdbcForeignKey>(currentConnection.getMetaData().getExportedKeys(null, null,
					"TOWN")) {
				@Override
				public JdbcForeignKey convert(ResultSet rs) throws SQLException {
					return new JdbcForeignKey(
							rs.getString("FK_NAME"),
							rs.getString("FKTABLE_NAME"), rs.getString("FKCOLUMN_NAME"),
							rs.getString("PKTABLE_NAME"), rs.getString("PKCOLUMN_NAME")
					);
				}
			};
			JdbcForeignKey foundForeignKey = Iterables.first(fkPersonIterator);
			JdbcForeignKey expectedForeignKey = new JdbcForeignKey("FK_DEVICE_LOCATIONID_TOWN_ID", "DEVICE", "LOCATIONID", "TOWN", "ID");
			assertThat(foundForeignKey.getSignature()).isEqualTo(expectedForeignKey.getSignature());
		}
	}
	
	@Test
	void multiple_manyToOne() throws SQLException {
		EntityPersister<Device, Identifier<Long>> devicePersister = FluentMappings.entityBuilder(Device.class, Identifier.LONG_TYPE)
				.mapKey(Device::getId, StatefulIdentifierAlreadyAssignedIdentifierPolicy.ALREADY_ASSIGNED)
				.map(Device::getName)
				.mapManyToOne(Device::setManufacturer, companyConfiguration).cascading(ALL)
				.mapManyToOne(Device::setLocation, addressConfiguration).cascading(ALL)
				.build(persistenceContext);
		
		DDLDeployer ddlDeployer = new DDLDeployer(persistenceContext);
		ddlDeployer.deployDDL();
		
		LongProvider deviceIdProvider = new LongProvider();
		Device dummyDevice = new Device(deviceIdProvider.giveNewIdentifier());
		dummyDevice.setName("UPS");
		
		Company company = new Company(new LongProvider().giveNewIdentifier());
		company.setName("World Company");
		dummyDevice.setManufacturer(company);
		
		Address somewhere = new Address(new LongProvider().giveNewIdentifier());
		somewhere.setStreet("somewhere");
		dummyDevice.setLocation(somewhere);
		
		// testing insert cascade
		devicePersister.insert(dummyDevice);
		Device persistedDevice = devicePersister.select(dummyDevice.getId());
		assertThat(persistedDevice.getId()).isEqualTo(new PersistedIdentifier<>(0L));
		assertThat(persistedDevice.getManufacturer().getName()).isEqualTo("World Company");
		assertThat(((Address) persistedDevice.getLocation()).getStreet()).isEqualTo("somewhere");
		assertThat(persistedDevice.getManufacturer().getId().isPersisted()).isTrue();
		assertThat(persistedDevice.getLocation().getId().isPersisted()).isTrue();
		
		// testing insert cascade with another Device reusing ManyToOne entities
		Device dummyDevice2 = new Device(deviceIdProvider.giveNewIdentifier());
		dummyDevice2.setName("France 2");
		dummyDevice2.setManufacturer(company);
		dummyDevice2.setLocation(somewhere);
		devicePersister.insert(dummyDevice2);
		// database must be up to date
		Device persistedDevice2 = devicePersister.select(dummyDevice2.getId());
		assertThat(persistedDevice2.getId()).isEqualTo(new PersistedIdentifier<>(1L));
		assertThat(persistedDevice2.getManufacturer().getName()).isEqualTo("World Company");
		assertThat(persistedDevice2.getManufacturer().getId().getDelegate()).isEqualTo(persistedDevice.getManufacturer().getId().getDelegate());
		assertThat(persistedDevice2.getLocation().getId().getDelegate()).isEqualTo(persistedDevice.getLocation().getId().getDelegate());
		assertThat(persistedDevice2.getManufacturer()).isNotSameAs(persistedDevice.getManufacturer());
		assertThat(persistedDevice2.getLocation()).isNotSameAs(persistedDevice.getLocation());
		
		// testing update cascade
		persistedDevice2.getManufacturer().setName("World Company renamed");
		((Address) persistedDevice2.getLocation()).setStreet("somewhere renamed");
		devicePersister.update(persistedDevice2, dummyDevice2, true);
		// database must be up to date
		ResultSet resultSet;
		resultSet = persistenceContext.getConnectionProvider().giveConnection().createStatement().executeQuery("select name from Company");
		resultSet.next();
		assertThat(resultSet.getString("name")).isEqualTo("World Company renamed");
		assertThat(resultSet.next()).isFalse();
		resultSet = persistenceContext.getConnectionProvider().giveConnection().createStatement().executeQuery("select street from Address");
		resultSet.next();
		assertThat(resultSet.getString("street")).isEqualTo("somewhere renamed");
		assertThat(resultSet.next()).isFalse();
		
		// testing delete cascade
		// but we have to remove first the other device that points to the same manufacturer, else will get a constraint violation
		assertThat(persistenceContext.getConnectionProvider().giveConnection().createStatement().executeUpdate(
				"update Device set manufacturerId = null, locationId = null where id = " + dummyDevice2.getId().getDelegate())).isEqualTo(1);
		devicePersister.delete(persistedDevice);
		// database must be up to date
		resultSet = persistenceContext.getConnectionProvider().giveConnection().createStatement()
				.executeQuery("select id from Device where id = " + persistedDevice.getId().getDelegate());
		assertThat(resultSet.next()).isFalse();
		resultSet = persistenceContext.getConnectionProvider().giveConnection().createStatement()
				.executeQuery("select id from Company where id = " + persistedDevice.getManufacturer().getId().getDelegate());
		assertThat(resultSet.next()).isTrue();
		resultSet = persistenceContext.getConnectionProvider().giveConnection().createStatement()
				.executeQuery("select id from Address where id = " + persistedDevice.getLocation().getId().getDelegate());
		assertThat(resultSet.next()).isTrue();
	}
	
	@Test
	void multiple_manyToOne_partialOrphanRemoval() throws SQLException {
		EntityPersister<Device, Identifier<Long>> devicePersister = FluentMappings.entityBuilder(Device.class, Identifier.LONG_TYPE)
				.mapKey(Device::getId, StatefulIdentifierAlreadyAssignedIdentifierPolicy.ALREADY_ASSIGNED)
				.map(Device::getName)
				.mapManyToOne(Device::setManufacturer, companyConfiguration).cascading(ALL_ORPHAN_REMOVAL)
				.mapManyToOne(Device::setLocation, addressConfiguration).cascading(ALL)
				.build(persistenceContext);
		
		DDLDeployer ddlDeployer = new DDLDeployer(persistenceContext);
		ddlDeployer.deployDDL();
		
		LongProvider deviceIdProvider = new LongProvider();
		Device dummyDevice = new Device(deviceIdProvider.giveNewIdentifier());
		dummyDevice.setName("UPS");
		
		Company company = new Company(new LongProvider().giveNewIdentifier());
		company.setName("World Company");
		dummyDevice.setManufacturer(company);
		
		Address somewhere = new Address(new LongProvider().giveNewIdentifier());
		somewhere.setStreet("somewhere");
		dummyDevice.setLocation(somewhere);
		
		// testing insert cascade
		devicePersister.insert(dummyDevice);
		Device persistedDevice = devicePersister.select(dummyDevice.getId());
		assertThat(persistedDevice.getId()).isEqualTo(new PersistedIdentifier<>(0L));
		assertThat(persistedDevice.getManufacturer().getName()).isEqualTo("World Company");
		assertThat(((Address) persistedDevice.getLocation()).getStreet()).isEqualTo("somewhere");
		assertThat(persistedDevice.getManufacturer().getId().isPersisted()).isTrue();
		assertThat(persistedDevice.getLocation().getId().isPersisted()).isTrue();
		
		// testing insert cascade with another Device reusing ManyToOne entities
		Device dummyDevice2 = new Device(deviceIdProvider.giveNewIdentifier());
		dummyDevice2.setName("UPS 2");
		dummyDevice2.setManufacturer(company);
		dummyDevice2.setLocation(somewhere);
		devicePersister.insert(dummyDevice2);
		// database must be up to date
		Device persistedDevice2 = devicePersister.select(dummyDevice2.getId());
		assertThat(persistedDevice2.getId()).isEqualTo(new PersistedIdentifier<>(1L));
		assertThat(persistedDevice2.getManufacturer().getName()).isEqualTo("World Company");
		assertThat(persistedDevice2.getManufacturer().getId().getDelegate()).isEqualTo(persistedDevice.getManufacturer().getId().getDelegate());
		assertThat(persistedDevice2.getLocation().getId().getDelegate()).isEqualTo(persistedDevice.getLocation().getId().getDelegate());
		assertThat(persistedDevice2.getManufacturer()).isNotSameAs(persistedDevice.getManufacturer());
		assertThat(persistedDevice2.getLocation()).isNotSameAs(persistedDevice.getLocation());
		
		// testing update cascade
		// but we have to remove first the other device that points to the same manufacturer, else will get a constraint violation
		assertThat(persistenceContext.getConnectionProvider().giveConnection().createStatement().executeUpdate(
				"update Device set manufacturerId = null, locationId = null where id = " + dummyDevice.getId().getDelegate())).isEqualTo(1);
		persistedDevice2.setManufacturer(null);
		((Address) persistedDevice2.getLocation()).setStreet("somewhere renamed");
		devicePersister.update(persistedDevice2, dummyDevice2, true);
		// database must be up to date
		ResultSet resultSet;
		resultSet = persistenceContext.getConnectionProvider().giveConnection().createStatement().executeQuery("select name from Company");
		assertThat(resultSet.next()).isFalse();
		resultSet = persistenceContext.getConnectionProvider().giveConnection().createStatement().executeQuery("select street from Address");
		resultSet.next();
		assertThat(resultSet.getString("street")).isEqualTo("somewhere renamed");
		assertThat(resultSet.next()).isFalse();
		
		// testing delete cascade
		devicePersister.delete(persistedDevice2);
		// database must be up to date
		resultSet = persistenceContext.getConnectionProvider().giveConnection().createStatement()
				.executeQuery("select id from Device where id = " + persistedDevice2.getId().getDelegate());
		assertThat(resultSet.next()).isFalse();
		resultSet = persistenceContext.getConnectionProvider().giveConnection().createStatement()
				.executeQuery("select id from Company where id = " + dummyDevice2.getManufacturer().getId().getDelegate());
		assertThat(resultSet.next()).isFalse();
		resultSet = persistenceContext.getConnectionProvider().giveConnection().createStatement()
				.executeQuery("select id from Address where id = " + persistedDevice2.getLocation().getId().getDelegate());
		assertThat(resultSet.next()).isTrue();
	}
	
	@Nested
	class BiDirectionality {
		
		@Test
		void reverseCollection() {
			EntityPersister<Device, Identifier<Long>> devicePersister = FluentMappings.entityBuilder(Device.class, Identifier.LONG_TYPE)
					.mapKey(Device::getId, StatefulIdentifierAlreadyAssignedIdentifierPolicy.ALREADY_ASSIGNED)
					.map(Device::getName)
					.mapManyToOne(Device::setManufacturer, companyConfiguration).cascading(ALL).reverseCollection(Company::getDevices)
					.build(persistenceContext);
			
			DDLDeployer ddlDeployer = new DDLDeployer(persistenceContext);
			ddlDeployer.deployDDL();
			
			LongProvider deviceIdProvider = new LongProvider();
			Device device1 = new Device(deviceIdProvider.giveNewIdentifier());
			device1.setName("device 1");
			Device device2 = new Device(deviceIdProvider.giveNewIdentifier());
			device2.setName("device 2");
			
			Company company = new Company(new LongProvider().giveNewIdentifier());
			company.setName("World Company");
			device1.setManufacturer(company);
			company.addDevice(device1);
			device2.setManufacturer(company);
			company.addDevice(device2);
			
			devicePersister.insert(device1);
			devicePersister.insert(device2);
			
			Set<Device> select = devicePersister.select(Arrays.asSet(device1.getId(), device2.getId()));
			Device loadedDevice1 = Iterables.find(select, Device::getName, "device 1"::equals).getLeft();
			Device loadedDevice2 = Iterables.find(select, Device::getName, "device 2"::equals).getLeft();
			assertThat(loadedDevice1.getManufacturer().getDevices()).containsExactlyInAnyOrder(loadedDevice1, loadedDevice2);
		}
		
		@Test
		void reverselySetBy() {
			EntityPersister<Device, Identifier<Long>> devicePersister = FluentMappings.entityBuilder(Device.class, Identifier.LONG_TYPE)
					.mapKey(Device::getId, StatefulIdentifierAlreadyAssignedIdentifierPolicy.ALREADY_ASSIGNED)
					.map(Device::getName)
					.mapManyToOne(Device::setManufacturer, companyConfiguration).cascading(ALL).reverselySetBy(Company::addDevice)
					.build(persistenceContext);
			
			DDLDeployer ddlDeployer = new DDLDeployer(persistenceContext);
			ddlDeployer.deployDDL();
			
			LongProvider deviceIdProvider = new LongProvider();
			Device device1 = new Device(deviceIdProvider.giveNewIdentifier());
			device1.setName("device 1");
			Device device2 = new Device(deviceIdProvider.giveNewIdentifier());
			device2.setName("device 2");
			
			Company company = new Company(new LongProvider().giveNewIdentifier());
			company.setName("World Company");
			device1.setManufacturer(company);
			company.addDevice(device1);
			device2.setManufacturer(company);
			company.addDevice(device2);
			
			devicePersister.insert(device1);
			devicePersister.insert(device2);
			
			Set<Device> select = devicePersister.select(Arrays.asSet(device1.getId(), device2.getId()));
			Device loadedDevice1 = Iterables.find(select, Device::getName, "device 1"::equals).getLeft();
			Device loadedDevice2 = Iterables.find(select, Device::getName, "device 2"::equals).getLeft();
			assertThat(loadedDevice1.getManufacturer().getDevices()).containsExactlyInAnyOrder(loadedDevice1, loadedDevice2);
		}
		
		@Test
		void reverselyInitializeWith() {
			EntityPersister<Device, Identifier<Long>> devicePersister = FluentMappings.entityBuilder(Device.class, Identifier.LONG_TYPE)
					.mapKey(Device::getId, StatefulIdentifierAlreadyAssignedIdentifierPolicy.ALREADY_ASSIGNED)
					.map(Device::getName)
					.mapManyToOne(Device::setManufacturer, companyConfiguration).cascading(ALL)
						.reverseCollection(Company::getDevices)
						.reverselyInitializeWith(LinkedHashSet::new)
					.build(persistenceContext);
			
			DDLDeployer ddlDeployer = new DDLDeployer(persistenceContext);
			ddlDeployer.deployDDL();
			
			LongProvider deviceIdProvider = new LongProvider();
			Device device1 = new Device(deviceIdProvider.giveNewIdentifier());
			device1.setName("device 1");
			Device device2 = new Device(deviceIdProvider.giveNewIdentifier());
			device2.setName("device 2");
			
			Company company = new Company(new LongProvider().giveNewIdentifier());
			company.setName("World Company");
			device1.setManufacturer(company);
			company.addDevice(device1);
			device2.setManufacturer(company);
			company.addDevice(device2);
			
			devicePersister.insert(device1);
			devicePersister.insert(device2);
			
			Set<Device> select = devicePersister.select(Arrays.asSet(device1.getId(), device2.getId()));
			Device loadedDevice1 = Iterables.find(select, Device::getName, "device 1"::equals).getLeft();
			Device loadedDevice2 = Iterables.find(select, Device::getName, "device 2"::equals).getLeft();
			assertThat(loadedDevice1.getManufacturer().getDevices()).containsExactlyInAnyOrder(loadedDevice1, loadedDevice2);
			assertThat(loadedDevice1.getManufacturer().getDevices()).isInstanceOf(LinkedHashSet.class);
		}
	}
	
	@Test
	void mandatory_withNullTarget_throwsException() {
		EntityPersister<Device, Identifier<Long>> devicePersister = FluentMappings.entityBuilder(Device.class, Identifier.LONG_TYPE)
				.mapKey(Device::getId, StatefulIdentifierAlreadyAssignedIdentifierPolicy.ALREADY_ASSIGNED)
				.map(Device::getName)
				.mapManyToOne(Device::getManufacturer, companyConfiguration).cascading(ALL).mandatory()
				.build(persistenceContext);
		
		DDLDeployer ddlDeployer = new DDLDeployer(persistenceContext);
		ddlDeployer.deployDDL();
		
		// asserting null check at insertion time
		Device dummyDevice = new Device(new PersistableIdentifier<>(42L));
		dummyDevice.setManufacturer(null);
		assertThatCode(() -> devicePersister.insert(dummyDevice)).isInstanceOf(RuntimeMappingException.class)
				.hasMessage("Non null value expected for relation Device::getManufacturer on object o.c.s.e.m.d.Device@42");
		
		// completing the relation for update test hereafter
		Company company = new Company(new PersistableIdentifier<>(1L));
		dummyDevice.setManufacturer(company);
		devicePersister.insert(dummyDevice);
		
		// asserting null check at update time
		dummyDevice.setManufacturer(null);
		assertThatCode(() -> devicePersister.update(dummyDevice)).isInstanceOf(RuntimeMappingException.class)
				.hasMessage("Non null value expected for relation Device::getManufacturer on object o.c.s.e.m.d.Device@42");
	}
	
	@Test
	void fetchSeparately() {
		EntityPersister<Device, Identifier<Long>> devicePersister = FluentMappings.entityBuilder(Device.class, Identifier.LONG_TYPE)
				.mapKey(Device::getId, StatefulIdentifierAlreadyAssignedIdentifierPolicy.ALREADY_ASSIGNED)
				.map(Device::getName)
				.mapManyToOne(Device::getManufacturer, companyConfiguration).cascading(ALL).fetchSeparately()
				.build(persistenceContext);
		
		DDLDeployer ddlDeployer = new DDLDeployer(persistenceContext);
		ddlDeployer.deployDDL();
		
		// asserting null check at insertion time
		Device dummyDevice = new Device(new PersistableIdentifier<>(42L));
		dummyDevice.setName("UPS");
		Company company = new Company(new PersistableIdentifier<>(1L));
		company.setName("World Company");
		dummyDevice.setManufacturer(company);
		devicePersister.insert(dummyDevice);
		
		Device loadedDevice = devicePersister.select(dummyDevice.getId());
		assertThat(loadedDevice.getManufacturer()).isNotNull();
		assertThat(loadedDevice.getManufacturer().getName()).isEqualTo("World Company");
	}
	
	public static class LiteCompany {
		private final String name;
		
		public LiteCompany(String name) {
			this.name = name;
		}
	}
}
