package db

import (
	"database/sql"
	"fmt"
	_ "github.com/mattn/go-sqlite3"
	"strconv"
	"time"
)

type User struct {
	Id        string
	Username  string
	IpAddress string
	Timezone  string
}

type Message struct {
	Id             string
	SenderIp       string
	SenderUsername string
	Content        string
	Timestamp      string
	Edited         bool
}

type Database struct {
	db *sql.DB
}

func OpenDatabase(filepath string) *Database {
	if db, err := sql.Open("sqlite3", filepath); err != nil {
		return nil
	} else {
		return &Database{
			db: db,
		}
	}
}

func (db *Database) Close() {
	db.db.Close()
}

func (db *Database) DbCreateTableMessages() {
	stmt := `CREATE TABLE IF NOT EXISTS messages (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		ip_address TEXT NOT NULL,
		content TEXT NOT NULL,
		edited INTEGER DEFAULT 0,
		created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
	)`
	db.db.Exec(stmt)
}

func (db *Database) DbCreateTableUsers() {
	stmt := `CREATE TABLE IF NOT EXISTS users (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		ip_address TEXT NOT NULL,
		username TEXT NOT NULL UNIQUE,
		timezone TEXT DEFAULT 'America/New_York',
		created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
	)`
	db.db.Exec(stmt)
}

func (db *Database) UserTimezoneSet(ip_address, timezone string) {
	_, err := db.db.Exec("UPDATE users SET timezone = ? WHERE ip_address = ?", timezone, ip_address)
	if err != nil {
		fmt.Println(err)
	}
}

func (db *Database) UserAdd(ip_address, username string) {
	_, err := db.db.Exec("INSERT INTO users (username, ip_address) VALUES (?, ?)", username, ip_address)
	if err != nil {
		fmt.Println(err)
	}
}

func (db *Database) MessageAdd(ip_address string, content string) {
	_, err := db.db.Exec("INSERT INTO messages (ip_address, content) VALUES (?, ?)", ip_address, content)
	if err != nil {
		fmt.Println(err)
	}
}

func (db *Database) UserNameGet(ip_address string) string {
	rows, err := db.db.Query("SELECT username FROM users WHERE ip_address = ?", ip_address)
	if err != nil {
		fmt.Println(err)
	}
	defer rows.Close()
	var username string
	rows.Next()
	rows.Scan(&username)
	return username
}

func (db *Database) UserIpGet(username string) string {
	rows, err := db.db.Query("SELECT ip_address FROM users WHERE username = ?", username)
	if err != nil {
		fmt.Println(err)
	}
	defer rows.Close()
	var ip_address string
	rows.Next()
	rows.Scan(&ip_address)
	return ip_address
}

func (db *Database) UserGetTimezone(ip_address string) string {
	rows, err := db.db.Query("SELECT timezone FROM users WHERE ip_address = ?", ip_address)
	if err != nil {
		fmt.Println(err)
	}
	defer rows.Close()
	var timezone string
	rows.Next()
	rows.Scan(&timezone)
	return timezone
}

func (db *Database) UsersGet() []User {
	rows, err := db.db.Query("SELECT * FROM users")
	if err != nil {
		fmt.Println(err)
	}
	defer rows.Close()
	var users []User
	for rows.Next() {
		var id string
		var ip_address string
		var username string
		var created_at string
		var timezone string
		rows.Scan(&id, &ip_address, &username, &created_at, &timezone)
		user := User{
			Id:        id,
			Username:  username,
			IpAddress: ip_address,
			Timezone:  timezone,
		}
		users = append(users, user)
	}
	return users
}

func (db *Database) MessagesGet() []Message {
	rows, err := db.db.Query(`
		SELECT messages.id, messages.ip_address, messages.content, 
		strftime('%Y-%m-%d %H:%M:%S', messages.created_at) as created_at, 
		users.username, messages.edited
		FROM messages 
		LEFT JOIN users ON messages.ip_address = users.ip_address;
	`)
	if err != nil {
		fmt.Println(err)
	}
	defer rows.Close()

	var messages []Message

	for rows.Next() {
		var id string
		var content string
		var ip_address string
		var created_at string
		var username string
		var edited int
		rows.Scan(&id, &ip_address, &content, &created_at, &username, &edited)

		editedBool := false
		if edited == 1 {
			editedBool = true
		}

		message := Message{
			Id:             id,
			Content:        content,
			SenderIp:       ip_address,
			SenderUsername: username,
			Edited:         editedBool,
			Timestamp:      created_at,
		}

		messages = append(messages, message)
	}
	return messages
}

func (db *Database) UserNameExists(username string) bool {
	rows, err := db.db.Query("SELECT * FROM users WHERE username = ?", username)
	if err != nil {
		fmt.Println(err)
	}
	defer rows.Close()
	return rows.Next()
}

func (db *Database) UserExists(ip string) bool {
	rows, err := db.db.Query("SELECT * FROM users WHERE ip_address = ?", ip)
	if err != nil {
		fmt.Println(err)
	}
	defer rows.Close()
	return rows.Next()
}

func (db *Database) UserNameChange(ip, newUsername string) {
	_, err := db.db.Exec("UPDATE users SET username = ? WHERE ip_address = ?", newUsername, ip)
	if err != nil {
		fmt.Println(err)
	}
}

func (db *Database) UserMessagesDelete(ip string) {
	_, err := db.db.Exec("DELETE FROM messages WHERE ip_address = ?", ip)
	if err != nil {
		fmt.Println(err)
	}
}

func (db *Database) UserMessagesGet(ip string) []Message {
	rows, err := db.db.Query(`
		SELECT messages.*, users.username 
		FROM messages 
		LEFT JOIN users ON messages.ip_address = users.ip_address 
		WHERE messages.ip_address = ?
		ORDER BY messages.created_at DESC;
	`, ip)
	if err != nil {
		fmt.Println(err)
	}
	defer rows.Close()

	var messages []Message

	for rows.Next() {
		var id string
		var content string
		var ip_address string
		var created_at string
		var username string
		var edited int
		rows.Scan(&id, &ip_address, &content, &created_at, &username, &edited)
		t, _ := time.Parse(created_at, created_at)
		editedBool := false
		if edited == 1 {
			editedBool = true
		}
		message := Message{
			Id:             id,
			Content:        content,
			SenderIp:       ip_address,
			SenderUsername: username,
			Edited:         editedBool,
			Timestamp:      t.Format(created_at),
		}
		messages = append(messages, message)
	}
	return messages
}

func (db *Database) MessageDeleteId(id string) {
	_, err := db.db.Exec("DELETE FROM messages WHERE id = ?", id)
	if err != nil {
		fmt.Println(err)
	}
}
func (db *Database) MessageDeleteIfOwner(id string, ip string) (int, error) {
	res, err := db.db.Exec("DELETE FROM messages WHERE id = ? AND ip_address = ?", id, ip)
	if err != nil {
		return 0, err
	}
	affected, err := res.RowsAffected()
	if err != nil {
		return 0, err
	}
	return int(affected), nil

}

func (db *Database) MessageEditIfOwner(id string, content string, ip string) (int, error) {
	res, err := db.db.Exec("UPDATE messages SET content = ?, edited = 1 WHERE id = ? AND ip_address = ?", content, id, ip)
	if err != nil {
		return 0, err
	}
	affected, err := res.RowsAffected()
	if err != nil {
		return 0, err
	}
	return int(affected), nil
}

func (db *Database) DeleteOldMessages(ageMinutes int) {
	if ageMinutes <= 0 {
		return
	}
	age := strconv.Itoa(ageMinutes)
	_, err := db.db.Exec("DELETE FROM messages WHERE created_at < datetime('now', ? || ' minutes')", "-"+age)
	if err != nil {
		fmt.Println(err)
	}
}

func (db *Database) UserDeleteIp(ip string) {
	_, err := db.db.Exec("DELETE FROM users WHERE ip_address = ?", ip)
	if err != nil {
		fmt.Println(err)
	}
}

func (db *Database) UsersDelete() {
	_, err := db.db.Exec("DELETE FROM users")
	if err != nil {
		fmt.Println(err)
	}
}

func (db *Database) MessagesDelete() {
	_, err := db.db.Exec("DELETE FROM messages")
	if err != nil {
		fmt.Println(err)
	}
}