#include "connection.h"
#include <QDebug>
#include <QSqlError>

//Q_GLOBAL_STATIC_WITH_ARGS(QString, add_musiclist_sql, ("insert into MusicList (name, description) values('%1','%2')"));
//Q_GLOBAL_STATIC_WITH_ARGS(QString, get_song_sql, ("SELECT id FROM SONG WHERE url = '%1'"));
//Q_GLOBAL_STATIC_WITH_ARGS(QString, add_song_sql, ("INSERT INTO SONG (name, singer, album, url, time) values(%1, %2, %3, %4, %5)"));
//Q_GLOBAL_STATIC_WITH_ARGS(QString, add_musiclist_song_sql, ("insert into MusicList_Song (musiclist_id, song_id) values('%1','%2')"));
//Q_GLOBAL_STATIC_WITH_ARGS(QString, get_song_from_musiclist_sql, ("SELECT * FROM Song as A INNER JOIN (select song_id from MusicList_Song where musislist_id = %1) as B ON A.id = B.song_id"));
//Q_GLOBAL_STATIC_WITH_ARGS(QString, get_musiclist_sql, ("select * from MusicList"));

QSqlDatabase Connection::db = QSqlDatabase::addDatabase("QSQLITE");
QSqlQuery* Connection::query = NULL;

static QString add_musiclist_sql = "insert into MusicList (name, description) values('%1','%2');";
static QString get_song_sql = "SELECT id FROM SONG WHERE url = '%1'";
static QString add_song_sql = "INSERT INTO SONG (name, singer, album, url, time) values('%1', '%2', '%3', '%4', %5)";
static QString add_musiclist_song_sql = "INSERT INTO MusicList_Song (musiclist_id, song_id) values(%1, %2)";
static QString get_song_from_musiclist_sql = "SELECT * FROM Song as A INNER JOIN (select song_id from MusicList_Song where musiclist_id = %1) as B ON A.id = B.song_id";
static QString get_musiclist_sql = "select * from MusicList";
static QString get_musiclist_song_sql = "SELECT id FROM MusicList_Song WHERE musiclist_id = %1 and song_id = %2";
static QString delete_musiclist_sql = "DELETE FROM Musiclist where id = %1";
static QString delete_song_from_musiclist_sql = "DELETE FROM Musiclist_Song where musiclist_id = %1 and song_id = %2";
static QString delete_all_song_from_musiclist_sql = "DELETE FROM Musiclist_Song where id = %1";

Connection::Connection()
{

}

bool Connection::createConnection()
{
    db.setDatabaseName("test.db");
    if (!db.open()) {
        return false;
    }
    query = new QSqlQuery(db);
    query->exec("CREATE table IF NOT EXISTS MusicList (id Integer primary key, "
               "name varchar(128),"
               "description varchar(256))");

    query->exec("CREATE table IF NOT EXISTS Song (id Integer primary key, "
               "name varchar(128) NOT NULL,"
               "singer varchar(48),"
               "album varchar(128),"
               "url varchar(256) NOT NULL,"
               "time int)");

    query->exec("CREATE table IF NOT EXISTS MusicList_Song (id Integer primary key, "
               "musiclist_id int NOT NULL,"
               "song_id int NOT NULL,"
               "foreign key(musiclist_id) references MusicList(ID),"
               "foreign key(song_id) references Song(ID))");

    return true;
}

//添加歌单，歌单可以重名，故不作判断
bool Connection::addMusicList(MusicList* ml)
{
    QString sql = add_musiclist_sql.arg(ml->musiclist_name, ml->description);
    bool success = query->exec(sql);
    if(success)
    {
        ml->id = query->lastInsertId().toInt();
        qDebug() << QObject::tr("添加歌单成功") << ml->musiclist_name;
        return true;
    }
    else
    {
        qDebug() << QObject::tr("添加歌单失败");
        qDebug()<< query->lastError().text();
        return false;
    }
}

//收藏歌曲到歌单，如果歌曲不存在数据库中，先添加歌曲
bool Connection::collectSong(Song* song, MusicList* ml)
{
    bool success;
    //如果歌曲不存在数据库中，先添加歌曲，根据url判断是否存在
    if(song->id == 0)
    {

        success = query->exec(get_song_sql.arg(song->Url));

        if(success)
        {
            query->next();
            if(!query->isValid())
            {
                QString sql = add_song_sql.arg(song->Name, song->Singer, song->Album, song->Url).arg(song->Time);
                success = query->exec(sql);
                if(success)
                {
                    song->id = query->lastInsertId().toInt();
                    qDebug() << QObject::tr("添加歌曲成功") << song->Url;
                }
                else
                {
                    qDebug() << QObject::tr("添加歌曲失败");
                    qDebug()<< query->lastError().text();
                    return false;
                }
            }
            else
            {
                qDebug() << QObject::tr("该歌曲已存在") << song->Url;
                song->id = query->value(0).toInt();
            }
        }
        else
        {
            qDebug() << QObject::tr("添加歌曲失败");
            qDebug()<< query->lastError().text();
            return false;
        }
    }
    else
        qDebug() << QObject::tr("该歌曲已存在");

    QString sql2 = get_musiclist_song_sql.arg(ml->id).arg(song->id);
    success = query->exec(sql2);

    if(success)
    {
        query->next();
        if(query->isValid())
        {
            qDebug() << QObject::tr("该歌单已收藏该歌曲");
            return true;
        }
    }
    else
    {
        qDebug() << QObject::tr("查询歌单是否收藏该歌曲失败");
        qDebug()<< query->lastError().text();
        return false;
    }

    sql2 = add_musiclist_song_sql.arg(ml->id).arg(song->id);
    success = query->exec(sql2);

    if(success)
    {
        qDebug() << QObject::tr("收藏歌曲成功");
        return true;
    }
    else
    {
        qDebug() << QObject::tr("收藏歌曲失败");
        qDebug()<< query->lastError().text();
        return false;
    }
}

//获取歌单歌曲
bool Connection::getSongsFromMusicList(MusicList* ml)
{

    bool success = query->exec(get_song_from_musiclist_sql.arg(ml->id));

    if(!success)
    {
        qDebug() << QObject::tr("查询歌单歌曲失败。" );
        qDebug()<< query->lastError().text();
        return false;
    }

    qDebug() << QObject::tr("歌曲数量：" ) << query->size();

    while(query->next())
    {
        qDebug() << query->value(0) << query->value(1) << query->value(2);
        Song* tmp = new Song(query->value(0).toInt(), query->value(1).toString(), query->value(2).toString(), query->value(3).toString()
                             , query->value(4).toString(), query->value(5).toInt());
        ml->addSong(tmp);
    }

    ml->isFlush = true;
    return true;
}

//获取歌单列表
MyList<MusicList*>* Connection::getMusicLists()
{
    MyList<MusicList*>* music_lists = new MyList<MusicList*>;

    bool success = query->exec(get_musiclist_sql);

    if(!success)
    {
        qDebug() << QObject::tr("查询歌单列表失败。" );
        qDebug()<< query->lastError().text();
        return music_lists;
    }

    qDebug() << QObject::tr("歌单数量：" ) << query->size();

    while(query->next())
    {
        qDebug() << query->value(0) << query->value(1) << query->value(2);
        MusicList* tmp = new MusicList((query->value(0)).toInt(), (query->value(1)).toString(), (query->value(2)).toString());
        music_lists->addNode(tmp);
    }

    return music_lists;
}

//删除歌单
bool Connection::deleteSongFromMusicList(MusicList* ml, Song* s)
{

    bool success = query->exec(delete_song_from_musiclist_sql.arg(ml->id).arg(s->id));

    if(!success)
    {
        qDebug() << QObject::tr("删除歌曲失败。" );
        qDebug()<< query->lastError().text();
        return false;
    }

    return true;
}

//删除歌单
bool Connection::deleteMusicList(MusicList* ml)
{
    bool success = query->exec(delete_all_song_from_musiclist_sql.arg(ml->id));

    if(!success)
    {
        qDebug() << QObject::tr("删除歌单歌曲失败。" );
        qDebug()<< query->lastError().text();
        return false;
    }


    success = query->exec(delete_musiclist_sql.arg(ml->id));

    if(!success)
    {
        qDebug() << QObject::tr("删除歌单失败。" );
        qDebug()<< query->lastError().text();
        return false;
    }

    return true;
}