package salestargets

import (
	"context"
	"time"

	"github.com/jackc/pgx/v5/pgxpool"
	"github.com/shopspring/decimal"
)

type Repository struct {
	pool *pgxpool.Pool
	loc  *time.Location
}

func NewRepository(pool *pgxpool.Pool, loc *time.Location) *Repository {
	if loc == nil {
		loc = time.Local
	}
	return &Repository{pool: pool, loc: loc}
}

// List returns sales targets filtered by period, role_level, or scope_level.
// mineUserID > 0 restricts the result to targets whose PIC is that account —
// the "my target" view every non-GM sales account sees.
func (r *Repository) List(ctx context.Context, period, roleLevel, scopeLevel string, mineUserID int64) ([]SalesTarget, error) {
	if period == "" {
		period = time.Now().In(r.loc).Format("2006-01")
	}

	query := `
SELECT t.id, t.period, t.role_level, t.user_id, COALESCE(u.name, ''),
       t.scope_level, t.scope_name, t.target_amount, t.target_orders, t.target_visits,
       t.achieved_amount, t.achieved_orders, t.achieved_visits,
       COALESCE(t.notes, ''), t.created_by,
       to_char(t.created_at, 'YYYY-MM-DD HH24:MI:SS'),
       to_char(t.updated_at, 'YYYY-MM-DD HH24:MI:SS')
FROM sales_targets t
LEFT JOIN users u ON u.id = t.user_id
WHERE t.period = $1
  AND ($2 = '' OR t.role_level = $2)
  AND ($3 = '' OR t.scope_level = $3)
  AND ($4::bigint = 0 OR t.user_id = $4)
ORDER BY
  CASE t.role_level
    WHEN 'gm' THEN 1
    WHEN 'bm' THEN 2
    WHEN 'spv' THEN 3
    WHEN 'sales' THEN 4
    ELSE 5
  END, t.id ASC`

	rows, err := r.pool.Query(ctx, query, period, roleLevel, scopeLevel, mineUserID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	out := make([]SalesTarget, 0)
	for rows.Next() {
		var t SalesTarget
		if err := rows.Scan(
			&t.ID, &t.Period, &t.RoleLevel, &t.UserID, &t.UserName,
			&t.ScopeLevel, &t.ScopeName, &t.TargetAmount, &t.TargetOrders, &t.TargetVisits,
			&t.AchievedAmount, &t.AchievedOrders, &t.AchievedVisits,
			&t.Notes, &t.CreatedBy, &t.CreatedAt, &t.UpdatedAt,
		); err != nil {
			return nil, err
		}

		// Calculate live achievement from sales_orders and sales_visits
		r.syncLiveAchievement(ctx, &t)

		if t.TargetAmount.Sign() > 0 {
			pct := t.AchievedAmount.Div(t.TargetAmount).Mul(decimal.NewFromInt(100)).Round(1)
			f, _ := pct.Float64()
			t.AchievementPct = f
		}
		out = append(out, t)
	}
	return out, nil
}

func (r *Repository) syncLiveAchievement(ctx context.Context, t *SalesTarget) {
	var achievedAmt decimal.Decimal
	var achievedOrders int
	var achievedVisits int

	var employeeID int64
	if t.UserID != nil && *t.UserID > 0 {
		_ = r.pool.QueryRow(ctx, `SELECT id FROM employees WHERE user_id = $1`, *t.UserID).Scan(&employeeID)
	}

	uid := userIDOrNil(t.UserID)

	switch t.RoleLevel {
	case "gm":
		// GM target covers entire sales force in that city/scope (excluding retail POS counter sales)
		_ = r.pool.QueryRow(ctx, `
SELECT COALESCE(SUM(so.total_amount), 0), COUNT(*)
FROM sales_orders so
JOIN customers c ON c.id = so.customer_id
LEFT JOIN employees e ON e.id = so.sales_id
WHERE to_char(so.order_date, 'YYYY-MM') = $1
  AND so.status IN ('confirmed', 'picking', 'shipped', 'delivered', 'invoiced', 'paid')
  AND so.order_type <> 'pos'
  AND (
    $2 = '' OR $2 = '-'
    OR c.city ILIKE '%' || $2 || '%'
    OR so.shipping_address ILIKE '%' || $2 || '%'
    OR c.city ILIKE '%' || regexp_replace($2, '^(kota|kabupaten)\s+', '', 'i') || '%'
    OR so.shipping_address ILIKE '%' || regexp_replace($2, '^(kota|kabupaten)\s+', '', 'i') || '%'
  )`, t.Period, t.ScopeName).Scan(&achievedAmt, &achievedOrders)

		_ = r.pool.QueryRow(ctx, `
SELECT COUNT(*)
FROM sales_visits sv
JOIN customers c ON c.id = sv.customer_id
LEFT JOIN employees e ON e.id = sv.sales_id
WHERE to_char(sv.visit_date, 'YYYY-MM') = $1
  AND sv.status = 'completed'
  AND (
    $2 = '' OR $2 = '-'
    OR c.city ILIKE '%' || $2 || '%'
    OR e.city ILIKE '%' || $2 || '%'
    OR c.city ILIKE '%' || regexp_replace($2, '^(kota|kabupaten)\s+', '', 'i') || '%'
    OR e.city ILIKE '%' || regexp_replace($2, '^(kota|kabupaten)\s+', '', 'i') || '%'
  )`, t.Period, t.ScopeName).Scan(&achievedVisits)

	case "bm":
		// Branch Manager: orders in BM's kecamatan
		_ = r.pool.QueryRow(ctx, `
SELECT COALESCE(SUM(so.total_amount), 0), COUNT(*)
FROM sales_orders so
JOIN customers c ON c.id = so.customer_id
LEFT JOIN employees e ON e.id = so.sales_id
WHERE to_char(so.order_date, 'YYYY-MM') = $1
  AND so.status IN ('confirmed', 'picking', 'shipped', 'delivered', 'invoiced', 'paid')
  AND so.order_type <> 'pos'
  AND (
    ($2::bigint > 0 AND (so.supervisor_id = $2 OR so.approved_by = $2 OR ($3::bigint > 0 AND (so.sales_id = $3 OR c.assigned_sales_id = $3))))
    OR ($2::bigint <= 0 AND $3::bigint <= 0)
  )
  AND (
    $4 = '' OR $4 = '-'
    OR c.kecamatan ILIKE '%' || $4 || '%'
    OR (c.kecamatan IS NULL AND (c.address ILIKE '%' || $4 || '%' OR so.shipping_address ILIKE '%' || $4 || '%'))
  )`, t.Period, uid, employeeID, t.ScopeName).Scan(&achievedAmt, &achievedOrders)

		_ = r.pool.QueryRow(ctx, `
SELECT COUNT(*)
FROM sales_visits sv
JOIN customers c ON c.id = sv.customer_id
LEFT JOIN employees e ON e.id = sv.sales_id
WHERE to_char(sv.visit_date, 'YYYY-MM') = $1
  AND sv.status = 'completed'
  AND (
    ($2::bigint > 0 AND ($3::bigint > 0 AND (sv.sales_id = $3 OR sv.scheduled_by = $3)))
    OR ($2::bigint <= 0 AND $3::bigint <= 0)
  )
  AND (
    $4 = '' OR $4 = '-'
    OR c.kecamatan ILIKE '%' || $4 || '%'
    OR (c.kecamatan IS NULL AND (c.address ILIKE '%' || $4 || '%'))
  )`, t.Period, uid, employeeID, t.ScopeName).Scan(&achievedVisits)

	case "spv":
		// Supervisor: orders in SPV's kelurahan
		_ = r.pool.QueryRow(ctx, `
SELECT COALESCE(SUM(so.total_amount), 0), COUNT(*)
FROM sales_orders so
JOIN customers c ON c.id = so.customer_id
LEFT JOIN employees e ON e.id = so.sales_id
WHERE to_char(so.order_date, 'YYYY-MM') = $1
  AND so.status IN ('confirmed', 'picking', 'shipped', 'delivered', 'invoiced', 'paid')
  AND so.order_type <> 'pos'
  AND (
    ($2::bigint > 0 AND (so.supervisor_id = $2 OR so.approved_by = $2 OR ($3::bigint > 0 AND (so.sales_id = $3 OR c.assigned_sales_id = $3))))
    OR ($2::bigint <= 0 AND $3::bigint <= 0)
  )
  AND (
    $4 = '' OR $4 = '-'
    OR c.kelurahan ILIKE '%' || $4 || '%'
    OR (c.kelurahan IS NULL AND (c.address ILIKE '%' || $4 || '%' OR so.shipping_address ILIKE '%' || $4 || '%'))
  )`, t.Period, uid, employeeID, t.ScopeName).Scan(&achievedAmt, &achievedOrders)

		_ = r.pool.QueryRow(ctx, `
SELECT COUNT(*)
FROM sales_visits sv
JOIN customers c ON c.id = sv.customer_id
LEFT JOIN employees e ON e.id = sv.sales_id
WHERE to_char(sv.visit_date, 'YYYY-MM') = $1
  AND sv.status = 'completed'
  AND (
    ($2::bigint > 0 AND ($3::bigint > 0 AND (sv.sales_id = $3 OR sv.scheduled_by = $3)))
    OR ($2::bigint <= 0 AND $3::bigint <= 0)
  )
  AND (
    $4 = '' OR $4 = '-'
    OR c.kelurahan ILIKE '%' || $4 || '%'
    OR (c.kelurahan IS NULL AND (c.address ILIKE '%' || $4 || '%'))
  )`, t.Period, uid, employeeID, t.ScopeName).Scan(&achievedVisits)

	case "sales":
		// Sales Lapangan: individual field sales performance matching territorial scope
		_ = r.pool.QueryRow(ctx, `
SELECT COALESCE(SUM(so.total_amount), 0), COUNT(*)
FROM sales_orders so
JOIN customers c ON c.id = so.customer_id
WHERE to_char(so.order_date, 'YYYY-MM') = $1
  AND so.status IN ('confirmed', 'picking', 'shipped', 'delivered', 'invoiced', 'paid')
  AND so.order_type <> 'pos'
  AND (
    ($2::bigint > 0 AND (so.sales_id = $2 OR c.assigned_sales_id = $2))
    OR ($2::bigint <= 0)
  )
  AND (
    $3 = '' OR $3 = '-'
    OR c.desa ILIKE '%' || $3 || '%'
    OR (c.desa IS NULL AND (c.address ILIKE '%' || $3 || '%' OR so.shipping_address ILIKE '%' || $3 || '%'))
  )`, t.Period, employeeID, t.ScopeName).Scan(&achievedAmt, &achievedOrders)

		_ = r.pool.QueryRow(ctx, `
SELECT COUNT(*)
FROM sales_visits sv
JOIN customers c ON c.id = sv.customer_id
WHERE to_char(sv.visit_date, 'YYYY-MM') = $1
  AND sv.status = 'completed'
  AND (
    ($2::bigint > 0 AND sv.sales_id = $2)
    OR ($2::bigint <= 0)
  )
  AND (
    $3 = '' OR $3 = '-'
    OR c.desa ILIKE '%' || $3 || '%'
    OR (c.desa IS NULL AND (c.address ILIKE '%' || $3 || '%'))
  )`, t.Period, employeeID, t.ScopeName).Scan(&achievedVisits)
	}

	t.AchievedAmount = achievedAmt
	t.AchievedOrders = achievedOrders
	t.AchievedVisits = achievedVisits

	if t.TargetAmount.Sign() > 0 {
		pct := t.AchievedAmount.Div(t.TargetAmount).Mul(decimal.NewFromInt(100)).Round(1)
		f, _ := pct.Float64()
		t.AchievementPct = f
	} else {
		t.AchievementPct = 0
	}

	// Persist synchronized achievement to database if target already exists (ID > 0)
	if t.ID > 0 {
		_, _ = r.pool.Exec(ctx, `
UPDATE sales_targets
SET achieved_amount = $1, achieved_orders = $2, achieved_visits = $3
WHERE id = $4 AND (achieved_amount <> $1 OR achieved_orders <> $2 OR achieved_visits <> $3)`,
			achievedAmt, achievedOrders, achievedVisits, t.ID)
	}
}

// syncTerritorial rolls up achievement for area-scoped targets (BM=1
// kecamatan, SPV=1 kelurahan, Sales Lapangan=1 desa). It sums orders and
// completed visits of customers inside the target's administrative area —
// exact match on the customers area column (migration 000094), falling back
// to the legacy free-text ILIKE on city/address when that column is NULL so
// pre-000094 data keeps counting.
func (r *Repository) syncTerritorial(ctx context.Context, t *SalesTarget, achievedAmt *decimal.Decimal, achievedOrders, achievedVisits *int) {
	areaCol := "kecamatan"
	switch t.RoleLevel {
	case "spv":
		areaCol = "kelurahan"
	case "sales":
		areaCol = "desa"
	}
	// areaCol comes from the fixed whitelist above — never user input.
	_ = r.pool.QueryRow(ctx, `
SELECT COALESCE(SUM(so.total_amount), 0), COUNT(*)
FROM sales_orders so
JOIN customers c ON c.id = so.customer_id
WHERE to_char(so.order_date, 'YYYY-MM') = $1
  AND (c.`+areaCol+` = $2 OR (c.`+areaCol+` IS NULL AND (c.city ILIKE '%' || $2 || '%' OR c.address ILIKE '%' || $2 || '%')))
  AND so.status IN ('confirmed', 'picking', 'shipped', 'delivered', 'invoiced', 'paid')`, t.Period, t.ScopeName).Scan(achievedAmt, achievedOrders)

	_ = r.pool.QueryRow(ctx, `
SELECT COUNT(*)
FROM sales_visits sv
JOIN customers c ON c.id = sv.customer_id
WHERE to_char(sv.visit_date, 'YYYY-MM') = $1
  AND (c.`+areaCol+` = $2 OR (c.`+areaCol+` IS NULL AND (c.city ILIKE '%' || $2 || '%' OR c.address ILIKE '%' || $2 || '%')))
  AND sv.status = 'completed'`, t.Period, t.ScopeName).Scan(achievedVisits)
}

// Create inserts a new sales target.
func (r *Repository) Create(ctx context.Context, in TargetInput, createdBy int64) (*SalesTarget, error) {
	if in.Period == "" {
		in.Period = time.Now().In(r.loc).Format("2006-01")
	}

	var id int64
	err := r.pool.QueryRow(ctx, `
INSERT INTO sales_targets (period, role_level, user_id, scope_level, scope_name,
                          target_amount, target_orders, target_visits, notes, created_by)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
RETURNING id`,
		in.Period, in.RoleLevel, in.UserID, in.ScopeLevel, in.ScopeName,
		in.TargetAmount, in.TargetOrders, in.TargetVisits, in.Notes, createdBy,
	).Scan(&id)
	if err != nil {
		return nil, err
	}

	return r.GetByID(ctx, id)
}

// GetByID returns a single target.
func (r *Repository) GetByID(ctx context.Context, id int64) (*SalesTarget, error) {
	var t SalesTarget
	err := r.pool.QueryRow(ctx, `
SELECT t.id, t.period, t.role_level, t.user_id, COALESCE(u.name, ''),
       t.scope_level, t.scope_name, t.target_amount, t.target_orders, t.target_visits,
       t.achieved_amount, t.achieved_orders, t.achieved_visits,
       COALESCE(t.notes, ''), t.created_by,
       to_char(t.created_at, 'YYYY-MM-DD HH24:MI:SS'),
       to_char(t.updated_at, 'YYYY-MM-DD HH24:MI:SS')
FROM sales_targets t
LEFT JOIN users u ON u.id = t.user_id
WHERE t.id = $1`, id).Scan(
		&t.ID, &t.Period, &t.RoleLevel, &t.UserID, &t.UserName,
		&t.ScopeLevel, &t.ScopeName, &t.TargetAmount, &t.TargetOrders, &t.TargetVisits,
		&t.AchievedAmount, &t.AchievedOrders, &t.AchievedVisits,
		&t.Notes, &t.CreatedBy, &t.CreatedAt, &t.UpdatedAt,
	)
	if err != nil {
		return nil, err
	}
	r.syncLiveAchievement(ctx, &t)
	if t.TargetAmount.Sign() > 0 {
		pct := t.AchievedAmount.Div(t.TargetAmount).Mul(decimal.NewFromInt(100)).Round(1)
		f, _ := pct.Float64()
		t.AchievementPct = f
	}
	return &t, nil
}

// Update updates an existing target. A zero user_id keeps the current PIC so
// a partial update never unlinks the accountable account.
func (r *Repository) Update(ctx context.Context, id int64, in TargetInput) (*SalesTarget, error) {
	_, err := r.pool.Exec(ctx, `
UPDATE sales_targets
SET period = $2, role_level = $3,
    user_id = COALESCE(NULLIF($4, 0), user_id),
    scope_level = $5, scope_name = $6,
    target_amount = $7, target_orders = $8, target_visits = $9, notes = $10, updated_at = now()
WHERE id = $1`,
		id, in.Period, in.RoleLevel, userIDOrNil(in.UserID), in.ScopeLevel, in.ScopeName,
		in.TargetAmount, in.TargetOrders, in.TargetVisits, in.Notes,
	)
	if err != nil {
		return nil, err
	}
	return r.GetByID(ctx, id)
}

// userIDOrNil converts the input's optional user id to the 0 sentinel the
// UPDATE's NULLIF expects.
func userIDOrNil(id *int64) int64 {
	if id == nil || *id <= 0 {
		return 0
	}
	return *id
}

// Delete removes a target.
func (r *Repository) Delete(ctx context.Context, id int64) error {
	_, err := r.pool.Exec(ctx, `DELETE FROM sales_targets WHERE id = $1`, id)
	return err
}

// Summary returns high-level target vs achievement grouped by role_level hierarchy.
func (r *Repository) Summary(ctx context.Context, period string) ([]LevelSummary, error) {
	if period == "" {
		period = time.Now().In(r.loc).Format("2006-01")
	}

	targets, err := r.List(ctx, period, "", "", 0)
	if err != nil {
		return nil, err
	}

	levels := []struct {
		Role  string
		Scope string
	}{
		{"gm", "kota"},
		{"bm", "kecamatan"},
		{"spv", "kelurahan"},
		{"sales", "desa"},
	}

	summaries := make([]LevelSummary, 0, len(levels))
	for _, l := range levels {
		ls := LevelSummary{
			RoleLevel:      l.Role,
			ScopeLevel:     l.Scope,
			TargetAmount:   decimal.Zero,
			AchievedAmount: decimal.Zero,
		}

		for _, t := range targets {
			if t.RoleLevel == l.Role {
				ls.TargetCount++
				ls.TargetAmount = ls.TargetAmount.Add(t.TargetAmount)
				ls.AchievedAmount = ls.AchievedAmount.Add(t.AchievedAmount)
				ls.TargetOrders += t.TargetOrders
				ls.AchievedOrders += t.AchievedOrders
				ls.TargetVisits += t.TargetVisits
				ls.AchievedVisits += t.AchievedVisits
			}
		}

		if ls.TargetAmount.Sign() > 0 {
			pct := ls.AchievedAmount.Div(ls.TargetAmount).Mul(decimal.NewFromInt(100)).Round(1)
			f, _ := pct.Float64()
			ls.AchievementPct = f
		}
		summaries = append(summaries, ls)
	}

	return summaries, nil
}