#include "tooldatabasewidget.h"
#include "ui_tooldatabasewidget.h"

//##########################################################################################
tooldatabasewidget::tooldatabasewidget(QWidget *parent) :
    QDialog(parent),
    ui(new Ui::tooldatabasewidget)
{
    ui->setupUi(this);

    //#### create Database Connection
    bool existingData = QFile::exists("tooldatabase.dat");
    if (!createConnection())
       {

       }
    if (!existingData)
        createFakeData();

    createToolGroupPanel();
    createToolPanel();

    ui->viewToolGroup->setCurrentIndex(toolGroupModel->index(0, 0));

}
//##########################################################################################
tooldatabasewidget::~tooldatabasewidget()
{
    delete ui;
}
//##########################################################################################
bool tooldatabasewidget::createConnection()
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("tooldatabase.dat");
    if (!db.open())
    {
        db = QSqlDatabase();
        QSqlDatabase::removeDatabase(db.connectionName());
        QMessageBox::warning(0, QObject::tr("Database Error"),db.lastError().text());        
        return false;
    }
    return true;
}
//##########################################################################################
void tooldatabasewidget::updateViewTool()
{
    QModelIndex index = ui->viewToolGroup->currentIndex();
    if (index.isValid())
    {
        QSqlRecord record = toolGroupModel->record(index.row());
        int id = record.value("id").toInt();
        toolModel->setFilter(QString("departmentid = %1").arg(id));
       // employeeLabel->setText(tr("E&mployees in the %1 Department").arg(record.value("name").toString()));
    }
    else
    {
        toolModel->setFilter("departmentid = -1");
        //employeeLabel->setText(tr("E&mployees"));
    }
    toolModel->select();
    ui->viewTool->horizontalHeader()->setVisible(toolModel->rowCount() > 0);
}
//##########################################################################################
void tooldatabasewidget::addToolGroup()
{
    int row = toolGroupModel->rowCount();
    toolGroupModel->insertRow(row);
    QModelIndex index = toolGroupModel->index(row, ToolGroup_Name);
    ui->viewToolGroup->setCurrentIndex(index);
    ui->viewToolGroup->edit(index);
}
//##########################################################################################
void tooldatabasewidget::deleteToolGroup()
{
    QModelIndex index = ui->viewToolGroup->currentIndex();
    if (!index.isValid())
        return;

    QSqlDatabase::database().transaction();
    QSqlRecord record = toolGroupModel->record(index.row());
    int id = record.value(ToolGroup_Id).toInt();
    int numTools = 0;

    QSqlQuery query(QString("SELECT COUNT(*) FROM employee " "WHERE departmentid = %1").arg(id));
    if (query.next())
        numTools = query.value(0).toInt();
    if (numTools > 0)
    {
        int r = QMessageBox::warning(this, tr("Delete ToolGroup"),
                    tr("Delete %1 and all its Tools?")
                    .arg(record.value(ToolGroup_Name).toString()),
                    QMessageBox::Yes | QMessageBox::No);
        if (r == QMessageBox::No)
        {
            QSqlDatabase::database().rollback();
            return;
        }

        query.exec(QString("DELETE FROM employee ""WHERE departmentid = %1").arg(id));
    }

    toolGroupModel->removeRow(index.row());
    toolGroupModel->submitAll();
    QSqlDatabase::database().commit();

    updateViewTool();
    ui->viewToolGroup->setFocus();
}
//##########################################################################################
void tooldatabasewidget::createToolGroupPanel()
{
    toolGroupModel = new QSqlRelationalTableModel(this);
    toolGroupModel->setTable("department");
    toolGroupModel->setRelation(ToolGroup_LocationId,QSqlRelation("location", "id", "name"));
    toolGroupModel->setSort(ToolGroup_Name, Qt::AscendingOrder);
    toolGroupModel->setHeaderData(ToolGroup_Name, Qt::Horizontal,tr("Dept."));
    toolGroupModel->setHeaderData(ToolGroup_LocationId,Qt::Horizontal, tr("Location"));
    toolGroupModel->select();

    ui->viewToolGroup->setModel(toolGroupModel);
    ui->viewToolGroup->setItemDelegate(new QSqlRelationalDelegate(this));
    ui->viewToolGroup->setSelectionMode(QAbstractItemView::SingleSelection);
    ui->viewToolGroup->setSelectionBehavior(QAbstractItemView::SelectRows);
    ui->viewToolGroup->setColumnHidden(ToolGroup_Id, true);
    ui->viewToolGroup->resizeColumnsToContents();
    ui->viewToolGroup->horizontalHeader()->setStretchLastSection(true);

    connect(ui->viewToolGroup->selectionModel(),SIGNAL(currentRowChanged(const QModelIndex &,const QModelIndex &)),this, SLOT(updateViewTool()));
}
//##########################################################################################
void tooldatabasewidget::createToolPanel()
{
    toolModel = new QSqlRelationalTableModel(this);
    toolModel->setTable("employee");
    toolModel->setRelation(Tool_DepartmentId,QSqlRelation("department", "id", "name"));
    toolModel->setSort(Tool_Name, Qt::AscendingOrder);
    toolModel->setHeaderData(Tool_Name, Qt::Horizontal,tr("Name"));
    toolModel->setHeaderData(Tool_Extension, Qt::Horizontal,tr("Ext."));
    toolModel->setHeaderData(Tool_Email, Qt::Horizontal,tr("Email"));

    ui->viewTool->setModel(toolModel);
    ui->viewTool->setSelectionMode(QAbstractItemView::SingleSelection);
    ui->viewTool->setSelectionBehavior(QAbstractItemView::SelectRows);
    ui->viewTool->setEditTriggers(QAbstractItemView::NoEditTriggers);
    ui->viewTool->horizontalHeader()->setStretchLastSection(true);
    ui->viewTool->setColumnHidden(Tool_Id, true);
    ui->viewTool->setColumnHidden(Tool_DepartmentId, true);
    ui->viewTool->setColumnHidden(Tool_StartDate, true);
}

//##########################################################################################
void tooldatabasewidget::createFakeData()
{
    QStringList names;
    names << "Eabha Biddell" << "Prentice Hutchison"
          << "Rameesha Davidge" << "Digby Roson" << "Nettah Newarch"
          << "Lewisha Middleton" << "Ahmed Hanmer"
          << "Jordyn-Leigh Lamant" << "Lindsay Bigham"
          << "Kaylay Weir" << "Sofia Weir" << "Coel Spurlock"
          << "Youcef Culpan" << "Lucy-Jasmine Blanchard"
          << "Ally Hodgkin" << "Ara Collinge" << "Luka Dempster"
          << "Samanta Winster" << "Keri Palin" << "Ruiridh Bisset"
          << "Norman Epworth" << "Kezia Raw"
          << "Kaylan-Thomas Swynford" << "Kashaf Benning"
          << "Norma Yair" << "Edan Bassett" << "Akshat Mcglasson"
          << "Philippa Upton" << "Tylor Rockliff" << "Aqdas Buckman"
          << "Briana Dowell" << "Querida North" << "Chelsay Botts"
          << "Kishanth Calloway" << "Jan Covington"
          << "Teighan Monson" << "Claudia Mendel" << "Kerra Doe"
          << "Kara Depp" << "Harlie Soole" << "Viggo Streeter"
          << "Ava Cofel" << "Catherine Balderston"
          << "Brendan Gosnay" << "Zhaoyun Haygarth" << "Deri Pepler"
          << "Vicki Hopwood" << "Amitra Bindless" << "Cerhys Hayton"
          << "Gwendoline Westall";


    QSqlQuery query;

    query.exec("DROP TABLE department");
    query.exec("DROP TABLE employee");
    query.exec("DROP TABLE location");


    query.exec("CREATE TABLE location ("
               "id INTEGER PRIMARY KEY AUTOINCREMENT, "
               "name VARCHAR(40) NOT NULL)");

    query.exec("CREATE TABLE department ("
               "id INTEGER PRIMARY KEY AUTOINCREMENT, "
               "name VARCHAR(40) NOT NULL, "
               "locationid INTEGER NOT NULL, "
               "FOREIGN KEY (locationid) REFERENCES location)");

    query.exec("CREATE TABLE employee ("
               "id INTEGER PRIMARY KEY AUTOINCREMENT, "
               "name VARCHAR(40) NOT NULL, "
               "departmentid INTEGER NOT NULL, "
               "extension INTEGER NOT NULL, "
               "email VARCHAR(40) NOT NULL, "
               "startdate DATE NOT NULL, "
               "FOREIGN KEY (departmentid) REFERENCES department)");

    query.exec("INSERT INTO location (name) VALUES (""'Floor 18, 1129 Evanston Heights, New York, NY')");
    query.exec("INSERT INTO location (name) VALUES (""'The Shed, Elmtree Drive, Boston, MA')");
    query.exec("INSERT INTO location (name) VALUES (""'14 Valentine Buildings, Amor Street, Cambridge, MA')");
    query.exec("INSERT INTO location (name) VALUES (""'Bunker Building, Silo Avenue, Los Angeles, CA')");
    query.exec("INSERT INTO department (name, locationid) VALUES (""'Sales', 1)");
    query.exec("INSERT INTO department (name, locationid) VALUES (""'Marketing', 2)");
    query.exec("INSERT INTO department (name, locationid) VALUES (""'Processing', 1)");
    query.exec("INSERT INTO department (name, locationid) VALUES (""'Support', 4)");
    query.exec("INSERT INTO department (name, locationid) VALUES (""'Research', 3)");


    int count = 0;
    query.prepare("INSERT INTO employee (name, departmentid, ""extension, email, startdate) ""VALUES (:name, :departmentid, :extension, "":email, :startdate)");
    foreach (QString name, names) {
        query.bindValue(":name", name);
        query.bindValue(":departmentid", 1 + (std::rand() % 5));
        query.bindValue(":extension", 400 + (std::rand() % 100));
        query.bindValue(":email", name.toLower().replace(" ", ".") +"@company.com");
        query.bindValue(":startdate",QDate::currentDate().addDays(-(std::rand() % 3600)));
        query.exec();
        ++count;
    }
}