package finance

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

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

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

// Sentinel errors mapped to HTTP in the handler.
var (
	ErrNotFound                       = errors.New("not found")
	ErrCannotInvoice                  = errors.New("SO must be confirmed or delivered")
	ErrPaymentTooLarge                = errors.New("Jumlah pembayaran melebihi sisa tagihan")
	ErrInvalidDate                    = errors.New("date must use YYYY-MM-DD format")
	ErrInvoiceUnpaid                  = errors.New("invoice must be unpaid")
	ErrInvoiceHasPayment              = errors.New("invoice has a payment")
	ErrInvoiceCreationHistoryRequired = errors.New("invoice creation history is required")
	ErrAccountingPeriodLocked         = errors.New("accounting period is locked")
	ErrGeneratedCashflowImmutable     = errors.New("generated cashflow is immutable")
	ErrNotGenerated                   = errors.New("only generated cashflows can be reversed")
	ErrAlreadyReversed                = errors.New("cashflow already reversed")
	ErrPeriodCloseBlocked             = errors.New("accounting period has blocking journal issues")
	ErrAccountingPeriodInvalid        = errors.New("invalid accounting period")
	ErrNotPayable                     = errors.New("invoice is not payable")
	// ErrUnknownPayable: partial payments ride an existing payment's payable;
	// only invoice payables flow through the finance recording logic (POS
	// sales-order payments are recorded at sale time by the sales service).
	ErrUnknownPayable = errors.New("payable type does not support additional payments")
	// ErrForbidden: a request that may not be performed by this caller. Used by
	// the cashflow outlet derivation when a restricted multi-store caller tries
	// to write a cashflow with no resolvable binding, and for a request that
	// reached a cashflow surface with no store scope attached at all. Both are
	// 403 rather than 404: the caller is authenticated and the resource class is
	// known to them, so pretending otherwise would be a different lie.
	ErrForbidden = errors.New("forbidden")
)

// InvoiceModel is the morph reference used by payments (Laravel FQCN, §5.2).
const InvoiceModel = "App\\Models\\Invoice"

// Seq allocates atomic counters (pkg/seq) — injected so tests fake it.
type Seq interface {
	Sequence(ctx context.Context, prefix string, date time.Time, tx any) (int64, error)
}

// Service implements finance use cases.
type Service struct {
	store Store
	seq   Seq
	loc   *time.Location
}

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

func (s *Service) ListAccountingPeriods(ctx context.Context) ([]AccountingPeriod, error) {
	p, ok := s.store.(periodStore)
	if !ok {
		return nil, errors.New("accounting periods unavailable")
	}
	return p.ListAccountingPeriods(ctx)
}
func (s *Service) GetAccountingPeriod(ctx context.Context, year, month int16) (AccountingPeriod, error) {
	if year < 1 || month < 1 || month > 12 {
		return AccountingPeriod{}, ErrAccountingPeriodInvalid
	}
	p, ok := s.store.(periodStore)
	if !ok {
		return AccountingPeriod{}, errors.New("accounting periods unavailable")
	}
	return p.AccountingPeriod(ctx, year, month)
}
func (s *Service) PreviewAccountingPeriod(ctx context.Context, year, month int16) (PeriodSummary, error) {
	if year < 1 || month < 1 || month > 12 {
		return PeriodSummary{}, ErrAccountingPeriodInvalid
	}
	p, ok := s.store.(periodStore)
	if !ok {
		return PeriodSummary{}, errors.New("accounting periods unavailable")
	}
	return p.AccountingPeriodSummary(ctx, year, month)
}
func (s *Service) ValidateAccountingPeriod(ctx context.Context, year, month int16) (PeriodValidationResult, error) {
	if year < 1 || month < 1 || month > 12 {
		return PeriodValidationResult{}, ErrAccountingPeriodInvalid
	}
	p, ok := s.store.(periodStore)
	if !ok {
		return PeriodValidationResult{}, errors.New("accounting periods unavailable")
	}
	return p.ValidateAccountingPeriod(ctx, year, month)
}
func (s *Service) CloseAccountingPeriod(ctx context.Context, year, month int16, actor int64) (AccountingPeriod, error) {
	if actor <= 0 || year < 1 || month < 1 || month > 12 {
		return AccountingPeriod{}, ErrAccountingPeriodInvalid
	}
	p, ok := s.store.(periodStore)
	if !ok {
		return AccountingPeriod{}, errors.New("accounting periods unavailable")
	}
	return p.CloseAccountingPeriod(ctx, year, month, actor)
}
func (s *Service) ReopenAccountingPeriod(ctx context.Context, year, month int16, actor int64, reason string) (AccountingPeriod, error) {
	if actor <= 0 || year < 1 || month < 1 || month > 12 {
		return AccountingPeriod{}, ErrAccountingPeriodInvalid
	}
	if reason == "" {
		return AccountingPeriod{}, errors.New("alasan pembukaan kembali periode wajib diisi")
	}
	p, ok := s.store.(periodStore)
	if !ok {
		return AccountingPeriod{}, errors.New("accounting periods unavailable")
	}
	return p.ReopenAccountingPeriod(ctx, year, month, actor, reason)
}
func (s *Service) AccountingPeriodHistory(ctx context.Context, year, month int16) ([]AccountingPeriodLog, error) {
	if year < 1 || month < 1 || month > 12 {
		return nil, ErrAccountingPeriodInvalid
	}
	p, ok := s.store.(periodStore)
	if !ok {
		return nil, errors.New("accounting periods unavailable")
	}
	return p.AccountingPeriodHistory(ctx, year, month)
}

// --- invoices ---------------------------------------------------------------

func (s *Service) ListInvoices(ctx context.Context, f InvoiceFilter) ([]Invoice, int, error) {
	if err := validateDateRange(f.DateFrom, f.DateTo); err != nil {
		return nil, 0, err
	}
	if f.Page < 1 {
		f.Page = 1
	}
	// Clamp oversized page sizes instead of resetting to the 20 default:
	// asking for MORE used to silently return LESS (per_page=200 -> 20 rows),
	// which under-reported every summary built on these lists (4 Nov audit).
	if f.PerPage < 1 {
		f.PerPage = 20
	} else if f.PerPage > 100 {
		f.PerPage = 100
	}
	rows, total, err := s.store.ListInvoices(ctx, f)
	if err != nil {
		return nil, 0, err
	}
	out := make([]Invoice, 0, len(rows))
	for _, row := range rows {
		out = append(out, invoiceFromListRow(row))
	}
	return out, total, nil
}
func (s *Service) ListPayments(ctx context.Context, f InvoiceFilter) ([]Payment, int, error) {
	if err := validateDateRange(f.DateFrom, f.DateTo); err != nil {
		return nil, 0, err
	}
	if f.Page < 1 {
		f.Page = 1
	}
	// Clamp oversized page sizes instead of resetting to the 20 default:
	// asking for MORE used to silently return LESS (per_page=200 -> 20 rows),
	// which under-reported every summary built on these lists (4 Nov audit).
	if f.PerPage < 1 {
		f.PerPage = 20
	} else if f.PerPage > 100 {
		f.PerPage = 100
	}
	rows, total, err := s.store.ListPayments(ctx, f)
	if err != nil {
		return nil, 0, err
	}
	out := make([]Payment, 0, len(rows))
	for _, row := range rows {
		out = append(out, paymentFromRow(row))
	}
	return out, total, nil
}

// MyPayments is the buyer-scoped ListPayments (GET /payments/me). Total comes
// from SumCustomerPayments — one indexed scan over the customer's invoice
// payments — instead of the generic CountPayments, which LEFT-joins payments
// against invoices on a polymorphic payable tuple and walks every row. Page
// rows still come from the join-heavy ListPayments (they carry the SO/invoice
// labels the UI renders).
func (s *Service) MyPayments(ctx context.Context, f InvoiceFilter) ([]Payment, int, error) {
	if err := validateDateRange(f.DateFrom, f.DateTo); err != nil {
		return nil, 0, err
	}
	if f.Page < 1 {
		f.Page = 1
	}
	// Clamp oversized page sizes instead of resetting to the 20 default:
	// asking for MORE used to silently return LESS (per_page=200 -> 20 rows),
	// which under-reported every summary built on these lists (4 Nov audit).
	if f.PerPage < 1 {
		f.PerPage = 20
	} else if f.PerPage > 100 {
		f.PerPage = 100
	}
	// customerID <= 0 means the caller has no linked customer — no payments.
	if f.CustomerID <= 0 {
		return []Payment{}, 0, nil
	}
	rows, total, err := s.store.ListPayments(ctx, f)
	if err != nil {
		return nil, 0, err
	}
	// The single-scan total is authoritative when no other filter narrows it;
	// fall back to the query total otherwise (row_count equals the customer's
	// full payment count only in the unfiltered case).
	if f.Search == "" && f.Status == "" && f.PayableID == 0 && f.PayableType == "" {
		sum, err := s.store.SumCustomerPayments(ctx, f.CustomerID, f.DateFrom, f.DateTo)
		if err != nil {
			return nil, 0, err
		}
		total = int(sum.RowCount)
	}
	out := make([]Payment, 0, len(rows))
	for _, row := range rows {
		out = append(out, paymentFromRow(row))
	}
	return out, total, nil
}

func (s *Service) GetInvoice(ctx context.Context, id int64) (*Invoice, error) {
	row, err := s.store.InvoiceByID(ctx, id)
	if err != nil {
		return nil, err
	}
	inv := invoiceFromRow(row)
	// There is no invoice_items table: the invoice's lines are the source
	// sales order's items (CreateInvoice only references the SO).
	if inv.SalesOrderID > 0 {
		items, err := s.store.InvoiceItems(ctx, inv.SalesOrderID)
		if err != nil {
			return nil, err
		}
		for _, it := range items {
			inv.Items = append(inv.Items, invoiceItemFromRow(it))
		}
	}
	return inv, nil
}

// PrintInvoice serves the printable invoice document — the same aggregate as
// GetInvoice (invoice + source SO items), exposed as a stable print contract.
func (s *Service) PrintInvoice(ctx context.Context, id int64) (*Invoice, error) {
	return s.GetInvoice(ctx, id)
}

// CreateInvoice invoices a confirmed/delivered SO (§5.1). total_amount = SO
// total; subtotal/tax_amount left 0.00 (parity); SO -> invoiced in one tx.
func (s *Service) CreateInvoice(ctx context.Context, soID int64) (*Invoice, error) {
	tx, err := s.store.Begin(ctx)
	if err != nil {
		return nil, err
	}
	defer tx.Rollback(ctx)

	so, err := s.store.LockSO(ctx, tx, soID)
	if err != nil {
		return nil, err
	}
	if so.Status != "confirmed" && so.Status != "delivered" {
		return nil, ErrCannotInvoice
	}
	exists, err := s.store.InvoiceExistsForSO(ctx, tx, soID)
	if err != nil {
		return nil, err
	}
	if exists {
		return nil, ErrCannotInvoice
	}

	today := time.Now().In(s.loc).Format("2006-01-02")
	// Read payment_terms from the order snapshot first (transactional snapshot
	// §11: customer changes must not retroactively alter historical invoices).
	// Migration 000049 added sales_orders.payment_terms; fall back to the
	// customer master only for orders created before that migration.
	// 0 = cash / due immediately; >0 = credit days.
	termsDays := 0
	var orderPT int32
	if err := tx.QueryRow(ctx, `SELECT payment_terms FROM sales_orders WHERE id = $1`, so.ID).Scan(&orderPT); err == nil {
		termsDays = int(orderPT)
	} else {
		// Fallback: pre-migration order — read from customer master.
		var custPT int32
		if err2 := tx.QueryRow(ctx, `SELECT COALESCE(payment_terms, 0) FROM customers WHERE id = $1`, so.CustomerID).Scan(&custPT); err2 == nil {
			termsDays = int(custPT)
		}
	}
	due := time.Now().In(s.loc).AddDate(0, 0, termsDays).Format("2006-01-02")
	invNumber, err := s.invCode(ctx, tx)
	if err != nil {
		return nil, err
	}
	inv := &Invoice{
		InvoiceNumber:   invNumber,
		SalesOrderID:    so.ID,
		CustomerID:      so.CustomerID,
		InvoiceDate:     today,
		DueDate:         due,
		Status:          "unpaid",
		Subtotal:        decimal.Zero, // §5.1.3 parity: subtotal/tax left 0.00
		TaxAmount:       decimal.Zero,
		TotalAmount:     so.TotalAmount,
		PaidAmount:      decimal.Zero,
		RemainingAmount: so.TotalAmount,
	}
	created, err := s.store.CreateInvoice(ctx, tx, createInvoiceParams(inv))
	if err != nil {
		return nil, err
	}
	inv.ID = created.ID
	inv.CreatedAt = created.CreatedAt.Time
	inv.UpdatedAt = created.UpdatedAt.Time
	// Stamp 'invoiced' ONLY while the order still sits in a pre-fulfilment
	// status. CreateInvoice fires from onConfirm, and a confirm that arrives
	// late (after the till handed a cash pickup over, i.e. the order is
	// already delivered) must not drag that order backwards — an
	// unconditional write is what made every till handover end "invoiced"
	// with a phantom open invoice beneath it (reported 1 Nov).
	if so.Status == "draft" || so.Status == "pending_approval" || so.Status == "confirmed" {
		if err := s.store.SetSOStatus(ctx, tx, db.SetSOStatusParams{ID: so.ID, Status: "invoiced"}); err != nil {
			return nil, err
		}
		if err := s.store.InsertSOStatusHistory(ctx, tx, db.InsertSOStatusHistoryParams{
			SalesOrderID: so.ID, FromStatus: so.Status, ToStatus: "invoiced", Reason: "invoice_created",
			ReferenceType: textOrNil(InvoiceModel), ReferenceID: pgtype.Int8{Int64: inv.ID, Valid: true},
		}); err != nil {
			return nil, err
		}
	}
	if err := tx.Commit(ctx); err != nil {
		return nil, err
	}
	return inv, nil
}

// PaymentVerdict is the persisted outcome of a verify/reject review.
const (
	PaymentVerdictVerified = "verified"
	PaymentVerdictRejected = "rejected"
)

// UpdatePaymentStatus records the admin verify/reject verdict ON the payment
// row (bug sweep 2026-09: the old version acknowledged "paid" without any DB
// write or audit). Money state itself still belongs to PayInvoice — payments
// are created settled — so the verdict is stamped as an auditable review note
// ([Verified]/[Rejected] prefix) instead of a phantom status flip.
//
// reviewerID/reviewerName identify WHO decided; they come from the request
// context (handler) and are nil/empty only for a system-initiated review.
// The returned string is the exact text persisted, so the caller never has to
// re-derive (and drift from) the note format. Deliberately NOT done here:
// reversing money on reject — that would corrupt AR state and needs its own
// transaction/ledger work.
func (s *Service) UpdatePaymentStatus(ctx context.Context, paymentID int64, approved bool, reviewerID *int64, reviewerName string) (string, error) {
	verdict := "[Rejected] pembayaran ditolak"
	if approved {
		verdict = "[Verified] pembayaran diverifikasi"
	}
	switch {
	case reviewerID != nil && reviewerName != "":
		verdict = fmt.Sprintf("%s oleh #%d (%s)", verdict, *reviewerID, reviewerName)
	case reviewerID != nil:
		verdict = fmt.Sprintf("%s oleh #%d", verdict, *reviewerID)
	}
	n, err := s.store.SetPaymentReview(ctx, paymentID, verdict)
	if err != nil {
		return "", err
	}
	if n == 0 {
		return "", ErrNotFound
	}
	return verdict, nil
}

// PayInvoice applies a payment (§5.2): paid += amount, remaining = max(0, ...),
// status paid|partial, records the PAY payment in one tx. No cashflow is
// generated — Arus Kas entries are manual.
func (s *Service) PayInvoice(ctx context.Context, invoiceID int64, in PaymentInput) (*Invoice, error) {
	if in.Amount.LessThan(decimal.NewFromInt(1)) {
		return nil, errors.New("amount must be >= 1")
	}
	if !isValidPaymentMethod(in.PaymentMethod) {
		return nil, errors.New("payment_method must be cash, transfer, giro, cheque, qris or kartu")
	}
	tx, err := s.store.Begin(ctx)
	if err != nil {
		return nil, err
	}
	defer tx.Rollback(ctx)

	row, err := s.store.LockInvoice(ctx, tx, invoiceID)
	if err != nil {
		return nil, err
	}
	inv := invoiceFromRow(db.GetInvoiceByIDRow(row))
	// Only invoices that are not yet settled may be paid. A cancelled or fully
	// paid invoice must never accept further payments. Overdue invoices remain
	// payable until settled.
	if inv.Status != "unpaid" && inv.Status != "partial" && inv.Status != "overdue" {
		return nil, ErrNotPayable
	}
	if in.Amount.GreaterThan(inv.RemainingAmount) {
		return nil, ErrPaymentTooLarge
	}

	if _, err := s.recordInvoicePayment(ctx, tx, inv, in, nil); err != nil {
		return nil, err
	}
	if err := tx.Commit(ctx); err != nil {
		return nil, err
	}
	return inv, nil
}

// recordInvoicePayment is the shared payment-recording core of PayInvoice and
// RecordPartialPayment. The caller must have locked inv and validated that the
// amount fits its remaining balance; this helper then mutates the payable's
// paid/remaining/status and records the PAY payment — never bypassed with
// ad-hoc SQL so the §5.2 invariants stay in one place. Arus Kas entries are
// manual: no cashflow row is written here.
func (s *Service) recordInvoicePayment(ctx context.Context, tx Tx, inv *Invoice, in PaymentInput, createdBy *int64) (*Payment, error) {
	inv.PaidAmount = inv.PaidAmount.Add(in.Amount)
	inv.RemainingAmount = inv.RemainingAmount.Sub(in.Amount)
	if inv.RemainingAmount.IsNegative() {
		inv.RemainingAmount = decimal.Zero
	}
	if inv.RemainingAmount.IsZero() {
		inv.Status = "paid"
	} else {
		inv.Status = "partial"
	}
	if err := s.store.UpdateInvoice(ctx, tx, updateInvoiceParams(inv)); err != nil {
		return nil, err
	}

	// Synchronize sales_orders paid_amount, remaining_amount, and payment status.
	// The Exec result is checked (OBSERVABILITY 2026-09-27): it used to be
	// discarded with `_, _ =`, so a failed sync still let the caller commit —
	// the invoice read as paid while the sales order kept a stale
	// paid_amount/remaining_amount and never reached 'paid'. Returning the error
	// rolls the whole tx back (both callers defer tx.Rollback and only reach
	// Commit after this returns nil), so a payment is never recorded against an
	// unsynced order.
	if inv.SalesOrderID > 0 {
		if _, err := tx.Exec(ctx,
			`UPDATE sales_orders
			 SET paid_amount = paid_amount + $1,
			     remaining_amount = GREATEST(0, remaining_amount - $1),
			     status = CASE
			         WHEN status IN ('picking', 'shipped', 'delivered') THEN status
			         WHEN remaining_amount - $1 <= 0 AND status IN ('draft', 'confirmed', 'invoiced') THEN 'paid'
			         ELSE status
			     END,
			     updated_at = now()
			 WHERE id = $2`,
			in.Amount, inv.SalesOrderID); err != nil {
			return nil, err
		}
	}

	payNumber, err := s.payCode(ctx, tx)
	if err != nil {
		return nil, err
	}
	p := &Payment{
		PaymentNumber:   payNumber,
		PayableType:     InvoiceModel,
		PayableID:       inv.ID,
		PaymentDate:     time.Now().In(s.loc).Format("2006-01-02"),
		Amount:          in.Amount,
		PaymentMethod:   in.PaymentMethod,
		BankName:        in.BankName,
		BankAccount:     in.BankAccount,
		ReferenceNumber: in.ReferenceNumber,
		Notes:           in.Notes,
		CreatedBy:       createdBy,
	}
	p.ID, err = s.store.CreatePayment(ctx, tx, createPaymentParams(p))
	if err != nil {
		return nil, err
	}
	return p, nil
}

// RecordPartialPayment creates an ADDITIONAL payment against the SAME payable
// as an existing payment row (§5.2): the referenced payment identifies the
// payable, the amount is validated against the payable's remaining balance,
// and the payable mutation + PAY recording reuses recordInvoicePayment —
// the exact logic PayInvoice uses — inside one tx.
func (s *Service) RecordPartialPayment(ctx context.Context, paymentID int64, createdBy *int64, in PartialPaymentInput) (*Payment, error) {
	if in.Amount.LessThan(decimal.NewFromInt(1)) {
		return nil, errors.New("amount must be >= 1")
	}
	if in.PaymentMethod == "" {
		in.PaymentMethod = "cash"
	}
	if !isValidPaymentMethod(in.PaymentMethod) {
		return nil, errors.New("payment_method must be cash, transfer, giro, cheque, qris or kartu")
	}
	tx, err := s.store.Begin(ctx)
	if err != nil {
		return nil, err
	}
	defer tx.Rollback(ctx)

	row, err := s.store.LockPayment(ctx, tx, paymentID)
	if err != nil {
		return nil, err
	}
	if row.PayableType != InvoiceModel {
		return nil, ErrUnknownPayable
	}
	invRow, err := s.store.LockInvoice(ctx, tx, row.PayableID)
	if err != nil {
		return nil, err
	}
	inv := invoiceFromRow(db.GetInvoiceByIDRow(invRow))
	// Same payable rules as PayInvoice: only unsettled invoices accept money.
	if inv.Status != "unpaid" && inv.Status != "partial" && inv.Status != "overdue" {
		return nil, ErrNotPayable
	}
	if in.Amount.GreaterThan(inv.RemainingAmount) {
		return nil, ErrPaymentTooLarge
	}

	p, err := s.recordInvoicePayment(ctx, tx, inv, PaymentInput{
		Amount:        in.Amount,
		PaymentMethod: in.PaymentMethod,
		Notes:         in.Notes,
	}, createdBy)
	if err != nil {
		return nil, err
	}
	if err := tx.Commit(ctx); err != nil {
		return nil, err
	}
	return p, nil
}

// PaymentOwnerUser resolves the user linked to a payment's payable customer
// (payments -> invoice -> customers.user_id). ok=false when the linkage does
// not resolve: non-invoice payables or customers without a linked user.
func (s *Service) PaymentOwnerUser(ctx context.Context, paymentID int64) (ownerUserID int64, ok bool, err error) {
	row, err := s.store.GetPaymentByID(ctx, paymentID)
	if err != nil {
		return 0, false, err
	}
	if !row.OwnerUserID.Valid {
		return 0, false, nil
	}
	return row.OwnerUserID.Int64, true, nil
}

// SetPaymentProof persists the uploaded proof URL on the payment row.
func (s *Service) SetPaymentProof(ctx context.Context, paymentID int64, url string) error {
	n, err := s.store.SetPaymentProofURL(ctx, paymentID, url)
	if err != nil {
		return err
	}
	if n == 0 {
		return ErrNotFound
	}
	return nil
}

// ListReceivables returns unpaid/partial/paid invoices with overdue computed
// against today (§2.7).
func (s *Service) ListReceivables(ctx context.Context, f ReceivableFilter) ([]ReceivableRow, int, decimal.Decimal, error) {
	if f.Page < 1 {
		f.Page = 1
	}
	// Clamp oversized page sizes instead of resetting to the 20 default:
	// asking for MORE used to silently return LESS (per_page=200 -> 20 rows),
	// which under-reported every summary built on these lists (4 Nov audit).
	if f.PerPage < 1 {
		f.PerPage = 20
	} else if f.PerPage > 100 {
		f.PerPage = 100
	}
	rows, count, total, err := s.store.ListReceivables(ctx, f)
	if err != nil {
		return nil, 0, decimal.Zero, err
	}
	out := make([]ReceivableRow, 0, len(rows))
	for _, row := range rows {
		out = append(out, receivableFromRow(row))
	}
	today := time.Now().In(s.loc)
	for i := range out {
		d := &out[i]
		d.Overdue = d.Remaining.IsPositive() && d.DueDate < today.Format("2006-01-02")
		if d.Overdue {
			d.OverdueDays = int64(today.Sub(parseDate(d.DueDate)).Hours() / 24)
		}
	}
	return out, count, total, nil
}

func (s *Service) DeleteInvoice(ctx context.Context, id int64) error {
	tx, err := s.store.Begin(ctx)
	if err != nil {
		return err
	}
	defer tx.Rollback(ctx)

	inv, err := s.store.LockInvoice(ctx, tx, id)
	if err != nil {
		return err
	}
	if inv.Status != "unpaid" {
		return ErrInvoiceUnpaid
	}
	hasPayment, err := s.store.PaymentsExistForInvoice(ctx, tx, id)
	if err != nil {
		return err
	}
	if hasPayment {
		return ErrInvoiceHasPayment
	}
	// The creation history row is how DeleteInvoice knows which status to
	// restore. When CreateInvoice did NOT stamp one (the order was already
	// delivered/paid — see the pre-fulfilment guard at creation), deletion
	// must simply remove the invoice and leave the order untouched.
	history, histErr := s.store.InvoiceCreationStatusHistory(ctx, tx, db.GetInvoiceCreationStatusHistoryForUpdateParams{
		SalesOrderID: inv.SalesOrderID.Int64, ReferenceID: pgtype.Int8{Int64: id, Valid: true},
	})
	historyExists := histErr == nil
	if histErr != nil && !errors.Is(histErr, ErrInvoiceCreationHistoryRequired) {
		return histErr
	}
	_, err = s.store.LockSO(ctx, tx, inv.SalesOrderID.Int64)
	if err != nil {
		return err
	}
	if historyExists {
		if err := s.store.SetSOStatus(ctx, tx, db.SetSOStatusParams{ID: inv.SalesOrderID.Int64, Status: history.FromStatus}); err != nil {
			return err
		}
		if err := s.store.InsertSOStatusHistory(ctx, tx, db.InsertSOStatusHistoryParams{
			SalesOrderID: inv.SalesOrderID.Int64, FromStatus: history.ToStatus, ToStatus: history.FromStatus,
			Reason: "invoice_deleted", ReferenceType: textOrNil(InvoiceModel),
			ReferenceID: pgtype.Int8{Int64: id, Valid: true},
		}); err != nil {
			return err
		}
	}
	if err := s.store.DeleteInvoice(ctx, tx, id); err != nil {
		return err
	}
	return tx.Commit(ctx)
}

// --- cashflows --------------------------------------------------------------

// ListCashflows is READ PATH 1 of 5 (GET /cashflows). The scope is resolved here,
// in the service, because the repository has no user on its context and must not
// make an authorization decision — the same split internal/inventory uses for
// inventory.cost.view.
//
// f.StoreIDs is set from the caller's store scope, never from the request, so a
// ?store_id= query parameter cannot widen the set. See cashflow_scope.go.
func (s *Service) ListCashflows(ctx context.Context, f CashflowFilter) ([]Cashflow, int, CashflowSummary, error) {
	if err := validateDateRange(f.DateFrom, f.DateTo); err != nil {
		return nil, 0, CashflowSummary{}, err
	}
	storeIDs, err := cashflowStoreScope(ctx)
	if err != nil {
		return nil, 0, CashflowSummary{}, err
	}
	f.StoreIDs = storeIDs
	if f.Page < 1 {
		f.Page = 1
	}
	// Clamp oversized page sizes instead of resetting to the 20 default:
	// asking for MORE used to silently return LESS (per_page=200 -> 20 rows),
	// which under-reported every summary built on these lists (4 Nov audit).
	if f.PerPage < 1 {
		f.PerPage = 20
	} else if f.PerPage > 100 {
		f.PerPage = 100
	}
	rows, total, summaryRow, err := s.store.ListCashflows(ctx, f)
	if err != nil {
		return nil, 0, CashflowSummary{}, err
	}
	out := make([]Cashflow, 0, len(rows))
	for _, row := range rows {
		out = append(out, cashflowFromRow(row))
	}
	return out, total, CashflowSummary{Income: summaryRow.Income, Expense: summaryRow.Expense}, nil
}

// GetCashflow is READ PATH 2 of 5 (GET /cashflows/{id}) and the one that decides
// whether the other four mean anything. A scoped list with an UNSCOPED id fetch
// is a list with a hole in it: a till operator who cannot see a row can still
// fetch it by guessing ids, and ids are small sequential integers.
//
// The scope is pushed into the query rather than applied after the fetch. A
// post-fetch check would have to load the row first, which is already the leak.
func (s *Service) GetCashflow(ctx context.Context, id int64) (*Cashflow, error) {
	storeIDs, err := cashflowStoreScope(ctx)
	if err != nil {
		return nil, err
	}
	row, err := s.store.CashflowByID(ctx, id, storeIDs)
	if err != nil {
		return nil, err
	}
	return cashflowFromGetRow(row), nil
}

// CreateCashflow validates type/category and stores a manual entry (§6).
//
// THE OUTLET IS DERIVED HERE, FROM THE CALLER'S CONTEXT, AND NOWHERE ELSE.
// CashflowInput has no store field and must not grow one: a body that can name
// its own outlet writes rows into a store the caller does not own, and then
// reads them back through every filter in this package. The body is not a
// security boundary and this is the specific place where trusting it would
// reopen the hole migration 000104 closes.
//
// See cashflowWriteStore for the derivation and the three cases it resolves.
func (s *Service) CreateCashflow(ctx context.Context, in CashflowInput) (*Cashflow, error) {
	if err := validateCashflowInput(in); err != nil {
		return nil, err
	}
	storeID, err := cashflowWriteStore(ctx)
	if err != nil {
		return nil, err
	}
	tx, err := s.store.Begin(ctx)
	if err != nil {
		return nil, err
	}
	defer tx.Rollback(ctx)
	cfNumber, err := s.cfCode(ctx, tx)
	if err != nil {
		return nil, err
	}
	if in.AccountID == nil {
		return nil, errors.New("cash/bank account is required")
	}
	switch ptrStr(in.FlowType) {
	case "", "operating", "investing", "financing":
	default:
		return nil, errors.New("flow_type must be operating, investing or financing")
	}
	c := &Cashflow{
		TransactionNumber:     cfNumber,
		Type:                  in.Type,
		Category:              in.Category,
		TransactionDate:       in.TransactionDate,
		Amount:                in.Amount,
		Description:           in.Description,
		AccountID:             in.AccountID,
		CounterpartAccountID:  in.CounterpartAccountID,
		FlowType:              in.FlowType,
		PaymentMethod:         in.PaymentMethod,
		AttachmentKey:         in.AttachmentKey,
		AttachmentFilename:    in.AttachmentFilename,
		AttachmentContentType: in.AttachmentContentType,
		AttachmentSize:        in.AttachmentSize,
		StoreID:               storeID,
	}
	c.ID, err = s.store.CreateCashflow(ctx, tx, createCashflowParams(c))
	if err != nil {
		return nil, err
	}
	if err := tx.Commit(ctx); err != nil {
		return nil, err
	}
	return c, nil
}

// UpdateCashflow locks the row it is about to change, and the LOCK is scoped.
// An unscoped lock here would both READ and WRITE another outlet's cash: a
// till operator could name an id, lock it, and overwrite its amount.
//
// Not in the brief's five read paths, and deliberately included anyway: the
// brief listed the reads, and a write that is not scoped is a read plus a
// mutation. TestCashflowOutletScoping_MutationPathsAreScoped covers all three.
func (s *Service) UpdateCashflow(ctx context.Context, id int64, in CashflowInput) (*Cashflow, error) {
	if err := validateCashflowInput(in); err != nil {
		return nil, err
	}
	storeIDs, err := cashflowStoreScope(ctx)
	if err != nil {
		return nil, err
	}
	tx, err := s.store.Begin(ctx)
	if err != nil {
		return nil, err
	}
	defer tx.Rollback(ctx)
	if _, err := s.store.LockCashflow(ctx, tx, id, storeIDs); err != nil {
		return nil, err
	}
	row, err := s.store.UpdateCashflow(ctx, tx, updateCashflowParams(id, in))
	if err != nil {
		return nil, err
	}
	if err := tx.Commit(ctx); err != nil {
		return nil, err
	}
	return cashflowFromUpdateRow(row), nil
}

// DeleteCashflow: scoped lock, same reason as UpdateCashflow.
func (s *Service) DeleteCashflow(ctx context.Context, id int64) error {
	storeIDs, err := cashflowStoreScope(ctx)
	if err != nil {
		return err
	}
	tx, err := s.store.Begin(ctx)
	if err != nil {
		return err
	}
	defer tx.Rollback(ctx)
	if _, err := s.store.LockCashflow(ctx, tx, id, storeIDs); err != nil {
		return err
	}
	if err := s.store.DeleteCashflow(ctx, tx, id); err != nil {
		return err
	}
	return tx.Commit(ctx)
}

// ReverseCashflow records a correction for a generated cashflow (§18). The
// original row is never mutated or deleted; a new entry with the opposite
// income/expense sign, same amount and same reference is created and linked
// via reversal_of. Only generated entries (reference set) can be reversed;
// manual entries are edited directly. The accounting-period lock is enforced by
// the cashflows_contract_guard trigger.
func (s *Service) ReverseCashflow(ctx context.Context, id int64, in CashflowReverseInput) (*Cashflow, error) {
	storeIDs, err := cashflowStoreScope(ctx)
	if err != nil {
		return nil, err
	}
	tx, err := s.store.Begin(ctx)
	if err != nil {
		return nil, err
	}
	defer tx.Rollback(ctx)
	row, err := s.store.LockCashflow(ctx, tx, id, storeIDs)
	if err != nil {
		return nil, err
	}
	orig := cashflowFromGetRow(db.GetCashflowByIDRow(row))
	if orig.ReferenceType == nil || orig.ReferenceID == nil {
		return nil, ErrNotGenerated
	}
	if orig.ReversalOf != nil {
		return nil, ErrAlreadyReversed
	}
	// Reject a second reversal of the same generated entry; the partial unique
	// index on reversal_of is the database-level backstop for concurrent calls.
	already, err := s.store.CashflowAlreadyReversed(ctx, tx, id)
	if err != nil {
		return nil, err
	}
	if already {
		return nil, ErrAlreadyReversed
	}
	revType := "income"
	if orig.Type == "income" {
		revType = "expense"
	}
	desc := fmt.Sprintf("Reversal of %s", orig.TransactionNumber)
	if in.Reason != nil && *in.Reason != "" {
		desc = fmt.Sprintf("%s: %s", desc, *in.Reason)
	}
	revNumber, err := s.cfCode(ctx, tx)
	if err != nil {
		return nil, err
	}
	rev := &Cashflow{
		TransactionNumber: revNumber,
		Type:              revType,
		Category:          orig.Category,
		TransactionDate:   orig.TransactionDate,
		Amount:            orig.Amount,
		Description:       &desc,
		ReferenceType:     orig.ReferenceType,
		ReferenceID:       orig.ReferenceID,
		ReversalOf:        &id,
		// A reversal corrects a row, so it belongs to the SAME outlet. Derived
		// from the caller's context exactly as the original entry was, rather
		// than copied off the locked row: copying it would be simpler and would
		// let a platform-wide caller attach a company-level reversal to another
		// outlet's entry.
		StoreID: cashflowWriteStoreOrNil(ctx),
	}
	rev.ID, err = s.store.CreateCashflow(ctx, tx, createCashflowParams(rev))
	if err != nil {
		return nil, err
	}
	if err := tx.Commit(ctx); err != nil {
		return nil, err
	}
	return rev, nil
}

// --- codes ------------------------------------------------------------------

const (
	seqKeyInv = "inv"
	seqKeyPay = "pay"
	seqKeyCF  = "cf"
)

func isValidPaymentMethod(m string) bool {
	switch m {
	case "cash", "transfer", "giro", "cheque", "qris", "kartu", "gateway":
		return true
	}
	return false
}

func (s *Service) invCode(ctx context.Context, tx any) (string, error) {
	return s.code(ctx, tx, seqKeyInv, "INV", true)
}
func (s *Service) payCode(ctx context.Context, tx any) (string, error) {
	return s.code(ctx, tx, seqKeyPay, "PAY", true)
}
func (s *Service) cfCode(ctx context.Context, tx any) (string, error) {
	date := time.Now().In(s.loc)
	n, err := s.seq.Sequence(ctx, seqKeyCF, seq.MonthStart(date), tx)
	if err != nil {
		return "", fmt.Errorf("CF number sequence: %w", err)
	}
	return fmt.Sprintf("CF-%s-%04d", date.Format("20060102"), n), nil
}

// code allocates a document number. PAY adopts the confirmed monthly scope
// (§17, business-rules.md): PAY-YYYYMM-000001. CF keeps the legacy daily
// format. The sequence is allocated on the supplied transaction (tx) so the
// number cannot be consumed by a caller whose write later rolls back (L6).
// Sequence errors are propagated, never converted to an empty number.
func (s *Service) code(ctx context.Context, tx any, seqKey, display string, monthly bool) (string, error) {
	date := time.Now().In(s.loc)
	scope := date
	format := "20060102"
	width := 4
	if monthly {
		scope = seq.MonthStart(date)
		format = "200601"
		width = 6
	}
	n, err := s.seq.Sequence(ctx, seqKey, scope, tx)
	if err != nil {
		return "", fmt.Errorf("%s number sequence: %w", display, err)
	}
	return fmt.Sprintf("%s-%s-%0*d", display, date.Format(format), width, n), nil
}

func ptrString(s string) *string { return &s }

func validateOptionalDate(field, value string) error {
	if value == "" {
		return nil
	}
	if _, err := time.Parse("2006-01-02", value); err != nil {
		return fmt.Errorf("%s: %w", field, ErrInvalidDate)
	}
	return nil
}

func validateDateRange(from, to string) error {
	if err := validateOptionalDate("date_from", from); err != nil {
		return err
	}
	if err := validateOptionalDate("date_to", to); err != nil {
		return err
	}
	if from != "" && to != "" && from > to {
		return fmt.Errorf("date_from must not be after date_to: %w", ErrInvalidDate)
	}
	return nil
}

func validateCashflowInput(in CashflowInput) error {
	if in.Type != "income" && in.Type != "expense" {
		return errors.New("type must be income or expense")
	}
	switch in.Category {
	case "sales", "purchase", "salary", "operational", "asset", "other":
	default:
		return errors.New("category must be sales, purchase, salary, operational, asset or other")
	}
	if !in.Amount.IsPositive() {
		return errors.New("amount must be > 0")
	}
	if in.TransactionDate == "" {
		return errors.New("transaction_date is required")
	}
	return validateOptionalDate("transaction_date", in.TransactionDate)
}

func parseDate(s string) time.Time {
	t, err := time.Parse("2006-01-02", s)
	if err != nil {
		return time.Now()
	}
	return t
}

func ptrStr(p *string) string {
	if p == nil {
		return ""
	}
	return *p
}