package infra

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"path/filepath"
	"strings"
	"time"

	_ "github.com/mattn/go-sqlite3"
)

// CapabilityEntry represents one function/pipeline line in the emitted markdown.
type CapabilityEntry struct {
	ID          string
	Signature   string
	Description string
	UpdatedAt   string // RFC3339 or date only, used for Fresh section
}

// CapabilitiesMdResult holds the three sections for the emitted markdown block.
type CapabilitiesMdResult struct {
	Top20           []CapabilityEntry // top 20 by calls_total (or fallback updated_at)
	Fresh7d         []CapabilityEntry // updated in last 7 days
	TopPipelines    []CapabilityEntry // top 5 pipelines by calls_total (or updated_at)
	TelemetryAvail  bool              // false = call_monitor.db not found or empty
	GeneratedAt     string            // RFC3339 timestamp
}

const capMdMaxDescLen = 80

func truncateDesc(s string) string {
	// strip newlines so description stays on one line
	s = strings.ReplaceAll(s, "\n", " ")
	s = strings.ReplaceAll(s, "\r", " ")
	if len(s) > capMdMaxDescLen {
		return s[:capMdMaxDescLen-3] + "..."
	}
	return s
}

// EmitCapabilitiesMd generates the markdown block for embedding in CLAUDE.md.
// It queries:
//  1. registry.db (root) for function metadata (signature, description, updated_at).
//  2. call_monitor/operations.db (projects/fn_monitoring/apps/call_monitor/) for
//     function_stats.calls_total. Falls back gracefully when unavailable.
//
// Exclusions:
//   - kind = "component" (frontend components, handled separately)
//   - lang = "cpp" AND file_path LIKE 'cpp/apps/%' (auto-touched C++ app files)
//   - tags containing "pendiente-usar" (orphaned/deprecated)
func EmitCapabilitiesMd(registryRoot string) (CapabilitiesMdResult, error) {
	result := CapabilitiesMdResult{
		GeneratedAt: time.Now().UTC().Format("2006-01-02T15:04"),
	}

	// --- 1. Open registry.db ---
	regDB, err := sql.Open("sqlite3", fmt.Sprintf("file:%s?mode=ro&_foreign_keys=on",
		filepath.Join(registryRoot, "registry.db")))
	if err != nil {
		return result, fmt.Errorf("emit_capabilities_md: open registry.db: %w", err)
	}
	defer regDB.Close()
	if err := regDB.Ping(); err != nil {
		return result, fmt.Errorf("emit_capabilities_md: ping registry.db: %w", err)
	}

	// --- 2. Try opening call_monitor/operations.db ---
	monitorDBPath := filepath.Join(registryRoot,
		"projects", "fn_monitoring", "apps", "call_monitor", "operations.db")
	monitorDB, monErr := sql.Open("sqlite3",
		fmt.Sprintf("file:%s?mode=ro", monitorDBPath))
	if monErr == nil {
		if pingErr := monitorDB.Ping(); pingErr != nil {
			monitorDB.Close()
			monitorDB = nil
		} else {
			result.TelemetryAvail = true
		}
	}
	if monitorDB != nil {
		defer monitorDB.Close()
	}

	// --- 3. Load call counts from function_stats if available ---
	// map[function_id]calls_total
	callCounts := map[string]int64{}
	if result.TelemetryAvail {
		rows, err := monitorDB.Query(
			`SELECT function_id, calls_total FROM function_stats WHERE calls_total > 0`)
		if err == nil {
			defer rows.Close()
			for rows.Next() {
				var fid string
				var ct int64
				if rows.Scan(&fid, &ct) == nil {
					callCounts[fid] = ct
				}
			}
		}
	}

	// --- 4. Load eligible functions from registry.db ---
	// Eligible: kind IN ('function','pipeline'), not component, not cpp/apps/, not pendiente-usar
	type fnRow struct {
		id          string
		kind        string
		signature   string
		description string
		updatedAt   string
		tags        string // JSON array
	}

	rows, err := regDB.Query(`
		SELECT id, kind, signature, description, updated_at, tags
		FROM functions
		WHERE kind IN ('function', 'pipeline')
		  AND NOT (lang = 'cpp' AND file_path LIKE 'cpp/apps/%')
		ORDER BY updated_at DESC
	`)
	if err != nil {
		return result, fmt.Errorf("emit_capabilities_md: query functions: %w", err)
	}
	defer rows.Close()

	var allFns []fnRow
	for rows.Next() {
		var r fnRow
		if err := rows.Scan(&r.id, &r.kind, &r.signature, &r.description, &r.updatedAt, &r.tags); err != nil {
			continue
		}
		// Exclude pendiente-usar
		var tagList []string
		_ = json.Unmarshal([]byte(r.tags), &tagList)
		pending := false
		for _, t := range tagList {
			if strings.TrimSpace(t) == "pendiente-usar" {
				pending = true
				break
			}
		}
		if pending {
			continue
		}
		allFns = append(allFns, r)
	}
	if err := rows.Err(); err != nil {
		return result, fmt.Errorf("emit_capabilities_md: rows: %w", err)
	}

	// --- 5. Build Top20 (functions only, kind=function) by calls_total desc ---
	// When telemetry unavailable, order by updated_at desc (already sorted that way).
	type scored struct {
		fnRow
		score int64 // calls_total if telemetry, else 0 (order preserved)
	}

	var fnOnly []scored
	var pipelines []scored
	for _, r := range allFns {
		s := scored{fnRow: r, score: callCounts[r.id]}
		if r.kind == "pipeline" {
			pipelines = append(pipelines, s)
		} else {
			fnOnly = append(fnOnly, s)
		}
	}

	// Sort functions by score desc (stable: updated_at desc is the tiebreaker from query order)
	if result.TelemetryAvail {
		stableSort(fnOnly, func(a, b scored) bool { return a.score > b.score })
		stableSort(pipelines, func(a, b scored) bool { return a.score > b.score })
	}
	// else: already in updated_at DESC from SQL

	// Top 20 functions
	top := min20(fnOnly)
	for _, s := range top {
		result.Top20 = append(result.Top20, CapabilityEntry{
			ID:          s.id,
			Signature:   s.signature,
			Description: truncateDesc(s.description),
			UpdatedAt:   dateOnly(s.updatedAt),
		})
	}

	// --- 6. Fresh 7d (functions + pipelines updated in last 7 days) ---
	cutoff := time.Now().UTC().Add(-7 * 24 * time.Hour)
	seen := map[string]bool{}
	// Mark top20 IDs so we can still include them in fresh if they were touched
	for _, e := range result.Top20 {
		seen[e.ID] = false // false = not yet in fresh
	}
	for _, r := range allFns {
		t, err := parseTime(r.updatedAt)
		if err != nil || t.Before(cutoff) {
			continue
		}
		// include any kind (function or pipeline) touched in last 7d
		result.Fresh7d = append(result.Fresh7d, CapabilityEntry{
			ID:          r.id,
			Signature:   r.signature,
			Description: truncateDesc(r.description),
			UpdatedAt:   dateOnly(r.updatedAt),
		})
	}

	// --- 7. Top 5 pipelines ---
	top5 := min5(pipelines)
	for _, s := range top5 {
		result.TopPipelines = append(result.TopPipelines, CapabilityEntry{
			ID:          s.id,
			Signature:   s.signature,
			Description: truncateDesc(s.description),
			UpdatedAt:   dateOnly(s.updatedAt),
		})
	}

	return result, nil
}

// RenderCapabilitiesMd formats CapabilitiesMdResult as a markdown string.
func RenderCapabilitiesMd(r CapabilitiesMdResult) string {
	var sb strings.Builder

	sb.WriteString("<!-- AUTO-GENERATED by `fn doctor capabilities --emit-claude-md` — do not edit by hand -->\n")
	sb.WriteString(fmt.Sprintf("<!-- Last refresh: %s -->\n\n", r.GeneratedAt))

	// Section 1: Top 20
	if !r.TelemetryAvail {
		sb.WriteString("## Capabilities — Top 20 (by updated_at, telemetry unavailable)\n\n")
	} else {
		sb.WriteString("## Capabilities — Top 20 (by calls_total)\n\n")
	}
	if len(r.Top20) == 0 {
		sb.WriteString("_(no functions found)_\n")
	}
	for _, e := range r.Top20 {
		sig := e.Signature
		if sig == "" {
			sig = e.ID
		}
		sb.WriteString(fmt.Sprintf("- `%s` — `%s` — %s\n", e.ID, sig, e.Description))
	}

	// Section 2: Fresh 7d
	sb.WriteString("\n## Capabilities — Fresh (last 7d)\n\n")
	if len(r.Fresh7d) == 0 {
		sb.WriteString("_(no functions updated in the last 7 days)_\n")
	}
	for _, e := range r.Fresh7d {
		sb.WriteString(fmt.Sprintf("- `%s` (%s) — %s\n", e.ID, e.UpdatedAt, e.Description))
	}

	// Section 3: Top 5 pipelines
	sb.WriteString("\n## Pipelines — Top 5 (one-shot composites)\n\n")
	if len(r.TopPipelines) == 0 {
		sb.WriteString("_(no pipelines found)_\n")
	}
	for _, e := range r.TopPipelines {
		sig := e.Signature
		if sig == "" {
			sig = e.ID
		}
		sb.WriteString(fmt.Sprintf("- `%s` — `%s` — %s\n", e.ID, sig, e.Description))
	}

	return sb.String()
}

// --- helpers ---

func stableSort[T any](slice []T, less func(a, b T) bool) {
	// insertion sort — small slices (<500), stable
	for i := 1; i < len(slice); i++ {
		for j := i; j > 0 && less(slice[j], slice[j-1]); j-- {
			slice[j], slice[j-1] = slice[j-1], slice[j]
		}
	}
}

func min20[T any](s []T) []T {
	if len(s) <= 20 {
		return s
	}
	return s[:20]
}

func min5[T any](s []T) []T {
	if len(s) <= 5 {
		return s
	}
	return s[:5]
}

func dateOnly(ts string) string {
	// "2026-05-14T01:30:00Z" -> "2026-05-14"
	if len(ts) >= 10 {
		return ts[:10]
	}
	return ts
}

func parseTime(ts string) (time.Time, error) {
	formats := []string{
		time.RFC3339,
		"2006-01-02T15:04:05Z",
		"2006-01-02T15:04:05",
		"2006-01-02",
	}
	for _, f := range formats {
		if t, err := time.Parse(f, ts); err == nil {
			return t, nil
		}
	}
	return time.Time{}, fmt.Errorf("cannot parse time: %q", ts)
}