package services

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"regexp"
	"strings"
	"time"
	"unicode"

	"github.com/google/uuid"
	"github.com/jmoiron/sqlx"

	"mgit.msbls.de/m/paliad/internal/models"
)

// EventTypeService manages paliad.event_types and the
// paliad.deadline_event_types junction. Visibility is enforced in this
// layer (not via RLS) because the DB pool runs as service-role; the
// migration's RLS policies are defense-in-depth.
//
// Users see firm-wide types (is_firm_wide=true) plus their own private
// types (created_by = user_id). Any authenticated user can create
// firm-wide types — admins moderate via Archive after the fact (m's
// Q6 call on t-paliad-088). Authors can edit/archive their own rows;
// global_admin can edit/archive any firm-wide row.
type EventTypeService struct {
	db    *sqlx.DB
	users *UserService
}

// NewEventTypeService wires the service.
func NewEventTypeService(db *sqlx.DB, users *UserService) *EventTypeService {
	return &EventTypeService{db: db, users: users}
}

const eventTypeColumns = `id, slug, label_de, label_en, category, jurisdiction,
	description, trigger_event_id, created_by, is_firm_wide, archived_at,
	created_at, updated_at`

var validEventTypeCategories = map[string]bool{
	models.EventTypeCategorySubmission: true,
	models.EventTypeCategoryDecision:   true,
	models.EventTypeCategoryOrder:      true,
	models.EventTypeCategoryService:    true,
	models.EventTypeCategoryFee:        true,
	models.EventTypeCategoryHearing:    true,
	models.EventTypeCategoryOther:      true,
}

var validEventTypeJurisdictions = map[string]bool{
	models.EventTypeJurisdictionUPC:  true,
	models.EventTypeJurisdictionEPO:  true,
	models.EventTypeJurisdictionDPMA: true,
	models.EventTypeJurisdictionDE:   true,
	models.EventTypeJurisdictionAny:  true,
}

// ErrEventTypeSlugTaken signals an attempt to create a row whose slug
// already exists in the same scope (firm-wide or per-user). Handlers
// surface this as 409.
var ErrEventTypeSlugTaken = errors.New("event_type slug already exists in scope")

// CreateEventTypeInput is the payload for POST /api/event-types.
type CreateEventTypeInput struct {
	Slug         *string `json:"slug,omitempty"` // optional; auto-derived from label_de if absent
	LabelDE      string  `json:"label_de"`
	LabelEN      string  `json:"label_en,omitempty"` // falls back to label_de when blank
	Category     string  `json:"category,omitempty"` // defaults to 'submission'
	Jurisdiction *string `json:"jurisdiction,omitempty"`
	Description  string  `json:"description,omitempty"`
	IsFirmWide   bool    `json:"is_firm_wide,omitempty"`
}

// UpdateEventTypeInput is the partial-update payload for PATCH.
// Setting Archive=true is the soft-delete pathway; never expose a hard
// delete.
type UpdateEventTypeInput struct {
	LabelDE      *string `json:"label_de,omitempty"`
	LabelEN      *string `json:"label_en,omitempty"`
	Category     *string `json:"category,omitempty"`
	Jurisdiction *string `json:"jurisdiction,omitempty"`
	Description  *string `json:"description,omitempty"`
	Archive      *bool   `json:"archive,omitempty"`
}

// ListFilter narrows List results.
type EventTypeListFilter struct {
	Category     string
	Jurisdiction string
}

// List returns event_types visible to userID (firm-wide ∪ own private,
// not archived), optionally filtered by category / jurisdiction. Sorted
// by category, then label_de.
func (s *EventTypeService) List(ctx context.Context, userID uuid.UUID, filter EventTypeListFilter) ([]models.EventType, error) {
	conds := []string{"archived_at IS NULL", "(is_firm_wide = TRUE OR created_by = $1)"}
	args := []any{userID}
	next := 2
	if filter.Category != "" {
		if !validEventTypeCategories[filter.Category] {
			return nil, fmt.Errorf("%w: invalid category %q", ErrInvalidInput, filter.Category)
		}
		conds = append(conds, fmt.Sprintf("category = $%d", next))
		args = append(args, filter.Category)
		next++
	}
	if filter.Jurisdiction != "" {
		if !validEventTypeJurisdictions[filter.Jurisdiction] {
			return nil, fmt.Errorf("%w: invalid jurisdiction %q", ErrInvalidInput, filter.Jurisdiction)
		}
		conds = append(conds, fmt.Sprintf("jurisdiction = $%d", next))
		args = append(args, filter.Jurisdiction)
		next++
	}
	rows := []models.EventType{}
	q := `SELECT ` + eventTypeColumns + ` FROM paliad.event_types
		WHERE ` + strings.Join(conds, " AND ") + `
		ORDER BY category ASC, label_de ASC`
	if err := s.db.SelectContext(ctx, &rows, q, args...); err != nil {
		return nil, fmt.Errorf("list event_types: %w", err)
	}
	return rows, nil
}

// GetByID returns one event_type, visibility-checked.
func (s *EventTypeService) GetByID(ctx context.Context, userID, id uuid.UUID) (*models.EventType, error) {
	var et models.EventType
	err := s.db.GetContext(ctx, &et,
		`SELECT `+eventTypeColumns+` FROM paliad.event_types
		  WHERE id = $1
		    AND archived_at IS NULL
		    AND (is_firm_wide = TRUE OR created_by = $2)`, id, userID)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return nil, ErrNotVisible
		}
		return nil, fmt.Errorf("fetch event_type: %w", err)
	}
	return &et, nil
}

// Create inserts a new event_type. created_by always = userID — the
// service never accepts a different value from the client. is_firm_wide
// is taken verbatim from the input (any user can publish firm-wide,
// per Q6).
func (s *EventTypeService) Create(ctx context.Context, userID uuid.UUID, input CreateEventTypeInput) (*models.EventType, error) {
	labelDE := strings.TrimSpace(input.LabelDE)
	if labelDE == "" {
		return nil, fmt.Errorf("%w: label_de is required", ErrInvalidInput)
	}
	labelEN := strings.TrimSpace(input.LabelEN)
	if labelEN == "" {
		labelEN = labelDE
	}
	category := input.Category
	if category == "" {
		category = models.EventTypeCategorySubmission
	}
	if !validEventTypeCategories[category] {
		return nil, fmt.Errorf("%w: invalid category %q", ErrInvalidInput, category)
	}
	if input.Jurisdiction != nil {
		j := strings.TrimSpace(*input.Jurisdiction)
		if j == "" {
			input.Jurisdiction = nil
		} else if !validEventTypeJurisdictions[j] {
			return nil, fmt.Errorf("%w: invalid jurisdiction %q", ErrInvalidInput, j)
		} else {
			input.Jurisdiction = &j
		}
	}
	slug := ""
	if input.Slug != nil {
		slug = strings.TrimSpace(*input.Slug)
	}
	if slug == "" {
		slug = slugify(labelDE)
	} else {
		slug = slugify(slug)
	}
	if slug == "" {
		return nil, fmt.Errorf("%w: could not derive slug from label", ErrInvalidInput)
	}

	id := uuid.New()
	const q = `INSERT INTO paliad.event_types
		(id, slug, label_de, label_en, category, jurisdiction, description,
		 trigger_event_id, created_by, is_firm_wide)
		VALUES ($1, $2, $3, $4, $5, $6, $7, NULL, $8, $9)`
	_, err := s.db.ExecContext(ctx, q,
		id, slug, labelDE, labelEN, category, input.Jurisdiction,
		strings.TrimSpace(input.Description), userID, input.IsFirmWide)
	if err != nil {
		if isUniqueViolation(err) {
			return nil, ErrEventTypeSlugTaken
		}
		return nil, fmt.Errorf("insert event_type: %w", err)
	}
	return s.GetByID(ctx, userID, id)
}

// Update applies a partial update. Authorization: author OR (firm-wide
// AND user.global_role='global_admin'). Setting Archive=true sets
// archived_at = now(); never hard-delete.
func (s *EventTypeService) Update(ctx context.Context, userID, id uuid.UUID, input UpdateEventTypeInput) (*models.EventType, error) {
	current, err := s.GetByID(ctx, userID, id)
	if err != nil {
		return nil, err
	}
	if err := s.assertCanEdit(ctx, userID, current); err != nil {
		return nil, err
	}

	sets := []string{}
	args := []any{}
	next := 1
	appendSet := func(col string, val any) {
		sets = append(sets, fmt.Sprintf("%s = $%d", col, next))
		args = append(args, val)
		next++
	}

	if input.LabelDE != nil {
		v := strings.TrimSpace(*input.LabelDE)
		if v == "" {
			return nil, fmt.Errorf("%w: label_de cannot be empty", ErrInvalidInput)
		}
		appendSet("label_de", v)
	}
	if input.LabelEN != nil {
		appendSet("label_en", strings.TrimSpace(*input.LabelEN))
	}
	if input.Category != nil {
		v := *input.Category
		if !validEventTypeCategories[v] {
			return nil, fmt.Errorf("%w: invalid category %q", ErrInvalidInput, v)
		}
		appendSet("category", v)
	}
	if input.Jurisdiction != nil {
		v := strings.TrimSpace(*input.Jurisdiction)
		if v == "" {
			appendSet("jurisdiction", nil)
		} else {
			if !validEventTypeJurisdictions[v] {
				return nil, fmt.Errorf("%w: invalid jurisdiction %q", ErrInvalidInput, v)
			}
			appendSet("jurisdiction", v)
		}
	}
	if input.Description != nil {
		appendSet("description", *input.Description)
	}
	if input.Archive != nil && *input.Archive {
		appendSet("archived_at", time.Now().UTC())
	}
	if len(sets) == 0 {
		return current, nil
	}
	appendSet("updated_at", time.Now().UTC())

	args = append(args, id)
	q := fmt.Sprintf(`UPDATE paliad.event_types SET %s WHERE id = $%d`,
		strings.Join(sets, ", "), next)
	if _, err := s.db.ExecContext(ctx, q, args...); err != nil {
		return nil, fmt.Errorf("update event_type: %w", err)
	}
	// Re-fetch through GetByID — if the row was archived in this update
	// the visibility check will return ErrNotVisible, which is the right
	// signal for the caller (the row no longer appears in pickers).
	if input.Archive != nil && *input.Archive {
		// Use a direct read so we can return the archived row exactly once.
		var et models.EventType
		if err := s.db.GetContext(ctx, &et,
			`SELECT `+eventTypeColumns+` FROM paliad.event_types WHERE id = $1`, id); err != nil {
			return nil, fmt.Errorf("fetch archived event_type: %w", err)
		}
		return &et, nil
	}
	return s.GetByID(ctx, userID, id)
}

func (s *EventTypeService) assertCanEdit(ctx context.Context, userID uuid.UUID, et *models.EventType) error {
	if et.CreatedBy != nil && *et.CreatedBy == userID {
		return nil
	}
	if !et.IsFirmWide {
		// Private row, not the author — should be invisible already, but
		// belt-and-braces: forbid the mutation explicitly.
		return ErrForbidden
	}
	// Firm-wide row, not the author — admin only.
	user, err := s.users.GetByID(ctx, userID)
	if err != nil {
		return err
	}
	if user == nil || user.GlobalRole != "global_admin" {
		return fmt.Errorf("%w: only admins can edit firm-wide event types they did not create", ErrForbidden)
	}
	return nil
}

// SuggestSimilar surfaces firm-wide rows whose label_de OR label_en
// starts with or contains the trimmed query (case-insensitive). Powers
// the "Existiert vermutlich schon: …" warning in the add modal (Q6
// mitigation against drift).
func (s *EventTypeService) SuggestSimilar(ctx context.Context, userID uuid.UUID, query string) ([]models.EventType, error) {
	q := strings.TrimSpace(query)
	if len(q) < 2 {
		return []models.EventType{}, nil
	}
	pattern := "%" + strings.ToLower(q) + "%"
	rows := []models.EventType{}
	const sqlQ = `SELECT ` + eventTypeColumns + ` FROM paliad.event_types
		WHERE archived_at IS NULL
		  AND (is_firm_wide = TRUE OR created_by = $1)
		  AND (lower(label_de) LIKE $2 OR lower(label_en) LIKE $2)
		ORDER BY (lower(label_de) = $3) DESC, label_de ASC
		LIMIT 5`
	if err := s.db.SelectContext(ctx, &rows, sqlQ, userID, pattern, strings.ToLower(q)); err != nil {
		return nil, fmt.Errorf("suggest similar event_types: %w", err)
	}
	return rows, nil
}

// ============================================================================
// Admin moderation surface (t-paliad-089)
// ============================================================================
//
// Q6 of t-paliad-088 left firm-wide creation open to any authenticated user.
// Admins moderate after the fact — bulk-archive, promote private→firm-wide,
// merge duplicates, and restore archived rows. Authorization for every method
// below is the caller's responsibility (handlers wrap the routes in
// auth.RequireAdminFunc); the service trusts that adminID has already passed
// the gate but still asserts global_admin defensively before any mutation.

// EventTypeWithUsage is one row in the admin moderation list. Embeds the
// EventType plus the count of paliad.deadline_event_types rows referencing
// it and the author's display_name (NULL on system seeds). Authors that have
// since been deleted resolve to display_name=NULL — the row still renders.
type EventTypeWithUsage struct {
	models.EventType
	UsageCount        int     `db:"usage_count"        json:"usage_count"`
	AuthorDisplayName *string `db:"author_display_name" json:"author_display_name,omitempty"`
}

// EventTypeAdminFilter narrows ListAllForAdmin / ListPrivatePendingPromotion.
// IncludeArchived=true surfaces archived rows alongside live ones (the panel's
// "Archivierte anzeigen" toggle).
type EventTypeAdminFilter struct {
	IncludeArchived bool
}

// requireAdmin is the defence-in-depth gate every admin-method below runs
// before mutating. The handler already wrapped the route in RequireAdminFunc,
// so this is belt-and-braces — it makes the service safe to call from a future
// non-HTTP entry point (CLI, batch job) without re-implementing the role
// check at every site.
func (s *EventTypeService) requireAdmin(ctx context.Context, adminID uuid.UUID) error {
	user, err := s.users.GetByID(ctx, adminID)
	if err != nil {
		return err
	}
	if user == nil || user.GlobalRole != "global_admin" {
		return fmt.Errorf("%w: admin role required", ErrForbidden)
	}
	return nil
}

// ListAllForAdmin returns every firm-wide event_type (live + archived when
// the filter requests it), enriched with the deadline_event_types usage count
// and the author's display_name. Sorted by category, then label_de.
//
// Private types are intentionally excluded — the panel's "promote" surface
// uses ListPrivatePendingPromotion for those.
func (s *EventTypeService) ListAllForAdmin(ctx context.Context, adminID uuid.UUID, filter EventTypeAdminFilter) ([]EventTypeWithUsage, error) {
	if err := s.requireAdmin(ctx, adminID); err != nil {
		return nil, err
	}
	conds := []string{"et.is_firm_wide = TRUE"}
	if !filter.IncludeArchived {
		conds = append(conds, "et.archived_at IS NULL")
	}
	rows := []EventTypeWithUsage{}
	q := `SELECT et.id, et.slug, et.label_de, et.label_en, et.category, et.jurisdiction,
		         et.description, et.trigger_event_id, et.created_by, et.is_firm_wide,
		         et.archived_at, et.created_at, et.updated_at,
		         COALESCE((SELECT COUNT(*) FROM paliad.deadline_event_types det
		                    WHERE det.event_type_id = et.id), 0) AS usage_count,
		         u.display_name AS author_display_name
		    FROM paliad.event_types et
		    LEFT JOIN paliad.users u ON u.id = et.created_by
		   WHERE ` + strings.Join(conds, " AND ") + `
		   ORDER BY et.archived_at IS NOT NULL ASC, et.category ASC, et.label_de ASC`
	if err := s.db.SelectContext(ctx, &rows, q); err != nil {
		return nil, fmt.Errorf("list event_types for admin: %w", err)
	}
	return rows, nil
}

// ListPrivatePendingPromotion returns every private (is_firm_wide=false),
// non-archived event_type across all users — feeds the admin's "promote"
// list. Same enrichment as ListAllForAdmin so the UI can reuse the row
// shape. Sorted by usage_count DESC (most-used first), then label_de.
func (s *EventTypeService) ListPrivatePendingPromotion(ctx context.Context, adminID uuid.UUID) ([]EventTypeWithUsage, error) {
	if err := s.requireAdmin(ctx, adminID); err != nil {
		return nil, err
	}
	rows := []EventTypeWithUsage{}
	const q = `SELECT et.id, et.slug, et.label_de, et.label_en, et.category, et.jurisdiction,
		         et.description, et.trigger_event_id, et.created_by, et.is_firm_wide,
		         et.archived_at, et.created_at, et.updated_at,
		         COALESCE((SELECT COUNT(*) FROM paliad.deadline_event_types det
		                    WHERE det.event_type_id = et.id), 0) AS usage_count,
		         u.display_name AS author_display_name
		    FROM paliad.event_types et
		    LEFT JOIN paliad.users u ON u.id = et.created_by
		   WHERE et.is_firm_wide = FALSE
		     AND et.archived_at IS NULL
		   ORDER BY usage_count DESC, et.label_de ASC`
	if err := s.db.SelectContext(ctx, &rows, q); err != nil {
		return nil, fmt.Errorf("list private event_types for admin: %w", err)
	}
	return rows, nil
}

// ArchiveBulk soft-deletes a set of firm-wide event_types in one round-trip.
// Already-archived rows are silently no-oped (the SET ... WHERE skips them).
// Returns the count of rows actually archived.
func (s *EventTypeService) ArchiveBulk(ctx context.Context, adminID uuid.UUID, ids []uuid.UUID) (int, error) {
	if err := s.requireAdmin(ctx, adminID); err != nil {
		return 0, err
	}
	if len(ids) == 0 {
		return 0, nil
	}
	res, err := s.db.ExecContext(ctx,
		`UPDATE paliad.event_types
		    SET archived_at = now(), updated_at = now()
		  WHERE id = ANY($1)
		    AND is_firm_wide = TRUE
		    AND archived_at IS NULL`, ids)
	if err != nil {
		return 0, fmt.Errorf("archive bulk event_types: %w", err)
	}
	n, err := res.RowsAffected()
	if err != nil {
		return 0, fmt.Errorf("archive bulk event_types rowcount: %w", err)
	}
	return int(n), nil
}

// Promote flips is_firm_wide=true on a private event_type. Slug uniqueness
// could collide (a firm-wide row with the same slug may already exist) —
// promotion fails with ErrEventTypeSlugTaken in that case so the admin can
// merge instead. Author retains created_by; the row simply becomes visible
// to everyone.
func (s *EventTypeService) Promote(ctx context.Context, adminID, id uuid.UUID) (*models.EventType, error) {
	if err := s.requireAdmin(ctx, adminID); err != nil {
		return nil, err
	}
	res, err := s.db.ExecContext(ctx,
		`UPDATE paliad.event_types
		    SET is_firm_wide = TRUE, updated_at = now()
		  WHERE id = $1
		    AND is_firm_wide = FALSE
		    AND archived_at IS NULL`, id)
	if err != nil {
		if isUniqueViolation(err) {
			return nil, ErrEventTypeSlugTaken
		}
		return nil, fmt.Errorf("promote event_type: %w", err)
	}
	n, err := res.RowsAffected()
	if err != nil {
		return nil, fmt.Errorf("promote event_type rowcount: %w", err)
	}
	if n == 0 {
		return nil, ErrNotVisible
	}
	var et models.EventType
	if err := s.db.GetContext(ctx, &et,
		`SELECT `+eventTypeColumns+` FROM paliad.event_types WHERE id = $1`, id); err != nil {
		return nil, fmt.Errorf("fetch promoted event_type: %w", err)
	}
	return &et, nil
}

// Restore flips archived_at back to NULL. Slug-uniqueness collisions are
// possible if a new firm-wide row with the same slug was created in the
// meantime — surface as ErrEventTypeSlugTaken so the admin can merge.
func (s *EventTypeService) Restore(ctx context.Context, adminID, id uuid.UUID) (*models.EventType, error) {
	if err := s.requireAdmin(ctx, adminID); err != nil {
		return nil, err
	}
	res, err := s.db.ExecContext(ctx,
		`UPDATE paliad.event_types
		    SET archived_at = NULL, updated_at = now()
		  WHERE id = $1
		    AND archived_at IS NOT NULL`, id)
	if err != nil {
		if isUniqueViolation(err) {
			return nil, ErrEventTypeSlugTaken
		}
		return nil, fmt.Errorf("restore event_type: %w", err)
	}
	n, err := res.RowsAffected()
	if err != nil {
		return nil, fmt.Errorf("restore event_type rowcount: %w", err)
	}
	if n == 0 {
		return nil, ErrNotVisible
	}
	var et models.EventType
	if err := s.db.GetContext(ctx, &et,
		`SELECT `+eventTypeColumns+` FROM paliad.event_types WHERE id = $1`, id); err != nil {
		return nil, fmt.Errorf("fetch restored event_type: %w", err)
	}
	return &et, nil
}

// MergeIDs redirects every paliad.deadline_event_types row referencing one
// of `loserIDs` to `winnerID`, then archives the losers. Junction PK dedups
// duplicate (deadline, winner) pairs that arise from the redirect — the
// pattern is INSERT ... ON CONFLICT DO NOTHING followed by DELETE on the
// loser rows that didn't get redirected.
//
// The whole flow runs in one tx: if anything fails halfway, both the
// junction redirect and the archive roll back together. winnerID must exist
// and not be in loserIDs; loserIDs must be non-empty.
func (s *EventTypeService) MergeIDs(ctx context.Context, adminID, winnerID uuid.UUID, loserIDs []uuid.UUID) error {
	if err := s.requireAdmin(ctx, adminID); err != nil {
		return err
	}
	if len(loserIDs) == 0 {
		return fmt.Errorf("%w: at least one loser id required", ErrInvalidInput)
	}
	for _, id := range loserIDs {
		if id == winnerID {
			return fmt.Errorf("%w: winner cannot also be a loser", ErrInvalidInput)
		}
	}

	tx, err := s.db.BeginTxx(ctx, nil)
	if err != nil {
		return fmt.Errorf("begin merge tx: %w", err)
	}
	defer tx.Rollback()

	// Verify the winner exists and is firm-wide. Allow archived winners only
	// if the admin explicitly restores them first — merging onto an archived
	// row would leave the new junction rows pointing at an invisible type.
	var winnerInfo struct {
		ArchivedAt sql.NullTime `db:"archived_at"`
		IsFirmWide bool         `db:"is_firm_wide"`
	}
	if err := tx.GetContext(ctx, &winnerInfo,
		`SELECT archived_at, is_firm_wide FROM paliad.event_types WHERE id = $1`, winnerID); err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return fmt.Errorf("%w: winner %s not found", ErrInvalidInput, winnerID)
		}
		return fmt.Errorf("fetch merge winner: %w", err)
	}
	if winnerInfo.ArchivedAt.Valid {
		return fmt.Errorf("%w: winner %s is archived — restore first", ErrInvalidInput, winnerID)
	}
	if !winnerInfo.IsFirmWide {
		return fmt.Errorf("%w: winner %s must be firm-wide — promote first", ErrInvalidInput, winnerID)
	}

	// Step 1 — copy each (deadline_id, loser_id) row to (deadline_id, winner_id),
	// dedupping on the junction's PK. Rows where the deadline already references
	// the winner are skipped by ON CONFLICT.
	if _, err := tx.ExecContext(ctx,
		`INSERT INTO paliad.deadline_event_types (deadline_id, event_type_id)
		 SELECT deadline_id, $1
		   FROM paliad.deadline_event_types
		  WHERE event_type_id = ANY($2)
		 ON CONFLICT DO NOTHING`, winnerID, loserIDs); err != nil {
		return fmt.Errorf("redirect deadline_event_types to winner: %w", err)
	}

	// Step 2 — drop the loser junction rows. The unique PK on (deadline_id,
	// event_type_id) ensures no duplicates are left.
	if _, err := tx.ExecContext(ctx,
		`DELETE FROM paliad.deadline_event_types WHERE event_type_id = ANY($1)`, loserIDs); err != nil {
		return fmt.Errorf("delete loser deadline_event_types: %w", err)
	}

	// Step 3 — archive the losers so they vanish from pickers. Hard-delete is
	// off the table per design (preserve history; the junction is already
	// redirected so live deadlines all point at the winner).
	if _, err := tx.ExecContext(ctx,
		`UPDATE paliad.event_types
		    SET archived_at = now(), updated_at = now()
		  WHERE id = ANY($1)
		    AND archived_at IS NULL`, loserIDs); err != nil {
		return fmt.Errorf("archive merge losers: %w", err)
	}

	if err := tx.Commit(); err != nil {
		return fmt.Errorf("commit merge tx: %w", err)
	}
	return nil
}

// ============================================================================
// Junction: paliad.deadline_event_types
// ============================================================================

// AttachToDeadlineTx replaces the set of event_type_ids attached to a
// deadline (delete + bulk insert). Caller is responsible for the visibility
// check on the deadline AND for the transaction lifecycle. event_type_ids
// must already be visibility-checked by the caller (use ValidateForUser
// before passing them in).
func (s *EventTypeService) AttachToDeadlineTx(ctx context.Context, tx *sqlx.Tx, deadlineID uuid.UUID, eventTypeIDs []uuid.UUID) error {
	if _, err := tx.ExecContext(ctx,
		`DELETE FROM paliad.deadline_event_types WHERE deadline_id = $1`, deadlineID); err != nil {
		return fmt.Errorf("clear deadline_event_types: %w", err)
	}
	if len(eventTypeIDs) == 0 {
		return nil
	}
	// One round-trip: build a multi-row VALUES clause.
	placeholders := make([]string, 0, len(eventTypeIDs))
	args := make([]any, 0, len(eventTypeIDs)*2)
	for i, etID := range eventTypeIDs {
		placeholders = append(placeholders, fmt.Sprintf("($%d, $%d)", i*2+1, i*2+2))
		args = append(args, deadlineID, etID)
	}
	q := `INSERT INTO paliad.deadline_event_types (deadline_id, event_type_id) VALUES ` +
		strings.Join(placeholders, ", ") +
		` ON CONFLICT DO NOTHING`
	if _, err := tx.ExecContext(ctx, q, args...); err != nil {
		return fmt.Errorf("insert deadline_event_types: %w", err)
	}
	return nil
}

// ValidateForUser ensures every id in ids points at a row visible to
// userID. Returns the deduplicated set in stable order; ErrNotVisible
// when any id is missing.
func (s *EventTypeService) ValidateForUser(ctx context.Context, userID uuid.UUID, ids []uuid.UUID) ([]uuid.UUID, error) {
	if len(ids) == 0 {
		return []uuid.UUID{}, nil
	}
	// Dedup while preserving order.
	seen := make(map[uuid.UUID]bool, len(ids))
	deduped := make([]uuid.UUID, 0, len(ids))
	for _, id := range ids {
		if seen[id] {
			continue
		}
		seen[id] = true
		deduped = append(deduped, id)
	}
	rows := []uuid.UUID{}
	const q = `SELECT id FROM paliad.event_types
		WHERE id = ANY($1)
		  AND archived_at IS NULL
		  AND (is_firm_wide = TRUE OR created_by = $2)`
	if err := s.db.SelectContext(ctx, &rows, q, deduped, userID); err != nil {
		return nil, fmt.Errorf("validate event_types: %w", err)
	}
	if len(rows) != len(deduped) {
		return nil, fmt.Errorf("%w: one or more event_type_ids not visible", ErrNotVisible)
	}
	return deduped, nil
}

// ListForDeadlines returns the event_type_ids attached to each deadline
// in deadlineIDs, keyed by deadline_id. Empty slices for deadlines with
// no attachments. Used by the list endpoints to enrich each row.
func (s *EventTypeService) ListForDeadlines(ctx context.Context, deadlineIDs []uuid.UUID) (map[uuid.UUID][]uuid.UUID, error) {
	out := make(map[uuid.UUID][]uuid.UUID, len(deadlineIDs))
	for _, id := range deadlineIDs {
		out[id] = []uuid.UUID{}
	}
	if len(deadlineIDs) == 0 {
		return out, nil
	}
	type pair struct {
		DeadlineID  uuid.UUID `db:"deadline_id"`
		EventTypeID uuid.UUID `db:"event_type_id"`
	}
	rows := []pair{}
	const q = `SELECT deadline_id, event_type_id
		FROM paliad.deadline_event_types
		WHERE deadline_id = ANY($1)
		ORDER BY created_at ASC`
	if err := s.db.SelectContext(ctx, &rows, q, deadlineIDs); err != nil {
		return nil, fmt.Errorf("list event_types for deadlines: %w", err)
	}
	for _, r := range rows {
		out[r.DeadlineID] = append(out[r.DeadlineID], r.EventTypeID)
	}
	return out, nil
}

// ListForDeadline is the single-row convenience wrapper.
func (s *EventTypeService) ListForDeadline(ctx context.Context, deadlineID uuid.UUID) ([]uuid.UUID, error) {
	m, err := s.ListForDeadlines(ctx, []uuid.UUID{deadlineID})
	if err != nil {
		return nil, err
	}
	return m[deadlineID], nil
}

// ============================================================================
// Helpers
// ============================================================================

var nonSlugChars = regexp.MustCompile(`[^a-z0-9]+`)

// slugify lower-cases, strips diacritics, replaces non-alphanumeric runs
// with underscores, and trims leading/trailing underscores. Keeps stable
// output across Go versions.
func slugify(s string) string {
	// Decompose Umlauts / accents to ASCII the cheap way.
	var b strings.Builder
	for _, r := range strings.ToLower(s) {
		switch r {
		case 'ä':
			b.WriteString("ae")
		case 'ö':
			b.WriteString("oe")
		case 'ü':
			b.WriteString("ue")
		case 'ß':
			b.WriteString("ss")
		default:
			if unicode.IsLetter(r) || unicode.IsDigit(r) || r == ' ' || r == '-' || r == '_' {
				b.WriteRune(r)
			}
		}
	}
	out := nonSlugChars.ReplaceAllString(b.String(), "_")
	out = strings.Trim(out, "_")
	if len(out) > 80 {
		out = out[:80]
	}
	return out
}

// isUniqueViolation detects Postgres SQLSTATE 23505 in a driver-agnostic
// way (string match on the error text). Used to convert slug-collision
// inserts into ErrEventTypeSlugTaken.
func isUniqueViolation(err error) bool {
	if err == nil {
		return false
	}
	s := err.Error()
	return strings.Contains(s, "SQLSTATE 23505") ||
		strings.Contains(s, "duplicate key") ||
		strings.Contains(s, "unique constraint")
}
