#include <QtCore/QCoreApplication>

#include <QtSql>

#include "database.h"

// This program is simply responsible for creating a SQLite database for the Seafood Selector

void createTable(QString sqlStmt)

{

    QSqlQuery query;

    bool rc = true;

    rc = query.exec(sqlStmt);

}

int getLastInsertRowId()

{

    int rc = -1;

    QSqlQuery query;

    query.exec("select last_insert_rowid()");

    if (!query.exec()) {

        QString errCode =  "last rowid query Failed: " + query.lastError().text();

        qWarning(errCode.toStdString().c_str());

    } else {

        query.next();

        rc = query.value(0).toInt();

    }

    return rc;

}

void insertFish( QString name, int cat, int calories,

                float fat, float protein, float omega3,

                int cholesterol, int sodium)

{

    QSqlQuery query;

    query.prepare("INSERT INTO fish ( name, category, calories, fat, protein,omega3,cholesterol, sodium) "

                  "VALUES (:name,:category,:calories,:fat,:protein,:omega3,:cholesterol, :sodium) ");

    query.bindValue(":name", name);

    query.bindValue(":category",cat);

    query.bindValue(":calories",calories);

    query.bindValue(":fat",fat);

    query.bindValue(":protein",protein);

    query.bindValue(":omega3",omega3);

    query.bindValue(":cholesterol",cholesterol);

    query.bindValue(":sodium", sodium);

    if (!query.exec())

    {

        qDebug() << query.lastError();

        qFatal("Failed to add fish.");

    }

}

void insertEco(int lastID, QString details)

{

    QSqlQuery query;

    query.prepare("INSERT INTO ecoDetails (fid, details) VALUES (:fid,:details) ");

    query.bindValue(":fid", lastID);

    query.bindValue(":details",details);

    if (!query.exec())

    {

        qDebug() << query.lastError();

        qFatal("Failed to add eco detail.");

    }

}

int main(int argc, char *argv[])

{

    QCoreApplication a(argc, argv);

    QSqlDatabase db;

    // Find QSLite driver

    db = QSqlDatabase::addDatabase("QSQLITE");

    db.setDatabaseName("C:\\workspace\\QtExamples\\Seafood\\populateDB\\seafood.db");

    // Open databasee

    if(!db.open())

    {

        qDebug() << "DB: failed to open" << endl;

        exit (1);

    }

    qDebug() << "DB: database opened " << endl;

    createTable("create table fish "

                "(fid integer primary key, "

                "name varchar(32),"

                "category int,"

                "calories int,"

                "fat float,"

                "protein float,"

                "omega3 float,"

                "cholesterol int,"

                "sodium int)");

    createTable ("create table ecoDetails (eid integer primary key, "

                 "fid integer, "

                 "details varchar(128))");

    int lastID = -1;

    insertFish("Crab, Dungeness",EBEST,86,0.96,17.4,0.3,59,295);

    lastID = getLastInsertRowId();

    insertEco(lastID,"Only adult males are caught in this fishery, which uses pots and traps made of biodegradable webs to avoid \'ghost fishing\' from lost gear.");

    insertEco(lastID, "Pots and traps are also equipped with built-in exits that allow escape of undersize crabs.");

    insertEco(lastID, "This gear has negligible bycatch and causes little habitat damage.");

    insertFish("Trout, rainbow (farmed)",EBEST,131,5.4,20.8,0.986,59,35);

    lastID = getLastInsertRowId();

    insertEco(lastID, "Although trout consume considerable amounts of wild fish in their feed, recent improvements have made them less reliant on this finite natural resource.");

    insertEco(lastID, "Industry practices and stringent government regulations also prevent pollution and fish escapes.");

    insertFish("Cod, Pacific (trawl)",EOK,83,0.63,17.9,0.0,37,71);

    lastID = getLastInsertRowId();

    insertEco(lastID, "U.S. Pacific cod populations are healthy, and cod caught by bottom longline, traps or hook-and-line do the least environmental harm.");

    insertEco(lastID, "U.S. Pacific cod are also caught by bottom trawl, which can damage seafloor habitats.");

    insertFish("Tuna, canned light",EOK,103,1.01,22.0,0.256,47,37);

    lastID = getLastInsertRowId();

    insertEco(lastID, "Canned light tuna consists primarily of skipjack. These small tunas are resilient to fishing pressure because of their short life spans and rapid growth and reproduction rates.");

    insertEco(lastID, "Most skipjack are caught by pole-and-line (a method of fishing that does relatively little ecological harm) or purse seine fleets (which can result in considerable bycatch).");

    insertEco(lastID, "This highly migratory species is managed by international organizations.");

    insertFish( "Orange Roughy",EWORST,69,0.7,14.7,0.02,20,63);

    lastID = getLastInsertRowId();

    insertEco(lastID, "Orange roughy mature at a very slow rate and live extremely long lives. Intensive exploitation in past decades has greatly decreased populations.");

    insertEco(lastID, "Management in New Zealand and Australia is good, but unregulated landings by other countries continue.");

    insertFish("Salmon, farmed or Atlantic",EWORST,142,6.33,19.8,1.73,55,44);

    lastID = getLastInsertRowId();

    insertEco(lastID, "Atlantic salmon are usually farmed in large-scale, densely stocked netpens that pollute surrounding waters with waste and chemicals.");

    insertEco(lastID, "Fish can escape and compete for resources, breed with or spread parasites to wild fish.");

    return 0;

}