package main

import (
	"database/sql"
	_ "github.com/Freeaqingme/golang-sql-driver-mysql"
	"fmt"
	"time"
)

var ClimateDb *sql.DB

func InitDb() error {
	db, err := sql.Open("mysql", "admin:sekna123jk@tcp(127.0.0.1:3306)/climate")
	if err != nil {
		return fmt.Errorf("error connecting to database: %w", err)
	}
	ClimateDb = db
	return nil
}

func CloseDb() {
	if ClimateDb != nil {
		err := ClimateDb.Close()
		if err != nil {
			fmt.Println("error closing database: " + err.Error())
		}
	}
}

func writeSnapshotToDb(snapshotSub *SnapshotSubmission) (int64, error) {
	queryStr := "INSERT INTO `snapshots` (`temp`, `humidity`, `co2`, `time`, `id`) VALUES (?, ?, ?, ?, NULL);"
	query, err := ClimateDb.Prepare(queryStr)
	if err != nil {
		return -1, fmt.Errorf("couldn't prepare snapshot query: %w", err)
	}
	result, err := query.Exec(
		snapshotSub.Temp,
		snapshotSub.Humidity,
		snapshotSub.Co2,
		snapshotSub.Timestamp,
	)
	if err != nil {
		return -1, fmt.Errorf("couldn't execute query to write snapshot to db: %w", err)
	}
	id, _ := result.LastInsertId()
	return id, nil
}

func getLastSnapshotRecordFromDb() (*SnapshotRecord, error) {
	var snapshotRecord SnapshotRecord
	query := "SELECT `%s`, `%s`, `%s`, `%s`, `%s` FROM `snapshots` ORDER BY `id` DESC LIMIT 1;"
	rows, err := ClimateDb.Query(fmt.Sprintf(query, "id", "temp", "humidity", "co2", "time"))
	if err != nil {
		return nil, err
	}
	rows.Next()
	err = rows.Scan(
		&snapshotRecord.Id,
		&snapshotRecord.Temp,
		&snapshotRecord.Humidity,
		&snapshotRecord.Co2,
		&snapshotRecord.Timestamp,
	)
	if err != nil {
		return nil, fmt.Errorf("couldn't read last snapshot from the database: %w", err)
	}
	return &snapshotRecord, nil
}

func getSnapshotRecordsFromDb(dateSince time.Time) ([]*SnapshotRecord, error) {
	snapshots := make([]*SnapshotRecord, 0)
	query := "SELECT `%s`, `%s`, `%s`, `%s`, `%s` FROM `snapshots` WHERE `%s` > %s ORDER BY `id` DESC;"
	rows, err := ClimateDb.Query(fmt.Sprintf(query, "id", "temp", "humidity", "co2", "time", "time", dateSince.String()))
	if err != nil {
		return nil, fmt.Errorf("couldn't execute select query: %w", err)
	}
	for rows.Next() {
		var snapshotRecord SnapshotRecord
		err = rows.Scan(
			&snapshotRecord.Id,
			&snapshotRecord.Temp,
			&snapshotRecord.Humidity,
			&snapshotRecord.Co2,
			&snapshotRecord.Timestamp,
		)
		if err != nil {
			return nil, fmt.Errorf("couldn't read rows from the database: %w", err)
		}
		snapshots = append(snapshots, &snapshotRecord)
	}
	return snapshots, nil
}