package eu.dnetlib.goldoa.service.dao;

import eu.dnetlib.goldoa.domain.BankAccount;
import eu.dnetlib.goldoa.domain.Budget;
import eu.dnetlib.goldoa.domain.Comment;
import eu.dnetlib.goldoa.domain.Currency;
import eu.dnetlib.goldoa.domain.Person;
import org.apache.commons.codec.digest.DigestUtils;
import org.apache.commons.io.IOUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;

import javax.sql.DataSource;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Date;
import java.util.List;
import java.util.UUID;

/**
 * Created by antleb on 4/2/15.
 */
public class BudgetDAO {

	@Autowired
	private DataSource dataSource;

	private static final String GET_BUDGET = "select id, date, startdate, \"enddate\", amount_requested, amount_granted, remaining, currency, status, bank_name, bank_address, bank_code, bank_holder, bank_iban, \"user\", transfer_cost, other_cost, date_paid, organization, publisher, invoice from budget b where id=?";
	private static final String GET_BUDGETS = "select id, date, startdate, \"enddate\", amount_requested, amount_granted, remaining, currency, status, bank_name, bank_address, bank_code, bank_holder, bank_iban, \"user\", transfer_cost, other_cost, date_paid, organization, publisher, invoice from budget b order by date";
	private static final String UPDATE_BUDGET = "update budget set date=?, startdate=?, \"enddate\"=?, amount_requested=?, amount_granted=?, remaining=?, currency=?, status=?, \"user\"=?, bank_name=?, bank_address=?, bank_code=?, bank_holder=?, bank_iban=?, transfer_cost=?, other_cost=?, date_paid=?, organization=?, publisher=?, invoice=? where id=?";
	private static final String INSERT_BUDGET = "insert into budget (date, startdate, \"enddate\", amount_requested, amount_granted, remaining, currency, status, \"user\", bank_name, bank_address, bank_code, bank_holder, bank_iban, transfer_cost, other_cost, date_paid, organization, publisher, invoice, id) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

	private static final String GET_USER_BUDGETS = "select id, date, startdate, \"enddate\", amount_requested, amount_granted, remaining, currency, status, bank_name, bank_address, bank_code, bank_holder, bank_iban, \"user\", transfer_cost, other_cost, date_paid, organization, publisher, invoice from budget b where \"user\"=? order by date";
	private static final String GET_ORGANIZATION_BUDGETS = "select id, date, startdate, \"enddate\", amount_requested, amount_granted, remaining, currency, status, bank_name, bank_address, bank_code, bank_holder, bank_iban, \"user\", transfer_cost, other_cost, date_paid, organization, publisher, invoice from budget b where array[organization]::text[] <@ ?::text[] order by date";
	private static final String GET_PUBLISHER_BUDGETS = "select id, date, startdate, \"enddate\", amount_requested, amount_granted, remaining, currency, status, bank_name, bank_address, bank_code, bank_holder, bank_iban, \"user\", transfer_cost, other_cost, date_paid, organization, publisher, invoice from budget b where publisher=? order by date";
	private static final String GET_ACCOUNTING_BUDGETS = "select id, date, startdate, \"enddate\", amount_requested, amount_granted, remaining, currency, status, bank_name, bank_address, bank_code, bank_holder, bank_iban, \"user\", transfer_cost, other_cost, date_paid, organization, publisher, invoice from budget b order by date";
	private static final String APPROVE_BUDGET = "update budget set status = ?, amount_granted=?, remaining=? where id=?";
	private static final String UPDATE_BUDGET_STATUS = "update budget set status = ?  where id=?";

	private static final String UPDATE_BUDGET_TERMS = "update budget set termsmimetype=?, termsfile=? where id=?";
	private static final String INSERT_BUDGET_TERMS = "insert into budget (termsmimetype, termsfile, id) values (?,?,?)";
//	private static final String GET_BUDGET_FILE = "select termsmimetype, termsfile from budget where id=?";

	private RowMapper<Budget> budgetRowMapper = new RowMapper<Budget>() {
		@Override
		public Budget mapRow(ResultSet rs, int rowNum) throws SQLException {
			BankAccount bankAccount = new BankAccount(rs.getString("bank_name"), rs.getString("bank_address"), rs.getString("bank_code"),
					rs.getString("bank_holder"), rs.getString("bank_iban"));


			Budget budget = new Budget(rs.getString("id"),  rs.getTimestamp("date"), rs.getTimestamp("startdate"),
					rs.getTimestamp("enddate"), rs.getFloat("amount_requested"), rs.getFloat("amount_granted"),
					Currency.valueOf(rs.getString("currency")), rs.getFloat("remaining"), rs.getInt("status"),
					rs.getString("user"), rs.getString("organization"), rs.getString("publisher"), bankAccount,
					rs.getFloat("transfer_cost"), rs.getFloat("other_cost"), rs.getTimestamp("date_paid"), rs.getString("invoice"));

			rs.getFloat("amount_requested");
			if (rs.wasNull())
				budget.setAmountRequested(null);
			rs.getFloat("amount_granted");
			if (rs.wasNull())
				budget.setAmountGranted(null);
			rs.getFloat("remaining");
			if (rs.wasNull())
				budget.setRemaining(null);
			rs.getFloat("transfer_cost");
			if (rs.wasNull())
				budget.setTransfer_cost(null);
			rs.getFloat("other_cost");
			if (rs.wasNull())
				budget.setOther_cost(null);

			return budget;
		}
	};

	public Budget saveBudget(Budget budget) {
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		BankAccount bankAccount = budget.getBankAccount() != null ? budget.getBankAccount() : new BankAccount();

		Object[] args = new Object[]{budget.getDate(), budget.getStartDate(), budget.getEndDate(),
				budget.getAmountRequested(), budget.getAmountGranted(), budget.getRemaining(), budget.getCurrency().name(),
				budget.getStatusCode(), budget.getUser(), bankAccount.getBankName(), bankAccount.getBankAddress(),
				bankAccount.getBankCode(), bankAccount.getAccountHolder(), bankAccount.getIban(), budget.getTransfer_cost(),
				budget.getOther_cost(), budget.getDatePaid(), budget.getOrganisation(), budget.getPublisher(), budget.getInvoice(), budget.getId()};
		int[] types = new int[]{Types.TIMESTAMP, Types.TIMESTAMP, Types.TIMESTAMP, Types.REAL, Types.REAL, Types.REAL,
				Types.VARCHAR, Types.INTEGER, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
				Types.VARCHAR, Types.REAL, Types.REAL, Types.TIMESTAMP, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR};

		if (jdbcTemplate.update(UPDATE_BUDGET, args, types) == 0) {
			jdbcTemplate.update(INSERT_BUDGET, args, types);
		}

		return budget;
	}

	public Budget getBudget(String budgetId) {
		return new JdbcTemplate(dataSource).queryForObject(GET_BUDGET, new String[]{budgetId}, new int[]{Types.VARCHAR}, budgetRowMapper);
	}

	public List<Budget> getBudgets() {
		return new JdbcTemplate(dataSource).query(GET_BUDGETS, budgetRowMapper);
	}

	public List<Budget> getBudgetsForUser(String userId) {
		return new JdbcTemplate(dataSource).query(GET_USER_BUDGETS, new String[]{userId}, new int[]{Types.VARCHAR}, budgetRowMapper);
	}

	public List<Budget> getBudgetsForOrganization(final List<String> organizationIds) {
		return new JdbcTemplate(dataSource).query(new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
				PreparedStatement ps = con.prepareStatement(GET_ORGANIZATION_BUDGETS);

				ps.setArray(1, con.createArrayOf("text", organizationIds.toArray()));

				return ps;
			}
		}, budgetRowMapper);
	}

	public List<Budget> getBudgetsForPublisher(String publisherId) {
		return new JdbcTemplate(dataSource).query(GET_PUBLISHER_BUDGETS, new Object[]{publisherId}, new int[]{Types.VARCHAR}, budgetRowMapper);
	}

	public List<Budget> getBudgetsForAccounting() {
		return new JdbcTemplate(dataSource).query(GET_ACCOUNTING_BUDGETS, budgetRowMapper);
	}


	public void initiallyApproveBudget(String budgetId) {
		new JdbcTemplate(dataSource).update(UPDATE_BUDGET_STATUS, new Object[]{Budget.Status.INITIALLY_APPROVED.getCode(), budgetId}, new int[]{Types.INTEGER, Types.VARCHAR});
	}

	public void approveBudget(String budgetId, float amountGranted) {
		new JdbcTemplate(dataSource).update(APPROVE_BUDGET, new Object[]{Budget.Status.APPROVED.getCode(), amountGranted, amountGranted, budgetId}, new int[]{Types.INTEGER, Types.REAL, Types.REAL, Types.VARCHAR});
	}

	public void rejectBudget(String budgetId) {
		new JdbcTemplate(dataSource).update(UPDATE_BUDGET_STATUS, new Object[]{Budget.Status.REJECTED.getCode(), budgetId}, new int[]{Types.INTEGER, Types.VARCHAR});
	}

	public void processingBudget(String budgetId) {
		new JdbcTemplate(dataSource).update(UPDATE_BUDGET_STATUS, new Object[]{Budget.Status.ACCOUNTING_PROCESSING.getCode(), budgetId}, new int[]{Types.INTEGER, Types.VARCHAR});
	}

	public void accountingDeniedBudget(String budgetId) {
		new JdbcTemplate(dataSource).update(UPDATE_BUDGET_STATUS, new Object[]{Budget.Status.ACCOUNTING_DENIED.getCode(), budgetId}, new int[]{Types.INTEGER, Types.VARCHAR});
	}

	public void accountingPaidBudget(String budgetId) {
		new JdbcTemplate(dataSource).update(UPDATE_BUDGET_STATUS, new Object[]{Budget.Status.ACCOUNTING_PAID.getCode(), budgetId}, new int[]{Types.INTEGER, Types.VARCHAR});
	}

	public void accountingOnHoldBudget(String budgetId) {
		new JdbcTemplate(dataSource).update(UPDATE_BUDGET_STATUS, new Object[]{Budget.Status.ACCOUNTING_ONHOLD.getCode(), budgetId}, new int[]{Types.INTEGER, Types.VARCHAR});
	}

	public void uploadTerms(final String id, final String contentType, InputStream inputStream) {
		try {
			final ByteArrayOutputStream baos = new ByteArrayOutputStream();
			final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

			IOUtils.copy(inputStream, baos);
			IOUtils.closeQuietly(baos);

			final PreparedStatementSetter pss = new PreparedStatementSetter() {
				@Override
				public void setValues(PreparedStatement ps) throws SQLException {
					ps.setString(1, contentType);
					ps.setBytes(2, baos.toByteArray());
					ps.setString(3, id);
				}
			};

			if (jdbcTemplate.update(UPDATE_BUDGET_TERMS, pss) == 0) {
				jdbcTemplate.update(INSERT_BUDGET_TERMS, pss);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void saveComment(String budgetId, String personId, String comment, String template) {
		String commentId = "portal::" + DigestUtils.md5Hex(comment + new Date());

		new JdbcTemplate(dataSource).update("with comm as ( insert into comment (id, person, comment, date, template) values (?, ?, ?, ?, ?) returning id) insert into budget_comment (budget, comment) select ?, comm.id from comm",
				new Object[]{commentId, personId, comment, new Date(), template, budgetId}, new int[]{Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.TIMESTAMP, Types.VARCHAR, Types.VARCHAR});
	}

	public List<Comment> getBudgetComments(String budgetId) {
		try {
			return new JdbcTemplate(dataSource).query("select c.comment, c.date, c.person, c.template from comment c join budget_comment bc on bc.comment=c.id and bc.budget=? order by c.date asc", new String[]{budgetId}, new int[]{Types.VARCHAR}, new RowMapper<Comment>() {
				@Override
				public Comment mapRow(ResultSet rs, int rowNum) throws SQLException {
					return new Comment(new Person(rs.getString("person")), rs.getTimestamp("date"), rs.getString("comment"), rs.getString("template"));
				}
			});
		} catch (EmptyResultDataAccessException e) {
			return null;
		}
	}

	public void uploadBankReceipt(final String budgetId, final String contentType, InputStream inputStream) {
		try {
			final ByteArrayOutputStream baos = new ByteArrayOutputStream();
			final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

			IOUtils.copy(inputStream, baos);
			IOUtils.closeQuietly(baos);

			final PreparedStatementSetter pss = new PreparedStatementSetter() {
				@Override
				public void setValues(PreparedStatement ps) throws SQLException {
					ps.setString(1, contentType);
					ps.setBytes(2, baos.toByteArray());
					ps.setString(3, budgetId);
				}
			};

			jdbcTemplate.update("update budget set bank_receipt_contenttype=?, bank_receipt=? where id=?", pss);

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void uploadInitialContract(final String budgetId, final String contentType, InputStream inputStream) {

		try {
			final ByteArrayOutputStream baos = new ByteArrayOutputStream();
			final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
			final String id = "portal::" + UUID.randomUUID().toString();

			IOUtils.copy(inputStream, baos);
			IOUtils.closeQuietly(baos);

			final PreparedStatementSetter pss = new PreparedStatementSetter() {
				@Override
				public void setValues(PreparedStatement ps) throws SQLException {
					ps.setString(1, id);
					ps.setString(2, contentType);
					ps.setBytes(3, baos.toByteArray());
					ps.setString(4, budgetId);
				}
			};

			jdbcTemplate.update("with con as (insert into budgetfile (id, contenttype, content) values (?,?,?) returning id) update budget b set initialcontractfile=con.id where b.id=?", pss);

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void uploadBeneficiaryContract(final String budgetId, final String contentType, InputStream inputStream) {
		try {
			final ByteArrayOutputStream baos = new ByteArrayOutputStream();
			final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
			final String id = "portal::" + UUID.randomUUID().toString();

			IOUtils.copy(inputStream, baos);
			IOUtils.closeQuietly(baos);

			final PreparedStatementSetter pss = new PreparedStatementSetter() {
				@Override
				public void setValues(PreparedStatement ps) throws SQLException {
					ps.setString(1, id);
					ps.setString(2, contentType);
					ps.setBytes(3, baos.toByteArray());
					ps.setString(4, budgetId);
				}
			};

			jdbcTemplate.update("with con as (insert into budgetfile (id, contenttype, content) values (?,?,?) returning id) update budget b set beneficiarysignedcontract=con.id where b.id=?", pss);

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void uploadSignedContract(final String budgetId, final String contentType, InputStream inputStream) {
		try {
			final ByteArrayOutputStream baos = new ByteArrayOutputStream();
			final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
			final String id = "portal::" + UUID.randomUUID().toString();

			IOUtils.copy(inputStream, baos);
			IOUtils.closeQuietly(baos);

			final PreparedStatementSetter pss = new PreparedStatementSetter() {
				@Override
				public void setValues(PreparedStatement ps) throws SQLException {
					ps.setString(1, id);
					ps.setString(2, contentType);
					ps.setBytes(3, baos.toByteArray());
					ps.setString(4, budgetId);
				}
			};

			jdbcTemplate.update("with con as (insert into budgetfile (id, contenttype, content) values (?,?,?) returning id) update budget b set signedcontract=con.id where b.id=?", pss);

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public int getRequestId() {
		return new JdbcTemplate(dataSource).queryForObject("select nextval('budget_id_seq') as id", new RowMapper<Integer>() {
			@Override
			public Integer mapRow(ResultSet rs, int rowNum) throws SQLException {
				return rs.getInt("id");
			}
		});
	}
}