package user

import (
	"context"

	"github.com/gocql/gocql"
	"go.uber.org/zap"

	domainuser "codeberg.org/mapleopentech/monorepo/cloud/maplepress-backend/internal/domain/user"
	"codeberg.org/mapleopentech/monorepo/cloud/maplepress-backend/internal/repository/user/models"
	"codeberg.org/mapleopentech/monorepo/cloud/maplepress-backend/pkg/logger"
)

// GetByID retrieves a user by ID from the users_by_id table
func (r *repository) GetByID(ctx context.Context, tenantID string, id string) (*domainuser.User, error) {
	r.logger.Debug("getting user by ID",
		zap.String("tenant_id", tenantID),
		zap.String("id", id))

	// EXPLICIT: We're querying the users_by_id table with tenant isolation
	var userByID models.UserByID

	query := `SELECT tenant_id, id, email, first_name, last_name, name, lexical_name, timezone, role, status,
                     phone, country, region, city, postal_code, address_line1, address_line2,
                     has_shipping_address, shipping_name, shipping_phone, shipping_country, shipping_region,
                     shipping_city, shipping_postal_code, shipping_address_line1, shipping_address_line2,
                     profile_timezone, agree_terms_of_service, agree_promotions, agree_to_tracking_across_third_party_apps_and_services,
                     password_hash_algorithm, password_hash, was_email_verified, code, code_type, code_expiry,
                     otp_enabled, otp_verified, otp_validated, otp_secret, otp_auth_url, otp_backup_code_hash, otp_backup_code_hash_algorithm,
                     created_from_ip_address, created_from_ip_timestamp, created_by_user_id, created_by_name,
                     modified_from_ip_address, modified_from_ip_timestamp, modified_by_user_id, modified_at, modified_by_name, last_login_at,
                     created_at, updated_at
              FROM users_by_id
              WHERE tenant_id = ? AND id = ?`

	err := r.session.Query(query, tenantID, id).
		Consistency(gocql.Quorum).
		Scan(&userByID.TenantID, &userByID.ID, &userByID.Email, &userByID.FirstName, &userByID.LastName,
			&userByID.Name, &userByID.LexicalName, &userByID.Timezone, &userByID.Role, &userByID.Status,
			&userByID.Phone, &userByID.Country, &userByID.Region, &userByID.City, &userByID.PostalCode,
			&userByID.AddressLine1, &userByID.AddressLine2, &userByID.HasShippingAddress, &userByID.ShippingName,
			&userByID.ShippingPhone, &userByID.ShippingCountry, &userByID.ShippingRegion, &userByID.ShippingCity,
			&userByID.ShippingPostalCode, &userByID.ShippingAddressLine1, &userByID.ShippingAddressLine2,
			&userByID.ProfileTimezone, &userByID.AgreeTermsOfService, &userByID.AgreePromotions, &userByID.AgreeToTrackingAcrossThirdPartyAppsAndServices,
			&userByID.PasswordHashAlgorithm, &userByID.PasswordHash, &userByID.WasEmailVerified, &userByID.Code,
			&userByID.CodeType, &userByID.CodeExpiry, &userByID.OTPEnabled, &userByID.OTPVerified, &userByID.OTPValidated,
			&userByID.OTPSecret, &userByID.OTPAuthURL, &userByID.OTPBackupCodeHash, &userByID.OTPBackupCodeHashAlgorithm,
			&userByID.CreatedFromIPAddress, &userByID.CreatedFromIPTimestamp, &userByID.CreatedByUserID, &userByID.CreatedByName,
			&userByID.ModifiedFromIPAddress, &userByID.ModifiedFromIPTimestamp, &userByID.ModifiedByUserID, &userByID.ModifiedAt, &userByID.ModifiedByName, &userByID.LastLoginAt,
			&userByID.CreatedAt, &userByID.UpdatedAt)

	if err != nil {
		if err == gocql.ErrNotFound {
			return nil, domainuser.ErrUserNotFound
		}
		r.logger.Error("failed to get user by ID", zap.Error(err))
		return nil, err
	}

	// Convert table model to domain entity
	return userByID.ToUser(), nil
}

// GetByEmail retrieves a user by email from the users_by_email table
func (r *repository) GetByEmail(ctx context.Context, tenantID string, email string) (*domainuser.User, error) {
	// CWE-532: Use redacted email for logging
	r.logger.Debug("getting user by email",
		zap.String("tenant_id", tenantID),
		logger.EmailHash(email),
		logger.SafeEmail("email_redacted", email))

	// EXPLICIT: We're querying the users_by_email table with tenant isolation
	var userByEmail models.UserByEmail

	query := `SELECT tenant_id, email, id, first_name, last_name, name, lexical_name, timezone, role, status,
                     phone, country, region, city, postal_code, address_line1, address_line2,
                     has_shipping_address, shipping_name, shipping_phone, shipping_country, shipping_region,
                     shipping_city, shipping_postal_code, shipping_address_line1, shipping_address_line2,
                     profile_timezone, agree_terms_of_service, agree_promotions, agree_to_tracking_across_third_party_apps_and_services,
                     password_hash_algorithm, password_hash, was_email_verified, code, code_type, code_expiry,
                     otp_enabled, otp_verified, otp_validated, otp_secret, otp_auth_url, otp_backup_code_hash, otp_backup_code_hash_algorithm,
                     created_from_ip_address, created_from_ip_timestamp, created_by_user_id, created_by_name,
                     modified_from_ip_address, modified_from_ip_timestamp, modified_by_user_id, modified_at, modified_by_name, last_login_at,
                     created_at, updated_at
              FROM users_by_email
              WHERE tenant_id = ? AND email = ?`

	err := r.session.Query(query, tenantID, email).
		Consistency(gocql.Quorum).
		Scan(&userByEmail.TenantID, &userByEmail.Email, &userByEmail.ID, &userByEmail.FirstName, &userByEmail.LastName,
			&userByEmail.Name, &userByEmail.LexicalName, &userByEmail.Timezone, &userByEmail.Role, &userByEmail.Status,
			&userByEmail.Phone, &userByEmail.Country, &userByEmail.Region, &userByEmail.City, &userByEmail.PostalCode,
			&userByEmail.AddressLine1, &userByEmail.AddressLine2, &userByEmail.HasShippingAddress, &userByEmail.ShippingName,
			&userByEmail.ShippingPhone, &userByEmail.ShippingCountry, &userByEmail.ShippingRegion, &userByEmail.ShippingCity,
			&userByEmail.ShippingPostalCode, &userByEmail.ShippingAddressLine1, &userByEmail.ShippingAddressLine2,
			&userByEmail.ProfileTimezone, &userByEmail.AgreeTermsOfService, &userByEmail.AgreePromotions, &userByEmail.AgreeToTrackingAcrossThirdPartyAppsAndServices,
			&userByEmail.PasswordHashAlgorithm, &userByEmail.PasswordHash, &userByEmail.WasEmailVerified, &userByEmail.Code,
			&userByEmail.CodeType, &userByEmail.CodeExpiry, &userByEmail.OTPEnabled, &userByEmail.OTPVerified, &userByEmail.OTPValidated,
			&userByEmail.OTPSecret, &userByEmail.OTPAuthURL, &userByEmail.OTPBackupCodeHash, &userByEmail.OTPBackupCodeHashAlgorithm,
			&userByEmail.CreatedFromIPAddress, &userByEmail.CreatedFromIPTimestamp, &userByEmail.CreatedByUserID, &userByEmail.CreatedByName,
			&userByEmail.ModifiedFromIPAddress, &userByEmail.ModifiedFromIPTimestamp, &userByEmail.ModifiedByUserID, &userByEmail.ModifiedAt, &userByEmail.ModifiedByName, &userByEmail.LastLoginAt,
			&userByEmail.CreatedAt, &userByEmail.UpdatedAt)

	if err != nil {
		if err == gocql.ErrNotFound {
			return nil, domainuser.ErrUserNotFound
		}
		r.logger.Error("failed to get user by email", zap.Error(err))
		return nil, err
	}

	// Convert table model to domain entity
	return userByEmail.ToUser(), nil
}

// GetByEmailGlobal retrieves a user by email across all tenants (for login)
// WARNING: This bypasses tenant isolation and should ONLY be used for authentication
func (r *repository) GetByEmailGlobal(ctx context.Context, email string) (*domainuser.User, error) {
	// CWE-532: Use redacted email for logging
	r.logger.Debug("getting user by email globally (no tenant filter)",
		logger.EmailHash(email),
		logger.SafeEmail("email_redacted", email))

	// EXPLICIT: Querying users_by_email WITHOUT tenant_id filter
	// This allows login with just email/password, finding the user's tenant automatically
	var userByEmail models.UserByEmail

	query := `SELECT tenant_id, email, id, first_name, last_name, name, lexical_name, timezone, role, status,
                     phone, country, region, city, postal_code, address_line1, address_line2,
                     has_shipping_address, shipping_name, shipping_phone, shipping_country, shipping_region,
                     shipping_city, shipping_postal_code, shipping_address_line1, shipping_address_line2,
                     profile_timezone, agree_terms_of_service, agree_promotions, agree_to_tracking_across_third_party_apps_and_services,
                     password_hash_algorithm, password_hash, was_email_verified, code, code_type, code_expiry,
                     otp_enabled, otp_verified, otp_validated, otp_secret, otp_auth_url, otp_backup_code_hash, otp_backup_code_hash_algorithm,
                     created_from_ip_address, created_from_ip_timestamp, created_by_user_id, created_by_name,
                     modified_from_ip_address, modified_from_ip_timestamp, modified_by_user_id, modified_at, modified_by_name, last_login_at,
                     created_at, updated_at
              FROM users_by_email
              WHERE email = ?
              LIMIT 1
              ALLOW FILTERING`

	err := r.session.Query(query, email).
		Consistency(gocql.Quorum).
		Scan(&userByEmail.TenantID, &userByEmail.Email, &userByEmail.ID, &userByEmail.FirstName, &userByEmail.LastName,
			&userByEmail.Name, &userByEmail.LexicalName, &userByEmail.Timezone, &userByEmail.Role, &userByEmail.Status,
			&userByEmail.Phone, &userByEmail.Country, &userByEmail.Region, &userByEmail.City, &userByEmail.PostalCode,
			&userByEmail.AddressLine1, &userByEmail.AddressLine2, &userByEmail.HasShippingAddress, &userByEmail.ShippingName,
			&userByEmail.ShippingPhone, &userByEmail.ShippingCountry, &userByEmail.ShippingRegion, &userByEmail.ShippingCity,
			&userByEmail.ShippingPostalCode, &userByEmail.ShippingAddressLine1, &userByEmail.ShippingAddressLine2,
			&userByEmail.ProfileTimezone, &userByEmail.AgreeTermsOfService, &userByEmail.AgreePromotions, &userByEmail.AgreeToTrackingAcrossThirdPartyAppsAndServices,
			&userByEmail.PasswordHashAlgorithm, &userByEmail.PasswordHash, &userByEmail.WasEmailVerified, &userByEmail.Code,
			&userByEmail.CodeType, &userByEmail.CodeExpiry, &userByEmail.OTPEnabled, &userByEmail.OTPVerified, &userByEmail.OTPValidated,
			&userByEmail.OTPSecret, &userByEmail.OTPAuthURL, &userByEmail.OTPBackupCodeHash, &userByEmail.OTPBackupCodeHashAlgorithm,
			&userByEmail.CreatedFromIPAddress, &userByEmail.CreatedFromIPTimestamp, &userByEmail.CreatedByUserID, &userByEmail.CreatedByName,
			&userByEmail.ModifiedFromIPAddress, &userByEmail.ModifiedFromIPTimestamp, &userByEmail.ModifiedByUserID, &userByEmail.ModifiedAt, &userByEmail.ModifiedByName, &userByEmail.LastLoginAt,
			&userByEmail.CreatedAt, &userByEmail.UpdatedAt)

	if err != nil {
		if err == gocql.ErrNotFound {
			return nil, domainuser.ErrUserNotFound
		}
		r.logger.Error("failed to get user by email globally", zap.Error(err))
		return nil, err
	}

	// CWE-532: Use redacted email for logging
	r.logger.Info("found user by email globally",
		logger.EmailHash(email),
		logger.SafeEmail("email_redacted", email),
		zap.String("tenant_id", userByEmail.TenantID))

	// Convert table model to domain entity
	return userByEmail.ToUser(), nil
}

// ListByDate lists users created within a date range from the users_by_date table
func (r *repository) ListByDate(ctx context.Context, tenantID string, startDate, endDate string, limit int) ([]*domainuser.User, error) {
	r.logger.Debug("listing users by date range",
		zap.String("tenant_id", tenantID),
		zap.String("start_date", startDate),
		zap.String("end_date", endDate),
		zap.Int("limit", limit))

	// EXPLICIT: We're querying the users_by_date table
	query := `SELECT tenant_id, created_date, id, email, first_name, last_name, name, lexical_name, timezone, role, status,
                     phone, country, region, city, postal_code, address_line1, address_line2,
                     has_shipping_address, shipping_name, shipping_phone, shipping_country, shipping_region,
                     shipping_city, shipping_postal_code, shipping_address_line1, shipping_address_line2,
                     profile_timezone, agree_terms_of_service, agree_promotions, agree_to_tracking_across_third_party_apps_and_services,
                     password_hash_algorithm, password_hash, was_email_verified, code, code_type, code_expiry,
                     otp_enabled, otp_verified, otp_validated, otp_secret, otp_auth_url, otp_backup_code_hash, otp_backup_code_hash_algorithm,
                     created_from_ip_address, created_from_ip_timestamp, created_by_user_id, created_by_name,
                     modified_from_ip_address, modified_from_ip_timestamp, modified_by_user_id, modified_at, modified_by_name, last_login_at,
                     created_at, updated_at
              FROM users_by_date
              WHERE tenant_id = ? AND created_date >= ? AND created_date <= ?
              LIMIT ?`

	iter := r.session.Query(query, tenantID, startDate, endDate, limit).
		Consistency(gocql.Quorum).
		Iter()

	var users []*domainuser.User
	var userByDate models.UserByDate

	for iter.Scan(&userByDate.TenantID, &userByDate.CreatedDate, &userByDate.ID, &userByDate.Email,
		&userByDate.FirstName, &userByDate.LastName, &userByDate.Name, &userByDate.LexicalName, &userByDate.Timezone,
		&userByDate.Role, &userByDate.Status, &userByDate.Phone, &userByDate.Country, &userByDate.Region,
		&userByDate.City, &userByDate.PostalCode, &userByDate.AddressLine1, &userByDate.AddressLine2,
		&userByDate.HasShippingAddress, &userByDate.ShippingName, &userByDate.ShippingPhone, &userByDate.ShippingCountry,
		&userByDate.ShippingRegion, &userByDate.ShippingCity, &userByDate.ShippingPostalCode, &userByDate.ShippingAddressLine1,
		&userByDate.ShippingAddressLine2, &userByDate.ProfileTimezone, &userByDate.AgreeTermsOfService, &userByDate.AgreePromotions,
		&userByDate.AgreeToTrackingAcrossThirdPartyAppsAndServices, &userByDate.PasswordHashAlgorithm, &userByDate.PasswordHash,
		&userByDate.WasEmailVerified, &userByDate.Code, &userByDate.CodeType, &userByDate.CodeExpiry, &userByDate.OTPEnabled,
		&userByDate.OTPVerified, &userByDate.OTPValidated, &userByDate.OTPSecret, &userByDate.OTPAuthURL,
		&userByDate.OTPBackupCodeHash, &userByDate.OTPBackupCodeHashAlgorithm, &userByDate.CreatedFromIPAddress,
		&userByDate.CreatedFromIPTimestamp, &userByDate.CreatedByUserID, &userByDate.CreatedByName, &userByDate.ModifiedFromIPAddress,
		&userByDate.ModifiedFromIPTimestamp, &userByDate.ModifiedByUserID, &userByDate.ModifiedAt, &userByDate.ModifiedByName, &userByDate.LastLoginAt,
		&userByDate.CreatedAt, &userByDate.UpdatedAt) {
		users = append(users, userByDate.ToUser())
	}

	if err := iter.Close(); err != nil {
		r.logger.Error("failed to list users by date", zap.Error(err))
		return nil, err
	}

	return users, nil
}