package main

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

// Tag : holds information about a specific Tag
type Tag struct {
	ID              int       `json:"id"`
	Name            string    `json:"name"`
	TagClassID      int       `json:"tagClassId"`
	TagClass        TagClass  `json:"tagClass"`
	TagName         string    `json:"tagName"`
	DeviceID        int       `json:"deviceId"`
	Device          Device    `json:"device"`
	Description     string    `json:"description"`
	DataTypeID      int       `json:"dataTypeId"`
	DataType        DataType  `json:"dataType"`
	ChangeThreshold float32   `json:"changeThreshold"`
	GuaranteeSec    int       `json:"guaranteeSec"`
	MapFunction     string    `json:"mapFunction"`
	Units           string    `json:"units"`
	MinExpected     float32   `json:"minExpected"`
	MaxExpected     float32   `json:"maxExpected"`
	CreatedAt       time.Time `json:"createdAt"`
	UpdatedAt       time.Time `json:"updatedAt"`
}

// Tags : a list of Tag items
type Tags []Tag

const tagTableCreationQuery = `CREATE TABLE IF NOT EXISTS tags (
	id int(10) unsigned NOT NULL AUTO_INCREMENT,
	name varchar(255),
	tagName varchar(255),
	tagClassId int(10) unsigned,
	deviceId int(10) unsigned,
	description varchar(255),
	dataTypeId int(10) unsigned,
	changeThreshold float,
	guaranteeSec int(10) unsigned,
	mapFunction varchar(255),
	units varchar(255),
	minExpected float,
	maxExpected float,
	createdAt datetime,
	updatedAt datetime,
	PRIMARY KEY (id),
	CONSTRAINT fk_tagclass FOREIGN KEY (tagClassId) REFERENCES tagClasses(id),
	CONSTRAINT fk_device FOREIGN KEY (deviceId) REFERENCES devices(id),
	CONSTRAINT fk_datatype FOREIGN KEY (dataTypeId) REFERENCES dataTypes(id)
  );`

const latestTagValueCreationQuery = `CREATE TABLE IF NOT EXISTS latestValues (
	id int(10) unsigned NOT NULL AUTO_INCREMENT,
	tagId int(10) unsigned,
	val varchar(64),
	createdAt datetime,
	updatedAt datetime,
	PRIMARY KEY (id),
	CONSTRAINT fk_tagId FOREIGN KEY (tagId) REFERENCES tags(id) ON DELETE CASCADE
	);`

func ensureTagTableExists(db *sql.DB) {
	if _, err := db.Exec(tagTableCreationQuery); err != nil {
		log.Fatal(err)
	}
}

func ensureLatestTagValueTableExists(db *sql.DB) {
	if _, err := db.Exec(latestTagValueCreationQuery); err != nil {
		log.Fatal(err)
	}
}

// getTag : used during GET command
func (t *Tag) getTag(db *sql.DB) error {
	sqlQuery := `SELECT
		tags.name,
		tags.tagName,
		tags.tagClassId,
		tagClasses.id,
		tagClasses.classType,
		tagClasses.description,
		tagClasses.updatedAt,
		tagClasses.createdAt,
		tags.deviceId,
		devices.id,
		devices.name,
		devices.deviceTypeId,
		deviceTypes.id,
		deviceTypes.name,
		deviceTypes.createdAt,
		deviceTypes.updatedAt,
		devices.address,
		devices.createdAt,
		devices.updatedAt,
		tags.description,
		tags.dataTypeId,
		dataTypes.id,
		dataTypes.dataType,
		dataTypes.plcType,
		dataTypes.updatedAt,
		dataTypes.createdAt,
		tags.changeThreshold,
		tags.guaranteeSec,
		tags.mapFunction,
		tags.units,
		tags.maxExpected,
		tags.minExpected,
		tags.createdAt,
		tags.updatedAt
		FROM tags
		JOIN tagClasses ON tags.tagClassId = tagClasses.id
		JOIN devices ON tags.deviceId = devices.id
		JOIN deviceTypes ON devices.deviceTypeId = deviceTypes.id
		JOIN dataTypes ON tags.dataTypeId = dataTypes.id
		WHERE tags.id=?`
	return db.QueryRow(sqlQuery, t.ID).Scan(
		&t.Name,
		&t.TagName,
		&t.TagClassID,
		&t.TagClass.ID,
		&t.TagClass.ClassType,
		&t.TagClass.Description,
		&t.TagClass.UpdatedAt,
		&t.TagClass.UpdatedAt,
		&t.DeviceID,
		&t.Device.ID,
		&t.Device.Name,
		&t.Device.DeviceTypeID,
		&t.Device.DeviceType.ID,
		&t.Device.DeviceType.Name,
		&t.Device.DeviceType.CreatedAt,
		&t.Device.DeviceType.UpdatedAt,
		&t.Device.Address,
		&t.Device.CreatedAt,
		&t.Device.UpdatedAt,
		&t.Description,
		&t.DataTypeID,
		&t.DataType.ID,
		&t.DataType.DataType,
		&t.DataType.PlcType,
		&t.DataType.UpdatedAt,
		&t.DataType.CreatedAt,
		&t.ChangeThreshold,
		&t.GuaranteeSec,
		&t.MapFunction,
		&t.Units,
		&t.MaxExpected,
		&t.MinExpected,
		&t.CreatedAt,
		&t.UpdatedAt)
}

// updateTag : used during PUT command
func (t *Tag) updateTag(db *sql.DB) error {
	sqlQuery := `UPDATE tags SET
		name=?,
		tagName=?,
		tagClassId=?,
		deviceId=?,
		description=?,
		dataTypeId=?,
		changeThreshold=?,
		guaranteeSec=?,
		mapFunction=?,
		units=?,
		minExpected=?,
		maxExpected=?,
		updatedAt=?
	WHERE id=?`
	updStmt, updErr := db.Prepare(sqlQuery)
	if updErr != nil {
		panic(updErr.Error()) // proper error handling instead of panic in your app
	}
	defer updStmt.Close() // Close the statement when we leave main() / the program terminates

	_, err := updStmt.Exec(t.Name, t.TagName, t.TagClassID, t.DeviceID,
		t.Description, t.DataTypeID, t.ChangeThreshold, t.GuaranteeSec,
		t.MapFunction, t.Units, t.MinExpected, t.MaxExpected, time.Now(), t.ID)
	return err
}

// deleteTag : used during DELETE command
func (t *Tag) deleteTag(db *sql.DB) error {
	_, err := db.Exec("DELETE FROM tags WHERE id=?", t.ID)
	return err
}

// createTag : used during PUSH command
func (t *Tag) createTag(db *sql.DB) error {
	tagSQLQuery := `INSERT INTO tags (
		name,
		tagName,
		tagClassId,
		deviceId,
		description,
		dataTypeId,
		changeThreshold,
		guaranteeSec,
		mapFunction,
		units,
		minExpected,
		maxExpected,
		createdAt,
		updatedAt) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`

	valSQLQuery := `INSERT INTO latestValues (tagId, val, createdAt, updatedAt) VALUES (?, ?, ?, ?)`

	stmtIns, insErr := db.Prepare(tagSQLQuery)
	if insErr != nil {
		panic(insErr.Error()) // proper error handling instead of panic in your app
	}
	defer stmtIns.Close() // Close the statement when we leave main() / the program terminates

	stmtInsVal, insValErr := db.Prepare(valSQLQuery)
	if insValErr != nil {
		panic(insValErr.Error()) // proper error handling instead of panic in your app
	}
	defer stmtInsVal.Close() // Close the statement when we leave main() / the program terminates

	res, err := stmtIns.Exec(t.Name, t.TagName, t.TagClassID, t.DeviceID,
		t.Description, t.DataTypeID, t.ChangeThreshold, t.GuaranteeSec,
		t.MapFunction, t.Units, t.MinExpected, t.MaxExpected, time.Now(), time.Now())

	lastID, idErr := res.LastInsertId()
	if idErr == nil {
		_, valErr := stmtInsVal.Exec(lastID, "0.0", time.Now(), time.Now())
		return valErr
	}
	fmt.Printf("\n%s -- %d\n", "Invalid last inserted id", lastID)

	return err
}

// getTags : used during GET command for all
func getTags(db *sql.DB, start, count int) (Tags, error) {
	sqlQuery := `SELECT
		tags.ID,
		tags.name,
		tags.tagName,
		tags.tagClassId,
		tagClasses.id,
		tagClasses.classType,
		tagClasses.description,
		tagClasses.updatedAt,
		tagClasses.createdAt,
		tags.deviceId,
		devices.id,
		devices.name,
		devices.deviceTypeId,
		deviceTypes.id,
		deviceTypes.name,
		deviceTypes.createdAt,
		deviceTypes.updatedAt,
		devices.address,
		devices.createdAt,
		devices.updatedAt,
		tags.description,
		tags.dataTypeId,
		dataTypes.id,
		dataTypes.dataType,
		dataTypes.plcType,
		dataTypes.updatedAt,
		dataTypes.createdAt,
		tags.changeThreshold,
		tags.guaranteeSec,
		tags.mapFunction,
		tags.units,
		tags.maxExpected,
		tags.minExpected,
		tags.createdAt,
		tags.updatedAt
	FROM tags
	JOIN tagClasses ON tags.tagClassId = tagClasses.id
	JOIN devices ON tags.deviceId = devices.id
	JOIN deviceTypes ON devices.deviceTypeId = deviceTypes.id
	JOIN dataTypes ON tags.dataTypeId = dataTypes.id
	LIMIT ? OFFSET ?;`

	getStmt, prepErr := db.Prepare(sqlQuery)
	if prepErr != nil {
		panic(prepErr.Error()) // proper error handling instead of panic in your app
	}
	defer getStmt.Close() // Close the statement when we leave main() / the program terminates

	rows, err := getStmt.Query(count, start)

	if err != nil {
		return nil, err
	}

	defer rows.Close()

	tags := Tags{}

	for rows.Next() {
		var t Tag
		if err := rows.Scan(&t.ID,
			&t.Name,
			&t.TagName,
			&t.TagClassID,
			&t.TagClass.ID,
			&t.TagClass.ClassType,
			&t.TagClass.Description,
			&t.TagClass.UpdatedAt,
			&t.TagClass.UpdatedAt,
			&t.DeviceID,
			&t.Device.ID,
			&t.Device.Name,
			&t.Device.DeviceTypeID,
			&t.Device.DeviceType.ID,
			&t.Device.DeviceType.Name,
			&t.Device.DeviceType.CreatedAt,
			&t.Device.DeviceType.UpdatedAt,
			&t.Device.Address,
			&t.Device.CreatedAt,
			&t.Device.UpdatedAt,
			&t.Description,
			&t.DataTypeID,
			&t.DataType.ID,
			&t.DataType.DataType,
			&t.DataType.PlcType,
			&t.DataType.UpdatedAt,
			&t.DataType.CreatedAt,
			&t.ChangeThreshold,
			&t.GuaranteeSec,
			&t.MapFunction,
			&t.Units,
			&t.MaxExpected,
			&t.MinExpected,
			&t.CreatedAt,
			&t.UpdatedAt); err != nil {
			return nil, err
		}
		tags = append(tags, t)
	}

	return tags, nil
}