package tangledalertbot

import (
	"database/sql"
	"errors"
	"fmt"
	"log/slog"
	"os"

	_ "github.com/glebarez/go-sqlite"
)

// Database is a sqlite database
type Database struct {
	db *sql.DB
}

// NewDatabase will open a new database. It will ping the database to ensure it is available and error if not
func NewDatabase(dbPath string) (*Database, error) {
	if dbPath != ":memory:" {
		err := createDbFile(dbPath)
		if err != nil {
			return nil, fmt.Errorf("create db file: %w", err)
		}
	}

	db, err := sql.Open("sqlite", dbPath)
	if err != nil {
		return nil, fmt.Errorf("open database: %w", err)
	}

	err = db.Ping()
	if err != nil {
		return nil, fmt.Errorf("ping db: %w", err)
	}

	err = createIssuesTable(db)
	if err != nil {
		return nil, fmt.Errorf("creating issues table: %w", err)
	}

	err = createCommentsTable(db)
	if err != nil {
		return nil, fmt.Errorf("creating comments table: %w", err)
	}

	err = createUsersTable(db)
	if err != nil {
		return nil, fmt.Errorf("creating users table: %w", err)
	}

	return &Database{db: db}, nil
}

// Close will cleanly stop the database connection
func (d *Database) Close() {
	err := d.db.Close()
	if err != nil {
		slog.Error("failed to close db", "error", err)
	}
}

func createDbFile(dbFilename string) error {
	if _, err := os.Stat(dbFilename); !errors.Is(err, os.ErrNotExist) {
		return nil
	}

	f, err := os.Create(dbFilename)
	if err != nil {
		return fmt.Errorf("create db file : %w", err)
	}
	err = f.Close()
	if err != nil {
		return fmt.Errorf("failed to close DB file: %w", err)
	}
	return nil
}

func createIssuesTable(db *sql.DB) error {
	createTableSQL := `CREATE TABLE IF NOT EXISTS issues (
		"id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
		"authorDid" TEXT,
		"rkey" TEXT,
		"title" TEXT,
		"body" TEXT,
		"repo" TEXT,
		"createdAt" integer NOT NULL,
		UNIQUE(authorDid,rkey)
	  );`

	slog.Info("Create issues table...")
	statement, err := db.Prepare(createTableSQL)
	if err != nil {
		return fmt.Errorf("prepare DB statement to create issues table: %w", err)
	}
	_, err = statement.Exec()
	if err != nil {
		return fmt.Errorf("exec sql statement to create issues table: %w", err)
	}
	slog.Info("issues table created")

	return nil
}

func createCommentsTable(db *sql.DB) error {
	createTableSQL := `CREATE TABLE IF NOT EXISTS comments (
		"id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
		"authorDid" TEXT,
		"rkey" TEXT,
		"body" TEXT,
		"issue" TEXT,
		"createdAt" integer NOT NULL,
		UNIQUE(authorDid,rkey)
	  );`

	slog.Info("Create comments table...")
	statement, err := db.Prepare(createTableSQL)
	if err != nil {
		return fmt.Errorf("prepare DB statement to create comments table: %w", err)
	}
	_, err = statement.Exec()
	if err != nil {
		return fmt.Errorf("exec sql statement to create comments table: %w", err)
	}
	slog.Info("comments table created")

	return nil
}

func createUsersTable(db *sql.DB) error {
	createTableSQL := `CREATE TABLE IF NOT EXISTS users (
		"id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
		"did" TEXT,
		"handle" TEXT,
		"convoId" TEXT,
		"createdAt" integer NOT NULL,
		UNIQUE(did)
	  );`

	slog.Info("Create users table...")
	statement, err := db.Prepare(createTableSQL)
	if err != nil {
		return fmt.Errorf("prepare DB statement to create users table: %w", err)
	}
	_, err = statement.Exec()
	if err != nil {
		return fmt.Errorf("exec sql statement to create users table: %w", err)
	}
	slog.Info("users table created")

	return nil
}

// CreateIssue will insert a issue into a database
func (d *Database) CreateIssue(issue Issue) error {
	sql := `REPLACE INTO issues (authorDid, rkey, title, body, repo, createdAt) VALUES (?, ?, ?, ?, ?, ?);`
	_, err := d.db.Exec(sql, issue.AuthorDID, issue.RKey, issue.Title, issue.Body, issue.Repo, issue.CreatedAt)
	if err != nil {
		return fmt.Errorf("exec insert issue: %w", err)
	}
	return nil
}

// CreateComment will insert a comment into a database
func (d *Database) CreateComment(comment Comment) error {
	sql := `REPLACE INTO comments (authorDid, rkey, body, issue, createdAt) VALUES (?, ?, ?, ?, ?);`
	_, err := d.db.Exec(sql, comment.AuthorDID, comment.RKey, comment.Body, comment.Issue, comment.CreatedAt)
	if err != nil {
		return fmt.Errorf("exec insert comment: %w", err)
	}
	return nil
}

// CreateUser will insert a user into a database
func (d *Database) CreateUser(user User) error {
	sql := `REPLACE INTO users (did, handle, convoId, createdAt) VALUES (?, ?, ?, ?);`
	_, err := d.db.Exec(sql, user.DID, user.Handle, user.ConvoID, user.CreatedAt)
	if err != nil {
		return fmt.Errorf("exec insert user: %w", err)
	}
	return nil
}

func (d *Database) GetIssues() ([]Issue, error) {
	sql := "SELECT authorDid, rkey, title, body, repo, createdAt FROM issues;"
	rows, err := d.db.Query(sql)
	if err != nil {
		return nil, fmt.Errorf("run query to get issues: %w", err)
	}
	defer rows.Close()

	var results []Issue
	for rows.Next() {
		var issue Issue
		if err := rows.Scan(&issue.AuthorDID, &issue.RKey, &issue.Title, &issue.Body, &issue.Repo, &issue.CreatedAt); err != nil {
			return nil, fmt.Errorf("scan row: %w", err)
		}

		results = append(results, issue)
	}
	return results, nil
}

func (d *Database) GetComments() ([]Comment, error) {
	sql := "SELECT authorDid, rkey, body, issue, createdAt FROM comments;"
	rows, err := d.db.Query(sql)
	if err != nil {
		return nil, fmt.Errorf("run query to get comments: %w", err)
	}
	defer rows.Close()

	var results []Comment
	for rows.Next() {
		var comment Comment
		if err := rows.Scan(&comment.AuthorDID, &comment.RKey, &comment.Body, &comment.Issue, &comment.CreatedAt); err != nil {
			return nil, fmt.Errorf("scan row: %w", err)
		}

		results = append(results, comment)
	}
	return results, nil
}

func (d *Database) GetUser(did string) (User, error) {
	sql := "SELECT did, handle, convoId, createdAt FROM users WHERE did = ?;"
	rows, err := d.db.Query(sql, did)
	if err != nil {
		return User{}, fmt.Errorf("run query to get user: %w", err)
	}
	defer rows.Close()

	for rows.Next() {
		var user User
		if err := rows.Scan(&user.DID, &user.Handle, &user.ConvoID, &user.CreatedAt); err != nil {
			return User{}, fmt.Errorf("scan row: %w", err)
		}

		return user, nil
	}
	return User{}, fmt.Errorf("user not found")
}

func (d *Database) GetUsers() ([]User, error) {
	sql := "SELECT did, handle, convoId, createdAt FROM users;"
	rows, err := d.db.Query(sql)
	if err != nil {
		return nil, fmt.Errorf("run query to get user: %w", err)
	}
	defer rows.Close()

	var results []User
	for rows.Next() {
		var user User
		if err := rows.Scan(&user.DID, &user.Handle, &user.ConvoID, &user.CreatedAt); err != nil {
			return nil, fmt.Errorf("scan row: %w", err)
		}
		results = append(results, user)
	}
	return results, nil
}

func (d *Database) DeleteIssue(did, rkey string) error {
	sql := "DELETE FROM issues WHERE authorDid = ? AND rkey = ?;"
	_, err := d.db.Exec(sql, did, rkey)
	if err != nil {
		return fmt.Errorf("exec delete issue: %w", err)
	}
	return nil
}

func (d *Database) DeleteComment(did, rkey string) error {
	sql := "DELETE FROM comments WHERE authorDid = ? AND rkey = ?;"
	_, err := d.db.Exec(sql, did, rkey)
	if err != nil {
		return fmt.Errorf("exec delete issue: %w", err)
	}
	return nil
}

func (d *Database) DeleteCommentsForIssue(issueURI string) error {
	sql := "DELETE FROM comments WHERE issue = ?;"
	_, err := d.db.Exec(sql, issueURI)
	if err != nil {
		return fmt.Errorf("exec delete comments for issue")
	}
	return nil
}

func (d *Database) DeleteUser(did string) error {
	sql := "DELETE FROM users WHERE did = ?;"
	_, err := d.db.Exec(sql, did)
	if err != nil {
		return fmt.Errorf("exec delete user")
	}
	return nil
}