package de.am_soft.sm_mtg.backend.daos.db.reports.meter_cnt.last_month;

import static de.am_soft.sm_mtg.backend.daos.db.jooq.Tables.CLT_REC;
import static de.am_soft.sm_mtg.backend.daos.db.jooq.Tables.CLT_REC_SRC;
import static de.am_soft.sm_mtg.backend.daos.db.jooq.Tables.COLLECTOR;
import static de.am_soft.sm_mtg.backend.daos.db.jooq.Tables.METER;
import static de.am_soft.sm_mtg.backend.daos.db.jooq.Tables.OMS_REC;
import static de.am_soft.sm_mtg.backend.daos.db.jooq.Tables.READING_COMPANY;
import static de.am_soft.sm_mtg.backend.daos.db.jooq.Tables.REAL_ESTATE;

import java.sql.Timestamp;
import java.time.OffsetDateTime;

import org.jooq.CommonTableExpression;
import org.jooq.DatePart;
import org.jooq.Field;
import org.jooq.Name;
import org.jooq.Record2;
import org.jooq.Record8;
import org.jooq.impl.DSL;
import org.jooq.impl.SQLDataType;

import de.am_soft.sm_mtg.backend.daos.db.jooq.enums.MeterMfctCode;
import de.am_soft.sm_mtg.backend.daos.db.jooq.enums.MeterType;
import de.am_soft.util.backend.Base;
import de.am_soft.util.backend.mgmt.MgResProvider;
import de.am_soft.util.jdbc.jooq.custom_fields.postgres.PgInterval;

public abstract class MclmSummary extends Base
{
	private static abstract class CteDefs
	{
		private static abstract class TimePeriod
		{
			private static final Name					Cte			= DSL.name("time_period");
			private static final Field<OffsetDateTime>	ColStartAt	= DSL.field("start_at",	SQLDataType.OFFSETDATETIME);
			private static final Field<OffsetDateTime>	ColEndAt	= DSL.field("end_at",	SQLDataType.OFFSETDATETIME);
		}

		private static abstract class RecsInTimePeriod
		{
			private static final Name Cte					= DSL.name("recs_in_time_period");
			private static final Name ColRcUserId			= DSL.name("rc_user_id");
			private static final Name ColRealEstateNr		= DSL.name("real_estate_nr");
			private static final Name ColCltMac				= DSL.name("clt_mac");
			private static final Name ColCltRecId			= DSL.name("clt_rec_id");
			private static final Name ColCltRecCapturedAt	= DSL.name("clt_rec_captured_at");
			private static final Name ColMeterMfctCode		= DSL.name("meter_mfct_code");
			private static final Name ColMeterReadingSerial	= DSL.name("meter_reading_serial");
			private static final Name ColMeterType			= DSL.name("meter_type");
		}
	}

	private CommonTableExpression<Record2<OffsetDateTime, OffsetDateTime>> cteTimePeriod()
	{
		Field<Timestamp> nowAsMonth	= DSL.trunc(DSL.now(), DatePart.MONTH);
		Field<Timestamp> startAtTs	= nowAsMonth.sub(new PgInterval("1 month"));
		Field<Timestamp> endAtTs	= nowAsMonth.sub(new PgInterval("0 month"));

		// Values need to be compared to clt_rec.captured_at, which is time zone-aware. At least in
		// Postgres "now()" should be the same already as well, but jOOQ seems to model a timestamp
		// without time zone for that function for some reason.
		Field<OffsetDateTime> startAt	= startAtTs	.cast(SQLDataType.OFFSETDATETIME);
		Field<OffsetDateTime> endAt		= endAtTs	.cast(SQLDataType.OFFSETDATETIME);

		return CteDefs.TimePeriod.Cte
			.fields(CteDefs.TimePeriod.ColStartAt.getName(),
					CteDefs.TimePeriod.ColEndAt.getName())
			.as(DSL.selectFrom(DSL.values(DSL.row(startAt, endAt))));
	}

	private	CommonTableExpression<Record8<Integer, String, String, Long, OffsetDateTime, MeterMfctCode, String, MeterType>>
			cteRecsInTimePeriod()
	{
		CommonTableExpression<Record2<OffsetDateTime, OffsetDateTime>> timePeriod = this.cteTimePeriod();

		return CteDefs.RecsInTimePeriod.Cte
			.fields
			(
				CteDefs.RecsInTimePeriod.ColRcUserId,
				CteDefs.RecsInTimePeriod.ColRealEstateNr,
				CteDefs.RecsInTimePeriod.ColCltMac,
				CteDefs.RecsInTimePeriod.ColCltRecId,
				CteDefs.RecsInTimePeriod.ColCltRecCapturedAt,
				CteDefs.RecsInTimePeriod.ColMeterMfctCode,
				CteDefs.RecsInTimePeriod.ColMeterReadingSerial,
				CteDefs.RecsInTimePeriod.ColMeterType
			)

			.as
			(
				DSL.select
				(
					READING_COMPANY.USER_ID	.as(CteDefs.RecsInTimePeriod.ColRcUserId),
					REAL_ESTATE.NUMBER		.as(CteDefs.RecsInTimePeriod.ColRealEstateNr),
					COLLECTOR.MAC_ADDRESS	.as(CteDefs.RecsInTimePeriod.ColCltMac),
					CLT_REC.ID				.as(CteDefs.RecsInTimePeriod.ColCltRecId),
					CLT_REC.CAPTURED_AT		.as(CteDefs.RecsInTimePeriod.ColCltRecCapturedAt),
					METER.MFCT_CODE			.as(CteDefs.RecsInTimePeriod.ColMeterMfctCode),
					METER.READING_SERIAL	.as(CteDefs.RecsInTimePeriod.ColMeterReadingSerial),
					METER.TYPE				.as(CteDefs.RecsInTimePeriod.ColMeterType)
				)

				.from(timePeriod, CLT_REC)

				.join(CLT_REC_SRC)		.on(CLT_REC_SRC.ID				.eq(CLT_REC.ID))
				.join(COLLECTOR)		.on(CLT_REC_SRC.COLLECTOR		.eq(COLLECTOR.ID))
				.join(REAL_ESTATE)		.on(COLLECTOR.REAL_ESTATE		.eq(REAL_ESTATE.ID))
				.join(READING_COMPANY)	.on(REAL_ESTATE.READING_COMPANY	.eq(READING_COMPANY.ID))
				.join(OMS_REC)			.on(CLT_REC.OMS_REC				.eq(OMS_REC.ID))
				.join(METER)			.on(OMS_REC.METER				.eq(METER.ID))

				.where
				(
					CLT_REC.CAPTURED_AT.between
					(
						timePeriod.field(CteDefs.TimePeriod.ColStartAt),
						timePeriod.field(CteDefs.TimePeriod.ColEndAt)
					)

					.and(REAL_ESTATE.DELETED.isFalse())
					.and(METER.REPLACED_WITH.isNull())
				)
			);
	}
}