package performance

import (
	"context"
	"database/sql"
	"strings"
	"time"

	"github.com/rs/zerolog"
)

// Scorer is the periodic catch-up job: link unlinked rows by fuzzy name
// match against the torrents table, then refresh final_uploaded_bytes /
// final_ratio for every linked row whose torrent has a more-recent snapshot.
//
// Designed to run via the Phase 4 ReconcileLoop. One pass per cycle is
// cheap (one query per unlinked row, one per linked row); the operator's
// historical row count grows linearly with total grabs, which at single-
// operator scale is well within SQLite's index-driven query latency.
type Scorer struct {
	db     *sql.DB
	logger zerolog.Logger
}

// NewScorer wraps a *sql.DB.
func NewScorer(db *sql.DB, logger zerolog.Logger) *Scorer {
	return &Scorer{
		db:     db,
		logger: logger.With().Str("component", "performance-scorer").Logger(),
	}
}

// Name satisfies scrape.Reconciler.
func (s *Scorer) Name() string { return "performance-scorer" }

// Reconcile is the one-pass entry point.
func (s *Scorer) Reconcile(ctx context.Context) error {
	if err := s.linkUnlinked(ctx); err != nil {
		s.logger.Warn().Err(err).Msg("linkUnlinked")
	}
	return s.updateScores(ctx)
}

// linkUnlinked finds filter_performance rows with NULL info_hash whose
// release_name fuzzy-matches a recently-seen torrent. The fuzzy match is
// case-insensitive substring containment in either direction — the simplest
// rule that handles common naming drift between release-name announcements
// and the actual torrent file name. Documented in DECISIONS.md D29.
func (s *Scorer) linkUnlinked(ctx context.Context) error {
	cutoff := time.Now().Add(-linkWindow).Unix()
	rows, err := s.db.QueryContext(ctx, `
		SELECT id, release_name, grabbed_at
		FROM filter_performance
		WHERE info_hash IS NULL AND grabbed_at >= ? AND release_name IS NOT NULL
	`, cutoff)
	if err != nil {
		return err
	}
	defer rows.Close()

	type pending struct {
		ID        int64
		Name      string
		GrabbedAt int64
	}
	var batch []pending
	for rows.Next() {
		var p pending
		var name sql.NullString
		if err := rows.Scan(&p.ID, &name, &p.GrabbedAt); err != nil {
			return err
		}
		if !name.Valid {
			continue
		}
		p.Name = name.String
		batch = append(batch, p)
	}
	if err := rows.Err(); err != nil {
		return err
	}

	// For each pending row, try to find a matching torrent. Stop at the
	// first match — multiple matches would be a heuristic failure; surface
	// it in a future phase if it ever matters.
	for _, p := range batch {
		hash, ok, err := s.findMatchingTorrent(ctx, p.Name, p.GrabbedAt)
		if err != nil {
			s.logger.Warn().Err(err).Int64("row", p.ID).Msg("fuzzy match query failed")
			continue
		}
		if !ok {
			continue
		}
		_, err = s.db.ExecContext(ctx,
			`UPDATE filter_performance SET info_hash = ? WHERE id = ? AND info_hash IS NULL`,
			hash, p.ID)
		if err != nil {
			s.logger.Warn().Err(err).Int64("row", p.ID).Msg("fuzzy match update failed")
		}
	}
	return nil
}

func (s *Scorer) findMatchingTorrent(ctx context.Context, releaseName string, grabbedAt int64) (string, bool, error) {
	candidates, err := s.db.QueryContext(ctx, `
		SELECT info_hash, name FROM torrents
		WHERE first_seen_at >= ? AND first_seen_at <= ?
	`, grabbedAt-60, grabbedAt+int64(linkWindow.Seconds()))
	if err != nil {
		return "", false, err
	}
	defer candidates.Close()
	releaseLower := strings.ToLower(releaseName)
	for candidates.Next() {
		var hash, name string
		if err := candidates.Scan(&hash, &name); err != nil {
			return "", false, err
		}
		nameLower := strings.ToLower(name)
		if nameLower == releaseLower ||
			strings.Contains(nameLower, releaseLower) ||
			strings.Contains(releaseLower, nameLower) {
			return hash, true, nil
		}
	}
	return "", false, candidates.Err()
}

// updateScores joins filter_performance with the latest torrent_snapshot per
// torrent (via window function) and refreshes final_uploaded_bytes /
// final_ratio / last_measured_at.
//
// SQLite's update-from-subquery requires the ROW_NUMBER() trick because
// torrent_snapshots is append-only (one row per scrape cycle).
func (s *Scorer) updateScores(ctx context.Context) error {
	_, err := s.db.ExecContext(ctx, `
		WITH latest AS (
		  SELECT info_hash, uploaded_bytes, ratio, timestamp,
		         ROW_NUMBER() OVER (PARTITION BY info_hash ORDER BY timestamp DESC) AS rn
		  FROM torrent_snapshots
		  WHERE simulation_id IS NULL
		)
		UPDATE filter_performance
		SET final_uploaded_bytes = (
		      SELECT uploaded_bytes FROM latest WHERE latest.info_hash = filter_performance.info_hash AND rn = 1
		    ),
		    final_ratio = (
		      SELECT ratio FROM latest WHERE latest.info_hash = filter_performance.info_hash AND rn = 1
		    ),
		    last_measured_at = ?
		WHERE info_hash IS NOT NULL
		  AND EXISTS (SELECT 1 FROM latest WHERE latest.info_hash = filter_performance.info_hash AND rn = 1)
	`, time.Now().Unix())
	return err
}