package reports

import (
	"context"
)

// SalesByCustomer aggregates sales order metrics grouped by customer.
func (r *Repository) SalesByCustomer(ctx context.Context, f BreakdownFilter) ([]SalesByCustomerRow, int, error) {
	dFrom := dateOrNil(f.DateFrom)
	dTo := dateOrNil(f.DateTo)
	var storeIDs []int64
	if len(f.StoreIDs) > 0 {
		storeIDs = f.StoreIDs
	}
	search := f.Search

	countQuery := `
WITH order_agg AS (
    SELECT 
        so.customer_id,
        so.id AS order_id,
        so.discount_amount AS order_discount,
        so.total_amount AS order_total,
        COALESCE(SUM(soi.quantity), 0) AS order_qty,
        COALESCE(SUM(soi.quantity * p.purchase_price), 0) AS order_hpp
    FROM sales_orders so
    LEFT JOIN sales_order_items soi ON soi.sales_order_id = so.id
    LEFT JOIN products p ON p.id = soi.product_id
    WHERE so.status <> 'cancelled'
      AND ($1::date IS NULL OR so.order_date >= $1)
      AND ($2::date IS NULL OR so.order_date <= $2)
      AND ($3::bigint[] IS NULL OR so.store_id = ANY($3))
    GROUP BY so.customer_id, so.id, so.discount_amount, so.total_amount
),
cust_summary AS (
    SELECT c.id
    FROM customers c
    JOIN order_agg oa ON oa.customer_id = c.id
    WHERE ($4::text = '' OR c.code ILIKE '%' || $4 || '%' OR c.name ILIKE '%' || $4 || '%')
    GROUP BY c.id, c.code, c.name
)
SELECT COUNT(*) FROM cust_summary`

	var total int
	if err := r.pool.QueryRow(ctx, countQuery, dFrom, dTo, storeIDs, search).Scan(&total); err != nil {
		return nil, 0, err
	}
	if total == 0 {
		return []SalesByCustomerRow{}, 0, nil
	}

	dataQuery := `
WITH order_agg AS (
    SELECT 
        so.customer_id,
        so.id AS order_id,
        so.discount_amount AS order_discount,
        so.total_amount AS order_total,
        COALESCE(SUM(soi.quantity), 0) AS order_qty,
        COALESCE(SUM(soi.quantity * p.purchase_price), 0) AS order_hpp
    FROM sales_orders so
    LEFT JOIN sales_order_items soi ON soi.sales_order_id = so.id
    LEFT JOIN products p ON p.id = soi.product_id
    WHERE so.status <> 'cancelled'
      AND ($1::date IS NULL OR so.order_date >= $1)
      AND ($2::date IS NULL OR so.order_date <= $2)
      AND ($3::bigint[] IS NULL OR so.store_id = ANY($3))
    GROUP BY so.customer_id, so.id, so.discount_amount, so.total_amount
),
cust_summary AS (
    SELECT 
        c.code AS customer_code,
        c.name AS customer_name,
        COUNT(oa.order_id)::int AS orders,
        COALESCE(SUM(oa.order_qty), 0)::numeric AS qty,
        COALESCE(SUM(oa.order_total), 0)::numeric AS omset,
        COALESCE(SUM(oa.order_discount), 0)::numeric AS discount,
        COALESCE(SUM(oa.order_hpp), 0)::numeric AS hpp,
        COALESCE(SUM(oa.order_total - oa.order_hpp), 0)::numeric AS profit
    FROM customers c
    JOIN order_agg oa ON oa.customer_id = c.id
    WHERE ($4::text = '' OR c.code ILIKE '%' || $4 || '%' OR c.name ILIKE '%' || $4 || '%')
    GROUP BY c.id, c.code, c.name
)
SELECT customer_code, customer_name, orders, qty, omset, discount, hpp, profit
FROM cust_summary
ORDER BY omset DESC, customer_name ASC
LIMIT $5 OFFSET $6`

	limit := f.PerPage
	offset := (f.Page - 1) * f.PerPage
	rows, err := r.pool.Query(ctx, dataQuery, dFrom, dTo, storeIDs, search, limit, offset)
	if err != nil {
		return nil, 0, err
	}
	defer rows.Close()

	out := make([]SalesByCustomerRow, 0, limit)
	for rows.Next() {
		var row SalesByCustomerRow
		if err := rows.Scan(&row.CustomerCode, &row.CustomerName, &row.Orders, &row.Qty, &row.Omset, &row.Discount, &row.HPP, &row.Profit); err != nil {
			return nil, 0, err
		}
		out = append(out, row)
	}
	return out, total, rows.Err()
}

// SalesByProduct aggregates sales and returns grouped by product.
func (r *Repository) SalesByProduct(ctx context.Context, f BreakdownFilter) ([]SalesByProductRow, int, error) {
	dFrom := dateOrNil(f.DateFrom)
	dTo := dateOrNil(f.DateTo)
	var storeIDs []int64
	if len(f.StoreIDs) > 0 {
		storeIDs = f.StoreIDs
	}
	search := f.Search

	countQuery := `
WITH prod_sales AS (
    SELECT 
        soi.product_id,
        COALESCE(SUM(soi.quantity), 0) AS qty,
        COALESCE(SUM(soi.subtotal), 0) AS omset
    FROM sales_order_items soi
    JOIN sales_orders so ON so.id = soi.sales_order_id
    WHERE so.status <> 'cancelled'
      AND ($1::date IS NULL OR so.order_date >= $1)
      AND ($2::date IS NULL OR so.order_date <= $2)
      AND ($3::bigint[] IS NULL OR so.store_id = ANY($3))
    GROUP BY soi.product_id
),
prod_returns AS (
    SELECT 
        sri.product_id,
        COALESCE(SUM(sri.quantity), 0) AS retur_qty,
        COALESCE(SUM(sri.refund_amount), 0) AS retur_amount
    FROM sales_return_items sri
    JOIN sales_returns sr ON sr.id = sri.sales_return_id
    WHERE sr.status = 'completed'
      AND ($1::date IS NULL OR sr.return_date >= $1)
      AND ($2::date IS NULL OR sr.return_date <= $2)
    GROUP BY sri.product_id
),
prod_summary AS (
    SELECT p.id
    FROM products p
    LEFT JOIN prod_sales ps ON ps.product_id = p.id
    LEFT JOIN prod_returns pr ON pr.product_id = p.id
    WHERE (COALESCE(ps.qty, 0) > 0 OR COALESCE(pr.retur_qty, 0) > 0)
      AND ($4::text = '' OR p.code ILIKE '%' || $4 || '%' OR p.name ILIKE '%' || $4 || '%')
)
SELECT COUNT(*) FROM prod_summary`

	var total int
	if err := r.pool.QueryRow(ctx, countQuery, dFrom, dTo, storeIDs, search).Scan(&total); err != nil {
		return nil, 0, err
	}
	if total == 0 {
		return []SalesByProductRow{}, 0, nil
	}

	dataQuery := `
WITH prod_sales AS (
    SELECT 
        soi.product_id,
        COALESCE(SUM(soi.quantity), 0) AS qty,
        COALESCE(SUM(soi.subtotal), 0) AS omset
    FROM sales_order_items soi
    JOIN sales_orders so ON so.id = soi.sales_order_id
    WHERE so.status <> 'cancelled'
      AND ($1::date IS NULL OR so.order_date >= $1)
      AND ($2::date IS NULL OR so.order_date <= $2)
      AND ($3::bigint[] IS NULL OR so.store_id = ANY($3))
    GROUP BY soi.product_id
),
prod_returns AS (
    SELECT 
        sri.product_id,
        COALESCE(SUM(sri.quantity), 0) AS retur_qty,
        COALESCE(SUM(sri.refund_amount), 0) AS retur_amount
    FROM sales_return_items sri
    JOIN sales_returns sr ON sr.id = sri.sales_return_id
    WHERE sr.status = 'completed'
      AND ($1::date IS NULL OR sr.return_date >= $1)
      AND ($2::date IS NULL OR sr.return_date <= $2)
    GROUP BY sri.product_id
),
prod_summary AS (
    SELECT 
        p.code AS product_code,
        p.name AS product_name,
        COALESCE(ps.qty, 0)::numeric AS qty,
        COALESCE(ps.omset, 0)::numeric AS omset,
        COALESCE(pr.retur_qty, 0)::numeric AS retur_qty,
        COALESCE(pr.retur_amount, 0)::numeric AS retur_amount
    FROM products p
    LEFT JOIN prod_sales ps ON ps.product_id = p.id
    LEFT JOIN prod_returns pr ON pr.product_id = p.id
    WHERE (COALESCE(ps.qty, 0) > 0 OR COALESCE(pr.retur_qty, 0) > 0)
      AND ($4::text = '' OR p.code ILIKE '%' || $4 || '%' OR p.name ILIKE '%' || $4 || '%')
)
SELECT product_code, product_name, qty, omset, retur_qty, retur_amount
FROM prod_summary
ORDER BY omset DESC, product_name ASC
LIMIT $5 OFFSET $6`

	limit := f.PerPage
	offset := (f.Page - 1) * f.PerPage
	rows, err := r.pool.Query(ctx, dataQuery, dFrom, dTo, storeIDs, search, limit, offset)
	if err != nil {
		return nil, 0, err
	}
	defer rows.Close()

	out := make([]SalesByProductRow, 0, limit)
	for rows.Next() {
		var row SalesByProductRow
		if err := rows.Scan(&row.ProductCode, &row.ProductName, &row.Qty, &row.Omset, &row.ReturQty, &row.ReturAmount); err != nil {
			return nil, 0, err
		}
		out = append(out, row)
	}
	return out, total, rows.Err()
}

// PurchasesBySupplier aggregates purchase orders grouped by supplier.
func (r *Repository) PurchasesBySupplier(ctx context.Context, f BreakdownFilter) ([]PurchasesBySupplierRow, int, error) {
	dFrom := dateOrNil(f.DateFrom)
	dTo := dateOrNil(f.DateTo)
	search := f.Search

	countQuery := `
WITH po_agg AS (
    SELECT 
        po.supplier_id,
        po.id AS po_id
    FROM purchase_orders po
    WHERE po.status <> 'cancelled'
      AND ($1::date IS NULL OR po.order_date >= $1)
      AND ($2::date IS NULL OR po.order_date <= $2)
),
sup_summary AS (
    SELECT s.id
    FROM suppliers s
    JOIN po_agg pa ON pa.supplier_id = s.id
    WHERE ($3::text = '' OR s.name ILIKE '%' || $3 || '%' OR s.code ILIKE '%' || $3 || '%')
    GROUP BY s.id
)
SELECT COUNT(*) FROM sup_summary`

	var total int
	if err := r.pool.QueryRow(ctx, countQuery, dFrom, dTo, search).Scan(&total); err != nil {
		return nil, 0, err
	}
	if total == 0 {
		return []PurchasesBySupplierRow{}, 0, nil
	}

	dataQuery := `
WITH po_agg AS (
    SELECT 
        po.supplier_id,
        po.id AS po_id,
        po.total_amount,
        po.paid_amount,
        po.remaining_amount,
        COALESCE(SUM(poi.quantity), 0) AS po_qty
    FROM purchase_orders po
    LEFT JOIN purchase_order_items poi ON poi.purchase_order_id = po.id
    WHERE po.status <> 'cancelled'
      AND ($1::date IS NULL OR po.order_date >= $1)
      AND ($2::date IS NULL OR po.order_date <= $2)
    GROUP BY po.supplier_id, po.id, po.total_amount, po.paid_amount, po.remaining_amount
),
sup_summary AS (
    SELECT 
        s.name AS supplier_name,
        COUNT(pa.po_id)::int AS orders,
        COALESCE(SUM(pa.po_qty), 0)::numeric AS qty,
        COALESCE(SUM(pa.total_amount), 0)::numeric AS total,
        COALESCE(SUM(pa.paid_amount), 0)::numeric AS paid,
        COALESCE(SUM(pa.remaining_amount), 0)::numeric AS remaining
    FROM suppliers s
    JOIN po_agg pa ON pa.supplier_id = s.id
    WHERE ($3::text = '' OR s.name ILIKE '%' || $3 || '%' OR s.code ILIKE '%' || $3 || '%')
    GROUP BY s.id, s.name
)
SELECT supplier_name, orders, qty, total, paid, remaining
FROM sup_summary
ORDER BY total DESC, supplier_name ASC
LIMIT $4 OFFSET $5`

	limit := f.PerPage
	offset := (f.Page - 1) * f.PerPage
	rows, err := r.pool.Query(ctx, dataQuery, dFrom, dTo, search, limit, offset)
	if err != nil {
		return nil, 0, err
	}
	defer rows.Close()

	out := make([]PurchasesBySupplierRow, 0, limit)
	for rows.Next() {
		var row PurchasesBySupplierRow
		if err := rows.Scan(&row.SupplierName, &row.Orders, &row.Qty, &row.Total, &row.Paid, &row.Remaining); err != nil {
			return nil, 0, err
		}
		out = append(out, row)
	}
	return out, total, rows.Err()
}

// PurchasesByProduct aggregates purchase order items grouped by product.
func (r *Repository) PurchasesByProduct(ctx context.Context, f BreakdownFilter) ([]PurchasesByProductRow, int, error) {
	dFrom := dateOrNil(f.DateFrom)
	dTo := dateOrNil(f.DateTo)
	search := f.Search

	countQuery := `
WITH prod_po AS (
    SELECT p.id
    FROM purchase_order_items poi
    JOIN purchase_orders po ON po.id = poi.purchase_order_id
    JOIN products p ON p.id = poi.product_id
    WHERE po.status <> 'cancelled'
      AND ($1::date IS NULL OR po.order_date >= $1)
      AND ($2::date IS NULL OR po.order_date <= $2)
      AND ($3::text = '' OR p.code ILIKE '%' || $3 || '%' OR p.name ILIKE '%' || $3 || '%')
    GROUP BY p.id
    HAVING SUM(poi.quantity) > 0
)
SELECT COUNT(*) FROM prod_po`

	var total int
	if err := r.pool.QueryRow(ctx, countQuery, dFrom, dTo, search).Scan(&total); err != nil {
		return nil, 0, err
	}
	if total == 0 {
		return []PurchasesByProductRow{}, 0, nil
	}

	dataQuery := `
WITH prod_po AS (
    SELECT 
        p.code AS product_code,
        p.name AS product_name,
        COALESCE(SUM(poi.quantity), 0)::numeric AS qty,
        COALESCE(SUM(poi.received_quantity), 0)::numeric AS received,
        COALESCE(SUM(poi.subtotal), 0)::numeric AS total
    FROM purchase_order_items poi
    JOIN purchase_orders po ON po.id = poi.purchase_order_id
    JOIN products p ON p.id = poi.product_id
    WHERE po.status <> 'cancelled'
      AND ($1::date IS NULL OR po.order_date >= $1)
      AND ($2::date IS NULL OR po.order_date <= $2)
      AND ($3::text = '' OR p.code ILIKE '%' || $3 || '%' OR p.name ILIKE '%' || $3 || '%')
    GROUP BY p.id, p.code, p.name
    HAVING SUM(poi.quantity) > 0
)
SELECT product_code, product_name, qty, received, total
FROM prod_po
ORDER BY total DESC, product_name ASC
LIMIT $4 OFFSET $5`

	limit := f.PerPage
	offset := (f.Page - 1) * f.PerPage
	rows, err := r.pool.Query(ctx, dataQuery, dFrom, dTo, search, limit, offset)
	if err != nil {
		return nil, 0, err
	}
	defer rows.Close()

	out := make([]PurchasesByProductRow, 0, limit)
	for rows.Next() {
		var row PurchasesByProductRow
		if err := rows.Scan(&row.ProductCode, &row.ProductName, &row.Qty, &row.Received, &row.Total); err != nil {
			return nil, 0, err
		}
		out = append(out, row)
	}
	return out, total, rows.Err()
}

// ReceivablesAging lists unpaid/partially paid sales invoices with aging buckets.
func (r *Repository) ReceivablesAging(ctx context.Context, f AgingFilter) ([]AgingRow, int, error) {
	search := f.Search

	countQuery := `
SELECT COUNT(*)
FROM invoices inv
LEFT JOIN customers c ON c.id = inv.customer_id
WHERE inv.status NOT IN ('paid', 'cancelled')
  AND inv.remaining_amount > 0
  AND ($1::text = '' OR inv.invoice_number ILIKE '%' || $1 || '%' OR c.name ILIKE '%' || $1 || '%')`

	var total int
	if err := r.pool.QueryRow(ctx, countQuery, search).Scan(&total); err != nil {
		return nil, 0, err
	}
	if total == 0 {
		return []AgingRow{}, 0, nil
	}

	dataQuery := `
SELECT 
    inv.invoice_number AS number,
    COALESCE(c.name, '') AS party,
    inv.invoice_date::text AS bill_date,
    CASE 
        WHEN CURRENT_DATE > inv.due_date THEN (CURRENT_DATE - inv.due_date)::int 
        ELSE 0 
    END AS age_days,
    CASE 
        WHEN CURRENT_DATE <= inv.due_date THEN 'not_due'
        WHEN (CURRENT_DATE - inv.due_date) BETWEEN 1 AND 30 THEN '1_30'
        WHEN (CURRENT_DATE - inv.due_date) BETWEEN 31 AND 60 THEN '31_60'
        WHEN (CURRENT_DATE - inv.due_date) BETWEEN 61 AND 90 THEN '61_90'
        ELSE 'over_90'
    END AS bucket,
    inv.total_amount AS total,
    inv.remaining_amount AS remaining
FROM invoices inv
LEFT JOIN customers c ON c.id = inv.customer_id
WHERE inv.status NOT IN ('paid', 'cancelled')
  AND inv.remaining_amount > 0
  AND ($1::text = '' OR inv.invoice_number ILIKE '%' || $1 || '%' OR c.name ILIKE '%' || $1 || '%')
ORDER BY inv.due_date ASC, inv.remaining_amount DESC
LIMIT $2 OFFSET $3`

	limit := f.PerPage
	offset := (f.Page - 1) * f.PerPage
	rows, err := r.pool.Query(ctx, dataQuery, search, limit, offset)
	if err != nil {
		return nil, 0, err
	}
	defer rows.Close()

	out := make([]AgingRow, 0, limit)
	for rows.Next() {
		var row AgingRow
		if err := rows.Scan(&row.Number, &row.Party, &row.BillDate, &row.AgeDays, &row.Bucket, &row.Total, &row.Remaining); err != nil {
			return nil, 0, err
		}
		out = append(out, row)
	}
	return out, total, rows.Err()
}

// PayablesAging lists unpaid/partially paid purchase orders with aging buckets.
func (r *Repository) PayablesAging(ctx context.Context, f AgingFilter) ([]AgingRow, int, error) {
	search := f.Search

	countQuery := `
SELECT COUNT(*)
FROM purchase_orders po
LEFT JOIN suppliers s ON s.id = po.supplier_id
WHERE po.status IN ('received', 'partial')
  AND po.remaining_amount > 0
  AND ($1::text = '' OR po.po_number ILIKE '%' || $1 || '%' OR s.name ILIKE '%' || $1 || '%')`

	var total int
	if err := r.pool.QueryRow(ctx, countQuery, search).Scan(&total); err != nil {
		return nil, 0, err
	}
	if total == 0 {
		return []AgingRow{}, 0, nil
	}

	dataQuery := `
SELECT 
    po.po_number AS number,
    COALESCE(s.name, '') AS party,
    po.order_date::text AS bill_date,
    CASE 
        WHEN CURRENT_DATE > COALESCE(po.due_date, po.order_date + 30) THEN (CURRENT_DATE - COALESCE(po.due_date, po.order_date + 30))::int 
        ELSE 0 
    END AS age_days,
    CASE 
        WHEN CURRENT_DATE <= COALESCE(po.due_date, po.order_date + 30) THEN 'not_due'
        WHEN (CURRENT_DATE - COALESCE(po.due_date, po.order_date + 30)) BETWEEN 1 AND 30 THEN '1_30'
        WHEN (CURRENT_DATE - COALESCE(po.due_date, po.order_date + 30)) BETWEEN 31 AND 60 THEN '31_60'
        WHEN (CURRENT_DATE - COALESCE(po.due_date, po.order_date + 30)) BETWEEN 61 AND 90 THEN '61_90'
        ELSE 'over_90'
    END AS bucket,
    po.total_amount AS total,
    po.remaining_amount AS remaining
FROM purchase_orders po
LEFT JOIN suppliers s ON s.id = po.supplier_id
WHERE po.status IN ('received', 'partial')
  AND po.remaining_amount > 0
  AND ($1::text = '' OR po.po_number ILIKE '%' || $1 || '%' OR s.name ILIKE '%' || $1 || '%')
ORDER BY COALESCE(po.due_date, po.order_date + 30) ASC, po.remaining_amount DESC
LIMIT $2 OFFSET $3`

	limit := f.PerPage
	offset := (f.Page - 1) * f.PerPage
	rows, err := r.pool.Query(ctx, dataQuery, search, limit, offset)
	if err != nil {
		return nil, 0, err
	}
	defer rows.Close()

	out := make([]AgingRow, 0, limit)
	for rows.Next() {
		var row AgingRow
		if err := rows.Scan(&row.Number, &row.Party, &row.BillDate, &row.AgeDays, &row.Bucket, &row.Total, &row.Remaining); err != nil {
			return nil, 0, err
		}
		out = append(out, row)
	}
	return out, total, rows.Err()
}

// StockValuation calculates inventory valuation based on purchase price and quantity on hand.
func (r *Repository) StockValuation(ctx context.Context, f StockValuationFilter) ([]StockValuationRow, int, error) {
	search := f.Search
	var warehouseIDs []int64
	if len(f.WarehouseIDs) > 0 {
		warehouseIDs = f.WarehouseIDs
	}

	countQuery := `
SELECT COUNT(*)
FROM stocks s
JOIN products p ON p.id = s.product_id AND p.deleted_at IS NULL
WHERE s.quantity > 0
  AND ($1::text = '' OR p.code ILIKE '%' || $1 || '%' OR p.name ILIKE '%' || $1 || '%')
  AND ($2::bigint[] IS NULL OR s.warehouse_id = ANY($2))`

	var total int
	if err := r.pool.QueryRow(ctx, countQuery, search, warehouseIDs).Scan(&total); err != nil {
		return nil, 0, err
	}
	if total == 0 {
		return []StockValuationRow{}, 0, nil
	}

	dataQuery := `
SELECT 
    p.code AS product_code,
    p.name AS product_name,
    s.warehouse_id,
    s.quantity AS qty,
    p.purchase_price AS avg_cost,
    (s.quantity * p.purchase_price)::numeric AS value,
    p.selling_price AS sell_price
FROM stocks s
JOIN products p ON p.id = s.product_id AND p.deleted_at IS NULL
WHERE s.quantity > 0
  AND ($1::text = '' OR p.code ILIKE '%' || $1 || '%' OR p.name ILIKE '%' || $1 || '%')
  AND ($2::bigint[] IS NULL OR s.warehouse_id = ANY($2))
ORDER BY value DESC, product_name ASC
LIMIT $3 OFFSET $4`

	limit := f.PerPage
	offset := (f.Page - 1) * f.PerPage
	rows, err := r.pool.Query(ctx, dataQuery, search, warehouseIDs, limit, offset)
	if err != nil {
		return nil, 0, err
	}
	defer rows.Close()

	out := make([]StockValuationRow, 0, limit)
	for rows.Next() {
		var row StockValuationRow
		if err := rows.Scan(&row.ProductCode, &row.ProductName, &row.WarehouseID, &row.Qty, &row.AvgCost, &row.Value, &row.SellPrice); err != nil {
			return nil, 0, err
		}
		out = append(out, row)
	}
	return out, total, rows.Err()
}

// StockMovements lists inventory movement history with running balance.
func (r *Repository) StockMovements(ctx context.Context, f StockMovementsFilter) ([]StockMovementRow, int, error) {
	dFrom := dateOrNil(f.DateFrom)
	dTo := dateOrNil(f.DateTo)
	search := f.Search
	var warehouseIDs []int64
	if len(f.WarehouseIDs) > 0 {
		warehouseIDs = f.WarehouseIDs
	}

	countQuery := `
SELECT COUNT(*)
FROM stock_movements sm
JOIN products p ON p.id = sm.product_id
WHERE ($1::date IS NULL OR (sm.created_at AT TIME ZONE 'Asia/Jakarta')::date >= $1)
  AND ($2::date IS NULL OR (sm.created_at AT TIME ZONE 'Asia/Jakarta')::date <= $2)
  AND ($3::text = '' OR sm.movement_number ILIKE '%' || $3 || '%' OR p.code ILIKE '%' || $3 || '%' OR p.name ILIKE '%' || $3 || '%')
  AND ($4::bigint[] IS NULL OR sm.warehouse_id = ANY($4))`

	var total int
	if err := r.pool.QueryRow(ctx, countQuery, dFrom, dTo, search, warehouseIDs).Scan(&total); err != nil {
		return nil, 0, err
	}
	if total == 0 {
		return []StockMovementRow{}, 0, nil
	}

	dataQuery := `
SELECT 
    (sm.created_at AT TIME ZONE 'Asia/Jakarta')::date::text AS date,
    sm.movement_number AS number,
    sm.movement_type AS type,
    p.code AS product_code,
    p.name AS product_name,
    sm.quantity_after - sm.quantity_before AS qty,
    sm.quantity_after AS after,
    COALESCE(
        CASE 
            WHEN NULLIF(sm.reference_type, '') IS NOT NULL AND sm.reference_id IS NOT NULL 
                THEN sm.reference_type || ' #' || sm.reference_id 
            WHEN NULLIF(sm.reference_type, '') IS NOT NULL 
                THEN sm.reference_type 
            ELSE NULL 
        END,
        sm.notes,
        ''
    ) AS reference
FROM stock_movements sm
JOIN products p ON p.id = sm.product_id
WHERE ($1::date IS NULL OR (sm.created_at AT TIME ZONE 'Asia/Jakarta')::date >= $1)
  AND ($2::date IS NULL OR (sm.created_at AT TIME ZONE 'Asia/Jakarta')::date <= $2)
  AND ($3::text = '' OR sm.movement_number ILIKE '%' || $3 || '%' OR p.code ILIKE '%' || $3 || '%' OR p.name ILIKE '%' || $3 || '%')
  AND ($4::bigint[] IS NULL OR sm.warehouse_id = ANY($4))
ORDER BY sm.created_at DESC, sm.id DESC
LIMIT $5 OFFSET $6`

	limit := f.PerPage
	offset := (f.Page - 1) * f.PerPage
	rows, err := r.pool.Query(ctx, dataQuery, dFrom, dTo, search, warehouseIDs, limit, offset)
	if err != nil {
		return nil, 0, err
	}
	defer rows.Close()

	out := make([]StockMovementRow, 0, limit)
	for rows.Next() {
		var row StockMovementRow
		if err := rows.Scan(&row.Date, &row.Number, &row.Type, &row.ProductCode, &row.ProductName, &row.Qty, &row.After, &row.Reference); err != nil {
			return nil, 0, err
		}
		out = append(out, row)
	}
	return out, total, rows.Err()
}

// StockCard returns movement ledger for a specific product and optional warehouse.
func (r *Repository) StockCard(ctx context.Context, f StockCardFilter) ([]StockCardRow, error) {
	if f.ProductID <= 0 {
		return []StockCardRow{}, nil
	}
	dFrom := dateOrNil(f.DateFrom)
	dTo := dateOrNil(f.DateTo)
	var warehouseIDs []int64
	if len(f.WarehouseIDs) > 0 {
		warehouseIDs = f.WarehouseIDs
	}

	dataQuery := `
SELECT 
    (sm.created_at AT TIME ZONE 'Asia/Jakarta')::date::text AS date,
    sm.movement_number AS number,
    sm.movement_type AS type,
    GREATEST(sm.quantity_after-sm.quantity_before,0) AS in_qty,
    GREATEST(sm.quantity_before-sm.quantity_after,0) AS out_qty,
    sm.quantity_after AS balance,
    COALESCE(
        CASE 
            WHEN NULLIF(sm.reference_type, '') IS NOT NULL AND sm.reference_id IS NOT NULL 
                THEN sm.reference_type || ' #' || sm.reference_id 
            WHEN NULLIF(sm.reference_type, '') IS NOT NULL 
                THEN sm.reference_type 
            ELSE NULL 
        END,
        sm.notes,
        ''
    ) AS reference
FROM stock_movements sm
WHERE sm.product_id = $1
  AND ($2::bigint IS NULL OR $2 = 0 OR sm.warehouse_id = $2)
  AND ($3::bigint[] IS NULL OR sm.warehouse_id = ANY($3))
  AND ($4::date IS NULL OR (sm.created_at AT TIME ZONE 'Asia/Jakarta')::date >= $4)
  AND ($5::date IS NULL OR (sm.created_at AT TIME ZONE 'Asia/Jakarta')::date <= $5)
ORDER BY sm.created_at ASC, sm.id ASC`

	rows, err := r.pool.Query(ctx, dataQuery, f.ProductID, f.WarehouseID, warehouseIDs, dFrom, dTo)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	out := make([]StockCardRow, 0)
	for rows.Next() {
		var row StockCardRow
		if err := rows.Scan(&row.Date, &row.Number, &row.Type, &row.In, &row.Out, &row.Balance, &row.Reference); err != nil {
			return nil, err
		}
		out = append(out, row)
	}
	return out, rows.Err()
}