package cashfree;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import main_package.DataBaseConnection;

public class SettlementRepository {
	
	
	
	public static Settlement findBySettlementId(Long settlementId) {

	    String query = "SELECT * FROM cashfree_settlement WHERE settlement_id = ?";

	    try (Connection conn = DataBaseConnection.JAVAConnection();
	         PreparedStatement ps = conn.prepareStatement(query)) {

	        ps.setLong(1, settlementId);

	        try (ResultSet rs = ps.executeQuery()) {

	            if (rs.next()) {

	                Settlement settlement = new Settlement();

	                settlement.setId(rs.getLong("id"));
	                settlement.setSettlementId(rs.getLong("settlement_id"));
	                settlement.setSettlementType(rs.getString("settlement_type"));
	                settlement.setStatus(rs.getString("status"));

	                settlement.setPaymentAmount(
	                        rs.getBigDecimal("payment_amount"));

	                settlement.setAmountSettled(
	                        rs.getBigDecimal("amount_settled"));

	                settlement.setSettlementAmount(
	                        rs.getBigDecimal("settlement_amount"));

	                settlement.setAdjustment(
	                        rs.getBigDecimal("adjustment"));

	                settlement.setServiceCharge(
	                        rs.getBigDecimal("service_charge"));

	                settlement.setServiceTax(
	                        rs.getBigDecimal("service_tax"));

	                settlement.setSettlementCharge(
	                        rs.getBigDecimal("settlement_charge"));

	                settlement.setSettlementTax(
	                        rs.getBigDecimal("settlement_tax"));

	                settlement.setPaymentFrom(
	                        rs.getTimestamp("payment_from"));

	                settlement.setPaymentTill(
	                        rs.getTimestamp("payment_till"));

	                settlement.setSettlementInitiatedOn(
	                        rs.getTimestamp("settlement_initiated_on"));

	                settlement.setSettledOn(
	                        rs.getTimestamp("settled_on"));

	                settlement.setUtr(
	                        rs.getString("utr"));

	                settlement.setReason(
	                        rs.getString("reason"));

	                settlement.setRemarks(
	                        rs.getString("remarks"));

	                settlement.setForexConversionHandlingCharge(
	                        rs.getBigDecimal(
	                                "forex_conversion_handling_charge"));

	                settlement.setForexConversionHandlingTax(
	                        rs.getBigDecimal(
	                                "forex_conversion_handling_tax"));

	                settlement.setForexConversionRate(
	                        rs.getBigDecimal(
	                                "forex_conversion_rate"));

	                settlement.setChargesCurrency(
	                        rs.getString("charges_currency"));

	                // Cashfree webhook information
	                settlement.setEventType(
	                        rs.getString("event_type"));

	                settlement.setEventTime(
	                        rs.getTimestamp("event_time"));

	                // Application processing flags
	                settlement.setBankTransferEntryDone(
	                        rs.getBoolean("bank_transfer_entry_done"));

	                settlement.setExpenseEntryDone(
	                        rs.getBoolean("expense_entry_done"));

	                // Raw Cashfree response
	                settlement.setRawResponse(
	                        rs.getString("raw_response"));

	                // Database timestamps
	                settlement.setCreatedAt(
	                        rs.getTimestamp("created_at"));

	                settlement.setUpdatedAt(
	                        rs.getTimestamp("updated_at"));

	                return settlement;
	            }
	        }

	    } catch (Exception e) {
	        e.printStackTrace();
	    }

	    return null;
	}
	
	public static boolean insertSettlement(Settlement settlement) {

	    String query = "INSERT INTO cashfree_settlement ("
	            + "settlement_id, settlement_type, status, "
	            + "payment_amount, amount_settled, settlement_amount, adjustment, "
	            + "service_charge, service_tax, settlement_charge, settlement_tax, "
	            + "payment_from, payment_till, settlement_initiated_on, settled_on, "
	            + "utr, reason, remarks, "
	            + "forex_conversion_handling_charge, forex_conversion_handling_tax, "
	            + "forex_conversion_rate, charges_currency, "
	            + "event_type, event_time, "
	            + "bank_transfer_entry_done, expense_entry_done, raw_response"
	            + ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

	    try (Connection conn = DataBaseConnection.JAVAConnection();
	         PreparedStatement ps = conn.prepareStatement(query)) {

	        ps.setLong(1, settlement.getSettlementId());
	        ps.setString(2, settlement.getSettlementType());
	        ps.setString(3, settlement.getStatus());

	        ps.setBigDecimal(4, settlement.getPaymentAmount());
	        ps.setBigDecimal(5, settlement.getAmountSettled());
	        ps.setBigDecimal(6, settlement.getSettlementAmount());
	        ps.setBigDecimal(7, settlement.getAdjustment());

	        ps.setBigDecimal(8, settlement.getServiceCharge());
	        ps.setBigDecimal(9, settlement.getServiceTax());
	        ps.setBigDecimal(10, settlement.getSettlementCharge());
	        ps.setBigDecimal(11, settlement.getSettlementTax());

	        ps.setTimestamp(12, settlement.getPaymentFrom());
	        ps.setTimestamp(13, settlement.getPaymentTill());
	        ps.setTimestamp(14, settlement.getSettlementInitiatedOn());
	        ps.setTimestamp(15, settlement.getSettledOn());

	        ps.setString(16, settlement.getUtr());
	        ps.setString(17, settlement.getReason());
	        ps.setString(18, settlement.getRemarks());

	        ps.setBigDecimal(19, settlement.getForexConversionHandlingCharge());
	        ps.setBigDecimal(20, settlement.getForexConversionHandlingTax());
	        ps.setBigDecimal(21, settlement.getForexConversionRate());
	        ps.setString(22, settlement.getChargesCurrency());

	        ps.setString(23, settlement.getEventType());
	        ps.setTimestamp(24, settlement.getEventTime());

	        ps.setBoolean(25, settlement.isBankTransferEntryDone());
	        ps.setBoolean(26, settlement.isExpenseEntryDone());

	        ps.setString(27, settlement.getRawResponse());

	        return ps.executeUpdate() > 0;

	    } catch (Exception e) {
	        e.printStackTrace();
	    }

	    return false;
	}
	
	public static boolean updateBankTransferEntryDone(Long settlementId, boolean status) {

	    String query = "UPDATE cashfree_settlement "
	            + "SET bank_transfer_entry_done = ? "
	            + "WHERE settlement_id = ?";

	    try (Connection conn = DataBaseConnection.JAVAConnection();
	         PreparedStatement ps = conn.prepareStatement(query)) {

	        ps.setBoolean(1, status);
	        ps.setLong(2, settlementId);

	        return ps.executeUpdate() > 0;

	    } catch (Exception e) {
	        e.printStackTrace();
	    }

	    return false;
	}
	
	public boolean updateExpenseEntryDone(Long settlementId, boolean status) {

	    String query = "UPDATE cashfree_settlement "
	            + "SET expense_entry_done = ? "
	            + "WHERE settlement_id = ?";

	    try (Connection conn = DataBaseConnection.JAVAConnection();
	         PreparedStatement ps = conn.prepareStatement(query)) {

	        ps.setBoolean(1, status);
	        ps.setLong(2, settlementId);

	        return ps.executeUpdate() > 0;

	    } catch (Exception e) {
	        e.printStackTrace();
	    }

	    return false;
	}
	
	


}
