package sales

import (
	"context"
	"encoding/json"
	"fmt"
	"net/http"
	"net/http/httptest"
	"os"
	"strconv"
	"strings"
	"testing"
	"time"

	"github.com/jackc/pgx/v5/pgxpool"
)

// The harness for the stranded-payment report is a REAL PostgreSQL database,
// not the package's fakeStore. The report is one SQL predicate and a hand-
// written fake never interprets SQL — a fake could only re-state the predicate
// back at us, which proves nothing. internal/sales has no database-backed seam
// of its own (see the note on TestRepositoryMarkInvoicePaid_RefusesWrittenOffInvoice
// in service_midtrans_test.go, which pins the STATEMENT for exactly that reason);
// the DATABASE_URL-gated pattern used by internal/finance and
// internal/integration is the repo's answer to the same problem and is what
// this file follows.
//
// The report's three tables are re-created in a per-test SCRATCH SCHEMA from the
// DDL copied verbatim out of migrations/000001_init_schema.sql,
// 000004_add_ecommerce.sql and 000095_midtrans_payments.sql. That is deliberate:
// the test never touches the application's own schema or rows (so it is safe
// against a developer database holding real invoices), and it does not depend on
// the target database having had every migration applied — the report's
// behaviour is fully determined by these three tables.

// reconSchemaDDL is the report's schema, verbatim from the migrations.
// employees/warehouses are id-only stubs: sales_orders references them and the
// report never reads them, but the foreign keys still have to resolve.
const reconSchemaDDL = `
CREATE TABLE customers (
    id               BIGSERIAL PRIMARY KEY,
    code             VARCHAR(30) NOT NULL UNIQUE,
    name             VARCHAR(255) NOT NULL,
    is_active        BOOLEAN NOT NULL DEFAULT true,
    created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at       TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE employees (id BIGSERIAL PRIMARY KEY);
CREATE TABLE warehouses (id BIGSERIAL PRIMARY KEY);
CREATE TABLE sales_orders (
    id               BIGSERIAL PRIMARY KEY,
    so_number        VARCHAR(50) NOT NULL UNIQUE,
    customer_id      BIGINT NOT NULL REFERENCES customers(id),
    warehouse_id     BIGINT REFERENCES warehouses(id),
    sales_id         BIGINT REFERENCES employees(id),
    order_date       DATE NOT NULL,
    delivery_date    DATE,
    status           TEXT NOT NULL DEFAULT 'draft'
                     CHECK (status IN ('draft','confirmed','picking','shipped','delivered','invoiced','paid','cancelled')),
    order_type       TEXT NOT NULL DEFAULT 'regular'
                     CHECK (order_type IN ('regular','pos','consignment','ecommerce')),
    subtotal         NUMERIC(15,2) NOT NULL DEFAULT 0,
    tax_amount       NUMERIC(15,2) NOT NULL DEFAULT 0,
    discount_amount  NUMERIC(15,2) NOT NULL DEFAULT 0,
    total_amount     NUMERIC(15,2) NOT NULL DEFAULT 0,
    paid_amount      NUMERIC(15,2) NOT NULL DEFAULT 0,
    remaining_amount NUMERIC(15,2) NOT NULL DEFAULT 0,
    shipping_cost    NUMERIC(15,2) NOT NULL DEFAULT 0,
    shipping_address TEXT,
    notes            TEXT,
    created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at       TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE invoices (
    id               BIGSERIAL PRIMARY KEY,
    invoice_number   VARCHAR(50) NOT NULL UNIQUE,
    sales_order_id   BIGINT REFERENCES sales_orders(id),
    customer_id      BIGINT NOT NULL REFERENCES customers(id),
    invoice_date     DATE NOT NULL,
    due_date         DATE NOT NULL,
    status           TEXT NOT NULL DEFAULT 'unpaid'
                     CHECK (status IN ('unpaid','partial','paid','overdue','cancelled')),
    subtotal         NUMERIC(15,2) NOT NULL DEFAULT 0,
    tax_amount       NUMERIC(15,2) NOT NULL DEFAULT 0,
    total_amount     NUMERIC(15,2) NOT NULL DEFAULT 0,
    paid_amount      NUMERIC(15,2) NOT NULL DEFAULT 0,
    remaining_amount NUMERIC(15,2) NOT NULL DEFAULT 0,
    notes            TEXT,
    created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at       TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- 000095_midtrans_payments.sql, verbatim.
ALTER TABLE invoices ADD COLUMN IF NOT EXISTS snap_order_ref TEXT UNIQUE;
`

// reconEnv is one test's scratch database.
type reconEnv struct {
	pool     *pgxpool.Pool
	svc      *Service
	customer int64
}

// newReconEnv builds an isolated schema, migrates only what the report reads,
// and seeds one customer. The schema is dropped on cleanup, so nothing survives
// the test — not in the scratch schema and not in the application's.
func newReconEnv(t *testing.T, schema string) *reconEnv {
	t.Helper()
	databaseURL := os.Getenv("DATABASE_URL")
	if databaseURL == "" {
		t.Skip("DATABASE_URL is not set")
	}
	ctx := context.Background()

	// Drop first: a previous run that died before its cleanup leaves the
	// schema behind, and CREATE SCHEMA would fail on the name.
	admin, err := pgxpool.New(ctx, databaseURL)
	if err != nil {
		t.Fatal(err)
	}
	if _, err := admin.Exec(ctx, "DROP SCHEMA IF EXISTS "+schema+" CASCADE"); err != nil {
		admin.Close()
		t.Fatal(err)
	}
	if _, err := admin.Exec(ctx, "CREATE SCHEMA "+schema); err != nil {
		admin.Close()
		t.Fatal(err)
	}
	t.Cleanup(func() {
		admin.Exec(ctx, "DROP SCHEMA IF EXISTS "+schema+" CASCADE")
		admin.Close()
	})

	cfg, err := pgxpool.ParseConfig(databaseURL)
	if err != nil {
		t.Fatal(err)
	}
	cfg.ConnConfig.RuntimeParams["search_path"] = schema
	cfg.MaxConns = 2
	pool, err := pgxpool.NewWithConfig(ctx, cfg)
	if err != nil {
		t.Fatal(err)
	}
	t.Cleanup(pool.Close)
	if _, err := pool.Exec(ctx, reconSchemaDDL); err != nil {
		t.Fatalf("apply report schema: %v", err)
	}

	env := &reconEnv{pool: pool, svc: NewService(NewRepository(pool), nil, time.FixedZone("WIB", 7*3600))}
	if err := pool.QueryRow(ctx,
		`INSERT INTO customers (code, name) VALUES ('RECON-C', 'Reconciliation Co') RETURNING id`,
	).Scan(&env.customer); err != nil {
		t.Fatal(err)
	}
	return env
}

// seedOrder writes one sales order and (optionally) its invoice. A nil snapRef
// leaves invoices.snap_order_ref NULL, and a negative age means "no invoice".
func (e *reconEnv) seedOrder(t *testing.T, soNumber, orderStatus, orderType, invStatus string, snapRef *string, age time.Duration) int64 {
	t.Helper()
	ctx := context.Background()
	var soID int64
	if err := e.pool.QueryRow(ctx,
		`INSERT INTO sales_orders (so_number, customer_id, order_date, status, order_type, total_amount, remaining_amount)
		 VALUES ($1, $2, current_date, $3, $4, 1000, 1000) RETURNING id`,
		soNumber, e.customer, orderStatus, orderType,
	).Scan(&soID); err != nil {
		t.Fatal(err)
	}
	if invStatus == "" {
		return soID
	}
	if _, err := e.pool.Exec(ctx,
		`INSERT INTO invoices (invoice_number, sales_order_id, customer_id, invoice_date, due_date,
		                       status, subtotal, tax_amount, total_amount, paid_amount, remaining_amount,
		                       snap_order_ref, updated_at)
		 VALUES ($1, $2, $3, current_date, current_date, $4, 1000, 0, 1000, 0, 1000, $5, now() - $6::interval)`,
		"INV-"+soNumber, soID, e.customer, invStatus, snapRef, age.String(),
	); err != nil {
		t.Fatal(err)
	}
	return soID
}

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

// byNumber indexes a report result for assertions.
func byNumber(rows []StrandedInvoice) map[string]StrandedInvoice {
	m := map[string]StrandedInvoice{}
	for _, r := range rows {
		m[r.InvoiceNumber] = r
	}
	return m
}

// TestListStrandedSnapInvoices_Predicate is the behavioural test for the one
// thing the report decides: which invoices are stranded. Every case the operator
// could act on wrongly is seeded, not just the happy path.
func TestListStrandedSnapInvoices_Predicate(t *testing.T) {
	env := newReconEnv(t, "recon_predicate")
	ctx := context.Background()

	const (
		old    = 20 * time.Hour // comfortably past the 12h horizon
		recent = 1 * time.Hour  // inside it
	)
	retryRef := func(soNumber string) *string {
		return reconStrPtr("KM-INV-" + soNumber + "-x-1700000000")
	}

	env.seedOrder(t, "SO-STRANDED", "invoiced", "ecommerce", "unpaid", retryRef("SO-STRANDED"), old)
	env.seedOrder(t, "SO-PAID", "invoiced", "ecommerce", "paid", retryRef("SO-PAID"), old)
	env.seedOrder(t, "SO-NOREF", "invoiced", "ecommerce", "unpaid", nil, old)
	env.seedOrder(t, "SO-FRESH", "invoiced", "ecommerce", "unpaid", retryRef("SO-FRESH"), recent)
	env.seedOrder(t, "SO-CANCELLED-SO", "cancelled", "ecommerce", "unpaid", retryRef("SO-CANCELLED-SO"), old)
	env.seedOrder(t, "SO-NOT-ECOM", "invoiced", "regular", "unpaid", retryRef("SO-NOT-ECOM"), old)
	// A cancelled INVOICE on a live order: the written-off debt, which is not
	// stranded money either (it was deliberately forgiven).
	env.seedOrder(t, "SO-WRITTEN-OFF", "invoiced", "ecommerce", "cancelled", retryRef("SO-WRITTEN-OFF"), old)

	rows, err := env.svc.ListStrandedSnapInvoices(ctx, 0)
	if err != nil {
		t.Fatal(err)
	}
	got := byNumber(rows)
	if len(got) != len(rows) {
		t.Fatalf("duplicate invoice numbers in report: %+v", rows)
	}

	for _, want := range []string{"INV-SO-STRANDED"} {
		if _, ok := got[want]; !ok {
			t.Errorf("an open Snap invoice past the horizon is not reported: want %s, got %+v", want, rows)
		}
	}
	for _, unwanted := range []struct {
		inv string
		why string
	}{
		{"INV-SO-PAID", "a paid invoice is settled, not stranded"},
		{"INV-SO-NOREF", "no snap_order_ref means Midtrans was never involved"},
		{"INV-SO-FRESH", "inside the horizon the webhook can still land"},
		{"INV-SO-CANCELLED-SO", "a cancelled order's debt was written off"},
		{"INV-SO-NOT-ECOM", "only the storefront settles through Snap"},
		{"INV-SO-WRITTEN-OFF", "a cancelled invoice is a written-off debt"},
	} {
		if _, ok := got[unwanted.inv]; ok {
			t.Errorf("report includes %s (%s): %+v", unwanted.inv, unwanted.why, rows)
		}
	}
	if len(rows) != 1 {
		t.Fatalf("report returned %d rows, want exactly 1: %+v", len(rows), rows)
	}
}

// TestListStrandedSnapInvoices_OrdersLongestStrandedFirst: the operator starts
// with the row most likely to have been abandoned outright.
func TestListStrandedSnapInvoices_OrdersLongestStrandedFirst(t *testing.T) {
	env := newReconEnv(t, "recon_order")
	ctx := context.Background()

	env.seedOrder(t, "SO-NEWER", "invoiced", "ecommerce", "unpaid", reconStrPtr("KM-INV-SO-NEWER-x-1"), 13*time.Hour)
	env.seedOrder(t, "SO-OLDER", "invoiced", "ecommerce", "unpaid", reconStrPtr("KM-INV-SO-OLDER-x-1"), 40*time.Hour)

	rows, err := env.svc.ListStrandedSnapInvoices(ctx, 0)
	if err != nil {
		t.Fatal(err)
	}
	if len(rows) != 2 {
		t.Fatalf("want 2 rows, got %+v", rows)
	}
	if rows[0].InvoiceNumber != "INV-SO-OLDER" {
		t.Errorf("first row = %s, want the longest-stranded INV-SO-OLDER", rows[0].InvoiceNumber)
	}
}

// TestListStrandedSnapInvoices_CarriesBothRefs is the reason this endpoint
// exists: snap_order_ref and the canonical ref are DIFFERENT values after a
// SnapToken retry, only one of them is stored, and an operator has to be able
// to search Midtrans with each.
func TestListStrandedSnapInvoices_CarriesBothRefs(t *testing.T) {
	env := newReconEnv(t, "recon_refs")
	ctx := context.Background()

	// A retry overwrote snap_order_ref with "<canonical>-<unix>", exactly as
	// SnapToken does, so the stored ref is NOT the canonical one.
	stored := "KM-INV-SO-RETRY-1700000000"
	soID := env.seedOrder(t, "SO-RETRY", "invoiced", "ecommerce", "unpaid", &stored, 20*time.Hour)

	rows, err := env.svc.ListStrandedSnapInvoices(ctx, 0)
	if err != nil {
		t.Fatal(err)
	}
	if len(rows) != 1 {
		t.Fatalf("want 1 row, got %+v", rows)
	}
	got := rows[0]

	if got.SnapOrderRef != stored {
		t.Errorf("snap_order_ref = %q, want the stored value %q", got.SnapOrderRef, stored)
	}
	// The documented canonical format: KM-<invoice_number>-<sales_order_id>.
	wantCanonical := fmt.Sprintf("KM-%s-%d", got.InvoiceNumber, soID)
	if got.CanonicalRef != wantCanonical {
		t.Errorf("canonical_ref = %q, want %q", got.CanonicalRef, wantCanonical)
	}
	// And it must agree with the single function that mints refs at send
	// time, so the report can never drift from the format SnapToken issues.
	if got.CanonicalRef != canonicalSnapRef(got.InvoiceNumber, soID) {
		t.Errorf("canonical_ref = %q, canonicalSnapRef says %q", got.CanonicalRef, canonicalSnapRef(got.InvoiceNumber, soID))
	}
	if got.CanonicalRef == got.SnapOrderRef {
		t.Errorf("both refs are %q; a retried invoice must report two DISTINCT refs or the operator has nothing to try", got.CanonicalRef)
	}
	// The superseded ref is not recoverable by stripping the suffix, so the
	// canonical one has to be derivable on its own terms.
	if !strings.HasPrefix(got.CanonicalRef, "KM-"+got.InvoiceNumber+"-") {
		t.Errorf("canonical_ref %q is not KM-<invoice_number>-<sales_order_id>", got.CanonicalRef)
	}
	if _, err := strconv.ParseInt(strings.TrimPrefix(got.CanonicalRef, "KM-"+got.InvoiceNumber+"-"), 10, 64); err != nil {
		t.Errorf("canonical_ref %q does not end in a sales order id: %v", got.CanonicalRef, err)
	}
}

// TestListStrandedSnapInvoices_ClampsLimit: a caller must not be able to turn
// the report into an unbounded scan.
func TestListStrandedSnapInvoices_ClampsLimit(t *testing.T) {
	env := newReconEnv(t, "recon_limit")
	ctx := context.Background()

	const stranded = 205 // more than the cap
	if _, err := env.pool.Exec(ctx,
		`INSERT INTO sales_orders (so_number, customer_id, order_date, status, order_type, total_amount, remaining_amount)
		 SELECT 'SO-BULK-' || g, $1, current_date, 'invoiced', 'ecommerce', 100, 100
		   FROM generate_series(1, $2) g`, env.customer, stranded,
	); err != nil {
		t.Fatal(err)
	}
	if _, err := env.pool.Exec(ctx,
		`INSERT INTO invoices (invoice_number, sales_order_id, customer_id, invoice_date, due_date,
		                       status, subtotal, tax_amount, total_amount, paid_amount, remaining_amount,
		                       snap_order_ref, updated_at)
		 SELECT 'INV-BULK-' || so.id, so.id, $1, current_date, current_date, 'unpaid', 100, 0, 100, 0, 100,
		        'KM-INV-BULK-' || so.id || '-' || so.id, now() - interval '20 hours'
		   FROM sales_orders so
		  WHERE so.so_number LIKE 'SO-BULK-%'`, env.customer,
	); err != nil {
		t.Fatal(err)
	}

	for _, tc := range []struct {
		name  string
		limit int
		want  int
	}{
		{"over the cap is cut to the cap", 100000, strandedReportMaxLimit},
		{"one over the cap is cut to the cap", strandedReportMaxLimit + 1, strandedReportMaxLimit},
		{"at the cap is honoured", strandedReportMaxLimit, strandedReportMaxLimit},
		{"under the cap is honoured", 3, 3},
		{"no preference takes the default", 0, strandedReportDefaultLimit},
		{"a negative limit takes the default", -7, strandedReportDefaultLimit},
	} {
		t.Run(tc.name, func(t *testing.T) {
			rows, err := env.svc.ListStrandedSnapInvoices(ctx, tc.limit)
			if err != nil {
				t.Fatal(err)
			}
			if len(rows) != tc.want {
				t.Errorf("limit %d returned %d rows, want %d", tc.limit, len(rows), tc.want)
			}
		})
	}
}

// TestClampStrandedReportLimit pins the clamp itself. It needs no database, so
// it runs in environments where the rest of this file skips.
func TestClampStrandedReportLimit(t *testing.T) {
	for _, tc := range []struct{ in, want int }{
		{0, strandedReportDefaultLimit},
		{-1, strandedReportDefaultLimit},
		{-100000, strandedReportDefaultLimit},
		{1, 1},
		{50, 50},
		{strandedReportMaxLimit, strandedReportMaxLimit},
		{strandedReportMaxLimit + 1, strandedReportMaxLimit},
		{1 << 30, strandedReportMaxLimit},
	} {
		if got := clampStrandedReportLimit(tc.in); got != tc.want {
			t.Errorf("clampStrandedReportLimit(%d) = %d, want %d", tc.in, got, tc.want)
		}
	}
}

// TestStrandedInvoiceReport_HTTP covers the wire shape an operator consumes:
// the api-design envelope, and an EMPTY report as [] rather than null so
// "nothing is stranded" is unambiguous.
func TestStrandedInvoiceReport_HTTP(t *testing.T) {
	env := newReconEnv(t, "recon_http")
	h := NewHandler(env.svc)

	rec := httptest.NewRecorder()
	h.StrandedInvoiceReport(rec, httptest.NewRequest(http.MethodGet, "/api/v1/sales-orders/payment-reconciliation", nil))
	if rec.Code != http.StatusOK {
		t.Fatalf("empty report status = %d, want 200: %s", rec.Code, rec.Body.String())
	}
	var empty struct {
		Data []StrandedInvoice `json:"data"`
	}
	if err := json.Unmarshal(rec.Body.Bytes(), &empty); err != nil {
		t.Fatalf("decode envelope: %v (%s)", err, rec.Body.String())
	}
	if len(empty.Data) != 0 {
		t.Errorf("empty report returned %d rows", len(empty.Data))
	}
	if !strings.Contains(rec.Body.String(), `"data":[]`) {
		t.Errorf("empty report must serialise data as [], got %s", rec.Body.String())
	}

	// With a stranded invoice, the handler surfaces both refs on the wire.
	env.seedOrder(t, "SO-WIRE", "invoiced", "ecommerce", "unpaid", reconStrPtr("KM-INV-SO-WIRE-1700000000"), 30*time.Hour)
	rec = httptest.NewRecorder()
	h.StrandedInvoiceReport(rec, httptest.NewRequest(http.MethodGet, "/api/v1/sales-orders/payment-reconciliation?limit=5", nil))
	if rec.Code != http.StatusOK {
		t.Fatalf("report status = %d, want 200: %s", rec.Code, rec.Body.String())
	}
	var full struct {
		Data []map[string]any `json:"data"`
	}
	if err := json.Unmarshal(rec.Body.Bytes(), &full); err != nil {
		t.Fatalf("decode envelope: %v (%s)", err, rec.Body.String())
	}
	if len(full.Data) != 1 {
		t.Fatalf("want 1 row, got %s", rec.Body.String())
	}
	row := full.Data[0]
	for _, field := range []string{"invoice_number", "so_number", "order_status", "customer",
		"total_amount", "snap_order_ref", "canonical_ref", "last_attempt_at"} {
		if _, ok := row[field]; !ok {
			t.Errorf("report row omits %q: %s", field, rec.Body.String())
		}
	}
	canonical, ok := row["canonical_ref"].(string)
	if !ok || !strings.HasPrefix(canonical, "KM-INV-SO-WIRE-") {
		t.Errorf("canonical_ref = %v, want KM-INV-SO-WIRE-<sales_order_id>", row["canonical_ref"])
	}
	if stored, ok := row["snap_order_ref"].(string); !ok || stored != "KM-INV-SO-WIRE-1700000000" {
		t.Errorf("snap_order_ref = %v, want the stored retry ref KM-INV-SO-WIRE-1700000000", row["snap_order_ref"])
	}
}