package employee

import (
	"database/sql"
	"encoding/json"
	"net/http"
	"time"

	"github.com/go-chi/chi/v5"
)

// Handler hanterar Employee Lifecycle API
type Handler struct {
	DB *sql.DB
}

func NewHandler(db *sql.DB) *Handler {
	return &Handler{DB: db}
}

// ==========================================
// EMPLOYEE CRUD
// ==========================================

// Employee representerar en medarbetare
type Employee struct {
	ID                  string     `json:"id"`
	TenantID            string     `json:"tenant_id"`
	FirstName           string     `json:"first_name"`
	LastName            string     `json:"last_name"`
	Email               string     `json:"email"`
	Phone               *string    `json:"phone,omitempty"`
	PersonalNumber      *string    `json:"personal_number,omitempty"`
	BirthDate           *time.Time `json:"birth_date,omitempty"`
	AvatarURL           *string    `json:"avatar_url,omitempty"`
	Bio                 *string    `json:"bio,omitempty"`
	EmploymentType      string     `json:"employment_type"`
	EmploymentStatus    string     `json:"employment_status"`
	StartDate           *time.Time `json:"start_date,omitempty"`
	EndDate             *time.Time `json:"end_date,omitempty"`
	ProbationEndDate    *time.Time `json:"probation_end_date,omitempty"`
	NoticePeriodDays    int        `json:"notice_period_days"`
	DepartmentID        *string    `json:"department_id,omitempty"`
	TeamID              *string    `json:"team_id,omitempty"`
	ManagerID           *string    `json:"manager_id,omitempty"`
	RoleID              *string    `json:"role_id,omitempty"`
	Salary              *float64   `json:"salary,omitempty"`
	SalaryCurrency      string     `json:"salary_currency"`
	BankAccount         *string    `json:"bank_account,omitempty"`
	BankClearing        *string    `json:"bank_clearing,omitempty"`
	Address             *string    `json:"address,omitempty"`
	PostalCode          *string    `json:"postal_code,omitempty"`
	City                *string    `json:"city,omitempty"`
	Country             string     `json:"country"`
	EmergencyContactName     *string `json:"emergency_contact_name,omitempty"`
	EmergencyContactPhone    *string `json:"emergency_contact_phone,omitempty"`
	EmergencyContactRelation *string `json:"emergency_contact_relation,omitempty"`
	CreatedAt           time.Time  `json:"created_at"`
	UpdatedAt           time.Time  `json:"updated_at"`
}

// ListEmployees returnerar alla medarbetare
func (h *Handler) ListEmployees(w http.ResponseWriter, r *http.Request) {
	rows, err := h.DB.Query(`
		SELECT id, tenant_id, first_name, last_name, email, phone, employment_type, employment_status,
		       start_date, department_id, team_id, manager_id, role_id, created_at
		FROM employees
		WHERE employment_status != 'terminated'
		ORDER BY created_at DESC
	`)
	if err != nil {
		http.Error(w, `{"error":"database error"}`, http.StatusInternalServerError)
		return
	}
	defer rows.Close()

	var employees []Employee
	for rows.Next() {
		var e Employee
		var phone sql.NullString
		var startDate sql.NullTime
		var deptID, teamID, managerID, roleID sql.NullString
		
		rows.Scan(&e.ID, &e.TenantID, &e.FirstName, &e.LastName, &e.Email, &phone,
			&e.EmploymentType, &e.EmploymentStatus, &startDate,
			&deptID, &teamID, &managerID, &roleID, &e.CreatedAt)
		
		if phone.Valid { e.Phone = &phone.String }
		if startDate.Valid { e.StartDate = &startDate.Time }
		if deptID.Valid { e.DepartmentID = &deptID.String }
		if teamID.Valid { e.TeamID = &teamID.String }
		if managerID.Valid { e.ManagerID = &managerID.String }
		if roleID.Valid { e.RoleID = &roleID.String }
		
		employees = append(employees, e)
	}

	w.Header().Set("Content-Type", "application/json")
	json.NewEncoder(w).Encode(map[string]interface{}{
		"employees": employees,
		"total":     len(employees),
	})
}

// GetEmployee returnerar en specifik medarbetare
func (h *Handler) GetEmployee(w http.ResponseWriter, r *http.Request) {
	id := chi.URLParam(r, "id")

	var e Employee
	var phone, personalNumber, avatarURL, bio, bankAccount, bankClearing sql.NullString
	var birthDate, startDate, endDate, probationEnd sql.NullTime
	var deptID, teamID, managerID, roleID sql.NullString
	var salary sql.NullFloat64
	var address, postalCode, city, emergencyName, emergencyPhone, emergencyRelation sql.NullString

	err := h.DB.QueryRow(`
		SELECT id, tenant_id, first_name, last_name, email, phone, personal_number, birth_date,
		       avatar_url, bio, employment_type, employment_status, start_date, end_date, probation_end_date,
		       notice_period_days, department_id, team_id, manager_id, role_id,
		       salary, salary_currency, bank_account, bank_clearing,
		       address, postal_code, city, country,
		       emergency_contact_name, emergency_contact_phone, emergency_contact_relation,
		       created_at, updated_at
		FROM employees WHERE id = $1
	`, id).Scan(
		&e.ID, &e.TenantID, &e.FirstName, &e.LastName, &e.Email, &phone, &personalNumber, &birthDate,
		&avatarURL, &bio, &e.EmploymentType, &e.EmploymentStatus, &startDate, &endDate, &probationEnd,
		&e.NoticePeriodDays, &deptID, &teamID, &managerID, &roleID,
		&salary, &e.SalaryCurrency, &bankAccount, &bankClearing,
		&address, &postalCode, &city, &e.Country,
		&emergencyName, &emergencyPhone, &emergencyRelation,
		&e.CreatedAt, &e.UpdatedAt,
	)

	if err == sql.ErrNoRows {
		http.Error(w, `{"error":"employee not found"}`, http.StatusNotFound)
		return
	}
	if err != nil {
		http.Error(w, `{"error":"database error"}`, http.StatusInternalServerError)
		return
	}

	// Sätt nullable fält
	if phone.Valid { e.Phone = &phone.String }
	if personalNumber.Valid { e.PersonalNumber = &personalNumber.String }
	if birthDate.Valid { e.BirthDate = &birthDate.Time }
	if avatarURL.Valid { e.AvatarURL = &avatarURL.String }
	if bio.Valid { e.Bio = &bio.String }
	if startDate.Valid { e.StartDate = &startDate.Time }
	if endDate.Valid { e.EndDate = &endDate.Time }
	if probationEnd.Valid { e.ProbationEndDate = &probationEnd.Time }
	if deptID.Valid { e.DepartmentID = &deptID.String }
	if teamID.Valid { e.TeamID = &teamID.String }
	if managerID.Valid { e.ManagerID = &managerID.String }
	if roleID.Valid { e.RoleID = &roleID.String }
	if salary.Valid { e.Salary = &salary.Float64 }
	if bankAccount.Valid { e.BankAccount = &bankAccount.String }
	if bankClearing.Valid { e.BankClearing = &bankClearing.String }
	if address.Valid { e.Address = &address.String }
	if postalCode.Valid { e.PostalCode = &postalCode.String }
	if city.Valid { e.City = &city.String }
	if emergencyName.Valid { e.EmergencyContactName = &emergencyName.String }
	if emergencyPhone.Valid { e.EmergencyContactPhone = &emergencyPhone.String }
	if emergencyRelation.Valid { e.EmergencyContactRelation = &emergencyRelation.String }

	w.Header().Set("Content-Type", "application/json")
	json.NewEncoder(w).Encode(e)
}

// CreateEmployee skapar en ny medarbetare
func (h *Handler) CreateEmployee(w http.ResponseWriter, r *http.Request) {
	var req Employee
	if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
		http.Error(w, `{"error":"invalid request"}`, http.StatusBadRequest)
		return
	}

	var id string
	err := h.DB.QueryRow(`
		INSERT INTO employees (tenant_id, first_name, last_name, email, phone, personal_number,
		                       employment_type, employment_status, start_date, department_id, team_id, manager_id, role_id,
		                       salary, salary_currency, bank_account, bank_clearing,
		                       address, postal_code, city, country)
		VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21)
		RETURNING id
	`, req.TenantID, req.FirstName, req.LastName, req.Email, req.Phone, req.PersonalNumber,
		req.EmploymentType, req.EmploymentStatus, req.StartDate, req.DepartmentID, req.TeamID, req.ManagerID, req.RoleID,
		req.Salary, req.SalaryCurrency, req.BankAccount, req.BankClearing,
		req.Address, req.PostalCode, req.City, req.Country).Scan(&id)

	if err != nil {
		http.Error(w, `{"error":"failed to create employee"}`, http.StatusInternalServerError)
		return
	}

	// Skapa timeline event
	h.DB.Exec(`
		INSERT INTO employee_timeline_events (employee_id, event_type, event_date, title, description)
		VALUES ($1, 'hired', $2, 'Anställd', 'Anställning påbörjad')
	`, id, time.Now())

	w.Header().Set("Content-Type", "application/json")
	w.WriteHeader(http.StatusCreated)
	json.NewEncoder(w).Encode(map[string]interface{}{
		"id":      id,
		"message": "Employee created",
	})
}

// UpdateEmployee uppdaterar en medarbetare
func (h *Handler) UpdateEmployee(w http.ResponseWriter, r *http.Request) {
	id := chi.URLParam(r, "id")

	var req Employee
	if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
		http.Error(w, `{"error":"invalid request"}`, http.StatusBadRequest)
		return
	}

	_, err := h.DB.Exec(`
		UPDATE employees
		SET first_name = COALESCE($1, first_name),
		    last_name = COALESCE($2, last_name),
		    email = COALESCE($3, email),
		    phone = COALESCE($4, phone),
		    employment_type = COALESCE($5, employment_type),
		    employment_status = COALESCE($6, employment_status),
		    department_id = COALESCE($7, department_id),
		    team_id = COALESCE($8, team_id),
		    manager_id = COALESCE($9, manager_id),
		    role_id = COALESCE($10, role_id),
		    salary = COALESCE($11, salary),
		    updated_at = NOW()
		WHERE id = $12
	`, req.FirstName, req.LastName, req.Email, req.Phone,
		req.EmploymentType, req.EmploymentStatus,
		req.DepartmentID, req.TeamID, req.ManagerID, req.RoleID,
		req.Salary, id)

	if err != nil {
		http.Error(w, `{"error":"failed to update employee"}`, http.StatusInternalServerError)
		return
	}

	w.Header().Set("Content-Type", "application/json")
	json.NewEncoder(w).Encode(map[string]interface{}{
		"message": "Employee updated",
	})
}

// ==========================================
// TIMELINE
// ==========================================

// TimelineEvent representerar en händelse i tidslinjen
type TimelineEvent struct {
	ID          string                 `json:"id"`
	EmployeeID  string                 `json:"employee_id"`
	EventType   string                 `json:"event_type"`
	EventDate   time.Time              `json:"event_date"`
	Title       string                 `json:"title"`
	Description *string                `json:"description,omitempty"`
	Metadata    map[string]interface{} `json:"metadata,omitempty"`
	CreatedAt   time.Time              `json:"created_at"`
}

// GetTimeline returnerar tidslinje för en medarbetare
func (h *Handler) GetTimeline(w http.ResponseWriter, r *http.Request) {
	employeeID := chi.URLParam(r, "id")

	rows, err := h.DB.Query(`
		SELECT id, event_type, event_date, title, description, metadata, created_at
		FROM employee_timeline_events
		WHERE employee_id = $1
		ORDER BY event_date DESC, created_at DESC
	`, employeeID)
	if err != nil {
		http.Error(w, `{"error":"database error"}`, http.StatusInternalServerError)
		return
	}
	defer rows.Close()

	var events []TimelineEvent
	for rows.Next() {
		var e TimelineEvent
		var desc sql.NullString
		var meta []byte
		rows.Scan(&e.ID, &e.EventType, &e.EventDate, &e.Title, &desc, &meta, &e.CreatedAt)
		if desc.Valid { e.Description = &desc.String }
		if len(meta) > 0 {
			json.Unmarshal(meta, &e.Metadata)
		}
		e.EmployeeID = employeeID
		events = append(events, e)
	}

	w.Header().Set("Content-Type", "application/json")
	json.NewEncoder(w).Encode(map[string]interface{}{
		"events": events,
		"total":  len(events),
	})
}

// AddTimelineEvent lägger till en händelse
func (h *Handler) AddTimelineEvent(w http.ResponseWriter, r *http.Request) {
	employeeID := chi.URLParam(r, "id")

	var req struct {
		EventType   string                 `json:"event_type"`
		EventDate   time.Time              `json:"event_date"`
		Title       string                 `json:"title"`
		Description string                 `json:"description"`
		Metadata    map[string]interface{} `json:"metadata"`
	}
	if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
		http.Error(w, `{"error":"invalid request"}`, http.StatusBadRequest)
		return
	}

	metaJSON, _ := json.Marshal(req.Metadata)

	var id string
	err := h.DB.QueryRow(`
		INSERT INTO employee_timeline_events (employee_id, event_type, event_date, title, description, metadata)
		VALUES ($1, $2, $3, $4, $5, $6)
		RETURNING id
	`, employeeID, req.EventType, req.EventDate, req.Title, req.Description, metaJSON).Scan(&id)

	if err != nil {
		http.Error(w, `{"error":"failed to add event"}`, http.StatusInternalServerError)
		return
	}

	w.Header().Set("Content-Type", "application/json")
	w.WriteHeader(http.StatusCreated)
	json.NewEncoder(w).Encode(map[string]interface{}{
		"id":      id,
		"message": "Timeline event added",
	})
}

// ==========================================
// COMPETENCES
// ==========================================

// Competence representerar en kompetens
type Competence struct {
	ID          string `json:"id"`
	TenantID    string `json:"tenant_id"`
	Name        string `json:"name"`
	Category    string `json:"category"`
	Description string `json:"description"`
}

// EmployeeCompetence representerar en medarbetares kompetens
type EmployeeCompetence struct {
	ID            string     `json:"id"`
	EmployeeID    string     `json:"employee_id"`
	CompetenceID  string     `json:"competence_id"`
	Competence    Competence `json:"competence"`
	Level         int        `json:"level"`
	AcquiredDate  *time.Time `json:"acquired_date,omitempty"`
	ValidatedBy   *string    `json:"validated_by,omitempty"`
	ValidationDate *time.Time `json:"validation_date,omitempty"`
	Notes         string     `json:"notes"`
}

// GetCompetences returnerar kompetenser för en medarbetare
func (h *Handler) GetCompetences(w http.ResponseWriter, r *http.Request) {
	employeeID := chi.URLParam(r, "id")

	rows, err := h.DB.Query(`
		SELECT ec.id, ec.competence_id, c.name, c.category, c.description,
		       ec.level, ec.acquired_date, ec.validated_by, ec.validation_date, ec.notes
		FROM employee_competences ec
		JOIN competences c ON ec.competence_id = c.id
		WHERE ec.employee_id = $1
		ORDER BY c.category, c.name
	`, employeeID)
	if err != nil {
		http.Error(w, `{"error":"database error"}`, http.StatusInternalServerError)
		return
	}
	defer rows.Close()

	var competences []EmployeeCompetence
	for rows.Next() {
		var ec EmployeeCompetence
		var acquired, validation sql.NullTime
		var validatedBy sql.NullString
		
		rows.Scan(&ec.ID, &ec.CompetenceID, &ec.Competence.Name, &ec.Competence.Category, &ec.Competence.Description,
			&ec.Level, &acquired, &validatedBy, &validation, &ec.Notes)
		
		if acquired.Valid { ec.AcquiredDate = &acquired.Time }
		if validatedBy.Valid { ec.ValidatedBy = &validatedBy.String }
		if validation.Valid { ec.ValidationDate = &validation.Time }
		
		competences = append(competences, ec)
	}

	w.Header().Set("Content-Type", "application/json")
	json.NewEncoder(w).Encode(map[string]interface{}{
		"competences": competences,
		"total":       len(competences),
	})
}

// AddCompetence lägger till en kompetens
func (h *Handler) AddCompetence(w http.ResponseWriter, r *http.Request) {
	employeeID := chi.URLParam(r, "id")

	var req struct {
		CompetenceID string     `json:"competence_id"`
		Level        int        `json:"level"`
		AcquiredDate *time.Time `json:"acquired_date"`
		Notes        string     `json:"notes"`
	}
	if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
		http.Error(w, `{"error":"invalid request"}`, http.StatusBadRequest)
		return
	}

	var id string
	err := h.DB.QueryRow(`
		INSERT INTO employee_competences (employee_id, competence_id, level, acquired_date, notes)
		VALUES ($1, $2, $3, $4, $5)
		RETURNING id
	`, employeeID, req.CompetenceID, req.Level, req.AcquiredDate, req.Notes).Scan(&id)

	if err != nil {
		http.Error(w, `{"error":"failed to add competence"}`, http.StatusInternalServerError)
		return
	}

	// Lägg till timeline event
	h.DB.Exec(`
		INSERT INTO employee_timeline_events (employee_id, event_type, event_date, title, description)
		VALUES ($1, 'competence_added', NOW(), 'Kompetens tillagd', $2)
	`, employeeID, req.Notes)

	w.Header().Set("Content-Type", "application/json")
	w.WriteHeader(http.StatusCreated)
	json.NewEncoder(w).Encode(map[string]interface{}{
		"id":      id,
		"message": "Competence added",
	})
}

// ==========================================
// DOCUMENTS
// ==========================================

// EmployeeDocument representerar ett dokument
type EmployeeDocument struct {
	ID                 string    `json:"id"`
	EmployeeID         string    `json:"employee_id"`
	TemplateID         *string   `json:"template_id,omitempty"`
	DocumentType       string    `json:"document_type"`
	Title              string    `json:"title"`
	SignatureStatus    string    `json:"signature_status"`
	SignedByEmployeeAt *time.Time `json:"signed_by_employee_at,omitempty"`
	SignedByCompanyAt  *time.Time `json:"signed_by_company_at,omitempty"`
	Version            int       `json:"version"`
	CreatedAt          time.Time `json:"created_at"`
}

// GetDocuments returnerar dokument för en medarbetare
func (h *Handler) GetDocuments(w http.ResponseWriter, r *http.Request) {
	employeeID := chi.URLParam(r, "id")

	rows, err := h.DB.Query(`
		SELECT id, template_id, document_type, title, signature_status,
		       signed_by_employee_at, signed_by_company_at, version, created_at
		FROM employee_documents
		WHERE employee_id = $1
		ORDER BY created_at DESC
	`, employeeID)
	if err != nil {
		http.Error(w, `{"error":"database error"}`, http.StatusInternalServerError)
		return
	}
	defer rows.Close()

	var documents []EmployeeDocument
	for rows.Next() {
		var d EmployeeDocument
		var templateID sql.NullString
		var signedEmp, signedComp sql.NullTime
		
		rows.Scan(&d.ID, &templateID, &d.DocumentType, &d.Title, &d.SignatureStatus,
			&signedEmp, &signedComp, &d.Version, &d.CreatedAt)
		
		if templateID.Valid { d.TemplateID = &templateID.String }
		if signedEmp.Valid { d.SignedByEmployeeAt = &signedEmp.Time }
		if signedComp.Valid { d.SignedByCompanyAt = &signedComp.Time }
		
		documents = append(documents, d)
	}

	w.Header().Set("Content-Type", "application/json")
	json.NewEncoder(w).Encode(map[string]interface{}{
		"documents": documents,
		"total":     len(documents),
	})
}

// ==========================================
// TRAININGS
// ==========================================

// EmployeeTraining representerar en medarbetares utbildning
type EmployeeTraining struct {
	ID             string     `json:"id"`
	TrainingID     string     `json:"training_id"`
	TrainingTitle  string     `json:"training_title"`
	Status         string     `json:"status"`
	ProgressPercent int      `json:"progress_percent"`
	StartedAt      *time.Time `json:"started_at,omitempty"`
	CompletedAt    *time.Time `json:"completed_at,omitempty"`
	ExpiresAt      *time.Time `json:"expires_at,omitempty"`
	Score          *int       `json:"score,omitempty"`
	CertificateURL *string    `json:"certificate_url,omitempty"`
}

// GetTrainings returnerar utbildningar för en medarbetare
func (h *Handler) GetTrainings(w http.ResponseWriter, r *http.Request) {
	employeeID := chi.URLParam(r, "id")

	rows, err := h.DB.Query(`
		SELECT et.id, et.training_id, t.title, et.status, et.progress_percent,
		       et.started_at, et.completed_at, et.expires_at, et.score, et.certificate_url
		FROM employee_trainings et
		JOIN trainings t ON et.training_id = t.id
		WHERE et.employee_id = $1
		ORDER BY et.created_at DESC
	`, employeeID)
	if err != nil {
		http.Error(w, `{"error":"database error"}`, http.StatusInternalServerError)
		return
	}
	defer rows.Close()

	var trainings []EmployeeTraining
	for rows.Next() {
		var et EmployeeTraining
		var started, completed, expires sql.NullTime
		var score sql.NullInt64
		var certURL sql.NullString
		
		rows.Scan(&et.ID, &et.TrainingID, &et.TrainingTitle, &et.Status, &et.ProgressPercent,
			&started, &completed, &expires, &score, &certURL)
		
		if started.Valid { et.StartedAt = &started.Time }
		if completed.Valid { et.CompletedAt = &completed.Time }
		if expires.Valid { et.ExpiresAt = &expires.Time }
		if score.Valid { s := int(score.Int64); et.Score = &s }
		if certURL.Valid { et.CertificateURL = &certURL.String }
		
		trainings = append(trainings, et)
	}

	w.Header().Set("Content-Type", "application/json")
	json.NewEncoder(w).Encode(map[string]interface{}{
		"trainings": trainings,
		"total":     len(trainings),
	})
}

// ==========================================
// TASKS
// ==========================================

// EmployeeTask representerar en uppgift
type EmployeeTask struct {
	ID          string     `json:"id"`
	Title       string     `json:"title"`
	Description string     `json:"description"`
	Status      string     `json:"status"`
	Priority    string     `json:"priority"`
	DueDate     *time.Time `json:"due_date,omitempty"`
	CompletedAt *time.Time `json:"completed_at,omitempty"`
	Source      string     `json:"source"`
	AssignedAt  time.Time  `json:"assigned_at"`
}

// GetTasks returnerar uppgifter för en medarbetare
func (h *Handler) GetTasks(w http.ResponseWriter, r *http.Request) {
	employeeID := chi.URLParam(r, "id")

	rows, err := h.DB.Query(`
		SELECT id, title, description, status, priority, due_date, completed_at, source, assigned_at
		FROM employee_tasks
		WHERE employee_id = $1
		ORDER BY 
			CASE status WHEN 'pending' THEN 1 WHEN 'in_progress' THEN 2 ELSE 3 END,
			CASE priority WHEN 'critical' THEN 1 WHEN 'high' THEN 2 WHEN 'medium' THEN 3 ELSE 4 END,
			due_date ASC
	`, employeeID)
	if err != nil {
		http.Error(w, `{"error":"database error"}`, http.StatusInternalServerError)
		return
	}
	defer rows.Close()

	var tasks []EmployeeTask
	for rows.Next() {
		var t EmployeeTask
		var dueDate, completed sql.NullTime
		
		rows.Scan(&t.ID, &t.Title, &t.Description, &t.Status, &t.Priority, &dueDate, &completed, &t.Source, &t.AssignedAt)
		if dueDate.Valid { t.DueDate = &dueDate.Time }
		if completed.Valid { t.CompletedAt = &completed.Time }
		
		tasks = append(tasks, t)
	}

	w.Header().Set("Content-Type", "application/json")
	json.NewEncoder(w).Encode(map[string]interface{}{
		"tasks": tasks,
		"total": len(tasks),
	})
}

// ==========================================
// PERFORMANCE
// ==========================================

// PerformanceReview representerar en prestationsbedömning
type PerformanceReview struct {
	ID               string    `json:"id"`
	ReviewerID       string    `json:"reviewer_id"`
	ReviewPeriodStart time.Time `json:"review_period_start"`
	ReviewPeriodEnd  time.Time `json:"review_period_end"`
	ReviewType       string    `json:"review_type"`
	OverallRating    *int      `json:"overall_rating,omitempty"`
	Status           string    `json:"status"`
	CreatedAt        time.Time `json:"created_at"`
}

// GetPerformance returnerar prestationsbedömningar
func (h *Handler) GetPerformance(w http.ResponseWriter, r *http.Request) {
	employeeID := chi.URLParam(r, "id")

	rows, err := h.DB.Query(`
		SELECT id, reviewer_id, review_period_start, review_period_end, review_type, overall_rating, status, created_at
		FROM performance_reviews
		WHERE employee_id = $1
		ORDER BY review_period_end DESC
	`, employeeID)
	if err != nil {
		http.Error(w, `{"error":"database error"}`, http.StatusInternalServerError)
		return
	}
	defer rows.Close()

	var reviews []PerformanceReview
	for rows.Next() {
		var pr PerformanceReview
		var rating sql.NullInt64
		
		rows.Scan(&pr.ID, &pr.ReviewerID, &pr.ReviewPeriodStart, &pr.ReviewPeriodEnd,
			&pr.ReviewType, &rating, &pr.Status, &pr.CreatedAt)
		if rating.Valid { r := int(rating.Int64); pr.OverallRating = &r }
		
		reviews = append(reviews, pr)
	}

	w.Header().Set("Content-Type", "application/json")
	json.NewEncoder(w).Encode(map[string]interface{}{
		"reviews": reviews,
		"total":   len(reviews),
	})
}