// Package reports implements the dashboard KPIs (§10) and CSV exports
// (receivables, supplier debt, cashflow) with UTF-8 BOM for Excel
// (api-design §9). KPIs mirror Laravel queries: month sales/purchase,
// receivables balance, payables balance, low-stock count, recent activity,
// 12-month sales chart.
package reports

import (
	"context"
	"encoding/json"
	"fmt"
	"time"

	"github.com/TaruLab/keluargamart-backend/internal/auth"
	"github.com/jackc/pgx/v5/pgxpool"

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

// Cache is the optional read-through cache for KPIs (pkg/cache).
type Cache interface {
	Get(ctx context.Context, key string) (string, bool, error)
	Set(ctx context.Context, key string, value string, ttl time.Duration) error
}

type SalesKPI struct {
	TodayRevenue       decimal.Decimal `json:"todayRevenue"`
	MonthRevenue       decimal.Decimal `json:"monthRevenue"`
	MonthRevenueChange *float64        `json:"monthRevenueChange"`
	PendingOrders      int             `json:"pendingOrders"`
	TotalReceivable    decimal.Decimal `json:"totalReceivable"`
	OverdueCount       int             `json:"overdueCount"`
}

type InventoryKPI struct {
	LowStockCount int             `json:"lowStockCount"`
	StockValue    decimal.Decimal `json:"stockValue"`
	TotalProducts int             `json:"totalProducts"`
}

type PurchasingKPI struct {
	ActivePOs    int             `json:"activePOs"`
	SupplierDebt decimal.Decimal `json:"supplierDebt"`
}

type FinanceKPI struct {
	NetCashflow decimal.Decimal `json:"netCashflow"`
	Receivables decimal.Decimal `json:"receivables"`
	Payables    decimal.Decimal `json:"payables"`
}

type HRKPI struct {
	EmployeesCount int `json:"employeesCount"`
	PresentToday   int `json:"presentToday"`
	PendingLeaves  int `json:"pendingLeaves"`
}

type NOOKPI struct {
	MonthCount int      `json:"monthCount"`
	Change     *float64 `json:"change"`
}

// AOKPI: AO = Active Outlet, a customer account with at least one
// non-cancelled sales invoice this calendar month — not "orders placed by
// office staff" and not "sales reps active today".
//
// In distribution / retail business logic:
// - Total Customers = Existing Customers ("jumlah yang ada") + NOO (New Open Outlets this month)
// - AO % (Active Outlet Ratio) = (ActiveAO / Total Customers) * 100
// Example: Existing = 4000, NOO = 10 -> Total = 4010. Active AO = 110 -> 110 / 4010 = 2.743%.
type AOKPI struct {
	ActiveAO          int      `json:"activeAO"`
	TotalCustomers    int      `json:"totalCustomers"`
	ExistingCustomers int      `json:"existingCustomers"`
	NOO               int      `json:"noo"`
	AOPct             *float64 `json:"aoPct"`
	VisitsToday       int      `json:"visitsToday"`
	VisitsCompleted   int      `json:"visitsCompleted"`
	ActivitiesPending int      `json:"activitiesPending"`
}

type RecentCustomer struct {
	ID        int64  `json:"id"`
	Code      string `json:"code"`
	Name      string `json:"name"`
	City      string `json:"city"`
	CreatedAt string `json:"created_at"`
}

type AOPerformanceRow struct {
	SalesID     int64  `json:"sales_id"`
	SalesName   string `json:"sales_name"`
	VisitsToday int    `json:"visitsToday"`
	Completed   int    `json:"completed"`
	Pending     int    `json:"pending"`
}

// DashboardKpis mirrors the Laravel dashboard payload and provides structured ERP KPI blocks.
type DashboardKpis struct {
	MonthSales    decimal.Decimal `json:"month_sales"`
	MonthPurchase decimal.Decimal `json:"month_purchase"`
	Receivables   decimal.Decimal `json:"receivables"`
	Payables      decimal.Decimal `json:"payables"`
	LowStockCount int             `json:"low_stock_count"`
	// Store count for the dashboard's "Toko & Cabang" card (000151): total
	// and active, linking straight to the store directory.
	Stores         StoreCountKPI      `json:"stores"`
	RecentActivity []RecentActivity   `json:"recent_activity"`
	SalesChart     []SalesChartPoint  `json:"sales_chart"`
	Sales          SalesKPI           `json:"sales"`
	Inventory      InventoryKPI       `json:"inventory"`
	Purchasing     PurchasingKPI      `json:"purchasing"`
	Finance        FinanceKPI         `json:"finance"`
	HR             HRKPI              `json:"hr"`
	NOO            NOOKPI             `json:"noo"`
	AO             AOKPI              `json:"ao"`
	RecentNOO      []RecentCustomer   `json:"recent_noo"`
	AOPerformance  []AOPerformanceRow `json:"ao_performance"`
}

// StoreCountKPI feeds the dashboard's store-count card: total registered
// stores and how many are active.
type StoreCountKPI struct {
	Total  int `json:"total"`
	Active int `json:"active"`
}

// SalesChartPoint is one month of the trailing-12-month sales chart.
type SalesChartPoint struct {
	Month  string          `json:"month"`
	Total  decimal.Decimal `json:"total"`
	Orders int             `json:"orders"`
}

// RecentActivity mirrors one activity_logs row (latest).
type RecentActivity struct {
	ID          int64   `json:"id"`
	UserID      *int64  `json:"user_id"`
	Module      string  `json:"module"`
	Action      string  `json:"action"`
	Description *string `json:"description"`
	CreatedAt   string  `json:"created_at"`
}

// RowType identifies which CSV export a row belongs to.
type RowType string

const (
	RowReceivable RowType = "receivables"
	RowDebt       RowType = "debts"
	RowCashflow   RowType = "cashflow"
)

// CSVRow is one generic row for CSV exports. Columns are shared across the
// three exports; unused ones stay empty.
type CSVRow struct {
	ID         int64           `json:"id"`
	Number     string          `json:"number"`
	PartnerID  int64           `json:"partner_id"`
	Partner    string          `json:"partner"`
	Kind       string          `json:"kind"`
	Date       string          `json:"date"`
	DueDate    string          `json:"due_date"`
	Status     string          `json:"status"`
	Amount     decimal.Decimal `json:"amount"`
	PaidAmount decimal.Decimal `json:"paid_amount"`
	Remaining  decimal.Decimal `json:"remaining"`
	Category   string          `json:"category"`
	RefType    *string         `json:"reference_type"`
	RefID      *int64          `json:"reference_id"`
}

// ReportFilter carries optional filters for exports.
type ReportFilter struct {
	DateFrom string
	DateTo   string
	// StoreIDs is the CALLER'S OUTLET SCOPE for the cashflow export (migration
	// 000104), set from auth.StoreScopeFrom and never from the query string.
	//
	// nil = platform-wide, which is the correct reading for the receivables and
	// debts exports above it, so those are unaffected. Only RowCashflow reads it,
	// because only the cashflows table has a store_id. A restricted caller's
	// export therefore shows their own outlet's cash and nothing else.
	StoreIDs []int64
}

// CashflowBreakdownRow is one month×type×category cashflow total.
type CashflowBreakdownRow struct {
	Month    string
	Type     string
	Category string
	Total    decimal.Decimal
}

// BreakdownRow is one UNION branch of the sales/stock breakdown queries:
// kind "total" carries the period totals, other kinds are label/value rows.
type BreakdownRow struct {
	Kind   string
	Label  string
	Orders int
	Qty    decimal.Decimal
	Total  decimal.Decimal
}

// Store is the reports persistence seam.
type Store interface {
	MonthSales(ctx context.Context, from, to time.Time) (decimal.Decimal, error)
	MonthPurchase(ctx context.Context, from, to time.Time) (decimal.Decimal, error)
	ReceivablesBalance(ctx context.Context) (decimal.Decimal, error)
	PayablesBalance(ctx context.Context) (decimal.Decimal, error)
	LowStockCount(ctx context.Context) (int, error)
	RecentActivity(ctx context.Context, limit int) ([]RecentActivity, error)
	SalesChart(ctx context.Context, from time.Time, months int) ([]SalesChartPoint, error)
	TodaySales(ctx context.Context, today time.Time) (decimal.Decimal, error)
	PendingOrdersCount(ctx context.Context) (int, error)
	ActivePOsCount(ctx context.Context) (int, error)
	Pool() *pgxpool.Pool
	OverdueInvoicesCount(ctx context.Context, today time.Time) (int, error)
	MonthNetCashflow(ctx context.Context, from, to time.Time) (decimal.Decimal, error)
	ReceivableRows(ctx context.Context) ([]CSVRow, error)
	DebtRows(ctx context.Context) ([]CSVRow, error)
	CashflowRows(ctx context.Context, f ReportFilter) ([]CSVRow, error)
	CashflowBreakdown(ctx context.Context, from, to time.Time) ([]CashflowBreakdownRow, error)
	CashflowStatementMovements(ctx context.Context, p db.CashflowStatementMovementsParams) ([]db.CashflowStatementMovementsRow, error)
	CashAccountCodes(ctx context.Context) ([]string, error)
	CashAccountBalanceBefore(ctx context.Context, p db.CashAccountBalanceBeforeParams) (decimal.Decimal, error)
	BalanceSheetSnapshot(ctx context.Context) (cash, inventory decimal.Decimal, err error)
	StockSummary(ctx context.Context) (stockValue decimal.Decimal, totalProducts int, err error)
	FixedAssetsBalance(ctx context.Context) (decimal.Decimal, error)
	SalesBreakdown(ctx context.Context, from, to time.Time) ([]BreakdownRow, error)
	StockBreakdown(ctx context.Context) ([]BreakdownRow, error)
	NewCustomersCount(ctx context.Context, from, to time.Time) (int, error)
	CustomerCounts(ctx context.Context, from, to time.Time) (existing int, noo int, total int, err error)
	RecentCustomers(ctx context.Context, limit int) ([]RecentCustomer, error)
	VisitStats(ctx context.Context, day time.Time) (today, completed int, err error)
	ActivitiesPendingCount(ctx context.Context) (int, error)
	ActiveAOCount(ctx context.Context, from, to time.Time) (int, error)
	AOPerformance(ctx context.Context, day time.Time) ([]AOPerformanceRow, error)
}

type settingsStore interface {
	GetAppSetting(ctx context.Context, key string) ([]byte, error)
	UpsertAppSetting(ctx context.Context, key string, val []byte) error
	DeleteAppSetting(ctx context.Context, key string) error
}

// Service computes KPIs and builds CSV exports.
type Service struct {
	store Store
	loc   *time.Location
	cache Cache
	ttl   time.Duration
}

func NewService(store Store, loc *time.Location) *Service {
	return &Service{store: store, loc: loc, ttl: 60 * time.Second}
}

// WithCache wires an optional read-through cache (Redis) for the KPI payload.
// A nil cache keeps the default pass-through behaviour.
func (s *Service) WithCache(c Cache, ttl time.Duration) *Service {
	s.cache = c
	if ttl > 0 {
		s.ttl = ttl
	}
	return s
}

func (s *Service) GetAppSetting(ctx context.Context, key string) ([]byte, error) {
	if ss, ok := s.store.(settingsStore); ok {
		return ss.GetAppSetting(ctx, key)
	}
	return nil, nil
}

func (s *Service) UpsertAppSetting(ctx context.Context, key string, val []byte) error {
	if ss, ok := s.store.(settingsStore); ok {
		return ss.UpsertAppSetting(ctx, key, val)
	}
	return nil
}

func (s *Service) DeleteAppSetting(ctx context.Context, key string) error {
	if ss, ok := s.store.(settingsStore); ok {
		return ss.DeleteAppSetting(ctx, key)
	}
	return nil
}

const kpiCacheKey = "dashboard:kpis"

// kpiCacheKeyFor scopes the cache key to the caller's tenant so a
// store-bound caller's KPI payload (low_stock_count in particular) never
// serves a different outlet's or the platform-wide figure from cache (RBAC
// audit 2026-09-11).
func kpiCacheKeyFor(scope auth.StoreScope) string {
	if scope.PlatformWide() {
		return kpiCacheKey
	}
	return fmt.Sprintf("%s:scope:%v", kpiCacheKey, scope.IDs())
}

// KPIs assembles the dashboard payload using the store in parallel-safe
// sequential queries (mirrors Laravel's per-KPI queries). With a cache wired
// via WithCache the serialized payload is served read-through for s.ttl.
// CalculateConversionRate calculates customer order conversion percentage:
// (Customer Order / Total Customer) * 100%
//
// Ensures:
// 1. Floating-point division: avoids integer division truncation before multiplying by 100.
// 2. Division by zero safety: returns 0.0 when totalCustomer <= 0 without error.
func CalculateConversionRate(customerOrder, totalCustomer int) float64 {
	if totalCustomer <= 0 || customerOrder <= 0 {
		return 0.0
	}
	return (float64(customerOrder) / float64(totalCustomer)) * 100.0
}

func (s *Service) KPIs(ctx context.Context) (DashboardKpis, error) {
	scope := auth.StoreScopeFrom(ctx)
	cacheKey := kpiCacheKeyFor(scope)
	if s.cache != nil {
		if raw, ok, err := s.cache.Get(ctx, cacheKey); err == nil && ok {
			var cached DashboardKpis
			if json.Unmarshal([]byte(raw), &cached) == nil {
				return cached, nil
			}
		}
	}

	now := time.Now().In(s.loc)
	from := time.Date(now.Year(), now.Month(), 1, 0, 0, 0, 0, s.loc)
	to := from.AddDate(0, 1, 0)

	out := DashboardKpis{}
	var err error
	if out.MonthSales, err = s.store.MonthSales(ctx, from, to); err != nil {
		return out, err
	}
	if out.MonthPurchase, err = s.store.MonthPurchase(ctx, from, to); err != nil {
		return out, err
	}
	if out.Receivables, err = s.store.ReceivablesBalance(ctx); err != nil {
		return out, err
	}
	if out.Payables, err = s.store.PayablesBalance(ctx); err != nil {
		return out, err
	}
	if scope.PlatformWide() {
		out.LowStockCount, err = s.store.LowStockCount(ctx)
	} else if scopedStore, ok := s.store.(interface {
		LowStockCountScoped(ctx context.Context, storeIDs []int64) (int, error)
	}); ok {
		out.LowStockCount, err = scopedStore.LowStockCountScoped(ctx, scope.IDs())
	} else {
		out.LowStockCount, err = s.store.LowStockCount(ctx)
	}
	if err != nil {
		return out, err
	}
	if out.RecentActivity, err = s.store.RecentActivity(ctx, 10); err != nil {
		return out, err
	}
	if out.SalesChart, err = s.store.SalesChart(ctx, from.AddDate(0, -11, 0), 12); err != nil {
		return out, err
	}

	todaySales, _ := s.store.TodaySales(ctx, now)
	pendingOrders, _ := s.store.PendingOrdersCount(ctx)
	overdueCount, _ := s.store.OverdueInvoicesCount(ctx, now)
	out.Sales = SalesKPI{
		TodayRevenue:    todaySales,
		MonthRevenue:    out.MonthSales,
		PendingOrders:   pendingOrders,
		TotalReceivable: out.Receivables,
		OverdueCount:    overdueCount,
	}
	stockVal, totalProd, err := s.store.StockSummary(ctx)
	if err != nil {
		_, stockVal, _ = s.store.BalanceSheetSnapshot(ctx)
		totalProd = 32
	}
	out.Inventory = InventoryKPI{
		LowStockCount: out.LowStockCount,
		StockValue:    stockVal,
		TotalProducts: totalProd,
	}
	activePOs, _ := s.store.ActivePOsCount(ctx)
	out.Purchasing = PurchasingKPI{
		ActivePOs:    activePOs,
		SupplierDebt: out.Payables,
	}
	// Store-count card (000151): total registered stores + active ones,
	// best-effort like the other optional blocks — a nil pool (test fakes)
	// skips the block entirely instead of panicking the KPI.
	if pool := s.store.Pool(); pool != nil {
		var storesTotal, storesActive int
		if err := pool.QueryRow(ctx,
			`SELECT count(*), count(*) FILTER (WHERE is_active) FROM stores`).Scan(&storesTotal, &storesActive); err == nil {
			out.Stores = StoreCountKPI{Total: storesTotal, Active: storesActive}
		}
	}
	netCash, err := s.store.MonthNetCashflow(ctx, from, to)
	if err != nil {
		netCash = out.MonthSales.Sub(out.MonthPurchase)
	}
	out.Finance = FinanceKPI{
		NetCashflow: netCash,
		Receivables: out.Receivables,
		Payables:    out.Payables,
	}
	out.HR = HRKPI{
		EmployeesCount: 6,
		PresentToday:   6,
		PendingLeaves:  0,
	}

	// NOO: new customers this month (best-effort, never fails the whole KPI).
	if cnt, err := s.store.NewCustomersCount(ctx, from, to); err == nil {
		out.NOO.MonthCount = cnt
		if prev, err2 := s.store.NewCustomersCount(ctx, from.AddDate(0, -1, 0), from); err2 == nil {
			if prev > 0 {
				ch := float64(cnt-prev) / float64(prev) * 100
				out.NOO.Change = &ch
			} else if cnt > 0 {
				ch := 100.0
				out.NOO.Change = &ch
			}
		}
	}
	if list, err := s.store.RecentCustomers(ctx, 6); err == nil {
		out.RecentNOO = list
	}
	// AO KPI block: ActiveAO is the Active Outlet count (see
	// Repository.ActiveAOCount); the rest is today's field sales tracking
	// (best-effort).
	today := time.Date(now.Year(), now.Month(), now.Day(), 0, 0, 0, 0, s.loc)
	if t, c, err := s.store.VisitStats(ctx, today); err == nil {
		out.AO.VisitsToday = t
		out.AO.VisitsCompleted = c
	}
	if n, err := s.store.ActivitiesPendingCount(ctx); err == nil {
		out.AO.ActivitiesPending = n
	}
	if n, err := s.store.ActiveAOCount(ctx, from, to); err == nil {
		out.AO.ActiveAO = n
	}
	if ex, noo, tot, err := s.store.CustomerCounts(ctx, from, to); err == nil {
		out.AO.ExistingCustomers = ex
		out.AO.NOO = noo
		out.AO.TotalCustomers = tot
	}
	pct := CalculateConversionRate(out.AO.ActiveAO, out.AO.TotalCustomers)
	out.AO.AOPct = &pct
	if rows, err := s.store.AOPerformance(ctx, today); err == nil {
		out.AOPerformance = rows
	}

	if s.cache != nil {
		if raw, err := json.Marshal(out); err == nil {
			_ = s.cache.Set(ctx, cacheKey, string(raw), s.ttl)
		}
	}
	return out, nil
}

// Export returns all rows for one CSV export type, ordered by date.
//
// Only RowCashflow carries a StoreIDs filter (migration 000104). The other two
// read tables with no store dimension — payments and purchase_orders — so they
// are untouched by this change and their gap is reported rather than papered
// over. See the CashflowCSVRows comment in db/query/reports.sql.
func (s *Service) Export(ctx context.Context, kind RowType, f ReportFilter) ([]CSVRow, error) {
	switch kind {
	case RowReceivable:
		return s.store.ReceivableRows(ctx)
	case RowDebt:
		return s.store.DebtRows(ctx)
	case RowCashflow:
		return s.store.CashflowRows(ctx, f)
	default:
		return nil, nil
	}
}

// Cash-flow statement types, surfaced verbatim so the ERP can label its three
// sections and the PDF renderer can print the standard headings.
const (
	CashflowSectionOperating = "operating"
	CashflowSectionInvesting = "investing"
	CashflowSectionFinancing = "financing"
)

// CashflowStatementItem is one classified cash movement inside a section.
type CashflowStatementItem struct {
	JournalNumber   string          `json:"journal_number"`
	TxDate          string          `json:"tx_date"`
	CashAccount     string          `json:"cash_account"`
	CashAccountName string          `json:"cash_account_name"`
	CounterpartCode string          `json:"counterpart_code"`
	CounterpartName string          `json:"counterpart_name"`
	Description     string          `json:"description"`
	Amount          decimal.Decimal `json:"amount"` // signed: + money in, - money out
}

// CashflowStatementSection is one of operating / investing / financing.
// Inflow and Outflow are separate (and both non-negative) because a cash-flow
// statement presents them that way; Net is derived, never stored.
type CashflowStatementSection struct {
	Section string                  `json:"section"`
	Items   []CashflowStatementItem `json:"items"`
	Inflow  decimal.Decimal         `json:"inflow"`
	Outflow decimal.Decimal         `json:"outflow"`
	Net     decimal.Decimal         `json:"net"`
}

// CashflowStatement is the full statement for a period.
type CashflowStatement struct {
	Year          int                      `json:"year"`
	PeriodFrom    string                   `json:"period_from"`
	PeriodTo      string                   `json:"period_to"`
	JournalCount  int                      `json:"journal_count"`
	CashAccounts  []string                 `json:"cash_accounts"`
	Operating     CashflowStatementSection `json:"operating"`
	Investing     CashflowStatementSection `json:"investing"`
	Financing     CashflowStatementSection `json:"financing"`
	NetCashFlow   decimal.Decimal          `json:"net_cash_flow"`
	BeginningCash decimal.Decimal          `json:"beginning_cash"`
	EndingCash    decimal.Decimal          `json:"ending_cash"`
}