@Repository
public class AspectPerformanceDataRepository {

    private static final String DATES = "DATES";
    private static final String YEAR = "YEAR";
    private static final String MONTH = "MONTH";
    private static final String DAY = "DAY";

    private final DSLContext dsl;

    public AspectPerformanceDataRepository(DSLContext dsl) {
        this.dsl = dsl;
    }

    public List<AspectPerformanceDataRecord> findByAspectAndYear(long id, int year) {
        final var dataPointDates = createDataPointDatesExpression(id, year);

        var yearField = field(DATES + "." + YEAR);
        var monthField = field(DATES + "." + MONTH);
        var dayField = field(DATES + "." + DAY);

        return dsl
                .with(dataPointDates)
                .select(ASPECT_PERFORMANCE_DATA.asterisk())
                .from(ASPECT_PERFORMANCE_DATA)
                .innerJoin(dataPointDates.asTable(DATES))
                .on(yearField.eq(year()))
                .and(monthField.eq(month()))
                .and(dayField.eq(day()))
                .where(ASPECT_PERFORMANCE_DATA.ASPECT_ID.eq(id))
                .orderBy(ASPECT_PERFORMANCE_DATA.DATE)
                .fetchInto(ASPECT_PERFORMANCE_DATA);
    }

    private CommonTableExpression<Record3<Integer, Integer, Integer>> createDataPointDatesExpression(long id, int year) {
        final var endOfMonthsQuery =
                select(
                        year().as(YEAR),
                        month().as(MONTH),
                        max(day()).as(DAY)
                )
                .from(ASPECT_PERFORMANCE_DATA)
                .where(ASPECT_PERFORMANCE_DATA.ASPECT_ID.eq(id))
                .and(ASPECT_PERFORMANCE_DATA.PERFORMANCE_INDEX.isNotNull())
                .and(year().eq(year))
                .groupBy(year(), month());

        var yearField = endOfMonthsQuery.field(YEAR, Integer.class);
        var monthField = endOfMonthsQuery.field(MONTH, Integer.class);
        var dayField = endOfMonthsQuery.field(DAY, Integer.class);

        return name(DATES)
                .fields(YEAR, MONTH, DAY)
                .as(
                        select(yearField, monthField, dayField)
                        .from(endOfMonthsQuery)
                        .union(createStartOfYearQuery(id, year))
                );
    }

    private SelectConditionStep<Record3<Integer, Integer, Integer>> createStartOfYearQuery(long id, int year) {
        return dsl
                .select(
                        year(),
                        month(),
                        day()
                )
                .from(ASPECT_PERFORMANCE_DATA)
                .where(ASPECT_PERFORMANCE_DATA.ASPECT_ID.eq(id))
                .and(ASPECT_PERFORMANCE_DATA.PERFORMANCE_INDEX.isNotNull())
                .and(ASPECT_PERFORMANCE_DATA.DATE.eq(firstDayOf(year)));
    }

    private Field<Integer> year() {
        return extract(ASPECT_PERFORMANCE_DATA.DATE, DatePart.YEAR);
    }

    private Field<Integer> month() {
        return extract(ASPECT_PERFORMANCE_DATA.DATE, DatePart.MONTH);
    }

    private Field<Integer> day() {
        return extract(ASPECT_PERFORMANCE_DATA.DATE, DatePart.DAY);
    }

    private Timestamp firstDayOf(int year) {
        return Timestamp.valueOf(LocalDateTime.of(year, 1, 1, 0, 0));
    }

}