package handlers

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

	"furry-sos-backend/config"
)

// ============================================
// 📊 DASHBOARD STATS
// ============================================

func GetDashboardStats(w http.ResponseWriter, r *http.Request) {
	var (
		totalUsers          int
		totalVolunteers     int
		totalSOS            int
		pendingSOS          int
		inProgressSOS       int
		completedSOS        int
		pendingVolunteers   int
		approvedVolunteers  int
		pendingTraining     int
		pendingFundraising  int
		approvedFundraising int
	)

	config.DB.QueryRow(`SELECT COUNT(*) FROM users`).Scan(&totalUsers)
	config.DB.QueryRow(`SELECT COUNT(*) FROM volunteers`).Scan(&totalVolunteers)
	config.DB.QueryRow(`SELECT COUNT(*) FROM volunteers WHERE application_status = 'pending'`).Scan(&pendingVolunteers)
	config.DB.QueryRow(`SELECT COUNT(*) FROM volunteers WHERE application_status = 'approved'`).Scan(&approvedVolunteers)
	config.DB.QueryRow(`SELECT COUNT(*) FROM emergency_reports`).Scan(&totalSOS)
	config.DB.QueryRow(`SELECT COUNT(*) FROM emergency_reports WHERE status = 'pending'`).Scan(&pendingSOS)
	config.DB.QueryRow(`SELECT COUNT(*) FROM emergency_reports WHERE status IN ('assigned','in_progress')`).Scan(&inProgressSOS)
	config.DB.QueryRow(`SELECT COUNT(*) FROM emergency_reports WHERE status = 'completed'`).Scan(&completedSOS)
	config.DB.QueryRow(`SELECT COUNT(*) FROM volunteer_quiz_results WHERE status = 'pending'`).Scan(&pendingTraining)
	config.DB.QueryRow(`SELECT COUNT(*) FROM shared_cases WHERE is_fundraising = TRUE`).Scan(&pendingFundraising)
	config.DB.QueryRow(`SELECT COUNT(*) FROM shared_cases WHERE is_fundraising = TRUE AND is_approved = TRUE`).Scan(&approvedFundraising)

	w.Header().Set("Content-Type", "application/json; charset=utf-8")
	json.NewEncoder(w).Encode(map[string]interface{}{
		"success": true,
		"stats": map[string]interface{}{
			"total_users":          totalUsers,
			"total_volunteers":     totalVolunteers,
			"pending_volunteers":   pendingVolunteers,
			"approved_volunteers":  approvedVolunteers,
			"total_sos":            totalSOS,
			"pending_sos":          pendingSOS,
			"in_progress_sos":      inProgressSOS,
			"completed_sos":        completedSOS,
			"pending_training":     pendingTraining,
			"pending_fundraising":  pendingFundraising,
			"approved_fundraising": approvedFundraising,
		},
	})
}

// ============================================
// 🚨 SOS CASES - List
// ✅ แก้: ดึง severity_level + province จาก deceased_reports
// ============================================

func GetAdminSOSCases(w http.ResponseWriter, r *http.Request) {
	status := r.URL.Query().Get("status")

	query := `
		SELECT
			report_id, case_code, report_type, user_id, user_name, user_phone,
			animal_type, injury_type, injury_description, severity_level,
			location_address, province, location_lat, location_lng, image_url,
			status, assigned_volunteer_id, volunteer_name, volunteer_phone, created_at
		FROM (
			SELECT
				er.report_id AS report_id,
				COALESCE(er.case_code, CONCAT('SOS-', er.report_id)) AS case_code,
				'emergency' AS report_type,
				er.user_id AS user_id,
				COALESCE(u.full_name, '') AS user_name,
				COALESCE(u.phone, '') AS user_phone,
				COALESCE(er.animal_type, '') AS animal_type,
				COALESCE(er.injury_type, '') AS injury_type,
				COALESCE(er.injury_description, '') AS injury_description,
				COALESCE(er.severity_level, 'medium') AS severity_level,
				COALESCE(er.location_address, '') AS location_address,
				COALESCE(er.province, '') AS province,
				COALESCE(er.location_lat, 0) AS location_lat,
				COALESCE(er.location_lng, 0) AS location_lng,
				COALESCE(er.image_url, '') AS image_url,
				COALESCE(er.status, 'pending') AS status,
				COALESCE(er.assigned_volunteer_id, 0) AS assigned_volunteer_id,
				COALESCE(v_u.full_name, '') AS volunteer_name,
				COALESCE(v_u.phone, '') AS volunteer_phone,
				er.created_at AS created_at
			FROM emergency_reports er
			LEFT JOIN users u ON er.user_id = u.user_id
			LEFT JOIN volunteers v ON er.assigned_volunteer_id = v.volunteer_id
			LEFT JOIN users v_u ON v.user_id = v_u.user_id

			UNION ALL

			SELECT
				dr.report_id AS report_id,
				CONCAT('BR-', LPAD(dr.report_id, 4, '0')) AS case_code,
				'deceased' AS report_type,
				dr.user_id AS user_id,
				COALESCE(u.full_name, '') AS user_name,
				COALESCE(u.phone, '') AS user_phone,
				COALESCE(dr.animal_type, '') AS animal_type,
				'จัดการซากสัตว์' AS injury_type,
				COALESCE(dr.notes, '') AS injury_description,
				COALESCE(dr.severity_level, 'low') AS severity_level,
				COALESCE(dr.location_address, '') AS location_address,
				COALESCE(dr.province, '') AS province,
				COALESCE(dr.location_lat, 0) AS location_lat,
				COALESCE(dr.location_lng, 0) AS location_lng,
				COALESCE(dr.image_url, '') AS image_url,
				COALESCE(dr.status, 'pending') AS status,
				COALESCE(dr.assigned_volunteer_id, 0) AS assigned_volunteer_id,
				COALESCE(v_u.full_name, '') AS volunteer_name,
				COALESCE(v_u.phone, '') AS volunteer_phone,
				dr.created_at AS created_at
			FROM deceased_reports dr
			LEFT JOIN users u ON dr.user_id = u.user_id
			LEFT JOIN volunteers v ON dr.assigned_volunteer_id = v.volunteer_id
			LEFT JOIN users v_u ON v.user_id = v_u.user_id
		) AS all_cases
	`

	var rows *sql.Rows
	var err error

	if status != "" && status != "all" {
		query += ` WHERE status = ? ORDER BY created_at DESC LIMIT 200`
		rows, err = config.DB.Query(query, status)
	} else {
		query += ` ORDER BY created_at DESC LIMIT 200`
		rows, err = config.DB.Query(query)
	}

	if err != nil {
		http.Error(w, "Database error: "+err.Error(), http.StatusInternalServerError)
		return
	}
	defer rows.Close()

	cases := []map[string]interface{}{}

	for rows.Next() {
		var (
			reportID, userID, assignedVolunteerID int
			caseCode, reportType                  string
			userName, userPhone                   string
			animalType, injuryType                string
			injuryDescription, severityLevel      string
			locationAddress, province             string
			locationLat, locationLng              float64
			imageURL, statusVal                   string
			volunteerName, volunteerPhone         string
			createdAt                             time.Time
		)

		err := rows.Scan(
			&reportID, &caseCode, &reportType, &userID, &userName, &userPhone,
			&animalType, &injuryType, &injuryDescription, &severityLevel,
			&locationAddress, &province, &locationLat, &locationLng, &imageURL,
			&statusVal, &assignedVolunteerID, &volunteerName, &volunteerPhone, &createdAt,
		)
		if err != nil {
			continue
		}

		cases = append(cases, map[string]interface{}{
			"report_id":             reportID,
			"case_code":             caseCode,
			"report_type":           reportType,
			"user_id":               userID,
			"user_name":             userName,
			"user_phone":            userPhone,
			"animal_type":           animalType,
			"injury_type":           injuryType,
			"injury_description":    injuryDescription,
			"severity_level":        severityLevel,
			"location_address":      locationAddress,
			"province":              province,
			"location_lat":          locationLat,
			"location_lng":          locationLng,
			"image_url":             imageURL,
			"status":                statusVal,
			"assigned_volunteer_id": assignedVolunteerID,
			"volunteer_name":        volunteerName,
			"volunteer_phone":       volunteerPhone,
			"created_at":            createdAt.Format("2006-01-02 15:04:05"),
		})
	}

	w.Header().Set("Content-Type", "application/json; charset=utf-8")
	json.NewEncoder(w).Encode(map[string]interface{}{
		"success": true,
		"count":   len(cases),
		"cases":   cases,
	})
}

// ============================================
// 🚨 SOS CASE DETAIL
// ✅ แก้: ดึง severity_level + province จาก deceased_reports
// ============================================

func GetAdminSOSCaseDetail(w http.ResponseWriter, r *http.Request) {
	reportIDStr := r.URL.Query().Get("report_id")
	if reportIDStr == "" {
		http.Error(w, "Missing report_id", http.StatusBadRequest)
		return
	}

	reportID, err := strconv.Atoi(reportIDStr)
	if err != nil {
		http.Error(w, "Invalid report_id", http.StatusBadRequest)
		return
	}

	reportType := r.URL.Query().Get("report_type")

	if reportType == "" {
		var count int
		_ = config.DB.QueryRow(`SELECT COUNT(*) FROM deceased_reports WHERE report_id = ?`, reportID).Scan(&count)
		if count > 0 {
			reportType = "deceased"
		} else {
			reportType = "emergency"
		}
	}

	var (
		caseCode, userName, userPhone            string
		animalType, injuryType, injuryDesc       string
		severityLevel, locationAddress, province string
		imageURL, status, volunteerName          string
		volunteerPhone                           string
		userID, assignedVolunteerID              int
		locationLat, locationLng                 float64
		createdAt                                time.Time
	)

	if reportType == "deceased" {
		query := `
			SELECT
				CONCAT('BR-', LPAD(dr.report_id, 4, '0')) AS case_code,
				dr.user_id,
				COALESCE(u.full_name, ''), COALESCE(u.phone, ''),
				COALESCE(dr.animal_type, ''), 'จัดการซากสัตว์',
				COALESCE(dr.notes, ''),
				COALESCE(dr.severity_level, 'low'),
				COALESCE(dr.location_address, ''),
				COALESCE(dr.province, ''),
				COALESCE(dr.location_lat, 0), COALESCE(dr.location_lng, 0),
				COALESCE(dr.image_url, ''), COALESCE(dr.status, 'pending'),
				COALESCE(dr.assigned_volunteer_id, 0),
				COALESCE(v_u.full_name, ''), COALESCE(v_u.phone, ''),
				dr.created_at
			FROM deceased_reports dr
			LEFT JOIN users u ON dr.user_id = u.user_id
			LEFT JOIN volunteers v ON dr.assigned_volunteer_id = v.volunteer_id
			LEFT JOIN users v_u ON v.user_id = v_u.user_id
			WHERE dr.report_id = ? LIMIT 1
		`
		err = config.DB.QueryRow(query, reportID).Scan(
			&caseCode, &userID, &userName, &userPhone,
			&animalType, &injuryType, &injuryDesc, &severityLevel,
			&locationAddress, &province, &locationLat, &locationLng,
			&imageURL, &status, &assignedVolunteerID,
			&volunteerName, &volunteerPhone, &createdAt,
		)
	} else {
		query := `
			SELECT
				er.case_code, er.user_id,
				COALESCE(u.full_name, ''), COALESCE(u.phone, ''),
				COALESCE(er.animal_type, ''), COALESCE(er.injury_type, ''),
				COALESCE(er.injury_description, ''), COALESCE(er.severity_level, 'moderate'),
				COALESCE(er.location_address, ''), COALESCE(er.province, ''),
				COALESCE(er.location_lat, 0), COALESCE(er.location_lng, 0),
				COALESCE(er.image_url, ''), COALESCE(er.status, 'pending'),
				COALESCE(er.assigned_volunteer_id, 0),
				COALESCE(v_u.full_name, ''), COALESCE(v_u.phone, ''),
				er.created_at
			FROM emergency_reports er
			LEFT JOIN users u ON er.user_id = u.user_id
			LEFT JOIN volunteers v ON er.assigned_volunteer_id = v.volunteer_id
			LEFT JOIN users v_u ON v.user_id = v_u.user_id
			WHERE er.report_id = ? LIMIT 1
		`
		err = config.DB.QueryRow(query, reportID).Scan(
			&caseCode, &userID, &userName, &userPhone,
			&animalType, &injuryType, &injuryDesc, &severityLevel,
			&locationAddress, &province, &locationLat, &locationLng,
			&imageURL, &status, &assignedVolunteerID,
			&volunteerName, &volunteerPhone, &createdAt,
		)
	}

	if err != nil {
		http.Error(w, "Case not found: "+err.Error(), http.StatusNotFound)
		return
	}

	w.Header().Set("Content-Type", "application/json; charset=utf-8")
	json.NewEncoder(w).Encode(map[string]interface{}{
		"success": true,
		"case": map[string]interface{}{
			"report_id":             reportID,
			"case_code":             caseCode,
			"report_type":           reportType,
			"user_id":               userID,
			"user_name":             userName,
			"user_phone":            userPhone,
			"animal_type":           animalType,
			"injury_type":           injuryType,
			"injury_description":    injuryDesc,
			"severity_level":        severityLevel,
			"location_address":      locationAddress,
			"province":              province,
			"location_lat":          locationLat,
			"location_lng":          locationLng,
			"image_url":             imageURL,
			"status":                status,
			"assigned_volunteer_id": assignedVolunteerID,
			"volunteer_name":        volunteerName,
			"volunteer_phone":       volunteerPhone,
			"created_at":            createdAt.Format("2006-01-02 15:04:05"),
		},
	})
}

// ============================================
// 👤 USERS
// ============================================

func GetAdminUsers(w http.ResponseWriter, r *http.Request) {
	role := r.URL.Query().Get("role")

	query := `
		SELECT
			user_id, username, full_name, email, phone,
			COALESCE(role, 'user'), COALESCE(is_verified, 0),
			COALESCE(profile_image, ''), created_at
		FROM users
	`

	var rows *sql.Rows
	var err error

	if role != "" && role != "all" {
		query += ` WHERE role = ? ORDER BY created_at DESC LIMIT 500`
		rows, err = config.DB.Query(query, role)
	} else {
		query += ` ORDER BY created_at DESC LIMIT 500`
		rows, err = config.DB.Query(query)
	}

	if err != nil {
		http.Error(w, "Database error: "+err.Error(), http.StatusInternalServerError)
		return
	}
	defer rows.Close()

	users := []map[string]interface{}{}

	for rows.Next() {
		var (
			userID       int
			username     string
			fullName     string
			email        string
			phone        string
			roleVal      string
			isVerified   bool
			profileImage string
			createdAt    time.Time
		)

		err := rows.Scan(
			&userID, &username, &fullName, &email, &phone,
			&roleVal, &isVerified, &profileImage, &createdAt,
		)
		if err != nil {
			continue
		}

		users = append(users, map[string]interface{}{
			"user_id":       userID,
			"username":      username,
			"full_name":     fullName,
			"email":         email,
			"phone":         phone,
			"role":          roleVal,
			"is_verified":   isVerified,
			"profile_image": profileImage,
			"created_at":    createdAt.Format("2006-01-02 15:04:05"),
		})
	}

	w.Header().Set("Content-Type", "application/json; charset=utf-8")
	json.NewEncoder(w).Encode(map[string]interface{}{
		"success": true,
		"count":   len(users),
		"users":   users,
	})
}

func GetAdminUserHistory(w http.ResponseWriter, r *http.Request) {
	userIDStr := r.URL.Query().Get("user_id")
	if userIDStr == "" {
		http.Error(w, "Missing user_id", http.StatusBadRequest)
		return
	}

	userID, err := strconv.Atoi(userIDStr)
	if err != nil {
		http.Error(w, "Invalid user_id", http.StatusBadRequest)
		return
	}

	query := `
		SELECT
			report_id, case_code,
			animal_type, COALESCE(injury_description, ''),
			COALESCE(location_address, ''), COALESCE(province, ''),
			COALESCE(status, 'pending'), created_at
		FROM emergency_reports
		WHERE user_id = ?
		ORDER BY created_at DESC
	`

	rows, err := config.DB.Query(query, userID)
	if err != nil {
		http.Error(w, "Database error: "+err.Error(), http.StatusInternalServerError)
		return
	}
	defer rows.Close()

	reports := []map[string]interface{}{}

	for rows.Next() {
		var (
			reportID          int
			caseCode          string
			animalType        string
			injuryDescription string
			locationAddress   string
			province          string
			status            string
			createdAt         time.Time
		)

		err := rows.Scan(&reportID, &caseCode, &animalType, &injuryDescription, &locationAddress, &province, &status, &createdAt)
		if err != nil {
			continue
		}

		reports = append(reports, map[string]interface{}{
			"report_id":          reportID,
			"case_code":          caseCode,
			"animal_type":        animalType,
			"injury_description": injuryDescription,
			"location_address":   locationAddress,
			"province":           province,
			"status":             status,
			"created_at":         createdAt.Format("2006-01-02 15:04:05"),
		})
	}

	w.Header().Set("Content-Type", "application/json; charset=utf-8")
	json.NewEncoder(w).Encode(map[string]interface{}{
		"success": true,
		"count":   len(reports),
		"reports": reports,
	})
}

// ============================================
// 📈 REPORTS
// ============================================

func GetAdminReports(w http.ResponseWriter, r *http.Request) {
	var (
		totalSOS, completedSOS, pendingSOS, inProgressSOS int
		totalVolunteers, activeVolunteers                 int
		totalUsers                                        int
		totalFundraising                                  float64
	)

	err := config.DB.QueryRow(`
		SELECT COUNT(*) FROM (
			SELECT report_id FROM emergency_reports
			UNION ALL
			SELECT report_id FROM deceased_reports
		) AS all_reports
	`).Scan(&totalSOS)

	if err != nil {
		http.Error(w, "โหลดจำนวนเคสทั้งหมดไม่สำเร็จ: "+err.Error(), http.StatusInternalServerError)
		return
	}

	type StatusCount struct {
		Status string
		Count  int
	}

	statusCounts := map[string]int{}

	rows, err := config.DB.Query(`
		SELECT final_status, COUNT(*) FROM (
			SELECT
				CASE
					WHEN COALESCE(er.status, 'pending') = 'completed' THEN 'completed'
					WHEN EXISTS (
						SELECT 1 FROM case_assignments ca
						WHERE ca.report_id = er.report_id
						  AND ca.report_type = 'emergency'
						  AND ca.status = 'completed'
					) THEN 'completed'
					ELSE COALESCE(
						(SELECT ca.status FROM case_assignments ca
						 WHERE ca.report_id = er.report_id
						   AND ca.report_type = 'emergency'
						 ORDER BY ca.updated_at DESC, ca.assignment_id DESC LIMIT 1),
						COALESCE(er.status, 'pending')
					)
				END AS final_status
			FROM emergency_reports er

			UNION ALL

			SELECT
				CASE
					WHEN COALESCE(dr.status, 'pending') = 'completed' THEN 'completed'
					WHEN EXISTS (
						SELECT 1 FROM case_assignments ca
						WHERE ca.report_id = dr.report_id
						  AND ca.report_type = 'deceased'
						  AND ca.status = 'completed'
					) THEN 'completed'
					ELSE COALESCE(
						(SELECT ca.status FROM case_assignments ca
						 WHERE ca.report_id = dr.report_id
						   AND ca.report_type = 'deceased'
						 ORDER BY ca.updated_at DESC, ca.assignment_id DESC LIMIT 1),
						COALESCE(dr.status, 'pending')
					)
				END AS final_status
			FROM deceased_reports dr
		) AS all_cases
		GROUP BY final_status
	`)
	if err == nil {
		defer rows.Close()
		for rows.Next() {
			var sc StatusCount
			if rows.Scan(&sc.Status, &sc.Count) == nil {
				statusCounts[sc.Status] = sc.Count
			}
		}
	}

	completedSOS = statusCounts["completed"]
	pendingSOS = statusCounts["pending"]
	inProgressSOS = statusCounts["accepted"] +
		statusCounts["assigned"] +
		statusCounts["arriving"] +
		statusCounts["arrived"] +
		statusCounts["in_progress"] +
		statusCounts["collected"]

	config.DB.QueryRow(`SELECT COUNT(*) FROM volunteers`).Scan(&totalVolunteers)
	config.DB.QueryRow(`SELECT COUNT(*) FROM volunteers WHERE is_online = TRUE`).Scan(&activeVolunteers)
	config.DB.QueryRow(`SELECT COUNT(*) FROM users`).Scan(&totalUsers)
	config.DB.QueryRow(`SELECT COALESCE(SUM(fundraising_goal), 0) FROM shared_cases WHERE is_approved = TRUE`).Scan(&totalFundraising)

	type MonthlyStat struct {
		Month string `json:"month"`
		Count int    `json:"count"`
	}

	monthlyStats := []MonthlyStat{}

	rowsM, err := config.DB.Query(`
		SELECT month, COUNT(*) AS count FROM (
			SELECT DATE_FORMAT(created_at, '%Y-%m') AS month
			FROM emergency_reports
			WHERE created_at >= DATE_SUB(NOW(), INTERVAL 6 MONTH)

			UNION ALL

			SELECT DATE_FORMAT(created_at, '%Y-%m') AS month
			FROM deceased_reports
			WHERE created_at >= DATE_SUB(NOW(), INTERVAL 6 MONTH)
		) AS all_months
		GROUP BY month
		ORDER BY month ASC
	`)

	if err == nil {
		defer rowsM.Close()
		for rowsM.Next() {
			var ms MonthlyStat
			if rowsM.Scan(&ms.Month, &ms.Count) == nil {
				monthlyStats = append(monthlyStats, ms)
			}
		}
	}

	type AnimalStat struct {
		AnimalType string `json:"animal_type"`
		Count      int    `json:"count"`
	}

	animalStats := []AnimalStat{}

	rowsA, err := config.DB.Query(`
		SELECT animal_type, COUNT(*) AS count FROM (
			SELECT COALESCE(NULLIF(animal_type, ''), 'ไม่ระบุ') AS animal_type
			FROM emergency_reports

			UNION ALL

			SELECT COALESCE(NULLIF(animal_type, ''), 'ไม่ระบุ') AS animal_type
			FROM deceased_reports
		) AS all_animals
		GROUP BY animal_type
		ORDER BY count DESC
		LIMIT 10
	`)

	if err == nil {
		defer rowsA.Close()
		for rowsA.Next() {
			var as AnimalStat
			if rowsA.Scan(&as.AnimalType, &as.Count) == nil {
				animalStats = append(animalStats, as)
			}
		}
	}

	type SevStat struct {
		Severity string `json:"severity"`
		Count    int    `json:"count"`
	}

	sevStats := []SevStat{}

	rowsSev, err := config.DB.Query(`
		SELECT 
			CASE 
				WHEN LOWER(TRIM(COALESCE(severity_level, ''))) IN ('critical','emergency','วิกฤต','ด่วนมาก','เร่งด่วนมาก') THEN 'critical'
				WHEN LOWER(TRIM(COALESCE(severity_level, ''))) IN ('high','severe','urgent','สูง','ด่วน','เร่งด่วน','รุนแรง') THEN 'high'
				WHEN LOWER(TRIM(COALESCE(severity_level, ''))) IN ('medium','moderate','ปานกลาง') THEN 'medium'
				WHEN LOWER(TRIM(COALESCE(severity_level, ''))) IN ('low','normal','ต่ำ','ปกติ','เล็กน้อย') THEN 'low'
				ELSE 'medium'
			END AS sev,
			COUNT(*) AS cnt
		FROM emergency_reports
		GROUP BY sev
		ORDER BY 
			CASE sev 
				WHEN 'critical' THEN 1 
				WHEN 'high' THEN 2 
				WHEN 'medium' THEN 3 
				ELSE 4 
			END
	`)

	if err == nil {
		defer rowsSev.Close()
		for rowsSev.Next() {
			var s SevStat
			if rowsSev.Scan(&s.Severity, &s.Count) == nil {
				sevStats = append(sevStats, s)
			}
		}
	}

	type CatStat struct {
		InjuryType string `json:"injury_type"`
		Count      int    `json:"count"`
	}

	catStats := []CatStat{}

	rowsCat, err := config.DB.Query(`
		SELECT 
			COALESCE(NULLIF(TRIM(injury_type), ''), 'อื่นๆ') AS itype,
			COUNT(*) AS cnt
		FROM emergency_reports
		GROUP BY itype
		ORDER BY cnt DESC
		LIMIT 6
	`)

	if err == nil {
		defer rowsCat.Close()
		for rowsCat.Next() {
			var c CatStat
			if rowsCat.Scan(&c.InjuryType, &c.Count) == nil {
				catStats = append(catStats, c)
			}
		}
	}

	type TopVol struct {
		VolunteerID       int     `json:"volunteer_id"`
		FullName          string  `json:"full_name"`
		Username          string  `json:"username"`
		Phone             string  `json:"phone"`
		WorkingArea       string  `json:"working_area"`
		CompletedMissions int     `json:"completed_missions"`
		AvgRating         float64 `json:"avg_rating"`
		TierLevel         string  `json:"tier_level"`
	}

	topVols := []TopVol{}

	rowsVol, err := config.DB.Query(`
		SELECT 
			v.volunteer_id,
			COALESCE(u.full_name, '') AS full_name,
			COALESCE(u.username, '') AS username,
			COALESCE(u.phone, '') AS phone,
			COALESCE(v.working_area, '') AS working_area,
			(
				SELECT COUNT(*) FROM case_assignments ca 
				WHERE ca.volunteer_id = v.volunteer_id 
				  AND ca.status = 'completed'
			) AS completed_missions,
			COALESCE(
				(
					SELECT AVG(rating) FROM volunteer_reviews 
					WHERE volunteer_id = v.volunteer_id 
					  AND rating > 0
				),
				0
			) AS avg_rating,
			COALESCE(v.tier_level, 'basic') AS tier_level
		FROM volunteers v
		INNER JOIN users u ON v.user_id = u.user_id
		WHERE v.is_approved = 1
		ORDER BY completed_missions DESC, avg_rating DESC
		LIMIT 5
	`)

	if err == nil {
		defer rowsVol.Close()
		for rowsVol.Next() {
			var t TopVol
			if rowsVol.Scan(&t.VolunteerID, &t.FullName, &t.Username, &t.Phone,
				&t.WorkingArea, &t.CompletedMissions, &t.AvgRating, &t.TierLevel) == nil {
				topVols = append(topVols, t)
			}
		}
	}

	w.Header().Set("Content-Type", "application/json; charset=utf-8")

	json.NewEncoder(w).Encode(map[string]interface{}{
		"success": true,
		"summary": map[string]interface{}{
			"total_sos":         totalSOS,
			"completed_sos":     completedSOS,
			"pending_sos":       pendingSOS,
			"in_progress":       inProgressSOS,
			"total_volunteers":  totalVolunteers,
			"active_volunteers": activeVolunteers,
			"total_users":       totalUsers,
			"total_fundraising": totalFundraising,
		},
		"monthly_stats":  monthlyStats,
		"animal_stats":   animalStats,
		"severity_stats": sevStats,
		"category_stats": catStats,
		"top_volunteers": topVols,
	})
}