package main

import (
	"database/sql"
	"errors"
	_ "github.com/mattn/go-sqlite3"
)

type SQLConnection struct {
	db *sql.DB
}

func (conn *SQLConnection) init(dbpath string) error {
	var err error
	conn.db, err = sql.Open("sqlite3", dbpath)
	return err
}

func (conn *SQLConnection) registerUser(username string, key string) error {
	_, err := conn.db.Exec("INSERT INTO users (username, key) VALUES (?, ?)", username, key)
	return err
}

func (conn *SQLConnection) checkUserRegistered(username string) (bool, error) {
	result, err := conn.db.Query("SELECT username FROM users WHERE username = ?", username)
	if err != nil {
		return false, err
	}
	for result.Next() {
		_ = result.Close()
		return true, nil
	}
	return false, nil
}

func (conn *SQLConnection) getUserKey(username string) (string, error) {
	query := "SELECT key FROM users WHERE username = ?"
	result, err := conn.db.Query(query, username)
	if err != nil {
		return "", err
	}
	if result.Next() {
		var key string
		_ = result.Scan(&key)
		_ = result.Close()
		return key, nil
	} else {
		return "", errors.New("user not found")
	}
}

func (conn *SQLConnection) saveMessage(message Message) error {
	var err error

	query := `INSERT INTO messages (userId, data, timestamp) values (
		(SELECT id FROM users WHERE username = ?), ?, ?
	)`
	_, err = conn.db.Exec(query, message.User, message.Data, message.Timestamp)

	return err
}

func (conn *SQLConnection) getMessagesSince(timestamp int64) ([]Message, error) {
	var err error
	var msg []Message

	query := `SELECT username, data, timestamp FROM users JOIN messages
	WHERE users.id = messages.userId AND timestamp > ?
	ORDER BY timestamp;`

	result, err := conn.db.Query(query, timestamp)
	if err != nil {
		return msg, err
	}

	for result.Next() {
		var message Message
		_ = result.Scan(&message.User, &message.Data, &message.Timestamp)
		msg = append(msg, message)
	}
	_ = result.Close()

	return msg, err
}