package eu.dnetlib.goldoa.service.stats;

import eu.dnetlib.goldoa.domain.Request;
import eu.dnetlib.goldoa.domain.stats.Browse;
import eu.dnetlib.goldoa.domain.stats.DateSeries;
import eu.dnetlib.goldoa.domain.stats.MapData;
import eu.dnetlib.goldoa.domain.stats.Numbers;
import eu.dnetlib.goldoa.domain.stats.Quadruple;
import eu.dnetlib.goldoa.domain.stats.Series;
import eu.dnetlib.goldoa.domain.stats.Triple;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.transaction.annotation.Transactional;

import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by antleb on 11/19/15.
 */
@Transactional(readOnly = true)
public class StatsManagerImpl implements StatsManager {

	public static final String NUMBERS_REQUESTS = "select count(*) from request r where r.status in (" + Request.RequestStatus.APPROVED.getCode() + ", " + Request.RequestStatus.ACCOUNTING_PROCESSING.getCode() + ", " + Request.RequestStatus.ACCOUNTING_PAID.getCode() + ", " + Request.RequestStatus.CONDITIONALLY_APPROVED.getCode() + ", " + Request.RequestStatus.ACCOUNTING_ON_HOLD.getCode() + ")" ;
	public static final String NUMBERS_PAID_REQUESTS = "select count(*) from request r where r.status in (" + Request.RequestStatus.ACCOUNTING_PAID.getCode() + ")" ;
	public static final String NUMBERS_PROJECTS = "select count(distinct project) from request r where r.status in (" + Request.RequestStatus.APPROVED.getCode() + ", " + Request.RequestStatus.ACCOUNTING_PROCESSING.getCode() + ", " + Request.RequestStatus.ACCOUNTING_PAID.getCode() + ", " + Request.RequestStatus.CONDITIONALLY_APPROVED.getCode() + ", " + Request.RequestStatus.ACCOUNTING_ON_HOLD.getCode() + ")";
	public static final String NUMBERS_TOTAL_AMOUNT = "select sum(apc_paid) from request r";
	public static final String NUMBERS_AVERAGE_AMOUNT = "select avg(apc_paid)::INTEGER from request r";
	public static final String NUMBERS_AVERAGE_PER_ARTICLE = "select avg(apc_paid)::INTEGER from request r join publication p on r.publication=p.id where p.type='ARTICLE'";
	public static final String NUMBERS_AVERAGE_PER_MONOGRAPH = "select avg(apc_paid)::INTEGER from request r join publication p on r.publication=p.id where p.type='MONOGRAPH'";
	public static final String NUMBERS_AVERAGE_PER_BOOK_CHAPTER = "select avg(apc_paid)::INTEGER from request r join publication p on r.publication=p.id where p.type='BOOK_CHAPTER'";
	public static final String NUMBERS_PAID_REQUESTS_WITH_DOI = "select count(*) from request r join publication p on r.publication=p.id where p.doi is not null and r.status=" + Request.RequestStatus.ACCOUNTING_PAID.getCode();
	public static final String NUMBERS_APPROVED_REQUESTS_WITH_DOI = "select count(*) from request r join publication p on r.publication=p.id where p.doi is not null and r.status in (" + Request.RequestStatus.APPROVED.getCode() + ", " + Request.RequestStatus.ACCOUNTING_PROCESSING.getCode() + ", " + Request.RequestStatus.ACCOUNTING_PAID.getCode() + ", " + Request.RequestStatus.CONDITIONALLY_APPROVED.getCode() + ", " + Request.RequestStatus.ACCOUNTING_ON_HOLD.getCode() + ", " + Request.RequestStatus.CONDITIONALLY_APPROVED.getCode() + ")";
	public static final String NUMBERS_PAID_ARTICLE_REQUESTS = "select count(*) from request r join publication p on r.publication=p.id where r.status in (" + Request.RequestStatus.ACCOUNTING_PAID.getCode() + ") and p.type='ARTICLE'" ;
	public static final String NUMBERS_PAID_MONOGRAPH_REQUESTS = "select count(*) from request r join publication p on r.publication=p.id where r.status in (" + Request.RequestStatus.ACCOUNTING_PAID.getCode() + ") and p.type='MONOGRAPH'" ;
	public static final String NUMBERS_PAID_BOOK_CHAPTER_REQUESTS = "select count(*) from request r join publication p on r.publication=p.id where r.status in (" + Request.RequestStatus.ACCOUNTING_PAID.getCode() + ") and p.type='BOOK_CHAPTER'" ;

	public static final String SERIES_COUNTRY = "select c.name as category, count(r.id) as count, sum(apc_paid) as apc, avg(apc_paid) as avgapc from request r join organisation o on r.organization=o.id join country c on c.id=o.country %WHERE% group by c.name";
	public static final String SERIES_STATUS = "select r.status as category, count(r.id) as count, sum(apc_paid) as apc, avg(apc_paid) as avgapc from request r %WHERE% group by r.status";
	public static final String SERIES_TYPE = "select p.type as category, count(r.id) as count, sum(apc_paid) as apc, avg(apc_paid) as avgapc from request r join publication p on p.id=r.publication %WHERE% group by p.type";
	public static final String SERIES_ORGANIZATION = "select o.name as category, count(r.id) as count, sum(apc_paid) as apc, avg(apc_paid) as avgapc from request r join organisation o on r.organization=o.id %WHERE% group by o.shortname, o.name";
	public static final String SERIES_PUBLISHER = "select p.name as category, count(r.id) as count, sum(apc_paid) as apc, avg(apc_paid) as avgapc from (select r.id, r.status, r.apc_paid, j.publisher, 'j' from request r join journal j on r.journal=j.id where j.publisher is not null union select r.id, r.status, r.apc_paid, p.id, 'p' from request r join publisher p on r.publisher=p.id) as r join publisher p on r.publisher=p.id %WHERE% group by p.name";

	public static final String BROWSE_SCIENTIFIC_AREA = "select distinct p.scientificarea as id, p.scientificarea as category, count(r.id) as count from request r join project p on r.project=p.id group by p.scientificarea order by category";
	public static final String BROWSE_COUNTRY = "select c.id as id, c.name as category, count(r.id) as count from request r join organisation o on r.organization=o.id join country c on c.id=o.country group by c.name, c.id order by category";
	public static final String BROWSE_ORGANIZATION = "select o.id as id, case when o.shortname is not null then o.shortname else o.name end  as category, count(r.id) as count from request r join organisation o on o.id=r.organization group by o.shortname, o.name, o.id order by category";
	public static final String BROWSE_PUBLISHER = "select p.id as id, p.name as category, count(r.id) as count from (select r.id, r.status, j.publisher, 'j' from request r join journal j on r.journal=j.id where j.publisher is not null union select r.id, r.status, p.id, 'p' from request r join publisher p on r.publisher=p.id) as r join publisher p on r.publisher=p.id group by p.name, p.id order by category";
	public static final String BROWSE_STATUS = "select r.status as id, r.status as category, count(r.id) as count from request r group by status";

	@Autowired
	private DataSource dataSource;

	@Override
	public Numbers getNumbers(Numbers.Category[] categories) {
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		Map<Numbers.Category, Integer> map = new HashMap<Numbers.Category, Integer>();

		for (Numbers.Category category:categories) {
			switch (category) {
				case REQUESTS:
					map.put(Numbers.Category.REQUESTS, jdbcTemplate.queryForObject(NUMBERS_REQUESTS, Integer.class));
					break;
				case PAID_REQUESTS:
					map.put(Numbers.Category.PAID_REQUESTS, jdbcTemplate.queryForObject(NUMBERS_PAID_REQUESTS, Integer.class));
					break;
				case PROJECTS:
					map.put(Numbers.Category.PROJECTS, jdbcTemplate.queryForObject(NUMBERS_PROJECTS, Integer.class));
					break;
				case TOTAL_AMOUNT:
					map.put(Numbers.Category.TOTAL_AMOUNT, jdbcTemplate.queryForObject(NUMBERS_TOTAL_AMOUNT, Integer.class));
					break;
				case AVERAGE_AMOUNT:
					map.put(Numbers.Category.AVERAGE_AMOUNT, jdbcTemplate.queryForObject(NUMBERS_AVERAGE_AMOUNT, Integer.class));
					break;
				case AVERAGE_PER_ARTICLE:
					map.put(Numbers.Category.AVERAGE_PER_ARTICLE, jdbcTemplate.queryForObject(NUMBERS_AVERAGE_PER_ARTICLE, Integer.class));
					break;
				case AVERAGE_PER_MONOGRAPH:
					map.put(Numbers.Category.AVERAGE_PER_MONOGRAPH, jdbcTemplate.queryForObject(NUMBERS_AVERAGE_PER_MONOGRAPH, Integer.class));
					break;
				case AVERAGE_PER_BOOK_CHAPTER:
					map.put(Numbers.Category.AVERAGE_PER_BOOK_CHAPTER, jdbcTemplate.queryForObject(NUMBERS_AVERAGE_PER_BOOK_CHAPTER, Integer.class));
					break;
				case PAID_REQUESTS_WITH_DOI:
					map.put(Numbers.Category.PAID_REQUESTS_WITH_DOI, jdbcTemplate.queryForObject(NUMBERS_PAID_REQUESTS_WITH_DOI, Integer.class));
					break;
				case APPROVED_REQUESTS_WITH_DOI:
					map.put(Numbers.Category.APPROVED_REQUESTS_WITH_DOI, jdbcTemplate.queryForObject(NUMBERS_APPROVED_REQUESTS_WITH_DOI, Integer.class));
					break;
				case PAID_ARTICLE_REQUESTS:
					map.put(Numbers.Category.PAID_ARTICLE_REQUESTS, jdbcTemplate.queryForObject(NUMBERS_PAID_ARTICLE_REQUESTS, Integer.class));
					break;
				case PAID_MONOGRAPH_REQUESTS:
					map.put(Numbers.Category.PAID_MONOGRAPH_REQUESTS, jdbcTemplate.queryForObject(NUMBERS_PAID_MONOGRAPH_REQUESTS, Integer.class));
					break;
				case PAID_BOOK_CHAPTER_REQUESTS:
					map.put(Numbers.Category.PAID_BOOK_CHAPTER_REQUESTS, jdbcTemplate.queryForObject(NUMBERS_PAID_BOOK_CHAPTER_REQUESTS, Integer.class));
					break;
			}
		}

		return new Numbers(map);
	}

	@Override
	public Series getSeries(Series.Category[] categories, Request.RequestStatus[] statuses) {
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		Map<Series.Category, List<Quadruple<String, Integer, Float, Float>>> map = new HashMap<Series.Category, List<Quadruple<String, Integer, Float, Float>>>();

		for (final Series.Category category:categories) {
			String query = null;

			switch (category) {
				case COUNTRY:
					query = SERIES_COUNTRY;
					break;
				case ORGANIZATION:
					query = SERIES_ORGANIZATION;
					break;
				case PUBLISHER:
					query = SERIES_PUBLISHER;
					break;
				case STATUS:
					query = SERIES_STATUS;
					break;
				case PUBLICATION_TYPE:
					query = SERIES_TYPE;
			}

			if (statuses != null) {
				StringBuilder ssb = new StringBuilder();

				for (Request.RequestStatus status:statuses) {
					String clause;
					int code = status.getCode();

					switch (code) {
						case 0:
							clause = " r.status=0";
							break;
						default:
							clause = " (r.status = " + code + ")";
							break;
					}

					ssb.append(ssb.length()==0?clause:" or " + clause);
				}

				query = query.replace("%WHERE%", " where " + ssb.toString());
			} else {
				query = query.replace("%WHERE%", "");
			}

			map.put(category, jdbcTemplate.query(query, new RowMapper<Quadruple<String, Integer, Float, Float>>() {
				@Override
				public Quadruple<String, Integer, Float, Float> mapRow(ResultSet rs, int rowNum) throws SQLException {
					if (category != Series.Category.STATUS)
						return new Quadruple<String, Integer, Float, Float>(rs.getString("category"), rs.getInt("count"), rs.getFloat("apc"), rs.getFloat("avgapc"));
					else
						return new Quadruple<String, Integer, Float, Float>(Request.RequestStatus.forStatus(rs.getInt("category")).getValue(), rs.getInt("count"), rs.getFloat("apc"), rs.getFloat("avgapc"));
				}
			}));
		}

		return new Series(map);
	}

	@Override
	public DateSeries getDateSeries(DateSeries.Over over, boolean cumulative, Date from, Date to, String[] publishers, String[] organizations, String[] countries, String[] scientificAreas) {

		StringBuilder sb = new StringBuilder();
		String dateColumn = null;
		String windowKeyword ;
		List<String> clauses = new ArrayList<String>();

		switch (over) {
			case DAY:
				dateColumn = "date_trunc('day', submissiondate)";
				break;
			case MONTH:
				dateColumn = "date_trunc('month', submissiondate)";
				break;
			case YEAR:
				dateColumn = "date_trunc('year', submissiondate)";
				break;
		}

		if (cumulative)
			windowKeyword = "order";
		else
			windowKeyword = "partition";

		if (from != null) {
			clauses.add(" r.submissiondate >= '" + new SimpleDateFormat("yyyy-MM-dd").format(from) + "' ");
		}
		if (to != null) {
			clauses.add(" r.submissiondate <= '" + new SimpleDateFormat("yyyy-MM-dd").format(to) + "' ");
		}

		if (publishers != null && publishers.length > 0) {
			String clause = "pub.id in (";

			for (int i = 0; i < publishers.length; i++) {
				if (i > 0)
					clause += ", ";

				clause += "'" + publishers[i] + "'";
			}

			clause += ")";

			clauses.add(clause);
		}

		if (organizations != null && organizations.length > 0) {
			String clause = "o.id in (";

			for (int i = 0; i < organizations.length; i++) {
				if (i > 0)
					clause += ", ";

				clause += "'" + organizations[i] + "'";
			}

			clause += ")";

			clauses.add(clause);
		}

		if (countries != null && countries.length > 0) {
			String clause = "c.id in (";

			for (int i = 0; i < countries.length; i++) {
				if (i > 0)
					clause += ", ";

				clause += "'" + countries[i] + "'";
			}

			clause += ")";

			clauses.add(clause);
		}

		if (scientificAreas != null && scientificAreas.length > 0) {
			String clause = "p.scientificarea in (";

			for (int i = 0; i < scientificAreas.length; i++) {
				if (i > 0)
					clause += ", ";

				clause += "'" + scientificAreas[i] + "'";
			}

			clause += ")";

			clauses.add(clause);
		}

		sb.append("select distinct ").append(dateColumn).append(" as date, count(r.id) over (").append(windowKeyword).append(" by ").append(dateColumn).append(") as count, sum(apc_paid) over (").append(windowKeyword).append(" by ").append(dateColumn).append(") as apc ");
		sb.append("from request r left join project p on r.project=p.id left join organisation o on r.organization=o.id left join country c on c.id=o.country left join (select r.id as rid,  p.id, p.name from request r join journal j on r.journal=j.id join publisher p on p.id=j.publisher  union select r.id, p.id, p.name from request r join publisher p on r.publisher=p.id) as pub on pub.rid=r.id");

		if (clauses.size() > 0)
			sb.append(" where ");

		for (int i = 0; i < clauses.size(); i++) {
			if (i > 0)
				sb.append(" and ");

			sb.append(" (").append(clauses.get(i)).append(") ");
		}

		sb.append(" order by ").append(dateColumn);

//		System.out.println(sb.toString());return null;

		return new DateSeries(new JdbcTemplate(dataSource).query(sb.toString(), new RowMapper<Triple<Date, Integer, Float>>() {
			@Override
			public Triple<Date, Integer, Float> mapRow(ResultSet rs, int rowNum) throws SQLException {
				return new Triple<Date, Integer, Float>(rs.getDate("date"), rs.getInt("count"), rs.getFloat("apc"));
			}
		}));
	}

	public static void main(String[] args) {
		System.out.println(new StatsManagerImpl().getDateSeries(DateSeries.Over.DAY, true, null,null,null,null,null,null));
	}

	@Override
	public Browse browse(Browse.Category[] categories) {
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		Map<Browse.Category, List<Triple<String, String, Integer>>> map = new HashMap<Browse.Category, List<Triple<String, String, Integer>>>();

		for (final Browse.Category category:categories) {
			String query = null;

			switch (category) {
				case COUNTRY:
					query = BROWSE_COUNTRY;
					break;
				case ORGANIZATION:
					query = BROWSE_ORGANIZATION;
					break;
				case PUBLISHER:
					query = BROWSE_PUBLISHER;
					break;
				case SCIENTIFIC_AREA:
					query = BROWSE_SCIENTIFIC_AREA;
					break;
				case STATUS:
					query =  BROWSE_STATUS;
					break;
			}

			map.put(category, jdbcTemplate.query(query, new RowMapper<Triple<String, String, Integer>>() {
				@Override
				public Triple<String, String, Integer> mapRow(ResultSet rs, int rowNum) throws SQLException {
					if (category != Browse.Category.STATUS)
						return new Triple<String, String, Integer>(rs.getString("id"), rs.getString("category"), rs.getInt("count"));
					else
						return new Triple<String, String, Integer>(Request.RequestStatus.forStatus(rs.getInt("id")).name(), Request.RequestStatus.forStatus(rs.getInt("category")).getValue(), rs.getInt("count"));
				}
			}));
		}

		return new Browse(map);
	}

	@Override
	public MapData getMapData() {
		return null;
	}
}