package db

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"time"

	"postern/internal/model"

	"modernc.org/sqlite"
	sqlite3 "modernc.org/sqlite/lib"
)

func InsertUser(ctx context.Context, tx *sql.Tx, username string, hash []byte) (int, error) {
	res, err := tx.ExecContext(ctx,
		`INSERT INTO users (name, password_hash, created_at) VALUES (?, ?, ?)`,
		username, hash, time.Now().Unix(),
	)
	if err != nil {
		if insertErr, ok := errors.AsType[*sqlite.Error](err); ok {
			if insertErr.Code() == sqlite3.SQLITE_CONSTRAINT_UNIQUE {
				return 0, fmt.Errorf("user %s already exists", username)
			}
			return 0, fmt.Errorf("inserting user: %w", err)
		}
	}

	userID, err := res.LastInsertId()
	return int(userID), err
}

func (db *DB) GetUser(ctx context.Context, username string) (model.User, error) {
	rows, err := db.read.QueryContext(
		ctx, `SELECT users.id, users.name, users.password_hash, addresses.name FROM users LEFT JOIN addresses ON users.id = addresses.user_id WHERE users.name = ?`, username)
	if err != nil {
		return model.User{}, err
	}
	defer func(rows *sql.Rows) {
		_ = rows.Close()
	}(rows)

	var u model.User
	var pw []byte
	var address sql.NullString
	found := false
	for rows.Next() {
		err := rows.Scan(&u.ID, &u.Name, &pw, &address)
		if err != nil {
			return model.User{}, err
		}
		found = true
		if address.Valid {
			u.Addresses = append(u.Addresses, address.String)
		}
	}
	if rows.Err() != nil {
		return model.User{}, rows.Err()
	}

	if !found {
		return model.User{}, errors.New("user not found")
	}

	u.SetPassword(pw)
	return u, nil
}

func (db *DB) UserExists(ctx context.Context, username string) (bool, error) {
	var count int
	err := db.read.QueryRowContext(ctx,
		`SELECT COUNT(*) FROM users WHERE name = ?`,
		username,
	).Scan(&count)
	if err != nil {
		return false, fmt.Errorf("checking user existence: %w", err)
	}
	return count > 0, nil
}

func InsertDefaultMailboxes(ctx context.Context, tx *sql.Tx, userID int) error {
	uidvalidity := uint32(time.Now().UnixMilli())

	for _, mb := range model.DefaultMailboxes() {
		_, err := tx.ExecContext(ctx, `
            INSERT INTO mailboxes
                (user_id, name, special_use, uidvalidity, uidnext, highest_modseq)
            VALUES
                (?, ?, ?, ?, 1, 0)`,
			userID, mb.Name, mb.SpecialUse, uidvalidity,
		)
		if err != nil {
			return fmt.Errorf("creating mailbox %q: %w", mb.Name, err)
		}
	}

	return nil
}

func (db *DB) ListUsers(ctx context.Context) ([]model.User, error) {
	query := `
        SELECT users.id, users.name, users.password_hash, addresses.name 
        FROM users 
        LEFT JOIN addresses ON users.id = addresses.user_id 
        ORDER BY users.name
    `

	rows, err := db.read.QueryContext(ctx, query)
	if err != nil {
		return nil, err
	}
	defer func(rows *sql.Rows) {
		_ = rows.Close()
	}(rows)

	var users []model.User

	for rows.Next() {
		var id int
		var name string
		var passwordHash []byte
		var addressName sql.NullString

		if err := rows.Scan(&id, &name, &passwordHash, &addressName); err != nil {
			return nil, err
		}

		// If the slice is empty, OR the last appended user has a different ID,
		// we have encountered a new user.
		if len(users) == 0 || users[len(users)-1].ID != id {
			nextUser := model.User{
				ID:        id,
				Name:      name,
				Addresses: []string{},
			}
			nextUser.SetPassword(passwordHash)
			users = append(users, nextUser)
		}

		if addressName.Valid {
			lastIdx := len(users) - 1
			users[lastIdx].Addresses = append(users[lastIdx].Addresses, addressName.String)
		}
	}

	if err = rows.Err(); err != nil {
		return nil, err
	}

	return users, nil
}

func (db *DB) RemoveUser(ctx context.Context, username string) error {
	_, err := db.write.ExecContext(ctx, "DELETE FROM users WHERE name = ?", username)
	return err
}