package exportprogress

import (
	"fmt"
	"sort"
	"strings"
	"time"

	"github.com/gofiber/fiber/v2"
	"github.com/xuri/excelize/v2"
)

const (
	UnassignedKandangName = "Farm-level / Unassigned"
	jakartaTZ             = "Asia/Jakarta"
)

type Query struct {
	StartDate    time.Time
	EndDate      time.Time
	StartDateRaw string
	EndDateRaw   string
}

type Row struct {
	Module       string
	FarmName     string
	KandangName  string
	ActivityDate time.Time
	Count        int
}

type monthBlock struct {
	Start time.Time
	Weeks int
}

func IsProgressExportRequest(c *fiber.Ctx) bool {
	return strings.EqualFold(strings.TrimSpace(c.Query("export")), "excel") &&
		strings.EqualFold(strings.TrimSpace(c.Query("type")), "progress")
}

func ParseQuery(c *fiber.Ctx) (*Query, error) {
	location, err := time.LoadLocation(jakartaTZ)
	if err != nil {
		return nil, fiber.NewError(fiber.StatusInternalServerError, "failed to load timezone configuration")
	}

	startRaw := strings.TrimSpace(c.Query("start_date"))
	endRaw := strings.TrimSpace(c.Query("end_date"))
	if startRaw == "" || endRaw == "" {
		return nil, fiber.NewError(fiber.StatusBadRequest, "start_date and end_date are required")
	}

	startDate, err := time.ParseInLocation("2006-01-02", startRaw, location)
	if err != nil {
		return nil, fiber.NewError(fiber.StatusBadRequest, "start_date must use format YYYY-MM-DD")
	}
	endDate, err := time.ParseInLocation("2006-01-02", endRaw, location)
	if err != nil {
		return nil, fiber.NewError(fiber.StatusBadRequest, "end_date must use format YYYY-MM-DD")
	}
	if endDate.Before(startDate) {
		return nil, fiber.NewError(fiber.StatusBadRequest, "end_date must be greater than or equal to start_date")
	}

	return &Query{
		StartDate:    startDate,
		EndDate:      endDate,
		StartDateRaw: startRaw,
		EndDateRaw:   endRaw,
	}, nil
}

func BuildWorkbook(moduleTitle string, query *Query, rows []Row) ([]byte, error) {
	file := excelize.NewFile()
	defer file.Close()

	sheetName := moduleTitle
	defaultSheet := file.GetSheetName(file.GetActiveSheetIndex())
	if defaultSheet != sheetName {
		if err := file.SetSheetName(defaultSheet, sheetName); err != nil {
			return nil, err
		}
	}

	location, err := time.LoadLocation(jakartaTZ)
	if err != nil {
		return nil, err
	}

	titleStyle, metaStyle, monthStyle, weekStyle, dayHeaderStyle, farmStyle, textStyle, numberStyle, subtotalStyle, err := buildStyles(file)
	if err != nil {
		return nil, err
	}

	months := monthBlocksBetween(query.StartDate, query.EndDate)
	maxWeeks := 4
	for _, block := range months {
		if block.Weeks > maxWeeks {
			maxWeeks = block.Weeks
		}
	}
	lastColName, err := excelize.ColumnNumberToName(1 + (maxWeeks * 7) + 1)
	if err != nil {
		return nil, err
	}

	if err := file.MergeCell(sheetName, "A1", lastColName+"1"); err != nil {
		return nil, err
	}
	if err := file.SetCellValue(sheetName, "A1", moduleTitle); err != nil {
		return nil, err
	}
	if err := file.SetCellStyle(sheetName, "A1", lastColName+"1", titleStyle); err != nil {
		return nil, err
	}

	metaValue := fmt.Sprintf(
		"Range: %s to %s | Generated at: %s",
		query.StartDateRaw,
		query.EndDateRaw,
		time.Now().In(location).Format("2006-01-02 15:04:05 MST"),
	)
	if err := file.MergeCell(sheetName, "A2", lastColName+"2"); err != nil {
		return nil, err
	}
	if err := file.SetCellValue(sheetName, "A2", metaValue); err != nil {
		return nil, err
	}
	if err := file.SetCellStyle(sheetName, "A2", lastColName+"2", metaStyle); err != nil {
		return nil, err
	}

	if err := applyColumnWidths(file, sheetName, maxWeeks); err != nil {
		return nil, err
	}

	grouped := groupRows(rows)
	currentRow := 4
	for _, month := range months {
		lastColIndex := 1 + (month.Weeks * 7) + 1
		monthLastCol, err := excelize.ColumnNumberToName(lastColIndex)
		if err != nil {
			return nil, err
		}

		if err := renderMonthHeader(file, sheetName, currentRow, month, monthLastCol, monthStyle, weekStyle, dayHeaderStyle); err != nil {
			return nil, err
		}
		currentRow += 4

		monthData := grouped[month.Start.Format("2006-01")]
		if len(monthData) == 0 {
			if err := file.MergeCell(sheetName, "A"+fmt.Sprint(currentRow), monthLastCol+fmt.Sprint(currentRow)); err != nil {
				return nil, err
			}
			if err := file.SetCellValue(sheetName, "A"+fmt.Sprint(currentRow), "No progress data"); err != nil {
				return nil, err
			}
			if err := file.SetCellStyle(sheetName, "A"+fmt.Sprint(currentRow), monthLastCol+fmt.Sprint(currentRow), textStyle); err != nil {
				return nil, err
			}
			currentRow += 2
			continue
		}

		farms := sortedKeys(monthData)
		for _, farm := range farms {
			if err := file.MergeCell(sheetName, "A"+fmt.Sprint(currentRow), monthLastCol+fmt.Sprint(currentRow)); err != nil {
				return nil, err
			}
			if err := file.SetCellValue(sheetName, "A"+fmt.Sprint(currentRow), farm); err != nil {
				return nil, err
			}
			if err := file.SetCellStyle(sheetName, "A"+fmt.Sprint(currentRow), monthLastCol+fmt.Sprint(currentRow), farmStyle); err != nil {
				return nil, err
			}
			currentRow++

			kandangs := sortedKeys(monthData[farm])
			farmTotals := make(map[string]int)
			farmGrandTotal := 0
			for _, kandang := range kandangs {
				rowCounts := monthData[farm][kandang]
				rowTotal := 0

				if err := file.SetCellValue(sheetName, "A"+fmt.Sprint(currentRow), kandang); err != nil {
					return nil, err
				}
				if err := file.SetCellStyle(sheetName, "A"+fmt.Sprint(currentRow), "A"+fmt.Sprint(currentRow), textStyle); err != nil {
					return nil, err
				}

				for dayKey, count := range rowCounts {
					activityDate, err := time.ParseInLocation("2006-01-02", dayKey, location)
					if err != nil {
						return nil, err
					}
					colIndex := dayColumnIndex(month, activityDate)
					colName, err := excelize.ColumnNumberToName(colIndex)
					if err != nil {
						return nil, err
					}
					if err := file.SetCellValue(sheetName, colName+fmt.Sprint(currentRow), count); err != nil {
						return nil, err
					}
					if err := file.SetCellStyle(sheetName, colName+fmt.Sprint(currentRow), colName+fmt.Sprint(currentRow), numberStyle); err != nil {
						return nil, err
					}
					rowTotal += count
					farmTotals[dayKey] += count
					farmGrandTotal += count
				}

				if err := file.SetCellValue(sheetName, monthLastCol+fmt.Sprint(currentRow), rowTotal); err != nil {
					return nil, err
				}
				if err := file.SetCellStyle(sheetName, monthLastCol+fmt.Sprint(currentRow), monthLastCol+fmt.Sprint(currentRow), subtotalStyle); err != nil {
					return nil, err
				}
				if err := file.SetCellStyle(sheetName, "B"+fmt.Sprint(currentRow), prevColumn(monthLastCol)+fmt.Sprint(currentRow), numberStyle); err != nil {
					return nil, err
				}

				currentRow++
			}

			if err := file.SetCellValue(sheetName, "A"+fmt.Sprint(currentRow), "Subtotal"); err != nil {
				return nil, err
			}
			if err := file.SetCellStyle(sheetName, "A"+fmt.Sprint(currentRow), "A"+fmt.Sprint(currentRow), subtotalStyle); err != nil {
				return nil, err
			}
			for dayKey, count := range farmTotals {
				activityDate, err := time.ParseInLocation("2006-01-02", dayKey, location)
				if err != nil {
					return nil, err
				}
				colIndex := dayColumnIndex(month, activityDate)
				colName, err := excelize.ColumnNumberToName(colIndex)
				if err != nil {
					return nil, err
				}
				if err := file.SetCellValue(sheetName, colName+fmt.Sprint(currentRow), count); err != nil {
					return nil, err
				}
			}
			if err := file.SetCellValue(sheetName, monthLastCol+fmt.Sprint(currentRow), farmGrandTotal); err != nil {
				return nil, err
			}
			if err := file.SetCellStyle(sheetName, "A"+fmt.Sprint(currentRow), monthLastCol+fmt.Sprint(currentRow), subtotalStyle); err != nil {
				return nil, err
			}
			currentRow += 2
		}
	}

	if err := file.SetPanes(sheetName, &excelize.Panes{
		Freeze:      true,
		YSplit:      2,
		TopLeftCell: "A3",
		ActivePane:  "bottomLeft",
	}); err != nil {
		return nil, err
	}

	buffer, err := file.WriteToBuffer()
	if err != nil {
		return nil, err
	}
	return buffer.Bytes(), nil
}

func ParseActivityDate(value string) (time.Time, error) {
	trimmed := strings.TrimSpace(value)
	if trimmed == "" {
		return time.Time{}, fmt.Errorf("empty activity date")
	}

	layouts := []string{
		"2006-01-02",
		time.RFC3339,
		time.RFC3339Nano,
		"2006-01-02 15:04:05Z07:00",
		"2006-01-02 15:04:05.999999999Z07:00",
	}
	for _, layout := range layouts {
		if parsed, err := time.Parse(layout, trimmed); err == nil {
			return parsed, nil
		}
	}

	if len(trimmed) >= len("2006-01-02") {
		if parsed, err := time.Parse("2006-01-02", trimmed[:10]); err == nil {
			return parsed, nil
		}
	}

	return time.Time{}, fmt.Errorf("unsupported activity date format: %s", value)
}

func buildStyles(file *excelize.File) (int, int, int, int, int, int, int, int, int, error) {
	titleStyle, err := file.NewStyle(&excelize.Style{
		Font:      &excelize.Font{Bold: true, Size: 18, Color: "1F2937"},
		Fill:      excelize.Fill{Type: "pattern", Pattern: 1, Color: []string{"DCEBFA"}},
		Alignment: &excelize.Alignment{Horizontal: "center", Vertical: "center"},
	})
	if err != nil {
		return 0, 0, 0, 0, 0, 0, 0, 0, 0, err
	}
	metaStyle, err := file.NewStyle(&excelize.Style{
		Font:      &excelize.Font{Italic: true, Color: "4B5563"},
		Alignment: &excelize.Alignment{Horizontal: "left", Vertical: "center"},
	})
	if err != nil {
		return 0, 0, 0, 0, 0, 0, 0, 0, 0, err
	}
	monthStyle, err := file.NewStyle(&excelize.Style{
		Font:      &excelize.Font{Bold: true, Color: "FFFFFF"},
		Fill:      excelize.Fill{Type: "pattern", Pattern: 1, Color: []string{"1D4ED8"}},
		Alignment: &excelize.Alignment{Horizontal: "left", Vertical: "center"},
	})
	if err != nil {
		return 0, 0, 0, 0, 0, 0, 0, 0, 0, err
	}
	weekStyle, err := file.NewStyle(&excelize.Style{
		Font:      &excelize.Font{Bold: true, Color: "1F2937"},
		Fill:      excelize.Fill{Type: "pattern", Pattern: 1, Color: []string{"DBEAFE"}},
		Alignment: &excelize.Alignment{Horizontal: "center", Vertical: "center"},
		Border:    []excelize.Border{{Type: "bottom", Color: "93C5FD", Style: 1}},
	})
	if err != nil {
		return 0, 0, 0, 0, 0, 0, 0, 0, 0, err
	}
	dayHeaderStyle, err := file.NewStyle(&excelize.Style{
		Font:      &excelize.Font{Bold: true, Color: "374151"},
		Fill:      excelize.Fill{Type: "pattern", Pattern: 1, Color: []string{"EFF6FF"}},
		Alignment: &excelize.Alignment{Horizontal: "center", Vertical: "center"},
		Border: []excelize.Border{
			{Type: "left", Color: "BFDBFE", Style: 1},
			{Type: "top", Color: "BFDBFE", Style: 1},
			{Type: "bottom", Color: "BFDBFE", Style: 1},
			{Type: "right", Color: "BFDBFE", Style: 1},
		},
	})
	if err != nil {
		return 0, 0, 0, 0, 0, 0, 0, 0, 0, err
	}
	farmStyle, err := file.NewStyle(&excelize.Style{
		Font:      &excelize.Font{Bold: true, Color: "111827"},
		Fill:      excelize.Fill{Type: "pattern", Pattern: 1, Color: []string{"E5E7EB"}},
		Alignment: &excelize.Alignment{Horizontal: "left", Vertical: "center"},
	})
	if err != nil {
		return 0, 0, 0, 0, 0, 0, 0, 0, 0, err
	}
	textStyle, err := file.NewStyle(&excelize.Style{
		Alignment: &excelize.Alignment{Horizontal: "left", Vertical: "center"},
		Border: []excelize.Border{
			{Type: "left", Color: "D1D5DB", Style: 1},
			{Type: "top", Color: "D1D5DB", Style: 1},
			{Type: "bottom", Color: "D1D5DB", Style: 1},
			{Type: "right", Color: "D1D5DB", Style: 1},
		},
	})
	if err != nil {
		return 0, 0, 0, 0, 0, 0, 0, 0, 0, err
	}
	numberStyle, err := file.NewStyle(&excelize.Style{
		Alignment: &excelize.Alignment{Horizontal: "center", Vertical: "center"},
		Border: []excelize.Border{
			{Type: "left", Color: "D1D5DB", Style: 1},
			{Type: "top", Color: "D1D5DB", Style: 1},
			{Type: "bottom", Color: "D1D5DB", Style: 1},
			{Type: "right", Color: "D1D5DB", Style: 1},
		},
	})
	if err != nil {
		return 0, 0, 0, 0, 0, 0, 0, 0, 0, err
	}
	subtotalStyle, err := file.NewStyle(&excelize.Style{
		Font:      &excelize.Font{Bold: true, Color: "1F2937"},
		Fill:      excelize.Fill{Type: "pattern", Pattern: 1, Color: []string{"F3F4F6"}},
		Alignment: &excelize.Alignment{Horizontal: "center", Vertical: "center"},
		Border: []excelize.Border{
			{Type: "left", Color: "9CA3AF", Style: 1},
			{Type: "top", Color: "9CA3AF", Style: 1},
			{Type: "bottom", Color: "9CA3AF", Style: 1},
			{Type: "right", Color: "9CA3AF", Style: 1},
		},
	})
	if err != nil {
		return 0, 0, 0, 0, 0, 0, 0, 0, 0, err
	}

	return titleStyle, metaStyle, monthStyle, weekStyle, dayHeaderStyle, farmStyle, textStyle, numberStyle, subtotalStyle, nil
}

func applyColumnWidths(file *excelize.File, sheet string, maxWeeks int) error {
	if err := file.SetColWidth(sheet, "A", "A", 28); err != nil {
		return err
	}
	for col := 2; col <= 1+(maxWeeks*7); col++ {
		colName, err := excelize.ColumnNumberToName(col)
		if err != nil {
			return err
		}
		if err := file.SetColWidth(sheet, colName, colName, 6); err != nil {
			return err
		}
	}
	totalCol, err := excelize.ColumnNumberToName(1 + (maxWeeks * 7) + 1)
	if err != nil {
		return err
	}
	return file.SetColWidth(sheet, totalCol, totalCol, 10)
}

func renderMonthHeader(file *excelize.File, sheet string, startRow int, block monthBlock, monthLastCol string, monthStyle, weekStyle, dayHeaderStyle int) error {
	if err := file.MergeCell(sheet, "A"+fmt.Sprint(startRow), monthLastCol+fmt.Sprint(startRow)); err != nil {
		return err
	}
	if err := file.SetCellValue(sheet, "A"+fmt.Sprint(startRow), block.Start.Format("January 2006")); err != nil {
		return err
	}
	if err := file.SetCellStyle(sheet, "A"+fmt.Sprint(startRow), monthLastCol+fmt.Sprint(startRow), monthStyle); err != nil {
		return err
	}

	if err := file.MergeCell(sheet, "A"+fmt.Sprint(startRow+1), "A"+fmt.Sprint(startRow+3)); err != nil {
		return err
	}
	if err := file.SetCellValue(sheet, "A"+fmt.Sprint(startRow+1), "Kandang"); err != nil {
		return err
	}
	if err := file.SetCellStyle(sheet, "A"+fmt.Sprint(startRow+1), "A"+fmt.Sprint(startRow+3), dayHeaderStyle); err != nil {
		return err
	}

	totalColIndex := 1 + (block.Weeks * 7) + 1
	totalColName, err := excelize.ColumnNumberToName(totalColIndex)
	if err != nil {
		return err
	}
	if err := file.MergeCell(sheet, totalColName+fmt.Sprint(startRow+1), totalColName+fmt.Sprint(startRow+3)); err != nil {
		return err
	}
	if err := file.SetCellValue(sheet, totalColName+fmt.Sprint(startRow+1), "Total"); err != nil {
		return err
	}
	if err := file.SetCellStyle(sheet, totalColName+fmt.Sprint(startRow+1), totalColName+fmt.Sprint(startRow+3), dayHeaderStyle); err != nil {
		return err
	}

	weekdayNames := []string{"Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"}
	for week := 0; week < block.Weeks; week++ {
		startCol := 2 + (week * 7)
		endCol := startCol + 6
		startColName, err := excelize.ColumnNumberToName(startCol)
		if err != nil {
			return err
		}
		endColName, err := excelize.ColumnNumberToName(endCol)
		if err != nil {
			return err
		}
		if err := file.MergeCell(sheet, startColName+fmt.Sprint(startRow+1), endColName+fmt.Sprint(startRow+1)); err != nil {
			return err
		}
		if err := file.SetCellValue(sheet, startColName+fmt.Sprint(startRow+1), fmt.Sprintf("Week %d", week+1)); err != nil {
			return err
		}
		if err := file.SetCellStyle(sheet, startColName+fmt.Sprint(startRow+1), endColName+fmt.Sprint(startRow+1), weekStyle); err != nil {
			return err
		}

		for weekday := 0; weekday < 7; weekday++ {
			colIndex := startCol + weekday
			colName, err := excelize.ColumnNumberToName(colIndex)
			if err != nil {
				return err
			}
			if err := file.SetCellValue(sheet, colName+fmt.Sprint(startRow+2), weekdayNames[weekday]); err != nil {
				return err
			}
			if err := file.SetCellStyle(sheet, colName+fmt.Sprint(startRow+2), colName+fmt.Sprint(startRow+2), dayHeaderStyle); err != nil {
				return err
			}
		}
	}

	daysInMonth := time.Date(block.Start.Year(), block.Start.Month()+1, 0, 0, 0, 0, 0, block.Start.Location()).Day()
	for day := 1; day <= daysInMonth; day++ {
		date := time.Date(block.Start.Year(), block.Start.Month(), day, 0, 0, 0, 0, block.Start.Location())
		colIndex := dayColumnIndex(block, date)
		colName, err := excelize.ColumnNumberToName(colIndex)
		if err != nil {
			return err
		}
		if err := file.SetCellValue(sheet, colName+fmt.Sprint(startRow+3), day); err != nil {
			return err
		}
		if err := file.SetCellStyle(sheet, colName+fmt.Sprint(startRow+3), colName+fmt.Sprint(startRow+3), dayHeaderStyle); err != nil {
			return err
		}
	}

	return nil
}

func groupRows(rows []Row) map[string]map[string]map[string]map[string]int {
	grouped := make(map[string]map[string]map[string]map[string]int)
	for _, row := range rows {
		monthKey := row.ActivityDate.Format("2006-01")
		if _, exists := grouped[monthKey]; !exists {
			grouped[monthKey] = make(map[string]map[string]map[string]int)
		}
		farmName := strings.TrimSpace(row.FarmName)
		if farmName == "" {
			farmName = "Unknown Farm"
		}
		if _, exists := grouped[monthKey][farmName]; !exists {
			grouped[monthKey][farmName] = make(map[string]map[string]int)
		}
		kandangName := strings.TrimSpace(row.KandangName)
		if kandangName == "" {
			kandangName = UnassignedKandangName
		}
		if _, exists := grouped[monthKey][farmName][kandangName]; !exists {
			grouped[monthKey][farmName][kandangName] = make(map[string]int)
		}
		dayKey := row.ActivityDate.Format("2006-01-02")
		grouped[monthKey][farmName][kandangName][dayKey] += row.Count
	}
	return grouped
}

func monthBlocksBetween(startDate, endDate time.Time) []monthBlock {
	location := startDate.Location()
	current := time.Date(startDate.Year(), startDate.Month(), 1, 0, 0, 0, 0, location)
	last := time.Date(endDate.Year(), endDate.Month(), 1, 0, 0, 0, 0, location)

	blocks := make([]monthBlock, 0)
	for !current.After(last) {
		blocks = append(blocks, monthBlock{
			Start: current,
			Weeks: monthWeeks(current),
		})
		current = current.AddDate(0, 1, 0)
	}
	return blocks
}

func monthWeeks(monthStart time.Time) int {
	daysInMonth := time.Date(monthStart.Year(), monthStart.Month()+1, 0, 0, 0, 0, 0, monthStart.Location()).Day()
	offset := mondayIndex(monthStart.Weekday())
	totalSlots := offset + daysInMonth
	weeks := totalSlots / 7
	if totalSlots%7 != 0 {
		weeks++
	}
	if weeks < 4 {
		return 4
	}
	return weeks
}

func dayColumnIndex(block monthBlock, date time.Time) int {
	day := date.Day()
	offset := mondayIndex(block.Start.Weekday())
	position := offset + (day - 1)
	return 2 + position
}

func mondayIndex(weekday time.Weekday) int {
	switch weekday {
	case time.Sunday:
		return 6
	default:
		return int(weekday) - 1
	}
}

func sortedKeys[V any](input map[string]V) []string {
	keys := make([]string, 0, len(input))
	for key := range input {
		keys = append(keys, key)
	}
	sort.Strings(keys)
	return keys
}

func prevColumn(col string) string {
	index, err := excelize.ColumnNameToNumber(col)
	if err != nil || index <= 1 {
		return col
	}
	result, err := excelize.ColumnNumberToName(index - 1)
	if err != nil {
		return col
	}
	return result
}