/*
 *  Copyright (C) 2006-2024  Ronald Blankendaal
 *
 *  This program is free software; you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation; either version 2 of the License, or
 *  (at your option) any later version.
 *
 *  This program is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  You should have received a copy of the GNU General Public License
 *  along with this program; if not, write to the Free Software
 *  Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
 */
package org.dbgl.app;

import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import javax.xml.bind.DatatypeConverter;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.text.similarity.LevenshteinDistance;
import org.dbgl.model.FileLocation;
import org.dbgl.model.conf.Settings;
import org.dbgl.model.repository.GameFilesRepository.FileData;
import org.dbgl.model.repository.GameFilesRepository.FileSet;
import org.dbgl.model.repository.GameFilesRepository.FileType;
import org.dbgl.model.repository.GameFilesRepository.GameData;
import org.dbgl.service.ITextService;
import org.dbgl.service.MetropolisDatabaseService;
import org.dbgl.service.TextService;


public class SetupGameMD5s {

	private static final ITextService TEXT = TextService.getInstance();

	private static final String IDENTITY_QRY = "CALL IDENTITY()";

	private static final String CREATE_TBL_FILENAMES_QRY = "CREATE CACHED TABLE FILENAMES(" + "ID_FILENAME INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"
			+ "NAME VARCHAR(12) NOT NULL)";
	private static final String CREATE_TBL_FILE_MD5S_QRY = "CREATE CACHED TABLE FILE_MD5S(" + "ID_FILE_MD5 INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"
			+ "ID_FILENAME INTEGER NOT NULL," + "MD5 BINARY(16) NOT NULL," + "CONSTRAINT SYS_FK_FILENAME FOREIGN KEY(ID_FILENAME) REFERENCES FILENAMES(ID_FILENAME))";
	private static final String CREATE_TBL_RELEASE_MD5S_QRY = "CREATE CACHED TABLE RELEASE_MD5S(" + "ID_GAME_MD5 INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"
			+ "ID_MOBY_RELEASES INTEGER NOT NULL," + "ID_FILE_SET INTEGER NOT NULL," + "ID_FILE_TYPE INTEGER NOT NULL," + "ID_FILE_MD5 INTEGER NOT NULL,"
			+ "CONSTRAINT SYS_FK_GAME FOREIGN KEY(ID_MOBY_RELEASES) REFERENCES tbl_Moby_Releases(id_Moby_Releases),"
			+ "CONSTRAINT SYS_FK_FILEMD5 FOREIGN KEY(ID_FILE_MD5) REFERENCES FILE_MD5S(ID_FILE_MD5))";

	private static final String GET_FILE_WITH_MD5_QRY = "SELECT FILE_MD5S.ID_FILE_MD5 FROM FILE_MD5S, FILENAMES WHERE FILE_MD5S.ID_FILENAME=FILENAMES.ID_FILENAME "
			+ "AND FILENAMES.NAME=? AND FILE_MD5S.MD5=?";
	private static final String GET_GAMES_QRY = "SELECT Game.id_Moby_Games, Game.Name FROM tbl_Moby_Games Game";
	private static final String GET_ALT_TITLES_QRY = "SELECT Alt.id_Moby_Games, Alt.Alternate_Title FROM tbl_Moby_Games_Alternate_Titles Alt";
	private static final String GET_RELEASE_QRY = "SELECT Rel.id_Moby_Releases FROM TBL_MOBY_RELEASES AS Rel, TBL_MOBY_PLATFORMS AS Pla "
			+ "WHERE Rel.id_Moby_Platforms = Pla.id_Moby_Platforms AND Pla.Name = 'DOS' AND Rel.id_Moby_Games = ?";

	private static final String INSERT_FILENAME_QRY = "INSERT INTO FILENAMES(NAME) VALUES(?)";
	private static final String INSERT_FILE_MD5_QRY = "INSERT INTO FILE_MD5S(ID_FILENAME, MD5) VALUES(?, ?)";
	private static final String INSERT_RELEASE_MD5_QRY = "INSERT INTO RELEASE_MD5S(ID_MOBY_RELEASES, ID_FILE_SET, ID_FILE_TYPE, ID_FILE_MD5) VALUES(?, ?, ?, ?)";

	public static void main(String[] args) {
		System.out.println("Setup Game MD5s (v0.1)");
		System.out.println();

		MetropolisDatabaseService mobyService = MetropolisDatabaseService.getInstance();

		try {
			try (Connection con = mobyService.getConnection(); Statement stmt = con.createStatement()) {
				stmt.executeUpdate(CREATE_TBL_FILENAMES_QRY);
				stmt.executeUpdate(CREATE_TBL_FILE_MD5S_QRY);
				stmt.executeUpdate(CREATE_TBL_RELEASE_MD5S_QRY);
			}

			List<Integer> gameIds = new ArrayList<>();
			List<String> gameNames = new ArrayList<>();
			try (Connection con = mobyService.getConnection(); Statement stmt = con.createStatement(); ResultSet resultset = stmt.executeQuery(GET_GAMES_QRY)) {
				while (resultset.next()) {
					gameIds.add(resultset.getInt(1));
					gameNames.add(resultset.getString(2));
				}
			}

			List<Integer> altGameIds = new ArrayList<>();
			List<String> altGameNames = new ArrayList<>();
			try (Connection con = mobyService.getConnection(); Statement stmt = con.createStatement(); ResultSet resultset = stmt.executeQuery(GET_ALT_TITLES_QRY)) {
				while (resultset.next()) {
					altGameIds.add(resultset.getInt(1));
					altGameNames.add(resultset.getString(2));
				}
			}

			File autosetupTemplatesDir = new File("src/test/D-Fend Reloaded/CleanedUpAutoSetup");
			File[] autosetupTemplates = autosetupTemplatesDir.listFiles();

			Map<String, GameData> gameFileMd5s = new LinkedHashMap<>();

			for (File template: autosetupTemplates) {
				if (template.getName().equalsIgnoreCase("Cache.dfr"))
					continue;

				Settings prof = new Settings();
				prof.setFileLocation(new FileLocation(template.getPath()));

				System.out.print(prof.load(TEXT));

				String name = prof.getValue("ExtraInfo", "name");

				Map<String, FileData> fileMd5s = new LinkedHashMap<>();

				String exe = prof.getValue("Extra", "exe");
				String exeMd5Hash = prof.getValue("Extra", "exemd5");
				if (StringUtils.isNoneBlank(exe, exeMd5Hash))
					fileMd5s.put(new File(exe).getName().toUpperCase(), new FileData(FileType.MAIN, DatatypeConverter.parseHexBinary(exeMd5Hash)));

				String setup = prof.getValue("Extra", "setup");
				String setupMd5Hash = prof.getValue("Extra", "setupmd5");
				if (StringUtils.isNoneBlank(setup, setupMd5Hash))
					fileMd5s.put(new File(setup).getName().toUpperCase(), new FileData(FileType.SETUP, DatatypeConverter.parseHexBinary(setupMd5Hash)));

				for (int i = 1; i < 6; i++) {
					String extra = prof.getValue("ExtraGameIdentify", "file" + i + ".filename");
					String extraMd5Hash = prof.getValue("ExtraGameIdentify", "file" + i + ".checksum");
					if (StringUtils.isNoneBlank(extra, extraMd5Hash))
						fileMd5s.put(new File(extra).getName().toUpperCase(), new FileData(FileType.EXTRA, DatatypeConverter.parseHexBinary(extraMd5Hash)));
				}

				if (gameFileMd5s.containsKey(name)) {
					gameFileMd5s.get(name).fileSets_.add(new FileSet(fileMd5s));
				} else {
					GameData gameData = new GameData();
					gameData.fileSets_.add(new FileSet(fileMd5s));
					gameFileMd5s.put(name, gameData);
				}
			}

			try (Connection con = mobyService.getConnection()) {
				for (Entry<String, GameData> entry: gameFileMd5s.entrySet()) {
					int idxGame = findBestMatchIndex(entry.getKey(), gameNames);
					int idxAltTitle = findBestMatchIndex(entry.getKey(), altGameNames);
					int distanceGame = LevenshteinDistance.getDefaultInstance().apply(entry.getKey(), gameNames.get(idxGame));
					int distanceAltTitle = LevenshteinDistance.getDefaultInstance().apply(entry.getKey(), altGameNames.get(idxAltTitle));

					entry.getValue().webProfile_.setGameId(distanceGame < distanceAltTitle ? gameIds.get(idxGame): altGameIds.get(idxAltTitle));

					try (PreparedStatement pstmt = con.prepareStatement(GET_RELEASE_QRY)) {
						pstmt.setInt(1, entry.getValue().webProfile_.getGameId());
						try (ResultSet resultset = pstmt.executeQuery()) {
							while (resultset.next())
								entry.getValue().webProfile_.setReleaseId(resultset.getInt(1));
						}
					}
				}
			}

			List<String> filenames = gameFileMd5s.entrySet().stream().flatMap(x -> x.getValue().fileSets_.stream().flatMap(y -> y.set_.keySet().stream())).distinct().sorted().toList();

			try (Connection con = mobyService.getConnection()) {
				for (String filename: filenames) {
					try (PreparedStatement pstmt = con.prepareStatement(INSERT_FILENAME_QRY)) {
						pstmt.setString(1, filename);
						pstmt.executeUpdate();
					}
				}

				for (GameData gameData: gameFileMd5s.values()) {
					for (int filesSetIndex = 0; filesSetIndex < gameData.fileSets_.size(); filesSetIndex++) {
						Map<String, FileData> fileSet = gameData.fileSets_.get(filesSetIndex).set_;
						for (Entry<String, FileData> file: fileSet.entrySet()) {
							byte[] md5 = file.getValue().md5_;
							int fileWithMd5Id = getFileWithMd5Id(con, file.getKey(), md5);
							if (fileWithMd5Id == -1) {
								try (PreparedStatement pstmt = con.prepareStatement(INSERT_FILE_MD5_QRY)) {
									pstmt.setInt(1, filenames.indexOf(file.getKey()));
									pstmt.setBytes(2, md5);
									pstmt.executeUpdate();
									fileWithMd5Id = identity(con);
								}
							}
							try (PreparedStatement pstmt = con.prepareStatement(INSERT_RELEASE_MD5_QRY)) {
								pstmt.setInt(1, gameData.webProfile_.getReleaseId());
								pstmt.setInt(2, filesSetIndex);
								pstmt.setInt(3, file.getValue().type_.ordinal());
								pstmt.setInt(4, fileWithMd5Id);
								pstmt.executeUpdate();
							}
						}
					}
				}
			}

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

		System.out.println("Finished.");
		try {
			mobyService.shutdown(false);
		} catch (SQLException e) {
			// nothing we can do
		}
	}

	private static int getFileWithMd5Id(Connection con, String filename, byte[] md5) throws SQLException {
		int fileWithMd5Id = -1;
		try (PreparedStatement pstmt = con.prepareStatement(GET_FILE_WITH_MD5_QRY)) {
			pstmt.setString(1, filename);
			pstmt.setBytes(2, md5);
			try (ResultSet resultset = pstmt.executeQuery()) {
				while (resultset.next())
					fileWithMd5Id = resultset.getInt(1);
			}
		}
		return fileWithMd5Id;
	}

	private static int identity(Connection con) throws SQLException {
		try (Statement stmt = con.createStatement(); ResultSet resultset = stmt.executeQuery(IDENTITY_QRY)) {
			resultset.next();
			return resultset.getInt(1);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SQLException(TextService.getInstance().get("database.error.query", new Object[] {"get identity"}));
		}
	}

	private static int findBestMatchIndex(String search, List<String> titles) {
		int minDistance = Integer.MAX_VALUE;
		int result = 0;
		for (int i = 0; i < titles.size(); i++) {
			String title = titles.get(i);
			int distance = (i == 0) ? LevenshteinDistance.getDefaultInstance().apply(search, title): new LevenshteinDistance(minDistance - 1).apply(search, title);
			if (distance == 0)
				return i;
			if (distance != -1) {
				minDistance = distance;
				result = i;
			}
		}
		return result;
	}
}
