import { SF_STALE_STATE, SFError } from "../errors.js";
import { _getAdapter, intBool, parseJsonObject } from "./sf-db-core.js";

/**
 * Canonical static-score formula for an active memory.
 *
 * Previously: confidence * (1 + hit_count * 0.1). That's linear in
 * hit_count, which compounds: a memory that surfaces once gets
 * hit_count++, ranks higher, surfaces again, ranks higher still.
 * Self-reinforcing popularity, not relevance.
 *
 * Now: confidence * (1 + log(1 + hit_count) * 0.5). Same value at
 * hit_count=0 (factor 1.0), close at low counts (hit_count=2 ≈ 1.55
 * vs old 1.2), flattens fast (hit_count=10 ≈ 2.20 vs old 2.0), and
 * stays bounded under runaway hits (hit_count=100 ≈ 3.30 vs old 11.0).
 *
 * Used by: memory-store ranked queries, memory-tools search ranker,
 * supersedeLowestRankedMemories pruner (this file). One formula
 * everywhere — no SQL/JS skew possible.
 *
 * Confidence in [0..1] by convention; hit_count non-negative integer.
 * Defensive against NaN/negative inputs because the DB column is
 * free-form numeric.
 */
export function computeStaticMemoryScore(confidence, hitCount) {
	const c = Number.isFinite(confidence) ? Math.max(0, confidence) : 0;
	const h = Number.isFinite(hitCount) ? Math.max(0, hitCount) : 0;
	return c * (1 + Math.log(1 + h) * 0.5);
}

export function getActiveMemories({ category, limit = 200 } = {}) {
	const currentDb = _getAdapter();
	if (!currentDb) return [];
	const rows = category
		? currentDb
				.prepare(
					"SELECT * FROM active_memories WHERE category = ? ORDER BY updated_at DESC LIMIT ?",
				)
				.all(category, limit)
		: currentDb
				.prepare(
					"SELECT * FROM active_memories ORDER BY updated_at DESC LIMIT ?",
				)
				.all(limit);
	return rows.map((r) => ({
		id: r["id"],
		category: r["category"],
		content: r["content"],
		confidence: r["confidence"],
		sourceUnitId: r["source_unit_id"],
		tags: (() => {
			try {
				return JSON.parse(r["tags"] ?? "[]");
			} catch {
				return [];
			}
		})(),
		createdAt: r["created_at"],
		updatedAt: r["updated_at"],
	}));
}

export function insertMemoryRow(args) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	currentDb
		.prepare(`INSERT INTO memories (id, category, content, confidence, source_unit_type, source_unit_id, created_at, updated_at, tags, tenant)
     VALUES (:id, :category, :content, :confidence, :source_unit_type, :source_unit_id, :created_at, :updated_at, :tags, :tenant)`)
		.run({
			":id": args.id,
			":category": args.category,
			":content": args.content,
			":confidence": args.confidence,
			":source_unit_type": args.sourceUnitType,
			":source_unit_id": args.sourceUnitId,
			":created_at": args.createdAt,
			":updated_at": args.updatedAt,
			":tags": JSON.stringify(args.tags ?? []),
			":tenant": args.tenant ?? null,
		});
}

/**
 * Backfill NULL tenant values for existing memory rows. Returns the number of
 * rows tagged.
 *
 * Purpose: migrate existing project memories into the repo tenant boundary so
 * SM sync and recall can stay fail-closed without dropping local history.
 *
 * Consumer: memory-store startup backfill before SM-enabled sessions query or
 * sync project memory.
 *
 * @param {string} tenant - The tenant identifier to assign
 * @returns {number} - Number of rows updated
 */
export function backfillMemoryTenantRows(tenant) {
	const currentDb = _getAdapter();
	if (!currentDb) return 0;
	try {
		const result = currentDb
			.prepare("UPDATE memories SET tenant = :tenant WHERE tenant IS NULL")
			.run({ ":tenant": tenant });
		return result.changes ?? 0;
	} catch {
		return 0;
	}
}

export function rewriteMemoryId(placeholderId, realId) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	currentDb
		.prepare("UPDATE memories SET id = :real_id WHERE id = :placeholder")
		.run({
			":real_id": realId,
			":placeholder": placeholderId,
		});
}

export function updateMemoryContentRow(id, content, confidence, updatedAt) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	if (confidence != null) {
		currentDb
			.prepare(
				"UPDATE memories SET content = :content, confidence = :confidence, updated_at = :updated_at WHERE id = :id",
			)
			.run({
				":content": content,
				":confidence": confidence,
				":updated_at": updatedAt,
				":id": id,
			});
	} else {
		currentDb
			.prepare(
				"UPDATE memories SET content = :content, updated_at = :updated_at WHERE id = :id",
			)
			.run({ ":content": content, ":updated_at": updatedAt, ":id": id });
	}
}

export function incrementMemoryHitCount(id, updatedAt) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	currentDb
		.prepare(
			"UPDATE memories SET hit_count = hit_count + 1, updated_at = :updated_at WHERE id = :id",
		)
		.run({ ":updated_at": updatedAt, ":id": id });
}

export function supersedeMemoryRow(oldId, newId, updatedAt) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	currentDb
		.prepare(
			"UPDATE memories SET superseded_by = :new_id, updated_at = :updated_at WHERE id = :old_id",
		)
		.run({ ":new_id": newId, ":updated_at": updatedAt, ":old_id": oldId });
}

export function markMemoryUnitProcessed(unitKey, activityFile, processedAt) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	currentDb
		.prepare(`INSERT OR IGNORE INTO memory_processed_units (unit_key, activity_file, processed_at)
     VALUES (:key, :file, :at)`)
		.run({ ":key": unitKey, ":file": activityFile, ":at": processedAt });
}

/**
 * Insert a memory extraction attempt row.
 *
 * Purpose: keep skipped and failed memory closeouts visible without marking
 * the unit as fully processed.
 *
 * Consumer: memory-extractor and /memory status diagnostics.
 */
export function insertMemoryExtractionAttempt(args) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	currentDb
		.prepare(`INSERT INTO memory_extraction_attempts
      (unit_key, unit_type, unit_id, activity_file, status, failure_class, reason, error, created_at)
      VALUES (:unit_key, :unit_type, :unit_id, :activity_file, :status, :failure_class, :reason, :error, :created_at)`)
		.run({
			":unit_key": args.unitKey,
			":unit_type": args.unitType ?? null,
			":unit_id": args.unitId ?? null,
			":activity_file": args.activityFile ?? null,
			":status": args.status,
			":failure_class": args.failureClass ?? null,
			":reason": args.reason ?? null,
			":error": args.error ?? null,
			":created_at": args.createdAt,
		});
}

/**
 * Return recent memory extraction attempt rows.
 *
 * Purpose: expose memory closeout health to diagnostics without requiring
 * ad-hoc SQL access on machines that lack sqlite3.
 *
 * Consumer: /memory status and focused memory lifecycle tests.
 */
export function listMemoryExtractionAttempts(limit = 10) {
	const currentDb = _getAdapter();
	if (!currentDb) return [];
	return currentDb
		.prepare(`SELECT * FROM memory_extraction_attempts
      ORDER BY created_at DESC, id DESC
      LIMIT :limit`)
		.all({ ":limit": limit });
}

export function decayMemoriesBefore(cutoffTs, now) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	currentDb
		.prepare(`UPDATE memories
     SET confidence = MAX(0.1, confidence - 0.1), updated_at = :now
     WHERE superseded_by IS NULL AND updated_at < :cutoff AND confidence > 0.1`)
		.run({ ":now": now, ":cutoff": cutoffTs });
}

export function expireStaleMemories(unstartedTtlDays = 28, maxTtlDays = 90) {
	const currentDb = _getAdapter();
	if (!currentDb) return 0;
	const now = new Date().toISOString();
	const cutoffUnstarted = new Date(
		Date.now() - unstartedTtlDays * 86_400_000,
	).toISOString();
	const cutoffMax = new Date(
		Date.now() - maxTtlDays * 86_400_000,
	).toISOString();
	const result = currentDb
		.prepare(`UPDATE memories SET superseded_by = 'ttl-expired', updated_at = :now
     WHERE superseded_by IS NULL
     AND (
       (hit_count = 0 AND updated_at < :cutoff_unstarted)
       OR updated_at < :cutoff_max
     )`)
		.run({
			":now": now,
			":cutoff_unstarted": cutoffUnstarted,
			":cutoff_max": cutoffMax,
		});
	return result.changes ?? 0;
}

export function supersedeLowestRankedMemories(limit, now) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	if (!Number.isFinite(limit) || limit <= 0) return;
	// JS-side ranking via computeStaticMemoryScore so the formula is
	// shared with memory-store's read paths. The candidate pool is
	// bounded by the same SUPERSEDE LIMIT — fetch just enough to know
	// which ids are the lowest-scoring.
	const candidates = currentDb
		.prepare(
			`SELECT id, confidence, hit_count FROM memories WHERE superseded_by IS NULL`,
		)
		.all();
	if (candidates.length === 0) return;
	const ranked = candidates
		.map((r) => ({
			id: r.id,
			score: computeStaticMemoryScore(r.confidence, r.hit_count),
		}))
		.sort((a, b) => a.score - b.score)
		.slice(0, limit);
	if (ranked.length === 0) return;
	// IN-list with parameterised placeholders, one per id, so we never
	// build the SQL with string concat.
	const placeholders = ranked.map((_, i) => `:id${i}`).join(", ");
	const params = { ":now": now };
	ranked.forEach((r, i) => {
		params[`:id${i}`] = r.id;
	});
	currentDb
		.prepare(
			`UPDATE memories SET superseded_by = 'CAP_EXCEEDED', updated_at = :now
       WHERE id IN (${placeholders})`,
		)
		.run(params);
}

export function insertMemorySourceRow(args) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	currentDb
		.prepare(`INSERT OR IGNORE INTO memory_sources (id, kind, uri, title, content, content_hash, imported_at, scope, tags)
     VALUES (:id, :kind, :uri, :title, :content, :content_hash, :imported_at, :scope, :tags)`)
		.run({
			":id": args.id,
			":kind": args.kind,
			":uri": args.uri,
			":title": args.title,
			":content": args.content,
			":content_hash": args.contentHash,
			":imported_at": args.importedAt,
			":scope": args.scope ?? "project",
			":tags": JSON.stringify(args.tags ?? []),
		});
}

export function deleteMemorySourceRow(id) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	const res = currentDb
		.prepare("DELETE FROM memory_sources WHERE id = :id")
		.run({ ":id": id });
	return (res?.changes ?? 0) > 0;
}

export function insertJudgment(entry) {
	const currentDb = _getAdapter();
	if (!currentDb) return;
	try {
		currentDb
			.prepare(`INSERT INTO judgments (unit_id, decision, alternatives_json, reasoning, confidence, ts)
       VALUES (:unit_id, :decision, :alternatives_json, :reasoning, :confidence, :ts)`)
			.run({
				":unit_id": entry.unitId ?? "",
				":decision": entry.decision ?? "",
				":alternatives_json": JSON.stringify(entry.alternatives ?? []),
				":reasoning": entry.reasoning ?? "",
				":confidence": entry.confidence ?? "medium",
				":ts": entry.ts ?? new Date().toISOString(),
			});
	} catch {
		// Judgment logging is best-effort
	}
}

export function getJudgmentsForUnit(unitIdPrefix, limit = 1000) {
	const currentDb = _getAdapter();
	if (!currentDb) return [];
	try {
		const rows = currentDb
			.prepare(
				`SELECT id, unit_id AS unitId, decision, alternatives_json AS alternativesJson, reasoning, confidence, ts
         FROM judgments
         WHERE unit_id LIKE :prefix
         ORDER BY ts DESC
         LIMIT :limit`,
			)
			.all({
				":prefix": `${unitIdPrefix}%`,
				":limit": limit,
			});
		return rows.map((r) => ({
			id: r.id,
			unitId: r.unitId,
			decision: r.decision,
			alternatives: parseJsonObject(r.alternativesJson, []),
			reasoning: r.reasoning,
			confidence: r.confidence,
			ts: r.ts,
		}));
	} catch {
		return [];
	}
}

export function insertRetrievalEvidence(args) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	const now = args.recordedAt ?? new Date().toISOString();
	currentDb
		.prepare(`INSERT INTO retrieval_evidence (
        backend, source_kind, query, strategy, scope, project_root,
        git_head, git_branch, worktree_dirty, freshness, status,
        hit_count, elapsed_ms, cache_path, error, result_json, recorded_at
      ) VALUES (
        :backend, :source_kind, :query, :strategy, :scope, :project_root,
        :git_head, :git_branch, :worktree_dirty, :freshness, :status,
        :hit_count, :elapsed_ms, :cache_path, :error, :result_json, :recorded_at
      )`)
		.run({
			":backend": args.backend,
			":source_kind": args.sourceKind ?? "code",
			":query": args.query ?? "",
			":strategy": args.strategy ?? "",
			":scope": args.scope ?? "",
			":project_root": args.projectRoot ?? "",
			":git_head": args.gitHead ?? null,
			":git_branch": args.gitBranch ?? null,
			":worktree_dirty": intBool(args.worktreeDirty),
			":freshness": args.freshness ?? "unknown",
			":status": args.status ?? "ok",
			":hit_count": args.hitCount ?? 0,
			":elapsed_ms": args.elapsedMs ?? 0,
			":cache_path": args.cachePath ?? null,
			":error": args.error ?? null,
			":result_json": JSON.stringify(args.result ?? {}),
			":recorded_at": now,
		});
}

export function getRetrievalEvidence(limit = 100) {
	const currentDb = _getAdapter();
	if (!currentDb) return [];
	const rows = currentDb
		.prepare(`SELECT
        id, backend, source_kind AS sourceKind, query, strategy, scope,
        project_root AS projectRoot, git_head AS gitHead,
        git_branch AS gitBranch, worktree_dirty AS worktreeDirty,
        freshness, status, hit_count AS hitCount, elapsed_ms AS elapsedMs,
        cache_path AS cachePath, error, result_json AS resultJson, recorded_at AS recordedAt
      FROM retrieval_evidence
      ORDER BY recorded_at DESC, id DESC
      LIMIT :limit`)
		.all({ ":limit": limit });
	return rows.map((row) => ({
		...row,
		worktreeDirty: row.worktreeDirty === 1,
		result: parseJsonObject(row.resultJson, {}),
	}));
}

export function upsertMemoryEmbedding(args) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	currentDb
		.prepare(`INSERT INTO memory_embeddings (memory_id, model, dim, vector, updated_at)
     VALUES (:memory_id, :model, :dim, :vector, :updated_at)
     ON CONFLICT(memory_id) DO UPDATE SET
       model = excluded.model,
       dim = excluded.dim,
       vector = excluded.vector,
       updated_at = excluded.updated_at`)
		.run({
			":memory_id": args.memoryId,
			":model": args.model,
			":dim": args.dim,
			":vector": args.vector,
			":updated_at": args.updatedAt,
		});
}

export function deleteMemoryEmbedding(memoryId) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	const res = currentDb
		.prepare("DELETE FROM memory_embeddings WHERE memory_id = :id")
		.run({ ":id": memoryId });
	return (res?.changes ?? 0) > 0;
}
