package nl.idgis.publisher.admin;

import static nl.idgis.publisher.database.QCategory.category;
import static nl.idgis.publisher.database.QDataSource.dataSource;
import static nl.idgis.publisher.database.QDataset.dataset;
import static nl.idgis.publisher.database.QSourceDataset.sourceDataset;
import static nl.idgis.publisher.database.QSourceDatasetVersion.sourceDatasetVersion;

import com.mysema.query.sql.SQLSubQuery;

import akka.actor.ActorRef;
import akka.actor.Props;

import nl.idgis.publisher.database.AsyncSQLQuery;
import nl.idgis.publisher.database.QSourceDatasetVersion;
import nl.idgis.publisher.database.messages.QSourceDatasetInfo;
import nl.idgis.publisher.database.messages.SourceDatasetInfo;

import nl.idgis.publisher.domain.EntityType;
import nl.idgis.publisher.domain.query.ListSourceDatasets;
import nl.idgis.publisher.domain.web.EntityRef;
import nl.idgis.publisher.domain.web.SourceDataset;
import nl.idgis.publisher.domain.web.SourceDatasetStats;
import nl.idgis.publisher.domain.response.Page;

public class DataSources extends AbstractAdmin {
	
	private final QSourceDatasetVersion sourceDatasetVersionSub = new QSourceDatasetVersion("source_dataset_version_sub");

	public DataSources(ActorRef database) {
		super(database);
	}
	
	public static Props props(ActorRef database) {
		return Props.create(DataSources.class, database);
	}	

	@Override
	protected void queries() {
		query(ListSourceDatasets.class, msg -> {			
			return db.transactional(tx -> {
				AsyncSQLQuery baseQuery = tx.query().from(sourceDataset)
					.join (sourceDatasetVersion).on(sourceDatasetVersion.sourceDatasetId.eq(sourceDataset.id)
						.and(new SQLSubQuery().from(sourceDatasetVersionSub)
							.where(sourceDatasetVersionSub.sourceDatasetId.eq(sourceDatasetVersion.sourceDatasetId)
								.and(sourceDatasetVersionSub.id.gt(sourceDatasetVersion.id)))
							.notExists()))
					.join (dataSource).on(dataSource.id.eq(sourceDataset.dataSourceId))
					.join (category).on(sourceDatasetVersion.categoryId.eq(category.id));
				
				String categoryId = msg.categoryId();
				if(categoryId != null) {				
					baseQuery.where(category.identification.eq(categoryId));
				}
				
				String dataSourceId = msg.dataSourceId();
				if(dataSourceId != null) {				
					baseQuery.where(dataSource.identification.eq(dataSourceId));
				}
				
				String searchStr = msg.getSearchString();
				if (!(searchStr == null || searchStr.isEmpty())){
					baseQuery.where(sourceDatasetVersion.name.containsIgnoreCase(searchStr)); 				
				}
					
				AsyncSQLQuery listQuery = baseQuery.clone()					
					.leftJoin(dataset).on(dataset.sourceDatasetId.eq(sourceDataset.id))					
					.orderBy(sourceDatasetVersion.name.asc());
				
				Long page = msg.getPage();
				singlePage(listQuery, page);
				
				return f
					.collect(listQuery					
						.groupBy(sourceDataset.identification).groupBy(sourceDatasetVersion.name)
						.groupBy(dataSource.identification).groupBy(dataSource.name)
						.groupBy(category.identification).groupBy(category.name)						
						.list(new QSourceDatasetInfo(sourceDataset.identification, sourceDatasetVersion.name, 
							dataSource.identification, dataSource.name,
							category.identification,category.name,
							dataset.count())))
					.collect(baseQuery.count()).thenApply((list, count) -> {
						Page.Builder<SourceDatasetStats> pageBuilder = new Page.Builder<> ();
						
						for(SourceDatasetInfo sourceDatasetInfo : list) {
							SourceDataset sourceDataset = new SourceDataset (
								sourceDatasetInfo.getId(), 
								sourceDatasetInfo.getName(),
								new EntityRef (EntityType.CATEGORY, sourceDatasetInfo.getCategoryId(),sourceDatasetInfo.getCategoryName()),
								new EntityRef (EntityType.DATA_SOURCE, sourceDatasetInfo.getDataSourceId(), sourceDatasetInfo.getDataSourceName())
							);
							
							pageBuilder.add (new SourceDatasetStats (sourceDataset, sourceDatasetInfo.getCount()));
						}
						
						addPageInfo(pageBuilder, page, count);
						
						return pageBuilder.build();
					});
			});
		});
	}
}

@@ -1479,21 +1479,18 @@
 msg -%3E %7B
-%09%09%09
 %0D%0A
+
 %09%09%09retur
@@ -2728,61 +2728,8 @@
 id))
-%09%09%09%09%09%0D%0A%09%09%09%09%09.orderBy(sourceDatasetVersion.name.asc())
 ;%0D%0A%09
@@ -3062,20 +3062,72 @@
 .name)%09%09
+%0D%0A
 %09%09%09%09
+%09%09.orderBy(sourceDatasetVersion.name.trim().asc())
 %0D%0A%09%09%09%09%09%09
@@ -4159,21 +4159,22 @@
 %09%09%09%7D);%0D%0A
-
 %09%09%7D);
+%09
 %0D%0A%09%7D%0D%0A%7D%0D

trim added to order by