package eeo

import (
	"context"
	"errors"
	"time"

	"github.com/jackc/pgx/v5"
)

// activeModelVersion returns the version string of the AEDT scoring model in force at snapshot time: the
// aedt_model_versions row with the latest effective_from, the same ordering the governance read path
// uses. The compliance role has SELECT on aedt_model_versions (granted in migration 0010). When the
// registry is empty it returns an empty string, which the snapshot stores as the model_version default.
func activeModelVersion(ctx context.Context, tx pgx.Tx) (string, error) {
	var version string
	err := tx.QueryRow(ctx, activeModelVersionQuery).Scan(&version)
	if errors.Is(err, pgx.ErrNoRows) {
		return "", nil
	}
	if err != nil {
		return "", err
	}
	return version, nil
}

const activeModelVersionQuery = `
SELECT version
FROM aedt_model_versions
ORDER BY effective_from DESC, version
LIMIT 1`

// insertSnapshot appends one fairness_snapshots row through the compliance pool inside the tenant tx, so
// RLS scopes the write to the caller's org. The organization_id is taken from the GUC the tenant tx set,
// never from a client value, exactly like the candidate_demographics insert. selection_rate and
// impact_ratio bind as decimals (impact_ratio is NULL for the reference group).
func insertSnapshot(ctx context.Context, tx pgx.Tx, snapAt time.Time, model string, r snapshotRow) error {
	var impact any
	if r.impactRatio != nil {
		impact = *r.impactRatio
	}
	_, err := tx.Exec(ctx, insertSnapshotQuery,
		snapAt, r.decisionContext, r.groupLabel, r.selected, r.total,
		r.selectionRate, impact, r.belowThreshold, model)
	return err
}

const insertSnapshotQuery = `
INSERT INTO eeo.fairness_snapshots
    (organization_id, snapshot_at, decision_context, group_label, selected, total,
     selection_rate, impact_ratio, below_threshold, model_version)
VALUES (
    NULLIF(current_setting('app.current_org_id', true), '')::uuid,
    $1, $2, $3, $4, $5, $6, $7, $8, $9)`

// trendQuery reads every appended snapshot for the caller's org at or after the since instant, ordered
// by bucket then time so each (decision_context, group_label) series arrives chronologically. RLS scopes
// it to the caller's org. impact_ratio is nullable and scans into a pointer.
const trendQuery = `
SELECT decision_context, group_label, snapshot_at, selected, total, selection_rate, impact_ratio, model_version
FROM eeo.fairness_snapshots
WHERE snapshot_at >= $1
ORDER BY decision_context, group_label, snapshot_at`