package mysql

import (
	"context"
	"errors"
	"strings"
	"time"

	"gorm.io/gorm"

	"wucher/internal/domain/auth"
	"wucher/internal/domain/base"
	"wucher/internal/domain/contact"
	"wucher/internal/shared/pkg/apperrors"
	"wucher/internal/shared/pkg/sortkey"
)

type ContactRepository struct {
	db                           *gorm.DB
	hasBaseContactRolesTable     bool
	baseContactRolesHasContactID bool
	baseContactRolesHasDeletedAt bool
}

const contactBaseRoleDUL = "dul" 

const microsoftIdentityProvider = "microsoft"

var contactProfileTables = []string{
	"pilot_profiles",
	"doctor_profiles",
	"air_rescuer_profiles",
	"technician_profiles",
	"flight_assistant_profiles",
	"staff_profiles",
}

func NewContactRepository(db *gorm.DB) *ContactRepository {
	schema := newSchemaCache(db)
	hasBaseContactRolesTable := schema.HasTable("base_contact_roles")
	return &ContactRepository{
		db:                           db,
		hasBaseContactRolesTable:     hasBaseContactRolesTable,
		baseContactRolesHasContactID: hasBaseContactRolesTable && schema.HasColumn("base_contact_roles", "contact_id"),
		baseContactRolesHasDeletedAt: hasBaseContactRolesTable && schema.HasColumn("base_contact_roles", "deleted_at"),
	}
}

func copyByteSlices(in [][]byte) [][]byte {
	out := make([][]byte, 0, len(in))
	for i := range in {
		if len(in[i]) == 0 {
			continue
		}
		out = append(out, append([]byte(nil), in[i]...))
	}
	return out
}

func (r *ContactRepository) ListContacts(ctx context.Context, filter contact.ListFilter) ([]contact.ContactListItem, int64, error) {
	items := make([]contact.ContactListItem, 0)
	var total int64

	base := r.baseListQuery(ctx)
	base = applyContactFilters(base, filter)

	if err := base.Count(&total).Error; err != nil {
		return nil, 0, err
	}

	query := base.Select(contactDetailSelectSQL())
	if strings.TrimSpace(filter.Sort) != "" {
		query = query.Order(filter.Sort)
	} else {
		for _, clause := range sortkey.ActivePositiveSortClauses("users", "is_active", "sortkey", "first_name", false) {
			query = query.Order(clause)
		}
	}

	if filter.Limit > 0 {
		query = query.Limit(filter.Limit).Offset(filter.Offset)
	}

	if err := query.Scan(&items).Error; err != nil {
		return nil, 0, err
	}
	return items, total, nil
}

func (r *ContactRepository) GetContactByID(ctx context.Context, userID []byte) (*contact.ContactListItem, error) {
	item := &contact.ContactListItem{}
	q := r.baseListQuery(ctx).Where("users.id = ?", userID)
	if err := q.Select(contactDetailSelectSQL()).Take(item).Error; err != nil {
		if errors.Is(err, gorm.ErrRecordNotFound) {
			return nil, nil
		}
		return nil, err
	}
	return item, nil
}

func (r *ContactRepository) IsActorAdmin(ctx context.Context) (bool, error) {
	return r.isActorAdmin(ctx, r.db.WithContext(ctx))
}

func (r *ContactRepository) ResolveRoleCodeByID(ctx context.Context, roleID []byte) (string, error) {
	_, roleCode, err := r.resolveRoleByID(r.db.WithContext(ctx), roleID)
	if err != nil {
		return "", err
	}
	return roleCode, nil
}

func (r *ContactRepository) CreateContact(ctx context.Context, in contact.CreateInput) ([]byte, error) {
	var outID []byte
	err := r.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		if in.User.SSOEmail != nil {
			isAdmin, err := r.isActorAdmin(ctx, tx)
			if err != nil {
				return err
			}
			if !isAdmin {
				return errors.New("only admin can set sso_email")
			}
		}
		assignedRoleIDs := make([][]byte, 0, len(in.RoleIDs))
		assignedRoleCodes := make([]string, 0, len(in.RoleIDs))
		switch {
		case len(in.RoleIDs) > 0:
			for i := range in.RoleIDs {
				resolvedID, resolvedCode, err := r.resolveRoleByID(tx, in.RoleIDs[i])
				if err != nil {
					return err
				}
				assignedRoleIDs = append(assignedRoleIDs, resolvedID)
				assignedRoleCodes = append(assignedRoleCodes, resolvedCode)
			}
		case len(in.RoleID) == 16:
			resolvedID, resolvedCode, err := r.resolveRoleByID(tx, in.RoleID)
			if err != nil {
				return err
			}
			assignedRoleIDs = append(assignedRoleIDs, resolvedID)
			assignedRoleCodes = append(assignedRoleCodes, resolvedCode)
		case len(in.Profiles) > 0:
			seen := make(map[string]struct{}, len(in.Profiles))
			for i := range in.Profiles {
				resolvedID, resolvedCode, err := r.resolveRoleID(tx, in.Profiles[i].RoleCode)
				if err != nil {
					return err
				}
				key := string(resolvedID)
				if _, ok := seen[key]; ok {
					continue
				}
				seen[key] = struct{}{}
				assignedRoleIDs = append(assignedRoleIDs, resolvedID)
				assignedRoleCodes = append(assignedRoleCodes, resolvedCode)
			}
		default:
			// Role assignment is optional for contact creation.
		}
		var roleID []byte
		normalizedRoleCode := ""
		if len(assignedRoleIDs) > 0 {
			roleID = assignedRoleIDs[0]
			normalizedRoleCode = assignedRoleCodes[0]
		}
		var adminRoleIDs [][]byte
		if in.User.IsAdmin != nil && *in.User.IsAdmin {
			adminRoleID, _, err := r.resolveRoleID(tx, "admin")
			if err != nil {
				return err
			}
			adminRoleIDs = append(adminRoleIDs, adminRoleID)
		}

		email := strings.TrimSpace(valueOrEmpty(in.User.Email))
		ssoEmail := normalizedEmailPtr(in.User.SSOEmail)
		firstName := strings.TrimSpace(valueOrEmpty(in.User.FirstName))
		lastName := strings.TrimSpace(valueOrEmpty(in.User.LastName))
		if email == "" || firstName == "" || lastName == "" {
			return errors.New("email, first_name, and last_name are required")
		}
		if err := ensureUniqueSSOEmailTx(tx, ssoEmail, nil); err != nil {
			return err
		}

		tz := "UTC"
		if in.User.Timezone != nil && strings.TrimSpace(*in.User.Timezone) != "" {
			tz = strings.TrimSpace(*in.User.Timezone)
		}
		isActive := true
		if in.User.IsActive != nil {
			isActive = *in.User.IsActive
		}
		username := trimmedPtr(in.User.Username)
		if username != nil {
			exists, err := usernameExistsTx(tx, *username, nil)
			if err != nil {
				return err
			}
			if exists {
				return contact.ErrUsernameAlreadyExists
			}
		}

		u := &auth.User{
			Email:       email,
			SSOEmail:    ssoEmail,
			Username:    username,
			FirstName:   firstName,
			LastName:    lastName,
			MobilePhone: strings.TrimSpace(valueOrEmpty(in.User.MobilePhone)),
			Timezone:    tz,
			SortKey:     in.User.SortKey,
			RoleID:      roleID,
			RoleIDs:     append(copyByteSlices(assignedRoleIDs), adminRoleIDs...),
			IsActive:    isActive,
		}
		if in.User.ProfileAttachmentID != nil {
			u.ProfileAttachmentID = append([]byte(nil), (*in.User.ProfileAttachmentID)...)
		}
		if actor := actorUserIDFromContext(ctx); len(actor) > 0 {
			u.CreatedBy = actor
			u.UpdatedBy = actor
		}
		if err := tx.Create(u).Error; err != nil {
			return err
		}
		if err := tx.Model(&auth.User{}).Where("id = ?", u.ID).UpdateColumn("is_active", isActive).Error; err != nil {
			return err
		}
		if err := replaceUserRolesTx(ctx, tx, u.ID, u.RoleID, u.RoleIDs); err != nil {
			return err
		}
		if len(in.Profiles) > 0 {
			for i := range in.Profiles {
				if err := r.upsertProfileTx(ctx, tx, u.ID, in.Profiles[i].RoleCode, in.Profiles[i].Patch, true); err != nil {
					return err
				}
			}
		} else if normalizedRoleCode != "" && hasAnyProfilePatch(in.Profile) {
			if err := r.upsertProfileTx(ctx, tx, u.ID, normalizedRoleCode, in.Profile, true); err != nil {
				return err
			}
		}
		outID = append([]byte(nil), u.ID...)
		return nil
	})
	if err != nil {
		return nil, err
	}
	return outID, nil
}

func (r *ContactRepository) UpdateContact(ctx context.Context, in contact.UpdateInput) error {
	if len(in.UserID) != 16 {
		return errors.New("invalid user id")
	}
	return r.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		exists, err := r.userExistsTx(tx, in.UserID)
		if err != nil {
			return err
		}
		if !exists {
			return gorm.ErrRecordNotFound
		}

		updates := map[string]any{}
		ssoEmailUpdated := false
		ssoEmailCleared := false
		if in.User.SSOEmail != nil {
			isAdmin, err := r.isActorAdmin(ctx, tx)
			if err != nil {
				return err
			}
			if !isAdmin {
				return errors.New("only admin can update sso_email")
			}
			normalizedSSO := normalizedEmailPtr(in.User.SSOEmail)
			if err := ensureUniqueSSOEmailTx(tx, normalizedSSO, in.UserID); err != nil {
				return err
			}
			updates["sso_email"] = normalizedSSO
			ssoEmailUpdated = true
			ssoEmailCleared = normalizedSSO == nil
		}
		if in.User.Email != nil {
			updates["email"] = strings.TrimSpace(*in.User.Email)
		}
		if in.User.Username != nil {
			username := trimmedPtr(in.User.Username)
			if username != nil {
				exists, err := usernameExistsTx(tx, *username, in.UserID)
				if err != nil {
					return err
				}
				if exists {
					return contact.ErrUsernameAlreadyExists
				}
			}
			updates["username"] = username
		}
		if in.User.FirstName != nil {
			updates["first_name"] = strings.TrimSpace(*in.User.FirstName)
		}
		if in.User.LastName != nil {
			updates["last_name"] = strings.TrimSpace(*in.User.LastName)
		}
		if in.User.MobilePhone != nil {
			updates["mobile_phone"] = strings.TrimSpace(*in.User.MobilePhone)
		}
		if in.User.Timezone != nil {
			updates["timezone"] = strings.TrimSpace(*in.User.Timezone)
		}
		if in.User.SortKeySet {
			if in.User.SortKey == nil {
				updates["sortkey"] = nil
			} else {
				updates["sortkey"] = *in.User.SortKey
			}
		}
		if in.User.IsActive != nil {
			updates["is_active"] = *in.User.IsActive
		}
		if in.User.ProfileAttachmentID != nil {
			updates["profile_attachment_id"] = *in.User.ProfileAttachmentID
		}
		if actor := actorUserIDFromContext(ctx); len(actor) > 0 {
			updates["updated_by"] = actor
		}
		if len(updates) > 0 {
			if err := tx.Table("users").Where("id = ?", in.UserID).Updates(updates).Error; err != nil {
				return err
			}
		}
		if ssoEmailUpdated && ssoEmailCleared {
			if err := tx.Where("user_id = ? AND provider = ?", in.UserID, microsoftIdentityProvider).
				Delete(&auth.UserIdentity{}).Error; err != nil {
				return err
			}
		}
		if in.UpdateRoles {
			if len(in.RoleID) != 0 && len(in.RoleID) != 16 {
				return errors.New("invalid role_id")
			}
			if err := tx.Table("users").Where("id = ?", in.UserID).Update("role_id", in.RoleID).Error; err != nil {
				return err
			}
			if err := replaceUserRolesTx(ctx, tx, in.UserID, in.RoleID, in.RoleIDs); err != nil {
				return err
			}
		}
		if in.User.IsAdmin != nil {
			if err := r.syncAdminRoleTx(ctx, tx, in.UserID, *in.User.IsAdmin); err != nil {
				return err
			}
		}

		if len(in.Profiles) > 0 {
			for i := range in.Profiles {
				if err := r.upsertProfileTx(ctx, tx, in.UserID, in.Profiles[i].RoleCode, in.Profiles[i].Patch, false); err != nil {
					return err
				}
			}
			if err := r.touchUserUpdatedByTx(ctx, tx, in.UserID); err != nil {
				return err
			}
		} else if hasAnyProfilePatch(in.Profile) {
			roleCode := strings.TrimSpace(in.TargetProfileRole)
			if roleCode == "" {
				resolvedRoleCode, err := r.roleCodeForUser(tx, in.UserID)
				if err != nil {
					if errors.Is(err, gorm.ErrRecordNotFound) {
						// Contact can now have no role. If profile payload still comes from FE
						// while no role is assigned, skip profile upsert and keep user update successful.
						return nil
					}
					return err
				}
				roleCode = resolvedRoleCode
			}
			if err := r.upsertProfileTx(ctx, tx, in.UserID, roleCode, in.Profile, false); err != nil {
				return err
			}
			if err := r.touchUserUpdatedByTx(ctx, tx, in.UserID); err != nil {
				return err
			}
		}
		return nil
	})
}

func (r *ContactRepository) DeleteContact(ctx context.Context, userID []byte) error {
	return r.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		exists, err := r.userExistsTx(tx, userID)
		if err != nil {
			return err
		}
		if !exists {
			return gorm.ErrRecordNotFound
		}
		if r.hasBaseContactRolesTable && r.baseContactRolesHasContactID {
			query := tx.Table("base_contact_roles").Where("contact_id = ?", userID)
			if r.baseContactRolesHasDeletedAt {
				query = query.Where("deleted_at IS NULL")
			}
			var count int64
			if err := query.Count(&count).Error; err != nil {
				return err
			}
			if count > 0 {
				return apperrors.NewDeleteConflictError("bases")
			}
		}
		if err := r.deleteContactProfilesTx(tx, userID); err != nil {
			return err
		}
		if err := tx.Where("user_id = ?", userID).Delete(&auth.UserRole{}).Error; err != nil {
			return err
		}
		return tx.Where("id = ?", userID).Delete(&auth.User{}).Error
	})
}

func (r *ContactRepository) UpdateContactStatus(ctx context.Context, userID []byte, isActive bool) error {
	return r.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		exists, err := r.userExistsTx(tx, userID)
		if err != nil {
			return err
		}
		if !exists {
			return gorm.ErrRecordNotFound
		}
		updates := map[string]any{"is_active": isActive}
		if actor := actorUserIDFromContext(ctx); len(actor) > 0 {
			updates["updated_by"] = actor
		}
		return tx.Table("users").Where("id = ?", userID).Updates(updates).Error
	})
}

func (r *ContactRepository) UpdateContactRole(ctx context.Context, userID []byte, roleCode string) error {
	return r.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		exists, err := r.userExistsTx(tx, userID)
		if err != nil {
			return err
		}
		if !exists {
			return gorm.ErrRecordNotFound
		}

		currentRoleID, err := r.primaryRoleIDForUserTx(tx, userID)
		if err != nil {
			return err
		}
		roleID, normalizedRoleCode, err := r.resolveRoleID(tx, roleCode)
		if err != nil {
			return err
		}
		userUpdates := map[string]any{"role_id": roleID}
		if actor := actorUserIDFromContext(ctx); len(actor) > 0 {
			userUpdates["updated_by"] = actor
		}
		if err := tx.Table("users").Where("id = ?", userID).Updates(userUpdates).Error; err != nil {
			return err
		}
		if err := replacePrimaryUserRoleTx(ctx, tx, userID, currentRoleID, roleID); err != nil {
			return err
		}
		// Ensure profile row exists for the new role.
		return r.upsertProfileTx(ctx, tx, userID, normalizedRoleCode, contact.ProfilePatch{}, false)
	})
}

func (r *ContactRepository) userExistsTx(tx *gorm.DB, userID []byte) (bool, error) {
	var count int64
	if err := tx.Table("users").Where("id = ?", userID).Limit(1).Count(&count).Error; err != nil {
		return false, err
	}
	return count > 0, nil
}

func (r *ContactRepository) deleteContactProfilesTx(tx *gorm.DB, userID []byte) error {
	for i := range contactProfileTables {
		if err := tx.Exec("DELETE FROM "+contactProfileTables[i]+" WHERE user_id = ?", userID).Error; err != nil {
			return err
		}
	}
	return nil
}

func (r *ContactRepository) UpdatePilotCategory(ctx context.Context, userID []byte, category string) error {
	category = strings.ToLower(strings.TrimSpace(category))
	if category == "" {
		return errors.New("pilot_category is required")
	}
	patch := contact.ProfilePatch{PilotCategory: &category}
	return r.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		if err := r.upsertProfileTx(ctx, tx, userID, auth.RoleCodePilot, patch, false); err != nil {
			return err
		}
		return r.touchUserUpdatedByTx(ctx, tx, userID)
	})
}

func (r *ContactRepository) UpdateLicense(ctx context.Context, userID []byte, licenseNo *string) error {
	return r.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		roleCode, err := r.roleCodeForUser(tx, userID)
		if err != nil {
			return err
		}
		patch := contact.ProfilePatch{LicenseNo: licenseNo}
		if err := r.upsertProfileTx(ctx, tx, userID, roleCode, patch, false); err != nil {
			return err
		}
		return r.touchUserUpdatedByTx(ctx, tx, userID)
	})
}

func (r *ContactRepository) UpdateChiefFlags(ctx context.Context, userID []byte, patch contact.ProfilePatch) error {
	return r.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		roleCode, err := r.roleCodeForUser(tx, userID)
		if err != nil {
			return err
		}
		if err := r.upsertProfileTx(ctx, tx, userID, roleCode, patch, false); err != nil {
			return err
		}
		return r.touchUserUpdatedByTx(ctx, tx, userID)
	})
}

func (r *ContactRepository) UpdateResponsibleComplaints(ctx context.Context, userID []byte, value bool) error {
	return r.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		patch := contact.ProfilePatch{IsResponsibleComplaints: &value}
		if err := r.upsertProfileTx(ctx, tx, userID, auth.RoleCodeTechnician, patch, false); err != nil {
			return err
		}
		return r.touchUserUpdatedByTx(ctx, tx, userID)
	})
}

func (r *ContactRepository) BulkUpdateStatus(ctx context.Context, userIDs [][]byte, isActive bool) (int64, error) {
	if len(userIDs) == 0 {
		return 0, nil
	}
	updates := map[string]any{"is_active": isActive}
	if actor := actorUserIDFromContext(ctx); len(actor) > 0 {
		updates["updated_by"] = actor
	}
	res := r.db.WithContext(ctx).Table("users").Where("id IN ?", userIDs).Updates(updates)
	if res.Error != nil {
		return 0, res.Error
	}
	return res.RowsAffected, nil
}

func (r *ContactRepository) touchUserUpdatedByTx(ctx context.Context, tx *gorm.DB, userID []byte) error {
	actor := actorUserIDFromContext(ctx)
	if len(actor) == 0 {
		return nil
	}
	return tx.Table("users").Where("id = ?", userID).Update("updated_by", actor).Error
}

func (r *ContactRepository) baseListQuery(ctx context.Context) *gorm.DB {
	return r.db.WithContext(ctx).
		Table("users").
		Joins("LEFT JOIN roles ON roles.id = users.role_id").
		Joins("LEFT JOIN pilot_profiles pp ON pp.user_id = users.id").
		Joins("LEFT JOIN doctor_profiles dp ON dp.user_id = users.id").
		Joins("LEFT JOIN air_rescuer_profiles arp ON arp.user_id = users.id").
		Joins("LEFT JOIN technician_profiles tp ON tp.user_id = users.id").
		Joins("LEFT JOIN flight_assistant_profiles fap ON fap.user_id = users.id").
		Joins("LEFT JOIN staff_profiles sp ON sp.user_id = users.id")
}

func (r *ContactRepository) isActorAdmin(ctx context.Context, tx *gorm.DB) (bool, error) {
	actor := actorUserIDFromContext(ctx)
	if len(actor) != 16 {
		return false, nil
	}
	var count int64
	err := tx.Table("users AS actor_users").
		Joins("LEFT JOIN user_roles ur ON ur.user_id = actor_users.id").
		Joins("LEFT JOIN roles role_primary ON role_primary.id = actor_users.role_id").
		Joins("LEFT JOIN roles role_assigned ON role_assigned.id = ur.role_id").
		Where("actor_users.id = ?", actor).
		Where(`(
			LOWER(COALESCE(NULLIF(role_primary.code, ''), role_primary.name)) = ? OR
			LOWER(COALESCE(NULLIF(role_assigned.code, ''), role_assigned.name)) = ?
		)`, "admin", "admin").
		Count(&count).Error
	if err != nil {
		return false, err
	}
	return count > 0, nil
}

func applyContactFilters(q *gorm.DB, filter contact.ListFilter) *gorm.DB {
	role := strings.ToLower(strings.TrimSpace(filter.Role))
	if role != "" {
		q = q.Where(`(
			LOWER(COALESCE(NULLIF(roles.code, ''), roles.name)) = ?
			OR
			EXISTS (
				SELECT 1
				FROM user_roles ur_filter
				JOIN roles r_filter ON r_filter.id = ur_filter.role_id
				WHERE ur_filter.user_id = users.id
				  AND LOWER(COALESCE(NULLIF(r_filter.code, ''), r_filter.name)) = ?
			)
		)`, role, role)
	}

	pilotCategory := strings.ToLower(strings.TrimSpace(filter.PilotCategory))
	if pilotCategory != "" {
		q = q.Where(`(
			LOWER(COALESCE(NULLIF(roles.code, ''), roles.name)) = ?
			OR
			EXISTS (
				SELECT 1
				FROM user_roles ur_pilot
				JOIN roles r_pilot ON r_pilot.id = ur_pilot.role_id
				WHERE ur_pilot.user_id = users.id
				  AND LOWER(COALESCE(NULLIF(r_pilot.code, ''), r_pilot.name)) = ?
			)
		)`, auth.RoleCodePilot, auth.RoleCodePilot).Where("pp.pilot_category = ?", pilotCategory)
	}
	if filter.ChiefDoctor != nil {
		q = q.Where("COALESCE(dp.is_chief_physician, 0) = ?", *filter.ChiefDoctor)
	}
	if filter.ChiefTechnician != nil {
		q = q.Where("COALESCE(tp.is_chief_technician, 0) = ?", *filter.ChiefTechnician)
	}
	if filter.ResponsibleComplaint != nil {
		q = q.Where("COALESCE(tp.is_responsible_complaints, 0) = ?", *filter.ResponsibleComplaint)
	}
	if filter.ChiefPilot != nil {
		q = q.Where("COALESCE(pp.is_chief_pilot, 0) = ?", *filter.ChiefPilot)
	}
	if filter.IFRQualified != nil {
		q = q.Where("COALESCE(pp.has_ifr_qualification, 0) = ?", *filter.IFRQualified)
	}

	search := strings.TrimSpace(filter.Search)
	if search != "" {
		like := "%" + search + "%"
		q = q.Where(`
			users.username LIKE ? OR
			users.email LIKE ? OR
			users.first_name LIKE ? OR
			users.last_name LIKE ? OR
			pp.note LIKE ? OR
			dp.note LIKE ? OR
			arp.note LIKE ? OR
			tp.note LIKE ? OR
			fap.note LIKE ? OR
			sp.note LIKE ? OR
			pp.short_name LIKE ? OR
			dp.short_name LIKE ? OR
			arp.short_name LIKE ? OR
			tp.short_name LIKE ? OR
			fap.short_name LIKE ? OR
			sp.short_name LIKE ?
		`, like, like, like, like, like, like, like, like, like, like, like, like, like, like, like, like)
	}
	return q
}

func contactListSelectSQL() string {
	return `
		users.id AS user_id,
		users.created_by AS created_by,
		users.updated_by AS updated_by,
		COALESCE(NULLIF(roles.code, ''), roles.name) AS role_code,
		roles.name AS role_name,
		pp.pilot_category AS pilot_category,
		users.username AS username,
		users.email AS email,
		users.sso_email AS sso_email,
		EXISTS (
			SELECT 1
			FROM user_roles ur_admin
			JOIN roles r_admin ON r_admin.id = ur_admin.role_id
			WHERE ur_admin.user_id = users.id
			  AND LOWER(COALESCE(NULLIF(r_admin.code, ''), r_admin.name)) = 'admin'
		) AS is_admin,
		users.first_name AS first_name,
		users.last_name AS last_name,
		COALESCE(pp.short_name, dp.short_name, arp.short_name, tp.short_name, fap.short_name, sp.short_name) AS short_name,
		COALESCE(pp.license_no, tp.license_no) AS license_no,
		COALESCE(pp.location, dp.location, arp.location, tp.location, fap.location, sp.location) AS location,
		users.mobile_phone AS mobile_phone,
		users.profile_attachment_id AS profile_attachment_id,
		(COALESCE(OCTET_LENGTH(users.password_hash), 0) > 0) AS has_password,
		users.sortkey AS sortkey,
		COALESCE(pp.note, dp.note, arp.note, tp.note, fap.note, sp.note, '') AS note,
		users.is_active AS is_active,
		users.created_at AS created_at,
		users.updated_at AS updated_at
	`
}

func contactDetailSelectSQL() string {
	return `
		users.id AS user_id,
		users.created_by AS created_by,
		users.updated_by AS updated_by,
		COALESCE(NULLIF(roles.code, ''), roles.name) AS role_code,
		roles.name AS role_name,
		pp.pilot_category AS pilot_category,
		users.username AS username,
		users.email AS email,
		users.sso_email AS sso_email,
		EXISTS (
			SELECT 1
			FROM user_roles ur_admin
			JOIN roles r_admin ON r_admin.id = ur_admin.role_id
			WHERE ur_admin.user_id = users.id
			  AND LOWER(COALESCE(NULLIF(r_admin.code, ''), r_admin.name)) = 'admin'
		) AS is_admin,
		users.first_name AS first_name,
		users.last_name AS last_name,
		COALESCE(pp.short_name, dp.short_name, arp.short_name, tp.short_name, fap.short_name, sp.short_name) AS short_name,
		COALESCE(pp.license_no, tp.license_no) AS license_no,
		COALESCE(pp.location, dp.location, arp.location, tp.location, fap.location, sp.location) AS location,
		users.mobile_phone AS mobile_phone,
		users.profile_attachment_id AS profile_attachment_id,
		(COALESCE(OCTET_LENGTH(users.password_hash), 0) > 0) AS has_password,
		users.sortkey AS sortkey,
		COALESCE(pp.note, dp.note, arp.note, tp.note, fap.note, sp.note, '') AS note,
			(
				SELECT GROUP_CONCAT(CONCAT(LOWER(HEX(bcr.base_id)), '|', LOWER(HEX(b.base)), '|', bcr.role_code, '|', LOWER(HEX(COALESCE(bc.name, '')))))
				FROM base_contact_roles bcr
				JOIN bases b ON b.id = bcr.base_id AND b.deleted_at IS NULL
				LEFT JOIN base_categories bc ON bc.id = b.base_category_id
				WHERE bcr.contact_id = users.id
			) AS base_roles_raw,
		(
			SELECT GROUP_CONCAT(CONCAT(LOWER(HEX(r_all.id)), '|', LOWER(HEX(r_all.name)), '|', LOWER(COALESCE(NULLIF(r_all.code, ''), r_all.name))))
			FROM user_roles ur_all
			JOIN roles r_all ON r_all.id = ur_all.role_id
			WHERE ur_all.user_id = users.id
		) AS roles_raw,
		users.is_active AS is_active,
		users.created_at AS created_at,
		users.updated_at AS updated_at,
		pp.short_name AS pilot_short_name,
		pp.license_no AS pilot_license_no,
		pp.license_issued_at AS pilot_license_issued_at,
		pp.license_expired_at AS pilot_license_expired_at,
		pp.technician_license_no AS pilot_technician_license_no,
		pp.has_ifr_qualification AS pilot_has_ifr_qualification,
		pp.is_chief_pilot AS pilot_is_chief_pilot,
		(
			EXISTS (
				SELECT 1
				FROM base_contact_roles bcr_dul
				JOIN bases b_dul ON b_dul.id = bcr_dul.base_id AND b_dul.deleted_at IS NULL
				WHERE bcr_dul.contact_id = users.id
				  AND LOWER(bcr_dul.role_code) = 'dul'
				  AND b_dul.dul = 1
			)
		) AS pilot_is_dul,
		pp.total_flight_minutes AS pilot_total_flight_minutes,
		pp.responsible_pilot_minutes AS pilot_responsible_pilot_minutes,
		pp.second_pilot_minutes AS pilot_second_pilot_minutes,
		pp.double_command_minutes AS pilot_double_command_minutes,
		pp.flight_instructor_minutes AS pilot_flight_instructor_minutes,
		pp.night_flight_minutes AS pilot_night_flight_minutes,
		pp.ifr_flight_minutes AS pilot_ifr_flight_minutes,
		pp.location AS pilot_location,
		pp.postcode AS pilot_postcode,
		pp.street_line AS pilot_street_line,
		pp.note AS pilot_note,
		pp.weight_kg AS pilot_weight_kg,
		dp.short_name AS doctor_short_name,
		dp.specialization AS doctor_specialization,
		dp.sub_specialization AS doctor_sub_specialization,
		dp.is_chief_physician AS doctor_is_chief_physician,
		dp.location AS doctor_location,
		dp.postcode AS doctor_postcode,
		dp.street_line AS doctor_street_line,
		dp.note AS doctor_note,
		arp.short_name AS air_rescuer_short_name,
		arp.responsible_flight_rescuer AS air_rescuer_responsible_flight_rescuer,
		arp.location AS air_rescuer_location,
		arp.postcode AS air_rescuer_postcode,
		arp.street_line AS air_rescuer_street_line,
		arp.note AS air_rescuer_note,
		tp.short_name AS technician_short_name,
		tp.license_no AS technician_license_no,
		tp.is_chief_technician AS technician_is_chief_technician,
		tp.is_responsible_complaints AS technician_is_responsible_complaints,
		tp.location AS technician_location,
		tp.postcode AS technician_postcode,
		tp.street_line AS technician_street_line,
		tp.note AS technician_note,
		fap.short_name AS flight_assistant_short_name,
		fap.location AS flight_assistant_location,
		fap.postcode AS flight_assistant_postcode,
		fap.street_line AS flight_assistant_street_line,
		fap.note AS flight_assistant_note,
		sp.short_name AS staff_short_name,
		sp.role_label AS staff_role_label,
		sp.location AS staff_location,
		sp.postcode AS staff_postcode,
		sp.street_line AS staff_street_line,
		sp.note AS staff_note
	`
}

func (r *ContactRepository) roleCodeForUser(tx *gorm.DB, userID []byte) (string, error) {
	var out struct{ RoleCode string }
	err := tx.Table("users").
		Select("LOWER(COALESCE(NULLIF(roles.code, ''), roles.name)) AS role_code").
		Joins("JOIN roles ON roles.id = users.role_id").
		Where("users.id = ?", userID).
		Take(&out).Error
	if err != nil {
		return "", err
	}
	return strings.TrimSpace(out.RoleCode), nil
}

func (r *ContactRepository) resolveRoleID(tx *gorm.DB, roleCode string) ([]byte, string, error) {
	roleCode = strings.ToLower(strings.TrimSpace(roleCode))
	if roleCode == "" {
		return nil, "", errors.New("role is required")
	}
	var role auth.Role
	err := tx.Where("LOWER(code) = ? OR LOWER(name) = ?", roleCode, roleCode).Take(&role).Error
	if err != nil {
		return nil, "", err
	}
	normalized := strings.ToLower(strings.TrimSpace(role.Code))
	if normalized == "" {
		normalized = strings.ToLower(strings.TrimSpace(role.Name))
	}
	return role.ID, normalized, nil
}

func (r *ContactRepository) resolveRoleByID(tx *gorm.DB, roleID []byte) ([]byte, string, error) {
	if len(roleID) != 16 {
		return nil, "", errors.New("role_id is invalid")
	}
	var role auth.Role
	if err := tx.Where("id = ?", roleID).Take(&role).Error; err != nil {
		return nil, "", err
	}
	normalized := strings.ToLower(strings.TrimSpace(role.Code))
	if normalized == "" {
		normalized = strings.ToLower(strings.TrimSpace(role.Name))
	}
	return role.ID, normalized, nil
}

func (r *ContactRepository) upsertProfileTx(ctx context.Context, tx *gorm.DB, userID []byte, roleCode string, patch contact.ProfilePatch, isCreate bool) error {
	now := time.Now().UTC()
	actor := actorUserIDFromContext(ctx)

	switch strings.ToLower(strings.TrimSpace(roleCode)) {
	case auth.RoleCodePilot:
		updates := map[string]any{}
		if patch.PilotCategory != nil {
			updates["pilot_category"] = strings.ToLower(strings.TrimSpace(*patch.PilotCategory))
		}
		if v := patch.ShortName; v != nil {
			updates["short_name"] = strings.TrimSpace(*v)
		}
		if v := patch.LicenseNo; v != nil {
			updates["license_no"] = strings.TrimSpace(*v)
		}
		if v := patch.LicenseIssuedAt; v != nil {
			updates["license_issued_at"] = v
		}
		if v := patch.LicenseExpiredAt; v != nil {
			updates["license_expired_at"] = v
		}
		if v := patch.TechnicianLicenseNo; v != nil {
			updates["technician_license_no"] = strings.TrimSpace(*v)
		}
		if v := patch.HasIFRQualification; v != nil {
			updates["has_ifr_qualification"] = *v
		}
		if v := patch.IsChiefPilot; v != nil {
			updates["is_chief_pilot"] = *v
		}
		if v := patch.TotalFlightMinutes; v != nil {
			updates["total_flight_minutes"] = *v
		}
		if v := patch.ResponsiblePilotMinutes; v != nil {
			updates["responsible_pilot_minutes"] = *v
		}
		if v := patch.SecondPilotMinutes; v != nil {
			updates["second_pilot_minutes"] = *v
		}
		if v := patch.DoubleCommandMinutes; v != nil {
			updates["double_command_minutes"] = *v
		}
		if v := patch.FlightInstructorMinutes; v != nil {
			updates["flight_instructor_minutes"] = *v
		}
		if v := patch.NightFlightMinutes; v != nil {
			updates["night_flight_minutes"] = *v
		}
		if v := patch.IFRFlightMinutes; v != nil {
			updates["ifr_flight_minutes"] = *v
		}
		if v := patch.Location; v != nil {
			updates["location"] = strings.TrimSpace(*v)
		}
		if v := patch.Postcode; v != nil {
			updates["postcode"] = strings.TrimSpace(*v)
		}
		if v := patch.StreetLine; v != nil {
			updates["street_line"] = strings.TrimSpace(*v)
		}
		if v := patch.Note; v != nil {
			updates["note"] = strings.TrimSpace(*v)
		}
		if v := patch.WeightKG; v != nil {
			updates["weight_kg"] = *v
		}
		if isCreate && patch.PilotCategory == nil {
			updates["pilot_category"] = auth.PilotCategoryRegular
		}
		if err := r.upsertByUserID(tx, "pilot_profiles", userID, updates, actor, now); err != nil {
			return err
		}
		if patch.DULBaseIDsSet {
			if err := r.syncContactDULBasesTx(ctx, tx, userID, patch.DULBaseIDs); err != nil {
				return err
			}
		}
		return nil

	case auth.RoleCodeDoctor:
		updates := map[string]any{}
		if v := patch.ShortName; v != nil {
			updates["short_name"] = strings.TrimSpace(*v)
		}
		if v := patch.Specialization; v != nil {
			updates["specialization"] = strings.TrimSpace(*v)
		}
		if v := patch.SubSpecialization; v != nil {
			updates["sub_specialization"] = strings.TrimSpace(*v)
		}
		if v := patch.IsChiefPhysician; v != nil {
			updates["is_chief_physician"] = *v
		}
		if v := patch.Location; v != nil {
			updates["location"] = strings.TrimSpace(*v)
		}
		if v := patch.Postcode; v != nil {
			updates["postcode"] = strings.TrimSpace(*v)
		}
		if v := patch.StreetLine; v != nil {
			updates["street_line"] = strings.TrimSpace(*v)
		}
		if v := patch.Note; v != nil {
			updates["note"] = strings.TrimSpace(*v)
		}
		return r.upsertByUserID(tx, "doctor_profiles", userID, updates, actor, now)

	case auth.RoleCodeAirRescuer:
		updates := map[string]any{}
		if v := patch.ShortName; v != nil {
			updates["short_name"] = strings.TrimSpace(*v)
		}
		if v := patch.ResponsibleFlightRescuer; v != nil {
			updates["responsible_flight_rescuer"] = *v
		}
		if v := patch.Location; v != nil {
			updates["location"] = strings.TrimSpace(*v)
		}
		if v := patch.Postcode; v != nil {
			updates["postcode"] = strings.TrimSpace(*v)
		}
		if v := patch.StreetLine; v != nil {
			updates["street_line"] = strings.TrimSpace(*v)
		}
		if v := patch.Note; v != nil {
			updates["note"] = strings.TrimSpace(*v)
		}
		return r.upsertByUserID(tx, "air_rescuer_profiles", userID, updates, actor, now)

	case auth.RoleCodeTechnician:
		updates := map[string]any{}
		if v := patch.ShortName; v != nil {
			updates["short_name"] = strings.TrimSpace(*v)
		}
		if v := patch.LicenseNo; v != nil {
			updates["license_no"] = strings.TrimSpace(*v)
		}
		if v := patch.IsChiefTechnician; v != nil {
			updates["is_chief_technician"] = *v
		}
		if v := patch.IsResponsibleComplaints; v != nil {
			updates["is_responsible_complaints"] = *v
		}
		if v := patch.Location; v != nil {
			updates["location"] = strings.TrimSpace(*v)
		}
		if v := patch.Postcode; v != nil {
			updates["postcode"] = strings.TrimSpace(*v)
		}
		if v := patch.StreetLine; v != nil {
			updates["street_line"] = strings.TrimSpace(*v)
		}
		if v := patch.Note; v != nil {
			updates["note"] = strings.TrimSpace(*v)
		}
		return r.upsertByUserID(tx, "technician_profiles", userID, updates, actor, now)

	case auth.RoleCodeFlightAssistant:
		updates := map[string]any{}
		if v := patch.ShortName; v != nil {
			updates["short_name"] = strings.TrimSpace(*v)
		}
		if v := patch.Location; v != nil {
			updates["location"] = strings.TrimSpace(*v)
		}
		if v := patch.Postcode; v != nil {
			updates["postcode"] = strings.TrimSpace(*v)
		}
		if v := patch.StreetLine; v != nil {
			updates["street_line"] = strings.TrimSpace(*v)
		}
		if v := patch.Note; v != nil {
			updates["note"] = strings.TrimSpace(*v)
		}
		return r.upsertByUserID(tx, "flight_assistant_profiles", userID, updates, actor, now)

	case auth.RoleCodeStaff:
		updates := map[string]any{}
		if v := patch.ShortName; v != nil {
			updates["short_name"] = strings.TrimSpace(*v)
		}
		if v := patch.RoleLabel; v != nil {
			updates["role_label"] = strings.TrimSpace(*v)
		}
		if v := patch.Location; v != nil {
			updates["location"] = strings.TrimSpace(*v)
		}
		if v := patch.Postcode; v != nil {
			updates["postcode"] = strings.TrimSpace(*v)
		}
		if v := patch.StreetLine; v != nil {
			updates["street_line"] = strings.TrimSpace(*v)
		}
		if v := patch.Note; v != nil {
			updates["note"] = strings.TrimSpace(*v)
		}
		return r.upsertByUserID(tx, "staff_profiles", userID, updates, actor, now)
	}
	return nil
}

func (r *ContactRepository) upsertByUserID(tx *gorm.DB, table string, userID []byte, updates map[string]any, actor []byte, now time.Time) error {
	if updates == nil {
		updates = map[string]any{}
	}
	exists, err := r.profileExistsTx(tx, table, userID)
	if err != nil {
		return err
	}

	updates["updated_at"] = now
	if len(actor) > 0 {
		updates["updated_by"] = actor
	}
	if exists {
		return tx.Table(table).Where("user_id = ?", userID).Updates(updates).Error
	}

	insert := map[string]any{"user_id": userID, "created_at": now, "updated_at": now}
	if len(actor) > 0 {
		insert["created_by"] = actor
		insert["updated_by"] = actor
	}
	for k, v := range updates {
		if k == "updated_at" || k == "updated_by" {
			continue
		}
		insert[k] = v
	}
	return tx.Table(table).Create(insert).Error
}

func (r *ContactRepository) profileExistsTx(tx *gorm.DB, table string, userID []byte) (bool, error) {
	var count int64
	if err := tx.Table(table).Where("user_id = ?", userID).Limit(1).Count(&count).Error; err != nil {
		return false, err
	}
	return count > 0, nil
}

func valueOrEmpty(v *string) string {
	if v == nil {
		return ""
	}
	return *v
}

func trimmedPtr(v *string) *string {
	trimmed := strings.TrimSpace(valueOrEmpty(v))
	if trimmed == "" {
		return nil
	}
	return &trimmed
}

func normalizedEmailPtr(v *string) *string {
	trimmed := strings.ToLower(strings.TrimSpace(valueOrEmpty(v)))
	if trimmed == "" {
		return nil
	}
	return &trimmed
}

func ensureUniqueSSOEmailTx(tx *gorm.DB, ssoEmail *string, currentUserID []byte) error {
	if ssoEmail == nil {
		return nil
	}
	q := tx.Table("users").Where("sso_email = ?", *ssoEmail)
	if len(currentUserID) == 16 {
		q = q.Where("id <> ?", currentUserID)
	}
	var count int64
	if err := q.Limit(1).Count(&count).Error; err != nil {
		return err
	}
	if count > 0 {
		return errors.New("sso_email already exists")
	}
	return nil
}

func usernameExistsTx(tx *gorm.DB, username string, currentUserID []byte) (bool, error) {
	username = strings.TrimSpace(username)
	if username == "" {
		return false, nil
	}

	q := tx.Table("users").Select("id").Where("username = ?", username)
	if len(currentUserID) == 16 {
		q = q.Where("id <> ?", currentUserID)
	}

	var row struct {
		ID []byte `gorm:"column:id"`
	}
	err := q.Limit(1).Take(&row).Error
	switch {
	case err == nil:
		return true, nil
	case errors.Is(err, gorm.ErrRecordNotFound):
		return false, nil
	default:
		return false, err
	}
}

func (r *ContactRepository) syncAdminRoleTx(ctx context.Context, tx *gorm.DB, userID []byte, wantAdmin bool) error {
	if len(userID) != 16 {
		return errors.New("invalid user id")
	}
	primaryRoleID, err := r.primaryRoleIDForUserTx(tx, userID)
	if err != nil {
		return err
	}
	roleIDs, err := listUserRoleIDsTx(tx, userID)
	if err != nil {
		return err
	}
	adminRoleID, _, err := r.resolveRoleID(tx, "admin")
	if err != nil {
		return err
	}
	adminKey := string(adminRoleID)

	adjusted := make([][]byte, 0, len(roleIDs)+1)
	for i := range roleIDs {
		if len(roleIDs[i]) != 16 {
			continue
		}
		if string(roleIDs[i]) == adminKey {
			continue
		}
		adjusted = append(adjusted, roleIDs[i])
	}
	if wantAdmin {
		adjusted = append(adjusted, adminRoleID)
	}
	return replaceUserRolesTx(ctx, tx, userID, primaryRoleID, adjusted)
}

func (r *ContactRepository) primaryRoleIDForUserTx(tx *gorm.DB, userID []byte) ([]byte, error) {
	var row struct {
		RoleID []byte `gorm:"column:role_id"`
	}
	if err := tx.Table("users").Select("role_id").Where("id = ?", userID).Take(&row).Error; err != nil {
		return nil, err
	}
	if len(row.RoleID) != 16 {
		return nil, nil
	}
	return row.RoleID, nil
}

func hasAnyProfilePatch(p contact.ProfilePatch) bool {
	return p.PilotCategory != nil ||
		p.ShortName != nil ||
		p.LicenseNo != nil ||
		p.LicenseIssuedAt != nil ||
		p.LicenseExpiredAt != nil ||
		p.TechnicianLicenseNo != nil ||
		p.HasIFRQualification != nil ||
		p.IsChiefPilot != nil ||
		p.IsDUL != nil ||
		p.DULBaseIDsSet ||
		p.ResponsibleFlightRescuer != nil ||
		p.TotalFlightMinutes != nil ||
		p.ResponsiblePilotMinutes != nil ||
		p.SecondPilotMinutes != nil ||
		p.DoubleCommandMinutes != nil ||
		p.FlightInstructorMinutes != nil ||
		p.NightFlightMinutes != nil ||
		p.IFRFlightMinutes != nil ||
		p.Specialization != nil ||
		p.SubSpecialization != nil ||
		p.IsChiefPhysician != nil ||
		p.IsChiefTechnician != nil ||
		p.IsResponsibleComplaints != nil ||
		p.RoleLabel != nil ||
		p.Note != nil ||
		p.Location != nil ||
		p.Postcode != nil ||
		p.StreetLine != nil ||
		p.WeightKG != nil
}

func (r *ContactRepository) syncContactDULBasesTx(ctx context.Context, tx *gorm.DB, userID []byte, baseIDs [][]byte) error {
	if len(userID) != 16 {
		return errors.New("invalid user id")
	}

	if err := tx.Where("contact_id = ? AND LOWER(role_code) = ?", userID, contactBaseRoleDUL).Delete(&base.BaseContactRole{}).Error; err != nil {
		return err
	}
	if len(baseIDs) == 0 {
		return nil
	}

	validIDs := dedupeBinaryIDs(baseIDs)
	if err := ensureDULBasesTx(tx, validIDs); err != nil {
		return err
	}

	rows := make([]base.BaseContactRole, 0, len(validIDs))
	actor := actorUserIDFromContext(ctx)
	for i := range validIDs {
		rows = append(rows, base.BaseContactRole{
			BaseID:    validIDs[i],
			ContactID: userID,
			RoleCode:  contactBaseRoleDUL,
			CreatedBy: actor,
			UpdatedBy: actor,
		})
	}
	return tx.Create(&rows).Error
}

func dedupeBinaryIDs(ids [][]byte) [][]byte {
	out := make([][]byte, 0, len(ids))
	seen := map[string]struct{}{}
	for i := range ids {
		if len(ids[i]) != 16 {
			continue
		}
		key := string(ids[i])
		if _, ok := seen[key]; ok {
			continue
		}
		seen[key] = struct{}{}
		copyID := make([]byte, 16)
		copy(copyID, ids[i])
		out = append(out, copyID)
	}
	return out
}

func ensureDULBasesTx(tx *gorm.DB, ids [][]byte) error {
	if len(ids) == 0 {
		return nil
	}
	var count int64
	if err := tx.Table("bases").Where("id IN ? AND deleted_at IS NULL AND dul = 1", ids).Count(&count).Error; err != nil {
		return err
	}
	if int(count) != len(ids) {
		return errors.New("all dul_base_ids must reference existing base with dul=true")
	}
	return nil
}