package db

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

	"postern/internal/model"
)

func (db *DB) InsertUserAddress(ctx context.Context, username, address string) error {
	tx, err := db.write.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer txRollback(tx)

	// Get userID
	var userID int
	row := tx.QueryRowContext(ctx, `SELECT id FROM users WHERE name = ?`, username)
	if err = row.Scan(&userID); err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return errors.New("user not found")
		}
		return err
	}

	// Insert address
	_, err = tx.ExecContext(ctx, `INSERT INTO addresses (user_id, name, created_at) VALUES (?, ?, ?)`, userID, address, time.Now().Unix())
	if err != nil {
		return err
	}
	return tx.Commit()
}

// RemoveUserAddress removes a specific address from a user. It returns
// model.ErrNotFound wrapped error if the user or address does not exist.
func (db *DB) RemoveUserAddress(ctx context.Context, username, address string) error {
	tx, err := db.write.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer txRollback(tx)

	// Get userID
	var userID int
	row := tx.QueryRowContext(ctx, `SELECT id FROM users WHERE name = ?`, username)
	if err = row.Scan(&userID); err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return fmt.Errorf("%w: user %q", model.ErrNotFound, username)
		}
		return err
	}

	res, err := tx.ExecContext(ctx, `DELETE FROM addresses WHERE user_id = ? AND name = ?`, userID, address)
	if err != nil {
		return err
	}

	n, err := res.RowsAffected()
	if err != nil {
		return err
	}
	if n == 0 {
		return fmt.Errorf("%w: address %q", model.ErrNotFound, address)
	}
	return tx.Commit()
}

// ListUserAddresses returns all addresses for a user, ordered by name.
func (db *DB) ListUserAddresses(ctx context.Context, username string) ([]string, error) {
	rows, err := db.read.QueryContext(
		ctx, `SELECT addresses.name FROM addresses INNER JOIN users ON users.id = addresses.user_id WHERE users.name = ? ORDER BY addresses.name`, username)
	if err != nil {
		return nil, err
	}
	defer func(rows *sql.Rows) {
		_ = rows.Close()
	}(rows)

	var addresses []string
	for rows.Next() {
		var name string
		if err := rows.Scan(&name); err != nil {
			return nil, err
		}
		addresses = append(addresses, name)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return addresses, nil
}

func (db *DB) GetUserForAddress(ctx context.Context, address string) (model.User, error) {
	res := db.read.QueryRowContext(ctx, `SELECT users.id, users.name, users.password_hash FROM users INNER JOIN addresses ON users.id = addresses.user_id WHERE addresses.name = ?`, address)

	var u model.User
	var pw []byte
	err := res.Scan(&u.ID, &u.Name, &pw)
	u.SetPassword(pw)
	return u, err
}

func (db *DB) GetGatekeepDecision(ctx context.Context, userID int, fromAddress string) (string, error) {
	res := db.read.QueryRowContext(ctx, `SELECT destination FROM gatekeepers WHERE from_address = ? and user_id = ?`, fromAddress, userID)

	var mailbox string
	err := res.Scan(&mailbox)
	if err != nil {
		return "", err
	}

	return mailbox, nil
}