/*
    SPDX-FileCopyrightText: 2010 Fernando Vilas <tonybloom@users.sourceforge.net>
    SPDX-FileCopyrightText: 2010 Fernando Vilas <fvilas@iname.com>
    SPDX-License-Identifier: GPL-2.0-or-later
*/

#include "mymoneydbdef.h"

// ----------------------------------------------------------------------------
// QT Includes
#include <QDate>

// ----------------------------------------------------------------------------
// KDE Includes

// ----------------------------------------------------------------------------
// Project Includes
#include "mymoneydbdriver.h"
#include "mymoneyfile.h"
#include "mymoneyaccount.h"
#include "mymoneyexception.h"
#include "mymoneystoragemgr.h"

#include <alkimia/alkvalue.h>

//***************** THE CURRENT VERSION OF THE DATABASE LAYOUT ****************
unsigned int MyMoneyDbDef::m_currentVersion = 12;

// ************************* Build table descriptions ****************************
MyMoneyDbDef::MyMoneyDbDef()
{
    FileInfo();
    PluginInfo();
    Institutions();
    Payees();
    PayeesPayeeIdentifier();
    Tags();
    TagSplits(); // a table to bind tags and splits
    Accounts();
    AccountsPayeeIdentifier();
    Transactions();
    Splits();
    KeyValuePairs();
    Schedules();
    SchedulePaymentHistory();
    Securities();
    Prices();
    Currencies();
    Reports();
    Budgets();
    Balances();
    OnlineJobs();
    PayeeIdentifier();
    CostCenter();
}

/* PRIMARYKEY - these fields combine to form a unique key field on which the db will create an index
   NOTNULL - this field should never be null
   UNSIGNED - for numeric types, indicates the field is UNSIGNED
   ?ISKEY - where there is no primary key, these fields can be used to uniquely identify a record
  Default is that a field is not a part of a primary key, nullable, and if numeric, signed */

static const bool PRIMARYKEY = true;
static const bool NOTNULL = true;
static const bool UNSIGNED = false;

#define appendField(a) fields.append(QExplicitlySharedDataPointer<MyMoneyDbColumn>(new a))
void MyMoneyDbDef::FileInfo()
{
    QList< QExplicitlySharedDataPointer<MyMoneyDbColumn> > fields;
    appendField(MyMoneyDbColumn("version", "varchar(16)"));
    appendField(MyMoneyDbColumn("created", "date"));
    appendField(MyMoneyDbColumn("lastModified", "date"));
    appendField(MyMoneyDbColumn("baseCurrency", "char(3)"));
    appendField(MyMoneyDbIntColumn("institutions", MyMoneyDbIntColumn::BIG, UNSIGNED));
    appendField(MyMoneyDbIntColumn("accounts", MyMoneyDbIntColumn::BIG, UNSIGNED));
    appendField(MyMoneyDbIntColumn("payees", MyMoneyDbIntColumn::BIG, UNSIGNED));
    appendField(MyMoneyDbIntColumn("tags", MyMoneyDbIntColumn::BIG, UNSIGNED, false, false, 7));
    appendField(MyMoneyDbIntColumn("transactions", MyMoneyDbIntColumn::BIG, UNSIGNED));
    appendField(MyMoneyDbIntColumn("splits", MyMoneyDbIntColumn::BIG, UNSIGNED));
    appendField(MyMoneyDbIntColumn("securities", MyMoneyDbIntColumn::BIG, UNSIGNED));
    appendField(MyMoneyDbIntColumn("prices", MyMoneyDbIntColumn::BIG, UNSIGNED));
    appendField(MyMoneyDbIntColumn("currencies", MyMoneyDbIntColumn::BIG, UNSIGNED));
    appendField(MyMoneyDbIntColumn("schedules", MyMoneyDbIntColumn::BIG, UNSIGNED));
    appendField(MyMoneyDbIntColumn("reports", MyMoneyDbIntColumn::BIG, UNSIGNED));
    appendField(MyMoneyDbIntColumn("kvps", MyMoneyDbIntColumn::BIG, UNSIGNED));
    appendField(MyMoneyDbColumn("dateRangeStart", "date"));
    appendField(MyMoneyDbColumn("dateRangeEnd", "date"));
    appendField(MyMoneyDbIntColumn("hiInstitutionId", MyMoneyDbIntColumn::BIG, UNSIGNED));
    appendField(MyMoneyDbIntColumn("hiPayeeId", MyMoneyDbIntColumn::BIG, UNSIGNED));
    appendField(MyMoneyDbIntColumn("hiTagId", MyMoneyDbIntColumn::BIG, UNSIGNED, false, false, 7));
    appendField(MyMoneyDbIntColumn("hiAccountId", MyMoneyDbIntColumn::BIG, UNSIGNED));
    appendField(MyMoneyDbIntColumn("hiTransactionId", MyMoneyDbIntColumn::BIG, UNSIGNED));
    appendField(MyMoneyDbIntColumn("hiScheduleId", MyMoneyDbIntColumn::BIG, UNSIGNED));
    appendField(MyMoneyDbIntColumn("hiSecurityId", MyMoneyDbIntColumn::BIG, UNSIGNED));
    appendField(MyMoneyDbIntColumn("hiReportId", MyMoneyDbIntColumn::BIG, UNSIGNED));
    appendField(MyMoneyDbColumn("encryptData", "varchar(255)"));
    appendField(MyMoneyDbColumn("updateInProgress", "char(1)"));
    appendField(MyMoneyDbIntColumn("budgets", MyMoneyDbIntColumn::BIG, UNSIGNED, false, false, 1));
    appendField(MyMoneyDbIntColumn("hiBudgetId", MyMoneyDbIntColumn::BIG, UNSIGNED, false, false, 1));
    appendField(MyMoneyDbIntColumn("hiOnlineJobId", MyMoneyDbIntColumn::BIG, UNSIGNED, false, false, 8));
    appendField(MyMoneyDbIntColumn("hiPayeeIdentifierId", MyMoneyDbIntColumn::BIG, UNSIGNED, false, false, 8));
    appendField(MyMoneyDbColumn("logonUser", "varchar(255)", false, false, 1));
    appendField(MyMoneyDbDatetimeColumn("logonAt", false, false, 1));
    appendField(MyMoneyDbIntColumn("fixLevel",
                                   MyMoneyDbIntColumn::MEDIUM, UNSIGNED, false, false, 6));
    MyMoneyDbTable t("kmmFileInfo", fields);
    t.buildSQLStrings();
    m_tables[t.name()] = t;
}

void MyMoneyDbDef::Institutions()
{
    QList<QExplicitlySharedDataPointer <MyMoneyDbColumn> > fields;
    appendField(MyMoneyDbColumn("id", "varchar(32)", PRIMARYKEY, NOTNULL));
    appendField(MyMoneyDbTextColumn("name", MyMoneyDbTextColumn::NORMAL, false, NOTNULL));
    appendField(MyMoneyDbTextColumn("manager"));
    appendField(MyMoneyDbTextColumn("routingCode"));
    appendField(MyMoneyDbTextColumn("addressStreet"));
    appendField(MyMoneyDbTextColumn("addressCity"));
    appendField(MyMoneyDbTextColumn("addressZipcode"));
    appendField(MyMoneyDbTextColumn("telephone"));
    MyMoneyDbTable t("kmmInstitutions", fields);
    t.buildSQLStrings();
    m_tables[t.name()] = t;
}

void MyMoneyDbDef::Payees()
{
    QList<QExplicitlySharedDataPointer <MyMoneyDbColumn> > fields;
    appendField(MyMoneyDbColumn("id", "varchar(32)",  PRIMARYKEY, NOTNULL));
    appendField(MyMoneyDbTextColumn("name"));
    appendField(MyMoneyDbTextColumn("reference"));
    appendField(MyMoneyDbTextColumn("email"));
    appendField(MyMoneyDbTextColumn("addressStreet"));
    appendField(MyMoneyDbTextColumn("addressCity"));
    appendField(MyMoneyDbTextColumn("addressZipcode"));
    appendField(MyMoneyDbTextColumn("addressState"));
    appendField(MyMoneyDbTextColumn("telephone"));
    appendField(MyMoneyDbTextColumn("notes", MyMoneyDbTextColumn::LONG, false, false, 5));
    appendField(MyMoneyDbColumn("defaultAccountId", "varchar(32)", false, false, 5));
    appendField(MyMoneyDbIntColumn("matchData", MyMoneyDbIntColumn::TINY, UNSIGNED, false, false, 5));
    appendField(MyMoneyDbColumn("matchIgnoreCase", "char(1)", false, false, 5));
    appendField(MyMoneyDbTextColumn("matchKeys", MyMoneyDbTextColumn::MEDIUM, false, false, 5));
    MyMoneyDbTable t("kmmPayees", fields);
    t.buildSQLStrings();
    m_tables[t.name()] = t;
}

void MyMoneyDbDef::PayeesPayeeIdentifier()
{
    QList<QExplicitlySharedDataPointer <MyMoneyDbColumn> > fields;
    appendField(MyMoneyDbColumn("payeeId", "varchar(32)",  PRIMARYKEY, NOTNULL, 8));
    appendField(MyMoneyDbIntColumn("\"order\"", MyMoneyDbIntColumn::SMALL, UNSIGNED, PRIMARYKEY, NOTNULL, 8, 9));
    appendField(MyMoneyDbIntColumn("userOrder", MyMoneyDbIntColumn::SMALL, UNSIGNED, PRIMARYKEY, NOTNULL, 10));
    appendField(MyMoneyDbColumn("identifierId", "varchar(32)", false, NOTNULL, 8));
    MyMoneyDbTable t("kmmPayeesPayeeIdentifier", fields);
    t.addFieldNameChange("\"order\"", "userOrder", 10);
    t.buildSQLStrings();
    m_tables[t.name()] = t;
}

void MyMoneyDbDef::Tags()
{
    QList<QExplicitlySharedDataPointer <MyMoneyDbColumn> > fields;
    appendField(MyMoneyDbColumn("id", "varchar(32)",  PRIMARYKEY, NOTNULL));
    appendField(MyMoneyDbTextColumn("name"));
    appendField(MyMoneyDbColumn("closed", "char(1)", false, false, 5));
    appendField(MyMoneyDbTextColumn("notes", MyMoneyDbTextColumn::LONG, false, false, 5));
    appendField(MyMoneyDbTextColumn("tagColor"));
    MyMoneyDbTable t("kmmTags", fields);
    t.buildSQLStrings();
    m_tables[t.name()] = t;
}

void MyMoneyDbDef::TagSplits()
{
    QList<QExplicitlySharedDataPointer <MyMoneyDbColumn> > fields;
    appendField(MyMoneyDbColumn("transactionId", "varchar(32)", PRIMARYKEY, NOTNULL));
    appendField(MyMoneyDbColumn("tagId", "varchar(32)", PRIMARYKEY, NOTNULL));
    appendField(MyMoneyDbIntColumn("splitId", MyMoneyDbIntColumn::SMALL, UNSIGNED, PRIMARYKEY, NOTNULL));
    MyMoneyDbTable t("kmmTagSplits", fields);
    t.buildSQLStrings();
    m_tables[t.name()] = t;
}

void MyMoneyDbDef::Accounts()
{
    QList<QExplicitlySharedDataPointer <MyMoneyDbColumn> > fields;
    appendField(MyMoneyDbColumn("id", "varchar(32)",  PRIMARYKEY, NOTNULL));
    appendField(MyMoneyDbColumn("institutionId", "varchar(32)"));
    appendField(MyMoneyDbColumn("parentId", "varchar(32)"));
    appendField(MyMoneyDbDatetimeColumn("lastReconciled"));
    appendField(MyMoneyDbDatetimeColumn("lastModified"));
    appendField(MyMoneyDbColumn("openingDate", "date"));
    appendField(MyMoneyDbTextColumn("accountNumber"));
    appendField(MyMoneyDbColumn("accountType", "varchar(16)", false, NOTNULL));
    appendField(MyMoneyDbTextColumn("accountTypeString"));
    appendField(MyMoneyDbColumn("isStockAccount", "char(1)"));
    appendField(MyMoneyDbTextColumn("accountName"));
    appendField(MyMoneyDbTextColumn("description"));
    appendField(MyMoneyDbColumn("currencyId", "varchar(32)"));
    appendField(MyMoneyDbTextColumn("balance"));
    appendField(MyMoneyDbTextColumn("balanceFormatted"));
    appendField(MyMoneyDbIntColumn("transactionCount", MyMoneyDbIntColumn::BIG, UNSIGNED, false, false, 1));
    MyMoneyDbTable t("kmmAccounts", fields);
    t.buildSQLStrings();
    m_tables[t.name()] = t;
}

void MyMoneyDbDef::AccountsPayeeIdentifier()
{
    QList<QExplicitlySharedDataPointer <MyMoneyDbColumn> > fields;
    appendField(MyMoneyDbColumn("accountId", "varchar(32)",  PRIMARYKEY, NOTNULL, 8));
    appendField(MyMoneyDbIntColumn("\"order\"", MyMoneyDbIntColumn::SMALL, UNSIGNED, PRIMARYKEY, NOTNULL, 8, 9));
    appendField(MyMoneyDbIntColumn("userOrder", MyMoneyDbIntColumn::SMALL, UNSIGNED, PRIMARYKEY, NOTNULL, 10));
    appendField(MyMoneyDbColumn("identifierId", "varchar(32)", false, NOTNULL, 8));
    MyMoneyDbTable t("kmmAccountsPayeeIdentifier", fields);
    t.addFieldNameChange("\"order\"", "userOrder", 10);
    t.buildSQLStrings();
    m_tables[t.name()] = t;
}

void MyMoneyDbDef::Transactions()
{
    QList<QExplicitlySharedDataPointer <MyMoneyDbColumn> > fields;
    appendField(MyMoneyDbColumn("id", "varchar(32)", PRIMARYKEY, NOTNULL));
    appendField(MyMoneyDbColumn("txType", "char(1)"));
    appendField(MyMoneyDbDatetimeColumn("postDate"));
    appendField(MyMoneyDbTextColumn("memo"));
    appendField(MyMoneyDbDatetimeColumn("entryDate"));
    appendField(MyMoneyDbColumn("currencyId", "char(3)"));
    appendField(MyMoneyDbTextColumn("bankId"));
    MyMoneyDbTable t("kmmTransactions", fields);
    t.buildSQLStrings();
    m_tables[t.name()] = t;
}

void MyMoneyDbDef::Splits()
{
    QList<QExplicitlySharedDataPointer <MyMoneyDbColumn> > fields;
    appendField(MyMoneyDbColumn("transactionId", "varchar(32)",  PRIMARYKEY, NOTNULL));
    appendField(MyMoneyDbColumn("txType", "char(1)"));
    appendField(MyMoneyDbIntColumn("splitId", MyMoneyDbIntColumn::SMALL, UNSIGNED,  PRIMARYKEY, NOTNULL));
    appendField(MyMoneyDbColumn("payeeId", "varchar(32)"));
    appendField(MyMoneyDbDatetimeColumn("reconcileDate"));
    appendField(MyMoneyDbColumn("action", "varchar(16)"));
    appendField(MyMoneyDbColumn("reconcileFlag", "char(1)"));
    appendField(MyMoneyDbTextColumn("value", MyMoneyDbTextColumn::NORMAL, false, NOTNULL));
    appendField(MyMoneyDbColumn("valueFormatted", "text"));
    appendField(MyMoneyDbTextColumn("shares", MyMoneyDbTextColumn::NORMAL, false, NOTNULL));
    appendField(MyMoneyDbTextColumn("sharesFormatted"));
    appendField(MyMoneyDbTextColumn("price", MyMoneyDbTextColumn::NORMAL, false, false, 2));
    appendField(MyMoneyDbTextColumn("priceFormatted", MyMoneyDbTextColumn::MEDIUM, false, false, 2));
    appendField(MyMoneyDbTextColumn("memo"));
    appendField(MyMoneyDbColumn("accountId", "varchar(32)", false, NOTNULL));
    appendField(MyMoneyDbColumn("costCenterId", "varchar(32)", false, false, 9));
    appendField(MyMoneyDbColumn("checkNumber", "varchar(32)"));
    appendField(MyMoneyDbDatetimeColumn("postDate", false, false, 1));
    appendField(MyMoneyDbTextColumn("bankId", MyMoneyDbTextColumn::MEDIUM, false, false, 5));
    MyMoneyDbTable t("kmmSplits", fields);
    QStringList list;
    list << "accountId" << "txType";
    t.addIndex("kmmSplitsaccount_type", list, false);
    t.buildSQLStrings();
    m_tables[t.name()] = t;
}

void MyMoneyDbDef::KeyValuePairs()
{
    QList<QExplicitlySharedDataPointer <MyMoneyDbColumn> > fields;
    appendField(MyMoneyDbColumn("kvpType", "varchar(16)", false, NOTNULL));
    appendField(MyMoneyDbColumn("kvpId", "varchar(32)"));
    appendField(MyMoneyDbColumn("kvpKey", "varchar(255)", false, NOTNULL));
    appendField(MyMoneyDbTextColumn("kvpData"));
    MyMoneyDbTable t("kmmKeyValuePairs", fields);
    QStringList list;
    list << "kvpType" << "kvpId";
    t.addIndex("type_id", list, false);
    t.buildSQLStrings();
    m_tables[t.name()] = t;
}

void MyMoneyDbDef::Schedules()
{
    QList<QExplicitlySharedDataPointer <MyMoneyDbColumn> > fields;
    appendField(MyMoneyDbColumn("id", "varchar(32)", PRIMARYKEY, NOTNULL));
    appendField(MyMoneyDbTextColumn("name", MyMoneyDbTextColumn::NORMAL, false, NOTNULL));
    appendField(MyMoneyDbIntColumn("type", MyMoneyDbIntColumn::TINY, UNSIGNED, false, NOTNULL));
    appendField(MyMoneyDbTextColumn("typeString"));
    appendField(MyMoneyDbIntColumn("occurence", MyMoneyDbIntColumn::SMALL, UNSIGNED, false, // krazy:exclude=spelling
                                   NOTNULL));
    appendField(MyMoneyDbIntColumn("occurenceMultiplier", MyMoneyDbIntColumn::SMALL, UNSIGNED, // krazy:exclude=spelling
                                   false, NOTNULL, 3));
    appendField(MyMoneyDbTextColumn("occurenceString")); // krazy:exclude=spelling
    appendField(MyMoneyDbIntColumn("paymentType", MyMoneyDbIntColumn::TINY, UNSIGNED));
    appendField(MyMoneyDbTextColumn("paymentTypeString", MyMoneyDbTextColumn::LONG));
    appendField(MyMoneyDbColumn("startDate", "date", false, NOTNULL));
    appendField(MyMoneyDbColumn("endDate", "date"));
    appendField(MyMoneyDbColumn("fixed", "char(1)", false, NOTNULL));
    appendField(MyMoneyDbColumn("lastDayInMonth", "char(1)", false, NOTNULL, 11, std::numeric_limits<int>::max(), QLatin1String("N")));
    appendField(MyMoneyDbColumn("autoEnter", "char(1)", false, NOTNULL));
    appendField(MyMoneyDbColumn("lastPayment", "date"));
    appendField(MyMoneyDbColumn("nextPaymentDue", "date"));
    appendField(MyMoneyDbIntColumn("weekendOption", MyMoneyDbIntColumn::TINY, UNSIGNED, false,
                                   NOTNULL));
    appendField(MyMoneyDbTextColumn("weekendOptionString"));
    MyMoneyDbTable t("kmmSchedules", fields);
    t.buildSQLStrings();
    m_tables[t.name()] = t;
}

void MyMoneyDbDef::SchedulePaymentHistory()
{
    QList<QExplicitlySharedDataPointer <MyMoneyDbColumn> > fields;
    appendField(MyMoneyDbColumn("schedId", "varchar(32)", PRIMARYKEY, NOTNULL));
    appendField(MyMoneyDbColumn("payDate", "date", PRIMARYKEY,  NOTNULL));
    MyMoneyDbTable t("kmmSchedulePaymentHistory", fields);
    t.buildSQLStrings();
    m_tables[t.name()] = t;
}

void MyMoneyDbDef::Securities()
{
    QList<QExplicitlySharedDataPointer <MyMoneyDbColumn> > fields;
    appendField(MyMoneyDbColumn("id", "varchar(32)", PRIMARYKEY, NOTNULL));
    appendField(MyMoneyDbColumn("name", "text", false, NOTNULL));
    appendField(MyMoneyDbTextColumn("symbol"));
    appendField(MyMoneyDbIntColumn("type", MyMoneyDbIntColumn::SMALL, UNSIGNED, false, NOTNULL));
    appendField(MyMoneyDbTextColumn("typeString"));
    appendField(MyMoneyDbColumn("smallestAccountFraction", "varchar(24)"));
    appendField(MyMoneyDbIntColumn("pricePrecision", MyMoneyDbIntColumn::SMALL, UNSIGNED, false, NOTNULL, 11, std::numeric_limits<int>::max(), QLatin1String("4")));
    appendField(MyMoneyDbTextColumn("tradingMarket"));
    appendField(MyMoneyDbColumn("tradingCurrency", "char(3)"));
    appendField(MyMoneyDbIntColumn("roundingMethod", MyMoneyDbIntColumn::SMALL, UNSIGNED, false, NOTNULL, 11, std::numeric_limits<int>::max(), QString("%1").arg(AlkValue::RoundRound)));
    MyMoneyDbTable t("kmmSecurities", fields);
    t.buildSQLStrings();
    m_tables[t.name()] = t;
}

void MyMoneyDbDef::Prices()
{
    QList<QExplicitlySharedDataPointer <MyMoneyDbColumn> > fields;
    appendField(MyMoneyDbColumn("fromId", "varchar(32)", PRIMARYKEY, NOTNULL));
    appendField(MyMoneyDbColumn("toId", "varchar(32)",  PRIMARYKEY, NOTNULL));
    appendField(MyMoneyDbColumn("priceDate", "date", PRIMARYKEY, NOTNULL));
    appendField(MyMoneyDbTextColumn("price", MyMoneyDbTextColumn::NORMAL, false, NOTNULL));
    appendField(MyMoneyDbTextColumn("priceFormatted"));
    appendField(MyMoneyDbTextColumn("priceSource"));
    MyMoneyDbTable t("kmmPrices", fields);
    t.buildSQLStrings();
    m_tables[t.name()] = t;
}

void MyMoneyDbDef::Currencies()
{
    QList<QExplicitlySharedDataPointer <MyMoneyDbColumn> > fields;
    appendField(MyMoneyDbColumn("ISOcode", "char(3)", PRIMARYKEY, NOTNULL));
    appendField(MyMoneyDbTextColumn("name", MyMoneyDbTextColumn::NORMAL, false, NOTNULL));
    appendField(MyMoneyDbIntColumn("type", MyMoneyDbIntColumn::SMALL, UNSIGNED));
    appendField(MyMoneyDbTextColumn("typeString"));
    appendField(MyMoneyDbIntColumn("symbol1", MyMoneyDbIntColumn::SMALL, UNSIGNED));
    appendField(MyMoneyDbIntColumn("symbol2", MyMoneyDbIntColumn::SMALL, UNSIGNED));
    appendField(MyMoneyDbIntColumn("symbol3", MyMoneyDbIntColumn::SMALL, UNSIGNED));
    appendField(MyMoneyDbColumn("symbolString", "varchar(255)"));
    appendField(MyMoneyDbColumn("smallestCashFraction", "varchar(24)"));
    appendField(MyMoneyDbColumn("smallestAccountFraction", "varchar(24)"));
    // the default for price precision was taken from MyMoneySecurity
    appendField(MyMoneyDbIntColumn("pricePrecision", MyMoneyDbIntColumn::SMALL, UNSIGNED, false, NOTNULL, 11, std::numeric_limits<int>::max(), QLatin1String("4")));
    MyMoneyDbTable t("kmmCurrencies", fields);
    t.buildSQLStrings();
    m_tables[t.name()] = t;
}

void MyMoneyDbDef::Reports()
{
    QList<QExplicitlySharedDataPointer <MyMoneyDbColumn> > fields;
    appendField(MyMoneyDbColumn("name", "varchar(255)", false, NOTNULL));
    appendField(MyMoneyDbTextColumn("XML", MyMoneyDbTextColumn::LONG));
    appendField(MyMoneyDbColumn("id", "varchar(32)", PRIMARYKEY, NOTNULL, 6));
    MyMoneyDbTable t("kmmReportConfig", fields);
    t.buildSQLStrings();
    m_tables[t.name()] = t;
}

void MyMoneyDbDef::OnlineJobs()
{
    QList<QExplicitlySharedDataPointer <MyMoneyDbColumn> > fields;

    appendField(MyMoneyDbColumn("id", "varchar(32)", PRIMARYKEY, NOTNULL, 8));
    appendField(MyMoneyDbColumn("type", "varchar(255)", false, NOTNULL, 8));
    appendField(MyMoneyDbDatetimeColumn("jobSend", false, false, 8));
    appendField(MyMoneyDbDatetimeColumn("bankAnswerDate", false, false, 8));
    appendField(MyMoneyDbColumn("state", "varchar(15)", false, NOTNULL, 8));
    appendField(MyMoneyDbColumn("locked", "char(1)", false, NOTNULL, 8));

    MyMoneyDbTable t("kmmOnlineJobs", fields);
    t.buildSQLStrings();
    m_tables[t.name()] = t;
}

void MyMoneyDbDef::PayeeIdentifier()
{
    QList<QExplicitlySharedDataPointer <MyMoneyDbColumn> > fields;

    appendField(MyMoneyDbColumn("id", "varchar(32)", PRIMARYKEY, NOTNULL, 8));
    appendField(MyMoneyDbColumn("type", "varchar(255)", false, false, 8));

    MyMoneyDbTable t("kmmPayeeIdentifier", fields);
    t.buildSQLStrings();
    m_tables[t.name()] = t;
}

void MyMoneyDbDef::PluginInfo()
{
    QList<QExplicitlySharedDataPointer <MyMoneyDbColumn> > fields;

    appendField(MyMoneyDbColumn("iid", "varchar(255)", PRIMARYKEY, NOTNULL, 8));
    appendField(MyMoneyDbIntColumn("versionMajor", MyMoneyDbIntColumn::TINY, false, false, NOTNULL, 8));
    appendField(MyMoneyDbIntColumn("versionMinor", MyMoneyDbIntColumn::TINY, false, false, false, 8));
    appendField(MyMoneyDbTextColumn("uninstallQuery", MyMoneyDbTextColumn::LONG, false, false, 8));

    MyMoneyDbTable t("kmmPluginInfo", fields);
    t.buildSQLStrings();
    m_tables[t.name()] = t;
}

void MyMoneyDbDef::Budgets()
{
    QList<QExplicitlySharedDataPointer <MyMoneyDbColumn> > fields;
    appendField(MyMoneyDbColumn("id", "varchar(32)", PRIMARYKEY, NOTNULL));
    appendField(MyMoneyDbColumn("name", "text", false, NOTNULL));
    appendField(MyMoneyDbColumn("start", "date", false, NOTNULL));
    appendField(MyMoneyDbTextColumn("XML", MyMoneyDbTextColumn::LONG));
    MyMoneyDbTable t("kmmBudgetConfig", fields);
    t.buildSQLStrings();
    m_tables[t.name()] = t;
}

void MyMoneyDbDef::CostCenter()
{
    QList<QExplicitlySharedDataPointer<MyMoneyDbColumn> > fields;
    appendField(MyMoneyDbColumn("id", "varchar(32)", PRIMARYKEY, NOTNULL));
    appendField(MyMoneyDbColumn("name", "text", false, NOTNULL));
    MyMoneyDbTable t("kmmCostCenter", fields);
    t.buildSQLStrings();
    m_tables[t.name()] = t;
}

void MyMoneyDbDef::Balances()
{
    MyMoneyDbView v("kmmBalances", "CREATE VIEW kmmBalances AS "
                    "SELECT kmmAccounts.id AS id, kmmAccounts.currencyId, "
                    "kmmSplits.txType, kmmSplits.value, kmmSplits.shares, "
                    "kmmSplits.postDate AS balDate, "
                    "kmmTransactions.currencyId AS txCurrencyId "
                    "FROM kmmAccounts, kmmSplits, kmmTransactions "
                    "WHERE kmmSplits.txType = 'N' "
                    "AND kmmSplits.accountId = kmmAccounts.id "
                    "AND kmmSplits.transactionId = kmmTransactions.id;");
    m_views[v.name()] = v;
}


// function to write create SQL to a stream
const QString MyMoneyDbDef::generateSQL(const QExplicitlySharedDataPointer<MyMoneyDbDriver>& driver) const
{
    QString retval;

    // Add the CREATE TABLE strings
    table_iterator tt = tableBegin();
    while (tt != tableEnd()) {
        retval += (*tt).generateCreateSQL(driver) + '\n';
        ++tt;
    }

    // Add the CREATE OR REPLACE VIEW strings
    view_iterator vt = viewBegin();
    while (vt != viewEnd()) {
        retval += (*vt).createString() + '\n';
        ++vt;
    }
    retval += '\n';

    // Add the strings to populate kmmFileInfo with initial values
    MyMoneyDbTable fi = m_tables["kmmFileInfo"];
    QString qs = fi.insertString();
    MyMoneyDbTable::field_iterator fit;
    for (fit = fi.begin(); fit != fi.end(); ++fit) {
        QString toReplace = (*fit)->name();
        toReplace.prepend(':');
        QString replace = "NULL";
        if ((*fit)->name() == "version")
            replace = QString::number(m_currentVersion);
        if ((*fit)->name() == "fixLevel")
            replace =  QString::number
                       (MyMoneyFile::instance()->storage()->currentFixVersion());
        if ((*fit)->name() == "created")
            replace = QLatin1Char('\'')
                      + QDate::currentDate().toString(Qt::ISODate)
                      + QLatin1Char('\'');
        if ((*fit)->name() == "lastModified")
            replace = QLatin1Char('\'')
                      + QDate::currentDate().toString(Qt::ISODate)
                      + QLatin1Char('\'');
        if ((*fit)->name() == "updateInProgress")
            replace = enclose("N");

        qs.replace(QRegExp(toReplace + "(?=[,\\s\\)])"), replace);
        // only replace parameters followed by comma, whitespace, closing parenthesis - otherwise
        // conflicts may occur if one parameter starts with the name of another one.
    }
    qs += "\n\n";
    retval += qs;

    // Add the strings to create the initial accounts
    qs.clear();
    QList<MyMoneyAccount> stdList;
    stdList.append(MyMoneyFile::instance()->asset());
    stdList.append(MyMoneyFile::instance()->equity());
    stdList.append(MyMoneyFile::instance()->expense());
    stdList.append(MyMoneyFile::instance()->income());
    stdList.append(MyMoneyFile::instance()->liability());
    for (int i = 0; i < stdList.count(); ++i) {
        MyMoneyAccount* pac = &stdList[i];
        MyMoneyDbTable ac = m_tables["kmmAccounts"];
        qs = ac.insertString();
        MyMoneyDbTable::field_iterator act;
        // do the following in reverse so the 'formatted' fields are
        // correctly handled.
        // Hmm, how does one use a QValueListIterator in reverse
        // It'll be okay in Qt4 with QListIterator
        for (act = ac.end(), --act; act != ac.begin(); --act) {
            QString toReplace = (*act)->name();
            toReplace.prepend(':');
            QString replace = "NULL";
            if ((*act)->name() == "accountType")
                replace = QString::number((int)pac->accountType());
            if ((*act)->name() == "accountTypeString")
                replace = enclose(pac->name());
            if ((*act)->name() == "isStockAccount")
                replace = enclose("N");
            if ((*act)->name() == "accountName")
                replace = enclose(pac->name());
            qs.replace(toReplace, replace);
        }
        qs.replace(":id", enclose(pac->id()));  // a real kludge
        qs += "\n\n";
        retval += qs;
    }

    return retval;
}

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

void MyMoneyDbTable::addIndex(const QString& name, const QStringList& columns, bool unique)
{
    m_indices.push_back(MyMoneyDbIndex(m_name, name, columns, unique));
}

void MyMoneyDbTable::buildSQLStrings()
{
    // build fixed SQL strings for this table
    // build the insert string with placeholders for each field
    QString qs = QString("INSERT INTO %1 (").arg(name());
    QString ws = ") VALUES (";
    field_iterator ft = m_fields.constBegin();
    while (ft != m_fields.constEnd()) {
        qs += QString("%1, ").arg((*ft)->name());
        ws += QString(":%1, ").arg((*ft)->name());
        ++ft;
    }
    qs = qs.left(qs.length() - 2);
    ws = ws.left(ws.length() - 2);
    m_insertString = qs + ws + ");";
    // build a 'select all' string (select * is deprecated)
    // don't terminate with semicolon coz we may want a where or order clause
    m_selectAllString = "SELECT " + columnList() + " FROM " + name();

    // build an update string; key fields go in the where clause
    qs = "UPDATE " + name() + " SET ";
    ws.clear();
    ft = m_fields.constBegin();
    while (ft != m_fields.constEnd()) {
        if ((*ft)->isPrimaryKey()) {
            if (!ws.isEmpty()) ws += " AND ";
            ws += QString("%1 = :%2").arg((*ft)->name()).arg((*ft)->name());
        } else {
            qs += QString("%1 = :%2, ").arg((*ft)->name()).arg((*ft)->name());
        }
        ++ft;
    }
    qs = qs.left(qs.length() - 2);
    if (!ws.isEmpty()) qs += " WHERE " + ws;
    m_updateString = qs + ';';
    // build a delete string; where clause as for update
    qs = "DELETE FROM " + name();
    if (!ws.isEmpty()) qs += " WHERE " + ws;
    m_deleteString = qs + ';';

    // Setup the column name hash
    ft = m_fields.constBegin();
    m_fieldOrder.reserve(m_fields.size());
    int i = 0;
    while (ft != m_fields.constEnd()) {
        m_fieldOrder[(*ft)->name()] = i;
        ++i;
        ++ft;
    }
}

const QString MyMoneyDbTable::columnList(const int version, bool useNewNames) const
{
    field_iterator ft = m_fields.begin();
    QString qs;
    ft = m_fields.begin();
    while (ft != m_fields.end()) {
        if ((*ft)->initVersion() <= version && (*ft)->lastVersion() >= version) {
            QString fieldName = (*ft)->name();
            if (useNewNames && m_newFieldNames.contains(fieldName)) {
                if (m_newFieldNames[fieldName].first == version+1) {
                    fieldName = m_newFieldNames[fieldName].second;
                }
            }
            qs += QString("%1, ").arg(fieldName);
        }
        ++ft;
    }
    return (qs.left(qs.length() - 2));
}

const QString MyMoneyDbTable::generateCreateSQL(const QExplicitlySharedDataPointer<MyMoneyDbDriver>& driver, int version) const
{
    QString qs = QString("CREATE TABLE %1 (").arg(name());
    QString pkey;
    for (field_iterator it = m_fields.begin(); it != m_fields.end(); ++it) {
        if ((*it)->initVersion() <= version && (*it)->lastVersion() >= version) {
            qs += (*it)->generateDDL(driver) + ", ";
            if ((*it)->isPrimaryKey())
                pkey += (*it)->name() + ", ";
        }
    }

    if (!pkey.isEmpty()) {
        qs += "PRIMARY KEY (" + pkey;
        qs = qs.left(qs.length() - 2) + "))";
    } else {
        qs = qs.left(qs.length() - 2) + ')';
    }

    qs += driver->tableOptionString();
    qs += ";\n";

    for (index_iterator ii = m_indices.begin(); ii != m_indices.end(); ++ii) {
        qs += (*ii).generateDDL(driver);
    }
    return qs;
}

const QString MyMoneyDbTable::dropPrimaryKeyString(const QExplicitlySharedDataPointer<MyMoneyDbDriver>& driver) const
{
    return driver->dropPrimaryKeyString(m_name);
}

bool MyMoneyDbTable::hasPrimaryKey(int version) const
{
    field_iterator ft = m_fields.constBegin();
    while (ft != m_fields.constEnd()) {
        if ((*ft)->initVersion() <= version && (*ft)->lastVersion() >= version) {
            if ((*ft)->isPrimaryKey())
                return (true);
        }
        ++ft;
    }
    return (false);
}

const QString MyMoneyDbTable::modifyColumnString(const QExplicitlySharedDataPointer<MyMoneyDbDriver>& driver, const QString& columnName, const MyMoneyDbColumn& newDef) const
{
    return driver->modifyColumnString(m_name, columnName, newDef);
}

int MyMoneyDbTable::fieldNumber(const QString& name) const
{
    QHash<QString, int>::ConstIterator i = m_fieldOrder.find(name);
    if (m_fieldOrder.constEnd() == i) {
        throw MYMONEYEXCEPTION(QString::fromLatin1("Unknown field %1 in table %2").arg(name).arg(m_name));
    }
    return i.value();
}

void MyMoneyDbTable::addFieldNameChange(const QString& fromName, const QString& toName, int version)
{
    Q_ASSERT(m_newFieldNames.contains(fromName) == false);
    m_newFieldNames[fromName] = qMakePair(version, toName);
}

//*****************************************************************************
const QString MyMoneyDbIndex::generateDDL(const QExplicitlySharedDataPointer<MyMoneyDbDriver>& driver) const
{
    Q_UNUSED(driver);

    QString qs = "CREATE ";

    if (m_unique)
        qs += "UNIQUE ";

    qs += "INDEX " + m_table + '_' + m_name + "_idx ON "
          + m_table + " (";

    // The following should probably be revised.  MySQL supports an index on
    // partial columns, but not on a function.  Postgres supports an index on
    // the result of an SQL function, but not a partial column.  There should be
    // a way to merge these, and support other DBMSs like SQLite at the same time.
    // For now, if we just use plain columns, this will work fine.
    for (QStringList::ConstIterator it = m_columns.constBegin(); it != m_columns.constEnd(); ++it) {
        qs += *it + ',';
    }

    qs = qs.left(qs.length() - 1) + ");\n";

    return qs;
}

//*****************************************************************************
// These are the actual column types.
//

MyMoneyDbColumn*         MyMoneyDbColumn::clone() const
{
    return (new MyMoneyDbColumn(*this));
}

MyMoneyDbIntColumn*      MyMoneyDbIntColumn::clone() const
{
    return (new MyMoneyDbIntColumn(*this));
}

MyMoneyDbDatetimeColumn* MyMoneyDbDatetimeColumn::clone() const
{
    return (new MyMoneyDbDatetimeColumn(*this));
}

MyMoneyDbTextColumn* MyMoneyDbTextColumn::clone() const
{
    return (new MyMoneyDbTextColumn(*this));
}

const QString MyMoneyDbColumn::generateDDL(const QExplicitlySharedDataPointer<MyMoneyDbDriver>& driver) const
{
    Q_UNUSED(driver);

    QString qs = name() + ' ' + type();
    if (isNotNull()) qs += " NOT NULL";
    if (!defaultValue().isEmpty())
        qs += QString(" DEFAULT '%1'").arg(defaultValue());
    return qs;
}

const QString MyMoneyDbIntColumn::generateDDL(const QExplicitlySharedDataPointer<MyMoneyDbDriver>& driver) const
{
    QString qs = driver->intString(*this);
    if (!defaultValue().isEmpty())
        qs += QString(" DEFAULT %1").arg(defaultValue());
    return qs;
}

const QString MyMoneyDbTextColumn::generateDDL(const QExplicitlySharedDataPointer<MyMoneyDbDriver>& driver) const
{
    return driver->textString(*this);
}

const QString MyMoneyDbDatetimeColumn::generateDDL(const QExplicitlySharedDataPointer<MyMoneyDbDriver>& driver) const
{
    return driver->timestampString(*this);
}


