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.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.READING_COMPANY;
import static de.am_soft.sm_mtg.backend.daos.db.jooq.Tables.REAL_ESTATE;

import java.time.OffsetDateTime;

import org.jooq.Field;
import org.jooq.Name;
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;

/**
 * Helper to manage names and fields used in queries.
 * <p>
 * Many things officially available in the DB-schema, like tables and their columns, views etc., are
 * used by jOOQ to create helper classes to not need to work with string-names. But queries themself
 * can become quite complex as well, e.g. because of using Common Table Expressions, which share a
 * lot of the concept of tables and their columns, but are unknown to jOOQ and its code-generator.
 * Working with those CTEs is easier when having names and fields etc. available like with other
 * tables and columns managed by jOOQ, so this class is one attempt to manage those known things in
 * a similar way.
 * </p>
 * <p>
 * This is better than using plain string names and search&replace in case of renames etc., because
 * jOOQ needs to know about things like data types when actually using CTEs in a query in many cases
 * anyway. So providing those details manually right from the start make code easier to read in the
 * actual query in the end and provides one place onyl where to change names, data types etc. The
 * only downside currently seems to be that this needs to be done manually and jOOQ can't generate
 * things. But accessing names and fields in the end can at least be made similar to what jOOQ
 * provides otherwise, though not the same. Reasons are that jOOQ implementes special interfaces
 * and overrides classes, which are internally to jOOQ mostly. Additionally, many of the implemented
 * behaviour of jOOQ isn't needed in most cases anyway, managing names and data types is enough in
 * many cases already.
 * </p>
 * <p>
 * OTOH, jOOQ would be able to assist using its code generator in case implementation of the CTEs is
 * done using e.g. views within the schema. But CTEs are query-private in the end and those change a
 * lot, which in case of views would often mean changing the schema by providing updates etc. This
 * sounds like unnecessary maintenance when dealing with those things entirely in Java is far easier
 * and one only needs to maintain some fields etc. manually.
 * </p>
 * <p>
 * Asked for experiences and opinions on the mailing list as well to get an idea how others deal
 * with this kind of problem. Especially things like naming conventions are of interest, because
 * many names are those for CTEs only, but those names can be reused e.g. for temporary tables as
 * well. So prefixes referring to CTEs only don't feel right etc.
 * </p>
 * @see <a href="https://groups.google.com/d/msg/jooq-user/7wPkNIfUviM/8r4nQluYAgAJ">How to handle names etc. for Common Table Expressions?</a>
 */
class MclmSchema
{
	static class TimePeriod
	{
		/**
		 * Private CTOR for internal use only.
		 */
		private TimePeriod()
		{

		}

		final Name					NAME			= DSL.name ("time_period");
		final Field<OffsetDateTime>	COL_START_AT	= DSL.field("start_at",	SQLDataType.OFFSETDATETIME);
		final Field<OffsetDateTime>	COL_END_AT		= DSL.field("end_at",	SQLDataType.OFFSETDATETIME);
	}

	static class RecsInTimePeriod
	{
		/**
		 * Private CTOR for internal use only.
		 */
		private RecsInTimePeriod()
		{

		}

		final Name					NAME						= DSL.name ("recs_in_time_period");
		final Field<Integer>		COL_RC_USER_ID				= DSL.field("rc_user_id",			READING_COMPANY.USER_ID.getDataType());
		final Field<String>			COL_REAL_ESTATE_NR			= DSL.field("real_estate_nr",		REAL_ESTATE.NUMBER.getDataType());
		final Field<String>			COL_CLT_MAC					= DSL.field("clt_mac",				COLLECTOR.MAC_ADDRESS.getDataType());
		final Field<Long>			COL_CLT_REC_ID				= DSL.field("clt_rec_id",			CLT_REC.ID.getDataType());
		final Field<OffsetDateTime>	COL_CLT_REC_CAPTURED_AT		= DSL.field("clt_rec_captured_at",	CLT_REC.CAPTURED_AT.getDataType());
		final Field<MeterMfctCode>	COL_METER_MFCT_CODE			= DSL.field("meter_mfct_code",		METER.MFCT_CODE.getDataType());
		final Field<String>			COL_METER_READING_SERIAL	= DSL.field("meter_reading_serial",	METER.READING_SERIAL.getDataType());
		final Field<MeterType>		COL_METER_TYPE				= DSL.field("meter_type",			METER.TYPE.getDataType());
	}

	static class ReAndCltPerMeterLid
	{
		/**
		 * Private CTOR for internal use only.
		 */
		private ReAndCltPerMeterLid()
		{

		}

		final Name					NAME							= DSL.name ("re_and_clt_per_meter_lid");
		final Field<MeterMfctCode>	COL_METER_MFCT_CODE				= DSL.field("meter_mfct_code",				METER.MFCT_CODE.getDataType());
		final Field<String>			COL_METER_READING_SERIAL		= DSL.field("meter_reading_serial",			METER.READING_SERIAL.getDataType());
		final Field<MeterType>		COL_METER_TYPE					= DSL.field("meter_type",					METER.TYPE.getDataType());
		final Field<String>			COL_REAL_ESTATE_NR				= DSL.field("real_estate_nr",				REAL_ESTATE.NUMBER.getDataType());
		final Field<String>			COL_CLT_MAC						= DSL.field("clt_mac",						COLLECTOR.MAC_ADDRESS.getDataType());
		final Field<Integer>		COL_METER_LID_CNT				= DSL.field("meter_lid_cnt",				DSL.count().getDataType());
		final Field<OffsetDateTime>	COL_METER_LID_LAST_CAPTURED_AT	= DSL.field("meter_lid_last_captured_at",	CLT_REC.CAPTURED_AT.getDataType());
	}

	static class MostLikelyReAndCltPerMeterLid
	{
		/**
		 * Private CTOR for internal use only.
		 */
		private MostLikelyReAndCltPerMeterLid()
		{

		}

		final Name					NAME						= DSL.name ("most_likely_re_and_clt_per_meter_lid");
		final Field<MeterMfctCode>	COL_METER_MFCT_CODE			= DSL.field("meter_mfct_code",		METER.MFCT_CODE.getDataType());
		final Field<String>			COL_METER_READING_SERIAL	= DSL.field("meter_reading_serial",	METER.READING_SERIAL.getDataType());
		final Field<MeterType>		COL_METER_TYPE				= DSL.field("meter_type",			METER.TYPE.getDataType());
		final Field<String>			COL_REAL_ESTATE_NR			= DSL.field("real_estate_nr",		REAL_ESTATE.NUMBER.getDataType());
		final Field<String>			COL_CLT_MAC					= DSL.field("clt_mac",				COLLECTOR.MAC_ADDRESS.getDataType());
	}

	static class RcPerMeterLid
	{
		/**
		 * Private CTOR for internal use only.
		 */
		private RcPerMeterLid()
		{

		}

		final Name					NAME						= DSL.name ("rc_per_meter_lid");
		final Field<MeterMfctCode>	COL_METER_MFCT_CODE			= DSL.field("meter_mfct_code",		METER.MFCT_CODE.getDataType());
		final Field<String>			COL_METER_READING_SERIAL	= DSL.field("meter_reading_serial",	METER.READING_SERIAL.getDataType());
		final Field<MeterType>		COL_METER_TYPE				= DSL.field("meter_type",			METER.TYPE.getDataType());
		final Field<Integer>		COL_RC_USER_ID				= DSL.field("rc_user_id",			READING_COMPANY.USER_ID.getDataType());
	}

	static class SummaryWoTimePeriod
	{
		/**
		 * Private CTOR for internal use only.
		 */
		private SummaryWoTimePeriod()
		{

		}

		final Name				NAME				= DSL.name ("summary_wo_time_period");
		final Field<Integer>	COL_RC_USER_ID		= DSL.field("rc_user_id",		READING_COMPANY.USER_ID.getDataType());
		final Field<String>		COL_REAL_ESTATE_NR	= DSL.field("real_estate_nr",	REAL_ESTATE.NUMBER.getDataType());
		final Field<String>		COL_CLT_MAC			= DSL.field("clt_mac",			COLLECTOR.MAC_ADDRESS.getDataType());
		final Field<Integer>	COL_METER_CNT		= DSL.field("meter_cnt",		DSL.count().getDataType());
	}

	static class SummaryWithTimePeriod
	{
		/**
		 * Private CTOR for internal use only.
		 */
		private SummaryWithTimePeriod()
		{

		}

		final Name					NAME				= DSL.name ("summary_with_time_period");
		final Field<Integer>		COL_RC_USER_ID		= DSL.field("rc_user_id",			READING_COMPANY.USER_ID.getDataType());
		final Field<String>			COL_REAL_ESTATE_NR	= DSL.field("real_estate_nr",		REAL_ESTATE.NUMBER.getDataType());
		final Field<String>			COL_CLT_MAC			= DSL.field("clt_mac",				COLLECTOR.MAC_ADDRESS.getDataType());
		final Field<Integer>		COL_METER_CNT		= DSL.field("meter_cnt",			DSL.count().getDataType());
		final Field<OffsetDateTime>	COL_TP_START_AT		= DSL.field("time_period_start_at",	CLT_REC.CAPTURED_AT.getDataType());
		final Field<OffsetDateTime>	COL_TP_END_AT		= DSL.field("time_period_end_at",	CLT_REC.CAPTURED_AT.getDataType());
	}

	static class SummedWithTimePeriod
	{
		/**
		 * Private CTOR for internal use only.
		 */
		private SummedWithTimePeriod()
		{

		}

		final Name					NAME				= DSL.name ("summary_with_time_period");
		final Field<Integer>		COL_RC_USER_ID		= DSL.field("rc_user_id",			READING_COMPANY.USER_ID.getDataType());
		final Field<Integer>		COL_REAL_ESTATE_CNT	= DSL.field("real_estate_cnt",		DSL.count().getDataType());
		final Field<Integer>		COL_CLT_CNT			= DSL.field("clt_cnt",				DSL.count().getDataType());
		final Field<Integer>		COL_METER_CNT		= DSL.field("meter_cnt",			DSL.count().getDataType());
		final Field<OffsetDateTime>	COL_TP_START_AT		= DSL.field("time_period_start_at",	CLT_REC.CAPTURED_AT.getDataType());
		final Field<OffsetDateTime>	COL_TP_END_AT		= DSL.field("time_period_end_at",	CLT_REC.CAPTURED_AT.getDataType());
	}

	static final TimePeriod						TIME_PERIOD								= new TimePeriod();
	static final RecsInTimePeriod				RECS_IN_TIME_PERIOD						= new RecsInTimePeriod();
	static final ReAndCltPerMeterLid			RE_AND_CLT_PER_METER_LID				= new ReAndCltPerMeterLid();
	static final MostLikelyReAndCltPerMeterLid	MOST_LIKELY_RE_AND_CLT_PER_METER_LID	= new MostLikelyReAndCltPerMeterLid();
	static final RcPerMeterLid					RC_PER_METER_LID						= new RcPerMeterLid();
	static final SummaryWoTimePeriod			SUMMARY_WO_TIME_PERIOD					= new SummaryWoTimePeriod();
	static final SummaryWithTimePeriod			SUMMARY_WITH_TIME_PERIOD				= new SummaryWithTimePeriod();
	static final SummedWithTimePeriod			SUMMED_WITH_TIME_PERIOD					= new SummedWithTimePeriod();
}