package inventory

import (
	"context"
	"errors"

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

// The same predicate serves the paginated ledger and its unpaginated totals.
const movementPredicate = ` FROM stock_movements m
LEFT JOIN products p ON p.id=m.product_id
LEFT JOIN warehouses w ON w.id=m.warehouse_id
WHERE ($1::bigint=0 OR m.product_id=$1)
AND ($2::bigint=0 OR m.warehouse_id=$2)
AND ($3::bigint[] IS NULL OR m.warehouse_id=ANY($3))
AND ($4::text='' OR m.movement_type=$4)
AND ($5::date IS NULL OR (m.created_at AT TIME ZONE 'Asia/Jakarta')::date >= $5)
AND ($6::date IS NULL OR (m.created_at AT TIME ZONE 'Asia/Jakarta')::date <= $6)
AND ($7::text='' OR m.movement_number ILIKE '%'||$7||'%' OR m.notes ILIKE '%'||$7||'%'
 OR p.name ILIKE '%'||$7||'%' OR p.code ILIKE '%'||$7||'%'
 OR m.reference_type ILIKE '%'||$7||'%' OR m.reference_id::text=$7)`

func movementArgs(f MovementFilter) []any {
	return []any{f.ProductID, f.WarehouseID, f.WarehouseIDs, f.Type, dateOrNil(f.DateFrom), dateOrNil(f.DateTo), f.Search}
}

func (r *Repository) movementList(ctx context.Context, f MovementFilter) ([]db.ListMovementsRow, int, error) {
	args := movementArgs(f)
	var total int
	if err := r.pool.QueryRow(ctx, `SELECT count(*)`+movementPredicate, args...).Scan(&total); err != nil {
		return nil, 0, err
	}
	args = append(args, f.PerPage, (f.Page-1)*f.PerPage)
	rows, err := r.pool.Query(ctx, `SELECT m.id,m.movement_number,m.product_id,m.warehouse_id,m.movement_type,m.quantity,
 m.quantity_before,m.quantity_after,m.reference_type,m.reference_id,m.notes,m.created_by,m.created_at,
 COALESCE(p.name,''),COALESCE(p.code,''),COALESCE(w.name,''),
 CASE WHEN m.quantity_after<m.quantity_before THEN 'out' ELSE 'in' END`+movementPredicate+` ORDER BY m.id DESC LIMIT $8 OFFSET $9`, args...)
	if err != nil {
		return nil, 0, err
	}
	defer rows.Close()
	out := make([]db.ListMovementsRow, 0)
	for rows.Next() {
		var m db.ListMovementsRow
		if err := rows.Scan(&m.ID, &m.MovementNumber, &m.ProductID, &m.WarehouseID, &m.MovementType, &m.Quantity, &m.QuantityBefore, &m.QuantityAfter, &m.ReferenceType, &m.ReferenceID, &m.Notes, &m.CreatedBy, &m.CreatedAt, &m.ProductName, &m.ProductCode, &m.WarehouseName, &m.Direction); err != nil {
			return nil, 0, err
		}
		out = append(out, m)
	}
	return out, total, rows.Err()
}

func (r *Repository) MovementSummary(ctx context.Context, f MovementFilter) (MovementSummary, error) {
	var out MovementSummary
	err := r.pool.QueryRow(ctx, `SELECT count(*),COALESCE(sum(GREATEST(m.quantity_after-m.quantity_before,0)),0),COALESCE(sum(GREATEST(m.quantity_before-m.quantity_after,0)),0)`+movementPredicate, movementArgs(f)...).Scan(&out.Total, &out.TotalIn, &out.TotalOut)
	return out, err
}

func (s *Service) MovementSummary(ctx context.Context, f MovementFilter) (MovementSummary, error) {
	if whs, scoped, err := s.outletWarehouses(ctx); err != nil {
		return MovementSummary{}, err
	} else if scoped {
		if len(whs) == 0 || (f.WarehouseID != 0 && !containsInt64(whs, f.WarehouseID)) {
			return MovementSummary{}, nil
		}
		f.WarehouseIDs = whs
	}
	store, ok := s.store.(interface {
		MovementSummary(context.Context, MovementFilter) (MovementSummary, error)
	})
	if !ok {
		return MovementSummary{}, errors.New("movement summary store unavailable")
	}
	return store.MovementSummary(ctx, f)
}