/****************************************************************************

**

** Copyright (C) 2009 Nokia Corporation and/or its subsidiary(-ies).

** All rights reserved.

** Contact: Nokia Corporation (qt-info@nokia.com)

**

** This file is part of the examples of the Qt Toolkit.

**

** $QT_BEGIN_LICENSE:LGPL$

** No Commercial Usage

** This file contains pre-release code and may not be distributed.

** You may use this file in accordance with the terms and conditions

** contained in the Technology Preview License Agreement accompanying

** this package.

**

** GNU Lesser General Public License Usage

** Alternatively, this file may be used under the terms of the GNU Lesser

** General Public License version 2.1 as published by the Free Software

** Foundation and appearing in the file LICENSE.LGPL included in the

** packaging of this file.  Please review the following information to

** ensure the GNU Lesser General Public License version 2.1 requirements

** will be met: http://www.gnu.org/licenses/old-licenses/lgpl-2.1.html.

**

** In addition, as a special exception, Nokia gives you certain additional

** rights.  These rights are described in the Nokia Qt LGPL Exception

** version 1.1, included in the file LGPL_EXCEPTION.txt in this package.

**

** If you have questions regarding the use of this file, please contact

** Nokia at qt-info@nokia.com.

**

**

**

**

**

**

**

**

** $QT_END_LICENSE$

**

****************************************************************************/

#include <QStringList>

#include "cntsqlsearch.h"

#include "cqwertykeymap.h"

#include "c12keykeymap.h"

#include <QHash>

#include <QLocale>

const char KLimitLength = 15;

const int KTwoTokens = 2;

const int KOneToken = 1;

const char KLowerLimitPadding = '0';

const char KUpperLimitPadding = 'F';

const int KMinimumSearchPatternLength = 1;

const int KHexadecimalBase = 16;

#define ORDER_BY_FIRSTNAME_LASTNAME " ORDER BY first_name, last_name ASC;"

#define SELECT_CONTACT_ID			"SELECT contact_id FROM "

// TODO: Since the column names are repeated several times, replace them with

// shorter names like w, x, y & z. Also replace contact_id by id etc.

// Predictive search table's columns

const QString KColumn1 = "nbr";

const QString KColumn2 = "nbr2";

const QString KColumn3 = "nbr3";

const QString KColumn4 = "nbr4";

// Predictive search qwert table's columns

const QString KQm1 = "n";

const QString KQm2 = "n2";

const QString KQm3 = "n3";

const QString KQm4 = "n4";

const QString KQm5 = "n5";

const QString KQm6 = "n6";

const QString KQm7 = "n7";

// Special handling for characters that originate from * and # keys

const QChar KStarChar('*');

const QChar KPlusChar('+');

const QChar KPChar('p');

const QChar KWChar('w');

const QChar KHashChar('#');

//Predictive search table

const QString QwertyTableName = "qm";

CntSqlSearch::CntSqlSearch()

	{

    QT_TRAP_THROWING(mQertyKeyMap = CQwertyKeyMap::NewL());

    QT_TRAP_THROWING(mkeyKeyMap = C12keyKeyMap::NewL());

	}

CntSqlSearch::~CntSqlSearch()

    {

    delete mQertyKeyMap;

    delete mkeyKeyMap;

    }

// Basic cases:

// 1: "0", "5"

// Just one digit. Select all contact ids from the table. No need to compare

// values.

//

// 2: "123", "01", "10", "010", "00"

// No zeros which have non-zeros in their both sides

// One or zero tokens, when pattern is split using '0'.

//

// 3: "101", "1001"

// Same digit of both sides of the zero

// Two tokens, each with length of 1 and tokens are the same.

// The queries of case 3 could also be handled with case 4 query, but that

// would yield a longer SQL statement.

//

// 4: "102", "1002"

// One or more zeros in the middle, just one digit on either side of the zero(s)

// and those digits are not the same.

// Two tokens, each with length of 1 and tokens are different.

//

// 5: "1023", "0102", "1010", "00100200", "10203", "01020304050"

// Two tokens, at least one of them has length > 1.

// If tokens are identical, handle as case 3, otherwise handle as case 4.

// ("10203" -> tokens "1" and "203"

//  "0010023004560" -> tokens "001" and "23004560")

//

// 6: "10", "1000"

// One token, ends with zero.

// In this case, query should look-up first toke and number ("10", "1000").

QString CntSqlSearch::CreatePredictiveSearch(const QString &pattern)

	{

	int len = pattern.length();

	QString newPattern;

	if (isQwerty(pattern))

	    {

        return CreateQwertyQuery(pattern);

	    }

	else

	    {

        newPattern = ChangeStringPadings(pattern);

        // For best performance, handle 1 digit case first

        if (len == KMinimumSearchPatternLength)

            {

            // Case 1

            return SELECT_CONTACT_ID + SelectTable(newPattern) + ORDER_BY_FIRSTNAME_LASTNAME;

            }

        if (len <= KLimitLength && len > KMinimumSearchPatternLength)

            {

            return CreateQuery(newPattern);

            }

        return QString(""); // Invalid pattern

        }

	}

QString CntSqlSearch::selectQweryTable(const QString &pattern) const

    {

    QString tableNumber; 

    if(pattern.length() > 0)

        {

        return QwertyTableName + tableNumber.setNum(mQertyKeyMap->MapKeyNameToValue(pattern[0]));

        }

    else

        {

        return QString("");

        }

    }

QString CntSqlSearch::SelectTable(const QString &pattern) const

	{

        QString predictivesearch;

        QStringList tokens = GetTokens(pattern);

        bool ok;

        if (pattern.length() == 0)

                {

                return "";

                }

        QString firstNumber(pattern.at(0));

        uint hex = firstNumber.toUInt(&ok, 16);

        if (!ok)

            {

            // TODO: handle error (=invalid characters in pattern)

            }

        switch (hex)

            {

            case 0:

                {

                predictivesearch = QString("predictivesearch0");

                }

            break;

            case 1:

                {

                predictivesearch = QString("predictivesearch1");

                }

            break;

            case 2:

                {

                predictivesearch = QString("predictivesearch2");

                }

            break;

            case 3:

                {

                predictivesearch = QString("predictivesearch3");

                }

            break;

            case 4:

                {

                predictivesearch = QString("predictivesearch4");

                }

            break;

            case 5:

                {

                predictivesearch = QString("predictivesearch5");

                }

            break;

            case 6:

                {

                predictivesearch = QString("predictivesearch6");

                }

            break;

            case 7:

                {

                predictivesearch = QString("predictivesearch7");

                }

            break;

            case 8:

                {

                predictivesearch = QString("predictivesearch8");

                }

            break;

            case 9:

                {

                predictivesearch = QString("predictivesearch9");

                }

             break;

            case 10:

                {

                predictivesearch = QString("predictivesearch10");

                }

            break;

            case 11:

                {

                predictivesearch = QString("predictivesearch11");

                }

            break;

                    default: // error

                            predictivesearch = "";

                            break;

            }

            return predictivesearch;

	}

// Even if there are over 2 tokens, make 2 tokens.

// If there are two or more tokens, include the leading zeros in the first

// token the and trailing zeros in the second token.

// E.g. "0010230" results tokens "001" and "230" and

// "001230045067800900" tokens "00123" and "45067800900".

QStringList CntSqlSearch::GetTokens(const QString& pattern) const

    {

    const QChar KZero('0');

    QStringList tokens = pattern.split(KZero, QString::SkipEmptyParts);

    if (tokens.count() < KTwoTokens)

        {

        return tokens;

        }

    QStringList twoTokens;

    int i(0);

    while (pattern[i] == KZero) // Skip leading zeros

        {

        ++i;

        }

    while (pattern[i] != KZero) // Skip non-zeros to find where first token ends

        {

        ++i;

        }

    twoTokens.append(pattern.left(i));

    while (pattern[i] == KZero) // Skip zeros to find where second token begins

        {

        ++i;

        }

    twoTokens.append(pattern.mid(i));

    return twoTokens;

    }

QString CntSqlSearch::CreateQwertyQuery(const QString& pattern) const

    {

    QStringList qwertyString; 

    qwertyString = qwertyTokens(pattern);

    if(qwertyString.count() == 1 )

        {

        if(qwertyString.at(0).length() == 1)

            {

            return SELECT_CONTACT_ID + selectQweryTable(qwertyString.at(0)) + ORDER_BY_FIRSTNAME_LASTNAME;

            }

        else if (qwertyString.at(0).length() > 1)

            {

            return ExactMatchSearchQwerty(qwertyString.at(0)) + ORDER_BY_FIRSTNAME_LASTNAME;

            }

        else

            {

            //Empty string

            return QString(""); 

            }

        }

    else

        {

        return SearchTokensFromOneTable(pattern, qwertyString, CntSqlSearch::QwertyEmail);

        }

    }

// pattern length is between KMinimumSearchPatternLength...KLimitLength

QString CntSqlSearch::CreateQuery(const QString& pattern) const

	{

	QStringList tokens = GetTokens(pattern);

	if (tokens.count() < KTwoTokens)

        {

	    if (TestPattern(pattern, CntSqlSearch::ZerosEndOfFirstToken))

            {

            return TwoDifferentTokensSearch(pattern, tokens);  // Case 6

            }

        else

            {

            return ExactMatchSearch(pattern) + Order(tokens); // Case 2

            }

        }

	else

        {

        if (tokens.at(0) == tokens.at(1))

            {

            return IdenticalTokensSearch(pattern, tokens); // Case 3

            }

        else

            {

            return IntersectionSearch(pattern, tokens); // Case 4

            }

        }

	}

QString CntSqlSearch::ExactMatchSearch(const QString& pattern) const

	{

	return QString(SELECT_CONTACT_ID + SelectTable(pattern) +

		           " WHERE " + ExactMatch(pattern)); 

	}

QString CntSqlSearch::ExactMatchSearchQwerty(const QString& pattern) const

    {

    return QString(SELECT_CONTACT_ID + selectQweryTable(pattern) +

                   " WHERE " + ExactMatchQwerty(pattern)); 

    }

// It has been checked that tokens are different, but they might begin with

// the same digit.

QString CntSqlSearch::IntersectionSearch(const QString& pattern,

									     const QStringList& tokens) const

	{

#if defined(SEARCH_FROM_ONE_TABLE)

	return SearchTokensFromOneTable(pattern, tokens);

#else

/* Query for pattern = "205":

SELECT predictivesearch2.contact_id FROM predictivesearch2 WHERE EXISTS

(SELECT contact_id FROM predictivesearch5 WHERE predictivesearch2.contact_id = predictivesearch5.contact_id)

OR

(SELECT contact_id FROM predictivesearch2 

WHERE(predictivesearch2.nbr>145522562959409152 AND predictivesearch2.nbr<145804037936119807) OR

(predictivesearch2.nbr2>145522562959409152 AND predictivesearch2.nbr2<145804037936119807) OR

(predictivesearch2.nbr3>145522562959409152 AND predictivesearch2.nbr3<145804037936119807) OR

(predictivesearch2.nbr4>145522562959409152 AND predictivesearch2.nbr4<145804037936119807))

ORDER BY predictivesearch2.first_name, predictivesearch2.last_name ASC;

This query works if both tokens have just one digit (e.g. "102", but not "1023")

*/	

	if (tokens.at(0).length() == KMinimumSearchPatternLength &&

		tokens.at(1).length() == KMinimumSearchPatternLength) 

		{

		// Case 4

		QString firstTable = SelectTable(tokens.at(0));

		QString secondTable = SelectTable(tokens.at(1));

		QString query =

			"SELECT " + firstTable + ".contact_id FROM " + firstTable +

			" WHERE EXISTS (" + SELECT_CONTACT_ID + secondTable + " WHERE " +

			firstTable + ".contact_id = " + secondTable + ".contact_id) OR (" +

			SELECT_CONTACT_ID + firstTable + " WHERE " + ExactMatch(pattern, firstTable) + ")";

		return query  + Order(tokens);

		}

	if (tokens.at(0).at(0) == tokens.at(1).at(0) ||

	    tokens.at(0).length() > 1 && tokens.at(1).length() > 1)

		{

		// Tokens begin with same digit or both tokens are longer than one digit.

		// Must search from one table.

		return SearchTokensFromOneTable(pattern, tokens);

		}

	return CreateJoinTableSearch(pattern, tokens); // Case 5

#endif

	}

// Find the exact match, or a column whose value is within

// lower..upper(exclusive) and another column whose value is within

// lower2..upper2(exclusive).

// In this case the limits are different, so there are 12 combinations the two

// values can exist in four columns:

// 

// (column = X  AND column2 = Y) OR

// (column = X  AND column3 = Y) OR

// (column = X  AND column4 = Y) OR

// (column2 = X AND column3 = Y) OR

// (column2 = X AND column4 = Y) OR

// (column3 = X AND column4 = Y) OR

// (column = Y  AND column2 = X) OR

// (column = Y  AND column3 = X) OR

// (column = Y  AND column4 = X) OR

// (column2 = Y AND column3 = X) OR

// (column2 = Y AND column4 = X) OR

// (column3 = Y AND column4 = X)

//

//

// Qwert case

// Where X means: (value > lower-limit AND value < upper-limit)

// and Y means: (value > lower-limit-2 AND value < upper-limit-2)

QString CntSqlSearch::SearchTokensFromOneTable(const QString& pattern,

											   const QStringList& tokens,

											   QueryType queryType) const

    {

    QString token;

    QString lower;

    QString upper;

    QString lower2;

    QString upper2;

    int err;

    if(queryType == CntSqlSearch::TwelveTable)

        {

        err = mkeyKeyMap->GetNumericLimits(tokens.at(0), lower, upper);

        if(err)

            {

            return QString("");

            }

        err = mkeyKeyMap->GetNumericLimits(tokens.at(1), lower2, upper2);

        if(err)

            {

            return QString("");

            }

        }

    else

        {

        err = mQertyKeyMap->GetNumericLimits(tokens.at(0), lower, upper);

        err = mQertyKeyMap->GetNumericLimits(tokens.at(1), lower2, upper2);

        if(err)

            {

            return QString("");

            }

        }

    QString query; 

    if (queryType == CntSqlSearch::TwelveTable)

		{

        query = SELECT_CONTACT_ID + SelectTable(pattern) + " WHERE NOT(NOT" +

	    ExactMatch(pattern) + " AND NOT" +

		CompareTwoColumns(lower, upper, lower2, upper2) + " AND NOT" +

        CompareTwoColumns(lower2, upper2, lower, upper) + ")";

		}

    else

        {

        query = SELECT_CONTACT_ID + selectQweryTable(tokens.at(0)) + " WHERE NOT(NOT" +

        ExactMatchQwerty(pattern) + " AND NOT" +

        CompareTwoQwertyColumns(lower, upper, lower2, upper2) + " AND NOT" +

        CompareTwoQwertyColumns(lower2, upper2, lower, upper) + ")";

        }

	query += Order(tokens, queryType);

	return query;

	}

// Either an exact match is required, or tokens must be found, but not in the

// same column.

// Since tokens are identical, they have same limits, and one call to

// CompareTwoColumns() is enough.

QString CntSqlSearch::IdenticalTokensSearch(const QString& pattern,

										    const QStringList& tokens) const

	{

	QString token = tokens.at(0);

	QString lower;

	QString upper;

	TInt err = mkeyKeyMap->GetNumericLimits(token, lower, upper);

#if defined(USE_DEMORGAN)

	QString query(SELECT_CONTACT_ID + SelectTable(pattern) + " WHERE NOT(NOT" +

	    ExactMatch(pattern) +

        " AND NOT" + CompareTwoColumns(lower, upper, lower, upper) + ")");

#else

	QString query(SELECT_CONTACT_ID + SelectTable(pattern) + " WHERE (" +

	    ExactMatch(pattern) +  // exact match (e.g. "101")

        ") OR " + CompareTwoColumns(lower, upper, lower, upper));

#endif

	query += Order(tokens);

	return query;

	}

QString CntSqlSearch::TwoDifferentTokensSearch(const QString& pattern, const QStringList& tokens) const

        {

        QString token = tokens.at(0);

        QString sortPatern = pattern;

        sortPatern.truncate(pattern.length()-1);

#if defined(USE_DEMORGAN)

        QString query(SELECT_CONTACT_ID + SelectTable(pattern) + " WHERE NOT(NOT" +

            ExactMatch(sortPatern) +

        " AND NOT" + ExactMatch(pattern) + ")");

#else

        QString query(SELECT_CONTACT_ID + SelectTable(pattern) + " WHERE (" +

            ExactMatch(sortPatern) +  // exact match (e.g. "2")

        ") OR " + ExactMatch(pattern)); // exact match (e.g. "20")

#endif

        query += Order(tokens);

        return query;

        }

// Put individual AND / OR operations in such order that in most cases there is

// no need to evaluate all arguments of the AND / OR.

// In case of AND, put the less likely condition on the left side of AND.

// In case of OR, put the more likely condition on the left side of OR.

// Since 2nd column is more likely to be empty, compare it before 1st column.

// Since 1st & 2nd columns are more likely to contain a match, compare them

// before other column combinations (1st & 3rd, 2nd & 3rd etc)

QString CntSqlSearch::CompareTwoColumns(const QString& lower,

										const QString& upper,

										const QString& lower2,

										const QString& upper2) const

	{

#if defined(USE_DEMORGAN)

	// Using De Morgan's law to replace ORs with ANDs: