/* -*- Mode: C++; tab-width: 2; indent-tabs-mode: nil; c-basic-offset: 2 -*- */
/***************************************************************************
 *            database.cc
 *
 *  Thu Sep  6 10:59:07 CEST 2007
 *  Copyright 2007 Bent Bisballe Nyeng
 *  deva@aasimon.org
 ****************************************************************************/

/*
 *  This file is part of Pracro.
 *
 *  Pracro 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 2 of the License, or
 *  (at your option) any later version.
 *
 *  Pracro 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 General Public License
 *  along with Pracro; if not, write to the Free Software
 *  Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA.
 */
#include "database.h"

Database::Database(std::string hostname, std::string user, std::string password)
  : c("host=" + hostname + " user=" + user + " password=" + password + " dbname=pracro")
{
}

Database::~Database()
{
}

void Database::commit(std::string user,
                      std::string cpr,
                      Macro &_macro,
                      Fields &values,
                      time_t now)
{
  //  / Create transaction ID (transaction OID?)
  // {
  //  \ Commit transaction data

  // Commit all field values using transaction ID.

  // INSERT INTO transactions VALUES('cpr', 'macro', 'version', 'timestamp', 'user')
  // Returns INSERT oid count
  // count == 1, oid is oid of newly inserted transaction.
  
  // INSERT INTO fields VALUES('oid', 'field', 'value')

  std::string version = _macro.attributes["version"];
  std::string macro = _macro.attributes["name"];
  std::stringstream timestamp; timestamp << now;

  pqxx::work W(c);

  std::string ts =
    "INSERT INTO transactions"
    " VALUES('"+cpr+"', '"+macro+"', '"+version+"', '"+timestamp.str()+"', '"+user+"')";

  pqxx::result R = W.exec(ts);

  std::stringstream oid; oid << R.inserted_oid();

  std::map< std::string, std::string >::iterator i = values.begin();
  while(i != values.end()) {

    std::string fs =
      "INSERT INTO fields"
      " VALUES('"+oid.str()+"', '"+i->first+"', '"+i->second+"')";
    
    W.exec(fs);
    
    i++;
  }
  
  W.commit();

#if 0
  char timestamp[32];
  sprintf(timestamp, "%u", (unsigned int)now); 
  //  UID uid;

	try {
		pqxx::work W(c);

    //      std::string transid = transidbase + toString(idx);

    // Insert transaction entry
    std::string sql = "INSERT INTO transactions VALUES('" + 
      cpr + "', '" + 
      uid.toString() + "', '" + 
      commit.macro + "', '" + 
      commit.version + "', '" + 
      timestamp + "', '" + 
      user + "')";
    W.exec(sql);
    
    // Insert field entries
    Fields::iterator j = commit.fields.begin();
    while(j != commit.fields.end()) {
      //      std::string name = j->first;
      //      std::string value = j->second;

      sql = "INSERT INTO fields VALUES('" + 
        uid.toString() + "', '" + 
        j->first + "', '" + 
        j->second + "')";
      //        name + "', '" + 
      //        value + "')";
      W.exec(sql);
      
      j++;
    }

    uid++;
    
		W.commit();
	}	catch(const std::exception &e) {
    //		throw PostgreSQLException(e.what());
	}
#endif/*0*/  
}


Fields Database::getValues(std::string cpr,
                           std::vector< std::string > &fields,
                           time_t oldest)
{
  Fields v;
  pqxx::work W(c);

  std::vector< std::string >::iterator i = fields.begin();
  while(i != fields.end()) {

    // TODO: Return only results that are recent enough (use oldest in statement)
    std::string query = "SELECT name, value FROM fields WHERE name='" + (*i) + "'";
    pqxx::result R = W.exec(query);
  
    pqxx::result::const_iterator ri = R.begin();
    while(ri != R.end()) {
      pqxx::result::tuple t = *ri;

      v[t[0].c_str()] = t[1].c_str();

      ri++;
    }

    i++;
  }

  return v;
}

/*
int Database::getTransaction(cpr, transid)
{
  SELECT fields.name, fields.value
    FROM transactions, fields
    WHERE transactions.cpr='2003791613'
    AND transactions.transaction='1234567890'
    AND transactions.transaction=fields.transaction
}
*/

/*
int Database::getMakro(cpr, macro)
{
  SELECT fields.name, fields.value
    FROM transactions, fields
    WHERE transactions.cpr='2003791613'
    AND transactions.macro='dims'
    AND transactions.transaction=fields.transaction
}
*/

// som root
// # createuser -P -h localhost -U postgres
// # createdb -U postgres -h localhost pracro

/*
DROP DATABASE pracro;

CREATE DATABASE pracro
  WITH OWNER = pracro
       ENCODING = 'UNICODE'
       TABLESPACE = pg_default;

DROP TABLE transactions;

CREATE TABLE transactions
(
  "cpr" varchar(11),
  "makro" text,
  "version" text,
  "timestamp" bigint,
  "user" text
)
WITH OIDS;
ALTER TABLE transactions OWNER TO pracro;

DROP TABLE fields;

CREATE TABLE fields
(
  "transaction" oid,
  "name" text,
  "value" text
) 
WITH OIDS;
ALTER TABLE fields OWNER TO pracro;


primary key(oid) ??

// Get all matching fields
SELECT transactions.timestamp, transactions.transaction, fields.name, fields.value
  FROM transactions, fields
  WHERE transactions.cpr='2003791613'
  AND transactions.transaction=fields.transaction
  AND fields.name='fisk';
*/

/*
  Employees:
  Employee_ID 	Name
  01            Hansen, Ola
  02            Svendson, Tove
  03            Svendson, Stephen
  04            Pettersen, Kari

  Orders:
  Prod_ID 	Product 	Employee_ID
  234       Printer   01
  657       Table     03
  865       Chair     03

  SELECT Employees.Name, Orders.Product
  FROM Employees, Orders
  WHERE Employees.Employee_ID=Orders.Employee_ID
*/

#ifdef TEST_DATABASE

int main()
{
  Database db;

  Macro macro;
  macro.attributes["name"] = "testmacro";
  macro.attributes["version"] = "1.0";
  
  Fields fields;
  fields["themeaning"] = "42";
  fields["microsoft"] = "waste of money";

  db.commit("testuser", "1505050505", macro, fields);

  std::vector< std::string > fieldnames;
  fieldnames.push_back("microsoft");
  fieldnames.push_back("themeaning");

  Fields results = db.getValues("1505050505", fieldnames);
  Fields::iterator i = results.begin();
  while(i != results.end()) {
    printf("%s -> %s\n", i->first.c_str(), i->second.c_str());
    i++;
  }
}

#endif/*TEST_DATABASE*/