package main

import (
    "database/sql"
    "fmt"
    "time"
    "os"
    _ "github.com/mattn/go-sqlite3"
)

type project struct{
    id int
    name string
    first time.Time
    last time.Time
    finish int
}

type task struct{
    id int
    projectid int
    start time.Time
    stop time.Time
    taskname string
    checkout bool
}

var db *sql.DB
var err error
var currproject project
var opentask task

func initDB(filename string) {
    if _, err := os.Stat(filename); os.IsNotExist(err) {
        db, err = sql.Open("sqlite3", filename)
        checkErr(err)
        fmt.Println("Creating new DB")
        sqlstmt := `
        CREATE TABLE projects(
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name VARCHAR(240) NOT NULL,
            first TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            last TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            finished INTEGER NOT NULL);
        CREATE TABLE timetable(
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            project INTEGER NOT NULL,
            start TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            stop TIMESTAMP DEFAULT '1791-09-30 19:07',
            task VARCHAR(240) NOT NULL,
            checkout INTEGER NOT NULL);
        `
        _, err = db.Exec(sqlstmt)
        checkErr(err)
    }else{
        db, err = sql.Open("sqlite3", filename)
        checkErr(err)
        fmt.Println("Opening DB")
    }
}

func newTask(proj int ,task string) {
    stmt, err := db.Prepare("INSERT INTO timetable(project, task, checkout) values(?, ?, ?)")
    checkErr(err)
    if (proj == 0) {
        _, err = stmt.Exec(currproject.id,task,0)
    }else{
        _, err = stmt.Exec(proj,task,0)
    }
    checkErr(err)
    fmt.Println("...New Task inserted into",currproject.name,": ",task)
    getOpenTask()
    updateProject(currproject.id)
}

func closeTask() {
    fmt.Println("...Closing Task",opentask.id)
    stmt, err := db.Prepare("UPDATE timetable SET stop = datetime('now') WHERE id = ?")
    checkErr(err)
    _, err = stmt.Exec(opentask.id)
    checkErr(err)
    opentask.id=0
    updateProject(opentask.projectid)
}

func updateProject(id int) {
    stmt, err := db.Prepare("UPDATE projects SET last = datetime('now') WHERE id = ?")
    checkErr(err)
    _, err = stmt.Exec(id)
    checkErr(err)
}

func getOpenTask() {
    rows,err := db.Query("SELECT id, project, start, task, checkout FROM timetable WHERE stop = '1791-09-30 19:07'")
    checkErr(err)
    var id,project,checkout int
    var task string
    var start time.Time

    defer rows.Close()
    for rows.Next() {
        err = rows.Scan(&id, &project, &start , &task, &checkout)
        checkErr(err)
        //fmt.Println(id, "Open Task:", task, project, start)
    }
    //rows.Close() //good habit to close
    opentask.id = id
    opentask.projectid = project
    opentask.start = start
    opentask.taskname = task
    opentask.checkout = checkout != 0
}

func showOpenTask() {
    if (opentask.id==0) {
        fmt.Println("___No Open Tasks____________")
    } else {
        fmt.Println("___Open Task________________")
        fmt.Println(opentask.id,":", opentask.taskname, opentask.projectid, opentask.start.Local().Format("Mon Jan _2 15:04"))
    }
}

func showLastProject() {
    if (currproject.id==0) {
        fmt.Println("Empty DB")
    } else {
        fmt.Println("___Last Project_____________")
        fmt.Println(currproject.id,":", currproject.name, currproject.last.Local().Format("Mon Jan _2 15:04 2006"))
    }
}


func newProject(nam string) {
    stmt, err := db.Prepare("INSERT INTO projects(name, finished) values(?, ?)")

    checkErr(err)
    _, err = stmt.Exec(nam,0)
    checkErr(err)
   fmt.Println("   Project Created:",nam)
   getLastProject()
}

func getClosedTasks(num int) {
    //var rows sql.Rows
    //var err error
    fmt.Println("___Past Tasks________________")
    rows,err := db.Query("SELECT * FROM timetable WHERE stop != '1791-09-30 19:07' ORDER BY datetime(start)",currproject.id)
    checkErr(err)
    if num > 0 {
        rows,err = db.Query("SELECT * FROM timetable WHERE project = $1 AND stop != '1791-09-30 19:07'ORDER BY datetime(start) DESC LIMIT $2",currproject.id,num)
        checkErr(err)
    } else {
        rows,err = db.Query("SELECT * FROM timetable WHERE project = $1 AND stop != '1791-09-30 19:07'ORDER BY datetime(start)",currproject.id)
        checkErr(err)
    }
    var id, proj,check int
    var sta,sto time.Time
    var tas string
    checkErr(err)

    for rows.Next() {
        err = rows.Scan(&id, &proj, &sta, &sto, &tas, &check)
        checkErr(err)
        fmt.Println(id,tas,sta.Local().Format("Mon Jan _2 15:04"),sto.Local().Format("15:04"))
    }
    rows.Close()
}

func getLastProject() {
    rows,err := db.Query("SELECT * FROM projects")
    checkErr(err)

    var uid,nuid int
    var prname,nprname string
    var first,nfirst time.Time
    var last,nlast time.Time
    var finish,nfinish int

    for rows.Next() {
        err = rows.Scan(&uid, &prname, &first, &last, &finish)
        checkErr(err)
        if (last.After(nlast)){
            nuid=uid
            nprname=prname
            nfirst=first
            nlast=last
            nfinish=finish
        }
    }
    rows.Close() //good habit to close
   // fmt.Println(nuid,"Last Project",nprname)
   // fmt.Println(nlast)
   // fmt.Println("----------------------------------")

    currproject.id = nuid
    currproject.name = nprname
    currproject.first = nfirst
    currproject.last = nlast
    currproject.finish = nfinish
   // getLastTasks(0)
}

func setProject (nid int) {
    rows,err := db.Query("SELECT * FROM projects WHERE id = $1",nid)
    checkErr(err)

    var uid int
    var prname string
    var first time.Time
    var last time.Time
    var finish int

    for rows.Next() {
        err = rows.Scan(&uid, &prname, &first, &last, &finish)
        checkErr(err)
   }
    rows.Close() //good habit to close
    currproject.id = uid
    currproject.name = prname
    currproject.first = first
    currproject.last = last
    currproject.finish = finish
    updateProject(uid)
}

func allProjects() {
    rows,err := db.Query("SELECT * FROM projects")
    checkErr(err)

    var uid int
    var prname string
    var first time.Time
    var last time.Time
    var finish int

    fmt.Println("___All Projects________________")
    for rows.Next() {
        err = rows.Scan(&uid, &prname, &first, &last, &finish)
        checkErr(err)
        fmt.Println(" ",uid,prname,first.Local().Format("2006-09-12 19:13"),last.Local().Format("2006-09-12 19:13"),finish)
    }
    rows.Close() //good habit to close
}

func checkErr(err error) {
    if err != nil {
        panic(err)
    }
}