package com.tsystems.diwo.business.queries;

import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.SerializationFeature;

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.LinkedHashMap;
import java.util.List;
import java.util.Map;

import oracle.jbo.JboException;

/**
 * Gemeinsame Basisklasse für Vergleichsansicht-JSON-DB-Funktionen.
 * Enthält alle gemeinsamen Methoden für die Verarbeitung von Stammakten, Vorgängen und grundlegenden DB-Operationen.
 */
public class VergleichsansichtBaseDbFunctions {

    protected static final ObjectMapper om =
        new ObjectMapper().enable( SerializationFeature.WRITE_DATES_AS_TIMESTAMPS );

    /* ===========================
       STAMMAKTE (Root-Daten)
       =========================== */

    protected static Map<String, Object> fetchStammakte ( IInwoDBCall dbCall, Integer sakId ) throws SQLException {
        String q =
            "SELECT sak.AZ_ANZEIGE AS aktenzeichen, " + "       bew.NAME AS antragstellername, " +
            "       TO_CHAR(bew.GEB_DAT, 'YYYY-MM-DD') AS antragstellerGeburtsdatum, " +
            "       sak.A_STRASSE AS adr_strasse, TO_CHAR(sak.A_HAUSNR) AS adr_hausnummer, " +
            "       sak.A_HAUSNRZUSATZ AS adr_zusatz, sak.A_PLZ_ORT AS adr_plz, sak.A_ORT AS adr_ort " +
            "FROM T_STAMMAKTEN sak " + "LEFT JOIN T_BEWOHNER bew ON bew.ID = sak.PE_ID " + "WHERE sak.ID = :sak_id";
        dbCall.query( q );
        dbCall.in( "sak_id", sakId, Types.INTEGER );

        Map<String, Object> stammakte = new LinkedHashMap<>();
        ResultSet rs = null;

        rs = dbCall.executeQuery( true );
        if ( rs.next() ) {
            stammakte.put( "aktenzeichen", rs.getString( "aktenzeichen" ) );
            stammakte.put( "antragstellername", rs.getString( "antragstellername" ) );
            String geb = rs.getString( "antragstellerGeburtsdatum" );
            if ( geb != null )
                stammakte.put( "antragstellerGeburtsdatum", geb );

            Map<String, Object> adr = new LinkedHashMap<>();
            adr.put( "strasse", rs.getString( "adr_strasse" ) );
            adr.put( "hausnummer", rs.getString( "adr_hausnummer" ) );
            // Fix: Handle null values for zusatz - schema expects string, not null
            String zusatz = rs.getString( "adr_zusatz" );
            adr.put( "zusatz", zusatz != null ? zusatz : "" );
            adr.put( "plz", rs.getString( "adr_plz" ) );
            adr.put( "ort", rs.getString( "adr_ort" ) );
            stammakte.put( "anschrift", adr );
        }

        return stammakte;
    }

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

    /** Holt BEZ und WOGNR aus T_AKTEN für eine gegebene SAK_ID */
    protected static Map<String, Integer> fetchBezWognrFromAkten ( IInwoDBCall dbCall,
                                                                   Integer sakId ) throws SQLException {
        if ( sakId == null ) {
            throw new JboException( "Fehler: SAK_ID ist null in fetchBezWognrFromAkten" );
        }

        String q = "SELECT a.BEZ, a.WOGNR FROM T_AKTEN a WHERE a.SAK_ID = :sak_id";
        dbCall.query( q );
        dbCall.in( "sak_id", sakId, Types.INTEGER );

        Map<String, Integer> result = new LinkedHashMap<>();
        ResultSet rs = null;

        rs = dbCall.executeQuery( true );
        if ( rs.next() ) {
            Object bezObj = rs.getObject( "BEZ" );
            Object wognrObj = rs.getObject( "WOGNR" );

            if ( bezObj == null || wognrObj == null ) {
                throw new JboException( "Fehler: BEZ oder WOGNR ist null für SAK_ID " + sakId + " - BEZ: " + bezObj +
                                        ", WOGNR: " + wognrObj );
            }

            result.put( "bez", ( ( BigDecimal ) bezObj ).intValue() );
            result.put( "wognr", ( ( BigDecimal ) wognrObj ).intValue() );
        } else {
            throw new JboException( "Fehler: Keine Daten in T_AKTEN für SAK_ID " + sakId + " gefunden" );
        }


        return result;
    }

    protected static Map<String, Object> fetchStammvorgang ( IInwoDBCall dbCall, Integer sakId,
                                                             Integer lfdnr ) throws SQLException {
        String q =
            "SELECT TO_CHAR(sv.LFDNR) AS id, " + "       COALESCE(st.BESCHREIBUNG, sv.SVORGANGSTYP) AS vorgangstyp, " +
            "       COALESCE(sa.BESCHREIBUNG, TO_CHAR(sv.STATUS)) AS vorgangsstatus " + "FROM T_STAMMVORGAENGE sv " +
            "LEFT JOIN T_SANTRAGSTYPEN st ON st.WERT = sv.SVORGANGSTYP " +
            "LEFT JOIN T_SANTRAGSSTATUS sa ON sa.WERT = sv.STATUS " + "WHERE sv.SAK_ID = :sak_id AND sv.LFDNR = :lfdnr";
        dbCall.query( q );
        dbCall.in( "sak_id", sakId, Types.INTEGER );
        dbCall.in( "lfdnr", lfdnr, Types.INTEGER );
        ResultSet rs = null;
        Map<String, Object> out = new LinkedHashMap<>();
        rs = dbCall.executeQuery( true );
        if ( rs.next() ) {
            out.put( "id", rs.getString( "id" ) ); // JSON erwartet String
            String vt = rs.getString( "vorgangstyp" );
            if ( vt != null )
                out.put( "vorgangstyp", vt );
            String vs = rs.getString( "vorgangsstatus" );
            if ( vs != null )
                out.put( "vorgangsstatus", vs );
        }

        return out;
    }

    protected static List<Integer> fetchAndereLfdnr ( IInwoDBCall dbCall, Integer sakId,
                                                      Integer lfdnr ) throws SQLException {
        String q = "SELECT LFDNR FROM T_STAMMVORGAENGE WHERE SAK_ID = :sak_id AND LFDNR <> :lfdnr ORDER BY LFDNR";
        dbCall.query( q );
        dbCall.in( "sak_id", sakId, Types.INTEGER );
        dbCall.in( "lfdnr", lfdnr, Types.INTEGER );
        ResultSet rs = null;
        List<Integer> out = new ArrayList<>();

        rs = dbCall.executeQuery( true );
        while ( rs.next() )
            out.add( ( ( BigDecimal ) rs.getObject( "LFDNR" ) ).intValue() );

        return out;
    }

    /* ===================== Utils ===================== */

    protected static void putNum ( Map<String, Object> target, String jsonKey, ResultSet rs,
                                   String col ) throws SQLException {
        Object o = rs.getObject( col );
        if ( o != null ) {
            if ( o instanceof BigDecimal )
                target.put( jsonKey, ( BigDecimal ) o );
            else if ( o instanceof Number )
                target.put( jsonKey, ( ( Number ) o ).doubleValue() );
            else
                target.put( jsonKey, o );
        }
    }

    protected static void putNumIfNotNull ( Map<String, Object> target, String jsonKey, ResultSet rs,
                                            String col ) throws SQLException {
        Object o = rs.getObject( col );
        if ( o != null ) {
            if ( o instanceof BigDecimal )
                target.put( jsonKey, ( BigDecimal ) o );
            else if ( o instanceof Number )
                target.put( jsonKey, ( ( Number ) o ).doubleValue() );
            else
                target.put( jsonKey, o );
        }
    }

    protected static void putStringIfNotNull ( Map<String, Object> target, String jsonKey, String value ) {
        if ( value != null && !value.trim().isEmpty() ) {
            target.put( jsonKey, value.trim() );
        }
    }

    protected static String nullTrim ( String s ) {
        return s == null ? null : s.trim();
    }
}