// NotificationProvider is the live-notifications closure formerly inline in
// cmd/server/main.go: pending sales orders, overdue invoices, low-stock
// alerts. Extracted so integration tests exercise the same queries.
package system

import (
	"context"
	"fmt"
	"strings"
	"time"

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

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

// NotificationProvider returns the production notification closure over pool.
func NotificationProvider(pool *pgxpool.Pool) func(ctx context.Context, u *auth.User) ([]NotificationItem, error) {
	return func(ctx context.Context, u *auth.User) ([]NotificationItem, error) {
		var notifs []NotificationItem
		canSeeSales := u == nil || u.Can("sales.sales-orders.view")
		canSeeInvoices := u == nil || u.Can("fakturisasi.invoices.view")
		canSeeStock := u == nil || u.Can("inventory.stock.view")

		// 1. Pending Sales Orders (Order Masuk / Butuh Persetujuan)
		if canSeeSales {
			rows, err := pool.Query(ctx,
				`SELECT id, so_number, status, total_amount, is_urgent, created_at
				 FROM sales_orders
				 WHERE status IN ('pending_approval', 'blocked_overdue')
				 ORDER BY created_at DESC LIMIT 5`)
			if err != nil {
				return nil, err
			}
			defer rows.Close()
			for rows.Next() {
				var soID int64
				var soNum, soStatus string
				var total decimal.Decimal
				var isUrgent bool
				var createdAt time.Time
				if err := rows.Scan(&soID, &soNum, &soStatus, &total, &isUrgent, &createdAt); err != nil {
					return nil, err
				}
				isBlocked := soStatus == "blocked_overdue"
				title := "Order Masuk: " + soNum
				if isBlocked {
					title = "Persetujuan Khusus: " + soNum
				}
				notifType := "order"
				if isUrgent {
					notifType = "urgent"
				}
				notifs = append(notifs, NotificationItem{
					ID:        fmt.Sprintf("so-%d", soID),
					Title:     title,
					Message:   fmt.Sprintf("Total Rp %s menunggu verifikasi atasan.", total.StringFixed(0)),
					Type:      notifType,
					IsUrgent:  isUrgent,
					Link:      "/sales/orders",
					Time:      createdAt.Format("15:04"),
					Read:      false,
					CreatedAt: createdAt,
				})
			}
			if err := rows.Err(); err != nil {
				return nil, err
			}
		}

		// 2. Overdue Invoices (Piutang Jatuh Tempo - Reminder Finansial, bukan sirine darurat)
		if canSeeInvoices {
			invRows, err := pool.Query(ctx,
				`SELECT id, invoice_number, remaining_amount, due_date, created_at
				 FROM invoices
				 WHERE status IN ('unpaid', 'partial') AND due_date < CURRENT_DATE
				 ORDER BY due_date ASC LIMIT 5`)
			if err != nil {
				return nil, err
			}
			defer invRows.Close()
			for invRows.Next() {
				var invID int64
				var invNum string
				var remaining decimal.Decimal
				var dueDate, createdAt time.Time
				if err := invRows.Scan(&invID, &invNum, &remaining, &dueDate, &createdAt); err != nil {
					return nil, err
				}
				notifs = append(notifs, NotificationItem{
					ID:        fmt.Sprintf("inv-%d", invID),
					Title:     "Invoice Overdue: " + invNum,
					Message:   fmt.Sprintf("Sisa piutang Rp %s melewati tempo (%s).", remaining.StringFixed(0), dueDate.Format("02/01/2006")),
					Type:      "payment",
					IsUrgent:  false,
					Link:      "/sales/receivables",
					Time:      dueDate.Format("02/01"),
					Read:      false,
					CreatedAt: createdAt,
				})
			}
			if err := invRows.Err(); err != nil {
				return nil, err
			}
		}

		// 3. Low stock alert (real schema: products.min_stock vs
		// summed stocks.quantity; there is no product_warehouses table).
		if canSeeStock {
			stockRows, err := pool.Query(ctx,
				`SELECT p.id, p.name, COALESCE(s.qty, 0), p.min_stock, p.updated_at
				 FROM products p
				 LEFT JOIN (
				   SELECT product_id, SUM(quantity) AS qty
				   FROM stocks GROUP BY product_id
				 ) s ON s.product_id = p.id
				 WHERE p.deleted_at IS NULL
				   AND p.min_stock > 0
				   AND COALESCE(s.qty, 0) <= p.min_stock
				 ORDER BY p.name ASC
				 LIMIT 5`)
			if err != nil {
				return nil, err
			}
			defer stockRows.Close()
			for stockRows.Next() {
				var pid int64
				var name string
				var stock, minStock decimal.Decimal
				var pUpdatedAt time.Time
				if err := stockRows.Scan(&pid, &name, &stock, &minStock, &pUpdatedAt); err != nil {
					return nil, err
				}
				notifs = append(notifs, NotificationItem{
					ID:        fmt.Sprintf("stock-%d", pid),
					Title:     "Stok Menipis: " + name,
					Message:   fmt.Sprintf("Sisa %s (min %s). Perlu restock gudang.", stock.StringFixed(0), minStock.StringFixed(0)),
					Type:      "stock",
					IsUrgent:  false,
					Link:      "/inventory/stock",
					Time:      "Hari ini",
					Read:      false,
					CreatedAt: pUpdatedAt,
				})
			}
			if err := stockRows.Err(); err != nil {
				return nil, err
			}
		}

		// 4. Open Support Tickets (Kendala Operasional / Kasir)
		// Tiket dengan prioritas 'critical' atau 'high' memicu status IsUrgent = true
		// agar atasan/SPV/admin langsung mendapatkan notifikasi dan sirine darurat.
		canSeeTickets := u == nil || u.Can("support.tickets.view") || u.Can("support.tickets.manage")
		if canSeeTickets {
			tktRows, err := pool.Query(ctx,
				`SELECT id, ticket_number, title, priority, reporter_name, created_at
				 FROM tickets
				 WHERE status IN ('open', 'triaged', 'in_progress')
				 ORDER BY CASE WHEN priority = 'critical' THEN 1 WHEN priority = 'high' THEN 2 ELSE 3 END, created_at DESC
				 LIMIT 5`)
			if err == nil {
				defer tktRows.Close()
				for tktRows.Next() {
					var tID int64
					var tNum, tTitle, tPriority, tReporter string
					var tCreatedAt time.Time
					if err := tktRows.Scan(&tID, &tNum, &tTitle, &tPriority, &tReporter, &tCreatedAt); err == nil {
						isUrgent := tPriority == "critical" || tPriority == "high"
						notifType := "ticket"
						if isUrgent {
							notifType = "urgent"
						}
						notifs = append(notifs, NotificationItem{
							ID:        fmt.Sprintf("tkt-%d", tID),
							Title:     fmt.Sprintf("Kendala Kasir/IT: %s", tTitle),
							Message:   fmt.Sprintf("[%s] %s dilaporkan oleh %s.", strings.ToUpper(tPriority), tNum, tReporter),
							Type:      notifType,
							IsUrgent:  isUrgent,
							Link:      fmt.Sprintf("/support/tickets/%d", tID),
							Time:      tCreatedAt.Format("15:04"),
							Read:      false,
							CreatedAt: tCreatedAt,
						})
					}
				}
			}
		}

		return notifs, nil
	}
}