// ============================================================================
//  Copyright (C) 2001-2014 by Jozef Starosczyk
//  ixen@copyhandler.com
//
//  This program is free software; you can redistribute it and/or modify
//  it under the terms of the GNU Library General Public License
//  (version 2) as published by the Free Software Foundation;
//
//  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 Library 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.
// ============================================================================
#include "stdafx.h"
#include "TSQLiteTaskSchema.h"
#include "TSQLiteTransaction.h"
#include "TSerializerVersion.h"
#include "TSQLiteStatement.h"
#include <tchar.h>

namespace serializer
{
	TSQLiteTaskSchema::TSQLiteTaskSchema()
	{
	}

	TSQLiteTaskSchema::~TSQLiteTaskSchema()
	{
	}

	void TSQLiteTaskSchema::Setup(const sqlite::TSQLiteDatabasePtr& spDatabase)
	{
		sqlite::TSQLiteTransaction tTransaction(spDatabase);

		// check version of the database
		TSerializerVersion tVersion(spDatabase);

		// if version is 0, then this is the fresh database with (almost) no tables inside
		// that's why the initialization always creates the latest version of database, whithout going
		// through all the intermediate migrations
		if (tVersion.GetVersion() == 0)
			CreateNewDatabase(spDatabase, tVersion);
		else
		{
			// migrate current database to the newest version
			if (tVersion.GetVersion() == 1)
				Migrate_001_002(spDatabase, tVersion);

			if (tVersion.GetVersion() == 2)
				Migrate_002_003(spDatabase, tVersion);

			if (tVersion.GetVersion() == 3)
				Migrate_003_004(spDatabase, tVersion);

			if(tVersion.GetVersion() == 4)
				Migrate_004_005(spDatabase, tVersion);

			if(tVersion.GetVersion() == 5)
				Migrate_005_006(spDatabase, tVersion);
				
			if(tVersion.GetVersion() == 6)
				Migrate_006_007(spDatabase, tVersion);
		}

		tTransaction.Commit();
	}

	void TSQLiteTaskSchema::CreateNewDatabase(const sqlite::TSQLiteDatabasePtr& spDatabase, TSerializerVersion &tVersion)
	{
		sqlite::TSQLiteStatement tStatement(spDatabase);
		tStatement.Prepare(_T("CREATE TABLE task(id BIGINT UNIQUE, name varchar(256) NOT NULL, current_state INT NOT NULL, destination_path varchar(32768) NOT NULL)"));
		tStatement.Step();

		tStatement.Prepare(_T("CREATE TABLE base_paths(id BIGINT UNIQUE, src_path varchar(32768) NOT NULL, skip_processing boolean NOT NULL, dst_path varchar(32768) NOT NULL)"));
		tStatement.Step();

		tStatement.Prepare(_T("CREATE TABLE scanned_files(id BIGINT UNIQUE, rel_path varchar(32768) NOT NULL, dst_rel_path varchar(32768) NOT NULL DEFAULT '', base_path_id BIGINT NOT NULL, attr INT NOT NULL, size BIGINT NOT NULL, time_created BIGINT NOT NULL, time_last_write BIGINT NOT NULL, time_last_access BIGINT NOT NULL, flags INT NOT NULL)"));
		tStatement.Step();

		tStatement.Prepare(_T("CREATE TABLE task_config(id BIGINT UNIQUE, name varchar(256) NOT NULL, node_order INT NOT NULL, value varchar(32768) NOT NULL)"));
		tStatement.Step();

		tStatement.Prepare(_T("CREATE TABLE filters(id BIGINT UNIQUE, use_mask INT NOT NULL, mask varchar(32768) NOT NULL, ")
			_T("use_exclude_mask INT NOT NULL, exclude_mask varchar(32768) NOT NULL, ")
			_T("use_size_1 INT NOT NULL, compare_type_1 INT NOT NULL, size_1 BIGINT NOT NULL, ")
			_T("use_size_2 INT NOT NULL, compare_type_2 INT NOT NULL, size_2 BIGINT NOT NULL, ")
			_T("date_type INT NOT NULL,")
			_T("use_date_time_1 INT NOT NULL, date_compare_type_1 INT NOT NULL, use_date_1 INT NOT NULL, use_time_1 INT NOT NULL, datetime_1 varchar(64) NOT NULL, ")
			_T("use_date_time_2 INT NOT NULL, date_compare_type_2 INT NOT NULL, use_date_2 INT NOT NULL, use_time_2 INT NOT NULL, datetime_2 varchar(64) NOT NULL, ")
			_T("use_attributes INT NOT NULL, attr_archive INT NOT NULL, attr_ro INT NOT NULL, attr_hidden INT NOT NULL, attr_system INT NOT NULL, attr_directory INT NOT NULL")
			_T(")"));
		tStatement.Step();

		tStatement.Prepare(_T("CREATE TABLE local_stats(id BIGINT UNIQUE, elapsed_time BIGINT NOT NULL)"));
		tStatement.Step();

		tStatement.Prepare(_T("CREATE TABLE subtasks(id BIGINT UNIQUE, type INT NOT NULL, is_current boolean NOT NULL, is_estimation boolean NOT NULL)"));
		tStatement.Step();

		tStatement.Prepare(_T("CREATE TABLE subtasks_info(id BIGINT UNIQUE, operation INT NOT NULL)"));
		tStatement.Step();

		tStatement.Prepare(_T("CREATE TABLE subtask_fastmove(id BIGINT UNIQUE, current_index INT NOT NULL, is_running boolean NOT NULL, is_initialized boolean NOT NULL, total_size BIGINT NOT NULL, processed_size BIGINT NOT NULL, size_speed varchar(1024) NOT NULL, ")
			_T("total_count BIGINT NOT NULL, processed_count BIGINT NOT NULL, count_speed varchar(1024) NOT NULL, ci_processed_size BIGINT NOT NULL, ci_total_size BIGINT NOT NULL, timer BIGINT NOT NULL, ")
			_T("buffer_index INT NOT NULL, current_path varchar(32768) NOT NULL, ci_silent_resume boolean NOT NULL)"));
		tStatement.Step();

		tStatement.Prepare(_T("CREATE TABLE subtask_scan(id BIGINT UNIQUE, current_index INT NOT NULL, is_running boolean NOT NULL, is_initialized boolean NOT NULL, total_size BIGINT NOT NULL, processed_size BIGINT NOT NULL, size_speed varchar(1024) NOT NULL, ")
			_T("total_count BIGINT NOT NULL, processed_count BIGINT NOT NULL, count_speed varchar(1024) NOT NULL, ci_processed_size BIGINT NOT NULL, ci_total_size BIGINT NOT NULL, timer BIGINT NOT NULL, ")
			_T("buffer_index INT NOT NULL, current_path varchar(32768) NOT NULL, ci_silent_resume boolean NOT NULL)"));
		tStatement.Step();

		tStatement.Prepare(_T("CREATE TABLE subtask_delete(id BIGINT UNIQUE, current_index INT NOT NULL, is_running boolean NOT NULL, is_initialized boolean NOT NULL, total_size BIGINT NOT NULL, processed_size BIGINT NOT NULL, size_speed varchar(1024) NOT NULL, ")
			_T("total_count BIGINT NOT NULL, processed_count BIGINT NOT NULL, count_speed varchar(1024) NOT NULL, ci_processed_size BIGINT NOT NULL, ci_total_size BIGINT NOT NULL, timer BIGINT NOT NULL, ")
			_T("buffer_index INT NOT NULL, current_path varchar(32768) NOT NULL, ci_silent_resume boolean NOT NULL)"));
		tStatement.Step();

		tStatement.Prepare(_T("CREATE TABLE subtask_copymove(id BIGINT UNIQUE, current_index INT NOT NULL, is_running boolean NOT NULL, is_initialized boolean NOT NULL, total_size BIGINT NOT NULL, processed_size BIGINT NOT NULL, size_speed varchar(1024) NOT NULL, ")
			_T("total_count BIGINT NOT NULL, processed_count BIGINT NOT NULL, count_speed varchar(1024) NOT NULL, ci_processed_size BIGINT NOT NULL, ci_total_size BIGINT NOT NULL, timer BIGINT NOT NULL, ")
			_T("buffer_index INT NOT NULL, current_path varchar(32768) NOT NULL, ci_silent_resume boolean NOT NULL)"));
		tStatement.Step();

		tStatement.Prepare(_T("CREATE TABLE feedback_already_exists(id BIGINT UNIQUE, use_mask INT NOT NULL, mask varchar(32768) NOT NULL, ")
			_T("use_exclude_mask INT NOT NULL, exclude_mask varchar(32768) NOT NULL, ")
			_T("use_date_compare INT NOT NULL, date_compare_type INT NOT NULL, ")
			_T("use_size_compare INT NOT NULL, size_compare_type INT NOT NULL, ")
			_T("result INT NOT NULL)"));
		tStatement.Step();

		tStatement.Prepare(_T("CREATE TABLE feedback_error(id BIGINT UNIQUE, use_mask INT NOT NULL, mask varchar(32768) NOT NULL, ")
			_T("use_exclude_mask INT NOT NULL, exclude_mask varchar(32768) NOT NULL, ")
			_T("use_error_type INT NOT NULL, error_type INT NOT NULL, ")
			_T("use_system_error_no INT NOT NULL, system_error_no INT NOT NULL, ")
			_T("result INT NOT NULL)"));
		tStatement.Step();

		tStatement.Prepare(_T("CREATE TABLE feedback_not_enough_space(id BIGINT UNIQUE, use_mask INT NOT NULL, mask varchar(32768) NOT NULL, ")
			_T("use_exclude_mask INT NOT NULL, exclude_mask varchar(32768) NOT NULL, ")
			_T("result INT NOT NULL)"));
		tStatement.Step();

		tStatement.Prepare(_T("CREATE TABLE feedback_operation_event(id BIGINT UNIQUE,")
			_T("use_operation_event INT NOT NULL, operation_event INT NOT NULL, ")
			_T("result INT NOT NULL)"));
		tStatement.Step();

		// and finally set the database version to current one
		tVersion.SetVersion(7);
	}

	void TSQLiteTaskSchema::Migrate_001_002(const sqlite::TSQLiteDatabasePtr& spDatabase, TSerializerVersion &tVersion)
	{
		sqlite::TSQLiteStatement tStatement(spDatabase);

		tStatement.Prepare(_T("ALTER TABLE subtask_fastmove ADD COLUMN ci_silent_resume boolean NOT NULL DEFAULT false"));
		tStatement.Step();
		tStatement.Prepare(_T("ALTER TABLE subtask_delete ADD COLUMN ci_silent_resume boolean NOT NULL DEFAULT false"));
		tStatement.Step();
		tStatement.Prepare(_T("ALTER TABLE subtask_copymove ADD COLUMN ci_silent_resume boolean NOT NULL DEFAULT false"));
		tStatement.Step();

		tVersion.SetVersion(2);
	}

	void TSQLiteTaskSchema::Migrate_002_003(const sqlite::TSQLiteDatabasePtr& spDatabase, TSerializerVersion &tVersion)
	{
		sqlite::TSQLiteStatement tStatement(spDatabase);

		// drop column from fastmove
		tStatement.Prepare(_T("CREATE TABLE subtask_fastmove_new(id BIGINT UNIQUE, current_index INT NOT NULL, is_running boolean NOT NULL, is_initialized boolean NOT NULL, total_size BIGINT NOT NULL, processed_size BIGINT NOT NULL, size_speed varchar(1024) NOT NULL, ")
			_T("total_count BIGINT NOT NULL, processed_count BIGINT NOT NULL, count_speed varchar(1024) NOT NULL, ci_processed_size BIGINT NOT NULL, ci_total_size BIGINT NOT NULL, timer BIGINT NOT NULL, ")
			_T("buffer_index INT NOT NULL, current_path varchar(32768) NOT NULL, ci_silent_resume boolean NOT NULL)"));
		tStatement.Step();

		tStatement.Prepare(_T("INSERT INTO subtask_fastmove_new(id, current_index, is_running, is_initialized, total_size, processed_size, size_speed, ")
			_T("total_count, processed_count, count_speed, ci_processed_size, ci_total_size, timer, buffer_index, current_path, ci_silent_resume)")
			_T("SELECT id, current_index, is_running, is_initialized, total_size, processed_size, size_speed, ")
			_T("total_count, processed_count, count_speed, ci_processed_size, ci_total_size, timer, buffer_index, current_path, ci_silent_resume ")
			_T("FROM subtask_fastmove"));
		tStatement.Step();

		tStatement.Prepare(_T("DROP TABLE subtask_fastmove"));
		tStatement.Step();

		tStatement.Prepare(_T("ALTER TABLE subtask_fastmove_new RENAME TO subtask_fastmove"));
		tStatement.Step();

		// drop column from subtask delete
		tStatement.Prepare(_T("CREATE TABLE subtask_delete_new(id BIGINT UNIQUE, current_index INT NOT NULL, is_running boolean NOT NULL, is_initialized boolean NOT NULL, total_size BIGINT NOT NULL, processed_size BIGINT NOT NULL, size_speed varchar(1024) NOT NULL, ")
			_T("total_count BIGINT NOT NULL, processed_count BIGINT NOT NULL, count_speed varchar(1024) NOT NULL, ci_processed_size BIGINT NOT NULL, ci_total_size BIGINT NOT NULL, timer BIGINT NOT NULL, ")
			_T("buffer_index INT NOT NULL, current_path varchar(32768) NOT NULL, ci_silent_resume boolean NOT NULL)"));
		tStatement.Step();

		//id, current_index, is_running, is_initialized, total_size, processed_size, size_speed, total_count, processed_count, count_speed, ci_processed_size, ci_total_size, timer, buffer_index, current_path, ci_silent_resume
		tStatement.Prepare(_T("INSERT INTO subtask_delete_new(id, current_index, is_running, is_initialized, total_size, processed_size, size_speed, total_count, processed_count, count_speed, ci_processed_size, ci_total_size, timer, buffer_index, current_path, ci_silent_resume)")
			_T("SELECT id, current_index, is_running, is_initialized, total_size, processed_size, size_speed, total_count, processed_count, count_speed, ci_processed_size, ci_total_size, timer, buffer_index, current_path, ci_silent_resume ")
			_T("FROM subtask_delete"));
		tStatement.Step();

		tStatement.Prepare(_T("DROP TABLE subtask_delete"));
		tStatement.Step();

		tStatement.Prepare(_T("ALTER TABLE subtask_delete_new RENAME TO subtask_delete"));
		tStatement.Step();

		// drop column from subtask copymove
		tStatement.Prepare(_T("CREATE TABLE subtask_copymove_new(id BIGINT UNIQUE, current_index INT NOT NULL, is_running boolean NOT NULL, is_initialized boolean NOT NULL, total_size BIGINT NOT NULL, processed_size BIGINT NOT NULL, size_speed varchar(1024) NOT NULL, ")
			_T("total_count BIGINT NOT NULL, processed_count BIGINT NOT NULL, count_speed varchar(1024) NOT NULL, ci_processed_size BIGINT NOT NULL, ci_total_size BIGINT NOT NULL, timer BIGINT NOT NULL, ")
			_T("buffer_index INT NOT NULL, current_path varchar(32768) NOT NULL, ci_silent_resume boolean NOT NULL)"));
		tStatement.Step();

		//id, current_index, is_running, is_initialized, total_size, processed_size, size_speed, total_count, processed_count, count_speed, ci_processed_size, ci_total_size, timer, buffer_index, current_path, ci_silent_resume
		tStatement.Prepare(_T("INSERT INTO subtask_copymove_new(id, current_index, is_running, is_initialized, total_size, processed_size, size_speed, total_count, processed_count, count_speed, ci_processed_size, ci_total_size, timer, buffer_index, current_path, ci_silent_resume)")
			_T("SELECT id, current_index, is_running, is_initialized, total_size, processed_size, size_speed, total_count, processed_count, count_speed, ci_processed_size, ci_total_size, timer, buffer_index, current_path, ci_silent_resume ")
			_T("FROM subtask_copymove"));
		tStatement.Step();

		tStatement.Prepare(_T("DROP TABLE subtask_copymove"));
		tStatement.Step();

		tStatement.Prepare(_T("ALTER TABLE subtask_copymove_new RENAME TO subtask_copymove"));
		tStatement.Step();

		tVersion.SetVersion(3);
	}

	void TSQLiteTaskSchema::Migrate_003_004(const sqlite::TSQLiteDatabasePtr& spDatabase, TSerializerVersion &tVersion)
	{
		sqlite::TSQLiteStatement tStatement(spDatabase);

		tStatement.Prepare(_T("CREATE TABLE feedback(id BIGINT UNIQUE, file_error INT NOT NULL, file_already_exists INT NOT NULL, not_enough_space INT NOT NULL, operation_finished INT NOT NULL, operation_error INT NOT NULL)"));
		tStatement.Step();

		// for tasks being migrated we have to insert some defaults for feedback
		tStatement.Prepare(_T("INSERT INTO feedback(id, file_error, file_already_exists, not_enough_space, operation_finished, operation_error) VALUES(?1, ?2, ?3, ?4, ?5, ?6)"));
		tStatement.BindValue(1, 0);
		tStatement.BindValue(2, 0);
		tStatement.BindValue(3, 0);
		tStatement.BindValue(4, 0);
		tStatement.BindValue(5, 0);
		tStatement.BindValue(6, 0);

		tStatement.Step();

		tVersion.SetVersion(4);
	}

	void TSQLiteTaskSchema::Migrate_004_005(const sqlite::TSQLiteDatabasePtr& spDatabase, TSerializerVersion &tVersion)
	{
		sqlite::TSQLiteStatement tStatement(spDatabase);

		// new table for scan stats
		tStatement.Prepare(_T("CREATE TABLE subtask_scan(id BIGINT UNIQUE, current_index INT NOT NULL, is_running boolean NOT NULL, is_initialized boolean NOT NULL, total_size BIGINT NOT NULL, processed_size BIGINT NOT NULL, size_speed varchar(1024) NOT NULL, ")
			_T("total_count BIGINT NOT NULL, processed_count BIGINT NOT NULL, count_speed varchar(1024) NOT NULL, ci_processed_size BIGINT NOT NULL, ci_total_size BIGINT NOT NULL, timer BIGINT NOT NULL, ")
			_T("buffer_index INT NOT NULL, current_path varchar(32768) NOT NULL, ci_silent_resume boolean NOT NULL)"));
		tStatement.Step();

		tVersion.SetVersion(5);
	}

	void TSQLiteTaskSchema::Migrate_005_006(const sqlite::TSQLiteDatabasePtr& spDatabase, TSerializerVersion &tVersion)
	{
		sqlite::TSQLiteStatement tStatement(spDatabase);

		// remove column log_path
		tStatement.Prepare(_T("ALTER TABLE task RENAME TO task_old"));
		tStatement.Step();

		tStatement.Prepare(_T("CREATE TABLE task(id BIGINT UNIQUE, name varchar(256) NOT NULL, current_state INT NOT NULL, destination_path varchar(32768) NOT NULL)"));
		tStatement.Step();

		tStatement.Prepare(_T("INSERT INTO task(id, name, current_state, destination_path) SELECT id, name, current_state, destination_path FROM task_old"));
		tStatement.Step();

		tStatement.Prepare(_T("DROP TABLE task_old"));
		tStatement.Step();

		tVersion.SetVersion(6);
	}

	void TSQLiteTaskSchema::Migrate_006_007(const sqlite::TSQLiteDatabasePtr& spDatabase, TSerializerVersion& tVersion)
	{
		sqlite::TSQLiteStatement tStatement(spDatabase);

		// remove old feedback table (with no migration)
		tStatement.Prepare(_T("DROP TABLE feedback"));
		tStatement.Step();

		// create new feedback tables
		tStatement.Prepare(_T("CREATE TABLE feedback_already_exists(id BIGINT UNIQUE, use_mask INT NOT NULL, mask varchar(32768) NOT NULL, ")
			_T("use_exclude_mask INT NOT NULL, exclude_mask varchar(32768) NOT NULL, ")
			_T("use_date_compare INT NOT NULL, date_compare_type INT NOT NULL, ")
			_T("use_size_compare INT NOT NULL, size_compare_type INT NOT NULL, ")
			_T("result INT NOT NULL)"));
		tStatement.Step();

		tStatement.Prepare(_T("CREATE TABLE feedback_error(id BIGINT UNIQUE, use_mask INT NOT NULL, mask varchar(32768) NOT NULL, ")
			_T("use_exclude_mask INT NOT NULL, exclude_mask varchar(32768) NOT NULL, ")
			_T("use_error_type INT NOT NULL, error_type INT NOT NULL, ")
			_T("use_system_error_no INT NOT NULL, system_error_no INT NOT NULL, ")
			_T("result INT NOT NULL)"));
		tStatement.Step();

		tStatement.Prepare(_T("CREATE TABLE feedback_not_enough_space(id BIGINT UNIQUE, use_mask INT NOT NULL, mask varchar(32768) NOT NULL, ")
			_T("use_exclude_mask INT NOT NULL, exclude_mask varchar(32768) NOT NULL, ")
			_T("result INT NOT NULL)"));
		tStatement.Step();

		tStatement.Prepare(_T("CREATE TABLE feedback_operation_event(id BIGINT UNIQUE,")
			_T("use_operation_event INT NOT NULL, operation_event INT NOT NULL, ")
			_T("result INT NOT NULL)"));
		tStatement.Step();

		// adjust scanned_paths to include destination path
		tStatement.Prepare(_T("ALTER TABLE scanned_files ADD COLUMN dst_rel_path varchar(32768) NOT NULL DEFAULT ''"));
		tStatement.Step();

		tVersion.SetVersion(7);
	}
}