package storage

import (
	"context"
	"encoding/json"
	"errors"
	"fmt"
	"regexp"
	"sort"
	"strings"
	"time"

	"github.com/jackc/pgx/v5"

	"archivdms/internal/matching"
)

// ErrSuggestionNotFound is returned when no metadata_suggestions row exists
// for a document (GetLatestSuggestion) or the id/tenant scope does not match
// (MarkSuggestionReviewed).
var ErrSuggestionNotFound = errors.New("storage: metadata suggestion not found")

// suggestionFloor is the minimum fuzzy score (0..1) at which a
// tag/document_type/correspondent is surfaced as a NON-binding suggestion.
// It sits deliberately BELOW matching.FuzzyThreshold (currently 0.85, the
// auto-assign confidence): candidates at/above FuzzyThreshold that were not
// already auto-assigned (e.g. a second document_type that also matched, or a
// name-only near-match on an entity whose configured algorithm isn't fuzzy)
// are strong suggestions; candidates in [suggestionFloor, FuzzyThreshold) are
// the near-misses this feature exists to expose for manual review. 0.55 keeps
// noise low while still catching typo-level OCR differences.
const suggestionFloor = 0.55

// maxSuggestionCandidates caps how many candidates are kept per category
// (tags / document_types / correspondents), sorted by score descending.
const maxSuggestionCandidates = 5

// autoGeneratedTitlePattern matches the timestamp placeholder title produced
// by titleFromOCRText ("Scan DD.MM.YYYY HH:MM") when no meaningful heading
// could be derived on ingest. A current title matching this is treated as
// "not a real title yet", so a re-derived title is suggested.
var autoGeneratedTitlePattern = regexp.MustCompile(`^Scan \d{2}\.\d{2}\.\d{4} \d{2}:\d{2}$`)

// SuggestionCandidate is one scored, non-binding metadata suggestion for a
// single taxonomy entity. Score is the fuzzy similarity in [0,1].
type SuggestionCandidate struct {
	ID          int64    `json:"id"`
	Name        string   `json:"name"`
	Score       float64  `json:"score"`
	Explanation []string `json:"explanation,omitempty"`
}

// DocumentDateCandidate is a non-binding belegdatum (invoice/document date)
// suggestion re-derived from the OCR text. Date is the ISO date (YYYY-MM-DD)
// the frontend can apply via PUT /api/documents/{id}/document-date; Score is a
// fixed heuristic confidence (the regex date scanner has no per-match score).
type DocumentDateCandidate struct {
	Date  string  `json:"date"`
	Score float64 `json:"score"`
}

// SuggestionPayload is the JSONB body persisted in metadata_suggestions.suggestion.
// A nil Title means no title suggestion was made (current title already looks
// human-authored). The candidate slices are always non-nil (possibly empty).
// DocumentDateCandidate is nil when the document already has a belegdatum set or
// no plausible date could be recognised in the OCR text.
type SuggestionPayload struct {
	Title                   *string                `json:"title,omitempty"`
	DocTypeCandidates       []SuggestionCandidate  `json:"doc_type_candidates"`
	CorrespondentCandidates []SuggestionCandidate  `json:"correspondent_candidates"`
	TagCandidates           []SuggestionCandidate  `json:"tag_candidates"`
	DocumentDateCandidate   *DocumentDateCandidate `json:"document_date_candidate,omitempty"`
}

// MetadataSuggestion is one persisted suggestion run for a document. It is a
// log/cache of what the heuristic provider proposed — applying an accepted
// field goes through the normal edit endpoints, NOT through this row.
type MetadataSuggestion struct {
	ID          int64             `json:"id"`
	TenantID    int64             `json:"tenant_id"`
	DocumentID  int64             `json:"document_id"`
	Provider    string            `json:"provider"`
	RequestedBy *int64            `json:"requested_by,omitempty"`
	RequestedAt time.Time         `json:"requested_at"`
	Suggestion  SuggestionPayload `json:"suggestion"`
	Status      string            `json:"status"`
	ReviewedAt  *time.Time        `json:"reviewed_at,omitempty"`
	ReviewedBy  *int64            `json:"reviewed_by,omitempty"`
}

// initMetadataSuggestionsSchema creates the metadata_suggestions table.
// Idempotent, called from (*Store).initSchema AFTER the documents/taxonomy
// schema exists. Documented (not executed) in
// migrations/012_metadata_suggestions.sql.
func (s *Store) initMetadataSuggestionsSchema(ctx context.Context) error {
	_, err := s.db.Exec(ctx, `
		CREATE TABLE IF NOT EXISTS metadata_suggestions (
			id            BIGSERIAL PRIMARY KEY,
			tenant_id     BIGINT NOT NULL,
			document_id   BIGINT NOT NULL,
			provider      TEXT NOT NULL DEFAULT 'heuristic',
			requested_by  BIGINT,
			requested_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
			suggestion    JSONB NOT NULL,
			status        TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','reviewed')),
			reviewed_at   TIMESTAMPTZ,
			reviewed_by   BIGINT
		);
		CREATE INDEX IF NOT EXISTS idx_metadata_suggestions_document ON metadata_suggestions(document_id);
	`)
	if err != nil {
		return fmt.Errorf("storage: create metadata suggestions table: %w", err)
	}
	return nil
}

const metadataSuggestionCols = `id, tenant_id, document_id, provider, requested_by, requested_at, suggestion, status, reviewed_at, reviewed_by`

func scanMetadataSuggestion(row interface {
	Scan(dest ...any) error
}) (*MetadataSuggestion, error) {
	var m MetadataSuggestion
	var payload []byte
	if err := row.Scan(&m.ID, &m.TenantID, &m.DocumentID, &m.Provider, &m.RequestedBy,
		&m.RequestedAt, &payload, &m.Status, &m.ReviewedAt, &m.ReviewedBy); err != nil {
		return nil, err
	}
	m.Suggestion = SuggestionPayload{
		DocTypeCandidates:       make([]SuggestionCandidate, 0),
		CorrespondentCandidates: make([]SuggestionCandidate, 0),
		TagCandidates:           make([]SuggestionCandidate, 0),
	}
	if len(payload) > 0 {
		if err := json.Unmarshal(payload, &m.Suggestion); err != nil {
			return nil, fmt.Errorf("storage: unmarshal suggestion payload: %w", err)
		}
	}
	return &m, nil
}

// GenerateHeuristicSuggestions builds a fresh, rule-based (no LLM) metadata
// suggestion for a document: it fuzzy-scores every taxonomy entity's name (and
// its configured match_pattern, if any) against the document's title+OCR text,
// surfaces the near-misses above suggestionFloor that are NOT already assigned,
// and — if the current title still looks auto-generated — proposes a
// re-derived title. The result is persisted as a metadata_suggestions row and
// returned. requestedBy may be nil for non-interactive callers.
func (s *Store) GenerateHeuristicSuggestions(ctx context.Context, documentID, tenantID int64, requestedBy *int64) (*MetadataSuggestion, error) {
	doc, err := s.GetDocument(ctx, documentID, tenantID)
	if err != nil {
		return nil, err // ErrDocumentNotFound propagates
	}

	haystack := doc.Title
	if doc.OCRText != "" {
		haystack = doc.Title + "\n" + doc.OCRText
	}

	// Entities already assigned to the document — excluded from suggestions.
	assignedTags := map[int64]bool{}
	tags, err := s.ListDocumentTags(ctx, documentID, tenantID)
	if err != nil {
		return nil, err
	}
	for _, t := range tags {
		assignedTags[t.ID] = true
	}

	tagCands, err := s.scoreCandidates(ctx, "tags", tenantID, haystack, func(id int64) bool { return assignedTags[id] })
	if err != nil {
		return nil, err
	}
	docTypeCands, err := s.scoreCandidates(ctx, "document_types", tenantID, haystack, func(id int64) bool {
		return doc.DocTypeID != nil && *doc.DocTypeID == id
	})
	if err != nil {
		return nil, err
	}
	corrCands, err := s.scoreCandidates(ctx, "correspondents", tenantID, haystack, func(id int64) bool {
		return doc.CorrespondentID != nil && *doc.CorrespondentID == id
	})
	if err != nil {
		return nil, err
	}

	payload := SuggestionPayload{
		DocTypeCandidates:       docTypeCands,
		CorrespondentCandidates: corrCands,
		TagCandidates:           tagCands,
	}
	if autoGeneratedTitlePattern.MatchString(strings.TrimSpace(doc.Title)) {
		if t := heuristicTitle(doc.OCRText); t != "" && t != doc.Title {
			payload.Title = &t
		}
	}
	// Belegdatum suggestion: only when the document has no document_date yet and
	// a plausible date is recognisable in the OCR text. Surfaced as a chip the
	// user can apply via PUT /api/documents/{id}/document-date. The confidence is
	// now derived from keyword-proximity scoring (see documentDateFromTextWithScore)
	// instead of a fixed value.
	if doc.DocumentDate == nil {
		if d, sc, ok := documentDateFromTextWithScore(doc.OCRText); ok {
			payload.DocumentDateCandidate = &DocumentDateCandidate{
				Date:  d.Format("2006-01-02"),
				Score: sc,
			}
		}
	}

	raw, err := json.Marshal(payload)
	if err != nil {
		return nil, fmt.Errorf("storage: marshal suggestion payload: %w", err)
	}

	row := s.db.QueryRow(ctx, `
		INSERT INTO metadata_suggestions (tenant_id, document_id, provider, requested_by, suggestion)
		VALUES ($1, $2, 'heuristic', $3, $4)
		RETURNING `+metadataSuggestionCols,
		tenantID, documentID, requestedBy, raw)
	m, err := scanMetadataSuggestion(row)
	if err != nil {
		return nil, fmt.Errorf("storage: insert metadata suggestion: %w", err)
	}
	return m, nil
}

// scoreCandidates fuzzy-scores every entity of a kind for a tenant against the
// haystack, keeps those at/above suggestionFloor that are not excluded (already
// assigned), sorts by score descending and caps at maxSuggestionCandidates.
func (s *Store) scoreCandidates(ctx context.Context, kind string, tenantID int64, haystack string, excluded func(id int64) bool) ([]SuggestionCandidate, error) {
	entities, err := s.ListTaxonomyEntities(ctx, kind, tenantID)
	if err != nil {
		return nil, err
	}
	out := make([]SuggestionCandidate, 0)
	for _, e := range entities {
		if excluded(e.ID) {
			continue
		}
		score := matching.FuzzyScore(e.Name, e.CaseSensitive, haystack)
		if e.MatchPattern != "" {
			if p := matching.FuzzyScore(e.MatchPattern, e.CaseSensitive, haystack); p > score {
				score = p
			}
		}
		if score < suggestionFloor {
			continue
		}
		out = append(out, SuggestionCandidate{ID: e.ID, Name: e.Name, Score: score})
	}
	sort.SliceStable(out, func(i, j int) bool { return out[i].Score > out[j].Score })
	if len(out) > maxSuggestionCandidates {
		out = out[:maxSuggestionCandidates]
	}
	return out, nil
}

// heuristicTitle re-derives a candidate title from OCR text using a different
// heuristic than titleFromOCRText (which takes the first meaningful line): it
// picks the longest trimmed line, which is more likely to be a real heading /
// company line than a short letterhead fragment or page number. Returns "" if
// no line qualifies. Kept simple on purpose (no NLP).
func heuristicTitle(ocrText string) string {
	if ocrText == "" {
		return ""
	}
	best := ""
	bestLen := 0
	for _, line := range strings.Split(ocrText, "\n") {
		line = strings.TrimSpace(line)
		r := []rune(line)
		if len(r) < 5 {
			continue
		}
		if len(r) > bestLen {
			bestLen = len(r)
			if len(r) > 120 {
				line = string(r[:120])
			}
			best = line
		}
	}
	return best
}

// GetLatestSuggestion returns the most recent metadata_suggestions row for a
// document, scoped to tenant ownership, or ErrSuggestionNotFound if none exist.
func (s *Store) GetLatestSuggestion(ctx context.Context, documentID, tenantID int64) (*MetadataSuggestion, error) {
	row := s.db.QueryRow(ctx, `SELECT `+metadataSuggestionCols+`
		FROM metadata_suggestions
		WHERE document_id = $1 AND tenant_id = $2
		ORDER BY requested_at DESC, id DESC
		LIMIT 1`, documentID, tenantID)
	m, err := scanMetadataSuggestion(row)
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return nil, ErrSuggestionNotFound
		}
		return nil, fmt.Errorf("storage: get latest metadata suggestion: %w", err)
	}
	return m, nil
}

// MarkSuggestionReviewed flags a suggestion row as reviewed (the user has acted
// on it in the UI, regardless of which fields they accepted — those went
// through the normal edit endpoints). Scoped to tenant ownership. Returns
// ErrSuggestionNotFound if the id/tenant scope does not match.
func (s *Store) MarkSuggestionReviewed(ctx context.Context, id, tenantID, reviewedBy int64) error {
	tag, err := s.db.Exec(ctx, `
		UPDATE metadata_suggestions
		SET status = 'reviewed', reviewed_at = now(), reviewed_by = $1
		WHERE id = $2 AND tenant_id = $3`, reviewedBy, id, tenantID)
	if err != nil {
		return fmt.Errorf("storage: mark metadata suggestion reviewed: %w", err)
	}
	if tag.RowsAffected() == 0 {
		return ErrSuggestionNotFound
	}
	return nil
}