package com.tsystems.diwo.business.queries;


import com.fasterxml.jackson.core.JsonProcessingException;

import com.tsystems.common.InwoLogger;
import com.tsystems.common.model.db.interfaces.IInwoDBCall;

import java.math.BigDecimal;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import oracle.jbo.JboException;

public class VergleichsansichtMieteToJsonDbFunctions extends VergleichsansichtBaseDbFunctions {

    private static final InwoLogger LOG = InwoLogger.createInwoLogger( VergleichsansichtMieteToJsonDbFunctions.class );

    /* ============================================================
       ÖFFENTLICHE EINSTIEGE (Root: "stammakte" -> vorgaenge[])
       ============================================================ */

    public static String buildJsonAktuellerVorgang ( IInwoDBCall dbCall, Integer sakId, Integer lfdnr,
                                                     Integer konnr ) throws SQLException, JsonProcessingException {
        Map<String, Object> stammakte = fetchStammakte( dbCall, sakId );
        if ( stammakte == null ) {
            stammakte = new LinkedHashMap<>();
        }

        Map<String, Object> stammvorgang = fetchStammvorgang( dbCall, sakId, lfdnr );
        if ( stammvorgang == null ) {
            stammvorgang = new LinkedHashMap<>();
        }

        Date eingang = fetchEingangsdatum( dbCall, sakId, lfdnr );

        // Hole bez und wognr aus t_akten für fetchRecht
        Map<String, Integer> aktenData = fetchBezWognrFromAkten( dbCall, sakId );
        if ( aktenData == null || aktenData.get( "bez" ) == null || aktenData.get( "wognr" ) == null ) {
            throw new JboException( "Fehler: Konnte BEZ/WOGNR für SAK_ID " + sakId + " nicht ermitteln" );
        }

        String recht = fetchRecht( dbCall, aktenData.get( "bez" ), aktenData.get( "wognr" ), lfdnr );

        List<Map<String, Object>> zeitraeume =
            fetchZeitraeumeUndDetails( dbCall, aktenData.get( "bez" ), aktenData.get( "wognr" ), lfdnr, eingang, recht,
                                       /*onlyKonnr*/konnr );
        if ( zeitraeume == null ) {
            zeitraeume = new ArrayList<>();
        }

        Map<String, Object> vorgangObj = new LinkedHashMap<>();
        vorgangObj.put( "stammvorgang", stammvorgang );
        vorgangObj.put( "zeitraeume", zeitraeume );

        List<Map<String, Object>> vorgaenge = new ArrayList<>();
        vorgaenge.add( vorgangObj );
        stammakte.put( "vorgaenge", vorgaenge );

        String result = om.writeValueAsString( Collections.singletonMap( "stammakte", stammakte ) );
        LOG.finest( "Resulting JSON: " + result );

        // Close database call at the very end after all queries are completed
        dbCall.closeQuery();

        return result;
    }

    /** Alle anderen Vorgänge (≠ lfdnr) + alle deren Zeiträume -> {"stammakte":{..., "vorgaenge":[{...}, ...]}} */
    public static String buildJsonAndereVorgaenge ( IInwoDBCall dbCall, Integer sakId,
                                                    Integer lfdnr ) throws SQLException, JsonProcessingException {
        Map<String, Object> stammakte = fetchStammakte( dbCall, sakId );
        if ( stammakte == null ) {
            stammakte = new LinkedHashMap<>();
        }

        List<Integer> andere = fetchAndereLfdnr( dbCall, sakId, lfdnr );
        List<Map<String, Object>> vorgaenge = new ArrayList<>();

        // Hole bez und wognr aus t_akten für fetchRecht
        Map<String, Integer> aktenData = fetchBezWognrFromAkten( dbCall, sakId );
        if ( aktenData == null || aktenData.get( "bez" ) == null || aktenData.get( "wognr" ) == null ) {
            throw new JboException( "Fehler: Konnte BEZ/WOGNR für SAK_ID " + sakId + " nicht ermitteln" );
        }

        for ( Integer otherLfdnr : andere ) {
            Map<String, Object> stammvorgang = fetchStammvorgang( dbCall, sakId, otherLfdnr );
            if ( stammvorgang == null ) {
                stammvorgang = new LinkedHashMap<>();
            }

            Date eingang = fetchEingangsdatum( dbCall, sakId, otherLfdnr );
            String recht = fetchRecht( dbCall, aktenData.get( "bez" ), aktenData.get( "wognr" ), otherLfdnr );

            List<Map<String, Object>> zeitraeume =
                fetchZeitraeumeUndDetails( dbCall, aktenData.get( "bez" ), aktenData.get( "wognr" ), otherLfdnr,
                                           eingang, recht, /*onlyKonnr*/null );
            if ( zeitraeume == null ) {
                zeitraeume = new ArrayList<>();
            }

            Map<String, Object> vorgangObj = new LinkedHashMap<>();
            vorgangObj.put( "stammvorgang", stammvorgang );
            vorgangObj.put( "zeitraeume", zeitraeume );
            vorgaenge.add( vorgangObj );
        }

        stammakte.put( "vorgaenge", vorgaenge );
        String result = om.writeValueAsString( Collections.singletonMap( "stammakte", stammakte ) );

        LOG.finest( "Resulting JSON: " + result );

        // Close database call at the very end after all queries are completed
        dbCall.closeQuery();

        return result;
    }

    /* ===========================
       Vorgang / Listen / Felder
       =========================== */

    private static Date fetchEingangsdatum ( IInwoDBCall dbCall, Integer sakId, Integer lfdnr ) throws SQLException {
        String q = "SELECT EING_DAT FROM T_STAMMVORGAENGE WHERE SAK_ID = :sak_id AND LFDNR = :lfdnr";
        dbCall.query( q );
        dbCall.in( "sak_id", sakId, Types.INTEGER );
        dbCall.in( "lfdnr", lfdnr, Types.INTEGER );

        ResultSet rs = dbCall.executeQuery( true );
        if ( rs.next() )
            return rs.getDate( "EING_DAT" );

        return null;
    }

    /** Holt RECHT; Fallback auf SGESETZESFASSUNG (String). */
    private static String fetchRecht ( IInwoDBCall dbCall, Integer bez, Integer wognr,
                                       Integer lfdnr ) throws SQLException {
        String q = "SELECT RECHT " + "FROM T_ANTRAEGE WHERE WOGNR = :wognr AND BEZ = :bez AND LFDNR = :lfdnr";
        dbCall.query( q );
        dbCall.in( "wognr", wognr, Types.INTEGER );
        dbCall.in( "bez", bez, Types.INTEGER );
        dbCall.in( "lfdnr", lfdnr, Types.INTEGER );

        ResultSet rs = dbCall.executeQuery( true );
        if ( rs.next() ) {
            String recht = nullTrim( rs.getString( "RECHT" ) );
            return recht;
        }

        return null;
    }

    /* ===========================
       Zeiträume + Details
       =========================== */

    /**
     * Lädt Zeiträume für einen Vorgang.
     * - onlyKonnr != null  -> nur dieser KONNR (aktueller Vorgang)
     * - onlyKonnr == null  -> alle Zeiträume (andere Vorgänge)
     */
    private static List<Map<String, Object>> fetchZeitraeumeUndDetails ( IInwoDBCall dbCall, Integer bez, Integer wognr,
                                                                         Integer lfdnr, Date eingang, String recht,
                                                                         Integer onlyKonnr ) throws SQLException {
        LOG.finest( "fetchZeitraeumeUndDetails BEZ: " + bez + " - WOGNR: " + wognr + " - LFDNR: " + lfdnr +
                    " - onlyKonnr: " + onlyKonnr );

        String base =
            "SELECT TO_CHAR(k.KONNR) AS zeitraum_id, " + "       TO_CHAR(k.BEGINN_DAT, 'YYYY-MM-DD') AS beginn, " +
            "       TO_CHAR(k.ENDE_DAT,   'YYYY-MM-DD') AS ende, " +
            "       k.MIETE AS miete_ref_id, k.AUSSTATTUNG AS ausstattung_ref_id " + "FROM T_KONSTELLATIONEN k " +
            "WHERE k.AN_WOGNR = :wognr AND k.AN_BEZ = :bez AND k.AN_LFDNR = :lfdnr ";
        String filter = ( onlyKonnr != null ) ? "AND k.KONNR = :konnr " : "";
        String order = "ORDER BY k.BEGINN_DAT NULLS FIRST, k.KONNR";
        String q = base + filter + order;

        LOG.finest( "SQL Query: " + q );
        LOG.finest( "Parameters - bez: " + bez + ", wognr: " + wognr + ", lfdnr: " + lfdnr + ", konnr: " + onlyKonnr );

        dbCall.query( q );
        dbCall.in( "wognr", wognr, Types.INTEGER );
        dbCall.in( "bez", bez, Types.INTEGER );
        dbCall.in( "lfdnr", lfdnr, Types.INTEGER );
        if ( onlyKonnr != null )
            dbCall.in( "konnr", onlyKonnr, Types.INTEGER );

        List<Map<String, Object>> out = new ArrayList<>();
        ResultSet rs = dbCall.executeQuery( true );
        LOG.finest( "SQL executed, checking for results..." );
        int rowCount = 0;
        while ( rs.next() ) {
            rowCount++;
            LOG.finest( "Processing row " + rowCount );
            String zeitraumId = rs.getString( "zeitraum_id" );
            String beginn = rs.getString( "beginn" );
            String ende = rs.getString( "ende" );
            Integer mieteId = ( ( BigDecimal ) rs.getObject( "miete_ref_id" ) ).intValue();
            Integer ausstId = ( ( BigDecimal ) rs.getObject( "ausstattung_ref_id" ) ).intValue();

            Map<String, Object> zr = new LinkedHashMap<>();
            zr.put( "id", zeitraumId );
            if ( beginn != null )
                zr.put( "beginn", beginn );
            if ( ende != null )
                zr.put( "ende", ende );

            Map<String, Object> miete = fetchMiete( dbCall, mieteId, recht );
            if ( miete == null ) {
                miete = new LinkedHashMap<>();
            }

            List<Map<String, Object>> untermieter = fetchUntermieter( dbCall, mieteId, recht );
            if ( untermieter == null ) {
                untermieter = new ArrayList<>();
            }
            miete.put( "untermieter", untermieter );

            List<Map<String, Object>> mitbewohner = fetchMitbewohner( dbCall, mieteId, recht );
            if ( mitbewohner == null ) {
                mitbewohner = new ArrayList<>();
            }
            miete.put( "mitbewohner", mitbewohner );
            zr.put( "miete", miete );

            Map<String, Object> ausstattung = fetchAusstattung( dbCall, ausstId, eingang );
            if ( ausstattung == null ) {
                ausstattung = new LinkedHashMap<>();
            }
            zr.put( "ausstattung", ausstattung );

            out.add( zr );
        }

        LOG.finest( "Returning " + out.size() + " zeitraeume" );
        return out;
    }

    /* ===================== Miete inkl. Pauschalen ===================== */

    private static Map<String, Object> fetchMiete ( IInwoDBCall dbCall, Integer mieteIdOrNull,
                                                    String recht ) throws SQLException {
        String q =
            "SELECT " + "  TO_CHAR(m.ID) AS id, " +
            "  m.MIETE AS miete, m.MIETE_ALT AS mieteAlt, m.SONST_ZUSCHLAG AS sonstZuschlag, " +
            "  m.BEITRAG_DRITTER AS beitragDritter, " + "  m.HEIZUNG AS heizung, m.HEIZUNG_P AS heizungP_raw, " +
            "  m.WARMWASSER AS warmwasser, m.WARMWASSER_P AS warmwasserP_raw, " +
            "  m.MOEBLIERUNG AS moeblierung, m.MOEBLIERUNG_P AS moeblierungP_raw, " +
            "  m.UMIET_ZUSCHLAG AS umietZuschlag, " +
            "  m.GEWERBE_ZUSCHLAG AS gewerbeZuschlag, m.GEWERBE_ZUSCHLAG_P AS gewerbeZuschlagP_raw, " +
            "  m.GEWERBE_FLAECHE AS gewerbeFlaeche, " +
            "  m.JHRL_FERNWARMW AS jhrlFernwarmw, m.JHRL_FERNWAERME AS jhrlFernwaerme, " +
            "  m.ANT_MIET_WW AS antMietWw, m.ANT_BRUT_WW AS antBrutWw, m.JHRL_FERN_W_UND_W AS jhrlFernWUndW, " +
            "  m.LSTG_AUFENTHALT_PERSON AS lstgAufenthaltPerson, " +
            "  m.SONST_WOG_FAEHIG_BETRAG AS sonstWogFaehigBetrag, m.SONST_WOG_FAEHIG_TEXT AS sonstWogFaehigText, " +
            "  m.SONST_ZUSCHLAG_BESCHR AS sonstZuschlagBeschr, " +
            "  m.ENERGIE AS energie, m.ENERGIE_P AS energieP_raw, " +
            "  m.STELLPLATZ AS stellplatz, m.STELLPLATZ_P AS stellplatzP_raw " +
            "FROM T_MIETEN m WHERE m.ID = COALESCE(:miete_id, 1)";
        dbCall.query( q );
        dbCall.in( "miete_id", mieteIdOrNull, Types.INTEGER );

        Map<String, Object> out = new LinkedHashMap<>();
        ResultSet rs = dbCall.executeQuery( true );
        if ( rs.next() ) {
            out.put( "id", "1" ); // Always set miete ID to "1" in JSON output
            putNum( out, "miete", rs, "miete" );
            putNum( out, "mieteAlt", rs, "mieteAlt" );
            putNum( out, "sonstZuschlag", rs, "sonstZuschlag" );
            putNum( out, "beitragDritter", rs, "beitragDritter" );
            putNum( out, "heizung", rs, "heizung" );

            // Pauschalen-Text nur, wenn *_P nicht NULL und Lookup Treffer:
            out.put( "heizungP",
                     resolvePauschaleOrNull( dbCall, rs.getString( "heizungP_raw" ), "HEIZKOSTENPAUSCHALE", recht ) );
            putNum( out, "warmwasser", rs, "warmwasser" );
            out.put( "warmwasserP",
                     resolvePauschaleOrNull( dbCall, rs.getString( "warmwasserP_raw" ), "WARMWASSERPAUSCHALE",
                                             recht ) );
            putNum( out, "moeblierung", rs, "moeblierung" );
            out.put( "moeblierungP",
                     resolvePauschaleOrNull( dbCall, rs.getString( "moeblierungP_raw" ), "MOEBLIERUNGSPAUSCHALE",
                                             recht ) );

            putNum( out, "umietZuschlag", rs, "umietZuschlag" );
            putNum( out, "gewerbeZuschlag", rs, "gewerbeZuschlag" );
            out.put( "gewerbeZuschlagP",
                     resolvePauschaleOrNull( dbCall, rs.getString( "gewerbeZuschlagP_raw" ), "GEWERBEPAUSCHALE",
                                             recht ) );

            putNum( out, "gewerbeFlaeche", rs, "gewerbeFlaeche" );
            putNum( out, "jhrlFernwarmw", rs, "jhrlFernwarmw" );
            putNum( out, "jhrlFernwaerme", rs, "jhrlFernwaerme" );
            putNum( out, "antMietWw", rs, "antMietWw" );
            putNum( out, "antBrutWw", rs, "antBrutWw" );
            putNum( out, "jhrlFernWUndW", rs, "jhrlFernWUndW" );
            putNum( out, "lstgAufenthaltPerson", rs, "lstgAufenthaltPerson" );
            putNum( out, "sonstWogFaehigBetrag", rs, "sonstWogFaehigBetrag" );

            // Ensure string fields are never null
            String sonstWogFaehigText = rs.getString( "sonstWogFaehigText" );
            out.put( "sonstWogFaehigText", sonstWogFaehigText != null ? sonstWogFaehigText : "" );

            String sonstZuschlagBeschr = rs.getString( "sonstZuschlagBeschr" );
            out.put( "sonstZuschlagBeschr", sonstZuschlagBeschr != null ? sonstZuschlagBeschr : "" );

            putNum( out, "energie", rs, "energie" );
            out.put( "energieP",
                     resolvePauschaleOrNull( dbCall, rs.getString( "energieP_raw" ), "ENERGIEPAUSCHALE", recht ) );

            putNum( out, "stellplatz", rs, "stellplatz" );

            out.put( "stellplatzP",
                     resolvePauschaleOrNull( dbCall, rs.getString( "stellplatzP_raw" ), "STELLPLATZPAUSCHALE",
                                             recht ) );
        }

        return out;
    }

    private static String resolvePauschaleOrNull ( IInwoDBCall dbCall, String rawValue, String art,
                                                   String recht ) throws SQLException {
        if ( rawValue == null )
            return null;
        if ( recht == null || recht.isEmpty() )
            return null;
        return lookupPauschaleBeschreibung( dbCall, rawValue, art, recht ); // null, wenn kein Treffer
    }

    private static String lookupPauschaleBeschreibung ( IInwoDBCall dbCall, String kuerzel, String art,
                                                        String recht ) throws SQLException {
        String q = "SELECT BESCHREIBUNG FROM T_PAUSCHALE WHERE KUERZEL = :kuerzel AND ART = :art AND RECHT = :recht";
        dbCall.query( q );
        dbCall.in( "kuerzel", kuerzel, Types.VARCHAR );
        dbCall.in( "art", art, Types.VARCHAR );
        dbCall.in( "recht", recht, Types.VARCHAR );

        ResultSet rs = dbCall.executeQuery( true );
        if ( rs.next() )
            return rs.getString( "BESCHREIBUNG" );

        return null;
    }

    /* ===================== Untermieter / Mitbewohner ===================== */

    private static List<Map<String, Object>> fetchUntermieter ( IInwoDBCall dbCall, Integer mieteIdOrNull,
                                                                String recht ) throws SQLException {
        String q =
            "SELECT TO_CHAR(u.MI_ID) AS miId, u.LFDNR, u.NAME, u.VORNAME, u.FLAECHE, u.MIETE, u.HEIZUNG, " +
            "       u.HEIZUNG_P AS heizungP_raw, u.WARMWASSER, u.WARMWASSER_P AS warmwasserP_raw, " +
            "       u.MOEBLIERUNG, u.MOEBLIERUNG_P AS moeblierungP_raw, u.SONST " +
            "FROM T_UNTERMIETEN u WHERE u.MI_ID = COALESCE(:miete_id, 1) ORDER BY u.LFDNR";
        dbCall.query( q );
        dbCall.in( "miete_id", mieteIdOrNull, Types.INTEGER );

        List<Map<String, Object>> out = new ArrayList<>();
        ResultSet rs = dbCall.executeQuery( true );
        while ( rs.next() ) {
            Map<String, Object> row = new LinkedHashMap<>();
            row.put( "miId", rs.getString( "miId" ) );
            row.put( "lfdnr", ( ( BigDecimal ) rs.getObject( "LFDNR" ) ).intValue() );

            String name = rs.getString( "NAME" );
            row.put( "name", name != null ? name : "" );

            String vorname = rs.getString( "VORNAME" );
            row.put( "vorname", vorname != null ? vorname : "" );

            putNum( row, "flaeche", rs, "FLAECHE" );
            putNum( row, "miete", rs, "MIETE" );
            putNum( row, "heizung", rs, "HEIZUNG" );

            // Pauschalen-Text nur, wenn *_P nicht NULL und Lookup Treffer:
            row.put( "heizungP",
                     resolvePauschaleOrNull( dbCall, rs.getString( "heizungP_raw" ), "HEIZKOSTENPAUSCHALE", recht ) );

            putNum( row, "warmwasser", rs, "WARMWASSER" );

            row.put( "warmwasserP",
                     resolvePauschaleOrNull( dbCall, rs.getString( "warmwasserP_raw" ), "WARMWASSERPAUSCHALE",
                                             recht ) );

            putNum( row, "moeblierung", rs, "MOEBLIERUNG" );

            row.put( "moeblierungP",
                     resolvePauschaleOrNull( dbCall, rs.getString( "moeblierungP_raw" ), "MOEBLIERUNGSPAUSCHALE",
                                             recht ) );

            putNum( row, "sonst", rs, "SONST" );
            out.add( row );
        }

        return out;
    }

    private static List<Map<String, Object>> fetchMitbewohner ( IInwoDBCall dbCall,
                                                                Integer mieteIdOrNull, String recht ) throws SQLException {
        String q =
            "SELECT TO_CHAR(mb.MI_ID) AS miId, mb.LFDNR, mb.NAME, mb.VORNAME, mb.ENTGELT, mb.HEIZUNG, " +
            "       mb.HEIZUNG_P AS heizungP_raw, mb.WARMWASSER, mb.WARMWASSER_P AS warmwasserP_raw, mb.SONST " +
            "FROM T_MITBEWOHNER mb WHERE mb.MI_ID = COALESCE(:miete_id, 1) ORDER BY mb.LFDNR";
        dbCall.query( q );
        dbCall.in( "miete_id", mieteIdOrNull, Types.INTEGER );

        List<Map<String, Object>> out = new ArrayList<>();
        ResultSet rs = dbCall.executeQuery( true );
        while ( rs.next() ) {
            Map<String, Object> row = new LinkedHashMap<>();
            row.put( "miId", rs.getString( "miId" ) );
            row.put( "lfdnr", ( ( BigDecimal ) rs.getObject( "LFDNR" ) ).intValue() );

            String name = rs.getString( "NAME" );
            row.put( "name", name != null ? name : "" );

            String vorname = rs.getString( "VORNAME" );
            row.put( "vorname", vorname != null ? vorname : "" );

            putNum( row, "entgelt", rs, "ENTGELT" );
            putNum( row, "heizung", rs, "HEIZUNG" );

            // Pauschalen-Text nur, wenn *_P nicht NULL und Lookup Treffer:
            row.put( "heizungP",
                     resolvePauschaleOrNull( dbCall, rs.getString( "heizungP_raw" ), "HEIZKOSTENPAUSCHALE", recht ) );

            putNum( row, "warmwasser", rs, "WARMWASSER" );

            row.put( "warmwasserP",
                     resolvePauschaleOrNull( dbCall, rs.getString( "warmwasserP_raw" ), "WARMWASSERPAUSCHALE", recht ) );

            putNum( row, "sonst", rs, "SONST" );
            out.add( row );
        }

        return out;
    }

    /* ===================== Ausstattung (mit S-Tabellen) ===================== */

    private static Map<String, Object> fetchAusstattung ( IInwoDBCall dbCall, Integer ausstattungId,
                                                          Date eingangsdatum ) throws SQLException {
        java.sql.Date ed =
            ( eingangsdatum != null ) ? new java.sql.Date( eingangsdatum.getTime() ) :
            java.sql
                                                                                                          .Date
                                                                                                          .valueOf( "2099-12-31" );

        String q =
            "SELECT " + "    TO_CHAR(a.ID) AS id, " + "    a.FLAECHE AS flaeche, " +
            "    COALESCE(heiz.BESCHREIBUNG, a.HEIZUNG) AS heizung, " +
            "    COALESCE(badart.BESCHREIBUNG, a.BAD) AS bad, " + "    a.SOZBAU_JN AS sozBauJn_raw, " +
            "    COALESCE(mietv.BESCHREIBUNG, a.MIETVERHAELTNIS) AS mietverhaeltnis, " +
            "    COALESCE(eigent.BESCHREIBUNG, a.EIGENTUM) AS eigentum, " +
            "    TO_CHAR(a.FERTIG_DAT, 'YYYY-MM-DD') AS fertigDat, " +
            "    a.NAME_EIG AS nameEig, a.ADRESSE_EIG AS adresseEig, a.TELEFON_EIG AS telefonEig, " +
            "    a.WOFG_JN AS wofgJn_raw, a.FOERDERUNG_JN AS foerderungJn_raw " + "FROM T_AUSSTATTUNGEN a " +
            "LEFT JOIN T_SHEIZUNGSARTEN heiz " + "       ON heiz.WERT = a.HEIZUNG " +
            "      AND :eingang_dat >= heiz.AB_DAT " +
            "      AND :eingang_dat <= NVL(heiz.BIS_DAT, DATE '9999-12-31') " + "LEFT JOIN T_SBADARTEN badart " +
            "       ON badart.WERT = a.BAD " + "      AND :eingang_dat >= badart.AB_DAT " +
            "      AND :eingang_dat <= NVL(badart.BIS_DAT, DATE '9999-12-31') " +
            "LEFT JOIN T_SMIETVERHAELTNISSE mietv " + "       ON mietv.WERT = a.MIETVERHAELTNIS " +
            "      AND :eingang_dat >= mietv.AB_DAT " +
            "      AND :eingang_dat <= NVL(mietv.BIS_DAT, DATE '9999-12-31') " + "LEFT JOIN T_SEIGENTUMSARTEN eigent " +
            "       ON eigent.WERT = a.EIGENTUM " + "      AND :eingang_dat >= eigent.AB_DAT " +
            "      AND :eingang_dat <= NVL(eigent.BIS_DAT, DATE '9999-12-31') " + "WHERE a.ID = :ausstattung_id";

        dbCall.query( q );
        dbCall.in( "eingang_dat", ed, Types.DATE );
        dbCall.in( "eingang_dat", ed, Types.DATE );
        dbCall.in( "eingang_dat", ed, Types.DATE );
        dbCall.in( "eingang_dat", ed, Types.DATE );
        dbCall.in( "eingang_dat", ed, Types.DATE );
        dbCall.in( "eingang_dat", ed, Types.DATE );
        dbCall.in( "eingang_dat", ed, Types.DATE );
        dbCall.in( "eingang_dat", ed, Types.DATE );
        dbCall.in( "ausstattung_id", ausstattungId, Types.INTEGER );

        Map<String, Object> out = new LinkedHashMap<>();
        ResultSet rs = dbCall.executeQuery( true );
        if ( rs.next() ) {
            out.put( "id", "1" ); // Always set ausstattung ID to "1" in JSON output
            putNum( out, "flaeche", rs, "flaeche" );

            String heizung = rs.getString( "heizung" );
            out.put( "heizung", heizung != null ? heizung : "" );

            String bad = rs.getString( "bad" );
            out.put( "bad", bad != null ? bad : "" );

            out.put( "sozBauJn", "J".equalsIgnoreCase( rs.getString( "sozBauJn_raw" ) ) );

            String mietverhaeltnis = rs.getString( "mietverhaeltnis" );
            out.put( "mietverhaeltnis", mietverhaeltnis != null ? mietverhaeltnis : "" );

            String eigentum = rs.getString( "eigentum" );
            out.put( "eigentum", eigentum != null ? eigentum : "" );

            String fertig = rs.getString( "fertigDat" );
            if ( fertig != null )
                out.put( "fertigDat", fertig );

            String nameEig = rs.getString( "nameEig" );
            out.put( "nameEig", nameEig != null ? nameEig : "" );

            String adresseEig = rs.getString( "adresseEig" );
            out.put( "adresseEig", adresseEig != null ? adresseEig : "" );

            String telefonEig = rs.getString( "telefonEig" );
            out.put( "telefonEig", telefonEig != null ? telefonEig : "" );

            out.put( "wofgJn", "J".equalsIgnoreCase( rs.getString( "wofgJn_raw" ) ) );
            out.put( "foerderungJn", "J".equalsIgnoreCase( rs.getString( "foerderungJn_raw" ) ) );
        }

        return out;
    }

}