// Copyright (c) 2008-2009 Nokia Corporation and/or its subsidiary(-ies).

// All rights reserved.

// This component and the accompanying materials are made available

// under the terms of "Eclipse Public License v1.0"

// which accompanies this distribution, and is available

// at the URL "http://www.eclipse.org/legal/epl-v10.html".

//

// Initial Contributors:

// Nokia Corporation - initial contribution.

//

// Contributors:

//

// Description:

//

#include "tzuserdata.h"

#include <tz.h>

#include <f32file.h>

#include <e32property.h>

#ifdef SYMBIAN_ENABLE_SPLIT_HEADERS

#include <tzusernames.h>

#include <tzuserdefineddata.h>

#endif

#include "tzidinternal.h"

_LIT(KUserDatabaseName, "c:\\private\\1020383e\\SQLite__tzuserdata.db");

_LIT(KTzPrivatePath, "c:\\private\\1020383e\\");

// Transaction constants 

_LIT(KBeginTransaction,"BEGIN TRANSACTION;");

_LIT(KCommitTransaction,"COMMIT;");

_LIT(KRollbackTransaction,"ROLLBACK;");

// Tz Rules Table Parameters

_LIT(KTzRulesTblTzIdParam, ":tzId");

_LIT(KTzRulesTblRuleTypeParam, ":ruleType");

_LIT(KTzRulesTblDSTStartTimeParam, ":dstStartTime");

_LIT(KTzRulesTblDSTStartRefParam, ":dstStartRef"); 

_LIT(KTzRulesTblDSTEndTimeParam, ":dstEndTime");

_LIT(KTzRulesTblDSTEndRefParam, ":dstEndRef");

_LIT(KTzRulesTblOldOffsetParam, ":oldOffset");

_LIT(KTzRulesTblNewOffsetParam, ":newOffset");

_LIT(KTzRulesTblMonthParam, ":month");

_LIT(KTzRulesTblDayParam, ":day");

_LIT(KTzRulesTblWeekParam, ":week");

_LIT(KTzRulesTblTimeRefParam, ":timeRef");

_LIT(KTzRulesTblMinsParam, ":mins");

// Tz Rules Table Index

_LIT(KTzRulesTblRuleTypeIndex, "ruleType");

_LIT(KTzRulesTblDSTStartTimeIndex, "dstStartTime");

_LIT(KTzRulesTblDSTStartRefIndex, "dstStartRef"); 

_LIT(KTzRulesTblDSTEndTimeIndex, "dstEndTime");

_LIT(KTzRulesTblDSTEndRefIndex, "dstEndRef");

_LIT(KTzRulesTblOldOffsetIndex, "oldOffset");

_LIT(KTzRulesTblNewOffsetIndex, "newOffset");

_LIT(KTzRulesTblMonthIndex, "month");

_LIT(KTzRulesTblDayIndex, "day");

_LIT(KTzRulesTblWeekIndex, "week");

_LIT(KTzRulesTblTimeRefIndex, "timeRef");

_LIT(KTzRulesTblMinsIndex, "mins");

// Tz Names Table Parameters

_LIT(KTzNamesTblTzIdParam, ":tzId");

_LIT(KTzNamesTblStdNameParam, ":stdName");

_LIT(KTzNamesTblShortStdNameParam, ":shortStdName");

_LIT(KTzNamesTblDSTNameParam, ":dstName");

_LIT(KTzNamesTblShortDSTNameParam, ":shortDstName");

_LIT(KTzNamesTblCityNameParam, ":cityName");

_LIT(KTzNamesTblRegionNameParam, ":regionName");

// Tz Names Table Index

_LIT(KTzNamesTblTzIdIndex, "tzId");

_LIT(KTzNamesTblStdNameIndex, "stdName");

_LIT(KTzNamesTblShortStdNameIndex, "shortStdName");

_LIT(KTzNamesTblDSTNameIndex, "dstName");

_LIT(KTzNamesTblShortDSTNameIndex, "shortDstName");

_LIT(KTzNamesTblCityNameIndex, "cityName");

_LIT(KTzNamesTblRegionNameIndex, "regionName");

// Identifier Table Index

_LIT(KNextTzIdentifierIndex, "nextTzId");

// InitialStdOffset Table Param

_LIT(KTzInitialStdOffsetTblTzIdParam, ":tzId");

_LIT(KTzInitialStdOffsetTblStdInitialOffsetParam, ":initialStdOffset");

// InitialStdOffset Table Index

_LIT(KTzInitialStdOffsetTblStdInitialOffsetIndex, "initialStdOffset");

// Create statements

_LIT8 (KTzRulesCreateStmnt, "CREATE TABLE rules (tzId INTEGER NOT NULL, ruleType \

							 INTEGER, dstStartTime INT64 NOT NULL, dstStartRef INTEGER, \

							 dstEndTime INT64 NOT NULL, dstEndRef INTEGER, oldOffset \

							 INTEGER NOT NULL, newOffset INTEGER NOT NULL, month INTEGER, \

							 day INTEGER, week INTEGER, timeRef INTEGER, mins INTEGER NOT NULL)");

_LIT8 (KTzNamesCreateStmnt, "CREATE TABLE names ( tzId INTEGER NOT NULL PRIMARY \

							 KEY, stdName CHAR(255), shortStdName CHAR(10), dstName \

							 CHAR(255), shortDstName CHAR(10), cityName CHAR(255), \

							 regionName CHAR(255))");

_LIT8 (KTzIdentifierCreateStmnt, "CREATE TABLE  identifier ( nextTzId INTEGER NOT NULL )");

_LIT8 (KTzInitialStdOffsetCreateStmnt, "CREATE TABLE initialStdOffset (tzId INTEGER NOT NULL, initialStdOffset INTEGER)");

// Insert statements

_LIT8(KInsertRulesStmnt, "INSERT INTO rules (tzId, ruleType, dstStartTime, dstStartRef, \

						  dstEndTime, dstEndRef, oldOffset, newOffset, month, day, \

						  week, timeRef, mins) VALUES (:tzId, :ruleType, :dstStartTime,\

						  :dstStartRef, :dstEndTime, :dstEndRef, :oldOffset, :newOffset,\

						  :month, :day, :week, :timeRef, :mins)");

_LIT8(KInsertNamesStmnt, "INSERT INTO names(tzId, stdName, shortStdName, dstName, shortDstName,\

						  cityName, regionName) VALUES(:tzId, :stdName, :shortStdName, :dstName,\

						  :shortDstName, :cityName, :regionName)");

_LIT(KInsertIdentifierStmnt, "INSERT INTO identifier (nextTzId) VALUES (%d)");

_LIT8(KInsertInitialStdOffsetStmnt, "INSERT INTO initialStdOffset (tzId, initialStdOffset) \

									 VALUES (:tzId, :initialStdOffset)");

//Select statements

_LIT8(KSelectNextTzIdStmnt, "SELECT nextTzId FROM identifier");

_LIT(KSelectRulesStmnt, "SELECT * FROM rules WHERE tzId = %d");

_LIT(KSelectRulesCountStmnt, "SELECT COUNT(tzId) FROM rules WHERE tzId = %d");

_LIT(KSelectNamesCountStmnt, "SELECT COUNT(tzId) FROM names WHERE tzId = %d");

_LIT(KSelectTzIdCountFromNamesStmnt, "SELECT COUNT(tzId) FROM names WHERE tzId = %d");

_LIT(KSelectNamesStmnt, "SELECT * FROM names WHERE tzId = %d");

_LIT(KSelectTzIdFromNamesStmnt, "SELECT tzId FROM names");

_LIT(KSelectInitialStdOffsetStmnt, "SELECT initialStdOffset FROM initialStdOffset WHERE tzId = %d");

//Update statements

_LIT (KUpdateIdentifierStmnt, "UPDATE identifier SET nextTzId = :nextTzId");

//Delete statements

_LIT(KDeleteRulesStmnt, "DELETE FROM rules WHERE tzId = %d");

_LIT(KDeleteNamesStmnt, "DELETE FROM names WHERE tzId = %d");

_LIT(KDeleteInitialStdOffset, "DELETE FROM initialStdOffset WHERE tzId = %d");

const TInt KSqlStringLength = 100;

const TUint KOutOfBoundsUserId = 0x5000;

CTzUserDataDb* CTzUserDataDb::NewL()

	{

	CTzUserDataDb* self = new(ELeave) CTzUserDataDb();

	CleanupStack::PushL(self);

	self->ConstructL();

	CleanupStack::Pop(self);

	return self;

	}

CTzUserDataDb::CTzUserDataDb()

	{

	}

CTzUserDataDb::~CTzUserDataDb()

	{

	// We don't own the pointers in this array

	iChangeObservers.Close();

	iDatabase.Close();

	}

void CTzUserDataDb::ConstructL()

	{

	OpenL();	

	}

void CTzUserDataDb::Close()

	{

	iDatabase.Close();

	}

void CTzUserDataDb::OpenL()

	{

	RFs fs;

	CleanupClosePushL(fs);

	// Open the database

	TInt ret = iDatabase.Open(KUserDatabaseName());

	// If the database could not be opened because 

	// the file was not found in the path provided, create the directory

	if(ret == KErrNotFound || ret == KErrPathNotFound)

		{

		User::LeaveIfError(fs.Connect());

		ret = fs.MkDir(KTzPrivatePath);

		// If there are no errors while creating path

		// or the path already exists, create the database

		if(ret == KErrNone || ret == KErrAlreadyExists)

			{

			CreateL();	

			}

		}

	else

		{

		User::LeaveIfError(ret);

		}

	CleanupStack::PopAndDestroy(&fs);

	}

void CTzUserDataDb::CreateL()

	{	

	User::LeaveIfError(iDatabase.Create(KUserDatabaseName()));

    iDatabase.Close();    

    CreateSchemaL();

   	}

void CTzUserDataDb::CreateSchemaL()

	{

	RSqlStatement stmt;

	CleanupClosePushL(stmt);

	User::LeaveIfError(iDatabase.Open(KUserDatabaseName()));

	User::LeaveIfError(stmt.Prepare(iDatabase, KTzRulesCreateStmnt));

	User::LeaveIfError(stmt.Exec());

	stmt.Close();

	User::LeaveIfError(stmt.Prepare(iDatabase, KTzNamesCreateStmnt));

	User::LeaveIfError(stmt.Exec());

	stmt.Close();

	User::LeaveIfError(stmt.Prepare(iDatabase, KTzIdentifierCreateStmnt));

	User::LeaveIfError(stmt.Exec());

	stmt.Close();

	TBuf<KSqlStringLength> sqlId;

	sqlId.Format(KInsertIdentifierStmnt, KUserTzIdMin);

	User::LeaveIfError(stmt.Prepare(iDatabase, sqlId));

	User::LeaveIfError(stmt.Exec());

	stmt.Close();

	User::LeaveIfError(stmt.Prepare(iDatabase, KTzInitialStdOffsetCreateStmnt));

	User::LeaveIfError(stmt.Exec());

	stmt.Close();

	CleanupStack::PopAndDestroy(&stmt);

	}

TInt CTzUserDataDb::CreateTzL(const CTzRules& aRules, const CTzUserNames& aNames)

	{

	if(iBackupInProgress || iRestoreInProgress)

		{

		User::Leave(KErrLocked);

		}

	RSqlStatement stmt;

	CleanupClosePushL(stmt);

	BeginTransactionL();

	TInt err = stmt.Prepare(iDatabase, KSelectNextTzIdStmnt);

	User::LeaveIfError(err);

	TInt colIndex = stmt.ColumnIndex(KNextTzIdentifierIndex);

	User::LeaveIfError(colIndex);

	while((err = stmt.Next()) == KSqlAtRow)

		{

		iNextCandidateTzId = stmt.ColumnInt64(colIndex);	

		}

	stmt.Close();

	// Check if the next available user ID is valid

	if(iNextCandidateTzId == KOutOfBoundsUserId || IsIdInDbL(iNextCandidateTzId))

		{

		iNextCandidateTzId = FindUnusedTzIdL();

		}

	// If all the user IDs have been used, leave with KErrOverflow	

	if(iNextCandidateTzId == KOutOfBoundsUserId)

		{

		User::Leave(KErrOverflow);		

		}

	TUint retId = iNextCandidateTzId;

	TInt index;	

	TInt count = aRules.Count();

	for(TInt i=0; i<count; i++)

		{

		TTzRule& rule = const_cast<CTzRules&>(aRules)[i];

		// Insert a row in the rules

		User::LeaveIfError(stmt.Prepare(iDatabase, KInsertRulesStmnt()));

		index = stmt.ParameterIndex(KTzRulesTblTzIdParam);

		User::LeaveIfError(index);

		User::LeaveIfError(stmt.BindInt64(index, iNextCandidateTzId));

		index = stmt.ParameterIndex(KTzRulesTblRuleTypeParam);

		User::LeaveIfError(index);

		User::LeaveIfError(stmt.BindInt(index, rule.iDayRule));

		index = stmt.ParameterIndex(KTzRulesTblDSTStartTimeParam);

		User::LeaveIfError(index);

		User::LeaveIfError(stmt.BindInt64(index, rule.iFrom.iTime.Int64()));

		index = stmt.ParameterIndex(KTzRulesTblDSTStartRefParam);

		User::LeaveIfError(index);

		User::LeaveIfError(stmt.BindInt(index, rule.iFrom.iTimeReference));

		index = stmt.ParameterIndex(KTzRulesTblDSTEndTimeParam);

		User::LeaveIfError(index);

		User::LeaveIfError(stmt.BindInt64(index, rule.iTo.iTime.Int64()));		

		index = stmt.ParameterIndex(KTzRulesTblDSTEndRefParam);

		User::LeaveIfError(index);

		User::LeaveIfError(stmt.BindInt(index, rule.iTo.iTimeReference));

		index = stmt.ParameterIndex(KTzRulesTblOldOffsetParam);

		User::LeaveIfError(index);

		User::LeaveIfError(stmt.BindInt(index, rule.iOldLocalTimeOffset));

		index = stmt.ParameterIndex(KTzRulesTblNewOffsetParam);

		User::LeaveIfError(index);

		User::LeaveIfError(stmt.BindInt(index, rule.iNewLocalTimeOffset));

		index = stmt.ParameterIndex(KTzRulesTblMonthParam);

		User::LeaveIfError(index);

		User::LeaveIfError(stmt.BindInt(index, rule.iMonth));

		index = stmt.ParameterIndex(KTzRulesTblDayParam);

		User::LeaveIfError(index);

		User::LeaveIfError(stmt.BindInt(index, rule.iDayOfMonth));

		index = stmt.ParameterIndex(KTzRulesTblWeekParam);

		User::LeaveIfError(index);

		User::LeaveIfError(stmt.BindInt(index, rule.iDayOfWeek));

		index = stmt.ParameterIndex(KTzRulesTblTimeRefParam);

		User::LeaveIfError(index);

		User::LeaveIfError(stmt.BindInt(index, rule.iTimeReference));

		index = stmt.ParameterIndex(KTzRulesTblMinsParam);

		User::LeaveIfError(index);

		User::LeaveIfError(stmt.BindInt(index, rule.iTimeOfChange));

		TInt ret = stmt.Exec();

		stmt.Close();

		if(ret < KErrNone)

			{

			User::Leave(ret);

			}			

		}

	// Insert a row in the names

	User::LeaveIfError(stmt.Prepare(iDatabase, KInsertNamesStmnt()));

	index = stmt.ParameterIndex(KTzNamesTblTzIdParam);

	User::LeaveIfError(index);

	User::LeaveIfError(stmt.BindInt64(index, iNextCandidateTzId));

	index = stmt.ParameterIndex(KTzNamesTblStdNameParam);

	User::LeaveIfError(index);

	User::LeaveIfError(stmt.BindText(index, aNames.StandardName()));

	index = stmt.ParameterIndex(KTzNamesTblShortStdNameParam);

	User::LeaveIfError(index);

	User::LeaveIfError(stmt.BindText(index, aNames.ShortStandardName()));

	index = stmt.ParameterIndex(KTzNamesTblDSTNameParam);

	User::LeaveIfError(index);

	User::LeaveIfError(stmt.BindText(index, aNames.DaylightSaveName()));

	index = stmt.ParameterIndex(KTzNamesTblShortDSTNameParam);

	User::LeaveIfError(index);

	User::LeaveIfError(stmt.BindText(index, aNames.ShortDaylightSaveName()));

	index = stmt.ParameterIndex(KTzNamesTblCityNameParam);

	User::LeaveIfError(index);

	User::LeaveIfError(stmt.BindText(index, aNames.CityName()));

	index = stmt.ParameterIndex(KTzNamesTblRegionNameParam);

	User::LeaveIfError(index);

	User::LeaveIfError(stmt.BindText(index, aNames.RegionName()));

	TInt ret = stmt.Exec();

	stmt.Close();

	if(ret < KErrNone)

		{

		User::Leave(ret);

		}

	User::LeaveIfError(stmt.Prepare(iDatabase, KInsertInitialStdOffsetStmnt));

	index = stmt.ParameterIndex(KTzInitialStdOffsetTblTzIdParam);

	User::LeaveIfError(index);

	User::LeaveIfError(stmt.BindInt(index, iNextCandidateTzId));

	index = stmt.ParameterIndex(KTzInitialStdOffsetTblStdInitialOffsetParam);

	User::LeaveIfError(index);

	TInt initialOffset = aRules.InitialStdTimeOffset();

	User::LeaveIfError(stmt.BindInt(index, initialOffset));

	ret = stmt.Exec();

	stmt.Close();

	if(ret < KErrNone)

		{

		User::Leave(ret);

		}

	// Update the next available user ID

	if(iNextCandidateTzId >= KUserTzIdMax)

		{

		iNextCandidateTzId = FindUnusedTzIdL();

		}

	else

		{

		iNextCandidateTzId++;	

		}

	//Insert a row with the new available value of tz id in TZ IdentifierTable

	User::LeaveIfError(stmt.Prepare(iDatabase, KUpdateIdentifierStmnt));

	User::LeaveIfError(stmt.BindInt64(0, iNextCandidateTzId));

	ret = stmt.Exec();

	stmt.Close();

	if(ret < KErrNone)

		{

		User::Leave(ret);

		}

	CommitTransactionL();

	NotifyTzRulesChange(retId, ETzUserDataCreated);

	NotifyTzNamesChange(retId, ETzUserDataCreated);

	CleanupStack::PopAndDestroy(&stmt);

	return retId;	

	}

void CTzUserDataDb::ReadTzRulesL(CTzRules& aRules, TUint aTzId)

	{

	if(iBackupInProgress || iRestoreInProgress)

		{

		User::Leave(KErrLocked);

		}

	TSqlScalarFullSelectQuery fullSelectQuery(iDatabase);

	RSqlStatement stmt;

	CleanupClosePushL(stmt);

	TTzRule tzRule;	

	TBuf<KSqlStringLength> sqlRules;

	sqlRules.Format(KSelectRulesCountStmnt, aTzId);

	TInt count = fullSelectQuery.SelectIntL(sqlRules);

	if(count == 0)

		{

		User::Leave(KErrNotFound);

		}

	sqlRules.Format(KSelectRulesStmnt, aTzId);

	TInt err = stmt.Prepare(iDatabase, sqlRules);

	User::LeaveIfError(err);

	TInt ruleTypeColIdx = stmt.ColumnIndex(KTzRulesTblRuleTypeIndex);

	User::LeaveIfError(ruleTypeColIdx);

	TInt startTimeColIdx = stmt.ColumnIndex(KTzRulesTblDSTStartTimeIndex);

	User::LeaveIfError(startTimeColIdx);

	TInt startRefColIdx = stmt.ColumnIndex(KTzRulesTblDSTStartRefIndex);

	User::LeaveIfError(startRefColIdx);

	TInt endTimeColIdx = stmt.ColumnIndex(KTzRulesTblDSTEndTimeIndex);

	User::LeaveIfError(endTimeColIdx);

	TInt endRefColIdx = stmt.ColumnIndex(KTzRulesTblDSTEndRefIndex);

	User::LeaveIfError(endRefColIdx);

	TInt oldOffsetColIdx = stmt.ColumnIndex(KTzRulesTblOldOffsetIndex);

	User::LeaveIfError(oldOffsetColIdx);

	TInt newOffsetColIdx = stmt.ColumnIndex(KTzRulesTblNewOffsetIndex);		

	User::LeaveIfError(newOffsetColIdx);

	TInt monthColIdx = stmt.ColumnIndex(KTzRulesTblMonthIndex);

	User::LeaveIfError(monthColIdx);

	TInt dayColIdx = stmt.ColumnIndex(KTzRulesTblDayIndex);

	User::LeaveIfError(dayColIdx);

	TInt weekColIdx = stmt.ColumnIndex(KTzRulesTblWeekIndex);	

	User::LeaveIfError(weekColIdx);

	TInt timeRefColIdx = stmt.ColumnIndex(KTzRulesTblTimeRefIndex);

	User::LeaveIfError(timeRefColIdx);

	TInt minsColIdx = stmt.ColumnIndex(KTzRulesTblMinsIndex);

	User::LeaveIfError(minsColIdx);

	while((err = stmt.Next()) == KSqlAtRow)

		{			

		tzRule.iDayRule = static_cast<TTzRuleDay>(stmt.ColumnInt(ruleTypeColIdx));

		tzRule.iFrom.iTime = stmt.ColumnInt64(startTimeColIdx);

		tzRule.iFrom.iTimeReference = static_cast<TTzTimeReference>(stmt.ColumnInt(startRefColIdx));

		tzRule.iTo.iTime = stmt.ColumnInt64(endTimeColIdx); 

		tzRule.iTo.iTimeReference = static_cast<TTzTimeReference>(stmt.ColumnInt(endRefColIdx));

		tzRule.iOldLocalTimeOffset = stmt.ColumnInt(oldOffsetColIdx);

		tzRule.iNewLocalTimeOffset = stmt.ColumnInt(newOffsetColIdx);

		tzRule.iMonth = static_cast<TMonth>(stmt.ColumnInt(monthColIdx));

		tzRule.iDayOfMonth = stmt.ColumnInt(dayColIdx);

		tzRule.iDayOfWeek = stmt.ColumnInt(weekColIdx);

		tzRule.iTimeReference = static_cast<TTzTimeReference>(stmt.ColumnInt(timeRefColIdx));

		tzRule.iTimeOfChange = stmt.ColumnInt(minsColIdx);

		aRules.AddRuleL(tzRule);	

		}

	stmt.Close();

	sqlRules.Format(KSelectInitialStdOffsetStmnt, aTzId);

	err = stmt.Prepare(iDatabase, sqlRules);

	User::LeaveIfError(err);

	TInt stdInitialOffsetIdx = stmt.ColumnIndex(KTzInitialStdOffsetTblStdInitialOffsetIndex);

	User::LeaveIfError(stdInitialOffsetIdx);	

	// Fetch the row.

	User::LeaveIfError(stmt.Next());

	TInt stdInitialOffset = stmt.ColumnInt(stdInitialOffsetIdx);

	aRules.SetInitialStdTimeOffset(stdInitialOffset);

	// This must be the only row.

	__ASSERT_ALWAYS(stmt.Next() == KSqlAtEnd, RTz::Panic(RTz::EPanicBadSchema));

	CleanupStack::PopAndDestroy(&stmt);	

	}

CTzUserNames* CTzUserDataDb::ReadTzNamesL(TUint aTzId)

	{

	if(iBackupInProgress || iRestoreInProgress)

		{

		User::Leave(KErrLocked);

		}

	RSqlStatement stmt;

	CleanupClosePushL(stmt);	

	TBuf<KSqlStringLength> sqlNames;	

	TSqlScalarFullSelectQuery fullSelectQuery(iDatabase);