package pso

import (
	"bytes"
	"errors"
	"fmt"
	"strconv"
	"strings"
	"system-altrak/internal/domain"
	"system-altrak/internal/modules/job"
	setModule "system-altrak/internal/modules/setting"
	"system-altrak/internal/service"
	"system-altrak/pkg/utils"
	"time"

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

var (
	ErrRecordLocked         = errors.New("authorized records cannot be modified")
	ErrNoExportData         = errors.New("no records available for export")
	ErrInvalidCutoffDate    = errors.New("invalid cutoff format, expected yyyy-mm-dd")
	ErrInvalidExportSearch  = errors.New("search query is too long")
	ErrPDFEngineUnavailable = errors.New("pdf engine is not available")
)

type PSOService interface {
	ListOutstandingRecords(page, limit int, search string, branchID uint, startDate, endDate, aging string) ([]domain.OperationalRecord, int64, error)
	UpdateRemark(id uint, remark string, branchID uint, role string) error
	UpdateRecord(id uint, data map[string]interface{}, branchID uint, role string) error
	GetByID(id uint, branchID uint) (*domain.OperationalRecord, error)
	Verify(id uint, branchID uint, userID uint) error
	BulkVerify(ids []uint, branchID uint, userID uint) error
	ImportExcel(filePath string, branchID uint, jobID uint) (ImportExcelSummary, error)
	ExportExcel(search string, cutoffStr string, branchID uint) ([]byte, error)
	ExportPdf(search string, cutoffStr string, branchID uint) ([]byte, error)
	Delete(id uint, branchID uint) error
}

type serviceImpl struct {
	repo    PSORepository
	pdf     *utils.PdfGenerator
	notif   service.NotificationService
	job     job.JobService
	setting setModule.SettingService
}

func NewService(repo PSORepository, pdf *utils.PdfGenerator, notif service.NotificationService, job job.JobService, setting ...setModule.SettingService) PSOService {
	var resolvedSetting setModule.SettingService
	if len(setting) > 0 {
		resolvedSetting = setting[0]
	}

	return &serviceImpl{repo: repo, pdf: pdf, notif: notif, job: job, setting: resolvedSetting}
}

func (s *serviceImpl) ListOutstandingRecords(page, limit int, search string, branchID uint, startDate, endDate, aging string) ([]domain.OperationalRecord, int64, error) {
	return s.repo.ListOutstandingRecords(page, limit, search, branchID, startDate, endDate, aging)
}

func (s *serviceImpl) GetByID(id uint, branchID uint) (*domain.OperationalRecord, error) {
	return s.repo.GetRecordByID(id, branchID)
}

func isSuperadminRole(role string) bool {
	return strings.EqualFold(strings.TrimSpace(role), "superadmin")
}

func (s *serviceImpl) UpdateRemark(id uint, remark string, branchID uint, role string) error {
	record, err := s.repo.GetRecordByID(id, branchID)
	if err != nil {
		return err
	}
	if record.IsVerified && !isSuperadminRole(role) {
		return ErrRecordLocked
	}
	record.Remark = remark
	return s.repo.UpdateRecord(record)
}

func (s *serviceImpl) UpdateRecord(id uint, data map[string]interface{}, branchID uint, role string) error {
	record, err := s.repo.GetRecordByID(id, branchID)
	if err != nil {
		return err
	}

	// Superadmin may override verified records when a manual correction is required.
	if record.IsVerified && !isSuperadminRole(role) {
		return ErrRecordLocked
	}

	if val, ok := data["pso_no"].(string); ok && val != "" {
		record.PsoNo = val
	}
	if val, ok := data["po_no"].(string); ok {
		record.PoNo = val
	}
	if val, ok := data["customer_name"].(string); ok && val != "" {
		record.CustomerName = val
	}
	if val, ok := data["currency"].(string); ok {
		record.Currency = val
	}
	if val, ok := data["remark"].(string); ok {
		record.Remark = val
	}

	if val, ok := extractNumericField(data["top"]); ok {
		record.TOP = int64(val)
	}
	if val, ok := extractNumericField(data["days"]); ok {
		record.Days = int64(val)
	}
	if val, ok := extractNumericField(data["discount"]); ok {
		record.Discount = val
	}

	if val, ok := extractNumericField(data["amount"]); ok {
		record.Amount = int64(val * 100)
		record.AmountIDR = record.Amount // Assumption: Base is IDR or synced
	}

	if val, ok := data["pso_date"].(string); ok {
		val = strings.TrimSpace(val)
		if val == "" {
			record.PsoDate = nil
		} else if t, err := time.Parse("2006-01-02", val); err == nil {
			record.PsoDate = &t
		}
	}

	return s.repo.UpdateRecord(record)
}

func extractNumericField(value interface{}) (float64, bool) {
	parseLooseNumeric := func(raw string) (float64, bool) {
		cleaned := strings.TrimSpace(raw)
		if cleaned == "" {
			return 0, false
		}
		cleaned = strings.ReplaceAll(cleaned, " ", "")
		cleaned = strings.ReplaceAll(cleaned, ",", "")
		cleaned = strings.ReplaceAll(cleaned, "Rp", "")
		cleaned = strings.ReplaceAll(cleaned, "rp", "")
		parsed, err := strconv.ParseFloat(cleaned, 64)
		if err != nil {
			return 0, false
		}
		return parsed, true
	}

	switch v := value.(type) {
	case float64:
		return v, true
	case float32:
		return float64(v), true
	case int:
		return float64(v), true
	case int64:
		return float64(v), true
	case int32:
		return float64(v), true
	case uint:
		return float64(v), true
	case uint64:
		return float64(v), true
	case uint32:
		return float64(v), true
	case string:
		return parseLooseNumeric(v)
	default:
		return 0, false
	}
}

func validateExportSearch(search string) error {
	if len(search) > 255 {
		return ErrInvalidExportSearch
	}

	return nil
}

func normalizeCutoffDate(cutoffStr string) (string, error) {
	trimmed := strings.TrimSpace(cutoffStr)
	if trimmed == "" {
		return time.Now().Format("02/01/2006"), nil
	}

	t, err := time.Parse("2006-01-02", trimmed)
	if err != nil {
		return "", ErrInvalidCutoffDate
	}

	return t.Format("02/01/2006"), nil
}

func (s *serviceImpl) Verify(id uint, branchID uint, userID uint) error {
	err := s.repo.VerifyRecord(id, branchID, userID)
	if err == nil {
		s.notif.SendApprovalNotification("PSO & PO Status", strconv.Itoa(int(id)), strconv.Itoa(int(userID)))
	}
	return err
}

func (s *serviceImpl) BulkVerify(ids []uint, branchID uint, userID uint) error {
	for _, id := range ids {
		if err := s.repo.VerifyRecord(id, branchID, userID); err != nil {
			return err
		}
		s.notif.SendApprovalNotification("PSO & PO Status", strconv.Itoa(int(id)), strconv.Itoa(int(userID)))
	}
	return nil
}

func safeCell(row []string, idx int) string {
	if idx >= 0 && idx < len(row) {
		return strings.Trim(strings.TrimSpace(row[idx]), "'\"")
	}
	return ""
}

func (s *serviceImpl) ImportExcel(filePath string, branchID uint, jobID uint) (ImportExcelSummary, error) {
	if jobID > 0 && s.job != nil {
		_ = s.job.StartJob(jobID)
	}

	rows, err := loadExcelRows(filePath)
	if err != nil {
		if jobID > 0 && s.job != nil {
			_ = s.job.FailJob(jobID, "Failed to initialize Excel document packet: "+err.Error())
		}
		return ImportExcelSummary{}, err
	}

	var summary ImportExcelSummary
	// Bungkus proses import ke dalam satu database transaction
	err = s.repo.WithTransaction(func(txRepo PSORepository) error {
		summary = s.importExcelRowsWithRepo(txRepo, rows, branchID, jobID)
		return nil
	})

	if err != nil {
		if jobID > 0 && s.job != nil {
			_ = s.job.FailJob(jobID, "Database transaction failed during import: "+err.Error())
		}
		return ImportExcelSummary{}, err
	}

	if jobID > 0 && s.job != nil {
		_ = s.job.CompleteJob(jobID)
	}

	return summary, nil
}

func (s *serviceImpl) ExportExcel(search string, cutoffStr string, branchID uint) ([]byte, error) {
	search = strings.TrimSpace(search)
	if err := validateExportSearch(search); err != nil {
		return nil, err
	}

	normalizedCutoff, err := normalizeCutoffDate(cutoffStr)
	if err != nil {
		return nil, err
	}
	generatedAt := time.Now().Format("1/2/06, 3:04 PM")

	f := excelize.NewFile()
	defer f.Close()
	sheet := "Report"
	f.SetSheetName("Sheet1", sheet)

	headers := []string{"N", "PSO No.", "Date", "PO No.", "Customer", "TOP", "Disc", "Currency", "Amount", "Days", "Remark"}

	titleStyle, err := f.NewStyle(&excelize.Style{
		Font:      &excelize.Font{Bold: true, Size: 16},
		Alignment: &excelize.Alignment{Horizontal: "center"},
	})
	if err != nil {
		return nil, fmt.Errorf("failed to build excel title style: %w", err)
	}
	subtitleStyle, err := f.NewStyle(&excelize.Style{
		Font:      &excelize.Font{Bold: true, Size: 10},
		Alignment: &excelize.Alignment{Horizontal: "center"},
	})
	if err != nil {
		return nil, fmt.Errorf("failed to build excel subtitle style: %w", err)
	}
	metaStyle, err := f.NewStyle(&excelize.Style{
		Font:      &excelize.Font{Bold: true, Size: 9},
		Alignment: &excelize.Alignment{Horizontal: "left"},
	})
	if err != nil {
		return nil, fmt.Errorf("failed to build excel meta style: %w", err)
	}
	headStyle, err := f.NewStyle(&excelize.Style{
		Fill:      excelize.Fill{Type: "pattern", Color: []string{"111827"}, Pattern: 1},
		Font:      &excelize.Font{Bold: true, Color: "FFFFFF"},
		Border:    []excelize.Border{{Type: "left", Color: "D1D5DB", Style: 1}, {Type: "right", Color: "D1D5DB", Style: 1}, {Type: "top", Color: "D1D5DB", Style: 1}, {Type: "bottom", Color: "D1D5DB", Style: 1}},
		Alignment: &excelize.Alignment{Horizontal: "center", Vertical: "center"},
	})
	if err != nil {
		return nil, fmt.Errorf("failed to build excel table header style: %w", err)
	}
	cellStyle, err := f.NewStyle(&excelize.Style{
		Border:    []excelize.Border{{Type: "left", Color: "D1D5DB", Style: 1}, {Type: "right", Color: "D1D5DB", Style: 1}, {Type: "top", Color: "D1D5DB", Style: 1}, {Type: "bottom", Color: "D1D5DB", Style: 1}},
		Alignment: &excelize.Alignment{Vertical: "top"},
	})
	if err != nil {
		return nil, fmt.Errorf("failed to build excel cell style: %w", err)
	}
	centerCellStyle, err := f.NewStyle(&excelize.Style{
		Border:    []excelize.Border{{Type: "left", Color: "D1D5DB", Style: 1}, {Type: "right", Color: "D1D5DB", Style: 1}, {Type: "top", Color: "D1D5DB", Style: 1}, {Type: "bottom", Color: "D1D5DB", Style: 1}},
		Alignment: &excelize.Alignment{Horizontal: "center", Vertical: "top"},
	})
	if err != nil {
		return nil, fmt.Errorf("failed to build excel center cell style: %w", err)
	}
	amountStyle, err := f.NewStyle(&excelize.Style{
		Border:       []excelize.Border{{Type: "left", Color: "D1D5DB", Style: 1}, {Type: "right", Color: "D1D5DB", Style: 1}, {Type: "top", Color: "D1D5DB", Style: 1}, {Type: "bottom", Color: "D1D5DB", Style: 1}},
		Alignment:    &excelize.Alignment{Horizontal: "right", Vertical: "top"},
		CustomNumFmt: func() *string { s := "#,##0"; return &s }(),
	})
	if err != nil {
		return nil, fmt.Errorf("failed to build excel amount style: %w", err)
	}

	if err := f.SetColWidth(sheet, "A", "A", 5); err != nil {
		return nil, fmt.Errorf("failed to set excel width: %w", err)
	}
	if err := f.SetColWidth(sheet, "B", "B", 14); err != nil {
		return nil, fmt.Errorf("failed to set excel width: %w", err)
	}
	if err := f.SetColWidth(sheet, "C", "C", 12); err != nil {
		return nil, fmt.Errorf("failed to set excel width: %w", err)
	}
	if err := f.SetColWidth(sheet, "D", "D", 14); err != nil {
		return nil, fmt.Errorf("failed to set excel width: %w", err)
	}
	if err := f.SetColWidth(sheet, "E", "E", 24); err != nil {
		return nil, fmt.Errorf("failed to set excel width: %w", err)
	}
	if err := f.SetColWidth(sheet, "F", "G", 8); err != nil {
		return nil, fmt.Errorf("failed to set excel width: %w", err)
	}
	if err := f.SetColWidth(sheet, "H", "H", 10); err != nil {
		return nil, fmt.Errorf("failed to set excel width: %w", err)
	}
	if err := f.SetColWidth(sheet, "I", "I", 14); err != nil {
		return nil, fmt.Errorf("failed to set excel width: %w", err)
	}
	if err := f.SetColWidth(sheet, "J", "J", 8); err != nil {
		return nil, fmt.Errorf("failed to set excel width: %w", err)
	}
	if err := f.SetColWidth(sheet, "K", "K", 22); err != nil {
		return nil, fmt.Errorf("failed to set excel width: %w", err)
	}

	// Header blocks matching the browser-print layout.
	f.SetCellValue(sheet, "A1", "PO Status Monitoring")
	if err := f.MergeCell(sheet, "A1", "F1"); err != nil {
		return nil, fmt.Errorf("failed to merge excel title: %w", err)
	}
	f.SetCellStyle(sheet, "A1", "A1", titleStyle)

	f.SetCellValue(sheet, "A2", "Print langsung dari data yang sedang tampil di layar.")
	if err := f.MergeCell(sheet, "A2", "F2"); err != nil {
		return nil, fmt.Errorf("failed to merge excel subtitle: %w", err)
	}
	f.SetCellStyle(sheet, "A2", "A2", subtitleStyle)

	dataCountCell := "A3"
	f.SetCellValue(sheet, dataCountCell, fmt.Sprintf("Data: %d baris", 0))
	if err := f.MergeCell(sheet, dataCountCell, "F3"); err != nil {
		return nil, fmt.Errorf("failed to merge excel data count: %w", err)
	}
	f.SetCellStyle(sheet, dataCountCell, dataCountCell, metaStyle)

	generatedCell := "A4"
	f.SetCellValue(sheet, generatedCell, fmt.Sprintf("Generated: %s", generatedAt))
	if err := f.MergeCell(sheet, generatedCell, "F4"); err != nil {
		return nil, fmt.Errorf("failed to merge excel generated timestamp: %w", err)
	}
	f.SetCellStyle(sheet, generatedCell, generatedCell, metaStyle)

	cutoffCell := "H1"
	f.SetCellValue(sheet, cutoffCell, "CUTOFF")
	if err := f.MergeCell(sheet, cutoffCell, "K1"); err != nil {
		return nil, fmt.Errorf("failed to merge excel cutoff label: %w", err)
	}
	f.SetCellStyle(sheet, cutoffCell, cutoffCell, metaStyle)

	cutoffValueCell := "H2"
	f.SetCellValue(sheet, cutoffValueCell, normalizedCutoff)
	if err := f.MergeCell(sheet, cutoffValueCell, "K2"); err != nil {
		return nil, fmt.Errorf("failed to merge excel cutoff value: %w", err)
	}
	f.SetCellStyle(sheet, cutoffValueCell, cutoffValueCell, metaStyle)

	scopeLabelCell := "H3"
	scopeValue := "All Records"
	if search != "" {
		scopeValue = fmt.Sprintf("Search: %s", search)
	}
	f.SetCellValue(sheet, scopeLabelCell, "SCOPE")
	if err := f.MergeCell(sheet, scopeLabelCell, "K3"); err != nil {
		return nil, fmt.Errorf("failed to merge excel scope label: %w", err)
	}
	f.SetCellStyle(sheet, scopeLabelCell, scopeLabelCell, metaStyle)

	scopeValueCell := "H4"
	f.SetCellValue(sheet, scopeValueCell, scopeValue)
	if err := f.MergeCell(sheet, scopeValueCell, "K4"); err != nil {
		return nil, fmt.Errorf("failed to merge excel scope value: %w", err)
	}
	f.SetCellStyle(sheet, scopeValueCell, scopeValueCell, metaStyle)

	headersRow := 6
	for i, h := range headers {
		cell, err := excelize.CoordinatesToCellName(i+1, headersRow)
		if err != nil {
			return nil, fmt.Errorf("failed to map excel header coordinates: %w", err)
		}
		f.SetCellValue(sheet, cell, h)
		f.SetCellStyle(sheet, cell, cell, headStyle)
	}

	r := 7
	hasData := false
	dataCount := 0
	err = s.repo.ProcessOutstandingRecordsBatches(search, branchID, 500, func(batch []domain.OperationalRecord) error {
		if len(batch) > 0 {
			hasData = true
		}

		for _, v := range batch {
			dataCount++
			f.SetCellValue(sheet, fmt.Sprintf("A%d", r), dataCount)
			f.SetCellValue(sheet, fmt.Sprintf("B%d", r), v.PsoNo)
			if v.PsoDate != nil {
				f.SetCellValue(sheet, fmt.Sprintf("C%d", r), v.PsoDate.Format("02/01/2006"))
			}
			f.SetCellValue(sheet, fmt.Sprintf("D%d", r), v.PoNo)
			f.SetCellValue(sheet, fmt.Sprintf("E%d", r), v.CustomerName)
			f.SetCellValue(sheet, fmt.Sprintf("F%d", r), v.TOP)
			f.SetCellValue(sheet, fmt.Sprintf("G%d", r), v.Discount)
			f.SetCellValue(sheet, fmt.Sprintf("H%d", r), v.Currency)
			f.SetCellValue(sheet, fmt.Sprintf("I%d", r), float64(v.Amount)/100.0)

			// Calculate days
			days := int64(0)
			if v.PsoDate != nil {
				diff := time.Since(*v.PsoDate)
				days = int64(diff.Hours() / 24)
			}
			f.SetCellValue(sheet, fmt.Sprintf("J%d", r), days)
			f.SetCellValue(sheet, fmt.Sprintf("K%d", r), v.Remark)

			for col := 1; col <= 11; col++ {
				cell, err := excelize.CoordinatesToCellName(col, r)
				if err != nil {
					return fmt.Errorf("failed to map excel row coordinates: %w", err)
				}
				if col == 9 {
					if err := f.SetCellStyle(sheet, cell, cell, amountStyle); err != nil {
						return err
					}
					continue
				}
				if col == 1 || col == 3 || col == 6 || col == 7 || col == 8 || col == 10 {
					if err := f.SetCellStyle(sheet, cell, cell, centerCellStyle); err != nil {
						return err
					}
					continue
				}
				if col == 11 {
					if err := f.SetCellStyle(sheet, cell, cell, cellStyle); err != nil {
						return err
					}
					continue
				}
				if err := f.SetCellStyle(sheet, cell, cell, cellStyle); err != nil {
					return err
				}
			}
			r++
		}
		return nil
	})

	if err != nil {
		return nil, fmt.Errorf("failed to collect export data: %w", err)
	}

	if !hasData {
		return nil, ErrNoExportData
	}

	f.SetCellValue(sheet, dataCountCell, fmt.Sprintf("Data: %d baris", dataCount))
	if err := f.SetCellStyle(sheet, dataCountCell, dataCountCell, metaStyle); err != nil {
		return nil, fmt.Errorf("failed to apply excel data count style: %w", err)
	}
	f.SetCellValue(sheet, cutoffValueCell, normalizedCutoff)
	if err := f.SetCellStyle(sheet, cutoffValueCell, cutoffValueCell, metaStyle); err != nil {
		return nil, fmt.Errorf("failed to apply excel cutoff style: %w", err)
	}
	f.SetCellValue(sheet, scopeValueCell, scopeValue)
	if err := f.SetCellStyle(sheet, scopeValueCell, scopeValueCell, metaStyle); err != nil {
		return nil, fmt.Errorf("failed to apply excel scope style: %w", err)
	}

	var buf bytes.Buffer
	err = f.Write(&buf)
	if err != nil {
		return nil, fmt.Errorf("failed to build excel file: %w", err)
	}

	return buf.Bytes(), nil
}

func (s *serviceImpl) ExportPdf(search string, cutoffStr string, branchID uint) ([]byte, error) {
	if s.pdf == nil {
		return nil, ErrPDFEngineUnavailable
	}

	search = strings.TrimSpace(search)
	if err := validateExportSearch(search); err != nil {
		return nil, err
	}

	normalizedCutoff, err := normalizeCutoffDate(cutoffStr)
	if err != nil {
		return nil, err
	}

	records, _, err := s.ListOutstandingRecords(1, 0, search, branchID, "", "", "")
	if err != nil {
		return nil, fmt.Errorf("failed to collect pdf data: %w", err)
	}

	if len(records) == 0 {
		return nil, ErrNoExportData
	}

	// Calculate Aging Days and Total Amount for each record
	now := time.Now()
	var totalAmount float64
	for i := range records {
		amountVal := float64(records[i].Amount) / 100.0
		records[i].Amount = int64(amountVal) // Note: this is for display if template expects int, but we'll use float for total
		totalAmount += amountVal
		
		// Ensure records[i].Amount IDR is also handled if needed, 
		// but usually template uses Amount directly.
		if records[i].PsoDate != nil {
			diff := now.Sub(*records[i].PsoDate)
			records[i].Days = int64(diff.Hours() / 24)
		}
	}

	helper := utils.NewExportHelper(s.pdf, s.setting)
	data := map[string]interface{}{
		"Records":     records,
		"TotalAmount": totalAmount,
		"CutoffDate":  normalizedCutoff,
		"Search":      search,
		"DataCount":   len(records),
		"GeneratedAt": time.Now().Format("1/2/06, 3:04 PM"),
	}

	buf, err := helper.GenerateStandardPDF("po_status/po_pdf.html", data)
	if err != nil {
		return nil, fmt.Errorf("failed to render po status pdf: %w", err)
	}

	return buf, nil
}
func (s *serviceImpl) Delete(id uint, branchID uint) error {
	return s.repo.DeleteRecord(id, branchID)
}