HR Management: for Customer Support A Deep Dive

// Package hr_core implements the core HR engine for customer support teams.
// This module handles shift scheduling with real-time ticket load integration.
package hr_core

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

    _ "github.com/lib/pq" // PostgreSQL driver
    "github.com/redis/go-redis/v9"
)

var (
    // ErrAgentNotFound is returned when an agent ID does not exist in the system.
    ErrAgentNotFound = errors.New("agent not found")
    // ErrShiftOverlap is returned when a requested shift overlaps with an existing shift.
    ErrShiftOverlap = errors.New("requested shift overlaps with existing shift")
    // ErrMaxTicketLoad is returned when assigning a shift would exceed the agent's max ticket load.
    ErrMaxTicketLoad = errors.New("agent would exceed max ticket load for shift period")
    // ErrInvalidShiftTime is returned when the shift start time is after the end time.
    ErrInvalidShiftTime = errors.New("invalid shift time: start after end")

    // maxTicketLoadPerHour is the maximum number of tickets an agent can handle per hour.
    maxTicketLoadPerHour = 8
    // shiftAssignmentTimeout is the timeout for shift assignment database writes.
    shiftAssignmentTimeout = 5 * time.Second
)

// Shift represents a scheduled work shift for a support agent.
type Shift struct {
    ID        string    // Unique shift identifier (UUID v4)
    AgentID   string    // ID of the agent assigned to the shift
    StartTime time.Time // Start time of the shift (UTC)
    EndTime   time.Time // End time of the shift (UTC)
    TicketLoad int      // Expected ticket load during the shift
}

// ShiftScheduler handles shift assignment, validation, and persistence.
type ShiftScheduler struct {
    db     *sql.DB       // PostgreSQL connection for shift persistence
    redis  *redis.Client // Redis client for ticket load caching
    topics []string      // Kafka topics to publish shift events to
}

// NewShiftScheduler initializes a new ShiftScheduler with the given dependencies.
func NewShiftScheduler(db *sql.DB, redis *redis.Client, topics []string) *ShiftScheduler {
    return &ShiftScheduler{
        db:     db,
        redis:  redis,
        topics: topics,
    }
}

// AssignShift assigns a new shift to an agent, validating ticket load and existing shifts.
// It returns the assigned Shift or an error if validation fails.
func (s *ShiftScheduler) AssignShift(ctx context.Context, agentID string, startTime, endTime time.Time, expectedTicketLoad int) (*Shift, error) {
    // Validate shift time is valid
    if startTime.After(endTime) {
        return nil, fmt.Errorf("%w: start=%s end=%s", ErrInvalidShiftTime, startTime, endTime)
    }

    // Check if agent exists
    var agentExists bool
    checkAgentCtx, cancel := context.WithTimeout(ctx, shiftAssignmentTimeout)
    defer cancel()
    err := s.db.QueryRowContext(checkAgentCtx, "SELECT EXISTS(SELECT 1 FROM agents WHERE id = $1 AND active = true)", agentID).Scan(&agentExists)
    if err != nil {
        return nil, fmt.Errorf("failed to check agent existence: %w", err)
    }
    if !agentExists {
        return nil, fmt.Errorf("%w: agentID=%s", ErrAgentNotFound, agentID)
    }

    // Check for overlapping shifts
    var overlapCount int
    overlapCtx, cancel := context.WithTimeout(ctx, shiftAssignmentTimeout)
    defer cancel()
    err = s.db.QueryRowContext(overlapCtx, `
        SELECT COUNT(*) FROM shifts 
        WHERE agent_id = $1 
        AND (start_time <= $3 AND end_time >= $2)
    `, agentID, startTime, endTime).Scan(&overlapCount)
    if err != nil {
        return nil, fmt.Errorf("failed to check shift overlap: %w", err)
    }
    if overlapCount > 0 {
        return nil, fmt.Errorf("%w: agentID=%s start=%s end=%s", ErrShiftOverlap, agentID, startTime, endTime)
    }

    // Calculate shift duration in hours to validate ticket load
    duration := endTime.Sub(startTime).Hours()
    if duration < 0 {
        return nil, fmt.Errorf("%w: negative duration", ErrInvalidShiftTime)
    }
    maxLoad := int(duration) * maxTicketLoadPerHour
    if expectedTicketLoad > maxLoad {
        return nil, fmt.Errorf("%w: expected=%d max=%d", ErrMaxTicketLoad, expectedTicketLoad, maxLoad)
    }

    // Generate shift ID (UUID v4 simplified for example)
    shiftID := fmt.Sprintf("shift-%d-%s", time.Now().UnixNano(), agentID)

    // Persist shift to PostgreSQL
    insertCtx, cancel := context.WithTimeout(ctx, shiftAssignmentTimeout)
    defer cancel()
    _, err = s.db.ExecContext(insertCtx, `
        INSERT INTO shifts (id, agent_id, start_time, end_time, ticket_load, created_at)
        VALUES ($1, $2, $3, $4, $5, $6)
    `, shiftID, agentID, startTime, endTime, expectedTicketLoad, time.Now().UTC())
    if err != nil {
        return nil, fmt.Errorf("failed to insert shift: %w", err)
    }

    // Update Redis cache with new shift availability
    cacheCtx, cancel := context.WithTimeout(ctx, shiftAssignmentTimeout)
    defer cancel()
    cacheKey := fmt.Sprintf("agent:%s:available", agentID)
    err = s.redis.Set(cacheCtx, cacheKey, "true", 24*time.Hour).Err()
    if err != nil {
        // Log but don't fail assignment, cache will be updated on next poll
        fmt.Printf("warning: failed to update Redis cache for agent %s: %v\n", agentID, err)
    }

    // Publish shift assignment event to Kafka (simplified, actual implementation uses Sarama)
    // kafkaCtx, cancel := context.WithTimeout(ctx, shiftAssignmentTimeout)
    // defer cancel()
    // msg := fmt.Sprintf("shift.assigned|%s|%s|%s|%s|%d", shiftID, agentID, startTime, endTime, expectedTicketLoad)
    // for _, topic := range s.topics {
    //  err = s.kafka.Produce(kafkaCtx, topic, []byte(msg))
    //  if err != nil {
    //      fmt.Printf("warning: failed to publish shift event to topic %s: %v\n", topic, err)
    //  }
    // }

    // Return the assigned shift
    return &Shift{
        ID:        shiftID,
        AgentID:   agentID,
        StartTime: startTime,
        EndTime:   endTime,
        TicketLoad: expectedTicketLoad,
    }, nil
}

// Package hr_core also handles leave requests, integrating with shift schedules and ticket load.
package hr_core

import (
    "context"
    "database/sql"
    "errors"
    "fmt"
    "time"
)

var (
    // ErrLeaveOverlap is returned when a leave request overlaps with an existing approved leave.
    ErrLeaveOverlap = errors.New("leave request overlaps with existing approved leave")
    // ErrInsufficientCoverage is returned when approving leave would leave shifts uncovered.
    ErrInsufficientCoverage = errors.New("no coverage available for requested leave period")
    // ErrLeaveBalanceExceeded is returned when the agent has insufficient leave balance.
    ErrLeaveBalanceExceeded = errors.New("agent has insufficient leave balance")
    // leaveApprovalTimeout is the timeout for leave approval database operations.
    leaveApprovalTimeout = 5 * time.Second
)

// LeaveRequest represents a request for time off from a support agent.
type LeaveRequest struct {
    ID         string    // Unique leave request ID (UUID v4)
    AgentID    string    // ID of the agent requesting leave
    StartTime  time.Time // Start of leave period (UTC)
    EndTime    time.Time // End of leave period (UTC)
    Type       string    // Type of leave: "vacation", "sick", "personal"
    Status     string    // Status: "pending", "approved", "rejected"
    ApprovedBy string    // ID of the manager who approved the request (empty if pending)
}

// LeaveManager handles leave request validation, approval, and balance tracking.
type LeaveManager struct {
    db *sql.DB // PostgreSQL connection for leave persistence
}

// NewLeaveManager initializes a new LeaveManager with the given database connection.
func NewLeaveManager(db *sql.DB) *LeaveManager {
    return &LeaveManager{db: db}
}

// SubmitLeaveRequest submits a new leave request for an agent, validating balance and overlaps.
func (l *LeaveManager) SubmitLeaveRequest(ctx context.Context, agentID string, startTime, endTime time.Time, leaveType string) (*LeaveRequest, error) {
    // Validate leave type
    validTypes := map[string]bool{"vacation": true, "sick": true, "personal": true}
    if !validTypes[leaveType] {
        return nil, fmt.Errorf("invalid leave type: %s", leaveType)
    }

    // Check agent exists and is active
    var agentActive bool
    checkCtx, cancel := context.WithTimeout(ctx, leaveApprovalTimeout)
    defer cancel()
    err := l.db.QueryRowContext(checkCtx, "SELECT active FROM agents WHERE id = $1", agentID).Scan(&agentActive)
    if err != nil {
        if err == sql.ErrNoRows {
            return nil, fmt.Errorf("%w: agentID=%s", ErrAgentNotFound, agentID)
        }
        return nil, fmt.Errorf("failed to check agent status: %w", err)
    }
    if !agentActive {
        return nil, fmt.Errorf("agent %s is not active", agentID)
    }

    // Check leave balance (vacation only, sick/personal are unlimited)
    if leaveType == "vacation" {
        var balance int
        balanceCtx, cancel := context.WithTimeout(ctx, leaveApprovalTimeout)
        defer cancel()
        err = l.db.QueryRowContext(balanceCtx, "SELECT vacation_balance FROM agents WHERE id = $1", agentID).Scan(&balance)
        if err != nil {
            return nil, fmt.Errorf("failed to get leave balance: %w", err)
        }
        daysRequested := int(endTime.Sub(startTime).Hours() / 24)
        if daysRequested > balance {
            return nil, fmt.Errorf("%w: requested=%d balance=%d", ErrLeaveBalanceExceeded, daysRequested, balance)
        }
    }

    // Check for overlapping approved leaves
    var overlapCount int
    overlapCtx, cancel := context.WithTimeout(ctx, leaveApprovalTimeout)
    defer cancel()
    err = l.db.QueryRowContext(overlapCtx, `
        SELECT COUNT(*) FROM leave_requests 
        WHERE agent_id = $1 
        AND status = 'approved'
        AND (start_time <= $3 AND end_time >= $2)
    `, agentID, startTime, endTime).Scan(&overlapCount)
    if err != nil {
        return nil, fmt.Errorf("failed to check leave overlap: %w", err)
    }
    if overlapCount > 0 {
        return nil, fmt.Errorf("%w: agentID=%s start=%s end=%s", ErrLeaveOverlap, agentID, startTime, endTime)
    }

    // Generate leave request ID
    leaveID := fmt.Sprintf("leave-%d-%s", time.Now().UnixNano(), agentID)

    // Persist leave request to PostgreSQL
    insertCtx, cancel := context.WithTimeout(ctx, leaveApprovalTimeout)
    defer cancel()
    _, err = l.db.ExecContext(insertCtx, `
        INSERT INTO leave_requests (id, agent_id, start_time, end_time, type, status, created_at)
        VALUES ($1, $2, $3, $4, $5, 'pending', $6)
    `, leaveID, agentID, startTime, endTime, leaveType, time.Now().UTC())
    if err != nil {
        return nil, fmt.Errorf("failed to insert leave request: %w", err)
    }

    // Return the submitted leave request
    return &LeaveRequest{
        ID:        leaveID,
        AgentID:   agentID,
        StartTime: startTime,
        EndTime:   endTime,
        Type:      leaveType,
        Status:    "pending",
    }, nil
}

// ApproveLeaveRequest approves a pending leave request, checking shift coverage first.
func (l *LeaveManager) ApproveLeaveRequest(ctx context.Context, leaveID, managerID string) error {
    // Fetch leave request details
    var lr LeaveRequest
    fetchCtx, cancel := context.WithTimeout(ctx, leaveApprovalTimeout)
    defer cancel()
    err := l.db.QueryRowContext(fetchCtx, `
        SELECT id, agent_id, start_time, end_time, type, status 
        FROM leave_requests 
        WHERE id = $1
    `, leaveID).Scan(&lr.ID, &lr.AgentID, &lr.StartTime, &lr.EndTime, &lr.Type, &lr.Status)
    if err != nil {
        if err == sql.ErrNoRows {
            return fmt.Errorf("leave request %s not found", leaveID)
        }
        return fmt.Errorf("failed to fetch leave request: %w", err)
    }

    // Check if already approved/rejected
    if lr.Status != "pending" {
        return fmt.Errorf("leave request %s is already %s", leaveID, lr.Status)
    }

    // Check shift coverage for the leave period (simplified: check if any other active agent is available)
    var uncoveredShifts int
    coverageCtx, cancel := context.WithTimeout(ctx, leaveApprovalTimeout)
    defer cancel()
    err = l.db.QueryRowContext(coverageCtx, `
        SELECT COUNT(*) FROM shifts 
        WHERE agent_id = $1 
        AND start_time >= $2 
        AND end_time <= $3
        AND NOT EXISTS (
            SELECT 1 FROM shifts s2 
            WHERE s2.agent_id != $1 
            AND s2.start_time <= shifts.end_time 
            AND s2.end_time >= shifts.start_time 
            AND s2.agent_id IN (SELECT id FROM agents WHERE active = true AND id != $1)
        )
    `, lr.AgentID, lr.StartTime, lr.EndTime).Scan(&uncoveredShifts)
    if err != nil {
        return fmt.Errorf("failed to check shift coverage: %w", err)
    }
    if uncoveredShifts > 0 {
        return fmt.Errorf("%w: %d uncovered shifts for leave %s", ErrInsufficientCoverage, uncoveredShifts, leaveID)
    }

    // Update leave request status to approved
    updateCtx, cancel := context.WithTimeout(ctx, leaveApprovalTimeout)
    defer cancel()
    _, err = l.db.ExecContext(updateCtx, `
        UPDATE leave_requests 
        SET status = 'approved', approved_by = $1, approved_at = $2
        WHERE id = $3
    `, managerID, time.Now().UTC(), leaveID)
    if err != nil {
        return fmt.Errorf("failed to approve leave request: %w", err)
    }

    // Deduct vacation balance if applicable
    if lr.Type == "vacation" {
        days := int(lr.EndTime.Sub(lr.StartTime).Hours() / 24)
        deductCtx, cancel := context.WithTimeout(ctx, leaveApprovalTimeout)
        defer cancel()
        _, err = l.db.ExecContext(deductCtx, `
            UPDATE agents 
            SET vacation_balance = vacation_balance - $1 
            WHERE id = $2
        `, days, lr.AgentID)
        if err != nil {
            return fmt.Errorf("failed to deduct leave balance: %w", err)
        }
    }

    return nil
}

// Package hr_core provides performance analytics for support agents, tying HR metrics to support KPIs.
package hr_core

import (
    "context"
    "database/sql"
    "errors"
    "fmt"
    "time"
)

var (
    // ErrAgentInactive is returned when trying to calculate performance for an inactive agent.
    ErrAgentInactive = errors.New("agent is inactive")
    // performanceCalcTimeout is the timeout for performance calculation database operations.
    performanceCalcTimeout = 10 * time.Second
)

// PerformanceScore represents a support agent's performance rating.
type PerformanceScore struct {
    AgentID       string    // Agent ID
    PeriodStart   time.Time // Start of the scoring period (UTC)
    PeriodEnd     time.Time // End of the scoring period (UTC)
    TicketScore   float64   // Score based on ticket resolution time (0-100)
    CSATScore     float64   // Score based on customer satisfaction (0-100)
    AttendanceScore float64 // Score based on shift attendance (0-100)
    OverallScore  float64   // Weighted overall score (0-100)
    Rating        string    // Rating: "excellent", "good", "needs_improvement"
}

// PerformanceCalculator calculates agent performance scores using ticket, CSAT, and attendance data.
type PerformanceCalculator struct {
    db *sql.DB // PostgreSQL connection for performance data
}

// NewPerformanceCalculator initializes a new PerformanceCalculator.
func NewPerformanceCalculator(db *sql.DB) *PerformanceCalculator {
    return &PerformanceCalculator{db: db}
}

// CalculatePerformance calculates an agent's performance score for a given period.
// It pulls ticket resolution times from the tickets table, CSAT from ticket_feedback,
// and attendance from shifts and leave_requests.
func (p *PerformanceCalculator) CalculatePerformance(ctx context.Context, agentID string, periodStart, periodEnd time.Time) (*PerformanceScore, error) {
    // Validate period
    if periodStart.After(periodEnd) {
        return nil, fmt.Errorf("invalid period: start after end")
    }

    // Check agent is active
    var agentActive bool
    checkCtx, cancel := context.WithTimeout(ctx, performanceCalcTimeout)
    defer cancel()
    err := p.db.QueryRowContext(checkCtx, "SELECT active FROM agents WHERE id = $1", agentID).Scan(&agentActive)
    if err != nil {
        if err == sql.ErrNoRows {
            return nil, fmt.Errorf("%w: agentID=%s", ErrAgentNotFound, agentID)
        }
        return nil, fmt.Errorf("failed to check agent status: %w", err)
    }
    if !agentActive {
        return nil, fmt.Errorf("%w: agentID=%s", ErrAgentInactive, agentID)
    }

    // Calculate TicketScore: 100 - (average resolution time in minutes - 30) * 2, min 0
    // Target resolution time is 30 minutes for support tickets
    var avgResolutionMinutes float64
    ticketCtx, cancel := context.WithTimeout(ctx, performanceCalcTimeout)
    defer cancel()
    err = p.db.QueryRowContext(ticketCtx, `
        SELECT COALESCE(AVG(EXTRACT(EPOCH FROM resolved_at - created_at)/60), 0) 
        FROM tickets 
        WHERE agent_id = $1 
        AND created_at >= $2 
        AND created_at <= $3 
        AND resolved_at IS NOT NULL
    `, agentID, periodStart, periodEnd).Scan(&avgResolutionMinutes)
    if err != nil {
        return nil, fmt.Errorf("failed to calculate avg resolution time: %w", err)
    }
    ticketScore := 100.0 - (avgResolutionMinutes-30)*2
    if ticketScore < 0 {
        ticketScore = 0
    }
    if ticketScore > 100 {
        ticketScore = 100
    }

    // Calculate CSATScore: average CSAT rating (1-5) * 20, max 100
    var avgCSAT float64
    csatCtx, cancel := context.WithTimeout(ctx, performanceCalcTimeout)
    defer cancel()
    err = p.db.QueryRowContext(csatCtx, `
        SELECT COALESCE(AVG(rating), 0) 
        FROM ticket_feedback 
        WHERE agent_id = $1 
        AND created_at >= $2 
        AND created_at <= $3
    `, agentID, periodStart, periodEnd).Scan(&avgCSAT)
    if err != nil {
        return nil, fmt.Errorf("failed to calculate avg CSAT: %w", err)
    }
    csatScore := avgCSAT * 20
    if csatScore > 100 {
        csatScore = 100
    }

    // Calculate AttendanceScore: (scheduled shifts - missed shifts) / scheduled shifts * 100
    var scheduledShifts int
    var missedShifts int
    attendanceCtx, cancel := context.WithTimeout(ctx, performanceCalcTimeout)
    defer cancel()
    err = p.db.QueryRowContext(attendanceCtx, `
        SELECT 
            COUNT(*) AS scheduled,
            COUNT(*) FILTER (WHERE NOT attended) AS missed
        FROM shifts 
        WHERE agent_id = $1 
        AND start_time >= $2 
        AND end_time <= $3
    `, agentID, periodStart, periodEnd).Scan(&scheduledShifts, &missedShifts)
    if err != nil {
        return nil, fmt.Errorf("failed to calculate attendance: %w", err)
    }
    attendanceScore := 100.0
    if scheduledShifts > 0 {
        attendanceScore = float64(scheduledShifts - missedShifts) / float64(scheduledShifts) * 100
    }

    // Calculate OverallScore: weighted average (ticket 40%, CSAT 40%, attendance 20%)
    overallScore := (ticketScore * 0.4) + (csatScore * 0.4) + (attendanceScore * 0.2)

    // Determine rating
    rating := "needs_improvement"
    if overallScore >= 80 {
        rating = "excellent"
    } else if overallScore >= 60 {
        rating = "good"
    }

    // Persist performance score to database
    insertCtx, cancel := context.WithTimeout(ctx, performanceCalcTimeout)
    defer cancel()
    _, err = p.db.ExecContext(insertCtx, `
        INSERT INTO performance_scores (agent_id, period_start, period_end, ticket_score, csat_score, attendance_score, overall_score, rating, created_at)
        VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
        ON CONFLICT (agent_id, period_start) DO UPDATE SET
            period_end = EXCLUDED.period_end,
            ticket_score = EXCLUDED.ticket_score,
            csat_score = EXCLUDED.csat_score,
            attendance_score = EXCLUDED.attendance_score,
            overall_score = EXCLUDED.overall_score,
            rating = EXCLUDED.rating,
            updated_at = EXCLUDED.created_at
    `, agentID, periodStart, periodEnd, ticketScore, csatScore, attendanceScore, overallScore, rating, time.Now().UTC())
    if err != nil {
        return nil, fmt.Errorf("failed to persist performance score: %w", err)
    }

    return &PerformanceScore{
        AgentID:       agentID,
        PeriodStart:   periodStart,
        PeriodEnd:     periodEnd,
        TicketScore:   ticketScore,
        CSATScore:     csatScore,
        AttendanceScore: attendanceScore,
        OverallScore:  overallScore,
        Rating:        rating,
    }, nil
}

// GetTicketLoad fetches an agent's current ticket load from Redis, falling back to Zendesk API.
func GetTicketLoad(ctx context.Context, agentID string, redis *redis.Client, zendesk *ZendeskClient) (int, error) {
    cacheKey := fmt.Sprintf("agent:%s:ticket_load", agentID)
    // Check Redis first
    val, err := redis.Get(ctx, cacheKey).Int()
    if err == nil {
        return val, nil
    }
    if !errors.Is(err, redis.Nil) {
        fmt.Printf("warning: Redis error for key %s: %v\n", cacheKey, err)
    }
    // Fall back to Zendesk API
    load, err := zendesk.GetAgentTicketCount(ctx, agentID)
    if err != nil {
        return 0, fmt.Errorf("failed to get ticket load from Zendesk: %w", err)
    }
    // Write to Redis with 5-second TTL
    redis.Set(ctx, cacheKey, load, 5*time.Second)
    return load, nil
}

// PublishShiftAssignedEvent publishes a shift.assigned event to Kafka.
func PublishShiftAssignedEvent(ctx context.Context, producer *kafka.Producer, shift *Shift) error {
    event := fmt.Sprintf("shift.assigned|%s|%s|%s|%s|%d|%d",
        shift.ID,
        shift.AgentID,
        shift.StartTime.Format(time.RFC3339),
        shift.EndTime.Format(time.RFC3339),
        shift.TicketLoad,
        time.Now().UnixNano(),
    )
    msg := &kafka.Message{
        TopicPartition: kafka.TopicPartition{Topic: "hr-events", Partition: kafka.PartitionAny},
        Value:          []byte(event),
    }
    return producer.Produce(ctx, msg)
}

// FetchCSATScores fetches an agent's average CSAT rating for a given period from Zendesk.
func FetchCSATScores(ctx context.Context, agentID string, start, end time.Time, zendesk *ZendeskClient) (float64, error) {
    tickets, err := zendesk.ListTickets(ctx, agentID, start, end)
    if err != nil {
        return 0, fmt.Errorf("failed to list tickets: %w", err)
    }
    var totalRating float64
    var count int
    for _, ticket := range tickets {
        if ticket.Feedback != nil {
            totalRating += ticket.Feedback.Rating
            count++
        }
    }
    if count == 0 {
        return 0, nil
    }
    return totalRating / float64(count), nil
}