package database

import (
	"log"

	"github.com/jmoiron/sqlx"
	_ "modernc.org/sqlite"
)

type DB struct {
	db *sqlx.DB
}

func New() *DB {
	d := &DB{}
	con, err := sqlx.Open("sqlite", "./todogo.db")
	if err != nil {
		log.Fatal(err)
	}
	d.db = con

	err = d.init()
	if err != nil {
		log.Fatal(err)
	}

	return d
}

func (d *DB) init() error {
	d.migrate()

	return nil
}

func (d *DB) InsertTodo(title string) error {
	_, err := d.db.Exec("INSERT INTO todos(title, completed) VALUES(?, ?)", title, 0)
	return err
}

func (d *DB) ToggleTodo(id int) error {
	_, err := d.db.Exec(`
    UPDATE todos
    SET completed = (CASE WHEN completed = 0 THEN 1 ELSE 0 END)
    WHERE id = ?
  `, id)
	return err
}

func (d *DB) DeleteTodo(id int) error {
	_, err := d.db.Exec("DELETE FROM todos WHERE id = ?", id)
	return err
}

func (d *DB) GetAllTodos() ([]Todo, error) {
	todos := []Todo{}
	err := d.db.Select(&todos, "SELECT id, title, completed FROM todos")
	if err != nil {
		return nil, err
	}

	return todos, nil
}

func (d *DB) GetTodosByCompleted(completed bool) ([]Todo, error) {
	completedInt := 0
	if completed {
		completedInt = 1
	}
	todos := []Todo{}
	err := d.db.Select(&todos, "SELECT id, title, completed FROM todos WHERE completed = ?", completedInt)
	if err != nil {
		return nil, err
	}

	return todos, nil
}

func (d *DB) GetIncompleteTodosCount() int {
	var count int
	err := d.db.Get(&count, "SELECT COUNT(*) FROM todos WHERE completed = 0")
	if err != nil {
		log.Printf("failed to get incomplete count")
		return 0
	}

	return count
}

func (d *DB) DeleteCompletedTodos() error {
	_, err := d.db.Exec("DELETE FROM todos WHERE completed = 1")
	return err
}

type Todo struct {
	Id        int
	Title     string
	Completed bool
}