package recruiting

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

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

	"github.com/missionworkforce/missionmatch/internal/platform/audit"
	"github.com/missionworkforce/missionmatch/internal/platform/tenant"
)

// PeriodRow is one bucket of a time series report.
type PeriodRow struct {
	Start string `json:"start"`
	Label string `json:"label"`
	// Opened, Filled and so on are counted on the period the EVENT falls in, in the organization's
	// timezone. A requisition opened in one period and filled in another appears in both, once each,
	// which is what makes a period series add up to the totals.
	Opened      int    `json:"opened"`
	Filled      int    `json:"filled"`
	Submissions int    `json:"submissions"`
	Placements  int    `json:"placements"`
	Activities  int    `json:"activities"`
	Connects    int    `json:"connects"`
	ConnectRate string `json:"connect_rate,omitempty"`
}

type ReportView struct {
	Period   Period      `json:"period"`
	Since    string      `json:"since"`
	TimeZone string      `json:"time_zone"`
	Rows     []PeriodRow `json:"rows"`
	Totals   PeriodRow   `json:"totals"`
}

// Report builds a time series at the requested grain.
//
// Each metric is counted on the period its own event falls in, then the buckets are merged. Counting
// everything on the requisition's open date instead would be simpler and wrong: a placement in March
// against an order opened in January is March's placement, and attributing it to January makes every
// month look empty until the quarter it was sold in.
func (s *Service) Report(ctx context.Context, p Period, sinceRaw string) (ReportView, error) {
	if s.db == nil {
		return ReportView{}, ErrNoDatabase
	}
	if p == "" {
		p = PeriodMonth
	}
	if !periods[p] {
		return ReportView{}, &ValidationError{Field: "period",
			Message: "must be day, week, month, quarter or fiscal_quarter"}
	}
	since, err := optTime("since", sinceRaw)
	if err != nil {
		return ReportView{}, err
	}
	if since == nil {
		t := time.Now().UTC().AddDate(0, 0, -365)
		since = &t
	}
	out := ReportView{Period: p, Since: ts(*since), Rows: []PeriodRow{}}

	err = s.db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error {
		if e := tx.QueryRow(ctx, `SELECT rm_org_tz()`).Scan(&out.TimeZone); e != nil {
			return translatePG(e)
		}
		// Five independent series, each bucketed on its own event date, then full outer joined on the
		// bucket so a period with activity but no placements still appears.
		q := `
			WITH opened AS (
			  SELECT ` + periodExpr("r.opened_at", p) + ` AS b, count(*) AS n
			    FROM requisitions r WHERE r.deleted_at IS NULL AND r.opened_at >= $1 GROUP BY 1),
			filled AS (
			  SELECT ` + periodExpr("r.closed_at", p) + ` AS b, count(*) AS n
			    FROM requisitions r WHERE r.deleted_at IS NULL AND r.status = 'filled'
			     AND r.closed_at IS NOT NULL AND r.closed_at >= $1 GROUP BY 1),
			subs AS (
			  SELECT ` + periodExpr("a.created_at", p) + ` AS b, count(*) AS n
			    FROM applications a WHERE a.created_at >= $1 GROUP BY 1),
			placed AS (
			  SELECT ` + periodExpr("pl.created_at", p) + ` AS b, count(*) AS n
			    FROM placements pl WHERE pl.created_at >= $1 GROUP BY 1),
			acts AS (
			  SELECT ` + periodExpr("ra.occurred_at", p) + ` AS b,
			         count(*) AS n,
			         count(*) FILTER (WHERE ra.outcome IN ('connected','replied')) AS c
			    FROM recruiter_activities ra
			   WHERE ra.occurred_at >= $1 AND NOT ra.auto_captured GROUP BY 1)
			SELECT b,
			       COALESCE(max(o), 0), COALESCE(max(f), 0), COALESCE(max(s), 0),
			       COALESCE(max(pp), 0), COALESCE(max(an), 0), COALESCE(max(ac), 0)
			  FROM (
			    SELECT b, n AS o, NULL::bigint f, NULL::bigint s, NULL::bigint pp, NULL::bigint an, NULL::bigint ac FROM opened
			    UNION ALL SELECT b, NULL, n, NULL, NULL, NULL, NULL FROM filled
			    UNION ALL SELECT b, NULL, NULL, n, NULL, NULL, NULL FROM subs
			    UNION ALL SELECT b, NULL, NULL, NULL, n, NULL, NULL FROM placed
			    UNION ALL SELECT b, NULL, NULL, NULL, NULL, n, c FROM acts) u
			 GROUP BY b ORDER BY b ASC`
		rows, e := tx.Query(ctx, q, *since)
		if e != nil {
			return translatePG(e)
		}
		defer rows.Close()
		for rows.Next() {
			var start time.Time
			var r PeriodRow
			if e := rows.Scan(&start, &r.Opened, &r.Filled, &r.Submissions,
				&r.Placements, &r.Activities, &r.Connects); e != nil {
				return translatePG(e)
			}
			r.Start = start.Format(dateLayout)
			r.Label = periodLabel(start, p)
			r.ConnectRate = rateOf(r.Connects, r.Activities)
			out.Rows = append(out.Rows, r)

			out.Totals.Opened += r.Opened
			out.Totals.Filled += r.Filled
			out.Totals.Submissions += r.Submissions
			out.Totals.Placements += r.Placements
			out.Totals.Activities += r.Activities
			out.Totals.Connects += r.Connects
		}
		if e := rows.Err(); e != nil {
			return e
		}
		out.Totals.Label = "Total"
		out.Totals.ConnectRate = rateOf(out.Totals.Connects, out.Totals.Activities)
		return nil
	})
	if err != nil {
		return ReportView{}, err
	}
	return out, nil
}

// ExportKind names what a CSV contains.
type ExportKind string

const (
	ExportPeriods    ExportKind = "periods"
	ExportPlacements ExportKind = "placements"
	ExportPipeline   ExportKind = "pipeline"
	ExportActivity   ExportKind = "activity"
)

var exportKinds = map[ExportKind]bool{
	ExportPeriods: true, ExportPlacements: true, ExportPipeline: true, ExportActivity: true,
}

// Export renders a report as CSV and records that it happened.
//
// Every cell passes through csvCell, so a candidate name carrying a spreadsheet formula is neutralised
// before it reaches a file a client will open.
//
// The audit row is not optional bookkeeping. Export logging is a named requirement of the security
// baseline this product is built to, and an export is the moment tenant data leaves the system: it is
// precisely the event an incident review needs and the one nothing else records.
func (s *Service) Export(ctx context.Context, kind ExportKind, p Period, sinceRaw string) (string, string, error) {
	if s.db == nil {
		return "", "", ErrNoDatabase
	}
	if !exportKinds[kind] {
		return "", "", &ValidationError{Field: "kind", Message: "is not a permitted export"}
	}
	actor := actorPtr(ctx)
	if actor == nil {
		return "", "", tenant.ErrNoTenant
	}

	var body, filename string
	var rowCount int
	stamp := time.Now().UTC().Format("20060102")

	switch kind {
	case ExportPeriods:
		v, err := s.Report(ctx, p, sinceRaw)
		if err != nil {
			return "", "", err
		}
		b := newCSV()
		b.row("Period", "Start", "Opened", "Filled", "Submissions", "Placements", "Activities", "Connects", "Connect rate %")
		for _, r := range v.Rows {
			b.row(r.Label, r.Start, itoa(r.Opened), itoa(r.Filled), itoa(r.Submissions),
				itoa(r.Placements), itoa(r.Activities), itoa(r.Connects), r.ConnectRate)
		}
		b.row("Total", "", itoa(v.Totals.Opened), itoa(v.Totals.Filled), itoa(v.Totals.Submissions),
			itoa(v.Totals.Placements), itoa(v.Totals.Activities), itoa(v.Totals.Connects), v.Totals.ConnectRate)
		body, rowCount = b.String(), b.Rows()
		filename = "recruiting-" + string(p) + "-" + stamp + ".csv"

	case ExportPlacements:
		list, err := s.ListPlacements(ctx, "", 200, 0)
		if err != nil {
			return "", "", err
		}
		// FIELD SENSITIVITY APPLIES TO AN EXPORT TOO, and it is the export that matters most: a hidden column
		// on a screen is a glance, a downloaded file is a copy that leaves the building. The capability to run
		// an export says a person may take data out; it does not say WHICH data, and rate and margin are
		// governed by the same setting here as everywhere else.
		//
		// The columns STAY and carry the word redacted. Dropping them would hand two people the same named
		// report with different shapes, which breaks whatever they paste it into, and blanking them would read
		// as a placement with no rate agreed, which is a false statement rather than a withheld one.
		policy := s.FieldPolicy(ctx)
		redactPlacementList(&list, policy)
		hiddenRate, hiddenMargin := "", ""
		if !policy.CanSeeRates {
			hiddenRate = "redacted"
		}
		if !policy.CanSeeMargins {
			hiddenMargin = "redacted"
		}
		// THE COLUMN NAMES CHANGED, and that is the point rather than a side effect. This file used to head two
		// columns "Margin per hour" and "Margin %" and fill them with bill minus pay, so anybody who opened it
		// in a spreadsheet and summed the column got a number they believed was profit. A downloaded file
		// outlives every explanation attached to the screen it came from, so the header has to be true on its
		// own. Spread and margin are now separate columns and the wrap that produced the second is named beside
		// it, so a reader can check the arithmetic rather than trust it.
		b := newCSV()
		b.row("Candidate", "Job order", "Client", "Status", "Type", "Start", "End",
			"Bill rate", "Pay rate", "Spread per hour", "Spread %",
			"Wrap %", "Cost per hour", "Margin per hour", "Margin %",
			"Fee", "Guarantee ends", "Guarantee breached")
		for _, x := range list.Placements {
			e := x.Economics
			// The spread rides with the RATES, because it is their difference and anybody holding both can do
			// the subtraction. Only the cost side is margin data.
			spread, spreadPct := decStr(e.SpreadPerHour), decStr(e.SpreadPct)
			if !policy.CanSeeRates {
				spread, spreadPct = hiddenRate, hiddenRate
			}
			wrap, cost, margin, marginPct := hiddenMargin, hiddenMargin, hiddenMargin, hiddenMargin
			fee := hiddenMargin
			if policy.CanSeeMargins {
				// UNBURDENED IS SAID IN WORDS, not left blank. A blank margin column in a spreadsheet reads as a
				// missing value somebody should chase; "no wrap set" tells them the one thing that would fill it.
				wrap, cost, margin, marginPct = "no wrap set", "no wrap set", "no wrap set", "no wrap set"
				if e.BurdenPct != nil {
					wrap, cost = e.BurdenPct.String(), decStr(e.CostPerHour)
					margin, marginPct = decStr(e.MarginPerHour), decStr(e.MarginPct)
				}
				fee = decStr(e.Fee)
			}
			breached := "no"
			if x.Guarantee.Breached {
				breached = "yes"
			}
			bill, pay := decStr(x.BillRate), decStr(x.PayRate)
			if !policy.CanSeeRates {
				bill, pay = hiddenRate, hiddenRate
			}
			b.row(x.CandidateName, x.RequisitionTitle, x.ClientName, x.Status, x.EmploymentType,
				x.StartDate, x.EndDate, bill, pay, spread, spreadPct,
				wrap, cost, margin, marginPct,
				fee, x.Guarantee.EndsOn, breached)
		}
		body, rowCount = b.String(), b.Rows()
		filename = "placements-" + stamp + ".csv"

	case ExportPipeline:
		list, err := s.ListApplications(ctx, "", "", 200, 0)
		if err != nil {
			return "", "", err
		}
		b := newCSV()
		b.row("Candidate", "Job order", "Client", "Stage", "Furthest stage", "Days in stage",
			"Disposition", "Source", "Owner")
		for _, a := range list.Applications {
			b.row(a.CandidateName, a.RequisitionTitle, a.ClientName, a.Stage, a.FurthestStage,
				formatDays(a.DaysInStage), a.DispositionCode, a.SourceFirstTouch, a.OwnerName)
		}
		body, rowCount = b.String(), b.Rows()
		filename = "pipeline-" + stamp + ".csv"

	case ExportActivity:
		list, err := s.ListActivities(ctx, ActivityFilter{Since: sinceRaw}, 200, 0)
		if err != nil {
			return "", "", err
		}
		b := newCSV()
		b.row("When", "Recruiter", "Type", "Direction", "Outcome", "Candidate", "Job order", "Note")
		for _, x := range list.Activities {
			b.row(x.OccurredAt, x.ActorName, x.Kind, x.Direction, x.Outcome,
				x.CandidateName, x.RequisitionTitle, x.Body)
		}
		body, rowCount = b.String(), b.Rows()
		filename = "activity-" + stamp + ".csv"
	}

	// Record the export. Written after the content is assembled so a failed report is not logged as a
	// successful disclosure, and the row count is included because "who exported what, and how much"
	// is the question an incident review actually asks.
	if err := s.db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error {
		nv, _ := json.Marshal(map[string]any{
			"kind": string(kind), "period": string(p), "since": sinceRaw,
			"rows": rowCount, "bytes": len(body), "filename": filename,
		})
		return s.audit.OnTx(ctx, tx, audit.Event{
			Action: "export.run", ActorUserID: actor,
			EntityType: "export", EntityID: string(kind), NewValues: nv,
		})
	}); err != nil {
		return "", "", err
	}
	return body, filename, nil
}

// decStr renders an optional money value. Empty rather than "0" when absent, because a rate that was
// never agreed and a rate of zero are different facts and a spreadsheet cannot tell them apart once
// they both read zero.
func decStr(d *decimal.Decimal) string {
	if d == nil {
		return ""
	}
	return d.String()
}

// formatDays renders a day count for a spreadsheet: fixed notation, never scientific, because a cell
// reading 1.2e+02 is not a number anyone can sum.
func formatDays(f float64) string {
	return strconv.FormatFloat(f, 'f', 1, 64)
}