/*
 * App: Open Source software for novelists and authors.
 * Original idea 2008 - 2012 Martin Mustun
 * Copyrigth (C) Favdb
 *
 * 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 3 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.
 */
package storybook.model.oldmodel;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import storybook.Const.STORYBOOK;
import storybook.model.H2File;
import storybook.tools.LOG;
import storybook.tools.file.IOUtil;
import storybook.ui.MainFrame;

//@Deprecated
public class ModelMigration {

	private static ModelMigration thePersistenceManager;

	private String databaseName;
	private boolean init;
	private boolean openOnlyIfExists;
	public Connection connection;
	private File file;
	private Statement stmt;
	public MainFrame mainFrame;
	private String oldDbVersion;
	private String newDbVersion;
	private boolean migrated;

	public ModelMigration() {
		// make the constructor private
		init = false;
		connection = null;
		databaseName = null;
	}

	public ModelMigration(MainFrame mainFrame) {
		// make the constructor private
		init = false;
		connection = null;
		databaseName = null;
		this.mainFrame = mainFrame;
	}

	public boolean open(H2File dbFile) {
		//LOG.trace("ModelMigration.open(" + dbFile.getH2Name() + ")");
		this.file = dbFile.getH2File();
		this.databaseName = dbFile.getH2Name();
		this.openOnlyIfExists = true;
		this.init = true;
		try {
			getConnection();
		} catch (Exception e) {
			LOG.err("ModelMigration.open(" + dbFile.getName() + ")", e);
			return (false);
		}
		return (true);
	}

	public static ModelMigration getInstance() {
		//LOG.trace("ModelMigration.getInstance()");
		if (thePersistenceManager == null) {
			thePersistenceManager = new ModelMigration();
		}
		return thePersistenceManager;
	}

	public Connection getConnection() {
		//LOG.trace("ModelMigration.getConnection()");
		if (!init) {
			return null;
		}
		if (connection == null) {
			String connectionStr = "jdbc:h2:" + databaseName;
			if (openOnlyIfExists) {
				connectionStr = connectionStr + ";IFEXISTS=TRUE";
			}
			if (LOG.getTraceHibernate()) {
				connectionStr += ";TRACE_LEVEL_FILE=3;TRACE_LEVEL_SYSTEM_OUT=3";
			} else {
				connectionStr += ";TRACE_LEVEL_FILE=0;TRACE_LEVEL_SYSTEM_OUT=0";
			}
			//LOG.trace("ModelMigration.getConnection() to " + connectionStr);
			try {
				Class.forName("org.h2.Driver");
				connection = DriverManager.getConnection(connectionStr, "sa", "");
			} catch (ClassNotFoundException | SQLException e) {
				LOG.err("ModelMigration.getConnection()", e);
			}
		}
		return connection;
	}

	public void closeConnection() {
		//LOG.trace("ModelMigration.closeConnection()");
		if (isConnectionOpen()) {
			try {
				this.connection.close();
				this.init = false;
				this.connection = null;
				this.databaseName = null;
			} catch (SQLException e) {
				LOG.err("ModelMigration.closeConnection()", e);
			}
		}
	}

	public String getDatabaseName() {
		return databaseName;
	}

	public File getFile() {
		return file;
	}

	public boolean isConnectionOpen() {
		return connection != null;
	}

	/**
	 * Closes the result set
	 *
	 * @param result The ResultSet that needs to close
	 */
	public void closeResultSet(ResultSet result) {
		try {
			if (result != null) {
				result.close();
			}
		} catch (SQLException se) {
			LOG.err("*** ModelMigration.closeResultSet(" + result.toString() + ")", se);
		}
	}

	/**
	 * Closes the prepare statement
	 *
	 * @param prepare The PreparedStatement that needs to close
	 */
	public void closePrepareStatement(PreparedStatement prepare) {
		try {
			if (prepare != null) {
				prepare.close();
			}
		} catch (SQLException se) {
			LOG.err("*** ModelMigration.closePrepareStatement(" + prepare.toString() + ")", se);
		}
	}

	/**
	 * Closes the statement
	 *
	 * @param stmt The Statement that needs to close
	 */
	public void closeStatement(Statement stmt) {
		try {
			if (stmt != null) {
				stmt.close();
			}
		} catch (SQLException se) {
			LOG.err("*** ModelMigration.closeStatement(" + stmt.toString() + ")", se);
		}
	}

	public int getGeneratedId(PreparedStatement stmt) throws SQLException {
		int retour = -1;
		ResultSet rs = null;
		try {
			rs = stmt.getGeneratedKeys();
			int count = 0;
			while (rs.next()) {
				if (count > 0) {
					throw new SQLException("error: got more than one id");
				}
				retour = rs.getInt(1);
				++count;
			}
		} catch (SQLException exc) {
			LOG.err("*** ModelMigration.getGeneratedId(" + stmt.toString() + ")", exc);
		} finally {
			this.closeResultSet(rs);
		}
		return retour;
	}

	public boolean checkAndAlterModel(boolean force) throws Exception {
		//LOG.trace("ModelMigration.checkAndAlterModel()");
		oldDbVersion = InternalPeer.getDbModelVersion();
		if (oldDbVersion == null) {
			//LOG.trace("oldDbVersion is null");
			if (!force) {
				return true;
			}
			oldDbVersion = "4.0";
		}
		newDbVersion = STORYBOOK.DB_VERSION.toString();
		//LOG.trace("actual is " + oldDbVersion + " check for " + newDbVersion);

		if (oldDbVersion.equals(newDbVersion)) {
			// versions matches, nothing to do
			return true;
		}

		// alter models
		stmt = this.getConnection().createStatement();
		// old versions
		String old[] = oldDbVersion.split("\\.");
		Integer oldMajor = Integer.parseInt(old[0]), oldMinor = Integer.parseInt(old[1]);
		if (oldMajor == 0 || (oldMajor == 1 && oldMinor < 5)) {
			throw new Exception(
					"File version too old. Update to the latest version of Storybook 3 first.");
		}
		// backup current file
		String fn = IOUtil.removeExtension(file.getAbsolutePath());
		fn = fn + ".bak";
		File backupFile = new File(fn);
		if (backupFile.exists()) {
			backupFile.delete();
		}
		IOUtil.fileCopy(file, backupFile);
		if (oldMinor == 5) {
			// 1.5 -> 4.0
			alterFrom15to40();
		} else if (oldMajor == 4) {
			this.alterFrom40to50();
		}
		return true;
	}

	private void alterFrom15to40() throws Exception {
		LOG.log("Upgading file version from 1.5 to 4.0 ...");
		String stmts[] = {
			// location
			"alter table LOCATION alter column NAME varchar(256);",
			"alter table LOCATION alter column CITY varchar(256);",
			"alter table LOCATION alter column COUNTRY varchar(256);",
			"alter table LOCATION alter column ADDRESS varchar(256);",
			"alter table LOCATION alter column DESCRIPTION varchar(32768);",
			"alter table LOCATION alter column NOTES varchar(32768);",
			// person
			"alter table PERSON alter column CATEGORY rename to CATEGORY_ID;",
			"alter table PERSON alter column CATEGORY_ID long;",
			"alter table PERSON alter column FIRSTNAME varchar(256);",
			"alter table PERSON alter column LASTNAME varchar(256);",
			"alter table PERSON alter column ABBREVIATION varchar(256);",
			"alter table PERSON alter column OCCUPATION varchar(256);",
			"alter table PERSON alter column DESCRIPTION varchar(32768);",
			"alter table PERSON alter column NOTES varchar(32768);",
			// scene
			"update SCENE set DATE=null where ID in(select ID from SCENE where SCENE.RELATIVE_SCENE_ID!=-1);",
			"alter table SCENE alter column TITLE varchar(2048);",
			"alter table SCENE alter column SUMMARY varchar(32768);",
			"alter table SCENE alter column NOTES varchar(32768);",
			"alter table SCENE alter column RELATIVE_SCENE_ID long;",
			"update SCENE set CHAPTER_ID=null where CHAPTER_ID=-1;",
			"update SCENE set RELATIVE_SCENE_ID=null where RELATIVE_SCENE_ID=-1;",
			"update SCENE set RELATIVE_DATE_DIFFERENCE=null where RELATIVE_DATE_DIFFERENCE=0;",
			"alter table SCENE alter column DATE RENAME to SCENE_TS;",
			"alter table SCENE alter column SCENE_TS timestamp;",
			// chapter
			"alter table CHAPTER alter column TITLE varchar(256);",
			"alter table CHAPTER alter column DESCRIPTION varchar(32768);",
			"alter table CHAPTER alter column NOTES varchar(32768);",
			// gender
			"alter table GENDER alter column NAME varchar(256);",
			// idea
			"alter table IDEAS alter column CATEGORY varchar(256);",
			"alter table IDEAS alter column NOTE varchar(32768);",
			// part
			"alter table PART alter column NAME varchar(256);",
			// strand
			"alter table STRAND alter column NAME varchar(256);",
			"alter table STRAND alter column ABBREVIATION varchar(256);",
			"alter table STRAND alter column NOTES varchar(32768);",
			// tag link
			"update TAG_LINK set START_SCENE_ID=null where START_SCENE_ID=0;",
			"update TAG_LINK set START_SCENE_ID=null where START_SCENE_ID=-1;",
			"update TAG_LINK set END_SCENE_ID=null   where END_SCENE_ID=0;",
			"update TAG_LINK set END_SCENE_ID=null   where END_SCENE_ID=-1;",
			"update TAG_LINK set CHARACTER_ID=null   where CHARACTER_ID=0;",
			"update TAG_LINK set CHARACTER_ID=null   where CHARACTER_ID=-1;",
			"update TAG_LINK set LOCATION_ID=null    where LOCATION_ID=0;",
			"update TAG_LINK set LOCATION_ID=null    where LOCATION_ID=-1;",
			"alter table TAG_LINK add TYPE integer;",
			"update TAG_LINK as TL set TYPE=(SELECT T.TYPE from TAG as T where T.ID=TL.TAG_ID);",
			// person link
			"update PERSON_LINK set START_SCENE_ID=null where START_SCENE_ID=0;",
			"update PERSON_LINK set START_SCENE_ID=null where START_SCENE_ID=-1;",
			"update PERSON_LINK set END_SCENE_ID=null   where END_SCENE_ID=0;",
			"update PERSON_LINK set END_SCENE_ID=null   where END_SCENE_ID=-1;",
			"update PERSON_LINK set PERSION1_ID=null    where CHARACTER_ID=0;",
			"update PERSON_LINK set PERSION2_ID=null    where CHARACTER_ID=-1;",
			"alter table PERSON_LINK add TYPE integer;",
			"update PERSON_LINK as TL set TYPE=(select T.TYPE from PERSON as T where T.ID=TL.PERSON_ID);",
			// category
			"create table CATEGORY(ID bigint primary key not null, SORT integer, NAME varchar(256));",
			"insert into CATEGORY (ID,SORT,NAME) values (1, 1, 'major');",
			"insert into CATEGORY (ID,SORT,NAME) values (2, 2, 'minor');",
			// internal
			"alter table INTERNAL alter column KEY varchar(512);",
			"alter table INTERNAL alter column STRING_VALUE varchar(4096);"
		};
		for (String st : stmts) {
			execStmt(st);
		}
		migrated = true;
	}

	private void alterFrom40to50() {
		LOG.log("Upgrading file version from 4.0 to 5.0 ...");
		if (Migration40to50.exec(this)) {
			migrated = true;
		}
	}

	public boolean isAltered() {
		return (migrated);
	}

	public void execStmt(String sql) {
		LOG.log("execute SQL Statement:\"" + sql + "\"");
		try {
			stmt.execute(sql);
		} catch (SQLException e) {
			LOG.err("SQL error: " + e.getLocalizedMessage());
		}
	}

	public MainFrame getMainFrame() {
		return mainFrame;
	}

	public void setMainFrame(MainFrame mainFrame) {
		this.mainFrame = mainFrame;
	}

	public String getOldDbVersion() {
		return oldDbVersion;
	}

	public String getNewDbVersion() {
		return newDbVersion;
	}

	public boolean hasAlteredDbModel() {
		if (oldDbVersion == null) {
			return false;
		}
		return !oldDbVersion.equals(newDbVersion);
	}

}
