package hr

import (
	"context"
	"errors"
	"fmt"
	"time"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgtype"
	"github.com/shopspring/decimal"

	"github.com/TaruLab/keluargamart-backend/internal/db"
)

// numberAnchorDate is the bucket date every EMP- number is allocated from.
//
// The employee number carries NO date component (EMP-001, EMP-002, ...), but
// the allocator's bucket key is (prefix, doc_date) - see pkg/seq.Next and the
// PRIMARY KEY in 000001_init_schema.sql. Passing the current day therefore
// resets the counter to 1 every midnight, and the next morning's first
// employee is handed EMP-001 again: a unique violation on a value the caller
// never supplied and cannot change.
//
// A fixed anchor gives the prefix one bucket for all time, so the counter is
// monotonic for the life of the table. This is the same fix partners/service.go
// already applies to CUS- and SUP- codes (globalAnchorDate), and the same
// reasoning cashier and galon apply by using seq.MonthStart for a number that
// does carry a month.
//
// It must match the anchor migration 000106 backfills, or the next allocation
// starts from an empty bucket and collides with the seeded EMP-001 again.
var numberAnchorDate = time.Date(2000, 1, 1, 0, 0, 0, 0, time.UTC)

// Sentinel errors mapped to HTTP in the handler.
var (
	ErrNotFound         = errors.New("not found")
	ErrDuplicate        = errors.New("duplicate value")
	ErrAlreadyClockedIn = errors.New("already clocked in today")
	ErrNotClockedIn     = errors.New("not clocked in today")
	ErrInvalidDates     = errors.New("end_date must be >= start_date")
	ErrNotPending       = errors.New("leave is not pending")
	ErrEmployeeRequired = errors.New("employee record required")
	// ErrLeaveForOther means the caller tried to file leave for an employee
	// other than themselves without holding hr.leaves.store.any.
	ErrLeaveForOther = errors.New("filing leave for another employee requires hr.leaves.store.any")
	// ErrLeaveSelfApproval means the approver is the employee the leave is for.
	ErrLeaveSelfApproval = errors.New("cannot approve your own leave")
	// ErrNumberAllocation means the server's own employee-number generator
	// produced a value that already exists. The caller supplied no employee
	// number, so this is never actionable by them and must be answered as a
	// server fault rather than as a duplicate they caused. See
	// mapEmployeeInsertErr in repository.go.
	ErrNumberAllocation = errors.New("employee number allocation collided")
)

// Seq allocates atomic counters (like catalog.Seq).
type Seq interface {
	Sequence(ctx context.Context, prefix string, date time.Time, tx any) (int64, error)
}

// DisbursementHook records an accounting journal when payroll is paid.
type DisbursementHook func(ctx context.Context, p *Payroll, accountCode string, userID *int64) (string, error)

// Service implements HR use cases.
type Service struct {
	store    Store
	seq      Seq
	loc      *time.Location
	disburse DisbursementHook
	// now is a clock seam. bucketDate deliberately does NOT read it - that is
	// the fix - but the field exists so a test can place two creates on
	// different days and so the regression test can actually fail. Without it,
	// reverting bucketDate to the current day would put both creates on the
	// same real date, no reset would occur, and the test would pass against the
	// broken code. Defaults to time.Now; production never sets it.
	now func() time.Time
}

func NewService(store Store, seq Seq, loc *time.Location) *Service {
	return &Service{store: store, seq: seq, loc: loc, now: time.Now}
}

func (s *Service) WithDisbursement(h DisbursementHook) *Service {
	s.disburse = h
	return s
}

// --- employees ---------------------------------------------------------------

func (s *Service) ListEmployees(ctx context.Context, f Filter, search string, active *bool) ([]Employee, int, error) {
	rows, total, err := s.store.ListEmployees(ctx, f, search, active)
	if err != nil {
		return nil, 0, err
	}
	out := make([]Employee, 0, len(rows))
	for _, r := range rows {
		out = append(out, *employeeFromRow(db.GetEmployeeByIDRow(r)))
	}
	return out, int(total), nil
}

func (s *Service) EmployeeByID(ctx context.Context, id int64) (*Employee, error) {
	row, err := s.store.EmployeeByID(ctx, id)
	if err != nil {
		return nil, err
	}
	return employeeFromRow(row), nil
}

// EmployeeByUser resolves the employee row bound to an authenticated user.
func (s *Service) EmployeeByUser(ctx context.Context, userID int64) (*Employee, error) {
	row, err := s.store.EmployeeByUserID(ctx, userID)
	if err != nil {
		return nil, ErrEmployeeRequired
	}
	return employeeFromRow(db.GetEmployeeByIDRow(row)), nil
}

func (s *Service) CreateEmployee(ctx context.Context, in EmployeeInput) (*Employee, error) {
	if in.Name == "" {
		return nil, errors.New("name is required")
	}
	if in.NIK == "" {
		return nil, errors.New("nik is required")
	}
	code := in.EmployeeNumber
	if code == "" {
		var err error
		code, err = s.nextCode(ctx, "emp", "EMP", "%03d")
		if err != nil {
			return nil, err
		}
	}
	row, err := s.store.CreateEmployee(ctx, db.CreateEmployeeParams{
		EmployeeNumber:        code,
		UserID:                int8OrNil(in.UserID),
		Name:                  in.Name,
		Nik:                   textOrNil(in.NIK),
		Department:            def(in.Department, "operations"),
		JobLevel:              def(in.JobLevel, "staff"),
		Position:              textOrNil(in.Position),
		EmploymentType:        def(in.EmploymentType, "permanent"),
		JoinDate:              dateOrNil(strVal(in.JoinDate)),
		EndDate:               dateOrNil(strVal(in.EndDate)),
		BasicSalary:           in.BasicSalary,
		Allowance:             in.Allowance,
		Phone:                 textOrNil(in.Phone),
		Email:                 textOrNil(in.Email),
		Address:               textOrNil(in.Address),
		City:                  textOrNil(in.City),
		BankName:              textOrNil(in.BankName),
		BankAccount:           textOrNil(in.BankAccount),
		BankAccountName:       textOrNil(in.BankAccountName),
		Npwp:                  textOrNil(in.NPWP),
		BpjsKetenagakerjaanNo: textOrNil(in.BpjsKetenagakerjaanNo),
		BpjsKesehatanNo:       textOrNil(in.BpjsKesehatanNo),
		Photo:                 pgtype.Text{},
		IsActive:              in.IsActive,
	})
	if err != nil {
		return nil, err
	}
	return employeeFromRow(db.GetEmployeeByIDRow(row)), nil
}

func (s *Service) UpdateEmployee(ctx context.Context, id int64, in EmployeeInput) (*Employee, error) {
	if in.Name == "" {
		return nil, errors.New("name is required")
	}
	if in.NIK == "" {
		return nil, errors.New("nik is required")
	}
	existing, err := s.store.EmployeeByID(ctx, id)
	if err != nil {
		return nil, err
	}
	if err := s.store.UpdateEmployee(ctx, db.UpdateEmployeeParams{
		ID:                    id,
		UserID:                int8OrNil(in.UserID),
		Name:                  in.Name,
		Nik:                   textOrNil(in.NIK),
		Department:            def(in.Department, existing.Department),
		JobLevel:              def(in.JobLevel, existing.JobLevel),
		Position:              textOrNil(in.Position),
		EmploymentType:        def(in.EmploymentType, existing.EmploymentType),
		JoinDate:              dateOrNil(strVal(in.JoinDate)),
		EndDate:               dateOrNil(strVal(in.EndDate)),
		BasicSalary:           in.BasicSalary,
		Allowance:             in.Allowance,
		Phone:                 textOrNil(in.Phone),
		Email:                 textOrNil(in.Email),
		Address:               textOrNil(in.Address),
		City:                  textOrNil(in.City),
		BankName:              textOrNil(in.BankName),
		BankAccount:           textOrNil(in.BankAccount),
		BankAccountName:       textOrNil(in.BankAccountName),
		Npwp:                  textOrNil(in.NPWP),
		BpjsKetenagakerjaanNo: textOrNil(in.BpjsKetenagakerjaanNo),
		BpjsKesehatanNo:       textOrNil(in.BpjsKesehatanNo),
		Photo:                 pgtype.Text{},
		IsActive:              in.IsActive,
	}); err != nil {
		return nil, err
	}
	row, err := s.store.EmployeeByID(ctx, id)
	if err != nil {
		return nil, err
	}
	return employeeFromRow(row), nil
}

func (s *Service) DeleteEmployee(ctx context.Context, id int64) error {
	return s.store.DeleteEmployee(ctx, id)
}

// --- attendance --------------------------------------------------------------

func (s *Service) ListAttendance(ctx context.Context, f Filter) ([]Attendance, int, error) {
	rows, total, err := s.store.ListAttendance(ctx, f, nil)
	if err != nil {
		return nil, 0, err
	}
	out := make([]Attendance, 0, len(rows))
	for _, r := range rows {
		out = append(out, *attendanceFromListRow(r))
	}
	return out, int(total), nil
}

func (s *Service) AttendanceByID(ctx context.Context, id int64) (*Attendance, error) {
	row, err := s.store.AttendanceByID(ctx, id)
	if err != nil {
		return nil, err
	}
	return attendanceFromRow(row), nil
}

func (s *Service) MyAttendance(ctx context.Context, userID int64, f Filter) ([]Attendance, int, error) {
	e, err := s.EmployeeByUser(ctx, userID)
	if err != nil {
		return nil, 0, err
	}
	rows, total, err := s.store.ListAttendance(ctx, f, &e.ID)
	if err != nil {
		return nil, 0, err
	}
	out := make([]Attendance, 0, len(rows))
	for _, r := range rows {
		out = append(out, *attendanceFromListRow(r))
	}
	return out, int(total), nil
}

// CreateAttendance backfills an attendance record (admin).
func (s *Service) CreateAttendance(ctx context.Context, in AttendanceInput) (*Attendance, error) {
	if in.AttendanceDate == "" {
		return nil, errors.New("attendance_date is required")
	}
	status := in.Status
	if status == "" {
		status = "present"
	}
	var wh, ov *decimal.Decimal
	if in.ClockIn != nil && in.ClockOut != nil {
		w, o := s.computeHours(in.ClockIn, in.ClockOut)
		wh, ov = &w, &o
	}
	row, err := s.store.CreateAttendance(ctx, db.CreateAttendanceParams{
		EmployeeID:     in.EmployeeID,
		AttendanceDate: dateOrNil(in.AttendanceDate),
		ClockIn:        tsToPgtz(in.ClockIn),
		ClockOut:       tsToPgtz(in.ClockOut),
		ClockInLat:     floatToNd(nil),
		ClockInLng:     floatToNd(nil),
		ClockOutLat:    floatToNd(nil),
		ClockOutLng:    floatToNd(nil),
		Status:         status,
		WorkHours:      decToNdPtr(wh),
		OvertimeHours:  decToNdPtr(ov),
		Notes:          textOrNil(in.Notes),
	})
	if err != nil {
		return nil, err
	}
	return attendanceFromRow(db.GetAttendanceByIDRow(row)), nil
}

// ClockIn records the self-service clock-in (§8.2). Late when after 08:00
// plus 15 minutes tolerance.
func (s *Service) ClockIn(ctx context.Context, userID int64, in ClockInOutInput) (*Attendance, error) {
	e, err := s.EmployeeByUser(ctx, userID)
	if err != nil {
		return nil, err
	}
	now := time.Now().In(s.loc)
	day := now.Format("2006-01-02")
	if _, err := s.store.AttendanceForDay(ctx, e.ID, day); err == nil {
		return nil, ErrAlreadyClockedIn
	} else if !errors.Is(err, ErrNotFound) {
		return nil, err
	}
	status := "present"
	if now.Hour() > 8 || (now.Hour() == 8 && now.Minute() > 15) {
		status = "late"
	}
	row, err := s.store.CreateAttendance(ctx, db.CreateAttendanceParams{
		EmployeeID:     e.ID,
		AttendanceDate: dateOrNil(day),
		ClockIn:        tsToPgtz(&now),
		ClockInLat:     floatToNd(in.Lat),
		ClockInLng:     floatToNd(in.Lng),
		Status:         status,
		Notes:          textOrNil(in.Notes),
	})
	if err != nil {
		return nil, err
	}
	return attendanceFromRow(db.GetAttendanceByIDRow(row)), nil
}

// ClockOut closes the day's attendance and computes work/overtime hours.
func (s *Service) ClockOut(ctx context.Context, userID int64, in ClockInOutInput) (*Attendance, error) {
	e, err := s.EmployeeByUser(ctx, userID)
	if err != nil {
		return nil, err
	}
	day := time.Now().In(s.loc).Format("2006-01-02")
	rec, err := s.store.AttendanceForDay(ctx, e.ID, day)
	if err != nil {
		return nil, ErrNotClockedIn
	}
	view := attendanceFromRow(db.GetAttendanceByIDRow(rec))
	if view.ClockIn == nil || view.ClockOut != nil {
		return nil, ErrNotClockedIn
	}
	now := time.Now().In(s.loc)
	view.ClockOut = &now
	view.ClockOutLat = in.Lat
	view.ClockOutLng = in.Lng
	wh, ov := s.computeHours(view.ClockIn, view.ClockOut)
	view.WorkHours = &wh
	view.OvertimeHours = &ov
	if err := s.store.UpdateAttendance(ctx, db.UpdateAttendanceParams{
		ID:            view.ID,
		EmployeeID:    view.EmployeeID,
		ClockIn:       tsToPgtz(view.ClockIn),
		ClockOut:      tsToPgtz(view.ClockOut),
		ClockInLat:    floatToNd(view.ClockInLat),
		ClockInLng:    floatToNd(view.ClockInLng),
		ClockOutLat:   floatToNd(view.ClockOutLat),
		ClockOutLng:   floatToNd(view.ClockOutLng),
		Status:        view.Status,
		WorkHours:     decToNdPtr(view.WorkHours),
		OvertimeHours: decToNdPtr(view.OvertimeHours),
		Notes:         textOrNil(view.Notes),
	}); err != nil {
		return nil, err
	}
	return view, nil
}

// computeHours derives work_hours and overtime_hours from the two timestamps.
// Overtime applies above the 8h threshold (business-rules §8.2).
func (s *Service) computeHours(in, out *time.Time) (decimal.Decimal, decimal.Decimal) {
	hours := decimal.NewFromFloat(out.Sub(*in).Hours()).Round(2)
	ov := decimal.Zero
	if hours.GreaterThan(decimal.NewFromInt(8)) {
		ov = hours.Sub(decimal.NewFromInt(8)).Round(2)
	}
	return hours, ov
}

// --- leaves ------------------------------------------------------------------

func (s *Service) ListLeaves(ctx context.Context, f Filter) ([]Leave, int, error) {
	rows, total, err := s.store.ListLeaves(ctx, f, nil)
	if err != nil {
		return nil, 0, err
	}
	out := make([]Leave, 0, len(rows))
	for _, r := range rows {
		out = append(out, *leaveFromListRow(r))
	}
	return out, int(total), nil
}

func (s *Service) MyLeaves(ctx context.Context, userID int64, f Filter) ([]Leave, int, error) {
	e, err := s.EmployeeByUser(ctx, userID)
	if err != nil {
		return nil, 0, err
	}
	rows, total, err := s.store.ListLeaves(ctx, f, &e.ID)
	if err != nil {
		return nil, 0, err
	}
	out := make([]Leave, 0, len(rows))
	for _, r := range rows {
		out = append(out, *leaveFromListRow(r))
	}
	return out, int(total), nil
}

// CreateLeave validates dates and computes total_days = end - start + 1 (§8.3).
func (s *Service) CreateLeave(ctx context.Context, userID int64, in LeaveInput) (*Leave, error) {
	// Whose leave is this? Not always the caller's — see resolveLeaveTarget.
	target, err := s.resolveLeaveTarget(ctx, userID, in)
	if err != nil {
		return nil, err
	}
	if in.LeaveType == "" {
		return nil, errors.New("leave_type is required")
	}
	if in.Reason == "" {
		return nil, errors.New("reason is required")
	}
	total, err := daySpan(in.StartDate, in.EndDate)
	if err != nil {
		return nil, ErrInvalidDates
	}
	row, err := s.store.CreateLeave(ctx, db.CreateLeaveParams{
		EmployeeID: target,
		LeaveType:  in.LeaveType,
		StartDate:  dateOrNil(in.StartDate),
		EndDate:    dateOrNil(in.EndDate),
		TotalDays:  int32(total),
		Reason:     textOrNil(in.Reason),
		Status:     "pending",
		Notes:      textOrNil(in.Notes),
	})
	if err != nil {
		return nil, err
	}
	return leaveFromRow(db.GetLeaveByIDRow(row)), nil
}

func (s *Service) ApproveLeave(ctx context.Context, id, approvedBy int64) (*Leave, error) {
	return s.reviewLeave(ctx, id, approvedBy, "approved")
}

func (s *Service) RejectLeave(ctx context.Context, id, approvedBy int64) (*Leave, error) {
	return s.reviewLeave(ctx, id, approvedBy, "rejected")
}

func (s *Service) reviewLeave(ctx context.Context, id, approvedBy int64, status string) (*Leave, error) {
	row, err := s.store.LeaveByID(ctx, id)
	if err != nil {
		return nil, err
	}
	l := leaveFromRow(row)
	if l.Status != "pending" {
		return nil, ErrNotPending
	}
	if err := s.approveGuard(ctx, l, approvedBy); err != nil {
		return nil, err
	}
	now := time.Now().In(s.loc)
	l.Status = status
	l.ApprovedBy = &approvedBy
	l.ApprovedAt = &now
	if err := s.store.UpdateLeave(ctx, db.UpdateLeaveParams{
		ID:         id,
		EmployeeID: l.EmployeeID,
		Status:     status,
		ApprovedBy: int8OrNil(l.ApprovedBy),
		ApprovedAt: tsToPgtz(l.ApprovedAt),
		Notes:      textOrNil(l.Notes),
	}); err != nil {
		return nil, err
	}
	// On approval, deduct the leave days from the employee's balance (§20 leave
	// entitlement default: balance enforced; error if insufficient).
	// Unpaid leave does not consume the annual leave balance quota.
	if status == "approved" {
		if l.LeaveType != "unpaid" {
			if _, err := s.store.DeductEmployeeLeave(ctx, db.DeductEmployeeLeaveParams{
				ID:           l.EmployeeID,
				LeaveBalance: decimal.NewFromInt(int64(l.TotalDays)),
			}); err != nil {
				return nil, errors.New("insufficient leave balance")
			}
		}
		// Automatically upsert attendance records for each day in the leave period
		start, err1 := time.Parse("2006-01-02", l.StartDate)
		end, err2 := time.Parse("2006-01-02", l.EndDate)
		if err1 == nil && err2 == nil {
			note := fmt.Sprintf("Cuti %s: %s", l.LeaveType, l.Reason)
			for d := start; !d.After(end); d = d.AddDate(0, 0, 1) {
				dayStr := d.Format("2006-01-02")
				_ = s.store.UpsertLeaveAttendance(ctx, db.UpsertLeaveAttendanceParams{
					EmployeeID:     l.EmployeeID,
					AttendanceDate: dateOrNil(dayStr),
					Notes:          textOrNil(note),
				})
			}
		}
	}
	return l, nil
}

// --- payrolls ----------------------------------------------------------------

func (s *Service) ListPayrolls(ctx context.Context, f Filter) ([]Payroll, int, error) {
	rows, total, err := s.store.ListPayrolls(ctx, f, nil)
	if err != nil {
		return nil, 0, err
	}
	out := make([]Payroll, 0, len(rows))
	for _, r := range rows {
		out = append(out, *payrollFromListRow(r))
	}
	return out, int(total), nil
}

func (s *Service) MyPayrolls(ctx context.Context, userID int64, f Filter) ([]Payroll, int, error) {
	e, err := s.EmployeeByUser(ctx, userID)
	if err != nil {
		return nil, 0, err
	}
	rows, total, err := s.store.ListPayrolls(ctx, f, &e.ID)
	if err != nil {
		return nil, 0, err
	}
	out := make([]Payroll, 0, len(rows))
	for _, r := range rows {
		out = append(out, *payrollFromListRow(r))
	}
	return out, int(total), nil
}

// CalculateMonthlyAttendance aggregates an employee's monthly attendance figures.
func (s *Service) CalculateMonthlyAttendance(ctx context.Context, employeeID int64, month, year int) (AttendanceSummary, error) {
	startDate := fmt.Sprintf("%04d-%02d-01", year, month)
	firstDay, err := time.Parse("2006-01-02", startDate)
	if err != nil {
		return AttendanceSummary{}, err
	}
	lastDay := firstDay.AddDate(0, 1, -1)
	endDate := lastDay.Format("2006-01-02")

	attRows, err := s.store.ListAttendanceForEmployeePeriod(ctx, db.ListAttendanceForEmployeePeriodParams{
		EmployeeID:       employeeID,
		AttendanceDate:   dateOrNil(startDate),
		AttendanceDate_2: dateOrNil(endDate),
	})
	if err != nil {
		return AttendanceSummary{}, err
	}

	summary := AttendanceSummary{
		TotalOvertimeHours: decimal.Zero,
	}

	for _, r := range attRows {
		switch r.Status {
		case "present":
			summary.PresentDays++
		case "late":
			summary.PresentDays++
			summary.LateDays++
		case "leave":
			summary.LeaveDays++
		case "absent":
			summary.AbsentDays++
		}
		if r.OvertimeHours.Valid {
			summary.TotalOvertimeHours = summary.TotalOvertimeHours.Add(r.OvertimeHours.Decimal)
		}
	}

	leaveRows, err := s.store.ListApprovedLeavesForEmployeePeriod(ctx, db.ListApprovedLeavesForEmployeePeriodParams{
		EmployeeID: employeeID,
		EndDate:    dateOrNil(startDate),
		StartDate:  dateOrNil(endDate),
	})
	if err == nil {
		for _, l := range leaveRows {
			if l.LeaveType == "unpaid" {
				summary.UnpaidLeaveDays += int(l.TotalDays)
			}
		}
	}

	return summary, nil
}

// payrollBucketDate is the (prefix, doc_date) bucket a PAY- number is allocated
// from: the first of the PAYROLL PERIOD, never today.
//
// The number is fmt.Sprintf("PAY-%04d-%04d", year*100+month, ...) - the visible
// month is the period being paid, which is a caller-supplied parameter. The
// bucket has to be the same month the number shows, or the ordinal and the
// visible suffix stop describing the same thing.
//
// This is the one allocator where cashier/galon's seq.MonthStart(time.Now())
// is the WRONG template to copy. Those two are self-consistent because the
// month they print IS time.Now()'s month:
//
//	SHF-%s-%06d  with  seq.MonthStart(time.Now().In(loc))   <- same instant
//	GLN-%s-%06d  with  seq.MonthStart(time.Now().In(loc))   <- same instant
//
// Payroll is not: PAY-202609-0001 can be generated on 2026-10-05, for the
// September period. Bucketing that on October would make the same September
// period draw from two different counters depending on which day it was run -
// exactly the drift a per-month ordinal exists to prevent, and it would collide
// as soon as a September period was run in September and then again in October.
//
// So the bucket is derived from (year, month), not from the clock. The ordinal
// is therefore per payroll period, which is what the format implies: the month
// is already in the number, so the ordinal only has to be unique within it.
func payrollBucketDate(year, month int, loc *time.Location) time.Time {
	return time.Date(year, time.Month(month), 1, 0, 0, 0, 0, loc)
}

// GeneratePayrolls creates draft payslips for every active employee pulling
// live attendance data (overtime, late/unpaid/alpha deductions) + statutory rates.
func (s *Service) GeneratePayrolls(ctx context.Context, month, year int) ([]Payroll, error) {
	if month < 1 || month > 12 {
		return nil, errors.New("period_month must be 1-12")
	}
	if year < 1900 || year > 9999 {
		return nil, errors.New("period_year is invalid")
	}
	emps, err := s.store.ActiveEmployees(ctx)
	if err != nil {
		return nil, err
	}
	out := []Payroll{}
	for _, e := range emps {
		basic := e.BasicSalary
		allowance := e.Allowance
		summary, _ := s.CalculateMonthlyAttendance(ctx, e.ID, month, year)

		// 1. Overtime Pay: 1/173 x basic salary per hour (Kepmenaker standard)
		overtimePay := decimal.Zero
		if basic.IsPositive() && summary.TotalOvertimeHours.IsPositive() {
			hourlyRate := basic.Div(decimal.NewFromInt(173))
			overtimePay = summary.TotalOvertimeHours.Mul(hourlyRate).Round(2)
		}

		// 2. Deductions: Late penalty (Rp 25.000/arrival) + Unpaid leave & Alpha (basic / 25 per day)
		dailyRate := decimal.Zero
		if basic.IsPositive() {
			dailyRate = basic.Div(decimal.NewFromInt(25)).Round(2)
		}
		latePenalty := decimal.NewFromInt(int64(summary.LateDays * 25000))
		unpaidPenalty := decimal.NewFromInt(int64(summary.UnpaidLeaveDays)).Mul(dailyRate).Round(2)
		absentPenalty := decimal.NewFromInt(int64(summary.AbsentDays)).Mul(dailyRate).Round(2)
		deductions := latePenalty.Add(unpaidPenalty).Add(absentPenalty)

		// 3. BPJS: Ketenagakerjaan (2.7%) + Kesehatan (1%)
		const bpjsTKRate = 0.027
		const bpjsKesRate = 0.01
		bpjsTK := basic.Mul(decimal.NewFromFloat(bpjsTKRate)).Round(2)
		bpjsKes := basic.Mul(decimal.NewFromFloat(bpjsKesRate)).Round(2)

		// 4. Gross & PPh 21
		gross := basic.Add(allowance).Add(overtimePay).Round(2)
		pph21 := decimal.Zero

		// 5. Net Salary (Take Home Pay)
		net := gross.Sub(deductions).Sub(bpjsTK).Sub(bpjsKes).Sub(pph21).Round(2)
		if net.IsNegative() {
			net = decimal.Zero
		}

		// One allocation per payslip, so the counter advances by exactly the
		// number of ordinals this run consumes.
		//
		// It used to be one allocation before the loop, with the rest of the
		// run derived as n+i. That handed employee i the ordinal n+i - correct
		// within the run - but advanced the counter by ONE while the run
		// consumed len(emps) ordinals, so the counter fell behind the data it
		// had issued. The next run then started INSIDE the previous run's
		// range. ON CONFLICT (employee_id, period_month, period_year) DO UPDATE
		// hid that whenever the roster was unchanged, because payroll_number is
		// not in the SET list and an existing row keeps the number it already
		// had. It surfaced when the roster shrank and then grew within one
		// period: a departed employee's row still holds its ordinal, a new
		// joiner was issued that same ordinal, and the insert failed on
		// payrolls_payroll_number_key.
		//
		// The within-run sequence is unchanged by this. seq.Next increments by
		// exactly one per call and returns the new value, so the (i+1)-th call
		// returns n+i - the same number the old arithmetic produced for
		// employee i. What changes is only the counter's value afterwards: it
		// now ends past everything this run issued instead of one step into
		// it. No already-issued number is read, written or reassigned.
		//
		// Allocated as late as possible so a failure in the attendance and
		// calculation above does not consume an ordinal.
		n, err := s.seq.Sequence(ctx, "pay", payrollBucketDate(year, month, s.loc), nil)
		if err != nil {
			return nil, fmt.Errorf("payroll sequence: %w", err)
		}

		row, err := s.store.UpsertPayroll(ctx, db.UpsertPayrollParams{
			PayrollNumber:       fmt.Sprintf("PAY-%04d-%04d", year*100+month, n),
			EmployeeID:          e.ID,
			PeriodMonth:         int16(month),
			PeriodYear:          int16(year),
			BasicSalary:         basic,
			Allowances:          allowance,
			OvertimePay:         overtimePay,
			Bonus:               decimal.Zero,
			Deductions:          deductions,
			BpjsKetenagakerjaan: bpjsTK,
			BpjsKesehatan:       bpjsKes,
			Pph21:               pph21,
			NetSalary:           net,
			Status:              "draft",
		})
		if err != nil {
			if errors.Is(err, pgx.ErrNoRows) || errors.Is(err, ErrNotFound) {
				// Payroll already exists in non-draft status (approved or paid); do not overwrite.
				continue
			}
			if errors.Is(err, ErrDuplicate) {
				return nil, ErrDuplicate
			}
			return nil, err
		}
		out = append(out, *payrollFromRow(db.GetPayrollByIDRow(row)))
	}
	return out, nil
}

func (s *Service) PayrollByID(ctx context.Context, id int64) (*Payroll, error) {
	row, err := s.store.PayrollByID(ctx, id)
	if err != nil {
		return nil, err
	}
	return payrollFromRow(row), nil
}

func (s *Service) ApprovePayroll(ctx context.Context, id int64) (*Payroll, error) {
	row, err := s.store.PayrollByID(ctx, id)
	if err != nil {
		return nil, err
	}
	p := payrollFromRow(row)
	if p.Status != "draft" {
		return nil, ErrNotPending
	}
	p.Status = "approved"
	if err := s.store.UpdatePayroll(ctx, db.UpdatePayrollParams{
		ID:         id,
		EmployeeID: p.EmployeeID,
		Status:     "approved",
		Notes:      textOrNil(p.Notes),
	}); err != nil {
		return nil, err
	}
	return p, nil
}

func (s *Service) PayPayroll(ctx context.Context, id int64, in PayPayrollInput, userID *int64) (*Payroll, error) {
	row, err := s.store.PayrollByID(ctx, id)
	if err != nil {
		return nil, err
	}
	p := payrollFromRow(row)
	if p.Status != "approved" {
		return nil, errors.New("hanya slip gaji berstatus disetujui (approved) yang dapat dibayarkan")
	}
	payDate := in.PaymentDate
	if payDate == "" {
		payDate = time.Now().In(s.loc).Format("2006-01-02")
	}
	p.PaymentDate = &payDate
	if in.Notes != "" {
		p.Notes = in.Notes
	}
	if s.disburse != nil {
		acct := in.AccountCode
		if acct == "" {
			if in.PaymentMethod == "cash" {
				acct = "1000.01.02"
			} else {
				acct = "1000.03.01"
			}
		}
		if _, err := s.disburse(ctx, p, acct, userID); err != nil {
			return nil, fmt.Errorf("gagal mencatat jurnal akuntansi: %w", err)
		}
	}
	if err := s.store.PayPayroll(ctx, db.PayPayrollParams{
		ID:          id,
		PaymentDate: dateOrNil(payDate),
		Notes:       textOrNil(in.Notes),
	}); err != nil {
		return nil, err
	}
	p.Status = "paid"
	return p, nil
}

// --- performances ------------------------------------------------------------

func (s *Service) ListPerformances(ctx context.Context, f Filter) ([]Performance, int, error) {
	rows, total, err := s.store.ListPerformances(ctx, f)
	if err != nil {
		return nil, 0, err
	}
	out := make([]Performance, 0, len(rows))
	for _, r := range rows {
		out = append(out, *performanceFromRow(r))
	}
	return out, int(total), nil
}

// CreatePerformance derives total_score (mean of the four scores) and grade
// A-D (§8.5). Grade thresholds follow the 0-100 scale: A>=90, B>=80, C>=70.
func (s *Service) CreatePerformance(ctx context.Context, evaluatorID int64, in PerformanceInput) (*Performance, error) {
	if in.PeriodMonth < 1 || in.PeriodMonth > 12 {
		return nil, errors.New("period_month must be 1-12")
	}
	for _, v := range []decimal.Decimal{in.KPIScore, in.AttitudeScore, in.TeamworkScore, in.AchievementScore} {
		if v.IsNegative() || v.GreaterThan(decimal.NewFromInt(100)) {
			return nil, errors.New("scores must be 0-100")
		}
	}
	total := in.KPIScore.Add(in.AttitudeScore).Add(in.TeamworkScore).Add(in.AchievementScore).
		Div(decimal.NewFromInt(4)).Round(2)
	row, err := s.store.CreatePerformance(ctx, db.CreatePerformanceParams{
		EmployeeID:       in.EmployeeID,
		EvaluatorID:      int8OrNil(&evaluatorID),
		PeriodMonth:      int16(in.PeriodMonth),
		PeriodYear:       int16(in.PeriodYear),
		KpiScore:         decToNd(in.KPIScore),
		AttitudeScore:    decToNd(in.AttitudeScore),
		TeamworkScore:    decToNd(in.TeamworkScore),
		AchievementScore: decToNd(in.AchievementScore),
		TotalScore:       decToNd(total),
		Grade:            textOrNil(gradeFor(total)),
		Achievements:     textOrNil(in.Achievements),
		Improvements:     textOrNil(in.Improvements),
		Notes:            textOrNil(in.Notes),
	})
	if err != nil {
		return nil, err
	}
	return performanceFromRow(db.ListPerformancesRow{
		ID: row.ID, EmployeeID: row.EmployeeID, EvaluatorID: row.EvaluatorID,
		PeriodMonth: row.PeriodMonth, PeriodYear: row.PeriodYear,
		KpiScore: row.KpiScore, AttitudeScore: row.AttitudeScore, TeamworkScore: row.TeamworkScore,
		AchievementScore: row.AchievementScore, TotalScore: row.TotalScore,
		Grade: row.Grade, Achievements: row.Achievements, Improvements: row.Improvements, Notes: row.Notes,
	}), nil
}

func (s *Service) PerformanceByID(ctx context.Context, id int64) (*Performance, error) {
	row, err := s.store.PerformanceByID(ctx, id)
	if err != nil {
		return nil, err
	}
	return performanceFromGetRow(row), nil
}

func (s *Service) UpdatePerformance(ctx context.Context, id int64, in PerformanceInput) (*Performance, error) {
	for _, v := range []decimal.Decimal{in.KPIScore, in.AttitudeScore, in.TeamworkScore, in.AchievementScore} {
		if v.IsNegative() || v.GreaterThan(decimal.NewFromInt(100)) {
			return nil, errors.New("scores must be 0-100")
		}
	}
	total := in.KPIScore.Add(in.AttitudeScore).Add(in.TeamworkScore).Add(in.AchievementScore).
		Div(decimal.NewFromInt(4)).Round(2)
	if err := s.store.UpdatePerformance(ctx, db.UpdatePerformanceParams{
		ID:               id,
		KpiScore:         decToNd(in.KPIScore),
		AttitudeScore:    decToNd(in.AttitudeScore),
		TeamworkScore:    decToNd(in.TeamworkScore),
		AchievementScore: decToNd(in.AchievementScore),
		TotalScore:       decToNd(total),
		Grade:            textOrNil(gradeFor(total)),
		Achievements:     textOrNil(in.Achievements),
		Improvements:     textOrNil(in.Improvements),
		Notes:            textOrNil(in.Notes),
	}); err != nil {
		return nil, err
	}
	return s.PerformanceByID(ctx, id)
}

func (s *Service) DeletePerformance(ctx context.Context, id int64) error {
	return s.store.DeletePerformance(ctx, id)
}

func gradeFor(total decimal.Decimal) string {
	switch {
	case total.GreaterThanOrEqual(decimal.NewFromInt(90)):
		return "A"
	case total.GreaterThanOrEqual(decimal.NewFromInt(80)):
		return "B"
	case total.GreaterThanOrEqual(decimal.NewFromInt(70)):
		return "C"
	default:
		return "D"
	}
}

// --- helpers -----------------------------------------------------------------

// nextCode allocates the next number for a prefix whose rendered form carries
// no date, so the bucket is the shared anchor rather than the current day. See
// numberAnchorDate for why, and migration 000106 for the backfill that puts the
// counter above the seeded rows.
func (s *Service) nextCode(ctx context.Context, prefix, display, pad string) (string, error) {
	n, err := s.seq.Sequence(ctx, prefix, s.bucketDate(), nil)
	if err != nil {
		return "", fmt.Errorf("%s sequence: %w", prefix, err)
	}
	return fmt.Sprintf("%s-"+pad, display, n), nil
}

// bucketDate is the (prefix, doc_date) bucket an EMP- number is allocated
// from: the fixed anchor, never the current day. See numberAnchorDate.
func (s *Service) bucketDate() time.Time {
	return numberAnchorDate
}

func def(v, fallback string) string {
	if v == "" {
		return fallback
	}
	return v
}

func daySpan(start, end string) (int, error) {
	s, err := time.Parse("2006-01-02", start)
	if err != nil {
		return 0, ErrInvalidDates
	}
	e, err := time.Parse("2006-01-02", end)
	if err != nil {
		return 0, ErrInvalidDates
	}
	if e.Before(s) {
		return 0, ErrInvalidDates
	}
	return int(e.Sub(s).Hours()/24) + 1, nil
}