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

import hr.com.port.functions.Functions;
import hr.com.port.ips.eracun.modeli.MerEreporting;
import org.apache.log4j.Logger;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.sql.Date;

public class MerEreportingDao {

    private static final Logger logger = Logger.getLogger(MerEreportingDao.class);

    public Long insert(Connection conn, MerEreporting m) {
        final String sql = "INSERT INTO eracun_ereporting (" +
                "vrsta_dokumenta, godina, opp, onu, broj, " +
                "delivery_date, is_copy, invoice_type_code, " +
                "status_id, xml_path, created_at, updated_at) " +
                "VALUES (?,?,?,?,?,?,?,?,?,?,?,?)";

        Long id = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setInt(1, m.getVrstaDokumenta());
            ps.setInt(2, m.getGodina());
            ps.setInt(3, m.getOpp());
            ps.setInt(4, m.getOnu());
            ps.setInt(5, m.getBroj());

            ps.setDate(6, m.getDeliveryDate());
            ps.setBoolean(7, m.getIsCopy() != null ? m.getIsCopy() : false);
            ps.setString(8, m.getInvoiceTypeCode());

            ps.setInt(9, m.getStatusId() != null ? m.getStatusId() : 0);
            ps.setString(10, m.getXmlPath());

            Timestamp now = new Timestamp(System.currentTimeMillis());
            ps.setTimestamp(11, m.getCreatedAt() != null ? m.getCreatedAt() : now);
            ps.setTimestamp(12, m.getUpdatedAt());

            ps.executeUpdate();
            rs = ps.getGeneratedKeys();
            if (rs.next()) {
                id = rs.getLong(1);
                m.setId(id);
            }
        } catch (Exception ex) {
            logger.error(new Functions().logging(ex));
        } finally {
            try { if (rs != null) rs.close(); } catch (Exception ex) { logger.error(new Functions().logging(ex)); }
            try { if (ps != null) ps.close(); } catch (Exception ex) { logger.error(new Functions().logging(ex)); }
        }
        return id;
    }

    public boolean updateStatusById(Connection conn, long id, Integer statusId) {
        final String sql = "UPDATE eracun_ereporting SET status_id = ?, updated_at = ? WHERE id = ?";
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            if (statusId != null) {
                ps.setInt(1, statusId);
            } else {
                ps.setNull(1, java.sql.Types.INTEGER);
            }
            ps.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
            ps.setLong(3, id);
            return ps.executeUpdate() > 0;
        } catch (Exception ex) {
            logger.error(new Functions().logging(ex));
        } finally {
            try { if (ps != null) ps.close(); } catch (Exception ex) { logger.error(new Functions().logging(ex)); }
        }
        return false;
    }

    public boolean updateStatusByBusinessKey(Connection conn,
                                             int vrstaDokumenta, int godina, int opp, int onu, int broj,
                                             Integer statusId) {
        final String sql = "UPDATE eracun_ereporting " +
                "SET status_id = ?, updated_at = ? " +
                "WHERE vrsta_dokumenta = ? AND godina = ? AND opp = ? AND onu = ? AND broj = ?";
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            if (statusId != null) {
                ps.setInt(1, statusId);
            } else {
                ps.setNull(1, java.sql.Types.INTEGER);
            }
            ps.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
            ps.setInt(3, vrstaDokumenta);
            ps.setInt(4, godina);
            ps.setInt(5, opp);
            ps.setInt(6, onu);
            ps.setInt(7, broj);
            return ps.executeUpdate() > 0;
        } catch (Exception ex) {
            logger.error(new Functions().logging(ex));
        } finally {
            try { if (ps != null) ps.close(); } catch (Exception ex) { logger.error(new Functions().logging(ex)); }
        }
        return false;
    }

    public boolean updateXmlAndParamsByBusinessKey(Connection conn,
                                                   int vrstaDokumenta, int godina, int opp, int onu, int broj,
                                                   String xmlPath, Date deliveryDate, boolean isCopy, String invoiceTypeCode) {
        final String sql = "UPDATE eracun_ereporting SET " +
                "xml_path = ?, delivery_date = ?, is_copy = ?, invoice_type_code = ?, updated_at = ? " +
                "WHERE vrsta_dokumenta = ? AND godina = ? AND opp = ? AND onu = ? AND broj = ?";
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            ps.setString(1, xmlPath);
            ps.setDate(2, deliveryDate);
            ps.setBoolean(3, isCopy);
            ps.setString(4, invoiceTypeCode);
            ps.setTimestamp(5, new Timestamp(System.currentTimeMillis()));
            ps.setInt(6, vrstaDokumenta);
            ps.setInt(7, godina);
            ps.setInt(8, opp);
            ps.setInt(9, onu);
            ps.setInt(10, broj);
            return ps.executeUpdate() > 0;
        } catch (Exception ex) {
            logger.error(new Functions().logging(ex));
        } finally {
            try { if (ps != null) ps.close(); } catch (Exception ex) { logger.error(new Functions().logging(ex)); }
        }
        return false;
    }

    public MerEreporting findById(Connection conn, long id) {
        final String sql = "SELECT * FROM eracun_ereporting WHERE id = ?";
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
            ps.setLong(1, id);
            rs = ps.executeQuery();
            if (rs.next()) {
                return map(rs);
            }
        } catch (Exception ex) {
            logger.error(new Functions().logging(ex));
        } finally {
            try { if (rs != null) rs.close(); } catch (Exception ex) { logger.error(new Functions().logging(ex)); }
            try { if (ps != null) ps.close(); } catch (Exception ex) { logger.error(new Functions().logging(ex)); }
        }
        return null;
    }

    public MerEreporting findByBusinessKey(Connection conn,
                                              int vrstaDokumenta, int godina, int opp, int onu, int broj) {
        final String sql = "SELECT * FROM eracun_ereporting " +
                "WHERE vrsta_dokumenta = ? AND godina = ? AND opp = ? AND onu = ? AND broj = ? " +
                "LIMIT 1";
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1, vrstaDokumenta);
            ps.setInt(2, godina);
            ps.setInt(3, opp);
            ps.setInt(4, onu);
            ps.setInt(5, broj);
            rs = ps.executeQuery();
            if (rs.next()) {
                return map(rs);
            }
        } catch (Exception ex) {
            logger.error(new Functions().logging(ex));
        } finally {
            try { if (rs != null) rs.close(); } catch (Exception ex) { logger.error(new Functions().logging(ex)); }
            try { if (ps != null) ps.close(); } catch (Exception ex) { logger.error(new Functions().logging(ex)); }
        }
        return null;
    }

    private MerEreporting map(ResultSet rs) throws Exception {
        MerEreporting m = new MerEreporting();
        m.setId(rs.getLong("id"));
        m.setVrstaDokumenta(rs.getInt("vrsta_dokumenta"));
        m.setGodina(rs.getInt("godina"));
        m.setOpp(rs.getInt("opp"));
        m.setOnu(rs.getInt("onu"));
        m.setBroj(rs.getInt("broj"));
        m.setDeliveryDate(rs.getDate("delivery_date"));
        m.setIsCopy(rs.getBoolean("is_copy"));
        m.setInvoiceTypeCode(rs.getString("invoice_type_code"));
        int st = rs.getInt("status_id");
        if (rs.wasNull()) {
            m.setStatusId(null);
        } else {
            m.setStatusId(st);
        }
        m.setXmlPath(rs.getString("xml_path"));
        m.setCreatedAt(rs.getTimestamp("created_at"));
        m.setUpdatedAt(rs.getTimestamp("updated_at"));
        return m;
    }
}