package services

import (
	"context"
	"encoding/json"
	"os"
	"testing"
	"time"

	"github.com/google/uuid"
	"github.com/jmoiron/sqlx"
	_ "github.com/lib/pq"

	"mgit.msbls.de/m/paliad/internal/db"
	"mgit.msbls.de/m/paliad/internal/models"
)

// TestReminderEnabled covers the JSON preference parsing for the digest
// path. The master gate ("deadline_reminders") and the per-category keys
// ("deadline_reminders.overdue" etc.) compose; either being explicitly
// false suppresses the relevant section.
func TestReminderEnabled(t *testing.T) {
	tests := []struct {
		name string
		raw  string
		key  string
		want bool
	}{
		// Empty / missing preferences → opt-in default.
		{"nil bytes default-on", "", "deadline_reminders.overdue", true},
		{"empty object default-on", "{}", "deadline_reminders.overdue", true},
		{"unrelated keys default-on", `{"theme":"dark"}`, "deadline_reminders.due_today", true},

		// Master switch.
		{"master off blocks overdue", `{"deadline_reminders":false}`, "deadline_reminders.overdue", false},
		{"master off blocks digest", `{"deadline_reminders":false}`, "deadline_reminders", false},
		{"master on allows overdue", `{"deadline_reminders":true}`, "deadline_reminders.overdue", true},

		// Per-category override.
		{"overdue explicitly off", `{"deadline_reminders.overdue":false}`, "deadline_reminders.overdue", false},
		{"due_warning explicitly off", `{"deadline_reminders.due_warning":false}`, "deadline_reminders.due_warning", false},
		{"due_today off doesn't block due_warning", `{"deadline_reminders.due_today":false}`, "deadline_reminders.due_warning", true},

		// Corrupt JSON must not silence reminders.
		{"corrupt json falls back on", `{not json`, "deadline_reminders.overdue", true},

		// Non-bool values are ignored (treated as absent → default on).
		{"non-bool master ignored", `{"deadline_reminders":"yes"}`, "deadline_reminders.overdue", true},
	}

	for _, tc := range tests {
		t.Run(tc.name, func(t *testing.T) {
			var raw json.RawMessage
			if tc.raw != "" {
				raw = json.RawMessage(tc.raw)
			}
			got := reminderEnabled(raw, tc.key)
			if got != tc.want {
				t.Errorf("reminderEnabled(%q, %q) = %v, want %v", tc.raw, tc.key, got, tc.want)
			}
		})
	}
}

// TestInSlot covers the per-user time-of-day gate. The hourly ticker scans
// every user every hour; inSlot is what keeps it from firing outside the
// user's chosen morning/evening hour.
func TestInSlot(t *testing.T) {
	// 2026-04-27 09:30 Europe/Berlin == 07:30 UTC.
	utc0930Berlin := time.Date(2026, 4, 27, 7, 30, 0, 0, time.UTC)
	utc1030Berlin := time.Date(2026, 4, 27, 8, 30, 0, 0, time.UTC)
	utc1605Berlin := time.Date(2026, 4, 27, 14, 5, 0, 0, time.UTC)

	tests := []struct {
		name    string
		now     time.Time
		tz      string
		morning string
		evening string
		slot    string
		want    bool
	}{
		{"morning slot matches", utc0930Berlin, "Europe/Berlin", "09:00:00", "16:00:00", "morning", true},
		{"morning slot misses one hour later", utc1030Berlin, "Europe/Berlin", "09:00:00", "16:00:00", "morning", false},
		{"evening slot matches at 16:05", utc1605Berlin, "Europe/Berlin", "09:00:00", "16:00:00", "evening", true},
		{"evening slot doesn't fire in morning", utc0930Berlin, "Europe/Berlin", "09:00:00", "16:00:00", "evening", false},
		{"morning custom 11:00", time.Date(2026, 4, 27, 9, 30, 0, 0, time.UTC), "Europe/Berlin", "11:00", "16:00", "morning", true},

		// t-paliad-064: bad / empty tz now skips the user (no silent UTC
		// fallback). The previous behaviour masked the alpine-tzdata bug.
		{"unknown tz skips user", time.Date(2026, 4, 27, 9, 0, 0, 0, time.UTC), "Mars/Olympus", "09:00", "16:00", "morning", false},
		{"empty tz skips user", time.Date(2026, 4, 27, 7, 0, 0, 0, time.UTC), "", "09:00", "16:00", "morning", false},

		// Empty HH:MM still falls back to the column default — different
		// failure mode (input string), still worth recovering.
		{"empty morning string falls back to default 09:00", time.Date(2026, 4, 27, 7, 0, 0, 0, time.UTC), "Europe/Berlin", "", "", "morning", true},
	}
	for _, tc := range tests {
		t.Run(tc.name, func(t *testing.T) {
			got := inSlot(tc.now, tc.tz, tc.morning, tc.evening, tc.slot)
			if got != tc.want {
				t.Errorf("inSlot(now=%s, tz=%q, m=%q, e=%q, slot=%q) = %v, want %v",
					tc.now.Format(time.RFC3339), tc.tz, tc.morning, tc.evening, tc.slot, got, tc.want)
			}
		})
	}
}

// TestCategorize locks the four-category boundary logic from the design
// doc: overdue is strictly past today, due_today is exactly today,
// due_warning fires only on the +offset day, everything else is "" (skip).
func TestCategorize(t *testing.T) {
	loc, err := time.LoadLocation("Europe/Berlin")
	if err != nil {
		t.Fatalf("LoadLocation Europe/Berlin: %v", err)
	}
	today := time.Date(2026, 4, 28, 0, 0, 0, 0, loc)
	mkDate := func(y, m, d int) time.Time {
		return time.Date(y, time.Month(m), d, 0, 0, 0, 0, loc)
	}

	cases := []struct {
		name    string
		dueDate time.Time
		offset  int
		want    string
	}{
		{"yesterday is overdue", mkDate(2026, 4, 27), 7, "overdue"},
		{"month ago is overdue", mkDate(2026, 3, 28), 7, "overdue"},
		{"today is due_today", today, 7, "due_today"},
		{"tomorrow is uncategorized (not in scope)", mkDate(2026, 4, 29), 7, ""},
		{"today+6 is uncategorized", mkDate(2026, 5, 4), 7, ""},
		{"today+7 is due_warning at default offset", mkDate(2026, 5, 5), 7, "due_warning"},
		{"today+14 is uncategorized at default offset", mkDate(2026, 5, 12), 7, ""},
		{"today+14 is due_warning at offset=14", mkDate(2026, 5, 12), 14, "due_warning"},
		{"today+1 is due_warning at offset=1", mkDate(2026, 4, 29), 1, "due_warning"},
	}
	for _, tc := range cases {
		t.Run(tc.name, func(t *testing.T) {
			got := categorize(tc.dueDate, today, tc.offset)
			if got != tc.want {
				t.Errorf("categorize(due=%s, offset=%d) = %q, want %q",
					tc.dueDate.Format("2006-01-02"), tc.offset, got, tc.want)
			}
		})
	}
}

// TestVisibleForCategory locks the recipient-rule table. Owner always
// sees their own deadlines; project leads get warnings + due_today but
// NOT overdue (overdue is a system-failure escalation past the team);
// the escalation channel — owner.escalation_contact_id when set, else
// global_admins — gets overdue everywhere and due_today on the evening
// slot only. When an escalation contact is set, global admins are
// suppressed from the channel (t-paliad-066).
func TestVisibleForCategory(t *testing.T) {
	type case_ struct {
		name        string
		category    string
		slot        string
		owner       bool
		lead        bool
		gAdm        bool
		escContact  bool
		hasOverride bool
		want        bool
	}
	cases := []case_{
		// Owner: gets every category in every slot. Escalation override
		// state doesn't change owner visibility.
		{"owner sees overdue morning", "overdue", "morning", true, false, false, false, false, true},
		{"owner sees overdue evening", "overdue", "evening", true, false, false, false, false, true},
		{"owner sees due_today morning", "due_today", "morning", true, false, false, false, false, true},
		{"owner sees due_today evening", "due_today", "evening", true, false, false, false, false, true},
		{"owner sees due_warning morning", "due_warning", "morning", true, false, false, false, false, true},

		// Lead: warnings + due_today, NOT overdue.
		{"lead sees due_warning", "due_warning", "morning", false, true, false, false, false, true},
		{"lead sees due_today morning", "due_today", "morning", false, true, false, false, false, true},
		{"lead sees due_today evening", "due_today", "evening", false, true, false, false, false, true},
		{"lead does NOT see overdue", "overdue", "morning", false, true, false, false, false, false},
		{"lead does NOT see overdue evening", "overdue", "evening", false, true, false, false, false, false},

		// Global admin (no override set on owner): overdue (any slot) +
		// due_today evening only — fallback escalation channel.
		{"global admin sees overdue", "overdue", "morning", false, false, true, false, false, true},
		{"global admin sees overdue evening", "overdue", "evening", false, false, true, false, false, true},
		{"global admin sees due_today evening", "due_today", "evening", false, false, true, false, false, true},
		{"global admin does NOT see due_today morning", "due_today", "morning", false, false, true, false, false, false},
		{"global admin does NOT see due_warning", "due_warning", "morning", false, false, true, false, false, false},

		// Global admin when owner has explicit escalation contact:
		// admin is OFF the channel (the override diverts away from admins).
		{"global admin suppressed for overdue when override set", "overdue", "morning", false, false, true, false, true, false},
		{"global admin suppressed for overdue evening when override set", "overdue", "evening", false, false, true, false, true, false},
		{"global admin suppressed for due_today evening when override set", "due_today", "evening", false, false, true, false, true, false},
		// And admins still don't see the non-escalation categories regardless.
		{"global admin still no due_today morning under override", "due_today", "morning", false, false, true, false, true, false},
		{"global admin still no due_warning under override", "due_warning", "morning", false, false, true, false, true, false},

		// Escalation contact (override set, U is the named contact): same
		// shape as global admin under fallback — overdue any slot +
		// due_today evening.
		{"escalation contact sees overdue morning", "overdue", "morning", false, false, false, true, true, true},
		{"escalation contact sees overdue evening", "overdue", "evening", false, false, false, true, true, true},
		{"escalation contact sees due_today evening", "due_today", "evening", false, false, false, true, true, true},
		{"escalation contact does NOT see due_today morning", "due_today", "morning", false, false, false, true, true, false},
		{"escalation contact does NOT see due_warning", "due_warning", "morning", false, false, false, true, true, false},

		// Stranger (none of the three): never.
		{"stranger never sees", "overdue", "evening", false, false, false, false, false, false},
		{"stranger never sees, override set", "overdue", "evening", false, false, false, false, true, false},
	}
	for _, tc := range cases {
		t.Run(tc.name, func(t *testing.T) {
			got := visibleForCategory(tc.category, tc.slot, tc.owner, tc.lead, tc.gAdm, tc.escContact, tc.hasOverride)
			if got != tc.want {
				t.Errorf("visibleForCategory(%s/%s, owner=%v lead=%v admin=%v esc=%v override=%v) = %v, want %v",
					tc.category, tc.slot, tc.owner, tc.lead, tc.gAdm, tc.escContact, tc.hasOverride, got, tc.want)
			}
		})
	}
}

// TestDigestSubjectTemplate locks the subject-line ladder. Overdue presence
// must promote the framing to ÜBERFÄLLIG / SYSTEMAUSFALL — the SLO is
// "no overdues, ever", so the inbox should reflect that. Drives the
// embedded subject template via MailService.RenderTemplate so a future
// admin who edits the template can't silently soften the framing without
// failing this test.
func TestDigestSubjectTemplate(t *testing.T) {
	cases := []struct {
		name                          string
		slot, lang                    string
		overdue, dueToday, dueWarning int
		wantContains                  []string
	}{
		{"DE morning quiet", "morning", "de", 0, 0, 1, []string{"Frist-Erinnerung", "1 offen"}},
		{"DE morning many", "morning", "de", 0, 2, 1, []string{"3 offen"}},
		{"DE morning overdue", "morning", "de", 1, 2, 0, []string{"ÜBERFÄLLIG", "1", "2 weitere"}},
		{"DE evening due_today", "evening", "de", 0, 2, 0, []string{"DRINGEND", "2 heute"}},
		{"DE evening overdue", "evening", "de", 1, 2, 0, []string{"SYSTEMAUSFALL", "1 überfällig"}},
		{"DE evening only overdue", "evening", "de", 1, 0, 0, []string{"1 überfällig"}},
		{"EN morning quiet", "morning", "en", 0, 1, 0, []string{"Deadline reminder", "1 open"}},
		{"EN evening overdue", "evening", "en", 1, 1, 0, []string{"SYSTEM FAILURE", "1 overdue"}},
		{"EN evening due_today", "evening", "en", 0, 1, 0, []string{"URGENT", "1 still open"}},
	}
	svc, err := NewMailService()
	if err != nil {
		t.Fatalf("NewMailService: %v", err)
	}
	for _, tc := range cases {
		t.Run(tc.name, func(t *testing.T) {
			subj, _, rerr := svc.RenderTemplate(TemplateData{
				Lang: tc.lang,
				Name: "deadline_digest",
				Data: map[string]any{
					"Slot":            tc.slot,
					"IsEvening":       tc.slot == "evening",
					"OverdueCount":    tc.overdue,
					"DueTodayCount":   tc.dueToday,
					"DueWarningCount": tc.dueWarning,
					"OpenTotal":       tc.dueToday + tc.dueWarning,
					// Body needs the slices to render the section tables, but
					// subject only reads the *Count fields. Pass empty slices
					// of the right length so html/template's range works.
					"Overdue":      placeholderRows(tc.overdue),
					"DueToday":     placeholderRows(tc.dueToday),
					"DueWarning":   placeholderRows(tc.dueWarning),
					"DeadlinesURL": "https://paliad.de/deadlines",
				},
			})
			if rerr != nil {
				t.Fatalf("RenderTemplate: %v", rerr)
			}
			for _, s := range tc.wantContains {
				if !contains(subj, s) {
					t.Errorf("subject = %q, want substring %q", subj, s)
				}
			}
		})
	}
}

func placeholderRows(n int) []map[string]any {
	out := make([]map[string]any, n)
	for i := range out {
		out[i] = map[string]any{}
	}
	return out
}

func contains(haystack, needle string) bool {
	return len(needle) == 0 || (len(haystack) >= len(needle) && indexOf(haystack, needle) >= 0)
}

func indexOf(s, sub string) int {
	for i := 0; i+len(sub) <= len(s); i++ {
		if s[i:i+len(sub)] == sub {
			return i
		}
	}
	return -1
}

// TestTZDataEmbedded locks down the t-paliad-064 tz fix: the production
// alpine container ships without /usr/share/zoneinfo, so any binary that
// expects time.LoadLocation("Europe/Berlin") to succeed at runtime must
// embed Go's tzdata. The `_ "time/tzdata"` import in reminder_service.go
// is what makes this pass; remove it and this test fails.
//
// The test deliberately covers a non-UTC IANA zone — UTC is hard-coded in
// the runtime and would pass even without the embed.
func TestTZDataEmbedded(t *testing.T) {
	zones := []string{"Europe/Berlin", "America/New_York", "Asia/Tokyo"}
	for _, z := range zones {
		if _, err := time.LoadLocation(z); err != nil {
			t.Errorf("time.LoadLocation(%q) failed: %v — is `_ \"time/tzdata\"` still imported?", z, err)
		}
	}
}

// TestNextTopOfHour locks the boundary-alignment math. The "fake clock"
// here is the time values fed to the pure function — we don't need a
// real goroutine + timer to verify scheduling, because the helper is what
// determines when the goroutine wakes up. Pre-fix the loop used
// time.NewTicker(time.Hour) directly off container start, which produced
// ticks at HH:MM:SS where MM/SS == container-start offset. Now every
// scheduled wake-up lands at HH:00:00, regardless of when the process
// booted (t-paliad-069).
func TestNextTopOfHour(t *testing.T) {
	cases := []struct {
		name string
		now  time.Time
		want time.Duration
	}{
		// Exactly on the hour → wait a full hour to the next boundary.
		{"on the hour", time.Date(2026, 4, 30, 13, 0, 0, 0, time.UTC), time.Hour},
		// Halfway through.
		{"halfway", time.Date(2026, 4, 30, 13, 30, 0, 0, time.UTC), 30 * time.Minute},
		// One second before HH:00 → 1 second to wait.
		{"one second to next hour", time.Date(2026, 4, 30, 13, 59, 59, 0, time.UTC), time.Second},
		// The exact bug signature from the task brief.
		{"container start signature 13:27:50", time.Date(2026, 4, 29, 13, 27, 50, 0, time.UTC), 32*time.Minute + 10*time.Second},
		// Sub-second precision.
		{"sub-second offset", time.Date(2026, 4, 30, 13, 0, 0, 250_000_000, time.UTC), time.Hour - 250*time.Millisecond},
		// Across UTC midnight.
		{"crosses midnight UTC", time.Date(2026, 4, 30, 23, 45, 0, 0, time.UTC), 15 * time.Minute},
	}
	for _, tc := range cases {
		t.Run(tc.name, func(t *testing.T) {
			got := nextTopOfHour(tc.now)
			if got != tc.want {
				t.Errorf("nextTopOfHour(%s) = %s, want %s", tc.now.Format(time.RFC3339Nano), got, tc.want)
			}
		})
	}
}

// TestNextTopOfHour_AlwaysLandsOnBoundary fuzzes the helper across an hour's
// worth of starting offsets and confirms now+delay always lands exactly on
// HH:00:00 (well within the 5-second tolerance the task brief allows).
// This is what makes ticks stable across redeploys.
func TestNextTopOfHour_AlwaysLandsOnBoundary(t *testing.T) {
	base := time.Date(2026, 4, 30, 13, 0, 0, 0, time.UTC)
	for sec := 0; sec < 3600; sec += 137 { // sample non-aligned offsets
		now := base.Add(time.Duration(sec) * time.Second)
		delay := nextTopOfHour(now)
		wakeup := now.Add(delay)
		if wakeup.Minute() != 0 || wakeup.Second() != 0 || wakeup.Nanosecond() != 0 {
			t.Errorf("from now=%s, wakeup=%s — not on HH:00:00",
				now.Format(time.RFC3339Nano), wakeup.Format(time.RFC3339Nano))
		}
	}
}

// TestNextTopOfHour_StableAfterRunOnce locks the second acceptance: after a
// RunOnce executes at exactly HH:00:00 and the loop re-enters, the next
// wake-up is at (HH+1):00:00 — not at HH:00:00 + (whatever delay we
// happened to compute on the previous iteration). Verifies the
// recompute-per-iteration design self-stabilises rather than drifting.
func TestNextTopOfHour_StableAfterRunOnce(t *testing.T) {
	// Container starts mid-hour at 13:27:50.
	start := time.Date(2026, 4, 30, 13, 27, 50, 0, time.UTC)
	delay1 := nextTopOfHour(start)
	if want := 32*time.Minute + 10*time.Second; delay1 != want {
		t.Fatalf("first delay = %s, want %s", delay1, want)
	}

	// First fire lands at 14:00:00 exactly. Simulate RunOnce taking ~zero
	// time; the next iteration computes its own delay from "now".
	afterFirstFire := start.Add(delay1)
	if want := time.Date(2026, 4, 30, 14, 0, 0, 0, time.UTC); !afterFirstFire.Equal(want) {
		t.Fatalf("first fire at %s, want %s", afterFirstFire, want)
	}
	delay2 := nextTopOfHour(afterFirstFire)
	if delay2 != time.Hour {
		t.Errorf("second delay = %s, want 1h (would have been %s in old broken design)", delay2, delay1)
	}
	// And the second fire lands at 15:00:00 — not at 14:32:10 (= 14:00 + delay1).
	afterSecondFire := afterFirstFire.Add(delay2)
	if want := time.Date(2026, 4, 30, 15, 0, 0, 0, time.UTC); !afterSecondFire.Equal(want) {
		t.Errorf("second fire at %s, want %s", afterSecondFire, want)
	}
}

// TestSlotPastDueToday locks the catch-up filter. Mirrors TestInSlot's
// table shape but with the looser `>=` predicate that the startup catch-up
// uses to redeliver slots a redeploy missed.
func TestSlotPastDueToday(t *testing.T) {
	// 2026-04-30 in Berlin is CEST (UTC+2).
	utc0830Berlin := time.Date(2026, 4, 30, 6, 30, 0, 0, time.UTC)  // 08:30 Berlin
	utc0930Berlin := time.Date(2026, 4, 30, 7, 30, 0, 0, time.UTC)  // 09:30 Berlin
	utc1150Berlin := time.Date(2026, 4, 30, 9, 50, 0, 0, time.UTC)  // 11:50 Berlin (the redeploy-after-09:00 case)
	utc1630Berlin := time.Date(2026, 4, 30, 14, 30, 0, 0, time.UTC) // 16:30 Berlin

	tests := []struct {
		name    string
		now     time.Time
		tz      string
		morning string
		evening string
		slot    string
		want    bool
	}{
		// Before morning slot: not past due.
		{"before morning slot, morning not due", utc0830Berlin, "Europe/Berlin", "09:00", "16:00", "morning", false},
		{"before morning slot, evening not due", utc0830Berlin, "Europe/Berlin", "09:00", "16:00", "evening", false},

		// Inside morning slot hour: past due (slot has begun).
		{"inside morning slot, morning past due", utc0930Berlin, "Europe/Berlin", "09:00", "16:00", "morning", true},
		{"inside morning slot, evening not yet due", utc0930Berlin, "Europe/Berlin", "09:00", "16:00", "evening", false},

		// After morning slot, before evening: morning past due, evening not. This is the
		// canonical bug from the task brief — redeploy at 11:50 Berlin after the
		// 09:00 slot was missed; catch-up must fire it.
		{"after morning, morning past due", utc1150Berlin, "Europe/Berlin", "09:00", "16:00", "morning", true},
		{"after morning, evening still not due", utc1150Berlin, "Europe/Berlin", "09:00", "16:00", "evening", false},

		// After evening slot: both past due (catch-up may fire either; dedup decides).
		{"after evening, morning still past due", utc1630Berlin, "Europe/Berlin", "09:00", "16:00", "morning", true},
		{"after evening, evening past due", utc1630Berlin, "Europe/Berlin", "09:00", "16:00", "evening", true},

		// Custom slot times.
		{"custom morning 11:00 not yet due at 10:30", time.Date(2026, 4, 30, 8, 30, 0, 0, time.UTC), "Europe/Berlin", "11:00", "16:00", "morning", false},
		{"custom morning 11:00 due at 11:30", time.Date(2026, 4, 30, 9, 30, 0, 0, time.UTC), "Europe/Berlin", "11:00", "16:00", "morning", true},

		// Bad / empty tz: skip user (mirror inSlot's defensive stance).
		{"unknown tz skips user", utc0930Berlin, "Mars/Olympus", "09:00", "16:00", "morning", false},
		{"empty tz skips user", utc0930Berlin, "", "09:00", "16:00", "morning", false},

		// Empty HH:MM still falls back to defaults (09:00 / 16:00).
		{"empty morning string falls back to default 09:00", utc0930Berlin, "Europe/Berlin", "", "", "morning", true},
		{"empty evening string falls back to default 16:00", utc1630Berlin, "Europe/Berlin", "", "", "evening", true},
	}
	for _, tc := range tests {
		t.Run(tc.name, func(t *testing.T) {
			got := slotPastDueToday(tc.now, tc.tz, tc.morning, tc.evening, tc.slot)
			if got != tc.want {
				t.Errorf("slotPastDueToday(now=%s, tz=%q, m=%q, e=%q, slot=%q) = %v, want %v",
					tc.now.Format(time.RFC3339), tc.tz, tc.morning, tc.evening, tc.slot, got, tc.want)
			}
		})
	}
}

// TestInSlot_BerlinAt0900_NotAt1100 is the headline regression test for the
// 11:16 prod surprise. With reminder_morning_time=09:00 and tz=Europe/Berlin,
// the slot must fire at 09:xx Berlin (07:xx UTC, the user's chosen time) and
// must not fire at 11:xx Berlin (09:xx UTC, the silent-UTC-fallback result).
//
// Pre-fix this test would either pass spuriously on a dev host with OS
// tzdata, or — without the embed — fail because LoadLocation errored and
// the fallback fired at the wrong wall-clock hour. With the fix in place
// it passes deterministically in any environment.
func TestInSlot_BerlinAt0900_NotAt1100(t *testing.T) {
	// 09:05 Berlin (CEST UTC+2) == 07:05 UTC.
	at0905Berlin := time.Date(2026, 4, 28, 7, 5, 0, 0, time.UTC)
	// 11:16 Berlin (CEST UTC+2) == 09:16 UTC. The exact bug signature.
	at1116Berlin := time.Date(2026, 4, 28, 9, 16, 0, 0, time.UTC)

	if !inSlot(at0905Berlin, "Europe/Berlin", "09:00:00", "16:00:00", "morning") {
		t.Error("inSlot at 09:05 Berlin / morning=09:00 should match — got false")
	}
	if inSlot(at1116Berlin, "Europe/Berlin", "09:00:00", "16:00:00", "morning") {
		t.Error("inSlot at 11:16 Berlin / morning=09:00 must not match — got true (UTC fallback bug)")
	}
}

// ----------------------------------------------------------------------------
// Live DB tests — skipped without TEST_DATABASE_URL.
// ----------------------------------------------------------------------------

// TestRunSlotForUser exercises the full digest path against a live DB.
// One pending deadline due today + one overdue → one bundled email at
// the morning slot, one log row, and a second tick is dedup'd. The
// evening slot then sees both rows again and writes a separate log row.
func TestRunSlotForUser(t *testing.T) {
	url := os.Getenv("TEST_DATABASE_URL")
	if url == "" {
		t.Skip("TEST_DATABASE_URL not set — skipping live DB test")
	}
	if err := db.ApplyMigrations(url); err != nil {
		t.Fatalf("apply migrations: %v", err)
	}
	pool, err := sqlx.Connect("postgres", url)
	if err != nil {
		t.Fatalf("connect: %v", err)
	}
	defer pool.Close()

	ctx := context.Background()
	userID := uuid.New()
	projectID := uuid.New()
	dlToday := uuid.New()
	dlOverdue := uuid.New()

	cleanup := func() {
		pool.ExecContext(ctx, `DELETE FROM paliad.reminder_log WHERE user_id = $1`, userID)
		pool.ExecContext(ctx, `DELETE FROM paliad.deadlines    WHERE id IN ($1, $2)`, dlToday, dlOverdue)
		pool.ExecContext(ctx, `DELETE FROM paliad.project_teams WHERE project_id = $1`, projectID)
		pool.ExecContext(ctx, `DELETE FROM paliad.projects     WHERE id      = $1`, projectID)
		pool.ExecContext(ctx, `DELETE FROM paliad.users        WHERE id      = $1`, userID)
		pool.ExecContext(ctx, `DELETE FROM auth.users          WHERE id      = $1`, userID)
	}
	cleanup()
	defer cleanup()

	if _, err := pool.ExecContext(ctx,
		`INSERT INTO auth.users (id, email) VALUES ($1, $2)`,
		userID, "digest-test@hlc.com"); err != nil {
		t.Fatalf("seed auth.users: %v", err)
	}
	if _, err := pool.ExecContext(ctx,
		`INSERT INTO paliad.users
		   (id, email, display_name, office, lang, email_preferences,
		    reminder_morning_time, reminder_evening_time, reminder_timezone,
		    reminder_warning_offset_days)
		 VALUES ($1, $2, 'Digest Test', 'munich', 'de', '{}'::jsonb,
		         '09:00:00', '16:00:00', 'Europe/Berlin', 7)`,
		userID, "digest-test@hlc.com"); err != nil {
		t.Fatalf("seed paliad.users: %v", err)
	}
	if _, err := pool.ExecContext(ctx,
		`INSERT INTO paliad.projects (id, type, path, title, reference, status, created_by)
		 VALUES ($1, 'project', $1::text, 'Digest Test', '2026/7777', 'active', $2)`,
		projectID, userID); err != nil {
		t.Fatalf("seed paliad.projects: %v", err)
	}

	// 2026-04-28 = Tuesday. due_today + overdue (yesterday).
	today := time.Date(2026, 4, 28, 0, 0, 0, 0, time.UTC)
	yesterday := today.AddDate(0, 0, -1)

	if _, err := pool.ExecContext(ctx,
		`INSERT INTO paliad.deadlines (id, project_id, title, due_date, source, status, created_by)
		 VALUES ($1, $2, 'Heute fällig', $3, 'manual', 'pending', $4)`,
		dlToday, projectID, today, userID); err != nil {
		t.Fatalf("seed today deadline: %v", err)
	}
	if _, err := pool.ExecContext(ctx,
		`INSERT INTO paliad.deadlines (id, project_id, title, due_date, source, status, created_by)
		 VALUES ($1, $2, 'Schon überfällig', $3, 'manual', 'pending', $4)`,
		dlOverdue, projectID, yesterday, userID); err != nil {
		t.Fatalf("seed overdue deadline: %v", err)
	}

	mail, err := NewMailService()
	if err != nil {
		t.Fatalf("NewMailService: %v", err)
	}
	users := NewUserService(pool)
	svc := NewReminderService(pool, mail, users, "https://paliad.test")

	countLog := func(slot string, slotDate time.Time) int {
		var n int
		if err := pool.GetContext(ctx, &n,
			`SELECT count(*) FROM paliad.reminder_log
			  WHERE user_id = $1 AND slot = $2 AND slot_date = $3`,
			userID, slot, slotDate); err != nil {
			t.Fatalf("count log: %v", err)
		}
		return n
	}

	// 09:30 Berlin (CEST) == 07:30 UTC. Morning slot.
	morning := time.Date(2026, 4, 28, 7, 30, 0, 0, time.UTC)
	svc.clock = func() time.Time { return morning }
	svc.RunOnce(ctx)

	if got := countLog("morning", today); got != 1 {
		t.Errorf("morning slot log rows = %d, want 1", got)
	}

	// Second tick at 09:31 Berlin → still morning slot, dedup'd.
	morning2 := morning.Add(1 * time.Minute)
	svc.clock = func() time.Time { return morning2 }
	svc.RunOnce(ctx)
	if got := countLog("morning", today); got != 1 {
		t.Errorf("morning slot log rows after dedup = %d, want 1 (dedup failed)", got)
	}

	// Move to evening slot — 16:30 Berlin == 14:30 UTC. New log row expected.
	evening := time.Date(2026, 4, 28, 14, 30, 0, 0, time.UTC)
	svc.clock = func() time.Time { return evening }
	svc.RunOnce(ctx)
	if got := countLog("evening", today); got != 1 {
		t.Errorf("evening slot log rows = %d, want 1", got)
	}
	if got := countLog("morning", today); got != 1 {
		t.Errorf("morning slot log rows after evening = %d, want still 1", got)
	}

	// Outside any slot — 02:28 Berlin == 00:28 UTC, the exact bug signature.
	svc.clock = func() time.Time {
		return time.Date(2026, 4, 28, 0, 28, 0, 0, time.UTC)
	}
	svc.RunOnce(ctx)
	// No new rows — still 1 morning + 1 evening.
	if got := countLog("morning", today); got != 1 {
		t.Errorf("morning rows after off-slot tick = %d, want still 1", got)
	}
	if got := countLog("evening", today); got != 1 {
		t.Errorf("evening rows after off-slot tick = %d, want still 1", got)
	}
}

// TestRunStartupCatchUp_RecoversMissedMorningSlot is the live-DB version of
// the third acceptance criterion in the task brief: a redeploy at 09:50
// (after the 09:00 slot's regular tick has been skipped) must still
// deliver the morning digest to anyone whose slot has not yet been logged
// for today.
//
// Pre-fix this would fail because RunOnce checks local.Hour() == slot_hour
// and a "now" of 11:50 Berlin is in hour 11, not 9 — the slot was lost
// for the day. With runStartupCatchUp the missed slot fires immediately,
// and the slot_date dedup ensures a second startup the same day is a
// no-op.
func TestRunStartupCatchUp_RecoversMissedMorningSlot(t *testing.T) {
	url := os.Getenv("TEST_DATABASE_URL")
	if url == "" {
		t.Skip("TEST_DATABASE_URL not set — skipping live DB test")
	}
	if err := db.ApplyMigrations(url); err != nil {
		t.Fatalf("apply migrations: %v", err)
	}
	pool, err := sqlx.Connect("postgres", url)
	if err != nil {
		t.Fatalf("connect: %v", err)
	}
	defer pool.Close()

	ctx := context.Background()
	userID := uuid.New()
	projectID := uuid.New()
	dlToday := uuid.New()

	cleanup := func() {
		pool.ExecContext(ctx, `DELETE FROM paliad.reminder_log WHERE user_id = $1`, userID)
		pool.ExecContext(ctx, `DELETE FROM paliad.deadlines    WHERE id      = $1`, dlToday)
		pool.ExecContext(ctx, `DELETE FROM paliad.project_teams WHERE project_id = $1`, projectID)
		pool.ExecContext(ctx, `DELETE FROM paliad.projects     WHERE id      = $1`, projectID)
		pool.ExecContext(ctx, `DELETE FROM paliad.users        WHERE id      = $1`, userID)
		pool.ExecContext(ctx, `DELETE FROM auth.users          WHERE id      = $1`, userID)
	}
	cleanup()
	defer cleanup()

	if _, err := pool.ExecContext(ctx,
		`INSERT INTO auth.users (id, email) VALUES ($1, $2)`,
		userID, "catchup-test@hlc.com"); err != nil {
		t.Fatalf("seed auth.users: %v", err)
	}
	if _, err := pool.ExecContext(ctx,
		`INSERT INTO paliad.users
		   (id, email, display_name, office, lang, email_preferences,
		    reminder_morning_time, reminder_evening_time, reminder_timezone,
		    reminder_warning_offset_days)
		 VALUES ($1, $2, 'Catch-Up Test', 'munich', 'de', '{}'::jsonb,
		         '09:00:00', '16:00:00', 'Europe/Berlin', 7)`,
		userID, "catchup-test@hlc.com"); err != nil {
		t.Fatalf("seed paliad.users: %v", err)
	}
	if _, err := pool.ExecContext(ctx,
		`INSERT INTO paliad.projects (id, type, path, title, reference, status, created_by)
		 VALUES ($1, 'project', $1::text, 'Catch-Up Test', '2026/9999', 'active', $2)`,
		projectID, userID); err != nil {
		t.Fatalf("seed paliad.projects: %v", err)
	}

	today := time.Date(2026, 4, 28, 0, 0, 0, 0, time.UTC)
	if _, err := pool.ExecContext(ctx,
		`INSERT INTO paliad.deadlines (id, project_id, title, due_date, source, status, created_by)
		 VALUES ($1, $2, 'Heute fällig', $3, 'manual', 'pending', $4)`,
		dlToday, projectID, today, userID); err != nil {
		t.Fatalf("seed today deadline: %v", err)
	}

	mail, err := NewMailService()
	if err != nil {
		t.Fatalf("NewMailService: %v", err)
	}
	users := NewUserService(pool)
	svc := NewReminderService(pool, mail, users, "https://paliad.test")

	countLog := func(slot string, slotDate time.Time) int {
		var n int
		if err := pool.GetContext(ctx, &n,
			`SELECT count(*) FROM paliad.reminder_log
			  WHERE user_id = $1 AND slot = $2 AND slot_date = $3`,
			userID, slot, slotDate); err != nil {
			t.Fatalf("count log: %v", err)
		}
		return n
	}

	// 11:50 Berlin (CEST) == 09:50 UTC — the exact "redeploy after the
	// morning slot" signature from the task brief. Pre-fix RunOnce would
	// see local.Hour()==11 and skip; runStartupCatchUp sees 11>=9 and fires.
	svc.clock = func() time.Time {
		return time.Date(2026, 4, 28, 9, 50, 0, 0, time.UTC)
	}
	svc.runStartupCatchUp(ctx)

	if got := countLog("morning", today); got != 1 {
		t.Errorf("after catch-up at 11:50 Berlin: morning rows = %d, want 1 (catch-up missed slot)", got)
	}
	// Evening slot is still in the future (16:00 > 11:50) — must not fire.
	if got := countLog("evening", today); got != 0 {
		t.Errorf("after catch-up at 11:50 Berlin: evening rows = %d, want 0 (slot not yet due)", got)
	}

	// Second startup-catch-up the same day must be a no-op (dedup).
	svc.clock = func() time.Time {
		return time.Date(2026, 4, 28, 12, 30, 0, 0, time.UTC)
	}
	svc.runStartupCatchUp(ctx)
	if got := countLog("morning", today); got != 1 {
		t.Errorf("second startup catch-up morning rows = %d, want still 1 (dedup failed)", got)
	}
}

// TestRunSlotForUser_EmptyDigest verifies the no-spam rule: a user with no
// matching deadlines in their slot gets no email and no log row.
func TestRunSlotForUser_EmptyDigest(t *testing.T) {
	url := os.Getenv("TEST_DATABASE_URL")
	if url == "" {
		t.Skip("TEST_DATABASE_URL not set — skipping live DB test")
	}
	if err := db.ApplyMigrations(url); err != nil {
		t.Fatalf("apply migrations: %v", err)
	}
	pool, err := sqlx.Connect("postgres", url)
	if err != nil {
		t.Fatalf("connect: %v", err)
	}
	defer pool.Close()

	ctx := context.Background()
	userID := uuid.New()

	cleanup := func() {
		pool.ExecContext(ctx, `DELETE FROM paliad.reminder_log WHERE user_id = $1`, userID)
		pool.ExecContext(ctx, `DELETE FROM paliad.users        WHERE id      = $1`, userID)
		pool.ExecContext(ctx, `DELETE FROM auth.users          WHERE id      = $1`, userID)
	}
	cleanup()
	defer cleanup()

	if _, err := pool.ExecContext(ctx,
		`INSERT INTO auth.users (id, email) VALUES ($1, $2)`,
		userID, "empty-test@hlc.com"); err != nil {
		t.Fatalf("seed auth.users: %v", err)
	}
	if _, err := pool.ExecContext(ctx,
		`INSERT INTO paliad.users
		   (id, email, display_name, office, lang, email_preferences,
		    reminder_morning_time, reminder_evening_time, reminder_timezone,
		    reminder_warning_offset_days)
		 VALUES ($1, $2, 'Empty Test', 'munich', 'de', '{}'::jsonb,
		         '09:00:00', '16:00:00', 'Europe/Berlin', 7)`,
		userID, "empty-test@hlc.com"); err != nil {
		t.Fatalf("seed paliad.users: %v", err)
	}

	mail, err := NewMailService()
	if err != nil {
		t.Fatalf("NewMailService: %v", err)
	}
	users := NewUserService(pool)
	svc := NewReminderService(pool, mail, users, "https://paliad.test")

	// Pin to a moment inside the morning slot.
	svc.clock = func() time.Time {
		return time.Date(2026, 4, 28, 7, 30, 0, 0, time.UTC)
	}
	svc.RunOnce(ctx)

	var n int
	if err := pool.GetContext(ctx, &n,
		`SELECT count(*) FROM paliad.reminder_log WHERE user_id = $1`, userID); err != nil {
		t.Fatalf("count log: %v", err)
	}
	if n != 0 {
		t.Errorf("empty-digest user got %d log rows, want 0 (no email should fire)", n)
	}
}

// Sanity: builders run the new digest template through MailService (which
// renders even when Enabled()=false), so a typo'd field would fail loud.
// This also exercises the deadline_digest.html template against the data
// shape deliverDigest produces.
func TestDeliverDigest_RendersTemplate(t *testing.T) {
	mail, err := NewMailService()
	if err != nil {
		t.Fatalf("NewMailService: %v", err)
	}
	svc := NewReminderService(nil, mail, nil, "https://paliad.test")

	u := models.User{
		ID:    uuid.New(),
		Email: "render@hlc.com",
		Lang:  "de",
	}
	rows := []digestRow{
		{
			DeadlineID:       uuid.New(),
			Title:            "Klageerwiderung",
			DueDate:          time.Date(2026, 4, 27, 0, 0, 0, 0, time.UTC),
			OwnerID:          u.ID,
			OwnerName:        "Test User",
			ProjectReference: "2026/0001",
			ProjectTitle:     "Acme v Widget",
			Category:         "overdue",
		},
		{
			DeadlineID:       uuid.New(),
			Title:            "Beschwerdebegründung",
			DueDate:          time.Date(2026, 4, 28, 0, 0, 0, 0, time.UTC),
			OwnerID:          u.ID,
			OwnerName:        "Test User",
			ProjectReference: "2026/0002",
			ProjectTitle:     "Acme v Gadget",
			Category:         "due_today",
		},
	}
	if err := svc.deliverDigest(u, "evening", rows); err != nil {
		t.Fatalf("deliverDigest: %v", err)
	}
}
