package shroom_internals

import (
	"database/sql"
	"fmt"
)

// TODO write tests for all of these

func CreateTable(db *sql.DB) error {
	// create the table for the shroom data if it doesn't exist
	create_table := `CREATE TABLE IF NOT EXISTS shrooms (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		time INTEGER,
		humidity REAL,
		temperature REAL,
		humidifier_volts REAL,
		humidifier_volts2 REAL
	);`

	_, err := db.Exec(create_table)
	return err
}

type Datapoint struct {
	Time             uint64  `json:"t"`
	Temperature      float32 `json:"temp"`
	Humidity         float32 `json:"hum"`
	HumidifierVolts  float32 `json:"hv"`
	HumidifierVolts2 float32 `json:"hv2"`
}

func QueryHistory(db *sql.DB, start int64) ([]Datapoint, error) {
	query := "SELECT time, temperature, humidity, humidifier_volts, humidifier_volts2 FROM shrooms WHERE time > ?"
	rows, err := db.Query(query, start)
	if err != nil {
		return nil, fmt.Errorf("sql error: %w", err)
	}
	defer rows.Close()
	results := make([]Datapoint, 0)
	for rows.Next() {
		d := Datapoint{}
		err = rows.Scan(&d.Time, &d.Temperature, &d.Humidity, &d.HumidifierVolts, &d.HumidifierVolts2)
		if err != nil {
			return nil, fmt.Errorf("sql scan error: %w", err)
		}
		results = append(results, d)
	}
	return results, nil
}

func LatestTime(db *sql.DB) (int64, error) {
	query := "SELECT MAX(time) FROM shrooms"
	rows, err := db.Query(query)
	if err != nil {
		return -1, fmt.Errorf("sql error: %w", err)
	}
	defer rows.Close()
	if !rows.Next() {
		// i guess the database is empty
		return 0, nil
	}
	t := int64(0)
	err = rows.Scan(&t)
	if err != nil {
		return -1, fmt.Errorf("sql scan error: %w", err)
	}
	return t, nil
}

func OldestTime(db *sql.DB) (int64, error) {
	query := "SELECT MIN(time) FROM shrooms"
	rows, err := db.Query(query)
	if err != nil {
		return -1, fmt.Errorf("sql error: %w", err)
	}
	defer rows.Close()
	if !rows.Next() {
		// i guess the database is empty
		return 0, nil
	}
	t := int64(0)
	err = rows.Scan(&t)
	if err != nil {
		return -1, fmt.Errorf("sql scan error: %w", err)
	}
	return t, nil
}

func InsertRow(db *sql.DB, s *DataJson) error {
	_, err := db.Exec("INSERT INTO shrooms (time, temperature, humidity, humidifier_volts, humidifier_volts2) VALUES (?, ?, ?, ?, ?)",
		*s.Time, *s.Temperature, *s.Humidity, *s.HumidifierVolts, *s.HumidifierVolts2)
	return err
}

func ClearOldRows(db *sql.DB, min_time int64) error {
	_, err := db.Exec("DELETE FROM shrooms WHERE time < ?", min_time)
	return err
}