package fic

import (
	"database/sql"
	"time"
)

// QAQuery represents a QA query.
type QAQuery struct {
	Id         int64      `json:"id"`
	IdExercice int64      `json:"id_exercice"`
	IdTeam     *int64     `json:"id_team"`
	User       string     `json:"user"`
	Creation   time.Time  `json:"creation"`
	State      string     `json:"state"`
	Subject    string     `json:"subject"`
	Solved     *time.Time `json:"solved,omitempty"`
	Closed     *time.Time `json:"closed,omitempty"`
	Exported   *int64     `json:"exported,omitempty"`
}

// GetQAQuery retrieves the query with the given identifier.
func GetQAQuery(id int64) (q *QAQuery, err error) {
	q = &QAQuery{}
	err = DBQueryRow("SELECT id_qa, id_exercice, id_team, authuser, creation, state, subject, solved, closed, exported FROM exercices_qa WHERE id_qa = ?", id).Scan(&q.Id, &q.IdExercice, &q.IdTeam, &q.User, &q.Creation, &q.State, &q.Subject, &q.Solved, &q.Closed, &q.Exported)
	return
}

// GetQAQueries returns a list of all QAQuery registered in the database.
func GetQAQueries() (res []*QAQuery, err error) {
	var rows *sql.Rows
	if rows, err = DBQuery("SELECT id_qa, id_exercice, id_team, authuser, creation, state, subject, solved, closed, exported FROM exercices_qa"); err != nil {
		return
	}
	defer rows.Close()

	for rows.Next() {
		q := &QAQuery{}
		if err = rows.Scan(&q.Id, &q.IdExercice, &q.IdTeam, &q.User, &q.Creation, &q.State, &q.Subject, &q.Solved, &q.Closed, &q.Exported); err != nil {
			return
		}
		res = append(res, q)
	}
	err = rows.Err()

	return
}

// GetQAQueries returns a list of all QAQuery registered for the Exercice.
func (e *Exercice) GetQAQueries() (res []*QAQuery, err error) {
	var rows *sql.Rows
	if rows, err = DBQuery("SELECT id_qa, id_exercice, id_team, authuser, creation, state, subject, solved, closed, exported FROM exercices_qa WHERE id_exercice = ?", e.Id); err != nil {
		return nil, err
	}
	defer rows.Close()

	for rows.Next() {
		q := &QAQuery{}
		if err = rows.Scan(&q.Id, &q.IdExercice, &q.IdTeam, &q.User, &q.Creation, &q.State, &q.Subject, &q.Solved, &q.Closed, &q.Exported); err != nil {
			return
		}
		res = append(res, q)
	}
	err = rows.Err()

	return
}

// GetQAQueries returns a list of all QAQuery registered for the Exercice.
func (t *Team) GetQAQueries() (res []*QAQuery, err error) {
	var rows *sql.Rows
	if rows, err = DBQuery("SELECT id_qa, id_exercice, id_team, authuser, creation, state, subject, solved, closed, exported FROM exercices_qa WHERE id_team = ?", t.Id); err != nil {
		return nil, err
	}
	defer rows.Close()

	for rows.Next() {
		q := &QAQuery{}
		if err = rows.Scan(&q.Id, &q.IdExercice, &q.IdTeam, &q.User, &q.Creation, &q.State, &q.Subject, &q.Solved, &q.Closed, &q.Exported); err != nil {
			return
		}
		res = append(res, q)
	}
	err = rows.Err()

	return
}

// GetQAQuery retrieves the query with the given identifier.
func (e *Exercice) GetQAQuery(id int64) (q *QAQuery, err error) {
	q = &QAQuery{}
	err = DBQueryRow("SELECT id_qa, id_exercice, id_team, authuser, creation, state, subject, solved, closed, exported FROM exercices_qa WHERE id_qa = ? AND id_exercice = ?", id, e.Id).Scan(&q.Id, &q.IdExercice, &q.IdTeam, &q.User, &q.Creation, &q.State, &q.Subject, &q.Solved, &q.Closed, &q.Exported)
	return
}

// NewQAQuery creates and fills a new struct QAQuery and registers it into the database.
func (e *Exercice) NewQAQuery(subject string, teamId *int64, user string, state string, solved *time.Time) (*QAQuery, error) {
	if res, err := DBExec("INSERT INTO exercices_qa (id_exercice, id_team, authuser, creation, state, subject, solved) VALUES (?, ?, ?, ?, ?, ?, ?)", e.Id, teamId, user, time.Now(), state, subject, solved); err != nil {
		return nil, err
	} else if qid, err := res.LastInsertId(); err != nil {
		return nil, err
	} else {
		return &QAQuery{qid, e.Id, teamId, user, time.Now(), state, subject, solved, nil, nil}, nil
	}
}

// Update applies modifications back to the database.
func (q *QAQuery) Update() (int64, error) {
	if res, err := DBExec("UPDATE exercices_qa SET subject = ?, id_team = ?, authuser = ?, id_exercice = ?, creation = ?, state = ?, solved = ?, closed = ?, exported = ? WHERE id_qa = ?", q.Subject, q.IdTeam, q.User, q.IdExercice, q.Creation, q.State, q.Solved, q.Closed, q.Exported, q.Id); err != nil {
		return 0, err
	} else if nb, err := res.RowsAffected(); err != nil {
		return 0, err
	} else {
		return nb, err
	}
}

// Delete the query from the database.
func (q *QAQuery) Delete() (int64, error) {
	if _, err := DBExec("DELETE FROM qa_comments WHERE id_qa = ?", q.Id); err != nil {
		return 0, err
	} else if res, err := DBExec("DELETE FROM exercices_qa WHERE id_qa = ?", q.Id); err != nil {
		return 0, err
	} else if nb, err := res.RowsAffected(); err != nil {
		return 0, err
	} else {
		return nb, err
	}
}

// ClearQAQueries removes all queries from database.
func ClearQAQueries() (int64, error) {
	if res, err := DBExec("DELETE FROM exercices_qa"); err != nil {
		return 0, err
	} else if nb, err := res.RowsAffected(); err != nil {
		return 0, err
	} else {
		return nb, err
	}
}

// QAComment represents some text describing a QAQuery.
type QAComment struct {
	Id      int64     `json:"id"`
	IdTeam  *int64    `json:"id_team"`
	User    string    `json:"user"`
	Date    time.Time `json:"date"`
	Content string    `json:"content"`
}

// GetComments returns a list of all descriptions stored in the database for the QAQuery.
func (q *QAQuery) GetComments() (res []*QAComment, err error) {
	var rows *sql.Rows
	if rows, err = DBQuery("SELECT id_comment, id_team, authuser, date, content FROM qa_comments WHERE id_qa = ?", q.Id); err != nil {
		return nil, err
	}
	defer rows.Close()

	for rows.Next() {
		c := &QAComment{}
		if err = rows.Scan(&c.Id, &c.IdTeam, &c.User, &c.Date, &c.Content); err != nil {
			return
		}
		res = append(res, c)
	}
	err = rows.Err()

	return
}

// GetComment returns the comment stored in the database for the QAQuery.
func (q *QAQuery) GetComment(id int64) (c *QAComment, err error) {
	c = &QAComment{}
	err = DBQueryRow("SELECT id_comment, id_team, authuser, date, content FROM qa_comments WHERE id_comment = ? AND id_qa = ?", id, q.Id).Scan(&c.Id, &c.IdTeam, &c.User, &c.Date, &c.Content)
	return
}

// AddComment append in the database a new description; then returns the corresponding structure.
func (q *QAQuery) AddComment(content string, teamId *int64, user string) (*QAComment, error) {
	if res, err := DBExec("INSERT INTO qa_comments (id_qa, id_team, authuser, date, content) VALUES (?, ?, ?, ?, ?)", q.Id, teamId, user, time.Now(), content); err != nil {
		return nil, err
	} else if cid, err := res.LastInsertId(); err != nil {
		return nil, err
	} else {
		return &QAComment{cid, teamId, user, time.Now(), content}, nil
	}
}

// Update applies modifications back to the database
func (c *QAComment) Update() (int64, error) {
	if res, err := DBExec("UPDATE qa_comments SET id_team = ?, authuser = ?, date = ?, content = ? WHERE id_comment = ?", c.IdTeam, c.User, c.Date, c.Content, c.Id); err != nil {
		return 0, err
	} else if nb, err := res.RowsAffected(); err != nil {
		return 0, err
	} else {
		return nb, err
	}
}

// Delete the comment in the database.
func (c *QAComment) Delete() (int64, error) {
	if res, err := DBExec("DELETE FROM qa_comments WHERE id_comment = ?", c.Id); err != nil {
		return 0, err
	} else if nb, err := res.RowsAffected(); err != nil {
		return 0, err
	} else {
		return nb, err
	}
}

type QATodo struct {
	Id         int64 `json:"id"`
	IdTeam     int64 `json:"id_team,omitempty"`
	IdExercice int64 `json:"id_exercice"`
}

func (t *Team) GetQATodo() (res []*QATodo, err error) {
	var rows *sql.Rows
	if rows, err = DBQuery("SELECT id_todo, id_exercice FROM teams_qa_todo WHERE id_team = ?", t.Id); err != nil {
		return nil, err
	}
	defer rows.Close()

	for rows.Next() {
		t := &QATodo{}
		if err = rows.Scan(&t.Id, &t.IdExercice); err != nil {
			return
		}
		res = append(res, t)
	}
	err = rows.Err()

	return
}

func (t *Team) NewQATodo(idExercice int64) (*QATodo, error) {
	if res, err := DBExec("INSERT INTO teams_qa_todo (id_team, id_exercice) VALUES (?, ?)", t.Id, idExercice); err != nil {
		return nil, err
	} else if tid, err := res.LastInsertId(); err != nil {
		return nil, err
	} else {
		return &QATodo{tid, t.Id, idExercice}, nil
	}
}

// Delete the comment in the database.
func (t *QATodo) Delete() (int64, error) {
	if res, err := DBExec("DELETE FROM teams_qa_todo WHERE id_todo = ?", t.Id); err != nil {
		return 0, err
	} else if nb, err := res.RowsAffected(); err != nil {
		return 0, err
	} else {
		return nb, err
	}
}

// QAView

func (t *Team) GetQAView() (res []*QATodo, err error) {
	var rows *sql.Rows
	if rows, err = DBQuery("SELECT id_view, id_exercice FROM teams_qa_view WHERE id_team = ?", t.Id); err != nil {
		return nil, err
	}
	defer rows.Close()

	for rows.Next() {
		t := &QATodo{}
		if err = rows.Scan(&t.Id, &t.IdExercice); err != nil {
			return
		}
		res = append(res, t)
	}
	err = rows.Err()

	return
}

func (t *Team) NewQAView(idExercice int64) (*QATodo, error) {
	if res, err := DBExec("INSERT INTO teams_qa_view (id_team, id_exercice) VALUES (?, ?)", t.Id, idExercice); err != nil {
		return nil, err
	} else if tid, err := res.LastInsertId(); err != nil {
		return nil, err
	} else {
		return &QATodo{tid, t.Id, idExercice}, nil
	}
}