package store

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

	"github.com/google/uuid"
	"github.com/huandu/go-sqlbuilder"
)

// User represents a user in the database
type User struct {
	ID           uuid.UUID
	Email        string
	PasswordHash string
	Role         string
	CreatedAt    time.Time
	UpdatedAt    time.Time
}

// UserStore handles database operations for users
type UserStore struct {
	readDB  *sql.DB
	writeDB *sql.DB
}

// NewUserStore creates a new UserStore with read/write database separation
func NewUserStore(readDB, writeDB *sql.DB) *UserStore {
	return &UserStore{
		readDB:  readDB,
		writeDB: writeDB,
	}
}

// userColumns lists the columns selected in user queries
var userColumns = []string{"id", "email", "password_hash", "role", "created_at", "updated_at"}

// scanUser scans a single user row into a User struct
func scanUser(scanner interface{ Scan(dest ...any) error }) (*User, error) {
	var user User
	var idStr string
	var createdAt, updatedAt string

	if err := scanner.Scan(&idStr, &user.Email, &user.PasswordHash, &user.Role, &createdAt, &updatedAt); err != nil {
		if err == sql.ErrNoRows {
			return nil, fmt.Errorf("user not found")
		}
		return nil, fmt.Errorf("failed to scan user: %w", err)
	}

	var err error
	user.ID, err = uuid.Parse(idStr)
	if err != nil {
		return nil, fmt.Errorf("failed to parse user ID: %w", err)
	}

	if createdAt != "" {
		user.CreatedAt, _ = time.Parse("2006-01-02 15:04:05", createdAt)
	}
	if updatedAt != "" {
		user.UpdatedAt, _ = time.Parse("2006-01-02 15:04:05", updatedAt)
	}

	return &user, nil
}

// FindByEmail finds a user by email
func (s *UserStore) FindByEmail(email string) (*User, error) {
	sb := sqlbuilder.NewSelectBuilder()
	sb.Select(userColumns...)
	sb.From("users")
	sb.Where(sb.Equal("email", email))
	sb.Limit(1)

	query, args := sb.Build()
	return scanUser(s.readDB.QueryRow(query, args...))
}

// FindByID finds a user by ID
func (s *UserStore) FindByID(id uuid.UUID) (*User, error) {
	sb := sqlbuilder.NewSelectBuilder()
	sb.Select(userColumns...)
	sb.From("users")
	sb.Where(sb.Equal("id", id.String()))
	sb.Limit(1)

	query, args := sb.Build()
	return scanUser(s.readDB.QueryRow(query, args...))
}

// List returns all users ordered by created_at DESC
func (s *UserStore) List() ([]*User, error) {
	sb := sqlbuilder.NewSelectBuilder()
	sb.Select(userColumns...)
	sb.From("users")
	sb.OrderByDesc("created_at")

	query, args := sb.Build()
	rows, err := s.readDB.Query(query, args...)
	if err != nil {
		return nil, fmt.Errorf("failed to query users: %w", err)
	}
	defer rows.Close()

	var users []*User
	for rows.Next() {
		user, err := scanUser(rows)
		if err != nil {
			return nil, err
		}
		users = append(users, user)
	}

	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("error iterating users: %w", err)
	}

	return users, nil
}

// Update updates an existing user's email, password_hash, role, and updated_at
func (s *UserStore) Update(user *User) error {
	ub := sqlbuilder.NewUpdateBuilder()
	ub.Update("users")
	ub.Set(
		ub.Assign("email", user.Email),
		ub.Assign("password_hash", user.PasswordHash),
		ub.Assign("role", user.Role),
		ub.Assign("updated_at", user.UpdatedAt),
	)
	ub.Where(ub.Equal("id", user.ID.String()))

	query, args := ub.Build()
	result, err := s.writeDB.Exec(query, args...)
	if err != nil {
		return fmt.Errorf("failed to update user: %w", err)
	}

	rowsAffected, err := result.RowsAffected()
	if err != nil {
		return fmt.Errorf("failed to get rows affected: %w", err)
	}
	if rowsAffected == 0 {
		return fmt.Errorf("user not found")
	}

	return nil
}

// Delete deletes a user by ID
func (s *UserStore) Delete(id uuid.UUID) error {
	db := sqlbuilder.NewDeleteBuilder()
	db.DeleteFrom("users")
	db.Where(db.Equal("id", id.String()))

	query, args := db.Build()
	result, err := s.writeDB.Exec(query, args...)
	if err != nil {
		return fmt.Errorf("failed to delete user: %w", err)
	}

	rowsAffected, err := result.RowsAffected()
	if err != nil {
		return fmt.Errorf("failed to get rows affected: %w", err)
	}
	if rowsAffected == 0 {
		return fmt.Errorf("user not found")
	}

	return nil
}

// Create creates a new user
func (s *UserStore) Create(user *User) error {
	// Default role to "user" if not set (required by DB CHECK constraint)
	role := user.Role
	if role == "" {
		role = "user"
	}

	ib := sqlbuilder.NewInsertBuilder()
	ib.InsertInto("users")
	ib.Cols(userColumns...)
	ib.Values(user.ID.String(), user.Email, user.PasswordHash, role, user.CreatedAt, user.UpdatedAt)

	query, args := ib.Build()
	_, err := s.writeDB.Exec(query, args...)
	if err != nil {
		return fmt.Errorf("failed to insert user: %w", err)
	}

	return nil
}