package repository

import (
	"context"
	"log"
	"lune/talentscale/internal/domain"
	"strconv"

	"github.com/google/uuid"
	"github.com/jackc/pgx/v5/pgxpool"
)

type postgresUserRepository struct {
	db *pgxpool.Pool
}

func NewPostgresUserRepository(db *pgxpool.Pool) domain.UserRepository {
	return &postgresUserRepository{db: db}
}

func (r *postgresUserRepository) Create(ctx context.Context, u *domain.User) error {
	// Defensive nullable UUID handling
	var candidateID interface{}
	if u.CandidateID != nil {
		candidateID = *u.CandidateID
	} else {
		candidateID = nil
	}

	var companyID interface{}
	if u.CompanyID != nil {
		companyID = *u.CompanyID
	} else {
		companyID = nil
	}

	log.Printf("[USER CREATE] user_id=%s candidate_id=%v email=%s",
		u.ID,
		candidateID,
		u.Email,
	)

	query := `INSERT INTO users (
		id, name, email, password, phone, 
		candidate_id, company_id, role_id, 
		verified, created_at, updated_at
	) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)`

	_, err := r.db.Exec(ctx, query,
		u.ID, u.Name, u.Email, u.Password, u.Phone,
		candidateID, companyID, u.RoleID,
		u.Verified, u.CreatedAt, u.UpdatedAt,
	)
	return err
}

func (r *postgresUserRepository) GetByEmail(ctx context.Context, email string) (*domain.User, error) {
	query := `SELECT id, name, email, password, COALESCE(phone, ''), candidate_id, company_id, role_id, verified, created_at, updated_at 
	          FROM users WHERE email = $1 AND deleted_at IS NULL `

	var u domain.User
	err := r.db.QueryRow(ctx, query, email).Scan(
		&u.ID, &u.Name, &u.Email, &u.Password, &u.Phone, &u.CandidateID, &u.CompanyID, &u.RoleID, &u.Verified, &u.CreatedAt, &u.UpdatedAt,
	)
	if err != nil {
		return nil, err
	}
	return &u, nil
}

func (r *postgresUserRepository) GetByID(ctx context.Context, id uuid.UUID, companyID uuid.UUID) (*domain.User, error) {
	query := `SELECT id, name, email, password, COALESCE(phone, ''), candidate_id, company_id, role_id, verified, created_at, updated_at 
	          FROM users WHERE id = $1 AND ($2 = '00000000-0000-0000-0000-000000000000'::uuid OR company_id = $2) AND deleted_at IS NULL`

	var u domain.User
	err := r.db.QueryRow(ctx, query, id, companyID).Scan(
		&u.ID, &u.Name, &u.Email, &u.Password, &u.Phone, &u.CandidateID, &u.CompanyID, &u.RoleID, &u.Verified, &u.CreatedAt, &u.UpdatedAt,
	)
	if err != nil {
		return nil, err
	}
	return &u, nil
}

func (r *postgresUserRepository) GetByIDGlobal(ctx context.Context, id uuid.UUID) (*domain.User, error) {
	query := `SELECT id, name, email, password, COALESCE(phone, ''), candidate_id, company_id, role_id, verified, created_at, updated_at 
	          FROM users WHERE id = $1 AND deleted_at IS NULL`

	var u domain.User
	err := r.db.QueryRow(ctx, query, id).Scan(
		&u.ID, &u.Name, &u.Email, &u.Password, &u.Phone, &u.CandidateID, &u.CompanyID, &u.RoleID, &u.Verified, &u.CreatedAt, &u.UpdatedAt,
	)
	if err != nil {
		return nil, err
	}
	return &u, nil
}

func (r *postgresUserRepository) GetAll(ctx context.Context, companyID uuid.UUID, search string, roleID uuid.UUID, limit, offset int) ([]*domain.User, int, error) {
	where := `WHERE ($1 = '00000000-0000-0000-0000-000000000000'::uuid OR company_id = $1) AND deleted_at IS NULL`
	args := []interface{}{companyID}
	argCount := 1

	if roleID != uuid.Nil {
		argCount++
		where += ` AND role_id = $` + strconv.Itoa(argCount)
		args = append(args, roleID)
	}

	if search != "" {
		argCount++
		where += ` AND (name ILIKE $` + strconv.Itoa(argCount) + ` OR email ILIKE $` + strconv.Itoa(argCount) + ` OR phone ILIKE $` + strconv.Itoa(argCount) + `)`
		args = append(args, "%"+search+"%")
	}

	var total int
	countQuery := `SELECT COUNT(*) FROM users ` + where
	err := r.db.QueryRow(ctx, countQuery, args...).Scan(&total)
	if err != nil {
		total = 0
	}

	query := `SELECT id, name, email, COALESCE(phone, ''), candidate_id, company_id, role_id, verified, created_at, updated_at 
	          FROM users ` + where + ` ORDER BY created_at DESC LIMIT $` + strconv.Itoa(argCount+1) + ` OFFSET $` + strconv.Itoa(argCount+2)

	args = append(args, limit, offset)
	rows, err := r.db.Query(ctx, query, args...)
	if err != nil {
		return nil, 0, err
	}
	defer rows.Close()

	var users []*domain.User
	for rows.Next() {
		var u domain.User
		if err := rows.Scan(&u.ID, &u.Name, &u.Email, &u.Phone, &u.CandidateID, &u.CompanyID, &u.RoleID, &u.Verified, &u.CreatedAt, &u.UpdatedAt); err != nil {
			return nil, 0, err
		}
		users = append(users, &u)
	}
	return users, total, rows.Err()
}

func (r *postgresUserRepository) Update(ctx context.Context, u *domain.User) error {
	// Defensive nullable UUID handling
	var candidateID interface{}
	if u.CandidateID != nil {
		candidateID = *u.CandidateID
	} else {
		candidateID = nil
	}

	var companyID interface{}
	if u.CompanyID != nil {
		companyID = *u.CompanyID
	} else {
		companyID = nil
	}

	query := `UPDATE users SET 
		name=$1, email=$2, phone=$3, 
		candidate_id=$4, company_id=$5, role_id=$6, 
		verified=$7, updated_at=NOW() 
	WHERE id=$8 AND deleted_at IS NULL`

	_, err := r.db.Exec(ctx, query,
		u.Name, u.Email, u.Phone,
		candidateID, companyID, u.RoleID,
		u.Verified, u.ID,
	)
	return err
}

func (r *postgresUserRepository) Delete(ctx context.Context, id uuid.UUID, companyID uuid.UUID) error {
	query := `UPDATE users SET deleted_at=NOW() WHERE id=$1 AND ($2 = '00000000-0000-0000-0000-000000000000'::uuid OR company_id = $2) AND deleted_at IS NULL`
	_, err := r.db.Exec(ctx, query, id, companyID)
	return err
}