package hr.com.port.ips.eracun.dao;

import hr.com.port.connectionPool.__Pool;
import hr.com.port.functions.DbFunctions;
import hr.com.port.functions.Functions;
import hr.com.port.ips.eracun.modeli.EracunDokument;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import org.apache.log4j.Logger;

public class EracunDokumentDao {

    static Logger logger = Logger.getLogger(EracunDokumentDao.class);

    // INSERT svih 42 polja u eracun_dokument.
    public static void insertNoviDokument(Connection conn, EracunDokument doc) throws SQLException {
        logger.debug("EracunDokumentDao insertNoviDokument: " + doc);

        final String sql =
            "INSERT INTO eracun_dokument (" +
            // 1..42
            "izlazni," +                           // 1
            "godina," +                            // 2
            "posrednik," +                         // 3
            "posrednik_naziv," +                   // 4
            "vrsta_dokumenta," +                   // 5
            "vrsta_dokumenta_naziv," +             // 6
            "tip_dokumenta," +                     // 7
            "tip_dokumenta_naziv," +               // 8
            "onu," +                               // 9
            "opp," +                               // 10
            "broj," +                              // 11
            "putanja_xml," +                       // 12
            "electronic_id," +                     // 13
            "document_nr," +                       // 14
            "document_type_id," +                  // 15
            "document_type_name," +                // 16
            "partner_business_number," +           // 17
            "partner_business_unit," +             // 18
            "partner_business_name," +             // 19
            "created," +                           // 20
            "updated," +                           // 21
            "sent," +                              // 22
            "delivered," +                         // 23
            "issue_date," +                        // 24
            "additional_dokument_status_id," +     // 25
            "reject_reason," +                     // 26
            "lokalni_status_id," +                 // 27
            "lokalni_status_naziv," +              // 28
            "transportni_status_id," +             // 29
            "transportni_status_naziv," +          // 30
            "procesni_status_id," +                // 31
            "procesni_status_naziv," +             // 32
            "datum_kreiranja," +                   // 33
            "datum_slanja," +                      // 34
            "broj_slanja," +                       // 35
            "datum_zadnjeg_statusa," +             // 36
            "poruka_greske," +                     // 37
            "sync_token," +                        // 38
			"fiskaliziran_status," +			   // 39
			"placen_status," +					   // 40
			"odbijen_status," +					   // 41
			"vizualiziran" +					   // 42
			") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            int i = 0;
            ps.setBoolean(++i, doc.isIzlazni());                              // 1
            ps.setInt(++i, doc.getGodina());                                  // 2
            ps.setInt(++i, doc.getPosrednik());                               // 3
            ps.setString(++i, doc.getPosrednikNaziv());                       // 4
            ps.setInt(++i, doc.getVrstaDokumenta());                          // 5
            ps.setString(++i, doc.getVrstaDokumentaNaziv());                  // 6
            ps.setString(++i, doc.getTipDokumenta());                         // 7
            ps.setString(++i, doc.getTipDokumentaNaziv());                    // 8
            ps.setInt(++i, doc.getOnu());                                     // 9
            ps.setInt(++i, doc.getOpp());                                     // 10
            ps.setInt(++i, doc.getBroj());                                    // 11
            ps.setString(++i, doc.getPutanjaXml());                           // 12
            ps.setLong(++i, doc.getElectronicId());                           // 13
            ps.setString(++i, doc.getDocumentNr());                           // 14
            ps.setInt(++i, doc.getDocumentTypeId());                          // 15
            ps.setString(++i, doc.getDocumentTypeName());                     // 16
            ps.setString(++i, doc.getPartnerBusinessNumber());                // 17
            ps.setString(++i, doc.getPartnerBusinessUnit());                  // 18
            ps.setString(++i, doc.getPartnerBusinessName());                  // 19
            ps.setTimestamp(++i, doc.getCreated());                           // 20
            ps.setTimestamp(++i, doc.getUpdated());                           // 21
            ps.setTimestamp(++i, doc.getSent());                              // 22
            ps.setTimestamp(++i, doc.getDelivered());                         // 23
            ps.setTimestamp(++i, doc.getIssueDate());                         // 24
            ps.setInt(++i, doc.getAdditionalDokumentStatusId());              // 25
            ps.setString(++i, doc.getRejectReason());                         // 26
            ps.setObject(++i, doc.getLokalniStatusId(), Types.INTEGER);       // 27
            ps.setString(++i, doc.getLokalniStatusNaziv());                   // 28
            ps.setObject(++i, doc.getTransportniStatusId(), Types.INTEGER);   // 29
            ps.setString(++i, doc.getTransportniStatusNaziv());               // 30
            ps.setObject(++i, doc.getProcesniStatusId(), Types.INTEGER);      // 31
            ps.setString(++i, doc.getProcesniStatusNaziv());                  // 32
            ps.setTimestamp(++i, doc.getDatumKreiranja());                    // 33
            ps.setTimestamp(++i, doc.getDatumSlanja());                       // 34
            ps.setInt(++i, doc.getBrojSlanja());                              // 35
            ps.setTimestamp(++i, doc.getDatumZadnjegStatusa());               // 36
            ps.setString(++i, doc.getPorukaGreske());						  // 37
			ps.setString(++i, doc.getSyncToken());							  // 38
			ps.setObject(++i, doc.getFiskaliziranStatus(), Types.INTEGER);	  // 39
			ps.setObject(++i, doc.getPlacenStatus(), Types.INTEGER);		  // 40
			ps.setObject(++i, doc.getOdbijenStatus(), Types.INTEGER);		  // 41
			ps.setBoolean(++i, doc.isVizualiziran());						  // 42
			ps.executeUpdate();
        }
    }

    // UPDATE statusnih polja po electronic_id (overload s Connection).
    public void updateStatus(Connection conn, EracunDokument doc) throws SQLException {
        final String sql =
            "UPDATE eracun_dokument SET " +
            "transportni_status_id = ?, transportni_status_naziv = ?, " +
            "procesni_status_id = ?, procesni_status_naziv = ?, " +
            "lokalni_status_id = ?, lokalni_status_naziv = ?, " +
            "datum_zadnjeg_statusa = ? " +
            "WHERE electronic_id = ? AND izlazni = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            int i = 0;
            ps.setObject(++i, doc.getTransportniStatusId(), Types.INTEGER);
            ps.setString(++i, doc.getTransportniStatusNaziv());
            ps.setObject(++i, doc.getProcesniStatusId(), Types.INTEGER);
            ps.setString(++i, doc.getProcesniStatusNaziv());
            ps.setObject(++i, doc.getLokalniStatusId(), Types.INTEGER);
            ps.setString(++i, doc.getLokalniStatusNaziv());
            ps.setTimestamp(++i, doc.getDatumZadnjegStatusa());
            ps.setLong(++i, doc.getElectronicId());
			ps.setBoolean(++i, doc.isIzlazni());
            ps.executeUpdate();
        }
    }

    // Ostavljen postojeći potpis; sada doista otvara konekciju iz poola.
    public void updateStatus(EracunDokument doc) {
        Connection conn = null;
        try {
            conn = getConnection();
            EracunDokumentDao.this.updateStatus(conn, doc);
        } catch (SQLException ex) {
            logger.error(new Functions().logging(ex));
        } finally {
            if (conn != null) try { conn.close(); } catch (SQLException ignore) {}
        }
    }
	
	public void updateAll(Connection externalConn, EracunDokument doc) throws SQLException {
        String sql = "UPDATE eracun_dokument SET " +
                "izlazni=?, godina=?, posrednik=?, posrednik_naziv=?, " +
                "vrsta_dokumenta=?, vrsta_dokumenta_naziv=?, tip_dokumenta=?, tip_dokumenta_naziv=?, " +
                "onu=?, opp=?, broj=?, putanja_xml=?, document_nr=?, electronic_id=?, " +
                "document_type_id=?, document_type_name=?, partner_business_number=?, partner_business_unit=?, partner_business_name=?, " +
                "created=?, updated=?, sent=?, delivered=?, issue_date=?, " +
                "additional_dokument_status_id=?, reject_reason=?, " +
                "datum_kreiranja=?, datum_slanja=?, broj_slanja=?, datum_zadnjeg_statusa=?, " +
                "poruka_greske=?, sync_token=?, fiskaliziran_status=?, placen_status=?, odbijen_status=?, vizualiziran=?, " +
                "transportni_status_id=?, transportni_status_naziv=?, procesni_status_id=?, procesni_status_naziv=?, " +
                "lokalni_status_id=?, lokalni_status_naziv=? " +
                "WHERE electronic_id = ? AND izlazni = ?";

        Connection conn = externalConn != null ? externalConn : getConnection();
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            int i = 1;
            ps.setBoolean(i++, doc.isIzlazni());
            ps.setInt(i++, doc.getGodina());
            ps.setInt(i++, doc.getPosrednik());
            ps.setString(i++, doc.getPosrednikNaziv());

            ps.setInt(i++, doc.getVrstaDokumenta());
            ps.setString(i++, doc.getVrstaDokumentaNaziv());
            ps.setString(i++, doc.getTipDokumenta());
            ps.setString(i++, doc.getTipDokumentaNaziv());

            ps.setInt(i++, doc.getOnu());
            ps.setInt(i++, doc.getOpp());
            ps.setInt(i++, doc.getBroj());
            ps.setString(i++, doc.getPutanjaXml());
            ps.setString(i++, doc.getDocumentNr());
            ps.setLong(i++, doc.getElectronicId());

            ps.setInt(i++, doc.getDocumentTypeId());
            ps.setString(i++, doc.getDocumentTypeName());
            ps.setString(i++, doc.getPartnerBusinessNumber());
            ps.setString(i++, doc.getPartnerBusinessUnit());
            ps.setString(i++, doc.getPartnerBusinessName());

            ps.setTimestamp(i++, doc.getCreated());
            ps.setTimestamp(i++, doc.getUpdated());
            ps.setTimestamp(i++, doc.getSent());
            ps.setTimestamp(i++, doc.getDelivered());
            ps.setTimestamp(i++, doc.getIssueDate());

            ps.setInt(i++, doc.getAdditionalDokumentStatusId());
            ps.setString(i++, doc.getRejectReason());

            ps.setTimestamp(i++, doc.getDatumKreiranja());
            ps.setTimestamp(i++, doc.getDatumSlanja());
            ps.setInt(i++, doc.getBrojSlanja());
            ps.setTimestamp(i++, doc.getDatumZadnjegStatusa());

            ps.setString(i++, doc.getPorukaGreske());
            ps.setString(i++, doc.getSyncToken());
			ps.setObject(i++, doc.getFiskaliziranStatus(), Types.INTEGER);
			ps.setObject(i++, doc.getPlacenStatus(), Types.INTEGER);
			ps.setObject(i++, doc.getOdbijenStatus(), Types.INTEGER);
            ps.setBoolean(i++, doc.isVizualiziran());

            ps.setObject(i++, doc.getTransportniStatusId(), java.sql.Types.INTEGER);
            ps.setString(i++, doc.getTransportniStatusNaziv());
            ps.setObject(i++, doc.getProcesniStatusId(), java.sql.Types.INTEGER);
            ps.setString(i++, doc.getProcesniStatusNaziv());
            ps.setObject(i++, doc.getLokalniStatusId(), java.sql.Types.INTEGER);
            ps.setString(i++, doc.getLokalniStatusNaziv());

            ps.setLong(i++, doc.getElectronicId());
			ps.setBoolean(i++, doc.isIzlazni());

            ps.executeUpdate();
        } finally {
            if (externalConn == null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
    }	

    // SELECT * po electronic_id; puni svih 40 svojstava.
    public EracunDokument findByElectronicId(long electronicId, boolean izlazni, Connection... defaultConnection) {
    final String sql = "SELECT * FROM eracun_dokument WHERE electronic_id = ? AND izlazni = ?";
        Connection conn = null;
        boolean external = defaultConnection != null && defaultConnection.length > 0 && defaultConnection[0] != null;

        try {
            conn = external ? defaultConnection[0] : getConnection();
            try (PreparedStatement ps = conn.prepareStatement(sql)) {
                ps.setLong(1, electronicId);
				ps.setBoolean(2, izlazni);
				
                try (ResultSet rs = ps.executeQuery()) {
                    if (!rs.next()) return null;

                    EracunDokument d = new EracunDokument();

                    d.setIzlazni(rs.getBoolean("izlazni"));
                    d.setGodina(rs.getInt("godina"));
                    d.setPosrednik(rs.getInt("posrednik"));
                    d.setPosrednikNaziv(rs.getString("posrednik_naziv"));
                    d.setVrstaDokumenta(rs.getInt("vrsta_dokumenta"));
                    d.setVrstaDokumentaNaziv(rs.getString("vrsta_dokumenta_naziv"));
                    d.setTipDokumenta(rs.getString("tip_dokumenta"));
                    d.setTipDokumentaNaziv(rs.getString("tip_dokumenta_naziv"));
                    d.setOnu(rs.getInt("onu"));
                    d.setOpp(rs.getInt("opp"));
                    d.setBroj(rs.getInt("broj"));
                    d.setPutanjaXml(rs.getString("putanja_xml"));
                    d.setElectronicId(rs.getLong("electronic_id"));
                    d.setDocumentNr(rs.getString("document_nr"));

                    // MER header info
                    d.setDocumentTypeId(rs.getInt("document_type_id"));
                    d.setDocumentTypeName(rs.getString("document_type_name"));
                    d.setPartnerBusinessNumber(rs.getString("partner_business_number"));
                    d.setPartnerBusinessUnit(rs.getString("partner_business_unit"));
                    d.setPartnerBusinessName(rs.getString("partner_business_name"));

                    d.setCreated(rs.getTimestamp("created"));
                    d.setUpdated(rs.getTimestamp("updated"));
                    d.setSent(rs.getTimestamp("sent"));
                    d.setDelivered(rs.getTimestamp("delivered"));
                    d.setIssueDate(rs.getTimestamp("issue_date"));

                    d.setAdditionalDokumentStatusId(rs.getInt("additional_dokument_status_id"));
                    d.setRejectReason(rs.getString("reject_reason"));

                    // statusi (nullable)
                    d.setLokalniStatusId((Integer) rs.getObject("lokalni_status_id"));
                    d.setLokalniStatusNaziv(rs.getString("lokalni_status_naziv"));
                    d.setTransportniStatusId((Integer) rs.getObject("transportni_status_id"));
                    d.setTransportniStatusNaziv(rs.getString("transportni_status_naziv"));
                    d.setProcesniStatusId((Integer) rs.getObject("procesni_status_id"));
                    d.setProcesniStatusNaziv(rs.getString("procesni_status_naziv"));

                    // lokalni datumi i ostalo
                    d.setDatumKreiranja(rs.getTimestamp("datum_kreiranja"));
                    d.setDatumSlanja(rs.getTimestamp("datum_slanja"));
                    d.setBrojSlanja(rs.getInt("broj_slanja"));
                    d.setDatumZadnjegStatusa(rs.getTimestamp("datum_zadnjeg_statusa"));

                    d.setPorukaGreske(rs.getString("poruka_greske"));
                    d.setSyncToken(rs.getString("sync_token"));
                    d.setFiskaliziranStatus(rs.getInt("fiskaliziran_status"));
					d.setPlacenStatus(rs.getInt("placen_status"));
					d.setOdbijenStatus(rs.getInt("odbijen_status"));
                    d.setVizualiziran(rs.getBoolean("vizualiziran"));

                    return d;
                }
            }
        } catch (SQLException ex) {
            logger.error(new Functions().logging(ex));
        } finally {
            if (!external && conn != null) try { conn.close(); } catch (SQLException ignore) {}
        }
        return null;
    }
	
	public static boolean eracunPostoji(int vrstaDokumenta, int godina, int onuId, int oppId, int broj){
		//ako je postoji ijedan eracun za zadani dokument, vraća true
		boolean postoji = false;
		try {
			String query = "SELECT COUNT(id) as postoji FROM eracun_dokument WHERE vrsta_dokumenta=" + vrstaDokumenta + " AND godina=" + godina 
					+ " AND opp=" + oppId + " AND onu=" + onuId + " AND broj=" + broj;
			ResultSet rs = DbFunctions.execQuery(query, null, false);
			rs.next();
			if (rs.getInt("postoji") > 0) {
				postoji = true;
			}
			rs.close();
		} catch (SQLException ex) {
			logger.error(new Functions().logging(ex));
		}
		return postoji;
	}
	
	public List<EracunDokument> findHeaders(int vrstaDokumenta, int godina, int onuId, int oppId, int broj, boolean izlazni, Connection... defaultConnection) {
		final String sql = "SELECT * FROM eracun_dokument " +
				"WHERE vrsta_dokumenta=? AND godina=? AND onu=? AND opp=? AND broj=? AND izlazni=? " +
				"ORDER BY COALESCE(updated, created) DESC, electronic_id DESC";
		System.out.println("ERACUNDOKUMENTDAO FINDHEADERS query=" + sql);
		Connection conn = null;
		boolean external = defaultConnection != null && defaultConnection.length > 0 && defaultConnection[0] != null;
		List<EracunDokument> list = new ArrayList<EracunDokument>();
		try {
			conn = external ? defaultConnection[0] : new hr.com.port.connectionPool.__Pool(null).getConnection();
			try (PreparedStatement ps = conn.prepareStatement(sql)) {
				int i = 1;
				ps.setInt(i++, vrstaDokumenta);
				ps.setInt(i++, godina);
				ps.setInt(i++, onuId);
				ps.setInt(i++, oppId);
				ps.setInt(i++, broj);
				ps.setBoolean(i++, izlazni);

				try (ResultSet rs = ps.executeQuery()) {
					while (rs.next()) {
						EracunDokument d = new EracunDokument();
						// popuni SVA polja koja već puniš u findByElectronicId(...)
						// (ovdje uzmi isti obrazac mapiranja koji već imaš)
						d.setIzlazni(rs.getBoolean("izlazni"));
						d.setGodina(rs.getInt("godina"));
						d.setPosrednik(rs.getInt("posrednik"));
						d.setPosrednikNaziv(rs.getString("posrednik_naziv"));
						d.setVrstaDokumenta(rs.getInt("vrsta_dokumenta"));
						d.setVrstaDokumentaNaziv(rs.getString("vrsta_dokumenta_naziv"));
						d.setOnu(rs.getInt("onu"));
						d.setOpp(rs.getInt("opp"));
						d.setBroj(rs.getInt("broj"));
						d.setPutanjaXml(rs.getString("putanja_xml"));
						d.setElectronicId(rs.getLong("electronic_id"));
						d.setCreated(rs.getTimestamp("created"));
						d.setUpdated(rs.getTimestamp("updated"));
						d.setSent(rs.getTimestamp("sent"));
						d.setDelivered(rs.getTimestamp("delivered"));
						d.setTransportniStatusId((Integer) rs.getObject("transportni_status_id"));
						d.setTransportniStatusNaziv(rs.getString("transportni_status_naziv"));
						d.setProcesniStatusId((Integer) rs.getObject("procesni_status_id"));
						d.setProcesniStatusNaziv(rs.getString("procesni_status_naziv"));
						d.setLokalniStatusId((Integer) rs.getObject("lokalni_status_id"));
						d.setLokalniStatusNaziv(rs.getString("lokalni_status_naziv"));
						d.setPorukaGreske(rs.getString("poruka_greske"));
						d.setDatumZadnjegStatusa(rs.getTimestamp("datum_zadnjeg_statusa"));
						d.setBrojSlanja(rs.getInt("broj_slanja"));
						d.setSyncToken(rs.getString("sync_token"));
						// ... (ostalo kao u postojećem mapperu)
						list.add(d);
					}
				}
			}
		} catch (SQLException ex) {
			logger.error(new Functions().logging(ex));
		} finally {
			if (!external && conn != null) try { conn.close(); } catch (SQLException ignore) {}
		}
		return list;
	}	
	
    // Dohvat konekcije iz poola.
    private Connection getConnection() throws SQLException {
        return new __Pool(null).getConnection();
    }
}