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

  mp3db.c -- routines for storing mp3 info in a mysql database

  Copyright (c) 1999 by Mike Oliphant - oliphant@gtk.org

    http://www.ling.ed.ac.uk/~oliphant/grip

  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.

  You should have received a copy of the GNU General Public License
  along with this program; if not, write to the Free Software
  Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111, USA.

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


#include <stdlib.h>
#include <stdio.h>
#include <string.h>
#include <glib.h>
#include <mysql.h>
#include "ddj.h"

gboolean GotData(void);

extern MYSQL mysql;
char sql_host[61];
char sql_user[17];
char sql_pswd[17];
char sql_dbname[33];

gboolean ConnectSQL(void)
{
  if(!(mysql_connect(&mysql,sql_host,sql_user,sql_pswd))) {
    printf("SQL error: Unable to connect to server\n");

    return FALSE;
  }

  if(mysql_select_db(&mysql,sql_dbname)) {
    printf("SQL error: Unable to access %s database\n",sql_dbname);

    mysql_close(&mysql);

    return FALSE;
  }

  return TRUE;
}

void DisconnectSQL(void)
{
  mysql_close(&mysql);
}

gboolean SQLQuery(char *query)
{
  ConnectSQL();

  mysql_query(&mysql,query);

  DisconnectSQL();

  return TRUE;
}

gboolean InitSQL(char *rootpswd)
{
  if(!(mysql_connect(&mysql,sql_host,"root",rootpswd))) {
    printf("SQL error: Unable to connect to server\n");

    return FALSE;
  }

  if(mysql_create_db(&mysql,sql_dbname))
    printf("SQL error: Unable to create %s database\n",sql_dbname);

  if(mysql_select_db(&mysql,sql_dbname)) {
    printf("SQL error: Unable to access %s database\n",sql_dbname);

    mysql_close(&mysql);

    return FALSE;
  }

  if(mysql_query(&mysql,"CREATE TABLE artist (id INTEGER NOT NULL
AUTO_INCREMENT,artist VARCHAR(80),num_plays INT,last_play DATETIME,
PRIMARY KEY (id))"))
    printf("SQL error: Unable to create artist table\n");

  if(mysql_query(&mysql,"CREATE TABLE disc (id INTEGER NOT NULL AUTO_INCREMENT,
title VARCHAR(80),artistid INTEGER,num_plays INT,last_play DATETIME,
genre VARCHAR(20),year INT,PRIMARY KEY (id))"))
    printf("SQL error: Unable to create disc table\n");

  if(mysql_query(&mysql,"CREATE TABLE song (id INTEGER NOT NULL AUTO_INCREMENT,
filename VARCHAR(255),title VARCHAR(80),artistid INTEGER,discid INTEGER,genre
VARCHAR(20),track_num INTEGER,start_frame INTEGER,num_frames INTEGER,
play_time VARCHAR(5),bpm INTEGER,num_plays INT,last_play DATETIME,year INT,
PRIMARY KEY (id))"))
    printf("SQL error: Unable to create song table\n");


  mysql_close(&mysql);

  return TRUE;
}

gboolean ReloadSQL(char *rootpswd)
{
  if(!(mysql_connect(&mysql,sql_host,"root",rootpswd))) {
    printf("SQL error: Unable to connect to server\n");

    return FALSE;
  }

  mysql_reload(&mysql);

  mysql_close(&mysql);

  return TRUE;
}

gboolean MakeSQLUser(char *user,char *host,char *pswd,char *rootpswd)
{
  char query[256];

  if(!(mysql_connect(&mysql,sql_host,"root",rootpswd))) {
    printf("SQL error: Unable to connect to server\n");

    return FALSE;
  }

  if(mysql_select_db(&mysql,"mysql")) {
    printf("SQL error: Unable to connect to mysql admin database\n");
    
    mysql_close(&mysql);
    
    return FALSE;
  }

  snprintf(query,256,"INSERT INTO user (host,user,password) values('%s'
,'%s',password('%s'))",host,user,pswd);

  mysql_query(&mysql,query);

  snprintf(query,256,"INSERT INTO db (host,db,user,Select_priv,Insert_priv,
Update_priv,Delete_priv) values ('%s','%s','%s','Y','Y','Y','Y')",
	   host,sql_dbname,user);

  mysql_query(&mysql,query);

  mysql_close(&mysql);

  return TRUE;
}

gboolean DeleteSQLUser(char *user,char *host,char *rootpswd)
{
  char query[256];

  if(!(mysql_connect(&mysql,sql_host,"root",rootpswd))) {
    printf("SQL error: Unable to connect to server\n");

    return FALSE;
  }

  if(mysql_select_db(&mysql,"mysql")) {
    printf("SQL error: Unable to connect to mysql admin database\n");
    
    mysql_close(&mysql);
    
    return FALSE;
  }

  snprintf(query,256,"DELETE FROM user WHERE Host='%s' AND User='%s'",
	   host,user);

  mysql_query(&mysql,query);

  snprintf(query,256,"DELETE FROM db WHERE Db='%s' AND HOST='%s' AND
USER='%s'",
	   sql_dbname,host,user);

  mysql_query(&mysql,query);

  mysql_close(&mysql);

  return TRUE;
}

void EscapeQuote(char *in,char *out,int maxlen)
{
  int inpos,outpos;

  for(inpos=outpos=0;outpos<maxlen;inpos++,outpos++) {
    if(!in[inpos]) {
      out[outpos]='\0';

      return;
    }

    if(in[inpos]=='\'') {
      out[outpos++]='\\';
    }

    out[outpos]=in[inpos];
  }

  return;
}

int AddSQLArtist(char *artist)
{
  char query[1024];
  MYSQL_RES *res;
  MYSQL_ROW row;
  int id;

  snprintf(query,1024,"SELECT * FROM artist WHERE artist='%.80s'",artist);

  if(mysql_query(&mysql,query)) {
    printf("SQL error: Artist query failed\n");

    return -1;
  }
  
  if((res=mysql_store_result(&mysql))&&
     (row=mysql_fetch_row(res)))
    id=atoi(row[0]);
  else {
    snprintf(query,1024,"INSERT INTO artist (id,artist,num_plays,last_play)
VALUES (NULL,'%.80s',0,NULL)",artist);

    if(mysql_query(&mysql,query)) {
      printf("SQL error: Artist insert failed\n");

      id=-1;
    }
    else id=mysql_insert_id(&mysql);
  }

  if(res) mysql_free_result(res);

  return id;
}

int AddSQLDisc(char *disc,int artistid,char *genre,int year)
{
  char query[1024];
  int left=1024;
  MYSQL_RES *res;
  MYSQL_ROW row;
  int id;

  left-=snprintf(query,1024,
		 "SELECT * FROM disc WHERE title='%.80s' AND artistid=%d",
		 disc,artistid);

  if(mysql_query(&mysql,query)) {
    printf("SQL error: Disc query failed\n");

    return -1;
  }
  
  if((res=mysql_store_result(&mysql))&&
     (row=mysql_fetch_row(res)))
    id=atoi(row[0]);
  else {
    snprintf(query,1024,
	     "INSERT INTO disc (id,title,artistid,num_plays,last_play,genre,
year)VALUES (NULL,'%.80s',%d,0,NULL,",disc,artistid);

    if(*genre)
      left-=snprintf(query+strlen(query),left,"'%.20s',",genre);
    else left-=snprintf(query+strlen(query),left,"NULL,");

    if(year)
      left-=snprintf(query+strlen(query),left,"%d)",year);
    else 
      left-=snprintf(query+strlen(query),left,"NULL)");

    printf("query is %s\n",query);

    if(mysql_query(&mysql,query)) {
      printf("SQL error: Disc insert failed\n");

      id=-1;
    }
    else id=mysql_insert_id(&mysql);
  }

  if(res) mysql_free_result(res);

  return id;
}

gboolean GotData(void)
{
  MYSQL_RES *res;
  MYSQL_ROW row;

  if((res=mysql_store_result(&mysql))&&
     (row=mysql_fetch_row(res))) {
    if(res) mysql_free_result(res);

    return TRUE;
  }

  if(res) mysql_free_result(res);

  return FALSE;
}

int AddSQLEntry(char *filename,char *artist,char *sartist,char *title,
		char *disc_name,char *genre,int year,
		int track_num,int start_frame,int num_frames,int mins,
		int secs,int bpm)
{
  char query[1024];
  int left=1024;
  char tfilename[257],tartist[81],tsartist[81],ttitle[81],tdisc_name[81],
    tgenre[21],
    play_time[8];
  int artistid,discid;
  int sartistid;
  int id;

  EscapeQuote(filename,tfilename,256);
  EscapeQuote(artist,tartist,80);
  EscapeQuote(sartist,tsartist,80);
  EscapeQuote(title,ttitle,80);
  EscapeQuote(disc_name,tdisc_name,80);
  EscapeQuote(genre,tgenre,20);

  ConnectSQL();

  artistid=AddSQLArtist(tartist);

  if(*tsartist)
    sartistid=AddSQLArtist(tsartist);

  if(*tdisc_name)
    discid=AddSQLDisc(tdisc_name,artistid,tgenre,year);
  else discid=-1;

  if(discid!=-1)
    snprintf(query,1024,"SELECT * from song WHERE title='%.80s' AND artistid=%d
AND discid=%d",ttitle,(*tsartist)?sartistid:artistid,discid);
  else
    snprintf(query,1024,"SELECT * from song WHERE title='%.80s' AND artistid=%d
AND discid is NULL",ttitle,(*tsartist)?sartistid:artistid);

  if(mysql_query(&mysql,query)) {
    printf("SQL error: Song query failed\n");

    DisconnectSQL();

    return -1;
  }

  if(GotData()) {
    printf("MP3 already in database\n");

    DisconnectSQL();

    return -1;
  }

  if((mins!=-1)&&(secs!=-1))
    snprintf(play_time,8,"'%02d:%02d'",mins,secs);
  else strcpy(play_time,"NULL");

  if(*tsartist)
    left-=snprintf(query,left,"INSERT INTO song
(id,filename,title,artistid,discid,genre,track_num,start_frame,num_frames,
play_time,bpm,num_plays,last_play,year) VALUES (NULL,'%.256s','%.80s',%d,",
		   tfilename,ttitle,sartistid);
  else
    left-=snprintf(query,left,"INSERT INTO song
(id,filename,title,artistid,discid,genre,track_num,start_frame,num_frames,
play_time,bpm,num_plays,last_play,year) VALUES (NULL,'%.256s','%.80s',%d,",
		   tfilename,ttitle,artistid);

  if(discid!=-1)
    left-=snprintf(query+strlen(query),left,"%d,",discid);
  else left-=snprintf(query+strlen(query),left,"NULL,");

  if(*tgenre)
    left-=snprintf(query+strlen(query),left,"'%.20s',",tgenre);
  else left-=snprintf(query+strlen(query),left,"NULL,");
  
  if(track_num!=-1)
    left-=snprintf(query+strlen(query),left,"%d,",track_num);
  else left-=snprintf(query+strlen(query),left,"NULL,");

  if(start_frame!=-1)
    left-=snprintf(query+strlen(query),left,"%d,",start_frame);
  else left-=snprintf(query+strlen(query),left,"NULL,");

  if(num_frames!=-1)
    left-=snprintf(query+strlen(query),left,"%d,",num_frames);
  else left-=snprintf(query+strlen(query),left,"NULL,");

  left-=snprintf(query+strlen(query),left,"%s,",play_time);

  if(bpm!=-1)
    left-=snprintf(query+strlen(query),left,"%d",bpm);
  else left-=snprintf(query+strlen(query),left,"NULL");

  left-=snprintf(query+strlen(query),left,",0,NULL");

  if(year)
    left-=snprintf(query+strlen(query),left,",%d)",year);
  else
    left-=snprintf(query+strlen(query),left,",NULL)");

  printf("Query is [%s]\n",query);

  if(mysql_query(&mysql,query)) {
    printf("SQL error: Insert failed\n");
    id=-1;
  }
  else id=mysql_insert_id(&mysql);

  DisconnectSQL();

  return id;
}

gboolean DeleteSongByID(int id)
{
  char query[80];
  gboolean success=FALSE;

  ConnectSQL();

  snprintf(query,80,"DELETE from song where id=%d",id);

  if(mysql_query(&mysql,query)) {
    printf("SQL error: Delete failed\n");
  }
  else success=TRUE;

  DisconnectSQL();

  return success;
}