package db

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

	"postern/internal/model"

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

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

func (db *DB) InsertSMTPAuthUser(ctx context.Context, name string, hash []byte) error {
	_, err := db.write.ExecContext(ctx,
		`INSERT INTO smtp_auth (name, password_hash, created_at) VALUES (?, ?, ?)`,
		name, hash, time.Now().Unix(),
	)
	if err != nil {
		if insertErr, ok := errors.AsType[*sqlite.Error](err); ok {
			if insertErr.Code() == sqlite3.SQLITE_CONSTRAINT_UNIQUE {
				return fmt.Errorf("name %s already exists", name)
			}
			return fmt.Errorf("inserting smtp auth: %w", err)
		}
	}

	return err
}

func (db *DB) GetSMTPAuthUser(ctx context.Context, name string) (model.SMTPAuth, error) {
	res := db.read.QueryRowContext(
		ctx, `SELECT id, name, password_hash FROM smtp_auth WHERE name = ?`, name)

	var u model.SMTPAuth
	var pw []byte
	err := res.Scan(&u.ID, &u.Name, &pw)
	if err != nil {
		return model.SMTPAuth{}, err
	}
	u.SetPassword(pw)

	return u, nil
}

func (db *DB) RemoveSMTPAuthUser(ctx context.Context, name string) error {
	_, err := db.write.ExecContext(ctx, `DELETE FROM smtp_auth WHERE name = ?`, name)
	return err
}

func (db *DB) ListSMTPAuthUsers(ctx context.Context) ([]model.SMTPAuth, error) {
	rows, err := db.read.QueryContext(ctx, `SELECT id, name, password_hash FROM smtp_auth ORDER BY name`)
	if err != nil {
		return nil, err
	}
	defer func(rows *sql.Rows) {
		_ = rows.Close()
	}(rows)

	var auths []model.SMTPAuth
	for rows.Next() {
		var u model.SMTPAuth
		var pw []byte
		if err := rows.Scan(&u.ID, &u.Name, &pw); err != nil {
			return nil, err
		}
		u.SetPassword(pw)
		auths = append(auths, u)
	}

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

	return auths, nil
}