package org.openlca.core.database.upgrades;

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

import org.openlca.core.database.IDatabase;
import org.openlca.core.database.NativeSql;
import org.openlca.core.matrix.LongPair;
import org.openlca.core.matrix.cache.FlowTable;
import org.openlca.core.model.FlowType;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import gnu.trove.impl.Constants;
import gnu.trove.map.hash.TObjectLongHashMap;

/** Upgrades the database to version 5. */
class Upgrade5 implements IUpgrade {

	@Override
	public int[] getInitialVersions() {
		return new int[] { 4 };
	}

	@Override
	public int getEndVersion() {
		return 5;
	}

	@Override
	public void exec(IDatabase db) {
		DbUtil u = new DbUtil(db);
		u.renameColumn("tbl_sources", "doi", "url VARCHAR(255)");
		u.renameColumn("tbl_process_links", "f_recipient", "f_process BIGINT");
		u.createColumn("tbl_process_links", "f_exchange BIGINT");
		updateLinks(db);
	}

	private void updateLinks(IDatabase db) {
		TObjectLongHashMap<LongPair> idx = inputIdx(db);
		try (Connection con = db.createConnection()) {
			con.setAutoCommit(false);
			Statement query = con.createStatement();
			query.setCursorName("UPDATE_LINKS");
			ResultSet cursor = query.executeQuery(
					"SELECT f_process, f_flow FROM tbl_process_links "
							+ "FOR UPDATE of f_exchange");
			PreparedStatement update = con.prepareStatement(
					"UPDATE tbl_process_links SET f_exchange = ? " +
							"WHERE CURRENT OF UPDATE_LINKS");
			while (cursor.next()) {
				long processId = cursor.getLong(1);
				long flowId = cursor.getLong(2);
				long exchangeId = idx.get(LongPair.of(processId, flowId));
				if (exchangeId > 0) {
					update.setLong(1, exchangeId);
					update.executeUpdate();
				}
			}
			cursor.close();
			query.close();
			update.close();
			con.commit();
		} catch (Exception e) {
			throw new RuntimeException("failed to upgrade process links", e);
		}
	}

	private TObjectLongHashMap<LongPair> inputIdx(IDatabase db) {
		TObjectLongHashMap<LongPair> idx = new TObjectLongHashMap<>(
				Constants.DEFAULT_CAPACITY, Constants.DEFAULT_LOAD_FACTOR, -1);
		FlowTable flowTypes = FlowTable.create(db);
		String sql = "SELECT id, f_owner, f_flow, is_input from tbl_exchanges";
		try {
			NativeSql.on(db).query(sql, r -> {
				long exchangeId = r.getLong(1);
				long processId = r.getLong(2);
				long flowId = r.getLong(3);
				boolean isInput = r.getBoolean(4);
				if (!isInput || flowTypes.type(flowId) == FlowType.ELEMENTARY_FLOW)
					return true;
				idx.put(LongPair.of(processId, flowId), exchangeId);
				return true;
			});
		} catch (Exception e) {
			Logger log = LoggerFactory.getLogger(getClass());
			log.error("failed to build exchange index");
		}
		return idx;
	}
}

@@ -376,65 +376,8 @@
 ype;
-%0Aimport org.slf4j.Logger;%0Aimport org.slf4j.LoggerFactory;
 %0A%0Aim
@@ -2013,36 +2013,11 @@
 %7B%0A%09%09
-TObjectLongHashMap%3CLongPair%3E
+var
  idx
@@ -2042,16 +2042,24 @@
 HashMap%3C
+LongPair
 %3E(%0A%09%09%09%09C
@@ -2084,17 +2084,21 @@
 APACITY,
- 
+%0A%09%09%09%09
 Constant
@@ -2123,25 +2123,23 @@
 TOR,
- 
+%0A%09%09%09%09
 -1);%0A%09%09
-FlowTable
+var
  flo
@@ -2157,22 +2157,24 @@
 owTable.
-create
+getTypes
 (db);%0A%09%09
@@ -2249,17 +2249,8 @@
 s%22;%0A
-%09%09try %7B%0A%09
 %09%09Na
@@ -2281,25 +2281,24 @@
 , r -%3E %7B%0A%09%09%09
-%09
 long exchang
@@ -2309,33 +2309,32 @@
 = r.getLong(1);%0A
-%09
 %09%09%09long processI
@@ -2350,25 +2350,24 @@
 Long(2);%0A%09%09%09
-%09
 long flowId 
@@ -2382,17 +2382,16 @@
 ong(3);%0A
-%09
 %09%09%09boole
@@ -2419,25 +2419,24 @@
 lean(4);%0A%09%09%09
-%09
 if (!isInput
@@ -2453,12 +2453,11 @@
 pes.
-type
+get
 (flo
@@ -2490,17 +2490,16 @@
 Y_FLOW)%0A
-%09
 %09%09%09%09retu
@@ -2510,17 +2510,16 @@
 rue;%0A%09%09%09
-%09
 idx.put(
@@ -2563,17 +2563,16 @@
 ngeId);%0A
-%09
 %09%09%09retur
@@ -2585,144 +2585,12 @@
 ;%0A%09%09
-%09
 %7D);%0A
-%09%09%7D catch (Exception e) %7B%0A%09%09%09Logger log = LoggerFactory.getLogger(getClass());%0A%09%09%09log.error(%22failed to build exchange index%22);%0A%09%09%7D%0A
 %09%09re

fix failing upgrade 5