/*
 * (C) 2001 Christian Jacobi
 * (C) 2001 Christoph Berg
 *
 * This program 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.
 *
 * 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.
 */

// glibc needs this
#include <features.h>
#define _XOPEN_SOURCE 1
#define __USE_BSD 1
#define __USE_XOPEN 1
// 

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <time.h>
#include <libpq-fe.h>
#include <assert.h>

#include "db.h"
#include "log.h"
#include "help.h"

#define SQLSIZE 1024

PGconn *db;
FILE *errf;


char* __LEERELISTE__[] = { (char*)NULL };
char** LEERELISTE = __LEERELISTE__;


PGresult *tante_PQexec(PGconn *db, char *sql) {
	PGresult *res;
	if((res=PQexec(db, sql)) == NULL) {
		fprintf(stderr, "PG95 connection failed.\n");
		writelog(L_FATAL, "PG95 connection failed.");
		exit(1);
	}
	if(PQstatus(db) != PGRES_EMPTY_QUERY && PQstatus(db) !=
			PGRES_COMMAND_OK && PQstatus(db) != PGRES_TUPLES_OK) {
		fprintf(stderr, "PG95 command failed (%u): %s\n",
				PQstatus(db), PQerrorMessage(db));
		writelog(L_FATAL, "PG95 command failed (%u): %s\n",
				PQstatus(db), PQerrorMessage(db));
		exit(1);
	}
	return res;
}



/************************************************
 *** Connect zur DB
 ************************************************/

void init_db()
{
	PGresult *res;
	char pg_passwd[SQLSIZE];
	FILE *f;
	if((f = fopen(PG_PASSWD_FILE, "rt")) == NULL) {
		writelog(L_FATAL,
			"Could not read password file " PG_PASSWD_FILE "\n");
		fprintf(stderr, 
			"Could not read password file " PG_PASSWD_FILE "\n");
		exit(1);
	}
	fscanf(f, "%50s", pg_passwd);
	fclose(f);

	db = PQsetdbLogin(PG_HOST, PG_PORT, PG_OPTIONS, PG_TTY, PG_DBNAME,
			PG_LOGIN, pg_passwd);
	if (PQstatus(db) == CONNECTION_BAD) {
		writelog(L_FATAL,
				"Could not connect to database; PG95: %s; Exit...\n",
				PQerrorMessage(db));
		fprintf(stderr, "Could not connect to database; PG95: %s; Exit...\n",
				PQerrorMessage(db));

		exit(-1);
	}

	res=tante_PQexec(db, "SET datestyle TO 'iso'");
	PQclear(res);

}



/************************************************
 *** Schneidet string von der form "xxxx    "
 *** zur form "xxxx"
 ************************************************/

char* clipstr(char *s)
{
  char *x;

  assert(s!=NULL);

  if (*s==0) return s;

  x=s+strlen(s)-1; // letztes zeichen des strings

  while(x>=s && *x==' ') x--;
  *(x+1)=0;
  return s;
}


/************************************************
 *** Gibt Listen von Strings wieder frei
 ************************************************/

void freelist(char** list)
{
  int i;

  assert(list!=NULL);

  if (list==LEERELISTE) return;

  for(i=0; list[i]!=NULL; i++)
      free(list[i]);

  free(list);
}




/************************************************
 *** Return: eine Liste aller kunden-namen, die mit
 *** prefix beginnen. Die Liste muß von der
 *** aufrufennden funktion mittels freelist
 *** freigegeben werden
 ************************************************/

char** complete_name(const char* prefix)
{
  char sql[SQLSIZE];
  char** ret;
  int i;
  PGresult *res;

  assert(prefix!=NULL);

  if (strlen(prefix)>50) return LEERELISTE;

  snprintf(sql, SQLSIZE,
	   "SELECT nickname from kunden where nickname LIKE '%s%%'"
	  "and not locked", prefix);
  writelog(L_UNWICHTIG, "SQL##%s##\n", sql);

  res=tante_PQexec(db,  sql);

  writelog(L_UNWICHTIG, "%d recs found\n", PQntuples(res));

  ret=malloc(sizeof(char*)*(PQntuples(res)+6+1)); // 6 = extra Kommandos
  for (i=0; i<PQntuples(res); i++)
	{
	  ret[i]=malloc(strlen(PQgetvalue(res,i,0))+1);
	  strcpy(ret[i], PQgetvalue(res,i,0));
	  clipstr(ret[i]);
	}
  ret[i++] = strdup("buchungen"); // wichtig: extra Kommandos mallocen!
  ret[i++] = strdup("help");
  ret[i++] = strdup("hilfe");
  ret[i++] = strdup("info");
  ret[i++] = strdup("ueberweisung");
  ret[i++] = strdup("passwd");
  ret[i]=NULL;
  PQclear(res);
  return ret;
}



/************************************************
 *** Return: eine Liste aller artikel-namen, die mit
 *** prefix beginnen. Die Liste muß von der
 *** aufrufennden funktion mittels freelist
 *** freigegeben werden
 ************************************************/

char** complete_article(const char* prefix)
{
  char sql[SQLSIZE];
  char** ret;
  int i;
  PGresult *res;

  assert(prefix!=NULL);

  if (strlen(prefix)>50) return LEERELISTE;

  snprintf(sql, SQLSIZE,
	   "SELECT name from artikel where name LIKE '%s%%' and aid > 0",
	   prefix);
  writelog(L_UNWICHTIG, "SQL##%s##\n", sql);

  res=tante_PQexec(db,  sql);

  writelog(L_UNWICHTIG, "%d recs found\n", PQntuples(res));

  ret=malloc(sizeof(char*)*(PQntuples(res)+4+1)); // 4 = extra Kommandos
  for (i=0; i<PQntuples(res); i++)
	{
	  ret[i]=malloc(strlen(PQgetvalue(res,i,0))+1);
	  strcpy(ret[i], PQgetvalue(res,i,0));
	  clipstr(ret[i]);
	}
  ret[i++] = strdup("buchungen"); // wichtig: extra Kommandos mallocen!
  ret[i++] = strdup("info");
  ret[i++] = strdup("passwd");
  ret[i++] = strdup("ueberweisung");
  ret[i]=NULL;
  PQclear(res);
  return ret;
}




/************************************************
 *** Return: ein malloc'ed record, der den kunden
 *** mit uebergebenem nickname beschreibt.
 *** NULL falls not found
 ************************************************/
struct db_kunde* get_kunde_by_nickname(const char* nickname)
{
  char sql[SQLSIZE];
  static struct db_kunde kunde;
  struct db_kunde* ret = &kunde;
  PGresult *res;

  assert(nickname!=NULL);

  if (strlen(nickname)>50) return NULL;

  snprintf(sql, SQLSIZE,
	   "SELECT nickname, vorname, nachname, kid, email, pw, "
	  "guthaben, created, last_contact, locked FROM kunden "
	  "WHERE nickname = '%s'", nickname);
  writelog(L_UNWICHTIG, "SQL##%s##\n", sql);

  res=tante_PQexec(db,  sql);

  writelog(L_UNWICHTIG, "%d recs found\n", PQntuples(res));

  if (PQntuples(res) == 0)
    {
      writelog(L_UNWICHTIG, "No records found\n");
      return NULL;
    }

  // static // ret=malloc(sizeof(struct db_kunde));

  strcpy(ret->nickname, PQgetvalue(res,0,0)); clipstr(ret->nickname);
  strcpy(ret->vorname,  PQgetvalue(res,0,1)); clipstr(ret->vorname);
  strcpy(ret->nachname, PQgetvalue(res,0,2)); clipstr(ret->nachname);
  ret->kid      = atoi(PQgetvalue(res,0,3));
  strcpy(ret->email,    PQgetvalue(res,0,4)); clipstr(ret->email);
  strcpy(ret->pw,       PQgetvalue(res,0,5)); clipstr(ret->pw);
  ret->guthaben = atoi(PQgetvalue(res,0,6));
#ifdef DEBUG
  printf("created: %s\n", PQgetvalue(res,0,7));
  printf("last_contact: %s\n", PQgetvalue(res,0,8));
#endif
  strptime(PQgetvalue(res,0,7), "%Y-%m-%d %H:%M:%S", &ret->created);
  strptime(PQgetvalue(res,0,8), "%Y-%m-%d %H:%M:%S", &ret->last_contact);
  ret->locked = (*PQgetvalue(res,0,9)=='t');

  PQclear(res);
  return ret;
}




/************************************************
 *** Return: ein malloc'ed record, der den artikel
 *** mit uebergebenem name beschreibt.
 *** NULL falls not found
 ************************************************/
struct db_artikel* get_artikel_by_name(const char* name)
{
  char sql[SQLSIZE];
  static struct db_artikel artikel;
  struct db_artikel* ret = &artikel;
  PGresult *res;

  assert(name!=NULL);

  if (strlen(name)>50) return NULL;

  snprintf(sql, SQLSIZE,
	  "SELECT name, aid, preis, anz FROM artikel WHERE name = '%s'", 
	  name);
  writelog(L_UNWICHTIG, "SQL##%s##\n", sql);

  res=tante_PQexec(db,  sql);

  writelog(L_UNWICHTIG, "%d recs found\n", PQntuples(res));

  if (PQntuples(res) == 0)
    {
      writelog(L_UNWICHTIG, "No records found\n");
      return NULL;
    }
 
  // static // ret=malloc(sizeof(struct db_artikel));

  strcpy(ret->name, PQgetvalue(res,0,0)); clipstr(ret->name);
  ret->aid      = atoi(PQgetvalue(res,0,1));
  ret->preis    = atoi(PQgetvalue(res,0,2));
  ret->anz      = atoi(PQgetvalue(res,0,3));

  PQclear(res);
  return ret;
}


/************************************************
 *** Bucht Artikel a vom Kunden k ab.
 *** Return 0: alles ok, sonst exit mit assert
 ************************************************/
int artikel_einkauf(struct db_kunde* k, struct db_artikel* a, 
		    const char* bemerkung)
{
  char sql[SQLSIZE];
  PGresult *res;
  time_t dt;
  char *zeit;

  assert(k!=NULL);
  assert(a!=NULL);

  if (!bemerkung) bemerkung="";
  assert(strlen(bemerkung)<200);

  k->guthaben -= a->preis;
  a->anz++;

  dt=time(NULL);
  zeit=ctime(&dt);
  zeit[strlen(zeit)-1]=0; // remove \n

  snprintf(sql, SQLSIZE,
	  "INSERT INTO buchungen (kid,aid,zeit,betrag,guthaben_neu,bemerkung,zahlung)"
	  "VALUES (%d, %d, '%s', %d, %d, '%s', '%s'); "

	  "UPDATE kunden SET guthaben=guthaben-%d, last_contact='%s' WHERE kid=%d; "

	  "UPDATE artikel SET anz=anz+1 WHERE aid=%d;",
	  // insert
	  k->kid, a->aid, zeit, -a->preis, 
	  k->guthaben, bemerkung, "f",
	  // update kunden
	  a->preis, zeit, k->kid,
	  // update artikel
	  a->aid);

  writelog(L_UNWICHTIG, "SQL##%s##\n", sql);

  res=tante_PQexec(db,  sql);

  if (PQresultStatus(res) != PGRES_COMMAND_OK)
    {
      writelog(L_ERROR, "INSERT/UPDATE failed: %s, %s\n", 
	       sql, PQerrorMessage(db));
      PQclear(res);
      assert(PQresultStatus(res) == PGRES_COMMAND_OK); // exit
      exit(-1);
    }

  PQclear(res);

  writelog(L_LOG, "%s (%d) kauft %s (%d); preis %s, guth_neu %s\n",
	   k->nickname, k->kid, a->name, a->aid, betrag2s(a->preis),
	   betrag2s_2(k->guthaben));

  return 0;
}


/************************************************
 *** Überweist von k1 an k2
 *** Return 0: alles ok, sonst exit mit assert
 ************************************************/
int db_ueberweisung(struct db_kunde *k1, struct db_kunde *k2, int betrag,
		const char* bemerkung)
{
  char sql[SQLSIZE];
  PGresult *res;
  time_t dt;
  char *zeit;

  assert(k1!=NULL);
  assert(k2!=NULL);
  assert(k1 != k2);
  assert(betrag > 0);
  assert(bemerkung!=NULL);

  if (!bemerkung) bemerkung="";
  assert(strlen(bemerkung)<200);

  k1->guthaben -= betrag;
  k2->guthaben += betrag;

  dt=time(NULL);
  zeit=ctime(&dt);
  zeit[strlen(zeit)-1]=0; // remove \n

  snprintf(sql, SQLSIZE,
	  "INSERT INTO buchungen (kid,aid,zeit,betrag,guthaben_neu,bemerkung,"
	  "zahlung) VALUES (%d, 0, '%s', %d, %d, 'Ueberweisung an %s: %s', '%s'); "

	  "INSERT INTO buchungen (kid,aid,zeit,betrag,guthaben_neu,bemerkung,"
	  "zahlung) VALUES (%d, 0, '%s', %d, %d, 'Ueberweisung von %s: %s', '%s'); "

	  "UPDATE kunden SET guthaben=guthaben-%d, last_contact='%s' WHERE kid=%d; "

	  "UPDATE kunden SET guthaben=guthaben+%d WHERE kid=%d; ",

	  // insert
	  k1->kid, zeit, -betrag, k1->guthaben, k2->nickname, bemerkung, "t",
	  k2->kid, zeit,  betrag, k2->guthaben, k1->nickname, bemerkung, "t",
	  // update kunden
	  betrag, zeit, k1->kid,
	  betrag, k2->kid);

  writelog(L_UNWICHTIG, "SQL##%s##\n", sql);

  res=tante_PQexec(db,  sql);

  if (PQresultStatus(res) != PGRES_COMMAND_OK)
    {
      writelog(L_ERROR, "INSERT/UPDATE failed: %s, %s\n", 
	       sql, PQerrorMessage(db));
      PQclear(res);
      assert(PQresultStatus(res) == PGRES_COMMAND_OK); // exit
      exit(-1);
    }

  PQclear(res);

  writelog(L_LOG, "%s (%d) ueberweist an %s (%d); betrag %d, bemerkung: %s, guth_neu %s, %s\n",
	   k1->nickname, k1->kid,
	   k2->nickname, k2->kid,
	   betrag, bemerkung,
	   betrag2s(k1->guthaben), betrag2s_2(k2->guthaben));

  return 0;
}


int set_passwd(struct db_kunde* k, const char* new_passwd)
{
  char sql[SQLSIZE];
  PGresult *res;
  time_t dt;
  char *zeit;

  assert(k!=NULL);
  assert(new_passwd!=NULL);

  dt=time(NULL);
  zeit=ctime(&dt);
  zeit[strlen(zeit)-1]=0; // remove \n

  snprintf(sql, SQLSIZE, 
	   "UPDATE kunden SET pw='%s', last_contact='%s' WHERE kid=%d;",
	   new_passwd, zeit, k->kid);

  writelog(L_UNWICHTIG, "SQL##%s##\n", sql);

  res=tante_PQexec(db,  sql);

  if (PQresultStatus(res) != PGRES_COMMAND_OK)
    {
      writelog(L_ERROR, "INSERT/UPDATE failed: %s, %s\n", 
	       sql, PQerrorMessage(db));
      PQclear(res);
      assert(PQresultStatus(res) == PGRES_COMMAND_OK); // exit
      exit(-1);
    }

  PQclear(res);

  strcpy(k->pw, new_passwd);
  writelog(L_LOG, "%s (%d) hat neues passwort %s\n", 
	   k->nickname, k->kid, new_passwd);
  return 0;
}



void print_zahlungen(struct db_kunde* k)
{
  char sql[SQLSIZE];
  PGresult *res;
  int i,betrag;
  char bemerkung[1000];
  struct tm zeit;

  assert(k!=0);

  snprintf(sql, SQLSIZE,
	   "SELECT zeit, bemerkung, betrag FROM buchungen WHERE "
	   "kid=%d AND zeit > '%s' AND zahlung",
	   k->kid, asctime(&k->last_contact));

  writelog(L_UNWICHTIG, "SQL##%s##\n", sql);

  res=tante_PQexec(db,  sql);

  if (PQresultStatus(res) != PGRES_TUPLES_OK)
    {
      writelog(L_ERROR, "SELECT failed: %s, %s\n", 
	       sql, PQerrorMessage(db));
      PQclear(res);
      assert(PQresultStatus(res) == PGRES_TUPLES_OK); // exit
      exit(-1);
    }

  if (PQntuples(res) > 0) {
	printf("  Buchungen seit dem letzten Einkauf (%s):\n",
			tante_strftime(k->last_contact));
  }
  for (i=0; i<PQntuples(res); i++)
    {
      strptime(PQgetvalue(res,i,0), "%Y-%m-%d %H:%M:%S", &zeit);
      strcpy(bemerkung, PQgetvalue(res,i,1)); clipstr(bemerkung); 
      bemerkung[50]=0;
      betrag=atoi(PQgetvalue(res,i,2));

      printf("    %s:%7s EUR\n      %s\n",
	     tante_strftime(zeit), betrag2s(betrag), bemerkung);
    }
  PQclear(res);
}


void print_letzte_zahlungen(struct db_kunde* k)
{
  char sql[SQLSIZE];
  PGresult *res;
  int i,betrag,guthaben_neu;
  char bemerkung[1000];
  struct tm zeit;

  assert(k!=0);

  snprintf(sql, SQLSIZE,
	   "SELECT zeit, bemerkung, betrag, guthaben_neu FROM buchungen WHERE "
	   "kid=%d ORDER BY zeit DESC LIMIT 11",
	   k->kid);

  writelog(L_UNWICHTIG, "SQL##%s##\n", sql);

  res=tante_PQexec(db,  sql);

  if (PQresultStatus(res) != PGRES_TUPLES_OK)
    {
      writelog(L_ERROR, "SELECT failed: %s, %s\n", 
	       sql, PQerrorMessage(db));
      PQclear(res);
      assert(PQresultStatus(res) == PGRES_TUPLES_OK); // exit
      exit(-1);
    }

  printf("  Letzte Buchungen:         Betrag                Neues Guthaben\n");

  for (i=PQntuples(res)-1; i>=0; i--)
    {
      strptime(PQgetvalue(res,i,0), "%Y-%m-%d %H:%M:%S", &zeit);
      strcpy(bemerkung, PQgetvalue(res,i,1)); clipstr(bemerkung); 
      bemerkung[50]=0;
      betrag=atoi(PQgetvalue(res,i,2));
      guthaben_neu=atoi(PQgetvalue(res,i,3));

      printf("  %s:%7s EUR",
	     tante_strftime(zeit), betrag2s(betrag));
      printf("  %6s EUR\n    %s\n",
	     betrag2s(guthaben_neu), bemerkung);
    }
  PQclear(res);
}