package reports

import (
	"context"
	"time"

	"github.com/shopspring/decimal"
)

// Tenant-scoped variants of the report aggregates. Used by SalesReport /
// StockReport when the caller is store-bound (a kepala toko) so the ERP report
// pages show that outlet's numbers only. Platform-wide callers keep the
// unscoped queries. Wired through an interface seam in the handler so test
// fakes and alternate stores are unaffected.

// SalesBreakdownScoped mirrors SalesReportBreakdown but filtered to the given
// tenant store ids.
func (r *Repository) SalesBreakdownScoped(ctx context.Context, from, to time.Time, storeIDs []int64) ([]BreakdownRow, error) {
	if len(storeIDs) == 0 {
		return nil, nil
	}
	rows, err := r.pool.Query(ctx, `
SELECT 'total'::text AS kind, ''::text AS label,
       count(*)::int AS orders, COALESCE(SUM(so.total_amount),0)::numeric AS total
FROM sales_orders so
WHERE so.order_date >= $1 AND so.order_date < $2 AND so.status <> 'cancelled'
  AND so.store_id = ANY($3)
UNION ALL
SELECT 'category', COALESCE(c.name, 'Lainnya'), 0, 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 AND so.store_id = ANY($3)
LEFT JOIN products p ON p.id = i.product_id
LEFT JOIN categories c ON c.id = p.category_id
GROUP BY c.name
UNION ALL
SELECT 'product', p.name, 0, 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 AND so.store_id = ANY($3)
JOIN products p ON p.id = i.product_id
GROUP BY p.name`, pgdate(from), pgdate(to), storeIDs)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := []BreakdownRow{}
	for rows.Next() {
		var b BreakdownRow
		if err := rows.Scan(&b.Kind, &b.Label, &b.Orders, &b.Total); err != nil {
			return nil, err
		}
		out = append(out, b)
	}
	return out, rows.Err()
}

// StockBreakdownScoped mirrors StockReportBreakdown but filtered to the given
// warehouse ids (resolved from the caller's tenant stores).
func (r *Repository) StockBreakdownScoped(ctx context.Context, storeIDs []int64) ([]BreakdownRow, error) {
	warehouseIDs, err := r.q.WarehousesForStores(ctx, storeIDs)
	if err != nil {
		return nil, err
	}
	if len(warehouseIDs) == 0 {
		warehouseIDs = []int64{0}
	}
	rows, err := r.pool.Query(ctx, `
SELECT 'total'::text AS kind, ''::text AS label,
       COALESCE(SUM(s.quantity),0)::numeric AS qty,
       COALESCE(SUM(s.quantity * p.purchase_price),0)::numeric AS value
FROM stocks s
JOIN products p ON p.id = s.product_id AND p.deleted_at IS NULL
WHERE s.warehouse_id = ANY($1)
UNION ALL
SELECT 'category', COALESCE(c.name, 'Lainnya'),
       COALESCE(SUM(s.quantity),0)::numeric,
       COALESCE(SUM(s.quantity * p.purchase_price),0)::numeric
FROM stocks s
JOIN products p ON p.id = s.product_id AND p.deleted_at IS NULL
LEFT JOIN categories c ON c.id = p.category_id
WHERE s.warehouse_id = ANY($1)
GROUP BY c.name`, warehouseIDs)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := []BreakdownRow{}
	for rows.Next() {
		var b BreakdownRow
		var qty, val decimal.Decimal
		if err := rows.Scan(&b.Kind, &b.Label, &qty, &val); err != nil {
			return nil, err
		}
		b.Qty, b.Total = qty, val
		out = append(out, b)
	}
	return out, rows.Err()
}

// LowStockCountScoped counts low/out-of-stock products within the caller's
// outlet warehouses.
func (r *Repository) LowStockCountScoped(ctx context.Context, storeIDs []int64) (int, error) {
	warehouseIDs, err := r.q.WarehousesForStores(ctx, storeIDs)
	if err != nil {
		return 0, err
	}
	if len(warehouseIDs) == 0 {
		return 0, nil
	}
	var n int
	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.warehouse_id = ANY($1) AND s.quantity <= p.min_stock
  )`, warehouseIDs).Scan(&n)
	return n, err
}