package at.procon.dip.domain.time.leitstand.source;

import at.procon.dip.domain.time.config.TimeDomainProperties;
import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.time.OffsetDateTime;
import java.time.ZoneId;
import java.util.List;
import java.util.Map;

import at.procon.dip.runtime.condition.ConditionalOnRuntimeMode;
import at.procon.dip.runtime.config.RuntimeMode;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Component;

@Component
@ConditionalOnRuntimeMode(RuntimeMode.NEW)
@ConditionalOnProperty(prefix = "dip.time.leitstand", name = "enabled", havingValue = "true")
public class JdbcLeitstandTimeSourceClient implements LeitstandTimeSourceClient {

    private final TimeDomainProperties properties;
    private final NamedParameterJdbcTemplate jdbcTemplate;

    public JdbcLeitstandTimeSourceClient(TimeDomainProperties properties,
                                         @Qualifier("leitstandTimeNamedParameterJdbcTemplate") NamedParameterJdbcTemplate jdbcTemplate) {
        this.properties = properties;
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public List<LeitstandSourceRows.ActivityTypeRow> fetchActivityTypes() {
        return jdbcTemplate.query("select ID, L_CODE, BEZ from E_90735 order by ID", Map.of(),
                (rs, n) -> new LeitstandSourceRows.ActivityTypeRow(intVal(rs, "ID"), trim(rs.getString("L_CODE")), trim(rs.getString("BEZ"))));
    }

    @Override
    public List<LeitstandSourceRows.PersonRow> fetchPersons(String watermark) {
        return queryByWatermark("E_10009", "select DBK, DBK_UID, UPD, UPD_ANZ, UPD_UID, STATUS, PERS_NR, NAME, VORNAME, REKEY01, ANREDE, TITEL, STRASSE, PLZ, ORT, LANDCODE, TEL_NR, FAX_NR, E_MAIL, MCL_COSTPH, N_REKEY02 from E_10009", watermark,
                (rs, n) -> new LeitstandSourceRows.PersonRow(trim(rs.getString("DBK")), trim(rs.getString("DBK_UID")), trim(rs.getString("UPD")), intVal(rs, "UPD_ANZ"), trim(rs.getString("UPD_UID")), intVal(rs, "STATUS"), intVal(rs, "PERS_NR"), trim(rs.getString("NAME")), trim(rs.getString("VORNAME")), trim(rs.getString("REKEY01")), trim(rs.getString("ANREDE")), trim(rs.getString("TITEL")), trim(rs.getString("STRASSE")), trim(rs.getString("PLZ")), trim(rs.getString("ORT")), trim(rs.getString("LANDCODE")), trim(rs.getString("TEL_NR")), trim(rs.getString("FAX_NR")), trim(rs.getString("E_MAIL")), dec(rs, "MCL_COSTPH"), trim(rs.getString("N_REKEY02"))));
    }

    @Override
    public List<LeitstandSourceRows.OrganizationRow> fetchOrganizations(String watermark) {
        return queryByWatermark("E_10072", "select DBK, DBK_UID, UPD, UPD_ANZ, UPD_UID, STATUS, BETREU, NAME, STRASSE, PLZ, ORT, TEL_NR, FAX_NR, E_MAIL, ORG_NR, org_kurz from E_10072", watermark,
                (rs, n) -> new LeitstandSourceRows.OrganizationRow(trim(rs.getString("DBK")), trim(rs.getString("DBK_UID")), trim(rs.getString("UPD")), intVal(rs, "UPD_ANZ"), trim(rs.getString("UPD_UID")), intVal(rs, "STATUS"), trim(rs.getString("BETREU")), trim(rs.getString("NAME")), trim(rs.getString("STRASSE")), trim(rs.getString("PLZ")), trim(rs.getString("ORT")), trim(rs.getString("TEL_NR")), trim(rs.getString("FAX_NR")), trim(rs.getString("E_MAIL")), intVal(rs, "ORG_NR"), trim(rs.getString("org_kurz"))));
    }

    @Override
    public List<LeitstandSourceRows.ContractRow> fetchContracts(String watermark) {
        return queryByWatermark("E_90711", "select DBK, DBK_UID, UPD, UPD_ANZ, UPD_UID, STATUS, REKEY01, LFDNR, NAME, IREF, EREF, MCL_DESC, MCL_FROM, MCL_TO from E_90711", watermark,
                (rs, n) -> new LeitstandSourceRows.ContractRow(trim(rs.getString("DBK")), trim(rs.getString("DBK_UID")), trim(rs.getString("UPD")), intVal(rs, "UPD_ANZ"), trim(rs.getString("UPD_UID")), intVal(rs, "STATUS"), trim(rs.getString("REKEY01")), intVal(rs, "LFDNR"), trim(rs.getString("NAME")), trim(rs.getString("IREF")), trim(rs.getString("EREF")), trim(rs.getString("MCL_DESC")), ts(rs, "MCL_FROM"), ts(rs, "MCL_TO")));
    }

    @Override
    public List<LeitstandSourceRows.ContractPositionRow> fetchContractPositions(String watermark) {
        return queryByWatermark("E_90712", "select DBK, DBK_UID, UPD, UPD_ANZ, UPD_UID, STATUS, REKEY01, LFDNR, PROJECTNAME, VK_PREIS, EK_PREIS, MCL_DESC, MCL_FROM, MCL_TO, NAME, IREF, EREF from E_90712", watermark,
                (rs, n) -> new LeitstandSourceRows.ContractPositionRow(trim(rs.getString("DBK")), trim(rs.getString("DBK_UID")), trim(rs.getString("UPD")), intVal(rs, "UPD_ANZ"), trim(rs.getString("UPD_UID")), intVal(rs, "STATUS"), trim(rs.getString("REKEY01")), intVal(rs, "LFDNR"), trim(rs.getString("PROJECTNAME")), dec(rs, "VK_PREIS"), dec(rs, "EK_PREIS"), trim(rs.getString("MCL_DESC")), ts(rs, "MCL_FROM"), ts(rs, "MCL_TO"), trim(rs.getString("NAME")), trim(rs.getString("IREF")), trim(rs.getString("EREF"))));
    }

    @Override
    public List<LeitstandSourceRows.CostUnitRow> fetchCostUnits(String watermark) {
        return queryByWatermark("E_90705", "select DBK, DBK_UID, UPD, UPD_ANZ, UPD_UID, STATUS, N_REKEY01, N_REKEY02, N_REKEY03, N_REKEY04, N_REKEY05, N_REKEY06, PROJECT_NAME, PROJECT_ID, PROJECT_TASK, MCL_ID, MCL_NAME, MCL_DESC, MCL_FROM, MCL_TO, MCL_EFFORT_PLAN from E_90705", watermark,
                (rs, n) -> new LeitstandSourceRows.CostUnitRow(trim(rs.getString("DBK")), trim(rs.getString("DBK_UID")), trim(rs.getString("UPD")), intVal(rs, "UPD_ANZ"), trim(rs.getString("UPD_UID")), intVal(rs, "STATUS"), trim(rs.getString("N_REKEY01")), trim(rs.getString("N_REKEY02")), trim(rs.getString("N_REKEY03")), trim(rs.getString("N_REKEY04")), trim(rs.getString("N_REKEY05")), trim(rs.getString("N_REKEY06")), trim(rs.getString("PROJECT_NAME")), intVal(rs, "PROJECT_ID"), intVal(rs, "PROJECT_TASK"), trim(rs.getString("MCL_ID")), trim(rs.getString("MCL_NAME")), trim(rs.getString("MCL_DESC")), ts(rs, "MCL_FROM"), ts(rs, "MCL_TO"), dec(rs, "MCL_EFFORT_PLAN")));
    }

    @Override
    public List<LeitstandSourceRows.TaskRow> fetchTasks(String watermark) {
        return queryByWatermark("E_90708", "select DBK, DBK_UID, UPD, UPD_ANZ, UPD_UID, STATUS, REKEY01, REKEY02, REKEY03, N_REKEY04, LFDNR, TASK_TYPE, MCL_ID, MCL_NAME, MCL_DESC, MCL_FROM, MCL_TO, MCL_EFFORT_PLAN, MCL_LASTWORK, MCL_COMPLETE, MCL_DONE_DATE, MCL_REMARK, N_RID, E_90702_RID, BETRAG, CR_DATE, E_90700_RID, E_90699_RID, ANZAHL from E_90708", watermark,
                (rs, n) -> new LeitstandSourceRows.TaskRow(trim(rs.getString("DBK")), trim(rs.getString("DBK_UID")), trim(rs.getString("UPD")), intVal(rs, "UPD_ANZ"), trim(rs.getString("UPD_UID")), intVal(rs, "STATUS"), trim(rs.getString("REKEY01")), trim(rs.getString("REKEY02")), trim(rs.getString("REKEY03")), trim(rs.getString("N_REKEY04")), intVal(rs, "LFDNR"), trim(rs.getString("TASK_TYPE")), trim(rs.getString("MCL_ID")), trim(rs.getString("MCL_NAME")), trim(rs.getString("MCL_DESC")), ts(rs, "MCL_FROM"), ts(rs, "MCL_TO"), dec(rs, "MCL_EFFORT_PLAN"), ts(rs, "MCL_LASTWORK"), intVal(rs, "MCL_COMPLETE"), ts(rs, "MCL_DONE_DATE"), trim(rs.getString("MCL_REMARK")), intVal(rs, "N_RID"), intVal(rs, "E_90702_RID"), dec(rs, "BETRAG"), ts(rs, "CR_DATE"), intVal(rs, "E_90700_RID"), intVal(rs, "E_90699_RID"), intVal(rs, "ANZAHL")));
    }

    @Override
    public List<LeitstandSourceRows.PersonTaskAssignmentRow> fetchPersonTaskAssignments(String watermark) {
        return queryByWatermark("E_90709", "select DBK, DBK_UID, UPD, UPD_ANZ, UPD_UID, STATUS, REKEY01, REKEY02, REKEY03, RTYPE, MCL_LASTWORK, MCL_EFFORT_PLAN from E_90709", watermark,
                (rs, n) -> new LeitstandSourceRows.PersonTaskAssignmentRow(trim(rs.getString("DBK")), trim(rs.getString("DBK_UID")), trim(rs.getString("UPD")), intVal(rs, "UPD_ANZ"), trim(rs.getString("UPD_UID")), intVal(rs, "STATUS"), trim(rs.getString("REKEY01")), trim(rs.getString("REKEY02")), trim(rs.getString("REKEY03")), trim(rs.getString("RTYPE")), ts(rs, "MCL_LASTWORK"), dec(rs, "MCL_EFFORT_PLAN")));
    }

    @Override
    public List<LeitstandSourceRows.TimeRecordingRow> fetchTimeRecordings(String watermark) {
        return queryByWatermark("E_90716", "select DBK, DBK_UID, UPD, UPD_ANZ, UPD_UID, STATUS, REKEY01, LFDNR, RECORD_TYPE, MCL_ID, MCL_DESC, MCL_FROM, MCL_TO, MCL_EFFORT, MCL_REMARK, MCL_URL, RID1 from E_90716", watermark,
                (rs, n) -> new LeitstandSourceRows.TimeRecordingRow(trim(rs.getString("DBK")), trim(rs.getString("DBK_UID")), trim(rs.getString("UPD")), intVal(rs, "UPD_ANZ"), trim(rs.getString("UPD_UID")), intVal(rs, "STATUS"), trim(rs.getString("REKEY01")), intVal(rs, "LFDNR"), trim(rs.getString("RECORD_TYPE")), trim(rs.getString("MCL_ID")), trim(rs.getString("MCL_DESC")), ts(rs, "MCL_FROM"), ts(rs, "MCL_TO"), dec(rs, "MCL_EFFORT"), trim(rs.getString("MCL_REMARK")), trim(rs.getString("MCL_URL")), intVal(rs, "RID1")));
    }

    @Override
    public List<LeitstandSourceRows.TimeRecordingAssignmentRow> fetchTimeRecordingAssignments(String watermark) {
        return queryByWatermark("E_90710", "select DBK, DBK_UID, UPD, UPD_ANZ, UPD_UID, STATUS, REKEY01, REKEY02, MCL_DESC, MCL_EFFORT, MCL_REMARK from E_90710", watermark,
                (rs, n) -> new LeitstandSourceRows.TimeRecordingAssignmentRow(trim(rs.getString("DBK")), trim(rs.getString("DBK_UID")), trim(rs.getString("UPD")), intVal(rs, "UPD_ANZ"), trim(rs.getString("UPD_UID")), intVal(rs, "STATUS"), trim(rs.getString("REKEY01")), trim(rs.getString("REKEY02")), trim(rs.getString("MCL_DESC")), dec(rs, "MCL_EFFORT"), trim(rs.getString("MCL_REMARK"))));
    }

    private <T> List<T> queryByWatermark(String table, String baseSelect, String watermark, RowMapper<T> mapper) {
        StringBuilder sql = new StringBuilder(baseSelect);
        Map<String, ?> params = Map.of();
        if (watermark != null && !watermark.isBlank() && properties.getLeitstand().isIncrementalEnabled()) {
            sql.append(" where UPD > :watermark");
            params = Map.of("watermark", watermark);
        }
        sql.append(" order by ");
        if (!"E_90735".equals(table)) sql.append("UPD asc, ");
        sql.append("DBK asc");
        return jdbcTemplate.query(sql.toString(), params, mapper);
    }

    private Integer intVal(ResultSet rs, String name) throws SQLException { Object v = rs.getObject(name); return v == null ? null : ((Number) v).intValue(); }
    private BigDecimal dec(ResultSet rs, String name) throws SQLException { Object v = rs.getObject(name); if (v == null) return null; return v instanceof BigDecimal bd ? bd : new BigDecimal(v.toString()); }
    private OffsetDateTime ts(ResultSet rs, String name) throws SQLException { Timestamp ts = rs.getTimestamp(name); return ts == null ? null : ts.toInstant().atZone(ZoneId.systemDefault()).toOffsetDateTime(); }
    private String trim(String value) { return value == null ? null : value.trim(); }
}
