package reports

import (
	"context"
	"sort"
	"time"

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

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

// Repository implements Store with aggregate queries via sqlc
// (read-only; mirrors Laravel KPI queries, migration-plan §5.7).
type Repository struct {
	pool *pgxpool.Pool
	q    *db.Queries
}

func NewRepository(pool *pgxpool.Pool) *Repository {
	return &Repository{pool: pool, q: db.New(pool)}
}

func (r *Repository) MonthSales(ctx context.Context, from, to time.Time) (decimal.Decimal, error) {
	return r.q.SumMonthSales(ctx, db.SumMonthSalesParams{
		OrderDate:   pgdate(from),
		OrderDate_2: pgdate(to),
	})
}

func (r *Repository) MonthPurchase(ctx context.Context, from, to time.Time) (decimal.Decimal, error) {
	return r.q.SumMonthPurchase(ctx, db.SumMonthPurchaseParams{
		OrderDate:   pgdate(from),
		OrderDate_2: pgdate(to),
	})
}

// ReceivablesBalance sums remaining on unpaid/partial invoices (§10) — note
// there is no stored 'overdue' status; overdue is computed on the fly.
func (r *Repository) ReceivablesBalance(ctx context.Context) (decimal.Decimal, error) {
	return r.q.SumReceivablesBalance(ctx)
}

// PayablesBalance sums remaining on partial/received POs (§10).
func (r *Repository) PayablesBalance(ctx context.Context) (decimal.Decimal, error) {
	return r.q.SumPayablesBalance(ctx)
}

func (r *Repository) LowStockCount(ctx context.Context) (int, error) {
	n, err := r.q.CountLowStockProducts(ctx)
	return int(n), err
}

func (r *Repository) RecentActivity(ctx context.Context, limit int) ([]RecentActivity, error) {
	rows, err := r.q.ListRecentActivity(ctx, int32(limit))
	if err != nil {
		return nil, err
	}
	out := make([]RecentActivity, 0, len(rows))
	for _, rw := range rows {
		out = append(out, RecentActivity{
			ID:          rw.ID,
			UserID:      int8Ptr(rw.UserID),
			Module:      textOr(rw.Module),
			Action:      textOr(rw.Action),
			Description: textPtr(rw.Description),
			CreatedAt:   rw.CreatedAt,
		})
	}
	return out, nil
}

func (r *Repository) SalesChart(ctx context.Context, from time.Time, months int) ([]SalesChartPoint, error) {
	rows, err := r.pool.Query(ctx, `
SELECT to_char(date_trunc('month', order_date) AT TIME ZONE 'UTC', 'YYYY-MM') AS month,
       COALESCE(SUM(total_amount),0)::numeric AS total,
       COUNT(*)::int AS orders
FROM sales_orders
WHERE order_date >= $1 AND status <> 'cancelled'
GROUP BY 1 ORDER BY 1 LIMIT $2`, pgdate(from), months)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := make([]SalesChartPoint, 0, months)
	for rows.Next() {
		var pt SalesChartPoint
		if err := rows.Scan(&pt.Month, &pt.Total, &pt.Orders); err != nil {
			return nil, err
		}
		out = append(out, pt)
	}
	return out, nil
}

func (r *Repository) TodaySales(ctx context.Context, today time.Time) (decimal.Decimal, error) {
	var sum decimal.Decimal
	err := r.pool.QueryRow(ctx, `
SELECT COALESCE(SUM(total_amount), 0)::numeric
FROM sales_orders
WHERE order_date = $1::date AND status <> 'cancelled'`, pgdate(today)).Scan(&sum)
	return sum, err
}

func (r *Repository) PendingOrdersCount(ctx context.Context) (int, error) {
	var count int
	err := r.pool.QueryRow(ctx, `
SELECT count(*)
FROM sales_orders
WHERE status IN (`+activeOrderStatuses+`)`).Scan(&count)
	return count, err
}

// Pool exposes the underlying pool for KPI blocks that need one-off
// aggregate queries (store-count card, 000151).
func (r *Repository) Pool() *pgxpool.Pool { return r.pool }

func (r *Repository) ActivePOsCount(ctx context.Context) (int, error) {
	var count int
	err := r.pool.QueryRow(ctx, `
SELECT count(*)
FROM purchase_orders
WHERE status IN ('draft', 'approved', 'sent', 'partial', 'received')
  AND remaining_amount > 0`).Scan(&count)
	return count, err
}

func (r *Repository) OverdueInvoicesCount(ctx context.Context, today time.Time) (int, error) {
	var count int
	err := r.pool.QueryRow(ctx, `
SELECT count(*)
FROM invoices
WHERE status IN ('unpaid', 'partial')
  AND due_date IS NOT NULL AND due_date < $1::date`, pgdate(today)).Scan(&count)
	return count, err
}

func (r *Repository) MonthNetCashflow(ctx context.Context, from, to time.Time) (decimal.Decimal, error) {
	var net decimal.Decimal
	err := r.pool.QueryRow(ctx, `
WITH all_cf AS (
  SELECT p.amount AS amount, 'in'::text AS dir
  FROM payments p
  WHERE p.payment_date >= $1::date AND p.payment_date < $2::date
  UNION ALL
  SELECT po.paid_amount AS amount, 'out'::text AS dir
  FROM purchase_orders po
  WHERE po.paid_amount > 0 AND po.order_date >= $1::date AND po.order_date < $2::date
  UNION ALL
  SELECT cf.amount AS amount,
         CASE WHEN cf.type = 'income' THEN 'in'::text ELSE 'out'::text END AS dir
  FROM cashflows cf
  WHERE cf.transaction_date >= $1::date AND cf.transaction_date < $2::date
)
SELECT COALESCE(SUM(CASE WHEN dir = 'in' THEN amount ELSE -amount END), 0)::numeric
FROM all_cf`, pgdate(from), pgdate(to)).Scan(&net)
	return net, err
}

func (r *Repository) CashflowBreakdown(ctx context.Context, from, to time.Time) ([]CashflowBreakdownRow, error) {
	rows, err := r.q.CashflowYearBreakdown(ctx, db.CashflowYearBreakdownParams{
		TransactionDate:  pgdate(from),
		TransactionDate2: pgdate(to),
	})
	if err != nil {
		return nil, err
	}
	out := make([]CashflowBreakdownRow, 0, len(rows))
	for _, rw := range rows {
		out = append(out, CashflowBreakdownRow{Month: rw.Month, Type: rw.Type, Category: rw.Category, Total: rw.Total})
	}
	return out, nil
}

func (r *Repository) BalanceSheetSnapshot(ctx context.Context) (decimal.Decimal, decimal.Decimal, error) {
	snap, err := r.q.BalanceSheetSnapshot(ctx)
	if err != nil {
		return decimal.Decimal{}, decimal.Decimal{}, err
	}
	return snap.Cash, snap.Inventory, nil
}

func (r *Repository) StockSummary(ctx context.Context) (decimal.Decimal, int, error) {
	var stockValue decimal.Decimal
	var totalProducts int
	// Per-product aggregation (GREATEST(0, …)) so the KPI equals the sum of
	// the per-product stock-valuation list (dashboard modal, /inventory/stock):
	// negative on-hand rows of one product must not cancel a sibling
	// warehouse's positive rows of the same product.
	err := r.pool.QueryRow(ctx, `
SELECT COALESCE(SUM(v.value), 0), COUNT(*)
FROM (
  SELECT SUM(GREATEST(0, s.quantity) * p.purchase_price) AS value
  FROM stocks s
  JOIN products p ON p.id = s.product_id
  WHERE p.deleted_at IS NULL
  GROUP BY s.product_id
  HAVING SUM(GREATEST(0, s.quantity)) > 0
) v
`).Scan(&stockValue, &totalProducts)
	return stockValue, totalProducts, err
}

// FixedAssetsBalance is the net book value of fixed assets: COA 120101
// (Aset Tetap Peralatan & Mesin, debit-normal) minus 120102 (Akumulasi
// Penyusutan Aset, credit-normal contra-asset), reusing the same
// journal-lines-derived balance mechanism as finance.Hardening's
// AccountBalances ("Saldo & Aset" cards) instead of a new subledger.
func (r *Repository) FixedAssetsBalance(ctx context.Context) (decimal.Decimal, error) {
	rows, err := r.q.AccountBalancesByCode(ctx, []string{"120101", "120102"})
	if err != nil {
		return decimal.Decimal{}, err
	}
	var cost, accumDep decimal.Decimal
	for _, row := range rows {
		switch row.Code {
		case "120101":
			cost = row.Debit.Sub(row.Credit)
		case "120102":
			accumDep = row.Credit.Sub(row.Debit)
		}
	}
	return cost.Sub(accumDep), nil
}

func (r *Repository) SalesBreakdown(ctx context.Context, from, to time.Time) ([]BreakdownRow, error) {
	rows, err := r.q.SalesReportBreakdown(ctx, db.SalesReportBreakdownParams{
		OrderDate:   pgdate(from),
		OrderDate_2: pgdate(to),
	})
	if err != nil {
		return nil, err
	}
	out := make([]BreakdownRow, 0, len(rows))
	for _, rw := range rows {
		out = append(out, BreakdownRow{Kind: rw.Kind, Label: rw.Label, Orders: int(rw.Orders), Total: rw.Total})
	}
	return out, nil
}

func (r *Repository) StockBreakdown(ctx context.Context) ([]BreakdownRow, error) {
	rows, err := r.q.StockReportBreakdown(ctx)
	if err != nil {
		return nil, err
	}
	out := make([]BreakdownRow, 0, len(rows))
	for _, rw := range rows {
		out = append(out, BreakdownRow{Kind: rw.Kind, Label: rw.Label, Qty: rw.Qty, Total: rw.Value})
	}
	return out, nil
}

func (r *Repository) ReceivableRows(ctx context.Context) ([]CSVRow, error) {
	rows, err := r.q.ReceivableCSVRows(ctx)
	if err != nil {
		return nil, err
	}
	out := make([]CSVRow, 0, len(rows))
	for _, rw := range rows {
		out = append(out, CSVRow{
			ID:         rw.ID,
			Number:     rw.InvoiceNumber,
			PartnerID:  rw.CustomerID,
			Partner:    rw.Partner,
			Date:       rw.Date,
			DueDate:    rw.DueDate,
			Status:     rw.Status,
			Amount:     rw.TotalAmount,
			PaidAmount: rw.PaidAmount,
			Remaining:  rw.RemainingAmount,
			RefType:    textPtr(rw.ReferenceType),
			RefID:      int8Ptr(rw.ReferenceID),
		})
	}
	return out, nil
}

func (r *Repository) DebtRows(ctx context.Context) ([]CSVRow, error) {
	rows, err := r.q.DebtCSVRows(ctx)
	if err != nil {
		return nil, err
	}
	out := make([]CSVRow, 0, len(rows))
	for _, rw := range rows {
		out = append(out, CSVRow{
			ID:         rw.ID,
			Number:     rw.PoNumber,
			PartnerID:  rw.SupplierID,
			Partner:    rw.Partner,
			Date:       rw.Date,
			DueDate:    rw.DueDate,
			Status:     rw.Status,
			Amount:     rw.TotalAmount,
			PaidAmount: rw.PaidAmount,
			Remaining:  rw.RemainingAmount,
			RefType:    textPtr(rw.ReferenceType),
			RefID:      int8Ptr(rw.ReferenceID),
		})
	}
	return out, nil
}

// CashflowRows carries f.StoreIDs into the query. The UNION has three arms and
// only the third reads cashflows, so only the third can be outlet-scoped; the
// first two are payments and purchase_orders, which have no store_id. That gap
// is a finding, not an oversight — see the CashflowCSVRows comment.
func (r *Repository) CashflowRows(ctx context.Context, f ReportFilter) ([]CSVRow, error) {
	rows, err := r.q.CashflowCSVRows(ctx, db.CashflowCSVRowsParams{
		DateFrom: dateOrNil(f.DateFrom),
		DateTo:   dateOrNil(f.DateTo),
		StoreIds: f.StoreIDs,
	})
	if err != nil {
		return nil, err
	}
	out := make([]CSVRow, 0, len(rows))
	for _, rw := range rows {
		out = append(out, CSVRow{
			ID:         rw.ID,
			Number:     rw.TransactionNumber,
			PartnerID:  rw.PartnerID,
			Partner:    rw.Partner,
			Kind:       rw.Kind,
			Date:       rw.Date,
			DueDate:    rw.DueDate,
			Status:     rw.Status,
			Amount:     rw.Amount,
			PaidAmount: rw.PaidAmount,
			Remaining:  rw.RemainingAmount,
			Category:   rw.Category,
			RefType:    strPtr(rw.ReferenceType),
			RefID:      int64Ptr(rw.ReferenceID),
		})
	}
	return out, nil
}

func (r *Repository) NewCustomersCount(ctx context.Context, from, to time.Time) (int, error) {
	var n int64
	err := r.pool.QueryRow(ctx, `SELECT count(*) FROM customers WHERE created_at >= $1 AND created_at < $2 AND deleted_at IS NULL`, from, to).Scan(&n)
	return int(n), err
}

func (r *Repository) RecentCustomers(ctx context.Context, limit int) ([]RecentCustomer, error) {
	rows, err := r.pool.Query(ctx, `SELECT id, code, name, COALESCE(city,''), created_at::text FROM customers WHERE deleted_at IS NULL ORDER BY created_at DESC, id DESC LIMIT $1`, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []RecentCustomer
	for rows.Next() {
		var rc RecentCustomer
		if err := rows.Scan(&rc.ID, &rc.Code, &rc.Name, &rc.City, &rc.CreatedAt); err != nil {
			return nil, err
		}
		out = append(out, rc)
	}
	return out, rows.Err()
}

func (r *Repository) VisitStats(ctx context.Context, day time.Time) (int, int, error) {
	var today, completed int64
	err := r.pool.QueryRow(ctx, `SELECT count(*) , count(*) FILTER (WHERE status='completed') FROM sales_visits WHERE visit_date = $1`, pgdate(day)).Scan(&today, &completed)
	return int(today), int(completed), err
}

func (r *Repository) ActivitiesPendingCount(ctx context.Context) (int, error) {
	var n int64
	err := r.pool.QueryRow(ctx, `SELECT count(*) FROM sales_activities WHERE COALESCE(result,'') = ''`).Scan(&n)
	return int(n), err
}

// ActiveAOCount counts Active Outlets for the calendar month [from, to):
// distinct customers with at least one non-cancelled sales invoice
// (nota/faktur) dated in the window. Passive stores — registered but
// orderless this month — are ignored by design: the count answers "how many
// outlets actually bought", not "how many exist". AO is a customer/account
// status, not "orders placed by office staff" and not "sales reps who visited
// today".
//
// The window comes from the caller (the KPI service) rather than
// date_trunc('month', CURRENT_DATE): the DB session runs in UTC while the
// dashboard month is Asia/Jakarta, so on the 1st of every month the
// DB-truncated window still pointed at the previous month for seven hours —
// AO Aktif and the other month KPIs then disagreed across midnight WIB.
func (r *Repository) ActiveAOCount(ctx context.Context, from, to time.Time) (int, error) {
	var n int64
	err := r.pool.QueryRow(ctx, `
		SELECT count(DISTINCT customer_id) FROM invoices
		WHERE status <> 'cancelled'
		  AND invoice_date >= $1 AND invoice_date < $2
	`, pgdate(from), pgdate(to)).Scan(&n)
	return int(n), err
}

// CustomerCounts returns customer base statistics relative to [from, to):
// - existing: count of customers created before 'from' (deleted_at IS NULL)
// - noo: count of new customers created in [from, to) (New Open Outlet, deleted_at IS NULL)
// - total: total customers (existing + noo, created before 'to', deleted_at IS NULL)
func (r *Repository) CustomerCounts(ctx context.Context, from, to time.Time) (int, int, int, error) {
	var existing, noo, total int64
	err := r.pool.QueryRow(ctx, `
		SELECT
			count(*) FILTER (WHERE created_at < $1) AS existing,
			count(*) FILTER (WHERE created_at >= $1 AND created_at < $2) AS noo,
			count(*) FILTER (WHERE created_at < $2) AS total
		FROM customers
		WHERE deleted_at IS NULL
	`, from, to).Scan(&existing, &noo, &total)
	return int(existing), int(noo), int(total), err
}

func (r *Repository) AOPerformance(ctx context.Context, day time.Time) ([]AOPerformanceRow, error) {
	rows, err := r.pool.Query(ctx, `
		SELECT v.sales_id, COALESCE(e.name, 'AO #'||v.sales_id::text),
		       count(*)::int,
		       count(*) FILTER (WHERE v.status='completed')::int,
		       count(*) FILTER (WHERE v.status NOT IN ('completed','cancelled'))::int
		FROM sales_visits v LEFT JOIN employees e ON e.id=v.sales_id
		WHERE v.visit_date = $1
		GROUP BY v.sales_id, e.name
		ORDER BY count(*) DESC LIMIT 6`, pgdate(day))
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []AOPerformanceRow
	for rows.Next() {
		var pr AOPerformanceRow
		if err := rows.Scan(&pr.SalesID, &pr.SalesName, &pr.VisitsToday, &pr.Completed, &pr.Pending); err != nil {
			return nil, err
		}
		out = append(out, pr)
	}
	return out, rows.Err()
}

// --- converters ------------------------------------------------------------

func pgdate(t time.Time) pgtype.Date {
	return pgtype.Date{Time: t, Valid: true}
}

func dateOrNil(s string) pgtype.Date {
	if s == "" {
		return pgtype.Date{}
	}
	if t, err := time.Parse("2006-01-02", s); err == nil {
		return pgtype.Date{Time: t, Valid: true}
	}
	return pgtype.Date{}
}

func textOr(t pgtype.Text) string {
	if !t.Valid {
		return ""
	}
	return t.String
}

func textPtr(t pgtype.Text) *string {
	if !t.Valid {
		return nil
	}
	v := t.String
	return &v
}

func int8Ptr(t pgtype.Int8) *int64 {
	if !t.Valid {
		return nil
	}
	v := t.Int64
	return &v
}

func strPtr(s string) *string {
	if s == "" {
		return nil
	}
	return &s
}

func int64Ptr(n int64) *int64 {
	if n == 0 {
		return nil
	}
	return &n
}

// --- POS laporan extras (raw SQL; ponytail: one-off aggregates, move to
// db/query/reports.sql + sqlc regen if the set grows) ---

// DayPoint is one day of revenue in a period breakdown.
type DayPoint struct {
	Label   string
	Revenue decimal.Decimal
}

// MethodTotal is revenue grouped by payment method.
type MethodTotal struct {
	Method  string
	Revenue decimal.Decimal
}

// NameUnits is one product's sold quantity for the top-products chart.
type NameUnits struct {
	Name  string
	Units decimal.Decimal
}

// SalesExtras carries the POS laporan breakdowns for one period.
type SalesExtras struct {
	ByDay     []DayPoint
	ByPayment []MethodTotal
	TopUnits  []NameUnits
	ItemsSold decimal.Decimal
	Done      int
	Pending   int
	Cancelled int
	DaySplit  []struct {
		Label         string
		Market, Galon decimal.Decimal
	}
	Market decimal.Decimal
	Galon  decimal.Decimal
}

const invoicePayableType = `App\Models\Invoice`

// SalesReportExtras aggregates the daily/payment/product-unit/status splits
// the POS laporan page renders. All windows follow order_date so the numbers
// reconcile with SalesBreakdown.
func (r *Repository) SalesReportExtras(ctx context.Context, from, to time.Time) (*SalesExtras, error) {
	ex := &SalesExtras{}

	// Daily market revenue (sales_orders).
	rows, err := r.pool.Query(ctx, `
SELECT order_date::text, COALESCE(SUM(total_amount),0)
FROM sales_orders
WHERE order_date >= $1 AND order_date < $2 AND status <> 'cancelled'
GROUP BY 1 ORDER BY 1`, pgdate(from), pgdate(to))
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	marketByDay := map[string]decimal.Decimal{}
	for rows.Next() {
		var d string
		var v decimal.Decimal
		if err := rows.Scan(&d, &v); err != nil {
			return nil, err
		}
		marketByDay[d] = v
		ex.Market = ex.Market.Add(v)
	}

	// Galon revenue per day (completed transactions).
	galonRows, err := r.pool.Query(ctx, `
SELECT created_at::date::text, COALESCE(SUM(quantity * unit_price),0)
FROM galon_transactions
WHERE status = 'completed' AND created_at::date >= $1 AND created_at::date < $2
GROUP BY 1 ORDER BY 1`, from.Format("2006-01-02"), to.Format("2006-01-02"))
	if err != nil {
		return nil, err
	}
	defer galonRows.Close()
	galonByDay := map[string]decimal.Decimal{}
	for galonRows.Next() {
		var d string
		var v decimal.Decimal
		if err := galonRows.Scan(&d, &v); err != nil {
			return nil, err
		}
		galonByDay[d] = v
		ex.Galon = ex.Galon.Add(v)
	}

	for d, m := range marketByDay {
		g := galonByDay[d]
		ex.DaySplit = append(ex.DaySplit, struct {
			Label         string
			Market, Galon decimal.Decimal
		}{
			Label: shortDayLabel(d), Market: m, Galon: g,
		})
	}
	for d, g := range galonByDay {
		if _, ok := marketByDay[d]; !ok {
			ex.DaySplit = append(ex.DaySplit, struct {
				Label         string
				Market, Galon decimal.Decimal
			}{
				Label: shortDayLabel(d), Market: decimal.Zero, Galon: g,
			})
		}
	}
	sort.Slice(ex.DaySplit, func(i, j int) bool { return ex.DaySplit[i].Label < ex.DaySplit[j].Label })
	// byDay feeds the trend chart: total revenue per day.
	for _, ds := range ex.DaySplit {
		ex.ByDay = append(ex.ByDay, DayPoint{Label: ds.Label, Revenue: ds.Market.Add(ds.Galon)})
	}

	// Payment methods joined through invoices back to the order window.
	payRows, err := r.pool.Query(ctx, `
SELECT p.payment_method, COALESCE(SUM(p.amount),0)
FROM payments p
JOIN invoices i ON i.id = p.payable_id AND p.payable_type = $3
JOIN sales_orders so ON so.id = i.sales_order_id
WHERE so.order_date >= $1 AND so.order_date < $2 AND so.status <> 'cancelled'
GROUP BY 1 ORDER BY 2 DESC`, pgdate(from), pgdate(to), invoicePayableType)
	if err != nil {
		return nil, err
	}
	defer payRows.Close()
	for payRows.Next() {
		var m string
		var v decimal.Decimal
		if err := payRows.Scan(&m, &v); err != nil {
			return nil, err
		}
		ex.ByPayment = append(ex.ByPayment, MethodTotal{Method: m, Revenue: v})
	}

	// Units sold per product (top products chart).
	unitRows, err := r.pool.Query(ctx, `
SELECT p.name, COALESCE(SUM(i.quantity),0)
FROM sales_order_items i
JOIN sales_orders so ON so.id = i.sales_order_id AND so.status <> 'cancelled'
     AND so.order_date >= $1 AND so.order_date < $2
JOIN products p ON p.id = i.product_id
GROUP BY p.name ORDER BY 2 DESC LIMIT 8`, pgdate(from), pgdate(to))
	if err != nil {
		return nil, err
	}
	defer unitRows.Close()
	for unitRows.Next() {
		var n string
		var q decimal.Decimal
		if err := unitRows.Scan(&n, &q); err != nil {
			return nil, err
		}
		ex.TopUnits = append(ex.TopUnits, NameUnits{Name: n, Units: q})
	}

	// Totals: items sold and status counts.
	var itemsSold decimal.Decimal
	var done, pend, canc int
	err = r.pool.QueryRow(ctx, `
SELECT
  COALESCE((SELECT SUM(i.quantity) FROM sales_order_items i
    JOIN sales_orders so ON so.id = i.sales_order_id
    WHERE so.order_date >= $1 AND so.order_date < $2 AND so.status <> 'cancelled'), 0),
  COALESCE((SELECT count(*) FROM sales_orders WHERE order_date >= $1 AND order_date < $2
    AND status IN ('paid','invoiced','delivered')), 0),
  COALESCE((SELECT count(*) FROM sales_orders WHERE order_date >= $1 AND order_date < $2
    AND status NOT IN ('paid','invoiced','delivered','cancelled')), 0),
  COALESCE((SELECT count(*) FROM sales_orders WHERE order_date >= $1 AND order_date < $2
    AND status = 'cancelled'), 0)`,
		pgdate(from), pgdate(to)).Scan(&itemsSold, &done, &pend, &canc)
	if err != nil {
		return nil, err
	}
	ex.ItemsSold, ex.Done, ex.Pending, ex.Cancelled = itemsSold, done, pend, canc
	return ex, nil
}

// shortDayLabel renders "2006-01-02" as "02 Jan".
func shortDayLabel(d string) string {
	t, err := time.Parse("2006-01-02", d)
	if err != nil {
		return d
	}
	return t.Format("02 Jan")
}

// --- POS dashboard (raw SQL; ponytail: one-off aggregates like SalesReportExtras) ---

// LowStockProduct is one product at/below its minimum stock.
type LowStockProduct struct {
	ID       int64           `json:"id"`
	Name     string          `json:"name"`
	Stock    decimal.Decimal `json:"quantity"`
	MinStock decimal.Decimal `json:"min_stock"`
}

// RecentPOSOrder is one latest transaction row for the dashboard table.
type RecentPOSOrder struct {
	ID            int64
	Number        string
	Date          string
	Cashier       *string
	PaymentMethod *string
	Total         decimal.Decimal
}

// TxCount is transactions per day for the bar chart.
type TxCount struct {
	Label string
	Count int
}

// SaleRow is one today-sale entry (market order or galon transaction).
type SaleRow struct {
	ID            int64
	Number        string
	Date          string
	ItemCount     int
	Cashier       *string
	Type          string
	PaymentMethod string
	Total         decimal.Decimal
}

// ItemRow is an aggregated sold-item line for the items dialog.
type ItemRow struct {
	ProductID int64
	Name      string
	Qty       decimal.Decimal
	Revenue   decimal.Decimal
}

// DaySplit is market vs galon revenue for one day.
type DaySplit struct {
	Label  string
	Market decimal.Decimal
	Galon  decimal.Decimal
}

// POSDashboard carries the whole payload for GET /dashboard/pos.
type POSDashboard struct {
	RevenueToday       decimal.Decimal
	RevenueDelta       *float64
	TransactionsToday  int
	TransactionsDelta  *float64
	ItemsToday         decimal.Decimal
	LowStockCount      int
	LowStock           []LowStockProduct
	RevenueTrend       []DayPoint
	DailyTransactions  []TxCount
	TopProducts        []NameUnits
	RecentTransactions []RecentPOSOrder
	TodaySales         []SaleRow
	TodayItems         []ItemRow
	DayBreakdown       []DaySplit
	MarketToday        decimal.Decimal
	GalonToday         decimal.Decimal
}

func pctDelta(today, prev float64) *float64 {
	if prev == 0 {
		return nil
	}
	v := (today - prev) / prev * 100
	return &v
}

// POSDashboard aggregates today's till numbers plus trailing-14-day trends.
// Windows follow order_date / created_at::date so the numbers reconcile with
// the laporan endpoint. tzName formats day labels in the store timezone.
func (r *Repository) POSDashboard(ctx context.Context, today time.Time, tzName string) (*POSDashboard, error) {
	d := &POSDashboard{}
	yest := today.AddDate(0, 0, -1)
	winFrom := today.AddDate(0, 0, -13)

	dayRev := func(day time.Time) (decimal.Decimal, int, error) {
		var rev decimal.Decimal
		var n int
		err := r.pool.QueryRow(ctx, `
SELECT COALESCE(SUM(total_amount),0), count(*) FROM sales_orders
WHERE order_date = $1 AND status <> 'cancelled'`, pgdate(day)).Scan(&rev, &n)
		return rev, n, err
	}
	f64 := func(v decimal.Decimal) float64 { f, _ := v.Float64(); return f }

	revT, txT, err := dayRev(today)
	if err != nil {
		return nil, err
	}
	revY, txY, err := dayRev(yest)
	if err != nil {
		return nil, err
	}
	d.RevenueToday, d.TransactionsToday = revT, txT
	d.RevenueDelta = pctDelta(f64(revT), f64(revY))
	d.TransactionsDelta = pctDelta(float64(txT), float64(txY))

	if err := r.pool.QueryRow(ctx, `
SELECT COALESCE(SUM(i.quantity),0)
FROM sales_order_items i JOIN sales_orders so ON so.id = i.sales_order_id
WHERE so.order_date = $1 AND so.status <> 'cancelled'`, pgdate(today)).Scan(&d.ItemsToday); err != nil {
		return nil, err
	}

	if err := r.pool.QueryRow(ctx, `
SELECT count(DISTINCT p.id) FROM products p
WHERE p.is_active = true AND (
  EXISTS (SELECT 1 FROM stocks s WHERE s.product_id = p.id AND s.quantity <= p.min_stock)
  OR NOT EXISTS (SELECT 1 FROM stocks s WHERE s.product_id = p.id))`).Scan(&d.LowStockCount); err != nil {
		return nil, err
	}
	lowRows, err := r.pool.Query(ctx, `
SELECT p.id, p.name,
  COALESCE((SELECT SUM(s.quantity) FROM stocks s WHERE s.product_id = p.id), 0),
  p.min_stock
FROM products p
WHERE p.is_active = true AND (
  EXISTS (SELECT 1 FROM stocks s WHERE s.product_id = p.id AND s.quantity <= p.min_stock)
  OR NOT EXISTS (SELECT 1 FROM stocks s WHERE s.product_id = p.id))
ORDER BY 3 ASC LIMIT 8`)
	if err != nil {
		return nil, err
	}
	defer lowRows.Close()
	for lowRows.Next() {
		var p LowStockProduct
		if err := lowRows.Scan(&p.ID, &p.Name, &p.Stock, &p.MinStock); err != nil {
			return nil, err
		}
		d.LowStock = append(d.LowStock, p)
	}

	// 14-day windows: market orders and completed galon transactions.
	marketByDay := map[string]decimal.Decimal{}
	txByDay := map[string]int{}
	galonByDay := map[string]decimal.Decimal{}
	trendRows, err := r.pool.Query(ctx, `
SELECT order_date::text, COALESCE(SUM(total_amount),0), count(*)
FROM sales_orders
WHERE order_date >= $1 AND order_date <= $2 AND status <> 'cancelled'
GROUP BY 1 ORDER BY 1`, pgdate(winFrom), pgdate(today))
	if err != nil {
		return nil, err
	}
	defer trendRows.Close()
	for trendRows.Next() {
		var day string
		var rev decimal.Decimal
		var n int
		if err := trendRows.Scan(&day, &rev, &n); err != nil {
			return nil, err
		}
		marketByDay[day] = rev
		txByDay[day] = n
	}
	galRows, err := r.pool.Query(ctx, `
SELECT created_at::date::text, COALESCE(SUM(quantity * unit_price),0)
FROM galon_transactions
WHERE status = 'completed'
  AND created_at::date >= $1 AND created_at::date <= $2
GROUP BY 1 ORDER BY 1`, winFrom.Format("2006-01-02"), today.Format("2006-01-02"))
	if err != nil {
		return nil, err
	}
	defer galRows.Close()
	for galRows.Next() {
		var day string
		var v decimal.Decimal
		if err := galRows.Scan(&day, &v); err != nil {
			return nil, err
		}
		galonByDay[day] = v
	}
	loc, lerr := time.LoadLocation(tzName)
	if lerr != nil || loc == nil {
		loc = time.UTC
	}
	for cur := winFrom; !cur.After(today); cur = cur.AddDate(0, 0, 1) {
		key := cur.Format("2006-01-02")
		label := cur.In(loc).Format("02 Jan")
		m, g := marketByDay[key], galonByDay[key]
		d.RevenueTrend = append(d.RevenueTrend, DayPoint{Label: label, Revenue: m.Add(g)})
		d.DayBreakdown = append(d.DayBreakdown, DaySplit{Label: label, Market: m, Galon: g})
		n := txByDay[key]
		if n == 0 && g.Sign() > 0 {
			n = 1 // galon-only day still counts as a transaction day
		}
		d.DailyTransactions = append(d.DailyTransactions, TxCount{Label: label, Count: n})
		d.MarketToday = d.MarketToday.Add(m)
		d.GalonToday = d.GalonToday.Add(g)
	}

	// Top products by units over the same window.
	unitRows, err := r.pool.Query(ctx, `
SELECT p.name, COALESCE(SUM(i.quantity),0)
FROM sales_order_items i
JOIN sales_orders so ON so.id = i.sales_order_id AND so.status <> 'cancelled'
     AND so.order_date >= $1 AND so.order_date <= $2
JOIN products p ON p.id = i.product_id
GROUP BY p.name ORDER BY 2 DESC LIMIT 6`, pgdate(winFrom), pgdate(today))
	if err != nil {
		return nil, err
	}
	defer unitRows.Close()
	for unitRows.Next() {
		var nu NameUnits
		if err := unitRows.Scan(&nu.Name, &nu.Units); err != nil {
			return nil, err
		}
		d.TopProducts = append(d.TopProducts, nu)
	}

	// Latest transactions: POS orders first-class, payment method via the
	// order's invoice payment, cashier via the linked shift.
	recRows, err := r.pool.Query(ctx, `
SELECT so.id, so.so_number, so.created_at,
       u.name AS cashier,
       pay.payment_method,
       so.total_amount
FROM sales_orders so
LEFT JOIN cashier_shifts cs ON cs.id = so.shift_id
LEFT JOIN users u ON u.id = cs.cashier_id
LEFT JOIN LATERAL (
  SELECT pm.payment_method
  FROM invoices inv
  JOIN payments pm ON pm.payable_type = $1 AND pm.payable_id = inv.id
  WHERE inv.sales_order_id = so.id
  LIMIT 1
) pay ON true
WHERE so.status <> 'cancelled' AND so.order_type IN ('pos','regular')
ORDER BY so.created_at DESC, so.id DESC
LIMIT 8`, `App\Models\Invoice`)
	if err != nil {
		return nil, err
	}
	defer recRows.Close()
	for recRows.Next() {
		var o RecentPOSOrder
		var ts time.Time
		if err := recRows.Scan(&o.ID, &o.Number, &ts, &o.Cashier, &o.PaymentMethod, &o.Total); err != nil {
			return nil, err
		}
		o.Date = ts.Format(time.RFC3339)
		d.RecentTransactions = append(d.RecentTransactions, o)
	}

	saleRows, err := r.pool.Query(ctx, `
SELECT so.id, so.so_number, so.created_at,
       COALESCE(pay.payment_method, 'cash'),
       (SELECT count(*) FROM sales_order_items ci WHERE ci.sales_order_id = so.id),
       u.name,
       so.total_amount
FROM sales_orders so
LEFT JOIN cashier_shifts cs ON cs.id = so.shift_id
LEFT JOIN users u ON u.id = cs.cashier_id
LEFT JOIN LATERAL (
  SELECT pm.payment_method
  FROM invoices inv
  JOIN payments pm ON pm.payable_type = $2 AND pm.payable_id = inv.id
  WHERE inv.sales_order_id = so.id
  LIMIT 1
) pay ON true
WHERE so.order_date = $1 AND so.status <> 'cancelled'
ORDER BY so.id DESC`, pgdate(today), `App\Models\Invoice`)
	if err != nil {
		return nil, err
	}
	defer saleRows.Close()
	for saleRows.Next() {
		var s SaleRow
		var ts time.Time
		s.Type = "market"
		if err := saleRows.Scan(&s.ID, &s.Number, &ts, &s.PaymentMethod, &s.ItemCount, &s.Cashier, &s.Total); err != nil {
			return nil, err
		}
		// RFC3339: the page runs t.date through formatDateTime(new Date(...)).
		s.Date = ts.Format(time.RFC3339)
		d.TodaySales = append(d.TodaySales, s)
	}
	galSaleRows, err := r.pool.Query(ctx, `
SELECT id, transaction_number, created_at, quantity, quantity * unit_price
FROM galon_transactions
WHERE status = 'completed' AND created_at::date = $1
ORDER BY id DESC`, today.Format("2006-01-02"))
	if err != nil {
		return nil, err
	}
	defer galSaleRows.Close()
	for galSaleRows.Next() {
		var s SaleRow
		var ts time.Time
		s.Type = "galon"
		s.PaymentMethod = "tunai"
		var qty decimal.Decimal
		if err := galSaleRows.Scan(&s.ID, &s.Number, &ts, &qty, &s.Total); err != nil {
			return nil, err
		}
		s.Date = ts.Format(time.RFC3339)
		s.ItemCount = int(qty.IntPart())
		d.TodaySales = append(d.TodaySales, s)
	}
	itemRows, err := r.pool.Query(ctx, `
SELECT p.id, p.name, SUM(i.quantity), SUM(i.subtotal)
FROM sales_order_items i
JOIN sales_orders so ON so.id = i.sales_order_id AND so.status <> 'cancelled'
JOIN products p ON p.id = i.product_id
WHERE so.order_date = $1
GROUP BY p.id, p.name ORDER BY 4 DESC LIMIT 20`, pgdate(today))
	if err != nil {
		return nil, err
	}
	defer itemRows.Close()
	for itemRows.Next() {
		var it ItemRow
		if err := itemRows.Scan(&it.ProductID, &it.Name, &it.Qty, &it.Revenue); err != nil {
			return nil, err
		}
		d.TodayItems = append(d.TodayItems, it)
	}
	return d, nil
}

// --- ERP dashboard chart series (raw SQL; same ponytail caveat as above) ---

// CategorySale is one product-category revenue point.
type CategorySale struct {
	Label string  `json:"label"`
	Value float64 `json:"value"`
}

// RecentOrder is one latest sales order for the dashboard list.
type RecentOrder struct {
	ID           int64           `json:"id"`
	SoNumber     string          `json:"so_number"`
	CustomerName string          `json:"customer_name"`
	Status       string          `json:"status"`
	TotalAmount  decimal.Decimal `json:"total_amount"`
	OrderDate    string          `json:"order_date"`
}

// activeOrderStatuses is the ONE definition of "an order that still needs
// processing". PendingOrdersCount (the dashboard card) and ActiveOrders (the
// card's drilldown) both use it: if they ever drift, the modal shows a
// different set than the number above it — the exact defect reported 1 Nov.
const activeOrderStatuses = `'draft', 'pending_approval', 'confirmed', 'picking', 'shipped'`

// ProductStockValue represents one product's physical stock, purchase price (HPP), and total valuation.
type ProductStockValue struct {
	ID         int64           `json:"id"`
	Code       string          `json:"code"`
	Name       string          `json:"name"`
	Category   string          `json:"category"`
	Unit       string          `json:"unit"`
	Quantity   decimal.Decimal `json:"quantity"`
	HPP        decimal.Decimal `json:"hpp"`
	StockValue decimal.Decimal `json:"stock_value"`
}

type PendingInvoice struct {
	ID              int64           `json:"id"`
	InvoiceNumber   string          `json:"invoice_number"`
	CustomerName    string          `json:"customer_name"`
	DueDate         string          `json:"due_date"`
	RemainingAmount decimal.Decimal `json:"remaining_amount"`
	IsOverdue       bool            `json:"is_overdue"`
}

type ActivePO struct {
	ID              int64           `json:"id"`
	PONumber        string          `json:"po_number"`
	SupplierName    string          `json:"supplier_name"`
	OrderDate       string          `json:"order_date"`
	TotalAmount     decimal.Decimal `json:"total_amount"`
	RemainingAmount decimal.Decimal `json:"remaining_amount"`
	Status          string          `json:"status"`
}

// ChartsExtras carries the ERP dashboard series beyond the KPI payload.
type ChartsExtras struct {
	CashflowIncome     []float64 // 12 slots, oldest → current month
	CashflowExpense    []float64
	CategorySales      []CategorySale // current month
	TopProducts        []NameUnits    // current month, by units
	RecentOrders       []RecentOrder
	ActiveOrders       []RecentOrder // same predicate as PendingOrdersCount
	TodaySales         []RecentOrder // sum == SalesKPI.TodayRevenue
	LowStock           []LowStockProduct
	StockValueProducts []ProductStockValue
	PendingInvoices    []PendingInvoice
	ActivePOs          []ActivePO
}

// ChartsExtras aggregates the ERP dashboard chart series: trailing-12-month
// cashflow income/expense, current-month category revenue and top products by
// units, latest orders and the low-stock list.
//
// Every list here is the drilldown of a card on the dashboard, so each uses
// the SAME predicate as its card's aggregate: pendingInvoices ↔
// TotalReceivable, activePOs ↔ ActivePOsCount, activeOrders ↔
// PendingOrdersCount, todaySales ↔ TodaySales. A modal that shows a different
// set than the number above it is the bug this shape exists to prevent.
func (r *Repository) ChartsExtras(ctx context.Context, cashFrom time.Time, catFrom, catTo, today time.Time) (*ChartsExtras, error) {
	ex := &ChartsExtras{
		CashflowIncome:     make([]float64, 12),
		CashflowExpense:    make([]float64, 12),
		CategorySales:      []CategorySale{},
		TopProducts:        []NameUnits{},
		RecentOrders:       []RecentOrder{},
		ActiveOrders:       []RecentOrder{},
		TodaySales:         []RecentOrder{},
		LowStock:           []LowStockProduct{},
		StockValueProducts: []ProductStockValue{},
		PendingInvoices:    []PendingInvoice{},
		ActivePOs:          []ActivePO{},
	}

	rows, err := r.pool.Query(ctx, `
WITH all_cf AS (
  SELECT p.payment_date AS tx_date, p.amount AS amount, 'income'::text AS cf_type
  FROM payments p
  WHERE p.payment_date >= $1::date AND p.payment_date < $2::date
  UNION ALL
  SELECT po.order_date AS tx_date, po.paid_amount AS amount, 'expense'::text AS cf_type
  FROM purchase_orders po
  WHERE po.paid_amount > 0 AND po.order_date >= $1::date AND po.order_date < $2::date
  UNION ALL
  SELECT cf.transaction_date AS tx_date, cf.amount AS amount, cf.type::text AS cf_type
  FROM cashflows cf
  WHERE cf.transaction_date >= $1::date AND cf.transaction_date < $2::date
)
SELECT to_char(date_trunc('month', tx_date), 'YYYY-MM'),
       COALESCE(SUM(amount) FILTER (WHERE cf_type = 'income'), 0),
       COALESCE(SUM(amount) FILTER (WHERE cf_type = 'expense'), 0)
FROM all_cf
WHERE tx_date >= $1::date AND tx_date < $2::date
GROUP BY 1 ORDER BY 1`, cashFrom, catTo)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	byMonth := map[string][2]float64{}
	for rows.Next() {
		var m string
		var inc, exp float64
		if err := rows.Scan(&m, &inc, &exp); err != nil {
			return nil, err
		}
		byMonth[m] = [2]float64{inc, exp}
	}
	for i := 0; i < 12; i++ {
		key := cashFrom.AddDate(0, i, 0).Format("2006-01")
		if v, ok := byMonth[key]; ok {
			ex.CashflowIncome[i], ex.CashflowExpense[i] = v[0], v[1]
		}
	}

	catRows, err := r.pool.Query(ctx, `
SELECT COALESCE(c.name, 'Lainnya'), COALESCE(SUM(i.subtotal), 0)
FROM sales_order_items i
JOIN sales_orders so ON so.id = i.sales_order_id AND so.status <> 'cancelled'
     AND so.order_date >= $1 AND so.order_date < $2
LEFT JOIN products p ON p.id = i.product_id
LEFT JOIN categories c ON c.id = p.category_id
GROUP BY c.name ORDER BY 2 DESC`, pgdate(catFrom), pgdate(catTo))
	if err != nil {
		return nil, err
	}
	defer catRows.Close()
	for catRows.Next() {
		var cs CategorySale
		var v decimal.Decimal
		if err := catRows.Scan(&cs.Label, &v); err != nil {
			return nil, err
		}
		cs.Value, _ = v.Float64()
		ex.CategorySales = append(ex.CategorySales, cs)
	}

	unitRows, err := r.pool.Query(ctx, `
SELECT p.name, SUM(i.quantity)
FROM sales_order_items i
JOIN sales_orders so ON so.id = i.sales_order_id AND so.status <> 'cancelled'
     AND so.order_date >= $1 AND so.order_date < $2
JOIN products p ON p.id = i.product_id
GROUP BY p.name ORDER BY 2 DESC LIMIT 8`, pgdate(catFrom), pgdate(catTo))
	if err != nil {
		return nil, err
	}
	defer unitRows.Close()
	for unitRows.Next() {
		var nu NameUnits
		if err := unitRows.Scan(&nu.Name, &nu.Units); err != nil {
			return nil, err
		}
		ex.TopProducts = append(ex.TopProducts, nu)
	}

	// scanOrderRows is shared by the three order lists below so the SELECT
	// column order can never drift from the Scan order in one of them.
	scanOrderRows := func(rows pgx.Rows, dst *[]RecentOrder) {
		defer rows.Close()
		for rows.Next() {
			var o RecentOrder
			if err := rows.Scan(&o.ID, &o.SoNumber, &o.CustomerName, &o.Status, &o.TotalAmount, &o.OrderDate); err == nil {
				*dst = append(*dst, o)
			}
		}
	}

	orderRows, err := r.pool.Query(ctx, `
SELECT so.id, so.so_number, COALESCE(c.name, ''), so.status, so.total_amount, so.order_date::text
FROM sales_orders so
LEFT JOIN customers c ON c.id = so.customer_id
ORDER BY so.created_at DESC, so.id DESC LIMIT 8`)
	if err != nil {
		return nil, err
	}
	scanOrderRows(orderRows, &ex.RecentOrders)

	// "Order Aktif" drilldown: exactly the rows the card counted.
	activeRows, err := r.pool.Query(ctx, `
SELECT so.id, so.so_number, COALESCE(c.name, ''), so.status, so.total_amount, so.order_date::text
FROM sales_orders so
LEFT JOIN customers c ON c.id = so.customer_id
WHERE so.status IN (`+activeOrderStatuses+`)
ORDER BY so.created_at DESC, so.id DESC LIMIT 200`)
	if err != nil {
		return nil, err
	}
	scanOrderRows(activeRows, &ex.ActiveOrders)

	// "Penjualan Hari Ini" drilldown: exactly the rows TodaySales summed.
	todayRows, err := r.pool.Query(ctx, `
SELECT so.id, so.so_number, COALESCE(c.name, ''), so.status, so.total_amount, so.order_date::text
FROM sales_orders so
LEFT JOIN customers c ON c.id = so.customer_id
WHERE so.order_date = $1::date AND so.status <> 'cancelled'
ORDER BY so.created_at DESC, so.id DESC LIMIT 500`, pgdate(today))
	if err != nil {
		return nil, err
	}
	scanOrderRows(todayRows, &ex.TodaySales)

	lowRows, err := r.pool.Query(ctx, `
SELECT p.id, p.name,
  COALESCE((SELECT SUM(s.quantity) FROM stocks s WHERE s.product_id = p.id), 0),
  p.min_stock
FROM products p
WHERE p.is_active = true AND (
  EXISTS (SELECT 1 FROM stocks s WHERE s.product_id = p.id AND s.quantity <= p.min_stock)
  OR NOT EXISTS (SELECT 1 FROM stocks s WHERE s.product_id = p.id))
ORDER BY 3 ASC LIMIT 10`)
	if err != nil {
		return nil, err
	}
	defer lowRows.Close()
	for lowRows.Next() {
		var p LowStockProduct
		if err := lowRows.Scan(&p.ID, &p.Name, &p.Stock, &p.MinStock); err != nil {
			return nil, err
		}
		ex.LowStock = append(ex.LowStock, p)
	}

	stockValRows, err := r.pool.Query(ctx, `
SELECT p.id, p.code, p.name, COALESCE(c.name, 'Umum'), COALESCE(u.symbol, 'Pcs'),
       COALESCE(SUM(s.quantity), 0), p.purchase_price,
       COALESCE(SUM(s.quantity * p.purchase_price), 0)
FROM products p
JOIN stocks s ON s.product_id = p.id
LEFT JOIN categories c ON c.id = p.category_id
LEFT JOIN units u ON u.id = p.unit_id
WHERE p.is_active = true AND p.deleted_at IS NULL AND s.quantity > 0
GROUP BY p.id, p.code, p.name, c.name, u.symbol, p.purchase_price
ORDER BY 8 DESC LIMIT 10`)
	if err == nil {
		defer stockValRows.Close()
		for stockValRows.Next() {
			var psv ProductStockValue
			if err := stockValRows.Scan(&psv.ID, &psv.Code, &psv.Name, &psv.Category, &psv.Unit, &psv.Quantity, &psv.HPP, &psv.StockValue); err == nil {
				ex.StockValueProducts = append(ex.StockValueProducts, psv)
			}
		}
	}

	invRows, err := r.pool.Query(ctx, `
SELECT i.id, i.invoice_number, COALESCE(c.name, 'Pelanggan Umum'),
       COALESCE(to_char(i.due_date, 'YYYY-MM-DD'), ''),
       i.remaining_amount,
       (i.due_date IS NOT NULL AND i.due_date < CURRENT_DATE) AS is_overdue
FROM invoices i
LEFT JOIN customers c ON c.id = i.customer_id
WHERE i.status IN ('unpaid', 'partial')
ORDER BY i.due_date ASC NULLS LAST, i.id DESC
LIMIT 200`)
	if err == nil {
		defer invRows.Close()
		for invRows.Next() {
			var pi PendingInvoice
			if err := invRows.Scan(&pi.ID, &pi.InvoiceNumber, &pi.CustomerName, &pi.DueDate, &pi.RemainingAmount, &pi.IsOverdue); err == nil {
				ex.PendingInvoices = append(ex.PendingInvoices, pi)
			}
		}
	}

	poRows, err := r.pool.Query(ctx, `
SELECT po.id, po.po_number, COALESCE(s.name, 'Supplier Umum'),
       po.order_date::text, po.total_amount, po.remaining_amount, po.status
FROM purchase_orders po
LEFT JOIN suppliers s ON s.id = po.supplier_id
WHERE po.status IN ('draft', 'approved', 'sent', 'partial', 'received')
  AND po.remaining_amount > 0
ORDER BY po.order_date DESC, po.id DESC
LIMIT 200`)
	if err == nil {
		defer poRows.Close()
		for poRows.Next() {
			var apo ActivePO
			if err := poRows.Scan(&apo.ID, &apo.PONumber, &apo.SupplierName, &apo.OrderDate, &apo.TotalAmount, &apo.RemainingAmount, &apo.Status); err == nil {
				ex.ActivePOs = append(ex.ActivePOs, apo)
			}
		}
	}

	return ex, nil
}

// CashflowStatementMovements returns classified cash movements for the period.
func (r *Repository) CashflowStatementMovements(ctx context.Context, p db.CashflowStatementMovementsParams) ([]db.CashflowStatementMovementsRow, error) {
	return r.q.CashflowStatementMovements(ctx, p)
}

// CashAccountCodes lists the chart's cash accounts.
func (r *Repository) CashAccountCodes(ctx context.Context) ([]string, error) {
	return r.q.CashAccountCodes(ctx)
}

// CashAccountBalanceBefore returns the signed cash balance before a date.
func (r *Repository) CashAccountBalanceBefore(ctx context.Context, p db.CashAccountBalanceBeforeParams) (decimal.Decimal, error) {
	return r.q.CashAccountBalanceBefore(ctx, p)
}

func (r *Repository) GetAppSetting(ctx context.Context, key string) ([]byte, error) {
	return r.q.GetAppSetting(ctx, key)
}

func (r *Repository) UpsertAppSetting(ctx context.Context, key string, val []byte) error {
	return r.q.UpsertAppSetting(ctx, db.UpsertAppSettingParams{Key: key, Value: val})
}

func (r *Repository) DeleteAppSetting(ctx context.Context, key string) error {
	_, err := r.pool.Exec(ctx, `DELETE FROM app_settings WHERE key = $1`, key)
	return err
}