package inventory

import (
	"context"
	"encoding/json"
	"errors"

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

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

// Repository implements Store on a pgx pool.
type Repository struct {
	pool *pgxpool.Pool
	q    *db.Queries
}

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

// Begin wraps pool.Begin; callers must Commit/Rollback.
func (r *Repository) Begin(ctx context.Context) (Tx, error) {
	return r.pool.Begin(ctx)
}

// --- stock (tx-scoped writes) ------------------------------------------------

func (r *Repository) StockRow(ctx context.Context, q db.DBTX, productID, warehouseID int64) (db.GetStockRowRow, error) {
	row, err := db.New(q).GetStockRow(ctx, db.GetStockRowParams{ProductID: productID, WarehouseID: warehouseID})
	if errors.Is(err, pgx.ErrNoRows) {
		return db.GetStockRowRow{}, ErrNotFound
	}
	return row, err
}

func (r *Repository) LockStockRow(ctx context.Context, q db.DBTX, productID, warehouseID int64) (db.GetStockRowForUpdateRow, error) {
	row, err := db.New(q).GetStockRowForUpdate(ctx, db.GetStockRowForUpdateParams{ProductID: productID, WarehouseID: warehouseID})
	if errors.Is(err, pgx.ErrNoRows) {
		return db.GetStockRowForUpdateRow{}, ErrNotFound
	}
	return row, err
}

func (r *Repository) EnsureStockRow(ctx context.Context, q db.DBTX, productID, warehouseID int64) error {
	return db.New(q).EnsureStockRow(ctx, db.EnsureStockRowParams{ProductID: productID, WarehouseID: warehouseID})
}

// UpdateStock writes the post-mutation quantity/reserved pair for a stock row
// the caller has already locked FOR UPDATE in the same tx (StockOut, adjustStock,
// OpnameFinalize, transfers), so the row cannot vanish underneath the update and
// a zero-row match is a real integrity failure rather than a legitimate no-op.
// Discarding the :execrows count made a stock movement report success while
// changing nothing — the oversell path — so the count is now mapped to
// ErrNotFound like every other package (see internal/purchasing UpdateOrder).
func (r *Repository) UpdateStock(ctx context.Context, q db.DBTX, p db.UpdateStockRowParams) error {
	n, err := db.New(q).UpdateStockRow(ctx, p)
	if err != nil {
		return err
	}
	if n == 0 {
		return ErrNotFound
	}
	return nil
}

func (r *Repository) CreateMovement(ctx context.Context, q db.DBTX, p db.CreateMovementParams) (db.CreateMovementRow, error) {
	return db.New(q).CreateMovement(ctx, p)
}

// --- queries (pool-scoped) ---------------------------------------------------

func (r *Repository) ListStock(ctx context.Context, f StockFilter) ([]db.ListStockLinesRow, int, error) {
	return r.ListStockOn(ctx, r.pool, f)
}

// ListStockOn runs on the supplied transaction so an opname snapshot reads a
// consistent point-in-time stock set within the snapshot tx (M9).
func (r *Repository) ListStockOn(ctx context.Context, q db.DBTX, f StockFilter) ([]db.ListStockLinesRow, int, error) {
	qx := db.New(q)
	search := textOrNil(f.Search)
	sortBy := textOrNil(f.SortBy)
	sortDir := textOrNil(f.SortDir)
	offset := (f.Page - 1) * f.PerPage

	if f.Warehouse > 0 {
		var total int64
		var err error
		if f.LowStock {
			total, err = qx.CountLowStockLinesByWarehouse(ctx, db.CountLowStockLinesByWarehouseParams{Column1: f.Warehouse, Search: search})
		} else {
			total, err = qx.CountStockLinesByWarehouse(ctx, db.CountStockLinesByWarehouseParams{Column1: f.Warehouse, Search: search})
		}
		if err != nil {
			return nil, 0, err
		}
		rows, err := qx.ListStockLinesByWarehouse(ctx, db.ListStockLinesByWarehouseParams{
			Limit:    int32(f.PerPage),
			Offset:   int32(offset),
			Column3:  f.Warehouse,
			Search:   search,
			LowStock: pgtype.Bool{Bool: f.LowStock, Valid: f.LowStock},
			SortBy:   sortBy,
			SortDir:  sortDir,
		})
		if err != nil {
			return nil, 0, err
		}
		whName := ""
		if wh, err := qx.GetWarehouseByID(ctx, f.Warehouse); err == nil {
			whName = wh.Name
		}
		out := make([]db.ListStockLinesRow, 0, len(rows))
		for _, row := range rows {
			b, _ := json.Marshal([]map[string]any{
				{
					"warehouse_id":      f.Warehouse,
					"warehouse_name":    whName,
					"quantity":          row.Quantity,
					"reserved_quantity": row.ReservedQuantity,
				},
			})
			out = append(out, db.ListStockLinesRow{
				ID: row.ID, Code: row.Code, Name: row.Name, CategoryName: row.CategoryName,
				UnitSymbol: row.UnitSymbol, Quantity: row.Quantity, MinStock: row.MinStock,
				IsActive: row.IsActive, ReservedQuantity: row.ReservedQuantity,
				PurchasePrice: row.PurchasePrice, Value: row.Value,
				Outlets: b,
			})
		}
		return out, int(total), nil
	}

	var total int64
	var err error
	if f.LowStock {
		total, err = qx.CountLowStockLines(ctx, search)
	} else {
		total, err = qx.CountStockLines(ctx, search)
	}
	if err != nil {
		return nil, 0, err
	}
	rows, err := qx.ListStockLines(ctx, db.ListStockLinesParams{
		Limit:       int32(f.PerPage),
		Offset:      int32(offset),
		Search:      search,
		LowStock:    pgtype.Bool{Bool: f.LowStock, Valid: f.LowStock},
		WarehouseID: pgtype.Int8{Int64: f.Warehouse, Valid: f.Warehouse > 0},
		SortBy:      sortBy,
		SortDir:     sortDir,
	})
	if err != nil {
		return nil, 0, err
	}
	return rows, int(total), nil
}

// StockSummary returns StockSummaryValues, NOT the view type: this layer has no
// authenticated user on its context and so cannot decide whether the caller may
// see cost. See the Store method's comment.
func (r *Repository) StockSummary(ctx context.Context, f StockFilter) (StockSummaryValues, error) {
	search := textOrNil(f.Search)
	if f.Warehouse > 0 {
		query := `
SELECT 
    COUNT(p.id)::int AS total_products,
    COALESCE(SUM(s.quantity), 0)::numeric AS total_qty,
    COALESCE(SUM(s.quantity * p.purchase_price), 0)::numeric AS total_value,
    COUNT(p.id) FILTER (WHERE s.quantity IS NULL OR s.quantity <= p.min_stock)::int AS low_stock
FROM products p
LEFT JOIN stocks s ON s.product_id = p.id AND s.warehouse_id = $1::bigint
WHERE p.deleted_at IS NULL
  AND EXISTS (
      SELECT 1 FROM warehouses w
      JOIN stores str ON str.warehouse_id = w.id
      WHERE w.id = $1::bigint AND w.is_active = true AND str.is_active = true
  )
  AND ($2::text IS NULL OR (p.code ILIKE '%' || $2 || '%' OR p.name ILIKE '%' || $2 || '%'))`

		var s StockSummaryValues
		err := r.pool.QueryRow(ctx, query, f.Warehouse, search).Scan(&s.TotalProducts, &s.TotalQty, &s.TotalValue, &s.LowStock)
		if err != nil {
			return StockSummaryValues{}, err
		}
		return s, nil
	}

	query := `
SELECT 
    COUNT(p.id)::int AS total_products,
    COALESCE(SUM(s.qty), 0)::numeric AS total_qty,
    COALESCE(SUM(s.qty * p.purchase_price), 0)::numeric AS total_value,
    COUNT(p.id) FILTER (WHERE s.qty IS NULL OR s.qty <= p.min_stock)::int AS low_stock
FROM products p
LEFT JOIN (
    SELECT st.product_id, SUM(GREATEST(0, st.quantity)) AS qty
    FROM stocks st
    JOIN warehouses w ON w.id = st.warehouse_id
    JOIN stores str ON str.warehouse_id = w.id
    WHERE w.is_active = true
      AND str.is_active = true
    GROUP BY st.product_id
) s ON s.product_id = p.id
WHERE p.deleted_at IS NULL
  AND ($1::text IS NULL OR (p.code ILIKE '%' || $1 || '%' OR p.name ILIKE '%' || $1 || '%'))`

	var s StockSummaryValues
	err := r.pool.QueryRow(ctx, query, search).Scan(&s.TotalProducts, &s.TotalQty, &s.TotalValue, &s.LowStock)
	if err != nil {
		return StockSummaryValues{}, err
	}
	return s, nil
}

func (r *Repository) ListMovements(ctx context.Context, f MovementFilter) ([]db.ListMovementsRow, int, error) {
	return r.movementList(ctx, f)
}

func (r *Repository) ProductExists(ctx context.Context, id int64) (bool, error) {
	n, err := r.q.CountProductsByID(ctx, id)
	return n > 0, err
}

func (r *Repository) WarehouseExists(ctx context.Context, id int64) (bool, error) {
	n, err := r.q.CountWarehousesByID(ctx, id)
	return n > 0, err
}

func (r *Repository) WarehousesForStores(ctx context.Context, storeIDs []int64) ([]int64, error) {
	if len(storeIDs) == 0 {
		return nil, nil
	}
	return r.q.WarehousesForStores(ctx, storeIDs)
}

// --- opname ------------------------------------------------------------------

// WarehouseStockFrozen reads the freeze flag inside the caller's transaction so
// a movement cannot slip in between the check and the write.
func (r *Repository) WarehouseStockFrozen(ctx context.Context, q db.DBTX, warehouseID int64) (bool, error) {
	return db.New(q).WarehouseStockFrozen(ctx, warehouseID)
}

func (r *Repository) CreateOpname(ctx context.Context, p db.CreateOpnameParams) (int64, error) {
	return r.CreateOpnameOn(ctx, r.pool, p)
}

// CreateOpnameOn runs on the supplied transaction so the opname snapshot can be
// committed atomically with its items (M9).
func (r *Repository) CreateOpnameOn(ctx context.Context, q db.DBTX, p db.CreateOpnameParams) (int64, error) {
	return db.New(q).CreateOpname(ctx, p)
}

func (r *Repository) OpnameByID(ctx context.Context, id int64) (db.GetOpnameByIDRow, error) {
	row, err := r.q.GetOpnameByID(ctx, id)
	if errors.Is(err, pgx.ErrNoRows) {
		return db.GetOpnameByIDRow{}, ErrNotFound
	}
	return row, err
}

func (r *Repository) LockOpnameByID(ctx context.Context, q db.DBTX, id int64) (db.LockOpnameByIDRow, error) {
	row, err := db.New(q).LockOpnameByID(ctx, id)
	if errors.Is(err, pgx.ErrNoRows) {
		return db.LockOpnameByIDRow{}, ErrNotFound
	}
	return row, err
}

func (r *Repository) ListOpnames(ctx context.Context, f OpnameFilter) ([]db.ListOpnamesRow, int, error) {
	filter := db.CountOpnamesParams{Status: textOrNil(f.Status), WarehouseID: positiveInt8(f.WarehouseID)}
	total, err := r.q.CountOpnames(ctx, filter)
	if err != nil {
		return nil, 0, err
	}
	rows, err := r.q.ListOpnames(ctx, db.ListOpnamesParams{
		Limit: int32(f.PerPage), Offset: int32((f.Page - 1) * f.PerPage), Status: filter.Status, WarehouseID: filter.WarehouseID,
	})
	if err != nil {
		return nil, 0, err
	}
	return rows, int(total), nil
}

func (r *Repository) CreateOpnameItem(ctx context.Context, p db.CreateOpnameItemParams) (int64, error) {
	return r.CreateOpnameItemOn(ctx, r.pool, p)
}

// CreateOpnameItemOn runs on the supplied transaction (M9).
func (r *Repository) CreateOpnameItemOn(ctx context.Context, q db.DBTX, p db.CreateOpnameItemParams) (int64, error) {
	return db.New(q).CreateOpnameItem(ctx, p)
}

func (r *Repository) OpnameItems(ctx context.Context, opnameID int64) ([]db.ListOpnameItemsRow, error) {
	return r.OpnameItemsOn(ctx, r.pool, opnameID)
}

func (r *Repository) OpnameItemsOn(ctx context.Context, q db.DBTX, opnameID int64) ([]db.ListOpnameItemsRow, error) {
	return db.New(q).ListOpnameItems(ctx, opnameID)
}

func (r *Repository) SetOpnameItemsPhysical(ctx context.Context, opnameID int64, items []OpnameItemInput) error {
	for _, in := range items {
		n, err := r.q.SetOpnameItemPhysical(ctx, db.SetOpnameItemPhysicalParams{
			StockOpnameID:    opnameID,
			ProductID:        in.ProductID,
			PhysicalQuantity: decToNdPtr(in.PhysicalQty),
		})
		if err != nil {
			return err
		}
		// A product id with no snapshot row in this opname is caller error,
		// not an empty write — surface it instead of recording nothing.
		if n == 0 {
			return ErrNotFound
		}
	}
	return nil
}

func (r *Repository) UpdateOpnameStatus(ctx context.Context, id int64, status string) error {
	return r.UpdateOpnameStatusOn(ctx, r.pool, id, status)
}

// UpdateOpnameStatusOn sets the opname status by primary key only — no status
// predicate — so Postgres counts the row even when the new status equals the old
// one and a zero can only mean the opname is gone. OpnameFinalize calls this
// after adjusting every counted item; letting a zero pass committed those
// adjustments while the opname stayed 'draft'. The count is now mapped to
// ErrNotFound so the enclosing tx rolls back.
func (r *Repository) UpdateOpnameStatusOn(ctx context.Context, q db.DBTX, id int64, status string) error {
	n, err := db.New(q).UpdateOpnameStatus(ctx, db.UpdateOpnameStatusParams{ID: id, Status: status})
	if err != nil {
		return err
	}
	if n == 0 {
		return ErrNotFound
	}
	return nil
}

func positiveInt8(v int64) pgtype.Int8 {
	if v <= 0 {
		return pgtype.Int8{}
	}
	return pgtype.Int8{Int64: v, Valid: true}
}