package main

import (
	"database/sql"
	"fmt"
	"log"

	_ "github.com/lib/pq"
)

// setup the Database
func setupDatabase() (*sql.DB, error) {
	connStr := "host=postgresql port=5432 user=postgres dbname=postgres sslmode=disable"

	// open a connection to the database
	db, err := sql.Open("postgres", connStr)
	if err != nil {
		log.Fatal(err)
	}

	// ping the database
	err = db.Ping()
	if err != nil {
		return nil, err
	}

	// create a challenges table if it does not exist yet
	err = dbCreateTableIfNotExist(db)
	if err != nil {
		log.Println(err)
	}

	return db, nil
}

// getNames gets the name of all the challenges
func dbGetName(db *sql.DB) string {

	// get the current name of the challenges
	query := fmt.Sprintf("SELECT name FROM challenges")
	var names string
	err := db.QueryRow(query).Scan(&names)
	if err != nil {
		log.Fatalf("[ E ] :", err)
	}

	return names
}

// getAllChallenges gets all the challenges from the server
func dbGetAllChallenges() []Challenge {

	// build the query
	query := fmt.Sprintf("SELECT * FROM challenges")

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

	var challenges []Challenge

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

		var uuid, name, description, flag, container, category string
		var points int
		var static bool

		scanErr := rows.Scan(&uuid, &name, &description, &flag, &container, &category, &points, &static)
		if scanErr != nil {
			log.Printf("[ E ] scan error: %v", scanErr)
			return []Challenge{}
		}

		newChallenge := Challenge{
			UUID:        uuid,
			Name:        name,
			Description: description,
			Flag:        flag,
			Container:   container,
			Category:    category,
			Points:      points,
			Static:      static,
		}

		challenges = append(challenges, newChallenge)
	}

	return challenges
}

// dbNewChallenge inserts the given challenge into the database
func dbNewChallenge(challenge Challenge) (string, error) {

	// build the query to be executed
	query := fmt.Sprintf("INSERT INTO challenges(name, description, flag, container, category, points, static) VALUES ($1, $2, $3, $4, $5, $6, $7) RETURNING uuid")

	// execute the query with the challenge values given
	var uuid string
	err := db.QueryRow(query, challenge.Name, challenge.Description, challenge.Flag, challenge.Container, challenge.Category, challenge.Points, challenge.Static).Scan(&uuid)

	// handle errors
	if err != nil {
		return "", err
	}
	return uuid, nil
}

// editChallengeUUID edited the challenge with the given uuid using the values in the updatedChallenge
func dbEditChallengeUUID(uuid string, updatedChallenge Challenge) error {

	query := fmt.Sprintf("UPDATE challenges SET name = '$1', description = '$2', flag = '$3', container = '$4', category = '$5', points = $6, static = $7 WHERE uuid::text = '$8'")

	err := db.QueryRow(query, updatedChallenge.Name, updatedChallenge.Description, updatedChallenge.Flag, updatedChallenge.Container, updatedChallenge.Category, updatedChallenge.Points, updatedChallenge.Static, updatedChallenge.UUID)

	if err != nil {
		return fmt.Errorf("could not edit the challenge: %s", err)
	}

	return nil
}

// dbGetChallengeByUUID returns the challenge with the given UUID from the database
func dbGetChallengeByUUID(uuid string) (Challenge, error) {
	// build the query to be executed
	query := fmt.Sprintf("SELECT uuid, name, description, flag, container, category, points, static FROM challenges WHERE uuid::text= '$1'")

	challenge := Challenge{}

	// execute the query storing the values in the challenge struct defined above
	err := db.QueryRow(query, uuid).Scan(&challenge.UUID, &challenge.Name, &challenge.Description, &challenge.Flag, &challenge.Container, &challenge.Category, &challenge.Points, &challenge.Static)

	if err != nil {
		return Challenge{}, err
	}
	return challenge, nil
}

func dbDeleteChallengeByUUID(uuid string) error {
	query := fmt.Sprintf("DELETE FROM challenges WHERE uuid::text = '%s'")

	err := db.QueryRow(query, uuid)
	if err != nil {
		return fmt.Errorf("could not delete the challenge: %s", err)
	}

	return nil
}

func dbCreateTableIfNotExist(db *sql.DB) error {
	log.Println("Creating a table in case it doesn't exist")
	query := `CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE public.challenges
(
	uuid uuid NOT NULL DEFAULT uuid_generate_v4(),
	name character varying COLLATE pg_catalog."default" NOT NULL,
	description character varying COLLATE pg_catalog."default",
	flag character varying COLLATE pg_catalog."default",
	container character varying COLLATE pg_catalog."default",
	category character varying COLLATE pg_catalog."default",
	points integer,
	static boolean NOT NULL,
	CONSTRAINT challenges_pkey PRIMARY KEY (uuid)
)`

	_, err := db.Exec(query)
	if err != nil {
		return err
	}
	return nil
}