package sales

import (
	"context"
	"net/http"
	"time"

	"github.com/shopspring/decimal"

	"github.com/TaruLab/keluargamart-backend/pkg/httpresponse"
	"github.com/TaruLab/keluargamart-backend/pkg/middleware"
)

// --- stranded Snap payments: the read-only reconciliation report ---------------
//
// Midtrans stops retrying the capture webhook roughly 5.7 hours after a
// capture. A buyer who closes the tab before it lands leaves an invoice that
// looks unpaid forever: the webhook is never coming, and the only thing that
// ever settled those was the buyer's own status poll. The background sweeper
// closes that window for the NEXT batch; it cannot help an invoice already past
// it. This report is how an operator finds those and drives them to a manual
// resolution.
//
// It is a QUERY, not a workflow, and stays that way on purpose: no write path,
// no per-invoice "recheck now" action, no UI. Resolution is a human matching
// the refs against the Midtrans dashboard and then either refunding there or
// booking the money through the already-shipped, already-permissioned
// POST /fakturisasi/invoices/{id}/pay. Everything this surface needs to do is
// therefore read, and it is the only place both refs are visible at once.

const (
	// strandedHorizon is how stale an invoice's last write must be before the
	// report is willing to call it stranded. It sits deliberately ABOVE
	// Midtrans' ~5.7h webhook retry horizon: an invoice still inside that
	// window can still be settled automatically, and listing it would only
	// teach the operator to resolve rows that were about to close themselves.
	// 12h gives the last webhook attempt room to land before the row is
	// reported.
	strandedHorizon = 12 * time.Hour

	// strandedReportDefaultLimit / strandedReportMaxLimit bound the scan. The
	// cap is not a suggestion: the report is the one query an operator can
	// point at an unbounded slice of invoices, and it must stay cheap enough
	// to run by hand during an incident.
	strandedReportDefaultLimit = 50
	strandedReportMaxLimit     = 200
)

// StrandedInvoice is one row of the stranded-payment report.
//
// BOTH refs are reported, and an operator has to try BOTH: they are not the
// same value and neither is authoritative on its own.
//
//   - SnapOrderRef is invoices.snap_order_ref — the ref stored by the LAST
//     Snap attempt.
//   - CanonicalRef is the FIRST-issue ref, re-derived with canonicalSnapRef.
//
// A SnapToken retry OVERWRITES snap_order_ref with "<canonical>-<unix>" so
// Midtrans will create a fresh transaction (a duplicate order_id is rejected).
// A buyer who paid on attempt 1 and then clicked pay again has left money at
// Midtrans under the CANONICAL ref while the row points at the retry ref. The
// superseded ref is not recoverable from the stored string, and
// canonicalSnapRef's contract says so explicitly: do not strip a trailing
// "-<timestamp>", because invoice numbers carry their own trailing digits and a
// dash-anchored strip is ambiguous. So the canonical ref is re-derived and the
// stored one is the only record of the retry. The webhook already tolerates
// this by resolving refs by prefix; this report is what lets a human do the
// same lookup in the Midtrans dashboard.
type StrandedInvoice struct {
	InvoiceNumber string          `json:"invoice_number"`
	SONumber      string          `json:"so_number"`
	OrderStatus   string          `json:"order_status"`
	Customer      string          `json:"customer"`
	TotalAmount   decimal.Decimal `json:"total_amount"`
	SnapOrderRef  string          `json:"snap_order_ref"`
	CanonicalRef  string          `json:"canonical_ref"`
	LastAttemptAt time.Time       `json:"last_attempt_at"`
}

// strandedInvoiceQuery is the report's one and only statement.
//
//   - status IN ('unpaid','partial') mirrors invoiceSettleable: an invoice with
//     no open balance has nothing to collect, and a written-off one had its
//     debt zeroed. Neither is stranded money.
//   - snap_order_ref IS NOT NULL keeps manual/POS invoices out: this report is
//     about gateway transactions, and a null ref means Midtrans has never seen
//     the invoice at all.
//   - order_type = 'ecommerce' restricts it to the storefront, the only channel
//     that settles through Snap.
//   - so.status <> 'cancelled' is redundant with the invoice-status filter
//     (canceling an order cancels its invoices in the same transaction) and is
//     here anyway: a written-off order is not reconcilable money, and the
//     guarantee belongs in the statement that makes the claim.
//   - updated_at is the last-attempt proxy because SetInvoiceSnapRef bumps it on
//     every Snap attempt, so the newest write on an open invoice IS the last
//     time the buyer touched the gateway.
//
// ORDER BY updated_at puts the longest-stranded invoice first: that is the row
// most likely to have been written off entirely, and it is where an operator
// should start. i.id breaks ties so the order is stable across calls.
const strandedInvoiceQuery = `
SELECT i.invoice_number,
       so.id,
       so.so_number,
       so.status,
       c.name,
       i.total_amount,
       i.snap_order_ref,
       i.updated_at
  FROM invoices i
  JOIN sales_orders so ON so.id = i.sales_order_id
  JOIN customers c ON c.id = i.customer_id
 WHERE i.status IN ('unpaid','partial')
   AND i.snap_order_ref IS NOT NULL
   AND so.order_type = 'ecommerce'
   AND so.status <> 'cancelled'
   AND i.updated_at < $2
 ORDER BY i.updated_at, i.id
 LIMIT $1`

// clampStrandedReportLimit bounds a caller-supplied limit. Zero or negative
// means "no preference" and takes the default; anything above the cap is cut to
// the cap rather than rejected, so a caller cannot turn a report into an
// unbounded scan by asking for one.
func clampStrandedReportLimit(limit int) int {
	switch {
	case limit <= 0:
		return strandedReportDefaultLimit
	case limit > strandedReportMaxLimit:
		return strandedReportMaxLimit
	default:
		return limit
	}
}

// ListStrandedSnapInvoices reports open Snap invoices that no automated poll
// can settle any more: the capture webhook is past Midtrans' retry horizon and
// the buyer's device is gone, so nothing will ever close them but a human.
// Each row carries BOTH the stored and the canonical Midtrans ref — see
// StrandedInvoice for why an operator has to check each of them.
//
// limit is clamped (clampStrandedReportLimit), so a caller can neither request
// an unbounded scan nor make the report expensive by accident.
//
// The report reads the local ledger only, so it stays available when the
// Midtrans keys are unset or being rotated — which is exactly when an operator
// most needs to see what is outstanding.
func (s *Service) ListStrandedSnapInvoices(ctx context.Context, limit int) ([]StrandedInvoice, error) {
	limit = clampStrandedReportLimit(limit)
	// The cutoff travels as a bound timestamp rather than an inline
	// `now() - interval` literal: the horizon stays one Go constant the tests
	// and this comment agree on, and the value is fixed for the whole scan
	// instead of being re-evaluated per row.
	cutoff := time.Now().Add(-strandedHorizon)

	// Read on the caller's own transaction instead of adding a Store method.
	// Store is the order/invoice write seam that every fake in this package
	// implements wholesale, and this is a single report-only join with no
	// reuse by the order or invoice paths — not a reason to widen that seam.
	// The transaction is never committed, so the report has no write path to
	// abort: it reads, then releases the connection.
	tx, err := s.store.Begin(ctx)
	if err != nil {
		return nil, err
	}
	defer tx.Rollback(ctx)

	rows, err := tx.Query(ctx, strandedInvoiceQuery, limit, cutoff)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	// Empty, not nil: the report answers with [] so an operator (and any
	// client) sees "nothing stranded" rather than a null body.
	out := make([]StrandedInvoice, 0)
	for rows.Next() {
		var (
			inv  StrandedInvoice
			soID int64
		)
		// snap_order_ref is NOT NULL by the predicate above, so it scans into
		// a plain string; so.id is selected only to re-derive the canonical
		// ref through the function that owns that format.
		if err := rows.Scan(
			&inv.InvoiceNumber,
			&soID,
			&inv.SONumber,
			&inv.OrderStatus,
			&inv.Customer,
			&inv.TotalAmount,
			&inv.SnapOrderRef,
			&inv.LastAttemptAt,
		); err != nil {
			return nil, err
		}
		// RE-DERIVED with the one function that mints it, not re-formatted in
		// SQL. Concatenating 'KM-' || invoice_number || '-' || so.id in the
		// query would be a second, hand-rolled copy of a format the send path
		// owns; if the two ever drift, the report hands an operator a ref
		// Midtrans has never seen. canonicalSnapRef is not modified, only
		// called.
		inv.CanonicalRef = canonicalSnapRef(inv.InvoiceNumber, soID)
		out = append(out, inv)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return out, nil
}

// StrandedInvoiceReport serves GET /sales-orders/payment-reconciliation. The
// ?limit query parameter is optional; absent, unparseable and negative values
// all take the report default (ListStrandedSnapInvoices clamps).
func (h *Handler) StrandedInvoiceReport(w http.ResponseWriter, r *http.Request) {
	rows, err := h.svc.ListStrandedSnapInvoices(r.Context(), queryInt(r.URL.Query().Get("limit")))
	if err != nil {
		// The only failure this path can produce is a database error, and
		// respondErr's fallback would answer 400 with err.Error() — driver
		// text on a report surface. Answer 500 with a safe message and let the
		// request_id carry the correlation to the logs instead.
		httpresponse.Error(w, http.StatusInternalServerError, middleware.RequestIDFrom(r.Context()),
			httpresponse.CodeInternal, httpresponse.MsgInternal, nil)
		return
	}
	httpresponse.OK(w, http.StatusOK, middleware.RequestIDFrom(r.Context()), rows)
}