package salestracking

import (
	"context"
	"errors"

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

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

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

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

// --- visits ------------------------------------------------------------------

func (r *Repository) ListVisits(ctx context.Context, f Filter, salesID *int64) ([]db.ListSalesVisitsRow, int64, error) {
	params := db.CountSalesVisitsParams{
		SalesID:   int8OrNil(salesID),
		Status:    textOrNil(f.Status),
		DateFrom:  dateOrNil(f.DateFrom),
		DateTo:    dateOrNil(f.DateTo),
		VisitType: textOrNil(f.VisitType),
	}
	total, err := r.q.CountSalesVisits(ctx, params)
	if err != nil {
		return nil, 0, err
	}
	page, per := normPage(f.Page, f.PerPage)
	rows, err := r.q.ListSalesVisits(ctx, db.ListSalesVisitsParams{
		Limit:     int32(per),
		Offset:    int32((page - 1) * per),
		SalesID:   params.SalesID,
		Status:    params.Status,
		DateFrom:  params.DateFrom,
		DateTo:    params.DateTo,
		VisitType: params.VisitType,
	})
	if err != nil {
		return nil, 0, err
	}
	return rows, total, nil
}

func (r *Repository) VisitByID(ctx context.Context, id int64) (db.GetSalesVisitByIDRow, error) {
	row, err := r.q.GetSalesVisitByID(ctx, id)
	return row, mapErr(err)
}

func (r *Repository) CreateVisit(ctx context.Context, p db.CreateSalesVisitParams) (int64, error) {
	return r.q.CreateSalesVisit(ctx, p)
}

func (r *Repository) UpdateVisit(ctx context.Context, p db.UpdateSalesVisitParams) error {
	n, err := r.q.UpdateSalesVisit(ctx, p)
	if err != nil {
		return err
	}
	if n == 0 {
		return ErrNotFound
	}
	return nil
}

func (r *Repository) UpdateVisitPhotos(ctx context.Context, id int64, photoIn, photoOut *string) error {
	if photoIn == nil && photoOut == nil {
		return nil
	}
	_, err := r.pool.Exec(ctx, `
		UPDATE sales_visits SET
			photo_check_in = COALESCE($2, photo_check_in),
			photo_check_out = COALESCE($3, photo_check_out)
		WHERE id = $1
	`, id, photoIn, photoOut)
	return err
}

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

// --- activities --------------------------------------------------------------

func (r *Repository) CreateActivity(ctx context.Context, p db.CreateSalesActivityParams) (int64, error) {
	return r.q.CreateSalesActivity(ctx, p)
}

func (r *Repository) ListActivities(ctx context.Context, f Filter, salesID *int64) ([]db.ListSalesActivitiesRow, int64, error) {
	params := db.CountSalesActivitiesParams{
		SalesID:      int8OrNil(salesID),
		DateFrom:     dateOrNil(f.DateFrom),
		DateTo:       dateOrNil(f.DateTo),
		ActivityType: f.Type,
	}
	total, err := r.q.CountSalesActivities(ctx, params)
	if err != nil {
		return nil, 0, err
	}
	page, per := normPage(f.Page, f.PerPage)
	rows, err := r.q.ListSalesActivities(ctx, db.ListSalesActivitiesParams{
		Limit:        int32(per),
		Offset:       int32((page - 1) * per),
		SalesID:      params.SalesID,
		DateFrom:     params.DateFrom,
		DateTo:       params.DateTo,
		ActivityType: params.ActivityType,
	})
	if err != nil {
		return nil, 0, err
	}
	return rows, total, nil
}

// --- routes ------------------------------------------------------------------

func (r *Repository) ListRoutes(ctx context.Context, f Filter, salesID *int64) ([]db.ListSalesRoutesRow, int64, error) {
	params := db.CountSalesRoutesParams{
		SalesID:  int8OrNil(salesID),
		Status:   textOrNil(f.Status),
		DateFrom: dateOrNil(f.DateFrom),
		DateTo:   dateOrNil(f.DateTo),
	}
	total, err := r.q.CountSalesRoutes(ctx, params)
	if err != nil {
		return nil, 0, err
	}
	page, per := normPage(f.Page, f.PerPage)
	rows, err := r.q.ListSalesRoutes(ctx, db.ListSalesRoutesParams{
		Limit:    int32(per),
		Offset:   int32((page - 1) * per),
		SalesID:  params.SalesID,
		Status:   params.Status,
		DateFrom: params.DateFrom,
		DateTo:   params.DateTo,
	})
	if err != nil {
		return nil, 0, err
	}
	return rows, total, nil
}

func (r *Repository) CreateRoute(ctx context.Context, p db.CreateSalesRouteParams) (int64, error) {
	return r.q.CreateSalesRoute(ctx, p)
}

// --- locations ---------------------------------------------------------------

func (r *Repository) InsertLocations(ctx context.Context, locs []db.CreateSalesLocationParams) error {
	if len(locs) == 0 {
		return nil
	}
	for _, l := range locs {
		if err := r.q.CreateSalesLocation(ctx, l); err != nil {
			return err
		}
	}
	return nil
}

func (r *Repository) LatestLocations(ctx context.Context) ([]db.LatestSalesLocationsRow, error) {
	return r.q.LatestSalesLocations(ctx)
}

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

// ListCustomerLocations lists customer sites with visit counts for the ERP
// "Lokasi" page. ponytail: raw SQL instead of sqlc — one query, no regen;
// move to db/query/salestracking.sql if it grows more filters.
func (r *Repository) ListCustomerLocations(ctx context.Context, search, city string, limit, offset int) ([]CustomerLocation, int64, error) {
	const base = `FROM customers c
WHERE c.deleted_at IS NULL
  AND ($1::text IS NULL OR c.name ILIKE '%'||$1||'%' OR c.code ILIKE '%'||$1||'%')
  AND ($2::text IS NULL OR c.city = $2)`
	var total int64
	if err := r.pool.QueryRow(ctx, "SELECT count(*) "+base, textOrNil(search), textOrNil(city)).Scan(&total); err != nil {
		return nil, 0, err
	}
	rows, err := r.pool.Query(ctx, `SELECT c.id, c.name, c.city,
       (SELECT COUNT(*) FROM sales_visits v WHERE v.customer_id = c.id) AS visit_count,
       c.is_active
`+base+" ORDER BY visit_count DESC, c.id LIMIT $3 OFFSET $4",
		textOrNil(search), textOrNil(city), limit, offset)
	if err != nil {
		return nil, 0, err
	}
	defer rows.Close()
	out := make([]CustomerLocation, 0, limit)
	for rows.Next() {
		var cl CustomerLocation
		if err := rows.Scan(&cl.ID, &cl.Name, &cl.City, &cl.VisitCount, &cl.IsActive); err != nil {
			return nil, 0, err
		}
		out = append(out, cl)
	}
	return out, total, rows.Err()
}

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

func mapErr(err error) error {
	if errors.Is(err, pgx.ErrNoRows) {
		return ErrNotFound
	}
	return err
}

func normPage(page, perPage int) (int, int) {
	if page < 1 {
		page = 1
	}
	if perPage < 1 || perPage > 100 {
		perPage = 20
	}
	return page, perPage
}