package distribution

import (
	"context"
	"errors"
	"strings"

	"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 on a pgx pool. Tx-scoped writes build a fresh
// querier per call (db.New(q)); pool reads use the shared querier.
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) Begin(ctx context.Context) (Tx, error) {
	return r.pool.Begin(ctx)
}

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

func (r *Repository) SalesOrderItems(ctx context.Context, orderID int64) ([]db.ListSalesOrderItemsRow, error) {
	return r.q.ListSalesOrderItems(ctx, orderID)
}

func (r *Repository) SetSalesOrderStatus(ctx context.Context, q db.DBTX, id int64, status string) error {
	_, err := db.New(q).SetSalesOrderStatus(ctx, db.SetSalesOrderStatusParams{ID: id, Status: status})
	return err
}

func (r *Repository) CreateDeliveryOrder(ctx context.Context, q db.DBTX, p db.CreateDeliveryOrderParams) (db.CreateDeliveryOrderRow, error) {
	return db.New(q).CreateDeliveryOrder(ctx, p)
}

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

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

func (r *Repository) ListDeliveryOrders(ctx context.Context, f DOFilter) ([]db.ListDeliveryOrdersRow, int, error) {
	page := f.Page
	if page < 1 {
		page = 1
	}
	perPage := f.PerPage
	if perPage < 1 || perPage > 100 {
		perPage = 20
	}
	offset := (page - 1) * perPage
	if offset < 0 {
		offset = 0
	}
	arg := db.ListDeliveryOrdersParams{
		Limit:        int32(perPage),
		Offset:       int32(offset),
		Status:       textOrNil(f.Status),
		SalesOrderID: int8OrZero(f.SalesOrderID),
		CustomerID:   int8OrZero(f.CustomerID),
		Search:       textOrNil(f.Search),
		SortBy:       textOrNil(f.SortBy),
		SortDir:      textOrNil(f.SortDir),
	}
	rows, err := r.q.ListDeliveryOrders(ctx, arg)
	if err != nil {
		return nil, 0, err
	}
	count, err := r.q.CountDeliveryOrders(ctx, db.CountDeliveryOrdersParams{
		Status:       arg.Status,
		SalesOrderID: arg.SalesOrderID,
		CustomerID:   arg.CustomerID,
		Search:       arg.Search,
	})
	if err != nil {
		return nil, 0, err
	}
	return rows, int(count), nil
}

func (r *Repository) UpdateDeliveryOrderTotals(ctx context.Context, q db.DBTX, id int64, shipped, delivered, remaining decimal.Decimal, status string) error {
	n, err := db.New(q).UpdateDeliveryOrderTotals(ctx, db.UpdateDeliveryOrderTotalsParams{
		ID: id, ShippedQuantity: shipped, DeliveredQuantity: delivered, RemainingQuantity: remaining, Status: status,
	})
	if err != nil {
		return err
	}
	if n == 0 {
		return ErrNotFound
	}
	return nil
}

func (r *Repository) SetDeliveryOrderStatus(ctx context.Context, q db.DBTX, id int64, status string) error {
	n, err := db.New(q).SetDeliveryOrderStatus(ctx, db.SetDeliveryOrderStatusParams{ID: id, Status: status})
	if err != nil {
		return err
	}
	if n == 0 {
		return ErrNotFound
	}
	return nil
}

func (r *Repository) CreateDeliveryOrderItem(ctx context.Context, q db.DBTX, p db.CreateDeliveryOrderItemParams) (int64, error) {
	return db.New(q).CreateDeliveryOrderItem(ctx, p)
}

func (r *Repository) DeliveryOrderItems(ctx context.Context, orderID int64) ([]db.ListDeliveryOrderItemsRow, error) {
	return r.q.ListDeliveryOrderItems(ctx, orderID)
}

func (r *Repository) DeliveryOrderItemsForUpdate(ctx context.Context, q db.DBTX, orderID int64) ([]db.DeliveryOrderItem, error) {
	return db.New(q).GetDeliveryOrderItemsForUpdate(ctx, orderID)
}

func (r *Repository) UpdateDeliveryOrderItemTotals(ctx context.Context, q db.DBTX, id int64, shipped, delivered, remaining decimal.Decimal) error {
	n, err := db.New(q).UpdateDeliveryOrderItemTotals(ctx, db.UpdateDeliveryOrderItemTotalsParams{
		ID: id, ShippedQuantity: shipped, DeliveredQuantity: delivered, RemainingQuantity: remaining,
	})
	if err != nil {
		return err
	}
	if n == 0 {
		return ErrNotFound
	}
	return nil
}

func (r *Repository) CreateShipment(ctx context.Context, q db.DBTX, p db.CreateShipmentParams) (db.CreateShipmentRow, error) {
	return db.New(q).CreateShipment(ctx, p)
}

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

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

func (r *Repository) ShipmentsByDeliveryOrder(ctx context.Context, orderID int64) ([]db.ListShipmentsByDeliveryOrderRow, error) {
	return r.q.ListShipmentsByDeliveryOrder(ctx, orderID)
}

func (r *Repository) SetShipmentStatus(ctx context.Context, q db.DBTX, id int64, status string) error {
	n, err := db.New(q).SetShipmentStatus(ctx, db.SetShipmentStatusParams{ID: id, Status: status})
	if err != nil {
		return err
	}
	if n == 0 {
		return ErrNotFound
	}
	return nil
}

func (r *Repository) CreateShipmentItem(ctx context.Context, q db.DBTX, p db.CreateShipmentItemParams) (int64, error) {
	return db.New(q).CreateShipmentItem(ctx, p)
}

func (r *Repository) ShipmentItems(ctx context.Context, shipmentID int64) ([]db.ListShipmentItemsRow, error) {
	return r.q.ListShipmentItems(ctx, shipmentID)
}

func (r *Repository) ShipmentItemsForUpdate(ctx context.Context, q db.DBTX, shipmentID int64) ([]db.ShipmentItem, error) {
	return db.New(q).GetShipmentItemsForUpdate(ctx, shipmentID)
}

func (r *Repository) UpdateShipmentItemDelivered(ctx context.Context, q db.DBTX, id int64, delivered decimal.Decimal) error {
	n, err := db.New(q).UpdateShipmentItemDelivered(ctx, db.UpdateShipmentItemDeliveredParams{ID: id, DeliveredQuantity: delivered})
	if err != nil {
		return err
	}
	if n == 0 {
		return ErrNotFound
	}
	return nil
}

func (r *Repository) ShipmentByShipmentNumber(ctx context.Context, number string) (db.GetShipmentByShipmentNumberRow, error) {
	row, err := r.q.GetShipmentByShipmentNumber(ctx, number)
	if errors.Is(err, pgx.ErrNoRows) {
		return db.GetShipmentByShipmentNumberRow{}, ErrNotFound
	}
	return row, err
}

func (r *Repository) ShipmentByTrackingNumber(ctx context.Context, trackingNumber string) (db.GetShipmentByTrackingNumberRow, error) {
	row, err := r.q.GetShipmentByTrackingNumber(ctx, pgtype.Text{String: trackingNumber, Valid: true})
	if errors.Is(err, pgx.ErrNoRows) {
		return db.GetShipmentByTrackingNumberRow{}, ErrNotFound
	}
	return row, err
}

func (r *Repository) ShipmentByBarcode(ctx context.Context, barcode string) (db.GetShipmentByBarcodeRow, error) {
	row, err := r.q.GetShipmentByBarcode(ctx, pgtype.Text{String: barcode, Valid: true})
	if errors.Is(err, pgx.ErrNoRows) {
		return db.GetShipmentByBarcodeRow{}, ErrNotFound
	}
	return row, err
}

func (r *Repository) ShipmentsByDeliveryOrderNumber(ctx context.Context, doNumber string) ([]db.ListShipmentsByDeliveryOrderNumberRow, error) {
	return r.q.ListShipmentsByDeliveryOrderNumber(ctx, doNumber)
}

func (r *Repository) CreateShipmentTrackingEvent(ctx context.Context, p db.CreateShipmentTrackingEventParams) (db.CreateShipmentTrackingEventRow, error) {
	return r.q.CreateShipmentTrackingEvent(ctx, p)
}

func (r *Repository) ShipmentTrackingEvents(ctx context.Context, shipmentID int64) ([]TrackingEvent, error) {
	rows, err := r.q.ListShipmentTrackingEvents(ctx, shipmentID)
	if err != nil {
		return nil, err
	}
	out := make([]TrackingEvent, 0, len(rows))
	for _, rw := range rows {
		out = append(out, eventFromListRow(rw))
	}
	return out, nil
}

func (r *Repository) ShipmentTrackingEventByProviderRef(ctx context.Context, shipmentID int64, providerRef string) (TrackingEvent, error) {
	row, err := r.q.GetShipmentTrackingEventByProviderRef(ctx, db.GetShipmentTrackingEventByProviderRefParams{
		ShipmentID:  shipmentID,
		ProviderRef: pgtype.Text{String: providerRef, Valid: true},
	})
	if errors.Is(err, pgx.ErrNoRows) {
		return TrackingEvent{}, ErrNotFound
	}
	return eventFromProviderRefRow(row), nil
}

func (r *Repository) CreateDeliveryStatusHistory(ctx context.Context, q db.DBTX, p db.CreateDeliveryStatusHistoryParams) (int64, error) {
	return db.New(q).CreateDeliveryStatusHistory(ctx, p)
}

func (r *Repository) DeliveryStatusHistory(ctx context.Context, orderID int64) ([]db.DeliveryStatusHistory, error) {
	return r.q.ListDeliveryStatusHistory(ctx, orderID)
}

func (r *Repository) CreateDispatch(ctx context.Context, q db.DBTX, p db.CreateDispatchParams) (db.CreateDispatchRow, error) {
	return db.New(q).CreateDispatch(ctx, p)
}

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

func (r *Repository) DispatchesByDeliveryOrder(ctx context.Context, orderID int64) ([]db.Dispatch, error) {
	return r.q.ListDispatchesByDeliveryOrder(ctx, orderID)
}

func (r *Repository) CreateFleet(ctx context.Context, q db.DBTX, p db.CreateFleetParams) (db.CreateFleetRow, error) {
	return db.New(q).CreateFleet(ctx, p)
}

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

func (r *Repository) ListActiveUsersForDriverPicker(ctx context.Context) ([]db.ListActiveUsersForDriverPickerRow, error) {
	return r.q.ListActiveUsersForDriverPicker(ctx)
}

func (r *Repository) CreateDriverUser(ctx context.Context, p db.CreateDriverUserParams) (db.CreateDriverUserRow, error) {
	return r.q.CreateDriverUser(ctx, p)
}

func (r *Repository) ListFleet(ctx context.Context, f FleetFilter) ([]db.Fleet, int, error) {
	page := f.Page
	if page < 1 {
		page = 1
	}
	perPage := f.PerPage
	if perPage < 1 || perPage > 100 {
		perPage = 20
	}
	offset := (page - 1) * perPage
	if offset < 0 {
		offset = 0
	}
	isActive := pgtypeBoolOrNil(f.IsActive)
	rows, err := r.q.ListFleet(ctx, db.ListFleetParams{
		Limit: int32(perPage), Offset: int32(offset), IsActive: isActive,
	})
	if err != nil {
		return nil, 0, err
	}
	count, err := r.q.CountFleet(ctx, isActive)
	if err != nil {
		return nil, 0, err
	}
	return rows, int(count), nil
}

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

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

func (r *Repository) SetStockQuantity(ctx context.Context, q db.DBTX, stockID int64, quantity, reserved decimal.Decimal) error {
	n, err := db.New(q).UpdateSalesStockRow(ctx, db.UpdateSalesStockRowParams{
		ID: stockID, Quantity: quantity, ReservedQuantity: reserved,
	})
	if err != nil {
		return err
	}
	if n == 0 {
		return ErrNotFound
	}
	return nil
}

// --- helpers ----------------------------------------------------------------

func textOrNil(v string) pgtype.Text {
	if v == "" {
		return pgtype.Text{}
	}
	return pgtype.Text{String: v, Valid: true}
}

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

func pgtypeBoolOrNil(v *bool) pgtype.Bool {
	if v == nil {
		return pgtype.Bool{}
	}
	return pgtype.Bool{Bool: *v, Valid: true}
}

// --- location/map feeds (RBAC audit 2026-08-25) ------------------------------

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

func (r *Repository) ActiveShipmentLocations(ctx context.Context) ([]db.ActiveShipmentLocationsRow, error) {
	return r.q.ActiveShipmentLocations(ctx)
}

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

func (r *Repository) ListWarehouseLocations(ctx context.Context) ([]db.ListWarehouseLocationsRow, error) {
	return r.q.ListWarehouseLocations(ctx)
}

func (r *Repository) ListStoreLocations(ctx context.Context) ([]db.ListStoreLocationsRow, error) {
	return r.q.ListStoreLocations(ctx)
}

func (r *Repository) ShipmentCustomerID(ctx context.Context, shipmentID int64) (int64, error) {
	var cid int64
	err := r.pool.QueryRow(ctx, `SELECT so.customer_id FROM shipments s
		JOIN delivery_orders d ON d.id = s.delivery_order_id
		JOIN sales_orders so ON so.id = d.sales_order_id WHERE s.id=$1`, shipmentID).Scan(&cid)
	if errors.Is(err, pgx.ErrNoRows) {
		return 0, ErrNotFound
	}
	return cid, err
}

func (r *Repository) CustomerByUserID(ctx context.Context, userID int64) (int64, error) {
	id, err := db.New(r.pool).GetCustomerIDByUserID(ctx, pgtype.Int8{Int64: userID, Valid: true})
	if errors.Is(err, pgx.ErrNoRows) {
		return 0, ErrNotFound
	}
	return id, err
}

func (r *Repository) FleetLatestLocations(ctx context.Context) ([]db.FleetLatestLocationsRow, error) {
	return r.q.FleetLatestLocations(ctx)
}

// SetDeliveryOrderPODRow writes the proof-of-delivery columns (000143). Photo
// storage happens earlier via the wired PODSaver; this only records the result.
// Raw SQL (not sqlc): a single-column UPDATE with nullable free-text values did
// not justify a query file entry.
func (r *Repository) SetDeliveryOrderPODRow(ctx context.Context, q db.DBTX, id int64, photoURL *string, recipient string) error {
	_, err := q.Exec(ctx,
		`UPDATE delivery_orders SET proof_photo_url = $2, recipient_name = $3, updated_at = now() WHERE id = $1`,
		id, textOrNilPtr(photoURL), textOrNilStrPtr(recipient))
	return err
}

func textOrNilPtr(s *string) any {
	if s == nil || strings.TrimSpace(*s) == "" {
		return nil
	}
	return *s
}

func textOrNilStrPtr(s string) any {
	if strings.TrimSpace(s) == "" {
		return nil
	}
	return s
}