// db_actions defines actions on the database
// Copyright (C) 2019 Emile Hansmaennel
//
// This program is free software: you can redistribute it and/or modify
// it under the terms of the GNU General Public License as published by
// the Free Software Foundation, either version 3 of the License, or
// (at your option) any later version.
//
// This program is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU General Public License for more details.
//
// You should have received a copy of the GNU General Public License
// along with this program. If not, see <https://www.gnu.org/licenses/>.

package main

import (
	"database/sql"
	"encoding/csv"
	"fmt"
	"git.darknebu.la/GalaxySimulator/structs"
	_ "github.com/lib/pq"
	"io"
	"io/ioutil"
	"log"
	"strconv"
	"strings"
	"time"
)

const (
	DB_USER    = "postgres"
	DB_NAME    = "postgres"
	DB_SSLMODE = "disable"
)

//connectToDB returns a pointer to an sql database writing to the database
func connectToDB() *sql.DB {
	connStr := fmt.Sprintf("user=%s dbname=%s sslmode=%s", DB_USER, DB_NAME, DB_SSLMODE)
	db := dbConnect(connStr)
	return db
}

// dbConnect connects to a PostgreSQL database
func dbConnect(connStr string) *sql.DB {
	// connect to the database
	db, err := sql.Open("postgres", connStr)
	if err != nil {
		log.Fatalf("[ E ] connection: %v", err)
	}

	return db
}

//func newTree(db *sql.DB, width float64) {
func newTree(width float64) {
	// get the current max root id
	query := fmt.Sprintf("SELECT COALESCE(max(root_id), 0) FROM nodes")
	var currentMaxRootID int64
	err := db.QueryRow(query).Scan(&currentMaxRootID)
	if err != nil {
		log.Fatalf("[ E ] max root id query: %v\n\t\t\t query: %s\n", err, query)
	}

	// build the query creating a new node
	query = fmt.Sprintf("INSERT INTO nodes (box_width, root_id, box_center, depth, isleaf) VALUES (%f, %d, '{0, 0}', 0, TRUE)", width, currentMaxRootID+1)

	// execute the query
	_, err = db.Query(query)
	if err != nil {
		log.Fatalf("[ E ] insert new node query: %v\n\t\t\t query: %s\n", err, query)
	}
}

func insertStar(star structs.Star2D, index int64) {
	start := time.Now()
	// insert the star into the stars table
	starID := insertIntoStars(star)

	// get the root node id
	query := fmt.Sprintf("SELECT node_id FROM nodes WHERE root_id=%d", index)
	var id int64
	err := db.QueryRow(query).Scan(&id)
	if err != nil {
		log.Fatalf("[ E ] Get root node id query: %v\n\t\t\t query: %s\n", err, query)
	}

	// insert the star into the tree (using it's ID) starting at the root
	insertIntoTree(starID, id)
	elapsedTime := time.Since(start)
	log.Printf("\t\t\t\t\t %s", elapsedTime)
}

// insertIntoStars inserts the given star into the stars table
func insertIntoStars(star structs.Star2D) int64 {
	// unpack the star
	x := star.C.X
	y := star.C.Y
	vx := star.V.X
	vy := star.V.Y
	m := star.M

	// build the request query
	query := fmt.Sprintf("INSERT INTO stars (x, y, vx, vy, m) VALUES (%f, %f, %f, %f, %f) RETURNING star_id", x, y, vx, vy, m)

	// execute the query
	var starID int64
	err := db.QueryRow(query).Scan(&starID)
	if err != nil {
		log.Fatalf("[ E ] insert query: %v\n\t\t\t query: %s\n", err, query)
	}

	return starID
}

func insertIntoTree(starID int64, nodeID int64) {
	//starRaw := getStar(starID)
	//nodeCenter := getBoxCenter(nodeID)
	//nodeWidth := getBoxWidth(nodeID)
	//log.Printf("[   ] \t Inserting star %v into the node (c: %v, w: %v)", starRaw, nodeCenter, nodeWidth)

	// There exist four cases:
	//                    | Contains a Star | Does not Contain a Star |
	// ------------------ + --------------- + ----------------------- +
	// Node is a Leaf     | Impossible      | insert into node        |
	//                    |                 | subdivide               |
	// ------------------ + --------------- + ----------------------- +
	// Node is not a Leaf | insert preexist | insert into the subtree |
	//                    | insert new      |                         |
	// ------------------ + --------------- + ----------------------- +

	// get the node with the given nodeID
	// find out if the node contains a star or not
	containsStar := containsStar(nodeID)

	// find out if the node is a leaf
	isLeaf := isLeaf(nodeID)

	// if the node is a leaf and contains a star
	// subdivide the tree
	// insert the preexisting star into the correct subtree
	// insert the new star into the subtree
	if isLeaf == true && containsStar == true {
		//log.Printf("Case 1, \t %v \t %v", nodeWidth, nodeCenter)
		subdivide(nodeID)
		//tree := printTree(nodeID)

		// Stage 1: Inserting the blocking star
		blockingStarID := getBlockingStarID(nodeID)                       // get the id of the star blocking the node
		blockingStar := getStar(blockingStarID)                           // get the actual star
		blockingStarQuadrant := quadrant(blockingStar, nodeID)            // find out in which quadrant it belongs
		quadrantNodeID := getQuadrantNodeID(nodeID, blockingStarQuadrant) // get the nodeID of that quadrant
		insertIntoTree(blockingStarID, quadrantNodeID)                    // insert the star into that node
		removeStarFromNode(nodeID)                                        // remove the blocking star from the node it was blocking

		// Stage 1: Inserting the actual star
		star := getStar(starID)                                  // get the actual star
		starQuadrant := quadrant(star, nodeID)                   // find out in which quadrant it belongs
		quadrantNodeID = getQuadrantNodeID(nodeID, starQuadrant) // get the nodeID of that quadrant
		insertIntoTree(starID, nodeID)
	}

	// if the node is a leaf and does not contain a star
	// insert the star into the node and subdivide it
	if isLeaf == true && containsStar == false {
		//log.Printf("Case 2, \t %v \t %v", nodeWidth, nodeCenter)
		directInsert(starID, nodeID)
	}

	// if the node is not a leaf and contains a star
	// insert the preexisting star into the correct subtree
	// insert the new star into the subtree
	if isLeaf == false && containsStar == true {
		//log.Printf("Case 3, \t %v \t %v", nodeWidth, nodeCenter)
		// Stage 1: Inserting the blocking star
		blockingStarID := getBlockingStarID(nodeID)                       // get the id of the star blocking the node
		blockingStar := getStar(blockingStarID)                           // get the actual star
		blockingStarQuadrant := quadrant(blockingStar, nodeID)            // find out in which quadrant it belongs
		quadrantNodeID := getQuadrantNodeID(nodeID, blockingStarQuadrant) // get the nodeID of that quadrant
		insertIntoTree(blockingStarID, quadrantNodeID)                    // insert the star into that node
		removeStarFromNode(nodeID)                                        // remove the blocking star from the node it was blocking

		// Stage 1: Inserting the actual star
		star := getStar(blockingStarID)                          // get the actual star
		starQuadrant := quadrant(star, nodeID)                   // find out in which quadrant it belongs
		quadrantNodeID = getQuadrantNodeID(nodeID, starQuadrant) // get the nodeID of that quadrant
		insertIntoTree(starID, nodeID)
	}

	// if the node is not a leaf and does not contain a star
	// insert the new star into the according subtree
	if isLeaf == false && containsStar == false {
		//log.Printf("Case 4, \t %v \t %v", nodeWidth, nodeCenter)
		star := getStar(starID)                                   // get the actual star
		starQuadrant := quadrant(star, nodeID)                    // find out in which quadrant it belongs
		quadrantNodeID := getQuadrantNodeID(nodeID, starQuadrant) // get the if of that quadrant
		insertIntoTree(starID, quadrantNodeID)                    // insert the star into that quadrant
	}
}

// containsStar returns true if the node with the given id contains a star
// and returns false if not.
func containsStar(id int64) bool {
	var starID int64

	query := fmt.Sprintf("SELECT star_id FROM nodes WHERE node_id=%d", id)
	err := db.QueryRow(query).Scan(&starID)
	if err != nil {
		log.Fatalf("[ E ] containsStar query: %v\n\t\t\t query: %s\n", err, query)
	}

	if starID != 0 {
		return true
	}

	return false
}

// isLeaf returns true if the node with the given id is a leaf
func isLeaf(nodeID int64) bool {
	var isLeaf bool

	query := fmt.Sprintf("SELECT COALESCE(isleaf, FALSE) FROM nodes WHERE node_id=%d", nodeID)
	err := db.QueryRow(query).Scan(&isLeaf)
	if err != nil {
		log.Fatalf("[ E ] isLeaf query: %v\n\t\t\t query: %s\n", err, query)
	}

	if isLeaf == true {
		return true
	}

	return false
}

// directInsert inserts the star with the given ID into the given node inside of the given database
func directInsert(starID int64, nodeID int64) {
	// build the query
	query := fmt.Sprintf("UPDATE nodes SET star_id=%d WHERE node_id=%d", starID, nodeID)

	// Execute the query
	rows, err := db.Query(query)
	defer rows.Close()
	if err != nil {
		log.Fatalf("[ E ] directInsert query: %v\n\t\t\t query: %s\n", err, query)
	}
}

func subdivide(nodeID int64) {
	boxWidth := getBoxWidth(nodeID)
	boxCenter := getBoxCenter(nodeID)
	originalDepth := getNodeDepth(nodeID)

	// calculate the new positions
	newPosX := boxCenter[0] + (boxWidth / 2)
	newPosY := boxCenter[1] + (boxWidth / 2)
	newNegX := boxCenter[0] - (boxWidth / 2)
	newNegY := boxCenter[1] - (boxWidth / 2)
	newWidth := boxWidth / 2

	// create new news with those positions
	newNodeIDA := newNode(newPosX, newPosY, newWidth, originalDepth+1)
	newNodeIDB := newNode(newPosX, newNegY, newWidth, originalDepth+1)
	newNodeIDC := newNode(newNegX, newPosY, newWidth, originalDepth+1)
	newNodeIDD := newNode(newNegX, newNegY, newWidth, originalDepth+1)

	// Update the subtrees of the parent node

	// build the query
	query := fmt.Sprintf("UPDATE nodes SET subnode='{%d, %d, %d, %d}', isleaf=FALSE WHERE node_id=%d", newNodeIDA, newNodeIDB, newNodeIDC, newNodeIDD, nodeID)

	// Execute the query
	rows, err := db.Query(query)
	defer rows.Close()
	if err != nil {
		log.Fatalf("[ E ] subdivide query: %v\n\t\t\t query: %s\n", err, query)
	}
}

// getBoxWidth gets the width of the box from the node width the given id
func getBoxWidth(nodeID int64) float64 {
	var boxWidth float64

	query := fmt.Sprintf("SELECT box_width FROM nodes WHERE node_id=%d", nodeID)
	err := db.QueryRow(query).Scan(&boxWidth)
	if err != nil {
		log.Fatalf("[ E ] getBoxWidth query: %v\n\t\t\t query: %s\n", err, query)
	}

	return boxWidth
}

// getBoxWidth gets the center of the box from the node width the given id
func getBoxCenter(nodeID int64) []float64 {
	var boxCenterX, boxCenterY []uint8

	query := fmt.Sprintf("SELECT box_center[1], box_center[2] FROM nodes WHERE node_id=%d", nodeID)
	err := db.QueryRow(query).Scan(&boxCenterX, &boxCenterY)
	if err != nil {
		log.Fatalf("[ E ] getBoxCenter query: %v\n\t\t\t query: %s\n", err, query)
	}

	x, parseErr := strconv.ParseFloat(string(boxCenterX), 64)
	y, parseErr := strconv.ParseFloat(string(boxCenterX), 64)

	if parseErr != nil {
		log.Fatalf("[ E ] parse boxCenter: %v\n\t\t\t query: %s\n", err, query)
		log.Fatalf("[ E ] parse boxCenter: (%f, %f)\n", x, y)
	}

	boxCenterFloat := []float64{x, y}

	return boxCenterFloat
}

// newNode Inserts a new node into the database with the given parameters
func newNode(x float64, y float64, width float64, depth int64) int64 {
	// build the query creating a new node
	query := fmt.Sprintf("INSERT INTO nodes (box_center, box_width, depth, isleaf) VALUES ('{%f, %f}', %f, %d, TRUE) RETURNING node_id", x, y, width, depth)

	var nodeID int64

	// execute the query
	err := db.QueryRow(query).Scan(&nodeID)
	if err != nil {
		log.Fatalf("[ E ] newNode query: %v\n\t\t\t query: %s\n", err, query)
	}

	return nodeID
}

func getBlockingStarID(nodeID int64) int64 {
	// get the star id from the node
	var starID int64
	query := fmt.Sprintf("SELECT star_id FROM nodes WHERE node_id=%d", nodeID)
	err := db.QueryRow(query).Scan(&starID)
	if err != nil {
		log.Fatalf("[ E ] getBlockingStarID id query: %v\n\t\t\t query: %s\n", err, query)
	}

	return starID
}

// deleteAll Stars deletes all the rows in the stars table
func deleteAllStars() {
	// build the query creating a new node
	query := "DELETE FROM stars WHERE TRUE"

	// execute the query
	rows, err := db.Query(query)
	defer rows.Close()
	if err != nil {
		log.Fatalf("[ E ] deleteAllStars query: %v\n\t\t\t query: %s\n", err, query)
	}
}

// deleteAll Stars deletes all the rows in the nodes table
func deleteAllNodes() {
	// build the query creating a new node
	query := "DELETE FROM nodes WHERE TRUE"

	// execute the query
	_, err := db.Query(query)
	if err != nil {
		log.Fatalf("[ E ] deleteAllStars query: %v\n\t\t\t query: %s\n", err, query)
	}
}

// getNodeDepth returns the depth of the given node in the tree
func getNodeDepth(nodeID int64) int64 {
	// build the query
	query := fmt.Sprintf("SELECT depth FROM nodes WHERE node_id=%d", nodeID)

	var depth int64

	// Execute the query
	err := db.QueryRow(query).Scan(&depth)
	if err != nil {
		log.Fatalf("[ E ] getNodeDepth query: %v \n\t\t\t query: %s\n", err, query)
	}

	return depth
}

// quadrant returns the quadrant into which the given star belongs
func quadrant(star structs.Star2D, nodeID int64) int64 {
	// get the center of the node the star is in
	center := getBoxCenter(nodeID)
	centerX := center[0]
	centerY := center[1]

	if star.C.X > centerX {
		if star.C.Y > centerY {
			// North East condition
			return 1
		}
		// South East condition
		return 3
	}

	if star.C.Y > centerY {
		// North West condition
		return 0
	}
	// South West condition
	return 2
}

// getQuadrantNodeID returns the id of the requested child-node
// Example: if a parent has four children and quadrant 0 is requested, the function returns the north east child id
func getQuadrantNodeID(parentNodeID int64, quadrant int64) int64 {
	var a, b, c, d []uint8

	// get the star from the stars table
	query := fmt.Sprintf("SELECT subnode[1], subnode[2], subnode[3], subnode[4] FROM nodes WHERE node_id=%d", parentNodeID)
	err := db.QueryRow(query).Scan(&a, &b, &c, &d)
	if err != nil {
		log.Fatalf("[ E ] getQuadrantNodeID star query: %v \n\t\t\tquery: %s\n", err, query)
	}

	returnA, _ := strconv.ParseInt(string(a), 10, 64)
	returnB, _ := strconv.ParseInt(string(b), 10, 64)
	returnC, _ := strconv.ParseInt(string(c), 10, 64)
	returnD, _ := strconv.ParseInt(string(d), 10, 64)

	switch quadrant {
	case 0:
		return returnA
	case 1:
		return returnB
	case 2:
		return returnC
	case 3:
		return returnD
	}

	return -1
}

func getStar(starID int64) structs.Star2D {
	var x, y, vx, vy, m float64

	// get the star from the stars table
	query := fmt.Sprintf("SELECT x, y, vx, vy, m FROM stars WHERE star_id=%d", starID)
	err := db.QueryRow(query).Scan(&x, &y, &vx, &vy, &m)
	if err != nil {
		log.Fatalf("[ E ] getStar query: %v \n\t\t\tquery: %s\n", err, query)
	}

	star := structs.Star2D{
		C: structs.Vec2{
			X: x,
			Y: y,
		},
		V: structs.Vec2{
			X: vx,
			Y: vy,
		},
		M: m,
	}

	return star
}

func removeStarFromNode(nodeID int64) {
	// build the query
	query := fmt.Sprintf("UPDATE nodes SET star_id=0 WHERE node_id=%d", nodeID)

	// Execute the query
	rows, err := db.Query(query)
	defer rows.Close()
	if err != nil {
		log.Fatalf("[ E ] removeStarFromNode query: %v\n\t\t\t query: %s\n", err, query)
	}
}

func getListOfStarsGo() []structs.Star2D {
	// build the query
	query := fmt.Sprintf("SELECT * FROM stars")

	// Execute the query
	rows, err := db.Query(query)
	defer rows.Close()
	if err != nil {
		log.Fatalf("[ E ] removeStarFromNode query: %v\n\t\t\t query: %s\n", err, query)
	}

	var starList []structs.Star2D

	// iterate over the returned rows
	for rows.Next() {

		var star_id int64
		var x, y, vx, vy, m float64
		scanErr := rows.Scan(&star_id, &x, &y, &vx, &vy, &m)
		if scanErr != nil {
			log.Fatalf("[ E ] scan error: %v", scanErr)
		}

		star := structs.Star2D{
			C: structs.Vec2{
				X: x,
				Y: y,
			},
			V: structs.Vec2{
				X: vx,
				Y: vy,
			},
			M: m,
		}

		starList = append(starList, star)
	}

	return starList
}

func getListOfStarsCsv() []string {
	// build the query
	query := fmt.Sprintf("SELECT * FROM stars")

	// Execute the query
	rows, err := db.Query(query)
	defer rows.Close()
	if err != nil {
		log.Fatalf("[ E ] removeStarFromNode query: %v\n\t\t\t query: %s\n", err, query)
	}

	var starList []string

	// iterate over the returned rows
	for rows.Next() {

		var star_id int64
		var x, y, vx, vy, m float64
		scanErr := rows.Scan(&star_id, &x, &y, &vx, &vy, &m)
		if scanErr != nil {
			log.Fatalf("[ E ] scan error: %v", scanErr)
		}

		row := fmt.Sprintf("%d, %f, %f, %f, %f, %f", star_id, x, y, vx, vy, m)
		starList = append(starList, row)
	}

	return starList
}

func insertList(filename string) {
	// open the file
	content, readErr := ioutil.ReadFile(filename)
	if readErr != nil {
		panic(readErr)
	}

	in := string(content)
	reader := csv.NewReader(strings.NewReader(in))

	// insert all the stars into the db
	for {
		record, err := reader.Read()
		if err == io.EOF {
			log.Println("EOF")
			break
		}
		if err != nil {
			log.Println("insertListErr")
			panic(err)
		}

		x, _ := strconv.ParseFloat(record[0], 64)
		y, _ := strconv.ParseFloat(record[1], 64)

		star := structs.Star2D{
			C: structs.Vec2{
				X: x / 100000,
				Y: y / 100000,
			},
			V: structs.Vec2{
				X: 0,
				Y: 0,
			},
			M: 1000,
		}

		fmt.Printf("Inserting (%f, %f)\n", star.C.X, star.C.Y)
		insertStar(star, 1)
	}
}